第四篇：记录相关操纵 - 图1

一 介绍

MySQL数据操作： DML
========================================================
在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括

  1. 使用INSERT实现数据的插入
  2. UPDATE实现数据的更新
  3. 使用DELETE实现数据的删除
  4. 使用SELECT查询数据以及。

========================================================
本节内容包括：
插入数据
更新数据
删除数据
查询数据

二 插入数据INSERT

  1. 1. 插入完整数据（顺序插入）
  2.     语法一：
  3.     INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
  5.     语法二：
  6.     INSERT INTO 表名 VALUES (值1,值2,值3…值n);
  8. 2. 指定字段插入数据
  9.     语法：
  10.     INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
  12. 3. 插入多条记录
  13.     语法：
  14.     INSERT INTO 表名 VALUES
  15.         (值1,值2,值3…值n),
  16.         (值1,值2,值3…值n),
  17.         (值1,值2,值3…值n);
  19. 4. 插入查询结果
  20.     语法：
  21.     INSERT INTO 表名(字段1,字段2,字段3…字段n) 
  22.                     SELECT (字段1,字段2,字段3…字段n) FROM 表2
  23.                     WHERE …;

三 更新数据UPDATE

  1. 语法：
  2.     UPDATE 表名 SET
  3.         字段1=值1,
  4.         字段2=值2,
  5.         WHERE CONDITION;
  7. 示例：
  8.     UPDATE mysql.user SET password=password(‘123’) 
  9.         where user=’root’ and host=’localhost’;

四 删除数据DELETE

  1. 语法：
  2.     DELETE FROM 表名 
  3.         WHERE CONITION;
  5. 示例：
  6.     DELETE FROM mysql.user 
  7.         WHERE password=’’;
  9. 练习：
  10.     更新MySQL root用户密码为mysql123
  11.     删除除从本地登录的root用户以外的所有用户

五 查询数据SELECT

1. 单表查询

1.1 单表查询的语法

  1. SELECT 字段1,字段2... FROM 表名
  2.                   WHERE 条件
  3.                   GROUP BY field
  4.                   HAVING 筛选
  5.                   ORDER BY field
  6.                   LIMIT 限制条数

1.2 关键字的执行优先级(重点)

  1. 重点中的重点：关键字的执行优先级
  2. from
  3. where
  4. group by
  5. having
  6. select
  7. distinct
  8. order by
  9. limit
  1. 1.找到表:from
  2. 2.拿着where指定的约束条件，去文件/表中取出一条条记录
  3. 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
  4. 4.将分组的结果进行having过滤
  5. 5.执行select
  6. 6.去重
  7. 7.将结果按条件排序：order by
  8. 8.限制结果的显示条数

SQL逻辑查询语句执行顺序

1.3.1 SELECT语句关键字的定义顺序

  1. SELECT DISTINCT <select_list>
  2. FROM <left_table>
  3. <join_type> JOIN <right_table>
  4. ON <join_condition>
  5. WHERE <where_condition>
  6. GROUP BY <group_by_list>
  7. HAVING <having_condition>
  8. ORDER BY <order_by_condition>
  9. LIMIT <limit_number>

1.3.2 SELECT语句关键字的执行顺序

  1. (7)     SELECT 
  2. (8)     DISTINCT <select_list>
  3. (1)     FROM <left_table>
  4. (3)     <join_type> JOIN <right_table>
  5. (2)     ON <join_condition>
  6. (4)     WHERE <where_condition>
  7. (5)     GROUP BY <group_by_list>
  8. (6)     HAVING <having_condition>
  9. (9)     ORDER BY <order_by_condition>
  10. (10)    LIMIT <limit_number>

1.3.3 准备表和数据

  • 新建一个测试数据库TestDB；

    1. create database TestDB;

  • 创建测试表table1和table2；

    1. CREATE TABLE table1
    2. (
    3. customer_id VARCHAR(10) NOT NULL,
    4. city VARCHAR(10) NOT NULL,
    5. PRIMARY KEY(customer_id)
    6. )ENGINE=INNODB DEFAULT CHARSET=UTF8;
    7. CREATE TABLE table2
    8. (
    9. order_id INT NOT NULL auto_increment,
    10. customer_id VARCHAR(10),
    11. PRIMARY KEY(order_id)
    12. )ENGINE=INNODB DEFAULT CHARSET=UTF8;

  • 插入测试数据；

    1. INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
    2. INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
    3. INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
    4. INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');
    5. INSERT INTO table2(customer_id) VALUES('163');
    6. INSERT INTO table2(customer_id) VALUES('163');
    7. INSERT INTO table2(customer_id) VALUES('9you');
    8. INSERT INTO table2(customer_id) VALUES('9you');
    9. INSERT INTO table2(customer_id) VALUES('9you');
    10. INSERT INTO table2(customer_id) VALUES('tx');
    11. INSERT INTO table2(customer_id) VALUES(NULL);

  • 准备工作做完以后，table1和table2看起来应该像下面这样：

    1. mysql> select * from table1;
    2. +-------------+----------+
    3. | customer_id | city     |
    4. +-------------+----------+
    5. | 163         | hangzhou |
    6. | 9you        | shanghai |
    7. | baidu       | hangzhou |
    8. | tx          | hangzhou |
    9. +-------------+----------+
    10. 4 rows in set (0.00 sec)
    11. mysql> select * from table2;
    12. +----------+-------------+
    13. | order_id | customer_id |
    14. +----------+-------------+
    15. |        1 | 163         |
    16. |        2 | 163         |
    17. |        3 | 9you        |
    18. |        4 | 9you        |
    19. |        5 | 9you        |
    20. |        6 | tx          |
    21. |        7 | NULL        |
    22. +----------+-------------+
    23. 7 rows in set (0.00 sec)

