掌握MySQL，看完这篇文章就够了

一、数据库

1、数据库：对大量数据进行存储和管理，即对数据进行增删改查。

2、分类

（1）、关系型数据库

a、典型代表：

• 大型：Oracle；
• 企业级：MySQL、SQLserver；
• 轻量级文件数据库：sqlite；

b、特点：

        通过表产生关系关联，每个表中都存有结构化化的数据，支持SQL结构化查询。

c、组织：

        数据库管理系统DBMS、数据库DB、表Table、行Row、列Column。

（2）、非关系型数据库

a、典型代表

• 文档数据库：MongoDB；
• 内存数据库：Redis；

二、MySQL

（一）、学习方法：

学习数据库就是学习数据库管理系统，一个数据库管理系统可以管理多个数据库。

（二）、安装：

1、流程：

• custom自定义安装；
• 选择MySQL Server的版本号；
• 默认使用TCP/IP协议，端口号3306；
• 配置用户密码；
• 配置服务器。

2、环境变量配置

• 高级设置；
• 环境变量；
• 在路径中新建一个MySQL的bin路径；

3、检测是否安装成功

win+r,输入cmd，输入MySQL -uroot -p， 输入密码；

4、连接到数据库管理系统

mysql.exe下配置主机端口，用户名，密码。

（三）、数据类型

1、数字

• int：整型，4个字节；
• bigint: 整数， 8个字节；
• float：浮点数，单精度；4字节；
• double：双精度，8个字节；
• decimal：高精度，需要指明精度小数位数；

2、字符串

• char：长度
• varchar：可变长度
• text：长文本

3、bool

• 在mysql中为tinyint

4、日期时间

• data：日期
• time：时间
• datatime：日期时间
• timestamp：时间戳

例子：datetime/timestamp，current_timestamp: 当前时间

alter table 表名 add 列名 datetime default current_timestamp；

alter table student add register_time datetime default current_timestamp;

5、枚举

• enum

例子：加入枚举类型的列：alter table 表名 add  列名 enum(  )

alter table student add sex enum('男','女','保密') default ‘保密’；

（四）、约数信息

约数信息不分先后顺序

1、primary key：主键

• 一个表中必须有一个列为主键，一般为一个独立的id列。
• 定义：a、可以直接在类型后使用primary key；b、在定义完所有列后单独使用primary key（列名）。

2、not null：非空

3、auto_increment：自增长

4、unique：唯一

5、default：默认

6、foreign key：外键

1）和其他键发生关联的键

2）使用：

a、命名：constraint 外键名

b、语法：foreign key（列名）references 主表（列）

c、附加条件：

cascade：当父表中进行更新和删除时，如果有匹配的子表记录，则对表中相关的记录也会更新或删除。

restrict：当父表进行更新或删除时，如果有匹配的子表存在，会阻止父表的更新或删除。

例子： create table student(id int not null auto_increment primary key,name varchar(20) not null, t_id int not null,    constraint fk_teacher_id  foreign key(t_id) references teacher(id) on update cascade on delete cascade);

删除外键：alter table 表名 drop foreign key 外键名；

例： alter table teacher drop foreign key fk_teacher_id;

添加外键：alter table 表名 add constraint 外键名 foreign key（需要关联的列名）references 主表（主键）on update cascade on delete cascade；

例： alter table student add constraint fk_teacher_id foreign key (t_id) references teacher(id) on update cascade on delete cascade;

（五）、SQL结构化语言

1、数据库相关

1）、展示所有：show databases；

2）、展示当前使用：select database（）；

3）、创建： create database 数据库名；

判断数据库是否存在：if not exists

编码方式：a、charset=utf8；b、character set utf8；

例： create database if not exists 数据库名称 charset=utf8;

4）、使用：use 数据库名称；

5）、删除： drop database 数据库名称；

2、表相关

有关表相关操作之前，先连接数据库；

1）、展示当前数据库中的所有表：show tables；

2）、创建表：create table 表名（列名 类型 约数信息， 列名 类型 约数信息，……）；

3）、更改表：

a、更改表名：rename table 旧表名 to 新表名；

例： rename table category_table to goods_table;

b、更改表中信息：alter table 表名 +

• ①添加：add 列名 类型 约数信息；
• ②修改列名：change 列名 新列名 类型 约数信息
• ③删除： drop 列名；

