Made By Herolh

目录

[TOC]

一、MySQL是什么：

服务端 客户端

Mysql数据库

Mysql是最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。 由瑞典MySQL AB公司开发，目前属于Oracle公司。

什么是数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库 我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。 所以，现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。 每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

关系型数据库的特点

RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

• 数据以表格的形式出现
• 每行为各种记录名称
• 每列为记录名称所对应的数据域
• 许多的行和列组成一张表单
• 若干的表单组成database

-- 关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL 数据库的优势

• Mysql是开源的，所以你不需要支付额外的费用。
• Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL使用标准的SQL数据语言形式。
• Mysql可以允许于多个系统上，并且对多种语言有很好支持。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和 Tcl 等。
• MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
• Mysql是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 Mysql 系统。

技能

安装

• 源码安装/exe
• 初始化
  • 启动服务端
  • 客户端连接
    • 发送指令
  • 环境变量

连接：

mysql -u root [-h 主机] -p

# 查看mysql的进程(LINUX下)
ps -ef |grep mysql 	

数据库操作

数据库级别操作：

status：	查看默认设置
desc 表名;  查看表结构

数据表操作：

• 数据类型
  • 约束
    • 唯一索引
    • 主键约束
    • 外键
      • 一对一
      • 一对多
      • 多对多
  • 自增

数据行操作：

• 增删改查
  • 排序
  • 分组
  • 条件
  • 临时表
  • 联表
  • 通配符
  • 分页
  • 组合

视图

触发器

函数

存储过程

• 游标
• 事务逻辑：

pymysql

• 连接 conect()
  • 操作（游标）
    • 增删改 ：commit
    • 查 ：fetchone，fetchall,fatchany
  • 存储过程调用方式
    • callproc("名",参数)
    • select @_存储过程名称_0
  • SQL注入
    • 关闭游标
    • 关闭连接

二、MySQL操作

创建用户

create user ‘username’@'pasword' *identified by* 'password'	

语法代码

use mysql;
grant create,delete,drop,update,insert,select   # 或者直接写grant all
	on TUTORIALS.*                  # 所有数据库.所有表
	to 'username'@'localhost'  
	# 用户名@ 从哪台机子登陆  '%' 代表任意主机登陆
	IDENTIFIED by 'password'        # 登陆密码
	;

授权

grant all privileges *on 库名.表名 to*  'username'@'%' 	

取消授权

revoke al privileges on 库名.表名 *from* 'username'@'%'

查看用户权限

show grants for 用户名;

字符编码

charset = GBK;
create 库名 default charset = utf8;

查看字符集：

• 查看查看MySQL数据库服务器和数据库MySQL字符集。

  show variables like '%char%'; 
  

  # 输出结果
  +--------------------------+----------------------------+
  | Variable_name            | Value                      |
  +--------------------------+----------------------------+
  | character_set_client     | utf8     (客户端字符集)      |
  | character_set_connection | utf8                       |
  | character_set_database   | latin1   (数据库字符集)      |
  | character_set_filesystem | binary                     |
  | character_set_results    | utf8                       |
  | character_set_server     | latin1   (服务器字符集)      |
  | character_set_system     | utf8                       |
  | character_sets_dir       | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+
  

• 查看MySQL数据表（table）的MySQL字符集。

  show table status from sqlstudy_db like '%countries%'; 
  

  # 输出结果
  +---------+--------+--------+------------+------+-----------------+ 
  | Name    | Engine | Version| Row_format | Rows | Collation       | 
  +---------+--------+--------+------------+------+-----------------+
  |countries| InnoDB |     10 | Compact    |   11 | utf8_general_ci | 
  +---------+--------+--------+------------+------+-----------------+
  

• 查看MySQL数据列（column）的MySQL字符集。

  show full columns from countries;  
  

  +----------------------+-------------+-----------------+
  | Field                | Type        | Collation       | 
  +----------------------+-------------+-----------------+ 
  | countries_id         | int(11)     | NULL            | 
  | countries_name       | varchar(64) | utf8_general_ci | 
  | countries_iso_code_2 | char(2)     | utf8_general_ci |
  | countries_iso_code_3 | char(3)     | utf8_general_ci |  
  | address_format_id    | int(11)     | NULL            | 
  +----------------------+-------------+-----------------+
  

修改字符集

暂时修改

修改全局字符集

