湖北专升本MySQL复习（MySQL数据库实用教程）——MySQL数据表的操作

表是数据库中存储的基本对象，是存储数据的容器，一个完整的表由表结构和表数据两部分组成。

---

表结构的设计

1.1创建数据表

例：创建学生信息表

create table 学生信息(
学号 char(10),
姓名 char(10),
性别 char(10),
年龄 int,
联系方式 char(11)
);

1.2 查看表的信息

语法格式： desc 表名;

desc 学生信息;

1.3 重命名表

语法格式：rename table 原数据表名 to 新数据表名;

rename table 学生信息 to student;

1.4 删除表

drop table 表名; 

drop table 表名1,表名2,...;   //可在同一语句中指定几个表对它们进行删除

drop table if exists 表名;  //如果不能肯定一个表是否存在，但如果它存在就删除它。

---

MySQL数据类型

1.1整数类型

特殊：如果只希望表示零和正整数，可以使用无符号整数。unsigned 用于定义大于等于0的数。

例如定义一个学生的年龄可使用“年龄 int unsigned”

整数类型

类型	字节数	说明
tinyint	1字节	最小整数
smallint	2字节	小型整数
medium	3字节	中型整数
int	4字节	标准整数
bigint	8字节	大整数

1.2小数类型

小数类型

数据类型	单位	说明
float	8字节或4字节	单精度浮点数
double	8字节	双精度浮点数
decimal	自定义长度	小数位数确定的小数

decimal（length，precision） 用于表示精度确定的小数类型，length决定了该小数的最大位数，precision 用于设置精度（小数点后数字的位数）

例;创建商品表，设置数值型数据类型

create table 商品表 (
订单号 char(10),
商品名称 char(10),
商品价格 decimal(5,2),
商品销量 int,
折后价 float(5,2),
销售总额 double
);

1.3 字符串类型

常规字符串类型

类型	说明
char(n)	固定长度为N的字符串，其中N的取值范围为0~255，英文一个字节，汉字两个字节
varchar(n)	长度可变，最多8000个英文，4000个汉字
text	长度可变，超大数据

char(N) ：为固定长度字符串类型，当保存char值时，字符串实际的长度小于定义的长度。

varchar(N)：为可变长度字符串类型，varchar的最大有效长度由最大度由最大行大小和使用的字符集确定。varchar存储变长数据可节约存储空间，但存储效率没有char高

text类型是一种特殊的字符串类型，text只能保存字符数据。

例：创建顾客信息表，表名为顾客信息

create table 顾客信息(
买家ID char(10),
姓名 char(10),
家庭住址 varchar(50),
联系电话 char(10),
备注 text
);

1.4 日期类型

日期类型

类型	说明
date	日期
time	时间
datetime	日期和时间
timestamp	时间标签
year	年份

1.5 复合类型

复合类型

类型	说明
enum('value1','value2'......)	该类型的字段只能容纳所列值之一或为NULL
set('value1','value2'......)	该类型的字段只能容纳一组值或为NULL

例：创建学生兴趣表，包含字段有学号、姓名、性别、爱好。

create table 学生信息表(
学号 char(10),
姓名 char(10),
性别 enum('男','女'),
爱好 set('唱','跳','Rap','篮球')
);

---

在创建表时，使用字符串类型时应遵循以下的原则：（了解即可）

1）从速度方面考虑，要选择固定的列，可以使用char类型

2）要节省空间，使用动态的列，可以使用varchar类型

3）要将列中的内容限制在一种选择，可以使用enum类型

4）允许在一个列中有多于一个的条目，可以使用set类型

5）如果搜索的内容不区分大小写，可以text类型

---

修改表的结构

1.1 增加字段

alter table 表名 add 新字段名 新数据类型 [first | after 旧字段名 ];

first:将新字段添加到表的最左边。

after 旧字段名：将新字段添加到表中原有的某个字段的后面。

1.2 删除字段

alter  table 表名 drop 字段名;

1.3 修改字段的数据类（modify）

alter table 表名 modify 字段名 新数据类型;

1.4 修改字段名（change）

alter table 表名 change 旧字段名  新字段名  数据类型;

alter table 顾客信息 add 顾客编号 char(10) first;
alter table 顾客信息 add 年龄 tinyint after 性别;
alter table 顾客信息 add 家庭住址 varchar(50) after 联系方式;
alter table 顾客信息 change 省份证 身份证 char(18);
alter table 顾客信息 modify 联系方式 char(11);
alter table 顾客信息 drop 爱好;

---

设置约束

