021 MySQL的一些进阶操作

前言：

        详细介绍了索引、数据导入导出、数据备份恢复、各种方式的查询以及一些重要的MySQL概念，列出了一些相关示例帮助理解，并对一些个人觉得值得记忆的语句进行了highlight~食用愉快~

1.索引

1.用的是BTree算法
2. 优点 ：加快数据的检索速度
3. 缺点 ：
1.需动态维护，占用系统资源，降低数据维护速度
2.占用物理存储空间
4.示例
1. 开启运行时间检测： set profiling = 1;
ps: show variables like 'profiling';
2.执行查询语句： select name from t1 where name='lucy99999';
3. 查看执行时间： show profiles;
4.给name字段 创建索引： create index name on t1(name);
5.执行查询语句：select name from t1 where name='lucy88888';
6. 查看执行时间：show profiles;
7. 关闭运行时间检测

5.索引类型

1.普通索引(index)

1.使用规则
1.可设置多个字段
2.字段值约束： 无
3.KEY标志： MUL
select ... from 表名 where 字段 ... order by 字段名
2.创建
1.创建表时：(写在最后，在括号内)
index(字段名),
index(字段名),
2.已有表时：
create index 索引名 on 表名(字段名)；
3.查看
1.desc 表名;
2.show index from 表名\G;
4.删除
1. drop index 索引名 on 表名;
(要一个一个删)

2.唯一索引(unique)

1.使用规则
1.可设置多个字段
2.约束： 字段值不允许重复，允许为null
3.KEY标志： UNI
2.创建
1.创建表时：
unique(字段名)，
unique(字段名)
2.已有表：
create unique index 索引名 on 表名(字段名);
3.查看、删除同普通索引
show index from 表名\G;
Non_unique:0 -->唯一索引
Non_unique:1 -->普通索引

3.主键(primary key) && 自增长属性(auto_increment)

1.自增长属性：和主键没关系， 可有可无;自动增长;不会维护id的连续;
(1.删除id=4，再插入null时，id自动=5
2.插入id=10，再插入null时，id自动=11)
(起到唯一标识主键的作用，不会维护)
2.使用规则
1. 只能有一个主键字段
2.约束： 不允许重复，不允许为null
3.KEY标志： PRI
4.通常设置记录编号字段id为主键，唯一锁定一条记录
3.创建
1.创建表时：
id int primary key auto_increment,
2.已有表时：
alter table 表名 add primary key(字段名);
增加自增长属性：
alter table 表名 modify id int auto_increment;
(修改id的数据类型)
4.删除
1.删除自增长属性
alter table 表名 modify id int;
2.删除主键
alter table 表名 drop primary key;
ps：非空约束去不掉
5.指定 自增长属性起始值
1.创建表
create table 表名(
id int primary key auto_increment,
... ...
) auto_increment=100,
character set=utf8;
2.已有表
alter table 表名 auto_increment=1000;

4.外键(foreign key)

                 1.定义：当前表字段值从另一个表范围内选择
                2.语法格式：

字段名 数据类型 foreign key(参考字段名)

references 主表(被参考字段名)

on delete 级联动作

on update 级联动作

3.使用规则

1.主表、从表的字段数据类型要一致

2.主表被参考字段 ： 主键

4.示例

表1 缴费信息表(财务)

学号   姓名         班级             金额

1  唐伯虎    AID1805       300

2 点秋香     AID1805      200

表2 学生信息表(班主任)

学号          姓名 金额

3 文征明          250

表1 jftab

create table jftab(

id int primary key, ---主表一定要是主键

name varchar(20),

class char(7),

money int

)character set utf8;

insert into jftab values

(1,'唐伯虎','AID1805',300),

(2,'点秋香','AID1805',200);

表2 bjtab

create table bjtab(

stu_id int,

name varchar(20),

money int,

foreign key(stu_id) ---创建外键

references jftab(id) ---参考

on delete cascade ---创建级联动作

on update cascade

)character set utf8;

