MySQL数据库（高级）

MySQL基础回顾

1、数据库概念

数据库

• 存储数据的仓库（逻辑概念，并未真实存在）

数据库软件

• 真实软件，用来实现数据库这个逻辑概念

数据仓库

• 数据量更加庞大，更加侧重数据分析和数据挖掘，供企业决策分析之用，主要是数据查询，修改和删除很少

2、MySQL的特点

• 关系型数据库
• 跨平台
• 支持多种编程语言（python、Java、php）
• 基于磁盘存储，数据是以文件形式存放在数据库目录/var/lib/mysql下

3、启动连接

• 服务端启动（两种方式）
  【1】sudo /etc/init.d/mysql start|stop|restart|status
  【2】sudo service mysql start|stop|restart|status
• 客户端连接
  mysql -hIP地址 -u用户名 -p密码
  本地连接可省略 -h 选项

4、基本SQL命令

库管理

1、查看已有库；
	show databases;
2、创建库并指定字符集（两种方式）；
	create database 库名 charset utf8;
	create database 库名 character set utf8;
3、查看当前所在库；
  	select database();
4、切换库；
  	use 库名；
5、查看库中已有表；
  	show tables;
6、删除库；
  	drop database 库名；

表管理

1、创建表并指定字符集；
  create table 表名(字段名 字段类型 xxx)charset=utf8;
2、查看创建表的语句 (字符集、存储引擎)；	
  show create table 表名;
3、查看表结构;
  desc 表名；     
4、删除表; 
  drop table 表名;
  drop table 表1,表2,表3;

表记录管理

1、增 ： insert into 表名(字段名) values(),();
2、删 ： delete from 表名 where 条件;
3、改 ： update 表名 set 字段名=值 where 条件;
4、查 ： select 字段名 from 表名 where 条件;

表字段管理（alter table 表名）

1、增 ： alter table 表名 add 字段名 字段类型 first | after 字段名; 
2、删 ： alter table 表名 drop 字段名;
3、改 ： alter table 表名 modify 字段名 新类型;
4、表重命名：alter table 表名 rename 新表名;

5、数据类型

四大数据类型

• 数值类型
  int[4] smallint[2] bigint[8] tinyint[1]
  float(m,n) double decimal
• 字符类型
  char()定长 varchar()不定长 text longtext blob longblob
• 枚举类型
  enum() set()
• 日期时间类型
  date datetime timestamp time year
• 日期时间函数
  NOW() CURDATE() YEAR(字段名) DATE(字段名) TIME(字段名)
• 日期时间运算
  select * from 表名 where 字段名 运算符 (NOW()-interval 间隔);
  间隔单位: 1 day | 3 month | 2 year
  e.g: 查询1年以前的用户充值信息
  select * from tab where time < (NOW() - interval 1 year)

6、MySQL运算符

• 数值比较

\> >= < <= = != 
eg1 : 查询成绩不及格的学生
     select * from students where score < 60; 
eg2 : 删除成绩不及格的学生
      delete from students where score < 60;
eg3 : 把id为3的学生的姓名改为 周芷若
      update students set name='周芷若' where id=3;

• 逻辑比较

and  or
eg1 : 查询成绩不及格的男生
  select * from students where score <60 and gender='male';
eg2 : 查询成绩在60-70之间的学生
  select * from students where score >=60 and score <= 70;

• 范围内比较

between 值1 and 值2 、in() 、not in()
eg1 : 查询不及格的学生姓名及成绩
  	select name, score from students where score between 0 and 59;
eg2 : 查询AID19和AID18班的学生姓名及成绩
  	select name,score from students where class  in('AID19', 'AID18');

• 模糊比较（like）

where 字段名 like 表达式(%_) % 代表0至多个字符 ，_ 代表一个字符
eg1 : 查询北京的姓赵的学生信息
  select * from students where address='北京' and name like '赵%';

• NULL判断

is NULL 、is not NULL
eg1 : 查询姓名字段值为NULL的学生信息
  select * from students where name is NULL;

7、查询

• order by
  给查询的结果进行排序(永远放在SQL命令的倒数第二的位置写)

order by 字段名 ASC/DESC
eg1 : 查询成绩从高到低排列
  select * from students order by score DESC;

• limit
  限制显示查询记录的条数（永远放在SQL命令的最后写）

