MySQL复习笔记（二）

最新推荐文章于 2023-07-24 08:38:38 发布

原创最新推荐文章于 2023-07-24 08:38:38 发布 · 326 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#public

Notes 同时被 2 个专栏收录

5 篇文章

订阅专栏

MySQL

3 篇文章

订阅专栏

博客围绕MySQL查询语句（DQL）展开，介绍了单表查询、字段控制查询、排序、聚合函数、分组函数、分页查询等内容，还提及MySQL的内置函数，如数学、字符串、日期和时间等函数，此外包括查询结果集的集合运算、连接查询、子查询及case when语句等。

5、查询语句★★★★★★（DQL）

以下是在本次复习中用到的几张表，如图所示，表数据sql文件点我查看。

（1）、单表查询语句及案例

1、基本查询(最简单的查询语句)
select ename from emp;
select ename,sal,comm from emp;
select * from emp;

2、条件查询
select * from emp where sal>20000;
/**查询工资高于20000的员工信息**/


select * from emp where sal>=10000 and sal<=30000;
select * from emp where sal between 10000 and 30000;
/**查询工资在1万到3万之间的员工信息**/


select * from emp where job='销售员' or job='文员';
select * from emp where job in('销售员','文员');
/**查询工作是销售员或文员的**/


select * from emp where hiredate>'2007-01-01' and comm is not null;
/**查询入职日期晚于2007年1月1日的，并且奖金不是null**/

3、模糊查询
select * from emp where ename like '___';
/**占位符查询，查询员工名字是三个字的**/


select * from emp where job like '%员%';
/**查询职位里带有‘员’字的**/

（2）、字段控制查询（select后面可以是字段名、运算，还可以是常量）

1、查询emp表中员工的工作有哪几种？
select distinct job from emp;
/**distinct关键字用来去重复查询**/

2、去除null，null和其他数据相加还是null
select ename,sal,comm,(sal+comm),'Test' from emp;
/**查询每个员工的名字、月薪、奖金、还有月薪奖金和**/


select ename,sal,comm,(sal+ifnull(comm,0)),'TEST' from emp;
/**查询月薪奖金和时用了判空函数ifnull(非空时的字段名，为空时需替换为的值)**/

3、给表或列起别名 as 关键字
select ename,sal,comm,(sal+ifnull(comm,0)) as '月薪总和','TEST' as '闹着玩的' from emp;
/**给字段起别名，也可以查询不在表中的字段，输出时会连续输出这个字段的值**/


select e.ename,e.sal,e.comm,(sal+ifnull(comm,0)) as '月薪总和','TEST' as '闹着玩的' from emp e;
/**给表emp起别名为e**/

（3）、查询时的排序

1、按照工资升序排序，关键字：...order by fieldname asc;(asc-->Ascending,升序)
select * from emp order by sal asc;

2、按照入职日期降序排序，关键字：...order by fieldname desc;(desc-->Descending,降序)
select * from emp order by hiredate desc;

3、按照入职日期的升序排序，若入职日期一样，则按工资的降序排
select * from emp order by hiredate asc,sal desc;

4、按照工资高低的降序排序，若工资一样，则按入职日期升序排
select * from emp order by sal desc,hiredate asc;

（4）、聚合函数查询，对某列或多个列的数据进行查，常见的聚合函数有 sum avg max min count

1、查询所有员工的工资和
select sum(sal) from emp;

2、查询所有员工的奖金和，聚合函数不计算null
select sum(comm) from emp;

3、查询所有员工的工资和奖金的平均值
select avg(comm) as '奖金平均值',avg(sal) as '工资平均值' from emp;

4、查询奖金总和的给emp表中所有人的平均值
select sum(comm)/count(empno) from emp;

5、查询表中共有多少条记录(若有不为空的唯一主键，建议使用count(主键名))
select count(*) as '总记录数',count(empno) as '主键数量'from emp;

（5）、分组函数，分组函数一般与聚合函数一起使用

1、查询每个部门有多少人数
select job,count(empno) from emp group by job;

2、查询每个部门有多少人，按部门号分组
select deptno,count(empno) from emp group by deptno;

3、查询emp表中工资最高的部门，按照部门号分组
select deptno,max(sal) from emp group by deptno;

