一、表的基本操作

1、数据类型

• 整型

• 浮点型

• 注意：可以使用float(M，D)、double(M，D)格式限制宽度按（M）和精度（D），如float（3,2），不指定M、D的时，会按照实际的精度来处理

• 定点型

• decimal(size,d) ，作为字符串存储的 DOUBLE 类型，允许固定的小数点，由于float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，而decimal无论写入数据中的数据是多少，都不会存在精度丢失问题，这就是要引入decimal类型的原因，decimal类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中，结论：float/double在db中存储的是近似值，而decimal则是以字符串形式进行保存

• 文本型

• blob和text都是为了存储很大数据而设计的字符串数据类型，blob采用二进制形式存储无排序规则和字符集，text采用字符形式存储且有排序规则和字符集适用于存储如文章内容、评论等

• char是固定长度字符串，其长度范围为0~255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足，char类型数据时会将结尾的所有空格处理掉

• char：适合存固定长度的内容（手机号、身份证）

• varchar：长度不固定的内容

• date 类型

• 总结：常用数据类型：

2、创建表

• 格式：

create table [if not exists] 表名 （表选项）

示例1

mysql> create table student1(id int, name varchar(30), sex char(2), age int unsigned, score float, addr varchar(50)); Query OK, 0 rows affected (0.02 sec) mysql> desc student1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(30) | YES | | NULL | | | sex | char(2) | YES | | NULL | | | age | int unsigned | YES | | NULL | | | score | float | YES | | NULL | | | addr | varchar(50) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+

3、查看表

show命令：

mysql> show tables; +-------------------------+ | Tables_in_mydb2_stuinfo | +-------------------------+ | class1 | | student1 | +-------------------------+ 2 rows in set (0.00 sec)

describe命令：

mysql> desc student1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(30) | YES | | NULL | | | sex | char(2) | YES | | NULL | | | age | int unsigned | YES | | NULL | | | score | float | YES | | NULL | | | addr | varchar(50) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)

• 字段意义分析：

  • Field :字段名称

  • type：字段类型

  • null：是否允许为空

  • key：索引类型

  • default：默认值

  • extra：填充

4、删除表

• 格式：

drop table [if exists] 表名；

5、修改表

# 修改表名 alter table 旧表名 rename 新表名; # 表中添加新列 alter table 表名 add 新列名 列类型 [after|first] 列名; （after为之后） # 删除列 alter table 表名 DROP 列名; # 修改列名，类型 alter table 表名 change 旧列名 新列名 列类型; # 修改列类型 alter table 表名 modify 列名 列类型; # 移动列 alter table 表名 modify 列名 列类型 after 某列;

6、复制表

6.1、复制表结构

• 格式：

create table 新表名 like 源表

示例：

mysql> insert into student1 value(1000,"zhangsan","M",18,98.5,"xi'an","12345678","2005-10-01"); Query OK, 1 row affected (0.00 sec) mysql> insert into student1 value(1001, "lisi", "W", 20,66.79, "baoji","87654321","2003-5-01"); mysql> select * from student1; mysql> create table student2 like student1; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------------+ | Tables_in_mydb2_stuinfo | +-------------------------+ | class1 | | student1 | | teacher1 | +-------------------------+ 3 rows in set (0.00 sec) mysql> select * from student2; # 注意：只是复制结构框架 Empty set (0.00 sec)

6.2、复制结构和数据

• 格式：

create table 新表名 select * from 源表

示例：

mysql> create table student3 select * from student1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from student3; +------+----------+------+------+-------+-------+----------+------------+ | id | name | sex | age | score | addr | passwd | birthday | +------+----------+------+------+-------+-------+----------+------------+ | 1000 | zhangsan | M | 18 | 98.5 | xi'an | 12345678 | 2005-10-01 | | 1001 | lisi | W | 20 | 66.79 | baoji | 87654321 | 2003-05-01 | +------+----------+------+------+-------+-------+----------+------------+

二、表的约束

1、非空约束

1.1、创建非空约束

• 作用：限定某个字段/某列的值不允许为空

• 两个值：null（默认的）和 not null（不为空）

• 示例：

  mysql> create table myclass1(class_name varchar(20) not null, class_room varchar(20) not null, class_id int); Query OK, 0 rows affected (0.02 sec) mysql> desc myclass1; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | class_name | varchar(20) | NO | | NULL | | | class_room | varchar(20) | NO | | NULL | | | class_id | int | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) # 插入数据时： mysql> insert into myclass1 (class_room) values('计科1'); ERROR 1364 (HY000): Field 'class_name' does not have a default value # 失败，因为name有约束不能为空 mysql> insert into myclass1 (class_name,class_room) values('A104','计科1'); # 同时插入成功 Query OK, 1 row affected (0.00 sec) mysql> select * from myclass1; +------------+------------+----------+ | class_name | class_room | class_id | +------------+------------+----------+ | A104 | 计科1 | NULL | +------------+------------+----------+ 1 row in set (0.00 sec) # id字段无约束，可以为空

  1.2、修改非空约束：

• 示例

  mysql> alter table student1 modify id int not null;

1.3、删除非空约束

