Mysql

一、数据库

数据库服务器（本质就是一台计算机，该计算机之上安装有数据库管理软件的服务端）

数据库管理系统RDBMS（本质就是一个C/S架构的套接字软件）

库（文件夹）

表（文件）

记录（记录一个事物所有典型的特征/数据）

 

数据库管理系统分类：

关系型：有表结构，存取数据前必先定义表结构，存数据必须按照字段的类型或约束来

非关系型：存取数据都是采用key:value的形式

 

二、Mysql

Mysql其实是采用线程池的socket的服务端

1、基本管理

启动：

启动服务端命令：mysqld

启动客户端：mysql -uroot -p -h 127.0.0.1 -P 3306      本机登录mysql服务端可简写为：mysql -uroot -p

 

把mysql软件做成系统服务：

1）、制作之前先把mysql关掉

2）、制作命令：mysql --install

3）、运行——services.msc查看

 

修改管理员密码命令：

mysqladmin -uroot -p原密码 password '新密码'

 

破解管理员密码：

1）、先关闭mysql服务端

2）、以跳过授权表的方式在命令行中启动mysql服务端，命令：mysqld --skip-grant-tables;

3）、客户端直接以无密码的方式登录root用户，命令：mysql -uroot -p

4）、再起一个客户端，设置新的密码：update mysql.user set password=password('新密码') where user='root' and host='localhost';

5）、刷新：flush privileges;

6）、再命令行中用taskkill杀死mysqld服务，命令：taskkill /F /PID...，然后正常启动mysqld

 

统一字符编码：

在根目录下新建一个my.ini文件，文件内容为：

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

[client]
default-character-set=utf8

[mysql]
user="root"
password="管理员密码"
default-character-set=utf8

 

2、基本的SQL语句

1）、库（文件夹）

增：

    create database 增加的库名 charset 字符编码;

 

改：

    alter database 库名 charset 字符编码;    （库改的只能是字符编码）

 

查：

     查看所有库的库名：show databases;

     单独查看某一个库的信息：show create database 库名;

 

删：

    drop database 库名;

 

2）、表（文件）

首先切换文件夹：use 库名;

查看当前文件夹：select database();

增：

    create table 增加的表名(

    字段名1 类型[宽度 约束条件],

    字段名2 类型[宽度 约束条件],

    字段名3 类型[宽度 约束条件]

     );             举例：create table t1(id int,name char);

 

改：

    修改表名：

         alter table 表名 rename 新表名;

    修改字段：

         alter table 表名 modify 字段名 数据类型[约束条件];

         alter table 表名 change 旧字段名 新字段名 数据类型[约束条件];

    增加字段：

         alter table 表名 add 字段名 数据类型[约束条件];

         alter table 表名 add 字段名 数据类型[约束条件] first;     （把字段加到第一位）

         alter table 表名 add 字段名 数据类型[约束条件] after 字段名;     （把字段加到某一个字段的后面）

     删除字段：

          alter table 表名 drop 字段名;

     复制表：

           复制表结构 + 记录：

               create table 复制的表名 select * from 被复制的表名;

          只复制表结构：

               create table 复制的表名 select * from 被复制的表名 where 假的条件;

 

查：

    查看当前库下所有的表名：show tables;

    查看表的详细信息：show create table 表名;

    查看表结构：describe 表名;    简写为：desc 表名;

 

删：

     删除表：

         drop table 表名;

     清空表：

          truncate 表名;

 

3）、记录（文件的一行内容）

增：

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

 

改：

    update 表名 set 字段的值 where 判断条件;

 

查：

    select 字段 from 表名;

 

删：

    delete from 表名 where 判断条件;

 

三、表操作

存储引擎：专门用来处理不同类型的文件

 

1、创建表的完整语法：

create table 增加的表名(

字段名1 类型[宽度 约束条件],

字段名2 类型[宽度 约束条件],

字段名3 类型[宽度 约束条件]

);    

 

类型：类型是限制字段必须以什么样的数据类型传值

约束条件：约束条件是在类型之外添加一种额外的限制

 

注意：

1）、在同一张表中，字段名是不能相同的

2）、宽度和约束条件可选，字段名和类型是必须的

3）、最后一个字段后不能加逗号

 

四、mysql常用数据类型

1、数字
1）、整型：
tinyinit、int、bigint
有符号数就是带正负号的，无符号数就是只有正整数和零，没有负数
默认整型都是有符号的

对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制，所以在创建表时，如果字段采用的是整型类型，完全无需指定显示宽度

默认的显示宽度，足够则显示完整当初存放的数据，不足够用0填充，如果超出则正常显示

2）、浮点型：
float、double、decimal

