数据表的基本操作

发表于 | 分类于 | | 阅读数
字数统计: 4.1k | 阅读时长 ≈ 15

数据表的基本操作

创建数据表

​ 所谓创建数据表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程。

创建表的语法形式

​ 数据表属于数据库，在创建数据表之前，我们需要指定我们操作哪个库下的哪个表，如果没有选择数据库会抛出‘No database selected’的错误。

​ 创建数据表的语句为CREATE TABLE <表名>;

1
2
3
4
5
6
7
CREATE TABLE <表名>
(
字段名1，数据类型[列级别约束条件][默认值],
字段名2，数据类型[列级别约束条件][默认值],
……
[表级别约束条件]
);

​ 使用CREATE TABLE创建表时，需要指定以下信息：

  • 要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等
  • 数据表中每一个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。

[e.g.]

首先创建数据库 test_db：

CREATE DATABASE test_db;

你想要把表创建在哪个数据库，就选择使用哪个数据库：

USE test_db;

创建一个名叫 tb_emp1的表：

1
2
3
4
5
6
7
CREATE TABLE tb_emp1
(
id		INT(11),
name	VARCHAR(25),
deptid	INT(11),
salary	FLOAT
);

查看表是否创建成功：

SHOW TABLES;

image-20181129093119599

（书P49页，第一句话中的tb_tmp1是错的，应该是tb_emp1）

另外，创建表之后还可以查看表结构

DESC tb_emp1;

image-20181129094143420

使用主键约束

​ 主键，又称做主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不能为空。主键的作用有，能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度（数据库最为重要的功能是查询！相信我，查询会让你头一个头两个大）。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的，主键是约束条件，人是一条记录，人的名字、爱好等是属性也就是字段，字段组成了一个完整的人，也就是一条字段。

单字段主键

​ 单字段主键由一个字段组成，sql语句格式分为以下两种情况。

  • 在定义列的同时指定主键

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    CREATE TABLE 表名
    (
    字段名 数据类型 PRIMARY KEY [默认值], 
        # [] 含义为 可有可无，没有就取默认值
        # 包括 数据类型指定的长度也是可有可无，没有就取默认值
        ...
    );

    # [e.g]
    CREATE TABLE tb_emp2
    (
    id		INT(11) PRIMARY KEY,
    name	VARCHAR(25),
    deptid	INT(11),
    salary	FLOAT
    );

  • 在定义完所有列之后指定主键

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE tb_emp3
    (
    id 		INT(11),
    name 	VARCHAR(25),
    deptid 	INT(11),
    salary	FLOAT,
    PRIMARY KEY(id)
    );

查看结果，执行后发现结果一样，都在id 字段上设置了主键约束。

image-20181129100606263

多字段联合主键

主键由多个字段联合组成

PRIMARY KEY [字段1，字段2，... ，字段n]

定义数据表tb_emp4,假设表中没有主键id，为了确定一个员工，可以将name、depid联合起来作为主键。

1
2
3
4
5
6
7
CREATE TABLE tb_emp4
(
name	VARCHAR(25),
deptid	INT(11),
salary	FLOAT,
PRIMARY KEY(name,deptid)
);

​ 查看结果，可以看到创建了一个名称为tb_emp4的表，name字段和deptid字段组合在一起成为了tb_emp4的多字段联合主键。

image-20181129101158362

使用外键约束

​ 外键用来在两个的数据之间建立链接，它可以是一列或者多列，一个表可以有一个或者多个外键。外键对应的事参照完整性，一个表的外键可以为空值，若不为空，则每一个外键值必须等于另一个表中主键的某个值。

​ 外键：首先它是表中的一个字段，它可以不是本表的主键，但对应另一个表的主键。外键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行（记录）（可以删除没有关联关系的行）。

[e.g.]

​ 部门表tb_dept的主键是id，在员工表tb_emp5中有一个字段deptid与部门表tb_dept中的id关联。

​ 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。

​ 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

创建外键的sql语法：

1
2
3
4
5
6
7
CREATE TABLE tb_emp5
(
字段名	数据类型 约束条件，
	...,
[CONSTRAINT <外键名>] FOREIGN KEY 字段名2[,字段名2,...]
REFERENCES <主表名> 主键列1 [,主键列2,...]
)

​ “外键名”为定义的外键约束的名称，一个表中不能有相同名称的外键；“字段名”表示子表需要添加外键约束的字段列；“主表名”即被子表外键所以来的表的名称；“主键列”表示主表中定义的主键列，或者列组合。

[e.g.]

创建一个部门表tb_dept1

1
2
3
4
5
6
CREATE TABLE tb_dept1
(
id			INT(11) PRIMARY KEY,
name		VARCHAR(22) NOT NULL,
location	VARCHAR(50)
);

定义数据表tb_emp5,让它的键deptid作为外键关联到tb_dept1的主键id

1
2
3
4
5
6
7
8
CREATE TABLE tb_emp5
(
id		INT(11) PRIMARY KEY,
name	VARCHAR(25),
deptid	INT(11),
salary	FLOAT,
CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptid) REFERENCES tb_dept1(id)
);

