来源：拉勾教育Java就业集训营

1.数据库的基本概念

1.1 什么是数据库

1. 数据库(DataBase) 就是存储和管理数据的仓库

2. 其本质是一个文件系统, 还是以文件的方式,将数据保存在电脑上

1.2 为什么使用数据库

数据存储方式的比较

通过上面的比较,我们可以看出,使用数据库存储数据, 用户可以非常方便对数据库中的数据进行增加, 删 除, 修改及查询操作。

1.3 常见的数据库

为什么选择MySQL ? 1. 功能强大,足以应付web应用开发 2. 开源, 免费

2.SQL(重点)

2.1 SQL的概念

1） 什么是SQL ？

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库 查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

2） SQL 的作用

• 是所有关系型数据库的统一查询规范，不同的关系型数据库都支持SQL

• 所有的关系型数据库都可以使用SQL

• 不同数据库之间的SQL 有一些区别 方言

2.2 SQL通用语法

1） SQL语句可以单行 或者 多行书写，以分号 结尾 ; （Sqlyog中可以不用写分号）

2） 可以使用空格和缩进来增加语句的可读性。

3） MySql中使用SQL不区分大小写，一般关键字大写，数据库名 表名列名 小写。

4） 注释方式

# show databases; 单行注释
-- show databases; 单行注释
/*
多行注释
show databases;
*/

2.3 SQL的分类

SQL的分类.jpg

2.4 DDL操作 数据库

2.4.1 创建数据库

方式1 直接指定数据库名进行创建
默认数据库字符集为：latin1
*/
CREATE DATABASE db1;
/*
方式2 指定数据库名称，指定数据库的字符集
一般都指定为 utf8,与Java中的编码保持一致
*/
CREATE DATABASE db1_1 CHARACTER SET utf8;

2.4.2 查看/选择数据库

USE db1_1;
-- 查看当前正在使用的数据库
SELECT DATABASE();
-- 查看Mysql中有哪些数据库
SHOW DATABASES;
-- 查看一个数据库的定义信息
SHOW CREATE DATABASE db1_1;

2.4.3 修改数据库

修改数据库字符集

ALTER DATABASE db1 CHARACTER SET utf8;
-- 查看当前数据库的基本信息，发现编码已更改
SHOW CREATE DATABASE db1;
</pre>

##### 2.4.4 删除数据库

| 命令 | 说明 |
| --- | --- |
| drop database 数据库名 | 从MySql中永久的删除某个数据库 |

