mysql数据库高级部分及查漏补缺（三）
一、视图
什么是视图？
方便查数据。
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。
创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
删除视图
DROP VIEW view_name
(详细介绍:http://www.cnblogs.com/linhaifeng/articles/7495918.html)
二、事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
- 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
事务控制语句：
- BEGIN或START TRANSACTION；显式地开启一个事务；
- COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
- ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
- SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
- RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
- ROLLBACK TO identifier；把事务回滚到标记点；
- SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
三、索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
普通索引
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length；数字类型不用指定length。
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
删除索引的语法
DROP INDEX [indexName] ON mytable;
唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
(详细介绍:http://www.cnblogs.com/linhaifeng/articles/7274563.html)
四、账户管理
select host, user, authentication_string from user;
grant select on python02.students2 to 'linyuhong'@'localhost' identified by '123456';
五、MySQL主从
主：写入
从：读
读写分离
数据备份
负载均衡
六、查漏补缺
1部分
约束
unsigned 数字，非字符约束
not null 非空
default 设置默认值，所有类型数据都可以设置
unique 唯一
唯一
联合唯一 unique（字段，字段）
auto——increment
自增 针对int
自带 not null
前提：只能对unique字段进行设置
不受删除影响
foreign key
外键约束
foreign key(cid) references class3(id) on delete cascade on update cascade
约束的字段至少unique
级联删除 on delete cascade
级联更新 on undate cascade
primary key
相当于非空+唯一
一张表只能有一个，并且必须有一个
联合主键
# show databases; 查看一共有多少库
# select database(); 查看当前所在的库
unique 能不能重复插入多个null -- 可以
对于mysql来说 数据与数据之间相等就是重复
null 不能用 = 判断
外键约束 : 对应外表中的字段至少是unique的,推荐使用主键作为关联字段
# 数据的插入操作
# insert into class3 select * from class;
# insert into class3(id,name) select id,cname from class;
单表查询-select
select 查询
# select user();
# select database();
# select now();
# select 字段名,字段名,字段名 from 表名;
distinct 去重
查询字段四则运算
concat ('as','ba') 字符串拼接
concat_ws（'/',''） 相当于字符串的join
select
(
case
when 条件 then
concat()
when 条件 then
字段
else
concat()
)
from xxxx:
单表查询-where
where 筛选的是行
select * from 表 where 条件
范围查询
< > <= >= = != /<>
between a and b
in(1,2,3,4) n选一
模糊查询
like
% 一个代表任意长度的任意字符
%a
a%
%a%
_ 一个代表一个任意字符
a_
regexp
'^a'
'\d+'
is is not
is null
is not null
逻辑运算
and
or
not
表与表之间的关系
校区表 班级表 一对多
# 校区表 一个校区可以有多个班级 一对多
# 班级表 一个班级可不可以对应多个校区
# 校区表 校区id 校区名称 校区城市 校区地址
# 班级表 班级id 班级名称 开班日期 班主任 校区id
# 多(foreign key)关联一这张表
# 班级表创建foreign key关联校区表的校区id字段
# 学生表 班级表 多对多
# 站在学生的角度上 一个学生属于多个班级 一对多
# 站在班级的角度上 一个班级可以有多个学生么 多对一
# 学生表 学生id 学生姓名 ...
# 班级表 班级id 班级名称 ...
# 产生第三张表
# 一个字段是外键关联另一个表的主键
# 另一个字段外键关联另一张表的主键
# 学生表 客户表 一对一
# 一个客户对应一个学生
# 学生表gid foreign key 关联客户表id主键
# 并且gid还要设置为unique
2部分
单表查询
关键词，以及执行顺序
select（5） 想要的列 from（1） 表
where（2） 先从这张表中查询的行
group by（3） 分组
having （4）对组过滤
order by （6） 排序
limit 取一个区间（7）
分组
根据某个重复率比较高的字段进行的
select * from 表 group by 字段名
# 分组 group by
# 根据某个重复率比较高的字段进行的
# 这个字段有多少种可能就分成多少个组
# 根据性别分组 : 男的一组 女的一组
# 根据部门分组 : 销售一组 教学一组 ...
# 去重
# 一旦分组了就不能对具体某一条数据进行操作了
# 永远都是考虑这组xxx
# group_concat : 只用来做最终的显示,不能作为中间结果操作其他数据
聚合函数
99.99%的情况都是和分组一起用的
# 99.99%的情况都是和分组一起用的
# 如果没有和分组一起用,默认一整张表是一组
count(id) / count(*) 计数 :每个组对应几条数据
max 求最大值: 这个组中某字段的最大值
min 求最大值: 这个组中某字段的最小值
avg 求平均值
sum 求和值
#select min(hire_date) from employee
# 求employee中所有人里最早入职的时间
# select min(hire_date) from employee group by post
# 求每个部门中入职最早的时间
# 练习
# 求各部门薪资大于1w的人的个数
# select * from employee where salary >10000;
# select post,count(id) from employee where salary >10000 group by post;
having
就是一个对组进行筛选的条件
# 就是一个对组进行筛选的条件
# 要部门人数大于3个人的部门 count(id)>3
# select post from employee group by post having count(id)>3;
# having的问题 不建议你用
# select id,emp_name,age from employee having age>20;
# select id,emp_name from employee group by age having age>20;
order by
排序
# order by 字段
# order by 字段 desc
# order by 字段1,字段2
# order by 字段 asc,字段2 desc
# order by 字段 desc,字段2 asc
# order by 字段 desc,字段2 desc
limit
显示分页
# 1.显示分页
# limit m,n
# 表示从m+1开始,取n条
# limit 0,6 表示从1开始取6条
# limit 6,6 表示从7开始取6条
# limit 12,6 表示从13开始取6条
# limit 18,6 表示从19开始取6条
# 2.取前n名
limit n m默认为0
跟order by一起用
# limit n offset m :从m+1开始,取n条
拾遗
表操作（基本不用，需要来查）
表操作
创建 crate table
删除 drop table
查看表结构 desc 表/show create table 表
修改表
id age name varchar(255)
alter table 表名 rename 新表明
alter table 表名 add 新字段 类型(宽度) 约束;
add 新字段 类型(宽度) 约束 after id
add 新字段 类型(宽度) 约束 first
alter table 表名 drop 字段名;
alter table 表名 change 旧字段 新字段 类型(宽度) 约束;
change name username char(12) not null
change name name char(12) not null
change name name varchar(255) after id;
alter table 表名 modify 存在的字段 新类型(新宽度) 新约束;
modify name char(12) unique;
modify name char(12) unique after id;
数据操作
id name gender
增加
insert into 表名 value (1,'alex','female')
insert into 表名 values (1,'alex','female'),(1,'alex','female');
insert into 表名(name,gender) values ('alex','female'); *****
insert into 表名(name,gender) select (username,sex) from day38.表2;
删除
delete from 表名 where 条件
修改
update 表名 set 字段=值1 where 条件
update 表名 set 字段1=值1,字段2=值2 where 条件
因为在MYSQL里，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录，
解决办法是，将select得到的结果，再通过中间表select一遍，这样就规避了错误，
这个问题只出现于mysql，mssql和oracle不会出现此问题。
所以只要将语句做如下修改即可：
UPDATE user_info SET STATUS = '1' WHERE USER_ID IN
(SELECT USER_ID FROM (SELECT USER_ID FROM user_info WHERE NAME != '') t1);
将以下查询结果作为中间表，
再查询一遍中间表作为结果集：
查
单表查
去重 distinct
python操作数据库
sql注入还没讲****
查询
import pymysql
conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",
database='day40')
#Connection 联结;接通;连接;连接点;
cur = conn.cursor() # 游标 数据库操作符
#coursor (计算机荧光屏上的)光标，游标
sql = 'select emp_name,salary from employee where age = %s'
cur.execute(sql,(80,)) sql里面的数值拼接，一定用execute的方法 % 会自动读取数据格式并装饰。安全
#execute实行;执行;实施;成功地完成(技巧或动作)
git@gitee.com:old_boy_python_stack_23/youliyang.git
# 获取结果
ret1 = cur.fetchone()
ret2 = cur.fetchmany(2)
ret3 = cur.fetchall()
#commit 做出(错或非法的事)
#fetch 取来
print(ret1)
print(ret2)
print(ret3)
cur.close()
conn.close()
insert 写入
写入一定有conn.commit() 同步数据
import pymysql
conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",
database='day40')
cur = conn.cursor() # 游标 数据库操作符
username = '太亮'
sql = 'insert into employee(id,emp_name,sex,age,hire_date) values(%s,%s,"female",88,20170707)'
cur.execute(sql,(21,username,))
conn.commit()
cur.close()
conn.close()
操作数据库
conn = pymysql.Connection(host='127.0.0.1', user='root', password="123",
database='day40')
cur = conn.cursor() # 游标 数据库操作符
cur.excute(sql,(值1,))
查询
fetchone fethmany(10) fetchaLL
插入\更新\删除
conn.commit()
cur.close()
conn.close()
3部分
多表查询
连表的本质
笛卡尔积
先计算两张表的笛卡尔积,再根据用户给出的条件进行筛选
select * from employee,department where dep_id = department.id
内连接
内连接 inner join ... on 连接条件
# select * from 表1 inner join 表2 on 条件
# select * from employee inner join department on dep_id = department.id
# employee --> dep_id: 200,201,202,204
# department --> id : 200,201,202,203
# 内连接 :只显示两张表中互相匹配的项,其他不匹配的不显示
外连接
左外连接 left join .. on
select * from 表1 left join 表2 on 条件
select * from employee left join department on dep_id = department.id
select * from department left join employee on dep_id = department.id
不管左表中是不是匹配上都会显示所有内容
右外连接 right join .. on
select * from 表1 right join 表2 on 条件
select * from employee right join department on dep_id = department.id
不管右表中是不是匹配上都会显示所有内容
全外连接
select * from department left join employee on dep_id = department.id
union
select * from department right join employee on dep_id = department.id
子查询
子查询
存在年龄大于25岁员工的部门
select * from employee where age>25;
select distinct dep_id from employee where age>25;
select * from department where id in (select distinct dep_id from employee where age>25);
总结
如果最终需要的结果只出现在一张表中,可以用子查询解决问题
如果最终需要的结果出现在2表中,那么最后用的一定是连表查询
select * from 表 where 字段 > 一个值
如果我们查询出来的结果是一个值,那么就可以使用 > < =
select * from 表 where 字段 in (1个值,1个值)
如果我们查询出来的结果是一列中的多个值
dep_id
201
202
204
如果我们查出来的是一张表,也不能作为条件,如果必须保留这两个字段,就不能用作条件,只能连表
id name
1 alex
select 语句到底做了什么?
select name from emp;
select name as n,(select age from employee where name = n) from emp;
子查询处理可以放在条件中,还可以放在连表中,还可以放在select字段(要求查询的结果必须是一个单行单列的值)中.
推荐连表 : 连表的效率比子查询的效率要高
4部分
索引原理
磁盘预读4096字节
b树
balance tree平衡树
b+树
# 数据只存储在叶子节点
# 在子节点之间加入了双向地址连接,更方便的在子节点之间进行数据的读取
索引原理
innodb索引
聚集索引 只有一个主键
辅助索引 除了主键之外所有的索引都是辅助索引
回表: 只查询一个索引并不能解决查询中的问题,还需要到具体的表中去获取正行数据
myisam索引
辅助索引 除了主键之外所有的索引都是辅助索引
索引种类
# 索引的种类
# priamry key 的创建自带索引效果 非空 + 唯一 + 聚集索引
# unique 唯一约束的创建也自带索引效果 唯一 + 辅助索引
# index 普通的索引 辅助索引
创建索引&删除索引
创建索引
create index ind_name on 表(name);
删除索引
drop index ind_name on 表;
索引优缺点
索引的优缺点
优点 : 查找速度快
缺点 : 浪费空间,拖慢写的速度
不要在程序中创建无用的索引
# 创建了索引之后的效率大幅度提高
# 文件所占的硬盘资源也大幅度提高
正确使用索引***
正确使用索引
1.所查询的列不是创建了索引的列
2.在条件中不能带运算或者函数,必须是"字段 = 值"
3.如果创建索引的列的内容重复率高也不能有效利用索引
重复率不超过10%的列比较适合做索引
4.数据对应的范围如果太大的话,也不能有效利用索引
between and > < >= <= != not in
5.like如果把%放在最前面也不能命中索引
6.多条件的情况
and 只要有一个条件列是索引列就可以命中索引
or 只有所有的条件列都是索引才能命中索引
7.联合索引
在多个条件相连的情况下,使用联合索引的效率要高于使用单字段的索引
where a=xx and b=xxx;
对a和b都创建索引 - 联合索引
create index ind_mix on s1(id,email)
1.创建索引的顺序id,email 条件中从哪一个字段开始出现了范围,索引就失效了
select * from s1 where id=1000000 and email like 'eva10000%' 命中索引
select count(*) from s1 where id > 2000000 and email = 'eva2000000' 不能命中索引
2.联合索引在使用的时候遵循最左前缀原则
select count(*) from s1 where email = 'eva2000000@oldboy';
3.联合索引中只有使用and能生效,使用or失效
字段 能够尽量的固定长度 就固定长度
varchar
mysql补充
mysql 神器 explain
查看sql语句的执行计划
explain select * from s1 where id < 1000000;
是否命中了索引,命中的索引的类型
索引覆盖&索引合并
覆盖索引 using index
select count(id) from 表;
select id from 表 where id <20;
索引合并
创建的时候是分开创建的
用的时候临时和在一起了
using union 表示索引合并
sql慢日志
# 慢日志是通过配置文件开启
# 如果数据库在你手里 你自己开
# 如果不在你手里 你也可以要求DBA帮你开
7表联查速度慢怎么办?
1.表结构
尽量用固定长度的数据类型代替可变长数据类型
把固定长度的字段放在前面
2.数据的角度上来说
如果表中的数据越多 查询效率越慢
列多 : 垂直分表
行多 : 水平分表
3.从sql的角度来说
1.尽量把条件写的细致点儿 where条件就多做筛选
2.多表尽量连表代替子查询
3.创建有效的索引,而规避无效的索引
4.配置角度上来说
开启慢日志查询 确认具体的有问题的sql
5.数据库
读写分离
解决数据库读的瓶颈
数据表\库的导入导出
备份表 :homwork库中的所有表和数据
mysqldump -uroot -p123 homework > D:\s23\day42\a.sql
备份单表
mysqldump -uroot -p123 homework course > D:\s23\day42\a.sql
备份库 :
mysqldump -uroot -p123 --databases homework > D:\s23\day42\db.sql
恢复数据:
进入mysql 切换到要恢复数据的库下面
sourse D:\s23\day42\a.sql
开启事务给数据加锁
# begin;
# select id from t1 where name = 'alex' for update;
# update t1 set id = 2 where name = 'alex';
# commit;
