卷妹带你学数据库---5天冲刺Day3

卷妹带你学数据库—5天冲刺Day3

👩‍💻博客主页：京与旧铺的博客主页

✨欢迎关注🖱点赞🎀收藏⭐留言✒

🔮本文由京与旧铺原创，

😘系列专栏：java学习

👕参考网课：尚硅谷

💻首发时间：🎞2022年6月7日🎠

🎨你做三四月的事，八九月就会有答案，一起加油吧

🀄如果觉得博主的文章还不错的话，请三连支持一下博主哦

🎧最后的话，作者是一个新人，在很多方面还做的不好，欢迎大佬指正，一起学习哦，冲冲冲

💬推荐一款模拟面试、刷题神器👉点击进入网站

🛒导航小助手🎪

文章目录

卷妹带你学数据库---5天冲刺Day3
🛒导航小助手🎪

@[toc]

子查询
union合并
limit的使用
表的创建
插入数据insert
insert插入日期
删除数据 delete
约束
唯一性约束: unique
主键约束: primary key

子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。

子查询都可以出现在哪里呢？

select

…(select).

from

…(select).

where

…(select).

where子句中的子查询:

第一步：查询最低工资是多少

select min(sal) from emp;

±---------+

| min(sal) |

±---------+

| 800.00 |

±---------+

第二步：找出>800的

select ename,sal from emp where sal > 800;

第三步：合并

select ename,sal from emp where sal > (select min(sal) from emp);

from子句中的子查询:

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。

select后面出现的子查询（这个内容不需要掌握，了解即可！！！）

注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果

union合并

案例：查询工作岗位是MANAGER和SALESMAN的员工？

select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;

select ename,job from emp where job in(‘MANAGER’,‘SALESMAN’);

±-------±---------+

| ename | job |

±-------±---------+

| ALLEN | SALESMAN |

| WARD | SALESMAN |

| JONES | MANAGER |

| MARTIN | SALESMAN |

| BLAKE | MANAGER |

| CLARK | MANAGER |

| TURNER | SALESMAN |

±-------±---------+

select ename,job from emp where job = ‘MANAGER’

union

select ename,job from emp where job = ‘SALESMAN’;

±-------±---------+

| ename | job |

±-------±---------+

| JONES | MANAGER |

| BLAKE | MANAGER |

| CLARK | MANAGER |

| ALLEN | SALESMAN |

| WARD | SALESMAN |

| MARTIN | SALESMAN |

| TURNER | SALESMAN |

±-------±---------+

union的效率要高一些。对于表连接来说，每连接一次新表，

则匹配的次数满足笛卡尔积，成倍的翻。。。

但是union可以减少匹配的次数。在减少匹配次数的情况下，

还可以完成两个结果集的拼接。

a 连接 b 连接 c

a 10条记录

b 10条记录

c 10条记录

匹配次数是：1000

a 连接 b一个结果：10 * 10 --> 100次

a 连接 c一个结果：10 * 10 --> 100次

使用union的话是：100次 + 100次 = 200次。（union把乘法变成了加法运算）

union在使用的时候有注意事项吗？

//错误的：union在进行结果集合并的时候，要求两个结果集的列数相同。

select ename,job from emp where job = ‘MANAGER’

union

select ename from emp where job = ‘SALESMAN’;

// MYSQL可以，oracle语法严格，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。

select ename,job from emp where job = ‘MANAGER’

union

select ename,sal from emp where job = ‘SALESMAN’;

limit

limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。

百度默认：一页显示10条记录。

分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差。可以一页一页翻页看

limit的使用

完整用法：limit startIndex, length

startIndex是起始下标，length是长度。

起始下标从0开始。

缺省用法：limit 5; 这是取前5

注意：mysql当中limit在order by之后执行！！！！！！

分页

每页显示3条记录

第1页：limit 0,3 [0 1 2]

第2页：limit 3,3 [3 4 5]

第3页：limit 6,3 [6 7 8]

第4页：limit 9,3 [9 10 11]

每页显示pageSize条记录

第pageNo页：limit (pageNo - 1) * pageSize , pageSize

public static void main(String[] args){

// 用户提交过来一个页码，以及每页显示的记录条数

int pageNo = 5; //第5页

int pageSize = 10; //每页显示10条

int startIndex = (pageNo - 1) * pageSize;

String sql = "select …limit " + startIndex + ", " + pageSize;

}

记公式：

limit (pageNo-1)*pageSize , pageSize

关于DQL语句的大总结：

select

…

from

…

where

…

group by

…

having

…

order by

…

limit

…

执行顺序？

1.from

2.where

