MySQL基础查询

学久了有些知识就忘记了，脑子太不好使了。。。

MySQL中SQl基本语法

1、数据库基本操作DML、DDL

1、DQL：查询语句，凡是select语句都是DQL

2、DML：insert delete update：对表中的数据进行增删改查

插入数据： insert into tname(field1, field2) values(value1, value2)
删除数据： delete from tname where 删除条件
更新数据： update <表名> set <列名=更新值> [where <更新条件>]  

3、DDL：create drop alter：对表的结构进行增删改

操作数据库：
    1、创建数据库： create database dbname
    2、删除数据库： drop database dbname
    3、查看数据库： show databases;
    4、查看详细数据库： show create database 数据库名；
   	5、使用数据库： use dbname；   
   	
操作表：
	1、创建表： 
        create table tname(
            字段名 1 数据类型 （解释） 约束条件，
            字段名 2 数据类型 （解释） 约束条件
        )
        约束条件包括以下三条：
        UNIQUE 唯一约束
        NOT NULL 非空约束， 此数据插入不可以为空
        PRIMARY KEY 主键约束， 用于标识表的记录， 不允许为空， 可以加快查询速度
	2、查看表： show tables
	3、查看详细表： show create table 表名
	4、修改表名： alter table 旧表名 rename to 新表名；
	5、修改字段的数据类型： alter table 表名 modify 字段名 新数据类型
    6、修改字段名： alter table 表名 change 字段名 新字段名 数据类型
    7、增加字段： alter table 表名 add 字段名 数据类型 约束条件 （First/after）
    8、删除字段： alter table 表名 drop 字段名
    9、删除表： drop table tname

​

2、数据库DQL（重点）

数据库表的准备：

1、简单查询

1、查询部分字段：
	select 字段列表 from 表名列表；-- 字段如果有多个使用逗号隔开
	select ename,job form emp;
	
2、查询所有的字段：
	select * from 表名;
	select * from emp;
	
3、查询时给字段重命名：
	select 字段列表 as 新的字段列表名 from 表名（as可以省略）
	select sal*12 as yearsal from emp;

2、条件查询

1、条件：>,<,>=,<=,!=
	select 字段列表 from 表名列表 where 条件列表
	select sal from emp where sal>3000;
	select sal from emp where sal=3000;
	select sal from emp where sal!=3000;
	
2、条件：between ...and...
	select 字段列表 from 表名 where ... between... and....
	select sal from emp where sal>=3000 and sal<=5000;
	select sal from emp where sql between 3000 and 5000;-- 和上一句作用相同,闭区间
	select ename from emp where ename between 'A' and 'K';-- 用于字符左闭右开
		
3、条件：is null
	select 字段列表 from 表名 where .... is null
	select comm from emp where comm is null;-- null不是一个值，因此不能使用=，而是is
	select comm from emp where comm is not null;
	
4、条件：and 和 or
	select 字段列表  from 表名 where...and ....
	select ename where sal>1800 and deptno=30;-- 交集
	select 字段列表  from 表名 where...or....
	select ename from emp where job="CLERK" or job="ANALYST";-- 并集
	
5、条件：in
	select 字段列表 from 表名 where ... in();
	select ename from emp where job="CLERK" or job="ANALYST";
	select ename from emp where job in("CLERK","ANALYST");
	select ename from emp where sal not in(2000,3000);
	
6、条件：like
	select 字段列表  from 表名 where ... like....
		-- %：代表0-n个任意字符
		-- _：代表任意一个字符
	select ename from emp where ename like '%o%';-- ename中含有o的员工
	select ename from emp where ename like 'S%'; -- ename以S开头的员工
	select ename from emp where ename like '%T'; -- ename以T结尾的员工
	select ename from emp where ename like '_A%';-- ename第2个字母为A的员工
	

3、排序查询

1、语法：order by 子句
	* order by 排序字段1 排序方式1，排序字段2 排序方式2，...
2、排序方式：
	* ASC：升序，默认的排序方式
	* DESC：降序的
