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
sql常见查询语句
最新推荐文章于 2020-12-03 16:41:33 发布