第3章 数据定义语言DDL

第3章 DDL语言：数据定义语言

DDL：数据定义语言：create（增）、drop（删）、alter（改）、truncate。主要是对表结构进行操作。

3.1 MySQL的数据类型

类型	作用
varchar	可变长的字符串类型，根据实际长度东忒分配空间。优点：节省空间。缺点：需要动态分配空间，速度慢。最长255
char	定长字符串类型， 不管实际的数据长度是多少，分配固定长度的空间去存储数据。优缺点与上面相反。最长255
int	整型。等同于java的int。最长11
bigint	长整型。等同于java中的long。
float	单精度浮点型
double	双精度浮点型
date	短日期类型
datetime	长日期类型
clob	Character Large OBject:CLOB。字符大对象， 最多存储4G的字符串。 超过255个字符的采用CLOB存储。 比如：存储一篇文章，一篇说明。
blob	Binary Large OBject； 二进制大对象， 用来存储图片、声音、视频等流媒体数据。 BLOB类型字段上插入数据时，需要使用IO流。

varchar和char我们应该怎么选择？
性别字段选：性别是固定长度字符串，选择char。
姓名字段选：每个人名字长度不同，选择varchar。

例如：t_movie 电影表，存储电影信息

编号			   名字			故事情节			  上映日期			   时长			  海报				类型
no(bigint)	name(varchar)	history(clob)		playtime(date)		time(double)	image(blob)			type(char)
------------------------------------------------------------------------------------------------------------------
10000		   哪吒		    ..........			  2019-10-11		   2.5			   ....				 '1'
10001		   悟空传         ..........	         2019-11-11			  1.5			   ....				  '2'
....

3.2 表的创建：create

建表的语法格式：create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);

create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型
);

表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。
字段名：见名知意。
表名和字段名都属于标识符。

3.3 表的删除：drop

drop table t_student;             //注意：当这张表不存在的时候会报错！
drop table if exists t_student;   //两句都可以，推荐这句

实例：创建一个学生表，学号、姓名、年龄、性别、邮箱地址

mysql> create table t_student(-> no int,-> name varchar(32),-> sex char(1),-> age int(3),-> email varchar(255)-> );
Query OK, 0 rows affected (0.01 sec)

3.4 快速创建表

了解内容， 将查询结果当做一张表新建，实现表集数据的快速复制

mysql> create table emp2 as select * from emp;  //两张表的数据一模一样
Query OK, 14 rows affected (0.01 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> create table mytable as select empno,ename from emp where job='MANAGER';
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from mytable;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
+-------+-------+
3 rows in set (0.00 sec)

3.5 快速删除表中的数据：truncate

delete from dept_bak;      //这种删除数据的方式比较慢。

delete原理： 表中数据被删除了，但是数据在硬盘上的真实存储空间不会被释放！
缺点：删除效率比较低； 优点：支持回滚（即可恢复）

回滚实例

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)mysql> delete from dept_bak;
Query OK, 4 rows affected (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.01 sec)mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

truncate原理：表被一次截断，物理删除。
缺点：不支持回滚。 优点：效率高，快速。

语法：truncate table dept_bak;

