Mysql数据库经验总结

大强哥666

已于 2023-03-24 11:28:43 修改

阅读量846

点赞数 1

分类专栏： python 数据库的使用 mysql 文章标签： mysql sql 数据库 python linux

于 2021-03-14 17:33:19 首次发布

本文链接：https://blog.csdn.net/liuzhiqiang66/article/details/114794120

版权

数据库的使用同时被 3 个专栏收录

12 篇文章 0 订阅

订阅专栏

python

7 篇文章 1 订阅

订阅专栏

mysql

4 篇文章 0 订阅

订阅专栏

MySQL数据库

数据库概述

EBA数据库管理员 .

数据存储阶段

【1】人工管理阶段

缺点：数据无法共享,不能单独保持,数据存储量有限

【2】文件管理阶段（.txt .doc .xls）
优点：数据可以长期保存,可以存储大量的数据,使用简单

缺点：数据一致性差,数据查找修改不方便,数据冗余度可能比较大

【3】数据库管理阶段

优点：数据组织结构化降低了冗余度,提高了增删改查的效率,容易扩展,方便程序调用，做自动化处理

缺点：需要使用sql 或者其他特定的语句，相对比较复杂

数据库

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

数据库软件

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

数据仓库

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

数据库应用

融机构、游戏网站、购物网站、论坛网站 … …

基础概念

数据：能够输入到计算机中并被识别处理的信息集合

数据结构：研究一个数据集合中数据之间关系的

数据库：按照数据结构，存储管理数据的仓库。数据库是在数据库管理系统管理和控制下，在一定介质上的数据集合。

数据库管理系统：管理数据库的软件，用于建立和维护数据库

数据库系统：由数据库和数据库管理系统，开发工具等组成的集合

数据库分类和常见数据库

关系型数据库和非关系型数据库

关系型：采用关系模型（二维表）来组织数据结构的数据库

非关系型：不采用关系模型组织数据结构的数据库

开源数据库和非开源数据库

开源：MySQL、SQLite、MongoDB

非开源：Oracle、DB2、SQL_Server

常见的关系型数据库

MySQL、Oracle、SQL_Server、DB2、SQLite

认识关系型数据库和MySQL

数据库结构

数据元素 --> 记录 -->数据表 --> 数据库

在这里插入图片描述

数据库概念解析

数据表（table）：存放数据的表格

字段（column）：每个列，用来表示该列数据的含义

记录（row）：每个行，表示一组完整的数据

在这里插入图片描述

MySQL特点

是开源数据库，使用C和C++编写的关系型数据库
能够工作在众多不同的平台上即跨平台
提供了用于C、C++、Python、Java、Perl、PHP、Ruby众多语言的API
存储结构优良，运行速度快
功能全面丰富
基于磁盘存储，数据是以文件形式存放在数据库目录 /var/lib/mysql 下

MySQL安装

Ubuntu安装MySQL服务

安装服务端: sudo apt-get install mysql-server
安装客户端: sudo apt-get install mysql-client

配置文件：/etc/mysql
命令集： /usr/bin
数据库存储目录：/var/lib/mysql

Windows安装MySQL

下载MySQL安装包(windows) https://dev.mysql.com/downloads/mysql/
mysql-installer5.7..msi
安装教程去安装

启动和连接MySQL服务

服务端启动

查看MySQL状态: sudo /etc/init.d/mysql status

启动服务：sudo /etc/init.d/mysql start | stop | restart | status

sudo service mysql start | stop | restart | status

客户端连接

命令格式

mysql -h主机地址 -u用户名 -p密码
mysql -hlocalhost -uroot -p123456
本地连接可省略 -h 选项: mysql -uroot -p123456

关闭连接

ctrl-D
exit

SQL语句

什么是SQL

结构化查询语言(Structured Query Language)，一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL语句使用特点

SQL语言基本上独立于数据库本身
各种不同的数据库对SQL语言的支持与标准存在着细微的不同
每条命令必须以 ; 结尾
SQL命令关键字不区分字母大小写

建立数据库和数据表

数据库操作

1.查看已有库

show databases;

2.创建库

create database 库名 [character set utf8];

e.g. 创建stu数据库，编码为utf8
create database stu character set utf8;
create database stu charset=utf8;

3.查看创建库的语句

show create database 库名;

e.g. 查看stu创建方法
show create database stu;

4.查看当前所在库

select from database();

5.切换库

use 库名;

e.g. 使用stu数据库
use stu;

6.删除库

drop database 库名;

e.g. 删除test数据库
drop database test;

7.库名的命名规则

数字、字母、下划线,但不能使用纯数字
库名区分字母大小写
不能使用特殊字符和mysql关键字

数据表的管理

表结构设计初步

【1】分析存储内容
【2】确定字段构成
【3】设计字段类型

数据类型支持

数字类型：

整数类型（精确值） - INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT
定点类型（精确值） - DECIMAL
浮点类型（近似值） - FLOAT，DOUBLE
比特值类型(0或1) - BIT

在这里插入图片描述

对于精度比较高的东西，比如money，用decimal类型提高精度减少误差。列的声明语法是DECIMAL(M,D)。

M是数字的最大位数（精度）。其范围为1～65，M 的默认值是10。
D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。
比如 DECIMAL(6,2)最多存6位数字，小数点后占2位,取值范围-9999.99到9999.99。

比特值类型指0，1值表达2种情况，如真，假

字符串类型：

CHAR和VARCHAR类型
BLOB和TEXT类型
ENUM类型和SET类型

在这里插入图片描述

char 和 varchar

char：定长，效率高，一般用于固定长度的表单提交数据存储，默认1字符
varchar：不定长，效率偏低，但是节省空间。

text 和blob

text用来存储非二进制文本
blob用来存储二进制字节串

enum 和 set

enum用来存储给出的一个值
set用来存储给出的值中一个或多个值

表的基本操作

创建表(指定字符集)

create table 表名(
字段名数据类型,
字段名数据类型,
…
字段名数据类型
);

如果你想设置数字为无符号则加上 unsigned
如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
DEFAULT 表示设置一个字段的默认值
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY 关键字用于定义列为主键。主键的值不能重复,且不能为空。

e.g.  创建班级表
create table class_1 (id int primary key auto_increment,name varchar(32) not null,age int unsigned not null,sex enum('w','m'),score float default 0.0);

e.g. 创建兴趣班表
create table interest (id int primary key auto_increment,name varchar(32) not null,hobby set('sing','dance','draw'),level char not null,price decimal(6,2),remark text);

查看数据表

show tables；

查看已有表的字符集

show create table 表名;

查看表结构

desc 表名;

删除表

drop table 表名;

数据操作基础

表记录管理

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

4 、查：select 字段名 from 表名 where 条件

插入(insert)

insert into 表名 values(值1),(值2),...;
insert into 表名(字段1,...) values(值1),...;

e.g. 
insert into class_1 values (2,'Baron',10,'m',91),(3,'Jame',9,'m',90);

查询(select)

select * from 表名 [where 条件];
select 字段1,字段名2 from 表名 [where 条件];

e.g. 
select * from class_1;#查看数据表 全部内容

select name,age from class_1;

where子句

where子句在sql语句中扮演了重要角色，主要通过一定的运算条件进行数据的筛选

MySQL 主要有以下几种运算符：

