1、排序语法,关键字排序
升序和降序
默认的排序方式就是升序
升序ASC/降序DESC 配合order by语法
select * from info order by score,id;
降序:select * from 表名 order by 列名 desc;
select * from info order by id,hobbid desc;
以多个列作为排序关键字,只有第一个参数有相同的值,第二个字段才有意义
2、where条件的筛选功能(比较符号)
区间判断:
select * from 表名 where 条件;
select * from info where score > 70;
嵌套多条件:
select * from 表名 where 条件1 or (条件2);
select * from info where score > 70 or ( score > 0 and score < 60 );
3、分组查询
对sql查询的结果进行分组,使用group by语句来实现
group by语句配合聚合函数一起使用
聚合函数的类型:
统计:count
求和:sum
求平均数:avg
最大值:max
最小值:min
在聚合函数分组语句中,所有的非聚合函数列,都要在group by语句中
select 聚合函数(列名1),列名2 from 表名 group by 列名2;
select count(name),hobbid from info group by hobbid;
where条件:
select count(name),hobbid,score from info where score >=80 group by hobbid;
select count(name),hobbid,score from info group by hobbid having score >=80;
例:
#以兴趣为分组,计算成绩的平均值,统计的结果筛选出平均成绩大于等于60分
select avg(score),hobbid from info group by hobbid having avg(score) >=60;
#统计姓名,以兴趣和分数作为分组,统计出成绩大于80的,然后按照降序,对统计姓名的列进行排序
select count(name),hobbid,score from info group by hobbid,score having score > 80 order by count(name) desc;
4、limit
limit 1,3 1是位置偏移量(可选参数),如果不设定位置偏移量,默认就从第一行开始,默认的值是0
查询2-4行
select * from info limit 1,3;
使用limit和降序排列,只显示最后三行
select * from info order by id desc limit 3;
5、对列和表设置别名
在实际工作中,表名和列名可能会很长,书写起来不太方便,多次声明表和列时,完整的展示太复杂,设置别名可以使书写简化,可读性增加,更简洁明了
列名:
select name as 姓名,score as 成绩 from info;
select name 姓名,score 成绩 from info;
表名:
select i.name 姓名,score 成绩 from info as i;
select i.name 姓名,score 成绩 from info i;
6、对表进行复制
主键的约束方式无法复制
create table test as select * from info;
只复制成绩大于等于60的
create table test1 as select * from info where score >=60;
7、通配符
like模糊查询
%:表示0个,也可以是1个,或者多个字符,类似于通配符中的*
_:表示单个字符
查询以s为开头的
select * from info where address like 's%';
查询s开头单个字符
select * from info where address like 's_';
查询s为开头的单个字符
select * from info where address like 's%_'
8、子查询/内查询/嵌套查询
select语句当中又嵌套了一个select,嵌套的select才是子查询。先执行子查询的语句,外部的select再根据子条件的结果进行过滤查找
子查询可以是多个表,也可以是同一张表
关联语法:in not in exists
一张表:
查询成绩大于等于80的id,展示id的成绩和姓名
select name,score from info where id in ( select id from info where score > = 80 );
两张表:
select name,score from info where id in ( select id from test1 where score > = 80 );
使用update修改id=4的分数为80
update info set score=80 where id in ( select id from test where id=4 );
exists判断子查询的结果是否为空,不为空返回true,空返回false
判断test表里是否有成绩大于80的id,存在的话统计主表的id
select count(*) from info where exists (select id from test where score > 80);
这里不是in和not in,会传给主表
这里是判断条件,存在执行,不存在,结果为空则不执行
查询分数,如果分数小于50的则统计info的字段数
select count(*) from info where exists ( select id from info where score < 50 );
子查询当中多表查询和别名
多表查询不超过三张,否则效率降低
info表和test表,这两张表id部分相同,然后根据id相同的部分,查询info表的id的值
select a.id from info a where id in (select b.id from test b where a.id =b.id);
查询info表成绩大于80的
select a.id from info a where a.score>80 and a.id in (select b.id from test b where a.id =b.id);
查询info表的平均成绩
select avg(a.score)from info a where id in ( select b.id from test b where a.id = b.id );
9、mysql的视图
9.1定义
视图是一个虚拟表,表的数据是基于查询的结果生成
视图可以简化复杂的查询,可以隐藏复杂的细节,访问数据更安全
视图表是多表数据的集合体
9.2视图和表的区别
1、存储方式,表是实际的数据行,视图不存储数据,仅仅是查询结果的虚拟表
2、数据更新,更新表可以直接更新视图表的数据
3、占用空间,表实际占用空间,视图表不占用空间,只是一个动态结果的展示
视图表的数据可能是一张表的部分查询数据,也可能是多个表的一部分查询数据
查询当前数据库中的视图表
show full tables in xy102 where table_type like 'VIEW';
创建视图表
create view test2 as select * from info where score>=80;
select * from test2 = select * from info where score>=80;
对视图表的数据进行更新,原表的数据也会发生变化
创建一张视图表v_info,视图表要包含id、name、address,从info和test当中的name值相同的部分创建
create view v_info as select a.id,a.name,a.address from info a where a.name in
( select b.name from test b where a.name=b.name);
视图表就是查询语句的别名,有了视图表可以简化查询到婀语句
表的权限是不一样的,库的权限是有控制的,所以查询视图表的权限相对低,既可以保证原表的数据安全,也简化了查询的过程
删除视图表
drop view 视图表名;
10、连接查询
两张表或者多个表的记录结合起来,基于这些表共同的字段进行数据的拼接。
首先,要确定一个主表作为结果集,然后将其他表的行有选择性的选定到主表的结果上
10.1内连接
两张表或者多张表之间符合条件的数据记录的集合
取两个表或者多个表之间的交集
inner join
select a.a_id,a.a_name from test1 a inner join test2 b on a.a_id=b.b_id;
10.2左连接
左外连接,left join/left outer join
左连接以左表为基础,接收左表的所有行,以左边的记录和右表的记录进行匹配
匹配左表的所有,以及右表中符合条件的行,不符合的显示null
select * from test1 a left join test2 b on a.a_name=b.b_name;
以比较条件为标准展示结果,两个表相同的部分展示出来做拼接,不同的结果显示null
10.3右连接
右外连接,right join/left outer join
左连接以右表为基础,接收右表的所有行,以右边的记录和左表的记录进行匹配
匹配右表的所有,以及左表中符合条件的行,不符合的显示null
select * from test1 a right join test2 b on a.a_name=b.b_name;