MySQL中常用的一些操作表，字段和数据的SQL语句(三)

mysq 整理当中的第三部分

六、获取部分行(分页)

当数据量过大时，在一页中查看数据是一件非常麻烦的事情

语法

select * from 表名 limit start,count

说明: 从start开始 获取count条数据

例: 查询前3行男生的信息

select * from students where gender=1 limit 0,3;

示例: 分页

• 已知：每页显示m条数据，当前显示第n页
  • 求总页数：此段逻辑后面会在python中实现
  • 查询总条数p1
  • 使用p1除以m得到p2
  • 如果整除则p2为总数页
  • 如果不整除则p2+1为总页数
• 求第n页的数据

select * from students where is_delete=0 limit (n-1)*m,m

七、连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回
mysql支持三种类型的连接查询，分别为：

• 内连接查询：查询的结果为两个表匹配到的数据
• 右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
• 左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

语法

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

例：使用内连接查询班级表与学生表

select * from students inner join classes on students.cls_id = classes.id;

例：使用左连接查询班级表与学生表
# 此处使用了as为表起别名，目的是编写简单

select * from students as s left join classes as c on s.cls_id = c.id;

例: 使用右连接查询班级表与学生表

select * from students as s right join classes as c on s.cls_id = c.id;

例: 查询学生姓名及班级名称

select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

八、自关联

设计省市区数据表中,可以通过自关联建立数据表结构.结构如下:

• id
• atitle
• pid

说明:

• 因为省没有所属的省份，所以可以填写为null
• 城市所属的省份pid，填写省所对应的编号id
• 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
• 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

创建areas表的语句如下：

create table areas(
	id int primary key,
	atitle varchar(20),
	pid int	
);

从sql文件中导入数据
资源连接: https://pan.baidu.com/s/19LS6Gsbfyrq9WXPVm56OCw
提取码: xzjv

source areas.sql

例: 查询一共有多少个省

select count(*) from areas where pid is null;

例: 查询省的名称为“山西省”的所有城市

select city.* from areas as city inner join areas as province
 on city.pid=province.id where province.atitle="山西省";

例: 查询市的名称为“广州市”的所有区县

select  dis.* from areas as dis inner join areas as city 
on dis.pid=city.id where city.atitle="广州市";

九、子查询

子查询

• 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

主查询

• 主要查询的对象,第一条 select 语句

主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的,要么充当条件,要么充当数据源
• 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询分类

• 标量子查询: 子查询返回的结果是一个数据(一行一列)
• 列子查询: 返回的结果是一列(一列多行)
• 行子查询: 返回的结果是一行(一行多列)

1) 标量子查询

例: 查询大于平均年龄的学生

select * from students where age > (select avg(age) from students);

2) 列级子查询

例: 查询还有学生在班的所有班级名字

select name from classes where id in (select distinct cls_id from students);

3) 列级子查询

例: 查找班级年龄最大,身高最高的学生

select * from students where (height,age) = (select max(height),max(age) from students);

4) 子查询中特定关键字使用

• in 范围
  格式: 主查询 where 条件 in (列子查询)

十、总结

查询的完整格式

SELECT select_expr [,select_expr,...] [      
     FROM tb_name
     [WHERE 条件判断]
     [GROUP BY {col_name | postion} [ASC | DESC], ...] 
     [HAVING WHERE 条件判断]
     [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
     [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

完整的select语句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

• 执行顺序为：
  • from 表名
  • where …
  • group by …
  • select distinct *
  • having …
  • order by …
  • limit start,count
• 实际使用中，只是语句中某些部分的组合，而不是全部