sql 练习

sql 练习

-- mysql 锁表查询
-- SELECT * FROM information_schema.INNODB_TRX; 
-- kill trx_mysql_thread_id


-- 创建部门表
DROP TABLE IF EXISTS dept; 
CREATE TABLE dept(
    -- 部门编号
    deptno int PRIMARY KEY, 
    -- 部门名称
    dname VARCHAR(14), 
    -- 部门所在地
    loc VARCHAR(13)
  );

-- 向部门表插入数据
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

-- 创建员工表
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(
  -- 员工编号
  empno int PRIMARY KEY, 
  -- 员工姓名
  ename VARCHAR(10), 
  -- 工作岗位
  job VARCHAR(9), 
  -- 直属领导
  mgr int, 
  -- 入职时间
  hiredate DATE, 
  -- 工资
  sal double, 
  -- 奖金
  comm double, 
  -- 所属部门
  deptno int
);


-- 为员工表表添加外键约束
ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY EMP(deptno) REFERENCES dept (deptno);

-- 向员工表插入数据
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);


-- 创建工资等级表
DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade(
  -- 等级
  grade int, 
  -- 最低工资
  losal double, 
  -- 最高工资
  hisal double
);

-- 向工资等级表插入数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);



-- emp 所有数据
select * from emp;

-- 查询有多少个员工
select COUNT(*) from emp;

-- 查询每个人的年薪
select ename , sal * 12 as nianxin from emp;

-- 查询每个人当月的工资
select ename, sal  + (case when comm is null then 0 else comm end) as nianxin from emp

-- 查询公司员工所处的所有部门
SELECT DISTINCT deptno FROM emp;

-- 查询公司员工所处的所有部门,以及工作岗位
SELECT DISTINCT deptno,job FROM emp;

-- 查询部门在10的员工
SELECT * FROM emp WHERE deptno = 10;

-- 查询部门不在10的员工
SELECT * FROM emp WHERE deptno <> 10;

-- 查询员工薪资在800-1500 之间的员工
SELECT * FROM emp WHERE sal BETWEEN 800 and 1500;

-- 查询工资是800和1500的员工
SELECT * FROM emp WHERE sal in(800,1500);

-- 查询comm为空的员工
SELECT * FROM emp WHERE comm is not null;

-- 查询雇佣日期 大于1981-12-01
SELECT * FROM emp WHERE hiredate > '1981-12-01'

-- 查询所有员工 根据雇佣日期排序
SELECT * FROM emp ORDER BY hiredate;

-- 查询名字包含A的
SELECT * FROM emp WHERE ename like '%A%'

-- 查询名字第二个字符是A的
SELECT * FROM emp WHERE ename like '_A%'

-- 查询名字里带有% 的，需要转义一下
SELECT * FROM emp WHERE ename like '%\%%'

-- 根据工资进行排序
SELECT * FROM emp ORDER BY sal;

-- 查询部门在10的员工按照工资排序
SELECT * FROM emp WHERE deptno = 10 ORDER BY sal

-- 查寻所有的名字 全小写 lower and upper
SELECT LOWER(ename) FROM emp;

-- concat 连接字符串
SELECT CONCAT('www.','mikael','.com') as url;

-- 字符串长度
SELECT CHAR_LENGTH('aaaa');

-- 截取字符串 值 0 pos返回空字符串.
SELECT SUBSTRING('goodgoodmove' ,1 , 4);

-- 将要从中提取子串的字符串中第一个字符的位置计算为 1。
SELECT SUBSTRING('goodgoodmove' FROM -4 FOR 4);

-- 去除前后字符串  LTRIM(str)  RTRIM(str) 去除左右空格
SELECT TRIM(' this is trim function ');

-- 向上取整  3.14 ceil end is 4
SELECT CEILING(3.14);

-- 向下取整
SELECT FLOOR(3.14);

-- absolute 绝对值
SELECT ABS(-3);

-- 四舍五入 第二个参数是保留几位小数
SELECT ROUND(3.1415926,1);

-- 截断 不进行四舍五入, 第二个参数是保留几个小数
SELECT TRUNCATE(3.1415926,3);

-- pi();
SELECT PI();

-- 当前日期
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT NOW();


-- ***** 重点 组函数 ******** 

-- 查询员工工资最高的人  先查询工资最高的,再去找他的名字 max()
SELECT * FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

-- 查询员工工资最低的人  先查询工资最高的,再去找他的名字
SELECT * FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);

-- 平均值  向上取整
SELECT CEIL(AVG(sal)) FROM emp;

-- 总和
SELECT SUM(sal) FROM emp;

