mysql命令回顾

CREATE TABLE emp(
    empno        INT,
    ename        VARCHAR(50),
    job        VARCHAR(50), 
    mgr        INT, -- 上级领导编号
    hiredate    DATE,-- 入职日期
    sal        INT,
    comm        INT, -- 奖金
    deptno        INT  --  部门编号
) ;

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

-- 1、按员工编号升序排列不在10号部门工作的员工信息
SELECT *
FROM emp
WHERE deptno NOT IN(10)
ORDER BY empno ASC
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列
SELECT *
FROM emp
WHERE NAME NOT LIKE '_A%' AND sal > 800
ORDER BY sal DESC
-- 3、求每个部门的平均薪水
SELECT AVG(sal)
FROM emp
GROUP BY deptno
-- 4、求各个部门的最高薪水
SELECT MAX(sal)
FROM emp
GROUP BY deptno
-- 5、求每个部门每个岗位的最高薪水
SELECT MAX(sal)
FROM emp
GROUP BY deptno,job
-- 6、求平均薪水大于2000的部门编号
SELECT deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000
-- 7、将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
SELECT deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000
ORDER BY AVG(sal) DESC
-- 8、求最高薪水的员工信息
SELECT *
FROM emp
HAVING sal = MAX(sal)
-- 9、求大于平均薪水的员工信息
SELECT *
FROM emp
HAVING sal > AVG(sal)1

第一题：

1. 创建学生students表信息如下:（创建表格+插入数据）

studentNo	name	sex	hometown	age	class_id	card
1	王昭君	女	北京	20	1	340322199001247654
2	诸葛亮	男	上海	18	2	340322199002242354
3	张飞	男	南京	24	3	340322199003247654
4	白起	男	安徽	22	4	340322199005247654
5	大乔	女	天津	19	3	340322199004247654
6	孙尚香	女	河北	18	1	340322199006247654
7	百里玄策	男	山西	20	2	340322199007247654
8	小乔	女	河南	15	3	NULL
9	百里守约	男	湖南	21	1
10	妲己	女	广东	26	2	340322199607247654
11	李白	男	北京	30	4	340322199005267754
12	孙膑	男	新疆	26	3	340322199000297655

库表创建：

create table students (
  studentNo int primary key auto_increment not null,
  name varchar(10),
  sex varchar(10),
  hometown varchar(20),
  age tinyint(4),
  class_id int not null,
  card varchar(20)
);
​
insert into students (name,sex,hometown,age,class_id,card)values
('王昭君', '女', '北京', 20, 1, '340322199001247654'),
('诸葛亮', '男', '上海', 18, 2, '340322199002242354'),
('张飞', '男', '南京', 24, 3, '340322199003247654'),
('白起', '男', '安徽', 22, 4, '340322199005247654'),
('大乔', '女', '天津', 19, 3, '340322199004247654'),
('孙尚香', '女', '河北', 18, 1, '340322199006247654'),
('百里玄策', '男', '山西', 20, 2, '340322199007247654'),
('小乔', '女', '河南', 15, 3, null),
('百里守约', '男', '湖南', 21, 1, ''),
('妲己', '女', '广东', 26, 2, '340322199607247654'),
('李白', '男', '北京', 30, 4, '340322199005267754'),
('孙膑', '男', '新疆', 26, 3, '340322199000297655');

1.查询学生"百里守约"或”百里玄策”的基本信息

SELECT *
FROM students
WHERE name IN ('百里守约','百里玄策')

2.查询姓"张"学生的姓名，年龄，班级

SELCET name,age,class_id
FROM students
WHERE name LIKE '张%'

3.查询姓"百"并且家乡是"山西"的学生信息

SELECT *
FROM students
WHERE name LIKE '百%' AND hometown IS '山西'

4.查询姓"孙"，但是家乡不是"河北"的学生信息

SELECT *
FROM students
WHERE name LIKE '孙%' AND hometown NOT IS '河北'

5.查询全部学生信息，并按照“性别”排序

SELECT *
FROM students
GROUP BY sex

6.查询1班学生中的最大年龄是多少

SELECT MAX(age)
FROM students
WHERE class_id = 1

7.统计2班男女生各有多少人

SELECT COUNT(*)
FROM students
WHERE class_id = 2
GROUP BY sex

8.统计年龄大于平均年龄的学生有多少个

SELECT COUNT(*)
FROM students
HAVING age > AVG(age)

1.部门表DEPT

1.1建表语句

CREATE TABLE dept(
    DEPTNO FLOAT(2) PRIMARY KEY comment '部门号',
    DNAME VARCHAR(14) comment '部门名称',
    LOC VARCHAR(13) comment '部门地址'
);

1.2数据

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'); 

2.员工表 EMP

2.1 建表语句

CREATE TABLE emp (
    EMPNO float(4)  PRIMARY KEY comment '员工编号',
    ENAME VARCHAR(10) comment '员工姓名',  --
    JOB VARCHAR(9) comment '员工职位',
    MGR float(4) comment '员工上级工号',
    HIREDATE DATE comment '生日',
    SAL float(7,2) comment '薪水',
    COMM float(7,2) comment '年终奖',
    DEPTNO float(2) comment '部门号' REFERENCES dept
);

2.2数据

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7902, 'FORD', 'ANALYST', 7566,'1981-12-02', 3000, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

3.基础查询 （25分）

• 3.1 查询年终奖不为空的所有员工的信息

  select *
  from emp
  where COMM NOT IS null

• 3.2 查询部门名称为“RESEARCH”的所有员工的全部信息 （连接查询）

  select *
  from dept,emp
  where dept.DEPTNO = emp.DEPTNO AND DNAME = 'RESEARCH'

• 3.3 查询每个部门的员工的平均薪资（连接查询）

  select AVG(SAL)
  from dept,emp
  where dept.DEPTNO = emp.DEPTNO
  GROUP BY emp.DEPTNO

4.复杂子查询 （25分）

• 4.1 列出薪资高于公司平均薪资但是没有年终奖的员工的全部信息

  select *
  from emp
  where COMM IS null AND sal > (
  select AVG(SAL)
  from emp)

• 4.2 列出薪资低于公司平均薪资的所有员工姓名、薪资。

  select ENAME,SAL
  from emp
  where sal < (
  select AVG(SAL)
  from emp)

• 4.3 列出与“FORD”员工上级相同的所有员工姓名、工作职位名称 。 （5分）

  select ENAME,JOB
  from emp
  where MGR IN(
  select MGR
  from emp
  where ENAME = 'FORD')

• 4.4 查询上级是“FORD”的所有员工的信息

  select *
  from emp
  where MGR IN(
  select EMPNO
  from emp
  where ENAME = 'FORD')