创建用户
为了安全起见，不应该使用root用户登录MySQL，除非是localhost的管理任务。应该创建用户、限制访问、限制资源使用等等。为了创建新用户，你应该具有CREATE USER权限，现在，我们使用root用户创建其它用户
具体操作
使用root用户连接到MySQL，执行CREATE USER 命令来创建新用户
mysql> create user if not exists 'lisi'@'localhost' identified with \
-> mysql_native_password by 'Com.123456' \
-> with MAX_QUERIES_PER_HOUR 500 \
-> MAX_UPDATES_PER_HOUR 100;
Query OK, 0 rows affected (0.02 sec)
以上命令将为用户创建以下内容：
- 用户名：lisi
- 仅从localhost主机访问数据库，可以限制对IP范围的访问，例如10.148.%.%。通过给出%，用户可以从任何主机访问。
- 密码：Com.123456
- 使用mysql_native_password身份验证方法，还可以指定其他的身份验证方式，例如：sha256_password、LDAP或者Kerberos。默认是caching_sha2_password；
- 用户可以在一小时内执行的最大查询数为500；
- 用户可以在一小时内执行的最大更新次数为100次。
连接数据库
当客户端连接到MySQL服务器时，它会经历两个访问控制阶段：
- 连接验证
- 请求验证
在连接验证过程中，服务器通过用户名和连接的主机名来识别连接。服务器会调用用户认证插件并验证密码。服务器还会检查用户是否被锁定。
在请求验证阶段，服务器会检查用户是否有足够的权限执行每项操作。
用户权限控制
可以限制用户访问特定数据库或表，或限制特定操作，如select、insert和update。需要用户GRANT权限，才能为其他用户授予权限。可以使用root用户管理权限，也可以创建管理员账号来管理权限
授予权限
- 将READ ONLY(select)权限授予lisi用户：
mysql> grant select on employees.* to 'lisi'@'localhost';
Query OK, 0 rows affected (0.06 sec)
- 授予zhangsan用户仅能对employees表有查询权限：
mysql> grant select on employees.employees to 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)
- 授予zhangsan用户仅能对employees表的first_name和last_name列有查询权限
mysql> grant select(first_name,last_name) on employees.employees to 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)
- 扩展权限。可以通过执行新授权来扩展权限。将权限扩展到zhangsan用户，以访问薪资（salaries）表中的薪水：
mysql> grant select(salary) on employees.salaries to 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.01 sec)
- 创建超级（SUPER）用户。需要一个管理员账号来管理该服务器。ALL表示除GRANT权限之外的所有权限。
mysql> create user 'dbadmin'@'%' identified with mysql_native_password by 'Com.123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to 'dbadmin'@'%';
Query OK, 0 rows affected (0.01 sec)
- 授予GRANT权限。用户拥有GRANT OPTION权限才能授予其他用户权限。
mysql> grant grant option on *.* to 'dbadmin'@'%';
Query OK, 0 rows affected (0.00 sec)
检查权限
可以检查所有用户的授权。
- 检查zhangsan用户的授权：
mysql> show grants for 'zhangsan'@'localhost'\G;
*************************** 1. row ***************************
Grants for zhangsan@localhost: GRANT USAGE ON *.* TO `zhangsan`@`localhost`
*************************** 2. row ***************************
Grants for zhangsan@localhost: GRANT SELECT, SELECT (`first_name`, `last_name`) ON `employees`.`employees` TO `zhangsan`@`localhost`
*************************** 3. row ***************************
Grants for zhangsan@localhost: GRANT SELECT (`salary`) ON `employees`.`salaries` TO `zhangsan`@`localhost`
3 rows in set (0.00 sec)
- 检查dbadmin用户的权限。可以看到其拥有的所有权限：
mysql> show grants for 'dbadmin'@'%'\G;
*************************** 1. row ***************************
Grants for dbadmin@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `dbadmin`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for dbadmin@%: GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `dbadmin`@`%` WITH GRANT OPTION
2 rows in set (0.00 sec)
撤销权限
撤销权限与创建权限的语法相同。向用户授权限用TO，撤销用户的权限用FROM。
- 撤销’lisi’@’localhost’用户的DELETE访问权限：
mysql> revoke delete on employees.* from 'lisi'@'localhost';
Query OK, 0 rows affected (0.01 sec)
- 撤销zhangsan用户对薪水列的访问权限：
mysql> revoke select(salary) on employees.salaries from 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)
修改mysql.user表
所有用户信息及权限都存储在mysql.user表中。如果你有权访问mysql.user表，则可以直接通过修改mysql.user表来创建用户并授予权限。如果使用GRANT、REVOKE、SET PASSWORD或RENAME USER等账户管理语句间接修改授权表，会立即生效，如果是直接修改mysql.user表，不会立即生效，除非重启服务器或者重新加载表，可以通过FLUSH PRIVILEGES语句来完成GRANT表的重新加载。
查找mysql.user表以找出dbadmin用户的所有条目：
mysql> select * from mysql.user where User='dbadmin' \G;
*************************** 1. row ***************************
Host: %
User: dbadmin
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *0E2B40BFB441C623555504F0949FC6988A578064
password_expired: N
password_last_changed: 2019-08-11 23:18:47
password_lifetime: NULL
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
1 row in set (0.00 sec)
可以看到dbadmin用户可以从任意主机（%）访问数据库，更新mysql.user表，令其只能从localhost访问数据库。
mysql> update mysql.user set Host='localhost' where User='dbadmin';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
设置用户密码有效期
可以设置一段时间作为用户密码的有效期，过期之后用户则需要更改密码。
当应用程序开发人员要求访问数据库时，可以设置默认密码创建该账户，并将其设置为过期状态，则其必须更改密码才能继续使用MySQL。
创建所有用户并设置其密码过期日期等于default_password_lifetime变量的值，默认情况下用户被禁用。
- 创建一个具有过期密码的用户。第一次登陆时会报错：1820（HY000）,这时候需要使用ALTER USER语句重置密码：
mysql> create user 'developer'@'%' identified with mysql_native_password by 'Com.123456' password expire;
Query OK, 0 rows affected (0.01 sec)
开发人员登陆
[root@www ~]# mysql -udeveloper -pCom.123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16
Copyright (c) 2000, 2019, 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> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
开发人员更改密码
mysql> alter user 'developer'@'%' identified with mysql_native_password by '123456.Com';
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
- 手动设置用户过期
mysql> alter user 'developer'@'%' password expire;
Query OK, 0 rows affected (0.00 sec)
- 要求用户每隔90天更改一次密码：
mysql> alter user 'developer'@'%' password expire interval 90 day;
Query OK, 0 rows affected (0.00 sec)
锁定用户
如果发现账户有任何问题，可以将其锁定。使用CREATE USER或ALTER USER 锁定用户。
- 通过将ACCOUNT LOCK子句添加到ALTER USER语句来锁定账户：
mysql> ALTER USER 'developer'@'%' ACCOUNT LOCK;
Query OK, 0 rows affected (0.01 sec)
账户登录后会提示账户被锁定
[root@www ~]# mysql -udeveloper -p123456.Com
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'developer'@'localhost'. Account is locked.
- 解除锁定
mysql> ALTER USER 'developer'@'%' ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.00 sec)
为用户创建角色
MySQL的角色是一个权限的集合。与用户一样，角色的权限可以被授予和撤销。用户账号被授予角色后，该角色会将其拥有的权限授予该账户。
- 创建角色：
mysql> create role 'app_read_only','app_writes','app_developer';
Query OK, 0 rows affected (0.01 sec)
- 使用GRANT语句为角色分配权限：
mysql> grant select on employees.* to 'app_read_only';
Query OK, 0 rows affected (0.00 sec)
mysql> grant insert,update,delete on employees.* to 'app_writes';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on employees.* to 'app_developer';
Query OK, 0 rows affected (0.01 sec)
- 创建用户。如果不指定主机，则将采用%（任意主机）：
mysql> create user emp_read_only identified by 'Com.123456';
Query OK, 0 rows affected (0.01 sec)
mysql> create user emp_writes identified by 'Com.123456';
Query OK, 0 rows affected (0.01 sec)
mysql> create user emp_developer identified by 'Com.123456';
Query OK, 0 rows affected (0.01 sec)
mysql> create user emp_read_write identified by 'Com.123456';
Query OK, 0 rows affected (0.01 sec)
- 使用GRANT语句为用户分配角色。你可以为用户分配多个角色。
mysql> grant 'app_read_only' to 'emp_read_only'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> grant 'app_writes' to 'emp_writes'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant 'app_developer' to 'emp_developer'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant 'app_read_only','app_writes' to 'emp_read_write'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql库中多了2张表
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables like '%role%';
+--------------------------+
| Tables_in_mysql (%role%) |
+--------------------------+
| default_roles |
| role_edges |
+--------------------------+
2 rows in set (0.00 sec)
mysql> select * from role_edges;
+-----------+---------------+---------+----------------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+---------------+---------+----------------+-------------------+
| % | app_developer | % | emp_developer | N |
| % | app_read_only | % | emp_read_only | N |
| % | app_read_only | % | emp_read_write | N |
| % | app_writes | % | emp_read_write | N |
| % | app_writes | % | emp_writes | N |
+-----------+---------------+---------+----------------+-------------------+
5 rows in set (0.00 sec)
保存查询结果和加载数据
可以使用select into outfile语句将输出保存到文件中。可以指定列和行分割符，然后可以将数据导入其他数据平台。
保存查询结果
可以将输出目标另存为文件或表。
1、另存为文件
- 要将输出结果保存到文件中，需要拥有file权限。FILE是一个全局特权，这意味着你不能将其限制为针对特定数据库的权限。但是，你可以限制用户查询的内容：
mysql> create user if not exists 'mary'@'%' identified with mysql_native_password by 'Com.123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select on employees.* to 'mary'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> grant file on *.* to 'mary'@'%';
Query OK, 0 rows affected (0.00 sec)
- 在Ubuntu系统中，默认情况下，MySQL不允许写入文件。应该在配置文件中设置secure_file_priv并重新启动MySQL；在CentOS、Red Hat系统中，secure_file_priv被设置为/var/lib/mysql-files，意味着所有文件都将被保存在该目录中。
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
2 rows in set (0.07 sec)
- 如需修改配置文件，可以像这样设置,需重启MySQL服务器
secure_file_priv = /var/lib/mysql
- 在CentOS上可以执行以下语句保存结果,保存文件为.csv格式。
mysql> select first_name,last_name into outfile '/var/lib/mysql-files/result.csv' \
-> fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' \
-> from employees.employees where hire_date < '1986-01-01' limit 10;
Query OK, 10 rows affected (0.00 sec)
- 查看保存结果：
[root@www ~]# cat /var/lib/mysql-files/result.csv
"Bezalel","Simmel"
"Sumant","Peac"
"Eberhardt","Terkki"
"Otmar","Herbst"
"Florian","Syrotiuk"
"Tse","Herber"
"Udi","Jansch"
"Reuven","Garigliano"
"Erez","Ritzmann"
"Premal","Baek"
2、另存为表
也可以将select语句的结果保存到表中。即使表不存在，也可以使用CREATE和SELECT来创建表并加载数据。如果表已经存在，则可以使用INSERT和SELECT加载数据。
- 查看titles表中的数据
mysql> select distinct title from employees.titles;
+--------------------+
| title |
+--------------------+
| Senior Engineer |
| Staff |
| Engineer |
| Senior Staff |
| Assistant Engineer |
| Technique Leader |
| Manager |
+--------------------+
7 rows in set (0.21 sec)
- 可以将上面的结果保存为一个新的表titles_only中
mysql> create table employees.titles_only as select distinct title from employees.titles;
Query OK, 7 rows affected (0.40 sec)
Records: 7 Duplicates: 0 Warnings: 0
- 如果表已经存在，则可以使用INSERT INTO SELECT语句：
mysql> insert into employees.titles_only select distinct title from employees.titles;
Query OK, 7 rows affected (0.42 sec)
Records: 7 Duplicates: 0 Warnings: 0
- 为了避免重复，可以使用INSERT IGNORE。本例中，titles_only表中没有primary key，因此IGNORE子句不会造成任何影响。
加载数据到表中
不仅可以将表数据保存到文件中，反过来操作也可以，即将文件中的数据加载到表中，这种方式广泛用于加载批量数据的情况，并且是将数据加载到表中的超快速方式。可以指定列分隔符将数据加载到相应的列中。需要拥有表的FILE权限和INSERT权限。
- 创建一个新表，如果表已经存在，则可以直接加载。
mysql> create table employee_names (first_name varchar(14) not null, last_name varchar(16) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> desc employee_names;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
- 使用LOAD DATA INFILE语句加载数据：
mysql> load data infile '/var/lib/mysql-files/result.csv' into table employee_names \
-> fields terminated by ',' \
-> optionally enclosed by '"' \
-> lines terminated by '\n';
Query OK, 10 rows affected (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from employee_names;
+------------+------------+
| first_name | last_name |
+------------+------------+
| Bezalel | Simmel |
| Sumant | Peac |
| Eberhardt | Terkki |
| Otmar | Herbst |
| Florian | Syrotiuk |
| Tse | Herber |
| Udi | Jansch |
| Reuven | Garigliano |
| Erez | Ritzmann |
| Premal | Baek |
+------------+------------+
10 rows in set (0.00 sec)
- 如果文件开头包含一些你想忽略的行，可以使用IGNORE n Lines指定：
mysql> load data infile '/var/lib/mysql-files/result.csv' into table employee_names \
-> fields terminated by ',' \
-> optionally enclosed by '"' \
-> lines terminated by '\n' \
-> ignore 1 lines;
- 可以使用REPLACE或者IGNORE来处理重复的行：
load data infile '/var/lib/mysql-files/result.csv' REPLACE into table employee_names \
-> fields terminated by ',' \
-> optionally enclosed by '"' \
-> lines terminated by '\n';
或者：
load data infile '/var/lib/mysql-files/result.csv' IGNORE into table employee_names \
-> fields terminated by ',' \
-> optionally enclosed by '"' \
-> lines terminated by '\n';
多表关联
本节内容是如何关联两个或多个表来检索结果。
例如：你想用emp_no：110022找到员工的姓名和部门号码：
- 部门编号和名称存储在departments表中
- 员工编号和其他详细信息（例如first_name和last_name）存储在employees表中
- 员工和部门的映射关系存储在dept_manager表中
如果是使用单表查询，可以这样做：
- 从employees表中查找emp_no为110022的员工姓名：
mysql> select emp.emp_no,emp.first_name,emp.last_name from employees.employees as emp \
-> where emp.emp_no=110022;
+--------+------------+------------+
| emp_no | first_name | last_name |
+--------+------------+------------+
| 110022 | Margareta | Markovitch |
+--------+------------+------------+
1 row in set (0.00 sec)
- 从dept_manager表中查找部门编号
mysql> select dept_no from employees.dept_manager where emp_no=110022;
+---------+
| dept_no |
+---------+
| d001 |
+---------+
1 row in set (0.01 sec)
- 从departments表中找到部门名称
mysql> select dept_name from employees.departments where dept_no='d001';
+-----------+
| dept_name |
+-----------+
| Marketing |
+-----------+
1 row in set (0.00 sec)
如何使用多表操作
可以使用JOIN来关联多个表，为了关联两个表，必须要找到共同列。例如，可以基于emp_no来关联employees表和dept_manager表，因为它们都有emp_no列。虽然名称不需要匹配，但应该找出可以用来做关联的列。与之类似，dept_manager表和departments表都有dept_no列，可以将其作为共有列。
与列别名一样，可以为表提供别名，并使用别名来引用该表的列。
mysql> select emp.emp_no,emp.first_name,emp.last_name,dept.dept_name \
-> from employees as emp JOIN dept_manager as dept_mgr \
-> ON emp.emp_no=dept_mgr.emp_no AND emp.emp_no=110022 \
-> JOIN departments as dept ON dept_mgr.dept_no=dept.dept_no;
+--------+------------+------------+-----------+
| emp_no | first_name | last_name | dept_name |
+--------+------------+------------+-----------+
| 110022 | Margareta | Markovitch | Marketing |
+--------+------------+------------+-----------+
1 row in set (0.00 sec)
假设想了解每个部门的平均工资，可以使用AVG函数并按照dept_no进行分组。要找出部门名称，可以将结果与departments表通过dept_no列进行关联。
mysql> select dept_emp.dept_no,dept_name,AVG(salary) as avg_salary \
-> from salaries \
-> JOIN dept_emp ON salaries.emp_no=dept_emp.emp_no \
-> JOIN departments ON dept_emp.dept_no=departments.dept_no \
-> GROUP BY dept_emp.dept_no \
-> ORDER BY avg_salary DESC ;
+---------+--------------------+------------+
| dept_no | dept_name | avg_salary |
+---------+--------------------+------------+
| d007 | Sales | 80667.6058 |
| d001 | Marketing | 71913.2000 |
| d002 | Finance | 70489.3649 |
| d008 | Research | 59665.1817 |
| d004 | Production | 59605.4825 |
| d005 | Development | 59478.9012 |
| d009 | Customer Service | 58770.3665 |
| d006 | Quality Management | 57251.2719 |
| d003 | Human Resources | 55574.8794 |
+---------+--------------------+------------+
9 rows in set (3.40 sec)
通过与自己关联来识别重复项
如果想在某个表中为某些特定列找出重复的行。例如，你想找出哪些员工具有相同的first_name、相同的last_name、相同的gender，以及相同的hire_date。就可以将employees表跟它自己进行关联，同时在JOIN子句中指定查找重复项的列。你需要为每个表设定不同的别名。
需要为用来做关联的列添加索引，使用以下命令添加索引：
mysql> ALTER TABLE employees ADD INDEX name(first_name,last_name);
Query OK, 0 rows affected (0.76 sec)
Records: 0 Duplicates: 0 Warnings: 0
然后执行以下命令：
mysql> select emp1.* from employees as emp1 \
-> JOIN employees as emp2 \
-> ON emp1.first_name=emp2.first_name \
-> AND emp1.last_name=emp2.last_name \
-> AND emp1.gender=emp2.gender \
-> AND emp1.hire_date=emp2.hire_date \
-> AND emp1.emp_no != emp2.emp_no \
-> ORDER BY first_name,last_name;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 493600 | 1964-01-26 | Keung | Heusch | M | 1986-06-01 |
| 232772 | 1962-05-14 | Keung | Heusch | M | 1986-06-01 |
| 424486 | 1952-07-06 | Marit | Kolvik | F | 1993-12-08 |
| 64089 | 1958-01-19 | Marit | Kolvik | F | 1993-12-08 |
| 14641 | 1953-05-08 | Marsha | Farrow | M | 1989-02-18 |
| 40965 | 1952-05-11 | Marsha | Farrow | M | 1989-02-18 |
| 427429 | 1962-11-06 | Naftali | Mawatari | M | 1985-09-14 |
| 422332 | 1954-08-17 | Naftali | Mawatari | M | 1985-09-14 |
| 243627 | 1957-02-14 | Taisook | Hutter | F | 1985-02-26 |
| 19454 | 1955-05-14 | Taisook | Hutter | F | 1985-02-26 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (1.11 sec)
必须要写emp1.emp_no != emp2.emp_no ，因为想要显示的员工应拥有不同的emp_no。否则将显示同一个员工。
使用子查询
子查询是另外一个语句中的select语句。
如果想查找从1986-06-26开始担任Senior Engineer(高级工程师)的员工的姓名。可以使用JOIN查找结果，命令如下：
mysql> select first_name,last_name from employees\
-> JOIN titles ON employees.emp_no=titles.emp_no\
-> WHERE titles.title="Senior Engineer"\
-> AND titles.from_date="1986-06-26";
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgi | Facello |
| Minghong | Kalloufi |
| Nechama | Bennet |
| Nagui | Restivo |
| Shuzo | Kirkerud |
+------------+-----------+
5 rows in set (0.09 sec)
也可以从titles表中获取emp_no，并从employees表中获取姓名。
mysql> SELECT emp_no FROM titles WHERE title='Senior Engineer' AND from_date='1986-06-26';
+--------+
| emp_no |
+--------+
| 10001 |
| 84305 |
| 228917 |
| 426700 |
| 458304 |
+--------+
5 rows in set (0.08 sec)
mysql> SELECT first_name,last_name FROM employees \
-> WHERE emp_no IN (10001,84305,228917,426700,458304);
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgi | Facello |
| Minghong | Kalloufi |
| Nechama | Bennet |
| Nagui | Restivo |
| Shuzo | Kirkerud |
+------------+-----------+
5 rows in set (0.01 sec)
使用子查询
mysql> SELECT first_name,last_name FROM employees \
-> WHERE emp_no IN (SELECT emp_no FROM titles WHERE title='Senior Engineer' AND from_date='1986-06-26');
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgi | Facello |
| Minghong | Kalloufi |
| Nechama | Bennet |
| Nagui | Restivo |
| Shuzo | Kirkerud |
+------------+-----------+
5 rows in set (0.31 sec)
找出工资最高的员工的编号：
mysql> select emp_no from salaries \
-> where salary=(select MAX(salary) from salaries);
+--------+
| emp_no |
+--------+
| 43624 |
+--------+
1 row in set (3.10 sec)
查找表之间不匹配的行：
如果想在一个表中找到其他表中没有的行，可以通过两种方法实现这一点：使用NOT IN子句或使用OUTER JOIN。
要找到匹配的行，可以使用普通的JOIN；要查找不匹配的行，可以使用OUTER JOIN。普通JOIN给出A和B的一个交集；OUTER JOIN给出A和B的匹配记录，并且用NULL给出与A不匹配的记录。如果想要A-B的输出，可以使用WHERE IS NULL子句。
先创建两个employees表并插入一些值：
mysql> CREATE TABLE employees_list1 AS \
-> SELECT * FROM employees WHERE first_name LIKE 'aa%';
Query OK, 444 rows affected (0.03 sec)
Records: 444 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE employees_list2 AS \
-> SELECT * FROM employees WHERE \
-> emp_no BETWEEN 400000 AND 500000 AND gender='F';
Query OK, 39892 rows affected (0.27 sec)
Records: 39892 Duplicates: 0 Warnings: 0
如果找出两个表中都存在的员工，可以这样：
mysql> SELECT * FROM employees_list1 WHERE \
-> emp_no IN (SELECT emp_no FROM employees_list2);
或者
mysql> select a.* from employees_list1 a JOIN employees_list2 b ON a.emp_no=b.emp_no;
要找出存在于employees_list1但不存在于employees_list2中的员工，可以如下：
mysql> SELECT * FROM employees_list1 WHERE \
-> emp_no NOT IN (SELECT emp_no FROM employees_list2);
也可以使用OUTER JOIN:
mysql> SELECT a.* FROM employees_list1 a LEFT OUTER JOIN employees_list2 b \
-> ON a.emp_no=b.emp_no WHERE b.emp_no IS NULL;
LEFT OUTER JOIN 为第二个表中所有与第一个表中的行不匹配的行创建NULL列。如果使用RIGHT JOIN，则为第一个表中所有与第二个表中的行不匹配的行创建NULL列。
- 原文作者：老鱼干🦈
- 原文链接：//www.tinyfish.top:80/post/Linux_%E8%BF%90%E7%BB%B4/%E6%95%B0%E6%8D%AE%E5%BA%93/MySql/2.-MySQL%E9%AB%98%E7%BA%A7%E4%BD%BF%E7%94%A8/
- 版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议. 进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。