1、数据库
1.1 概念
数据库是“按照数据结构来组织,存储和管理数据的仓库”,是长期存储在计算机内的、有组织的、有共享的、同意管理的数据集合
1.2 数据库的分类
- 网状结构数据库:美国通用电气公司IDS(Integrated Data Store),以节点形式存储和访问。
- 层次结构数据库:IBM公司IMS(Information Management System)定向有序的树状结构实现存储和访问
- 关系结构数据库:Oracle、DB2、MySQL、Sql Server以表格的形式存储,多表之间建立关联关系,通过分类、合并、连接、选取等运算实现访问
- 非关系型数据库:ElastecSearch、MongoDB、Redis,多数使用哈希表,表中以键值(key-value)的方式实现特定的键和一个指针指向的特定数据。
1.3 mysql简介
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational DatabaseManagement System:关系数据库管理系统)应用软件之一。
1.4 mysql下载安装
1.4.1 mysql下载
链接:https://pan.baidu.com/s/117wwrQ-jRa6F8FUah7FVfg
提取码:85i8
1.4.2 安装
详情见:java day34 mysql (1)
https://blog.csdn.net/qq_43642864/article/details/112196322
2、数据查询
2.1 排序查询
排序规则:
- ASC: 升序排序
- DESC:降序排序
语法:
- SELECT 列名 FROM 表名 ORDER BY 排序列 [排序规则]
单列排序
#查询员工的编号,名字,薪资。按照工资高低进行降序排序。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC;
多列排序
#查询员工的编号,名字,薪资。按照工资高低进行升序排序(薪资相同时,按照编号进行升序排序)。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC , employee_id ASC;
2.2 条件查询
关键字
关键字 | 描述 |
---|---|
where | 在查询结结果中,筛选符合where后边的条件的数据,条件为布尔表达式 |
7.4.1 等值判断(=)
#查询薪资是11000的员工信息(编号、名字、薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000;
7.4.2 逻辑判断(and、or、not)
#查询薪资是11000并且提成是0.30的员工信息(编号、名字、薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000 AND commission_pct = 0.30;
7.4.3 不等值判断(> 、< 、>= 、<= 、!= 、<>)
#查询员工的薪资在6000~10000之间的员工信息(编号,名字,薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary >= 6000 AND salary <= 10000;
7.4.4 区间判断(between and)
#查询员工的薪资在6000~10000之间的员工信息(编号,名字,薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary BETWEEN 6000 AND 10000; #闭区间,包含区间边界的两个值
7.4.5 NULL 值判断(IS NULL、IS NOT NULL)
- IS NULL
列名 IS NULL- IS NOT NULL
列名 IS NOT NULL
#查询没有提成的员工信息(编号,名字,薪资 , 提成)
SELECT employee_id , first_name , salary , commission_pct
FROM t_employees
WHERE commission_pct IS NULL;
7.4.6 枚举查询( IN (值 1,值 2,值 3 ) )
#查询部门编号为70、80、90的员工信息(编号,名字,薪资 , 部门编号)
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN(70,80,90);
注:in的查询效率较低,可通过多条件拼接。
7.4.7 模糊查询
- LIKE _ (单个任意字符)
列名 LIKE ‘张_’- LIKE %(任意长度的任意字符)
列名 LIKE ‘张%’
#查询名字以"L"开头的员工信息(编号,名字,薪资 , 部门编号)
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L%';
#查询名字以"L"开头并且长度为4的员工信息(编号,名字,薪资 , 部门编号)
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L___';
7.4.8 分支结构查询
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
WHEN 条件3 THEN 结果3
ELSE 结果
END
#查询员工信息(编号,名字,薪资 , 薪资级别<对应条件表达式生成>)
SELECT employee_id , first_name , salary , department_id ,
CASE
WHEN salary>=10000 THEN 'A'
WHEN salary>=8000 AND salary<10000 THEN 'B'
WHEN salary>=6000 AND salary<8000 THEN 'C'
WHEN salary>=4000 AND salary<6000 THEN 'D'
ELSE 'E'
END as "LEVEL"
FROM t_employees;
2.3 字符串查询
语法: SELECT 字符串函数 ([参数列表])
字符串函数 | 说明 |
---|---|
CONCAT(str1,str2,str…) | 将 多个字符串连接 |
INSERT(str,pos,len,newStr) | 将str 中指定 pos 位置开始 len 长度的内容替换为 newStr |
LOWER(str) | 将指定字符串转换为小写 |
UPPER(str) | 将指定字符串转换为大写 |
SUBSTRING(str,num,len) | 将str 字符串指定num位置开始截取 len 个内容 |
2.4 聚合函数
语法:SELECT 聚合函数(列名) FROM 表名;
聚合函数 | 说明 |
---|---|
SUM() | 求所有行中单列结果的总和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
COUNT() | 求总行数 |
7.7.1 单列总和
#统计所有员工每月的工资总和
SELECT sum(salary)
FROM t_employees;
7.7.2 单列平均值
#统计所有员工每月的平均工资
SELECT AVG(salary)
FROM t_employees;
7.7.3 单列最大值
#统计所有员工中月薪最高的工资
SELECT MAX(salary)
FROM t_employees;
7.7.4 单列最小值
#统计所有员工中月薪最低的工资
SELECT MIN(salary)
FROM t_employees;
7.7.5 总行数
#统计员工总数
SELECT COUNT(*)
FROM t_employees;
#统计有提成的员工人数
SELECT COUNT(commission_pct)
FROM t_employees;
2.5 分组查询
语法:SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列);
关键字 | 说明 |
---|---|
GROUP BY | 分组依据,必须在 WHERE 之后生效 |
7.8.1 查询各部门的总人数
#思路:
#1.按照部门编号进行分组(分组依据是 department_id)
#2.再针对各部门的人数进行统计(count)
SELECT department_id,COUNT(employee_id)
FROM t_employees
GROUP BY department_id;
7.8.2 查询各部门的平均工资
#思路:
#1.按照部门编号进行分组(分组依据department_id)。
#2.针对每个部门进行平均工资统计(avg)。
SELECT department_id , AVG(salary)
FROM t_employees
GROUP BY department_id
7.8.3 查询各个部门、各个岗位的人数
#思路:
#1.按照部门编号进行分组(分组依据 department_id)。
#2.按照岗位名称进行分组(分组依据 job_id)。
#3.针对每个部门中的各个岗位进行人数统计(count)。
SELECT department_id , job_id , COUNT(employee_id)
FROM t_employees
GROUP BY department_id , job_id;
7.8.4 常见问题
#查询各个部门id、总人数、first_name
SELECT department_id , COUNT(*) , first_name
FROM t_employees
GROUP BY department_id; #error
2.6 分组过滤查询
语法:SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则
关键字 | 说明 |
---|---|
HAVING 过滤规则 | 过滤规则定义对分组后的数据进行过滤 |
7.9.1 统计部门的最高工资
#统计60、70、90号部门的最高工资
#思路:
#1). 确定分组依据(department_id)
#2). 对分组后的数据,过滤出部门编号是60、70、90信息
#3). max()函数处理
SELECT department_id , MAX(salary)
FROM t_employees
GROUP BY department_id
HAVING department_id in (60,70,90)
# group确定分组依据department_id
#having过滤出60 70 90部门
#select查看部门编号和max函数。
2.7 限定查询
SELECT 列名 FROM 表名 LIMIT 起始行,查询行数
| 关键字 | 说明 |
| ----------------------------- | ---------------------------- |
| LIMIT offset_start,row_count | 限定查询结果的起始行和总行数 |
7.10.1 查询前 5 行记录
#查询表中前五名员工的所有信息
SELECT * FROM t_employees LIMIT 0,5;
7.10.2 查询范围记录
#查询表中从第四条开始,查询 10 行
SELECT * FROM t_employees LIMIT 3,10;
7.10.3 LIMIT典型应用
分页查询:一页显示 10 条,一共查询三页
#思路:第一页是从 0开始,显示 10 条
SELECT * FROM LIMIT 0,10;
#第二页是从第 10 条开始,显示 10 条
SELECT * FROM LIMIT 10,10;
#第三页是从 20 条开始,显示 10 条
SELECT * FROM LIMIT 20,10;
7.11.1 SQL 语句编写顺序
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列(asc|desc)LIMIT 起始行,总条数
2.8 子查询
语法:
- SELECT 列名 FROM(子查询的结果集)WHERE 条件;
7.14.1 查询员工表中工资排名前 5 名的员工信息
#思路:
#1. 先对所有员工的薪资进行排序(排序后的临时表)
select employee_id , first_name , salary
from t_employees
order by salary desc
#2. 再查询临时表中前5行员工信息
select employee_id , first_name , salary
from (临时表)
limit 0,5;
#SQL:合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from t_employees order by salary desc) as temp
limit 0,5;
2.9 表连接查询
语法:
2.9.1 内连接
#1.查询所有有部门的员工信息(不包括没有部门的员工) SQL 标准
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID
#2.查询所有有部门的员工信息(不包括没有部门的员工) MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID
2.9.1 左连接
#查询所有员工信息,以及所对应的部门名称(没有部门的员工,也在查询结果中,部门名称以NULL 填充)
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e
LEFT JOIN t_departments d
ON e.department_id = d.department_id;
2.9.1 右连接
#查询所有部门信息,以及此部门中的所有员工信息(没有员工的部门,也在查询结果中,员工信息以NULL 填充)
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e
RIGHT JOIN t_departments d
ON e.department_id = d.department_id;
3、约束
实体完整性约束:
- 表中的一行数据代表一个实体(entity),实体完整性的作用即是标识每一行数据不重复、实体唯一。
3.1 主键约束
PRIMARY KEY 唯一,标识表中的一行数据,此列的值不可重复,且不能为 NULL
#为表中适用主键的列添加主键约束
CREATE TABLE subject(
subjectId INT PRIMARY KEY,#课程编号标识每一个课程的编号唯一,且不能为 NULL
subjectName VARCHAR(20),
subjectHours INT
)charset=utf8;
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);#error 主键 1 已存在
3.2 外键约束
详解:FOREIGN KEY 引用外部表的某个列的值,新增数据时,约束此列的值必须是引用表中存在的值。
#创建专业表
CREATE TABLE Speciality(
id INT PRIMARY KEY AUTO_INCREMENT,
SpecialName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;
#创建课程表(课程表的SpecialId 引用专业表的 id)
CREATE TABLE subject(
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR(20) UNIQUE NOT NULL,
subjectHours INT DEFAULT 20,
specialId INT NOT NULL,
CONSTRAINT fk_subject_specialId FOREIGN KEY(specialId) REFERENCES Speciality(id) #引用专业表里的 id 作为外键,新增课程信息时,约束课程所属的专业。
)charset=utf8;
#专业表新增数据
INSERT INTO Speciality(SpecialName) VALUES('Java');
INSERT INTO Speciality(SpecialName) VALUES('C#');
#课程信息表添加数据
INSERT INTO subject(subjectName,subjectHours) VALUES('Java',30,1);#专业 id 为 1,引用的是专业表的 Java
INSERT INTO subject(subjectName,subjectHours) VALUES('C#MVC',10,2);#专业 id 为 2,引用的是专业表的 C#
3.2 自动增长列
AUTO_INCREMENT 自动增长,给主键数值列添加自动增长。从 1 开始,每次加 1。不能单独使用,和主键配合。
#为表中主键列添加自动增长,避免忘记主键 ID 序号
CREATE TABLE subject(
subjectId INT PRIMARY KEY AUTO_INCREMENT,#课程编号主键且自动增长,会从 1 开始根据添加数据的顺序依次加 1
subjectName VARCHAR(20) UNIQUE,
subjectHours INT
)charset=utf8;
INSERT INTO subject(subjectName,subjectHours) VALUES('Java',40);#课程编号自动从 1 增长
INSERT INTO subject(subjectName,subjectHours) VALUES('JavaScript',30);#第二条编号为 2
3.2 非空约束
NOT NULL 非空,此列必须有值。
#课程名称虽然添加了唯一约束,但是有 NULL 值存在的可能,要避免课程名称为NULL
CREATE TABLE subject(
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR(20) UNIQUE NOT NULL,
subjectHours INT
)charset=utf8;
INSERT INTO subject(subjectName,subjectHours) VALUES(NULL,40);#error,课程名称约束了非空
4、事务
4.1 事务的概念
事务:
- 所谓事务,他就是一个原子操作,这些操作要么全部执行,要么就不执行,它是一个不可分割的部分。
- 可以有一条SQL语句组成,也可以有多条SQL语句组成;当所有的SQL语句执行成功时,则整个事务执行成功,但是只要有一条SQL执行失败,则整个事务都执行失败。
4.2 事务的边界
4.3 事务的原理
数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段),一个事务中所有的增删改语句的执行结果都会缓存在回滚段中,只有当事务中所有SQL 语句均正常结束(commit),才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败,整个事务将回滚(rollback)。
4.4 事务的四大特性(简称ACID)
1.原子性(Atomicity)
- 表示一个事务内的全部操作,要么全部成功,要么全部失败,不可分割
2.一致性(Consistency)
- 表示事务内有一个操作失败时,所有被更改过的数据都必须回滚(rollback)到没有修改前的状态
3.隔离性(Isolation)
- 事务查看数据操作时数据所处的状态,要么是另一个并发事务修改它之前的状态,要么是修改后的状态,事务不会查看也不能查看中间状态的数据,只能查看rollbalck(回滚)后或者commit(提交)后的数据。简单点说,就是一个事务所做的修改在最终提交以前,对其他事务是不可见的。
4.持久性(Durability)
- 事务执行成功后,对系统的影响是永久性的。一旦事务提交,则其所做的修改会永久保存到数据库。(此时即使系统崩溃,修改的数据也不会丢失。)
4.5 事务应用
应用环境:基于增删改语句的操作结果(均返回操作后受影响的行数),可通过程序逻辑手动控制事务提交或回滚
案例演示:
11.6.1 事务完成转账
#A 账户给 B 账户转账。
#1.开启事务
START TRANSACTION;|setAutoCommit=0;#禁止自动提交 setAutoCommit=1;#开启自动提交
#2.事务内数据操作语句
UPDATE ACCOUNT SET MONEY = MONEY-1000 WHERE ID = 1;
UPDATE ACCOUNT SET MONEY = MONEY+1000 WHERE ID = 2;
#3.事务内语句都成功了,执行 COMMIT;
COMMIT;
#4.事务内如果出现错误,执行 ROLLBACK;
ROLLBACK;
5、视图
5.1 概念
视图:
- 视图是一张虚拟的表,从一个或者多个表查询出来的数据形成的一个新的表,作用和真实的表一直,包含了一系列带有行和列的数据。
通俗的讲,视图就是一条SELECT语句执行后返回的结果集。- 视图中,用户可以对表的数据进行insert、update和delete等操作,
视图是为了用户使用方便,以及保证数据库系统的数据安全。- 方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性;
5.2 视图特点
优点
- 简单化,数据所见即所得。
- 安全性,用户只能查询或修改他们所能见到得到的数据。
- 逻辑独立性,可以屏蔽真实表结构变化带来的影响。
缺点
- 性能相对较差,简单的查询也会变得稍显复杂。
- 修改不方便,特变是复杂的聚合视图基本无法修改。
5.3 视图的创建
语法:CREATE VIEW 视图名 AS select语句;
5.4 视图的修改
DROP VIEW 视图名
13.5.1 删除视图
#删除t_empInfo视图
DROP VIEW t_empInfo;
13.5.2 视图的作用
- 提高了数据的重用性,就像函数一样
- 对数据库进行重构,但是却不影响其他程序使用到的数据
- 提高了数据使用的安全性,可以针对不同的用户
- 让数据更加清晰
13.5.3 视图的注意事项
- 注意:
- 视图不会独立存储,原表发生变化时,视图也会发生变化,没有优化任何查询性能
- 如果视图包含以下结构中的一种,则视图无法更新
- 聚合函数的结果
- distinct 去重后的结果
- group by 分组之后的结果
- having 筛选之后的结果
- union 、union all之后的结果
6、存储过程
6.1 概念
MySQL 5.0 版本开始支持存储过程。
- 存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
- 存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
- 存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
6.2 存储过程的特点
- 能够完成较为复杂的判断和运算
- 可编程性强,灵活
- SQL代码可以重复使用
- 执行速度相对较快一些
- 减少网络之间的数据传输,节省开销
6.3 创建一个简单的存储过程
- 创建语法
create procedure 名称()
begin
.........
end
- 创建存储过程
create procedure testa()
begin
select * from users;
select * from orders;
end;
- 使用存储过程
call testa();
7、触发器
7.1 概念
- 触发器是一种特殊的存储过程,不同于存储过程,主要是通过事件的触发而执行,而不是主动调用来执行。存储过程则是需要主动调用其名字来执行。
- 触发器:trigger, 是指事先为某一张表绑定的一段代码,当表中的某一项数据发生变化时(增、删、改)的时候,系统自动触发代码而执行
7.2 作用
- 可以在写入数据之前,强制检查和转换数据类型(保证数据安全)
- 触发器发生错误时,在此之前被触发器触发执行的操作会被撤销,类似事务的回滚。
7.3 语法
delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
-- 触发器内容主体,每行用分号结尾
end
自定义的结束符合
delimiter ;
7.4 创建触发器
delimiter ##
-- 创建触发器
create trigger after_insert_order after insert on orders for each row
begin
-- 更新商品表的库存,这里只指定了更新第一件商品的库存
update goods set goods_num = goods_num - 1 where id = 1;
end
##
delimiter ;