Loading

MySQL笔记(二)

一、表的创建和数据类型

建表的语法格式

建表语句属于DDL语句,DDL包括:create、drop、alter

create table <表名>(
	<字段名> <数据类型>,
    <字段名> <数据类型>,
    <字段名> <数据类型>
);

注意:表名和字段名都属于标识符。要见名知意!!

表名:建议以 t_tbl_ 开始,可读性强。

字段名:见名知意。

数据类型

类型后面的长度不是字符也不是字节。,就是长度。英文'a'是1个长度,中文"啊"也是1个长度。

  1. varchar(最长255)

    可变长度的字符串,比较智能,节省空间。会根据实际的数据长度动态分配空间。

    优点:节省空间

    缺点:需要动态分配空间,速度慢。

  2. char(最长255)

    定长字符串,不管实际的数据长度是多少,分配固定长度的空间去存储数据。若使用不恰当,可能会导致空间的浪费。

    优点:不需要动态分配空间,速度快。

    缺点:使用不当可能导致空间的浪费。

varchar和char应该怎么选择?

​ 性别字段选什么?因为性别是固定长度的字符串('男'、'女'),所以选char。(char(1))

​ 姓名字段选什么?因为姓名的长度有长有短,所以选择varchar。

  1. int(最长11)

    数字中的整数型。等同于Java中的int。

  2. bigint

    数字中的长整型。等同于Java中的long。

  3. float

    单精度浮点型数据。

  4. double

    双精度浮点型数据。

  5. date

    短日期类型。

  6. datetime

    长日期类型。

    clob

    字符大对象,最多可以存储 4G 的字符串。比如:存储一篇文章,存储一个说明。

    超过255个字符的都要采用CLOB字符大对象来存储。

    Character Large OBject:CLOB

  7. 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秒。但是不可恢复,删表前请仔细询问客户!

八、对表结构的增删改

什么是对表结构的修改?

​ 添加一个字段、删除一个字段、修改一个字段。

但是!!

  1. 在实际的开发中,需求一旦确定之后,表一旦设计好之后,很少会进行表结构的修改。因为开发进行中的时候,修改表结构,成本比较高。修改表的结构,对应的Java代码就需要进行大量的修改。程表是比较高的。这个责任应该由设计人员来承担!
  2. 由于修改表结构的操作很少,所以——可以使用工具。
-- 增加一个字段
alter table <表名> add column 字段名 类型;
-- 删除一个字段
alter table <表名> drop column 字段名 类型;
-- 修改一个字段
alter table <表名> modify column 字段名 类型;

九、约束(⭐⭐⭐⭐⭐)

什么是约束?

约束对应的英语单词是:constraint

在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性!!!

约束的作用就是为了保证:表中的数据有效!!!

常见的约束

  1. 非空约束:not null
  2. 唯一性约束:unique
  3. 主键约束:primary key (简称PK)
  4. 外键约束:foreign key (简称FK)
  5. 检查约束:check (MySQL不支持,Oracle支持)

重点学习:not nulluniqueprimary keyforeign 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)
);

image-20220430133114237

在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)
);

在实际开发中不建议使用复合主键。建议使用单一主键!
因为主键值存在的意义就是这行数据的唯一标识,只要意义达到即可,单一主键就可以做到这个功能。
复合主键比较复杂,不建议使用!

注意:一张表,只能有一个主键!

主键类型

主键建议使用:intbigintchar

不建议使用:varchar,主键值一般都是数字,一般都是定长的!

自然主键和业务主键

主键除了:单一主键和复合主键之外,还可以这样分类。

  1. 自然主键:主键值是一个自然数,和业务没有关系。
  2. 业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键。这就是业务主键!

在实际开发中自然主键的使用比较多,因为主键只要做到不重复就行,不需要有意义。

业务主键不好,因为主键一旦和业务挂钩,那么当业务发生变动的时候,可能会影响到主键值,所以业务主键不建议使用。尽量使用自然主键。

在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');

image-20220430140127511

外键约束(🌕)

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是子表

  1. 删除表的顺序:先删除子,再删除父
  2. 创建表的顺序:先创建父,再创建子
  3. 删除数据的顺序:先删除子,再删除父
  4. 插入数据的顺序:先插入父,再插入子
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。

参考资源:老杜带你学_mysql入门基础(mysql基础视频+数据库实战)_哔哩哔哩_bilibili

posted @ 2022-05-01 11:44  KledKled  阅读(25)  评论(0编辑  收藏  举报