07-数据定义

合集 - mysql(11)

1.02-数据库和表的基本操作2023-07-302.03-简单查询2023-07-303.04-条件查询2023-07-304.05-分组汇总查询2023-07-305.06-连接查询和子查询2023-07-30

6.07-数据定义2023-07-30

7.08-存储引擎和事务2023-07-308.09-索引和视图2023-07-309.10-DBA命令和数据库设计的三范式2023-07-3010.11-MySQL数据库的存储过程2023-07-3011.12-MySQL数据库的触发器2023-07-30

收起

07-数据定义

课程目标

掌握创建表、增加和删除和修改表结构、添加和修改和删除表中数据、创建表并加入约束。

7.1创建表结构

1.语法格式

create table tableName(

columnName dataType(length),

………………..

columnName dataType(length)

);

set character_set_results='gbk';

show variables like '%char%';

创建表的时候，表中有字段，每一个字段有：

* 字段名

* 字段数据类型

* 字段长度限制

* 字段约束

2. MySQL常用数据类型，如下表7-1所示：

表7-1

类型	描述
char(长度)	定长字符串，存储空间大小固定，适合作为主键或外键
varchar(长度)	变长字符串，存储空间等于实际数据空间
double(有效数字位数，小数位)	数值型
float(有效数字位数，小数位)	数值型
int( 长度)	整型
bigint(长度)	长整型
date	日期型
BLOB	Binary Large OBject（二进制大对象）
CLOB	Character Large OBject（字符大对象）
其它…………………

3.建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识，如下图7-1所示：

create table t_student(

student_id int(10),

student_name varchar(20),

sex char(2),

birthday date,

email varchar(30),

classes_id int(3)

);

图7-1

4.向t_student表中加入数据,（必须使用客户端软件，我们的cmd默认是GBK编码,数据中设置的编码是UTF-8），如下图7-2所示：

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10);

图7-2

5.向t_student表中加入数据（使用默认值），如下图7-3所示：

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20),

sex char(2) default 'm',

birthday date,

email varchar(30),

classes_id int(3)

);

insert into t_student(student_id, student_name, birthday, email, classes_id)

values

(1002, 'zhangsan', '1988-01-01', 'qqq@163.com', 10);

图7-3

7.2 增加/删除/修改表结构

采用alter table来增加/删除/修改表结构，不影响表中的数据

1.添加字段

如：需求发生改变，需要向t_student中加入联系电话字段，字段名称为：contatct_tel 类型为varchar(40) ，如下图7-4所示：

alter table t_student add contact_tel varchar(40);

图7-4

2.修改字段

如：student_name无法满足需求，长度需要更改为100，如下图7-5所示：

alter table t_student modify student_name varchar(100) ;

图7-5

如sex字段名称感觉不好，想用gender那么就需要改列的名称，如下图7-6所示：

alter table t_student change sex gender char(2) not null;

图7-6

3.删除字段

如：删除联系电话字段，如下图7-7所示：

alter table t_student drop contact_tel;

图7-7

7.3 添加、修改和删除表中数据

1.insert

添加、修改和删出都属于DML，主要包含的语句：insert、update、delete

Insert语法格式

Insert into 表名(字段名1，字段名2，…) values(字段值1, 字段值2,…);

省略字段的插入，如下图7-8和7-9所示：

insert into emp values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);

图7-8

图7-9

不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到insert语句

指定字段的插入(建议使用此种方式) ，如下图7-10所示：

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);

图7-10

出现了主键重复的错误，主键表示了记录的唯一性，不能重复，如下图7-11所示：

图7-11

如何插入日期：

第一种方法，插入的日期格式和显示的日期格式一致，如下图7-12所示：

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10) ;

图7-12

第二种方法，采用str_to_date，如下图7-13所示：

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);

图7-13

第三种方法，添加系统日期now()，如下图7-14和7-15所示：

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995,'zhangsan','MANAGER',null,now() ,3000, 500, 10);

图7-14

图7-15

表复制，如下图7-16和7-17所示：

create table emp_bak as select * from emp;

图7-16

图7-17

以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中

如何将查询的数据直接放到已经存在的表中，可以使用条件，如下图7-18所示：

insert into emp_bak select * from emp where sal=3000;

图7-18

2.update

可以修改数据，可以根据条件修改数据

语法格式：

update 表名 set 字段名称1=需要修改的值1, 字段名称2=需要修改的值2 where ……

将job为manager的员工的工资上涨10%

update emp set sal=sal+sal*0.1 where job='MANAGER';

3.delete

可以删除数据，可以根据条件删除数据

语法格式：

delete from表名 where ……

删除津贴为500的员工

delete from emp where comm=500;

删除津贴为null的员工

delete from emp where comm is null;

7.4 创建表加入约束

常见的约束：

• 1. 非空约束，not null
  2. 唯一约束，unique
  3. 主键约束，primary key
  4. 外键约束，foreign key
  5. 自定义检查约束，check（不建议使用）(在MySQL中现在还不支持)

1.非空约束，not null

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30),

classes_id int(3)

);

insert into t_student(student_id, birthday, email, classes_id)

values(1002, '1988-01-01', 'qqq@163.com', 10);

图7-19

以上错误为加入的学生姓名为空，如上图7-19所示。

2.唯一约束，unique

唯一性约束，它可以使某个字段的值不能重复，如：email不能重复：

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30) unique,