float ：在位数比较短的情况下不精准
double ：在位数比较长的情况下不精准
decimal：最精准，内部原理是以字符串形式去存

float[(m,d)]
m是数字总个数，d是小数点后个数，m最大值为255，d最大值为30

相同：对于三者来说，都能存放30位小数
不同：
1、float和double类型能存放的整数位比decimal更多
2、精度排序从低到高：float---double---decimal

3）、mysql模式：
查看mysql模式：
show variables like '%sql_mode%';

修改sql_mode为严格模式：
set global sql_mode = 'strict_trans_tables';
在该模式下，如果插入的数据超过限制，则会立即报错

2、字符：
char、varchar
字符的宽度限制单位是字符个数

char：定长，超过则报错，不够则用空格补全
varchar：变长，超过则报错，不够则不变

针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中，但会在读出结果时自动去掉末尾的空格，因为末尾的空格在以下场景中是无用的

字段明确的等于一个值，该值后填充的空格是没用的；like模糊匹配一个值，该值后的空格是有用的

对比char和varchar
char：缺点：浪费空间；优点：存取速度都快
varchar：缺点：存取速度都慢；优点：节省空间

 

3、日期类型：
date、time、datetime、year

 

4、枚举与集合类型：
枚举enum，多选一
集合set，多选多

五、约束条件：

unsigned 指定整型为无符号
zerofill 使用0填充
not null 字段不能为空
default 字段设置为默认值，通常与not null连用
unique 限制字段的值是唯一的，分为单列唯一和联合唯一

primary key 主键
单单从约束角度看，primary key就等同于not null unique，不为空且唯一，分为单列主键和联合主键
1）、一张表中必须有，并且只能有一个主键
2）、一张表中都应该有一个id字段，而且应该把id字段做成主键

auto_increment 自动增加，默认从1开始，步长为1
1）、通常与primary key连用，而且通常是给id字段加
2）、auto_increment只能给被定义成key（unique key，primary key）的字段加，不能单独使用

foreign key 外键
语法：
foreign key(关联表id) references 被关联表名(被关联表id);

foreign key带来的约束效果：
1）、创建表时，必须先创建被关联的表，才能创建关联表
2）、插入记录时，必须先插入被关联的表，才能插关联表
3）、更新与删除都需要考虑到关联与被关联的关系
解决方案：1、先删除关联表，再删除被关联表，准备重建
2、重建：新增功能，同步更新，同步删除
语法：
foreign key(关联表id) references 被关联表名(被关联表id)
on update cascade on delete cascade;

六、表关系
把所有数据都存放于一张表的弊端：
1）、表的组织结构复杂不清晰
2）、浪费空间
3）、扩展性极差

如何找出两张表之间的关系：
步骤1）、先站在左表的角度去找，如果左表的多条记录可以对应右表的一条记录，则证明左表的一个字段 foreign key 右表一个字段（通常是id）
步骤2）、再站在右表的角度去找，右表的多条记录可以对应左表的一条记录，则证明右表的一个字段 foreign key 左表一个字段（通常是id）
总结）、多对一：
如果只有步骤1成立，则是左表多对一右表；如果只有步骤2成立，则是右表多对一左表
多对多：
如果步骤1和2同时成立，则证明这两张表是一个双向的多对一，即多对多，需要定义一个 这两张表的关系表来专门存放二者的关系
一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况 就在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

清空表：
delete命令确实可以将表里的所有记录都删掉，但不会将id重置为0
所以delete命令根本不是用来清空表的，而是用来删除表中某一些符合条件的记录，如delete from tb1 where id > 10;

如果要清空表，应使用truncate 表名；
作用：将整张表重置

多对一：

多对多：
两张表之间是一个双向的多对一关系，称之为多对多

如何实现：
建立第三张表，该表中有一个字段是左表的id，还有一个字段是右表的id

一对一：
左表的一条记录唯一对应右表的一条记录，反之也一样
除了foreign key，还要再加一个关联表id是唯一的，用unique

单表查询

语法：
select distinct 查询字段1,查询字段2 from 表名
where 分组之前的过滤条件
group by 分组依据
having 分组之后的过滤条件
order by 排序字段
limit 显示的条数;

关键字的执行优先级：
from --> where --> group by --> having --> select --> distinct --> order by --> limit
1、找到表：from
2、拿着where指定的约束条件，去文件/表中取出一条条记录
3、将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4、将分组的结果进行having过滤
5、执行select
6、distinct去重
7、将结果按条件排序：order by
8、限制结果的显示条数

