SQL基础

       学习每一个数据库不能缺少的就是如何访问它的语言，Oracle也不例外。SQL（Structured Query Language 结构化查询语言）是每一个数据库都通用的语言，使用SQL语言可以在数据库中创建表、检索数据、操作数据，并对权限进行控制。

本节要点：

• l  SQL——数据沟通的语言标准
  •  什么是SQL？
  •  了解SQL的种类
• l  Oracle中支持的数据类型
• l  数据定义语言（DDL）
  •  CREARE TABLE……
  •   ALTER TABLE……
  •   DROP TABLE……
• l  约束的使用
  •  主键约束
  •   外键约束
  •   唯一约束
  •   检查约束
  •   非空约束
  •   约束的其他操作
• l  数据操纵语言（DML）数据查询语言（DQL）
• l  数据控制语言（DCL）

 

1           SQL——数据沟通的语言标准

SQL的主要功能是在各种数据库间建立联系，进行沟通。

1.1          什么是SQL？

美国国家标准学会（American National Standards Institute）成立于1918年，SQL就是美国国家标准学会确定的。SQL主要用于存取数据以及查询、更新和管理关系数据库系统。现行的数据库几乎都支持SQL语言，但是这些数据库各自又对SQL语言进行了改进，如我们学习的Oracle数据库使用的是PL/SQL语言，而PL/SQL就是对SQL语言的一种扩展，这也导致一种现象：当前数据库脚本一般只能在当前数据库中执行，很难兼容其他数据库，造成代码的移植性差等问题。

SQL语言本身可以分成4类，即：

• n  定义要在数据库存储哪些信息的数据定义语言（DDL）
• n  对数据库中的表进行操作的数据操纵语言（DML）
• n  对数据库中的表进行检索的数据查询语言（DQL）
• n  对数据库中的对象进行权限管理的数据控制语言（DCL）

1.2          了解SQL的种类

1)              数据定义语言（Date Definition Language, DDL）

定义数据库中要如何存储数据。DDL语言包括对数据库中对象的创建、修改、删除的操作，这些对象主要有数据库、数据表、视图、索引等。

2)              数据操纵语言（Date Manipulation Language, DML）

用来对数据库表进行操作的。这些操作主要包括对数据库表中的数据进行增加、删除、修改的操作。

3)              数据查询语言（Date Query Language, DQL）

对数据库表中的数据进行查询的。

4)              数据控制语言（Date Control Language, DCL）

对数据库中的对象权限进行权限设置等操作。

2           Oracle中支持的数据类型

创建表时必须为列指定数据类型，所有在学习SQL语言之前先来学习一下数据类型。

不同数据库种类和同一种数据库不同的版本所提供的数据库类型不尽相同，但是都可以分为字符型、数字型、日期类型和其他数据类型四类。

1)         字符型

Oracle中常见的有varchar2、char、long。注：Long类型的用的较少，最常用varchar2。

 

数据类型	取值范围（字节）	说明
varchar2	0~4000	可变长度的字符串
char	0~2000	用于描述定长的字符型数据
long	0~2GB	用来存储变长的字符串

2)         数字型

Oracle中常用的有number，用他来表示数字类型数据。

数据类型	取值范围	说明
number(p,s)	P最大精度是38位（十进制）	P代表的是精度（包含小数位），s代表的是保留的小数位数

3)         日期类型

Oracle中常用的有date和timestamp，用来存储日期和时间。

数据类型	说明
date	用来存储日期和时间，范围在公元前4712年1月1日到公元9999年12月31日
timestamp	用来存储日期和时间，与date类型的区别就是在显示日期和时间时更精准，date类型的时间精确到秒，而timestamp的数据类型可以精确到小数秒。此外，使用timestamp存放日期和时间还能够显示当前是上午还是下午。

4)         其他数据类型

存放大数据的数据类型及存放二进制文件的数据类型。

数据类型	取值范围（字节）	说明
blob	最多可以存放4GB	存储二进制数据
clob	最多可以存放4GB	存储字符串数据
bfile	大小与操作系统有关	用来把非结构化的二进制数据存储在数据库以外的操作系统文件中

3           数据定义语言（DDL）

数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象。

用于操纵表结构的数据定义语言命令有：

•   CREATE TABLE
•   ALTER TABLE
•   TRUNCATE TABLE
•   DROP TABLE

1)        使用create语句创建表

具体语法：

CREATE TABLE[schema] tablename

( columnname  datatype [null | not null],

   columnname  datatype  [null | not null],

    …

    [constraint]

);

• l  关键字“CREATE TABLE”指示Oralce创建一个表。
• l  Tablename：表名，一个数据库中的同一个用户下表名不能重复
• l  Columnname：列名，一个表中列名不能重复
• l  Datatype：该列存放数据的数据类型
• l  [null | not null]：是否允许该列为空
• l  [constraint]：设置约束