算术运算符
比较运算符
逻辑运算符
位运算符

算数运算符

在这里插入图片描述

e.g.
select * from class_1 where age % 2 = 0;

比较运算符

在这里插入图片描述

e.g.
select * from class_1 where age > 8;
select * from class_1 where between 8 and 10;
select * from class_1 where age in (8,9);
delete from class_1 where score<60;
update class_1 set name=’小明’  where id=3;

逻辑运算符

在这里插入图片描述

e.g.
select * from class_1 where sex='m' and age>9;
select * from class_1 where score>=60 and score<=70;

位运算符

在这里插入图片描述

更新表记录(update)

update 表名 set 字段1=值1,字段2=值2,... where 条件;

e.g.
update class_1 set age=11 where name='Abby';

###　删除表记录（delete）

delete from 表名 where 条件;

注意:delete语句后如果不加where条件,所有记录全部清空

e.g.
delete from class_1 where name='Abby';

表字段管理（alter table 表名）

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

表字段的操作(alter)

语法 ：alter table 表名 执行动作;

* 添加字段(add)
    alter table 表名 add 字段名 数据类型;
    alter table 表名 add 字段名 数据类型 first;
    alter table 表名 add 字段名 数据类型 after 字段名;
* 删除字段(drop)
    alter table 表名 drop 字段名;
* 修改数据类型(modify)
    alter table 表名 modify 字段名 新数据类型;
* 修改字段名(change)
    alter table 表名 change 旧字段名 新字段名 新数据类型;
* 表重命名(rename)
    alter table 表名 rename 新表名;

e.g. 
alter table interest add tel char(11) after name;

时间类型数据

时间和日期类型:

日期DATE，日期时间DATETIME，时间戳TIMESTAMP
时间TIME
年份YEAR

在这里插入图片描述

时间格式

date ：“YYYY-MM-DD”
time ：“HH:MM:SS”
datetime ：“YYYY-MM-DD HH:MM:SS”
timestamp ：“YYYY-MM-DD HH:MM:SS”
注意
1、datetime ：以系统时间存储
2、timestamp ：以标准时间存储但是查看时转换为系统时区，所以表现形式和datetime相同

e.g.
create table marathon (id int primary key auto_increment,athlete varchar(32),birthday date,registration_time datetime,performance time);

日期时间函数

now() 返回服务器当前日期时间,格式对应datetime类型
curdate() 返回当前日期，格式对应date类型
curtime() 返回当前时间，格式对应time类型

时间操作

查找操作

  select * from marathon where birthday>='2000-01-01';
  select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

日期时间运算
- 语法格式
  
  select * from 表名 where 字段名运算符 (时间-interval 时间间隔单位);
- 时间间隔单位： 2 hour | 1 minute | 2 second | 2 year | 3 month | 1 day

  select * from marathon where registration_time > (now()-interval 7 day);

高级查询语句

模糊查询和正则查询

LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号 %来表示任意0个或多个字符，下划线_表示任意一个字符。

使用 LIKE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1

e.g. 
mysql> select * from class_1 where name like 'A%';

mysql中对正则表达式的支持有限，只支持部分正则元字符

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 REGEXP condition1

e.g. 
select * from class_1 where name regexp '^B.+';

NULL判断

is NULL 、is not NULL

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

as 用法

在sql语句中as用于给字段或者表重命名

select name as 姓名,age as 年龄 from class_1;
select * from class_1 as c where c.age > 17;

排序

ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN from table_name1 where field1
ORDER BY field1 [ASC [DESC]]

默认情况ASC表示升序，DESC表示降序

select * from class_1 where sex='m' order by age;

limit分页(限制)

LIMIT 子句用于限制由 SELECT 语句返回的数据数量或者 UPDATE,DELETE语句的操作数量（永远放在SQL命令的最后写）

带有 LIMIT 子句的 SELECT 语句的基本语法如下：

limit n ：显示前 n 条
limit m,n ：从第(m+1)条记录开始，显示n条

例如：分页：每页显示10条，显示第6页的内容
limit (6-1)*10,10;
每页显示a条，显示第b页的内容
limit (b-1)*a,a;

SELECT column1, column2, columnN 
FROM table_name
WHERE field
LIMIT [num]

联合查询

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

UNION 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

expression1, expression2, … expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

select * from class_1 where sex='m' UNION ALL select * from class_1 where age > 9;

子查询

定义：当一个select语句中包含另一个select 查询语句（即：把内层的查询结果作为外层的查询条件），则称之为有子查询的语句

语法格式
select ... from 表名 where 条件(select ....);
子查询出现的位置：

from 之后，此时子查询的内容作为一个新的表内容，再进行外层select查询

select name from (select * from class_1 where sex='m') as s where s.score > 90;

需要将子查询结果集重命名一下，方便where子句中的引用操作

where字句中，此时select查询到的内容作为外层查询的条件值

 select *  from class_1 where age = (select age from class_1 where name='Tom');

子句返回的结果需要一个明确值，即需要时某个记录的某个元素，不能是多行或者多列

示例（结合下面三国表）

1、把攻击值小于平均攻击值的英雄名字和攻击值显示出来
  select name, attack from sanguo where attack < (select avg(attack) from sanguo)
2、找出每个国家攻击力最高的英雄的名字和攻击值(子查询)
  select name, attack from sanguo where (country, attack) in(select country ,max(attack) from sanguo group by country)

以下为mysql基础总结

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

create database country charset utf8 ;

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

create table sanguo(id int primary key auto_increment,name char(32),attack int,defense int,gemder char(32),country char(32));

插入 5条表记录（id 1-5,name-诸葛亮、司马懿、貂蝉、张飞、赵云）

insert into sanguo values(null,'诸葛亮',150,30,'男','蜀国');
insert into sanguo values(null,'曹操',180,120,'男','魏国');

查找所有蜀国人的信息

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

将曹操的攻击力设置为 360,防御力设置为 68;

update sanguo set attack=260,defense=180 where name='曹操';

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

update sanguo set attack=100,defense=90 where attack=80 and country='吴国';

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

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

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

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

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

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普通查询

1、select ...聚合函数 from 表名
2、where ...
3、group by ...
4、having ...
5、order by ...
6、limit ...;

聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，在认识聚合之前先看一个更完整的sql语句

select语句执行顺序

(7)     SELECT 
(8)     [DISTINCT] <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

聚合函数（铁三角之一）

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

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

select max(attack) from sanguo;

eg2 : 表中共有多少个英雄？

select count(name) as number from sanguo;

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

select count(id) from sanguo where attack > 200;

注意： select 后只能写聚合函数，无法查找其他字段。

聚合分组（铁三角之二）

group by

给查询的结果进行分组
e.g. : 计算每个国家的平均攻击力

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

e.g. : 对多个字段创建索引，此时多个字段都相同时为一组

select age,sex,count(*) from class1 group by age,sex;

e.g. : 所有国家的男英雄中英雄数量最多的前2名的国家名称及英雄数量

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后字段不一致,则必须对该字段进行聚合处理(聚合函数)

group by也可以同时依照多个字段分组，如group by A，B 此时必须A,B两个字段值均相同才算一组。

聚合筛选（铁三角之三）

having语句

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

eg1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力
select country,avg(attack) from sanguo 
group by country
having avg(attack)>105
order by avg(attack) DESC
limit 2;