limit n ：显示前n条
limit m,n ：从第(m+1)条记录开始，显示n条
分页：每页显示10条，显示第6页的内容
     limit (6-1)*10, 10
     
     每页显示 a 条， 显示第b页的内容
     limit (b-1)*a, a

MySQL高级-Day01

MySQL基础巩固

• 创建库 ：country（指定字符编码为utf8）

create database country CHARSET utf8;

• 创建表 ：sanguo 字段：id 、name、attack、defense、gender[M/F]、country
  ​要求 ：id设置为主键,并设置自增长属性
  id int primary key auto_increment

create table sanguo(id int primary key auto_increment,
name varchar(20),
attack int,
defense int,
gender char(1),
country VARCHAR(20))
CHARSET=utf8;

• 插入5条表记录（id 1-5,name-诸葛亮、司马懿、貂蝉、张飞、赵云），攻击>100,防御<100）

INSERT into sanguo values(NULL,'诸葛亮',160,60,'M','蜀国'),
(NULL,'司马懿',170,70,'M','魏国'),
(NULL,'貂蝉',180,80,'F','吴国'),
(NULL,'张飞',190,90,'M','蜀国'),
(NULL,'赵云',200,99,'M','蜀国');

• 查找所有蜀国人的信息

select * from sanguo where country='蜀国';

• 将赵云的攻击力设置为360,防御力设置为68

update sanguo set attack=360,defense=68 where name='赵云';

• 将吴国英雄中攻击值为110的英雄的攻击值改为100,防御力改为60

update sanguo set attack=100,defense=60 where country='吴国' and attack=110;

• 找出攻击值高于200的蜀国英雄的名字、攻击力

select name,attack from sanguo where attack>200 and country='蜀国';

• 将蜀国英雄按攻击值从高到低排序

select * from sanguo where country='蜀国' order by attack desc;

• 魏蜀两国英雄中名字为三个字的按防御值升序排列

select * from sanguo where country in ('魏国','蜀国') and name like '___' order by defense ASC;

• 在蜀国英雄中,查找攻击值前3名且名字不为 NULL 的英雄的姓名、攻击值和国家

select name,attack,country from sanguo where country='蜀国' and name is not NULL order by attack desc limit 3;

MySQL普通查询

3、select ...聚合函数 from 表名
1、where ...
2、group by ...
4、having ...
5、order by ...
6、limit ...;
(前面的数字为执行顺序)

• 聚合函数

方法	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计该字段记录的个数

eg1：找出表中的最大攻击力的值？

select max(attack) from sanguo;

eg2：表中共有多少个英雄？(as 后面可以起个别名，也可以不加as)

select count(name) as number from sanguo;

eg3：蜀国英雄中攻击值大于200的英雄的数量

select count(id) from sanguo where country='蜀国' and attack>200;

NULL统计不了

• group by

给查询的结果分组

eg1：计算每个国家的平均攻击力

select country,avg(attack) from sanguo group by country;

eg2：所有国家的男英雄中，英雄数量最多的前两名的国家名称及英雄数量

select country,count(id) as number from sanguo 
where gender='M' group by country 
order by number desc 
limit 2;

​ group by后字段名必须要为select后的字段
​ 查询字段和group by后字段不一致,则必须对该字段进行聚合处理(聚合函数)

• having语句

对分组聚合后的结果进行进一步筛选

eg1：找出平均攻击力大于105的国家的前2名，显示国家名称和平均攻击力

select country,avg(attack) from sanguo group by country having avg(attack)>105 order by avg(attack) desc limit 2;

注意

1. having语句通常与group by联合使用
2. having语句存在弥补了where关键字不能与聚合函数联合使用的不足，where只能操作表中实际存在的字段，having操作的是聚合函数生成的显示列

• distinct语句

不显示字段重复值

eg1 : 表中都有哪些国家

select distinct country from sanguo;

补充：去除当name和country字段完全相同时的两条数据

select distinct name,country from sanguo;

eg2 : 计算一共有多少个国家

select count(distinct country) from sanguo;

注意：

1. distinct和from之间所有字段都相同才会去重
2. distinct不能对任何字段做聚合处理

• 查询表记录时做数学运算

运算符：+ - * / % **

eg1：查询时显示攻击力翻倍

select name, attack*2 from sanguo;

eg2：更新蜀国所有英雄的攻击力 * 2

