DQL操作单表

创建数据库,复制表

创建一个新的数据库 db2

CREATE DATABASE db2 CHARACTER SET utf8;

将db1数据库中的emp表复制到当前db2数据库

排序

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

语法结构

SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]

ASC表示升序排序(默认)
DESC表示降序排序

排序方式

单列排序

只按照某一个字段进行排序，就是单列排序需求1：
使用salary字段，对emp表数据进行排序(升序/降序)

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

-- 降序排序
SELECT * FROM emp ORDER BY salary DESC;

组合排序

同时对多个字段进行排序，如果第一个字段相同就按照第二个字段进行排序，以此类推需求2：
在薪水排序的基础上，再使用id进行排序，如果薪水相同就以id做降序排序

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

聚合函数

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

语法结构 SELECT 聚合函数(字段名) FROM 表名;

聚合函数	作用
count(字段)	统计指定列不为NULL的记录行数
sum(字段)	计算指定列的数值和
max(字段)	计算指定列的最大值
min(字段)	计算指定列的最小值
avg(字段)	计算指定列的平均值

需求1：

#1 查询员工的总数 
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值 
#3 查询薪水大于4000员工的个数 
#4 查询部门为'教学部'的所有员工的个数 
#5 查询部门为'市场部'所有员工的平均薪水

SQL实现

#1 查询员工的总数
SELECT COUNT(*) FROM emp; -- 使用*
SELECT COUNT(1) FROM emp; -- 使用1与*效果一样
SELECT COUNT(eid) FROM emp; -- 使用某一个字段

-- 下面这条SQL 得到的总条数不准确,因为count函数忽略了空值
-- 所以使用时注意不要使用带有null的列进行统计
SELECT COUNT(dept_name) FROM emp;

#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
-- sum函数求和, max函数求最大, min函数求最小, avg函数求平均值
SELECT 
    SUM(salary) '总薪水', 
    MAX(salary) '最高薪水', 
    MIN(salary) '最低薪水', 
    AVG(salary) '平均薪水' 
FROM emp;

#3 查询薪水大于4000员工的个数
SELECT COUNT(*) FROM emp WHERE salary > 4000;

#4 查询部门为'教学部'的所有员工的个数
SELECT COUNT(*) FROM emp WHERE dept_name = '教学部';

#5 查询部门为'市场部'所有员工的平均薪水
SELECT 
    AVG(salary) '市场部平均薪资'
FROM emp 
WHERE dept_name = '市场部';

分组

分组查询指的是使用 GROUP BY 语句，对查询的信息进行分组，相同数据作为一组
语法格式
SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
需求1：通过性别字段进行分组

-- 按照性别进行分组操作
SELECT * FROM emp GROUP BY sex; -- 注意 这样写没有意义

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

需求：通过性别字段进行分组，求各组的平均薪资

SELECT sex, AVG(salary) FROM emp GROUP BY sex;

需求2：

#1.查询所有部门信息 
#2.查询每个部门的平均薪资 
#3.查询每个部门的平均薪资, 部门名称不能为null

SQL实现

#1.查询有几个部门
SELECT dept_name '部门名称' FROM emp GROUP BY dept_name;

#2.查询每个部门的平均薪资
SELECT 
    dept_name '部门名称',
    AVG(salary) '平均薪资'
FROM emp GROUP BY dept_name;

#3.查询每个部门的平均薪资, 部门名称不能为null
SELECT 
    dept_name '部门名称',
    AVG(salary) '平均薪资'
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;

需求3：

# 查询平均薪资大于6000的部门.

分析：

需要在分组后，对数据进行过滤，使用关键字hiving
分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

SQL实现：

# 查询平均薪资大于6000的部门
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后面可以写聚合函数

limit关键字

limit是限制的意思，用于限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
limit语法是MySql的方言，用来完成分页
语法结构

SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;

limit offset , length; 关键字可以接受一个或者两个为0或者正整数的参数
offset起始行数, 从0开始记数，如果省略则默认为0。
length返回的行数
需求1：

# 查询emp表中的前5条数据 
# 查询emp表中从第4条开始,查询6条

SQL实现

# 查询emp表中的前5条数据
-- 参数1起始值,默认是0,参数2要查询的条数
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0, 5;

# 查询emp表中从第4条开始,查询6条
-- 起始值默认是从0开始的
SELECT * FROM emp LIMIT 3, 6;