1.3.4 准备SQL逻辑查询测试语句

  1. #查询来自杭州，并且订单数少于2的客户。
  2.  SELECT a.customer_id, COUNT(b.order_id) as total_orders
  3.  FROM table1 AS a
  4.  LEFT JOIN table2 AS b
  5.  ON a.customer_id = b.customer_id
  6.  WHERE a.city = 'hangzhou'
  7.  GROUP BY a.customer_id
  8.  HAVING count(b.order_id) < 2
  9.  ORDER BY total_orders DESC;

1.3.5 执行顺序分析

在这些SQL语句的执行过程中，都会产生一个虚拟表，用来保存SQL语句的执行结果（这是重点），我现在就来跟踪这个虚拟表的变化，得到最终的查询结果的过程，来分析整个SQL逻辑查询的执行顺序和过程。

执行FROM语句

第一步，执行FROM语句。我们首先需要知道最开始从哪个表开始的，这就是FROM告诉我们的。现在有了和两个表，我们到底从哪个表开始，还是从两个表进行某种联系以后再开始呢？它们之间如何产生联系呢？——笛卡尔积

关于什么是笛卡尔积，请自行Google补脑。经过FROM语句对两个表执行笛卡尔积，会得到一个虚拟表，暂且叫VT1（vitual table 1），内容如下：

  1. +-------------+----------+----------+-------------+
  2. | customer_id | city     | order_id | customer_id |
  3. +-------------+----------+----------+-------------+
  4. | 163         | hangzhou |        1 | 163         |
  5. | 9you        | shanghai |        1 | 163         |
  6. | baidu       | hangzhou |        1 | 163         |
  7. | tx          | hangzhou |        1 | 163         |
  8. | 163         | hangzhou |        2 | 163         |
  9. | 9you        | shanghai |        2 | 163         |
  10. | baidu       | hangzhou |        2 | 163         |
  11. | tx          | hangzhou |        2 | 163         |
  12. | 163         | hangzhou |        3 | 9you        |
  13. | 9you        | shanghai |        3 | 9you        |
  14. | baidu       | hangzhou |        3 | 9you        |
  15. | tx          | hangzhou |        3 | 9you        |
  16. | 163         | hangzhou |        4 | 9you        |
  17. | 9you        | shanghai |        4 | 9you        |
  18. | baidu       | hangzhou |        4 | 9you        |
  19. | tx          | hangzhou |        4 | 9you        |
  20. | 163         | hangzhou |        5 | 9you        |
  21. | 9you        | shanghai |        5 | 9you        |
  22. | baidu       | hangzhou |        5 | 9you        |
  23. | tx          | hangzhou |        5 | 9you        |
  24. | 163         | hangzhou |        6 | tx          |
  25. | 9you        | shanghai |        6 | tx          |
  26. | baidu       | hangzhou |        6 | tx          |
  27. | tx          | hangzhou |        6 | tx          |
  28. | 163         | hangzhou |        7 | NULL        |
  29. | 9you        | shanghai |        7 | NULL        |
  30. | baidu       | hangzhou |        7 | NULL        |
  31. | tx          | hangzhou |        7 | NULL        |
  32. +-------------+----------+----------+-------------+

总共有28（table1的记录条数 * table2的记录条数）条记录。这就是VT1的结果，接下来的操作就在VT1的基础上进行。
执行ON过滤
执行完笛卡尔积以后，接着就进行ON a.customer_id = b.customer_id条件过滤，根据ON中指定的条件，去掉那些不符合条件的数据，得到VT2表，内容如下：

  1. +-------------+----------+----------+-------------+
  2. | customer_id | city     | order_id | customer_id |
  3. +-------------+----------+----------+-------------+
  4. | 163         | hangzhou |        1 | 163         |
  5. | 163         | hangzhou |        2 | 163         |
  6. | 9you        | shanghai |        3 | 9you        |
  7. | 9you        | shanghai |        4 | 9you        |
  8. | 9you        | shanghai |        5 | 9you        |
  9. | tx          | hangzhou |        6 | tx          |
  10. +-------------+----------+----------+-------------+

VT2就是经过ON条件筛选以后得到的有用数据，而接下来的操作将在VT2的基础上继续进行。
添加外部行
这一步只有在连接类型为OUTER JOIN时才发生，如LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。在大多数的时候，我们都是会省略掉OUTER关键字的，但OUTER表示的就是外部行的概念。
LEFT OUTER JOIN把左表记为保留表，得到的结果为：

  1. +-------------+----------+----------+-------------+
  2. | customer_id | city     | order_id | customer_id |
  3. +-------------+----------+----------+-------------+
  4. | 163         | hangzhou |        1 | 163         |
  5. | 163         | hangzhou |        2 | 163         |
  6. | 9you        | shanghai |        3 | 9you        |
  7. | 9you        | shanghai |        4 | 9you        |
  8. | 9you        | shanghai |        5 | 9you        |
  9. | tx          | hangzhou |        6 | tx          |
  10. | baidu       | hangzhou |     NULL | NULL        |
  11. +-------------+----------+----------+-------------+

RIGHT OUTER JOIN把右表记为保留表，得到的结果为：

  1. +-------------+----------+----------+-------------+
  2. | customer_id | city     | order_id | customer_id |
  3. +-------------+----------+----------+-------------+
  4. | 163         | hangzhou |        1 | 163         |
  5. | 163         | hangzhou |        2 | 163         |
  6. | 9you        | shanghai |        3 | 9you        |
  7. | 9you        | shanghai |        4 | 9you        |
  8. | 9you        | shanghai |        5 | 9you        |
  9. | tx          | hangzhou |        6 | tx          |
  10. | NULL        | NULL     |        7 | NULL        |
  11. +-------------+----------+----------+-------------+

FULL OUTER JOIN把左右表都作为保留表，得到的结果为：

  1. +-------------+----------+----------+-------------+
  2. | customer_id | city     | order_id | customer_id |
  3. +-------------+----------+----------+-------------+
  4. | 163         | hangzhou |        1 | 163         |
  5. | 163         | hangzhou |        2 | 163         |
  6. | 9you        | shanghai |        3 | 9you        |
  7. | 9you        | shanghai |        4 | 9you        |
  8. | 9you        | shanghai |        5 | 9you        |
  9. | tx          | hangzhou |        6 | tx          |
  10. | baidu       | hangzhou |     NULL | NULL        |
  11. | NULL        | NULL     |        7 | NULL        |
  12. +-------------+----------+----------+-------------+

