数据库学习

1.简单查询 2022-07-13

测试脚本：

drop TABLE emp;
drop TABLE dept;
drop TABLE salgrade;
create table dept
(
deptno integer(4) not null,
dname varchar(14),
loc varchar(13)
);
alter table dept add constraint pk_dept primary key (deptno);
create table emp
(
empno integer(4) not null,
ename varchar(10),
job varchar(9),
mgr integer(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno integer(2)
);
alter table emp add constraint pk_emp primary key (empno);
alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno);
create table salgrade
(
grade integer(1),
losal decimal(7,2),
hisal decimal(7,2)
);
insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');
insert into SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);
-- 下面要特别注意，%d-%m-%Y 里的 d 和 m 一定要小写
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600.00, 300.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250.00, 500.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250.00, 1400.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('19-04-1987', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000.00, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500.00, 0.00, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('23-05-1987', '%d-%m-%Y'), 1100.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950.00, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000.00, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300.00, null, 10);

单元概述

通过本章的学习能够了解MySQL结构查询语言的概念，掌握SELECT查询语句的基本语法，掌握SELECT查询语句中过滤条件的使用，掌握过滤条件中比较运算符和逻辑运算符的使用，掌握查询结果的排序等

单元练习：

1. 查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
SELECT ename,hiredate,job
FROM emp
WHERE hiredate>'1982-7-9'
AND job<> 'SALESMAN';
2. 查询员工姓名的第三个字母是a的员工姓名。
SELECT ename
FROM emp
WHERE ename
LIKE '__a%';
3. 查询除了10、20号部门以外的员工姓名、部门编号。
SELECT ename,deptno
FROM emp
WHERE deptno
not in (10,20);
4. 查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。
SELECT *
FROM emp
WHERE deptno = 30
ORDER BY sal DESC, ename ASC;
5. 查询没有上级的员工(经理号为空)的员工姓名。
SELECT ename
FROM emp
WHERE mgr is null;
6. 查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。
SELECT ename,sal,deptno
FROM emp
WHERE sal > 4500
AND deptno in (10,20);

课后练习：

查询入职日期在82年至85年的员工姓名，入职日期。
查询月薪在3000到5000的员工姓名，月薪。
查询部门编号为10或者20的员工姓名，部门编号。
查询经理编号为7902, 7566, 7788的员工姓名，经理编号

SELECT ename,hiredate FROM emp WHERE hiredate BETWEEN '1982-01-01' AND '1985-12-31'

SELECT ename,sal FROM emp WHERE sal BETWEEN 3000 and 5000

SELECT ename,deptno FROM emp WHERE deptno IN (10,20)

SELECT ename,mgr FROM emp WHERE mgr in (7902,7566,7788)
1. 查询员工姓名以W开头的员工姓名。
2. 查询员工姓名倒数第2个字符为T的员工姓名。
3. 查询奖金为空的员工姓名，奖金。
  
  SELECT ename FROM emp WHERE ename LIKE 'W%'
  
  SELECT ename FROM emp WHERE ename LIKE '%T_'
  
  SELECT ename,comm FROM emp WHERE comm is null
1.查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资

SELECT ename,job,sal

FROM emp WHERE hiredate >2000AND job IN ('MANAGER','SALESMAN')
查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。

SELECT ename,hiredate,salSELECT ename,hiredate,salFROM emp WHERE deptno in (10,20)and sal BETWEEN 3000 and 5000

SELECT ename,hiredate,jobFROM emp WHERE hiredate LIKE '1981%'and job not like 'SALES%'

SELECT ename,job,deptnoFROM empWHERE job in ('SALESMAN','MANAGER')AND deptno IN (10,20)AND ename LIKE '%A%'AND ename LIKE '%A%'

1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。

SELECT ename,deptno FROM emp WHERE deptno IN (20,30) ORDER BY sal ASC

2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。

SELECT ename,deptno,sal FROM emp WHERE sal BETWEEN 2000 AND 3000 AND deptno <> 10 ORDER BY deptno ASC,sal DESC

3.查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序

SELECT ename,hiredate,job FROM emp WHERE hiredate BETWEEN '1982-01-01' AND '1982-12-31'AND (job like 'SALES%' or job like 'MAN%') ORDER BY hiredate DESC