SQL知识学习（Oracle）

预备知识-SQL基础

0.1数据类型

关系型数据库中，SQL语句主要包括四种即DDL、DML、DQL、DCL

DDL(Data Definition Language) 数据定义语言

DML(Data Manipulation Language) 数据操纵语言

DQL(Data Query Language) 数据查询语言

DCL(Data Control Language) 数据控制语言

1. 数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE

子句组成的查询块：

SELECT <字段名表>

FROM <表或视图名>

WHERE <查询条件>

2 .数据操纵语言DML

数据操纵语言DML主要有三种形式：

插入：INSERT

更新：UPDATE

删除：DELETE

3.数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、

索引、同义词、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

| | | | |

表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback

4 .数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制

数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

 

GRANT：授权

 

回滚—ROLLBACK

ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。

回滚命令使数据库状态回到上次最后提交的状态。其格式为：

SQL>ROLLBACK;

 

COMMIT [WORK]：提交

在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。

提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。

(1) 显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：

SQL>COMMIT；

(2) 隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，

EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，

系统将自动进行提交，这就是自动提交。其格式为：

SQL>SET AUTOCOMMIT ON；

一、以Oracle为例，学习SQL

在Oracle开发中，客户端把SQL语句发送给服务器，服务器对SQL语句进行编译、执行，把执行的结果返回给客户端。Oracle SQL语句由如下命令组成：

Ø 数据定义语言（DDL），包括CREATE（创建）命令、ALTER（修改）命令、DROP（删除）命令等。

Ø 数据操纵语言（DML），包括INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT … FOR UPDATE（查询）等。

Ø 数据查询语言（DQL），包括基本查询语句、Order By子句、Group By子句等。

Ø 事务控制语言（TCL），包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。

Ø 数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令。

 

目前主流的数据库产品（比如：SQL Server、Oracle）都支持标准的SQL语句。数据定义语言，表的增删改操作，数据的简单查询，事务的提交和回滚，权限的授权和撤销等，Oracle与SQL Server在操作上基本一致。

 

二、Oracle的数据类型

Oracle数据的核心是表，表中的列使用到的常用数据如下：

类型	含义
CHAR(length)	存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。
VARCHAR2(length)	存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。
NUMBER(p，s)	既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38为），s是指小数位数。
DATE	存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。
TIMESTAMP	不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。
CLOB	存储大的文本，比如存储非结构化的XML文档
BLOB	存储二进制对象，如图形、视频、声音等。

 

NUMBER类型的示例：

格式	输入的数字	实际的存储
NUMBER	1234.567	1234.567
NUMBER（6，2）	123.4567	123.46
NUMBER（4，2）	12345.67	输入的数字超过了所指定的精度，数据库不能存储

对于日期类型，可以使用sysdate内置函数可以获取当前的系统日期和时间，返回DATE类型，用systimestamp函数可以返回当前日期、时间和时区。

 

如：SELECT sysdate systimestamp from DUAL

返回系统日期和时间、日期、时间和时区

 

注意：

         Oracle的查询中，必须使用“select 列… from 表”的完整语法，当查询单行函数的时候，from后面使用DUAL表，dual表在系统中只有一行一列，该表在输出单行函数时为了select…from的语法完整性而使用。

三、创建表和约束

3.1创建表

Oracle创建表同SQL Server一样，使用CREATE TABLE命令来完成。

3.2创建约束

创建约束使用如下命令：

语法格式：ALTER TABLE命令

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容。

不论创建表还是约束，与SQL Server基本相同，注意：在Oracle中default是一个值，而SQL Server中default是一个约束，因此Oracle的default设置可以在建表的时候创建。

 

案例1：创建一个学生信息（INFOS）表和约束

代码：Oracle创建表和约束

 

CREATE TABLE INFOS

(

STUID VARCHAR2(7) NOT NULL,    --学号 学号=‘S’+班号+2位序号

STUNAME VARCHAR2(10) NOT NULL,  --姓名

GENDER VARCHAR2(2) NOT NULL,    --性别

AGE NUMBER(2) NOT NULL,        --年龄

SEAT NUMBER(2) NOT NULL,        --座号

ENROLLDATE DATE,      --入学时间

STUADDRESS VARCHAR2(50) DEFAULT '地址不详',      --住址

CLASSNO VARCHAR2(4) NOT NULL    --班号 班号=学期序号+班级序号

)

/  ①

ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID)  ②

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER

CHECK(GENDER = '男' OR GENDER = '女')  ③

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT

CHECK(SEAT >=0 AND SEAT <=50)  ④

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE

CHECK(AGE >=0 AND AGE<=100)  ⑤

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO

CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR

(CLASSNO >='2001' AND CLASSNO<='2999'))  ⑥

/

ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME)  ⑦

/

代码解析：

①  在Oracle代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。

②  创建一个主键约束。

③  与 ④ ⑤ ⑥ ⑦一起创建各种check约束。其中⑦是唯一约束，表示该列值是唯一的，列中的值不能重复。

 

Oracle中创建外键约束与SQL Server相同。

 

案例2：创建一个成绩表（SCORES）表和约束

代码演示：Oracle创建表和约束

CREATE TABLE SCORES

(

     ID NUMBER ,        --ID ①

     TERM VARCHAR2(2),                                  --学期 S1或S2

     STUID VARCHAR2(7) NOT NULL,                  --学号

     EXAMNO VARCHAR2(7) NOT NULL,              --考号 E+班号+序号     

     WRITTENSCORE NUMBER(4,1) NOT NULL,   --笔试成绩

     LABSCORE NUMBER(4,1) NOT NULL             --机试成绩

)

ALTER TABLE SCORES

   ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2')

/

ALTER TABLE SCORES

   ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID)  ②

/

代码解析：

①  SQL Server中可以使用identify创建自动增长列，但是Oracle中的自动增长需要借助序列（Sequence）完成，在后面讲解。

②  Oracle中的外键约束定义。

 

四、DML数据操纵语言

数据操纵语言（DML）用于对数据库的表中数据进行添加、修改、删除和SELECT…For UPDATE(后面专门学习该查询)操作。

4.1简单查询

数据查询是用SELECT命令从数据库的表中提取信息。SELECT语句的语法是：

SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名

语法解析：

1.     *表示表中的所有列。

2.     列名可以选择若干个表中的列名，各个列表中间用逗号分隔。

3.     表达式可以是列名、函数、常数等组成的表达式。

4.     WHERE子句是查询的条件。

5.     ORDER BY 要求在查询的结果中排序，默认是升序。

 

注：Oracle中可以把查询的结果根据结果集中的表结构和数据形成一张新表。

语法结构：根据结果集创建表

CREATE TABLE 表名 AS SELECT语句

代码：根据结果集创建表

SQL> CREATE TABLE INFOS1 AS SELECT * FROM INFOS; TABLE CREATED

使用上面命令创建的新表中，不存在任何约束，并且把查询的数据一起插入到新表中。如果只复制表结构，只需使查询的条件不成立（比如where 1=2），就不会查询从出任何数据，从而复制一个表结构。     

代码演示：复制表结构

SQL> CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2; TABLE CREATED

4.2数据插入

使用INSERT命令完成对数据的插入

语法结构：根据结果集创建表

INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……)

语法解析：

1. 列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。
2. 值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。

 

代码演示：向INFOS表和SCORES表中插入数据

SQL> INSERT INTO INFOS VALUES (  ①

  2 's100102', '林冲', '男', 22, 2,

  3  TO_DATE('2009-8-9 06:30:10',' YYYY-MM-DD HH24:MI:SS '),  ②

  4  '西安', '1001'

  5  )

  6  /

1 row inserted

SQL> INSERT INTO INFOS VALUES (

's100104','阮小二','男',26,3,SYSDATE,default,'1001');  ③

 1 row inserted

SQL>COMMIT;  ④

代码解析：

①  表名后面缺省了列名，默认是表Infos中的所有列名，values中的值要与表中列一一对应，包括顺序和数据类型的对应。在SQL*Plus中一条语句可以写在多行，那么从第二行开始，sqlplus会为每一行前面给出行号。

②  在Oracle中，日期是国际化的，不同的区域安装的数据库，默认的日期格式不同，因此为了程序便于移植，日期的输入要使用TO_DATE函数对日期格式化后输入，采用格式化字符串对日期进行格式化时，格式化字符串中字符不区分大小写，常见的格式化字符如下：

1.       yyyy表示四位年份

2.       mm表示两位月份，比如3月表示为03

3.       dd表示两位日期

4.       hh24表示小时从0-23，hh12也表示小时从0-11。

5.       mi 表示分钟

6.       ss表示秒

③  在遇到存在默认值的列时，可以使用default值代替。

④  commit是把用户操作（添加、删除、修改操作）提交，只有提交操作后，数据才能真正更新到表中，否则其他用户无法查询到当前用户操作的结果。

 

在Oracle中，一个INSERT命令可以把一个结果集一次性插入到一张表中。使用的语句是：INSERT INTO 表 SELECT子句，如下示例：

代码演示：INSERT向表中插入一个结果集

SQL> INSERT INTO INFOS2 SELECT * FROM INFOS; 5 rows inserted