sql常见查询语句

CREATE DATABASE practice;
CREATE TABLE dept(
	deptNo INT PRIMARY KEY AUTO_INCREMENT,
	dName VARCHAR(20) NOT NULL,
	loc VARCHAR(20) NOT NULL
);


CREATE TABLE emp(
	empNo INT PRIMARY KEY AUTO_INCREMENT,
	eName VARCHAR(20) NOT NULL,
	job VARCHAR(20) NOT NULL,
	mgr INT,
	hireDate DATE,
	sal DOUBLE,
	comm DOUBLE,
	deptNo INT
);

CREATE TABLE salGrade(
	grade INT PRIMARY KEY AUTO_INCREMENT,
	losal DOUBLE ,
	hisal DOUBLE
);

ALTER TABLE emp ADD FOREIGN KEY(deptNo) REFERENCES dept(deptNo);
DESC emp


INSERT INTO dept(dName,loc)VALUES('开发','南京');
INSERT INTO dept(dName,loc)VALUES('测试','上海');
INSERT INTO dept(dName,loc)VALUES('运营','北京');
INSERT INTO dept(dName,loc)VALUES('销售','无锡');
INSERT INTO dept(dName,loc)VALUES('总经理室','南京');

INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('王老板','总经理',NULL,'2018-10-10',50000,0,5);

INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('周伯通','开发经理',1,'2018-10-10',40000,0,5);

INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('郭靖','测试经理',1,'2018-10-10',40000,0,5);

INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('杨康','运营经理',1,'2018-10-10',40000,0,5);

INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('黄蓉','销售经理',1,'2018-10-10',40000,0,5);


INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('周一一','开发员工',2,'2018-10-10',8000,5000,1);

INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('周二二','开发员工',2,'2017-10-10',9000,6000,1);
INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('周三三','开发员工',2,'2016-10-10',10000,7000,1);

INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('郭一一','测试员工',3,'2018-10-10',6000,3000,2);
INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('郭二二','测试员工',3,'2017-10-10',7000,4000,2);
INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('郭三三','测试员工',3,'2016-10-10',8000,5000,2);

INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('杨一一','运营员工',4,'2018-10-10',6000,3000,3);
INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('杨二二','运营员工',4,'2017-10-10',7000,4000,3);
INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('杨三三','运营员工',4,'2016-10-10',8000,5000,3);


INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('黄一一','销售员工',5,'2018-10-10',6000,3000,4);
INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('黄二二','销售员工',5,'2017-10-10',7000,4000,4);
INSERT INTO  emp(eName,job,mgr,hireDate,sal,comm,deptNo)VALUES('黄三三','销售员工',5,'2016-10-10',8000,5000,4);



INSERT INTO salGrade(losal,hisal)VALUES(6000,20000);
INSERT INTO salGrade(losal,hisal)VALUES(5000,15000);
INSERT INTO salGrade(losal,hisal)VALUES(4000,10000);

SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salGrade;

-- 去重distance（）查询员工表中有哪几种工种
SELECT DISTINCT(job) FROM emp

-- 计数函数(如果是具体字段会将为null的值过滤掉)查询员工表中一共有几条数据
SELECT COUNT(*) FROM emp
SELECT COUNT(mgr) FROM emp

-- 分组查询每个工种下面分别有多少名员工
SELECT job,COUNT(*) FROM emp GROUP BY job;

-- group by having 测试 查询人数大于2人的工种
SELECT job,COUNT(*) FROM emp GROUP BY job  HAVING COUNT(*)>2

-- 排序 order by 对年工资进行逆序排序，显示员工姓名和年工资
SELECT eName,sal*12 AS ysal FROM emp ORDER BY sal DESC

-- 分页 limt+数据 显示员工表的前五条数据
SELECT * FROM emp LIMIT 5;

-- 分页 linit+数据+数据
SELECT * FROM emp LIMIT 1,5;

-- max 查询月工资的最大值
SELECT sal,eName FROM emp WHERE sal=(SELECT MIN(sal) FROM emp )
-- min
SELECT MIN(sal) FROM emp
-- avg
SELECT AVG(sal) FROM emp
-- sum
SELECT SUM(sal) FROM emp

UPDATE emp SET eName = 'zc' WHERE empNo =1

-- 子查询
-- 查询工资大于平均工资的员工姓名和工资
SELECT eName ,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)
-- 查询工资最高的员工姓名和工资
SELECT eName ,sal FROM emp WHERE sal=(SELECT MIN(sal) FROM emp)
-- 查询在销售部门工作的员工姓名
SELECT eName,job FROM emp WHERE deptNo=(SELECT deptNo FROM dept WHERE dName='销售')
-- 查询员工黄一一所在的部门名称
SELECT dName FROM dept WHERE deptNo=(SELECT deptNo FROM emp WHERE eName='黄一一');

-- 自然连接
-- 查询员工黄一一所在的部门名称
SELECT dName FROM emp AS e,dept d WHERE e.`deptNo`=d.`deptNo` AND e.`eName`='黄一一'
-- 查询在销售部门工作的员工姓名
SELECT eName FROM emp AS e,dept d WHERE e.`deptNo`=d.`deptNo` AND d.`dName`='销售'
-- 显示所有员工的员工信息，包括员工部门
SELECT e.*,dName FROM emp e,dept d WHERE e.`deptNo`=d.`deptNo`
-- 查询员工数量大于2人的工种，显示部门编号、部门名称、位置和具体人数
SELECT d.`deptNo`,d.`dName`,loc ,COUNT(*) number FROM emp e,dept d WHERE e.`deptNo`=d.`deptNo` GROUP BY job HAVING number>2

-- 内链接
-- 查询员工黄一一所在的部门名称
SELECT dName FROM emp e INNER JOIN dept d ON e.`deptNo`=d.`deptNo` WHERE eName='黄一一'
-- 查询每个部门的人数、部门名称，部门编号
SELECT d.`deptNo`,dName,COUNT(*) number FROM emp e  INNER JOIN dept d ON e.`deptNo`=d.`deptNo`  GROUP BY d.deptNo
-- 查询员工数量大于3人的部门，显示部门编号、部门名称、位置和具体人数
SELECT d.`deptNo`,dName,COUNT(*) number FROM emp e  INNER JOIN dept d ON e.`deptNo`=d.`deptNo`  GROUP BY d.deptNo HAVING number>3
-- 查询入职日期早于直接上级的员工编号、员工姓名、部门名称
SELECT e1.empno,e1.eName,dName FROM emp e1 INNER JOIN emp e2 ON e1.mgr=e2.empNo INNER JOIN dept d ON e1.deptNo=d.`deptNo` WHERE e1.hireDate<e2.hireDate

-- 左右连接 
-- ：查询员工姓名及其直接上级的姓名（先采取内连接方式）
-- 内连接
SELECT e1.eName,e2.eName FROM emp e1 JOIN emp e2 ON e1.mgr=e2.empNo
-- 左外连接 列出所有的左边数据
SELECT e1.eName,e2.eName FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empNo
-- 右外连接 列出所有的右边的数据
SELECT e1.eName,e2.eName FROM emp e1 RIGHT JOIN emp e2 ON e1.mgr=e2.empNo