update sanguo set attack=attack*2 where country='蜀国';

嵌套查询（子查询）

• 定义
  把内层的查询结果作为外层的查询条件

• 语法格式

select ... from 表名 where 条件(select ....);

• 示例
  eg1.把攻击值小于平均攻击值的英雄名字和攻击值显示出来

select name,attack from sanguo where attack<(select avg(attack) from sanguo);

eg2.找出每个国家攻击力最高的英雄的名字和攻击值

select name,attack from sanguo where (country,attack) in(select country,max(attack) from sanguo group by country);

多表查询

1、笛卡尔积是如何匹配的
2、多表查询：在笛卡尔积的基础上进行进一步条件筛选

导入名为join_query.sql内的SQL语句

mysql -uroot -p123456
mysql>source /home/tarena/join_query.sql

也可以自己创建：

-- 如果db1不存在的话就创建
create database if not exists db1 charset utf8;
use db1

-- 如果province表不存在的话就创建
-- 表结构
create table if not exists province(
id int primary key auto_increment,
pid int,
pname varchar(15)
)default charset=utf8;

-- 向province中插入数据
insert into province values
(1, 130000, '河北省'),
(2, 140000, '陕西省'),
(3, 150000, '四川省'),
(4, 160000, '广东省'),
(5, 170000, '山东省'),
(6, 180000, '湖北省'),
(7, 190000, '河南省'),
(8, 200000, '海南省'),
(9, 200001, '云南省'),
(10,200002,'山西省');

-- 如果city表不存在的话就创建
create table if not exists city(
id int primary key auto_increment,
cid int,
cname varchar(15),
cp_id int
)default charset=utf8;

-- 向city中插入数据
insert into city values
(1, 131100, '石家庄市', 130000),
(2, 131101, '沧州市', 130000),
(3, 131102, '廊坊市', 130000),
(4, 131103, '西安市', 140000),
(5, 131104, '成都市', 150000),
(6, 131105, '重庆市', 150000),
(7, 131106, '广州市', 160000),
(8, 131107, '济南市', 170000),
(9, 131108, '武汉市', 180000),
(10,131109, '郑州市', 190000),
(11,131110, '北京市', 320000),
(12,131111, '天津市', 320000),
(13,131112, '上海市', 320000),
(14,131113, '哈尔滨', 320001),
(15,131114, '雄安新区', 320002);

-- 如果county表不存在的话就创建
create table if not exists county(
id int primary key auto_increment,
coid int,
coname varchar(15),
copid int
)default charset=utf8;

-- 向county 中插入数据
insert into county values
(1, 132100, '正定县', 131100),
(2, 132102, '浦东新区', 131112),
(3, 132103, '武昌区', 131108),
(4, 132104, '哈哈', 131115),
(5, 132105, '安新县', 131114),
(6, 132106, '容城县', 131114),
(7, 132107, '雄县', 131114),
(8, 132108, '嘎嘎', 131115);

• 笛卡尔积

select 字段名列表 from 表名列表；

e.g.

-- 方式一
select * from tt1,tt2;
-- 方式二（表名.字段名）
select tt1.tt1name,tt2.tt2name from tt1,tt2;

• 多表查询

select 字段名列表 from 表名列表 where 条件;

• 示例

1. 显示省和市的详细信息
   河北省 石家庄市
   河北省 廊坊市
   湖北省 武汉市

select province.pname , city.cname from province,city 
where province.pid = city.cp_id;

2. 显示 省 市 县 详细信息

select province.pname,city.cname,county.coname from province,city,county 
where province.pid=city.cp_id and city.cid=county.copid;

连接查询

全程重点，整体分类，具体语法实现

• 内连接（结果同多表查询，显示匹配到的记录【重点】）

select 字段名 from  表1 
inner join 表2 on 条件 
inner join 表3 on 条件
...;

eg1 : 显示省市详细信息

select province.pname, city.cname from province 
inner join city on province.pid = city.cp_id;

eg2 : 显示 省 市 县 详细信息

select province.pname, city.cname, county.coname from province 
inner join city on province.pid = city.cp_id 
inner join county on city.cid = county.copid;

• 左外连接
  以 左表(表1) 为主显示查询结果

select 字段名 from 表1 
left join 表2 on 条件 
left join 表3 on 条件
...;

eg1 : 显示 省 市 详细信息（要求省全部显示）

