about

mysql5.7 + win10

https://zhuanlan.zhihu.com/p/368440685

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';


sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Microsoft Windows [版本 10.0.19043.1826]
(c) Microsoft Corporation。保留所有权利。

E:\桌面>mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

E:\桌面>mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

E:\桌面>mysql -uroot -p
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use text
Database changed
mysql> SELECT
    ->   author.name,
    ->   GROUP_CONCAT(book.title),
    ->   GROUP_CONCAT(book.price),
    ->   MAX(book.price),
    ->   book.title
    -> FROM author INNER JOIN book_authors ON author.id=book_authors.author_id INNER JOIN book ON book_authors.book_id=book.id
    -> GROUP BY author.name;
ERROR 1055 (42000): Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'text.book.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>
mysql> SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   author.name,
    ->   GROUP_CONCAT(book.title),
    ->   GROUP_CONCAT(book.price),
    ->   MAX(book.price),
    ->   book.title
    -> FROM author INNER JOIN book_authors ON author.id=book_authors.author_id INNER JOIN book ON book_authors.book_id=book.id
    -> GROUP BY author.name;
+--------+-----------------------------------+--------------------------+-----------------+--------------------+
| name   | GROUP_CONCAT(book.title)          | GROUP_CONCAT(book.price) | MAX(book.price) | title              |
+--------+-----------------------------------+--------------------------+-----------------+--------------------+
| 吴小红 | python从入门到入狱                | 85.40                    |           85.40 | python从入门到入狱 |
| 张玉   | 苏丹美食探索,python从入门到入狱   | 94.49,85.40              |           94.49 | 苏丹美食探索       |
| 李英   | python核心编程                    | 100.00                   |          100.00 | python核心编程     |
| 申雷   | python核心编程,马其顿旅游景点探秘 | 100.00,16.84             |          100.00 | python核心编程     |
| 赵淑珍 | 苏丹美食探索,python学习手册       | 94.49,50.00              |           94.49 | 苏丹美食探索       |
| 陈利   | 土耳其旅游指南                    | 62.91                    |           62.91 | 土耳其旅游指南     |
+--------+-----------------------------------+--------------------------+-----------------+--------------------+
6 rows in set (0.00 sec)

about ​

about