3.group by

4.having

5.select

6.order by

7.limit…

表的创建

建表的语法格式：(建表属于DDL语句，DDL包括：create drop alter)

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

create table 表名(

字段名1 数据类型,

字段名2 数据类型,

字段名3 数据类型

);

表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。

字段名：见名知意。

表名和字段名都属于标识符。

关于mysql中的数据类型

很多数据类型，我们只需要掌握一些常见的数据类型即可。

varchar(最长255)

可变长度的字符串

比较智能，节省空间。

会根据实际的数据长度动态分配空间。

优点：节省空间

缺点：需要动态分配空间，速度慢。

char(最长255)

定长字符串

不管实际的数据长度是多少。

分配固定长度的空间去存储数据。

使用不恰当的时候，可能会导致空间的浪费。

优点：不需要动态分配空间，速度快。

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

varchar 和 char 我们应该怎么选择？

性别字段你选什么？因为性别是固定长度的字符串，所以选择char。

姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。

int(最长11)

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

bigint

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

float

单精度浮点型数据

double

双精度浮点型数据

date

短日期类型

datetime

长日期类型

clob

字符大对象

最多可以存储4G的字符串。

比如：存储一篇文章，存储一个说明。

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

Character Large OBject:CLOB

blob

二进制大对象

Binary Large OBject

专门用来存储图片、声音、视频等流媒体数据。

往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，

你需要使用IO流才行。

创建一个学生表？

学号、姓名、年龄、性别、邮箱地址

create table t_student(

no int,

name varchar(32),

sex char(1),

age int(3),

email varchar(255)

);

删除表：

drop table t_student; // 当这张表不存在的时候会报错！

// 如果这张表存在的话，删除

drop table if exists t_student;

插入数据insert

插入数据insert （DML）

语法格式：

insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3);

注意：字段名和值要一一对应。什么是一一对应？

数量要对应。数据类型要对应。

insert语句中的“字段名”可以省略吗？可以

insert into t_student values(2); //错误的

// 注意：前面的字段名省略的话，等于都写上了！所以值也要都写上！

insert into t_student values(2, ‘lisi’, ‘f’, 20, ‘[email protected]’);

±-----±-----±-----±-----±-------------+

| no | name | sex | age | email |

±-----±-----±-----±-----±-------------+

| 2 | lisi | f | 20 | [email protected] |

±-----±-----±-----±-----±-------------+

一次可以插入多条记录：

insert into t_user(id,name,birth,create_time) values

(1,‘zs’,‘1980-10-11’,now()),

(2,‘lisi’,‘1981-10-11’,now()),

(3,‘wangwu’,‘1982-10-11’,now());

语法：insert into t_user(字段名1,字段名2) values(),(),(),();

insert插入日期

数字格式化：format

格式化数字：format(数字, ‘格式’)

select ename,format(sal, ‘$999,999’) as sal from emp;

str_to_date：将字符串varchar类型转换成date类型

date_format：将date类型转换成具有一定格式的varchar字符串类型。

插入数据？

insert into t_user(id,name,birth) values(1, ‘zhangsan’, ‘01-10-1990’); // 1990年10月1日

出问题了：原因是类型不匹配。数据库birth是date类型，这里给了一个字符串varchar。

怎么办？可以使用str_to_date函数进行类型转换。

str_to_date函数可以将字符串转换成日期类型date？

语法格式：

str_to_date(‘字符串日期’, ‘日期格式’)

mysql的日期格式：

%Y 年

%m 月

%d 日

%h 时

%i 分

%s 秒

insert into t_user(id,name,birth) values(1, ‘zhangsan’, str_to_date(‘01-10-1990’,‘%d-%m-%Y’));

str_to_date函数可以把字符串varchar转换成日期date类型数据，

通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，

需要通过该函数将字符串转换成date。

如果你提供的日期字符串是这个格式，str_to_date函数就不需要了！！！

%Y-%m-%d

insert into t_user(id,name,birth) values(2, ‘lisi’, ‘1990-10-01’);

查询的时候可以以某个特定的日期格式展示吗？

date_format

这个函数可以将日期类型转换成特定格式的字符串。

select id,name,date_format(birth, ‘%m/%d/%Y’) as birth from t_user;

±-----±---------±-----------+

| id | name | birth |

±-----±---------±-----------+

| 1 | zhangsan | 10/01/1990 |

| 2 | lisi | 10/01/1990 |

±-----±---------±-----------+

date_format函数怎么用？

date_format(日期类型数据, ‘日期格式’)

这个函数通常使用在查询日期方面。设置展示的日期格式。

