SQL-高级命令（三）索引，表，数据库，视图（Views）；CREATE，DROP，ALTER，AUTO INCREMENT

素颜马尾好姑娘i 2023-10-03 11:46 6阅读 0赞

#### 1、SQL CREATE DATABASE ####

CREATE DATABASE 语句用于创建数据库

CREATE DATABASE dbname;

#### 2、SQL CREATE TABLE ####

CREATE TABLE 语句用于创建数据库中的表

CREATE TABLE table_name
    (
    column_name1 data_type(size),
    column_name2 data_type(size),
    column_name3 data_type(size),
    ....
    );

column\_name 参数规定表中列的名称

data\_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）

size 参数规定表中列的最大长度

创建库并设置字符集编码为utf8

create database '库名' default character set = 'utf8';

创建表并设置字符集编码为utf8

create table ‘表名’(id int(6),name char(10)) default character set = 'utf8';

3、SQL CREATE INDEX

CREATE INDEX 语句用于在表中创建索引

### 索引 ###

可以在表中创建索引，以便更加快速高效地查询数据。

用户无法看到索引，它们只能被用来加速搜索/查询。

**注：**

更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引

（1）SQL CREATE INDEX，允许使用重复的值

CREATE INDEX index_name
    ON table_name (column_name)

（2）SQL CREATE UNIQUE INDEX 语法

在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
    ON table_name (column_name)

**注：**用于创建索引的语法在不同的数据库中不一样。因此，检查您的数据库中创建索引的语法。

（3）CREATE INDEX 实例

在 "Persons" 表的 "LastName" 列上创建一个名为 "PIndex" 的索引：

CREATE INDEX PIndex
    ON Persons (LastName)

如果索引不止一个列，可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PIndex
    ON Persons (LastName, FirstName)

#### 4、SQL DROP（撤销索引、表以及数据库） ####

DROP 语句，可以轻松地删除索引、表和数据库

（1）DROP INDEX 语句

DROP INDEX 语句用于删除表中的索引。

MS Access 的 DROP INDEX 语法：

DROP INDEX index_name ON table_name

MS SQL Server 的 DROP INDEX 语法：

DROP INDEX table_name.index_name

DB2/Oracle 的 DROP INDEX 语法：

DROP INDEX index_name

MySQL 的 DROP INDEX 语法：

ALTER TABLE table_name DROP INDEX index_name

（2）DROP TABLE 语句

DROP TABLE 语句用于删除表。

DROP TABLE table_name

（3）DROP DATABASE 语句

DROP DATABASE 语句用于删除数据库。

DROP DATABASE database_name

（4）TRUNCATE TABLE 语句

如果我们仅仅需要删除表内的数据，但并不删除表本身

请使用 TRUNCATE TABLE 语句：

TRUNCATE TABLE table_name

#### 5、SQL ALTER TABLE ####

ALTER TABLE 语句用于在已有的表中添加、删除或修改**列**

（1）在表中添加列

ALTER TABLE table_name
    ADD column_name datatype

（2）删除表中的列（注：某些数据库系统不允许这种在数据库表中删除列的方式）：

ALTER TABLE table_name
    DROP COLUMN column_name

（3）改变表中列的数据类型

**SQL Server / MS Access：**

ALTER TABLE table_name
    ALTER COLUMN column_name datatype

**My SQL / Oracle：**

ALTER TABLE table_name
    MODIFY COLUMN column_name datatype

Oracle 10G 之后版本:

ALTER TABLE table_name
    MODIFY column_name datatype;

#### 6、SQL AUTO INCREMENT ####

Auto-increment 会在新记录插入表中时生成一个唯一的数字

在表中创建一个 auto-increment 字段，在每次插入新记录时，自动地创建主键字段的值。

**（1） MySQL 语法**

把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段：

CREATE TABLE Persons
    (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (ID)
    )

**MySQL** 使用 **AUTO\_INCREMENT** 关键字来执行 auto-increment 任务。

默认地，**AUTO\_INCREMENT 的开始值是 1，每条新记录递增 1**。

让 AUTO\_INCREMENT 序列以其他的值起始

ALTER TABLE Persons AUTO_INCREMENT=100

要在 "Persons" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)
    VALUES ('Lars','Monsen')

上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"ID" 列会被赋予一个唯一的值。"FirstName" 列会被设置为 "Lars"，"LastName" 列会被设置为 "Monsen"。

**（2）Oracle 的语法**

在 **Oracle** 中，代码稍微复杂一点。

必须通过 **sequence** 对象（该对象生成数字序列）创建 auto-increment 字段。

请使用下面的 CREATE SEQUENCE 语法：

CREATE SEQUENCE seq_person
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 10

上面的代码创建一个名为 seq\_person 的 sequence 对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。cache 选项规定了为了提高访问速度要存储多少个序列值。

要在 "Persons" 表中插入新记录，必须使用 **nextval** 函数（**该函数从 seq\_person 序列中取回下一个值**）：

INSERT INTO Persons (ID,FirstName,LastName)
    VALUES (seq_person.nextval,'Lars','Monsen')

上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"ID" 列会被赋值为来自 seq\_person 序列的下一个数字。"FirstName"列 会被设置为 "Lars"，"LastName" 列会被设置为 "Monsen"。

**（3） SQL Server 的语法**

把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段：

CREATE TABLE Persons
    (
    ID int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    )

MS SQL Server 使用 **IDENTITY** 关键字来执行 auto-increment 任务。

在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1。

**提示：**要规定 "ID" 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)。

要在 "Persons" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)
    VALUES ('Lars','Monsen')

**（4）Access 的语法**

把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段：

CREATE TABLE Persons
    (
    ID Integer PRIMARY KEY AUTOINCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    )

MS Access 使用 **AUTOINCREMENT** 关键字来执行 auto-increment 任务。

默认地，AUTOINCREMENT 的开始值是 1，每条新记录递增 1。

**提示：**要规定 "ID" 列以 10 起始且递增 5，请把 autoincrement 改为 AUTOINCREMENT(10,5)。

要在 "Persons" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)
    VALUES ('Lars','Monsen')

#### 7、SQL 视图（Views） ####

视图是可视化的表

**（1）SQL CREATE VIEW 语句**

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

CREATE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition

**注：**视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

**（2）SQL 更新视图**

SQL CREATE OR REPLACE VIEW 语法

CREATE OR REPLACE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition

**SQL Server**

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ] 
    [ WITH <view_attribute> [ ,...n ] ] 
    AS select_statement 
    [ WITH CHECK OPTION ] [ ; ]
    
    <view_attribute> ::= 
    { 
        [ ENCRYPTION ]
        [ SCHEMABINDING ]
        [ VIEW_METADATA ]     
    }

schema\_name: 视图所属架构的名称。  
view\_name: 要更改的视图。  
column: 将成为指定视图的一部分的一个或多个列的名称（以逗号分隔）。

**（3）SQL 撤销视图**

通过 DROP VIEW 命令来删除视图

DROP VIEW view_name