select province.pname, city.cname from province 
left join city on province.pid = city.cp_id;

• 右外连接
  用法同左连接，以 右表 为主显示查询结果

select 字段名 from 表1 
right join 表2 on 条件 
right join 表3 on 条件
...;

eg1 : 显示 省 市 详细信息（要求市全部显示）

select province.pname, city.cname from province 
right join city on province.pid = city.cp_id;

索引概述

• 定义
  对数据库表的一列或多列的值进行排序的一种结构(Btree方式)

• 优点
  加快数据检索速度

• 缺点

1. 占用物理内存空间（/var/lib/mysql）
2. 当对表中数据更新时，索引需要动态维护，降低数据维护速度

• 索引示例

# cursor.executemany(SQL,[data1,data2,data3])
# 一次IO执行多条表记录操作，效率高，节省资源

1. 开启运行时间检测
   mysql> show variables like ‘%pro%’;
   mysql> set profiling=1;
2. 执行查询语句(无索引)
   mysql> select name from students where name=‘Tom99999’;
3. 查看执行时间
   mysql> show profiles;
4. 在name字段创建索引（给字段名创建索引 create index 索引名 on 表名(字段名)）
   mysql> create index name on students(name);
5. 再执行查询语句
   mysql> select name from students where name=‘Tom88888’;
6. 查看执行时间
   mysql> show profiles;

索引分类

普通(MUL) and 唯一(UNI)

• 使用规则

1. 可设置多个字段
2. 普通索引（index）：字段值无约束，KEY标志为 MUL
3. 唯一索引（unique）：字段值不允许重复，但可为 NULL，KEY标志为 UNI
4. 哪些字段创建索引：经常用来查询的字段、where条件判断字段、order by排序字段

• 创建普通索引and唯一索引

创建表时

create table 表名(
字段名 数据类型，
字段名 数据类型，
index(字段名),
index(字段名),
unique(字段名)
);

eg. 创建表AID1903tab，其中name字段为普通索引，phone为唯一索引

create table AID1903tab(
id int,
name varchar(20),
phone char(11),
index(name),
unique(phone)
)charset=utf8;

已有表中创建

create [unique] index 索引名 on 表名(字段名);

• 查看索引

1、desc 表名;  --> KEY标志为：MUL 、UNI
2、show index from 表名\G;

• 删除索引（一个一个删）

drop index 索引名 on 表名;

主键（PRI）and自增长（auto_increment）

• 使用规则

1. 只能有一个主键字段
2. 所带约束：不允许重复，且不能为NULL
3. KEY标志(primary) ：PRI
4. 通常设置记录编号字段id，能唯一锁定一条记录

• 创建

创建表添加主键

方式一：

create table student(
id int primary key auto_increment
)charset=utf8;

方式二：

create table student(
id int auto_increment,
name varchar(20),
primary key(id)
)charset=utf8,auto_increment=10000;##设置自增长起始值(默认为1)

已有表添加主键

alter table 表名 add primary key(id);

已有表操作自增长属性

1、已有表添加自增长属性
  alter table 表名 modify id int auto_increment;
2、已有表重新指定起始值：
  alter table 表名 auto_increment=20000;

• 删除

1、先删除自增长属性(modify)
  alter table 表名 modify id int;
2、再删除主键索引
  alter table 表名 drop primary key;

外键（foreign key）

1、原理
2、级联动作，以及每个级联动作特点

• 定义

让当前表字段的值在另一个表的范围内选择

• 语法

foreign key(参考字段名)
references 主表(被参考字段名)
on delete 级联动作
on update 级联动作

• 使用规则

1. 主表、从表字段数据类型要一致
2. 主表被参考字段 ：KEY的一种，一般为主键

• 示例

表1、缴费信息表（财务）

id	姓名	班级	缴费金额
1	唐伯虎	AID19	300
2	点秋香	AID19	300
3	祝枝山	AID19	300

建表语句：

-- 新建一个库
create database db2 charset=utf8;
use db2;
-- 表结构
create table master(
id int primary key auto_increment,
name varchar(20),
class char(7),
money decimal(10,2)
)charset=utf8;
-- 插入数据
insert into master values(NULL,'唐伯虎','AID19',300),(NULL,'点秋香','AID19',300),(NULL,'祝枝山','AID19',300);

表2、学生信息表（班主任）-- 做外键关联