4、查询每个部门及部门的工资和，只查询工资和大于85000的,分组聚合后对整体进行筛选
select deptno,sum(sal) from emp group by deptno having sum(sal)>85000;
/**先分组再筛，此处用到having子句，having是对已经分好组并且计算好的数据进行筛选**/


5、查询每个部门及部门里工资大于15000的人数
select deptno,count(ename) from emp where sal>15000 group by deptno;
/**分组加条件，分组前，对参与分组的数据，进行筛选，然后再分组计算，用where子句,即先筛选再分组**/

（6）、分页查询，查询结果集的数据下标是从0开始的，关键字： limit(查询开始的索引，要查几条)，公式：查询第page页，每页显示pagesize条记录，则为：limit((page-1)*pagesize,pagezize);

1、按照工资降序排序，查询第一页，每页显示4条记录
select * from emp order by sal desc limit 0,4;

（7）、单表查询的模板sql

select 要查的字段 from 表
where 条件
group by 按照分组的字段
having 筛选组的条件
order by 按照排序的字段 desc/asc
limit((第几页-1)*每页几条记录,每页几条记录);

（8）、MySQL提供的一些内置函数，这些内部函数可以帮助我们更加方便的处理表中的数据。MySQL函数包括数学函数、字符串函数、日期和时间函数、系统信息函数等。SELECT语句及其条件表达式都可以使用这些函数。同时，INSERT 、UPDATE、DELECT语句及其条件表达式也可以使用这些函数。例如，表中的某个数据是负数，现在需要将这个数据显示为正数。这就可以使用绝对值函数。从上面可以知道，MySQL函数可以对表中数据进行相应的处理，以便得到用户希望得到的数据。这些函数可以使MySQL数据库的功能更加强大。下面表中列出了MySQL的内置函数。

a、数学函数，数学函数主要用于处理数字，包括整型、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数、获取随机数的函数等。

函数名	用法	作用
ABS(X)	select ABS(-32);	返回X的绝对值
MOD(N,M)或%	select MOD(15,7); select 15 % 7;	返回N被M除的余数
FLOOR(X)	select FLOOR(1.23); select FLOOR(-1.23);	返回不大于X的最大整数值
CEILING(X)	select CEILING(1.23); select CEILING(-1.23);	返回不小于X的最小整数值
ROUND(X)	select ROUND(1.58); select ROUND(-1.58);	返回参数X的四舍五入的一个整数

b、字符串函数

函数名	用法	作用
ASCII(str)	select ASCII('dx')	返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL。
CONCAT(str1,str2,...)	select CONCAT('My', 'S', 'QL'); select CONCAT('My', NULL, 'QL'); select CONCAT(14.3);	返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
LENGTH(str)	select LENGTH('text');	返回字符串str的长度。

LOCATE(substr,str)	select LOCATE('bar', 'foobarbar'); select LOCATE('xbar', 'foobar');	返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.
INSTR(str,substr)	select INSTR('foobarbar', 'bar'); select INSTR('xbar', 'foobar');	返回子串substr在字符串str中的第一个出现的位置。
LEFT(str,len)	select LEFT('foobarbar', 5);	返回字符串str的最左面len个字符。
RIGHT(str,len)	select RIGHT('foobarbar', 4);	返回字符串str的最右面len个字符。

SUBSTRING(str,pos)	select SUBSTRING('Quadratically',5);	从字符串str的起始位置pos返回一个子串。
TRIM(str)	select TRIM(' bar ');	返回字符串str，所有前缀或后缀被删除了。
LTRIM(str)	select LTRIM(' barbar');	返回删除了其前置空格字符的字符串str。
RTRIM(str)	select RTRIM(‘barbar ’);	返回删除了其拖后空格字符的字符串str。

REPLACE(str,from_str,to_str)	select REPLACE('www.mysql.com', 'w', 'Ww');	返回字符串str，其字符串from_str的所有出现由字符串to_str代替。
REPEAT(str,count)	select REPEAT('MySQL', 3);	返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。
REVERSE(str)	select REVERSE('abc');	返回颠倒字符顺序的字符串str。
INSERT(str,pos,len,newstr)	select INSERT(‘whatareyou', 5, 3, ‘is');	返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。