注意

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

去重语句

distinct语句

不显示字段重复值

eg1 : 表中都有哪些国家
  select distinct name,country from sanguo;
eg2 : 计算一共有多少个国家
  select count(distinct country) from sanguo;

注意

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

聚合运算

查询表记录时做数学运算

运算符： + - * / % **

eg1: 查询时显示攻击力翻倍（注意：数据库中的数据不会变，相当于一次性显示变化）
  select name,attack*2 from sanguo;
eg2: 更新蜀国所有英雄攻击力 * 2（注意：数据库中数据永久改变了）
  update sanguo set attack=attack*2 where country='蜀国';

索引操作

索引概述

定义

索引是对数据库表中一列或多列的值进行排序的一种结构(Btree,即 B树方式：降低高度)，使用索引可快速访问数据库表中的特定信息。

mysql实际是 B+树，数据都在叶子节点里，叶子的东东都是排好系的，首尾相连。

传统B树的特点：

每个节点能存储多个索引【包括数据】，由于改特征，促使树的高度比二叉树矮，从而降低了磁盘 io查找。
但是由于每个节点存储了数据。
B+树的特点：
1.节点内只存储索引，不存储数据，从而单个节点能存储的缩影数量远远大于 B树。
2.数据均存储在叶子节点中，并且有序的相连（范围查询效果好）

优点

加快数据检索速度,提高查找效率

缺点

占用数据库物理存储空间
当对表中数据更新时,索引需要动态维护,降低数据写入效率

索引示例

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

1 、开启运行时间检测

mysql>show variables like '%pro%';
mysql>set profiling=1; 开启

2、执行查询语句(无索引)

select name from students where name='Tom99999';

3 、查看执行时间

show profiles;

4、在 name 字段创建索引

create index name on students(name);

5 、再执行查询语句

select name from students where name='Tom88888';

6 、查看执行时间

show profiles;

索引分类

普通(MUL)

普通索引：字段值无约束,KEY标志为 MUL

唯一索引(UNI)

唯一索引(unique) ：字段值不允许重复,但可为 NULL,KEY标志为 UNI

主键索引（PRI）

一个表中只能有一个主键字段, 主键字段不允许重复,且不能为NULL，KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录

索引使用规则

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

索引创建

主键索引的创建方法已经在数据表中介绍过了，下面是普通索引和唯一索引的创建方法：

创建表时直接创建索引

create table 表名(
字段名 数据类型，
字段名 数据类型，
index(字段名),
index(字段名),
unique(字段名)
);
例如：create table class(name char(32),unique(name));没有指定索引名，索引名与字段名一致。

在已有表中创建索引：

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

查看索引

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

扩展：借助性能查看选项去查看索引性能

show variables like 'profiling'；

set  profiling = 1； 打开功能 （项目上线一般不打开）

show profiles  查看语句执行信息

删除索引

drop index 索引名 on 表名;

主键(PRI)and自增长(auto_increment)
使用规则
1 、只能有一个主键字段
2 、所带约束：不允许重复，且不能为 NULL
3、KEY标志(primary) ：PRI
4、通常设置记录编号字段 id, 能唯一锁定一条记录

创建
创建表添加主键

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

已有表添加主键索引或自增长属性及重新指定起始值

alter table 表名 add primary key(id);#已有表添加主键
alter table 表名 modify id int auto_increment;#已有表操作自增长属性
alter table 表名 auto_increment=20000;#已有表重新指定起始值

创建主键联合索引

primary key（uid,pid）
此时两个字段只要不都相同即可。

删除主键索引或自增长属性

alter table 表名 modify id int; #要先删除自增长，因为它只作用于主键字段
alter table 表名 drop primary key; #删除主键索引

扩展：索引结构（B+树）

mysql可以选择不同的索引表结构，但是其中默认的也是最为常用的就是使用B-Tree（B+Tree）作为索引结构。

二叉树

在这里插入图片描述

二叉查找树

在这里插入图片描述

B树

在这里插入图片描述

B+树

在这里插入图片描述

外键约束和表关联关系

外键约束

约束 : 约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性
外键：让当前表字段的值在另一个表的范围内选择。

语法

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

使用规则

1、主表、从表字段数据类型要一致
2、主表被参考字段：KEY的一种，一般为主键
foreign key 功能 : 建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强，为了具体说明创建如下部门表和人员表。

表1：创建部门

CREATE TABLE dept (id int PRIMARY KEY auto_increment,dname VARCHAR(50) not null);

创建人员

CREATE TABLE `person` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` tinyint DEFAULT 0,
  `sex` enum('m','w','o') DEFAULT 'o',
  `salary` decimal(8,2) DEFAULT 250.00,
  `hire_date` date NOT NULL,
  `dept_id` int DEFAULT NULL
) ;

上面两个表中每个人员都应该有指定的部门，但是实际上在没有约束的情况下人员是可以没有部门的或者也可以添加一个不存在的部门，这显然是不合理的。当然并不是任何情况都需要建立外键关系，如果没有这种约束时也可以不建立，但是当表与表之间存在这种约束时最好建立。

主表和从表：若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表。

foreign key 外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

REFERENCES tbl_name (index_col_name, ...)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，mysql会自动生成一个名字,可以通过show create table [tb]命令查看

建立表时直接建立外键关联，注意本表的外键列类型与指定的主表列相同，且主表指定列需为主键

CREATE TABLE `person` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` tinyint DEFAULT 0,
  `sex` enum('m','w','o') DEFAULT 'o',
  `salary` decimal(10,2) DEFAULT 250.00,
  `hire_date` date NOT NULL,
  `dept_id` int DEFAULT NULL,
   constraint dept_id foreign key(dept_id) references dept(id));

建立表后增加外键即已有表添加外键

 alter table person add constraint dept_fk foreign key(dept_id) references dept(id);

通过外键名称解除外键约束

#删除外键语法：
alter table 表名 drop foreign key 外键名;
外键名 ：show create table 表名;

alter table person drop foreign key dept_fk;

删除外键后发现desc查看索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除之后才可以。

级联动作

restrict(默认) : on delete restrict on update restrict
- 当主表删除记录时，如果从表中有相关联记录则不允许主表删除
- 当主表更改主键字段值时，如果从表有相关记录则不允许更改
cascade ：数据级联更新 on delete cascade on update cascade
- 当主表删除记录或更改被参照字段的值时,从表会级联更新
set null : on delete set null on update set null
- 当主表删除记录时，从表外键字段值变为null
- 当主表更改主键字段值时，从表外键字段值变为null
no action
- 同 restrict，都是立即检查外键限制

示例2：

表1、缴费信息表(财务)

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

库  db2
create database db2 charset utf8;
use db2;
create table master(
id int primary key,
name varchar(20),
class char(10),
money decimal(6,2) 
)charset=utf8;

insert into master values(1, '唐伯虎', '28ban', 300),(2, '点秋香','28ban',300),(3,'祝枝山', '28ban', 300);

表2、学生信息表(班主任) – 做外键关联（注意：以下三个表主要是级联动作不同）

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

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

insert into slave values(1, '唐伯虎', 300),(2, '点秋香',300),(3,'祝枝山', 300);


create table slave_2(
stu_id int,
name varchar(20),
money decimal(6,2),
foreign key(stu_id) references master(id) on delete restrict on update restrict
)charset=utf8;