stu_id	姓名	缴费金额
1	唐伯虎	300
2	点秋香	300
3	祝枝山	300

建表语句：

-- 表结构
create table slave(
stu_id int,
name varchar(20),
money decimal(10,2),
foreign key(stu_id) references master(id)
on delete cascade 
on update cascade
)charset=utf8;

-- 插入数据
insert into slave values(1,'唐伯虎',300),(2,'点秋香',300); 

主表数据删除，从表对应数据也删除(如果更新的话，只能更新参考字段和被参考字段)

delete from master where id=3;

主表：

从表：

• 删除外键

alter table 表名 drop foreign key 外键名;
外键名 ：show create table 表名;

eg. 删除外键

-- 查找外键名（比如：slave_ibfk_1）
show create table slave;
-- 删除外键
alter table slave drop foreign key slave_ibfk_1;

eg.添加外键

-- 默认restrict
alter table slave add foreign key(stu_id)
references master(id);

• 级联动作

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

restrict(默认)
从表有相关联记录,不允许主表操作

set null
主表删除、更新,从表相关联记录字段值为NULL

• 已有表添加外键

alter table 表名 add foreign key(参考字段) 
references 主表(被参考字段) 
on delete 级联动作 
on update 级联动作

MySQL高级-Day02

数据导入

掌握大体步骤
source 路径/文件名.sql

作用

把文件系统的内容导入到数据库中

语法（方式一）

load data infile “文件名”
into table 表名
fields terminated by “分隔符”
lines terminated by “\n”

示例

将scoretable.csv文件导入到数据库db2的表

1. 将scoretable.csv放到数据库搜索路径中
   mysql> show variables like ‘secure_file_priv’;
   搜索路径： /var/lib/mysql-files/
   Linux: sudo cp /home/tarena/scoreTable.csv /var/lib/mysql-files/
   ls -l scoreTable.csv 查看权限（-rw-rw-rw- ：root所有者 root所属组 其他组的其他用户）
   chmod 666 scoreTable.csv 赋予文件读权限

2. 在数据库中创建对应的表

 create table scoretab(
 rank int,
 name varchar(20) not null,
 score float(5,2),
 phone char(11),
 class char(7)
 )charset=utf8;

3. 执行数据导入语句

mysql> load data infile ‘/var/lib/mysql-files/scoreTable.csv’
-> into table scoretab
-> fields terminated by ‘,’
-> lines terminated by ‘\n’;

补充练习：添加id字段，要求主键自增长，显示宽度为3，位数不够用0填充

alter table scoretab add id int(3) zerofill primary key auto_increment first;

语法（方式二）

source 路径/文件名.sql

数据导出

====

作用

将数据库中表的记录保存到系统文件里

语法格式

select … from 表名
into outfile “文件名”
fields terminated by “分隔符”
lines terminated by “分隔符”;

练习

1. 把sanguo表中英雄的姓名、攻击值和国家三个字段导出来,放到 sanguo.csv中

select name,attack,country from country.sanguo
into outfile '/var/lib/mysql-files/sanguo.csv'
fields terminated by ','
lines terminated by '\n';

2. 将mysql库下的user表中的 user、host两个字段的值导出到 user2.txt，将其存放在数据库目录下

select user,host from mysql.user
into outfile '/var/lib/mysql-files/user2.txt'
fields terminated by ','
lines terminated by '\n'

注意

1. 导出的内容由SQL查询语句决定
2. 执行导出命令时路径必须指定在对应的数据库目录下

表的复制

1、表能根据实际需求复制数据
2、复制表时不会把KEY属性复制过来
语法

create table 表名 select 查询命令;

练习

1. 复制sanguo表的全部记录和字段，sanguo2

create table sanguo2 select * from country.sanguo;

2. 复制sanguo表的前3条记录，sanguo3

create table sanguo3 select * from country.sanguo limit 3;

3. 复制sanguo表的 id,name,country 三个字段的前3条记录,sanguo4

create table sanguo4 select id,name,country from country.sanguo limit 3;

注意

复制表的时候不会把原有表的 KEY 属性(主键、外键···)复制过来

复制表结构

create table 表名 select 查询命令 where false; 

锁（自动加锁和释放锁，重点）

全程重点，理论和锁分类及特点
目的

解决客户端并发访问的冲突问题

锁类型分类

