MySQL学习01-基础语法以及练习

SQL语句：

DDL 数据定义语言（对数据库结构进行改变的语言）

​ 常用：alter改变 、drop 删除 、Create 创建

DML数据操作语言（增删改查）

​			 Insert：增加数据    insert into 表名（列名，列名……）values（值1，值2……）  后面的值要和列一一对应
  			 Update：更新数据    update 表名 set 列名=值，列名=值 where 条件……
  			 Delete：表示删除表格中的所有数据，表结构还有，操作会记入数据库日志
					delete from 表名 where 条件

​			 Trancat table：表示删除表格中的所有数据，表结构还有，只会剩下列名						（截断数据库表格，操作快速效率比delete高，但是操作不会记入数据库日志）
  			 Select：select查询    select列名，列名…… from 表格 where 条件

DCL**数据控制语言（一些授权语句）

			grant 权限 to user

			remove 权限 from user

TCL**事务控制语言

			commit rollback（回滚） savepoint（保存点）

			distinct去重，去掉重复的行

实例：

先创建一个数据库以及相关列表，员工列表emp和部门列表dept

create table emp(

empno int PRIMARY KEY,

ename VARCHAR(30) not null,

job VARCHAR(50) not null,

mgr int ,

hiredate date ,

sal int,

comm INT,

deptno int );



create table dept(

deptno int PRIMARY key,

dname VARCHAR(100) not null UNIQUE,

loc VARCHAR(200));



alter TABLE emp ADD CONSTRAINT fk_emp_deptno

FOREIGN KEY(deptno) REFERENCES dept(deptno);



insert into dept values(10,'教学部','济南');

insert into dept values(20,'实训中心','莱芜');

insert into dept values(30,'就业部','济南');

insert into dept values(40,'市场部','济南');

insert into dept values(50,'总经理办公室','济南');



select * from dept;



insert into emp values(1001,'李军','总经理',null,'2018-7-10',10000,2000,50);

insert into emp values(1002,'赵旭','班主任',1003,'2018-7-10',3000,2000,20);

insert into emp values(1003,'李萌','实训中心主任',1001,'2018-7-10',4000,2000,20);

insert into emp values(1004,'刘枫','教学总监',1001,'2018-7-10',15000,2000,10);

insert into emp values(1005,'王涛','嵌入式讲师',1004,'2018-7-10',5000,2000,10);

insert into emp values(1006,'实验者','班主任',1003,'2018-7-10',2888,2000,10);

insert into emp values(1005,'实验者二型','实训中心副主任',1001,'2018-7-10',5000,2000,20);

查找属于教学部的员工的信息

先到部门表中查询教学部的编号，再到员工表中查询谁的部门编号是教学部的

​ 子查询：一个查询结果作为另一个查询的一部分出现

​ Select * from emp where deptno =(Select deptno from dept where dname=’教学部’)

​ 多表连接查询：查询的内容或者条件出现在多个表格里面，先把多个表格整合成一张大的表格，然后从大的表格里面查询

​ 有低效率的交叉链接和高效率的内链接和外链接，前两个只会出现符合条件的记录

​ Select * from emp,dept（大表制作）where emp.deptno =dept.deptno（整合相同部门）

​ 或者 Select empno,ename,job,mgr,hiredate,sal,comm,emp.deptno from emp,dept（大表制作）where emp.deptno =dept.deptno（整合相同部门）

​ Select * from emp **cross join** dept where emp.deptno = dept.deptno and dname=‘教学部’ 交叉链接

​ Select * from emp **inner join** dept on emp.deptno = dept.deptno; 内链接

上面两种方法只会出现符合条件的对象

外链接特点：主表里面的记录一定会出现在结果里面（不符合条件的东西也会显示），例如下面的right就是指 dept是主表，如果是left的话则emp是主表

但是使用外链接的话，

​ Select * from emp **right** **outer join** dept on emp.depton = dept.deptno;（right是指右边的表格无论如何都会出现，outer join 是指外链接）

​

分组聚合查询：

​ 1.查询总工资（select sum（sal+comm）），最大工资（max（sal）），最小工资（min（sal）），平均工资（avg（sal））， 最大工资和最小工资差额（max（sal）-min（sal）） from emp；

​ 2.查询各个部门的平均工资 先把所有的员工按照部门分组，然后在每一个组里面按聚合函数做计算

​ Select deptno, avg(sal) from emp group by deptno

