SQL语句进阶

数据库SQL语句进阶查询(主要是上一篇的内容太长了)

1、记录部分问题

数据库设置了utf8然后插入中文还是会报错，考虑建表的时候的字符集不对，可以这样建表create table t2(id int primary key auto_increment,name varchar(64)) charset utf8 collate utf8_general_ci;
字段太多了，显示不出来可以加\G来显示，select * from t1\G;
部分Windows旧版本还有问题，可以把字符集设置成GBK

2、查询关键字/语句

先看关键字执行顺序，select id,name from t2 where id = 2;

from：先找到表
where：然后找到筛选条件
select：再找具体数据

3、where条件

用于对整体数据的筛选

-- 查找id>=3 & id <=6的
select val from t3 where id between 3 and 6;

-- age=22|33|44的值
select * from t3 where age in (22,33,44)；
-- 还可以查反着的数据
select * from t3 where age not in (22,33,44)；

-- 查询包含字母a的数据
select * from t3 where name like '%a%';

-- 查询4个字符长度的内容
select * from t3 where name like '____';
select * from t3 where char_length(name) = 4;

-- 查询id小于3或者id大于6的数据
select * from t3 where id not between 3 and 6;

-- 查询name为空的数据
select * from t3 where name is NULL;

4、group by分组

分组之后，最小的可操作单位应该是组，不再是单个数据。

在什么时候需要分组：比如需要获取每个部分的最高薪资、平均薪资之类的时候。

select * from emp group by post;

上面的命令在非严格模式下是可以正常执行的，返回每个组的第一条数据。但是这不符合分组的规范。分组之后不应该考虑单个数据，应该以组为操作单位，分组之后没办法单独获取组内单个数据。如果开启了严格模式，上面的命令会直接报错。

设置严格模式以及group by

-- 设置完之后需要重新退出进入
set global sql_mode = 'STRICT_TRANS_TABLES, ONLY_FULL_GROUP_BY';

select post from emp group by post;

设置严格模式之后分组默认只能拿到分组的依据。按照什么分组就只能拿到分组，其他字段不能直接获取，需要借助一些方法(聚合函数)。

比如要获取部门最高薪资

select post,max(salary) from emp group by post;

-- 加个别名，方便看
select post as '部门',max(salary) as '最高薪资' from emp group by post;

分组的注意事项：

关键字where和group by同时出现的时候，group by必须在where的后面
聚合函数只能在分组之后使用，where后面不能使用聚合函数

4.1 group_concat()

group_concat()可以获取分组之后的其他字段值，还支持拼接，以及多个字段获取

-- 获取其他普通字段值
select post,group_concat(name) from emp group by post;

-- 拼接
select post,group_concat(name, '__abc') from emp group by post;

-- 多字段
select post,group_concat(name, ':', salary) from emp group by post;

分组之后用group_concat，没分组之前可以使用concat

select concat('name:',name) as '名字',concat('age:',age) as '年龄' from emp;

如果分隔符号是一样的话可以使用concat_ws,比如都是:

select concat_ws(':', name, age) from emp;

PS：as不只可以给字段起别名，也可以给表起别名：select t1.id from emp as t1;

5、聚合函数

聚合函数如果在没分组的地方使用，默认整张表就是一个组。

常用的聚合函数：

count()：计数，返回数量，不能填null的字段
avg()：平均值
sum()：返回和
max()：返回最大值
min()：返回最小值

6、having筛选

having筛选是分组之后筛选，where是分组之前筛选。他们的语法是一样的。

having是可以直接使用聚合函数的。

-- 统计各部门年龄30以上的员工的平均工资，并且保留平均工资大于10000的部门
select post, avg(salary) from where age > 30 group by post having avg(salary) > 10000;

6、distinct去重

去重必须是完全一样的数据才可以去重，一定不要将主键忽视，有主键存在的情况下不重复。

-- id是主键
select distinct id,age from emp;
select distinct age from emp;

7、order by 排序

-- 默认是升序 asc  可以不写
select * from emp order by salary;

-- 修改为降序
select * from emp order by salary desc;

-- 多个参数
select * from emp order by age desc,salary asc;

-- 统计各部门年龄30以上的员工的平均工资，并且保留平均工资大于10000的部门并且根据平均工资降序排序
select post, avg(salary) from where age > 30 group by post having avg(salary) > 10000 order by avg(salary) desc;

8、limit限制展示条数

数据量太大的情况下，需要做分页处理。

select * from emp limit 3;

-- 范围限制，从位置0开始取5条数据
select * from emp limit 0,5;

9、正则

select name from emp where name regexp 'ok$';

10、多表操作

假设有一张员工表dep一张部门表emp，员工表里的字段对应部门表里的，需要查询出来。

-- 查询出两张表交织出来的笛卡尔积
select * from dep,emp;
-- 拼表操作
select * from emp,dep where emp.dep_id = dep.dep_id;

sql提供的连表操作的方法：

inner join：内连接，只拼接两张表中公有的数据
left join：左连接，拼接完成后，左表的数据都显示，右表没有与左表对应的数据显示为null
right join：右连接，拼接完成后，右表的数据都显示，左表没有与右表对应的数据显示为null
union：全连接，左右两表的数据都展示出来，就是左连接和右连接拼到了一起。

使用场景：

-- 1. 内连接
select * from emp inner join dep on emp.dep_id = dep.id;

-- 2. 左连接
select * from emp left join dep on emp.dep_id = dep.id;

-- 3. 右连接
select * from emp right join dep on emp.dep_id = dep.id;

-- 4. 全连接
select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;

11、子查询

将一个查询语句的结果当做另外一个查询语句的条件。

-- 假设有两条语句，第一条的结果是第二条的条件
select id from dep where name = '技术' or name = '人力';
select name from emp where dep_id in (200, 201);

-- 可以结合成一句
select name from emp where dep_id in (select id from dep where name = '技术' or name = '人力');

表的查询结果可以作为其他表的查询条件，也可以通过起别名的方式把它作为一张虚拟表跟其他表关联。

11、exists

判断查到的内容是否为空，只返回true或者false，用于子查询中，如果返回false，外层查询语句不再执行。

select * from emp where exists (select id from dep where id > 300);

猫说