Mysql查询语法总结

普通查询

1、查询表结构

desc 表名;

2、查询工资大于10000小于20000的员工

第一种写法：select * from 表名 where salary > 10000 and salary < 20000;
第二种写法：select * from 表名 where salary between 10000 and 20000;
between and对比>=和<=的好处就是可以简洁sql语句
关键字：>或<或>=或<=或<>（不等于）

3、拼接函数，将某几列拼接在一列中

select concat(列名, '，', 列名, '，'列名, '，', 列名) from 表名;

效果图：

如果某列为空，则这列的所有数据都显示为空，因为字符串之间的连接运算只要有一个为null，那么整个结果都为null，可以用下面这个sql

如果某列为空：select concat(列名, '，', ifnull(可能为空的列名, 0), '，'列名, '，', 列名) from 表名;

4、查询结果去重

select distinct 列名 from 表名;

5、模糊查询

查询员工名字中带有“a”的名字：select * from 表名 where name like '%a%';
查询第三个字符为“n”或者第五个字符为“x”的员工：select * from 表名 where name like '__n%' or name like '_____l%';
前面的位置用下划线代替，第几个就用几个下划线，下划线为通配符
查询员工信息表中员工名字第二个字符为“_”的员工：select * from 表名 where name like '_\_%';
\ 为转译字符

6、查询员工编号为“001”、“002”、“003”中任意一个的员工

select * from 表名 where number in ('001', '002', '003');

7、查询某一列值为空的所有数据

select * from 表名 where 列名 is not null;

函数查询

1、函数的用法

select 函数名(实参) from 表名;

2、获取参数的字节数：length(‘参数’)

select length(列名) from 表名;

3、字符串连接函数：concat(str1, str2)

select concat(str1, '连接符', str2, '连接符', str3) as 别名 from 表名;
效果看上图

4、小写字母变大写：upper(字段名)；大写字母变小写：lower(字段名)

select upper('abc');
select lower('ABCC');
select upper(列名) 别名, lower(列名) 别名 from 表名;

5、截取字符串：substr(‘目标字符串’, 索引)

截取name列从四个字符开始到最后：select substr(name, 4) from 表名;	//注：mysql中索引值是从1开始的，java中的索引值是从0开始的
截取name列从二个字符开始，截取到第5个：select substr(name, 2, 3) from 表名;	注：第一个数是开始截取的索引值，第二个数为截取的长度

6、返回子字符串在源字符串中的起始索引：instr(‘原字符串’, ‘子字符串’)

select instr('原字符串', '子字符串') from 表名;		//注：找不到返回0

7、去掉字符串前后的空格，中间空格去不掉：trim(‘前后有空格的字符串’)

select trim(列名) from 表名;
扩展用法：
select trim ('a' from 'aaaa库里投篮666aaa啦啦啦啦aaa');		//返回结果：库里投篮666aaa啦啦啦啦；去掉字符串的前后特定的字符

8、将填充字符填充到目标字符的左边：ipad(‘目标字符串’, 10, ‘填充字符’)，rpad相反，填充右边

select ipad('哈哈哈哈', 10, 'a');		//输出：aaaaaa哈哈哈哈； 中间的数字为填充后的总长度，数字应该比字符串长度大

9、替换：replace(‘目标字符串’,‘被替换子串’, ‘用于替换的新串’)

select replace('LeBron james curry jordan', 'curry', 'abc');

数学函数

1、四舍五入：round(小数)

select round(1.45);		//不管正负数，先取括号里数的绝对值，然后round四舍五入，最后加上正负号
select round(1.5678990, 2);		//表示小数点后两位，第二个参数表示取小数点后几位

2、向上取整：ceil(小数)，向下取整：floor(小数)

select ceil(1.8989);
select floor(1.7899);

3、截断：truncate(小数, 3)

select truncate(1.678, 1);		//结果保留小数1位，	输出1.6

4、取余：mod(a,b)

select mod(10,3);		//结果为：1
select mod(-10, 3);		//结果为：-1
select mod(10, -3);		//结果为：1
select mod(-10, -3);		//结果为：-1		 符号取决于被除数的符号	内部计算过程为a-(a/b*b)

日期函数

1、返回当前日期时间：now()

select now();		//返回2021-03-15 22:35:54

2、返回当前系统日期不带时分秒：curdate()

select curdate();		//返回2021-03-15	没有时分秒

