Mysql Notes - 难过的吃手手

公告

Mysql Notes

DBMS：

数据库管理系统(Database Manager System),数据库是通过DBMS创建和操作的容器。例如，mysql,oracle......

SQL：结构化查询语言,专门用来与数据库通信的语言。

数据库存储数据的特点：

1.数据放到表中，表放到库中

2.一个库有多个表，表的名字具有唯一性

3.表具有一些特性，特性定义了数据在表中如何存储，类似于“类”的设计

4.表由列组成，也叫字段，表由一个或多个字段组成，类似于“属性”

5.表按行存储，每一行，类似于“对象”

DBMS分为两类：

-基于共享文件系统的DBMS（Access）

-基于客户机-服务器的DBMS（mysql,oracle,sqlserver）

personal settings: root admin 3309 mysql2021

mysql服务的启动：services.msc

mysql服务端的登录：mysql -h localhost -P3309 -u root -p 退出：exit

mysql常见命令：select version(); select database;

创建表：create table 表名（

列名列类型，

列名列类型

）；

DQL语言：data query language

1.基础查询

（1）别名，如果要查询的字段有重名，使用别名可以区分开来。

（2）concat 的作用：例如查询一个员工姓和名链接成一个字段，就可以使用 concat

(3)IFNULL():if...else...

2.条件查询

select 查询列表 from 表名 where 筛选条件

一，按条件表达式筛选： > < >= <= !=

二，按逻辑表达式筛选：and or not

三，模糊查询

1.like :一般和通配符搭配使用

通配符：

% 任意多个字符，包括0个字符

_ 任意单个字符

反斜杠转义关键符号

2.between and

可以提高语句的简洁度

包含临界值

不能随意调换位置

类似 >= and <= 的条件筛选

用于判断某字段的值是否属于in列表中的某一项

使用in提高语句简洁度

in列表的值类型必须统一或兼容

in列表里不支持通配符

is null

=null 或 <> 不能判断null值，只能使用 is null(is not null)

安全等于：is null 仅仅可以判断null值，可读性较高，

<=>既可以判断null值，也可以的判断普通的数值，可读性较低

3.排序查询

select 查询列表 form 表【where 筛选条件】 order by 排序的列表 asc|desc

注意，可以用【列的别名】，【列表达式】，【按函数排序:length(列名)】，【按多个字段进行不同的升降排序】

4.常见函数

将一组逻辑语句封装在方法中，对外暴露方法名

1.隐藏了实现细节

2.提高代码的重用性

3.单行函数：concat：concat(str1,str2,..)

返回结果为连接参数产生的字符串，如何参数为null,则返回值为null

,length,ifnull

3.1字符函数

length,concat,upper,lower,substr（截取从指定索引处后面所有字符）,substring(索引从1开始，)

#姓名中首字符大写，其他字符小写用_拼接

select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2) ) ) from ...

instr ：返回子串第一次出现的索引，找不到返回0

trim: trim('a', from 'aaaaaastringaaaaaa'); ===>string

lpad:用指定的字符实现左填充的长度

rpad:用指定的字符实现右填充的长度

replace 替换： select replace('张无忌爱上了周芷若','周芷若','赵敏')

3.2数学函数

round 四舍五入

ceil 向上取整，返回 >=该参数的最小整数

floor 向下取整，返回 <= 该参数的最大整数

truncate 截断

mod 取余

3.3日期函数

now 返回当前系统日期+时间

curdate 返回当前系统日期，不包含时分秒

curtime 返回当前时间，不包含日期

获取指定部分的年(year(now())),月，日，时，分，秒

str_to_date:将日期格式的字符转换成指定格式的日期 str_to_date('7-11-2021','%m-%d-%y') =>2021-07-11

date_format:将日期转换成字符 date_formate('2021/07/11','%Y年%M月%d日') => 2021年07月11日

3.4其他函数

3.5流程控制函数

1.if函数： if else的效果

2.case when 条件1 then 要显示的值1

select salary ,

case

when salary > 20000 then A

when salary > 15000 then B

when salary > 10000 then C

else 'D'

end as 工资级别

from 表名

5.分组函数：做统计使用，传一组值，返回一个结果。又称聚合函数，统计函数，组函数

4.1 Sum 求和，avg 平均值 max最大值 min最小值

Count 计算个数

分组函数支持的类型：sum、avg用于处理数值型

Max、min、count用于处理任何类型

以上分组都忽略null值

4.2和distinct的搭配可以实现去重计算

4.3 和分组函数一同查询的字段要求是group by字段

6.分组查询

语法： select 分组函数，列（要求出现在group by的后面） from 表名

【where筛选条件】

Group by 分组的列表

【order by】字句

1.使用Having 可以对分组后的条件进行筛选，而where只能对分组前进行筛选

7.连接查询

如果没有添加有效的链接条件，会发生笛卡尔乘积现象

内连接：

等值连接：1.多表等值连接的结果为多表的交集部分

2.n表连接，至少需要一个n-1个连接条件

3.多表的顺序没有要求，

4.一般需要为表起别名 5.可以搭配分组筛选排序。