1. 读锁(共享锁)：select 加读锁之后别人不能更改表记录，但可以进行查询
2. 写锁(互斥锁、排他锁)：加写锁之后别人不能查、不能改

锁粒度分类

表级锁 ：myisam
行级锁 ：innodb(支持外键)

MySQL高级-Day03

存储引擎

定义

处理表的处理器

基本操作

1. 查看所有存储引擎

mysql> show engines;

2. 查看已有表的存储引擎

mysql> show create table 表名;

3. 创建表指定

create table 表名(...)engine=MyISAM,charset=utf8,auto_increment=10000;

4. 已有表指定

alter table 表名 engine=InnoDB;

常用存储引擎及特点

• InnoDB

1. 支持行级锁
2. 支持外键、事务、事务回滚
3. 表字段和索引同存储在一个文件中（/var/lib/mysql/表名）
   【1】表名.frm：表结构及索引文件
   【2】表名.ibd： 表记录

• MyISAM

1. 支持表级锁
2. 表字段和索引分开存储
   【1】表名.frm ：表结构
   【2】表名.MYI : 索引文件(my index)
   【3】表名.MYD : 表记录(my data)

• MEMORY

1. 表记录存储在内存中，效率高
2. 服务或主机重启，表记录清除

如何选择存储引擎

1. 执行查操作多的表用 MyISAM（使用InnoDB浪费资源）
   【原因】：当需要对表执行多次查询时，不希望其他人对表内记录进行更改，因此需要加读锁。如果使用InnoDB只能加行级锁，加锁的效率没有MyISAM加表级锁的效率高。
2. 执行写操作多的表用 InnoDB
3. 临时表，表内数据不需要保留 ： MEMORY

MySQL的用户账户管理

开启MySQL远程连接

更改配置文件，重启服务！

1、sudo -i
2、cd /etc/mysql/mysql.conf.d
3、cp mysqld.cnf mysqld.cnf.bak 保留一份原始配置文件，如果改坏了可以把文件名中的.bak删掉，恢复原始文件（备份）
4、vi mysqld.cnf  # 找到绑定地址，加 # 注释
   # bind-address = 127.0.0.1
   [mysqld]
   character_set_server = utf8
5、保存退出 :wq!
6、service mysql restart

vi使用：按a或者i编辑文件 --> ESC 退出编辑模式--> :wq! 保存、退出、强制执行

添加授权用户

1. 用root用户登录mysql

mysql -uroot -p123456

2. 授权

mysql> grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;

%表示任何地址都可以登录数据库

3. 刷新权限

 mysql> flush privileges;

权限列表

all privileges 、select 、insert … （多个权限时中间用，隔开）
库.表 ： * . * 代表所有库的所有表

示例

1. 添加授权用户work，密码123，对所有库的所有表有所有权限

mysql> grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;
mysql> flush privileges;

2. 添加用户duty，对db2库中所有表有所有权限

mysql> grant all privileges on db2.* to 'duty'@'%' identified by '123456' with grant option;
mysql> flush privileges;

事务和事务回滚

事务定义

一件事从开始发生到结束的过程

作用

确保数据的一致性、准确性、有效性

事务操作

1. 开启事务(在终止事务之前，所有SQL命令都不会提交到数据库执行)

mysql> begin; # 方法1
mysql> start transaction; # 方法2

2. 开始执行事务中的1条或者n条SQL命令
3. 终止事务

mysql> commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
mysql> rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

事务四大特征（ACID）

• 1、原子性（atomicity）
  一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。

• 2、一致性（consistency）
  数据库总是从一个一致性的状态转换到另一个一致性的状态。

• 3、隔离性（isolation）
  一个事务所做的修改在最终提交以前，对其他事务是不可见的。

• 4、持久性（durability）
  一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

注意

1. 事务只针对于表记录操作(增删改)有效，对于库和表的操作（如新建库或者新建表等）无效。
2. 事务一旦提交结束，对数据库中数据的更改是永久性的。

E-R模型（Entry-Relationship）

定义

1. E-R模型即 实体-关系 数据模型，用于数据库设计
2. 用简单的图（E-R图）反映了现实世界中存在的事物或数据以及他们之间的关系

实体、属性、关系

• 实体（就是一张表）

1. 描述客观事物的概念
2. 表示方法 ：矩形框
3. 示例 ：一个人、一本书、一杯咖啡、一个学生