insert into bjtab values

(1,'唐伯虎',300),

(2,'点秋香',200);

5.删除外键

1.查看外键名： show create table 表名;

2.删除外键：alter table 表名 drop foreign key 外键名;

alter table bjtab drop foreign key bjtab_ibfk_1;

6.级联动作

1.cascade

数据级联删除、级联更新(参考字段)

2.restrict(默认)

如果从表有相关联记录，不允许主表操作

3.set null

主表更新，从表相关联记录字段值自动设置为：null

7.已有表添加外键

alter table 表名 add

foreign key(参考字段名) references 主表(被参考字段)

on delete ...

on update ...

alter table bjtab add

        foreign key(stu_id) references jftab(id)

2.数据导入

1.把文件系统内容导入到数据库的表中
2.命令格式
load data infile "文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "\n"
csv 的分隔符是逗号(,)
3.将scoretable.csv文件导入到db3库下的score表中
1.先把scoretable.csv文件拷贝到数据库的默认搜索路径
1.查看搜索路径方法
show variables like 'secure_file_priv';
(默认搜索路径可能被人改过，所以导入之前查看一下)
2.执行复制命令
sudo cp /home/tarena/scoretable.csv 
/var/lib/mysql-files/
2.创建库、表(库和表都要是utf8的)
1.create database db3 character set utf8;
2.use db3;
3.
create table score (
id int,
name varchar(20),
score decimal(5,2),
phone char(11),
class char(7)
)character set utf8;
3.执行导入语句
load data infile '/var/lib/mysql-files/scoretable.csv'
into table score
fields terminated by ','
lines terminated by '\n';
注意：
1.库、表必须都为utf8
2.路径必须写绝对路径
4.文件权限问题
rwx ：所有者
r-- ：所属组其它用户对此文件的权限
r-- ：其他组的用户对此文件的权限
root ：所有者
root ：所属组

r:read
w:write
x:execute

1.修改文件权限
chmod +rw scoretable.csv (给3组都加权限)
chmod 666 scoretable.csv
步骤：
1.sudo -i
2.cd /var/lib/mysql-files
3.chmod 666 scoretable.csv

ex：执行hello.py文件
1.python3 hello.py
2.chmod +x hello.py
3.  ./hello.py
4 .excel文件转为csv文件
1.打开excel --> 文件 --> 另存为 --> *.csv(逗号分隔)
5. 更改一个文件的字符编码
1.用记事本 /编辑器打开 --> 文件 --> 另存为 --> 选择编码

3.数据导出

1.将数据库中表记录保存到系统文件里
2.语法格式
select ... from 表名
into outfile '文件名'
fields terminated by '分隔符'
lines terminated by '\n';
3.示例
1.把sanguo表中的姓名、攻击值、国家三个字段导出到文件sanguo.csv中
select name,gongji,country from MOSHOU.sanguo
into outfile '/var/lib/mysql-files/sanguo.csv'
fields terminated by ','
lines terminated by '\n';
2.把MySQL库下的user表中user、host两个字段的值导出到user.txt中;字段之间用三个空格去分隔
select user,host from mysql.user
into outfile '/var/lib/mysql-files/user.txt'
fields terminated by '   '
lines terminated by '\n';
4.注意
1.导出的内容完全有sql查询语句决定
2.路径必须指定为数据库搜索的 绝对路径

4.表的复制

1.语法
create table 表名 select ... from 表名;
2.示例
1.复制sanguo表，起名为sanguo2
create table MOSHOU.sanguo2 select * from MOSHOU.sanguo;
2.复制sanguo表的前三条记录，放到sanguo3
create table sanguo3 select * from sanguo limit 3;
3.复制sanguo表的id、name、country的前5条记录，放到sanguo4
create table sanguo4 select id,name,country from sanguo limit 5;
3.只 复制表结构
create table 表名 select * from 表名 where false;

