sql

sql-建表建库

wujiawen 发布于

阅读量

建表、改表、删表、建库、删库

建库 create database 语句

CREATE DATABASE 语句用于创建数据库

语法

1
CREATE DATABASE dbname;

示例

1
CREATE DATABASE my_db;

建表 create table 语句

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

语法

1
2
3
4
5
6
7
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

示例

1
2
3
4
5
6
7
8
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

约束

SQL 约束用于规定表中的数据规则
如果存在违反约束的数据行为，行为会被约束终止
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）

语法

1
2
3
4
5
6
7
8
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);
-- constraint_name 是具体约束

在 SQL 中，我们有如下约束：

  • NOT NULL - 指示某列不能存储 NULL 值

  • UNIQUE - 保证某列的每行必须有唯一的值

  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合
    确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录

  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性

  • CHECK - 保证列中的值符合指定的条件

  • DEFAULT - 规定没有给列赋值时的默认值

创建索引 create index 语句

CREATE INDEX 语句用于在表中创建索引
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据

在表中创建索引，以便更加快速高效地查询数据
用户无法看到索引，它们只能被用来加速搜索/查询
注释：
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间
这是由于索引本身也需要更新
因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引

语法

1
2
3
4
5
6
7
-- 简单索引 允许使用重复值
CREATE INDEX index_name
ON table_name (column_name)

-- 唯一索引 不允许使用重复值
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

示例

1
2
3
4
5
CREATE INDEX PIndex
ON Persons (LastName)

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

撤销索引、撤销表以及撤销数据库

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

DROP INDEX 语句
删除索引

1
2
-- Mysql
ALTER TABLE table_name DROP INDEX index_name

DROP TABLE 语句
删除表

1
DROP TABLE table_name

DROP DATABASE 语句
删除数据库

1
DROP DATABASE database_name

TRUNCATE TABLE 语句
删除表内的数据，但并不删除表本身

1
TRUNCATE TABLE table_name

修改表 ALTER TABLE 语句

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

语法

1
2
3
4
5
6
7
8
9
10
11
-- 在表中添加列
ALTER TABLE table_name
ADD column_name datatype

-- 删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name

-- 改变表中列的数据类型(MySQL / Oracle)
ALTER TABLE table_name
MODIFY COLUMN column_name datatype

自增 AUTO INCREMENT 字段

Auto-increment 会在新记录插入表中时生成一个唯一的数字
如：自动地创建主键字段的值

语法

1
2
3
4
5
6
7
8
9
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)
)

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

改变初始值

1
ALTER TABLE Persons AUTO_INCREMENT=100

视图（Views）

放在前面

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

视图是可视化的表
是基于 SQL 语句的结果集的可视化的表

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

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

语法

创建视图 create view

1
2
3
4
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

更新视图 create or replace view

1
2
3
4
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

撤销视图

1
DROP VIEW view_name

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

视图的作用：

  • 视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系

  • 视图提供了一个统一访问数据的接口（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）

  • 从而加强了安全性，使用户只能看到视图所显示的数据

  • 视图还可以被嵌套，一个视图中可以嵌套另一个视图

表复制

完整的复制MySQL数据表（包括表结构以及数据）

分三步

  1. 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等

  2. 复制第一步命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构

  3. 使用 INSERT INTO … SELECT 语句复制表的内容