/*建立连接使用的编码*/
set character_set_connection=utf8;
/*数据库的编码*/
set character_set_database=utf8;
/*结果集的编码*/
set character_set_results=utf8;
/*数据库服务器的编码*/
set character_set_server=utf8;

修改库的字符集

alter database 库名 default character set 字符集;

修改表的字符集

alter table 表名 convert to character set 字符集;

修改字段的字符集

alter table 表名 modify 字段名 字段属性 character set gbk；

永久修改

• 修改 mysql 配置文件

  sudo vi /etc/my.cnf
  

• 在 [mysqld] 上方添加以下设置：

  default-character-set=utf8
  

• 在[mysqld]下方添加以下设置：

  character-set-server=utf8
  collation-server=utf8_general_ci
  

原子性操作

engine = inodb

数据库操作

查看数据库

show databases;

创建数据库

create database 库名;         # 这么写默认不能处理中文
create database 库名 charset utf8;	# 解决中文字符处理问题：

删除数据库

drop database 库名;

进入数据库

use 数据库名;

查看所有表

show tables;

查看表结构

desc 表名;
-- 或
show colums from 表名;

增删改查

创建数据表

CREATE TABLE table_name (column_name column_type);

插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );

查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

UPDATE 更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

DELETE 语句

DELETE FROM table_name [WHERE Clause]

数据类型

int decimal datatime char，text

性别 enum（ ‘','’ ） username char
set()

String类型：

Number类型

Date 类型

其他数据类型

约束

• 唯一约束 unique
• 主键约束 primary key
• 外键约束 constraint 外键名 foreign key（ 列名 ） references 外表（ 列名 ）

自动递增

*auto_increment

通配符

• %

• _

算数、逻辑、比较运算符

排序( order by )

• asc
• desc

分组( group by )

• 多表连接

• 左右联表 : join

• 上下联表 : union

  • 自动去重：

    select * from 表1		（假设十条）
    union
    select * from 表2		（假设十条）
    (返回二十条)
    

  • 不去重

    select * from 表1		（假设十条）
    union
    select * from 表1		
    (返回二十条)
    

分页(limit)

select * from 表名 limit 0,10;

（ 翻的页越大越慢 ）---> 加速方法：只对索引进行扫描

加速方式一：索引加速

# 覆盖索引，但是快不了多少
select * from userinfo3 where uid in ( select uid from limit 20000,10)

最佳方法加速方式：

记录当前页最大值最小值ID：

# 当前页后十条
select * from userinfo3 where uid > 20000 limit 10;					
# 当前页前十条
select * from userinfo3 where uid < 20000 order by id desc limit 10;

分页方式：

页面只有上一页和下一页：

# 上一页：
select * from userinfo3 where uid > max_id limit 10;
# 下一页：
select * from userinfo3 where uid < min_id order by id desc limit 10;

上一页 192 193 [196] 197 198 199 下一页

# 上一页：
select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
)

三、MySQL 高级编程

视图

创建视图( 临时表的反复使用 )

create views 视图名称 as SQL语句( select ....)				# 从真实表动态获取数据

使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。

修改视图：

alter views 视图名称 as SQL语句( select ....)

删除视图：

 drop views 视图名称

触发器( 数据库级别操作 )

当对某张表做增删改操作时，可以使用触发器自定义关联行为

创建触发器

create trigger 触发器名称 before/after (SQL行为语句) ON 表、库

eg:

create trigger tri_before_insert before insert ON tb1 [For EACH ROW 每插入一行就会触发 ]
begin
	...;
end

create trigger tri_before_insert after insert ON tb1 For EACH ROW
begin
	...;
end

注意:

# 查询不会触发触发器

函数( 性能低 )

执行函数：

select 函数名();

内置函数：

聚合函数

select CURDATE();			# 2018-05-15				年-月-日
select CURRENT_TIMESTAMP();	#  2018-05-15 20:53:36		年-月-日 时:分:秒
select CHAR_LENGTH(str);	# 字符串str长度
select CONCAT(str1,str2)	# 字符串拼接

自定义函数：

delimiter //
create function f1(
	i1 int,		--传入参数
	i2 int 
)
return int
begin
	-- 不能写 select * from 表名 等SQL行为
	declare num int default 0;	# 定义临时变量
	set num = i1 + i2;
	return(num);
END//
delimiter ;

存储过程( 更为重要 )

mysql 前几年还没有这个 保存在 MySQL 上的一个别名 => 一坨SQL语句

1.创建存储过程

无参存储过程

