dql 数据库查询语言。基本语法为
select 字段1,字段2...from 表名 where 条件名
表连接 外连接,内连接(需要外键),外连接,以及like,limit,as 等关键字的应用
举列
##部门表
#DROP IF EXISTS TABLE DEPT;
CREATE TABLE DEPT(
DEPTNO int PRIMARY KEY,##部门编号
DNAME VARCHAR(14) , ##部门名称
LOC VARCHAR(13) ##部门地址
) ;
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');
##员工表
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
EMPNO int PRIMARY KEY, #员工编号
ENAME VARCHAR(10), #员工姓名
JOB VARCHAR(9), #员工工作
MGR int, #员工直属领导编号
HIREDATE DATE, #入职时间
SAL double, #工资
COMM double, #奖金
DEPTNO int #对应dept表的外键
);
## 添加 部门 和 员工 之间的主外键关系
ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO);
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);
#工资等级表
#DROP IF EXISTS TABLE SALGRADE;
CREATE TABLE SALGRADE(
GRADE int, #等级
LOSAL double, #最低工资
HISAL double ); #最高工资
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
查询语句
SELECT * from dept;
-- 部门编号,名称,地址
SELECT * from emp;
-- 员工编号,姓名,工作,直属领导,入职时间,工资,奖金,对应dept外键
SELECT * from salgrade;
-- 等级,最低工资,最高工资
-- 1. 返回拥有员工的部门名、部门号。
SELECT DEPTNO,DNAME from dept d WHERE d.DEPTNO in (SELECT DISTINCT DEPTNO from emp);
-- 2. 工资水平多于smith的员工信息。
SELECT * from emp e2 WHERE e2.SAL>(SELECT sal from emp e WHERE e.ENAME ='smith');
-- 3. 返回员工和所属经理的姓名。
SELECT e1.ENAME ,e2.ENAME from emp e2 join emp e1 on e2.EMPNO = e1.MGR ;
-- 4. 返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
SELECT e1.ENAME ,e2.ENAME from emp e2 join emp e1 on e2.EMPNO = e1.MGR WHERE e1.HIREDATE <e2.HIREDATE;
-- 5. 返回员工姓名及其所在的部门名称。
SELECT e.ENAME,d.DNAME from emp e join dept d on e.DEPTNO = d.DEPTNO;
-- 6. 返回从事clerk工作的员工姓名和所在部门名称。
SELECT e.ENAME,d.DNAME from emp e join dept d on e.DEPTNO = d.DEPTNO WHERE e.JOB = 'clerk';
-- 8. 返回销售部(sales)所有员工的姓名。
SELECT e.ename from emp e JOIN dept d on e.DEPTNO=d.DEPTNO WHERE d.DNAME = 'sales';
-- 9. 返回工资水平多于平均工资的员工。
SELECT * from emp e WHERE SAL > (SELECT AVG(sal) from emp);
-- 10. 返回与SCOTT从事相同工作的员工。
SELECT * from emp WHERE JOB = (SELECT JOB from emp WHERE ENAME = 'scott');
SELECT * from emp WHERE JOB = (SELECT JOB from emp WHERE ENAME = 'scott') AND ENAME !='scott';
-- 11. 返回与30部门员工工资水平相同的员工姓名与工资。
SELECT e1.ENAME,e1.SAL from emp e1 WHERE e1.SAL in (SELECT SAL from emp WHERE DEPTNO=30);
-- 12. 返回工资高于30部门所有员工工资水平的员工信息。 SELECT e1.ENAME,e1.SAL from emp e1 WHERE e1.SAL > (SELECT max(SAL) from emp WHERE DEPTNO=30);
-- 13. 返回部门号、部门名、部门所在位置及其每个部门的员工总数。
SELECT d.DEPTNO,DNAME,LOC,count(EMPNO) from emp e join dept d on d.DEPTNO=e.DEPTNO GROUP BY DEPTNO;
-- 14. 返回员工的姓名、所在部门名及其工资。
SELECT e.ENAME,d.DNAME,e.SAL FROM emp e join dept d on e.DEPTNO=d.DEPTNO;
-- 15. 返回员工的详细信息。(包括部门名)
SELECT * from emp e JOIN dept d on e.DEPTNO=d.DEPTNO;
-- 16. 返回员工工作及其从事此工作的最低工资。
SELECT JOB, min(sal) from emp GROUP BY JOB;
-- 17. 计算出员工的年薪,并且以年薪排序。
SELECT SAL*12 from emp ORDER BY SAL;
-- 18. 返回工资处于第四级别的员工的姓名。
SELECT e.ENAME from emp e WHERE SAL BETWEEN (SELECT LOSAL from salgrade WHERE GRADE = 4) AND (SELECT HISAL from salgrade WHERE GRADE = 4);
-- 19. 工资等级多于smith的员工信息。
SELECT * from emp WHERE SAL > (SELECT HISAL FROM salgrade WHERE GRADE = (SELECT GRADE FROM salgrade s WHERE s.LOSAL<(SELECT SAL FROM emp WHERE ENAME = 'smith')AND(SELECT SAL FROM emp WHERE ENAME = 'smith')<HISAL))