短实训-数据库基础（一）

**

1创建表的语法：

**

CREATE TABLE STUDENT(
    SID NUMBER NOT NULL PRIMARY KEY,
    STUNAME VARCHAR(30) NOT NULL,
    STUAGE NUMBER ,
    ADDRESS VARCHAR(100），
    TEL NUMBER
);

2.插入信息

INSERT INTO STUDENT(SID,STUNAME,STUAGE,ADDRESS,TEL)VALUES(1,'张三',20,'天津空港',17602684398)

3.插入指定信息

INSERT INTO STUDNET(SID,STUNAME,TEL)VALUES(2,'李四',82374879328)

4.查询信息

SELECT * FROM STUDENT

5.修改表

添加列
ALTER TABLE XX ADD(CLASS NUMBER)
修改列
ALTER TABLE 表名 MODIFY(列名VARCHAR(50) )
删除列
ALTER TABLE 表名 DROP(列名)

6.删除表

DROP TABLE 表名

7.重命名表

RENAME 旧表名 TO 新表名

8.DML语言

EMP表

DEPT表

数据操作语言INSERT,UPDATE,DELETE

UPDATE 表名 SET 列名 = 新值 WHERE 列名=XXX
DELETE FROM 表名 WHERE 列名 = XXX

练习
1.查询职位为SALESMAN的员工编号、经理编号, 工作地点。

SELECT EMPNO,MGR,LOC FROM EMP,DEPT WHERE JOB = 'SALESMAN' AND EMP.DEPTNO=DEPT.DEPTNO

2.查询部门编号不在10部门的员工姓名、部门编号。

SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO <> 10

9.操作运算符

连接符||

SELECT ENAME || JOB EMPLY FROM EMP

消除重复行

SELECT DISTINCT DEPTNO FROM EMP

*练习
查询工资在1000到1500之间的员工姓名，部门编号以及工资*

SELECT ENAME DEPTNO,SAL FROM EMP 
WHERE SAL>=1000 AND SAL<=1500 

----------

SELECT ENAME DEPTNO,SAL FROM EMP 
WHERE SAL BETWEEN 1000 AND 1500

查询员工编号在7369 7521 和7698的员工编号，姓名，奖金

SELECT EMPNO,ENAME,COMM FROM EMP
 WHERE EMPNO IN(7369,7521,7698)

查询名字中包含S的员工姓名，编号，工作

SELECT ENAME,EMPNO,JOB FROM EMP 
WHERE ENAME LIKE '%S%'

查询员工姓名以W开头的员工姓名

SELECT ENAME FROM EMP 
WHERE ENAME LIKE 'W%'

查询员工姓名倒数第二个字符为T的员工姓名

SELECT ENAME FROM EMP 
WHERE ENAME LIKE '%T_'

查询奖金为空的员工姓名，奖金

SELECT ENAME COMM FROM EMP 
WHERE COMM IS NULL

查询工资超过2000,并且职位是MANAGER，或者职位是SALESMAN的员工姓名，职位，工资

SELECT ENAME,JOB,SAL FROM EMP 
WHERE SAL > 2000 AND JOB= 'MANAGER' OR JOB = 'SALESMAN'

查询工资超过2000并且职位是MANAGER或SALESMAN的员工姓名，职位，工资

S1ELECT ENAME,JOB,SAL FROM EMP 
WHERE SAL >2000 AND JOB IN ('MANAGER','SALESMAN')

查询部门在10或者20，并且工资在3000到5000之间的员工姓名，部门，工资

SELECT ENAME,DEPTNO,SAL FROM EMP 
WHERE DEPTNO IN (10,20) AND SAL BETWEEN 3000 AND 5000

查询职位为SALESMAN或MANAGER，部门编号为10或者20并且姓名包含A的员工姓名，职位，部门编号

SELECT ENAME,JOB,DEPTNO FROM EMP 
WHERE JOB IN ('SALESMAN','MANAGER') AND DEPTNO IN (10,20) AND ENAME LIKE '%A%'

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

SELECT ENAME,DEPTNO,SAL FROM EMP 
WHERE DEPTNO IN (20,30) ORDER BY SAL

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

SELECT ENAME,DEPTNO,SAL FROM EMP 
WHERE SAL BETWEEN 2000 AND 3000 AND DEPTNO <> 10 ORDER BY DEPTNO,SAL DESC

10.外部链接（+）

查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示

SELECT ENAME,E.DEPTNO ,DNAME FROM EMP E ,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO

11.自连接

查询每个员工姓名和直接上级名称

SELECT E.ENAME 员工姓名，M.ENAME 经理姓名 FROM EMP E ,EMP M WHERE E.MGR = M.EMPNO

*练习
1.查询所有工作在NEWYORK 和 CHICAGO 的员工姓名 ，员工编号，以及他们的经理姓名，经理编号*

SELECTE E.ENAME 员工姓名,E.EMPNO 员工编号,M.ENAME 经理姓名 ,M.EMPNO 经理编号,LOC FROM EMP E,EMP M,DEPT D 
WHERE E.DEPTNO = D.DEPTNO AND E.MGR = M.EMPNO AND LOC IN ('NEWYORK' , 'CHICAGO')

2.上一题基础上，添加没有经理的员工King，并按照员工编号排序

SELECTE E.ENAME 员工姓名,E.EMPNO 员工编号,M.ENAME 经理姓名 ,M.EMPMO 经理编号,LOC FROM EMP E,EMP M,DEPT D 
WHERE E.DEPTNO = D.DEPTNO AND E.MGR = M.EMPNO(+) AND LOC IN ('NEWYORK' , 'CHICAGO') ORDER BY E.EMPNO

3.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来
略

12.笛卡尔积

查询员工姓名，编号，工作地点

SELECT ENAME,EMPNO,LOC FROM EMP,DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO

13.交叉连接（两个表的乘积）cross join

SELECT ENAME,EMPNO,LOC FROM EMP CROSS JOIN DEPT

14.自然连接 NATURAL join

SELECT ENAME,EMPNO,LOC FROM EMP NATURAL JOIN DEPT

15.using子句指定连接条件

查询部门编号为20的员工姓名，部门名称以及工资

SELECT ENAME,DNAME,SAL FROM EMP 
JOIN DEPT USING(DEPTNO) 
WHERE DEPTNO = 20

16.on子句指定连接条件

SELECT ENAME,DNAME,LOC FROM EMP 
JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO 
WHERE EMP.DEPTNO = 20

练习
1.使用using子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点

SELECT ENAME,DNAME,LOC FROM EMP 
JOIN DEPT USING(DEPTNO) 
WHERE LOC = 'CHICAGO'

2.使用On子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级

SELECT ENAME,DNAME,LOC FROM EMP 
JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO 
WHERE LOC = 'CHICAGO'

3.使用左连接，查询每个员工的姓名，经理姓名，没有经理的KING也要显示出来

SELECT E.ENAME,M.ENAME FROM EMP E 
LEFT OUTER JOIN EMP M ON E.MGR = M.EMPNO

4.使用右连接，查询每个员工的姓名，经理姓名，没有经理的KING也要显示出来

SELECT E.ENAME,M.ENAME FROM EMP E 
RIGHT OUTER JOIN EMP M ON E.MGR = M.EMPNO

17.课后作业

1.显示员工SMITH的姓名，部门名称，直接上级名称

SELECT E.ENAME,DNAME,M.ENAME FROM EMP E,EMP M, DEPT D 
WHERE E.MGR = M.EMPNO AND E.DEPTNO = D.DEPTNO AND E.ENAME = 'SMITH'

2.显示员工KING和FORD管理的员工姓名及其经理姓名。

SELECT E.ENAME , M.ENAME FROM EMP E,EMP M 
WHERE E.MGR = M.EMPNO AND (M.ENAME = 'KING' OR M.ENAME = 'FORD')

3.查询员工姓名的第三个字母是a的员工姓名。

SELECT ENAME FROM EMP 
WHERE ENAME LIKE '__A%'

4.查询除了10、20号部门以外的员工姓名、部门编号。

SELECT ENAME,DEPTNO FROM EMP 
WHERE DEPTNO <> 10 AND DEPTNO <> 20

5.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。

SELECT ENAME,DEPTNO,SAL FROM EMP 
WHERE DEPTNO =30 
ORDER BY SAL DESC,ENAME ASC

6.查询没有上级的员工(经理号为空)的员工姓名。

SELECT ENAME FROM EMP 
WHERE MGR IS NULL

7.查询工资大于等于4500并且部门为10或者20的员工的姓名，工资、部门编号。

SELECT ENAME,SAL,DEPTNO FROM EMP 
WHERE SAL >= 4500 AND DEPTNO IN (10,20);

8.写一个查询，显示所有员工姓名，部门编号，部门名称。

SELECT ENAME,DEPTNO,DNAME FROM EMP,DEPT AND EMP.DEPTNO = DEPT.DEPTNO

9.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

SELECT E.ENAME,LOC,E.COMM FROM EMP,DEPT 
WHERE EMP.COMM<>0 AND DEPT.DEPTNO=EMP.DEPTNO

10.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

SELECT E.ENAME,LOC FROM EMP 
JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO 
WHERE EMP.ENAME LIKE '%A%'

2017.9.6