Made By Herolh
目录 {#index}
一、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类型:
数据类型: | 描述 | 存储 |
---|---|---|
char(n) | 固定长度的字符串。最多 8,000 个字符。 定义类型为char(5),那么就表示该类型可以存储5个字符 即使存入2个字符,剩余的3个字符也会用空格补齐。 |
Defined width |
varchar(n) | 可变长度的字符串。最多 8,000 个字符。 定义类型为varchar(5),那么就表示该类型可以存储5个字符 如果存入 2 个字符,字符长度就是 2 而不是 5 |
2 bytes + number of chars |
varchar(max) | 可变长度的字符串。最多 $1,073,741,824$ 个字符。 | 2 bytes + number of chars |
text | 可变长度的字符串。最多 2GB 文本数据。 | 4 bytes + number of chars |
nchar | 固定长度的 Unicode 字符串。最多 4,000 个字符。 | Defined width x 2 |
nvarchar | 可变长度的 Unicode 字符串。最多 4,000 个字符。 | |
nvarchar(max) | 可变长度的 Unicode 字符串。最多 536,870,912 个字符。 | |
ntext | 可变长度的 Unicode 字符串。最多 2GB 文本数据。 | |
bit | 允许 0、1 或 NULL 如果表中的列为8bit,则这些列作为一个字节存储 如果列为9-16bit,这这些列作为2个字节存储,以此类推 |
|
binary(n) | 固定长度的二进制字符串。最多 8,000 字节。 | |
varbinary | 可变长度的二进制字符串。最多 8,000 字节。 | |
varbinary(max) | 可变长度的二进制字符串。最多 2GB。 | |
image | 可变长度的二进制字符串。最多 2GB。 |
Number类型
数据类型 | 描述 | 存储 |
---|---|---|
tinyint | 允许从 $0$ 到 $255$ 的所有数字。 | 1 字节 |
smallint | 允许介于 $-32,768$ 与 $32,767$ 的所有数字。 | 2 字节 |
int | 允许介于 $-2,147,483,648$ 与 $2,147,483,647$ 的所有数字。 | 4 字节 |
bigint | 允许介于 $-9,223,372,036,854,775,808$ ~ $9,223,372,036,854,775,807?$ 之间的所有数字。 | 8 字节 |
decimal(p,s) | 固定精度和比例的数字。 允许从 $-10^{38} +1$ 到 $10^{38} -1?$ 之间的数字。 p 参数指示可以存储的最大位数(小数点左侧和右侧)。 p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 |
5-17 字节 |
numeric(p,s) | 固定精度和比例的数字。 允许从 $-10^{38} +1$ 到 $10^{38} -1$ 之间的数字。 p 参数指示可以存储的最大位数(小数点左侧和右侧)。 p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 |
5-17 字节 |
smallmoney | 介于 $-214,748.3648$ 与 $214,748.3647$ 之间的货币数据。 | 4 字节 |
money | 介于 $-922,337,203,685,477.5808$ ~ $922,337,203,685,477.5807?$ 之间的货币数据。 | 8 字节 |
float(n) | 从 $-1.79E + 308$ 到 $1.79E + 308$ 的浮动精度数字数据。 n 参数指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节,而 float(53) 保存 8 字节。n 的默认值是 53。 |
4 或 8 字节 |
real | 从 $-3.40E + 38$ 到 $3.40E + 38$ 的浮动精度数字数据。 | 4 或 8 字节 |
Date 类型
数据类型 | 描述 | 存储 |
---|---|---|
datetime | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 3.33 毫秒。 | 8 字节 |
datetime2 | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 100 纳秒。 | 6-8 字节 |
smalldatetime | 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日,精度为 1 分钟。 | 4 字节 |
date | 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 | 3 bytes |
time | 仅存储时间。精度为 100 纳秒。 | 3-5 字节 |
datetimeoffset | 与 datetime2 相同,外加时区偏移。 | 8-10 字节 |
timestamp | 存储唯一的数字,每当创建或修改某行时,该数字会更新。 timestamp 值基于内部时钟,不对应真实时间。 每个表只能有一个 timestamp 变量。 |
其他数据类型
数据类型 | 描述 |
---|---|
sql_variant | 存储最多 8,000 字节不同数据类型的数据,除了 text、ntext 以及 timestamp。 |
uniqueidentifier | 存储全局唯一标识符 (GUID)。 |
xml | 存储 XML 格式化数据。最多 2GB。 |
cursor | 存储对用于数据库操作的指针的引用。 |
table | 存储结果集,供稍后处理。 |
约束
- 唯一约束 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 ;
有参数存储过程:( 关键字:in\out\inout )
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:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini‘
my.conf内容:
slow_query_log = ON
slow_query_log_file = D:/....