【MySQL】SQL语法练习

SQL语法练习

sql资源

/*demo.sql*/


CREATE TABLE t_dept(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE t_emp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    salary DOUBLE,
    dept_id INT,
    manager_id INT,
    FOREIGN KEY(dept_id) REFERENCES t_dept(id),
    FOREIGN KEY(manager_id) REFERENCES t_emp(id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

INSERT INTO t_dept (id,name) VALUES (10,'accounting');
INSERT INTO t_dept (id,name) VALUES (20,'research');
INSERT INTO t_dept (id,name) VALUES (30,'sales');
INSERT INTO t_dept (id,name) VALUES (40,'operations');

INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7839, 'king', NULL, 5000, NULL);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7566, 'jones', 7839, 2975, 20);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7698, 'blake', 7839, 2850, 30);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7782, 'clark', 7839, 2450, 10);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7788, 'scott', 7566, 3000, 20);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7902, 'ford', 7566, 3000, 20);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7499, 'allen', 7698, 1600, 30);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7521, 'ward', 7698, 1250, 30);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7654, 'martin', 7698, 1250, 30);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7844, 'turner', 7698, 1500, 30);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7900, 'james', 7698, 950, 30);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7934, 'miller', 7782, 1300, 10);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7876, 'adams', 7788, 1100, 20);
INSERT INTO t_emp (id, name, manager_id, salary, dept_id) VALUES (7369, 'smith', 7902, 800, 20);

– ---------------------基础查询---------------------------------
– 1.查询 部门表中的所有数据

SELECT * FROM `t_dept`;

– 2.查询 所有员工的 姓名 和 薪资

SELECT name,salary FROM `t_emp`;

– ---------------------条件查询---------------------------------
– 3.查询 所有员工的 姓名 和 年薪

SELECT name,salary*12 as annual FROM `t_emp`;

– 4.查询 boss 的姓名（manager_id 为null 的）

SELECT name FROM `t_emp` WHERE manager_id IS NULL;

– 5.查询 所有薪资 低于2000 的员工

SELECT name FROM `t_emp`
WHERE manager_id IS NOT NULL and salary<2000;

– 6.查询 所有薪资 在 2000- 3000 之间的员工信息

SELECT * FROM `t_emp` 
WHERE manager_id IS NOT NULL and salary BETWEEN 2000 and 3000;

– 7.查询10号部门的所有的员工信息和 老板的信息

SELECT * FROM `t_emp` 
WHERE dept_id =10 or dept_id is null ;

– ---------------------ifnull---------------------------------
– 8 查询所有员工的姓名和 部门编号，没有部门 ， 显示 BOSS

SELECT name,IFNULL(dept_id,'BOSS') FROM `t_emp`  ;

– ---------------------distinct---------------------------------
– 9 查询员工分布在哪些部门

SELECT distinct dept_id FROM `t_emp`  ;

– ---------------------作业---------------------------------
– 10. 查询20号部门所有的员工姓名和薪资

SELECT name,salary FROM `t_emp` WHERE dept_id = 20  ;

– 11.查询所有年薪>40000的员工姓名和部门编号，如果为null ，显示BOSS

SELECT name,IFNULL(dept_id,'BOSS')
FROM `t_emp` WHERE salary*12>40000 ;

– 12.查询所有月薪 > 4000 或者 月薪 < 3000 的员工姓名

SELECT name FROM `t_emp` 
WHERE salary>4000 OR salary <3000 ;

– 13.查询10号部门和20号部门的所有员工的姓名（2种方式）

SELECT name FROM `t_emp` 
WHERE dept_id =10 OR dept_id =20 ;

SELECT name FROM `t_emp` WHERE dept_id in (10,20);

– 14.查询除了10号部门和20号部门的所有员工的姓名（2种方式：此题有坑）

SELECT name FROM `t_emp` 
WHERE dept_id NOT IN (10,20);

SELECT name FROM `t_emp` 
WHERE dept_id !=10 AND dept_id !=20 AND dept_id IS NOT NULL;