c、查看表信息：desc 表名；

d、删除表： drop table 表名；

3、数据相关

1）、查询

• a、select * from 表名；
• b、select 列名1，列名2， 列名3……from 表名；
• c、select * from 表名 where id< 5;

2)、插入

• a、全列插入

①一行：insert into 表名 values （列1， 列2……）；

②多行： insert into 表名 values （列1， 列2……），（列1， 列2……），……；

• b、缺省插入：

需要指定列 指定值， 有默认值的可以省略

①插入一行：nsert into 表名 （列1， 列2） values (值1， 值2)；

②插入多行：insert into 表名 （列1， 列2）values （值1，值2），（值1，值2），（值1，值2）……；

③指定插入：insert into表名 set 列名1 = 值1，列名2=值2；

3）、修改：update 表名 set 列名1= 值1，列名2=值2……where 条件；

4）、删除：delete from 表名 where……；

4、SQL语言分类

1）、数据定义语言DDL(Data Definition Language)

create/drop/alter/rename

2）、数据管理语言 DML

insert/delete/update/select

（六）、进阶查询

1、基础使用

•     select * from 表名； 查询所有行的所有列
•     select 列1，列2，列3…… from 表名； 查询所有行的部分列
•     select 列1，列2， 列3…… from 表名 where 条件； 查询部分行的部分列

2、别名

1）、只是显示，不会更改数据库
2）、格式：select 列名 as 别名 ， 列名 as 别名，…… from 表名；
3）、使用场景

• 针对查询到的列起别名

• 多表查询，有共同的列表

例：   select teacher.name as 老师姓名 , student.name as 学生姓名 from teacher,student;

3、条件where

⑴、比较运算符： =/!=、<>/</<=/>/>=
⑵、逻辑运算符：[and的优先级大于or]

①and：并且,条件都要满足;

②or:  或者, 满足其一条件即可.

⑶、成员:

①in,在其中一个

例： where id in (1,2,3,4);           

②not in

范围比较:between ……and ……;成员、范围的比较效率比较低

⑷模糊运算符： like

• _： 一个下划线表示一个字符

例： where name like ‘张_';

• %： 表示所有内容都可

例： where name like '%张%';
⑸判空：

①is null
②is not null

例：select * from teacher where address is not null;
注意： 在mysql中空字符串不算空，只有NULL为空。

（七）、常用技术

1、系统函数调用：select

也可以进行基本的运算，例：  select 100/20;】

①user()： 例：  select user(); 用户
②database（）：当前使用的数据库
③version()： 版本
④current_time： 时间
⑤current_date： 日期
⑥current_timestamp：日期时间

2、聚合函数

①max(列名)： 最大值， 例： select max(id) from teacher
②min(列名)：最小值
③sum(列名)：总和
④avg(列名)：平均值
⑤count(任意列名)

3、排序：order by 列名 排序方式，列名 排列方式

①desc：降序
②asc：默认升序
例： select * from teacher order by id ,age desc;

4、分页：limit

①方式一： limit n；显示前几个
②方式二： limit m,n：从索引m开始显示n个，显示第page页每页显示size个，limit (page-1)*size,size;例：select * from teacher limit 3,3;第二页的索引为（2-1）*3

5、分组
①having：针对的是分组结果进行分组

即 having 列，中的列是group by的列
例：select sex,count(*) from teacher group by sex having sex in ("女");

②group by： 针对查询条件进行分组