添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表VT3。
由于我在准备的测试SQL查询逻辑语句中使用的是LEFT JOIN，过滤掉了以下这条数据：

  1. | baidu       | hangzhou |     NULL | NULL        |

现在就把这条数据添加到VT2表中，得到的VT3表如下：

  1. +-------------+----------+----------+-------------+
  2. | customer_id | city     | order_id | customer_id |
  3. +-------------+----------+----------+-------------+
  4. | 163         | hangzhou |        1 | 163         |
  5. | 163         | hangzhou |        2 | 163         |
  6. | 9you        | shanghai |        3 | 9you        |
  7. | 9you        | shanghai |        4 | 9you        |
  8. | 9you        | shanghai |        5 | 9you        |
  9. | tx          | hangzhou |        6 | tx          |
  10. | baidu       | hangzhou |     NULL | NULL        |
  11. +-------------+----------+----------+-------------+

接下来的操作都会在该VT3表上进行。
执行WHERE过滤
对添加外部行得到的VT3进行WHERE过滤，只有符合的记录才会输出到虚拟表VT4中。当我们执行WHERE a.city = 'hangzhou'的时候，就会得到以下内容，并存在虚拟表VT4中：

  1. +-------------+----------+----------+-------------+
  2. | customer_id | city     | order_id | customer_id |
  3. +-------------+----------+----------+-------------+
  4. | 163         | hangzhou |        1 | 163         |
  5. | 163         | hangzhou |        2 | 163         |
  6. | tx          | hangzhou |        6 | tx          |
  7. | baidu       | hangzhou |     NULL | NULL        |
  8. +-------------+----------+----------+-------------+

但是在使用WHERE子句时，需要注意以下两点：

  1. 由于数据还没有分组，因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类对分组统计的过滤；
  2. 由于还没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的，如：SELECT city as c FROM t WHERE c='shanghai';是不允许出现的。

执行GROUP BY分组
GROU BY子句主要是对使用WHERE子句得到的虚拟表进行分组操作。我们执行测试语句中的GROUP BY a.customer_id，就会得到以下内容(默认只显示组内第一条)：

  1. +-------------+----------+----------+-------------+
  2. | customer_id | city     | order_id | customer_id |
  3. +-------------+----------+----------+-------------+
  4. | 163         | hangzhou |        1 | 163         |
  5. | baidu       | hangzhou |     NULL | NULL        |
  6. | tx          | hangzhou |        6 | tx          |
  7. +-------------+----------+----------+-------------+

得到的内容会存入虚拟表VT5中，此时，我们就得到了一个VT5虚拟表，接下来的操作都会在该表上完成。
执行HAVING过滤
HAVING子句主要和GROUP BY子句配合使用，对分组得到的VT5虚拟表进行条件过滤。当我执行测试语句中的HAVING count(b.order_id) < 2时，将得到以下内容：

  1. +-------------+----------+----------+-------------+
  2. | customer_id | city     | order_id | customer_id |
  3. +-------------+----------+----------+-------------+
  4. | baidu       | hangzhou |     NULL | NULL        |
  5. | tx          | hangzhou |        6 | tx          |
  6. +-------------+----------+----------+-------------+

这就是虚拟表VT6。
SELECT列表
现在才会执行到SELECT子句，不要以为SELECT子句被写在第一行，就是第一个被执行的。
我们执行测试语句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders，从虚拟表VT6中选择出我们需要的内容。我们将得到以下内容：

  1. +-------------+--------------+
  2. | customer_id | total_orders |
  3. +-------------+--------------+
  4. | baidu       |            0 |
  5. | tx          |            1 |
  6. +-------------+--------------+

还没有完，这只是虚拟表VT7。
执行DISTINCT子句
如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT7是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。
由于我的测试SQL语句中并没有使用DISTINCT，所以，在该查询中，这一步不会生成一个虚拟表。
执行ORDER BY子句
对虚拟表中的内容按照指定的列进行排序，然后返回一个新的虚拟表，我们执行测试SQL语句中的ORDER BY total_orders DESC，就会得到以下内容：

  1. +-------------+--------------+
  2. | customer_id | total_orders |
  3. +-------------+--------------+
  4. | tx          |            1 |
  5. | baidu       |            0 |
  6. +-------------+--------------+

可以看到这是对total_orders列进行降序排列的。上述结果会存储在VT8中。
执行LIMIT子句
LIMIT子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY的LIMIT子句，得到的结果同样是无序的，所以，很多时候，我们都会看到LIMIT子句会和ORDER BY子句一起使用。
MySQL数据库的LIMIT支持如下形式的选择：

  1. LIMIT n, m

表示从第n条记录开始选择m条记录。而很多开发人员喜欢使用该语句来解决分页问题。对于小数据，使用LIMIT子句没有任何问题，当数据量非常大的时候，使用LIMIT n, m是非常低效的。因为LIMIT的机制是每次都是从头开始扫描，如果需要从第60万行开始，读取3条数据，就需要先扫描定位到60万行，然后再进行读取，而扫描的过程是一个非常低效的过程。所以，对于大数据处理时，是非常有必要在应用层建立一定的缓存机制（现在的大数据处理，大都使用缓存）