c、日期和时间函数

函数名	用法	作用
NOW()	Select now();	以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回当前的日期和时间,格式如：2016-01-21 06:48:28
CURDATE()	Select curdate();	返回时间的日期，格式如：2016-01-21
CURTIME()	Select curtime();	返回时间，格式如：06:48:28

DAYOFWEEK(date)	select DAYOFWEEK('1998-02-03');	返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)。
DAYOFMONTH(date)	select DAYOFMONTH('1998-02-03');	返回date的月份中的日期，在1到31范围内。
DAYOFYEAR(date)	select DAYOFYEAR('1998-02-03');	返回date在一年中的日数, 在1到366范围内。
DAYNAME(date)	select DAYNAME("1998-02-05");	返回date的星期名字。
MONTHNAME(date)	select MONTHNAME("1998-02-05");	返回date的月份名字。

d、系统信息函数

函数名	用法	作用
VERSION()	select version();	返回数据库的版本号。
CONNECTION_ID()	select CONNECTION_ID();	返回服务器的连接数，也就是到现在为止MySQL服务的连接次数；
DATABASE()	select database();	返回当前数据库名。
LAST_INSERT_ID()	select LAST_INSERT_ID();	返回最后生成的AUTO_INCREMENT值。
USER()	select user();	返回用户名。

e、格式化函数

函数名	用法	作用
FORMAT(x,n)	select format(23.11456,4);	将数字x进行格式化，将x保留到小数点后n位。这个过程需要进行四舍五入。
BIN(x)	select bin(9);	返回x的二进制编码；
HEX(x)	select hex(9);	返回x的十六进制编码；
OCT(x)	select otx(9);	返回x的八进制编码；
CONV(x,f1,f2)	select conv(9,10,2);	将x从f1进制数变成f2进制数。

（9）、查询结果集的集合运算

1、并集运算，关键字：union，要求被合并的两个结果：列数、列类型必须要相同
查询 emp表里的两行数据，与dept表里的两行数据合并
select ename,job from emp 
union 
select dname,loc from dept;


2、差集运算,关键字：minus，但是MySQL并不支持差集运算
select ename,job from emp 
minus 
select dname,loc from dept;


3、交集运算，关键字：intersect，但是MySQL并不支持交集运算
select ename,job from emp 
intersect 
select dname,loc from dept;

（10）、连接查询：内连接、外连接、自然连接★★★★★★

1、内连接:将两个表简单的组合在一起，不加条件时，就是两个表里的字段数量的乘积
select * from emp,dept;   -- >这句是MySQL的方言，oracle里不支持
select * from emp e inner join dept d;


2、内连接的sql语句写法，与上面两句功能一样
select * from emp,dept where emp.deptno=dept.deptno;
select * from emp e inner join dept d where e.deptno=d.deptno;


3、查询‘甘宁’所在部门的部门号、部门名称
select e.ename,e.deptno,d.dname from emp e,dept d 
where e.deptno=d.deptno and e.ename='甘宁';


4、查询‘张二’所在的部门号和部门名称
select e.ename,e.deptno,d.dname from
emp e inner join dept d
where e.deptno=d.deptno and e.ename='张二';


5、外连接查询，就是以某个表为主要查询表，分为左连接和右连接
(1)、左连接：左表 left join 右表，左表里的数据必定会查出，右表里没有的数据对应为null
select e.ename,e.deptno,d.dname from 
emp e left join dept d on e.deptno=d.deptno
where e.ename='张二';

(2)、右连接，左表 right join 右表，右表里的数据一定会查出，左表里没有的数据对应为null
select * from emp e 
right join dept d on d.deptno=e.deptno;


6、自然连接，系统自动找到两个表关联的连接字段，然后做内连接形式的查询
select * from emp e
natural join dept d;


/**注意：上述各示例中出项的形如“emp e”，这些语句等同于“emp as e”，是给表起别名，省略关键字的效果，也可以不起别名**/

（11）、连接查询的练习

1、查询公司在各个城市的员工人数
select d.loc,count(e.empno) from dept d left join emp e
on e.deptno=d.deptno group by d.loc;
/**因为地点必须要查出，所以选择dept左连接emp表，这样，dept表中的数据会确保都被查出**/