mysql> select id,name,birth from t_user;

±-----±---------±-----------+

| id | name | birth |

±-----±---------±-----------+

| 1 | zhangsan | 1990-10-01 |

| 2 | lisi | 1990-10-01 |

±-----±---------±-----------+

以上的SQL语句实际上是进行了默认的日期格式化，

自动将数据库中的date类型转换成varchar类型。

并且采用的格式是mysql默认的日期格式：‘%Y-%m-%d’

select id,name,date_format(birth,‘%Y/%m/%d’) as birth from t_user;

java中的日期格式？

yyyy-MM-dd HH:mm:ss SSS

date和datetime两个类型的区别？

date是短日期：只包括年月日信息。

datetime是长日期：包括年月日时分秒信息。

mysql短日期默认格式：%Y-%m-%d

mysql长日期默认格式：%Y-%m-%d %h:%i:%s

修改update

语法格式：

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where 条件;

注意：没有条件限制会导致所有数据全部更新。

update t_user set name = ‘jack’, birth = ‘2000-10-11’ where id = 2;

±-----±---------±-----------±--------------------+

±-----±---------±-----------±--------------------+

| 1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |

| 2 | jack | 2000-10-11 | 2020-03-18 15:51:23 |

±-----±---------±-----------±--------------------+

update t_user set name = ‘jack’, birth = ‘2000-10-11’, create_time = now() where id = 2;

更新所有？

update t_user set name = ‘abc’;

删除数据 delete

语法格式？

delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除！

delete from t_user where id = 2;

insert into t_user(id) values(2);

delete from t_user; // 删除所有！

快速删除表中的数据？【truncate比较重要，必须掌握】

//删除dept_bak表中的数据

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

mysql> select * from dept_bak;

Empty set (0.00 sec)

delete语句删除数据的原理？（delete属于DML语句！！！）

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！

这种删除缺点是：删除效率比较低。

这种删除优点是：支持回滚，后悔了可以再恢复数据！！！

truncate语句删除数据的原理？

这种删除效率比较高，表被一次截断，物理删除。

这种删除缺点：不支持回滚。

这种删除优点：快速。

用法：truncate table dept_bak; （这种操作属于DDL操作。）

大表非常大，上亿条记录？？？？

删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。

可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。

但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！

truncate是删除表中的数据，表还在！

删除表操作？

drop table 表名; // 这不是删除表中的数据，这是把表删除。

约束

什么是约束？

约束对应的英语单词： constraint

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的

完整性、有效性！！！

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

约束包括哪些？

非空约束：not null

唯一性约束: unique

主键约束: primary key （简称PK）

外键约束：foreign key（简称FK）

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

非空约束：not null

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

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255) not null // not null只有列级约束，没有表级约束！

);

唯一性约束: unique

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

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255) unique,

email varchar(255)

);

如何做到联合唯一约束

drop table if exists t_vip;

create table t_vip(

id int,

name varchar(255),

email varchar(255),

unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。

);

name和email两个字段联合起来唯一

在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。

主键约束: primary key

主键约束的相关术语？

主键约束：就是一种约束。

主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段

主键值：主键字段中的每一个值都叫做：主键值。

什么是主键？有啥用？

主键值是每一行记录的唯一标识。

主键值是每一行记录的身份证号！！！

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

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

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

drop table if exists t_vip;

// 1个字段做主键，叫做：单一主键

create table t_vip(

id int primary key, //列级约束

name varchar(255)

);

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

drop table if exists t_vip;

// id和name联合起来做主键：复合主键！！！！

create table t_vip(

id int,

name varchar(255),

email varchar(255),

primary key(id,name)

);

在实际开发中不建议使用：复合主键。建议使用单一主键！

因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。

复合主键比较复杂，不建议使用！！！

主键值建议使用：

int

bigint

char

等类型。

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

主键除了：单一主键和复合主键之外，还可以这样进行分类？

自然主键：主键值是一个自然数，和业务没关系。

业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

在实际开发中使用业务主键多，还是使用自然主键多一些？

自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。

业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，

可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

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

drop table if exists t_vip;

create table t_vip(

id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！

name varchar(255)

义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。

复合主键比较复杂，不建议使用！！！

主键值建议使用：

int

bigint

char

等类型。

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

主键除了：单一主键和复合主键之外，还可以这样进行分类？

自然主键：主键值是一个自然数，和业务没关系。

业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

在实际开发中使用业务主键多，还是使用自然主键多一些？

自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。

业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，

可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

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

drop table if exists t_vip;

create table t_vip(

id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！

name varchar(255)

卷妹带你学数据库---5天冲刺Day3