資料庫學習 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 va ...
資料庫學習
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);
課後練習：
-
-
查詢月薪在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)
-
查詢員工姓名以W開頭的員工姓名。
-
查詢員工姓名倒數第2個字元為T的員工姓名。
-
查詢獎金為空的員工姓名，獎金。
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
1.查詢入職日期最早的前5名員工姓名，入職日期
SELECT ename,hiredate FROM emp ORDER BY hiredate ASC LIMIT 0,5;
2.查詢20號部門下入職日期最早的前2名員工姓名，入職日期。
SELECT ename,hiredate FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.deptno=20 ORDER BY hiredate ASC LIMIT 0,2;
3.按照每頁顯示5條記錄，分別查詢第1頁，第2頁，第3頁信息，要求顯示員工姓名、入職日期、部門編號 。
SELECT ename,hiredate,deptno FROM emp LIMIT 0,5
SELECT ename,hiredate,deptno FROM emp LIMIT 5,5
SELECT ename,hiredate,deptno FROM emp LIMIT 10,5