• 示例

  mysql> alter table student1 modify id int null; # 或者 null 可省略，默认为 null

2、默认值约束

2.1、创建默认值约束

• 作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

  mysql> create table teacher1(id int not null, name varchar(30) not null, sex char(2) default '女'); Query OK, 0 rows affected (0.03 sec) mysql> desc teacher1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | | NULL | | | name | varchar(30) | NO | | NULL | | | sex | char(2) | YES | | 女 | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into teacher1(id,name) values('1001','林雪'); Query OK, 1 row affected (0.00 sec) mysql> select * from teacher1; +------+--------+------+ | id | name | sex | +------+--------+------+ | 1001 | 林雪 | 女 | +------+--------+------+ 1 row in set (0.00 sec) mysql> insert into teacher1 values('1002','石磊','男'); Query OK, 1 row affected (0.01 sec) mysql> select * from teacher1; +------+--------+------+ | id | name | sex | +------+--------+------+ | 1001 | 林雪 | 女 | | 1002 | 石磊 | 男 | +------+--------+------+ 2 rows in set (0.00 sec)

2.2、增加默认值约束

mysql> alter table student1 modify sex char(2) default '女';

3.列描述

• 列描述没有实际含义,专门用来描述字段,会根据表创建语句保存,供操作者来查看该列的含义，相当于C/C++的注释

mysql> create table myclass2( class_name varchar(20) not null comment '教室',class_room varchar(20) default '计科2');

4、主键约束

• 作用：数据库存储数据不是唯一的目的,还要考虑到提取数据，一般要求数据库表中的记录要有一个 特定的唯一标识，来表示唯一性,这个唯一的特定标识就是主键，类似于序号学号这样的唯一标识,可以根据主键来唯一地筛选出一条记录
• 主键：primary key，用来唯一的约束该字段里面的数据
• 特点：

1. 主键字段不能为空，不能重复
2. 一张表中最多只能有一个主键
3. 主键所在的列通常是整数类型
4. 主键约束是最频繁的约束

4.1、创建主键

• 示例： 创建时增加主键

  mysql> create table t1 ( id int unsigned primary key comment '学号不能为空', name varchar(20) not null ,sex char(2) default '男'); Query OK, 0 rows affected (0.02 sec) mysql> desc t1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | | sex | char(2) | YES | | 男 | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into t1 values(1,'孙文','女'); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----+--------+------+ | id | name | sex | +----+--------+------+ | 1 | 孙文 | 女 | +----+--------+------+ 1 row in set (0.00 sec) mysql> insert into t1(id,name) values(2,'李文华'); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 1 | 孙文 | 女 | | 2 | 李文华 | 男 | +----+-----------+------+ 2 rows in set (0.00 sec) mysql> insert into t1(id,name) values(1,'黎明'); ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY' # 再次插入出错，因为需要保持唯一性

  4.2、修改表，追加主键

mysql> alter table teacher1 add primary key(id);

4.3、删除主键

alter table 表名 drop primary key;

4.4、复合主键

在创建表的时候,在所有字段之后,使用primary key（主键字段列表）来创建主键,如果有 多个字段作为主键,可以使用复合主键,这些字段合在一起是一个主键，也就是让多个字段联合在一起 作为唯一标识,单个字段主键重复是没有问题的,只要不是成为复合主键的字段一起冲突就行

示例：

mysql> create table t2 (id int, hostname varchar(10), ip varchar(20), port int unsigned, primary key (ip,port));

4.5、主键自增长

• 作用：给主键添加自增长的数值
• 示例：
• 指定自增长的起始值：如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值 开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加

5、唯一性约束

• 作用：限制某个字段/某列的值不能重复
• 唯一键和主键的关系：

1、主键更多的是标识唯一性

2、唯一键更多的是保证在业务上，不要和别的信息出现重复

3、主键只能有一个,唯一键能设置多个

• 示例：unique为唯一性约束：

  mysql> create table t4( id int primary key, name varchar(20) unique comment '名子 不能重名，可以为空' );

  6、外键约束

• 作用：限定某个表的某个字段的引用完整性
• 概念：

1、主表（父表）：被引用的表、被参考的表

2、从表（子表）：引用别人的表，参考别人的表

3、外键：从表中的某个字段引用自主表的某个字段或多个字段

4、引用键：主表被引用的字段

• 格式：

  foreign key (从表的字段名称) references 主表名字(主表的字段名称) # 建立外键关联

• 示例：
• 学生表通过class_id和班级表产生关联,在语义上,class_id称为外键,此时的这个学生表就相当于从表, 班级表相当于是主表, 比如我们新增一个class_id为30的学生,但是班级表中得先有id为30的班级,才 能在学生表中添加, 要删除班级表中id为20的班级,首先必须保证学生表中没有对应班级为class_id =20的学生.,当然我们也可以新增一个class_id为NULL的学生,表示该学生还没有分配班级, 外键可以 为空！
• 代码示例：

7、检查约束

• 作用：检查约束（check）是用来检查数据表中字段值有效性的一种手段，一般用于设置值的范 围
• 示例1：创建表时设置
• 示例2：修改表时设置

8、删除表的约束

9、总结