MySQL笔记(二)
一、表的创建和数据类型
建表的语法格式
建表语句属于DDL语句,DDL包括:create、drop、alter
create table <表名>(
<字段名> <数据类型>,
<字段名> <数据类型>,
<字段名> <数据类型>
);
注意:表名和字段名都属于标识符。要见名知意!!
表名:建议以 t_ 或 tbl_ 开始,可读性强。
字段名:见名知意。
数据类型
类型后面的长度不是字符也不是字节。,就是长度。英文'a'是1个长度,中文"啊"也是1个长度。
-
varchar(最长255)
可变长度的字符串,比较智能,节省空间。会根据实际的数据长度动态分配空间。
优点:节省空间
缺点:需要动态分配空间,速度慢。
-
char(最长255)
定长字符串,不管实际的数据长度是多少,分配固定长度的空间去存储数据。若使用不恰当,可能会导致空间的浪费。
优点:不需要动态分配空间,速度快。
缺点:使用不当可能导致空间的浪费。
varchar和char应该怎么选择?
性别字段选什么?因为性别是固定长度的字符串('男'、'女'),所以选char。(char(1))
姓名字段选什么?因为姓名的长度有长有短,所以选择varchar。
-
int(最长11)
数字中的整数型。等同于Java中的int。
-
bigint
数字中的长整型。等同于Java中的long。
-
float
单精度浮点型数据。
-
double
双精度浮点型数据。
-
date
短日期类型。
-
datetime
长日期类型。
clob
字符大对象,最多可以存储
4G
的字符串。比如:存储一篇文章,存储一个说明。超过255个字符的都要采用CLOB字符大对象来存储。
Character Large OBject:CLOB
-
blob
二进制大对象。专门用来存储图片、声音、视频等流媒体数据。
向BLOB类型的字段上插入数据的时候,例如插入一张图片、视频等,需要使用IO流才行。
Binary Large OBject:BLOB
查看表结构
-- 查看表结构
desc <表名>
模拟表
tbl_movie 电影表(专门存储电影信息的)
编号 | 名字 | 类型 | 故事情节 | 上映日期 | 时长 | 海报 |
---|---|---|---|---|---|---|
no(bigint) | name(varchar) | type(char) | history(clob) | playtime(date) | time(double) | image(blob) |
10000 | 哪吒 | 1 | ...... | 2019-10-11 | 2.5 | ...... |
10001 | 孙悟空 | 1 | ...... | 2019-10-12 | 1.5 | ...... |
创建表
创建一个学生表?
学号、姓名、年龄、性别、邮箱地址
create table tbl_student(
sno int,
name varchar(32),
age int(3),
sex char(1),
email varchar(255)
);
二、删除表
drop table tbl_student; -- 表必须存在,否则会报错。
-- 或
drop table if exists tbl_student; -- 表如果存在则删除,不存在则不执行。
三、插入数据
insert 插入语句(DML)
-- 注意:是values
insert into <表名>(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);
注意:字段名和值要一一对应。数量要对应,数据类型也要对应。
若字段名全部省略,默认是都写上,插入所有字段,值要和数据表中的字段排序对应。(不建议,可读性差)
insert into tbl_student(sno,name,age,sex,email) values(211906,'张三',20,'男','211906@qq.com');
insert into tbl_student(sno,name,age,sex,email) values(211907,'法外狂徒',20,'男','211907@qq.com');
一个问题
只插入一个字段,不插入全部?
insert into tbl_student(sno) values(211906344);
/*
mysql> insert into tbl_student(sno) values(211906344);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tbl_student;
+-----------+-----------+------+------+-------------------+
| sno | name | age | sex | email |
+-----------+-----------+------+------+-------------------+
| 211906344 | NULL | NULL | NULL | NULL |
+-----------+-----------+------+------+-------------------+
2 rows in set (0.00 sec)
*/
insert语句但凡执行成功了,那么必然会多一条记录。只插入一个字段,没有给其他字段指定值的话,默认值是NULL。
给字段设置默认值
给 年龄
设置一个默认值。然后执行插入一个字段的sql语句。
drop table if exists tbl_student;
create table tbl_student(
sno int,
name varchar(32),
age int(3) default 20,
sex char(1),
email varchar(255)
);
insert into tbl_student(sno) values(2119063);
/*
mysql> insert into tbl_student(sno) values(2119063);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tbl_student;
+---------+------+------+------+-------+
| sno | name | age | sex | email |
+---------+------+------+------+-------+
| 2119063 | NULL | 20 | NULL | NULL |
+---------+------+------+------+-------+
1 row in set (0.00 sec)
*/
当为某一字段设置默认值后,插入数据时不指定该字段的值,则会默认填充默认值,而不是NULL。
插入日期
str_to_date:将字符串varchar类型转换为date类型。
date_format:将date类型转换为具有一定格式的varchar类型
str_to_date
drop table if exists tbl_user;
create table tbl_user(
id int,
name varchar(32),
birth date
);
插入数据。
insert into tbl_user(id,name,birth) values(1,'张三','01-10-1990'); -- 错误!
错误原因是类型不匹配,数据库birth是date类型,这里给了一个字符串varchar。
怎么办?可以使用str_to_date函数进行类型抓换。
怎么转换?
语法格式:str_to_date('字符串日期','日期格式');
MySQL的日期格式:
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
-- 使用 str_to_date 来将varchar转换为date。
insert into tbl_user(id,name,birth) values(1,'张三',str_to_date('01-10-1990','%d-%m-%Y'));
好消息!
如果你提供的日期字符串是这个格式,str_to_date函数就不需要了!!
%Y-%m-%d,能够自动转换为日期类型date。
insert into tbl_user(id,name,birth) values(1,'张三','1990-10-01');
date_format
这个函数可以将日期类型转换成特定格式的字符串。
语法格式:
date_format(日期类型数据, '日期格式');
这个函数通常使用在日期查询方面,设置展示的日期格式。
select id,name,date_format(birth, '%Y/%m/%d') from tbl_user;
/*
mysql> select id,name,date_format(birth, '%Y/%m/%d') from tbl_user;
+------+--------+--------------------------------+
| id | name | date_format(birth, '%Y/%m/%d') |
+------+--------+--------------------------------+
| 1 | 张三 | 1990/10/01 |
| 1 | 张三 | 1990/10/01 |
+------+--------+--------------------------------+
2 rows in set (0.00 sec)
*/
若不给日期格式,直接查询date类型数据的话,MySQL会按默认日期格式 %Y-%m-%d 来转换date类型数据。
所以:想以默认格式展示,这个就没啥意义。但如果想按不同形式展示日期,就有用了。
Java中的日期格式?
yyyy-MM-dd HH:mm:ss
date和datetime的区别
date是短日期:只包括年月日的信息。默认格式:%Y-%m-%d
datetime是长日期:包括年月日时分秒的信息。默认格式:%Y-%m-%d %h-%i-%s
drop table if exists tbl_user;
create table tbl_user(
id int,
name varchar(32),
birth date,
create_time datetime
);
insert into tbl_user(id,name,birth,create_time) values(211,'法外狂徒','2022-04-30','2022-04-30 01:17:22');
在MySQL中怎么获取系统当前时间?
now()函数:能够获取时分秒信息,当然也可以直接存入date类型字段。
insert into tbl_user(id,name,birth,create_time) values(211,'法外狂徒','2022-04-30',now()); /* mysql> select * from tbl_user; +------+--------------+------------+---------------------+ | id | name | birth | create_time | +------+--------------+------------+---------------------+ | 211 | 法外狂徒 | 2022-04-30 | 2022-04-30 01:17:22 | | 211 | 法外狂徒 | 2022-04-30 | 2022-04-30 01:19:14 | +------+--------------+------------+---------------------+ 2 rows in set (0.00 sec) */
插入多条记录
insert into tbl_user(id, name, birth, create_time)
values
(211, '法外狂徒', '2022-04-30', now()),
(212, '张三', '2022-04-30', now()),
(213, '李四', '2022-04-30', now());
四、更新数据
update 更新语句(DML)
update <表名> set 字段名1=值1,字段名2=值2,字段名3=值3 where <条件>;
注意:更新语句若没有条件限制会导致所有数据全部更新。
update tbl_user set name='法外狂徒',birth='2022-04-29' where id=211;
/*
mysql> select * from tbl_user;
+------+--------+------------+---------------------+
| id | name | birth | create_time |
+------+--------+------------+---------------------+
| 211 | 张三 | 2022-04-30 | 2022-04-30 01:17:22 |
| 212 | 张三 | 2022-04-30 | 2022-04-30 01:19:14 |
+------+--------+------------+---------------------+
2 rows in set (0.00 sec)
mysql> update tbl_user set name='法外狂徒',birth='2022-04-29' where id =211;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tbl_user;
+------+--------------+------------+---------------------+
| id | name | birth | create_time |
+------+--------------+------------+---------------------+
| 211 | 法外狂徒 | 2022-04-29 | 2022-04-30 01:17:22 |
| 212 | 张三 | 2022-04-30 | 2022-04-30 01:19:14 |
+------+--------------+------------+---------------------+
2 rows in set (0.00 sec)
*/
五、删除数据
delete 删除语句(DML)
delete from <表名> where <条件>;
注意:没有条件的话,会导致整张表的数据全部删除。
delete from tbl_user where id=212;
/*
mysql> select * from tbl_user;
+------+--------------+------------+---------------------+
| id | name | birth | create_time |
+------+--------------+------------+---------------------+
| 211 | 法外狂徒 | 2022-04-29 | 2022-04-30 01:17:22 |
| 212 | 张三 | 2022-04-30 | 2022-04-30 01:19:14 |
+------+--------------+------------+---------------------+
2 rows in set (0.00 sec)
mysql> delete from tbl_user where id=212;
Query OK, 1 row affected (0.02 sec)
mysql> select * from tbl_user;
+------+--------------+------------+---------------------+
| id | name | birth | create_time |
+------+--------------+------------+---------------------+
| 211 | 法外狂徒 | 2022-04-29 | 2022-04-30 01:17:22 |
+------+--------------+------------+---------------------+
1 row in set (0.00 sec)
*/
六、增删改查总结
-- 增
insert into <表名>(字段1,字段2,...) values(值1,值2,...);
-- 删
delete from <表名> where <条件>;
-- 改
update <表名> set 字段名1=值1,字段名2=值2,... where <条件>;
-- 查(基础语法,“查”是最复杂的)
select 字段名1,字段名2,... from <表名>;
insert、delete、update都是小菜,它们的语法格式都是固定的。
select是大头。因为有分组查询、连接查询、子查询...,所以select是非常重要且复杂的。
七、快速处理(了解)
快速复制表
-- 将查询结果当作一张表新建
create table tbl_student2 as select * from tbl_student;
create table tbl_student3 as select name from tbl_student where age=20;
将 (select * from tbl_student;) 的结果集当作一张表新建!
可以完成表的快速复制。表结构和表数据都复制了!!
将查询结果插入到一张已存在的表中
-- 将查询结果插入到已存在的一张表中
insert into tbl_student2 select * from tbl_student;
快速删除大表中的数据
之前有一个删除表数据的方式是:
delete from <表名>;
delete语句删除数据的原理:
表中的数据被删除了,但是这个数据在硬盘上的真实存储空间不会被释放。
这种删除的缺点是:删除效率比较低,速度比较慢。
这种删除的优点是:支持数据回滚,后悔了可以再恢复数据!!!
/* -- 需要先开启事务 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> delete from tbl_student2; Query OK, 2 rows affected (0.00 sec) mysql> select * from tbl_student2; Empty set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> select * from tbl_student2; +---------+------+------+------+-------+ | sno | name | age | sex | email | +---------+------+------+------+-------+ | 2119063 | NULL | 20 | NULL | NULL | | 2119063 | NULL | 20 | NULL | NULL | +---------+------+------+------+-------+ 2 rows in set (0.00 sec) */
快速删除数据:
truncate table <表名>;
原理:这种删除效率比较高,表被一次截断,物理删除。
缺点:不支持回滚。
优点:快速。
delete属于DML语句,truncate属于DDL语句。
truncate使用场景
一个大表,有上亿条数据。
删除的时候,使用delete
,也许需要1个小时才能删完!效率较低。
若使用truncate
来删除表中的数据,只需要1秒。但是不可恢复,删表前请仔细询问客户!
八、对表结构的增删改
什么是对表结构的修改?
添加一个字段、删除一个字段、修改一个字段。
但是!!
- 在实际的开发中,需求一旦确定之后,表一旦设计好之后,很少会进行表结构的修改。因为开发进行中的时候,修改表结构,成本比较高。修改表的结构,对应的Java代码就需要进行大量的修改。程表是比较高的。这个责任应该由设计人员来承担!
- 由于修改表结构的操作很少,所以——可以使用工具。
-- 增加一个字段
alter table <表名> add column 字段名 类型;
-- 删除一个字段
alter table <表名> drop column 字段名 类型;
-- 修改一个字段
alter table <表名> modify column 字段名 类型;
九、约束(⭐⭐⭐⭐⭐)
什么是约束?
约束对应的英语单词是:constraint
在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性!!!
约束的作用就是为了保证:表中的数据有效!!!
常见的约束
- 非空约束:not null
- 唯一性约束:unique
- 主键约束:primary key (简称PK)
- 外键约束:foreign key (简称FK)
- 检查约束:check (MySQL不支持,Oracle支持)
重点学习:not null
、unique
、primary key
、foreign key
非空约束
非空约束 not null 约束的字段不能为NULL。
drop table if exists tbl_test;
create table tbl_test(
id int,
name varchar(255) not null
);
这样的话,插入数据时,name
字段就不能不写了,否则会报错!
唯一性约束
唯一性约束 unique 约束的字段数据不能重复,但是可以为NULL。
drop table if exists tbl_test;
create table tbl_test(
id int unique,
name varchar(255),
email varchar(255)
);
这样的话,插入数据时,id
字段中的数据就不能重复了,若插入数据中的id在表中已经存在,会报错!
一个需求
name和email两个字段联合起来具有唯一性。
drop table if exists tbl_test;
create table tbl_test(
id int,
name varchar(255) unique,
email varchar(255) unique
);
这样创建表是错误的,两个字段分别有一个unique约束,表示:name具有唯一性,email也具有唯一性。
正确的做法是:
drop table if exists tbl_test;
create table tbl_test(
id int,
name varchar(255),
email varchar(255),
unique(name,email)
);
这样,name和email两个字段联合起来就具有唯一性了。当name相同的时候,只要email不相同就行;email相同的时候,name要不相同。
创建表的时候:
若约束直接添加在字段的后面,这是列级约束。例:name varchar(255)
若约束不是添加在字段的后面,这是表级约束。例:unique(name,email)
若要多个字段联合约束,则需要使用表级约束。
非空+唯一
drop table if exists tbl_test;
create table tbl_test(
id int,
name varchar(255) not null unique,
email varchar(255)
);
在MySQL中,not null 和 unique 联合使用,就成为了
primary key——主键
(Oracle中不一样)
主键约束(🌕)
primary key,简称PK
主键约束的相关术语?
主键约束:就是一种约束
主键字段:该字段上添加了主键约束,这样的字段叫做
主键字段
主键值:主键字段上的每一个值都叫做
主键值
。
什么是主键?有什么用?
主键值是每一行记录的唯一标识!!身份证号!!
任何一张表都应该有主键,没有主键,表无效!
主键的特征 :not null + unique
-- 列级约束
drop table if exists tbl_test;
create table tbl_test(
id int primary key,
name varchar(255),
email varchar(255)
);
========================================
-- 表级约束
drop table if exists tbl_test;
create table tbl_test(
id int,
name varchar(255),
email varchar(255),
primary key(id)
);
表级约束主要是给多个字段联合起来添加约束
-- id和name联合起来做主键:复合主键!!
drop table if exists tbl_test;
create table tbl_test(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);
在实际开发中不建议使用复合主键。建议使用单一主键!
因为主键值存在的意义就是这行数据的唯一标识,只要意义达到即可,单一主键就可以做到这个功能。
复合主键比较复杂,不建议使用!
注意:一张表,只能有一个主键!
主键类型
主键建议使用:int
、bigint
、char
不建议使用:varchar
,主键值一般都是数字,一般都是定长的!
自然主键和业务主键
主键除了:单一主键和复合主键之外,还可以这样分类。
- 自然主键:主键值是一个自然数,和业务没有关系。
- 业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键。这就是业务主键!
在实际开发中自然主键的使用比较多,因为主键只要做到不重复就行,不需要有意义。
业务主键不好,因为主键一旦和业务挂钩,那么当业务发生变动的时候,可能会影响到主键值,所以业务主键不建议使用。尽量使用自然主键。
在MySQL当中,有一种机制,可以帮助我们自动维护一个主键值。————自增
插入数据时,可以不插入id值,由MySQL自动生成!
drop table if exists tbl_test;
-- auto_increment表示自增,从1开始,自动递增
create table tbl_test(
id int primary key auto_increment,
name varchar(255),
email varchar(255)
);
insert into tbl_test(name,email) values('张三','2238@qq.com');
insert into tbl_test(name,email) values('张三','2238@qq.com');
insert into tbl_test(name,email) values('张三','2238@qq.com');
insert into tbl_test(name,email) values('张三','2238@qq.com');
外键约束(🌕)
foreign key,简称FK
外键约束的相关术语:
外键约束:一种约束(foreign key)
外键字段:该字段上添加了外键约束
外键值:外键字段当中的每一个值
业务背景
请设计数据库表,来描述“班级和学生”的信息?
第一种方案:班级和学生存储在一张表中。
tbl_student
sno(PK) | name | class_no | class_name |
---|---|---|---|
1 | 张三 | 100 | 高三1班 |
2 | 李四 | 100 | 高三1班 |
3 | 王五 | 100 | 高三1班 |
4 | 赵六 | 100 | 高三1班 |
5 | 法外狂徒 | 101 | 高三2班 |
6 | 暴怒骑士 | 101 | 高三2班 |
7 | 齐天大圣 | 101 | 高三2班 |
8 | 腕豪 | 101 | 高三2班 |
分析以上方案的缺点:数据冗余,空间浪费!这个设计是比较失败的。
第二种方案:班级一张表、学生一张表
tbl_class
class_no(PK) | class_name |
---|---|
100 | 高三1班 |
101 | 高三2班 |
tbl_student
sno(PK) | name | class_no(FK引用tbl_class的class_no) |
---|---|---|
1 | 张三 | 100 |
2 | 李四 | 100 |
3 | 王五 | 100 |
4 | 赵六 | 100 |
5 | 法外狂徒 | 101 |
6 | 暴怒骑士 | 101 |
7 | 齐天大圣 | 101 |
8 | 腕豪 | 101 |
有一个问题:学生表中的class_no字段没有任何约束的时候,可能会导致数据失效。可能出现一个102,但是102班级不存在。
所以为了保证cno字段中的值都是100和101,需要给学生表中的class_no字段添加外键约束。
那么:学生表中的class_no字段就是外键字段,字段中的每一个值都是外键值。
那么此时,注意了:
tbl_class是父表
tbl_student是子表
- 删除表的顺序:先删除子,再删除父
- 创建表的顺序:先创建父,再创建子
- 删除数据的顺序:先删除子,再删除父
- 插入数据的顺序:先插入父,再插入子
drop table if exists tbl_student;
drop table if exists tbl_class;
create table tbl_class(
class_no int primary key,
class_name varchar(255)
);
create table tbl_student(
sno int primary key auto_increment,
name varchar(255),
class_no int,
foreign key(class_no) references tbl_class(class_no)
);
insert into tbl_class(class_no,class_name) values(100, '高三1班');
insert into tbl_class(class_no,class_name) values(101, '高三2班');
insert into tbl_student(name,class_no) values('张三', 100); -- 成功
insert into tbl_student(name,class_no) values('李四', 100); -- 成功
insert into tbl_student(name,class_no) values('法外狂徒', 101); -- 成功
insert into tbl_student(name,class_no) values('暴怒骑士', 101); -- 成功
insert into tbl_student(name,class_no) values('牧魂人', 102); -- 失败,因为外键102不行
思考:子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?
答:不一定,但是必须具有唯一性。因为外键可以为NULL。
测试:外键可以为NULL吗?
答:可以为NULL。