需求2：分页操作每页显示3条数据

-- 分页操作每页显示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) * 每页条数

SQL约束

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

2) 常见的约束

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key

主键约束

特点
不可重复、唯一、非空
作用
用来表示数据库中的每一条记录

添加主键约束

语法格式 字段名字段类型 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);

-- 查看表的详细信息
DESC emp2;

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
主键是给数据库和程序使用的，跟最终的客户无关，所以主键没有意义没有关系，只要能够保证不重复就好，比如身份证就可以作为主键。

删除主键约束

删除表中的主键约束

-- 使用DDL语句删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;
DESC emp2;

主键的自增

注：主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值。
关键字： AUTO_INCREMENT表示自动增长(字段类型必须是整数类型) 1) 创建主键自增的表

-- 创建主键自增的表
CREATE TABLE emp2(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)
);

2) 添加数据观察主键的自增

INSERT INTO emp2(ename,sex) VALUES('张三','男'); 
INSERT INTO emp2(ename,sex) VALUES('李四','男'); 
INSERT INTO emp2 VALUES(NULL,'翠花','女'); 
INSERT INTO emp2 VALUES(NULL,'艳秋','女');

修改主键自增的起始值

默认地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('艳秋','女');

DELETE和TRUNCATE对自增长的影响

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

清空表数据的方式	特点
DELETE	只是删除表中所有数据，对自增没有影响
TRUNCATE	truncate 是将整个表删除掉，然后创建一个新的表自增的主键，重新从1开始

测试1：delete删除表中所有数据

-- 目前最后的主键值是 101
SELECT * FROM emp2;

-- delete 删除表中数据,对自增没有影响
DELETE FROM emp2;

-- 插入数据 查看主键 
INSERT INTO emp2(ename,sex) VALUES('张百万','男'); 
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');

测试2：truncate删除表中数据

-- 使用 truncate 删除表中所有数据
TRUNCATE TABLE emp2;

-- 插入数据 查看主键
INSERT INTO emp2(ename,sex) VALUES('张百万','男'); 
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');

非空约束

非空约束的特点：某一列不予许为空
语法格式 字段名字段类型 not null
需求1：为ename字段添加非空约束

# 非空约束
CREATE TABLE emp2(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    -- 添加非空约束, ename字段不能为空
    ename VARCHAR(20) NOT NULL,
    sex CHAR(1)
);

唯一约束

唯一约束的特点：表中的某一列的值不能重复(对null不做唯一的判断)
语法格式 字段名字段值 unique

1) 添加唯一约束

#创建emp3表为ename字段添加唯一约束
CREATE TABLE emp3(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20) UNIQUE,
     sex CHAR(1)
);

2) 测试唯一约束

-- 测试唯一约束 添加一条数据 
INSERT INTO emp3 (ename,sex) VALUES('张百万','男'); 

-- 添加一条 ename重复的 数据 -- Duplicate entry '张百万' for key 'ename' ename不能重复 
INSERT INTO emp3 (ename,sex) VALUES('张百万','女');

主键约束与唯一约束的区别:
1. 主键约束，唯一且不能够为空
2. 唯一约束，唯一但是可以为空
3. 一个表中只能有一个主键，但是可以有多个唯一约束

外键约束

FOREIGN KEY表示外键约束

默认值

默认值约束用来指定某列的默认值
语法格式 字段名字段类型 DEFAULT 默认值

1) 创建emp4表, 性别字段默认女

-- 创建带有默认值的表
CREATE TABLE emp4(
    eid INT PRIMARY KEY AUTO_INCREMENT, 
    -- 为ename 字段添加默认值
    ename VARCHAR(20) DEFAULT '奥利给',
    sex CHAR(1)
);

2) 测试添加数据使用默认值

-- 添加数据 使用默认值 
INSERT INTO emp4(ename,sex) VALUES(DEFAULT,'男'); 
INSERT INTO emp4(sex) VALUES('女'); 

-- 不使用默认值 
INSERT INTO emp4(ename,sex) VALUES('艳秋','女');

数据库事务

什么是事务

事务是一个整体，由一条或者多条SQL语句组成，这些SQL语句要么都执行成功，要么都执行失败，只要有一条SQL出现异常，整个操作就会回滚，整个业务执行失败
比如：银行的转账业务，张三给李四转账500元，至少要操作两次数据库，张三-500，李四+500，这中间任何一步出现问题，整个操作就必须全部回滚，这样才能保证用户和银行都没有损失。
回滚
即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