mysql> truncate table dept_bak;          //删数据，不是删表
Query OK, 0 rows affected (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from dept_bak;          //回滚也没用
Empty set (0.00 sec)

truncate删除速度很快，但是数据不可恢复。

注意：truncate是删除表中的数据，表还在。删除表用drop

3.6 修改表结构：alter

意思：添加一个字段，删除一个字段，修改一个字段！！！

第一：实际开发中，需求一旦确定，表一旦设计完成，很少对表结构修改。因为开发进行中修改结果成本较高，对应的java代码需要进行大量修改。这个责任应该由设计人员来承担！

第二：由于修改表结构操作很少，我们不需要掌握，如果真要修改，可以使用工具！

修改表结构操作不需要写到java程序中，也不是java程序员的范畴。

第5章 约束

constraint
在创建表时，可以给表中字段加上约束，来保证数据的完整性、有效性

约束包括哪些：

1. 非空约束：not null

2. 唯一性约束: unique

3. 主键约束: primary key （PK）

4. 外键约束：foreign key（FK）

5. 检查约束：check（mysql不支持，oracle支持）

5.1 非空约束：not null

非空约束not null约束的字段不能为NULL。
not null 只有列级约束，没有表级约束

vip.sql，该文件为SQL脚本文件

drop table if exists t_vip;
create table t_vip(id int,name varchar(255) not null
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');

mysql> source C:\Users\14051\Desktop\mysql_learning\document\vip.sql   //执行整个脚本文件
Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.00 sec)

我们执行sql脚本文件的时候，该文件中所有的sql语句会全部执行！

你在实际的工作中，第一天到了公司，项目经理会给你一个xxx.sql文件，你执行这个脚本文件，你电脑上的数据库数据就有了！

5.2 唯一性约束: unique

唯一性约束unique约束的字段不能重复，但是可以为NULL。

drop table if exists t_vip;
create table t_vip(id int,name varchar(255) unique,   //name只能唯一email varchar(255)
);insert into t_vip(id,name,email) values(1, 'zhangsan', '[e-mail]');
insert into t_vip(id,name,email) values(2, 'lisi', '[e-mail]');
insert into t_vip(id,name,email) values(3, 'wangwu', '[e-mail]');

mysql> insert into t_vip(id,name,email) values(4, 'wangwu', '[e-mail]');   //名字不可以重复
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'mysql> insert into t_vip(id) values(4);    //可以为NULL
Query OK, 1 row affected (0.00 sec)mysql> select * from t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | [e-mail] |
|    2 | lisi     | [e-mail]     |
|    3 | wangwu   | [e-mail]   |
|    4 | NULL     | NULL             |
+------+----------+------------------+
4 rows in set (0.00 sec)

新需求：name和email两个字段联合起来具有唯一性！

drop table if exists t_vip;
create table t_vip(id int,name varchar(255),email varchar(255),unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。
);insert into t_vip(id,name,email) values(1,'zhangsan','[e-mail]');

mysql> insert into t_vip(id,name,email) values(2,'zhangsan','[e-mail]');
Query OK, 1 row affected (0.01 sec)mysql> select * from t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | [e-mail] |
|    2 | zhangsan | [e-mail] |
+------+----------+------------------+
2 rows in set (0.00 sec)mysql> insert into t_vip(id,name,email) values(3,'zhangsan','[e-mail]');
ERROR 1062 (23000): Duplicate entry '[e-mail]' for key 'name'

什么时候使用表级约束
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

实例：unique 和not null可以联合

drop table if exists t_vip;
create table t_vip(id int,name varchar(255) not null unique  //联合使用);

mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意：oracle中不一样！）

5.3 主键约束：primary key

重要内容

相关术语

• 主键约束：一种约束。
• 主键字段：该字段上添加了主键约束
• 主键值：主键字段中的每一个值都叫主键值

什么是主键？有啥用？

主键值是每一行记录的唯一标识。键值是每一行记录的身份证号！

记住：任何一张表都应该有主键，没有主键，表无效！

主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）

怎么给一张表添加主键约束呢？

drop table if exists t_vip;
create table t_vip(id int primary key,    //列级约束name varchar(255));mysql> insert into t_vip(id,name) values(1,'z');
mysql> insert into t_vip(id,name) values(2,'a');

mysql> insert into t_vip(id,name) values(2,'s');    //不能重复
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'mysql> insert into t_vip(name) values('c');        //不能为空
ERROR 1364 (HY000): Field 'id' doesn't have a default value

（1）使用表级约束，添加主键

同样实现功能

create table t_vip(id int,name varchar(255),primary key(id)); //表级约束

表级约束主要是给多个字段联合起来添加约束

drop table if exists t_vip;
create table t_vip(id int,name varchar(255),email varchar(255),primary key(id,name)  // id和name联合起来做主键：复合主键！！！！
);mysql> insert into t_vip(id,name,email) values(1,'zhangsan','[e-mail]');
mysql> insert into t_vip(id,name,email) values(1,'lisi','[e-mail]');

mysql> insert into t_vip(id,name,email) values(1,'lisi','[e-mail]');
ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'

在实际开发中不建议使用：复合主键。建议使用单一主键！
因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。

（2）表中主键约束能两个吗

mysql> create table t_vip(-> id int primary key,-> name varchar(255) primary key);
ERROR 1068 (42000): Multiple primary key defined

结论：一张表，主键约束只能添加1个。（主键只能有1个。）

主键值建议使用：int，bigint，char等类型。

不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！

主键除了：单一主键和复合主键之外，还可以这样进行分类
自然主键：主键值是一个自然数，和业务没关系。
业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

在实际开发中使用业务主键多，还是使用自然主键多一些？
自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。
因为主键一旦和业务挂钩，当业务发生变动时，会影响到主键值，所以业务主键不建议使用。

（3）auto_increment

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值

create table t_vip(id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！name varchar(255)
);insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');

mysql> select * from t_vip;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
|  3 | zhangsan |
+----+----------+
3 rows in set (0.00 sec)

5.4 外键约束：foreign key

简称FK

业务背景：请设计数据库表，来描述“班级和学生”的信息

第一种方案：班级和学生存储在一张表中

t_student
no(pk)			name		classno			classname
----------------------------------------------------------------------------------
1				jack		  100			北京市大兴区亦庄镇第二中学高三1班
2				lucy		  100			北京市大兴区亦庄镇第二中学高三1班
3				lilei		  100			北京市大兴区亦庄镇第二中学高三1班
4				hanmeimei	  100			北京市大兴区亦庄镇第二中学高三1班
5				zhangsan	  101			北京市大兴区亦庄镇第二中学高三2班
6				lisi		  101			北京市大兴区亦庄镇第二中学高三2班
7				wangwu		  101			北京市大兴区亦庄镇第二中学高三2班
8				zhaoliu		  101			北京市大兴区亦庄镇第二中学高三2班

以上方案缺点：数据冗余，空间浪费！这个设计是比较失败的！

第二种方案：班级一张表、学生一张表

t_class 班级表
classno(pk)			      classname
------------------------------------------------------100					北京市大兴区亦庄镇第二中学高三1班101					北京市大兴区亦庄镇第二中学高三1班

t_student 学生表no(pk)			name		cno(FK引用t_class这张表的classno)
----------------------------------------------------------------1				jack				1002				lucy				1003				lilei				1004				hanmeimei		    1005				zhangsan			1016				lisi				1017				wangwu			    1018				zhaoliu			    101

当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在。
所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束。
那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

注意：
t_class是父表
t_student是子表

• 删除表的顺序：先删子，再删父。
• 创建表的顺序：先创建父，再创建子。
• 删除数据的顺序：先删子，再删父。
• 插入数据的顺序：先插入父，再插入子。

drop table if exists t_student;
drop table if exists t_class;create table t_class(classno int primary key,classname varchar(255)
);
create table t_student(no int primary key auto_increment,name varchar(255),cno int,foreign key(cno) references t_class(classno)
);insert into t_class(classno,classname) values(100,'北京市大兴区亦庄镇第二中学高三1班');
insert into t_class(classno,classname) values(101,'北京市大兴区亦庄镇第二中学高三1班');
insert into t_student(name,cno) values('jack',100);
insert into t_student(name,cno) values('lucy',100);
insert into t_student(name,cno) values('lilei',100);
insert into t_student(name,cno) values('hanmeimei',100);
insert into t_student(name,cno) values('zhangsan',101);
insert into t_student(name,cno) values('lisi',101);
insert into t_student(name,cno) values('wangwu',101);
insert into t_student(name,cno) values('zhaoliu',101);

思考：子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
不一定是主键，但至少具有unique约束。

测试：外键可以为NULL吗？
外键值可以为NULL

mysql> insert into t_student(name,cno) values('zhaoliu',NULL);
Query OK, 1 row affected (0.00 sec)mysql> select * from t_student;
+----+-----------+------+
| no | name      | cno  |
+----+-----------+------+
|  1 | jack      |  100 |
|  2 | lucy      |  100 |
|  3 | lilei     |  100 |
|  4 | hanmeimei |  100 |
|  5 | zhangsan  |  101 |
|  6 | lisi      |  101 |
|  7 | wangwu    |  101 |
|  8 | zhaoliu   |  101 |
| 10 | zhaoliu   | NULL |
+----+-----------+------+
9 rows in set (0.00 sec)