[Mysql] 创建和操纵数据表

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要极为小心，应该在进行改动前做一个完整的备份(模式和数据的备份)，数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据