笔记内容输出来源：拉勾教育Java大数据学科 训练营；
目录
1. DQL操作单表
1.1 创建数据库,复制表
1) 创建一个新的数据库 db2
CREATE DATABASE db2 CHARACTER SET utf8;
1.2 排序
SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC] (排序只是显示效果,不会影响真实数据
| 排序方式 | |
|---|---|
| 单列排序 | 只按照某一个字段进行排序, 就是单列排序
|
| 组合排序 | 同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推
|
1.3 聚合函数
语法结构
SELECT 聚合函数(字段名) FROM 表名;
| 聚合函数 | 作用 |
|---|---|
| count(字段) | 统计指定列不为NULL的记录行数 |
| sum(字段) | 计算指定列的数值和 |
| max(字段) | 计算指定列的最大值 |
| min(字段) | 计算指定列的最小值 |
| avg(字段) | 计算指定列的平均值 |
#1 查询员工的总数
-- 统计表中的记录条数 使用 count()
SELECT COUNT(eid) FROM emp; -- 使用某一个字段
SELECT COUNT(*) FROM emp; -- 使用 *
SELECT COUNT(1) FROM emp; -- 使用 1,与 * 效果一样
-- 下面这条SQL 得到的总条数不准确,因为count函数忽略了空值
-- 所以使用时注意不要使用带有null的列进行统计
SELECT COUNT(dept_name) FROM emp;
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
-- sum函数求和, max函数求最大, min函数求最小, avg函数求平均值
SELECT
SUM(salary) AS '总薪水',
MAX(salary) AS '最高薪水',
MIN(salary) AS '最低薪水',
AVG(salary) AS '平均薪水'
FROM emp;
#3 查询薪水大于4000员工的个数
SELECT COUNT(*) FROM emp WHERE salary > 4000;
#4 查询部门为'教学部'的所有员工的个数
SELECT COUNT(*) FROM emp WHERE dept_name = '教学部';
#5 查询部门为'市场部'所有员工的平均薪水
SELECT
AVG(salary) AS '市场部平均薪资'
FROM emp
WHERE dept_name = '市场部';1.4 分组
分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组
语法格式
SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
#1. 查询有几个部门
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;
#2.查询每个部门的平均薪资
SELECT
dept_name AS '部门名称',
AVG(salary) AS '平均薪资'
FROM emp GROUP BY dept_name;#3.查询每个部门的平均薪资, 部门名称不能为null
SELECT
dept_name AS '部门名称',
AVG(salary) AS '平均薪资'
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;
# 查询平均薪资大于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的区别
| 过滤方式 | 特点 |
|---|---|
| where | where 进行分组前的过滤 where 后面不能写 聚合函数 |
| having | having 是分组后的过滤 having 后面可以写 聚合函数 |
1.5 limit关键字
limit 关键字的作用
- limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
- limit 语法是 MySql的方言,用来完成分页
语法结构
SELECT 字段1,字段2... FROM 表名 LIMIT offset , 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中的方言
2. SQL约束
1) 约束的作用:
对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中
2) 常见的约束
| 约束名 | 约束关键字 |
|---|---|
| 主键 | primary key |
| 唯一 | unique |
| 非空 | not null |
| 外键 | foreign key |
2.1 主键约束 primary key
- 特点 不可重复 唯一 非空
- 作用 用来表示数据库中的每一条记录
3.1.1 添加主键约束
语法格式
字段名 字段类型 primary key
1) 需求: 创建一个带主键的表
# 方式1 创建一个带主键的表
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)
2) 测试主键的唯一性 非空性
# 正常插入一条数据
INSERT INTO emp2 VALUES(1,'宋江','男');
# 插入一条数据,主键为空
-- Column 'eid' cannot be null 主键不能为空
INSERT INTO emp2 VALUES(NULL,'李逵','男');
# 插入一条数据,主键为 1
-- Duplicate entry '1' for key 'PRIMARY' 主键不能重复
INSERT INTO emp2 VALUES(1,'孙二娘','女');
3) 哪些字段可以作为主键 ?
- 通常针对业务去设计主键,每张表都设计一个主键id
- 主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复 就好,比如 身份证就可以作为主键
3.1.2 删除主键约束
-- 使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;
DESC emp2;3.1.3 主键的自增
注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段 的值
关键字: AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
-- 创建主键自增的表
CREATE TABLE emp2(
-- 关键字 AUTO_INCREMENT,主键类型必须是整数类型
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);
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对自增长的影响
| 清空表数据的方式 | 特点 |
|---|---|
| DELETE | 只是删除表中所有数据,对自增没有影响 |
| TRUNCATE | truncate 是将整个表删除掉,然后创建一个新的表 自增的主键,重新从 1开始 |
2.2 非空约束
非空约束的特点: 某一列不予许为空
字段名 字段类型 not null
# 非空约束
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 添加非空约束, ename字段不能为空
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
);
2.3 唯一约束
唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )
-- 测试唯一约束 添加一条数据
INSERT INTO emp3 (ename,sex) VALUES('张百万','男');
-- 添加一条 ename重复的 数据
-- Duplicate entry '张百万' for key 'ename' ename不能重复
INSERT INTO emp3 (ename,sex) VALUES('张百万','女');
2.4 外键约束
FOREIGN KEY 表示外键约束，将在多表中学习。
2.5 默认值
字段名 字段类型 DEFAULT 默认值
-- 创建带有默认值的表
CREATE TABLE emp4(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 为ename 字段添加默认值
ename VARCHAR(20) DEFAULT '奥利给',
sex CHAR(1)
);3.数据库事务
3.1 什么是事务
事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有 一条SQL出现异常,整个操作就会回滚,整个业务执行失败
3.3 MySQL事务操作
- 手动提交事务
- 自动提交事务
3.3.1 手动提交事务
| 功能 | 语句 |
|---|---|
| 开启事务 | start transaction; 或者 BEGIN; |
| 提交事务 | commit; |
| 回滚事务 | rollback; |
- START TRANSACTION 这个语句显式地标记一个事务的起始点。
- COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写 到磁盘上的物理数据库中，事务正常结束。
- ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中 对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态
3.3.1.2 手动提交事务流程
- 执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务 执行失败的情况：
start transaction;
update account set money = money - 500 where name = 'tom'
update account set money = money + 500 where name = 'jack';
commit;开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
start transaction;
INSERT INTO account VALUES(NULL,'张百万',3000);
INSERT INTO account VALUES(NULL,'有财',3500);
-- 不去提交事务 直接关闭窗口,发生回滚操作,数据没有改变3.3.2 自动提交事务
MySQL默认是自动提交事务
3.3.2.2 取消自动提交
MySQL默认是自动提交事务,设置为手动提交
SHOW VARIABLES LIKE 'autocommit';
on ：自动提交 off : 手动提交
2) 把 autocommit 改成 of
、SET @@autocommit=off;
-- 选择数据库
use db2;
-- 修改数据
update account set money = money - 500 where name = 'jack';
-- 手动提交
commit;3.4 事务的四大特性 ACID
| 特 性 | 含义 |
|---|---|
| 原 子 性 | 每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都 失败。 |
| 一 致 性 | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的 总金额 是 2000，转账后 2 个人总金额也是 2000. |
| 隔 离 性 | 事务与事务之间不应该相互影响，执行时保持隔离的状态. |
| 持 久 性 | 一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的. |
3.5 MySQL 事务隔离级别(了解
3.5.1 数据并发访问
一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能 被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性
3.5.2 并发访问会产生的问题
事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问 同一个 数据。可能引发并发访问的问题
| 并发访问 的问题 | 说明 |
|---|---|
| 脏读 | 一个事务读取到了另一个事务中尚未提交的数据 |
| 不可重复 读 | 一个事务中两次读取的数据内容不一致, 要求的是在一个事务中多次读取时数据是一 致的. 这是进行 update 操作时引发的问题 |
| 幻读 | 一个事务中,某一次的 select 操作得到的结果所表征的数据状态, 无法支撑后续的业务 操作. 查询得到的数据状态不准确,导致幻读. |
3.5.3 四种隔离级别
| 级 别 | 名字 | 隔离级别 | 脏 读 | 不可重复 读 | 幻 读 | 数据库的默认隔离级 别 |
|---|---|---|---|---|---|---|
| 1 | 读未提 交 | read uncommitted | v | v | v | |
| 2 | 读已提 交 | read committed | x | v | v | Oracle和SQLServer |
| 3 | 可重复 读 | repeatable read | x | x | v | MySql |
| 4 | 串行化 | serializable | x | x | x |
3.5.4 隔离级别相关命令
1) 查看隔离级别
select @@tx_isolation;
2) 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化
例如: 修改隔离级别为 读未提交
set global transaction isolation level read uncommitted
笔记内容输出来源：拉勾教育Java大数据学科 训练营；
扫一扫
1769
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