模拟转账操作

1) 创建账户表

-- 创建账户表
CREATE TABLE account(
    -- 主键
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- 姓名
    name VARCHAR(10),
    -- 余额
    money DOUBLE
);

-- 添加两个用户 
INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000);

2) 模拟tom给jack转500元钱，一个转账的业务操作最少要执行下面的2条语句：

-- tom账户 -500元
UPDATE account SET money = money - 500 WHERE name = 'tom';

-- jack账户 +500元
UPDATE account SET money = money + 500 WHERE name = 'jack';

注:

假设当tom账号上-500元，服务器崩溃了。jack的账号并没有+500元，数据就出现问题了。我们要保证整个事务执行的完整性，要么都成功，要么都失败。这个时候我们就要学习如何操作事务。

MySQL事务操作

MYSQL中可以有两种方式进行事务的操作：
- 手动提交事务
- 自动提交事务

手动提交事务

语法格式

功能	语句
开启事务	start transaction; 或者 BEGIN;
提交事务	commit;
回滚事务	rollback;

START TRANSACTION
- 这个语句显式地标记一个事务的起始点。
COMMIT
- 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
ROLLBACK
- 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态

手动提交事务流程

执行成功的情况：开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
执行失败的情况：开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

成功案例演示

模拟tom给jack转500元钱

-- 开启事务
START TRANSACTION;

--  tom账户-500
UPDATE account SET money = money - 500 WHERE name = 'tom';

-- jack账户+500
UPDATE account SET money = money + 500 WHERE name = 'jack';

-- 提交事务
COMMIT;

事务回滚演示

如果事务中，有某条sql语句执行时报错了，我们没有手动的commit，那整个事务会自动回滚

-- 开启事务
START TRANSACTION;

-- 插入两条数据
INSERT INTO account VALUES(NULL,'张百万',3000); 
INSERT INTO account VALUES(NULL,'有财',3500);

-- 如果事务中SQL语句没有问题，commit提交事务，会对数据库数据的数据进行改变。如果事务中SQL语句有问题，rollback回滚事务，会回退到开启事务时的状态。

自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL默认开始自动提交事务
MySQL默认是自动提交事务

自动提交事务演示

将tom账户金额+500元

UPDATE account SET money = money + 500 WHERE name = 'tom';

取消自动提交

MySQL默认是自动提交事务,设置为手动提交

SHOW VARIABLES LIKE 'autocommit';

ON：自动提交
OFF：手动提交

-- 把autocommit改成off
SET @@autocommit=off;

将jack账户金额-500元

-- 修改数据
UPDATE account SET money = money - 500 WHERE name = 'jack';

-- 手动提交
COMMIT;

事务的四大特性ACID

特性	含义
原子性	每个事务都是一个整体，不可再拆分，事务中所有的SQL语句要么都执行成功，要么都失败。
一致性	事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的总金额是2000，转账后2个人总金额也是2000
隔离性	事务与事务之间不应该相互影响，执行时保持隔离的状态
持久性	一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的

MySQL事务隔离级别

数据并发访问

一个数据库可能拥有多个访问客户端，这些客户端都可以并发方式访问数据库。数据库的相同数据可能被多个事务同时访问，如果不采取隔离措施，就会导致各种问题，破坏数据的完整性

并发访问会产生的问题

事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题

并发访问的问题	说明
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是在一个事务中多次读取时数据是一致的。这是进行update操作时引发的问题
幻读	一个事务中，某一次的select操作得到的结果所表征的数据状态，无法支撑后续的业务操作。查询得到的数据状态不准确，导致幻读

四种隔离级别

通过设置隔离级别，可以防止上面的三种并发问题。
MySQL数据库有四种隔离级别上面的级别最低，下面的级别最高。

✔ 会出现问题
✘ 不会出现问题

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库的默认隔离级别
1	读未提交	read uncommitted	✔	✔	✔
2	读已提交	read committed	✘	✔	✔	Oracle和SQLServer
3	可重复读	repeatable read	✘	✘	✔	MySql
4	串行化	serializable	✘	✘	✘

隔离级别相关命令

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;

MySQL数据库-MySql单表&约束&事务