管理数据库

SQL

定义数据结构

基本数据类型

• 定长字符串
  CHARACTER(n)
  定长字符串具有相同的长度，n 定义了字段中能够保存的最多的字符数量，数量不足的字符将由空格填充。
• 变长字符串
  CHARACTER VAEYING(n)
  变长字符串的长度不固定，n 表示字段中能够保存的最多的字符数量。
• 大对象类型
  专门用于保存大数据集的数据类型，常见的常见的有 BLOB 和 TEXT。
• 数值类型
  数值类型一般包括 NUMBER、INTEGER、REAL、DECIMAL等
• 小数类型
  DECIMAL(p, s)
  小数类型是包含小数点的数值，p 表示字段的最大长度，s 表示小数点后面的位数。
• 整数
• 浮点数
  浮点数是有效位数和标度都可变并且没有限制的小数数值。
• 日期和时间类型
• 直义字符串
  直义字符串通常由单引号包围。
• NULL 数据类型
• 布尔值
• 自定义类型
  自定义类型允许用户根据已有的数据类型来定制自己的数据类型，语句 CREATE TYPE 用于创建自定义类型。
• 域
  域是能够被使用的有效数据类型的集合。可以向域中添加约束，约束与数据类型共同作用进一步限制字段能够接受的数据。

管理数据库对象

规划是与数据库某个用户名相关联的数据库对象的集合。数据库里可以有一个或多个规划。当用户创建了一个对象时就是在自己的规划里创建了它。

使用用户名 USER1 创建一个名为 EMPLOYEE_TBL 的表，这个表的实际名称是 USER1.EMPLOYEE_TBL。当访问自己所拥有的表时不用加规划名称，如果其他用户要访问这个表就必须要加规划名。

CREATE TABLE语句

创建表的基本语法如下所示

CREATE TABLE table_name
( field1 data_type [ not null ],
  field1 data_type [ not null ],
  field1 data_type [ not null ] );

示例

CREATE TABLE EMPLOYEE
(EMP_ID     CHAR(9)     NOT NULL,
EMP_NAME    VARCHAR(48) NOT NULL,
EMP_ST_ADDR VARCHAR(20) NOT NULL,
EMP_PHONE   INTEGER(10) NULL);

ALTER TABLE命令

这个命令可以对已创建的表进行修改，可以添加列、修改列定义、添加和去除约束。

基本语法如下

alter table table_name [modify] [column column_name][datatype|null not null]
[restrict|cascade]
[drop]    [constraint constraint_name]
[add]     [column] column definition

修改表的元素

列的属性指的是其所包含的数据规则和行为，利用这个命令可以修改列的属性。

ALTER TABLE EMPLOYEE_TBL MODIFY
EMP_ID VARCHAR(10);

添加列

如果表中已经包含数据，那么添加的列就不能定义为 NOT NULL,如果要添加的话需要添加一列，定义为 NULL，然后在每条记录中都插入数据，最后再将列的定义修改为 NOT NULL。列的默认属性是 NULL，可以不必明确设置。

添加自动增加的列

使用 IDENTITY 类型可以添加自动增加的列。

CREATE TABLE TEST_INCREMENT(
        ID     INT IDENTITYY(1, 1) NOT NULL,
        TEST_NAME   VARCHAR(20));

从现有表新建另一个表

利用 CREATE TABLE 语句与 SELECT 语句的组合可以复制现有表。

create table new_table_name as
select [ *|column1, column2 ]
from table_name
[ where ]

删除表

drop table table_name

在 SQL Server 中删除表必须要删除与该表有引用关系的所有对象。

完整性约束

完整性约束用于确定关系型数据库里数据的准确性和一致性。

主键约束

主键在表里的每条记录都具有唯一的值，主键在创建表时指定。以下两种方法都是可行的。

CREATE TABLE EMPLOYEE
(EMP_ID     CHAR(9)     NOT NULL PRIMARY KEY,
EMP_NAME    VARCHAR(48) NOT NULL,
EMP_ST_ADDR VARCHAR(20) NOT NULL,
EMP_PHONE   INTEGER(10) NULL);

CREATE TABLE EMPLOYEE
(EMP_ID     CHAR(9)     NOT NULL,
EMP_NAME    VARCHAR(48) NOT NULL,
EMP_ST_ADDR VARCHAR(20) NOT NULL,
EMP_PHONE   INTEGER(10) NULL,
PRIMARY KEY (EMP_ID));

