mysql高阶语句

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;