3、注意：如果有多个排序方式，则当前面的条件一样时，才会判断后面的条件
	select ename，sal from emp order by sal;
	select ename，sal from emp order by sal desc;
	select ename，sal from emp order by sal desc,ename asc;-- 当工资相同时才会考虑ename排序
	

4、分组函数与去重

1.分组函数自动忽略null值，如果null值参加运算，最后结果为null，但是分组函数自动忽略空值
2.分组函数不能直接使用在where关键字后面。（和sql解析顺序有关）

1、分组函数语法：
	select sum(sal) from emp;-- 计算sal总和
	select avg(sal) from emp;-- 计算sal的平均数
	select count(ename) from emp; -- 计算员工总数
	select max(sal) from emp;-- 计算工资最大值
	select min(sal) from emp;-- 计算工资最小值
	select count(*) from emp; -- 计算满足条件的所有记录总数
2、去重函数语法：
	select distinct job from emp;-- 去除重复岗位
	-- distinct只能放在所在所有字段的最前面，放在后面会报错
	-- distinct后面有多个字段时，去除的是两个字段都重复的元素，交集
	select distinct deptno,job from emp;
	

5、分组查询

1、group by ...：通过哪个或者哪些字段进行分组
   order by ...：通过哪个或者哪些字段进行排序

2、having 和 where 都是完成数据过滤的，一个在分组之前过滤，一个在分组之后过滤
	where在group by之前进行过滤
	having在group by之后进行过滤
	
3、原则：尽量在where中过滤，实在不能过滤的再用having过滤

4、实例：
	1、找出每个工作岗位的最高薪水：
		select job,max(sal) from emp group by job;-- select后面只能跟参与跟分组的字段和分组函数
	2、计算每个部门的平均薪水：
     	select deptno,avg(sal) from emp group by deptno;
	3、计算不同部门不同工作岗位的最高薪水：
		select deptno,max(sal) from emp group by deptno;-- 计算不同部门的最高薪水
		select job,max(sal) from emp group by job;-- 计算不同工作岗位的最高薪水
		select deptno,job,max(sal) from emp group by deptno,job;-- 取并集，只要一个不同视为不同
	4、找出每个工作岗位的最高薪水，除了manager：
		select job,max(sql) from emp where job!='MANAGER' group by job;
	5、每个工作岗位的平均薪水，要求平均薪水大于1500：
		-- 错误的写法，因为where后面不能跟分组函数，sql解析过程是先过滤再分组，没有分组无法执行分组函数
    	select job,avg(sal) from emp where avg(sal)>1500 group by job;
    	-- 使用having过滤，因为having在分组之后过滤，where在分组之前过滤
    	select job,avg(sal) from emp group by job having avg(sal)>1500;
    	

6、SQL编写和解析顺序

1、编写顺序：
			select
				...
			from
				...
			where
				...
			group by
				...
			order by
				...
				
2.解析顺序：
			1.from					从某张表中检索数据
			2.where 				经过某条件进行过滤
			3.group  by				然后分组
			4.having 				分组之后不满意再过滤
			5.select				查询出来
			6.order by				排序出来

7、连接查询

1.什么是连接查询？
		查询的时候如果只从一张表中检索数据称为单表查询。
		在实际的开发中，数据并不是存储在一张表中的，是同时存储在多张表中的，
		这些表和表之间存在关系，检索的时候通常需要将多张表联合起来取得有效数据，这种多表查询被称为连接查询。

2.连接查询根据连接方式可以分为：
		内连接
				等值连接
				非等值连接
				自连接
		外连接
				左外连接
				右外连接
3、在进行多表查询时，尽量给表起别名，这样效率高，可读性高				

1、内连接：

注意表与表之间的关系：

1、内连接中的等值连接：
	查询每个员工所在的部门名称，要求最终显示员工名和部门名：
		select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;-- 92语法 
		select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;-- 99语法
		
