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