3、返回当前系统日期不带年月日：curtime()

select curtime();		//返回22:37:56	没有年月日

4、单独获取年/月/日：

select year(now());		//返回当前年份：2021
select year('1998-01-09');		//返回：1998
select month(now());		//返回当前月：03
select monthname(now());		//获取当前月名.. 
day		hour	nimute	second	和上面类同

5、将日期格式的字符串转换成指定格式的日期：str_to_date(‘2021-01-02’, ‘%y-%m-%d’);

select str_to_date('2021-01-02', '%y/%m/%d');		//返回：21/03/15

6、将日期转换为字符串或者理解为格式化日期：date_format(‘2021-01-02’, ‘%y-%m-%d’);

select str_to_date(now(), '%y/%m/%d');		//返回：21/03/15

序号	格式符	功能
1	%Y	四位的年份
2	%y	两位的年份
3	%m	月份（01,02,…,12）
4	%c	月份（1,2,3,…,12）
5	%d	日（01,02,…）
6	%H	小时（24小时制）
7	%h	小时（12小时制）
8	%i	分钟（00,01,02,…,59）
9	%s	秒（00,01,02,…,59）

7、查询入职时间为2021-03-06的员工

select str_to_date(now(), '%y/%m/%d');		//返回：21/03/15

流程控制函数

1、if函数：

select if(10>1, '大', '小');		//条件满足返回第一个，不满足返回第二个

2、case函数：

语法：
select 
CASE	表达式
WHEN 条件1 THEN 结果1 
WHEN 条件2 THEN 结果2  
END AS 别名
from 表名
案例：员工表中，如果部门为01，显示工资为1.1倍；如果部门为02，显示工资为1.5倍。
select salary as 原工资, dept, 
		CASE dept
		WHEN 01 THEN salary*1.1
		WHEN 02 THEN salary*1.5
		ELSE salary
		END AS 新工资
		from 表名;
案例：员工表中，如果工资大于1000返回高工资，如果小于1000返回低工资
select 
		CASE salary > 1000
		WHEN TRUE THEN ‘高工资’
		WHEN FALSE THEN '低工资'
		END
		AS 工资
		from 表名;

3、多重if：

语法：
select 
CASE	
WHEN 条件1 THEN 要显示的值1(或语句1)
WHEN 条件2 THEN 要显示的值2(或语句2)
......
else 前面条件都不符合时显示
end as 别名
from 表名;

案例：
select salary,
	case
	when salary > 2000 then 'A级工资'
	when salary > 1000 then 'B级工资'
	else 'C级工资'
	end as 工资等级
	from 表名;

分组函数

分组函数和上述函数的不同之处在于前面的函数是对内容本身的处理，而分组函数主要的功能是统计
分组函数会忽略null

1、求和：sum(列名)

select sum(列名) from 表名;	

2、平均值：avg(列名)

select avg(列名) from 表名;	

3、获取最大值：max(列名)

select max(列名) from 表名;	

4、获取最小值：min(列名)

select min(列名) from 表名;	

5、统计行数：count(*)

统计这个表有多少行：select count(*) from 表名;	
统计某个列中不为空的数据有多少行：select count(列名) from 表名;	
统计某个列中不为空且去除重复后有多少行：select count(distinct 列名) from 表名;

排序查询

1、排序查询语法

select
			列
from 	
			表名
where 
			条件
order by
			要排序的字段
asc | desc ;
排序查询：asc升序，desc降序，按多个字段排序后面要跟上相应的asc或者desc，如果没写，默认asc（升序）。

2、排序查询实例

查询所有员工信息，要求工资从高到低排列，工资相同的按照id降序：
select * from 表名 order by salary desc, id desc;

查询部门编号大于90的员工信息，按照入职时间先后排序：
select * from 表名 where number > 90 order by ruzhidate asc;