delimiter //
create procedure p1()
begin
	INSERT INTO userinfo(username,password) VALUES("user5","pd5");
	SELECT *  FROM userinfo;
end//
delimiter ;

有参数存储过程：( 关键字：inoutinout )

delimiter //
create procedure p2(
	in n1 int,
	in n2 int
)
begin
	select * from userinfo where id > n1;
end//
delimiter ;

• 存储过程没有返回值的概念，out 是传进一个变量，然后你可以在存储过程中改变该变量的值

• out 不往里面传值，也就是虽然在外面 @var=1，实际上 p3 里使用不了 @var 的值

  delimiter //
  create procedure p3(
  	in n1 int,
  	out n2 int
  )
  begin
  	set n2 = 123123;
  	select * from userinfo where id > n1;
  end//
  delimiter ;
  
  set @var = 1;			# 定义一个变量@var = 0
  call p3(8,@var );
  select @var				# @var = 123123
  

• inout：即可往存储过程里面传值，也可以往外传值

！注意：

• 为什么要有结果集，又要有out伪造的的返回值？

  out用于设置一个值，标识存储过程的执行结果

2.调用存储过程：

call p1();

存储过程的优缺点

好处：

网络传的数据就少了

坏处：

存储过程如果改了，程序就GG了

以后工作的方式：

方式一：存储过程

MySQL: 存储过程 程序：调用存储过程

方式二：SQL语句

MySQL：。。 程序：SQL语句

方式三：ORM 框架

MySQL：。。 程序：类和对象（SQL语句）

事务（ 性能不高 ）

事务逻辑：

delimiter //
create procedure p4(
	out status int
)
BEGIN
	# 声明如果出现异常则执行
	{
		set status = 1;
		rollback;
	}

	#开始事务
	SQL;
	commit;

	# 结束
	set status = 2;
END //
delimiter ;

语法代码

delimiter \
create PROCEDURE p5(
	OUT p_return_code tinyint
)
BEGIN 
	DECLARE exit handler for sqlexception 		--声明如果出现异常则执行
	BEGIN 
		-- ERROR 
		set p_return_code = 1; 
		rollback; 
	END; 

	START TRANSACTION; 							--开始事务
		DELETE from tb1;
		insert into tb2(name)values('seven');
	COMMIT; 

	-- SUCCESS 
	set p_return_code = 2; 
END\
delimiter ;

游标( 性能不高 )

创建游标

declare my_cursor CURSOR FOR [SQL语句]select * from A；

使用游标

fetch my_cursor into row_namename,row_psd;

一个循环的例子：

delimiter \
create procedure p6()
BEGIN
	DECLARE row_name varchar(20);		-- 自定义一个变量1
	DECLARE row_psd varchar(50);		-- 自定义一个变量2
	declare done int default False;		-- 自定义循环结束标志

	declare my_cursor CURSOR FOR select username,password from userinfo;
	declare CONTINUE HANDLER FOR NOT FOUND set done = TRUE;				
	-- 当游标为空时改变循环结束标志

	open my_cursor;						-- 打开游标
		SIGN:LOOP						-- 开始循环标志
			fetch my_cursor into row_namename,row_psd;	-- 获取游标内容
				if done then 				
					leave SIGN;				-- breake
				end if;
				insert into userinfo2( row_namename,row_psd) values( row_name,row_psd);
		end loop SIGN;					-- 关闭循环标志
	close my_cursor;					-- 关闭游标
END\
delimiter ;

动态执行SQL语句：( 防SQL注入 )

伪代码：

delimiter \
create procedure p7(
	in str varchar(255),			# 放要执行的SQL语句
	in arg int
)
begin 
	1. 预检测某个东西 SQL语句合法性
	2. SQL =格式化 tpl + arg 
	3. 执行SQL语句
end

语法代码

delimiter \
CREATE PROCEDURE p7(
	in nid int
)
BEGIN
	set @nid = nid;				-- 因为 execute 只能使用@ 所以要给他赋值
	PREPARE prod FROM 'select * from student where sid > ?';		
	-- 预检测SQL语句
	EXECUTE prod USING @nid;										
	-- 拼接
	DEALLOCATE prepare prod; 
END\
delimiter ;

索引( 加速查找 )

对于频繁查找的列要创建索引

作用：

约束

加速查找

慢速查找：

select * from tb where 列名="...”

快速查找：

select * from tb where id = 65

无索引：

从前往后依次查询

有索引：