唯一性约束

唯一性约束唯一性约束要求某个字段在每条记录中都是唯一的，跟主键约束非常相似。

CREATE TABLE EMPLOYEE
(EMP_ID     CHAR(9)     NOT NULL PRIMARY KEY,
EMP_NAME    VARCHAR(48) NOT NULL,
EMP_ST_ADDR VARCHAR(20) NOT NULL UNIQUE,
EMP_PHONE   INTEGER(10) NULL);

外键约束

CREATE TABLE EMPLOYEE
(EMP_ID     CHAR(9)     NOT NULL,
EMP_NAME    VARCHAR(48) NOT NULL,
EMP_ST_ADDR VARCHAR(20) NOT NULL,
EMP_PHONE   INTEGER(10) NULL);
CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL

NOT NULL 约束

检查约束

检查约束可以对检查输入的数据的有效性做出约束。

CONSTRAINT CHK_EMP_ZIP CHECK( EMP_ZIP = '46234' )
--检查 EMP_ZIP 是否等于 '46234'
CONSTRAINT CHK_EMP_ZIP CHECK( EMP_ZIP in ('46234','46277','46745') )
--检查 EMP_ZIP 是否在上述值之中
CONSTRAINT CHK_PAY CHECK( PAY_RATE > 12.50 )
--检查 PAY_RATE 是否大于 12.50

去除约束

可以通过定义约束时设置的约束名来去除约束

ALTER TABLE EMPLOYEES DROP CONSTRANT EMPLOYEES_PK;

SQL还提供了去除特定约束的快捷方式

ALTER TABLE EMPLOYEES DTOP PRIMARY KEY;

操作数据

INSERT 命令

基本语法，使用这种语法时 VALUES 列表里必须要包含表里的每个列，而且要使用创建列时的顺序。

INSERT INTO TABLE_NAME
VALUES ('value1','value2', [ NULL ]),
       ('value1','value2', [ NULL ]);

也可以仅对指定的字段插入。

INSERT INTO EMPLOYEE_TBL
(EMP_ID, LAST_NAME, FIRST_NAME)
VALUES
('1', 'SMITH', 'JOHN'),
('2', 'TITO', 'JOHN');

利用 INSERT 语句和 SELECT 语句的组合可以实现从另一个表中插入数据。

insert into table_name [('column1', 'column2')]
select [*|('column1', 'column2')]
from table_name
[where condition(s)];

UPDATE 命令

UPDATE 语句用于更新表里的一列数据。

update table_name
set column_name = 'value'
[where condition];

UPDATE 语句通常与 WHERE 子句一起使用，当没有使用 WHERE 子句时 UPDATE 命令会更新所有记录的响应字段。

更新多个字段。

update table_name
set column1 = 'value',
   [column2 = 'value',]
   [column3 = 'value']
[where condition];

DELETE 命令

delete from table_name
[ where condition ];

DELETE 语句可以删除一行或者多行数据，如果没有 WHERE 子句，那么表中所有数据都会被删除。

管理数据库事务

事务是对数据库执行的一个操作单位，事务是对数据库所做的一个或多个修改。所有事务都会暂时保存在回退区域内，直到被提交或者被回退。在 SQL SERVER 中需要手动设定 BEGIN TRANSACTION 才会将事务保存在回退区域。

COMMIT 命令

commit [ work ];

COMMIT 命令用于把事务所做的修改保存到数据库上，它把上一个 COMMIT 或 ROLLBACK 命令之后的全部事务保存到数据库上。对于大规模的数据加载或者撤销应该多使用 COMMIT，因为一旦临时回退区域没有空间了，数据库就可能挂起，禁止进一步的事务。

ROLLBACK 命令

rollback [ work ];

ROLLBACK 命令用于撤销还没有被保存到数据库的命令。

SAVEPOINT 命令

savepoint savepoint_name

保存点是事务过程的一个逻辑点，可以借由 ROLLBACK TO SAVEPOINT 命令会退到这个点而不用回退整个事务。

ROLLBACK TO SAVEPOINT_NAME;

RELEASE SAVEPOINT 命令用于删除创建的保存点。

RELEASE SAVEPOINT SAVEPOINT_NAME

SET TRANSACTION 命令

SET TRANSACION READ WRITE;
SET TRANSACION READ ONLY;

这个命令用于初始化数据库事务，可以指定事务的特性。在生成报告时使用 READ ONLY 能够提高事务完成的速度。