2、内连接中的非等值连接：
   	找出每一个员工对应的工资等级，要求显示员工名，工资，工资等级：
		select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;-- 92语法
		select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;-- 99语法，这份表的字段在另一个表的什么范围内

3、内连接中的自连接：所谓自连接就是将一张表看成两张表
	找出每个员工的上级领导，要求显示员工名以及对应的领导名：
	-- 将emp这张表看成两张表一张员工表，一张部门领导表，员工表的部门领导id等于部门表的id
	select e.ename,m.ename from emp e join emp m on e.mgr=m.empno;
	

2、外连接：

内连接：
A表和B表的记录能够完全匹配出来，称为内连接
外连接：
A表和B表的记录能够完全匹配出来，将其中一张表的记录无条件的查询出来，
对方没有匹配的记录会自动的模拟出null与之匹配，这种查询称为外连接。
外连接的查询结果条数>=内连接的查询结果条数

1、找出每个员工对应的部门名称，要求部门名和部门编号全部显示：
	1、内来接：
	-- 只有内连接时只能显示出来10、20、30对应的部门名称，但是40对应的部门名称无法显示，只能使用外连接
 	select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno=d.deptno;
 
	2、右外连接：
	-- outer可省略: right (outer) join
     select e.ename,d.dname,d.deptno from emp e right join dept d on e.deptno=d.deptno;
	
	3、左外连接：
 	-- 颠倒两张表的顺序，就从右外连接变成了左外连接
 	-- outer可省略: left (outer) join
 	-- 右外链接和左外连接可以相互转换，只需要使用一种即可。
 	select e.ename,d.dname,d.deptno from dept d left join emp e on e.deptno=d.deptno;

2、找出每一个员工对应的领导名，要求显示所有的员工:
	 select e.ename,m.ename from emp e left join emp m on e.mgr=m.empno;
	 -- 最好给两张表起个别名，查询的时候显示别名
 	select e.ename empname,m.ename mgrname from emp e left join emp m on e.mgr=m.empno;

三表以及三表以上的连接查询：

多张表进行表连接的语法格式：
			select
					xxx
			from 
					a
			join 
					b
			on 
					条件
			join
					c
			on 
					条件
原理：a表和b表先进行表连接，a表再和c表连接

找出每一个员工对应的部门名称，以及员工对应的员工等级，要求显示员工名，部门名，工资等级。
	select
    	e.ename,d.dname,s.grade 
	from 
		emp e
	join 
		dept d
	on 
		e.deptno=d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and hisal;
		

8、子查询

什么是子查询？
select语句嵌套select语句