• 创建额外文件( 某种格式存储)，保存特殊的数据结构
• 查询快，插入更新删除慢
• 命中索引( 版本和版本，数据库和数据库也有不同标准 )

索引类别

按作用分类

• 主键索引：加速查找 + 不能为空 + 不能重复
• 普通索引：加速查找
• 唯一索引：加速查找 + 不能重复
• 联合索引（多列）：
  • 联合主键索引
  • 联合唯一索引
  • 联合普通索引

特殊

以下并非真实索引

• 覆盖索引：

  --在索引文件中直接获取数据
  select id from userinfo3 where email = ".....";
  

• 索引合并：

  --把多个单列索引合并使用
  select id from userinfo3 where email = "....." and pd = ”...“;	
  

按实现方法

hash索引

• 单值快
• 范围慢

btree索引（ 默认下 ）

• 按二叉树查
• 快

索引的创建

普通索引：

create index 索引名 on 表名( 列名 );

唯一索引：

create unique index 索引名 on 表名( 列名 );

联合索引：

create unique index 索引名 on 表名( 列名1，列名2 );

最左前缀匹配：

select * from 表名 where 列名1 = xxx and 列名2 = xxx;		-- 走索引
select * from 表名 where 列名2 = xxx;					  -- 不走索引
-- 联合索引的效率大于索引合并

短索引：

create index 索引名 on 表名( 列名(16) )  -- 对列名1的16个字节之后的数据建立索引

注意: 在 Mysql 里 TEXT 类型要想建立索引必须建立短索引，否则报错

ORM 框架操作 ( 关系对象映射 )

如 SQLAlchmy

• 当一类函数公用同样参数时候，可以转变成类进行 - 分类

• 面向对象： 数据和逻辑（属性和行为）组合在一起 函数编程： 数据和逻辑分离

• 模板“约束”

  提取共性 一类事物共同具有：属性和行为

作用

• 提供简单的规
• 自动转换成SQL语句

ORM框架类别：

• DB first:

  graph LR A(手动创建数据库以及表) --> B(ORM框架 ) B --> C(自动生成类)

• code first：

  SQLAlchmy 属于该类

  graph LR A(手动创建类和数据库) -->B(ORM框架) B -->C(以及表)

注意事项

不用 like

不用like,用 like 永远也命中不了索引

避免使用函数

避免使用 or

特别的：当or条件中有未建立索引的列才失效，以下会走索引

select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'

类型要一致，类型不一致也会使用类型转化函数

普通索引不走以下语法

!=：

select * from tb1 where email != '...'
-- 特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123

>：

select * from tb1 where email > 'alex'
-- 特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123	

order by

select name from tb1 order by email desc;( 前后不一致不走索引 )
-- 当根据索引排序时候，选择的映射如果不是索引，则不走索引
-- 特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;

四、MySQL 注意事项

• 避免使用select *

• count(1)或count(列) 代替 count(*)

• 创建表时尽量时 char 代替 varchar

• 表的字段顺序固定长度的字段优先

• 组合索引代替多个单列索引（经常使用多个条件查询时）

• 尽量使用短索引

• 使用连接（JOIN）来代替子查询(Sub-Queries)

  -- MYSQL里已经没有区别了
  

• 连表时注意条件类型需一致

• 索引散列值（重复少）不适合建索引，例：性别不适合

• 因为mysql默认;为语句结束符，所以当执行复合语句内部[begin...end]时会出错,解决如下：

  delimiter //					修改语句结束符为 //
  create trigger tri_before_insert after insert ON tb1 For EACH ROW
  begin
  ...;
  end//
  delimiter ;						还原，避免其他语句被干扰
  补充：
  -- NEW,代指新数据（添加的时候有）
  insert into tb() values( NEW.user );
  -- OLD,代指老数据( 删除的时候有 ) 
  

补充：

• NEW,代指新数据（添加的时候有）

  insert into tb() values( NEW.user );
  

• OLD,代指老数据( 删除的时候有 )

五、DBA工作

慢日志

• 执行时间 > 10
• 未命中索引
• 日志文件路径

配置：

方式一：内存

show variables like '%query%'
set global 变量名 = 值
set global slow_query_log = ON;			--开启慢日志，默认关闭

方式二：指定配置文件

mysqld --defaults-file='E:wupeiqimysql-5.7.16-winx64mysql-5.7.16-winx64my-default.ini'

my.conf内容：

slow_query_log = ON
slow_query_log_file = D:/....

方式三：Mysql的配置文件里修改