第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: 0
mysql> 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
在创建表时,可以给表中字段加上约束,来保证数据的完整性、有效性
约束包括哪些:
-
非空约束:not null
-
唯一性约束: unique
-
主键约束: primary key (PK)
-
外键约束:foreign key(FK)
-
检查约束: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', 'zhangsan@123.com');
insert into t_vip(id,name,email) values(2, 'lisi', 'lisi@123.com');
insert into t_vip(id,name,email) values(3, 'wangwu', 'wangwu@123.com');
mysql> insert into t_vip(id,name,email) values(4, 'wangwu', 'wangwu@163.com'); //名字不可以重复
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 | zhangsan@123.com |
| 2 | lisi | lisi@123.com |
| 3 | wangwu | wangwu@123.com |
| 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','zhangsan@123.com');
mysql> insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@163.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_vip;
+------+----------+------------------+
| id | name | email |
+------+----------+------------------+
| 1 | zhangsan | zhangsan@123.com |
| 2 | zhangsan | zhangsan@163.com |
+------+----------+------------------+
2 rows in set (0.00 sec)
mysql> insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@123.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@123.com' 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','zhangsan@123.com');
mysql> insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
mysql> insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
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 100
2 lucy 100
3 lilei 100
4 hanmeimei 100
5 zhangsan 101
6 lisi 101
7 wangwu 101
8 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约束。文章来源:https://www.toymoban.com/news/detail-849345.html
测试:外键可以为NULL吗?
外键值可以为NULL文章来源地址https://www.toymoban.com/news/detail-849345.html
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)
到了这里,关于第3章 数据定义语言DDL的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!