注：表名和列名不能使用Oracle “保留字”，例如：SELECT，DISTINCT，CHAR和NUMBER等

案例1：CREATE TABLE acctmanager

(

amid  VARCHAR2(4),

amname VARCHAR2(20),

amedate DATE DEFAULT SYSDATE,

region CHAR(2)

);

• l  一个表最多可以包含1000列。
• l  创建表的时候允许向列分配一个默认值。默认值是用户没有在列中输入内容情况，Oracle自动存储的值。
• l  表名虽然是以小写字母输入，但ORACLE会在处理命令时自动转为大写。
• l  创建表的用户就是这个表的拥有者。
• l  amedate分配了一个默认值SYSDATE，表明如果用户输入新的销售经理信息，而没有包括这个人的聘用时间时，则oracle服务器会插入当前日期。
• l  执行了命令之后，只是创建了表结构，并没有数据。
• l  能够执行CREATE TABLE命令必须有这个权限，如果没有要求管理员授予。

案例2：CREATE TABLE secustomerorders

AS (SELECT customer#,state,ISBN,

Category,quantity,cost,retail

FROM customers NATURAL JOIN orders NATURAL JOIN

Orderitems NATURAL JOIN books

WHERE state IN('FL','GA','AL'));

• 创建一个包含现有表的数据的表，可以使用CREATE TABLE命令并包括一个包含子查询的AS子句。

 

2)        使用Alter语句修改表

具体语法：

ALTER TABLE tablename

ADD|MODIFY|DROP COLUMN|coloumnname[definition];

•  对表结构进行更改：添加一列，删除一列，更改列的大小
•  通过ALTER TABLE命令来来实现
•  ORACLE的特性：可以修改表而不必关闭数据库，即使用户正在访问这个表，仍然可以修改这个表而不必中断服务。

案例：

/*添加列*/

ALTER TABLE acctmanager

ADD(ext NUMBER(4));

/* 对列进行的更改，可以更改三个方面：列的大小、列的数据类型和列的默认值*/

ALTER TABLE customers

MODIFY (lastname VARCHAR(6));

注意：修改时不能降低列的数据精度和小数位，不能缩小字符宽度。比如说原先是VARCHAR(16)现在改为VARCHAR(12)就不可行，因为有可能存在数据项是16位，无法保存。

/* 删除列*/

ALTER TABLE acctmanager

DROP COLUMN ext;

• l  如果从表中删除一列，那么删除将是永久的，如果不小心从表中错误地删除了列，那么你不能“取消”这种损坏，唯一的选择是将这一列重新添加到表中，然后手工重新输入以前包含的所有数据。
• l  与带有ADD或MODIFY的子句的ALTER TABLE命令不同，DROP COLUMN子句只能引用一个列。
• l  你不能删除表中剩余的最后一列

 

另一种删除列方式：ALTER TABLE…SET UNUSED/DROP UNUSED COLUMNS命令

首先，设置为不可用：

ALTER TABLE  tablename

SET UNUSED(columnname);

或者

ALTER TABLE  tablename

SET UNUSED COLUMN columnname;

然后，删除：

ALTER TABLE tablename

DROP UNUSED COLUMNS;

• l  SET UNUSED是不可恢复的 ，所以与其他drop语句一样想好了再用
• l  当Oracle服务器从一个非常大的表中删除一列时，这将降低用户的查询或其他SQL命令的处理速度，为了避免这种问题，可以在ALTER TABLE命令中包括SET UNUSED子句，将这一标记记为以后再删除，它不会显示在表结构中，也不会出现在任何查询的结果中。
• l  最后在合适的时机使用DROP UNUSED子句完成删除。

 

3)        使用Drop语句删除表

删除列具体语法：

DROP TABLE tablename;

• l  删除时一定要非常小心，删除一个表之后，表及它所包含的所有数据都将丢失，这个表所创建的任何索引也将删除，索引后面讲解。

4           约束的使用

需要保证数据完整性的案例：

输入的类型是否正确？

——年龄必须是数字

输入的格式是否正确？

——身份证号码必须是18位

是否在允许的范围内？

——性别只能是”男”或者”女”

是否存在重复输入？

——学员信息输入了两次

是否符合其他特定要求？

——信誉值大于5的用户才能够加入会员列表

……

约束是保证表中数据完整性的手段。为了保证数据的完整性，创建表的时候，就应当保证以后数据输入是正确的——错误的数据、不符合要求的数据不允许输入。以下是约束概要介绍：

