我们先来建个表哦~~~
mysql> select * from e1;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | rose | 25 |
| 2 | jack | 18 |
| 3 | bob | 29 |
| 4 | alice | 31 |
| 5 | ben | 20 |
| 6 | fan | 20 |
+----+-------+------+
6 rows in set (0.00 sec)
第三部分——数据排序
asc升序、desc降序
单个列排序
select * from e1 order by age desc;
多个列排序
select * from e1 order by age asc, id desc;
先按第一个(age升序)、再按第二个(id降序)排序
按别名排序
select id,age*2 A_G_E from e1 order by A_G_E desc;
选择id列、age*2列(改名为A_G_E)并按照A_G_E列降序排序
mysql> select * from e1 order by age desc;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 4 | alice | 31 |
| 3 | bob | 29 |
| 1 | rose | 25 |
| 5 | ben | 20 |
| 6 | fan | 20 |
| 2 | jack | 18 |
+----+-------+------+
6 rows in set (0.00 sec)
mysql> select * from e1 order by age asc, id desc;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | jack | 18 |
| 6 | fan | 20 |
| 5 | ben | 20 |
| 1 | rose | 25 |
| 3 | bob | 29 |
| 4 | alice | 31 |
+----+-------+------+
6 rows in set (0.00 sec)
mysql> select id,age*2 A_G_E from e1 order by A_G_E desc;
+----+-------+
| id | A_G_E |
+----+-------+
| 4 | 62 |
| 3 | 58 |
| 1 | 50 |
| 5 | 40 |
| 6 | 40 |
| 2 | 36 |
+----+-------+
6 rows in set (0.00 sec)
第四部分——子查询
建个新表,作为调用条件
mysql> select * from e2;
+----+------+
| id | age |
+----+------+
| 1 | 10 |
| 2 | 26 |
| 3 | 20 |
| 4 | 35 |
| 5 | 50 |
+----+------+
5 rows in set (0.00 sec)
mysql> select * from e1;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | rose | 25 |
| 2 | jack | 18 |
| 3 | bob | 29 |
| 4 | alice | 31 |
| 5 | ben | 20 |
| 6 | fan | 20 |
+----+-------+------+
6 rows in set (0.00 sec)
单行子查询
select * from e1 where id = (select id from e2 where id+1=3);
多行子查询
等于列表中任意一个:select * from e1 where id in (select id from e2);
和任意一个值比较:select * from e1 where id >any (select id from e2);
和所有值比较:select * from e1 where age >all (select age from e2);
mysql> select * from e1 where id = (select id from e2 where id+1=3);
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | jack | 18 |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from e1 where id in (select id from e2);
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | rose | 25 |
| 2 | jack | 18 |
| 3 | bob | 29 |
| 4 | alice | 31 |
| 5 | ben | 20 |
+----+-------+------+
5 rows in set (0.00 sec)
mysql> select * from e1 where id >any (select id from e2);
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | jack | 18 |
| 3 | bob | 29 |
| 4 | alice | 31 |
| 5 | ben | 20 |
| 6 | fan | 20 |
+----+-------+------+
5 rows in set (0.00 sec)
mysql> select * from e1 where age >all (select age from e2);
Empty set (0.01 sec)
第五部分——多表查询
内连接
select a.id,a.name,a.age,b.id,b.age from e1 a inner join e2 b on a.age=b.age;
mysql> select a.id,a.name,a.age,b.id,b.age from e1 a inner join e2 b on a.age=b.age;
+----+------+------+----+------+
| id | name | age | id | age |
+----+------+------+----+------+
| 5 | ben | 20 | 3 | 20 |
| 6 | fan | 20 | 3 | 20 |
+----+------+------+----+------+
2 rows in set (0.00 sec)
非等值连接
select a.id,a.name,a.age,b.id,b.age from e1 a,e2 b where b.age = 20;
select a.id,a.name,a.age,b.id,b.age from e1 a,e2 b where b.age > 30;
mysql> select a.id,a.name,a.age,b.id,b.age from e1 a,e2 b where b.age = 20;
+----+-------+------+----+------+
| id | name | age | id | age |
+----+-------+------+----+------+
| 1 | rose | 25 | 3 | 20 |
| 2 | jack | 18 | 3 | 20 |
| 3 | bob | 29 | 3 | 20 |
| 4 | alice | 31 | 3 | 20 |
| 5 | ben | 20 | 3 | 20 |
| 6 | fan | 20 | 3 | 20 |
+----+-------+------+----+------+
6 rows in set (0.00 sec)
mysql> select a.id,a.name,a.age,b.id,b.age from e1 a,e2 b where b.age > 30;
+----+-------+------+----+------+
| id | name | age | id | age |
+----+-------+------+----+------+
| 1 | rose | 25 | 4 | 35 |
| 1 | rose | 25 | 5 | 50 |
| 2 | jack | 18 | 4 | 35 |
| 2 | jack | 18 | 5 | 50 |
| 3 | bob | 29 | 4 | 35 |
| 3 | bob | 29 | 5 | 50 |
| 4 | alice | 31 | 4 | 35 |
| 4 | alice | 31 | 5 | 50 |
| 5 | ben | 20 | 4 | 35 |
| 5 | ben | 20 | 5 | 50 |
| 6 | fan | 20 | 4 | 35 |
| 6 | fan | 20 | 5 | 50 |
+----+-------+------+----+------+
12 rows in set (0.00 sec)
外连接
select a.id,a.name,a.age,b.id,b.age from e1 a left join e2 b on a.age=b.age;
select a.id,a.name,a.age,b.id,b.age from e1 a right join e2 b on a.age=b.age;
mysql> select a.id,a.name,a.age,b.id,b.age from e1 a left join e2 b on a.age=b.age;
+----+-------+------+------+------+
| id | name | age | id | age |
+----+-------+------+------+------+
| 5 | ben | 20 | 3 | 20 |
| 6 | fan | 20 | 3 | 20 |
| 1 | rose | 25 | NULL | NULL |
| 2 | jack | 18 | NULL | NULL |
| 3 | bob | 29 | NULL | NULL |
| 4 | alice | 31 | NULL | NULL |
+----+-------+------+------+------+
6 rows in set (0.00 sec)
mysql> select a.id,a.name,a.age,b.id,b.age from e1 a right join e2 b on a.age=b.age;
+------+------+------+----+------+
| id | name | age | id | age |
+------+------+------+----+------+
| 5 | ben | 20 | 3 | 20 |
| 6 | fan | 20 | 3 | 20 |
| NULL | NULL | NULL | 1 | 10 |
| NULL | NULL | NULL | 2 | 26 |
| NULL | NULL | NULL | 4 | 35 |
| NULL | NULL | NULL | 5 | 50 |
+------+------+------+----+------+
6 rows in set (0.00 sec)
自然连接
内连接查询:查询的结果为两个表匹配到的数据
select * from e1 inner join e2 on e1.age=e2.age;
右连接查询:两个表匹配到的数据,右表有的、左表中不存在的数据使用null填充
select * from e1 left join e2 on e1.age=e2.age;
左连接查询:两个表匹配到的数据,左表有的、右表中不存在的数据使用null填充
select * from e1 right join e2 on e1.age=e2.age;
mysql> select * from e1 inner join e2 on e1.age=e2.age;
+----+------+------+----+------+
| id | name | age | id | age |
+----+------+------+----+------+
| 5 | ben | 20 | 3 | 20 |
| 6 | fan | 20 | 3 | 20 |
+----+------+------+----+------+
2 rows in set (0.00 sec)
mysql> select * from e1 left join e2 on e1.age=e2.age;
+----+-------+------+------+------+
| id | name | age | id | age |
+----+-------+------+------+------+
| 5 | ben | 20 | 3 | 20 |
| 6 | fan | 20 | 3 | 20 |
| 1 | rose | 25 | NULL | NULL |
| 2 | jack | 18 | NULL | NULL |
| 3 | bob | 29 | NULL | NULL |
| 4 | alice | 31 | NULL | NULL |
+----+-------+------+------+------+
6 rows in set (0.00 sec)
mysql> select * from e1 right join e2 on e1.age=e2.age;
+------+------+------+----+------+
| id | name | age | id | age |
+------+------+------+----+------+
| 5 | ben | 20 | 3 | 20 |
| 6 | fan | 20 | 3 | 20 |
| NULL | NULL | NULL | 1 | 10 |
| NULL | NULL | NULL | 2 | 26 |
| NULL | NULL | NULL | 4 | 35 |
| NULL | NULL | NULL | 5 | 50 |
+------+------+------+----+------+
6 rows in set (0.00 sec)
第六部分——Mysql和Python的交互
使用Python DB API 访问数据库流程
- 开始
- 创建connection
- 获取cursor
- 执行SQL语句(增删改查数据)
- 关闭cursor
- 关闭connection
- 结束
第七部分——分组
建个表
mysql> select * from e3;
+----+-----------------+------+--------+--------+
| id | name | age | height | gender |
+----+-----------------+------+--------+--------+
| 1 | 温迪 | 16 | 40 | 男 |
| 2 | 钟离 | 30 | 65 | 男 |
| 3 | 纳西妲 | 22 | 35 | 女 |
| 4 | 荒泷一斗 | 27 | 70 | 男 |
| 5 | 珊瑚宫心海 | 25 | 45 | 女 |
| 6 | 深海龙蜥 | 100 | 500 | 未知 |
| 7 | 雷电将军 | 26 | 47 | 女 |
| 8 | 草龙 | 85 | 350 | 未知 |
| 9 | 流浪者 | 20 | 50 | 保密 |
| 10 | 凝光 | 30 | 48 | 女 |
+----+-----------------+------+--------+--------+
10 rows in set (0.00 sec)
group by
将查询结果按照1个或者多个字段进行分组,相同的字段为一组。
既可用于单字段分组、也可用于多字段分组。
select gender from e3 group by gender;
mysql> select gender from e3 group by gender;
+--------+
| gender |
+--------+
| 保密 |
| 女 |
| 未知 |
| 男 |
+--------+
4 rows in set (0.00 sec)
group by + group _concat()
group _concat(字段名)可作为单独字段输出。
分组后根据结果,使用group _concat()防止每一组每一字段的值的集合。
select gender,group_concat(name) from e3 group by gender;
mysql> select gender,group_concat(name) from e3 group by gender;
+--------+-----------------------------------------------+
| gender | group_concat(name) |
+--------+-----------------------------------------------+
| 保密 | 流浪者 |
| 女 | 纳西妲,珊瑚宫心海,雷电将军,凝光 |
| 未知 | 深海龙蜥,草龙 |
| 男 | 温迪,钟离,荒泷一斗 |
+--------+-----------------------------------------------+
4 rows in set (0.01 sec)
select gender,group_concat(age) from e3 group by gender;
mysql> select gender,group_concat(age) from e3 group by gender;
+--------+-------------------+
| gender | group_concat(age) |
+--------+-------------------+
| 保密 | 20 |
| 女 | 22,25,26,30 |
| 未知 | 100,85 |
| 男 | 16,30,27 |
+--------+-------------------+
4 rows in set (0.00 sec)
group + 集合函数
通过集合函数对这些值做操作
select gender,count(*) from e3 group by gender;
mysql> select gender,count(*) from e3 group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 保密 | 1 |
| 女 | 4 |
| 未知 | 2 |
| 男 | 3 |
+--------+----------+
4 rows in set (0.00 sec)
select gender,avg(age) from e3 group by gender;
mysql> select gender,avg(age) from e3 group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 保密 | 20.0000 |
| 女 | 25.7500 |
| 未知 | 92.5000 |
| 男 | 24.3333 |
+--------+----------+
4 rows in set (0.00 sec)
group + having
having条件表达式:分组查询后指定条件输出查询结果
having作用和where一样,但having只可用于group by
select gender,count(*) from e3 group by gender having count(*)>2;
mysql> select gender,count(*) from e3 group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女 | 4 |
| 男 | 3 |
+--------+----------+
2 rows in set (0.00 sec)
group by + with rollup
with rollup得到作用是:在最后新增一行,记录当前列里的所有记录之和(可取值、可打印输出)
select gender,count(*) from e3 group by gender with rollup;
mysql> select gender,count(*) from e3 group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 保密 | 1 |
| 女 | 4 |
| 未知 | 2 |
| 男 | 3 |
| NULL | 10 |
+--------+----------+
5 rows in set (0.00 sec)
select gender,group_concat(age) from e3 group by gender with rollup;
mysql> select gender,group_concat(age) from e3 group by gender with rollup;
+--------+--------------------------------+
| gender | group_concat(age) |
+--------+--------------------------------+
| 保密 | 20 |
| 女 | 22,25,26,30 |
| 未知 | 100,85 |
| 男 | 16,30,27 |
| NULL | 20,22,25,26,30,100,85,16,30,27 |
+--------+--------------------------------+
5 rows in set (0.00 sec)
第八部分——分页
还是那样,先建个表。
mysql> select * from e3;
+----+-----------------+------+--------+--------+
| id | name | age | height | gender |
+----+-----------------+------+--------+--------+
| 1 | 温迪 | 16 | 40 | 男 |
| 2 | 钟离 | 30 | 65 | 男 |
| 3 | 纳西妲 | 22 | 35 | 女 |
| 4 | 荒泷一斗 | 27 | 70 | 男 |
| 5 | 珊瑚宫心海 | 25 | 45 | 女 |
| 6 | 深海龙蜥 | 100 | 500 | 未知 |
| 7 | 雷电将军 | 26 | 47 | 女 |
| 8 | 草龙 | 85 | 350 | 未知 |
| 9 | 流浪者 | 20 | 50 | 保密 |
| 10 | 凝光 | 30 | 48 | 女 |
| 11 | 宵宫 | 19 | 55 | 女 |
| 12 | 达达利亚 | 33 | 60 | 男 |
| 13 | 行秋 | 24 | 43 | 保密 |
| 14 | 风魔龙 | 210 | 390 | 未知 |
| 15 | 枫原万叶 | 24 | 63 | 男 |
| 16 | 妮露 | 17 | 46 | 女 |
| 17 | 琴 | 25 | 40 | 女 |
+----+-----------------+------+--------+--------+
17 rows in set (0.00 sec)
当数据量过大的时候,在一页中查看信息比较麻烦
select * from e3 limit 7,3;
mysql> select * from e3 limit 7,3;
+----+-----------+------+--------+--------+
| id | name | age | height | gender |
+----+-----------+------+--------+--------+
| 8 | 草龙 | 85 | 350 | 未知 |
| 9 | 流浪者 | 20 | 50 | 保密 |
| 10 | 凝光 | 30 | 48 | 女 |
+----+-----------+------+--------+--------+
3 rows in set (0.00 sec)
select * from e3 where gender='女' limit 2,4;
mysql> select * from e3 where gender='女';
+----+-----------------+------+--------+--------+
| id | name | age | height | gender |
+----+-----------------+------+--------+--------+
| 3 | 纳西妲 | 22 | 35 | 女 |
| 5 | 珊瑚宫心海 | 25 | 45 | 女 |
| 7 | 雷电将军 | 26 | 47 | 女 |
| 10 | 凝光 | 30 | 48 | 女 |
| 11 | 宵宫 | 19 | 55 | 女 |
| 16 | 妮露 | 17 | 46 | 女 |
| 17 | 琴 | 25 | 40 | 女 |
+----+-----------------+------+--------+--------+
7 rows in set (0.00 sec)
mysql> select * from e3 where gender='女' limit 2,4;
+----+--------------+------+--------+--------+
| id | name | age | height | gender |
+----+--------------+------+--------+--------+
| 7 | 雷电将军 | 26 | 47 | 女 |
| 10 | 凝光 | 30 | 48 | 女 |
| 11 | 宵宫 | 19 | 55 | 女 |
| 16 | 妮露 | 17 | 46 | 女 |
+----+--------------+------+--------+--------+
4 rows in set (0.00 sec)