classes_id int(3)

);

insert into t_student(student_id, student_name , sex, birthday, email, classes_id)

values

(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);

图7-20

以上插入了重复的email，所以出现了“违反唯一约束错误”，所以unique起作用了

同样可以为唯一约束起个约束名，如上图7-20所示。

我们可以查看一下约束，如下图7-21所示：

MySQL> use information_schema;

MySQL> select * from table_constraints where table_name = 't_student';

图7-21

关于约束名称可以到table_constraints中查询

以上约束的名称我们也可以自定义。

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30) ,

classes_id int(3) ,

constraint email_unique unique(email)/*表级约束*/

);

3.主键约束，primary key

每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的

drop table if exists t_student;

create table t_student(

student_id int(10) primary key,/*列级约束*/

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30),

classes_id int(3)

);

insert into t_student(student_id, student_name, sex, birthday, email, classes_id)

values

(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);

向以上表中加入学号为1001的两条记录，出现如下错误，因为加入了主键约束，如下图7-22所示：

图7-22

我们也可以通过表级约束为约束起个名称：

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30) ,

classes_id int(3),

constraint p_id primary key(student_id)

);

insert into t_student(student_id, student_name , sex, birthday, email, classes_id)

values

(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);

4.外键约束 foreign key

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp中的deptno值必须来源于dept表中的deptno字段值。

建立学生和班级表之间的连接

首先建立班级表t_classes

drop table if exists t_classes;

create table t_classes(

classes_id int(3),

classes_name varchar(40),

constraint pk_classes_id primary key(classes_id)

);

在t_student中加入外键约束

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20),

sex char(2),

birthday date,

email varchar(30),

classes_id int(3),

constraint student_id_pk primary key(student_id),

constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)

);

向t_student中加入数据，如下图7-23所示：

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)；

图7-23

出现错误，因为在班级表中不存在班级编号为10班级，外键约束起到了作用

存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表，如下图7-24所示：

图7-24

以上成功的插入了学生信息，当时classes_id没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20),

sex char(2),

birthday date,

email varchar(30),

classes_id int (3) not null,

constraint student_id_pk primary key(student_id),

constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)

);

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', null);

再次插入班级编号为null的数据，如下图7-25所示：

图7-25

添加数据到班级表，添加数据到学生表，删除班级数据，将会出现如下错误，如下图7-26所示：

insert into t_classes (classes_id,classes_name) values (10,'366');

insert into t_student(

student_id, student_name, sex, birthday, email, classes_id

) values(

1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10

);

MySQL> update t_classes set classes_id = 20 where classes_name = '366';

图7-26

因为子表（t_student）存在一个外键classes_id，它参照了父表（t_classes）中的主键，所以先删除子表中的引用记录，再修改父表中的数据。

我们也可以采取以下措施 级联更新，如下图7-27所示：

MySQL> delete from t_classes where classes_id = 10;

图7-27

因为子表（t_student）存在一个外键classes_id，它参照了父表（t_classes）中的主键，所以先删除父表，那么将会影响子表的参照完整性，所以正确的做法是，先删除子表中的数据，再删除父表中的数据，采用drop table也不行，必须先drop子表，再drop父表

我们也可以采取以下措施 级联删除。

5.级联更新与级联删除

(1).on update cascade;

MySQL对有些约束的修改比较麻烦，所以我们可以先删除，再添加，如下图7-28所示：

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on update cascade;

图7-28

我们只修改了父表中的数据，但是子表中的数据也会跟着变动。

(2).on delete cascade;

MySQL对有些约束的修改时不支持的，所以我们可以先删除，再添加，如下图7-29所示：

alter table t_student drop foreign key fk_classes_id_1;

alter table t_student add constraint fk_classes_id_2 foreign key(classes_id) references t_classes(classes_id) on delete cascade;

delete from t_classes where classes_id = 20;

图7-29

我们只删除了父表中的数据，但是子表也会中的数据也会删除。

6. t_student和t_classes完整示例

drop table if exists t_classes;

create table t_classes(

classes_id int (3),

classes_name varchar(30) not null,

constraint pk_classes_id primary key(classes_id)

);

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(50) not null,

sex char(2) not null,

birthday date not null,

email varchar(30) unique,

classes_id int (3) not null,

constraint pk_student_id primary key(student_id),

constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)

);

7. 增加/删除/修改表约束

1.删除约束

将t_student中的约束删除

删除外键约束：alter table 表名 drop foreign key 外键（区分大小写）;

alter table t_student drop foreign key fk_classes_id;

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

alter table t_student drop primary key;

删除唯一约束：alter table 表名 drop key 约束名称 ;

alter table t drop key uk;

2.添加约束

向t_student中的添加约束

添加外键约束：alter table 从表 add constraint 约束名称 foreign key 从表(外键字段) references 主表(主键字段);

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id);

添加主键约束：alter table 表 add constraint 约束名称 primary key 表(主键字段);

alter table t_student add constraint pk primary key(student_id);

添加唯一性约束：alter table 表 add constraint 约束名称 unique 表(字段);

alter table t_student add constraint uk unique(email);

3.修改约束，其实就是修改字段

alter table t_student modify student_name varchar(30) unique;

MySQL对有些约束的修改时不支持，所以我们可以先删除，再添加

7.5 本章小结

本章主要阐述了SQL语句中的数据定义。