[MySQL & Python] 07. 必备的SQL命令

数据准备

本节用到两张数据表，他们使用depart_id进行关联。

depart表
+----+--------+
| id | title  |
+----+--------+
|  1 | 开发   |
|  2 | 运维   |
|  3 | 销售   |
+----+--------+
​
info表
+----+---------+----------------+-----+-----------+
| id | name    | email          | age | depart_id |
+----+---------+----------------+-----+-----------+
|  1 | Leo     | leo@qq.com     |  41 |         1 |
|  2 | Kevin   | kevin@qq.com   |  45 |         2 |
|  3 | Winston | winston@qq.com |  27 |         2 |
|  4 | Kaiser  | kaiser@qq.com  |  40 |         3 |
|  5 | Scott   | scott@qq.com   |  40 |         3 |
+----+---------+----------------+-----+-----------+

 

 

复制一下语句到MySQL客户端进行数据准备

-- 创建depart表，并添加内容
create table depart (
    id int not null primary key auto_increment,
    title varchar(20)) default charset=utf8 ; 
​
insert into depart (title) values ('开发'),('运维'),('销售');
​
-- 创建info表，并添加内容
create table info (
    id int not null primary key auto_increment,
    name varchar(20) not null,
    email varchar(40) not null,
    age int not null,
    depart_id int) default charset=utf8;
​
insert into info (name, email,age,depart_id) values ('Leo','leo@qq.com',41,1);
insert into info (name, email,age,depart_id) values ('Kevin','kevin@qq.com',45,2);
insert into info (name, email,age,depart_id) values ('Winston','winston@qq.com',27,2);
insert into info (name, email,age,depart_id) values ('Kaiser','kaiser@qq.com',40,3);
insert into info (name, email,age,depart_id) values ('Scott','scott@qq.com',40,3);

 

 

条件查询 where

• select * from 数据表 where 条件

​

select * from info where age = 41;
+----+------+------------+-----+-----------+
| id | name | email      | age | depart_id |
+----+------+------------+-----+-----------+
|  1 | Leo  | leo@qq.com |  41 |         1 |
+----+------+------------+-----+-----------+
​
select * from info where name != 'Leo' or 'Kaiser' or 'Kevin';
+----+---------+----------------+-----+-----------+
| id | name    | email          | age | depart_id |
+----+---------+----------------+-----+-----------+
|  2 | Kevin   | kevin@qq.com   |  45 |         2 |
|  3 | Winston | winston@qq.com |  27 |         2 |
|  4 | Kaiser  | kaiser@qq.com  |  40 |         3 |
|  5 | Scott   | scott@qq.com   |  40 |         3 |
+----+---------+----------------+-----+-----------+
​
select * from info where age between 41 and 45;
+----+-------+--------------+-----+-----------+
| id | name  | email        | age | depart_id |
+----+-------+--------------+-----+-----------+
|  1 | Leo   | leo@qq.com   |  41 |         1 |
|  2 | Kevin | kevin@qq.com |  45 |         2 |
+----+-------+--------------+-----+-----------+
​
mysql> select * from info where id in (1,4);
+----+--------+---------------+-----+-----------+
| id | name   | email         | age | depart_id |
+----+--------+---------------+-----+-----------+
|  1 | Leo    | leo@qq.com    |  41 |         1 |
|  4 | Kaiser | kaiser@qq.com |  40 |         3 |
+----+--------+---------------+-----+-----------+
​
select * from info where id not in (1,4,5);
+----+---------+----------------+-----+-----------+
| id | name    | email          | age | depart_id |
+----+---------+----------------+-----+-----------+
|  2 | Kevin   | kevin@qq.com   |  45 |         2 |
|  3 | Winston | winston@qq.com |  27 |         2 |
+----+---------+----------------+-----+-----------+

 

​
--子查询
mysql> select * from info  
    -> where  depart_id in 
    -> (select id from depart where title in ('开发' , '销售'));
+----+--------+---------------+-----+-----------+
| id | name   | email         | age | depart_id |
+----+--------+---------------+-----+-----------+
|  1 | Leo    | leo@qq.com    |  41 |         1 |
|  4 | Kaiser | kaiser@qq.com |  40 |         3 |
|  5 | Scott  | scott@qq.com  |  40 |         3 |
+----+--------+---------------+-----+-----------+
​
--判断depart里面, id=5是否存在。存在，执行select * from info
mysql> select * from depart where exists(select * from depart where id = 1);
+----+--------+
| id | title  |
+----+--------+
|  1 | 开发   |
|  2 | 运维   |
|  3 | 销售   |
+----+--------+
3 rows in set (0.00 sec)
​
mysql> select * from depart where not exists(select * from depart where id = 1);
Empty set (0.00 sec)
​
​

 

