SQL语言的五大组成部分DQL DML DDL DCL TC
SQL语言共分为五大类:
数据查询语言(DATA QUERY LANGUAGE, DQL),
数据操纵语言(DATA MANIPULATION LANGUAGE, DML)
数据定义语言(DATA DEFINITION LANGUAGE, DDL)
数据控制语言(DATA CONTROL LANGUAGE, DCL)
事务控制(TRANSACTION CONTROL, TC)
一、数据查询语言DQL
查询语句的一般结构:
单表查询
多表查询
嵌套查询
分组查询
集合查询
查询语句的一般结构
SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]···
FROM <表名或视图名>[,<表名或视图名>]···
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]];
SQL语句的执行顺序
SELECT DNAME,MAX(SAL)-- 5 对列筛选(分组字段或聚集函数)
FROM EMP
LEFTOUTERJOIN DEPT -- 1 确定表
USING(DEPTNO)
WHERE DEPTNO >0-- 2 确定行(记录)
GROUPBY DNAME -- 3 将行分组
HAVINGMAX(COMM)ISNULLORMAX(COMM)>0-- 4 对组筛选
ORDERBY DNAME -- 6 对结果集排序
单表查询:
1、无条件:
SELECT中的<目标表达式>可以是表中的列,也可以是表达式,包括算术表达式、字符串常数、函数等。(字符串用单引号定界)
1. 使用算术表达式(+、-、*、/)
SQL>SELECT EMPNO, SAL *0.8FROM EMP;
2. 使用字符常量
SQL>SELECT EMPNO,'NAME IS :', ENAME FROM EMP;
3.使用函数
SQL>SELECT EMPNO,UPPER(ENAME)FROM EMP;
4. 改变列标题
SQL>SELECT EMPNO EMPLOYEENAME,SAL AS SALARY, JOB, DEPT FROM EMP;
5.使用连接字符串
SQL>SELECT ENAME ||':'||'1'||'month salary='|| SAL AS MONTHY FROM EMP
6.消除重复行
SQL>select [ALL] ename from emp;
SQL>selectDISTINCT ename from emp;
2、有条件:
WHERE常用的查询条件
查询条件 |
谓词 |
比较大小 |
=, >, <, >=, <=, < > |
确定范围 |
BETWEEN AND,NOT BETWEEN AND |
确定集合 |
IN,NOT IN |
字符匹配 |
LIKE,NOT LIKE |
空值 |
IS NULL,IS NOT NULL |
多重条件 |
AND,OR |
确定范围:
SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO BETWEEN10AND20;
确定集合:
SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO IN(10,30);
等价于:
SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO =10OR 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的交互查询:
使用替代变量,以“&”开头。
SQL>SELECT*FROM EMP WHERE ENAME ='&NAME';
3、排序: ASC升序排序,DESC降序排序
ORDERBY从句要放在 SELECT语句的最后。
表达式排序:
SQL>SELECT EMPNO, ENAME, SAL,SAL *12FROM EMP ORDERBY SAL *12DESC;
多列排序:
SQL>SELECT EMPNO, DEPTNO, SAL FROM EMP ORDERBY DEPTNO ASC, SAL DESC;
显示为按部门号升序,相同部门号内的工资降序排列。
别名排序:
SQL>SELECT EMPNO, ENAME,SAL *12 ANNSAL FROM EMP ORDERBY ANNSAL DESC;
多表查询
1. 等值查询:
在存在主外键关系的表中,使用等号建立表之间的连接
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO;
SQL99新标准:内连接(innerjoin)
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A
INNERJOIN DEPT B -- inner可省略
ON A.DEPTNO = B.DEPTNO;
2. 非等值查询:
使用非等号关系运算符进行多表的连接
SELECT A.ENAME, A.SAL, B.GRADE
FROM EMP A, SALGRADE B
WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL;
3. 外连接(outerjoin):
类似等值查询,使用特定的符号(+)
(+)符号如果出现在where子句等号的左边,叫左外连接,左外连接会显示右表的全部数据;
(+)符号如果出现在where子句等号的右边,叫右外连接,右外连接会显示左表的全部数据;
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO(+)= B.DEPTNO;
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO(+);
SQL99新标准:
左外连接:
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A
LEFTOUTERJOIN DEPT B -- outer可省略
ON A.DEPTNO = B.DEPTNO;
右外连接:
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A
RIGHTOUTERJOIN DEPT B -- outer可省略
ON A.DEPTNO = B.DEPTNO;
全外连接:外连接是在等值(内)连接的基础上将左表和右表的未匹配数据都加上
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A
FULLOUTERJOIN DEPT B -- outer可省略
ON A.DEPTNO = B.DEPTNO;
4. 自连接:
特殊的多表连接,连接的多个表来自于同一个表
SELECT A.EMPNO, A.ENAME, B.ENAME MANAGER
FROM EMP A, EMP B
WHERE A.MGR = B.EMPNO;
5. 自然连接(naturaljoin):
基于多个表有一个或多个相同字段名,且这些字段的类型相同
由oracle自行决定哪些列作为连接的条件:将不同表中的那些具有相同名称和数据类型的字段用等值连接起来。
SELECT A.ENAME, B.DNAME FROM EMP A NATURALJOIN DEPT B;
6. 交叉查询(crossjoin):笛卡尔集
SELECT A.ENAME, A.SAL, B.GRADE FROM EMP A, SALGRADE B;
7. join...using:
使用 USING可以在natural join时有多个列满足条件,指定具体的字段做等值连接
如果列名相同但是数据类型不同,这时不能使用natural join可以使用 using来连接。
当多列匹配时,using只能匹配一列。
在引用的列前面不能有表名或者表别名。
natural join和using 是相互排斥的
SELECT A.ENAME, B.DNAME FROM MYEMP A JOIN DEPT B USING(DEPTNO);
嵌套查询
概念:
查询块:一个select-from-where语句称为一个查询块。
嵌套查询:将一个查询块嵌套在另一个查询块中的查询,称为嵌套查询,也称为子查询。
处理步骤:
一般由里向外进行处理。
注意:
子查询可以有多层,所存取的表可以是父查询没有存取的表;
子查询作为查询列时显示子查询选出的记录,其他情况子查询选出的记录不显示;
一个子查询必须放在圆括号中;
将子查询放在比较条件的右边以增加可读性;
子查询不包含 ORDER BY 子句,对一个 SELECT 语句只能用一个 ORDER BY 子句。
子查询位置:
可放在SELECT后面,子查询选出的记录作为列传显示;
可放在FROM后面,子查询选出的记录作为结果集的一部分;
可放在WHERE后面,子查询选出的记录作为条件表达式的一部分;
可放在HAVING后面,子查询选出的记录作为分组结果的过滤条件;
可放在ORDER BY后面,子查询选出的记录作为结果集的排序内容;
SQL>SELECT(SELECT DNAME FROM DEPT WHERE DEPT.DEPTNO='10') DNAME ,EMP.*FROM EMP;
SQL>SELECT*FROM(SELECT*FROM DEPT WHERE DEPT.DEPTNO='10') DEPT, EMP WHERE EMP.DEPTNO = DEPT.DEPTNO;
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DEPT.DNAME ='SALES');
SQL>SELECT DEPTNO,AVG(SAL)FROM EMP GROUPBY DEPTNO HAVINGAVG(SAL)>(SELECTMIN(SAL)FROM EMP );
SQL>SELECT*FROM EMP ORDERBY(SELECT DEPTNO FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO);
子查询的类型:
单行子查询:从内 SELECT语句只返回一行的查询,可采用单行比较符:=、<、>、<=、>=等;
多行子查询:从内 SELECT语句可返回多行的查询,需采用多行比较符:IN、>ANY、>ALL、<ANY、< ALL等。
相关子查询(Correlated Sub-QUERY):相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。
非相关子查询(Uncorrelated Sub-QUERY):非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO =(SELECT DEPTNO FROM DEPT WHERE DEPT.DNAME ='SALES');
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(SELECT DEPTNO FROM DEPT );
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO);
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(SELECT DEPTNO FROM DEPT );
子查询返回结果对主查询的影响:
如果子查询返回的是零值,不会对主程序造成影响;
如果子查询返回的是空值,会影响主程序的返回值;
如果单行子查询返回的是多行,则会查询错误“单行子查询出现多行”;
SQL>SELECT*FROM EMP WHERE EMP.COMM IN(SELECT COMM FROM EMP);--有结果数据
SQL>SELECT*FROM EMP WHERE EMP.COMM IN(SELECTNULLFROM EMP);--无结果数据
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(10,0);--有结果数据
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(NULL);--无结果数据
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(10,NULL);--有结果数据
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO NOTIN(NULL);--无结果数据
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO NOTIN(10,NULL);--无结果数据
SQL>SELECT*FROM EMP WHERE EMP.DEPTNO =(SELECT DEPTNO FROM DEPT );--错误“单行子查询出现多行”
多列子查询适应于:成对比较;非成对比较。
非成对的子查询:
SELECT EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE(MANAGER_ID, DEPARTMENT_ID) IN
(SELECT MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN(178,174))
AND EMPLOYEE_ID NOTIN(178,174);
只有要查询的东西和子查询返回的东西一一对应上了,查询条件才能满足,
如果有一个对应不上那么查询不会满足。
非成对的子查询:
SELECT EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE MANAGER_ID IN
(SELECT MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN(174,141))
AND DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN(174,141))
AND EMPLOYEE_ID NOTIN(174,141);
多行嵌套查询(子查询的结果是多行的)
多值比较运算符
in、exists、not in、not exists之间的区别
all、any(some替代)、not in、in之间的区别
来看下面这样一条SQL语句:
select*from student where20<all(select score from student)
其中,(select score from student)返回的是所有分数的记录集
只有当记录集中的所有分数都大于20后,才会执行前面的SQL语句。显然,任何一条记录都大于20.
如果把它改成50,则查不到任何数据,因为只要有一条记录小于50,条件都不成立。
但是,如果把all改面any就不一样了:
select*from student where50<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.b
4、 NOTIN与 <>ALL等价,而不等于<>ANY,前两者均表示,变量不在(子查询)列表之中,即 a NOTIN(table B)表示 a <>ALL B.b。而如果a <>ANY B.b,则只要任意一个b<>a就true了。
5、 IN与 EXISTS的性能区别主要来自,IN会编列子查询的每行记录,然后再返回,而EXISTS 则只要遇到第一个满足条件的记录就马上返回。
6、NOTIN与 NOTEXISTS并不能完全等价,只有当子查询中,select关键字后的字段有not null约束或者有这种暗示时才用NOT IN。
[例]查询所有雇员的工资都不低于1000的部门的所有人员信息
SQL>select ename,sal,job from emp where deptno notin(select deptno from emp where sal <1000);
SQL>select ename,sal,job from emp where deptno !=all(select deptno from emp where sal <1000);
结果
ENAME SAL JOB
---------------- ---------- ----------
CLARK 2450 MANAGER
KING 5000 PRESIDENT
MILLER 1300 CLERK
[例2]查找有工资在4000-5000之间的雇员所在部门的所有人员信息。
SQL> select ename,sal,job from emp where deptno =any(select deptno from emp where sal between4000and5000);
结果
ENAME SAL JOB
---------------- ---------- -----------------
CLARK 2450 MANAGER
KING 5000 PRESIDENT
MILLER 1300 CLERK
[例3]查找那些雇员的部门号在部门表中没有的雇员的信息。
SQL>select ename, sal, job from emp where notexists(select * from dept where deptno = emp.deptno);
分组查询
(一般句中有每个、各个字样的要用到分组查询)
Having后一般是统计函数,不能是emp.depno=dept.depno这样的等式
常用统计函数常与分组查询一块用
常用统计函数(一般where后不用统计函数)
函数AVG 和 SUM 应用于数值型
MIN 和 MAX 可用于任意类型.
函数AVG 和 SUM 应用于数值型
COUNT(*) 返回符合条件的记录数.(包括含null的所有行数)
COUNT(expr) 返回的非NULL的行数.
集合函数忽略列中的 null 值(COUTN除外).
SQL> SELECT AVG(comm) FROM emp;
在组函数中使用 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')
在包含GROUP BY子句的查询语句中,SELECT子句后面的所有字段列表(除聚集函数外),均应该包含在GROUP BY 子句中。
在 GROUP BY 从句中使用多个列
SQL > SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job
使用 HAVING 从句选择满足条件分组
执行次序:
对行进行分组.
对每组数据执行组函数.
返回符合 HAVING 从句的分组结果.
WHERE与HAVING的区别
作用对象不同。
WHERE 作用于基本表或视图,从中选择满足条件的元组
HAVING短语作用于组,从中选择满足条件的组。
[例]查询平均工资超过2000的部门,并按部门号进行排序
SQL> SELECT deptno, avg(sal) from emp
GROUP BY deptno
HAVING AVG(SAL)>2000
ORDER BY deptno;
结果
DEPTNO AVG(SAL)
10 3725
20 2175
集合查询:
查询结果的集合运算
并集(UNION [ ALL])
交集(INTERSECT [ALL])
差集(MINUS [ALL] )
1. union[all]:
取多个查询结果的并集,不要all表示重复的记录只保留一条
注意:union、minus、intersect做连接查询时,多个查询结果的字段和类型要一致
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE JOB ='CLERK'
UNIONALL-- ALL可以省略
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO =20
ORDERBY ENAME;
2. minus:取多个查询结果的差集
注意:union、minus、intersect做连接查询时,多个查询结果的字段和类型要一致
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE JOB ='CLERK'
MINUS
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO =20
ORDERBY EMPNO;
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO =20
MINUS
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE JOB ='CLERK'
ORDERBY EMPNO;
3. intersect:取多个查询结果的交集
注意:union、minus、intersect做连接查询时,多个查询结果的字段和类型要一致
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE JOB ='CLERK'
INTERSECT
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO =20
ORDERBY EMPNO;
二、数据操纵语言DML
数据操纵语言DML主要有四种形式:
1) 插入:INSERT
2) 更新:UPDATE
3) 删除:DELETE
4)合并:MERGE(插入或修改)
INSERT 语句
插入单个元组
格式:
INSERT INTO <表名>[(<属性列1>[,<属性列2>]…)]VALUES (<常量1>[,<常量2>]…);
注意:
1)在表定义时说明了NOT NULL的属性列不能取空值,否则会出错。
2)如果INTO子句中没有指明任何列名,则新插入的纪录必须在每个属性列上均有值。
3)指定列名时,列名顺序任意,列值与列名对应.
4)字符型和日期型数据在插入时要加单引号。
[例]
SQL>INSERTINTO DEPT VALUES(60,'PRODUCTION','SAN FRANCISCO');
SQL>INSERTINTO DEPT(DNAME,DEPTNO)VALUES('TEST',70);
插入子查询结果
格式:
INSERT INTO <表名>[(<属性列1>[,<属性列2>]…)]SELECT [(<属性列1>[,<属性列2>]…)] FROM <表名>;
[例]向表中插入一条与SMITH的内容相同的记录,但姓名改为了FAN,雇员号改为了8000.
SQL>INSERT INTO EMP (EMPNO, ENAME, JOB, MGR ,HIREDATE, SAL,, COMM, DEPTNO)
SELECT8000,'FAN',JOB,MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP WHERE ENAME ='SMITH';
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;
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');
MERGE语句(合并,插入或修改)
Mergeinto详细介绍
MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。
通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,
连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。
这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。
语法格式:
MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table|view| subquery } [t_alias]
ON( condition )
WHENMATCHEDTHEN merge_update_clause
WHENNOTMATCHEDTHEN merge_insert_clause;
语法说明:
MERGEINTO [your table-name] [rename your table here]
USING( [write your query here] )[rename your query-sqlandusing just like a table]
ON([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can executesomeupdatesqlor something else ]
WHENNOT MATHED THEN [execute something else here ]
[示例]
MERGEINTO EMP --emp表是需要更新的表
USING(SELECT*FROM EMP1 WHERE EMP1.DEPTNO ='10') T -- 关联表
ON(EMP.EMPNO = T.EMPNO)--关联条件
WHENMATCHEDTHEN--匹配关联条件,作更新处理
UPDATE
SET EMP.ENAME ='newname', EMP.COMM =888--此处只是说明可以同时更新多个字段。
WHENNOTMATCHEDTHEN--不匹配关联条件,作插入处理。如果只是作更新,下面的语句可以省略。
INSERTVALUES(T.EMPNO, T.ENAME, T.JOB, T.MGR, T.HIREDATE, T.SAL, T.COMM, T.DEPTNO);
[示例]不能更新ON (EMP.EMPNO = T.EMPNO)关联条件中的字段
MERGEINTO EMP --emp表是需要更新的表
USING(SELECT*FROM EMP1 WHERE EMP1.DEPTNO ='10') T -- 关联表
ON(EMP.EMPNO = T.EMPNO)--关联条件
WHENMATCHEDTHEN--匹配关联条件,作更新处理
UPDATE
SET EMP.EMPNO =888--不能更新ON (EMP.EMPNO = T.EMPNO)关联条件中的字段
WHENNOTMATCHEDTHEN--不匹配关联条件,作插入处理。如果只是作更新,下面的语句可以省略。
INSERTVALUES(T.EMPNO, T.ENAME, T.JOB, T.MGR, T.HIREDATE, T.SAL, T.COMM, T.DEPTNO);
三、数据定义语言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);
创建成绩表
SQL>CREATE 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;
基本表的修改
一般格式:
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;
基本表的删除
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 SAL>3000 AND DEPT.DEPTNO =EMP.DEPTNO;
视图的删除
一般格式:
DROP VIEW <视图>;
[例]删除TEST视图。
DROP VIEW TEST;
总结:视图是保存select语句的,为了下一次懒得写而存在的,下一次只需
select * from HIGHSAL;
四、数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权。DCL语句主要有如下两种:
1) GRANT:授予其他用户对数据库结构的访问权限。
2) REVOKE:收回用户访问数据库结构的权限。
五、事务控制 TC
用于将对行所做的修改永久性地存储到表中,或者取消这些修改操作。
控制数据库操纵事务发生的时间及效果,对数据库实行监视等。TC语句主要有如下几种:
1) COMMIT:永久性地保存对行所做的修改。
2) ROLLBACK [WORK] TO [SAVEPOINT]:取消对行所做的修改,或回退到某一点。
3)SAVEPOINT:设置一个“保存点”,可以将对行的修改回液滚到此处。
回滚命令使数据库状态回到上次最后提交的状态。其格式为:
SQL>ROLLBACK;
ROLLBACK后的数据状态:
数据改变被取消(Undo).
数据恢复到以前状态.
被影响的行的锁被释放.
SQL>DELETEFROM EMP WHERE EMP.DEPTNO =88;
SQL>ROLLBACK;
Rollback到某一点:
用SAVEPOINT在当前事务中指定该点.
使用ROLLBACK TO SAVEPOINT 回退至该点.
SQL> UPDATE EMP SET SAL = SAL + 0.1*SAL WHERE DEPTNO = 20;
SQL>SAVEPOINT UPDATE_done
SQL> INSERT INTO DEPT(DNAME,DEPTNO) VALUES('TEST',70);
SQL>ROLLBACK To UPDATE_done
COMMIT [WORK]:提交
COMMIT/ROLLBACK前数据的状态:
被修改的数据可以修改.
当前用户可以看到DML操作的数据改动结果.
其他用户不能看到DML操作的数据改动结果.
被影响的行被锁定(lock); 其他用户不能修改被影响的行.
COMMIT后的数据状态:
被改变的数据被持久写入DB.
以前的数据状态不可恢复.
所有用户都可以看到.
被锁定的行解锁—其他用户可以操作这些行.
全部savepoint被清除.
总结:一旦commit就不能rollback
提交数据有三种类型:显式提交、隐式提交及自动提交。
显式提交:
用COMMIT命令直接完成的提交为显式提交。其格式为:
SQL>COMMIT;
[例子]修改
SQL>UPDATE EMP SET EMP.COMM =888WHERE EMP.DEPTNO =88;
执行
SQL>COMMIT;
隐式提交:
用SQL命令间接完成的提交为隐式提交。这些命令是:
ALTER,AUDIT,COMMENT,CONNECT,CREATE,DISCONNECT,DROP,
EXIT,GRANT,NOAUDIT,QUIT,REVOKE,RENAME。
自动提交:
若把AUTOCOMMIT设置为ON,则在插入、修改、删除语句执行后,
系统将自动进行提交,这就是自动提交。其格式为:
SQL>SET AUTOCOMMIT ON;
相关推荐
### Oracle学习笔记知识点详解 #### 一、Oracle简介 Oracle是一家知名的软件公司,以其数据库管理系统闻名全球。该公司成立于1977年,总部位于美国加利福尼亚州。Oracle不仅提供数据库解决方案,还涉及中间件、...
Oracle学习笔记精华版是针对数据库管理系统Oracle的一份重要学习资源,涵盖了从基础概念到高级特性的全面知识。Oracle,作为全球广泛使用的大型企业级数据库系统,对于IT专业人员尤其是数据库管理员(DBA)来说,是...
3. **PL/SQL**:Oracle特有的过程化语言,用于编写存储过程、函数和触发器,增强数据库的业务逻辑。 4. **数据仓库与OLAP**:Oracle提供强大的数据仓库功能和在线分析处理(OLAP)工具,支持复杂的数据分析。 5. **...
以下是对Oracle学习笔记中提到的一些关键知识点的详细解释: 1. **SQL执行顺序**: SQL语句的执行顺序是:`FROM` -> `WHERE` -> `SELECT` -> `GROUP BY` -> `HAVING` -> `ORDER BY`。首先从`FROM`子句开始,确定...
Oracle SQL是数据库管理员和开发人员在Oracle数据库系统中进行数据查询和管理的重要工具。这篇笔记主要涵盖了Oracle SQL的...这两份“Oracle SQL笔记”文档应包含了上述各个方面的详细解释和实例,值得仔细阅读和学习。
精通Oracle SQL【第2版】ORACLE SQL高级编程【第二版】学习笔记
Oracle 10g SQL 学习笔记涵盖了Oracle数据库的基础知识,包括版本信息、数据库系统特点、关系型数据库结构、SQL语言、操作环境以及常见的数据库操作。以下是对这些知识点的详细说明: 1. **Oracle 9i基础知识**: ...
以下是对Oracle学习笔记整理的主要知识点的详细说明: 1. **数据库选择**: 在决定使用哪种数据库时,通常需要考虑项目的规模、性能需求、安全性要求以及可用资源。Oracle数据库因其稳定性、可扩展性和高性能而被...
本资料包是针对Oracle SQL的学习笔记,包含了丰富的练习题及解答,旨在帮助学习者深入理解和掌握SQL在Oracle环境中的运用。 1. **SQL基本查询操作** "1.SQL基本查询操作.txt"这部分内容可能涵盖了简单的SELECT语句...
根据提供的信息,我们可以总结出以下Oracle数据库学习的关键知识点: ...以上是基于提供的内容整理出的Oracle学习笔记中的关键知识点。通过理解这些基础知识,可以更好地管理和操作Oracle数据库。
### Oracle 学习笔记知识点概览 #### 一、Oracle 数据库系统参数查询与管理 在 Oracle 数据库的学习过程中,了解如何查看和管理数据库的系统参数是非常重要的。这些参数直接影响着数据库的性能和稳定性。 ##### ...
从提供的标题、描述以及部分文本内容来看,这份“Oracle学习笔记”主要围绕Oracle数据库中的查询语句及其各种类型进行深入探讨。尽管描述部分重复了标题的文字,但主要内容段落包含了丰富的信息,下面将对这些知识点...
在“MSDN Oracle学习笔记”中,我们可以期待找到关于Oracle数据库的详细讲解和实践指导。 首先,Oracle数据库的基础知识是必不可少的。这通常涵盖数据库系统的基本概念,如SQL(结构化查询语言)的使用,数据类型,...
│ Oracle学习笔记.pdf │ Oracle学习笔记.wps │ 安装Oracle后myEclipse不能正常使用.txt │ 手工配置listener.ora【避免出现ORA-12514错误】.txt │ 贴子树状态存储结构.jpg │ 贴子树状态存储结构.sql │ ├─01...
【Java SSH Oracle SQLServer 学习笔记】 Java是一种广泛使用的面向对象的编程语言,它以其跨平台、开源和丰富的库而闻名。SSH是三个开源框架的缩写,分别是Spring、Struts和Hibernate,它们在Java Web开发中起着至...
索引类似于书籍的目录,是Oracle数据库为了加速SQL查询而创建的一种独立于表的物理结构。它们直接指向包含查询值的行,减少了磁盘I/O操作,从而提高查询速度。创建索引的语法为`CREATE INDEX index ON table (column...
### ORACLE经典学习笔记知识点概览 #### 第一章 ORACLE 命令 本章节主要介绍了Oracle数据库中常用的命令及其使用方法。 1. **查看参数文件**: `Desc v$parameter` - 这个命令用于查看Oracle的参数文件信息,通过...
这篇资源是一个全面的SQL,特别是针对Oracle数据库的学习笔记集合。Oracle是世界上最广泛使用的数据库管理系统之一,它提供了丰富的功能来处理各种类型的数据和满足复杂的业务需求。SQL,全称Structured Query ...