约束	说明
PRIMARY KEY	确定哪些列唯一的标识了各个记录。主键不能是NULL，并且根据值，必须是唯一的。缩写_pk
FOREIGN KEY	在一种一对多的关系中，将约束添加到多个表。约束确保了如果将一个值输入一个指定的列，那么它必须已经存在于“一”表中，或者还没有添加到这个列。缩写_fk
UNIQUE	确保存储在一个指定列中的所有数据都是唯一的，它与主键约束的不同之处它允许NULL值。缩写_uk
CHECK	确保在向表中添加数据值前满足一个指定的条件，一个订单的发货日期不能早于订购日期。缩写_ck
NOT NULL	确保一个指定的列不能包含一个NULL值。只能使用创建表的列一级方法来创建 NOT NULL约束。缩写_nn

创建约束的方式：

• 在列一级上创建约束；在列的后面添加约束即可

columnname [CONSTRAINT constraintname] constraintype

例：Create Table Orders

(Order# NUMBER(4) PRIMARY KEY,……

•   在表一级上创建约束；约束定义与列定义是分开的，在定义了所有列之后列出它

[CONSTRAINT constraintname] constraintype (colunmnname,…)

 

4.1          主键约束

主键约束在一个表中只有一个，但是一个主键约束可以由多个列组成，但是如果主键中包含多个列，也就是复合主键，则只能在表一级上创建。主键用来唯一标识一条记录,保证实体完整性。

a)         创建表时添加主键：

Create table categoryinfo

（categoryId varchar2（10），

CategoryName varchar2（30），

Primary key（categoryId））；

b)         使用alter table语句为表添加主键约束

ALTER TABLE table_name

ADD CONSTRAINTS constraint_name PRIMARY KEY(columen_name);

c)         移除主键

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

 

4.2          外键约束

外键约束可以保证使用外键约束的数据库列与所引用的主键约束的数据列一致。如：防止没有订单信息中的客户信息可靠，可以创建一个约束，在orders表的customer#列中输入任何数据与customers表中现有的客户作比较，如果找不到客户，那么orders表中相应项目将被拒绝

语法：

ALTER TABLE tablename

ADD [CONSTRAINT constraintname] FOREIGN KEY (columnname)

REFERENCES referencedtablename(referencedcolumnname);

实例：

ALTER TABLE orders

ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#)

REFERENCES customers(customer#);

 

添加关键字ON DELETE CASCADE，从父表中删除一个记录，则自动删除子表中的任何相应记录：

ALTER TABLE orders

ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#)

REFERENCES customers(customer#) ON DELETE CASCADE;

从customers表中删除记录时同时删除该客户关联的订单信息。

 

4.3          唯一约束

UNIQUE约束称为唯一约束，可以设置表中输入的字段值都是唯一的，和主键约束非常相似，不同的是唯一约束在一个表中可以有多个，而主键约束只能有一个，而且唯一约束允许null值，主键约束不允许。

语法：

ALTER TABLE tablename

ADD [CONSTRAINT constraintname] UNIQUE (columnname);

实例：（希望库存中的每一本书都有一个不同的书名 ）

ALTER TABLE books

ADD CONSTRAINT book_title_uk UNIQUE(title);

 

4.4          检查约束

CHECK约束是检查约束，能够规定每一个列能够输入的值，以保证数据的正确性。

语法：

ALTER TABLE tablename

ADD [CONSTRAINT constraintname] CHECK (condition);

实例：（解决输入错误发货日期的问题）

ALTER TABLE orders

ADD CONSTRAINT orders_shipdate_ck

CHECK(orderdate<=shipdate);

 

4.5          非空约束

NOT NULL约束是非空约束，用于确保字段必须要输入值。可以认为是一种特殊的check约束

语法：

ALTER TABLE tablename

MODIFY (columnname[CONSTRAINT constraintname]

NOT NULL);

实例：

ALTER TABLE books

MODIFY(pubid CONSTRAINT book_pubid_nn NOT NULL);

 

以上设置非空约束的方式很少用，一般是在创建表的时候指定该字段不能为空：

案例：

CREATE TABLE acctmanager

(amid VARCHAR2(4) PRIMARY KEY,

amname VARCHAR2(20) NOT NULL,

amedate DATE DEFAULT SYSDATE,

region CHAR(2) NOT NULL

);

4.6          约束的其他操作

a)         查看约束

SELECT constraint_name,constraint_type,search_condition

FROM user_constraints

WHERE table_name='table_name';

b)         禁用/启用约束

ALTER TABLE table_name

DISABLE CONSTRAINT constraint_name;

ALTER TABLE table_name

ENABLE CONSTRAINT constraint_name;

c)         删除约束

ALTER TABLE tablename

DROP PRIMARY KEY|UNIQUE(columnname)|CONSTRAINT constraintname;

 