1.3 简单查询

  • 准备表和记录 ```python company.employee 员工id id int
    姓名 emp_name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int

创建表

create table employee( id int not null unique auto_increment, name varchar(20) not null, sex enum(‘male’,’female’) not null default ‘male’, #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int );

查看表结构

mysql> desc employee; +———————+———————————-+———+——-+————-+————————+ | Field | Type | Null | Key | Default | Extra | +———————+———————————-+———+——-+————-+————————+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum(‘male’,’female’) | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +———————+———————————-+———+——-+————-+————————+

插入记录

三个部门：教学，销售，运营

insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values (‘xio’,’male’,18,’20170301’,’teacher’,7300.33,401,1), #以下是教学部 (‘jion’,’male’,78,’20150302’,’teacher’,1000000.31,401,1), (‘wupeiqi’,’male’,81,’20130305’,’teacher’,8300,401,1), (‘yuanhao’,’male’,73,’20140701’,’teacher’,3500,401,1), (‘liwenzhou’,’male’,28,’20121101’,’teacher’,2100,401,1), (‘jingliyang’,’female’,18,’20110211’,’teacher’,9000,401,1), (‘jinxin’,’male’,18,’19000301’,’teacher’,30000,401,1), (‘成龙’,’male’,48,’20101111’,’teacher’,10000,401,1),

(‘歪歪’,’female’,48,’20150311’,’sale’,3000.13,402,2),#以下是销售部门 (‘丫丫’,’female’,38,’20101101’,’sale’,2000.35,402,2), (‘丁丁’,’female’,18,’20110312’,’sale’,1000.37,402,2), (‘星星’,’female’,18,’20160513’,’sale’,3000.29,402,2), (‘格格’,’female’,28,’20170127’,’sale’,4000.33,402,2),

(‘张野’,’male’,28,’20160311’,’operation’,10000.13,403,3), #以下是运营部门 (‘程咬金’,’male’,18,’19970312’,’operation’,20000,403,3), (‘程咬银’,’female’,18,’20130311’,’operation’,19000,403,3), (‘程咬铜’,’male’,18,’20150411’,’operation’,18000,403,3), (‘程咬铁’,’female’,18,’20140512’,’operation’,17000,403,3) ;

ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

  1. ```python
  2. #简单查询
  3.     SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
  4.     FROM employee;
  6.     SELECT * FROM employee;
  8.     SELECT name,salary FROM employee;
  10. #避免重复DISTINCT
  11.     SELECT DISTINCT post FROM employee;    
  13. #通过四则运算查询
  14.     SELECT name, salary*12 FROM employee;
  15.     SELECT name, salary*12 AS Annual_salary FROM employee;
  16.     SELECT name, salary*12 Annual_salary FROM employee;
  18. #定义显示格式
  19.    CONCAT() 函数用于连接字符串
  20.    SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary 
  21.    FROM employee;
  23.    CONCAT_WS() 第一个参数为分隔符
  24.    SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
  25.    FROM employee;
  27.    结合CASE语句：
  28.    SELECT
  29.        (
  30.            CASE
  31.            WHEN NAME = 'xio' THEN
  32.                NAME
  33.            WHEN NAME = 'jion' THEN
  34.                CONCAT(name,'_BIGSB')
  35.            ELSE
  36.                concat(NAME, 'SB')
  37.            END
  38.        ) as new_name
  39.    FROM
  40.        emp;

例：

  • 1 查出所有员工的名字，薪资,格式为

    1.   <名字:xio>    <薪资:3000>

  • 2 查出所有的岗位（去掉重复）

  • 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
    1. select concat('<名字:',name,'>    ','<薪资:',salary,'>') from employee;
    2. select distinct depart_id from employee;
    3. select name,salary*12 annual_salary from employee;

    1.4 WHERE约束

    where字句中可以使用：
    1. 比较运算符：> < >= <= <> !=
    2. between 80 and 100 值在10到20之间
    3. in(80,90,100) 值是10或20或30
    4. like ‘egon%’
    pattern可以是%或，
    %表示任意多字符
    表示一个字符
    5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not ```python

    1:单条件查询

    SELECT name FROM employee
    1.   WHERE post='sale';

2:多条件查询

  1. SELECT name,salary FROM employee
  2.     WHERE post='teacher' AND salary>10000;

3:关键字BETWEEN AND

  1. SELECT name,salary FROM employee 
  2.     WHERE salary BETWEEN 10000 AND 20000;
  4. SELECT name,salary FROM employee 
  5.     WHERE salary NOT BETWEEN 10000 AND 20000;

4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)

  1. SELECT name,post_comment FROM employee 
  2.     WHERE post_comment IS NULL;
  4. SELECT name,post_comment FROM employee 
  5.     WHERE post_comment IS NOT NULL;
  7. SELECT name,post_comment FROM employee 
  8.     WHERE post_comment=''; 注意''是空字符串，不是null
  9. ps：
  10.     执行
  11.     update employee set post_comment='' where id=2;
  12.     再用上条查看，就会有结果了

5:关键字IN集合查询

  1. SELECT name,salary FROM employee 
  2.     WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
  4. SELECT name,salary FROM employee 
  5.     WHERE salary IN (3000,3500,4000,9000) ;
  7. SELECT name,salary FROM employee 
  8.     WHERE salary NOT IN (3000,3500,4000,9000) ;

6:关键字LIKE模糊查询

  1. 通配符’%’
  2. SELECT * FROM employee 
  3.         WHERE name LIKE 'xi%';
  5. 通配符’_’
  6. SELECT * FROM employee 
  7.         WHERE name LIKE 'ji__';
  1. 例：
  3. - 1. 查看岗位是teacher的员工姓名、年龄
  4. - 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
  5. - 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
  6. - 4. 查看岗位描述不为NULL的员工信息
  7. - 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
  8. - 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
  9. - 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
  10. ```python
  11. select name,age from employee where post = 'teacher';
  12. select name,age from employee where post='teacher' and age > 30; 
  13. select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
  14. select * from employee where post_comment is not null;
  15. select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
  16. select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
  17. select name,salary*12 from employee where post='teacher' and name like 'jin%';