• select * from (查询) as 别名 where 条件

mysql> select * from  (select * from info where age > 40) as T  where T.id = 2;
+----+-------+--------------+-----+-----------+
| id | name  | email        | age | depart_id |
+----+-------+--------------+-----+-----------+
|  2 | Kevin | kevin@qq.com |  45 |         2 |
+----+-------+--------------+-----+-----------+

 

通配符 % _

• % 百分号代表n个字符

• _ 下划线代表一个字符

 

mysql> select * from depart where title like '_发';
+----+--------+
| id | title  |
+----+--------+
|  1 | 开发   |
+----+--------+
​
mysql> select * from info where name like '%n';
+----+---------+----------------+-----+-----------+
| id | name    | email          | age | depart_id |
+----+---------+----------------+-----+-----------+
|  2 | Kevin   | kevin@qq.com   |  45 |         2 |
|  3 | Winston | winston@qq.com |  27 |         2 |
+----+---------+----------------+-----+-----------+

 

映射 select 字段

• 映射代表查询想获取的列。

select 列名，列名 from 表

select 列名, 列名 as 别名 from 表

select 列名， 列名，常量值 from 表 -- 常量值的那列内容和表头都是常量值

select 列名, (返回只能一个值的子查询) from 表

mysql> select 
    -> 'SLQ' as 酒店, 
    -> name as 姓名, 
    -> (select max(id) from depart) as 最大id, 
    -> (select min(id) from depart) as 最小id 
    -> from info;
+--------+---------+----------+----------+
| 酒店   | 姓名    | 最大id   | 最小id   |
+--------+---------+----------+----------+
| SLQ    | Leo     |        3 |        1 |
| SLQ    | Kevin   |        3 |        1 |
| SLQ    | Winston |        3 |        1 |
| SLQ    | Kaiser  |        3 |        1 |
| SLQ    | Scott   |        3 |        1 |
+--------+---------+----------+----------+
​
-- 搜索出来第一个表的结果，拿着每个结果的depart_id去depart表中查找出title，放到返回结果作为单独的列。
-- 效率比较低
mysql> select
    -> id,
    -> name,
    -> (select title from depart where depart.id = info.depart_id) as title
    -> from info;
+----+---------+--------+
| id | name    | title  |
+----+---------+--------+
|  1 | Leo     | 开发   |
|  2 | Kevin   | 运维   |
|  3 | Winston | 运维   |
|  4 | Kaiser  | 销售   |
|  5 | Scott   | 销售   |
+----+---------+--------+
​

 

• case 字段 when 条件 then 显示真的内容 end 行名。

• case 字段 when 条件 then 显示真的内容 else 显示假的内容 end 行名。

如果字段的内容符合条件的时候，显示规定的内容。不符合条件，显示假的内容。如果未定义else，显示NULL。

• case 字段 when 条件1 then 内容1 when 条件2 then 内容2 else 其他内容 end 行名。

进行多次判断。

 

mysql> select  name,  age, 
    -> case depart_id when 1 then "开发" when 2 then "运维" else "其他" end 部门
    -> from info;
+---------+-----+--------+
| name    | age | 部门   |
+---------+-----+--------+
| Leo     |  41 | 开发   |
| Kevin   |  45 | 运维   |
| Winston |  27 | 运维   |
| Kaiser  |  40 | 其他   |
| Scott   |  40 | 其他   |
+---------+-----+--------+

 

• case when 范围条件 then 内容 when 范围条件 then 内容 else 内容 end 行号

mysql> select id, name,
    -> case when id <3 then "领导" when id = 4 then "领班" when id >4 then "员工" end 角色
    -> from info;
+----+---------+--------+
| id | name    | 角色   |
+----+---------+--------+
|  1 | Leo     | 领导   |
|  2 | Kevin   | 领导   |
|  3 | Winston | NULL   |
|  4 | Kaiser  | 领班   |
|  5 | Scott   | 员工   |
+----+---------+--------+
​
-- 未定义的都是NULL

-- 范例： 80分以上的，通过case when 记为1，除以总数获得通过率。
select cname, sum(number), avg(number) , 
sum(case when score.number >= 80 then 1 end)/count(course_id) * 100 as 通过率 
from score left outer join course on course.cid = score.course_id group by course_id;

 

 

排序 order by asc / desc

select * from 表名 order by 字段 asc / desc

-- 按照字段1顺序排列，字段1相同的内容，按照字段2降序排列

select * from 表名 order by 字段1 asc, 字段2 desc

mysql> select 
    -> name, depart_id 
    -> from info
    -> where 1=1    --等于是 1=1 为真时，查询所有数据
    -> order by depart_id asc, name desc ;