5.嵌套查询(子查询)

1.定义
把内层的查询结果作为外层的查询条件
2 .select ... from 表名 where 字段名 运算符(select ...);
3.示例
1.把攻击值小于平均攻击值的名字和攻击值显示出来
select name,gongji from sanguo where gongji <(select avg(gongji) from sanguo );
2.找出每个国家攻击力最高的英雄名字和攻击值
select name,gongji from sanguo where gongji in (select max(gongji) from sanguo group by country);
select name,max(gongji) from sanguo group by country;
逻辑有问题。。。

()里的sql语句：打印出3条
整个嵌套语句：打印出4条
原因：某个国家的最大攻击值有重复；()语句是以group by country来去重；
嵌套语句是以gongji来筛选的

where后面的gongji用in
原因：()返回的值有多个

select name,gongji from sanguo 
where(country,gongji) in (select country,max(gongji) from sanguo group by country);
没括号，会报错；
逻辑没有问题：限制了其它国家也等于本国最大值的情况。

6.多表查询

1.两种方式
1. select 字段名列表 from 表1,表2,...; 笛卡尔积(不加where条件)
(第一张表的所有记录和第二张表第一条记录去匹配,...)
2. select 字段名列表 from 表1,表2,... where 条件;
2.示例
1.显示省、市详细信息
select sheng.s_name,city.c_name from sheng,city 
where sheng.s_id=city.cfather_id;
2.显示省、市、县详细信息
select sheng.s_name,city.c_name,xian.x_name from sheng,city,xian
where sheng.s_id=city.cfather_id 
having xian.xfather_id = city.c_id;

7.连接查询

1.内连接
1.语法格式
select ... from 表1 
inner join 表2 on 条件 
(inner join 表3 on 条件...);
ps :只显示匹配到的记录
2.示例
1.显示省、市详细信息
select sheng.s_name,city.c_name from sheng 
inner join city on sheng.s_id=city.cfather_id; 
2.显示省、市、县详细信息
select sheng.s_name,city.c_name,xian.x_name from sheng 
inner join city on sheng.s_id = city.cfather_id 
inner join xian on xian.xfather_id = city.c_id;
2.外连接
1.左连接
1.以左表为主显示查询结果
2.语法格式
select ... from 表1 
left join 表2 on 条件
(left join 表3 on 条件);
3.示例
1.显示省、市详细信息,要求省全部显示
select sheng.s_name,city.c_name from sheng
left join city
on sheng.s_id=city.cfather_id;
2.显示省、市、县详细信息,要求省全部显示
select sheng.s_name,city.c_name,xian.x_name from sheng
left join city
on sheng.s_id=city.cfather_id
left join xian
on city.c_id=xian.xfather_id;
3.显示省、市、县详细信息,要求市全部显示
select sheng.s_name,city.c_name,xian.x_name from sheng
right join city
on sheng.s_id=city.cfather_id
left join xian
on city.c_id=xian.xfather_id;
2.右连接

8.数据备份

(mysqldump，在Linux终端操作)

1.分类：
完全备份：备份所有数据
增量备份：每次都备份新增加的数据
2.命令格式
mysqldump -u用户名 -p 源库名 > 路径/xxx.sql
3.源库名的表示方式
--all-databases 备份所有库
库名 备份单个库
-B 库1 库2 ...         备份多个库
库名 表1 表2...         备份指定表
4.示例
1.备份所有库为 all.sql, /home/tarena/mydata/
mysqldump -uroot -p --all-databases > all.sql
2.备份MOSHOU库中的sheng、city、xian三张表MOSHOUscx.sql
mysqldump -uroot -p MOSHOU sheng city xian > MOSHOUscx.sql
3.备份2个库
mysqldump -uroot -p -B MOSHOU db4 > MSdb4.sql

9.数据恢复