或者用右连接的方式
select d.loc,count(e.empno) from emp e right join dept d
on e.deptno=d.deptno group by d.loc;


2、查询所有销售员或分析师的名字、月薪、部门号和部门名
select e.ename,e.sal,e.deptno,d.dname from
emp e left join dept d on e.deptno=d.deptno
where e.job in('销售员','分析师');


3、查询公司所有部门号，部门名，及这个部门中工资+奖金和大于2万的人数，并降序排序
select d.deptno,d.dname,count(e.empno) from 
dept d left join emp e on e.deptno=d.deptno
where (ifnull(sal,0)+ifnull(comm,0))>20000 or (d.deptno is not null and e.empno is null)
group by d.deptno
order by count(e.empno) desc;
/**因部门号为40的这个部门里没人，所以需要在where条件判断完工资后再加(d.deptno is not null and e.empno is null)这句，目的是只要这个部门号存在，不为空，就进入分组**/


4、表的自关联查询，查询每个员工的名字、入职日期，直接上司的名字、上司的入职日期
select e.ename,e.hiredate,m.ename,m.hiredate from 
emp e left join emp m on e.mgr=m.empno;
或者用右连接的方式
select e.ename,e.hiredate,m.ename,m.hiredate from 
emp m right join emp e on m.empno=e.mgr;


5、查询员工入职日期早于上司入职日期的员工
select e.ename,e.hiredate,m.ename,m.hiredate from 
emp e left join emp m on e.mgr=m.empno
where e.hiredate<m.hiredate;


6、三表查询，查询员工入职日期早于上司入职日期的员工姓名、入职日期、上司名、上司入职日期、员工部门 
   号和员工所在地
select e.ename,e.hiredate,m.ename,m.hiredate,e.deptno,d.loc from 
emp e left join emp m on e.mgr=m.empno left join dept d on e.deptno=d.deptno
where e.hiredate<m.hiredate;
/**这里是三表联查，若还有其他表参与则继续向后追加连接即可**/

（12）、子查询，一个select语句里的查询条件可能来自于另外一个查询，或者是一个select语句里的查询列们中的某个列或多个列可能来自另外一个查询的结果表中

1、查询工资大于‘关羽’的员工名字和工资
①：select ename,sal from emp where ename='关羽';
②：select ename,sal from emp where sal>(select sal from emp where ename='关羽');


2、查询工资大于20部门任何人工资的人的名字和工资
①：select sal from emp where deptno=20;
②：select ename,sal from emp where sal> all(select sal from emp where deptno=20);
    /**all相当于max**/

   select ename,sal from emp where sal> any(select sal from emp where deptno=20);
    /**顺便补充下any函数的作用， any相当于min**/


3、查询‘增阿牛’的部门号，部门名，以及他的下属名，下属部门名
①：查‘增阿牛’的部门号和部门名
select e.ename,e.empno,e.deptno,d.dname from 
emp e left join dept d on e.deptno=d.deptno where e.ename='增阿牛';
②：select e.ename as '下属名字',d.dname as '下属部门名称',m.ename as '上司名字',m.dname as '上司部门名称' from 
emp e left join dept d on e.deptno=d.deptno 
left join (select e.ename,e.empno,e.deptno,d.dname from 
emp e left join dept d on e.deptno=d.deptno where e.ename='增阿牛') m on m.empno=e.mgr
where m.ename is not null;

（13）、case when then else end 语句，类似于Java中的switch语句

1、本月要给员工加薪，如果工资小于2万，翻5倍；2万<sal<3万的，翻2倍；sal>3万的，不变，请查询员工本月工资和涨幅
select ename,sal,(
	case 
	when sal<20000 then sal*5
	when sal>20000 and sal<30000 then sal*2
	else sal
	end
) as '本月工资' from emp;


2、查询文员的人数和销售员的人数
select job,count(*) from emp group by job having job in('文员','销售员');
/**方法1(两行显示)**/


select sum(
	case 
	when job='文员' then 1
	else 0
	end
) as 文员人数,sum(
	case
	when job='销售员' then 1
	else 0
	end
) as 销售员人数 from emp;
/**方法2(一行显示)：**/