03 MySQL查询操作
1.创建数据库
create database python_test charset=utf8
# 提示:在MySQL 语句中,用的是utf8不是utf-8
查看数据库
show databases;
使用数据库
use python_test;
显示当前使用那个数据库
select database();
创建一个数据表 存0 1时使用bit 就行 bit(2)可以存4种 tinyint = bit(8) 2^8 = 256
create table studuent(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal (5,2),
gender enum('男', '女', '中性', '保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
);
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
向表中插入数据
insert into studuent values
(0,'小明',18,168.00,2,1,0),
(0,'小黄',17,175.00,1,2,0),
(0,'小红',14,177.00,2,3,0),
(0,'小汉',11,180.00,3,4,0),
(0,'小八',12,187.00,3,5,0),
(0,'小九',13,182.00,4,6,0),
(0,'小十',18,188.00,3,7,0),
(0,'小之',17,186.00,2,8,0),
(0,'小一',10,188.00,2,9,0),
(0,'小二',15,182.00,3,9,0),
(0,'小三',18,184.00,2,6,0),
(0,'小四',19,185.00,4,4,0),
(0,'小五',13,190.00,2,3,0),
(0,'小六',14,189.00,2,4,0),
(0,'小七',15,178.00,2,5,0),
(0,'十一',15,167.00,1,7,0),
(0,'十二',18,176.00,1,2,0);
insert into classes values
(0, "python01期"),
(0, "python02期"),
(0, "python04期");
show tables ; #展示表
desc classes;# 创建表详情
select * from studuent; # 查看student表中所有信息
2.查询
select * from studuent;
# 查询指定字段
select id, name from studuent;
# 使用as给字段起别名
select name as 姓名, age as 年龄 from studuent;
# select 表明.字段 ..... from 表名;
select studuent.name, studuent.age from studuent;
# 可以通过as给表其别名
# select 别名.字段 .... from 表名 as 别名;
select studuent.name, studuent.age from studuent;
select s.name, s.age from studuent as s;
# 消除重复行
# distinct 字段
select distinct gender from studuent;
3.条件查询
# 条件查询
# 1. 比较运算符 > < >= <= !=
#--select ... from 表名 where ...;
select * from studuent where age > 18; # 显示所有字段select name, gender, id from studuent where age > 18; # 显示选中的字段信息
#2. 逻辑运算符
# and
# 18到28岁之间的学生信息
select * from studuent where age > 18 and age < 28;
slect * from studuent where age > 18 and gender = "女";
select * from studuent where age > 18 and gender = 2;
# or
select * from studuent where age > 18 or gender = 2;
# not
# 不在 18岁以上 并且是女性这个范围内的信息
select * from studuent where not age > 18 or gender = 2; # 仅仅对前一个条件起作用
# 不在18岁以上的女性的这个范围内的信息
slect * from studuent where not (age > 18 or gender = 2); # not对后面和括号内的都起作用
# 年龄不是小于或者等于18 并且是女性
select * from studuent where not ( age < 18 or age = 18) and gender = 2;
select * from studuent where not age <=18 and gender = 2;
#3. 模糊查询(进行匹配 所以效率低)
# like
-- % 替换零个或者多个
-- _ 替换一个
# eg:查询姓名以小开头的信息
select * from studuent where name like "小%";
# 查询姓名当中有小的名字
select * from studuent where name like "%小%";
# eg:查询有两个字的名字
select * from studuent where name like "小_";
# eg:查询两个字以上的名字数据信息
slect * from studuent where name like "__%";
# rlike (正则表达式)
# 查询以小开始的信息
select * from studuent where name rlike "^小.*";
# 查询已小开始 九结束的信息
select * from studuent where name rlike "^小*九$"
select * from studuent where name regexp "^小*九$"
select * from studuent where name regexp "一"
4.范围查询
# in (1,3,8)表示在一个非连续的范围内
# 查询年龄为18,34的人信息
select name from studuent where age = 18 or age = 34;
select name from studuent where age = 18 or age = 34 or age = 12;
select name, age from studuent where age in (12, 18, 34);
# not in不非连续的范围内
# 不是年龄为18, 34的人信息
select name, age from studuent where age not in (12, 18, 34);
# between ... and ...表示在什么范围之内 属于连续问题查询
# 在什么范围
select name, age from studuent where age between 12 and 34;
# 不再范围
select name, age from studuent where age not between 12 and 34;
select name, age from studuent where not age between 12 and 34; # not ... between ... and 是一种语法
select name, age from studuent where age not (between 12 and 34); # 语法错误
# 判断空 is null
# 判断身高为空
select name from studuent where age is null;
# 判断非空 is not null
select name from studuent where age is not null;
5.排序
# 排序
# order by 字段
# asc 从小到达排序 即升序
# desc 从大到小排序 即将序
# 在排序过程中遇到相同值时会按照主键进行排序
# 查询年龄到18~34之间的男性,按照年龄从小到达排序
select * from studuent where (age between 12 and 23) and gender = 1 order by age desc;
# order by 多个字段
select * from studuent where (age between 12 and 23) and gender = 1 order by age desc, gender desc, height desc; # 在年龄相同的情况下按照性别按照升序的方式排序,如果前面排序字段都不相同则后面的字段不执行
6. 聚合函数
聚合函数:一些普通的函数得出的一些结论,不允许与其他字段混合使用, 想要用就要使用分组, 分组和聚合函数一起使用
# 求总数
# count
# 查询男性有多少人
select count(name), avg(height) from studuent;
select * from studuent where gender = 1;
select count(*) from studuent where gender = 1;
select count(*) as 男性人数 from studuent where gender = 1;
# 求最大值
# max
# 查询最大年龄
select * from studuent;
select max(age) as 最大值 from studuent;
# 最小值
# min 秋女性年龄最小值
select min(age) as 最小值 from studuent where gender = 2;
# 求和
# sum
# 求所有年龄总和
select sum(age) as 和 from studuent;
# 求平均值
# avg
select sum(age)/count(*) as 均值 from studuent;
select avg(age) as 最大值 from studuent;
# 四舍五入
# round(123.232221133, 4)
select round(avg(age), 2) as 四舍五入 from studuent;
# 经验:语言里面小数是由误差的,例如银行当中,但是整数没有
# 举例:3.14 我们乘以100变成整数。
6.1 分组
分组和聚合函数一起使用,否则分组就没啥意义
GROUP BY关键字通常和集合函数一起使用,比如MAX()、MIN()、COUNT()、SUM()、AVG()。
例如,要返回每个水果供应商提供的水果种类,这时就要在分组过程中用到COUNT()函数,把数据分为多个逻辑组,并对每个组进行集合计算。
# 按照性别分组,查询所有的性别
select gender, count(gender) from studuent group by gender; # 先分组,再从组内取数据
# 计算每种性别的人数
select gender, count(*) from studuent group by gender; # count(*)表示对分组的计算个数 count(*)指从分组内计算个数
desc studuent;
# 按照性别分组
select gender, count(gender), group_concat(name, ' ', id, ' ', height, ' ', cls_id) from studuent group by gender;
# 计算男性的人数
select gender, count(*) from studuent where gender = 1;
select gender, count(*) from studuent where gender = 1 group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 3 |
+--------+----------+
1 row in set (0.00 sec)
# group_concat(...) 显示的内容
#在MySQL中,可以在GROUP BY子句中使用GROUP_CONCAT()函数,将每个分组中各个字段的值显示出来。
select gender, group_concat(name, age, id), count(*) from studuent where gender = 1 group by gender;
+--------+---------------------------------+----------+
| gender | group_concat(name, age, id) | count(*) |
+--------+---------------------------------+----------+
| 男 | 小黄172,十一1516,十二1817 | 3 |
+--------+---------------------------------+----------+
1 row in set (0.00 sec)
select gender, group_concat(name, "_", age, " ", id), count(*) from studuent where gender = 1 group by gender;
+--------+---------------------------------------+----------+
| gender | group_concat(name, "_", age, " ", id) | count(*) |
+--------+---------------------------------------+----------+
| 男 | 小黄_17 2,十一_15 16,十二_18 17 | 3 |
+--------+---------------------------------------+----------+
1 row in set (0.00 sec)
# where是对原表的数据进行判断
# having表示对分组进行条件判断,是进行分组之后在判断
# 查询平均年年龄超过18岁的性别以及姓名 hanving avg(age)>18
select gender, group_concat(name) from studuent group by gender having avg(age)>8;
select gender, group_concat(name), avg(age), count(*) from studuent group by gender having count(*) > 3;
select gender, group_concat(name), avg(age) from studuent group by gender having avg(age) > 11;
#在GROUP BY子句中使用WITH ROLLUP
#使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量。
select gender, group_concat(name), count(name) from studuent group by gender with rollup having avg(age)>8;
#由结果可以看到,通过GROUP BY分组之后,在显示结果的最后面新添加了一行,该行Total列的值正好是上面所有数值之和。
6.2多字段分组
使用GROUP BY可以对多个字段进行分组,GROUP BY关键字后面跟需要分组的字段,MySQL根据多字段的值来进行层次分组,分组层次从左到右,即先按第1个字段分组,然后在第1个字段值相同的记录中再根据第2个字段的值进行分组,以此类推。
select gender, cls_id, group_concat(name, ' ', cls_id) from studuent group by gender, cls_id;
# GROUP BY和ORDER BY一起使用
# 某些情况下需要对分组进行排序,在前面的介绍中,ORDER BY用来对查询的记录排序,如果和GROUP BY一起使用可以完成对分组的排序。
select gender, group_concat(name, age, height) from studuent group by gender having avg(age)>8 order by count(height);
# GROUP BY子句按订单号对数据进行分组,SUM()函数便可以返回总的订单价格,HAVING子句对分组数据进行过滤,使得只返回总价格大于100的订单,最后使用ORDER BY子句排序输出。
# 当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUP和ORDER BY是互相排斥的。
7 分页查询
limit start, count --直接限制查询出来的个数
带一个参数的LIMIT指定从查询结果的首行开始,唯一的参数表示返回的行数,即“LIMIT n”与“LIMIT 0,n”等价。带两个参数的LIMIT可以返回从任何一个位置开始的指定的行数。返回第一行时,位置偏移量是0。因此,“LIMIT 1, 1”将返回第二行,而不是第一行。
MySQL 8.0中可以使用“LIMIT 4 OFFSET 3”,意思是获取从第5条记录开始后面的3条记录,和“LIMIT 4,3;”返回的结果相同。
# 查询前五个数据
select * from studuent limit 5; # 只显示前两个
select * from studuent order by age desc limit 5;
# 限制查询的个数
select * from studuent limit 7, 4; # 开始第一个为0 规律:limit (第N页-1)*每页的个数, 每页的个数;
# +----+--------+------+--------+--------+--------+-----------+
# | id | name | age | height | gender | cls_id | is_delete |
# +----+--------+------+--------+--------+--------+-----------+
# | 9 | 小一 | 10 | 188.00 | 女 | 9 | |
# | 10 | 小二 | 15 | 182.00 | 中性 | 9 | |
# | 11 | 小三 | 18 | 184.00 | 女 | 6 | |
# | 12 | 小四 | 19 | 185.00 | 保密 | 4 | |
# +----+--------+------+--------+--------+--------+-----------+
# 4 rows in set (0.00 sec)
# 注意:limit必须放在最后。
# 顺序为where order by limit
select * from studuent where gender = 2 order by height desc limit 0, 2
8连接查询
连接查询:即多个表之间进行关联查询
#inner join ... on on意味着条件
select * from studuent inner join classes;
select * from studuent inner join classes on studuent.cls_id = classes.id; #找打相应的信息显示
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# | id | name | age | height | gender | cls_id | is_delete | id | name |
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | 1 | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | 2 | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | 3 | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | 3 | python04期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | 2 | python02期 |
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# 5 rows in set (0.00 sec)
# --按照要求现实姓名和年级
select studuent.* from studuent;
select classes.* from classes;
select studuent.*, classes.name from studuent inner join classes on studuent.cls_id = classes.id;
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | id | name | age | height | gender | cls_id | is_delete | name |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | python04期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | python02期 |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# 5 rows in set (0.00 sec)
# --简写重命名
select s.*, c.name from studuent as s inner join classes as c on s.cls_id = c.id;
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | id | name | age | height | gender | cls_id | is_delete | name |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | python04期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | python02期 |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# 5 rows in set (0.00 sec)
# --加上排序
select s.*, c.name from studuent as s inner join classes as c on s.cls_id = c.id order by c.name, s.id ;
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | id | name | age | height | gender | cls_id | is_delete | name |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | python01期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | python02期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | python02期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | python04期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | python04期 |
# +----+--------+------+--------+--------+--------+-----------+-------------+
# 5 rows in set (0.00 sec)
# --更改显示顺序
select c.name, s.* from studuent as s inner join classes as c on s.cls_id = c.id order by c.name, s.id ;
# +-------------+----+--------+------+--------+--------+--------+-----------+
# | name | id | name | age | height | gender | cls_id | is_delete |
# +-------------+----+--------+------+--------+--------+--------+-----------+
# | python01期 | 1 | 小明 | 18 | 168.00 | 女 | 1 | |
# | python02期 | 17 | 十二 | 18 | 176.00 | 男 | 2 | |
# | python02期 | 2 | 小黄 | 17 | 175.00 | 男 | 2 | |
# | python04期 | 13 | 小五 | 13 | 190.00 | 女 | 3 | |
# | python04期 | 3 | 小红 | 14 | 177.00 | 女 | 3 | |
# +-------------+----+--------+------+--------+--------+--------+-----------+
# 5 rows in set (0.00 sec)
# --多条件排序
select c.name, s.* from studuent as s inner join classes as c on s.cls_id = c.id order by c.name, s.id, s.age;
# +-------------+----+--------+------+--------+--------+--------+-----------+
# | name | id | name | age | height | gender | cls_id | is_delete |
# +-------------+----+--------+------+--------+--------+--------+-----------+
# | python01期 | 1 | 小明 | 18 | 168.00 | 女 | 1 | |
# | python02期 | 2 | 小黄 | 17 | 175.00 | 男 | 2 | |
# | python02期 | 17 | 十二 | 18 | 176.00 | 男 | 2 | |
# | python04期 | 3 | 小红 | 14 | 177.00 | 女 | 3 | |
# | python04期 | 13 | 小五 | 13 | 190.00 | 女 | 3 | |
# +-------------+----+--------+------+--------+--------+--------+-----------+
# 5 rows in set (0.00 sec)
# 2. 外连接查询(分为左连接右连接)
# --右连接查询
#
# --左连接查询(那个表在左边,以这个表为基准取查询的信息,取不出来莫认为Null)
select * from studuent as s left join classes as c on s.cls_id = c.id;
# +----+--------+------+--------+--------+--------+-----------+------+-------------+
# | id | name | age | height | gender | cls_id | is_delete | id | name |
# +----+--------+------+--------+--------+--------+-----------+------+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | 1 | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | 2 | python02期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | 2 | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | 3 | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | 3 | python04期 |
# | 4 | 小汉 | 11 | 180.00 | 中性 | 4 | | NULL | NULL |
# | 5 | 小八 | 12 | 187.00 | 中性 | 5 | | NULL | NULL |
# | 6 | 小九 | 13 | 182.00 | 保密 | 6 | | NULL | NULL |
# | 7 | 小十 | 18 | 188.00 | 中性 | 7 | | NULL | NULL |
# | 8 | 小之 | 17 | 186.00 | 女 | 8 | | NULL | NULL |
# | 9 | 小一 | 10 | 188.00 | 女 | 9 | | NULL | NULL |
# | 10 | 小二 | 15 | 182.00 | 中性 | 9 | | NULL | NULL |
# | 11 | 小三 | 18 | 184.00 | 女 | 6 | | NULL | NULL |
# | 12 | 小四 | 19 | 185.00 | 保密 | 4 | | NULL | NULL |
# | 14 | 小六 | 14 | 189.00 | 女 | 4 | | NULL | NULL |
# | 15 | 小七 | 15 | 178.00 | 女 | 5 | | NULL | NULL |
# | 16 | 十一 | 15 | 167.00 | 男 | 7 | | NULL | NULL |
# +----+--------+------+--------+--------+--------+-----------+------+-------------+
# 17 rows in set (0.00 sec)
#
# --与inner join ... on比较
select * from studuent as s inner join classes as c on s.cls_id = c.id;
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# | id | name | age | height | gender | cls_id | is_delete | id | name |
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# | 1 | 小明 | 18 | 168.00 | 女 | 1 | | 1 | python01期 |
# | 2 | 小黄 | 17 | 175.00 | 男 | 2 | | 2 | python02期 |
# | 3 | 小红 | 14 | 177.00 | 女 | 3 | | 3 | python04期 |
# | 13 | 小五 | 13 | 190.00 | 女 | 3 | | 3 | python04期 |
# | 17 | 十二 | 18 | 176.00 | 男 | 2 | | 2 | python02期 |
# +----+--------+------+--------+--------+--------+-----------+----+-------------+
# 5 rows in set (0.01 sec)
#
#
# --left是指left左边(等号左边)的数据表
select * from studuent;
select * from classes as c left join studuent as s on c.id = s.cls_id;
# +----+-------------+------+--------+------+--------+--------+--------+-----------+
# | id | name | id | name | age | height | gender | cls_id | is_delete |
# +----+-------------+------+--------+------+--------+--------+--------+-----------+
# | 1 | python01期 | 1 | 小明 | 18 | 168.00 | 女 | 1 | |
# | 2 | python02期 | 2 | 小黄 | 17 | 175.00 | 男 | 2 | |
# | 3 | python04期 | 3 | 小红 | 14 | 177.00 | 女 | 3 | |
# | 3 | python04期 | 13 | 小五 | 13 | 190.00 | 女 | 3 | |
# | 2 | python02期 | 17 | 十二 | 18 | 176.00 | 男 | 2 | |
# +----+-------------+------+--------+------+--------+--------+--------+-----------+
# 5 rows in set (0.00 sec)
# --right join ... on 一般用的比较少 使用左连接调换两个数据表的顺序即可
#
# --查询没有对应班级信息的学生
# --思路:对于查询出的结果我们可以将其看作一个新的表,然后在此新表的基础上进行再次操作查询
# --如果在原表里面进行查询使用where
# --在查询的基础上进行再次操作使用having
select * from studuent as s left join classes as c on c.id = s.cls_id having c.id is NULL;
select * from studuent as s left join classes as c on c.id = s.cls_id where c.id is NULL; #这样也行
# +----+--------+------+--------+--------+--------+-----------+------+------+
# | id | name | age | height | gender | cls_id | is_delete | id | name |
# +----+--------+------+--------+--------+--------+-----------+------+------+
# | 4 | 小汉 | 11 | 180.00 | 中性 | 4 | | NULL | NULL |
# | 5 | 小八 | 12 | 187.00 | 中性 | 5 | | NULL | NULL |
# | 6 | 小九 | 13 | 182.00 | 保密 | 6 | | NULL | NULL |
# | 7 | 小十 | 18 | 188.00 | 中性 | 7 | | NULL | NULL |
# | 8 | 小之 | 17 | 186.00 | 女 | 8 | | NULL | NULL |
# | 9 | 小一 | 10 | 188.00 | 女 | 9 | | NULL | NULL |
# | 10 | 小二 | 15 | 182.00 | 中性 | 9 | | NULL | NULL |
# | 11 | 小三 | 18 | 184.00 | 女 | 6 | | NULL | NULL |
# | 12 | 小四 | 19 | 185.00 | 保密 | 4 | | NULL | NULL |
# | 14 | 小六 | 14 | 189.00 | 女 | 4 | | NULL | NULL |
# | 15 | 小七 | 15 | 178.00 | 女 | 5 | | NULL | NULL |
# | 16 | 十一 | 15 | 167.00 | 男 | 7 | | NULL | NULL |
# +----+--------+------+--------+--------+--------+-----------+------+------+
# 12 rows in set (0.00 sec)
9自关联查询
导入areas.sql文件中的数据,从areas.sql文件目录下登录数据库,使用source areas.sql导入数据。
一个表中的一列关联到该表中的另一列:自关联
#使用:省市关联、行政关系等。
create table areas(
aid int primary key,
pid int,
atitle varchar(20),
types tinyint(1) default "2"
);
# 查看省份
select * from areas where pid = 1;
# 查看省份市的个数
select * from areas where atitle = '陕西';
+-----+------+--------+-------+
| aid | pid | atitle | types |
+-----+------+--------+-------+
| 24 | 1 | 陕西 | 1 |
+-----+------+--------+-------+
1 row in set (0.00 sec)
select * from areas where pid = 24;
+-----+------+--------+-------+
| aid | pid | atitle | types |
+-----+------+--------+-------+
| 311 | 24 | 西安 | 2 |
| 312 | 24 | 安康 | 2 |
| 313 | 24 | 宝鸡 | 2 |
| 314 | 24 | 汉中 | 2 |
| 315 | 24 | 商洛 | 2 |
| 316 | 24 | 铜川 | 2 |
| 317 | 24 | 渭南 | 2 |
| 318 | 24 | 咸阳 | 2 |
| 319 | 24 | 延安 | 2 |
| 320 | 24 | 榆林 | 2 |
+-----+------+--------+-------+
10 rows in set (0.00 sec)
select * from areas where pid = 311;
+------+------+-----------+-------+
| aid | pid | atitle | types |
+------+------+-----------+-------+
| 2596 | 311 | 莲湖区 | 3 |
| 2597 | 311 | 新城区 | 3 |
| 2598 | 311 | 碑林区 | 3 |
| 2599 | 311 | 雁塔区 | 3 |
| 2600 | 311 | 灞桥区 | 3 |
| 2601 | 311 | 未央区 | 3 |
| 2602 | 311 | 阎良区 | 3 |
| 2603 | 311 | 临潼区 | 3 |
| 2604 | 311 | 长安区 | 3 |
| 2605 | 311 | 蓝田县 | 3 |
| 2606 | 311 | 周至县 | 3 |
| 2607 | 311 | 户县 | 3 |
| 2608 | 311 | 高陵县 | 3 |
+------+------+-----------+-------+
13 rows in set (0.01 sec)
# 另一种查询方法(假如有两章表的情况下,一张省份表,一张地级市表的情况下,地级市的pid = 省份表的aid)
# 思路:可以通过as生成新的表
select * from areas as province inner join areas as city on province.aid = city.pid having province.atitle = "陕西";
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "陕西";
+-----+------+--------+-------+-----+------+--------+-------+
| aid | pid | atitle | types | aid | pid | atitle | types |
+-----+------+--------+-------+-----+------+--------+-------+
| 24 | 1 | 陕西 | 1 | 311 | 24 | 西安 | 2 |
| 24 | 1 | 陕西 | 1 | 312 | 24 | 安康 | 2 |
| 24 | 1 | 陕西 | 1 | 313 | 24 | 宝鸡 | 2 |
| 24 | 1 | 陕西 | 1 | 314 | 24 | 汉中 | 2 |
| 24 | 1 | 陕西 | 1 | 315 | 24 | 商洛 | 2 |
| 24 | 1 | 陕西 | 1 | 316 | 24 | 铜川 | 2 |
| 24 | 1 | 陕西 | 1 | 317 | 24 | 渭南 | 2 |
| 24 | 1 | 陕西 | 1 | 318 | 24 | 咸阳 | 2 |
| 24 | 1 | 陕西 | 1 | 319 | 24 | 延安 | 2 |
| 24 | 1 | 陕西 | 1 | 320 | 24 | 榆林 | 2 |
+-----+------+--------+-------+-----+------+--------+-------+
10 rows in set (0.01 sec)
select * from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "西安";
+-----+------+--------+-------+------+------+-----------+-------+
| aid | pid | atitle | types | aid | pid | atitle | types |
+-----+------+--------+-------+------+------+-----------+-------+
| 311 | 24 | 西安 | 2 | 2596 | 311 | 莲湖区 | 3 |
| 311 | 24 | 西安 | 2 | 2597 | 311 | 新城区 | 3 |
| 311 | 24 | 西安 | 2 | 2598 | 311 | 碑林区 | 3 |
| 311 | 24 | 西安 | 2 | 2599 | 311 | 雁塔区 | 3 |
| 311 | 24 | 西安 | 2 | 2600 | 311 | 灞桥区 | 3 |
| 311 | 24 | 西安 | 2 | 2601 | 311 | 未央区 | 3 |
| 311 | 24 | 西安 | 2 | 2602 | 311 | 阎良区 | 3 |
| 311 | 24 | 西安 | 2 | 2603 | 311 | 临潼区 | 3 |
| 311 | 24 | 西安 | 2 | 2604 | 311 | 长安区 | 3 |
| 311 | 24 | 西安 | 2 | 2605 | 311 | 蓝田县 | 3 |
| 311 | 24 | 西安 | 2 | 2606 | 311 | 周至县 | 3 |
| 311 | 24 | 西安 | 2 | 2607 | 311 | 户县 | 3 |
| 311 | 24 | 西安 | 2 | 2608 | 311 | 高陵县 | 3 |
+-----+------+--------+-------+------+------+-----------+-------+
13 rows in set (0.01 sec)
10子查询
简单来讲,子查询就是一个select中嵌套了一个select,子查询效率比较低。
--查询身高最高的男生
select max(height) from studuent;
+-------------+
| max(height) |
+-------------+
| 190.00 |
+-------------+
1 row in set (0.00 sec)
select * from studuent where height = (select max(height) from studuent);
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 13 | 小五 | 13 | 190.00 | 女 | 3 | |
+----+--------+------+--------+--------+--------+-----------+
1 row in set (0.00 sec)
--利用子查询解决自关联查询中的问题
select * from areas where pid = (select aid from areas where atitle = "陕西");
+-----+------+--------+-------+
| aid | pid | atitle | types |
+-----+------+--------+-------+
| 311 | 24 | 西安 | 2 |
| 312 | 24 | 安康 | 2 |
| 313 | 24 | 宝鸡 | 2 |
| 314 | 24 | 汉中 | 2 |
| 315 | 24 | 商洛 | 2 |
| 316 | 24 | 铜川 | 2 |
| 317 | 24 | 渭南 | 2 |
| 318 | 24 | 咸阳 | 2 |
| 319 | 24 | 延安 | 2 |
| 320 | 24 | 榆林 | 2 |
+-----+------+--------+-------+
10 rows in set (0.00 sec)