Mysql数据库查询语句(史上最全)_mysql 全库查询某个值的语句

公众号 CppCoding

show databases;//显示所有数据库
use 库;//进入库
show tables;//查看库中的表
select database();//查看当前所在的库
desc table;//查看表结构
select version();//查看mysql版本

注释 # 单行
/* */多行
1
2
3
4
5
6
7
8
9

起别名
select 100%98 as 结果;//as可以省略

去重 distinct
select distinct department_id from employees;

+的作用（在mysql中+的作用只有算数加和作用）
select 10+11;//21
select '100'+10;//110
select 'from'+90;//90
select null+any;//只要其中一个为null，结果为null
//加和中出现字符型，则自动转为数值型，若转换成功，则使用加法，若转换失败，则字符串值为0

拼接字符串 concat
select concat(last_name,first_name) as 姓名;

函数 ifnull
ifnull(str1,str2)//判断str1字段里的值是否是null，若是则输出str2，若不是则输出原来的值

显示表employees的全部列，各个列之间使用，连接，列头显示为out_put
select concat(`first_name`,',',ifnull(last_name,0)) as out_put
from employees;
** 注意：字段使用`为反引号，字符使用单引号 **

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

条件查询

结构：
	where
条件运算符：>    <     =       !=(<>)      >=     <=
逻辑运算符：&&(and)    ||(or)    !(not)
模糊查询：like(not like)        between and        in          is null(is not null)


查询工资大于12000员工的信息
select *
from employees
where salary>12000;

部门编号不等于90的员工名和部门编号
select last_name,department_id
from employees
where department_id != 90;

查询工资在10000-20000之间的员工的名字，工资和奖金
select last_name,salsry,commission_pct
from employees
where salary>=10000 and salary<=20000;

部门编号不在90到110之间，或工资高于15000的员工信息
select *
from employees
where department_id<90 and department_id>110 or salary>15000;

##### 模糊查询
_   代表代替一个字符
% 通配符

员工名中包含字符a的员工信息
select *
from empolyees
where last_name like '%a%';

查询员工名第三个字母为a，第五个为a的员工信息
select *
from employees
where last_name like '__a_a%';

查询员工名第二个字符为_的信息
select *
from employees
where last_name like '_\_%';       \为转义字符

也可以写为
'_$_%' escape '$';   escape是指定一个字符转义字符

员工编号在100-200之间
where employee_id between 100 and 200;

查询员工的工种编号是IT_PROG,AD_UP中的一个的信息
where iob_id in ('IT_PROG','AD_UP');

查询没有奖金的员工名和奖金率
where commission_pct is null;
where commission_pct is not null;     有奖金率

**=或者<>不能用于判断null值
<=> 安全等于，可以判断null值也可以判断数值=
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

排序查询

语法
	select
	from
	where
	order by//(排序)按照某一个字段    asc 升序      desc  降序
1
2
3
4
5

工资从高到低
select * from empolyees order by salary desc;

部门编号>=90的员工信息，按照时间先后
select * from empolyees where department_id >=90 order by hireday asc;

按照年薪高低显示员工信息和年薪
select *,salary*12(1+ifnull(cpmmission_pct,0)) as 年薪 from employees order by 年薪 desc;

按照姓名长度显示姓名和工资
select length(last_name) 姓名长度,last_name,salary from employees order by 姓名长度 desc;

查询员工信息要求先按照工资升序再按照员工编号降序
select *
from employees
order by salary asc,employee_id desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

常见函数

length('张三丰abc');//3*3+3=12    utf-8的汉字为3个字节
upper('abc');//ABC
lower('AFv');//afv

将姓变大写，将名变小写然后拼接
select concat(upper(last_name),lower(first_name));

==字符串函数的第一个索引从1开始==

substr//字符截取
用法1：substr('字符串',num);//表示在第num个字符处截取，从第num个字符处显示，第num个也显示
用法2：substr('字符串',num,count);//表示在num开始截取，总共count个字符。

instr//查找子串在主串的第一个索引，找不到为0
select instr('123456','1');//返回1

==注意：只去掉前后的，中间不去==
trim //去掉前后的空格
select length(trim('  abc  '));//返回3
select length(trim(' a bds  '));//返回5

lpad//用指定的字符实现左填充
select lpad('abc',10,'+');//+++++++abc
select lpad('abc',2,'_');//ab

rpad//右填充

replace//替换
select replace('ababab','a','b');//bbbbbb

round//四舍五入
round(-1.45);//-1
round(1.65);//2
round(-1.657,2);//-1.66

ceil//向上取整(大于或者等于该参数的最小整数)
select ceil(1.002);//2

floor//向下取整


truncate//截断
select truncate(1.299,1);//1.2    注意，后面的数字是要保留的小数后面的位数，截断不能进位

mod//取余
select mod(-10,3);//-1

now//返回当前系统时间日期
select now();

curdate//返回日期

curtime//返回时间

获取指定时间的年、月、日、时分秒
select year(now());//剩下的类似

sum//求和
select sum(salary) from employees;

avg//求平均值

max/min  //求最大或者最小

count//求非非空值有多少

sum和avg支持数值型
max、min、count支持日期、名字、函数等

max、min、count、sum和avg忽略null值

count//可用来统计行数
select count(*) from employees;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

流控制函数

if//实现if else的效果
select if(10<5,'true','false');//如果第一个语句是正确的则返回第二个，否则将返回第三个

case//实现switch case效果
case 要判断的表达式
when 常量 then 要显示的
else 要显示的值
end

select salary,
case department_id
when 30 then salary*1.1
when 40 then salary*2
else salary
end as 新工资
from employees;

case//第二种用法，类似多重if
case后面不加字段

select salary
case
when salary>2000 then 'A'
when salary>3000 then 'B'
else 'C'
end;
=总结：两者的不同在于，第一种是等值查询，第二种是条件查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

#### 分组语句

语法：
select 显示的列，group by后面出现的字段
from 表
where 条件
group by 列
order by 字段
1
2
3
4
5
6

查询每个位置上部门的个数
select count(*),location_id
from departmentsgroup
group by location_id;

查询邮箱中包含a的每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;

查询哪个部门员工数>2
select count(*),department_id
from employees
group by department_id
having count(*)>2;

查询每个工种有奖金员工的最高工资大于12000的工种编号和工资
select max(salary),job_id
from employees
where commission_pct is not null
having max(salary)>12000;

==having的使用场景是在已经查询完的基础上再次查询==

按照多个字段分组

查询每个部门每个工种的员工平均工资
select avg(salary),department_id,job_id
from employees
group by department_id,job_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

连接查询

查询女孩表中有男朋友的名字
select girls,boys from gid,boyid;//最后显示3*4=12行   假设girls为3行，boys为4行

正确的查询是
select name,boyname 
from girls,boys
where girls_id=boys_id;

SQL99语法支持内、外、交叉连接（不支持全外连接）
内连接：支持等值连接，非等值连接，自连接
外连接：支持左外连接，右外连接，全外连接
1
2
3
4
5
6
7
8
9
10
11

==等值连接：==
查询有奖金的员工名和部门名
select last_name,department_id
from employees e,department_id d
where e.department_id=d.department_id
and e.commission is not null;

查询每个城市的部门个数
select count(*),city
from departments d,location l
where d.location_id=l.location_id
group by city;

三表连查
查询员工名，部门名和所在的城市
select last_name,department_name,city
from employees e,departments d,location l
where e.department_id=d.department_id
and d.location_id=l.location_id;

==非等值连接==
查询员工的工资和工资级别
select salary,grade_level
from employees e,job_grades g
where salary between g.lowest and g.highest;
[非等值的详细介绍](https://blog.csdn.net/Dear_mango/article/details/80612757)

==自连接==指从一张表中筛选等值条件
select e.employees_id,e.last_name,m.employees_id,m.last_name
from employees e,employees m
where e.manager_id=m.employee_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

SQL99语法

select 查询列表
from 表1
[连接类型] join 表2
on 连接条件
where 筛选条件
group by
order by
1
2
3
4
5
6
7

内连接 inner
左外连接 left outer
右外连接 right outer
全外连接 full outer不支持
交叉连接 cross

==内连接==对表的顺序没有要求
查询员工名和部门名
select last_name,department_name
from departments d
inner join employees e
on e.department_id=d.department_id;

查询员工名，部门名，工种名，并按照部门名降序
select last_name,department_name,job_title
from employees e
inner join departments d 
on e.department_id=d.department_id
inner join jobs j
on j.job_id=e.job_id
order by departtment_name desc;
**需要注意多表连接的时候是一个inner后面跟着自己的on（筛选条件）

==外连接==
用于查询一个表有另一个表没有的记录
外连接的查询结果是主表中所有记录
如果从表中有匹配的则显示从表内容
如果没有，则显示null
左外连接的主表是左边的
外连接对表的顺序有要求
哪个表当主表哪个表就在前面
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

子查询

子查询是出现在其他语句内部的select语句

出现的位置：
	select后//支持标量子查询
	from后//表子查询
	where或者having //标量子查询、列子查询、行子查询
	exists//相关子查询

按行列数不同分为：标量子查询（一行一列）
				列子查询（一行多列）
				行子查询（多行一列）
				表子查询（多行多列）表子就是相关子查询

特点：
1.子查询在小括号内
2.在条件的右侧
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

==标量子查询==
使用单行操作符（大于，小于，等于，不等于，大于等于，小于等于）

查询谁的工资比Abel高
select *
from employees
where salary>(
	select salary
	from employees
	where last_name='Abel'
);

返回job_id与141号工种相同salary比143号员工多的工资姓名job_id和工资
select salary,last_name,job_id
from employees
where job_id=(
	select job_id
	from employees
	where employee_id=141
)and salary>(
	select salary
	from employees
	where employee_id=143
);

==列子查询==
使用多行操作符
in/not in(查询列表 等于列表中的一个)
any/some(和查询返回的某一个值比较)
all(和所有的比较)

返回location_id是1400或者1700的部门中所有员工的姓名
select last_name
from employees
where department_id in(
	select distinct department_id
	from departments
	where location_id in(1400,1700)
);

查询员工编号最小且工资最高的员工信息
select *
from employees
where(employee_id,salary)=(
	select min(employee_id),max(salary)
	from employees
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

from后面
exists后面

分页查询

应用场景：当要显示的数据一页显示不全，需要分页提交sql请求

语法：
		select 查询列表
		from 表
		【join type join 表2
		on 连接条件
		where 筛选条件
		group by分组字段
		having 分组后的筛选
		order by排序的字段】
		limit offset,size;

		offset是要显示的条目的起始索引（从0开始）
		size是要显示的条目个数
特点：
		1，limit语句在查询语句最后
		2，公式
		要查询的页数 page，每页的条目数 size
			select 查询列表
			from 表
			limit (page-1)*size,size;
			
案例1：查询前五条员工信息

select * from employees limit 0,5;

案例2：查询第11条到第25条

select * from emploees limit 10,15;

案例3：有奖金的员工信息，并且工资较高的前十名显示

select * 
from employees 
where commission_pct if not null
order by salary desc
limit 10;

案例4：查询所有学员邮箱的用户名

select substr(email,1,instr(email,'@')-1)
from stuinfo;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

联合查询

union 联合 合并：将多个查询结果合并为一个结果
语法：
	查询语句
	union
	查询语句
	union
	...

特点：
1.要求查询的列数（字段数）一样
2.使用union会自动去重，使用union all会包含重复
应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但是查询的信息（字段数）一致时就可以使用
1
2
3
4
5
6
7
8
9
10
11
12