SQL语言的分类

窗体底端SQL语言的四大组成部分DQL DML DDL DCLSQL语言共分为四大类： DQL(数据查询语言),DML(数据操纵语言),DDL(数据定义语言),DCL(数据控制语言)。1 数据查询语言(DQL)v 查询语句的一般结构v 单表查询v 多表查询v 嵌套查询v 分组查询v 集合查询查询语句的一般结构 SELECT ALL|DISTINCT， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC；单表查询:无条件、有条件、排序无条件：SELECT中的可以是表中的列，也可以是表达式，包括算术表达式、字符串常数、函数等。（字符串用单引号定界）1. 使用算术表达式（+、-、*、/）Select empno,sal*0.8 from emp;2. 使用字符常量Select empno,Name is:, ename from emp;3.使用函数Select empno,UPPER（ ename） from emp;4. 改变列标题Select empno employeename,sal AS salary , job,dept from emp;5.使用连接字符串Select ename | : | 1 | month salary= | sal as monthy from emp 6.消除重复行 SQL select ALL ename from emp; SQL select DISTINCT ename from emp;有条件：WHERE常用的查询条件 查询条件谓词比较大小=, , =, =, 确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重条件AND，OR确定范围： select empno,ename,sal ,deptno from emp where deptno between 10 and 20;确定集合： select empno,ename,sal,deptno from emp where deptno in (10,30); 等于select empno,ename,sal,deptno from emp where deptno=10 or deptno=30;字符匹配：NOT LIKE 1. 可以是一个完整的字符串，也可以含有通配符的字符串。通配符包括%、_。2. %（百分号）代表任意长（长度为0）字符串。3. _（下划线）代表任意单个字符。 SQL select empno,ename,sal,deptno from emp where ename like S%;SQL select ename from emp where ename like _S%;空值：涉及空值查询时使用IS NULL或 IS NOT NULL，这里的IS不能用=替代多重条件:用逻辑运算符NOT、AND和OR来联结多个查询条件。优先级：NOT、AND、OR（用户可以用括号改变优先级）。 IN谓词实际上是多个OR运算的缩写。 SELECT的交互查询： 使用替代变量 Select * from emp where ename= &name 排序： ORDER BY 从句要放在 SELECT语句的最后. 表达式排序：SQL select empno,ename,sal，sal*12 from emp order by sal*12 desc; 多列排序: SQL select empno,deptno,sal from emp order by deptno asc,sal desc;显示：按部门号升序，相同部门号内的工资降序排列。 别名排序：SQL select empno,ename，sal*12 annsal from emp order by annsal desc;多表查询 连接条件写在 WHERE 从句中. 同一列名出现在多个表中时，列名前要加上表名来区分.连接类型： 交叉连接：笛卡尔积。 内连接：使用比较运算符进行表间某列或某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。内连接分为等值连接、非等值连接和自身连接。例： 查询所有工资大于2000的雇员的姓名和他的经理的名字。（自身连接例子）SQL select first.ename,second.ename,first.sal from emp first,emp second where first.sal 2000 and first.deptno =second.deptno and second.job = MANAGER; 结果 ENAME ENAME SAL - - - CLARK CLARK 2450 KING CLARK 5000 JONES JONES 2975 CLARK JONES 3000 SCOTT JONES 3000 BLAKE BLAKE 2850 外连接：外连接不仅要列出与连接条件相匹配的数据行，还要列出左表、右表或两个表中所有不符合查询条件的数据行。外连接分为左外连接、右外连接和全外连接。1） 用外连接可以看见通常不符合连接条件的行.2）外连接操作符： (+).外连接：与内连接相比，可以避免信息丢失现象。 嵌套查询v 概念 查询块：一个select-from-where语句称为一个查询块。 嵌套查询：将一个查询块嵌套在另一个查询块的where子句或having子句中的查询。子查询的结果用于建立主查询的查找条件。v 处理步骤 一般由里向外进行处理。v 注意 子查询可以有多层，所存取的表可以是父查询没有存取的表。 子查询中不能有ORDER BY子句。 子查询选出的记录不显示。单行嵌套查询示例查询所有工资高于Jones的雇员的姓名、工资等信息。SQL select ename,job,sal from emp where sal (select sal from emp where ename = JONES);结果ENAME JOB SAL- - -SCOTT ANALYST 3000KING PRESIDENT 5000CLARK ANALYST 3000l 子查询中使用组函数 l HAVING 从句中使用子查询 l 多行嵌套查询(子查询的结果是多行的)多值比较运算符 in、exists、not in、not exists之间的区别 all、any（some替代）、not in、in之间的区别来看下面这样一条SQL语句：select * from student where 20 all (select score from student)其中，(select score from student)返回的是所有分数的记录集只有当记录集中的所有分数都大于20后，才会执行前面的SQL语句。显然，任何一条记录都大于20.如果把它改成50，则查不到任何数据，因为只要有一条记录小于50,条件都不成立。但是，如果把all改面any就不一样了：select * from student where 50 any (select score from student)它的意思是，只要记录集中的记录有一条大于50,条件就成立。1 、ALL与ANY的关系就是，AND 与 OR 的关系。2 、ANY与SOME等价，据说搞这两个不同的词出来是为了迁就英语语法。例如，在用= ANY 的地方在（英语） 语法上就应该是= SOME。 some是新版本的SQL中取代any的关键字，用法基本一样。3、 IN 与= ANY等价 ，均表示，变量在（子查询）列表之中，即 a IN (table B) 表示 a = ANY B.b4、 NOT IN 与 ALL 等价，而不等于 ANY，前两者均表示，变量不在（子查询）列表之中，即 a NOT IN (table B) 表示 a ALL B.b。而如果a ANY B.b，则只要任意一个ba就true了。5、 IN 与 EXISTS 的性能区别主要来自，IN 会编列子查询的每行记录，然后再返回，而EXISTS 则只要遇到第一个满足条件的记录就马上返回。6 、NOT IN 与 NOT EXISTS 并不能完全等价，只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时才用NOT IN。例查询所有雇员的工资都不低于1000的部门的所有人员信息SQL select ename,sal,job from emp where deptno not in (select deptno from emp where sal select ename,sal,job from emp where deptno != all (select deptno from emp where sal select ename,sal,job from emp where deptno = any (select deptno from emp where sal between 4000 and 5000);结果? ENAME SAL JOB? - - -? CLARK 2450 MANAGER? KING 5000 PRESIDENT? MILLER 1300 CLERK例3查找那些雇员的部门号在部门表中没有的雇员的信息。SQL select ename, sal, job from emp where not exists (select * from dept where deptno = emp.deptno);常用统计函数（一般where后不用统计函数）v 函数AVG 和 SUM 应用于数值型v MIN 和 MAX 可用于任意类型.v 函数AVG 和 SUM 应用于数值型v COUNT(*) 返回符合条件的记录数.(包括含null的所有行数)v COUNT(expr) 返回的非NULL的行数.v 集合函数忽略列中的 null 值(COUTN除外).SQL SELECT AVG(comm) FROM emp;v 在组函数中使用 NVL函数，NVL 函数强制集合函数包括 null 值.SQL SELECT AVG(NVL(comm,0) FROM emp;总结：当NVL(comm,0)时，佣金值赋0值，显示结果为0，不空 当NVL(comm,-1)时，佣金值赋-1值，不显示结果，为空NVL 函数： 功能：把可能包含了空值的表达式1，转换成有实际意义的数据表达式2 。 格式：NVL(表达式1，表达式2） 作用的数据类型：可用于字符型、数值型、日期型. 注意事项：转换前后的类型必须一致： NVL(comm,0) NVL(hiredate,01-JAN-97) NVL(job,No Job Yet)分组查询（一般句中有每个、各个字样的要用到分组查询）Having后一般是统计函数,不能是emp.depno=dept.depno这样的等式常用统计函数常与分组查询一块用 在包含GROUP BY子句的查询语句中，SELECT子句后面的所有字段列表（除聚集函数外），均应该包含在GROUP BY 子句中。 在 GROUP BY 从句中使用多个列 SQL SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, jobv 使用 HAVING 从句选择满足条件分组v 执行次序： 对行进行分组. 对每组数据执行组函数. 返回符合 HAVING 从句的分组结果. WHERE与HAVING的区别v 作用对象不同。WHERE 作用于基本表或视图，从中选择满足条件的元组HAVING短语作用于组，从中选择满足条件的组。 v 例查询平均工资超过2000的部门，并按部门号进行排序v SQL SELECT deptno, avg(sal) from emp v GROUP BY deptno v HAVING AVG(SAL)2000 v ORDER BY deptno; 结果v DEPTNO AVG(SAL) 10 3725 20 2175集合查询：查询结果的集合运算v 并集（UNION ALL）v 交集（INTERSECT ALL） v 差集（MINUS ALL ）2 数据操纵语言数据操纵语言DML主要有三种形式：1) 插入：INSERT2) 更新：UPDATE3) 删除：DELETEv INSERT 语句 插入单个元组格式：INSERT INTO (,)VALUES (,);注意：1）在表定义时说明了NOT NULL的属性列不能取空值，否则会出错。2）如果INTO子句中没有指明任何列名，则新插入的纪录必须在每个属性列上均有值。3）指定列名时，列名顺序任意，列值与列名对应. 4） 字符型和日期型数据在插入时要加单引号。例SQL INSERT INTO DEPT VALUES (60,PRODUCTION,SAN FRANCISCO);SQL INSERT INTO DEPT(DNAME,DEPTNO) VALUES(TEST,70); 插入子查询结果 例向表中插入一条与SMITH的内容相同的记录，但姓名改为了FAN,雇员号改为了8000.SQL INSERT INTO EMP (EMPNO, ENAME, JOB, MGR ,HIREDATE, SAL, COMM, DEPTNO) SELECT 8000, FAN,JOB,MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP WHERE ENAME = SMITH;v UPDATE语句 修改某一个元组的值 格式：UPDATE SET =,=WHERE;例将雇员号为8000的雇员提升为部门20的经理，工资增加1000。.SQL UPDATE EMP SET JOB = MANAGER,SAL = SAL + 1000,DEPTNO = 20 WHERE EMPNO = 8000; 修改多个元组的值 例将部门号为20的所有的雇员的工资加10%.SQL UPDATE EMP SET SAL = SAL + 0.1*SAL WHERE DEPTNO = 20; 带子查询的修改语句 例将20号部门的所有雇员的工资都变为该部门平均工资加1000.SQL UPDATE EMP SET SAL = 1000 + (SELECT AVG (SAL) FROM EMP WHERE DEPTNO = 20) WHERE DEPTNO = 20;v DELETE语句 删除某一个元组的值 格式：DELETE FROM WHERE;例删除名字为FAN的雇员.SQL DELETE FROM EMP WHERE ENAME = FAN; 删除多个元组的值 例删除EMP表中的所有数据.SQL DELETE FROM EMP ； 带子查询的删除语句 例删除所有与雇员FAN相同部门的雇员信息。SQL DELETE FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = FAN);3 数据定义语言DDL数据定义语言DDL用来创建数据库中的各种对象-表、视图、索引、同义词、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER| | | | |表 视图 索引 同义词 簇创建表（CREATE）的一般格式：CREATE TABEL (列级完整性约束条件,列级完整性约束条件,);CREATE TABEL 的总结：1、实体完整性规则：主键（所有主属性）非空。2、参照完整性规则：不得引用不存在的外键（定义外键）。3、用户定义的约束（CHECK,UNIQUE,NOT NULL)。例 创建学生表SQL CREATE TABLE STUDENT(sno CHAR(5) primary key,sname CHAR(20) not null,ssex CHAR(2) CHECK(ssex IN(男,女) ,sage NUMBER(2) CHECK（sage between 16 and 20),Sclass CHAR(7) not null,);创建课程表SQL CREATE TABLE COURCE (CNO CHAR(4) PRIMARY KEY, CNAME CHAR(16) NOT NULL); 创建成绩表SQLCREATE TABLE score ( SNO CHAR(4)， CNO CHAR(16) ， SCORE1 NUMBER（5，2）， PRIMARY KEY（SNO，CNO)， FOREIGN KEY（SNO） REFERENCES STUDENT（SNO）， FOREIGN KEY（CNO） REFERENCES COURCE（CNO）；例创建新表，结构与STUDENT相同，并拷贝数据。SQL CREATE TABLE Student_COPY AS SELECT * FROM STUDENT;例创建新表，结构与STUDENT相同，不拷贝数据。SQL CREATE TABLE Student_COPY AS SELECT * FROM STUDENT where1=2;v 基本表的修改一般格式：l ALTER TABLE ADD 完整性约束DROP MODIFY ; 说明? ADD子句用于新增列及其完整性约束条件;? DROP子句用于删除指定的完整性约束条件;? MODIFY子句用于修改原有的列定义,包括修改列名和数据类型。? SQL没有提供删除属性列的语句，用户只能间接实现这一功能。方法是先将表中要保留的列及其内容复制到一个新表中，然后删除原表，再将新表重新命名为原表。例删除STUDENT表上SNAME列上的唯一约束。 ALTER TABLE STUDENT DROP UNIQUE(SNAME);例删除STUDENT表上建立的主键约束。 ALTER TABLE STUDENT DROP PRIMARY KEY;例 为表STUDENT添加一个新的列。 ALTER TABLE STUDENT ADD(SID CHAR(18) NULL); ? 注意：新增加的字段只能作为表的最后一个字段。例将刚添加的列SID改为DATE类型。 ALTER TABLE STUDENT MODIFY SID DATE;v 基本表的删除l DROP TABLE ； ALTER TABLE的补充：（constraint 或constraint ）索引的创建（INDEX）一般格式：（unique对应表，distinct对应列）? CREATE UNIQUE INDEX ON (column1,column2 )? tablespace 表空间名? storage storage_clause); 说明? 是要建立索引的基本表名字。? 索引可建立在一列或多列上，割裂名之间用逗号分隔。? 索引值的排列次序缺省为ASC。? UNIQUE指明此索引的每一个索引值只对应唯一的数据记录。（唯一性）例为表EMP建立以ENAME为索引列的索引，索引名为emp_index, 索引存放的表空间为index 。 SQL CREATE INDEX emp_index ON EMP(ENAME DESC) tablespace index ;例在表DEPT的DNAME列上建立唯一性索引，索引名为ON_DNAME。SQL CREATE UNIQUE INDEX ON_DNAME ON DEPT(DNAME DESC); 当表中有数据时，建立索引将检查数据的唯一性， 如果出现重复，将有下列提示： ORA-01452: 无法 CREATE UNIQUE INDEX； 找到重复的关键字 建立了唯一性索引后，以后对数据的修改将自动进行 唯一性验证。索引的删除： 一般格式：? DROP INDEX ;总结索引：索引一旦建立，就由系统使用和维护，不需要用户干预。索引的建立是为了减少查询时间，但如果数据增删频繁，系统将会花费很多时间来维护索引，因此应该删除一些不必要视图的创建（VIEW）一般格式：CREATE VIEW (,)AS SELECT 语句 WITH CHECK OPTION;说明 其中子查询可以是任意复杂的SELECT 语句，但通常不允许含有ORDER BY子句和DISTINCT短语。 WITH CHECK OPTION表示对视图进行UPDATE, INSERT和DELETE操作时要保证更新、插入和删除的行满足视图定义中的谓词条件。 属性列名或者全部省略或者全部指定，没有第3种选择。例建立一个视图，使它包含所有工资高于3000的雇员的姓名、工资、部门以及部门地址SQL CREATE VIEW HIGHSAL (NAME, SAL, DEPT, LOC) AS SELECT ENAME, SAL, DNAME, LOC FROM EMP, DEPT WHERE SAL3000 AND DEPT.DEPTNO =EMP.DEPTNO;v 视图的删除 一般格式：? DROP VIEW ; 例删除TEST视图。? DROP VIEW TEST; 总结：视图是保存select语句的，为了下一次懒得写而存在的，下一次只需select * from HIGHSAL;4 数据控制语言DCL数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：1) GRANT：授权。2) ROLLBACK WORK TO SAVEPOINT：回退到某一点。回滚-ROLLBACK回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQLROLLBACK;ROLLBACK后的数据状态： 数据改变被取消(Undo). 数据恢复到以前状态. 被影响的行的锁被释放.Rollback到某一点： 用SAVEPOINT在当前事务中指定该点. 使用ROLLBACK TO SAVEPOINT 回退至该点.SQL UPDATE EMP SET SAL = SAL + 0.