子查询可以出现在哪？
select… (select)
from …(select）
where… (select)

1、where后面使用子查询：子查询可以作为条件，使用运算符去判断

  找出薪水比公司平均薪水高的员工，要求显示员工名和薪水：
  select avg(sal) from emp;
  select e.ename,e.sal from emp e join salgrade s where e.sal>(select avg(sal) from emp;)
  
2、from后面使用select子查询：from 后面使用子查询，可以把查询结果当做一张表，查询出的结果是多行多列

  找出每一个部门的平均薪水，要求显示平均薪水的薪水等级：
  -- 这个表作为临时表t
  select e.deptno,avg(sal) avgsal from emp e group by e.deptno;
  -- 连接临时表t和薪水等级表s
  select 
  	t.deptno,s.grade
  from
 	 (select deptno,avg(sal) avgsal from emp group by deptno) t 
  join
  	  salgrade s 
  on 
  	t.avgsal between s.losal and s.hisal;	

9、limit 分页查询

语法： limit 开始的索引，每页查询的条数
公式：开始的索引=(当前的页码-1)*每页显示的条数（注意是索引，不是编号，索引是从0开始的）
		0,3;-- 第1页
		3,3;-- 第2页
		6,3;-- 第3页
总语法：
			select 
				ename,sal
			from 
				emp
			order by
				sal desc
			limit
				(pageNO-1)*pageSize,pageSize;

1、找出emp表前5条记录：
	select count(*) from emp limit 0,5 
	-- 默认从0开始
	select count(*) from emp limit 5 
	
2、找出公司中工资排名在前五名的员工：
	-- 先进行降序排序，然后使用limit取出前5
	select ename,sal from emp order by sal desc limit 0,5;

3、找出工资排名在3-9名的员工：
	-- 先将员工薪资倒叙排序，limit开始索引为3，每页显示7条数据
	select ename ,sal from emp order by sal desc limit 3,7;
	

3、MySQL 20道练习题

1、取得每个部门最高薪水的人员名称：
	-- 下面是错误的，以为select后面只能跟参与分组的字段和分组函数，尽管答案正确，但是只是巧合
	select ename,deptno,max(sal) from emp group by deptno;
	-- 查询出每个部门的最高薪水，将查询结果作为一张临时表t
	select deptno,max(sal) maxsal from emp group by deptno;-- 结果多列作为一张临时表t
	select 
		e.ename
	from 
		(select deptno,max(sal) maxsal from emp group by deptno) t
	join 
		emp e
	on 
		t.maxsal = e.sal and t.deptno = e.deptno;
	
2、哪些人的薪水在部门的平均薪水之上：
	select deptno,avg(sal) avgsal from emp group by deptno;-- 结果多列作为一张临时表t
	select 
		ename,sal 
	from 
		(select deptno,avg(sal) avgsal from emp group by deptno) t
	join 
		emp e
	on
		e.deptno=t.deptno and e.sal>t.avgsal;
		
3、取得平均薪水的等级：
	select avg(sal) avgsal from emp;	-- 查询平均薪水，单列作为查询条件
	select 
		grade
    from 
    	salgrade 
	where 
		(select avg(sal) avgsal from emp) between losal and hisal;
		
4、求每个部门中平均薪水的等级：
	-- 每个部门的平均薪水，一般分组查询查询结果都是多列，查询结果可作为临时表t
	select deptno,avg(sal) avgsal  from emp group by deptno; -- 结果多列,可作为临时表t
	select 
		grade
	from 
		(select deptno,avg(sal) avgsal from emp group by deptno) t
	join 
		salgrade s
	on	
		t.avgsal between s.losal and s.hisal; 
		
5、取得部门中平均的薪水等级：
	-- 加入求每个人的薪水等级
	select 
		e.deptno,s.grade
	from 
		emp e 
	join 
		salgrade s 
	on 
		e.sal between s.losal and s.hisal;
		
	-- 按部门分组求平均的薪水等级
	select 
		e.deptno, avg(s.grade)
	from 
		emp e
	join 
		salgrade s
	on 
		e.sal between s.losal and s.hisal
	group by
		e.deptno;
		
6、不准用分组函数,取得最高薪水（给出两种解决方案）：
	-- 1、使用limit+order by
	select sal from emp order by sal desc limit 1;
	-- 2、使用自连接,找出第一张表的薪水比第二张表的薪水低的,只有最高薪水不在这张表中
	select  distinct a.sal from emp a join emp b on a.sal<b.sal;
	-- 找出不在自连接表中的数据
	select 
		sal 
	from 
		emp
    where 
        sal not in(select  distinct a.sal from emp a join emp b on a.sal<b.sal);
        
7、取得平均薪水最高的部门的部门编号（至少两种方案）：
	-- 1、先求出每个部门的平均薪水,然后按照平均薪水排序orderby +limit
	select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
	-- 2、先求出每个部门的平均薪水,使用max()函数
	select max(t.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) t;
	
8、取得平均薪水最高的部门的部门名称：
	-- 取得平均薪水最高的部门
	select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
	select 
		d.dname
	from 
    	(select deptno,avg(sal) avgsal 
         from emp group by deptno 
         order by avgsal desc 
         limit 1) t
    join 
    	dept d
    on
    	d.deptno = t.deptno ;
    	
9、取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名：
	-- 带有mgr字段的是领导，不带有mgr字段的是普通员工
	-- 1、找出领导编号
	 select distinct mgr from emp where mgr is not null;
    -- 2、找出所有普通员工的最高薪水
    select  max(sal) from emp e where empno not in(select distinct mgr from emp where mgr is not null);
    -- 3、取得比普通员工的最高薪水还要高的领导人姓名
    select  
    	ename,sal
    from
    	emp
    where
    	sal> (select max(sal) maxsal 
              from emp
              where empno not in(select distinct mgr from emp where mgr is not null)
             );
    	
10、取得薪水最高的前五名员工：
	select ename from emp order by sal desc limit 5;
	
11、取得薪水最高的第六名到第十名：
	select ename from emp order by sal desc limit 5,5;
	
12、取得最后入职的5名员工：
	select ename  from emp order by hiredate desc limit 5;
    	
13、取得每个薪水等级有多少员工：
	-- 分组的字段在查询的字段内，直接分组即可
	select 
		s.grade,count(*) 
	from 
		emp e 
	join 
		salgrade s 
	on 
		e.sal between s.losal and s.hisal 
	group by 
		s.grade;

14、列出所有员工及领导的姓名：
	-- 自连接
	select a.ename empname,b.ename leadername from emp a join emp b on a.mgr = b.empno;
    	
15、列出所有受雇日期早于其直接上级的所有员工的编号，姓名，部门名称：
	-- 1、列出所有受雇日期早于其直接上级的所有员工的编号，姓名
	select a.empno,b.ename from emp a join emp b on a.mgr = b.empno and a.hiredate> b.hiredate;
	select a.empno,b.ename from emp a join emp b on a.mgr = b.empno where a.hiredate> b.hiredate;
	-- 2、加上部门名称，三表连接
	select 
		a.empno,a.ename,d.dname
	from 
		emp a 
	join 
		emp b 
	on 
		a.mgr = b.empno and  a.hiredate< b.hiredate
	join 
		dept d
	on 
		a.deptno = d.deptno ;
	where
        a.hiredate< b.hiredate;
    -- 建议使用下面的方式
    select 
		a.empno,a.ename,d.dname
	from 
		emp a 
	join 
		emp b 
	on 
		a.mgr = b.empno and  a.hiredate< b.hiredate
	join 
		dept d
	on 
		a.deptno = d.deptno ;

16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门：
	-- 左连接
	select 
		d.dname,e.*
	from 
		dept d
	left join 
		emp e
	on 
		e.deptno = d.deptno
	
	
17、列出至少有5个员工的所有部门的部门信息：
	-- 按照部门分组，分组后过滤
	select deptno from emp group by deptno having count(*)>=5;
	select d.* from emp e join dept d on e.deptno=d.deptno group by deptno having count(*)>=5;
	
18、列出所有薪金比’SIMITH’多的员工信息：
	select sal from emp where ename='SIMITH';
	select * from emp where sal > (select sal from emp where ename='SIMITH');

19、列出所有"CLERK"的姓名及其部门名称，部门人数：
	select ename,deptno from emp where job = 'CLERK';
	
	-- 方法1:两张临时表连接
	select e.enmae ,e.deptno ,d.dname from emp e join dept d on e.deptno=d.deptno where job='CLERK';
	select deptno,count(*) count from dept group by deptno;
	-- 将上面两张表连接
	select 
		t1.ename,t1.dname,t2.count 
	from 
        (select e.ename ,e.deptno ,d.dname from emp e join dept d on e.deptno=d.deptno where job='CLERK') t1
	join 
		(select deptno,count(*) count from emp group by deptno) t2
	on t1.deptno = t2.deptno;
	
	-- 方法2：三表连接
	select 
		e.ename,d.dname ,e.deptno,t.count
	from 
		emp e 
	join 
		dept d
    on 
    	e.deptno =d.deptno 
	join 
		(select deptno,count(*) count from emp group by deptno) t 
	on 
		e.deptno = t.deptno  
	where 
		e.job="CLERK";
		
20、	列出薪金大于1500的各种工作及从事此工作的全部雇员人数：
	select job,count(*) count from emp where sal>1500 group by job;