网站接单,济南自助建站软件,那个网站学做披萨比较好,企业展厅设计公司的区别在哪MySQL 单表 约束 事务 1. DQL操作单表 1.1 创建数据库,复制表 1) 创建一个新的数据库 db2 CREATE DATABASE db2 CHARACTER SET utf8; 2) 将 db1 数据库中的 emp 表 复制到当前 db2 数据库 1.2 排序 通过 ORDER BY 子句 , 可以将查询出的结果进行排序 ( 排序只… MySQL 单表 约束 事务 1. DQL操作单表 1.1 创建数据库,复制表 1) 创建一个新的数据库 db2 CREATE DATABASE db2 CHARACTER SET utf8; 2) 将 db1 数据库中的 emp 表 复制到当前 db2 数据库 1.2 排序 通过 ORDER BY 子句 , 可以将查询出的结果进行排序 ( 排序只是显示效果 , 不会影响真实数据 ) 语法结构: 1.2.1 排序方式 1) 单列排序 只按照某一个字段进行排序 , 就是单列排序 需求 1: 使用 salary 字段 , 对 emp 表数据进行排序 ( 升序 / 降序 ) -- 默认升序排序 ASC
SELECT * FROM emp ORDER BY salary;
-- 降序排序
SELECT * FROM emp ORDER BY salary DESC; 2) 组合排序 同时对多个字段进行排序 , 如果第一个字段相同 就按照第二个字段进行排序 , 以此类推 需求 2: 在薪水排序的基础上 , 再使用 id 进行排序 , 如果薪水相同就以 id 做降序排序 -- 组合排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC; 1.3 聚合函数 之前我们做的查询都是横向查询它们都是根据条件一行一行的进行判断而使用聚合函数查询是纵向查询它是对某一列的值进行计算然后返回一个单一的值( 另外聚合函数会忽略 null 空值。 ) 语法结构: 我们来学习 5 个聚合函数 需求 1: SQL 实现 #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 语句 , 对查询的信息进行分组 , 相同数据作为一组 语法格式: 需求 1: 通过性别字段 进行分组 -- 按照性别进行分组操作
SELECT * FROM emp GROUP BY sex; -- 注意 这样写没有意义 分析 : GROUP BY 分组过程 注意 : 分组时可以查询要分组的字段 , 或者使用聚合函数进行统计操作 . * 查询其他字段没有意义 需求 : 通过性别字段 进行分组 , 求各组的平均薪资 SELECT sex, AVG(salary) FROM emp GROUP BY sex; 需求 2: SQL 实现 #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; 需求 3: 分析 : 1) 需要在分组后 , 对数据进行过滤 , 使用 关键字 hiving 2) 分组操作中的 having 子语句是用于在分组后对数据进行过滤的作用类似于 where 条件。 SQL 实现 : # 查询平均薪资大于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 的区别 分组小总结 首先我再给大家去回顾一下我们分组操作呢。首先我们分组一定要去做统计的操作才有意义然后呢我们要写上我们的这个分组的一个字段给它加上好吧嗯然后再就说我们这个分组之后如果你想进行条件判断的话那你就要加上having。好吧不能使用where啊它们两个区别大家在这里一定要把它记住。 1.5 limit关键字 limit 关键字的作用 1 limit 是限制的意思 , 用于 限制返回的查询结果的行数 ( 可以通过 limit 指定查询多少行数据 ) 2 limit 语法是 MySql 的方言 , 用来完成分页 语法结构: 参数说明 需求 1: 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 条数据 SQL 实现 -- 分页操作 每页显示3条数据
SELECT * FROM emp LIMIT 0,3; -- 第1页
SELECT * FROM emp LIMIT 3,3; -- 第2页 2-11 1*33
SELECT * FROM emp LIMIT 6,3; -- 第三页
-- 分页公式 起始索引 (当前页 - 1) * 每页条数
-- limit是MySql中的方言 2. SQL约束 1) 约束的作用 : 对表中的数据进行进一步的限制从而保证数据的正确性、有效性、完整性 . 违反约束的不正确数据 , 将无法插入到表中 2) 常见的约束 2.1 主键约束 2.1.1 添加主键约束 语法格式 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 查看表结构 -- 查看表的详细信息
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 主键是给数据库和程序使用的 , 跟最终的客户无关 , 所以主键没有意义没有关系 , 只要能够保证不重复 就好 , 比如 身份证就可以作为主键 . 2.1.2 删除主键约束 删除 表中的主键约束 ( 了解 ) -- 使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;
DESC emp2; 2.1.3 主键的自增 注 : 主键如果让我们自己添加很有可能重复 , 我们通常希望在每次插入新记录时 , 数据库自动生成主键字段 的值 . 1) 创建主键自增的表 -- 创建主键自增的表
CREATE TABLE emp2(
-- 关键字 AUTO_INCREMENT,主键类型必须是整数类型
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,艳秋,女); 2.1.4 修改主键自增的起始值 默认地 AUTO_INCREMENT 的开始值是 1 如果希望修改起始值 , 请使用下面的方式 -- 创建主键自增的表,自定义自增其实值
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT100;
-- 插入数据,观察主键的起始值
INSERT INTO emp2(ename,sex) VALUES(张百万,男);
INSERT INTO emp2(ename,sex) VALUES(艳秋,女); 2.1.5 DELETE和TRUNCATE对自增长的影响 删除表中所有数据有两种方式 测试 1: delete 删除表中所有数据 -- 目前最后的主键值是 101
SELECT * FROM emp2;
-- delete 删除表中数据,对自增没有影响
DELETE FROM emp2;
-- 插入数据 查看主键
INSERT INTO emp2(ename,sex) VALUES(张百万,男);
INSERT INTO emp2(ename,sex) VALUES(艳秋,女); 测试 2: truncate 删除 表中数据 -- 插入数据 查看主键
INSERT INTO emp2(ename,sex) VALUES(张百万,男);
INSERT INTO emp2(ename,sex) VALUES(艳秋,女); 2.2 非空约束 非空约束的特点 : 某一列不予许为空 语法格式 需求 1: 为 ename 字段添加非空约束 # 非空约束
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 添加非空约束, ename字段不能为空
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
); 2.3 唯一约束 唯一约束的特点 : 表中的某一列的值不能重复 ( 对 null 不做唯一的判断 ) 语法格式 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(张百万,女); 2.4 外键约束 FOREIGN KEY 表示外键约束将在多表中学习。 2.5 默认值 默认值约束 用来指定某列的默认值 语法格式 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(艳秋,女); 3.数据库事务 3.1 什么是事务 事务是一个整体 , 由一条或者多条 SQL 语句组成 , 这些 SQL 语句要么都执行成功 , 要么都执行失败 , 只要有一条SQL 出现异常 , 整个操作就会回滚 , 整个业务执行失败 回滚 即在事务运行的过程中发生了某种故障事务不能继续执行系统将事务中对数据库的所有已完成 的操作全部撤销滚回到事务开始时的状态。在提交之前执行 3.2 模拟转账操作 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 元数据就出现问题了。 我们要保证整个事务执行的完整性 , 要么都成功 , 要么都失败 . 这个时候我们就要学习如何操作事 务 . 3.3 MySQL事务操作 MYSQL 中可以有两种方式进行事务的操作 手动提交事务 自动提交事务 3.3.1 手动提交事务 3.3.1.1 语法格式 START TRANSACTION 这个语句显式地标记一个事务的起始点。 COMMIT 表示提交事务即提交事务的所有操作具体地说就是将事务中所有对数据库的更新都写 到磁盘上的物理数据库中事务正常结束。 ROLLBACK 表示撤销事务即在事务运行的过程中发生了某种故障事务不能继续执行系统将事务中 对数据库的所有已完成的操作全部撤销回滚到事务开始时的状态 3.3.1.2 手动提交事务流程 执行成功的情况 开启事务 - 执行多条 SQL 语句 - 成功提交事务 执行失败的情况 开启事务 - 执行多条 SQL 语句 - 事务的回滚 3.3.1.3 成功案例 演示 模拟张三给李四转 500 元钱 1) 命令行登录数据库 2) 使用 db2 数据库 USE db2; 3) 执行以下 SQL 3.1 开启事务 start transaction; 3.2 tom 账户 -500 update account set money money - 500 where name tom 3.3 jack 账户 500 update account set money money 500 where name jack; 4) 此时我们使用 sqlYog 查看表 , 发现数据并没有改变 5) 在控制台执行 commit 提交事务 commit; 6) 再次使用 sqlYog 查看 , 发现数据在事务提交之后 , 发生改变 3.3.1.4 事务回滚演示 如果事务中有某条 sql 语句执行时报错了我们没有手动的 commit 那整个事务会自动回滚 1) 命令行 开启事务 start transaction; 2) 插入两条数据 INSERT INTO account VALUES(NULL,张百万,3000);
INSERT INTO account VALUES(NULL,有财,3500); 3) 不去提交事务 直接关闭窗口 , 发生回滚操作 , 数据没有改变 注 : 如果事务中 SQL 语句没有问题 commit 提交事务会对数据库数据的数据进行改变。 如果事务 中 SQL 语句有问题 rollback 回滚事务会回退到开启事务时的状态。 3.3.2 自动提交事务 MySQL 默认每一条 DML( 增删改 ) 语句都是一个单独的事务每条语句都会自动开启一个事务语句执行完毕 自动提交事务MySQL 默认开始自动提交事务 MySQL 默认是自动提交事务 3.3.2.1 自动提交事务演示 1) 将 tom 账户金额 500 元 2) 使用 SQLYog 查看数据库发现数据已经改变 3.3.2.2 取消自动提交 MySQL 默认是自动提交事务 , 设置为手动提交 . 1) 登录 mysql 查看 autocommit 状态。 SHOW VARIABLES LIKE autocommit; 2) 把 autocommit 改成 off; SET autocommitoff; 3) 再次修改 , 需要提交之后才生效 将 jack 账户金额 -500 元 -- 选择数据库
use db2;
-- 修改数据
update account set money money - 500 where name jack;
-- 手动提交
commit; 3.4 事务的四大特性 ACID 3.5 MySQL 事务隔离级别(了解) 3.5.1 数据并发访问 一个数据库可能拥有多个访问客户端 , 这些客户端都可以并发方式访问数据库 . 数据库的相同数据可能被多个事务同时访问, 如果不采取隔离措施 , 就会导致各种问题 , 破坏数据的完整性 3.5.2 并发访问会产生的问题 事务在操作时的理想状态 所有的事务之间保持隔离互不影响。因为并发操作多个用户同时访问同一个 数据。可能引发并发访问的问题 3.5.3 四种隔离级别 通过设置隔离级别 , 可以防止上面的三种并发问题 . MySQL 数据库有四种隔离级别 上面的级别最低下面的级别最高。 ✔ 会出现问题 ✘ 不会出现问题 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; 3.6 隔离性问题演示 3.6.1 脏读演示 脏读 : 一个事务读取到了另一个事务中尚未提交的数据 1. 打开窗口登录 MySQL 设置全局的隔离级别为最低 1) 登录是 MySQL 2) 使用 db2 数据库 use db2; 3) 设置隔离级别为最低 读未提交 set global transaction isolation level read uncommitted; 2. 关闭窗口 , 开一个新的窗口 A , 再次查询隔离级别 1) 开启新的 窗口 A 2) 查询隔离级别 select tx_isolation; 3. 再开启一个新的窗口 B 1) 登录数据库 2) 选择数据库 use db2; 3) 开启事务 start transaction; 4) 查询 select * from account; 1. A 窗口执行 1) 选择数据库 use db2; 2) 开启事务 start transaction; 3) 执行修改操作 -- tom账户 -500元
UPDATE account SET money money - 500 WHERE NAME tom;
-- jack账户 500元
UPDATE account SET money money 500 WHERE NAME jack; 2. B 窗口查询数据 1) 查询账户信息 select * from account; 3. A 窗口转账异常 , 进行回滚 rollback; 4. B 窗口再次查询 账户 select * from account; 3.6.2 解决脏读问题 脏读非常危险的比如张三向李四购买商品张三开启事务向李四账号转入 500 块然后打电 话给李四说钱 已经转了。李四一查询钱到账了发货给张三。张三收到货后回滚事务李四的再 查看钱没了。 解决方案 将全局的隔离级别进行提升为 : read committed 1. 在 A 窗口设置全局的隔离级别为 read committed set global transaction isolation level read committed; 2. 重新开启 A 窗口 , 查看设置是否成功 select tx_isolation; 3. 开启 B 窗口 , A 和 B 窗口选择数据库后 , 都开启事务 4. A 窗口 只是更新两个人的账户 , 不提交事务 -- tom账户 -500元
UPDATE account SET money money - 500 WHERE NAME tom;
-- jack账户 500元
UPDATE account SET money money 500 WHERE NAME jack; 5. B 窗口进行查询 , 没有查询到未提交的数据 mysqlselect * from account; 6. A 窗口 commit 提交数据 commit; 7. B 窗口查看数据 select * from account; 3.6.3 不可重复读演示 不可重复读 : 同一个事务中 , 进行查询操作 , 但是每次读取的数据内容是不一样的 1. 恢复数据 ( 把数据改回初始状态 ) 2. 打开两个 窗口 A 和 窗口 B, 选择数据库后 开启事务 use db2;
start transaction; 1. B 窗口开启事务后 , 先进行一次数据查询 select * from account; 2. 在 A 窗口开启事务后将用户 tom 的账户 500 , 然后提交事务 -- 修改数据
update account set money money 500 where name tom;
-- 提交事务
commit; 1. B 窗口再次查询数据 两次查询输出的结果不同到底哪次是对的 不知道以哪次为准。 很多人认为这种情况就对了无须困惑 当然是后面的为准。 我们可以考虑这样一种情况 : 比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客 户结果在一个事务 中针对不同的输出目的地进行的两次查询不一致导致文件和屏幕中的结果不一致银 行工作 人员就不知道以哪个为准了 3.6.4 解决不可重复读问题 将全局的隔离级别进行提升为 repeatable read 1. 恢复数据 UPDATE account SET money 1000 2. 打开 A 窗口 , 设置隔离级别为 repeatable read -- 查看事务隔离级别
select tx_isolation;
-- 设置事务隔离级别为 repeatable read
set global transaction isolation level repeatable read; 3. 重新开启 A,B 窗口 选择数据库 , 同时开启事务 4. B 窗口事务 先进行第一次查询 select * from account; 5. A 窗口更新数据 , 然后提交事务 -- 修改数据
update account set money money 500 where name tom;
-- 提交事务
commit; 6. B 窗口 再次查询 select * from account; 同一个事务中为了保证多次查询数据一致必须使用 repeatable read 隔离级别 3.6.5 幻读演示 幻读 : select 某记录是否存在不存在准备插入此记录但执行 insert 时发现此记录已存在 无法插入此时就发生了幻读。 1. 打开 A B 窗口 , 选择数据库 开启事务 2. A 窗口 先执行一次查询操作 -- 假设要再添加一条id为3的 数据,在添加之前先判断是否存在
select * from account where id 3; 3. B 窗口 插入一条数据 提交事务 INSERT INTO account VALUES(3,lucy,1000);
commit; 4. A 窗口执行 插入操作 , 发现报错 . 出现幻读 见鬼了我刚才读到的结果应该可以支持我这样操作才对啊为什么现在不可以 3.6.6 解决幻读问题 将事务隔离级别设置到最高 SERIALIZABLE 以挡住幻读的发生 如果一个事务使用了 SERIALIZABLE—— 可串行化隔离级别时在这个事务没有被提交之前 , 其 他的线程只能等到当前操作完成之后才能进行操作这样会非常耗时而且影响数据库的 性能数据库不会使用这种隔离级别 1. 恢复数据 DELETE FROM account WHERE id 3; 2. 打开 A 窗口 将数据隔离级别提升到最高 set global transaction isolation level SERIALIZABLE; 3. 打开 A B 窗口 , 选择数据库 开启事务 4. A 窗口 先执行一次查询操作 SELECT * FROM account WHERE id 3; 5. B 窗口插入一条数据 INSERT INTO account VALUES(3,lucy,1000); 6. A 窗口执行 插入操作 , 提交事务 数据插入成功 . INSERT INTO account VALUES(3,lucy,1000);
commit; 7. B 窗口在 A 窗口提交事务之后 , 再执行 , 但是主键冲突出现错误 总结 : serializable 串行化可以彻底解决幻读 , 但是 事务只能排队执行 , 严重影响效率 , 数据库不会使用这种隔离级别