select count(*） from 表名 where 条件
例：根据性别分组：例：select count(*), sex from teacher group by sex;

6、去重：distinct，去重某一列

例： select distinct(sex) from teacher;

（八）、关联查询

⑴嵌套查询： 一个查询结果，作为另外一个查询的内容，不同的表格使用关键字之间建立连接

例： select * from student where t_id in (select id from teacher where id in (1,2));

⑵笛卡尔连接： 组合表中的所有数据， 一个有m行，一个有n行，一共右m*n行

例： select * from student ， teacher；

⑶连接查询：

①内连接，表1 inner join 表2 on 条件

例：  select student.name as 学生名, teacher.name as 老师名 from student inner join teacher on student.t_id = teacher.id;

②左外连接 ：left join，内连接结果 + 左表内容（右补NULL）

例： select student.name, teacher.name from student left join teacher on student.t_id = teacher.id;

③右外连接： right join，内连接结果 + 右表内容（左补NULL）

例： select student.name, teacher.name from student right join teacher on student.t_id = teacher.id;

④全连接： 左外连接 union 右外连接

例子：select student.name, teacher.name from student right join teacher on student.t_id = teacher.id union select student.name, teacher.name from student left join teacher on student.t_id = teacher.id;

（九）、用户权限

⑴select user()：查看当前用户

⑵创建用户：create user ’用户名‘@’主机名‘ identified by ’密码‘；

例：create user ’temp1‘@'%' identified by '123456';

创建一个用户temp1，@指定用户的主机部分，它将用户名和主机名分开，并指定了用户可以从哪个主机连接到 MySQL 服务器。

指定该用户可以从任何主机 '%' 连接到 MySQL 服务器

⑶分配权限

grant 权限 on 数据库名.表名 to ’用户名‘@’主机名‘；

grant all on *.* to 'temp1'@'%'

所有数据库中的所有表的所有权限

grant select,insert on mydb.* to 'temp2'@'%';

mydb数据库 所有表 查询，插入权限

⑷删除用户

drop user 'temp2'@'%';

⑸刷新权限

flush privileges;

（十）扩展

⑴视图：

一张虚拟表，方便查询，修改表等同于操作真实表，隐藏真实表结构。使用视图，等同于使用表。

⑵函数与存储过程：

都是存储在服务器上的，可以提升数据的安全

客户端不需要关注具体的实现

①函数：

经过计算返回一个结果，需要指定形参，指定返回值，select 函数名（实参）；

②存储过程：

一套SQL操作，没有返回值，在黑窗口调用：call 过程名(实参)；

⑶索引：

一种查询优化技术，可以提升查询效率

实现的本质是要预先存储一些额外数据，牺牲存储空间，提升查询效率

①索引类型

a、主键索引：主键

b、唯一索引：唯一约数unique字段

c、普通索引：任何字段

②何时定义索引：

索引不是创建的越多越好，如果表的修改频率非常高，不适合创建索引；如果表的查询频率非常高，几乎不修改，非常适合创建索引

③索引方法：

BTREE，(btree)大数据量，适合范围比较；HASH，（hash）适合小数据量，适合精准的等值比较；

⑷事务

mysql中存储引擎InnoDB 支持事务

①事务的作用：

mysql数据库对应的一系列操作要么全部执行成功全部提交，要么全部失败然后回滚

mysql终端默认全部提交：

set autocommit = 0；取消自动提交。

set autocommit = 1；设置自动提交。

②事务相关

开启事务：start transaction；

commit；成功提交

rollback；失败回滚

③特性

ACID原则

原子性：不可再分，要么全成功，要么全失败

原子性（Atomicity）： 事务中的操作要么全部执行成功，要么全部失败回滚，不存在部分执行的情况。

一致性：执行前后数据要保持一致

一致性（Consistency）： 在事务开始和结束时，数据库中的数据必须保持一致性状态。即，事务的执行不会破坏数据库的完整性约束。

隔离性：多个事务之间相互不影响

隔离性（Isolation）： 并发执行的事务相互之间应该是隔离的，一个事务的修改对其他事务来说是不可见的，直到事务提交。

永久性：一旦提交，则执行完毕，永久改变

持久性（Durability）： 一旦事务提交成功，其所做的修改将永久保存在数据库中，即使系统发生故障或重启，修改的结果也不会丢失。

⑷存储引擎

数据的存储的存储方式，不同的存储引擎适合不同的场景

①关键字：create table 表名 （……）engine = 引擎名；

②常用：

a、InnoDB，默认

特点：支持外键，支持事务，支持行级别锁定，阻塞，综合能力强，使用大多数引擎，默认存储引擎。

b、MyISAM

特点：查询、排序速度非常快，不支持外键

c、Memory

特点：读写内存速度非常快，但是不能持久化，不能在本地保存

d、CSV

特点：使用逗号隔开，适合导入导出操作，数据库备份，将数据库信息转储为SQL文件

（十一）、使用Python操作数据库

⑴pymysql

使用pymysq

安装： pip install pymysql

⑵步骤

1、导入pymysql模块

2、建立数据库连接

con = pymysql.connect()

3、创建游标实例

cur =con.cursor()

4、通过游标执行sql

execute（）

executemany()

5、处理sql结果

fetch*

6、释放游标与连接

con.close()

cur.close()