非等值连接：

自连接：

外连接：

Select 查询列表

From 表1 别名【连接类型】

Join 表2 别名

On 连接条件

Where 筛选条件

内连：inner 【连接类型】

左外连接：left 【连接类型】

应用场景：用于查询一个表中有，另一个表没有的记录

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

如果从表中有和它匹配的，则显示匹配的值

如果从表中没有和他匹配的，则显示null

外连接查询结果=内连结果+主表中有，从表中没有的记录。

左外连接，left左边是主表，右外连接则反之。

右外连接：right 【连接类型】

全外：full:内连结果+表1中有但表2没有的+表2中有但表1没有的。

交叉连接： cross ：查询结果就是一个笛卡尔乘积

8.子查询

出现在其他语句中的select语句，称为子查询或内查询

外部的查询语句，称为主查询

分类：

1. 按子查询出现的位置：

a) Select后面：仅仅支持标量子查询

b) From后面：表子查询

c) Where或having后面：标量子查询（单行），列子查询（多行）行子查询

d) Exists后面：相关子查询

2. 按结果集的行列数：

a) 标量子查询（结果集只有一行一列）

b) 列子查询（多行子查询）

c) 行子查询（多行多列）

d) 表子查询

where或having后面：

特点：a)子查询放在小括号内

b)子查询一般放在条件的右侧

c)标量子查询，一般搭配着单行运算符操作 >,<,=…

d)子查询的执行优先于主查询

in/not in:等于列表的任意一个 => =any

any/some: 和子查询返回的某一个值比较，=> min()

All:和子查询返回的所有值进行比较

9.分页查询

应用场景：当要显示得数据一页显示不全或求取得数据量太大，需要分页提交sql请求

语法：

Select 查询列表 from 表

（Join type） join 表2

On 连接条件

Where 筛选条件

Having ,order by,

Limit 【offset,】size

Offset 要显示条目得起始索引，从0开始

Size 要显示得条目个数

特点：

limit语句放在查询语句的最后
公式：page 要显示的页数，size 每页的条目数

select 查询列表 from 表 limit (page-1)*size,size

查询语句中涉及到关键字，以及执行的先后顺序

Select 查询列表 执行7

From 表名 执行1

连接类型 join 执行2

On 连接条件 执行3

Where 查询条件 执行4

Group by 分组列表 执行5

Having 分组后筛选 执行6

Order by 排序列表 执行8

Limit 偏移，条目数 执行9

10.union联合查询

将多条查询语句的结果合并成一个结果。

语法：查询语句1 Union 查询语句2 ……

应用场景：要查询的结果来自于多个表，且多个表没有直接的链接关系，但查询的信息一致时

特点：1.要求多条查询语句的查询列数是一致的。

2要求多条查询语句的查询的每一列的类型和顺序最好一致。

3.如果不是Union All 则会自动去重

Select * from employees where email like ‘%a%’

Union

Select * from employees where department_id > 9

DML语言：

插入:Insert into 表名(列名，，，)

Values (值，，，)

1.不可以为Null的列必须插入值：直接插入null ,或者不用写该列的名称

2.插入时列的顺序是否可以颠倒?可以，但是值必须一一对应。

3.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致。

4.可以用子查询的方式查询出对应列需要的值然后进行插入数据。

Insert into table(clounm,,) select ‘v1’,,,,

修改

Update 表名 set 列=新值，列=新值…where 筛选条件

修改多表的记录：

Update table Inner join table2 on 连接条件

Set 修改列 where 条件

主键和唯一对比：

事务：

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

事务的ACID属性：

1.原子性：指事务是一个不可分割的单位，事务中的操作要么都发生要么都不发生

2.一致性：事务必须使数据库从一个一致性变换到另外一个一致性状态。（能量守恒）

3.隔离性：指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4.持久性：一个事务一旦被提交，它对数据库中的数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的创建：

隐式事务：事务没有明显的开启和结束标记

比如insert,update,delete语句

显示式事务：必须先设置自动提交功能为禁用。

Set autocommit = 0;

Start transaction 可选

Sql语句1，语句2。。。

Commit;提交

Rollback;回滚

Delete 和 truncate在事务中使用时的区别

Delete可以回滚取消，truncate则不行

Savepoint a 保存节点a 搭配rollback to a （回滚到保存节点a）

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制

就会导致各种并发问题：

脏读：对于两个事务T1，T2,T1读取了已经被T2更新但还没有提交的字段之后，若T2回滚，T1的读取的内容就是临时且无效的。

不可重复读：对于两个事务T1，T2,T1读取了一个字段，T2接着更新了该字段，之后T1再次读取这个字段，值就变了

幻读：对于两个事务T1，T2,T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后如果T1再次读取同一个表，会多出预期的数据

数据库的事务隔离性：数据库必须具有隔离并发运行各个事务的能力，使他们不会相互影响，避免引发各种并发问题。

mysql支持4种事务隔离级别：

读未提交：允许事务读取未被其他事务提交的变更，脏读，幻读，不可重复读仍然可能出现。