1.5 分组查询:GROUP BY

  • 什么是分组？为什么要分组？ ```python

    1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

3、为何要分组呢？

  1. 取每个部门的最高工资
  2. 取每个部门的员工数
  3. 取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据

4、大前提：

  1. 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
  2. - **ONLY_FULL_GROUP_BY**
  3. ```python
  4. # ！！！SQL_MODE设置！！！
  6. #查看MySQL 5.7默认的sql_mode如下：
  7. mysql> select @@global.sql_mode;
  8. 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
  10. #！！！注意
  11. ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。
  14. #设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
  15. 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';
  1. mysql> select @@global.sql_mode;
  2. +-------------------+
  3. | @@global.sql_mode |
  4. +-------------------+
  5. |                   |
  6. +-------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> select * from emp group by post; 
  10. +----+------+--------+-----+------------+--------------+--------------+------------+--------+-----------+
  11. | id | name | sex    | age | hire_date  | post       | post_comment | salary     | office | depart_id |
  12. +----+------+--------+-----+------------+--------------+--------------+------------+--------+-----------+
  13. | 14 | 张野 | male   |  28 | 2016-03-11 | operation  | NULL         |   10000.13 |    403 |         3 |
  14. |  9 | 歪歪 | female |  48 | 2015-03-11 | sale        | NULL         |    3000.13 |    402 |         2 |
  15. |  2 | jion | male   |  78 | 2015-03-02 | teacher     | NULL         | 1000000.31 |    401 |         1 |
  16. |  1 | xio | male   |  18 | 2017-03-01 | teacher     | NULL         |    7300.33 |    401 |         1 |
  17. +----+------+--------+-----+------------+-------------+--------------+------------+--------+-----------+
  18. 4 rows in set (0.00 sec)
  21. #由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的
  23. mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
  24. Query OK, 0 rows affected (0.00 sec)
  26. mysql> quit #设置成功后，一定要退出，然后重新登录方可生效
  27. Bye
  29. mysql> use db1;
  30. Database changed
  31. mysql> select * from emp group by post; #报错
  32. ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY
  33. mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数
  34. +----------------------------+-----------+
  35. | post                       | count(id) |
  36. +----------------------------+-----------+
  37. | operation                  |         5 |
  38. | sale                       |         5 |
  39. | teacher                    |         8 |
  40. +----------------------------+-----------+
  41. 4 rows in set (0.00 sec)
  • GROUP BY ```python 单独使用GROUP BY关键字分组 SELECT post FROM employee GROUP BY post; 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用 SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名 SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;

GROUP BY与聚合函数一起使用 select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

  1. **强调：**
  2. ```python
  3. 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
  4. 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
  • 聚合函数 ```python

    强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例： SELECT COUNT() FROM employee; SELECT COUNT() FROM employee WHERE depart_id=1; SELECT MAX(salary) FROM employee; SELECT MIN(salary) FROM employee; SELECT AVG(salary) FROM employee; SELECT SUM(salary) FROM employee; SELECT SUM(salary) FROM employee WHERE depart_id=3;

  1. 例：<br />. 查询岗位名以及岗位包含的所有员工名字<br />. 查询岗位名以及各岗位内包含的员工个数<br />. 查询公司内男员工和女员工的个数<br />. 查询岗位名以及各岗位的平均薪资<br />. 查询岗位名以及各岗位的最高薪资<br />. 查询岗位名以及各岗位的最低薪资<br />. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
  2. ```python
  3. #题1：分组
  4. mysql> select post,group_concat(name) from employee group by post;
  6. #题目2：
  7. mysql> select post,count(id) from employee group by post;
  10. #题目3：
  11. mysql> select sex,count(id) from employee group by sex;
  14. #题目4：
  15. mysql> select post,avg(salary) from employee group by post;
  17. #题目5
  18. mysql> select post,max(salary) from employee group by post;
  20. #题目6
  21. mysql> select post,min(salary) from employee group by post;
  23. #题目七
  24. mysql> select sex,avg(salary) from employee group by sex;

1.6 HAVING过滤

HAVING与WHERE不一样的地方在于!!!!!!

  1. #！！！执行优先级从高到低：where > group by > having 
  2. #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
  4. #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