[小tips]

​ 关联指的是在关系型数据库中，相关表之间的联系。它是通过相容或相同的属性或属性组来表示的。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时，就会报错”ERROR 1005 (HY000):Can’t create table ‘database.tablename’(error:150)”。

使用非空约束

​ 非空约束（Not Null Constraint）指字段的值不能为空。如果用户在添加数据时，在非空约束字段，没有给指定值，数据库系统就会报错。

1
2
3
4
5
6
7
CREATE TABLE tb_emp5
(
字段名	数据类型 NOT NULL，
	...
)
# 虽然说大小写无关，字段名可以写成一行，但是大写和一定格式的书写，
# 能让你一目了然，更好的理解。

[e.g.]

定义数据表 tb_emp6，指定员工的名称不能为空

1
2
3
4
5
6
7
CREATE TABLE tb_emp6
(
id		INT(11) 	PRIMARY KEY,
name	VARCHAR(25) NOT NULL,
deptid	INT(11)	,
salary	FLOAT
); # 注意分号！！！！

查看结果，发现tp_emp6表中的name字段的 Null 列为NO，意味着，插入之不能为空。

image-20181129121204479

使用唯一性约束

​ 唯一性约束（Unique Constraint）要求该列唯一，允许为空，只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

唯一性约束语法：字段名 数据类型 UNIQUE

这里仅仅是写了唯一性约束的语法，没有写在完整的创建表时定义字段的那部分中。

  1. 在定义完列之后直接指定唯一约束：

    1
    2
    3
    4
    5
    6
    CREATE TABLE tb_dept2
    (
    id			INT(11)		PRIMARY KEY,
    name		VARCHAR(22)	UNIQUE,  
    location	VARCHAR(50)
    );

  2. 在定义完所有列之后指定唯一约束

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE tb_dept3
    (
    id			INT(11)		PRIMARY KEY,
    name		VARCHAR(22)	UNIQUE,  
    location	VARCHAR(50),
    CONSTRAINT	STH UNIQUE(name)
    );

    以上两种方式都可以用来指定唯一约束。

    UNIQUE 和 PRIMARY KEY 是有区别的：

    • 一个表中可以有多个字段声明为UNIQUE，但只能有一个PRIMARY KEY声明
    • PRIMARY KEY 是唯一且不能有空值，而UNIQUE允许空值的存在
    • PRIMARY KEY 当表定义主键将自动创建主键索引，而UNIQUE NOT NULL的组合没有索引一说

使用默认约束

​ 默认约束（Default Constraint）指定某列的默认值。如男性同学较多，性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。

默认约束的语法：字段名 数据类型 DEFAULT 默认值

[e.g.]

1
2
3
4
5
6
7
CREATE TABLE tb_emp7
(
id		INT(11)		PRIMARY KEY,
name	VARCHAR(25)	NOT NULL,
deptid	INT(11) 	DEFAULT 1111,
salary 	FLOAT
);

image-20181129230022612

​ 可以发现字段deptid行的default列有了默认值为1111，意味着当你在新插入的记录如果没有指定部门编号，则默认值为1111。

[小tips]

​ 当你插入NULL时，默认值会被改变，NULL被当做一个值插入，不会采用默认值。

设置表的属性值自动增加

​ 在数据库应用中，经常希望在每次插入新记录的时候，系统自动生成字段的主键值，这时我们就可以通过为表的主键添加 AUTO_INCREMENT 关键字来实现。**默认的，在MySQL 中AUTO_INCREMENT的初始值为1，每新增一条记录，字段值自动+1。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLIN、INT、BIGINT等）。

设置表的属性值自动增长的语法：

字段名 数据类型 AUTO_INCREMENT

[e.g.]

1
2
3
4
5
6
7
CREATE TABLE tb_emp8
(
id		INT(11)		PRIMARY KEY AUTO_INCREMENT,
name	VARCHAR(25)	NOT	NULL,
deptid	INT(11),
salary	FLOAT
);

image-20181129230935539

​ 可以发现在表tb_emp8中，字段名id行的Extra列多了个auto_increment，当你在插入记录的时候，默认的自增字段ID的值从1开始，每次添加一条新纪录，该值自动加1。

查看数据表结构

​ 使用SQL语句创建好数据表之后，可以查看表结构的定义，以确认表的定义是否正确。在MySQL中，查看表结构可以使用DESCRIBE 和SHOW CREATE TABLE语句。

查看表基本结构语句 DESCRIBE

​ DESCRIBE/DESC 语句可以查看表的字段信息，其中包括：字段名、字段数据类型、是否为主键、是否有默认值等。其实之前已经用到过很多次了。

语法规则：

DESCRIBE 表名;

DESC 表名;（简写）

image-20181129235302132

其中，各个字段的含义分别解释：

  • NULL: 表示该列是否可以存储NULL值
  • KEY:表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
  • DEFAULT:表示该列是否有默认值，如果有的话值是多少。
  • EXTRA:表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

查看表详细结构语句 SHOW CREATE TABLE