where 过滤条件
1、比较运算：>、<、>=、<=、!=
2、between .. and .. 在..到..之间
3、集合查询：in(.., .., ..) 多选一
4、模糊查询：like '%_' %表示任意多字符，_表示一个字符
5、逻辑运算符：and、or、not
6、is null 判断某个字段是否为null不能用等号，需要用is

group by 分组查询
分组指的是将所有记录按照某个相同字段进行归类
分组发生在where之后，即分组是基于where之后得到的记录而进行的

设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据
命令：set global sql_mode="strict_trans_tables,only_full_group_by";

聚合函数：
聚合函数聚合的是组的内容，若是没有分组，则默认一组
count() 统计个数
max() 最大值
min() 最小值
avg() 取平均数
sum() 求和

字符串拼接操作，也属于聚合函数：
group_concat() 用于拼接字符串，在分组之后用
concat() 用于拼接字符串，不分组时用
concat_ws() 字段之间拼接加入相同的字段

查询四则运算：
+ - * /

as 起别名

having 过滤
having的语法格式与where一模一样，只不过having是在分组之后进行的进一步过滤
即where不能用聚合函数，而having是可以用聚合函数的
having必须在group by后面使用

distinct 去重
执行角度是在having之后执行，但语法角度是放在select之后

order by 排序
升序排：asc，mysql默认就是升序排
降序排：desc
例：select * from 表名 order by a desc,b asc; 先按照a降序排，如果a中有相同的部分，再把相同的部分按照b升序排

order by在执行角度和语法角度都是在having之后

limit 限制显示条数
默认从头开始显示，语法：limit 数字;
分页显示，语法：limit 数字,数字; 第一个数字是从哪开始取，第二个数是每次取多少条

正则表达式查询

多表查询

内连接：把两张表有对应关系的记录连接成一张虚拟表，即笛卡尔积。不要用where做连表的活
语法：
select * from 表1 inner join 表2 on 两表相同的部分; 两表相同的部分可以用=相连

左连接：在内连接的基础上保留左表没有对应关系的记录，即优先显示左表全部记录
语法：
select * from 表1 left join 表2 on 两表相同的部分;

右连接：在内连接的基础上保留右表没有对应关系的记录，即优先显示右表全部记录
语法：
select * from 表1 right join 表2 on 两表相同的部分;

全连接：在内连接的基础上保留左、右两边表没有对应关系的记录，即显示左右两个表全部记录
语法：
select * from 表1 left join 表2 on 两表相同的部分
union
select * from 表1 right join 表2 on 两表相同的部分;

虚拟表连接查询：
多表连接可以不断的与虚拟表连接

子查询：
子查询是将一个查询语句嵌套在另一个查询语句中，即把一个查询语句用括号括起来，当做另外一条查询语句的条件去用
语法：
select 字段1 from 表名1 约束条件1的前半部分 (select 字段2 from 表名2 约束条件2);

子查询中可以包含：in、not in、any、all、exists、not exists等关键字和=、!=、>、<运算符
in是= any的简写 ，not in是< > all的简写

 

七、pymysql模块

pymysql.connect()    打开数据库连接
cursor()      创建一个游标对象，结果集以元组显示
execute()     执行SQL查询、为数据库创建表等
rowcount     这是一个只读属性，并返回执行execute()方法后影响的行数
fetchone()     取出游标中的一条记录装入变量，以字典显示
fetchall()     接收全部的返回结果行，以列表显示，列表里面是字典
close()    关闭数据库连接

游标cursor：
一种能从包括多条数据记录的结果集中每次提取一条记录的机制。尽管游标能遍历结果中的所有行，但它一次只指向一行
游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由select语句产生
游标用完之后一定要关闭和释放

 

八、视图

视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可
视图在硬盘中只有表结构，没有表数据文件
视图通常是用于查询，尽量不要修改视图中的数据

建立视图语法：
create view 视图名 as SQL语句

修改视图
alter view 视图名 as SQL语句

删除视图
drop view 视图名

九、触发器
在满足对某张表数据的增create、删delete、改update（没有查）的情况下，会自动触发的功能称为触发器

创建触发器语法：
插入后：
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
SQL语句
end

插入前：
create trigger tri_before_insert_t2 before insert on 表名 for each row
begin
SQL语句
end

删除前：...before delete on...
删除后：...after delete on...
更新前：...before update on ...
更新后：...after uptate on...

delimiter的作用就是对整个小段语句做一个简单的封装
delimiter $$
sql语句
$$
delimiter ;

使用触发器：
触发器无法由用户直接调用，而是由于对表的【增/删/改】操作被动引发的

删除触发器：
drop trigger tri_before_insert_t2

十、事务
事务指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。事务可以是一条SQL语句、一组SQL语句或整个程序

用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

事务具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）：持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