1.命令格式
mysql -u用户名 -p 目标库名 < xxx.sql
ps：恢复前，先创建空库
2.目标库名
--one-database 从所有库中，恢复某一个库
ps:删除时是什么名，恢复时也叫什么名
3.示例
1.恢复MOSHOU库,MOSHOU.sql
mysql -uroot -p MOSHOU < MOSHOU.sql
2.恢复MOSHOU库,all.sql
mysql -uroot -p --one-database MOSHOU < all.sql
3.在MOSHOU库中，
1.新建一张表t888(id)
2.sheng表中，新添加一条记录：台湾
4.注意
1.恢复库时，原库中旧表数据会被覆盖，原库中新表不会被删除

10.事务和事务回滚

1.事务：一件事 从开始发生到结束的整个过程
2.作用：确保数据的 一致性；
3.事务应用
1.开启事务
mysql > begin; | start transaction;
2.终止事务
mysql > commit; | rollback;(回滚，什么也没做)
ps：show variables like 'autocommit';
mysql中语句都是自动提交，输入分号后，自动执行语句；
4.注意
1.只针对 表记录的增删改；
2.对 建库、建表无效；
5.案例
1.背景
你：建行卡
朋友：工商卡
你在建行ATM给朋友转5000元
2.过程
表1 CCB
create table CCB(
name varchar(20),
money int
);
insert into CCB values('有钱人',10000);
表2 ICBC
create table ICBC(
name varchar(20),
money int
);
insert into ICBC values('没钱人',0);
3.开始转账
mysql> begin;
mysql> update CCB set money=money-5000 where name='有钱人';
mysql> update ICBC set money=money+5000 where name='没钱人';
mysql> commit; | rollback;

11.mysql存储引擎(处理表的处理器)

1.基本操作
1.查看所有存储引擎
show engines;
2.查看表存储引擎
show create table 表名;
3.指定存储引擎
create table 表名 (...)engine=innodb,chara...;
4.修改表存储引擎
alter table 表名 engine=myisam;
5.工作中常用
InnoDB MyISAM
2.修改表默认存储引擎
1.sudo -i
2.cd /etc/mysql/mysql.conf.d/
3.cp mysqld.cnf mysqld.cnf.bak
4.vi mysqld.cnf
[mysqld]
default-storage-engine = myisam
5./etc/init.d/mysql restart

12.锁

1.目的：解决客户端的 并发访问的冲突问题
2.分类
1.按锁类型分
1. 读锁(select)(共享锁)：其它用户 只能查看，不能修改
2. 写锁(insert)(互斥锁、排他锁)：其它用户不能查看，不能修改
2.按锁粒度分
1.表级锁：可以加读锁或写锁
2.行级锁：可以加读锁或写锁

13.存储引擎特点

1.MyISAM特点
1.独享表空间
表名.frm 表结构
表名.myd 表记录
表名.myi 索引文件
2. 支持表级锁，不支持行级锁
2.InnoDB特点
1.共享表空间
表名.frm 表结构和索引信息
表名.idb 表记录
2. 支持行级锁
3.memory
1.表结构存储在硬盘里，表记录存储在内存中
表名.frm
2.服务重启后， 表结构还在，表记录都消失
4.如何决定表使用什么存储引擎
1.主要用来查询的表用MyISAM
2.写操作多的表用InnoDB

14.mysql调优

1.选择合适的存储引擎(13.4)
2.创建索引
在select、where、order by常涉及到的字段建立索引
3.sql语句优化
1.where条件判断尽量不使用 !=, 否则放弃索引全表扫描
2.尽量避免使用null值判断,否则放弃索引全表扫描
优化前：select id from t1 where id is null;
优化后：在id字段设置默认值为0,确保id字段没有null值
select id from t1 where id=0;
3.尽量避免使用 or 来连接条件,否则放弃索引全表扫描
优化前：select id from t1 where id=10 or id=20l
优化后：select id from t1 where id=10 
union all 
select id from t1 where id=20
4.模糊查询尽量避免使用前置%，否则放弃索引全表扫描
select name from t1 where name like '%c%';
5.尽量避免使用in 和 not in，否则放弃索引全表扫描
优化前：select id from t1 where id lin(1234);
优化后：where id between 1 and 4； 
6.尽量避免使用*，要用具体的字段代替'*',不要返回用不到的任何字段