读已提交：只允许事务读取已经被其他事务提交的变更，避免脏读，幻读，不可重复读仍然可能出现

可重复读：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和

不可重复度，但幻读的问题仍然存在

串行化：确保事务可以从一个表中读取相同的行，在这个事务持续期间，

禁止其他事务对该表执行插入，更新和删除操作，所有并发问题都可以避免，但性能十分低下。

oracle只支持读已提交和串行化。

视图

虚拟表，和普通表一样使用，通过表动态生成的数据

只保存了sql逻辑，不保存查询结果

应用场景：

-多个地方用到同样的查询结果

-该查询结果使用的sql语句比较复杂

-一定程度上可替代复杂的子查询，提高效率，而且保护了原始数据的安全性。

视图的修改：

Alter view 视图名称

As 查询语句

删除视图

语法：drop view 视图名

查看视图结构：

Desc 视图名

Show create view 视图名

变量：

一．系统变量:变量由系统提供，不是用户定义，属于服务器层面

全局变量：show global variables(查看全局变量)

会话变量

注意：如果是全级别则需要加global，如果什么都不写则默认是会话变量

二．自定义变量：

用户变量:

声明并初始化： SET @用户变量名 = 值；

SET @用户变量名:= 值；

Select @用户变量名:= 值；

查看用户变量名：select @用户变量名；

set @a = 1;

set @b = 2 ;

set @s = @a+ @b;

select @s;

SELECT COUNT(*) INTO @depart_counts FROM departments

SELECT @depart_counts

局部变量：作用域仅仅在定义它的 begin end 中有效

存储过程：类似方法，一组预先编译好的SQL语句的集合，提高代码的重用性，简化操作。

IN:该参数可以作为输入，该参数需要调用方传入值

OUT：该参数可以作为输出，该参数作为返回值

INOUT：该参数可以作为输入也可以作为输出，既需要传入值，又可以返回值

2如果存储过程仅仅只有一句话，begin end 可以省略

存储体的每条SQL语句的结尾必须加分号。
存储过程的结尾可以使用 delimiter 重新设置

语法：delimiter 结束标记（delimiter $）

MSSQL：

Create PROCEDURE Getstudentname

(

@studentid INT --Input parameter ,

Studentid of the student

)

BEGIN

SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid

Mysql:

END

Delimiter $

Create Procedure myp1()

Begin

SQL…

End $

Call myp1()$

创建带 in模式参数的存储过程

Call myp2(‘jack’);

4. 创建带out模式的存储过程

delimiter $

create procedure myp2(in beautyname varchar(20),out boyname varchar(20) )

begin

select bo.boyname from boys bo

right join beauty b on bo.id = b.boyfriend_id

where b.name = beautyname;

end$

set @bname = '';

call myp2('小昭',@bname);

二，调用语法

Call 存储过程名（实参列表）；

函数：

区别：存储过程可以有0个返回，也可以有多个返回，适合做批量插入和批量更新。

函数有且只有一个返回

创建语法：

Create function 函数名（参数列表）return 返回类型

Begin

函数体

End

函数体肯定会有return语句，如果没有会报错。

使用delimiter语句设置结束标记

无参返回函数

delimiter $

create function myf1()

returns int

begin

declare c int default 0;

select count(*) into c

from employees ;

return c;

end $

select myf1()$

有参又返回

delimiter $

create function myf2(empName varchar(20)) returns double

begin

set @sal = 0;#定义一个用户变量

select salary into @sal #赋值

from employees where last_name = empName;

return @sal;

end $

select myf2('Kochhar') $

http://vipmv.tv/okplay/98595-1-1.html 01：19

mysql语法规范： 1.不区分大小写 2.每条命令用分号结尾 3.注释：单行-->#注释文字 or --注释文字；多行--> /* 注释文字 */

索引：帮助mysql高效获取数据的数据结构。

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

2.1 软优化

2.1.1 查询语句优化

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
2.例:

DESC SELECT * FROM `user`

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的三大注意事项:

LIKE关键字匹配'%'开头的字符串,不会使用索引.
OR关键字的两个字段必须都是用了索引,该查询才会使用索引.
使用多列索引必须满足最左匹配.

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1.分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

Op:表示执行的操作.
Msg_type:信息类型,有status,info,note,warning,error.
Msg_text:显示信息.

2.检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:
QUICK:不扫描行,不检查错误的连接.
FAST:只检查没有正确关闭的表.
CHANGED:只检查上次检查后被更改的表和没被正确关闭的表.
MEDIUM:扫描行,以验证被删除的连接是有效的,也可以计算各行关键字校验和.
EXTENDED:最全面的的检查,对每行关键字全面查找.

3.优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

1.配置多核心和频率高的cpu,多核心可以执行多个线程.
2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

key_buffer_size:索引缓冲区大小
table_cache:能同时打开表的个数
query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
sort_buffer_size:排序缓冲区

2.2.3 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。建议收藏备查！MySQL 常见错误代码说明

结语

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

posted on 2022-02-23 19:29 难过的吃手手阅读(4) 评论(0) 收藏举报

刷新页面返回顶部