Mysql学习笔记day2 --三天学完mysql所有知识点

标题mysql学习笔记

---

一.练习（第一天学习留下的练习）

1. 查看EMP表中部门号为10的员工的姓名，职位，参加工作时间，工资。

   select
   	ename as 姓名,
   	job 职位,
   	hiredate 参加工作时间,
   	sal 工资
   from
   	emp
   where
   	deptno = 10;
   

2. 计算每个员工的年薪，要求输出员工姓名，年薪。

   select
   	ename 员工姓名,
   	(sal + ifnull(comm,0)) * 12 年薪
   from
   	emp;
   

3. 查询每个员工每个月拿到的总金额（sal为工资，comm为补助）。

   select
   	ename 员工姓名,
   	(sal + ifnull(comm,0)) 每个月拿到的总金额
   from
   	emp;
   

4. 显示职位是主管（manager）的员工的姓名，工资。

   select
   	ename 员工姓名,
   	sal  工资
   from
   	emp
   where
   	job = 'manager';
   

5. 显示第3个字符为大写R的所有员工的姓名及工资。

   select
   	ename 姓名,
   	sal 工资
   from
   	emp
   where
   	ename
   like
   	'__R%';
   

6. 显示职位为销售员（SALESMAN）或主管（MANAGER）的员工的姓名，工资，职位。

   select
   	ename 姓名,
   	sal 工资,
   	job 职位
   from
   	emp
   where
   	job in('SALESMAN','MANAGER');
   

7. 显示所有没有补助的员工的姓名。

   select 
   	ename 员工的姓名
   from
   	emp
   where
   	comm is null;
   

8. 显示有补助的员工的姓名，工资，补助。

   select 
   	ename 员工的姓名,
   	sal,
   	comm
   from
   	emp
   where
   	comm is not null;
   

9. 查询出部门编号为30的所有员工

   select 
   	*
   from
   	emp
   where
   	deptno = 30;
   

10. 所有销售员的姓名、编号和部门编号。

    select 
    	ename,
    	empno,
    	deptno
    from
    	emp
    where
    	job = 'salesman';
    

11. 找出奖金高于工资的员工。

    select 
    	*
    from
    	emp
    where
    	comm > sal;
    

12. 找出奖金高于工资60%的员工。

    select 
    	*
    from
    	emp
    where
    	comm > sal * 0.6;
    

13. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。

    select
    	*
    from
    	emp
    where
    	(deptno = 10
    	and
        job = 'manager')
    or
    	(deptno = 20
        and
        job = 'salesman');
    

14. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于2000的所有员工详细资料。

    select
    	*
    from
    	emp
    where
    	(deptno = 10
    	and
        job = 'manager')
    or
    	(deptno = 20
        and
        job = 'salesman')
    or
    	(job not in ('manager','salesmane')
        and	
        sal >= 2000
        );
    

15. 无奖金或奖金低于1000的员工。

    select 
    	ename 员工的姓名,
    	sal,
    	comm
    from
    	emp
    where
    	comm is null
    or
    	comm < 1000;
    

16. 查询名字由四个字组成的员工。

    select
    	*
    from
    	emp
    where
    	ename 
    like  '____';
    

17. 查询2000年入职的员工。

    select
    	*
    from
    	emp
    where
    	hiredate 
    like
    	'2000%';
    

18. 查询所有员工详细信息，用编号升序排序

    select
    	*
    from
    	emp
    order by
    	empno;
    

19. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排列

    select
    	*
    from
    	emp
    order by
    	sal desc,hiredate asc;
    

20. 查询姓名不包含M，且工资大于1500，或年薪不低于30000的雇员编号、姓名、工资、年薪，按雇员姓名降序排列

    select
    	empno,
    	ename,
    	sal,
    	(sal + ifnull(comm,0)) 年薪
    from
    	emp
    where
    	ename
    not like 
    	'%M%'
    or
    	(sal + ifnull(comm,0)) > 30000
    order by
    	ename desc;
    

二.排序

排序的关键字：
	order by 
语法：
	select
		...
	from
		...
	where
		...
	order by
		排列1 asc | desc,排列2 asc | desc
