SQL常用基础知识的应用示例.doc

-创建表CREATE TABLE DEPT (DID CHAR(3) PRIMARY KEY, DNAME VARCHAR(30) NOT NULL, ADR VARCHAR(50)CREATE TABLE EMP (EID CHAR(6) PRIMARY KEY, ENAME CHAR(30) NOT NULL, DID CHAR(3), SAL INT, FOREIGN KEY(DID)REFERENCES DEPT(DID)-删除表DROP TABLE EMPDROP TABLE DEPT-注意创建和删除表的顺序-插入数据INSERT INTO DEPTVALUES(001,销售部,青岛)INSERT INTO DEPTVALUES(002,科技部,北京)INSERT INTO DEPTVALUES(003,生产部,上海)INSERT INTO EMPVALUES(0001,张三,001,2000)INSERT INTO EMPVALUES(0002,李四,001,2500)INSERT INTO EMPVALUES(0011,王五,002,3000)INSERT INTO EMPVALUES(0012,赵六,002,2800)INSERT INTO EMPVALUES(0013,老七,002,3200)INSERT INTO EMPVALUES(0031,小八,003,2600)INSERT INTO EMP(EID,ENAME,DID)VALUES(0032,小久,003)INSERT INTO EMP(EID,ENAME,SAL)VALUES(0000,老大,10000)-结果：-查询SELECT * FROM DEPTSELECT * FROM EMP-1SELECT *FROM EMPWHERE SALBETWEEN 2500 AND 3200-2SELECT *FROM EMPWHERE SAL=2500 AND SAL3200-注意1和2的区别SELECT ENAME 姓名,SAL 工资FROM EMPWHERE EID LIKE 001%-多表查询SELECT * FROM EMP,DEPT-笛卡尔积SELECT E.ENAME,D.DNAME,E.DIDFROM EMP E,DEPT DWHERE E.DID=D.DID-等值连接SELECT * FROM EMP ELEFT JOIN DEPT D-左外连接ON E.DID=D.DIDSELECT * FROM EMP ERIGHT JOIN DEPT D-右外连接ON E.DID=D.DIDSELECT * FROM EMP EFULL JOIN DEPT D-全外连接ON E.DID=D.DID-单行函数SELECT SUM(SAL) 总工资FROM EMPGROUP BY DIDHAVING DID=002;SELECT AVG(SAL) 平均工资FROM EMPSELECT MAX(SAL) 最高工资FROM EMPSELECT MIN(SAL) 最低工资FROM EMPSELECT COUNT(*) 总员工数FROM EMP;-添加主键，主键值应为非空CREATE TABLE C (CID CHAR(3) NOT NULL)ALTER TABLE C ADD PRIMARY KEY(CID)-创建和删除索引CREATE INDEX EMP_IDX ON EMP(EID)DROP INDEX EMP.EMP_IDX-创建和删除视图CREATE VIEW EMP_VIEWAS(SELECT * FROM EMP)DROP VIEW EMP_VIEW-复制表，复制了表的结构但没复制表的约束-1SELECT * INTO BB FROM EMP WHERE 11-只复制表的结构SELECT * INTO BB FROM EMP-复制整个表，包括表的数据内容-2-拷贝表，只将表中的数据复制到另一个表中，对应的数据结构应相同，和1连用INSERT INTO BB(EID,ENAME)SELECT EID,ENAME FROM EMP-IN的使用SELECT EID,ENAMEFROM EMPWHERE DID IN(SELECT DID FROM DEPT)SELECT EID,ENAMEFROM EMPWHERE DID IN(001,002)SELECT EID,ENAMEFROM EMPWHERE DID NOT IN(001,002)OR DID IS NULL-ORDER BY 的使用