​ Select *from emp;

​ 3.查询平均工资大于5000的部门信息（分组后再次过滤用HAVING）

​ Select * from dept where deptno in (Select deptno, avg(sal) from emp group by deptno HAVING avg(sal)>5000)

Where字句和Having有什么区别

​ 相同点：HAVING和WHERE都是对查询结果进行筛选，后面都可以进行条件判 断

​ 不同点：HAVING可用于GROUP BY后，WHERE不行

​ HAVING可用于聚合函数后，WHERE不行

​ HAVING是对分组后数据进行筛选，WHERE是对表数据进行筛选

子查询：

​ Select 字段名， （子查询） from 表名；

作业题

#1列出至少有一个员工的所有部门
#SELECT dname from dept where EXISTS(select * from emp where emp.deptno = dept.deptno);

#2列出薪金比“王涛”多的所有员工
#SELECT ename FROM emp where sal > (SELECT sal from emp where ename='王涛');

#3列出所有员工的姓名及其直接上级的姓名，没有直接领导的员工，领导位置显示Boss
#select e1.ename,e2.ename from emp as e1,emp as e2 where e1.mgr=e2.empno;

#4列出受雇日期早于其直接上级的所有员工
#		select e1.ename,e2.ename from emp as e1,emp as e2 where e1.mgr=e2.empno and e1.hiredate<e2.hiredate;

#5列出部门名称和这些部门的员工信息，包括那些没有员工的部门。
#SELECT * from dept LEFT JOIN emp on dept.deptno = emp.deptno

#6列出所有job为“CLERK”（办事员）的姓名及其部门名称
#SELECT emp.ename,dept.dname FROM emp,dept 
#	WHERE emp.deptno=dept.deptno
#		AND emp.job='办事员'
#;

#7列出最低薪金大于1500的各种工作。
#SELECT distinct job from emp WHERE sal > 1500;

#8列出在部门“教学部”工作的员工的姓名，假定不知道教学部的部门编号。
#SELECT emp.ename from emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='教学部'

#9列出薪金高于公司平均薪金的所有员工。
#SELECT * FROM emp WHERE sal > (SELECT avg(sal) from emp);

#10列出与“刘枫”从事相同工作的所有员工。
#SELECT * from emp WHERE job=(SELECT job FROM emp WHERE ename='刘枫');

#11列出薪金等于部门10中员工的薪金的所有员工的姓名和薪金。
#SELECT ename,sal FROM emp WHERE sal=ANY(SELECT sal FROM emp WHERE deptno=10);

#12列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
#SELECT ename,sal FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=30)；

#13列出在每个部门工作的员工数量、平均工资和平均服务期限。
-- SELECT COUNT(emp.empno), avg(emp.sal) 
-- FROM dept 
-- LEFT JOIN emp
-- on emp.deptno=dept.deptno
-- GROUP BY dept.deptno;

#14列出所有员工的姓名、部门名称和工资。
-- SELECT emp.ename,emp.sal,dept.dname 
-- FROM dept
-- RIGHT OUTER JOIN emp
-- on emp.deptno=dept.deptno
-- GROUP BY emp.ename;

#15列出从事同一种工作但属于不同部门的员工的一种组合。
-- SELECT emp1.ename,emp2.ename,emp1.job,emp2.job,emp1.deptno,emp2.deptno
-- FROM emp AS emp1,emp AS emp2
-- WHERE emp1.job=emp2.job AND emp1.deptno<>emp2.deptno;

#16列出所有部门的详细信息和部门人数。
-- SELECT dept.deptno,dept.dname,dept.loc,COUNT(emp.empno)
-- FROM dept
-- LEFT JOIN emp
-- ON emp.deptno=dept.deptno
-- GROUP BY dept.deptno;

#17列出各种工作的最低工资。
-- SELECT job,min(sal)
-- FROM emp
-- GROUP BY job;

#18、列出各个部门的MANAGER（班主任）的最低薪金（job为MANAGER）
-- SELECT emp.deptno,dept.dname, min(sal)
-- FROM emp,dept
-- WHERE emp.deptno=dept.deptno AND emp.job='班主任'
-- GROUP BY emp.deptno;

#19列出所有员工的年工资，按年薪从低到高排序
-- SELECT emp.ename,(IFNULL(emp.comm,0)+sal)*12 AS NIAN
-- From emp 
-- ORDER BY NIAN;