asc：默认的排序规则，升序
desc：降序规则。


执行顺序：
1.from
2.where
3.select
4.order by

例子：查询所有员工的信息，按照工资由低到高进行排序

select
	*
from
	emp
order by
	sal;

例子：查询部门编号为10的雇员信息，按照工资由高到低排序。

select
	*
from
	emp
where
	deptno = 10
order by
	sal desc;

例子：查询部门编号为10的员工的姓名、年薪，按照年薪由高到低排序。

select
	ename 员工的姓,
	(sal + ifnull(comm,0)) * 12 年薪
from
	emp
where
	deptno = 10;
order by
	年薪 desc;

三.分页

什么是分页，就是查询时候数据量太大，一次性返回所有查询结果既耗费网络资源、又降低了查询效率，用户也不可能一下子看完成千上万条数据。所以分页的技术就应运而生。分页可以只显示指定数量的数据。

分页在我们的生活中随处可见，如下图所示的电商网站：

关键字：limit
语法：
	语法：
	select
		...
	from
		...
	where
		...
	order by
		排列1 asc | desc,排列2 asc | desc
	limit 
		数据下标,条目数;
		
需求：
	假设每页显示3条数据
	
	每页显示的条目数：int pageSize = 3
	
	总页数：int pageCount =  Math.ceil(总数据 / pageSize)。	14 / 3 = 5
			如果不能整除，则向上取整。
	
	当前页：int currentPage = 1;
	
	求第五页数据: currentPage = 5;
	
		数据下标 = (currentPage - 1) * pageSize;
		条目数 = pageSize;
		
	求第四页第三条数据:
		select
            *
        from
            emp
        order by
            sal
        limit
        	9,3;

总结公式： 
LIMIT (pageNo - 1) * pageSize, pageSize;

四.多表查询

1.等值连接

结果集的数据来自于多张表。
同时查询多张表，使用自连接来将表与表之间联系起来。
关系型：
	就是将一张表的某一列作为另一张的引用。
	让表与表之间产生联系，这种方式就叫做关系型。
笛卡尔积：
	表示为X × Y
	在数据库中的表示为A表的一行 * B表的所有。
	所以要避免这种行为。
select
	*
from
	emp e,dept d;


使用等值连接，去除笛卡尔积：
select
	*
from
	emp e,dept d
where
	e.deptno = d.deptno;

例子：查询员工的编号，姓名，工资，所在部门名称及位置。

select
	e.empno 编号,
	e.ename 姓名,
	e.sal 工资,
	d.dname 所在部门名称,
	d.loc 位置
from
	emp e,dept d
where
	e.deptno = d.deptno;

例子：查询雇员的姓名，工资，入职时间，所在部门编号，部门名称

select
	e.ename 姓名,
	e.sal 工资,
	e.hiredate 入职时间,
	d.deptno 所在部门名称,
	d.dname 部门名称
from
	emp e,dept d
where
	e.deptno = d.deptno;

例子：查询员工姓名，员工工资，领导姓名，领导工资

---分析：
	领导也是员工，领导的编号[mgr]来自于员工的编号[empno]
	所以，将员工的领导编号 等于 领导的员工编号。
	领导的编号来自于emp表
	员工的而编号也来自于emp表。
	这种方式成为自连接.

---查询
select
	e.ename 员工姓名,
	e.sal 员工工资,
	m.ename 领导姓名,
	m.sal 领导工资
from
	emp e,emp m
where
	e.mgr = m.empno
order by
	e.sal;

例子：查询员工的姓名，工资，部门的名称，工资所在等级。

select
	e.ename 员工的姓名,
	e.sal 工资,
	d.dname 部门的名称,
	s.grade 工资所在等级
from
	emp e,dept d,salgrade s
where
	e.deptno = d.deptno
and
	e.sal
between 
	s.losal and s.hisal;

例子：查询员工姓名，工资，部门名称，工资等级，领导的姓名，领导的工资，领导工资等级。

select
	e.ename 员工的姓名,
	e.sal 工资,
	d.dname 部门的名称,
	s.grade 工资所在等级,
	m.ename 领导的姓名,
	m.sal 领导的工资,
	sm.grade 领导工资等级