5           数据操纵语言（DML）和数据查询语言（DQL）

DML就是用来操纵数据库中数据所使用的语言，即对数据进行增加、删除、修改、查询的操作。其中对数据的查询也称数据查询语言。

命令	说明
INSERT	向表添加新行。用户可以包括一个子查询来复制现有表的行
UPDATE	向现有的行添加数据或者修改现有行中的数据
DELETE	删除表中的行
SELECT	查询表中数据
ROLLBACK	允许用户“撤销”对数据未提交的更改
COMMIT	更改后的数据永久地保存在表中
LOCK TABLE	禁止其他用户更改表
SELECT…FOR UPDATE	在表上创建一个共享锁，以便禁止其他用户更改制定列中的数据

                学习DML之前先来了解一下事务管理。使用INSERT INTO 和UPDATE命令执行的操作都是对表的内部包含的数据进行的更改，而没有改变表的实际结构，对数据所做的更改并没有永久的保存到这个表中，用户可以执行“事务控制”语句，保存修改后的数据或者在他们犯了错误时撤销更改。事务控制命令如下：

•  COMMIT命令 ：提交对表所做的操作
•  ROLLBACK命令 ：撤销在上一次执行COMMIT命令之后执行的所有DML操作

对应到工具上一般是如所示按钮，点击绿色按钮表示执行COMMIT命令提交所做操作，点击红色按钮执行ROLLBACK命令撤销所做操作。

 

5.1          添加数据用insert

a)        直接添加数据

语法：

INSERT INTO tablename[(columnnae,…)]

VALUES(datavalue,…)

实例：(向books表中添加一条记录)

INSERT INTO BOOKS VALUES ('2147428900', 'WORLD NEWS', TO_DATE('01-05-08',’DD-MM-YY’),  5,21.85,39.95,  'LITERATURE');

注意：如果VALUES输入的数据与表中的列的顺序相同并且没有空余的列，那么可以忽略列名；如果只输入了某些列或列出的列的顺序与表中列出的顺序不同，必须在INSERT INTO后面提供这些列名称。

 

b)        通过其他数据表向表中添加数据

语法：

INSERT INTO table_name1 (columnnae,…)

SELECT columnnae, … FROM table_name2

注意：table_name1和table_name2对应的数据类型必须一致

 

5.2          修改数据用update

a)        修改表中指定字段的全部值

语法：

UPDATE table_name SET column_name1=data1,column_name2=data2,… [WHERE condition]

实例：

Update table set tableName=’test’;

b)        根据条件修改指定字段的值

语法：

UPDATE tablename

SET columnname = new_datavalue

[WHERE condition];

实例：（更新编号为J500的人员的聘用日期 ）

UPDATE acctmanager

SET amedate = TO_DATE('2008-01-12', ' YYY-MM-DD')

WHERE  amid='J500';

 

5.3          删除数据用delete

语法：

DELETE  FROM tablename [WHERE condition];

实例：

DELETE FROM acctmanager

WHERE amid = 'J500';

 

5.4          查询数据用select

数据查询语言也称为DQL。

语法：

SELECT column_name,…  FROM table_name WHERE [condition];

实例：(查询loginname是AAA的记录信息，*是所有列的缩写)

Select  *  from login where loginname=’AAA’;

 

5.5          其他数据操纵语言

a)        Truncate语句

Truncate就是截断表，截断一个表时，表中包含的所有行都将被删除，但表本身将会保留。实际上与删除一个表中的所有行是相同的，但是，如果只是删除一个表中的所有行，那么这些行多占用的存储空间仍然是分配给这个表的。要想删除存储在一个表中的行并释放这些行所占有的存储空间，可以使用TRUNCATE TABLE命令。TRUNCATE删除更快一些。

语法：

TRUNCATE TABLE tablename;

 

b)        Merge语句

Merge语句与update语句的功能类似，都是修改数据表中的数据的，但是使用merge语句可以对数据表同时进行增加和修改的操作。

语法：

MERGE [INTO] table_name1

   USING table_name2

   ON (condition)

  WHEN MATCHED THEN merge_update_clause

  WHEN NOT MATCHED THEN merge_insert_clause;

注：以condition作为条件进行判断，如果条件匹配就执行merge_update_clause表达式，如果条件不匹配则执行merge_insert_clause，merge_update_clause和merge_insert_clause都是可以省略的，但是只能省略一个，如果两个都省略了那merge语句就失去意义了。

 

6           数据控制语言（DCL）

数据控制语言为用户提供权限控制命令，具体的命令有：

•   GRANT 授予权限
•   REVOKE 撤销已授予的权限

实例：

GRANT SELECT, UPDATE ON order_master TO DAVID;

REVOKE SELECT, UPDATE ON books FROM DAVID;