```-- 删除某个数据库
DROP DATABASE db1_1;

2.5 DDL 操作 数据表

2.5.1 MySQL常见的数据类型

1）常用的数据类型：

2） 详细的数据类型（了解即可）

微信图片_20210422173245.png

注意：MySQL中的 char类型与 varchar类型，都对应了 Java中的字符串类型，区别在于：

• char类型是固定长度的： 根据定义的字符串长度分配足够的空间。

• varchar类型是可变长度的： 只使用字符串长度所需的空间

比如：保存字符串 "abc"

x char(10) 占用10个字节
y varchar(10) 占用3个字节

适用场景：

• char类型适合存储 固定长度的字符串，比如 密码 ，性别一类

• varchar类型适合存储 在一定范围内，有长度变化的字符串

2.5.2 创建表

语法格式：

字段名称1 字段类型（长度），
字段名称2 字段类型 注意 最后一列不要加逗号
)；
#快速创建一个表结构相同的表（复制表结构）
-- 创建一个表结构与 test1 相同的 test2表
CREATE TABLE test2 LIKE test1;

2.5.3 查看表

2.5.4 删除表

2.5.5 修改表

1）修改表名

rename table 旧表名 to 新表名

2. 修改表的字符集

alter table 表名 character set 字符集

3）向表中添加列， 关键字 ADD

alert table 表名 add 字段名称 字段类型

4）修改表中列的 数据类型或长度 ， 关键字 MODIFY

alter table 表名 modify 字段名称 字段类型

5）修改列名称 , 关键字 CHANGE

alter table 表名 change 旧列名 新列名 类型(长度);

6）删除列 ，关键字 DROP

alter table 表名 drop 列名;

2.6 DML 操作表中数据

2.6.1 插入数据

insert into 表名 （字段名1，字段名2...） values(字段值1，字段值2...);
#方式1： 插入全部字段， 将所有字段名都写出来
INSERT INTO student (sid,sname,age,sex,address) VALUES(1,'孙悟空',20,'男','花果山');
#方式2： 插入全部字段，不写字段名
INSERT INTO student VALUES(2,'孙悟饭',10,'男','地球');
#方式3：插入指定字段的值
INSERT INTO category (cname) VALUES('白骨精');

注意：

1. 值与字段必须要对应，个数相同&数据类型相同

2）值的数据大小，必须在字段指定的长度范围内

3）varchar char date类型的值必须使用单引号，或者双引号 包裹。

4）如果要插入空值，可以忽略不写，或者插入null

5. 如果插入指定字段的值，必须要上写列名

2.6.2 更改数据

#不带条件的修改
update 表名 set 列名 = 值
#带条件的修改
update 表名 set 列名 = 值 [where 条件表达式：字段名 = 值 ]
#一次修改多个列， 将sid为 2 的学员，年龄改为 20，地址改为 北京
UPDATE student SET age = 20,address = '北京' WHERE sid = 2;

2.6.3 删除数据

#删除所有数据
delete from 表名
#指定条件 删除数据
delete from 表名 [where 字段名 = 值]
#如果要删除表中的所有数据,有两种做法
#1\. delete from 表名; 不推荐. 有多少条记录 就执行多少次删除操作. 效率低
#2\. truncate table 表名: 推荐. 先删除整张表, 然后再重新创建一张一模一样的表. 效率高
truncate table student;

2.7 DQL 查询表中数据

2.7.1 简单查询

查询不会对数据库中的数据进行修改.只是一种显示数据的方式 SELECT

#语法格式
select 列名 from 表名

#查询emp中的 所有数据
SELECT * FROM emp; -- 使用 * 表示所有列

#查询emp表中的所有记录，仅显示id和name字段
SELECT eid,ename FROM emp;

#将所有的员工信息查询出来，并将列名改为中文
#别名查询，使用关键字 as 
SELECT
 eid AS '编号',
 hire_date '入职时间', -- AS 可以省略
FROM emp;

#查询一共有几个部门
#使用去重关键字 distinct
-- 使用distinct 关键字,去掉重复部门信息
SELECT DISTINCT dept_name FROM emp;

#将所有员工的工资 +1000 元进行显示
#运算查询 (查询结果参与运算) 
SELECT ename , salary + 1000 FROM emp;

2.7.2 条件查询

如果查询语句中没有设置条件,就会查询所有的行信息,在实际应用中,一定要指定查询条件,对记录进行过 滤

select 列名 from 表名 where 条件表达式
* 先取出表中的每条数据,满足条件的数据就返回,不满足的就过滤掉

运算符

1. 比较运算符

2. 逻辑运算符

模糊查询 通配符

2.7.3排序

通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据

SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
#ASC 表示升序排序(默认)
#DESC 表示降序排序

排序方式

1. 单列排序 :只按照某一个字段进行排序, 就是单列排序

-- 默认升序排序 ASC
SELECT * FROM emp ORDER BY salary;
-- 降序排序
SELECT * FROM emp ORDER BY salary DESC;

2. 组合排序:同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推

-- 组合排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;

2.7.4聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵 向查询，它是对某一列的值进行计算，然后返回一个单一的值(另外聚合函数会忽略null空值。)；

SELECT 聚合函数(字段名) FROM 表名;

2.7.5分组

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组

SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];

注意：分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作. * 查询其他字段没有意义

• 需求:查询平均薪资大于6000的部门.

• 分析： 需要在分组后,对数据进行过滤,使用 关键字 hiving

• 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

# 查询平均薪资大于6000的部门
-- 需要在分组后再次进行过滤,使用 having
SELECT
dept_name ,
AVG(salary)
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING AVG(salary) >
6000 ;

where 与 having的区别

2.7.6 limit关键字

limit 关键字的作用

• limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)

• limit 语法是 MySql的方言,用来完成分页

SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;
#limit offset , length; 关键字可以接受一个 或者两个 为0 或者正整数的参数
#offset 起始行数, 从0开始记数, 如果省略 则默认为 0
#length 返回的行数

-- 分页操作 每页显示3条数据
SELECT * FROM emp LIMIT 0,3; -- 第1页
SELECT * FROM emp LIMIT 3,3; -- 第2页 2-1=1 1*3=3
SELECT * FROM emp LIMIT 6,3; -- 第三页
-- 分页公式 起始索引 = (当前页 - 1) * 每页条数
-- limit是MySql中的方言

3.SQL约束

1. 约束的作用:对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中

2. 常见的约束

3.1 主键约束

3.1.1 添加主键约束

字段名 字段类型 primary key

CREATE TABLE emp2(
-- 设置主键 唯一 非空
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);
-- 删除表
DROP TABLE emp2;
-- 方式2 创建一个带主键的表
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1),
-- 指定主键为 eid字段
PRIMARY KEY(eid)
);
-- 方式3 创建一个带主键的表
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1)
)
-- 创建的时候不指定主键,然后通过 DDL语句进行设置
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

哪些字段可以作为主键 ?

• 通常针对业务去设计主键,每张表都设计一个主键id

• 主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复 就好,比如 身份证就可以作为主键.

3.1.2 删除主键约束

ALTER TABLE emp2 DROP PRIMARY KEY;
DESC emp2;

3.1.3 主键的自增

注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段 的值.

AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

3.1.4 修改主键自增的起始值

默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式

CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;
-- 插入数据,观察主键的起始值
INSERT INTO emp2(ename,sex) VALUES('张百万','男');
INSERT INTO emp2(ename,sex) VALUES('艳秋','女')

3.1.5 DELETE和TRUNCATE对自增长的影响

删除表中所有数据有两种方式

3.2 非空约束

非空约束的特点: 某一列不予许为空

字段名 字段类型 not null

3.3 唯一约束

唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )

字段名 字段值 unique

主键约束与唯一约束的区别:

• 主键约束 唯一且不能够为空

• 唯一约束,唯一 但是可以为空

• 一个表中只能有一个主键 , 但是可以有多个唯一约束

3.4 外键约束

3.4.1 什么是外键

• 外键指的是在 从表 中 与 主表 的主键对应的那个字段,比如员工表的 dept_id,就是外键

• 使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性

微信图片_20210424203428.png

• 多表关系中的主表和从表 主表: 主键id所在的表, 约束别人的表 从表: 外键所在的表多, 被约束的表

微信图片_20210424203434.png

3.4.2 创建外键约束

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
-- 已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES
主表(主 键字段名);
-- 可以省略外键名称, 系统会自动生成一个

添加外键约束,就会产生强制性的外键数据检查, 从而保证了数据的完整性和一致性,插入一条外键不存在的数据时会报错，外键可以为NULL

3.4.3 删除外键约束

alter table 从表 drop foreign key 外键约束名称

3.4.4 外键约束的注意事项

1. 从表外键类型必须与主表主键类型一致 否则创建失败.

2. 添加数据时, 应该先添加主表中的数据.

3. 删除数据时,应该先删除从表中的数据.

3.4.5 级联删除操作(了解)

如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作

级联删除
ON DELETE CASCADE
-- 重新创建添加级联操作
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
-- 添加级联删除
ON DELETE CASCADE
);

3.5 默认值

默认值约束 用来指定某列的默认值

字段名 字段类型 DEFAULT 默认值

4. 多表关系设计

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、 商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们一起 学习一下多表关系设计方面的知识

4.1 一对多关系(常见)

• 一对多关系（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品

• 一对多建表原则 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

一对多.jpg

4.2 多对多关系(常见)

• 多对多（m:n） 例如：老师和学生，学生和课程，用户和角色

• n 多对多关系建表原则 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的 主 键。

多对多.jpg

4.3 一对一关系(了解)

• 一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表。

• 一对一建表原则 外键唯一 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE

一对一.jpg

5.多表查询

5.1 什么是多表查询

• DQL: 查询多张表,获取到需要的数据

• 比如 我们要查询家电分类下 都有哪些商品,那么我们就需要查询分类与商品这两张表

5.2数据准备

CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);
#商品表 (多方 从表)
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
category_id VARCHAR(32),
-- 添加外键约束
FOREIGN KEY (category_id) REFERENCES category (cid)
);

5.3 笛卡尔积

交叉连接查询,因为会产生笛卡尔积,所以 基本不会使用,得到两个表数据的乘积

SELECT 字段名 FROM 表1, 表2;

5.4 多表查询的分类

5.4.1 内连接查询

• 内连接的特点:

  • 通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示

    • 比如通过: 从表的外键 = 主表的主键 方式去匹配

5.4.1.1 隐式内连接

from子句 后面直接写 多个表名 使用where指定连接条件的 这种连接方式是 隐式内连接. 使用where条件过滤无用的数据

SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;

-- 可以通过给表起别名的方式, 方便我们的查询(有提示)
#查询商品表的商品名称 和 价格,以及商品的分类信息
SELECT
p.`pname`,
p.`price`,
c.`cname`
FROM products p , category c WHERE p.`category_id` = c.`cid`;

5.4.1.2 显式内连接

使用 inner join ...on 这种方式, 就是显式内连接

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
-- inner 可以省略
# 显式内连接查询  查询所有商品信息和对应的分类信息
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;

5.4.2 外连接查询

5.4.2.1 左外连接

• 左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略

• 左外连接的特点

  • 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据

  • 如果匹配不到, 左表中的数据正常展示, 右边的展示为null.

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
-- 左外连接查询
SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;

5.4.2.2 右外连接

• 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略

• 右外连接的特点

  • 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据

  • 如果匹配不到，右表中的数据正常展示, 左边展示为null

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
-- 右外连接查询
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

5.4.3 各种连接方式的总结

多表查询总结.jpg

• 内连接: inner join , 只获取两张表中 交集部分的数据.

• 左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分

• 右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分

6.子查询 (SubQuery)

6.1 什么是子查询

• 子查询概念

  • 一条select 查询语句的结果, 作为另一条 select 语句的一部分

• 子查询的特点

  • 子查询必须放在小括号中 子查询一般作为父查询的查询条件使用

• 子查询常见分类

  • where型 子查询: 将子查询的结果, 作为父查询的比较条件

  • from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用

  • exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查 询的结果

6.2 子查询的结果作为查询条件

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
#1\. 通过子查询的方式, 查询价格最高的商品信息
# 通过子查询的方式, 查询价格最高的商品信息
-- 1.先查询出最高价格
SELECT MAX(price) FROM products;
-- 2.将最高价格作为条件,获取商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

6.3 子查询的结果作为一张表

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;
#1\. 查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称
-- 1\. 先查询分类表的数据
SELECT * FROM category;
-- 2.将上面的查询语句 作为一张表使用
SELECT
p.`pname`,
p.`price`,
c.cname
FROM products p
-- 子查询作为一张表使用时 要起别名 才能访问表中字段
INNER JOIN (SELECT * FROM category) c
ON p.`category_id` = c.cid WHERE p.`price` > 500;

注意： 当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段。

6.4 子查询结果是单列多行

• 子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

#SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
#1\. 查询价格小于两千的商品,来自于哪些分类(名称)
-- 子查询获取的是单列多行数据
SELECT * FROM category
WHERE cid IN (SELECT DISTINCT category_id FROM products WHERE price < 2000);

6.5 子查询总结

1. 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.

2. 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).