-- count(*)  count(字段名) 空值不进行计数
SELECT COUNT(*) FROM emp;
SELECT COUNT(comm) FROM emp;

-- 分组 group by  查询每个部门赚最多的人
SELECT deptno , MAX(sal) FROM emp GROUP BY deptno;
 

-- 查询每个部门薪水最高的名字，先查出每个部门最高的薪资，在查询工资为这些的人信息
SELECT
	ename,
	sal,
	e.deptno 
FROM
	emp AS e
	INNER JOIN ( SELECT deptno, max( sal ) AS max_sal FROM emp GROUP BY deptno ) AS temp 
WHERE
	e.deptno = temp.deptno 
	AND e.sal = temp.max_sal


-- 查询平均薪水在2000以上的部门 HAVING 对分组后的数据进行过滤
SELECT CEIL(AVG(sal)) FROM emp GROUP BY deptno HAVING CEIL(AVG(sal)) > 2000;


-- 查询薪水大于1200的员工按照部门分组平均工资大于1500的部门，倒叙排序
SELECT AVG(sal),deptno FROM emp WHERE sal > 1200 GROUP BY deptno HAVING AVG(sal) > 1500  ORDER BY AVG(sal) DESC;


-- 子查询  求那些人比平均工资高
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp) ORDER BY deptno,sal DESC;

-- 按照部门分组，查询部门最高薪水，可能是多个人  

-- 查询一个人的名字和他的经理名字 left JOIN
SELECT e1.ename , e2.ename FROM emp e1 LEFT JOIN emp e2 on e1.mgr = e2.empno

-- 查询每个人对应的部门
SELECT e.ename ,d.dname FROM emp e JOIN dept d on e.deptno = d.deptno ORDER BY d.dname


-- 查询薪资登记表
SELECT * FROM salgrade;
-- 查询每个人对应的部门 加上薪资级别  1992
SELECT e.ename,e.sal, d.dname, s.grade FROM emp e, dept d, salgrade s WHERE e.deptno = d.deptno AND e.sal >= s.losal AND e.sal <= hisal ORDER BY s.grade;

-- 查询每个人对应的部门 加上薪资级别  1999
SELECT e.ename,e.sal,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 s.hisal) ORDER BY s.grade,sal;

-- 查询每个组薪资等级最高的人的薪资等级
SELECT
	ename,
	sal,
	e.deptno,
	s.grade
FROM
	emp AS e
	JOIN ( SELECT deptno, max( sal ) AS max_sal FR OM emp GROUP BY deptno ) AS temp ON(e.deptno = temp.deptno AND e.sal = temp.max_sal)
	JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal)
	

-- 查询部门总和薪水的等级
SELECT
	* 
FROM
	( SELECT e.deptno, sum( e.sal ) sum_sal FROM emp e GROUP BY e.deptno ) temp
	LEFT JOIN salgrade s ON (
		temp.sum_sal BETWEEN s.losal 
	AND s.hisal)

-- 查询部门平均薪水的等级
SELECT
	* 
FROM
	( SELECT e.deptno, CEIL(avg( e.sal ) ) avg_sal FROM emp e GROUP BY e.deptno ) temp
	LEFT JOIN salgrade s ON (
		temp.avg_sal BETWEEN s.losal 
	AND s.hisal)

-- 查询部门的平均薪水 平均等级
SELECT temp.deptno,AVG(temp.grade) FROM
(SELECT e.deptno,e.sal,s.grade FROM emp e JOIN salgrade s ON (e.sal BETWEEN s.losal and s.hisal)) temp GROUP BY temp.deptno

-- 查询那些人是经理
SELECT ename FROM emp WHERE empno IN(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL);

-- 不用组函数查最高薪水
SELECT sal FROM emp WHERE sal NOT IN (select DISTINCT e1.sal FROM emp e1 JOIN emp e2 ON (e1.sal < e2.sal));

-- 创建视图 mysql 视图不支持select语句的嵌套
CREATE VIEW emp_view as SELECT ename FROM emp;

-- 查询视图
SELECT * FROM emp_view;

-- 修改视图的数据，这里会影响到我们原来的表 
UPDATE emp_view SET ename = '%%%%' WHERE ename = '%MILLER%'

-- 删除视图
DROP VIEW IF EXISTS emp_view;

-- 查询薪资比普通员工最高薪水高的经理人 
-- 先查出经理人的编号 在查出不是经理人的员工的最大工资 ,连接条件
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	empno IN (
	SELECT
		empno 
	FROM
		emp 
	WHERE
	empno IN ( SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL )) 
	AND sal > (
	SELECT
		MAX( sal ) max_sal 
	FROM
		emp 
	WHERE
	empno NOT IN ( SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL ))