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

​数据库 对大量数据进行存储和管理（增删改查）

客户端：

    黑窗口终端navicat 熊掌软件

数据库分类：

关系型数据库

通过表与表产生关联关系，每个表中都存储结构化数据，支持sql结构化查询语言MysqlOracleSQLServer轻量级文件数据库sqlite: python自带sqlite3,可以快速集成到管理系统

非关系型数据库

存储管理非结构化数据，高性能，适应分布式
文档数据库mongodb
内存数据库redismysql 软件就是 数据库管理系统DBMS
数据库DB：表行列

MySQL数据库

连接到数据库管理系统

默认使用tcp/ip协议 端口3306
mysql -umao -p
mysql -uroot -p
mysql -h192.168.11.3 -uroot -p注意远程访问需要有权限
-p3306端口
-uroot 用户名
-p 密码

用户与授权:

select user(); 查看当前用户
create user '用户名'@'%' identified by '密码';
grant all on *.* to '用户名'@'%'; 所有数据库中所有表的所有权限
grant select insert on 数据库名.*to '用户名'@'%'; 数据库中所有表查询，插入权限
flush privileges; 刷新
drop user '用户名'@'%'; 删除用户

sql的分类

DDL:数据定义语言：create alter drop rename truncate
DML:数据操作语言:insert delete update select
DCL:数据控制语言:commit rollback savepoint grant revoke

列约束:

主键 primary key 学号，id
一张表必须有一个主键，主键列默认不能重复，一般都有一个独立的列id
可以直接在列类型后使用
在所有列定义后用 primary key（列名）
非空 null
自增长 auto_increment
唯一 unique 名字 不为空
默认 default
外键 foreign key  和其他表发生关联，一个表中的外键是另一个表的主键

列的数据类型:

数字：int(4字节 32位）bigint(8字节）float(浮点数 单精度 4字节）double(浮点数 双精度 8字节）decimal(高精度 需要指明精度小数位数）
字符串：char 长度varchar 可变长度text 文本
布尔：bool (1,0)
时间日期:datetimedatetimetimestamp
枚举（罗列所有可能)enum

指令

数据库相关：

展示所有数据库 show databases;
查看当前数据库 select database();
创建数据库 create database 数据库名;
指定编码:create database 数据库名 charset=utf8;
不存在才创建 create database if not exists 数据库名 charset=utf8;
使用数据库 use 数据库名;
删除数据库 drop database 数据库名;

表相关：

展示当前数据库所有表 show tables;
创建表create table 表名(列名 类型 约束信息,列名 类型 约束信息);例:create table teacher(id int primary key not null auto_increment,name varchar(50) not null unique);
查看表信息 desc 表名;describe 表名;
删除表 drop table 表名;
修改表名 rename table 原始表名 to 新表名;
alter table 表名
add 列名 类型 约束; 添加列
drop 列名; 删除列
change 原始列名 新列名 类型 约束;modify name varchar(30);

数据相关：

查询数据select

select * from 表名;
select 列1，列2... from 表名;
select * from 表名 where id<5;

插入数据insert

一.全列插入（需要给每一行赋值，包括自增长的可以写0）insert into 表名 values(列1，列2，列3....);插入多行insert into 表名 values(),(),()...;
二.缺省插入（需要指定列，指定值，有默认值的可以省略）insert into 表名 （id,name,age...） values(值1，值2..);插入多行insert into 表名 （列1，列2..） values(),(),();2.只能写一行 insert into 表名 set 列1=值1，列2=值2；

修改数据update

不带条件，整张表都会被修改
update 表名 set 列=值，列2=值2 where 条件查询;

删除数据delete

不带条件，整张表数据都会被删除
delete from 表名 where 条件;

外键指令：

创建表时添加：

create table 表名（列....,constraint 外键名 foreign key(外键列名） references 主表(主键) on update cascade on delete cascade);例子：create table student(id int primary key not null auto_increment,name varchar(30),tid int not null,constraint fk_teacher_id foreign key(tid) references teacher(id) on update cascade on delete cascade);

创建表以后添加：

alter table 表名 add constraint 外键名 foreign key(外键列名） references 主表(主键) on update cascade on delete cascade);外键的值在主表中必须存在
删除外键：alter table 表名 drop foreign key 外键名;
外键的修饰选项：restrict 拒绝 假如有外键使用到主表中的主键，在修改删除主键时不能操作cascade 级联 删除修改主表时 外键对应的内容直接删除更新

进阶查询：

基础查询select

select * from 表名; 查所有行所有列
select 列1，列2... from 表名; 查部分列
select * from 表名 where id<5; 部分行的部分列

别名：

针对查询到的列名起别名
多表查询，有共同列表select 列名 as 别名，列名 as 别名...from 表名 where 条件;
列的别名，关键字 as1.表名 as 新名字（as可以省略）2.列的别名 可以用""引起来,不要用''
例子：select student_id as id,name,age from 表名;select student_id id,name,age from 表名;select student_id "id",name,age from 表名;select student_id "id",name,age,salary * 12 "new_s" from 表名;

条件：

where 比较运算符 =，！=，<>不等于,<,<=,>,>=

多个条件：

and 并且
or 或者

成员：

in 在其中一个
select * from teacher where id in (1,2,3);
not in

范围

between and
select * from teacher where id between 1 and 4;

模糊查询like

%代表任意多个字符
_代表一个字符
例子：select * from teacher where name like '黄_';like '黄__';like '黄%';like '%'; 所有数据

判空:

is null是空
is not null不是空 空字符串不是null

常用技术:

系统函数调用 select

user()当前用户
version()当前数据库版本
database()当前数据库
current_data()当前日期
current_time()当前时间
current_timestamp()当前日期时间

聚合函数（分组函数）

max(列名)最大值
min(列名)最小值
avg(列名)平均值
sum(列名)列求和
count(任意列名)统计行数
select count(id) from student;

排序

order by 列名 排序方式,列名 排序方式
asc默认升序
desc降序
select * from student order by id; 通过id分组

分页:limit

1.limit n 显示前n个
2.limit m,n从索引m开始显示n个 第一个索引是0
显示第page页，每页显示size个 limit (page-1)*size，size
select * from student limit 2,3; 第一个是索引数据库定义变量set @page-2set @size-2

分组group by:

count(*) 统计行数针对查询的结果进行分组select count(*),列名 from 表名 where 条件 group by 列名;select sex,count(*) from teacher group by sex;having指的是针对分组结果进行处理 having 列名条件例1:select sex,count(*) from teacherwhere id > 3group by sexhaving sex in('男','女');例2:select count(*) as count,sex from teacher where id>3 group by sex having count > 1;select sex,count(*) from teacher where id>2 group by sex having sex='girl';

去重distinct

去重某一列
select distinct id from 表; 去掉相同的id号

分组查询：

select 分组函数，列（出现在group by 后面）from 表where 筛选条件group by分组的列表order by子句;having ;

关联查询

嵌套查询

一个查询的结果 作为另外一个查询的内容
例子:select * from studentwhere t_id in(select id from teacher where name='t1' or name='t2');

连接查询：

笛卡尔连接

组合两个表中的所有数据
m行 n行 结果 m*n行
select * from student,teacher

内连接:inner join…on

例:select student.name as 学生,teacher.name as 老师from student inner join teacher on student.t_id=teacher.id;
类型：等值连接(表与表有相同的字段)id=id自连接(同一张表找两次)非等值链接between and
语法:select 查询列表from 表1 别名inner join 表2 别名on 连接条件;

外连接:查询结果为主表中所有记录

左外连接:left(outer) join

左边是主表 内连接结果+左表内容（右侧补null)

右外连接:right(outer) join

左边是主表 内连接结果+右表内容（左侧补null)

全连接

full(outer) join左外连接 union 右外连接

扩展:

视图:

 一张虚拟表,方便查询,修改表等于修改真实表创建视图 create view使用navicat工具创建使用视图,等于使用表

函数与存储过程：

（都是存储在服务器上的，可以提升数据的安全） 客户端不需要关注具体实现函数：select 函数名(实参)计算返回一个结果，需要指定形参，指定返回值例子：select myadd(1,2);存储过程 一套sql操作，没有返回值call 过程名（实参）;例子：call myselect(3)beginselect 语句
end

索引:

一种查询优化技术，可以提升查询效率 本质是预先存储额外的数据，牺牲存储空间，提升查询效率
索引类型：主键索引 主键唯一索引 唯一约束unique字段普通索引 任何字段（地址）何时需要创建：索引不是创建越多越好表的修改频率非常高，不适合创建索引表的查询频率非常高，几乎不修改，适合创建索引方法:btree 大数据量，适合范围比较hash 适合小数据量，精准的等值比较
例子：select * from user where address='988';

事务:

mysql存储引擎 innoDB支持事务
mysql数据库中对应的一系列操作要么全部执行成功，然后提交，要么执行失败，然后回滚成功commit失败rollback
mysql终端默认自动提交set autocommit=0; 取消自动提交set autocommit=1; 设置自动提交

事务相关:

开启事务start transaction默认不会自动提交insert into teacher set name='t10'存成变量@tid:=id结束事务commit成功提交rollback失败回滚特性acid原则原子性 不可再分，要么全部成功，要么全部失败一致性 执行前后数据完整性，安全性要保持一致隔离性 多个事务相互不影响永久性 一旦提交，则执行完毕，永久改变

存储引擎: create table () engin=引擎名字

数据存储的实现方式，不同的存储引擎适合不同的场景
常用:innoDB默认
特点:支持事务，支持行级别锁定，阻塞，支持外键，综合能力强，适合大多数场景使用后缀为ibd存储内容myisam 特点:查询，排序 ，速度非常快,不支持外键，不支持事务
Memory 特点:读写内存速度最快，但是不能持久化，不能在本地保存
csv 特点:使用逗号隔开，适合导入导出操作

数据库备份：

将数据库信息转储为sql文件，qiku.sql
手动创建数据库 执行sql文件

使用python操作数据库 pymysql

使用pymysql模块 pip install pymysql

使用流程:

    1.导入模块2.构建连接con=pymysql.connect()host,port,user,password,database3.构建游标 cur=con.cursor()4.通过游标执行sqlexecuteexecutemany5.处理sql结果 fetch6.释放游标与连接cur.close()con.close()fetchone获取一个
fetchmany获取指定个数，默认获取一个
fetchall获取所有scroll偏移游标位置
relative相对，正值向后，负值倒着
mode=absolute绝对位置 不能从后往前找

Python使用mysql事务

事务: mysql数据库中对应的一系列操作要么全部执行成功，然后提交
创建表 用存储引擎 innoDB支持事务特性acid原则:原子性 不可再分，要么全部成功，要么全部失败一致性 执行前后数据完整性，安全性要保持一致隔离性 多个事务相互不影响永久性 一旦提交，则执行完毕，永久改变开启事务start transaction默认不会自动提交
commit 提交事务
rollback 回滚​