mysql的 表的创建以及 按条件查询（排序 分组 左右链接 子查询）

-- 8.25 作业
-- 第一题


CREATE TABLE tutors (
    id INT PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    title VARCHAR(255),
    researchfield VARCHAR(255)
);

CREATE TABLE students (
    id INT PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    gender VARCHAR(10),
    admissiondate DATE,
    tutor_id INT,
    FOREIGN KEY (tutor_id) REFERENCES Tutors(id)
);
-- 插入导师信息
INSERT INTO tutors (id, NAME, title, researchfield) VALUES
(1, '张伟', '教授', '计算机科学'),
(2, '王伟', '副教授', '数据分析'),
(3, '李伟', '助理教授', '网络安全'),
(4, '刘洋', '教授', '量子物理'),
(5, '张敏', '教授', '生物工程');
INSERT INTO tutors (id, NAME, title, researchfield) VALUES
(6, '诸葛孔明', '教授', '计算机科学');

-- 插入研究生信息
INSERT INTO students (id, NAME, gender, admissiondate, tutor_id) VALUES
(101, '王芳', '女', '2021-09-01', 1),
(102, '李娜', '女', '2020-01-15', 2),
(103, '张涛', '男', '2021-03-01', 3),
(104, '刘超', '男', '2021-07-10', 1),
(105, '李静', '女', '2020-09-05', 4),
(106, '王静', '女', '2019-02-20', 5),
(107, '刘阳', '男', '2018-11-15', 2),
(108, '张艳', '女', '2020-06-06', 3),
(109, '李勇', '男', '2021-05-10', 4),
(110, '王艳', '女', '2019-09-09', 5);
INSERT INTO students (id, NAME, gender, admissiondate, tutor_id) VALUES (111, '王艳', '女', '2019-09-09', 5);






-- 1.请查出每个导师所带研究生的姓名。
SELECT  t.name '导师姓名',s.`NAME` '学生姓名'
FROM tutors t, students s
WHERE s.`tutor_id` = t.`id`
ORDER BY t.`NAME`;

-- 2.清查出特定姓名的导师所带研究生的姓名。
SELECT  t.name '导师姓名',s.`NAME` '学生姓名'
FROM tutors t, students s
WHERE s.`tutor_id` = t.`id` AND t.`NAME` = '刘洋';


-- 3.请查出每个导师所带研究生的数量。
SELECT  t.name '导师姓名',COUNT(s.`id`) '所带学生数量'
FROM tutors t
LEFT JOIN students s
ON s.`tutor_id` = t.`id`
GROUP BY t.`id`;


-- 4.请查出每个导师所带的男研究生的数量。

SELECT  t.name '导师姓名',COUNT(s.`id`) '所带学生数量'
FROM tutors t 
LEFT JOIN students s
ON s.`tutor_id` = t.`id` AND s.`gender` = '男'
GROUP BY t.`id`;


-- 5.请找出选择哪个研究方向的导师最多。
SELECT  t.`researchfield` '研究方向',COUNT(t.`id`) '老师数量'
FROM tutors t
GROUP BY t.`researchfield`
ORDER BY  COUNT(t.`id`) DESC
LIMIT 1;


-- 6.请找统计不同职称的导师的个数。

SELECT title,COUNT( id)
FROM tutors
GROUP BY title;




-- 第二题

CREATE TABLE dept 
( 
deptno VARCHAR(10) PRIMARY KEY, 
dname VARCHAR(10) 
); 


CREATE TABLE emp 
( 
empno VARCHAR(10) PRIMARY KEY, 
ename VARCHAR(10), 
job VARCHAR(10), 
mgr VARCHAR(10), 
sal FLOAT, 
deptno VARCHAR(10) REFERENCES dept(deptno) 
); 

 
INSERT INTO dept VALUES ('1','事业部'); 
INSERT INTO dept VALUES ('2','销售部'); 
INSERT INTO dept VALUES ('3','技术部'); 
 
INSERT INTO emp VALUES ('01','jacky','clerk','tom','1000','1'); 
INSERT INTO emp VALUES ('02','tom','clerk','','2000','1'); 
INSERT INTO emp VALUES ('07','biddy','clerk','','2000','1'); 
INSERT INTO emp VALUES ('03','jenny','sales','pretty','600','2'); 
INSERT INTO emp VALUES ('04','pretty','sales','','800','2'); 
INSERT INTO emp VALUES ('05','buddy','jishu','canndy','1000','3'); 
INSERT INTO emp VALUES ('06','canndy','jishu','','1500','3'); 
SELECT * FROM dept; 
SELECT * FROM emp;


 
-- 1 列出emp表中各部门的部门号，最高工资，最低工资

SELECT deptno  '部门号' , MIN(sal)  '最低工资',MAX(sal)  '最高工资'
FROM  emp 
GROUP BY deptno;



 
-- 2  列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资

SELECT deptno '部门号' , MIN(sal)  '最低工资',MAX(sal)  '最高工资'
FROM emp 
WHERE job = 'CLERK'
GROUP BY deptno;
 
-- 3 对于emp中最低工资小于2000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资 
SELECT deptno '部门号' , MIN(sal)  '最低工资',MAX(sal)  '最高工资'
FROM emp 
WHERE job = 'CLERK'  
GROUP BY deptno
HAVING MIN(sal)<2000;

 
-- 4 根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资
SELECT ename '员工姓名', deptno  '部门号' , sal '工资'
FROM emp 
ORDER BY deptno DESC ,sal;


 
-- 5 列出'buddy'所在部门中每个员工的姓名与部门号
SELECT ename '员工姓名', deptno  '部门号' 
FROM emp 
WHERE deptno = (
	SELECT deptno
	FROM emp
	WHERE ename = 'buddy'
	);


 
-- 6 列出每个员工的姓名，工作，部门号，部门名

SELECT e.`ename` '员工姓名',e.`job` '工作', d.`deptno`AS '部门号' , d.`dname` '部门名'
FROM dept d 
LEFT JOIN emp e
ON e.`deptno` = d.`deptno`

-- 7 列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名
SELECT e.`ename` '员工姓名',e.`job` '工作', d.`deptno`AS '部门号' , d.`dname` '部门名'
FROM dept d 
LEFT JOIN emp e
ON e.`deptno` = d.`deptno`
WHERE e.`job` = 'CLERK';


-- 8 对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）
SELECT ename '姓名', mgr '管理者姓名'
FROM emp
WHERE mgr !='' ;



 
-- 9 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
SELECT d.`deptno`AS '部门号' , d.`dname` '部门名',e.`ename` '员工姓名',e.`job` '工作'
FROM dept d  
LEFT JOIN  emp e  
ON e.`deptno` = d.`deptno` AND e.`job` = 'CLERK'
ORDER BY d.`deptno`;

 
-- 10 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
SELECT e.deptno '部门号', e.ename '姓名' , e.sal '工资'
FROM emp e
WHERE sal > (
	SELECT AVG(sal)
	FROM emp e2
	WHERE e.`deptno` = e2.`deptno` )
ORDER BY e.deptno;

-- 11 对于emp，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排序

SELECT e.deptno '部门号', COUNT(e.`empno`) '员工数量' 
FROM emp e
WHERE sal > (
	SELECT AVG(sal)
	FROM emp e2
	WHERE e.`deptno` = e2.`deptno` )
GROUP BY e.deptno
ORDER BY deptno;