语法：
start transaction;
SQL语句
rollback; #出现异常，回滚到初始状态
commit; #把事务所做的修改保存到数据库

十一、存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql，可以看做是python中面向对象方法的模拟

使用存储过程的优点：
1、用于替代程序写的SQL语句，实现程序与SQL解耦合
2、基于网络传输，传别名的数据量小，而直接传sql数据量大
缺点：程序员扩展功能不方便

开发模式：
1、应用程序：只需要开发用于应用程序的逻辑
mysql：编写好存储过程，以供应用程序调用
优点：开发效率，执行效率都高
缺点：考虑到人为因素，跨部门沟通等问题，扩展性差

2、应用程序：除了开发应用程序的逻辑，还需要编写原生sql
mysql：
优点：比方式1的扩展性高（非技术性）
缺点：开发效率、执行效率都不如方式1；编写原生sql太过于复杂，而且需要考虑sql语句的优化问题

3、应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据
mysql：
优点：不用再编写原生sql，这意味着开发效率比方式2高，同时兼顾方式2扩展性高的好处
缺点：执行效率连方式2都比不过

存储过程的参数有3类：
1、in 仅用于传入参数用，存储过程中修改该参数的值不能被返回，为默认值
2、out 仅用于返回值用，该值可在存储过程内部被改变
3、inout 既可以传入又可以当作返回值

参数in：

创建存储过程：
create procedure 存储过程的名字(in 参数1 数据类型,in 参数2 数据类型)
begin
sql语句
end

如何用存储过程：
1、直接在mysql中调用
call 存储过程的名字();

2、在python中基于pymysql调用
cursor.callproc('存储过程的名字')

参数out：

创建存储过程：
create procedure 存储过程的名字(in 参数1 数据类型,out 参数2 数据类型)
begin
sql语句
set 参数2 = 1;
end

如何用存储过程：
1、直接在mysql中调用
set @参数2 = 0; #0代表假（执行失败），1代表真（执行成功）
call 存储过程的名字(参数1,);
select @参数2; #查询select的查询结果

2、在python中基于pymysql调用
cursor.callproc('存储过程的名字',(参数1,参数2 = 1))
cursor.execute('select @_存储过程的名字_0,@_存储过程的名字_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值

参数inout：

创建存储过程：
create procedure 存储过程的名字(inout 参数 数据类型)
begin
sql语句
set 参数 = 1;
end

如何用存储过程：
1、直接在mysql中调用
set @参数 = 3;
call 存储过程的名字(@参数);
select @参数;

2、在python中基于pymysql调用
cursor.callproc('存储过程的名字',(参数,))
cursor.execute('select @_存储过程的名字_0;')

删除存储过程：
drop procedure 存储过程的名字;

函数
mysql内置的函数只能在sql语句中使用，不能单独调用
1、数字函数
2、聚合函数
3、字符串函数
4、日期和时间函数
5、加密函数
6、控制流函数

删除函数：
drop function 函数名;

执行函数：
#获取返回值：
select UPPER('egon') into @res;
SELECT @res;

# 在查询中使用
select f1(11,nid) ,name from tb2;

流程控制
1、if条件语句
语法：
if ... then
...
elseif ... then
...
else
...
end if;

2、循环语句
1）、while循环
语法：
while ... do
...
end while;

2）、repeat循环 #repeat循环是在执行操作后检查结果，而while则是执行前进行检查
语法：
repeat
...
end repeat;

3）、loop循环

loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

十二、索引

1、为什么要用索引：
对于一个应用来说，对数据库的读写，读多写少
而且对于写来说，极少出现性能问题，大多数性能问题都是慢查询

2、什么是索引：
索引相当于书的目录，是mysql中一种专门的数据结构，称为key
索引的本质原理就是通过不断地缩小查询范围为，来降低IO次数，从而提升查询性能

3、索引的影响：
1）、在表中有大量数据的前提下，创建索引速度会很慢
2）、在索引创建完毕后，对表的查询性能会大幅度提升，但写的性能会降低

4、聚集索引（primary key）
特点：叶子节点存放的是一整条数据

5、辅助索引（unique、index）
特点：如果是按照这个字段创建的索引，叶子节点存放的是：{名字:名字所在的那条记录的主键的值}

6、覆盖索引
只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

7、创建索引

十三、ORM对象关系映射

ORM将数据库中的表，字段，行与我们面向对象编程的类及其方法、属性等一一对应，即将该部分操作封装起来，我们不需懂得sql语句即可完成对数据库的操作。ORM相当于中继数据

将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
第一个阶段：将对象转换成可执行的sql语句
第二个阶段：将sql语句交给数据库执行

类===>表
对象==>表中的一行记录