15.与python交互

1.交互类型
python3：pymysql $ sudo pip3 install pymysql
python2：MySQLdb  $ sudo pip install mysql-python
2.connect对象
1.创建与数据库连接的对象(调用connect()方法)
conn = pymysql.connect(参数列表)
参数列表
1.host：主机地址
2.port：端口3306
3.db ：数据库名
4.passwd：密码
5.charset：编码方式，推荐utf8
6.user：用户名
示例：
conn = pymysql.connect(
host='localhost',
user='root',
passwd='123456',
db='db4',
charset='utf8')
2.连接对象(conn)的方法
1. close() ：关闭与数据库的连接
2. commit() ：提交到数据库执行
3. rollback()  ：回滚
4. cursor() ：创建游标对象，用于执行sql语句
3.游标对象
1.作用：执行sql语句
2.示例：
cur = conn.cursor() 创建游标对象
cur.execute("delete from sheng;") 游标对象的执行语句
3.常用方法
1. execute(sql命令,[补位的参数列表]) 执行sql命令
2. fetchone() 获取查询记录集的第一条记录
3. fetchmany(n) 获取查询记录集的n条记录
4. fetchall() 获取所有记录
code见：select.py

#执行查询语句，并使用fetchone()方法
import pymysql

db = pymysql.connect(host='localhost',
                    user='root',
                    passwd='123456',
                    db='MOSHOU',
                    charset='utf8')

cur = db.cursor()
try :
    sql_select = 'select * from sheng;'
    cur.execute(sql_select) #所有查询结果都在cur对象里
    data = cur.fetchone()
    print('fetchone:',data)

    data2 = cur.fetchmany(3)    #拿一个，少一个；接着从第2个开始拿
    print('fetchmany:',data2)

    data3 = cur.fetchall()
    print('fetchall:',data3)

    print('ok')
    db.commit()
except Exception as e:
    print(e)

cur.close()
db.close()

5.pymysql使用流程
1.建立数据库连接 conn
2.创建游标对象 cur = conn.cursor()
3.执行语句 cur.execute("...")
4.提交执行 conn.commit()
5.关闭游标对象 cur.close()
6.断开连接 conn.close()

code见：zsg.py | zsg2.py

#zsg.py

# 增删改　数据库

import pymysql #1.创建数据库的连接对象 conn = pymysql.connect(host='localhost', user='root',passwd='123456', db='MOSHOU',charset='utf8') #2.创建游标对象 cur = conn.cursor() #3.利用游标对象的execute方法执行sql语句 cur.execute('insert into sheng values \ (33,999999,"新疆");') #4.提交到数据库执行 conn.commit() #5.关闭游标 cur.close() #6.关闭数据库连接 conn.close()

#zsg2.py
#使用try语句
import pymysql

db = pymysql.connect(host='localhost',
                    user='root',
                    passwd='123456',
                    db='MOSHOU',
                    charset='utf8')

cur = db.cursor()

try:
    sql_delete = 'delete from sheng where id = 1;'
    cur.execute(sql_delete)

    sql_update = 'update sheng set id = 200 where id = 2;'
    cur.execute(sql_update)

    db.commit()
    print('ok')
except Exception as e:
    db.rollback()
    print('Failed', e)

cur.close()
db.close()

16.ER模型(Entry-Relationship)

1.定义
实体-关系模型，用于数据库的设计
2.三个概念
1.实体：矩形
2.属性：椭圆形
3.关系：实体之间的关系
1.1:1 
2.1:n 父亲对孩子
3.m:n 兄弟姐妹对兄弟姐妹