from
	emp e,dept d,salgrade s,salgrade sm,emp m
where
	e.deptno = d.deptno
and
	e.sal
between 
	s.losal and s.hisal
and
	e.mgr = m.empno
and
	m.sal
between
	sm.losal and sm.hisal;

2.SQL99标准

1.什么是SQL99标准？
	由1999年制定的SQL语法的一个标准规范。
2.SQL99语法分类
	a)内连接
	b)外链接
		1.左外连接
		2.右外连接

1.内连接

只有两张表的连接列的值相等。
才会将数据放入结果集中。
与等值连接一样。
语法：
	表A inner join 表B  on 连接条件。

inner 可以省略

2.外链接——左外连接

始终以左表为主表，不管连接列是否等值。
都会显示左边表的所有的数据。
语法：
	表A left join 表B  on 连接条件。

3.外链接——右外连接

始终以右表为主表，不管连接列是否等值。
都会显示右边表的所有的数据。
语法：
	表A right join 表B  on 连接条件。

案例：

create table t_a(
	id int,
    name varchar(20)
);

insert into t_a values(1,'弗拉基米尔'),(2,'菲奥娜'),(3,'基兰');


create table t_b(
	id int,
    job varchar(20),
    aid int
);

insert into t_b values(1,'中单',1),(2,'打野',4),(3,'上单',5);

1.使用内连接查询

select
	a.name,
	b.job
from
	t_a a inner join t_b b
on
	a.id = b.aid;

结果：
	弗拉基米尔	中单
	菲奥娜		  AD

2.左外连接

select
	a.name,
	b.job
from
	t_a a left join t_b b
on
	a.id = b.aid;
	
结果：
	弗拉基米尔	中单
	菲奥娜		  AD
	基兰		   null

2.右外连接

select
	a.name,
	b.job
from
	t_a a right join t_b b
on
	a.id = b.aid;

结果：
	弗拉基米尔	中单
	null		打野
	null		上单
	菲奥娜		  AD			

例子：查询员工的编号，姓名，工资，部门名称

select
	e.empno,
	e.ename,
	e.sal,
	d.dname
from
	emp e join dept d
on
	e.deptno = d.deptno;

例子：查询工资大于1500的员工的姓名，工资，部门名称，领导姓名

select
	e.ename 员工的姓名,
	e.sal 工资,
	d.dname 部门名称,
	m.ename 领导姓名
from
	emp e join dept d
on
	e.deptno = d.deptno
join 
	emp m
on
	e.mgr = m.empno
where
	e.sal > 1500;

五.聚合函数和分组统计

1.聚合函数

1.什么是聚合函数
	聚合函数就将所有的数据进行统计。
2.Mysql提供的聚合函数
	a)avg()	平均数
		例子：求公司的平均公司是多少？
		select avg(sal + ifnull(comm,0)) 平均工资 from emp;
	b)max()	最大值
		例子：求公司最高工资的员工的工资是多少？
		select max(sal + ifnull(comm,0)) 最高工资 from emp;
	c)min()	最小值
		例子：求公司最低工资的员工的工资是多少？
		select min(sal + ifnull(comm,0)) 最低工资 from emp;
	d)sum()	求和
		例子：求公司每月工资成本是多少？
		select sum(sal + ifnull(comm,0)) 工资成本 from emp;
	e)count() 总数
		例子：查询有奖金的员工的人数是多少？
		select count(empno) from emp where comm is not null;

2.分组统计

1.什么是分组？
	分组的含义就是将数据通过某个字段进行分组化。
2.语法：
	select
	...
	from
	....
	where
	....
	group by 分组列1,分组列2...

示例：

create table test(
	id int,
    name varchar(20),
    num int
);

insert into test values(1,'aa',2);
insert into test values(2,'aa',3);
insert into test values(3,'bb',4);
insert into test values(4,'bb',5);
insert into test values(5,'cc',6);
insert into test values(6,'dd',7);
insert into test values(7,'ee',7);
insert into test values(8,'bb',5);
insert into test values(9,'cc',6);