create table slave_3(
stu_id int,
name varchar(20),
money decimal(6,2),
foreign key(stu_id) references master(id) on delete set null on update set null
)charset=utf8;

多表查询

create table if not exists province(
id int primary key auto_increment,
pid int,
pname varchar(15)
)default charset=utf8;

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

create table if not exists city(
id int primary key auto_increment,
cid int,
cname varchar(15),
cp_id int
)default charset=utf8;

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);

create table if not exists county(
id int primary key auto_increment,
coid int,
coname varchar(15),
copid int
)default charset=utf8;

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);

示例

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  字段1,字段2... from 表1,表2... [where 条件]

e.g.
select * from dept,person where dept.id = person.dept_id;

内连接

内连接查询只会查找到符合条件的记录，其实结果和多表查询是一样的,官方更推荐使用内连接查询。

在这里插入图片描述

SELECT 字段列表 FROM 表1  INNER JOIN  表2 ON 表1.字段 = 表2.字段;
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;
eg3 : 
select * from person inner join  dept  on  person.dept_id =dept.id;

笛卡尔积

笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。

#语法：select 字段名列表 from 表名列表; 
select * from person inner join  dept;

外连接

左连接

左连接 : 左表为主表，显示右表中与左表匹配的项

在这里插入图片描述

SELECT 字段列表 FROM 表1  LEFT JOIN  表2 ON 表1.字段 = 表2.字段;
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;
eg2 :
select * from person left join  dept  on  person.dept_id =dept.id;

右连接

右连接：右表为主表，显示左表中与左表匹配的项

在这里插入图片描述

SELECT 字段列表 FROM 表1  RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
select 字段名 from 表1 right join 表2 on 条件 right join 表3 on 条件;

eg1 :
select * from person right join  dept  on  person.dept_id =dept.id;

表关联设计

当我们应对复杂的数据关系的时候，数据表的设计就显得尤为重要，认识数据之间的依赖关系是更加合理创建数据表关联性的前提。常见的数据关系如下：

一对一关系

一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。

举例 : 学生信息和学籍档案，一个学生对应一个档案，一个档案也只属于一个学生

create table student(id int primary key auto_increment,name varchar(50) not null);

create table record(id int primary key auto_increment,
comment text not null,
st_id int unique,
foreign key(st_id) references student(id) 
on delete cascade 
on update cascade
);

一对多关系

一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录
只能对应第一张表的一条记录，这种关系就是一对多或多对一

举例：一个人可以拥有多辆汽车，每辆车登记的车主只有一人。

create table person(
  id varchar(32) primary key,
  name varchar(30),
  sex char(1),
  age int
);

create table car(
  id varchar(32) primary key,
  name varchar(30),
  price decimal(10,2),
  pid varchar(32),
  constraint car_fk foreign key(pid) references person(id)
);

多对多关系

一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录
也能对应A表中的多条记录

举例：一个运动员可以报多个项目，每个项目也会有多个运动员参加,这时为了表达多对多关系需要单独创建关系表。

