CyanHall.com 创建于 2020-11-13, 上次更新:2021-04-30。
👉  github shields 如果有用请点赞。

1. 命令行登录

    mysql -h localhost -u root -p
// /usr/local/Cellar/mysql@xx/x.xx.xx/bin/mysql -h localhost -u root
  

2. 导入 SQL 文件

    mysql -u username -p database_name < file.sql
  

3. Dump all databases

    mysqldump --user root --password  --all-databases > all-databases.sql
mysql --user root --password mysql < all-databases.sql
  

4. 列出所有数据库

    show databases;
  

5. 列出一个数据库中的所有表

    use [db name];
show tables;
  

6. 列出所有用户

    SELECT User, Host, authentication_string FROM mysql.user;
  

7. 创建用户和数据库

    # Create a user
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'password';

# Create a database
CREATE DATABASE example_db;

# Grant privileges
GRANT ALL ON example_db.* TO 'db_user'@'localhost';
  

8. 删除用户和数据库

    # Delete database
DROP DATABASE example_db;

# Delete user
DROP USER 'db_user'@'localhost';
  

9. 退出命令行

    quit
  

10. sql_mode

    SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

# result: 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

SET GLOBAL sql_mode = '...';
SET SESSION sql_mode = '...';

# Remove ONLY_FULL_GROUP_BY
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
  

11. JOIN

    INNER JOIN:如果表中有至少一个匹配,则返回行
LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行
FULL JOIN:只要其中一个表中存在匹配,则返回行
# LEFT JOIN and LEFT OUTER JOIN are equivalent.
  

12. UUID

    select uuid_short() as id;
  

13. SQL Select with Subquery

    USE database_name;
SELECT COUNT(*) FROM table_name WHERE table_name.a_id NOT IN (1,2,3,4) AND table_name.b_id = 12 AND table_name.c_id IN (SELECT some_id FROM another_table WHERE another_table.d_id = 34);
  

1. 命令行登录

    mysql -h localhost -u root -p
// /usr/local/Cellar/mysql@xx/x.xx.xx/bin/mysql -h localhost -u root
  

3. Dump all databases

    mysqldump --user root --password  --all-databases > all-databases.sql
mysql --user root --password mysql < all-databases.sql
  

5. 列出一个数据库中的所有表

    use [db name];
show tables;
  

7. 创建用户和数据库

    # Create a user
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'password';

# Create a database
CREATE DATABASE example_db;

# Grant privileges
GRANT ALL ON example_db.* TO 'db_user'@'localhost';
  

9. 退出命令行

    quit
  

11. JOIN

    INNER JOIN:如果表中有至少一个匹配,则返回行
LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行
FULL JOIN:只要其中一个表中存在匹配,则返回行
# LEFT JOIN and LEFT OUTER JOIN are equivalent.
  

13. SQL Select with Subquery

    USE database_name;
SELECT COUNT(*) FROM table_name WHERE table_name.a_id NOT IN (1,2,3,4) AND table_name.b_id = 12 AND table_name.c_id IN (SELECT some_id FROM another_table WHERE another_table.d_id = 34);
  

2. 导入 SQL 文件

    mysql -u username -p database_name < file.sql
  

4. 列出所有数据库

    show databases;
  

6. 列出所有用户

    SELECT User, Host, authentication_string FROM mysql.user;
  

8. 删除用户和数据库

    # Delete database
DROP DATABASE example_db;

# Delete user
DROP USER 'db_user'@'localhost';
  

10. sql_mode

    SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

# result: 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

SET GLOBAL sql_mode = '...';
SET SESSION sql_mode = '...';

# Remove ONLY_FULL_GROUP_BY
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
  

12. UUID

    select uuid_short() as id;
  


Maitained by Cyanhall.com, Copy Rights @ CC BY-NC-SA 4.0