Mysql 从入门到遗忘

程序员文章站 2022-07-04 22:35:01

Made By Herolh 目录 { index} [TOC] 一、MySQL是什么：服务端客户端 Mysql数据库 Mysql是最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据 ......

made by herolh

一、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:/....

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

上一篇：张飞曾经和吕布交手赐死张飞到底有没有输给吕布

下一篇： SQLyog使用期限（治标不治本的，治本的还没找到）

Mysql 从入门到遗忘

目录

一、mysql是什么：

mysql数据库

什么是数据库

关系型数据库的特点

mysql 数据库的优势

技能

安装

连接：

数据库操作

数据库级别操作：

数据表操作：

数据行操作：

视图

触发器

函数

存储过程

pymysql

二、mysql操作

创建用户

语法代码

授权

取消授权

查看用户权限

字符编码

查看字符集：

修改字符集

暂时修改

修改全局字符集

修改库的字符集

修改表的字符集

修改字段的字符集

永久修改

原子性操作

数据库操作

查看数据库

创建数据库

删除数据库

进入数据库

查看所有表

查看表结构

增删改查

创建数据表

插入数据

查询数据

update 更新数据

delete 语句

数据类型

string类型：

number类型

date 类型

其他数据类型

约束

自动递增

通配符

算数、逻辑、比较运算符

排序( order by )

分组( group by )

分页(limit)

加速方式一：索引加速

最佳方法加速方式：

分页方式：

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

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

三、mysql 高级编程

视图

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

修改视图：

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

创建触发器

注意:

函数( 性能低 )

执行函数：

内置函数：

聚合函数

自定义函数：

存储过程( 更为重要 )

1.创建存储过程

无参存储过程

事务（性能不高）

btree索引（默认下）

Oracle数据库从入门到精通多表查询知识以及范例