CREATE TABLE `athlete` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` tinyint NOT NULL,
  `country` varchar(30) NOT NULL,
  `description` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
);

CREATE TABLE `item` (
  `id` int NOT NULL AUTO_INCREMENT,
  `rname` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
);

CREATE TABLE `athlete_item` (
  `aid` int NOT NULL,
  `tid` int NOT NULL,
  PRIMARY KEY (`aid`,`tid`),
  CONSTRAINT `athlete_fk` FOREIGN KEY (`id`) REFERENCES `athlete_item` (`aid`)，
  CONSTRAINT `item_fk` FOREIGN KEY (`id`) REFERENCES `athlete_item` (`tid`)
);

E-R模型

定义

E-R模型(Entry-Relationship)即 实体-关系 数据模型,用于数据库设计
用简单的图(E-R图)反映了现实世界中存在的事物或数据以及他们之间的关系

实体、属性、关系

实体

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

属性

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

关系

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

ER图的绘制

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

在这里插入图片描述

示例1：

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

1、实体：学生、课程、老师
2、属性
3、关系
   学生 选择 课程 (m:n)
   课程 任课 老师 (1:n)

关系映射实现

1:1实现 --> 主外键关联,外键字段添加唯一索引
  表t1 : id int primary key,
          1
  表t2 : t2_id int unique,
         foreign key(t2_id) references t1(id)
          1
1:n实现 --> 主外键关联
  表t1 : id int primary key,
         1
  表t2 : t2_id int,
         foreign key(t2_id) references t1(id)
         1
         1        
m:n实现(借助中间表):
   t1 : t1_id 
   t2 : t2_id

示例2：

设计一个老师与课题的E-R图（多对多实现）

表1、老师表
表2、课题表
问题？如何实现老师和课程之间的多对多映射关系？

create table teacher(
id int primary key,
tname varchar(20),
level varchar(20)
)charset=utf8;
insert into teacher values(1,'小明','牛X'),(2,'小红','牛XX');

create table course(
id int primary key,
cname varchar(20)
)charset=utf8;
insert into course values(1,'Django'),(2,'Mysql'),(3,'Project');

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、小明在研究什么课题？
Sleect 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=’小明’;

视图

视图概念

视图是存储的查询语句,当调用的时候,产生结果集,视图充当的是虚拟表的角色。其实视图可以理解为一个表或多个表中导出来的表，作用和真实表一样，包含一系列带有行和列的数据视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

视图操作:视图的增删改就会影响原表．

创建视图

语法结构：

CREATE [OR REPLACE] VIEW [view_name] AS [SELECT_STATEMENT];

释义：

CREATE VIEW： 创建视图
OR REPLACE : 可选，如果添加原来有同名视图的情况下回覆盖掉原有视图
view_name ： 视图名称
SELECT_STATEMENT ：SELECT语句

e.g.
create view  c1 as select name,age from class_1;

删除视图

drop view [IF EXISTS] 视图名；

IF EXISTS 表示如果存在，这样即使没有指定视图也不会报错。
```
drop view c1;
```

修改视图

参考创建视图，将create关键字改为alter

alter view  c1 as select name,age,score from class_1;

视图表的增删改查操作

视图的增删改查操作与一般表的操作相同，使用insert update delete select即可。

视图作用

作用
1. 是对数据的一种重构，不影响原数据表的使用。
2. 简化高频复杂操作的过程，就像一种对复杂操作的封装。
3. 提高安全性，可以给不同用户提供不同的视图
4. 让数据更加清晰
缺点
1. 视图的性能相对较差，从数据库视图查询数据可能会很慢
2. 表依赖关系处理麻烦，根据数据库的基础表创建一个视图。每当更改视图或者原表时，另一个也会修改。

函数和存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，调用存储过程和函数可以简化应用搞开发工作，提高数据处理的效率。

函数创建

delimiter 自定义符号　　-- 如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略

　　create function 函数名(形参列表) returns 返回类型　　-- 注意是retruns

　　begin

　　　　函数体　　　　-- 函数语句集,set @a 定义变量

　　　　return val

　　end  自定义符号

　　delimiter ;

释义：
delimiter 自定义符号 是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志,一般用$$或者//
形参列表 ： 形参名 类型   类型为mysql支持类型
返回类型:  函数返回的数据类型,mysql支持类型即可
函数体： 若干sql语句组成，如果只有一条语句也可以不写delimiter和begin,end
return: 返回指定类型返回值

如：
mysql> select * from hobby;
+----+--------+--------------+
| id | cid    | cname        |
+----+--------+--------------+
|  1 | 131100 | 石家庄市     |
|  2 | 131101 | 沧州市       |
|  3 | 131102 | 廊坊市       |
|  4 | 131103 | 西安市       |
|  5 | 131104 | 成都市       |
|  6 | 131105 | 重庆市       |
|  7 | 131106 | 广州市       |
|  8 | 131107 | 济南市       |
|  9 | 131108 | 武汉市       |
| 10 | 131109 | 郑州市       |
| 11 | 131110 | 北京市       |
| 12 | 131111 | 天津市       |
| 13 | 131112 | 上海市       |
| 14 | 131113 | 哈尔滨       |
| 15 | 131114 | 雄安新区     |
+----+--------+--------------+
15 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> delimiter $$
mysql> create function st() returns int
    -> begin
    -> return (select cid from hobby order by cid desc limit 1);
    -> end $$
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> select st();
+--------+
| st()   |
+--------+
| 131114 |
+--------+
1 row in set (0.04 sec)

mysql> delimiter $$
mysql> create function query66(uid int) returns varchar(20) begin return (select cname from hobby where id=cid); end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select query66(2);
+------------+
| query66(2) |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)


mysql> delimiter $$
mysql> create function st3(uid1 int,uid2 int) returns float begin set @val1=(select cid from hobby where id =uid1); set @val2=(select cid from hobby where id =uid2); set @r=@val1-@val2; return @r; end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select st3(9,4);
+----------+
| st3(9,4) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

e.g. 无参数的函数调用
delimiter $$
create function st() returns int 
begin 
return (select score from class_1 order by score desc limit 1); 
end $$
delimiter ;

select st();

e.g. 含有参数的函数调用
delimiter $$
create function queryNameById(uid int(10)) 
returns varchar(20)
begin
return  (select name from class_1 where id=uid);
end $$
delimiter ;

select queryNameById(1);

存储过程创建

创建存储过程语法与创建函数基本相同，但是没有返回值。

delimiter 自定义符号　

　　create procedure 存储过程名(形参列表)

　　begin

　　　　存储过程　　　　-- 存储过程语句集,set @a 定义变量

　　end  自定义符号

　　delimiter ;

释义：
delimiter 自定义符号 是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志
形参列表 ：[ IN | OUT | INOUT ] 形参名 类型
          in 输入，out  输出，inout 可以输入也可以输出
存储过程： 若干sql语句组成，如果只有一条语句也可以不写delimiter和begin,end

e.g. 存储过程创建和调用
delimiter $$
create procedure st() 
begin 
    select name,age from class_1; 
    select name,score from class_1 order by score desc; 
end $$
delimiter ;

call st();

如：
mysql> delimiter $$
mysql> create procedure st()
    -> begin
    -> select cid,cname from hobby;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call st();
+--------+--------------+
| cid    | cname        |
+--------+--------------+
| 131100 | 石家庄市     |
|    666 | 沧州市       |
| 131102 | 廊坊市       |
| 131103 | 西安市       |
| 131104 | 成都市       |
| 131105 | 重庆市       |
| 131106 | 广州市       |
| 131107 | 济南市       |
| 131108 | 武汉市       |
| 131109 | 郑州市       |
| 131110 | 北京市       |
| 131111 | 天津市       |
| 131112 | 上海市       |
| 131113 | 哈尔滨       |
| 131114 | 雄安新区     |
+--------+--------------+
15 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


mysql> delimiter $$
mysql> create procedure test(out num int)
    -> begin
    -> declare val int;
    -> select cid from hobby where id=6 into val;
    -> set num=val;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @cid=77;
Query OK, 0 rows affected (0.00 sec)

mysql> call test(@cid);
Query OK, 1 row affected (0.01 sec)

mysql> select @cid;
+--------+
| @cid   |
+--------+
| 131105 |
+--------+
1 row in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure test(out num int)
    -> begin
    -> declare val int;
    -> select cid from hobby where id=6 into val;
    -> set num=val;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @cid=77;
Query OK, 0 rows affected (0.00 sec)

mysql> call test(@cid);
Query OK, 1 row affected (0.01 sec)

mysql> select @cid;
+--------+
| @cid   |
+--------+
| 131105 |
+--------+
1 row in set (0.00 sec)


mysql> delimiter $$
mysql> create procedure get_cid(in uid int,out num int)
    -> begin
    -> declare val int;
    -> select cid from hobby where id=uid into val;
    -> set num=val;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @cid=3;
Query OK, 0 rows affected (0.00 sec)

mysql> call get_cid(3,@cid);
Query OK, 1 row affected (0.00 sec)

mysql> select @cid;
+--------+
| @cid   |
+--------+
| 131102 |
+--------+
1 row in set (0.00 sec)

存储过程三个参数的区别
- IN 类型参数可以接收变量也可以接收常量，传入的参数在存储过程内部使用即可，但是在存储过程内部的修改无法传递到外部。
- OUT 类型参数只能接收一个变量，接收的变量不能够在存储过程内部使用（内部为NULL），但是可以在存储过程内对这个变量进行修改。因为定义的变量是全局的，所以外部可以获取这个修改后的值。
- INOUT类型参数同样只能接收一个变量，但是这个变量可以在存储过程内部使用。在存储过程内部的修改也会传递到外部。
- 设置变量方法： set @[变量名] = 值；表示这是一个全局变量，使用时用@[变量名]。在函数内部设置declare [变量名] [变量类型]为局部变量，局部变量可以使用set赋值或者着使用into关键字。

e.g. : 分别将参数类型改为IN OUT INOUT 看一下结果区别
delimiter $$
create procedure p_out ( OUT num int )
begin
    select num;
    set num=100;
    select num;
end $$

delimiter ;

set @num=10;
call p_out(@num)

函数和存储过程区别

函数有且只有一个返回值，而存储过程不能有返回值。
函数只能有输入参数，而存储过程可以有in,out,inout多个类型参数。
存储过程中的语句功能更丰富，实现更复杂的业务逻辑，而函数中不会使用insert,update,delete等语句，只是完成查询的工作，受输入参数并返回一个结果，功能针对性比较强。
存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

存储过程和存储函数操作

调用存储过程

语法：

call 存储过程名字（[存储过程的参数[,……]])

调用存储函数

语法：

select 存储函数名字（[存储过程的参数[,……]])

使用show status语句查看存储过程和函数的信息

语法：

show {procedure|function} status [like’存储过程或存储函数的名称’]

显示内容：数据库、名字、类型、创建者、创建和修改日期

使用show create语句查看存储过程和函数的定义

语法：

show create  {procedure|function}  存储过程或存储函数的名称

删除存储过程或存储函数

语法：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

存储引擎

定义

mysql数据库管理系统中用来处理表的处理器

基本操作

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、表字段和索引同存储在一个文件中
   1、表名.frm ：表结构
   2、表名.ibd : 表记录及索引文件

MyISAM

1、支持表级锁,在锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许
2、表字段和索引分开存储
   1、表名.frm ：表结构
   2、表名.MYI : 索引文件(my index)
   3、表名.MYD : 表记录(my data)

MEMORY

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

如何选择存储引擎

1、执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
2、执行写操作多的表用 InnoDB
3、临时表 ： MEMORY

CREATE TABLE tb_stu(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

事务控制

事务概述

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，如果操作就必须同时操作成功，如果有一个不成功则所有数据都不动。这时候数据库操作语句就构成一个事务。事务主要处理数据的增删改操作。

定义

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

作用

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

事务操作

开启事务

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

开始执行事务中的1条或者n条SQL命令
终止事务，若begin之后没有commit，而是使用其他语句，比如select，那么会自动commit，修改直接生效。

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

事务四大特性

原子性（atomicity）

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

一致性（consistency）

数据库总是从一个一致性的状态转换到另一个一致性的状态

隔离性（isolation）

一个事务所做的修改在最终提交以前，对其他事务是不可见的

持久性（durability）

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

注意:

事务只针对于表记录操作(增删改)有效,对于库和表的操作无效
事务一旦提交结束，对数据库中数据的更改是永久性的

数据库优化

数据库设计范式

通常数据库设计遵循第一第二第三范式，以避免数据操作异常。

常见异常：

插入异常：一个实体随另一个实体存在而存在，一个实体不插入另一个也没法插入
更新异常：一个实体无法单独更新，更新一个实体需要同时更新多个
删除异常：删除一个实体导致另一个实体的信息也会丢失
数据冗余：一个数据在多个表中多次出现，或者可以容易计算到

范式简介：

第一范式：要求数据库中的表示二维表，每个数据元素不可再分
第二范式：要求字段不能存在依赖关系，将很多一对多的关系存在了一个表中，并且存在如果某个元素没有很可能对应的一些信息就不会体现。
第三范式：在第二范式的基础上不存在传递的依赖关系。比如A字段依赖B字段，B却决定着C

字段数据类型选择

优先程度数字 > 时间日期 > 字符串
同一级别占用空间小的 > 占用空间多的

字符串在查询比较排序时数据处理慢
占用空间少，数据库占磁盘页少，IO处理就更快

少于50字节 char > varchar
对数据存储精确不要求 float > decimel
如果很少被查询可以用 TIMESTAMP（时间戳实际是整形存储）

键的设置

Innodb如果不设置主键也会自己设置隐含的主键，所以最好自己设置
尽量设置占用空间小的字段为主键
外键的设置用于保持数据完整性，但是会降低数据导入和操作效率，特别是高并发情况下，而且会增加维护成本
虽然高并发下不建议使用外键约束，但是在表关联时建议在关联键上建立索引，以提高查找速度，因为关联查询，使用外键效率更高．

explain语句查看SQL执行计划后进行索引优化

常说的SQL优化，就是利用explain语句查收SQL执行计划，然后再一步一步进行优化。使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

EXPLAIN SELECT * FROM test1.dingdan 
WHERE YEAR(dingdan_time) = 2021 AND diqu = '华东'

在这里插入图片描述

EXPLAIN字段解析：

id：编号：反映SQL执行顺序

1.id值相同，从上往下顺序执行(数据量小的表，优先查询)；
2.id值不同：id值越大越优先查询 (本质：在嵌套子查询时，先查内层，再查外层)
3.id值有相同，又有不同：id值越大越优先；id值相同，从上往下顺序执行

select_type：显示查询类型

1、PRIMARY: 包含子查询SQL中的 主查询 （最外层）
2、DEPENDENT SUBQUERY：包含子查询SQL中的 子查询 （非最外层）
2、simple:	简单查询（不包含子查询、union）
3、derived:	衍生查询(使用到了临时表即子查询)
a.在from子查询中只有一张表
b.在from子查询中， 如果有table1 union/union all table2则table1 就是derived,table2就是union
4、union:上例
5、union result :告知开发人员，那些表之间存在union查询

table：显示这一行的数据是关于哪张表的
type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。

type中包含的值：
- system：数据极少的系统表；或衍生表只有一条数据的主查询才能达到，现实几乎难于达到
- const： 仅仅能查到一条数据的SQL ,用于Primary key 或unique索引（类型与索引类型有关）
	1）查到一条数据，表中可以是多条数据
	2）必须是命中主键（primary key）或者唯一（unique）索引，其他类型的索引不可以，而且必须命中
	3）被连接的部分是一个常量（const）值 
	可以将查询的变量转为常量. 如id=1; id为主键或唯一键，现实中也几乎难于达到.
- eq_ref： 唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）。
		   访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或惟一键)，也很难达到。
- ref： 访问索引,对于每个索引键的查询,返回匹配的所有行(可以返回多行) 通常使用=时发生 
- range： 这个连接类型使用索引返回一个范围中的行，where后面是一个范围查询(between,> < >=,特殊:in有时候会失效从而转为无索引all)，并且该字段上建有索引时发生的情况(注:不一定好于index) 
- index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描 
- ALL： 全表扫描，应该尽量避免

possible_keys：显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引。
key：实际使用的索引。如果为NULL，则没有使用索引。MySQL很少会选择优化不足的索引，此时可以在SELECT语句中使用FORCE INDEX（index_name）来强制使用一个索引或者用IGNORE INDEX（index_name）来强制忽略索引。
key_len：使用的索引的长度，作用：用于判断复合索引是否被完全使用。在不损失精确性的情况下，长度越短越好。
ref：显示索引的哪一列被使用了（即：指明当前表所参照的字段），如果可能的话，是一个常数
rows：MySQL认为必须检索的用来返回请求数据的行数
Extra：关于MySQL如何解析查询的额外信息，主要有以下几种

Extra中包含的值：
- using index： 性能提升；索引覆盖。只用到索引,可以避免访问表，性能很高。 
- using where： 使用到where来过滤数据，不是所有的where clause都要显示using where. 如以=方式访问索引。
- using tmporary： 性能损耗大，用到临时表去处理当前的查询。需要优化操作。
- using filesort： 性能消耗大；用到额外的排序，通常出现在order by后面的索引失效了的情况下。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。(当使用order by v1,而没用到索引时,就会使用额外的排序)。需要优化操作。
- range checked for eache record(index map:N)： 没有好的索引可以使用。
- Using index for group-by：表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。explain 
- NULL  空
- impossible where ： where子句永远为false

根据SQL执行计划进行的索引优化示例

创建book表并插入数据：

CREATE TABLE book
(bid INT(4) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
authorid INT(4) NOT NULL,
publicid INT(4) NOT NULL,
typeid INT(4) NOT NULL
)CHARSET=utf8;
INSERT INTO book VALUES(1,"tjava",1,1,2),
(2,"tc",2,1,2),
(3,"wx",3,2,1),
(4,"math",4,2,3);

在这里插入图片描述

-- 查询typeid为2或3且authorid=1 的bid，并按照typeid 降序排列，借助explain语句查看SQL执行计划
EXPLAIN SELECT bid FROM book WHERE typeid IN (2,3) AND authorid = 1 ORDER BY typeid DESC;

在这里插入图片描述

因为SQL执行计划里面的Extra中包含的值是《using filesort》，说明需要SQL优化，我们先通过创建联合索引进行优化。

-- 创建联合索引来进行SQL优化
ALTER TABLE book ADD INDEX index_bta(bid,typeid,authorid);

如下图所示，是创建了联合索引后的情况：

在这里插入图片描述

-- EXPLAIN查询创建了联合索引后的SQL执行计划
EXPLAIN SELECT bid FROM book WHERE typeid IN (2,3) AND authorid = 1 ORDER BY typeid DESC;

在这里插入图片描述

创建联合索引后，在借助EXPLAIN查询创建了联合索引后的SQL执行计划，发现Extra中包含《using filesort》值，说明依然需要进行SQL优化。至于出现这样的原因是因为：SQL执行顺序与SQL书写顺序是不同，通过上面《聚合操作—SQL执行顺序》章节知道下面的SQL语句是先执行顺序为：FROM→SELECT→ORDER BY；而联合索引追求最佳左前置原则，根据执行顺序，我们需要先执行typeid，再执行authorid，最后是bid，所以需要修改联合索引的顺序为：index_tab(typeid,authorid,bid)。

-- 先删除旧的联合索引
ALTER TABLE book DROP INDEX index_bta;
-- 再创建新的联合索引
ALTER TABLE book ADD INDEX index_tab(typeid,authorid,bid);

在这里插入图片描述

-- EXPLAIN查询新创建的联合索引后的SQL执行计划
EXPLAIN SELECT bid FROM book WHERE typeid IN (2,3) AND authorid = 1 ORDER BY typeid DESC;

在这里插入图片描述

第二次创建联合索引后，在借助EXPLAIN查询创建了联合索引后的SQL执行计划，发现Extra中没有《using filesort》值了，但是type级别是《range》，依然没有达到我们希望的《ref》级别，说明依然需要进行SQL优化。至于出现这样的原因是因为：in有时候会失效从而转为无索引all，所以需要修改联合索引的顺序为：index_tab(authorid,typeid,bid)，然后调整SQL语句顺序。

-- 先删除旧的联合索引
ALTER TABLE book DROP INDEX index_tab;
-- 再创建新的联合索引
ALTER TABLE book ADD INDEX index_atb(authorid,typeid,bid);

在这里插入图片描述

-- EXPLAIN查询最新创建的联合索引后并调换SQL书写顺序后的SQL执行计划
EXPLAIN SELECT bid FROM book WHERE authorid = 1 AND typeid IN (2,3) ORDER BY typeid DESC;

在这里插入图片描述

避免索引失效的原则

1、复合索引（ a.复合索引，不要跨列或无序使用（最佳左前缀）； b.复合索引，尽量使用全索引匹配）

2、不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

3、复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。另外：复合索引中如果有>，则自身和右侧索引全部失效。

4、尽量使用索引覆盖（using index）

5、like尽量以“常量”开头，不要以’%'开头，否则索引失效

6、尽量不要使用类型转换（显示、隐式），否则索引失效

7、尽量不要使用or，否则索引失效

其他优化方法

exist和in

如果主查询的数据集大，则使用In，效率高。

如果子查询的数据集大，则使用exist，效率高。

order by 优化

using filesort 有两种算法：双路排序、单路排序

单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小：

set max_length_for_sort_data = 1024 单位byte

如果max_length_for_sort_data值太低，则mysql会自动从单路->双路

（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

提高order by查询的策略：

1、选择使用单路、双路；调整buffer的容量大小；

2、避免select * …

3、复合索引不要跨列使用，避免using filesort

4、保证全部的排序字段排序的一致性(都是升序或降序)

存储引擎优化

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 * ...;用具体字段代替 * ,不要返回用不到的任何字段
8、explain 放在查询语句前面可以获取查询计划，建立合适索引
9、子查询优化为join查询
10、控制使用自定义函数

示例

有一张文章评论表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 文件的内容导入到数据库的表中

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字段的外键,更新删除同步
insert into orders values(1,"iphone",5288),(1,"ipad",3299),(3,"mate9",3688),(2,"iwatch",2222),(2,"r11",4400);

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 c_salary>4000 or c_age<29 limit2;
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 s_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 cid from where c_salary>5000) ;

表的拆分

垂直拆分：表中列太多，分为多个表，每个表是其中的几个列。将常查询的放到一起，blob或者text类型字段放到另一个表

水平拆分：减少每个表的数据量，通过hash key进行划分然后拆成多个表

数据库备份和用户管理

数据备份

备份命令格式

mysqldump -u用户名 -p 源库名 > ~/stu.sql

–all-databases 备份所有库
库名备份单个库
-B 库1 库2 库3 备份多个库
库名表1 表2 表3 备份指定库的多张表

如：tarena@tarena:~/liuzhiqiang$ mysqldump -uroot -p books > ./book.sql　备份boks数据库

mysql> create database test charset=utf8;

tarena@tarena:~/liuzhiqiang$ mysql -uroot -p test < book.sql　：将备份恢复到test数据库中．

恢复命令格式

mysql -uroot -p 目标库名 < stu.sql
从所有库备份中恢复某一个库(–one-database)

mysql -uroot -p --one-database 目标库名 < all.sql

与文件系统交互

数据导入

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

导入csv数据

目标：把csv文件系统的内容导入到数据库中

语法格式

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

示例
scoretable.csv文件导入到scoretab表中

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/
2、在数据库中创建对应的表
  create table scoretab(
  rank int,
  name varchar(20),
  score float(5,2),
  phone char(11),
  class char(7)
  )charset=utf8;
3、执行数据导入语句
load data infile '/var/lib/mysql-files/scoreTable.csv'
into table scoretab
fields terminated by ','
lines terminated by '\n'
4、添加id字段,要求主键自增长,显示宽度为3,位数不够用0填充
  alter table scoretab add id int(3) zerofill primary key auto_increment first;

数据导出

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

导出csv数据

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

语法格式

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表的 id,name,country 三个字段的前3条记录,sanguo4
  create table sanguo4 select id,name,country from country.sanguo limit 3

注意

扩展分享-常规分表套路：

用户ID int % 表数量

用户名 ASCII % 表数量

经典案例：用户表分表

点赞 -》MVCC 多版本控制解决方案

version int

每次update的时候 version + 1

topic_like, version = select topic_like , version from topic;

topic_like += 1

while n<3

update … where version=version

复制表的时候不会把原有表的 KEY 属性复制过来

复制表结构
create table 表名 select 查询命令 where false; 心

锁（自动加锁和释放锁）

目的

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

锁类型分类

读锁(共享锁)：select 加读锁之后别人不能更改表记录，更不能删除表内容,但可以进行查询
lock table sanguo2 read;就是给sanguo2添加读锁。
写锁(互斥锁、排他锁)：加写锁之后别人不能查、不能改
lock table sanguo2 write;就是给sanguo2添加写锁。

如果某一个会话对A表加了read（读）锁，则该会话可以对A表进行读(select)操作、不能进行写（insert update delete）操作；并且该会话不能对其他表进行读、写(select update insert delete)操作。即如果给A表加了读锁，则当前会话只能对A表进行读操作。其他会话可以对其他表A表以外的表进行读、写操作，对A表读-可以，写-需要等待释放锁。

如果某一个会话对A表加了write（写）锁，则当前会话（会话0）可以对加了写锁的A表进行任何操作（增删改查）；但是不能操作（增删改查）其他表，其他会话需要等待会话0释放写锁之后，才可以对A表进行增删改查操作，对其他表可以进行读写操作。

锁粒度（操作范围）分类

表级锁：myisam：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低。

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁。所以对MyISAM表进行操作，会有以下情况：1、对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。2、对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

行级锁：innodb：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；比表锁性能损耗大，容易出现死锁；但是锁的范围较小，不易发生锁冲突，效率高，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。因此建议，高并发用InnoDB，否则用MyISAM。新建的数据表默认也是InnoDB。

行锁，注意：如果没有索引，则行锁会转为表锁。所以对InnoDB表进行操作，会有以下情况：1、如果会话x对某条数据a进行 DML操作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后才能对数据a进行操作。2、表锁是通过unlock tables，也可以通过事务解锁 ; 行锁是通过事务解锁。行锁，一次锁一行数据；因此如果操作的是不同数据，则不干扰。

分类及约束
1、普通索引（MUL） : 无约束
2、唯一索引（UNI）：字段值不允许重复，但可为 NULL
3、主键（PRI）：字段值不允许重复，不可为 NULL
4、外键：让当前表字段的值在另一张表的范围内选择

用户账号权限管理

开启MySQL远程连接

Sudo su
输入密码后
root@tarena:/home/tarena# cd vvar/lib/mysql: 到 mysql 根目录下
ls 查看 mysql 下所有数据库 .

更改配置文件，重启服务！
1、sudo -i
2、cd /etc/mysql/mysql.conf.d
3、cp mysqld.conf mysqld.cnf.bak
4、vi mysqld.cnof #找到44行左右,加 # 注释
   #bind-address = 127.0.0.1
   [mysqld]
   character_set_server = utf8
5、保存退出
6、service mysql restart
vi使用 : 按a ->编辑文件 ->ESC ->shift+: ->wq

添加授权用户

1. 用root用户登录mysql
   mysql -uroot -p123456
2. 添加用户 % 表示自动选择可用IP
   CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;
3. 授权
   grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;
4. 刷新权限
   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，密码123,对db2库中所有表有所有权限
  mysql>grant all privileges on db2.* to 'duty'@'%' identified by '123' with grant option;
  mysql>flush privileges;

pymysql模块

pymysql安装

sudo pip3 install pymysql

pymysql使用流程

建立数据库连接(db = pymysql.connect(…))
创建游标对象(cur = db.cursor())
游标方法: cur.execute(“insert …”)
提交到数据库或者获取数据 : db.commit()/db.fetchall()
关闭游标对象：cur.close()
断开数据库连接：db.close()

常用函数

"""
pymysql 操作数据库
"""
import pymysql
# 连接数据库
db = pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='123456',
                     database='stu',
                     charset='utf8')

# 生成游标对象 (操作数据库，执行sql语句)
cur = db.cursor()
# 执行各种对数据库的读写操作
# 关闭游标和数据库连接
cur.close()
db.close()

"""
pymysql 读操作
"""
import pymysql
# 连接数据库
db = pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='123456',
                     database='stu',
                     charset='utf8')

# 生成游标对象 (操作数据库，执行sql语句)
cur = db.cursor()
# 执行读操作
# sql = "select name,age from class1 where sex='m';"
# cur.execute(sql) # 执行语句

# 输入姓名查询信息
# name = input("Name:")
# 传入字符串时%s也要加上引号
# sql = "select * from interest where name='%s';"%name
# cur.execute(sql) # 执行语句

# class1中查询性别为m 分数大于85
sql="select * from class1 where sex=%s and score>%s;"
# 通过execute第二个参数列表传参给sql语句
cur.execute(sql,['m',85])

# 迭代cur获取查询结果
# for i in cur:
#     print(i)

# 获取一个查询结果
# one_row = cur.fetchone()
# print(one_row)

# 获取多个查询结果
# many_row = cur.fetchmany(2)
# print(many_row)

# 获取所有查询结果
all_row = cur.fetchall()
print(all_row)

# 关闭游标和数据库连接
cur.close()
db.close()

"""
write_db.py 写数据库
"""
import pymysql

# 连接数据库
db = pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     password='123456',
                     database='stu',
                     charset='utf8')

# 生成游标对象 (操作数据库，执行sql语句)
cur = db.cursor()

# 执行对数据库的写操作
try:
    # 执行增删改等语句
    # sql="insert into class1 (name,age,score) \
    # values ('Dave',13,79);"

    # 修改操作
    # sql="update class1 set sex='m' where name='Dave';"

    # 删除操作
    # sql = "delete from class1 where name='Dave';"
    # cur.execute(sql)

    # 从input输入内容传给sql语句
    # name = input("Name:")
    # age = int(input('Age:'))
    # score = float(input("Score:"))
    # sql="insert into class1 (name,age,score) \
    # values (%s,%s,%s);"
    # cur.execute(sql,[name,age,score])

    # executemany 多次执行sql语句
    exe = []
    for i in range(3):
        name = input("Name:")
        age = int(input('Age:'))
        score = float(input("Score:"))
        exe.append((name,age,score))

    sql = "insert into class1 (name,age,score) \
        values (%s,%s,%s);"
    cur.executemany(sql,exe)

    db.commit() # 将操作结果立即提交
except Exception as e:
    db.rollback() # 事务回滚
    print(e)

# 关闭游标和数据库连接
cur.close()
db.close()

db = pymysql.connect(参数列表)

host ：主机地址,本地 localhost
port ：端口号,默认3306
user ：用户名
password ：密码
database ：库
charset ：编码方式,推荐使用 utf8

数据库连接对象(db)的方法

cur = db.cursor() 返回游标对象,用于执行具体SQL命令
db.commit() 提交到数据库执行
db.rollback() 回滚，用于当commit()出错是回复到原来的数据形态
db.close() 关闭连接

游标对象(cur)的方法

cur.execute(sql命令,[列表]) 执行SQL命令
cur.executemany(sql命令,[data]) 根据数据列表项多次执行SQL命令
cur.fetchone() 获取查询结果集的第一条数据，查找到返回一个元组否则返回None
cur.fetchmany(n) 获取前n条查找到的记录，返回结果为元组嵌套元组， ((记录1),(记录2))。
cur.fetchall() 获取所有查找到的记录，返回结果形式同上。
cur.close() 关闭游标对象

'''
 mysql.py pymysql 数据库操作流程
'''
import pymysql
#连接数据库
db = pymysql.connect(host = 'localhost',port =3306,user='root',password='123456',database = 'python',charset='utf8')
#创建游标对象(操作数据库语句，获取查询结果)
cur = db.cursor()
#数据库操作
cur.execute('insert into class_1 values(5,"levi",11,"m",98);')
#向数据库提交(可以多次execute一次提交,只有写操作需要)
db.commit()
#关闭游标和数据库
cur.close()
db.close()

'''
 read_db.py pymysql 数据库读操作流程select
'''
import pymysql
#连接数据库
db = pymysql.connect(host = 'localhost',port =3306,user='root',password='123456',database = 'python',charset='utf8')
#创建游标对象(操作数据库语句，获取查询结果)
cur = db.cursor()

#数据库操作
sql = "select * from class_1 where sex='w'"
cur.execute(sql)
#向数据库提交(可以多次execute一次提交,只有写操作需要)

one_row = cur.fetchone()
print (one_row)# 元组

many_row = cur.fetchmany(1)
print (many_row)# 元组套元组

all_row = cur.fetchall()
print (all_row)

#关闭游标和数据库
cur.close()
db.close()