为表设置约束是解决数据完整性的主要方法。MySQL数据库将数据的完整性解释为：存储在数据库中的所有数据值均正确。

在MySQL中，为表创建约束有两种方法：1）在创建表时添加约束；2）通过修改表结构添加约束。

1.1主键约束——当字段设置为主键时，该字段的值就不能为空，也不能重复。

主键的建立有三种方法，纯了以下两种，还有修改表结构添加约束

设置主键的方法有两种：

1） 字段名 数据类型 primary key; 或 constraint 约束名称 primary key(字段名);

2) 如果一张表的单个字段无法定义为主键，则需要把多个字段共同设置为主键，即复合主键。

constraint 约束名称 primary key(字段名1,字段名2........);

create table 学生选课(
学号 char(10),
课程号 char(10),
成绩 int,
constraint x_k_pk primary key(学号,课程号)
);

例：为课程信息表中的“课程号”设置自动增长型字段，并设置为主键。

auto_increment 约束的字段可以是任何整数，默认情况下，该字段的值从1开始自增。MySQL中规定自增列必须为主键。

create table 课程信息(
课程号 int auto_increment primary key,
课程名称 char(10),
学分 int default 4
);

1.2 唯一约束

字段名 数据类型 unique;

1.3 非空约束——用来强调实现数据的域完整性，它用来设定某列值不能为空。

字段名 数据类型 not null;

1.4 默认约束

字段名 数据类型 default '值';      //值如果是数字时可以省略单引号。

1.5 外键约束

一张表中的字段值恰好引用了另外一张表的主键值，那么该字段值就是本表的外键。

外键不应该是本表的主键，但它可以与其它字段共同设置为该表的主键。

外键的作用：

1）禁止从表中插入主表中不存在的数据。

2）禁止由于修改主表中的主键的值，导致从表中相应的外键值孤立。

3）禁止删除在从表中有对应记录的主表记录。

设置外键约束的语法格式：

constraint 约束名称 foreign key(字段名) references 主表名(字段名);

create table 学生选课(
学号 char(10),
课程号 int,
成绩 int,
constraint x_k_pk primary key(学号,课程号),
constraint x_fk foreign key(学号) references 学生信息(学号),
constraint k_fk foreign key(课程号) references 课程信息(课程号)
);

create table book(
图书编号 char(10) primary key,
书名 varchar(30) unique,
作者 varchar(30) default'作者不详',
单价 decimal(5,2) default 0,
出版社 char(10) not null
);

1.6修改表结构添加约束

alter table 表名 add constraint 约束名称 约束类型(字段名，......);

其中约束类型只能是 primary key 和 foreign key 。

not null、unique、default约束可以通过修改表结构中的字段实现

例：alter table 学生选课 modify 成绩 int not null default 0；

alter table 学生选课 add constraint x_k_pk primary key(学号,课程号);
alter table 学生选课 add constraint x_fk foreign key(学号) references 学生信息(学号);
alter table 学生选课 add constraint k_fk foreign key(课程号) references 课程信息(课程);

1.7 删除约束

删除主键约束：alter table 表名 drop primary key;

删除外键约束：alter table 表名 drop foreign key 约束名;

alter table 学生选课 drop primary key;
alter table 学生选课 drop foreign key x_fk;

---

数据更新

1.1 插入数据

格式一：insert into 表名  [(字段列表)] values(字段值1，字段值2，字段值3，....)；(普遍用这个)

格式二：insert into 表名 set 字段1=值1,字段2=值2，...;

insert into 学生信息 values
('1906010801','飞雪','女',21,'13691875603'),
('1906010802','西门吹雪','男',18,'13691875604'),
('1906010803','夜未央','女',28,'13691875613'),
('1906010804','菊花残','男',38,'13691875673'),
('1906010805','满地伤','男',26,'13691875663');

1.2 修改数据

修改数据时可以修改数据表中的一个字段或多个字段的全部值

update 表名

set 字段名1=值1[,字段名2=值2，....]

[where 条件表达式]

update 学生信息 set 联系方式='无';
update 学生信息 set 年龄=年龄-5 where 性别='女';
update 学生信息 set 联系方式='11011010' where 学号='1906010801';

1.3 删除数据

1）用delete语句删除记录：（如果没有指定where子句（省略where子句），那么该表的所有记录都将被删除，但表结构依然存在）

delete from 表名 [where 条件表达式];

2)使用truncate table 语句完全清空一个表

truncate table 表名;

truncate table 语句是不支持事物的回滚，并且不会触发触发器程序的运行。而delete语句删除的数据是可以恢复的。