+---------+-----------+
| name    | depart_id |
+---------+-----------+
| Leo     |         1 |
| Winston |         2 |
| Kevin   |         2 |
| Scott   |         3 |
| Kaiser  |         3 |
+---------+-----------+
​

 

获取部分数据 limit

select * from 表名 limit 3;

select * from 表名where 条件 order by 列名 limit 3;

select * from 表名where 条件 order by 列名 limit 3 offset 2; -- 从位置2(不包含)向后取3条数据(第3，4，5条)。

limit n offset m 用于分页显示数据。

mysql> select * from info limit 3;
+----+---------+----------------+-----+-----------+
| id | name    | email          | age | depart_id |
+----+---------+----------------+-----+-----------+
|  1 | Leo     | leo@qq.com     |  41 |         1 |
|  2 | Kevin   | kevin@qq.com   |  45 |         2 |
|  3 | Winston | winston@qq.com |  27 |         2 |
+----+---------+----------------+-----+-----------+
​
mysql> select * from info limit 3 offset 2;
+----+---------+----------------+-----+-----------+
| id | name    | email          | age | depart_id |
+----+---------+----------------+-----+-----------+
|  3 | Winston | winston@qq.com |  27 |         2 |
|  4 | Kaiser  | kaiser@qq.com  |  40 |         3 |
|  5 | Scott   | scott@qq.com   |  40 |         3 |
+----+---------+----------------+-----+-----------+

 

分组 group by

使用分组的需求，一般是根据某个字段，要获知各个值有多少对应的记录数。

使用分组后，select选取的列不能是包含多个行的数据，可以用聚合函数获得对任何列的统计值。

聚合函数： min, max, sum, count

select age, max(id), min(id), count(id) from info group by age; -- 所有age相同的合并在一起，显示统计信息。

mysql> select depart_id, count(depart_id),sum(id),max(id), min(id)  
    -> from info group by depart_id;
+-----------+------------------+---------+---------+---------+
| depart_id | count(depart_id) | sum(id) | max(id) | min(id) |
+-----------+------------------+---------+---------+---------+
|         1 |                1 |       1 |       1 |       1 |
|         2 |                2 |       5 |       3 |       2 |
|         3 |                2 |       9 |       5 |       4 |
+-----------+------------------+---------+---------+---------+

group by 以后在进行筛选，需要用having子句。

select depart_id, count(1) from info group by depart_id having count(id) >= 2 ;

mysql> select depart_id, count(1) from info group by depart_id having count(id) >= 2 ;
+-----------+----------+
| depart_id | count(1) |
+-----------+----------+
|         2 |        2 |
|         3 |        2 |
+-----------+----------+
​

 

连接表

• 左外连接： 主表 left outer join 从表 on 条件

select * from 主表 left outer join 从表 on 主表.列 = 从表.列；

select 主表.列，从表.列 from 主表 left outer join 从表 on 主表.列 = 从表.列；

 

• 右外连接： 从表 right outer join 主表 on 条件

select * from 从表 right outer join 从表 on 主表.列 = 从表.列；

select 主表.列，从表.列 from 主表 right outer join 从表 on 主表.列 = 从表.列；

 

outer可以省略， left join和right join的写法都是合法的。

作为主表的数据会全部展示出来，从表中的数据，如果和主表没有关联，那么就不会展示出来。

 

• 内连接： 表1 inner join 表2

只有两个表能关联上的数据才会显示，两张表没有对应关系的数据会被忽略掉。

select * from 表1 inner join 表2 on 表1.列 = 表2.列

 

• 多个表可以用join多次连接

表1 inner join 表2 inner join 表3 ...

 

• SQL命令的执行顺序：

join

on

where

group by

having

order by

limit

上下连接 union

• 上下连表，并且去重

select 列 from 表1 union select 列 from表2

 

• 上下连表，不去重

select 列 from 表1 union all select 列 from表2

 

两个查询的列数要相同，两次查询出来的列的类型不需要相同。

mysql> select id, title from depart
    -> union
    -> select name, age from info;
+---------+--------+
| id      | title  |
+---------+--------+
| 1       | 开发   |
| 2       | 运维   |
| 3       | 销售   |
| 4       | 运维   |
| Leo     | 41     |
| Kevin   | 45     |
| Winston | 27     |
| Kaiser  | 40     |
| Scott   | 40     |
+---------+--------+

 

SELECT 插入数据

A表与B表的结构相同，可以使用以下方法从B表向A表插入数据：

insert into A表 select * from B表 ;

 

如果A表和B表的结构不相同，需要指定向A表的哪几列插入数据：

insert into A表 (字段1, 字段2) select 字段1, 字段2 from B表 where 条件 ;