• 属性（表中的属性）

1. 实体具有的某种特性
2. 表示方法 ：椭圆形
3. 示例
   学生属性 ：学号、姓名、年龄、性别、专业 …
   感受属性 ：悲伤、喜悦、刺激、愤怒 …

• 关系（重要）

1. 实体之间的联系
2. 表示方法：菱形框
3. 一对一关联(1:1)：老公对老婆
   A中的一个实体，B中只能有一个实体与其发生关联
   B中的一个实体，A中只能有一个实体与其发生关联
4. 一对多关联(1:n)：父亲对孩子
   A中的一个实体，B中有多个实体与其发生关联
   B中的一个实体，A中只能有一个与其发生关联
5. 多对多关联(m:n)：兄弟姐妹对兄弟姐妹、学生对课程
   A中的一个实体，B中有多个实体与其发生关联
   B中的一个实体，A中有多个实体与其发生关联

ER图的绘制

矩形框代表实体，菱形框代表关系，椭圆形代表属性

• 课堂示例（老师研究课题）

1. 实体 ：教师、课题
2. 属性
   教师 ：教师代码、姓名、职称
   课题 ：课题号、课题名
3. 关系
   多对多（m:n)
   #一个老师可以选择多个课题，一个课题也可以被多个老师选

• 练习

设计一个学生选课系统的E-R图

1、实体：学生、课程、老师
2、属性
3、关系

• 学生 选择 课程（m:n）
• 课程 任课 老师（1:n）

关系映射实现（重要）

1. 1:1 实现 --> 主外键关联，外键字段添加唯一索引

表t1: id int primary key,
表t2: t2_id int unique,
	foreign key(t2_id) references t1(id)

2. 1:n 实现 --> 主外键关联

表t1: id int primary key,
表t2: t2_id int,
	foreign key(t2_id) references t1(id)

3. m:n 实现（借助中间表）：
   表t1：t1_id
   表t2：t2_id

多对多实现

• 老师研究课题

表1、老师表

CREATE TABLE teacher(
id int PRIMARY KEY ,
tname VARCHAR(20),
level VARCHAR(20)
)CHARSET=utf8;

INSERT INTO teacher VALUES(1,'程序员姐姐','牛掰'),(2,'超哥哥','司机');

表2、课题表

CREATE TABLE course(
id int PRIMARY KEY ,
cname VARCHAR(20),
score int
)CHARSET=utf8;

INSERT INTO course VALUES(1,'HTML',5),(2,'spider',5),(3,'MySQL',5);

问题：如何实现老师和课程之间的多对多映射关系？-- 引入中间表

create TABLE middle(
id int PRIMARY KEY,
tid int,
cid int,
foreign key(tid) REFERENCES teacher(id),
foreign key(cid) REFERENCES course(id)
)charset=utf8;

INSERT INTO middle VALUES(1,1,1),(2,1,3),(3,2,1),(4,2,2),(5,2,3);

• 后续

1. 每个老师都在研究什么课题？

select teacher.tname,course.cname from teacher 
inner join middle on teacher.id=middle.tid
inner join course on course.id=middle.cid; 

2. 程序员姐姐在研究什么课题？

select teacher.tname,course.cname from teacher 
inner join middle on teacher.id=middle.tid
inner join course on course.id=middle.cid
where teacher.tname='程序员姐姐'; 

MySQL调优

存储引擎优化

1. 读操作多：MyISAM
2. 写操作多：InnoDB

索引优化

• 在 select、where、order by 常涉及到的字段建立索引

SQL语句优化

1. 单条查询最后添加 LIMIT 1，停止全表扫描

2. where子句中不使用 != ,否则放弃索引全表扫描

3. 尽量避免 NULL 值判断,否则放弃索引全表扫描
   优化前：select number from t1 where number is null;
   优化后：select number from t1 where number=0;

   在number列上设置默认值0，确保number列无NULL值

4. 尽量避免 or 连接条件,否则放弃索引全表扫描
   优化前：select id from t1 where id=10 or id=20;
   优化后： select id from t1 where id=10 union all select id from t1 where id=20;

5. 模糊查询尽量避免使用前置 % ,否则全表扫描
   select name from t1 where name like "c%";

6. 尽量避免使用 in 和 not in,否则全表扫描
   优化前：select id from t1 where id in(1,2,3,4);
   优化后：select id from t1 where id between 1 and 4;