验证：

  1. mysql> select @@sql_mode;
  2. +--------------------+
  3. | @@sql_mode         |
  4. +--------------------+
  5. | ONLY_FULL_GROUP_BY |
  6. +--------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> select * from emp where salary > 100000;
  10. +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
  11. | id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
  12. +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
  13. |  2 | jion | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
  14. +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
  15. 1 row in set (0.00 sec)
  17. mysql> select * from emp having salary > 100000;
  18. ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause
  20. mysql> select post,group_concat(name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段
  21. ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
  22. mysql> select post,group_concat(name) from emp group by post having avg(salary) > 10000;
  23. +-----------+-------------------------------------------------------+
  24. | post | group_concat(name) |
  25. +-----------+-------------------------------------------------------+
  26. | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
  27. | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,jion,xio |
  28. +-----------+-------------------------------------------------------+
  29. 2 rows in set (0.00 sec)

例：

    1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
    1. 查询各岗位平均薪资大于10000的岗位名、平均工资
    1. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 ```python

      题1：

      mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2;

题目2：

mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;

题目3：

mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;

  2. <a name="s0rmK"></a>
  3. ### 1.7 查询排序:ORDER BY
  4. ```python
  5. 按单列排序
  6.     SELECT * FROM employee ORDER BY salary;
  7.     SELECT * FROM employee ORDER BY salary ASC;
  8.     SELECT * FROM employee ORDER BY salary DESC;
  10. 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
  11.     SELECT * from employee
  12.         ORDER BY age,
  13.         salary DESC;

例：

    1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
    1. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
    1. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 ```python

      题目1

      mysql> select * from employee ORDER BY age asc,hire_date desc;

题目2

mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;

题目3

mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;

  2. <a name="R5XNS"></a>
  3. ### 1.8 限制查询的记录数:LIMIT
  4. ```python
  5. 示例：
  6.     SELECT * FROM employee ORDER BY salary DESC 
  7.         LIMIT 3;                    #默认初始位置为0 
  9.     SELECT * FROM employee ORDER BY salary DESC
  10.         LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
  12.     SELECT * FROM employee ORDER BY salary DESC
  13.         LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

例：

  • 分页显示，每页5条 ```python mysql> select * from employee limit 0,5;

mysql> select * from employee limit 5,5;

mysql> select * from employee limit 10,5;

  2. <a name="1KARu"></a>
  3. ### 1.9 使用正则表达式查询
  4. ```python
  5. SELECT * FROM employee WHERE name REGEXP '^on';
  7. SELECT * FROM employee WHERE name REGEXP 'on$';
  9. SELECT * FROM employee WHERE name REGEXP 'm{2}';
  12. 小结：对字符串匹配的方式
  13. WHERE name = 'xio';
  14. WHERE name LIKE 'yua%';
  15. WHERE name REGEXP 'on$';

例：

  • 查看所有员工中名字是jin开头，x或者y结果的员工信息

    1. select * from employee where name regexp '^jin.*[xy]$';

    2. 多表查询

  • 准备表 ```python

    建表

    create table department( id int, name varchar(20) );

create table employee( id int primary key auto_increment, name varchar(20), sex enum(‘male’,’female’) not null default ‘male’, age int, dep_id int );

插入数据

insert into department values (200,’技术’), (201,’人力资源’), (202,’销售’), (203,’运营’);

insert into employee(name,sex,age,dep_id) values (‘xio’,’male’,18,200), (‘jion’,’female’,48,201), (‘wupeiqi’,’male’,38,201), (‘yuanhao’,’female’,28,202), (‘liwenzhou’,’male’,18,200), (‘jingliyang’,’female’,18,204) ;

查看表结构和数据

mysql> desc department; +———-+——————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +———-+——————-+———+——-+————-+———-+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +———-+——————-+———+——-+————-+———-+

mysql> desc employee; +————+———————————-+———+——-+————-+————————+ | Field | Type | Null | Key | Default | Extra | +————+———————————-+———+——-+————-+————————+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum(‘male’,’female’) | NO | | male | | | age | int(11) | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | +————+———————————-+———+——-+————-+————————+

mysql> select * from department; +———+———————+ | id | name | +———+———————+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +———+———————+

mysql> select * from employee; +——+——————+————+———+————+ | id | name | sex | age | dep_id | +——+——————+————+———+————+ | 1 | xio | male | 18 | 200 | | 2 | jion | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +——+——————+————+———+————+

  1. <a name="GOhFQ"></a>
  2. ### 2.1 多表连接查询
  3. ```python
  4. #重点：外链接语法
  6. SELECT 字段列表
  7.     FROM 表1 INNER|LEFT|RIGHT JOIN 表2
  8.     ON 表1.字段 = 表2.字段;

  • 交叉连接：不适用任何匹配条件。生成笛卡尔积

    1. mysql> select * from dep,emp;
    2. +------+--------------+----+--------+--------+------+--------+
    3. | id   | name         | id | name   | sex    | age  | dep_id |
    4. +------+--------------+----+--------+--------+------+--------+
    5. |  200 | 技术         |  1 | xio    | male   |   18 |    200 |
    6. |  201 | 人力资源     |  1 | xio    | male   |   18 |    200 |
    7. |  202 | 销售         |  1 | xio    | male   |   18 |    200 |
    8. |  203 | 运营         |  1 | xio    | male   |   18 |    200 |
    9. |  200 | 技术         |  2 | jiaxin | female |   48 |    201 |
    10. |  201 | 人力资源     |  2 | jiaxin | female |   48 |    201 |
    11. |  202 | 销售         |  2 | jiaxin | female |   48 |    201 |
    12. |  203 | 运营         |  2 | jiaxin | female |   48 |    201 |
    13. |  200 | 技术         |  3 | haojie | male   |   18 |    201 |
    14. |  201 | 人力资源     |  3 | haojie | male   |   18 |    201 |
    15. |  202 | 销售         |  3 | haojie | male   |   18 |    201 |
    16. |  203 | 运营         |  3 | haojie | male   |   18 |    201 |
    17. |  200 | 技术         |  4 | nick   | male   |   28 |    202 |
    18. |  201 | 人力资源     |  4 | nick   | male   |   28 |    202 |
    19. |  202 | 销售         |  4 | nick   | male   |   28 |    202 |
    20. |  203 | 运营         |  4 | nick   | male   |   28 |    202 |
    21. |  200 | 技术         |  5 | owen   | male   |   18 |    203 |
    22. |  201 | 人力资源     |  5 | owen   | male   |   18 |    203 |
    23. |  202 | 销售         |  5 | owen   | male   |   18 |    203 |
    24. |  203 | 运营         |  5 | owen   | male   |   18 |    203 |
    25. |  200 | 技术         |  6 | jerry  | female |   18 |    204 |
    26. |  201 | 人力资源     |  6 | jerry  | female |   18 |    204 |
    27. |  202 | 销售         |  6 | jerry  | female |   18 |    204 |
    28. |  203 | 运营         |  6 | jerry  | female |   18 |    204 |
    29. +------+--------------+----+--------+--------+------+--------+
    30. 24 rows in set (0.00 sec)

  • 内连接：只连接匹配的行 ```python

    找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果

    department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来

    mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +——+—————-+———+————+———————+ | id | name | age | sex | name | +——+—————-+———+————+———————+ | 1 | xio | 18 | male | 技术 | | 2 | jion | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | liwenzhou | 18 | male | 技术 | +——+—————-+———+————+———————+

上述sql等同于

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

  2. - **外链接之左连接：优先显示左表全部记录**
  3. ```python
  4. #以左表为准，即找出所有员工信息，当然包括没有部门的员工
  5. #本质就是：在内连接的基础上增加左边有右边没有的结果
  6. mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
  7. +----+------------+--------------+
  8. | id | name       | depart_name  |
  9. +----+------------+--------------+
  10. |  1 | xio        | 技术         |
  11. |  5 | liwenzhou  | 技术         |
  12. |  2 | jion       | 人力资源     |
  13. |  3 | wupeiqi    | 人力资源     |
  14. |  4 | yuanhao    | 销售         |
  15. |  6 | jingliyang | NULL         |
  16. +----+------------+--------------+

  • 外链接之右连接：优先显示右表全部记录

    1. #以右表为准，即找出所有部门信息，包括没有员工的部门
    2. #本质就是：在内连接的基础上增加右边有左边没有的结果
    3. mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
    4. +------+-----------+--------------+
    5. | id   | name      | depart_name  |
    6. +------+-----------+--------------+
    7. |    1 | xio       | 技术         |
    8. |    2 | jion      | 人力资源     |
    9. |    3 | wupeiqi   | 人力资源     |
    10. |    4 | yuanhao   | 销售         |
    11. |    5 | liwenzhou | 技术         |
    12. | NULL | NULL      | 运营         |
    13. +------+-----------+--------------+

  • 全外连接：显示左右两个表全部记录 ```python 全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

    注意：mysql不支持全外连接 full JOIN

    强调：mysql可以使用此种方式间接实现全外连接

    select from employee left join department on employee.dep_id = department.id union select from employee right join department on employee.dep_id = department.id ;

    查看结果

    +———+——————+————+———+————+———+———————+ | id | name | sex | age | dep_id | id | name | +———+——————+————+———+————+———+———————+ | 1 | xio | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | jion | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +———+——————+————+———+————+———+———————+

注意 union与union all的区别：union会去掉相同的纪录

  1. **
  2. <a name="mUJMX"></a>
  3. ### 2.2 符合条件连接查询
  4. ```python
  5. #示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
  6. select employee.name,department.name from employee inner join department
  7.     on employee.dep_id = department.id
  8.     where age > 25;
  10. #示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
  11. select employee.id,employee.name,employee.age,department.name from employee,department
  12.     where employee.dep_id = department.id
  13.     and age > 25
  14.     order by age asc;

2.3 子查询

  1. #1：子查询是将一个查询语句嵌套在另一个查询语句中。
  2. #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
  3. #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
  4. #4：还可以包含比较运算符：= 、 !=、> 、<等
  • 带IN关键字的子查询 ```python

    查询平均年龄在25岁以上的部门名

    select id,name from department where id in 
    1.   (select dep_id from employee group by dep_id having avg(age) > 25);

查看技术部员工姓名

select name from employee where dep_id in (select id from department where name=’技术’);

查看不足1人的部门名(子查询得到的是有人的部门id)

select name from department where id not in (select distinct dep_id from employee);

  1. ```python
  2. # ！！！注意not in
  4. not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理，如下
  6. mysql> select * from emp;
  7. +----+------------+--------+------+--------+
  8. | id | name | sex | age | dep_id |
  9. +----+------------+--------+------+--------+
  10. | 1 | egon | male | 18 | 200 |
  11. | 2 | alex | female | 48 | 201 |
  12. | 3 | wupeiqi | male | 38 | 201 |
  13. | 4 | yuanhao | female | 28 | 202 |
  14. | 5 | liwenzhou | male | 18 | 200 |
  15. | 6 | jingliyang | female | 18 | 204 |
  16. | 7 | xxx | male | 19 | NULL |
  17. +----+------------+--------+------+--------+
  18. 7 rows in set (0.00 sec)
  20. mysql> select * from dep;
  21. +------+--------------+
  22. | id | name |
  23. +------+--------------+
  24. | 200 | 技术 |
  25. | 201 | 人力资源 |
  26. | 202 | 销售 |
  27. | 203 | 运营 |
  28. +------+--------------+
  29. 4 rows in set (0.00 sec)
  31. # 子查询中存在null
  32. mysql> select * from dep where id not in (select distinct dep_id from emp);
  33. Empty set (0.00 sec)
  35. # 解决方案如下
  36. mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
  37. +------+--------+
  38. | id | name |
  39. +------+--------+
  40. | 203 | 运营 |
  41. +------+--------+
  42. 1 row in set (0.00 sec)
  44. mysql>
  • 带ANY关键字的子查询 ```python

    在 SQL 中 ANY 和 SOME 是同义词，SOME 的用法和功能和 ANY 一模一样。

ANY 和 IN 运算符不同之处1

ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符放在 ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义

例如：使用 IN 和使用 ANY运算符得到的结果是一致的 select * from employee where salary = any ( select max(salary) from employee group by depart_id);

select * from employee where salary in ( select max(salary) from employee group by depart_id);

结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符

ANY和 IN 运算符不同之处2

ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的

SELECT * FROM T_Book WHERE FYearPublished < ANY (2001, 2003, 2005)

  2. - **带ALL关键字的子查询**
  3. ```python
  4. # all同any类似，只不过all表示的是所有，any表示任一
  5. 查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
  6. select * from employee where salary > all (
  7. select avg(salary) from employee group by depart_id);
  8. 查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
  9. select * from employee where salary < all (
  10. select avg(salary) from employee group by depart_id);
  12. 查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工select * from employee where salary < any ( select avg(salary) from employee group by depart_id); 
  13. 查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
  14. select * from employee where salary > any (
  15. select avg(salary) from employee group by depart_id);
  • 带比较运算符的子查询 ```python

    比较运算符：=、!=、>、>=、<、<=、<>

    查询大于所有人平均年龄的员工名与年龄

    mysql> select name,age from emp where age > (select avg(age) from emp); +————-+———+ | name | age | +————-+———+ | xio | 48 | | wupeiqi | 38 | +————-+———+ 2 rows in set (0.00 sec)

查询大于部门内平均年龄的员工名、年龄

select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

  2. - **带EXISTS关键字的子查询**
  4. EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。<br />而是返回一个真假值。True或False<br />当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
  5. ```python
  6. #department表中存在dept_id=203，Ture
  7. mysql> select * from employee
  8.     ->     where exists
  9.     ->         (select id from department where id=200);
  10. +----+------------+--------+------+--------+
  11. | id | name       | sex    | age  | dep_id |
  12. +----+------------+--------+------+--------+
  13. |  1 | xio        | male   |   18 |    200 |
  14. |  2 | jion       | female |   48 |    201 |
  15. |  3 | wupeiqi    | male   |   38 |    201 |
  16. |  4 | yuanhao    | female |   28 |    202 |
  17. |  5 | liwenzhou  | male   |   18 |    200 |
  18. |  6 | jingliyang | female |   18 |    204 |
  19. +----+------------+--------+------+--------+
  21. #department表中存在dept_id=205，False
  22. mysql> select * from employee
  23.     ->     where exists
  24.     ->         (select id from department where id=204);
  25. Empty set (0.00 sec)

in与exists

  1. ！！！！！！当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！！！！！！
  2. ==============================exists==============================
  3. # exists
  4. exists后面一般都是子查询，后面的子查询被称做相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回true，
  5. 否则返回false，exists是不返回列表的值的，exists只在乎括号里的数据能不能查找出来，是否存在这样的记录。
  7. # 例
  8. 查询出那些班级里有学生的班级
  9. select * from class where exists (select * from stu where stu.cid=class.id)
  11. # exists的执行原理为：
  12. 1、依次执行外部查询：即select * from class 
  13. 2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
  14. 3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录
  16. ==============================in==============================
  17. # in
  18. in后跟的都是子查询，in()后面的子查询 是返回结果集的
  20. # 例
  21. 查询和所有女生年龄相同的男生
  22. select * from stu where sex='男' and age in(select age from stu where sex='女')
  24. # in的执行原理为：
  25. in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
  26. 然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.
  • not in与 not exists ```python !!!!!!not exists查询的效率远远高与not in查询的效率。!!!!!!

==============================not in============================== not in()子查询的执行顺序是： 为了证明not in成立，即找不到，需要一条一条地查询表，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。

==============================not exists============================== not exists： 如果主查询表中记录少，子查询表中记录多，并有索引。 例如:查询那些班级中没有学生的班级 select * from class

where not exists

(select * from student where student.cid = class.cid)

not exists的执行顺序是： 在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。

  3. ---
  5. <a name="nNNhy"></a>
  6. # 六 权限管理
  7. ![](https://cdn.nlark.com/yuque/0/2021/png/12472135/1614824979460-b3ffad48-53f2-4aa1-a08b-f1cffaeb0e2a.png#align=left&display=inline&height=827&margin=%5Bobject%20Object%5D&name=&originHeight=827&originWidth=666&size=0&status=done&style=none&width=666)
  8. ```python
  9. #授权表
  10. user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段
  11. db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段
  12. tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段
  13. columns_priv #该表放行的权限，针对：某一个字段
  15. #按图解释：
  16. user：放行db1，db2及其包含的所有
  17. db：放行db1，及其db1包含的所有
  18. tables_priv:放行db1.table1，及其该表包含的所有
  19. columns_prive:放行db1.table1.column1，只放行该字段

权限相关操作

  1. #创建用户
  2. create user 'aaaa'@'1.1.1.1' identified by '123';
  3. create user 'aaaa'@'192.168.1.%' identified by '123';
  4. create user 'aaaa'@'%' identified by '123';
  7. #授权：对文件夹，对文件，对文件某一字段的权限
  8. 查看帮助：help grant
  9. 常用权限有：select,update,alter,delete
  10. all可以代表除了grant之外的所有权限
  12. #针对所有库的授权:*.*
  13. grant select on *.* to 'aaaa'@'localhost' identified by '123'; #只在user表中可以查到aaaa1用户的select权限被设置为Y
  15. #针对某一数据库：db1.*
  16. grant select on db1.* to 'aaaa2'@'%' identified by '123'; #只在db表中可以查到aaaa2用户的select权限被设置为Y
  18. #针对某一个表：db1.t1
  19. grant select on db1.t1 to 'aaaa3'@'%' identified by '123';  #只在tables_priv表中可以查到aaaa3用户的select权限
  21. #针对某一个字段：
  22. mysql> select * from t3;
  23. +------+-------+------+
  24. | id   | name  | age  |
  25. +------+-------+------+
  26. |    1 | aaaa1 |   18 |
  27. |    2 | aaaa2 |   19 |
  28. |    3 | aaaa3 |   29 |
  29. +------+-------+------+
  31. grant select (id,name),update (age) on db1.t3 to 'aaaa4'@'localhost' identified by '123'; 
  32. #可以在tables_priv和columns_priv中看到相应的权限
  33. mysql> select * from tables_priv where user='aaaa4'\G
  34. *************************** 1. row ***************************
  35.        Host: localhost
  36.          Db: db1
  37.        User: aaaa4
  38.  Table_name: t3
  39.     Grantor: root@localhost
  40.   Timestamp: 0000-00-00 00:00:00
  41.  Table_priv:
  42. Column_priv: Select,Update
  43. row in set (0.00 sec)
  45. mysql> select * from columns_priv where user='egon4'\G
  46. *************************** 1. row ***************************
  47.        Host: localhost
  48.          Db: db1
  49.        User: aaaa4
  50.  Table_name: t3
  51. Column_name: id
  52.   Timestamp: 0000-00-00 00:00:00
  53. Column_priv: Select
  54. *************************** 2. row ***************************
  55.        Host: localhost
  56.          Db: db1
  57.        User: aaaa4
  58.  Table_name: t3
  59. Column_name: name
  60.   Timestamp: 0000-00-00 00:00:00
  61. Column_priv: Select
  62. *************************** 3. row ***************************
  63.        Host: localhost
  64.          Db: db1
  65.        User: aaaa4
  66.  Table_name: t3
  67. Column_name: age
  68.   Timestamp: 0000-00-00 00:00:00
  69. Column_priv: Update
  70. rows in set (0.00 sec)
  72. #删除权限
  73. revoke select on db1.* from 'aaaa'@'%';