按年薪高低显示员工信息（月工资*12*（1+奖金率））：
select *, (salary*12+(salary*12*ifnull(jiangjinlv, 0)) year_salary from 表名 order by year_salary desc;

按姓名长度显示员工信息：
select * from 表名 order by length(name) desc;
显示名字长度：
select *, length(name) `length` from 表名 order by length(name) desc;
如果别名是关键字，可以用`（着重号）包裹

分组查询

1、分组查询语法

select
			列（这个列要求必须只能是group by后面的字段名）,  分组函数()
from 	
			表名
where 
			条件（针对表中的所有记录的筛选条件）
group by
			分组字段列表
having（只能配合group by使用）
			与分组有关的筛选条件（针对分组后的每组内的记录）
order by
			排序;
分组查询：group by关键字实现分组，group by放在where条件语句之后，order by放置在group by的后面，如果还需要有having关键字，having放在group by的后面，order by的前面；总体顺序先后为：where 条件，group by 分组语句，having关键字，order by 排序语句，where条件是针对所有记录的，having 局限的针对每一组记录的。

2、分组查询实例

查询每个工种的最高工资：
select max(salary), job from 表名 group by job;

查询每个地方的部门个数：
select count(*), dept from 表名 group by dept;

查询每个部门每个工种的员工的平均工资：
select avg(salary), dept, job from 表名 group by dept, job;

查询员工邮箱里包含"a"字母的，每个部门的平均工资：
select avg(salary) as 每个部门的平均工资, dept from 表名 
	where email like '%a%' 
	group by dept;

查询有奖金的每个领导手下的员工的最高工资：
select max(salary) 员工的最高工资, lingdao from 表名
	where jiangjin is not null 
	group by lingdao;

查询部门的员工个数>5的所有部门的员工数：
select count(*) 部门员工个数, dept from 表名
	group by dept having 部门员工个数>5;

查询没有奖金的每个领导手下员工的最高工资，且最高工资大于12000：
select max(salary) 最高工资, lingdao from 表名
	where jiangjin is null
	group by lingdao having 最高工资>12000;

查询没有奖金的每个领导手下员工的最高工资，且最高工资大于5000，并对结果按工资从高到低排序：
select max(salary) 最高工资, lingdao from 表名
	where jiangjin is null
	group by lingdao having 最高工资>5000
	order by 最高工资 desc;

连接查询

两个表组合在一起的错误查询：select name, age from ta, tb;
将发现这种组合查询会出现笛卡尔积的错误情况，a表有m行，b表有n行，结果=m*n行，这种错误的原因是没有连接条件
正确写法：select name, age from ta, tb where ta.id = tb.ta_id（等值连接）;

1、连接查询的分类

按照功能的分类：
			内连接：
						等值连接（重点）
						非等值连接（重点）
						自连接（重点）  --内连接为92年发布的SQL语法，不支持外连接
			外连接：
						左外连接（重点）
						右外连接
						全外连接（mysql数据库不支持全外连接）
			交叉连接

2、等值连接用法实例（内连接）

简单说明就是表之间用=连接
员工表：person	部门表：dept	城市表：location	关联关系：员工表中有部门id（deptid）

查询员工名和对应的部门名：
select p.personname, d.deptname from person p, dept d
	where p.deptid = d.id;

查询有奖金的员工以及所属部门名：
select p.personname, d.deptname from person p, dept d
	where p.deptid = d.id	and p.jiangjin is not null;

查询每个城市的部门个数，城市名：
select count(*) cityCount, l.cityname from location l, dept d
	where d.locationid = l.id
	group by l.cityname;

查询有奖金的员工每个部门的部门名和部门的领导编号和该部门的最低工资：
select d,deptname 部门名,d.lingdaonumber 部门的领导编号, min(p.salary)  部门最低工资 
	from dept d, person p
	where d.id = p.deptid and p.jiangjin is not null 
	group by d.deptname, d.lingdaonumber ;(因为分组函数语法为group by后面的字段必须于select后的字段名字相同，但实际后面的d.lingdaonumber可加可不加，因为一个部门名称对应一个部门领导，)	

查询每个工种的工种名和员工个数，并且按照员工个数排序升序：
select j.jobname, count(*) 员工个数 from job j, person p 
	where j.id = p.jobid
	group by j.jobname 
	order by 员工个数 asc;

查询员工名，部门名和所在城市名：
select p,personname, d.deptname, l.cityname from person p, dept d, location l 
	where e.deptid = d.id and d.locationid = l.id;

3、非等值连接用法实例（内连接）

等值连接中的等号等于非等号情况；
员工表：person	部门表：dept	城市表：location	工资级别表：job_grades 工作表：job  关联关系：员工表中有部门id（deptid）

查询员工的工资和工资级别：
select p.salary, g.grade
	from person p, job j
	where p.salary bewteen j.minSalary and j.maxSalary;

4、自连接用法实例（内连接）

自连接：相当于等值连接，只不过是自己连接自己，不像等值连接是两个不同的表之间连接

员工表：person	员工id：personId	员工名字：name		上司id：managerId

查询员工和他的上司的名字
select p.name, m.name 
	from person p, person m
	where p.managerId = m.personId;

5、99年SQL的连接语法

上面说到，99版SQL语法格式区别于92版的是连接不在是用“=”了，他的语法格式：
	select 查询列表
		from 表1 别名
		[连接类型] join 表2 别名
			on 连接条件
			where 数据筛选条件
		
【连接类型】的关键字
内连接：inner（还可以省略），如果只有一个join，默认为内连接
外连接：
		左外连接：left [outer]			--outer可以省略	
		右外连接：right [outer]			--outer可以省略	
		全外连接：full [outer]			--outer可以省略	
交叉连接：cross [outer]			--outer可以省略	

6、等值连接用法实例（99年SQL语法的内连接）

查询名字中包含“旭”的员工和其对象的部门名
select p.name, d.deptname 
	from person p
	inner join dept d		--inner可以省略
	on p.deptid = d.id
	where p.name like '%旭%';

查询所在部门个数大于3的城市名和部门个数
select city, count(*)
	from dept d
	inner join location l		--inner可以省略
	on d.localtionId = l.id
	group by city
	having count(*) > 3;

7、非等值连接用法实例（99年SQL语法的内连接）

等值连接中的等号等于非等号情况；
员工表：person	部门表：dept	城市表：location	工资级别表：job_grades 工作表：job  关联关系：员工表中有部门id（deptid）

查询员工的工资和工资级别：
select p.salary, g.grade
	from person p
	inner join job j
	on p.salary bewteen j.minSalary and j.maxSalary;

8、自连接用法实例（99年SQL语法的内连接）

员工表：person	员工id：personId	员工名字：name		上司id：managerId

查询员工和他的上司的名字
select p.name, m.name 
	from person p
	inner join  person m
	on p.managerId = m.personId;

9、外连接知识点

场景：在表A和表B中，查询没有对应年龄数据的学生姓名和年龄；例：
select a.name, b.age
		from ta a 
		inner join tb b
		on a.taId = b.tbId
		where b.id is null;
查询结果没有数据，所以内连接是解决不了这种问题的，所以就会用到外连接

外连接和内连接的区别：
内连接：当从表没有记录的时候，主、从表的记录都会丢掉！
外连接：当从表没有记录的时候，会保留主表的记录，对应从表显示为null
1、外连接查询结果为主表中的所有记录
			外表有对应数据，结果记录上显示对应数据
			外表中没有对应数据，结果记录上填null
2、左外连接：left join左边的是主表，右外连接右边的是主表
3、左外连接和右外连接上互通的所以掌握一个就好	

10、外连接用法实例

把没有员工的部门找出
select deptname from dept d
	left join person p 
	on d.deptid = p.deptid
	where p.id is null;

11、全外连接

在内连接的基础上，还包含两个表中不符合条件的数据行，并在其中的左表和右表列填写null

全外连接MySQL不支持，不举例
语法：
select  查询列表 from 表名1 别名1
	full join 表名2 别名2
	on 连接条件;

12、交叉连接

两个记录做笛卡尔积！没什么好说的，基本上没用过！！
语法：
select 查询列表 from 表名1
	cross join 表名2;	

子查询

子查询：出现在其他语句中的select语句，被包裹的select语句就是子查询或者内查询 
包裹子查询的外部的查询语句：称主查询语句
比如：
select last_name from employes
	where dept_id in (
	select deptid from dept where location_id = 1700			--子查询
);

1、子查询的分类

通过位置来分：
	select 后面：仅仅支持标量子查询
	from 后面：支持表子查询
	where 或 having 后面：支持标量子查询（重要）和列子查询和行子查询（不常用）
	exists 后面（相关查询）：支持表子查询
按结果集的行列不同分类：
	标量子查询（结果集只有一行一列）
	列子查询（结果集一列多行）
	行子查询（结果集一行多列）
	表子查询（结果集多行多列）

2、子查询的特点

1、子查询一般放在小括号内
2、子查询一般放在条件的右侧（条件的最后面）
3、标量子查询一般搭配着单行操作符来使用（> < >= <= <> =）
4、列子查询一般搭配着多行操作符来使用，如in、any/some、all
5、子查询的执行顺序优先于主查询
6、主查询可以包含多个子查询
7、子查询可以用分组函数

3、where后标量子查询的案例

查询出工资比james这个人高的员工信息
select * from employees 			--员工信息表
	where salary > (
		select salary from employees where name = 'james'
	);

查询最低工资大于50号部门的最低工资的部门id和其最低工资
select dept_id, min(salary) from employees 
	group by dept_id
	having salary > (
			select min(salary) from employees where dept_id = 50
	)

4、where后面的列子查询案例

多行操作符：
	in / not in ：等于列表中的任意一个
		a in (1,2,3); 意思等同于a = 1 or a = 2 or = a = 3;
	any / some ：和子查询返回的某一个值比较
		a > any(1,2,3)：a大于any集合中的最小值即可;意思等同于a > min(1, 2, 3)
	all ：和子查询返回的所有值比较
		a > all(1, 2, 3)：a比列表中的所有值都大; 可以替换为a > max(1, 2, 3)

返回location_id是1400或1700的部门中的所有员工的名字：
select e.name from employees e
	where e.dept_id in (
		select location_id from dept d
			where d.location_id = 1400 or d.location_id = 1700
	)

5、select后面的子查询案例（用的较少，一般用连接查询可以解决）

查询每个部门的部门信息和对应的员工个数（用连接查询）：
select d.*, count(employee_id)				--这里的count函数里不能用（*）号
	from dept d 
	left join employees e 
	on d.deptid = e.deptid
	group by d.deptid;
查询每个部门的部门信息和对应的员工个数（不用连接查询）：
select d.*,(select count(*) from employees e where e.deptid = d.deptid) 
	from dept d;

查询员工号等于120的部门名（不用连接查询）：
select (select d.dept_name from dept d where d.dept_id = e.dept_id) 
	from employees e where e.employee_id = 120;

6、from后面的子查询案例

部门表：dept			工资等级表：job_grade
查询每个部门的平均工资等级：
select avg_sal_res.avg_sal, g.grade_level 
	from (select avg(salary) avg_sal from dept d group by d.dept_id) avg_sal_res
	inner join job_grade g
	on avg_sal_res.avg_sal between g.lowest_sal and g.highest_sal ;

7、exists后面的子查询案例

exists的作用是：判断子查询有没有结果的存在
语法：select exists (完整的子查询);		--子查询有结果返回1，没有结果返回0;

查询有员工的部门名：
select dept_name from dept d 
	where exists 
			(select * from employees e where d.dept_id = e.dept_id);

分页查询

分页查询：数据记录条数过多的时候，需要分页来显示;
语法：select 查询字段 from 表名 where ...等等前面学过的所有语法
										limit offset(开始记录索引，是从0开始的), size(要取出的条数);

分页查询的特点：
	1、limit语句在位置上是要放在比order by语句的还后面，在sql执行的过程中，limit也是最后去执行的
	2、通用的分页查询写法（page:总页数，size:每页显示的记录条数）
			select 查询列名 from 表名 limit (page-1)*size, size;

查询前5条员工数据：
	select * from employees limit 0, 5;		--如果是从0开始的，则0可以省略
查询第11条到第25条：
	select * from employees limit 10, 15;

查询有奖金且工资最高的前10名的员工信息：
select * from employees 
		where jiangjin is not null order by salary desc limit 10;

联合查询

联合查询：关键字union，作用就是将多条查询语句的结果合并成一个结果集
union应用场景：比如有两张表，老师表和学生表，两个表之间没有直接的关联关系，但是这两个表有一些共通性，		比如都有姓名，都有性别，都有出生日期，但是现在我们想要查询学校所有男老师和男学生的信息（学生和老师的编号、姓名、性别、年龄），这个时候就用到了union；
语句：
	select number, name, sex, age from teachers where sex = '男'
	union
	select number, name, sex, age from students where sex = '男'

联合查询的特点：
	1、联合查询的两个子查询的查询字段个数要一致
	2、联合查询的两个子查询的查询列表顺序要在内容上保持一致
	3、使用union联合默认会自动去重，如果不想去重可以将union改为union all

案例：查询部门编号大于100或名字中含有a字符的员工信息
不用联合查询语法：
	select * from employees where dept_id >100 or name like '%a%';
用联合查询的语法：
	select * from employees where dept_id > 100
	union
	select * from employees where name like '%a%';