7. 尽量避免使用select * ...;用具体字段代替 * ,不要返回用不到的任何字段

补充：Ubuntu下重装MySQL（彻底卸载再安装）

1. 删除mysql

sudo apt-get autoremove --purge mysql-server-5.5
sudo apt-get remove mysql-common

2. 清理残留数据

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P 

3. 重新安装mysql

sudo apt-get install mysql-server
sudo apt-get install mysql-client

作业讲解

1、有一张文章评论表comment如下：

comment_id	article_id	user_id	date
1	10000	10000	2018-01-30 09:00:00
2	10001	10001	… …
3	10002	10000	… …
4	10003	10015	… …
5	10004	10006	… …
6	10025	10006	… …
7	10009	10000	… …

以上是一个应用的comment表格的一部分，请使用SQL语句找出在本站发表的所有评论数量最多的10位用户及评论数，并按评论数从高到低排序

备注：

• comment_id为评论id
• ​article_id为被评论文章的id
• user_id 指用户id

答案：

select user_id, count(user_id) from comment group by user_id order by count(user_id) desc limit 10;

2、把 /etc/passwd 文件的内容导入到数据库的表中
tarena: x:1000:1000:tarena, , ,: /home/tarena:/bin/bash

1、拷贝文件
	sudo cp /etc/passwd /var/lib/mysql-files
2、建表
	create table user(
	username varchar(20),
	password char(1),
	uid int,
	gid int,
	comment varchar(50),
	homedir varchar(100),
	shell varchar(50)
	)charset=utf8;
3、导入
	load data infile '/var/lib/mysql-files/passwd'
	into table user
	fields terminated by ':'
	lines terminated by '\n';

3、外键及查询题目

综述：两张表，一张顾客信息表customers，一张订单表orders

表1：顾客信息表，完成后插入3条表记录

c_id 类型为整型，设置为主键，并设置为自增长属性
c_name 字符类型，变长，宽度为20
c_age 微小整型，取值范围为0~255(无符号)
c_sex 枚举类型，要求只能在(‘M’,‘F’)中选择一个值
c_city 字符类型，变长，宽度为20
c_salary 浮点类型，要求整数部分最大为10位，小数部分为2位

create table customers(
c_id int primary key auto_increment,
c_name varchar(20),
c_age tinyint unsigned,
c_sex enum('M','F'),
c_city varchar(20),
c_salary decimal(12,2)
)charset=utf8;

insert into customers values(1,'Tom',25,'M','上海',10000),(2,'Lucy',23,'F','广州',12000),(3,'Jim',22,'M','北京',11000);

表2：顾客订单表（在表中插入5条记录）

o_id 整型
o_name 字符类型，变长，宽度为30
o_price 浮点类型，整数最大为10位，小数部分为2位
设置此表中的o_id字段为customers表中c_id字段的外键,更新删除同步

create table orders(
o_id int,
o_name varchar(30),
o_price decimal(12,2),
foreign key(o_id) references customers(c_id) 
on delete cascade
on update cascade 
)charset=utf8;

insert into orders values(1,"iphone",5288),(1,"ipad",3299),(2,"iwatch",2222),(2,"r11",4400);

增删改查题

1. 返回customers表中，工资大于4000元，或者年龄小于29岁，满足这样条件的前2条记录

select * from customers where c_salary>4000 or c_age<29 limit 2; 

2. 把customers表中，年龄大于等于25岁，并且地址是北京或者上海，这样的人的工资上调15%

update customers set c_salary=c_salary*1.15 where c_age>=25 and c_city in('北京','上海');

3. 把customers表中，城市为北京的顾客，按照工资降序排列，并且只返回结果中的第一条记录

select * from customers where c_city='北京' order by c_salary DESC limit 1;

4. 选择工资c_salary最少的顾客的信息

select * from customers where c_salary=(select min(c_salary) from customers);

5. 找到工资大于5000的顾客都买过哪些产品的记录明细

select * from orders where o_id in(select c_id from customers where c_salary>5000);

6. 删除外键限制

• show create table orders;
• alter table orders drop foreign key 外键名;

7. 删除customers主键限制

• alter table customers modify id int;
• alter table customers drop primary key;

8. 增加customers主键限制c_id

• alter table customers add primary key(c_id);