​ SHOW CREATE TABLE 语句可以用来显示创建表时的CREATE TABLE语句。

语法格式：

SHOW CREATE TABLE 表名;

image-20181130000130950

SHOW CREATE TABLE 表名\G

image-20181130000300888

修改数据表

​ 修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE语句修改表。常用的修改表的操作有：修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

修改表名

语法规则

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

其中TO为可选参数，使用与否均不影响结果。

[e.g.]

将数据表tb_dept1改名为tb_deptment1

  1. 查看数据库中的所有表

    SHOW TABLES;

    image-20181130161246927

  2. 将数据表tb_dept1改名为tb_deptment1

    ALTER TABLE tb_dept1 RENAME tb_deptment1;

    image-20181130161419225

[小tips]

​ 修改表名称后，可以通过DESC命令来查看修改前后两个表的结构，修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构必然是相同的。

修改字段的数据类型

​ 修改字段的数据类型，就是把字段的数据类型转换成另外一种数据类型。

语法规则：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

​ 其中，‘表名’ 指的是你想要修改的数据类型所在的表的名称，‘字段名’指的是需要修改的字段，‘数据类型’指的是修改后的字段的新数据类型。

[e.g.]

将数据表 tb_dept1 中 name 字段的数据类型由 VARCHAR(22) 修改成 VARCHAR(30)

  1. 查看表结构

    DESC tb_dept1;

    image-20181130162104262

  2. 修改

    ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);

    image-20181130162215549

修改字段名

语法规则

ALTER TABLE <表名> CHANGE <旧字段> <新字段> <新数据类型>;

​ 其中，‘旧字段’指的是修改前的字段名；‘新字段’指的是修改后的字段名；‘新数据类型’指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样即可，但数据类型不能为空。

[e.g.]

将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变

  1. sql 语句

    ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);

  2. 使用DESC查看表tb_dept1

    image-20181130192913299

  3. 将tb_dept1中loc字段名称再改回location，同时将数据类型变为VARCHAR(60)

    ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);

  4. 再次查看tb_dept1

    image-20181130193213418

[小tips]

​ CHANGE 和 MODIFY 有区别 MODIFY 单纯用来修改数据类型，而CHANGE 可以修改字段名和数据类型，但是CHANGE也可以实现和MODIFY一样的效果设置一样的新名字就可以，要注意修改数据类型，有可能会影响到数据表中已经有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。

添加字段

​ 随着业务需求的变化，可能需要在已经存在的表中添加新的字段。

添加字段的语法规则：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已经存在字段];

[小tips]

​ 注意 ：‘FIRST’ 或 ‘AFTER 已经存在字段名’，用于指定新增的字段在表中的位置，如果没有添加参数，则默认将新添加的字段设置为数据表的最后列。

添加无完整性约束条件的字段

[e.g.]

在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段 managerid

  1. sql语句

    ALTER TABLE tb_dept1 ADD managerid INT(10);

  2. 查看表tb_dept1

    image-20181130194324583

添加有完整性约束条件的字段

[e.g.]

在数据表tb_dept1中添加一个不能为空的 VARCHAR(12)类型的字段column1

  1. sql语句

    ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) NOT NULL;

  2. 查看表tb_dept1

    image-20181130194531269

在表的第一列添加一个字段

[e.g.]

在数据表tb_dept1 中添加一个INT类型的字段column2

  1. sql语句

    ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;

  2. 查看表tb_dept1

    image-20181130195052065

在表的指定列之后添加一个字段

[e.g.]

在数据表tb_dept1 中 name列后添加一个INT类型的字段column3

  1. sql语句

    ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;

  2. 查看表tb_dept1

    image-20181130195243048

删除字段

​ 删除字段是将数据表中的某个字段从表中移除

语法规则：

ALTER TABLE <表名> DROP <字段名>;

[e.g.]

删除数据表tb_dept1表中的column2字段

  1. 查看tb_dept1表结构

    DESC tb_dept1;

    image-20181130195653550

  2. sql语句

    ALTER TABLE tb_dept1 DROP column2;

  3. 查看tb_dept1表

    DESC tb_dept1;

    image-20181130195708927

修改字段的排列位置

​ 对于一个数据表来说，在创建的时候，字段在表中的顺序已经确定了，但是表的结构并不是完全不可以改变的，可以通过ALTER TABLE来改变表中的字段的相对位置。

语法规则：

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

​ ‘字段1’指要修改的字段位置，‘数据类型’指‘字段1’的数据类型，‘FIRST’为可选参数，指将‘字段1’修改为表的第一个子弹，‘AFTER 字段2’指将‘字段1’插入到‘字段2’后面。

修改字段为表的第一个字段
修改字段到表的指定列之后

更改表的存储引擎

删除表的外键约束

删除数据表

删除没有被关联的表

删除被其他表关联的主表

综合案例——数据表的基本操作

专家解惑

  1. 表删除操作需谨慎
  2. 每一个表中都要有一个主键么？
  3. 并不是每个表都可以任意选择存储引擎
  4. 带AUTO_INCREMENT约束的字段值是从1开始的么？