24.29mysql 表查询--加强
24.29.1 介绍
-- 查询加强
-- ■使用 where 子句
-- ?如何查找 1992.1.1 后入职的员工
-- 老师说明: 在 mysql 中,日期类型可以直接比较, 需要注意格式
-- ■ 如何使用 like 操作符(模糊)
-- %: 表示 0 到多个任意字符 _: 表示单个任意字符
-- ?如何显示首字符为 S 的员工姓名和工资
-- ?如何显示第三个字符为大写 O 的所有员工的姓名和工资 “ '__O' ” 这里是两个下划线
-- ■ 如何显示没有上级的雇员的情况
-- ■ 查询表结构 DESC emp
-- 使用 order by 子句
-- ?如何按照工资的从低到高的顺序[升序],显示雇员的信息
-- ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
24.29.2 分页查询
-- 分页查询
-- 按雇员的 id 号升序取出, 每页显示 3 条记录,请分别显示 第 1 页,第 2 页,第 3 页
-- 第 1 页
-- 第 2 页
-- 第 3 页
-- 推导一个公式
SELECT * FROM emp
ORDER BY empno
LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数
-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY job;
-- 显示雇员总数,以及获得补助的雇员数
SELECT COUNT(*) FROM emp WHERE mgr IS NOT NULL;
SELECT MAX(sal) - MIN(sal) FROM emp;
24.29.3 使用分组函数和分组子句 group by
-- 增强 group by 的使用
-- (1) 显示每种岗位的雇员总数、平均工资。
-- (2) 显示雇员总数,以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列),如果该列的值为 null, 是
-- 不会统计 , SQL 非常灵活,需要我们动脑筋.
-- 老师的扩展要求:统计没有获得补助的雇员数
方式一:
方式二:
-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
-- (4) 显示雇员工资的最大差额。
-- 思路: max(sal) - min(sal)
-- 应用案例:请统计各个部门 group by 的平均工资 avg,
-- 并且是大于 1000 的 having,并且按照平均工资从高到低排序, order by
-- 取出前两行记录 limit 0, 2
24.29.4 数据分组的总结
24.32表复制
24.32.1 自我复制数据(蠕虫复制)--
-- 表的复制
-- 为了对某个 sql 语句进行效率测试,我们需要海量数据时,可以使用此法为表创建海量数据
CREATE TABLE my_tab01
( id INT,
name VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
DESC my_tab01
SELECT * FROM my_tab01;
-- 演示如何自我复制
-- 1. 先把 emp 表的记录复制到 my_tab01
-- 2. 自我复制
-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02,
-- 2. 让 my_tab02 有重复的记录
-- 3. 考虑去重 my_tab02 的记录
24.33合并查询
24.33.1 介绍
-- 合并查询
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
-- union all 就是将两个查询结果合并,不会去重
-- union 就是将两个查询结果合并,会去重
24.34mysql 表外连接
24.34.1 提出一个问题
24.34.2 外连接
-- 外连接
-- 比如:列出部门名称和这些部门的员工名称和工作,
-- 同时要求 显示出那些没有员工的部门。
-- 使用我们学习过的多表查询的 SQL, 看看效果如何?
SELECT dname, ename, job
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY dname
SELECT * FROM dept;
SELECT * FROM emp;
创建stu 和 exam表
24.34.3 课堂练习
24.35mysql 约束
24.35.1 基本介绍
24.35.2 primary key(主键)-基本使用
-- 主键使用
-- id name email
-- 主键使用的细节讨论
-- primary key 不能重复而且不能为 null。
-- 一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
-- 演示复合主键 (id 和 name 做成复合主键)
-- 主键的指定方式 有两种
-- 1. 直接在字段名后指定:字段名 primakry key
-- 2. 在表定义最后写 primary key(列名);
24.35.3 not null(非空)
24.35.4 unique(唯一)
-- unique的基本使用
-- unique的细节
24.35.5 foreign key(外键)
reference参考、指向
如果一旦做成外键约束,那么,加入再加入3 hsp 300就加不进去了,因为,300在班级表中不存在;
同理,因为jack到的class_id指向班级表中的200,因此,外键的关系已经形成了,
此时不能直接删掉班级表中的200 web2 上海这一行,除非先把学生表中的jack删除掉才可以;
——这就是外键约束!
为什么要求是primary key 或者 unique约束?
因为,它们两个表之间形成的外键关系是唯一的,如果主表的主键不是unique,那么它就很有可能还有一个
id PHP 广州 的,此时 jack 是web班级 还是 PHP班级呢?所以要求,外键要指向某一个表的列的话,它要求这个列
必须是主键或者是unique
失败了,若想添加成功,首先要在my_class中添加 id 为 300 的班级
加Null是可以的,前提是外键字段允许为空,一旦 class_id INT NOT NULL,那么就不能再为NuLL了
constraint约束,如果要删,就要先把学生先删掉,让没有任何一个外键指向主表它;
24.35.6 check
24.35.7 商店售货系统表设计案例[先练,再评 10min]
1.创建数据库
2.创建商品表
3.创建客户表
4。创建购买表
24.36自增长
24.36.1 自增长基本介绍 一个问题
方式一:
方式二:
24.36.2 自增长使用细节
]\
指定的值,自增长
24.37mysql 索引
24.37.1 索引快速入门
-- 创建测试数据库 tmp
CREATE DATABASE tmp;
CREATE TABLE dept( /部门表/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;
#创建表 EMP 雇员
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /编号/
ename VARCHAR(20) NOT NULL DEFAULT "", /名字/
job VARCHAR(9) NOT NULL DEFAULT "",/工作/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/上级编号/
hiredate DATE NOT NULL,/入职时间/
sal DECIMAL(7,2) NOT NULL,/薪水/
comm DECIMAL(7,2) NOT NULL,/红利/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /部门编号/
) ;
#工资级别表
CREATE TABLE salgrade
(grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
);
#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
DELIMITER $$
#创建一个函数,名字
rand_string,可以随机返回我指定的个数字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
#定义了一个变量 chars_str, 类型 varchar(100)
#默认给 chars_str 初始值
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
# concat 函数 : 连接函数 mysql 函数
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
#这里我们又自定了一个函数,返回一个随机的部门号
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(10+RAND()*500);
RETURN i;
END $$
#创建一个存储过程, 可以添加雇员
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把 autocommit 设置成 0
#autocommit = 0 含义: 不要自动提交
SET autocommit = 0; #默认不提交 sql 语句
REPEAT
SET i = i + 1;
#通过前面写的函数随机产生字符串和部门编号,然后加入到 emp 表
INSERT INTO emp VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
#commit 整体提交所有 sql 语句,提高效率
COMMIT;
END $$
#添加 8000000 数据
CALL insert_emp(100001,8000000)$$
#命令结束符,再重新设置为;
DELIMITER ;
SELECT COUNT(*) FROM emp;
-- 在没有创建索引时,我们的查询一条记录
-- 在没有创建索引时,我们的查询一条记录
SELECT * FROM emp WHERE empno = 1234567
-- 使用索引来优化一下, 体验索引的牛
-- 在没有创建索引前 , emp.ibd 文件大小 是 524m
-- 创建索引后 emp.ibd 文件大小 是 655m [索引本身也会占用空间.]
-- 创建 ename 列索引,emp.ibd 文件大小 是 827m
-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp 表的
empno 列创建索引
CREATE INDEX empno_index ON emp (empno)
-- 创建索引后, 查询的速度如何
SELECT *
FROM emp
WHERE empno = 1234578 -- 0.003s
原来是 4.5s
-- 创建索引后,只对创建了索引的列有效
SELECT *
FROM emp
WHERE ename = 'PjDlwy' -- 没有在 ename 创建索引时,时间 4.7s
CREATE INDEX ename_index ON emp (ename) -- 在 ename 上创建索引
24.37.2 索引的原理
假设表为:
没有索引的时候:
select * from emp where id = 1
则会进行全表扫描,就算查询到的第一个 id = 1,但它不知道下边还有没有id为1 的,
因此还会继续进行全表扫描,所以查询速度慢。
索引的原理是二叉树:
比如说想要查找 id = 1
先查找 1比 5 小,就会查找 5 的左边,1比2小,因此就会查找 2的左边,就找到了1,如果1 的左边、右边都没有了,说明只有一个 1;如果比较30次,它可以覆盖表的2^30个数
二叉树只适用于查询(select)多的情况,如果添加、删除、修改即(insert、delete、update)就会很慢
24.37.3 索引的类型
24.37.4 索引使用
\
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
0代表是唯一索引, 1 代表不是唯一索引
当 id 这一列不会重复的时候,就使用唯一索引,如果有可能重复,就用 普通索引
添加主键索引
删除索引
删除主键索引
修改索引和删除索引
24.37.5 索引课堂练习
24.37.6 小结: 哪些列上适合使用索引
24.38mysql 事务
24.38.1 什么是事务
事务理解:
需求
1.将tom的100块钱,转到king
update balance money set money = money - 100
where id = 100
update balance set money = money +100
where id = 200
问题:
如果第一条语句执行成功,第二条语句执行失败,就会导致:
id = 100 的money 少了100,而id = 200 的money 没有加上100,无缘无故100块钱就没了
因此,这里就引出一个需求:
把一组SQL语句当成一个整体,要么都成功,要么都失败,不能一部分成功;
即,将多个dml(update、insert、delete)当做一个整体,要么全部成功,要么全部失败,
这里就使用事务来解决
24.38.2 事务和锁
24.38.3 回退事务
24.38.4 提交事务
24.38.5 事务细节讨论 transaction_detail.sql
事务细节
24.39mysql 事务隔离级别
24.39.1 事务隔离级别介绍
24.39.2 查看事务隔离级别
当我们在一个事务中,看到了看到了另外一个事物提交、修改或者删除,这就是不可重复读,
比如,有客户端C1 和 C2 去操作数据库,在C2事务里看到了C1提交的动作,那么C2就发生了不可重复读。
比如,C2在读的时候,时间点定在了连接到数据库的时间点,比如说10点,C1在10点以后提交了数据,但C2却读到了C1在10点以后提交的数据。
24.39.3 事务隔离级别
24.39.4 mysql 的事务隔离级--案例
24.39.5 设置事务隔离级别
-- 演示 mysql 的事务隔离级别
-- 1. 开了两个 mysql 的控制台
-- 2. 查看当前 mysql 的隔离级别
SELECT @@tx_isolation;
-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 4. 创建表
CREATE TABLE account(
id INT,
name VARCHAR(32),
money INT);
-- 查看当前会话隔离级别
SELECT @@tx_isolation
-- 查看系统当前隔离级别
SELECT @@global.tx_isolation
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]
24.40mysql 事务 ACID
24.40.1 事务的 acid 特性
24.40.2 事务的课堂练习 [一定要自己去练习,体会]
24.41mysql 表类型和存储引擎
24.41.1 基本介绍
24.41.2 主要的存储引擎/表类型特点
24.41.3 细节说明
我这里重点给大家介绍三种: MyISAM、InnoDB、MEMORY
24.41.4 三种存储引擎表使用案例
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎,是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁
-- myisam 存储引擎
CREATE TABLE t28 (
id INT,
name VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
START TRANSACTION;
SAVEPOINT t1
INSERT INTO t28 VALUES(1, 'jack');
SELECT * FROM t28;
ROLLBACK TO t1
-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了 Mysql 服务,数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有 IO 读写) 3. 默认支持索引(hash 表)
CREATE TABLE t29 (
id INT,
name VARCHAR(32)) ENGINE MEMORY
DESC t29
INSERT INTO t29
VALUES(1,'tom'), (2,'jack'), (3, 'hsp');
SELECT * FROM t29
-- 指令修改存储引擎
ALTER TABLE t29 ENGINE = INNODB
24.41.5 如何选择表的存储引擎
24.41.6 修改存储引擎
24.42视图(view)
24.42.1 看一个需求
24.30mysql 多表查询
24.30.1 问题的引出(重点,难点)
24.30.2 说明
-- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*
老韩分析
\1. 雇员名,雇员工资 来自 emp 表
\2. 部门的名字 来自 dept 表
\3. 需求:对 emp 和 dept 查询 ename,sal,dname,deptno
在默认情况下,当两个表查询时,规则:
1.从第一张表中取出一行,和第二张表的每一行进行组合,返回结果含有两张表的所有列。
2.一共返回的记录数 第一张表行数 * 第二张表行数
3.这样多表查询默认处理返回的结果,称为笛卡尔集
4.解决这个多表的关键就是要写出正确的过滤条件 where。
--1 ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*
老韩分析
1.雇员名,雇员工资 来自 emp 表
2. 部门的名字来自 dept 表
3. 需求对 emp 和 dept 查询 ename,sal,dname,deptno
\4. 当我们需要指定显示某个表的列是,需要 表.列表
*/
当添加where条件 emp.deptno = dept.deptno
-- 老韩小技巧:多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
--2 ?如何显示部门号为 10 的部门名、员工名和工资
-- ?显示各个员工的姓名,工资,及其工资的级别
-- 思路 姓名,工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写 sql , 先写一个简单,然后加入过滤条件...
24.30.3 多表查询练习 many_tab.sql
24.30.4 自连接
-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 韩分析: 员工名字 在 emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp 表的 mgr 列关联
其中有一个人是president,他没有上级,所以是12行
-- 这里老师小结:
-- 自连接的特点
-- 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名 例如:emp worker;加 as 的是给列起别名的时候用的
-- 3. 列名不明确,可以指定列的别名 例如:worker.ename as '职员'
24.31mysql 表子查询
24.31.1 什么是子查询 subquery.sql
子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询
24.31.2 单行子查询
单行子查询是指只返回一行数据的子查询语句
24.31.3 请思考:如何显示与 SMITH 同一部门的所有员工?
24.31.4 多行子查询
多行子查询指返回多行数据的子查询
使用关键字 in
-- 子查询的演示
-- 请思考:如何显示与 SMITH 同一部门的所有员工?
-- 课堂练习:如何查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员.
在MySQL中 “!=” <=> “< >”
24.31.5 子查询当做临时表使用 练习题 subquery.sql
-- 查询 ecshop 中各个类别中,价格最高的商品
思路:
24.31.6 在多行子查询中使用 all 操作符
-- all 和 any 的使用
-- 请思考:显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
方法一:
-- 可以这样写
方法二:
24.31.7 在多行子查询中使用 any 操作符
-- 请思考:如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号
方式一:
方式二:
24.31.8 多列子查询 manycolumn.sql
-- 多列子查询
-- 请思考如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
-- (字段 1, 字段 2 ...) = (select 字段 1,字段 2 from 。。。。)
-- 分析: 1. 得到 smith 的部门和岗位
-- 请查询 和宋江数学,英语,语文成绩 完全相同的学生
24.31.9 在 from 子句中使用子查询 subquery03.sql
24.31.10 在 from 子句中使用子查询—课堂小练习
视图
24.42.2 基本概念
24.42.3 视图的基本使用
24.42.4 完成前面提出的需求 view.sql
--
视图的使用
--
创建一个视图 emp_view01,只能查询 emp 表的(empno、ename, job 和 deptno ) 信息
--
创建视图
CREATE VIEW emp_view01
AS
SELECT empno, ename, job, deptno FROM emp;
-- 查看视图
DESC emp_view01
SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;
-- 视图的细节
-- 1.
创建视图后,到数据库去看,对应视图只有一个视图结构文件(形式: 视图名.frm)
-- 2.
视图的数据变化会影响到基表,基表的数据变化也会影响到视图[insert update delete ]
-- 修改视图 会影响到基表
UPDATE emp_view01
SET job = 'MANAGER'
WHERE empno = 7369
SELECT * FROM emp; -- 查询基表
SELECT * FROM emp_view01
-- 修改基本表, 会影响到视图
UPDATE emp
SET job = 'SALESMAN'
WHERE empno = 7369
-- 3. 视图中可以再使用视图 , 比如从 emp_view01 视图中,选出 empno,和 ename 做出新视图
DESC emp_view01
CREATE VIEW emp_view02
AS
SELECT empno, ename FROM emp_view01
SELECT * FROM emp_view02
24.42.5 视图细节讨论
24.42.6 视图最佳实践
24.42.7 视图课堂练习(学员练习)
--
视图的课堂练习
--
针对 emp ,dept , 和
salgrade 张三表.创建一个视图 emp_view03,
--
可以显示雇员编号,雇员名,雇员部门名称和 薪水级别[即使用三张表,构建一个视图]
/*
分析: 使用三表联合查询,得到结果
将得到的结果,构建成视图
*/
CREATE VIEW emp_view03
AS
SELECT empno, ename, dname, grade
FROM emp, dept, salgrade
WHERE emp.deptno = dept.deptno AND
(sal BETWEEN losal AND hisal)
DESC emp_view03
SELECT * FROM emp_view03
24.43Mysql 管理
24.43.1 Mysql 用户
24.43.2 创建用户
24.43.3 删除用户
24.43.4 用户修改密码
24.43.5 mysql 中的权限
24.43.6 给用户授权
24.43.7 回收用户授权
24.43.8 权限生效指令
24.43.9 课堂练习题 grant.sql
--
演示 用户权限的管理
--
创建用户 shunping 密码 123 , 从本地登录
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123'
-- 使用 root 用户创建 testdb ,表 news
CREATE DATABASE testdb
CREATE TABLE news (
id INT ,
content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;
-- 给 shunping 分配查看 news 表和 添加 news 的权限
GRANT SELECT , INSERT
ON testdb.news
TO 'shunping'@'localhost'
-- 可以增加 update 权限
GRANT UPDATE
ON testdb.news
TO 'shunping'@'localhost'
-- 修改 shunping 的密码为 abc
SET PASSWORD FOR 'shunping'@'localhost' = PASSWORD('abc');
-- 回收 shunping 用户在 testdb.news 表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM 'shunping'@'localhost'
REVOKE ALL ON testdb.news FROM 'shunping'@'localhost'
-- 删除 shunping
DROP USER 'shunping'@'localhost'
24.43.10 细节说明 manage_detail.sql
--
说明 用户管理的细节
--
在创建用户的时候,如果不指定 Host,
则为% , %表示表示所有 IP 都有连接权限
-- create user xxx;
CREATE USER jack
SELECT host, user FROM mysql.user
-- 你也可以这样指定
-- create user 'xxx'@'192.168.1.%' 表示
xxx 用户在 192.168.1.*的 ip 可以登录 mysql
CREATE USER 'smith'@'192.168.1.%'
-- 在删除用户的时候,如果 host 不是 %, 需要明确指定 '用户'@'host 值'
DROP USER jack -- 默认就是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%'
24.44本章作业