一、最简单的增、删、改、查
增
语法:insert into 表名(字段1,字段2)values(值1,值2);
inster into Students (age,sex)values(18,"女”);
删
语法:delete from 表名 where条件;
delete from Students wehere id = 1;
改
语法:updata 表名 set 字段1=值1 ,字段2=值2 where条件;
updata Students set age=10,sex="女“ where id =2;
查
语法:select 字段1,字段2 from 表名 where条件;
select * from Students where age>20;
select name ,sex from Students wherre age >20;
二、MySQL查询集
1.条件
- 使用where子句对表中的数据筛选,结果为true的行会出现在结果集中
- 语法如下:
select * from 表名 where 条件;
例: select * from students where id=1;
- where后面支持多种运算符,进行条件的处理
- 比较运算符
- 逻辑运算符
- 模糊查询
- 范围查询
- 空判断
比较运算符
- 等于: =
- 大于: >
- 大于等于: >=
- 小于: <
- 小于等于: <=
- 不等于: != 或 <>
- 例1:查询编号大于3的学生
select * from students where id > 3;
- 例2:查询编号不大于4的学生
select * from students where id <= 4;
- 例3:查询姓名不是“黄蓉”的学生
select * from students where name != '黄蓉';
- 例4:查询没被删除的学生
select * from students where isdelete=0;
逻辑运算符
- and
- or
- not
- 例5:查询编号大于3的女同学
select * from students where id > 3 and gender=0;
- 例6:查询编号小于4或没被删除的学生
select * from students where id < 4 or isdelete=0;
模糊查询
- like
- %表示任意多个任意字符
- _表示一个任意字符
- 例7:查询姓黄的学生
select * from students where name like '黄%';
- 例8:查询姓黄并且名字是一个字的学生
select * from students where name like '黄_';
- 例9:查询姓黄或叫靖的学生
select * from students where name like '黄%' or name like '%靖';
范围查询
- in表示在一个非连续的范围内
- 例10:查询编号是1或3或8的学生
select * from students where id in(1,3,8);
- between ... and ...表示在一个连续的范围内
- 例11:查询编号为3至8的学生
select * from students where id between 3 and 8;
- 例12:查询学生是3至8的男生
select * from students where id between 3 and 8 and gender=1;
空判断
- 注意:null与''是不同的
- 判空is null
- 例13:查询没有填写身高的学生
select * from students where height is null;
- 判非空is not null
- 例14:查询填写了身高的学生
select * from students where height is not null;
- 例15:查询填写了身高的男生
select * from students where height is not null and gender=0;
优先级
- 优先级由高到低的顺序为:小括号,not,比较运算符,逻辑运算符
- and比or先运算,如果同时出现并希望先算or,需要结合()使用
2.排序
- 为了方便查看数据,可以对数据进行排序
- 语法:
select * from 表名 order by 列1 asc|desc,列2 asc|desc,...
- 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
- 默认按照列值从小到大排列
- asc从小到大排列,即升序
- desc从大到小排序,即降序
- 例1:查询未删除男生信息,按学号降序
select * from students where gender=1 and isdelete=0 order by id desc;
- 例2:查询未删除学生信息,按名称升序
select * from students where isdelete=0 order by name;
3.聚合函数
- 为了快速得到统计数据,经常会用到如下5个聚合函数
- count(*)表示计算总行数,括号中写星与列名,结果是相同的
- 聚合函数不能在 where 中使用
- 例1:查询学生总数
select count(*) from students;
- max(列)表示求此列的最大值
- 例2:查询女生的编号最大值
select max(id) from students where gender=0;
- min(列)表示求此列的最小值
- 例3:查询未删除的学生最小编号
select min(id) from students where isdelete=0;
- sum(列)表示求此列的和
- 例4:查询男生的编号之和
select sum(id) from students where gender=1;
- avg(列)表示求此列的平均值
- 5:查询未删除女生的编号平均值
select avg(id) from students where isdelete=0 and gender=0;
4.分组
- 按照字段分组,表示此字段相同的数据会被放到一个组中
- 分组后,分组的依据列会显示在结果集中,其他列不会显示在结果集中
- 可以对分组后的数据进行统计,做聚合运算
- 语法:
select 列1,列2,聚合... from 表名 group by 列1,列2...
- 例1:查询男女生总数
select gender as 性别,count(*) from students group by gender;
- 例2:查询各种年龄的人数
select age as 年龄,count(*) as 数量 from students group by age;
分组后的数据筛选
- 语法:
select 列1,列2,聚合... from 表名 group by 列1,列2,列3... having 列1,...聚合...
- having后面的条件运算符与where的相同
- 例3:查询男生总人数
方案一 select count(*) from students where gender=1;
方案二: select gender as 性别,count(*) from students group by gender having gender=1;
对比where与having
- where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
- having是对group by的结果进行筛选
5.获取部分行
- 当数据量过大时,在一页中查看数据是一件非常麻烦的事情
- 语法
select * from 表名 limit start,count
- 从start开始,获取count条数据
- start索引从0开始
- 例1:查询前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 isdelete=0 limit (n-1)*m,m
6.连接查询
- 当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回
- mysql支持三种类型的连接查询,分别为:
- 内连接查询:查询的结果为两个表匹配到的数据
- 右连接查询:查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用null填充
- 左连接查询:查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用null填充
- 语法
select * from 表1 inner或left或right join 表2 on 表1.列=表2.列
- 例1:使用内连接查询班级表与学生表
- 此处使用了as为表起别名,目的是编写简单
select * from students inner join pythons on students.cls_id = pythons.id;
- 例2:使用左连接查询班级表与学生表
select * from students as s left join pythons as p on s.cls_id = p.id;
- 例3:使用右连接查询班级表与学生表
select * from students as s inner join pythons as p on s.cls_id = p.id;
- 例4:查询学生姓名及班级名称
select s.name,p.name from students as s inner join pythons as p on s.cls_id = p.id;
7.自关联
- 设计省信息的表结构provinces
- id
- ptitle
- 设计市信息的表结构citys
- id
- ctitle
- proid
- citys表的proid表示城市所属的省,对应着provinces表的id值
- 问题:能不能将两个表合成一张表呢?
- 思考:观察两张表发现,citys表比provinces表多一个列proid,其它列的类型都是一样的
- 意义:存储的都是地区信息,而且每种信息的数据量有限,没必要增加一个新表,或者将来还要存储区、乡镇信息,都增加新表的开销太大
- 答案:定义表areas,结构如下
- id
- atitle
- pid
- 因为省没有所属的省份,所以可以填写为null
- 城市所属的省份pid,填写省所对应的编号id
- 这就是自关联,表中的某一列,关联了这个表中的另外一列,但是它们的业务逻辑含义是不一样的,城市信息的pid引用的是省信息的id
- 在这个表中,结构不变,可以添加区县、乡镇街道、村社区等信息
- 创建areas表的语句如下:
create table areas( aid int primary key, atitle varchar(20), pid int );
- 从sql文件中导入数据
source areas.sql;
- 查询一共有多少个省
select count(*) from areas where pid is null;
- 例1:查询省的名称为“山西省”的所有城市
select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';
- 例2:查询市的名称为“广州市”的所有区县
select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='广州市';
8.子查询(sub query)
子查询
- 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
主查询
- 主要查询的对象,第一条 select 语句
主查询和子查询的关系
- 子查询是嵌入到主查询中
- 子查询是辅助主查询的,要么充当条件,要么充当数据源
- 子查询是可以独立存在的语句,是一条完整的 select 语句
子查询分类
- 标量子查询: 子查询返回的结果是一个数据(一行一列)
- 列子查询: 返回的结果是一列(一列多行)
- 行子查询: 返回的结果是一行(一行多列)
- 表级子查询: 返回的结果是多行多列
标量子查询
- 查询班级学生平均年龄
- 查询大于平均年龄的学生
查询班级学生的平均身高
select * from students where age > (select avg(age) from students);
列级子查询
- 查询还有学生在班的所有班级名字
- 找出学生表中所有的班级 id
- 找出班级表中对应的名字
select name from pythons where id in (select cls_id from students);
行级子查询
- 需求: 查找班级年龄最大,身高最高的学生
- 行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素
select * from students where (height,age) = (select max(height),max(age) from students);
表级子查询
- 查询学生与班级对应的信息
select * from (select stu.*,pys.name as clsname from students as stu inner join pythons as pys on stu.cls_id = pys.id) as t1;
说明:发现很多表级子查询的语句,都是可以使用连接查询实现的,此时推荐使用连接查询,因为连接查询的语句更简洁,逻辑更清晰
子查询中特定关键字使用
- in 范围
- 格式: 主查询 where 条件 in (列子查询)
- any | some 任意一个
- 格式: 主查询 where 列 = any (列子查询)
- 在条件查询的结果中匹配任意一个即可,等价于 in
- all
- 格式: 主查询 where 列 = all(列子查询) : 等于里面所有
- 格式: 朱查询 where 列 <>all(列子查询) : 不等一其中所有
9.总结
查询的完整格式
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
- 实际使用中,只是语句中某些部分的组合,而不是全部