MySQL创建和操纵表

目录

1、创建表

（1）使用基础SQL语句创建表-简单的 CREATE TABLE

（2） 使用NULL值

（3） 指定默认值

（4）指定主键或外键 

2、更新、删除表

（1）更新表 -- ALTER TABLE

（2）删除表 -- DROP TABLE

4、小结

博主用的是mysql8 DBMS，附上示例资料：

百度网盘链接: https://pan.baidu.com/s/1XaWi3Y7hpXbs_uHq2cPI6Q

提取码: fpnx

1、创建表

一般有两种创建表的方法：

• 多数 DBMS 都具有交互式创建和管理数据库表的工具；
• 表也可以直接用 SQL 语句操纵。

（1）使用基础SQL语句创建表-简单的 CREATE TABLE

利用 CREATE TABLE 创建表，必须给出下列信息：

• 新表的名字，在关键字 CREATE TABLE 之后给出；
• 表列的名字和定义，用逗号分隔；
• 有的 DBMS 还要求指定表的位置。

-- CREATE TABLE 表名 + (列名 类型（长度） 是否NULL  默认)
-- 不能为NULL的值，需要指定NOT NULL，可以为NULL的值可以不指定也可以指定NULL
CREATE TABLE Products_new
( 
 prod_id CHAR(10) NOT NULL, 
 vend_id CHAR(10) NOT NULL, 
 prod_name CHAR(254) NOT NULL, 
 prod_price DECIMAL(8, 2) NOT NULL, 
 prod_desc TEXT NULL                   -- 对于 MySQL，varchar 必须替换为 text
);

（2） 使用NULL值

NULL 值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值。

每个表列要么是 NULL 列，要么是 NOT NULL 列，这种状态在创建时由表的定义规定。

-- 举例而已
CREATE TABLE Orders 
( 
 order_num INTEGER NOT NULL, 
 order_date DATETIME NULL, 
 cust_id CHAR(10) NOT NULL 
);

 NULL 为默认设置，如果不指定 NOT NULL，就认为指定的是 NULL。

提示：主键和 NULL 值

第1课介绍过，主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键，允许 NULL 值的列不能作为唯一标识。

•  以下是指定主键的例子

-- 举例而已
CREATE TABLE Orders 
( 
 order_num INTEGER NOT NULL PRIMARY KEY, 
 order_date DATETIME NULL, 
 cust_id CHAR(10) NOT NULL 
);

（3） 指定默认值

SQL 允许指定默认值，在插入行时如果不给出值，DBMS 将自动采用默认值。

默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。

-- 对于系统日期：MySQL的DATETIME类型需要的值为CURRENT_TIMESTAMP
CREATE TABLE Orders_new
( 
 order_num INTEGER NOT NULL PRIMARY KEY, 
 order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 cust_id CHAR(10) NOT NULL 
);

（4）指定主键或外键 

• 单列主键：列定义时加上 PRIMARY KEY 即可将该列定义为主键，但是这种方式只能指定单列主键。

• 复合主键：在列定义完成后一行使用 PRIMARY KEY (col1, col2 ...) 来指定复合主键。

-- 单列主键
CREATE TABLE Orders_new
( 
 order_num INTEGER NOT NULL PRIMARY KEY, 
 order_date DATETIME NOT NULL, 
 cust_id CHAR(10) NOT NULL 
);


-- 复合主键
CREATE TABLE OrderItems_new2
( 
 order_num INTEGER NOT NULL, 
 order_item INTEGER NOT NULL, 
 prod_id CHAR(10) NOT NULL, 
 quantity INTEGER NOT NULL DEFAULT 1, 
 item_price DECIMAL(8,2) NOT NULL,
 PRIMARY KEY (order_num, order_item)
);

• 外键用于在一个表中存储另一个表的主键值，建立两个表之间的引用关系。它可以保证数据的完整性，防止出现不一致的数据，例如在子表中插入一个在父表中不存在的主键值。

• 在创建表时指定外键，只需要在列定义完成后使用 

FOREIGN KEY (prod_id) REFERENCES products(prod_id)

-- 使用复合主键、外键、DEFAULT默认值和NULL值
CREATE TABLE OrderItems_new3
( 
 order_num INTEGER NOT NULL, 
 order_item INTEGER NOT NULL, 
 prod_id CHAR(10) NOT NULL, 
 quantity INTEGER NOT NULL DEFAULT 1, 
 item_price DECIMAL(8,2) NOT NULL,
 PRIMARY KEY (order_num, order_item),
 FOREIGN KEY (prod_id) REFERENCES products(prod_id)
);

2、更新、删除表

（1）更新表 -- ALTER TABLE

• 新增列：

-- 新增列
ALTER TABLE orders_new
ADD COLUMN vend_phone CHAR(20);

• 修改已有列：

-- 修改已有列
ALTER TABLE orders_new
MODIFY COLUMN vend_phone CHAR(30);

• 删除列：

-- 删除列
ALTER TABLE orders_new
DROP COLUMN vend_phone;

• 重命名表：

-- 重命名表
-- ALTER TABLE
ALTER TABLE orders_new RENAME TO orders_new1;

-- RENAME TABLE
RENAME TABLE orders_new TO orders_new1;

（2）删除表 -- DROP TABLE

-- 删除表
DROP TABLE orders_new;

4、小结

1. 创建表：CREATE TABLE
2. 列定义：name + data_type(len) + NULL? + DEFAULT?
3. 创建表时指定主键和外键的语法
4. 更新表：ALTER TABLE
5. 新增列、修改已有列、删除列、重命名表的两种方法
6. 删除表：DROP TABLE