1.创建表
创建表的作用在于指定表字段的类型+属性(约束)
创建表使用CREATE TABLE关键字进行操作
语法结构
CREATE TABLE 表名(
字段名 数据类型 属性/约束,
...
字段名 数据类型 属性/约束,
[PRIMARY KEY(字段名)]
);
注意
1.表名和字段名需唯一
2.属性/约束中,默认允许设置NULL
理解NULL
NULL不等于空串,空串是有效值
不要把NULL值与空串相混淆。NULL值是没有值,它不是空串。如果指定''(两个单引号,其间没有字符),这在NOT NULL列中是允许的。空串是一个有效的值,它不是无值。NULL值用关键字NULL而不是空串指定
3.属性/约束中,默认值DEFAULT设置必须是常数,不允许使用函数作为默认值
4.属性/约束AUTO_INCREMENT
每个表只允许一个AUTO_INCREMENT列
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
cust_city char(50) NULL,
.....
PRIMARY KEY(cust_id)
);
AUTO_INCREMENT告诉Mysql,本列每当增加一行记录时进行自动增量。每次执行一个INSERT操作,Mysql自动对该列增量,给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id,从而可以用作主键值
例:在test数据库中创建pet表
CREATE TABLE pet
(
id int NOT NULL AUTO_INCREMENT,
name VARCHAR(225) NOT NULL,
owner VARCHAR(225) DEFAULT 'Odin',
species VARCHAR(225),
sex CHAR(1),
birth DATE,
death DATE,
PRIMARY KEY(id)
);
结果展示:
在创建新表时,指定的表名必须不存在,否则将出错。如果要防止意外覆盖已有的表,SQL要求首先手动删除该表,然后再重建它,而不是简单地用创建表语句覆盖它。如果仅想在一个表不存在时创建它,应该在表名后给出IF NOT EXISTS,它不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在,并且仅在表名不存在时创建它
扩展练习案例
问题:创建一个actor表,包含如下列信息
列表 | 类型 | 是否为NULL | 含义 |
actor_id | smallint(5) | NOT NULL | 主键id |
first_name | varchar(45) | NOT NULL | 名字 |
last_name | varchar(45) | NOT NULL | 姓氏 |
last_update | date | NOT NULL | 日期 |
DROP TABLE IF EXISTS actor;
CREATE TABLE actor(
actor_id smallint(5) NOT NULL COMMENT'主键id',
first_name varchar(45) NOT NULL COMMENT'名字',
last_name varchar(45) NOT NULL COMMENT'姓氏',
last_update date NOT NULL COMMENT'日期',
PRIMARY KEY(actor_id)
);
2.删除表
删除表使用DROP TABLE关键字进行操作,删除的是整个表而不是其内容
语法结构
DROP TABLE 表名;
例:删除test数据库中的pet表
DROP TABLE pet;
结果展示:
删除表没有确认,也不能撤销,执行这条DROP TABLE语句将永久删除指定数据表,因此删除数据表的操作应该谨慎使用,一旦删除了数据表,那么表中的数据将会全部清除,没有备份则无法恢复
在删除数据表的过程中,如果删除一个不存在的表将会产生错误,这时我们可以在删除语句中加入if exists关键字就可避免出错,格式如下所示
DROP TABLE IF EXISTS 数据表名;
3.查看表
1.查看表的创建语句
语法结构
SHOW CREATE TABLE 表名;
例:查看pet表的创建语句
SHOW CREATE TABLE pet;
结果展示:
2.查看当前数据库中所有表
SHOW TABLES;
结果展示:
3.查看表结构
语法结构
DESC 表名;
例:查看pet表结构
DESC pet;
结果展示:
在查看表结构时,也可以只列出某一列的信息,语法格式如下
DESC 数据表名 列名;
例:查看pet数据表中的birth列信息
可以使用show columns命令查看指定数据表的表结构,格式如下所示
SHOW COLUMNS FROM 数据库名.数据表名;
例:查看数据表pet的表结构
4.复制表
语法结构
CREATE TABLE 表名
AS
目标二维表;
注意
1.表名要唯一
2.目标二维表
(1)任何形式的SELECT语句
(2)表结构由SELECT字段决定
(3)表记录由WHERE条件决定
案例1:复制pet表至表pet1
案例2:复制pet表的id/name/owner列至表pet3,使pet3表中的id列值添加前缀'T',其余列与pet表一模一样
补充练习案例:创建一个actor_name表
数据导入
drop table if exists actor;
CREATE TABLE actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update datetime NOT NULL);
insert into actor values ('1', 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('2', 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');
actor表
问题:创建一个actor_name表,并且将actor表中的所有first_name以及last_name导入该表
CREATE TABLE actor_name
AS
SELECT first_name, last_name FROM actor;
结果展示:acotr_name表
5.重命名表
重命名表使用RENAME TABLE关键字进行操作
语法结构
RENAME TABLE 表名1 TO 新表名1,表名2 TO 新表名2...;
可批量,从左往右依次执行;可连续执行
test数据库中总共有三张数据表:pet表、pet2表、pet3表
使用RENAME TABLE语句可以重命名一个表
案例1:将pet表重命名为new_pet1表
RENAME TABLE pet TO new_pet1;
结果展示:
使用RENAME TABLE语句对多个表重命名
案例2:将pet2表和pet3表分别重命名为new_pet2表和new_pet3表
6.更新表
更新表使用ALTER TABLE关键字进行操作,在ALTER TABLE之后给出要更改的表名(该表必须存在,否则将出错)
1.修改表名
语法结构
ALTER TABLE 原表名 RENAME TO 新表名;
例子:将new_pet1表更名为pet1表
2.添加字段
必须明确其数据类型
语法结构
ALTER TABLE 表名 ADD 列名 类型;
例子:在pet1数据表中增加新字段'country',取默认值'China'
ALTER TABLE pet1 ADD country varchar(20) DEFAULT'China';
结果展示:
扩展练习案例:在last_update后面新增加一列名字为create_date
数据导入
drop table if exists actor;
CREATE TABLE actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update datetime NOT NULL);
actor表
问题:在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为'2020-10-01 00:00:00'
ALTER TABLE actor
ADD(create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00');
结果展示:
3.修改字段:不重命名版
语法结构
ALTER TABLE 表名 MODIFY 列名 类型及约束;
例子:将pet1表中的country字段的默认值'China'修改为'New Zealand'
ALTER TABLE pet1 MODIFY country varchar(20) DEFAULT'New Zealand';
结果展示:
4.修改字段:重命名版
语法结构
ALTER TABLE 表名 CHANGE 原名 新名 类型及约束;
例子:将pet1表中的name/country列分别更名为'pet_name'和'pet_country',并将原先country列的数据类型'varchar(20)'更改为'varchar(225)'
ALTER TABLE pet1 CHANGE name pet_name varchar(225) NOT NULL, CHANGE country pet_country varchar(225) DEFAULT 'New Zealand';
结果展示:
5.删除字段
语法结构
ALTER TABLE 表名 DROP 列名;
例子:删除pet1表中的owner列与pet_country列
ALTER TABLE pet1 DROP owner, DROP pet_country;
结果展示:
小心使用ALTER TABLE
使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份),数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们。类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据