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

本文详细介绍了MySQL的查询操作,包括选择特定字段、计算年薪、筛选条件、排序、分页以及多表查询。还讲解了SQL99标准中的内连接、外连接,以及聚合函数和分组统计的使用。此外,还涵盖了子查询的概念和应用,如单列子查询、多行子查询和多列子查询,并提供了多个练习题目以加深理解。
摘要由CSDN通过智能技术生成

标题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);

执行如下sqlselect
        name
    from
        test
    group by
        name;
结果:
	aa
	bb
	cc
	dd
	ee
	
执行如下sqlselect
        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 |
+-----------+------+----------+	

多列分组:

执行如下sqlselect
        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'字符的部门员工的工资总和 、部门人数
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

huangshaohui00

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值