执行如下sql：
    select
        name
    from
        test
    group by
        name;
结果：
	aa
	bb
	cc
	dd
	ee
	
执行如下sql：
	select
        id,
        name,
        num
    from
        test
    group by
        name;
结果：
    +------+------+------+
    | id   | name | num  |
    +------+------+------+
    |    1 | aa   |    2 |
    |    3 | bb   |    4 |
    |    5 | cc   |    6 |
    |    6 | dd   |    7 |
    |    7 | ee   |    7 |
    
    
执行如下sql:
	select
        count(id),
        name,
        sum(num)
    from
        test
    group by
        name;	

结果：
+-----------+------+----------+
| count(id) | name | sum(num) |
+-----------+------+----------+
|         2 | aa   |        5 |
|         3 | bb   |       14 |
|         2 | cc   |       12 |
|         1 | dd   |        7 |
|         1 | ee   |        7 |
+-----------+------+----------+	

多列分组：

执行如下sql：
 	select
        name
    from
        test
    group by
        name,num;
结果：
    +------+
    | name |
    +------+
    | aa   |
    | aa   |
    | bb   |
    | bb   |
    | cc   |
    | dd   |
    | ee   |
    +------+

执行如下sql:
	select
        count(id),
        name,
        sum(num)
    from
        test
    group by
        name,num;

结果：
	1	aa	2
	1	aa	3
	1	bb	4
	2	bb	10
	2	cc	12
	1	dd	7
	1	ee	7

例子：查询每个部门的平均工资是多少？

select
	deptno,
	round(avg(sal),2) 平均工资
from
	emp
group by
	deptno;

例子：查询部门的名称及每个部门的员工数量

select
	d.deptno 部门编号,
	d.dname 部门名称,
	count(e.empno) 员工数量
from
	emp e right join dept d
on
	e.deptno = d.deptno
group by
	d.deptno;

3.having

1.什么是having关键字？
	having通常与group by联合使用，用来过滤由group by语句返回的记录。
	having语句的存在弥补了where关键字不能与聚合函数联合使用的不足，
	通俗的说，where语句过滤行，having语句过滤组。
	
执行顺序：
1.from
2.where
3.group by
4.select
5.having

例子：查询平均工资大于2000的部门的编号和平均工资。

select
	deptno,
	avg(sal)
from
	emp
group by
	deptno
having
	avg(sal) > 2000;

例子：查询部门人数高于4个人部门的编号和部门名称以及部门人数。

select
	e.deptno 部门的编号,
	d.dname 部门名称,
	count(e.empno) 部门人数
from
	emp e join dept d
on
	e.deptno = d.deptno
group by
	e.deptno
having
	count(e.empno) > 4;

六.子查询

一个查询中嵌套着另一个查询，成为子查询。
语法：
	select(子查询)
	from(子查询)
	where(子查询)
	group by
	having(子查询)
	order by

子查询必须写在小括号中
子查询可以写在除group by 和 order by 之外任意的位置。

子查询是将查询的结果当做另一个查询的条件来执行的。


子查询的分类：
1.单列子查询
	返回的结果的单行单列。
2.多行子查询
	返回的结果是多行单列。
3.多列子查询
	返回单行多列或者多行多列。

1.单列子查询

例子：查询工资比7566高的员工的信息。

---首先要查询7566的工资是多少？
select sal from emp where empno = 7566;

select
	*
from
	emp
where
	sal > (select sal from emp where empno = 7566);

例子：查询工资比7654工资高同时又与7900从事相同工作的员工信息。

---查询7654员工的工资是多少？
select sal from emp where empno = 7654;

--查询7900员工从事什么工作？
select job from emp where empno = 7900;

---主查询
select
	*
from
	emp
where
	sal > (select sal from emp where empno = 7654)
and
	job = (select job from emp where empno = 7900);

例子：查询工资最高的员工信息。

---先查询公司最低工资是多少？
select max(sal) from emp;

---主查询
select
	ename 姓名,
	sal 工资
from
	emp
where
	sal = (select max(sal) from emp);

例子：查询每个部门的编号和最低的工资，要求最低工资大于等于部门30的最低工资。