– ---------------------order by-----------------------
– 15.查询员工的年薪，ResultSet中显示的名字是 salary of year，按照降序排序

SELECT salary*12 AS 'salary of year' 
FROM `t_emp` WHERE  dept_id IS NOT NULL
ORDER BY salary DESC;

– 16.查询员工的姓名和部门编号，按照部门编号降序排序

SELECT name,dept_id FROM `t_emp` 
WHERE  dept_id IS NOT NULL 
ORDER BY dept_id DESC;

– --------------------------常用函数---------------------------
– 17.修改所有员工的姓名：前面追加e_ , 后面追加 id 值

UPDATE t_emp
SET name = CONCAT('e_',`name`,`id`) 
WHERE dept_id IS NOT NULL;

– --------------------------组函数---------------------------
– 18. 求20号部门的最高薪资

SELECT MAX(salary) FROM t_emp WHERE dept_id =20;

– 19. 求10号部门的最低薪资

SELECT Min(salary) FROM t_emp WHERE dept_id =10;

– 20. 求员工的平均薪资

SELECT avg(salary) FROM t_emp WHERE dept_id is not null;

– 21. 求员工的薪资总和

SELECT sum(salary) FROM t_emp WHERE dept_id is not null;

– 22. 求30号部门的员工数量

SELECT COUNT(id) FROM t_emp WHERE dept_id =30;

– 23. 求所有员工的部门的数量

SELECT COUNT(DISTINCT dept_id) 
FROM t_emp WHERE dept_id IS NOT NULL;

– 24. 将7934号员工的薪资 修改为 null

UPDATE t_emp
set salary = null WHERE id = 7934;
SELECT * FROM t_emp;

– 25. 求10号部门的平均薪资，null 认为薪资为0

SELECT AVG(IFNULL(salary,0)) FROM t_emp WHERE dept_id = 10;

– --------------------------子查询---------------------------
– 26. 找出所有allen 的同部门同事

SELECT * FROM t_emp 
WHERE dept_id =( SELECT dept_id FROM t_emp WHERE name = 'allen');

– 27. 找出所有的领导的姓名

SELECT name FROM t_emp WHERE	dept_id IS NULL;

– 28. 找出所有 不是领导的 员工姓名

SELECT name FROM t_emp WHERE	dept_id IS NOT NULL;

– 29. 找出所有没有员工的部门

SELECT d.id 
FROM t_dept d 
LEFT JOIN t_emp e ON d.id = e.dept_id 
WHERE	e.dept_id IS NULL;

– 30. 找出所有工资比martin高的员工

SELECT name FROM t_emp 
WHERE salary > (SELECT salary FROM t_emp WHERE name ='martin');

– 31. 找出各个部门工资最低的员工姓名和薪资

SELECT name,salary FROM t_emp e WHERE salary = (
SELECT MIN(salary) FROM t_emp WHERE dept_id = e.dept_id);

– 32. 找出人数最多的部门的部门名称

SELECT name
FROM t_dept
WHERE id = (
    SELECT dept_id
    FROM (
        SELECT dept_id, COUNT(*) AS employee_count
        FROM t_emp
        GROUP BY dept_id
    ) AS subquery_dept_counts
    ORDER BY employee_count DESC
    LIMIT 1
);

– 33.找出那些部门的平均薪资比research部门的平均薪资高，需要平均薪资和部门编号，按照平均薪资降序排序

SELECT
	dept_id,
	AVG(
	IFNULL( salary, 0 )) AS avs 
FROM
	t_emp 
WHERE
	dept_id IS NOT NULL 
GROUP BY
	dept_id 
HAVING
	AVG(
	IFNULL( salary, 0 )) > ( SELECT AVG( IFNULL( salary, 0 )) FROM t_emp WHERE dept_id = ( SELECT id FROM t_dept WHERE NAME = 'research' ) ) 
ORDER BY
	avs DESC;