---先查询出部门30的最低工资是多少？
select min(sal) from emp where deptno = 30;

select
	deptno 部门编号,
	min(sal) 最低工资
from
	emp
group by
	deptno
having
	最低工资 >= (select min(sal) from emp where deptno = 30);

2.多行子查询

对于多行子查询，使用三种操作符进行操作：
1.in	包含
2.any	任意一个
3.all	所有

• in

  例子：查询部门编号大于等于部门20的雇员的信息。

  ---哪些部门的部门编号大于等于部门20
  select deptno from dept where deptno >= 20;
  
  select
  	*
  from
  	emp
  where
  	deptno in (select deptno from dept where deptno >= 20);
  

• any

  any有3种用法：
  =any	与 in 关键字相同
  >any	只要比里面的最小值大即可
  <any	只要比里面的最大值小即可。
  

  例子：查询工资比部门20任意员工的工资高的员工

  ---先查询出部门20员工在工资。
  select sal from emp where deptno = 20;
  
  ---主查询
  select 
  	*
  from
  	emp
  where
  	sal >any (select sal from emp where deptno = 20);
  
  

  例子：查询工资比部门20任意员工的工资小的员工

  ---先查询出部门20员工在工资。
  select sal from emp where deptno = 20;
  
  ---主查询
  select 
  	*
  from
  	emp
  where
  	sal <any (select sal from emp where deptno = 20);
  
  

• all

  all关键字有两种用法
  >all	大于里面的最大值
  <all	小于里面的最小值
  

  例子：查询工资比部门20最高工资要高的员工

  ---先查询出部门20员工在工资。
  select sal from emp where deptno = 20;
  
  ---主查询
  select 
  	*
  from
  	emp
  where
  	sal >all (select sal from emp where deptno = 20);
  

  例子：查询工资比部门20最低工资要低的员工

  ---先查询出部门20员工在工资。
  select sal from emp where deptno = 20;
  
  ---主查询
  select 
  	*
  from
  	emp
  where
  	sal <all (select sal from emp where deptno = 20);
  

here deptno = 20);

例子：查询工资比部门20任意员工的工资小的员工

```sql
---先查询出部门20员工在工资。
select sal from emp where deptno = 20;

---主查询
select 
	*
from
	emp
where
	sal <any (select sal from emp where deptno = 20);

• all

  all关键字有两种用法
  >all	大于里面的最大值
  <all	小于里面的最小值
  

  例子：查询工资比部门20最高工资要高的员工

  ---先查询出部门20员工在工资。
  select sal from emp where deptno = 20;
  
  ---主查询
  select 
  	*
  from
  	emp
  where
  	sal >all (select sal from emp where deptno = 20);
  

  例子：查询工资比部门20最低工资要低的员工

  ---先查询出部门20员工在工资。
  select sal from emp where deptno = 20;
  
  ---主查询
  select 
  	*
  from
  	emp
  where
  	sal <all (select sal from emp where deptno = 20);
  

练习题

1、列出至少有4个员工的部门名称

2、列出薪金比"SMITH"多的所有员工

3、列出所有员工的姓名以及其直接上级的姓名 

4、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称

5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

6、列出所有从事"CLERK"工作的雇员姓名及其部门名称、部门人数

7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

8、列出在部门"salesman"（销售部）工作的员工的姓名，假定不知道销售部的部门编号

9、列出薪金高于公司平均薪金的雇员姓名、所在部门名称、领导姓名、雇员的工资等级求出公司平均薪金

10、列出与"SMITH"从事相同工作的所有员工及部门名称

11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金 

12、列出薪金高于部门30所有员工薪金的员工姓名、薪金及部门名称

13、列出在每个部门工作的员工数量、平均工资 

14、列出所有员工的姓名、部门名称和工资

15、列出所有部门的详细信息和部门人数

16、列出每种工作的最低工资以及从事此工作的雇员姓名

17、列出各个部门的经理的最低薪金

18、列出所有员工的年工资，按年薪从低到高排序

19、查询雇员的领导信息，要求领导的薪水要超过3000

20、求出部门名称中，带'S'字符的部门员工的工资总和 、部门人数