首页  思维导图  详情

mysql

2019-12-26 15:44:51   451  举报





AI智能生成

mysql

mysql高级

作者其他创作

大纲/内容

表数据操作

增

添加单条

INSERT INTO tb_name(`field1`,`field2`,....)VALUES('value1','value2',.....);

添加多条

INSERT INTO tb_name(`field1`,`field2`,....)VALUES('value1','value2',.....),('value1','value2',.....),('value1','value2',.....),....;

删

sql

DELETE FROM tb_name WHERE ...

注意

删除时必须加where条件

改

sql

UPDATE tb_name SET field1 = value1,field2 = value2,..... WHERE ....

注意

修改时必须加where条件

查

基础的查询

SELECT * FROM tb_name

where子句

比较运算符

大于、小于、等于、不等于、大于等于、小于等于

SELECT * FROM tb_name WHERE user_id >10;

逻辑运算符

逻辑运算符是用来拼接其他条件的。用and或者or来连接两个条件，如果用or来连接的时候必须使用小括号

SELECT * FROM tb_name WHERE user_id > 10 AND sex = '男'

LIKE模糊查询

通配符

%（百分号）匹配零个或者多个任意字符

_（下划线）匹配一个任意字符

sql

SELECT * FROM tb_name WHERE username LIKE '张%';查找username开头是张的数据

SELECT * FROM tb_name WHERE username LIKE '%张%';查询username中含有张的数据

SELECT * FROM tb_name WHERE username LIKE '%张';查询username字段的数据以张结尾的

SELECT * FROM tb_name WHERE username LIKE '张_';查询username以张开头后边有一个字符的数据

IN字段指定多个值查询

IN (value1,value2,value3,....)

SELECT * FROM tb_name WHERE user_id IN (1,3,5,7,9,11);查询user_id是1，3，5，7，9，11的所有数据

BETWEEN AND 区间查询

field BETWEEN value1 AND value2

SELECT * FROM user WHERE user_id BETWEEN 2 AND 9;查询user表中user_id大于等于2小于等于9的所有值

GROUP BY 分组查询

配合函数

count(field)获取符合条件出现的非null值的次数

SUM(field)获取所有符合条件的数据的总和

AVG(field)或者平均值

SELECT sex,COUNT(*) count FROM class GROUP BY sex;获取class表中男生和女生的数量

ORDER BY 查询排序

查询顺序

ORDER BY field DESC;降序查询

ORDER BY field ASC;升序查询

SELECT * FROM tb_name ORDER BY id DESC; 查询tb_name表中所有数据，按id的降序来查找

LIMIT 查询结果截取

参数

LIMIT 后边可以跟两个参数，如果只写一个表示从零开始查询指定长度，如果两个参数就是从第一个参数开始查询查询长度是第二个参数的值，俩个参数必须是整形。

SELECT * FROM tb_name LIMIT 5;查询tb_name表中的所有数据，只要前边的5条数据

SELECT * FROM tb_name LIMIT 5,5;查询tb_name中所有的数据，返回的结果是从第五条开始截取五条数据

关联查询

外关联

左关联

SELECT * FORM tb_name1 LEFT JOIN tb_name2 ON tb_name1.t2_id = tb_name2.t2_id;用表一的t2_id和表二的t2_id来关联，查询所有的值。

FROM 之后的表是主表

中关联

SELECT * FORM tb_name1 JOIN tb_name2 ON tb_name1.t2_id = tb_name2.t2_id;用表一的t2_id和表二的t2_id来关联，查询所有的值。

中关联没有主表

右关联

SELECT * FORM tb_name1 RIGHT JOIN tb_name2 ON tb_name1.t2_id = tb_name2.t2_id;用表一的t2_id和表二的t2_id来关联，查询所有的值。

在ON后边的表是主表

内关联

内管理的关联条件是用where来说关联的，多张表之间用AND来拼接where条件

SELECT * FROM tb_name1,tb_name2,.... WHERE tb_name1.t2_id = tb_name2.t2_id AND ...

外关联的说明

主表关联副表，如果副表数据不够用NULL来补全，但是中关联的时候，如果不够了，左边的数据或者右边的数据不会显示。直接去掉

事务

关键词

BEGIN开启事务

ROLLBACK;事务回滚

COMMIT;事务提交

必备条件

表的引擎为InnoDB

数据库导入导出

数据库导出

1.打开cmd命令

2.打开到mysql文件夹下的bin目录

3.通过mysqldump来执行导出

4.命令：mysqldump -u root -p 数据库（class15） > 要导出的文件名如：（test.sql）

5.导出之后的文件会出现在bin目录下

sql文件导入

1.cmd打开到mysql的bin目录下

2.通过 mysql -uroot -p 输入密码的形式进入到数据库中

3.选择数据库 USE db_name;

4.执行导入命令： source d:\datafilename.sql 后边路径是sql文件存放的物理路径

引擎

innoDB

innodb主键使用自增bigint效率比uuid高
1.方便比较大小
2.不会破坏B+TREE结构

聚集索引：索引和数据在同一张表
非聚集索引：索引在一张表，数据在一张表

innodb使用b+tree存索引和数据不使用hash的原因：范围查找使用hash不合适，需要全表扫描，hash(主键)直接存储到位置，因此一般使用B+Tree

show engines查看所有引擎

笔试+面试

数据库操作

创建数据库

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

查询数据库

查询所有数据库

SHOW DATABASES

查询数据库建表时的sql

SHOW CREATE DATABASE db_name;

删除数据库

DROP DATABASE db_name;

修改数据库

修改数据库的字符编码和排序方式

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

选择数据库

USE db_name;

命令行设置之后操作的编码格式

SET NAMES UTF8

数据库表操作

创建表

SQL : CREATE TABLE tb_name (建表的字段、类型、长度、约束、默认、注释)

约束

非空

NOT NULL

非负

UNSIGNED

主键

PRIMARY KEY

自增

AUTO_INCREMENT

默认

DEFAULT

注释

COMMENT

常用类型

极小整形

TINYINT

非负最大值255

1个字节

小整形

SAMLLINT

非负最大值65535

2个字节

整形

INT

非负最大值4 294 967 295

4个字节

单精度

FLOAT

4个字节

定长字符串

CHAR

最大保存255个字节

如果值没有到给定长度用空格补充

变长字符串

VARCHAR

最大保存255个字节

用多大占多大

文本

TEXT

最大保存65535个字节

表字段索引

唯一索引

添加

创建索引

CREATE UNIQUE INDEX index_name ON tb_name (account);

表字段修改

ALTER TABLE tb_name ADD UNIQUE index_name(field_name);

删除

DROP INDEX 索引名称 ON 表名

普通索引

添加

表字段修改

ALTER TABLE 表名 ADD INDEX 索引名称(字段名称);

创建索引

CREATE INDEX Index_name ON tb_name(`account`);

CREATE INDEX 索引名称 ON 表名(字段名);

删除

DROP INDEX 索引名称 ON 表名

主键

添加

ALTER TABLE tb_name ADD PRIMARY KEY (field_name);

ALTER TABLE 表名 ADD PRIMARY KEY (字段名称)

删除

ALTER TABLE tb_name DROP PRIMARY KEY;

联合索引

添加

ALTER TABLE tb_name ADD index_name (field_name1,field_name2);

删除

DROP INDEX 索引名称 ON 表名

索引最左前缀原理：

修改表

表字段的增删改查

字段添加

ALTER TABLE tb_name ADD address VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';

ALERT TABLE tb_name ADD 添加字段字段类型非空约束默认注释

字段类型修改

ALTER TABLE tb_name MODIFY address VARCHAR (50) NOT NULL DEFAULT '' COMMENT '地址';

ALERT TABLE tb_name MODIFY 字段名称新的字段类型非负非空默认注释

字段名称类型修改

ALTER TABLE tb_name CHANGE address addr VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';

ALTER TABLE tb_name CHANGE 旧的字段名新的字段名新的类型约束默认注释

字段类型查询

DESC tb_name;

字段删除

ALTER TABLE tb_name DROP addr;

ALTER TABLE 表名 DROP 删除的字段名

表修改

表名修改

ALTER TABLE tb_name RENAME TO new_tb_name;

ALTER TABLE 旧表名 RENAME TO 新表名

引擎修改

ALTER TABLE tb_name ENGINE = InnoDB;

ALTER TABLE 表名 ENGINE = 新的引擎名称

删除表

DROP TABLE tb_name;

查询表

查询所有表

SHOW TABLES;

查询建表时的sql

SHOW CREATE TABLE tb_name;

MySQL函数

1.数学函数

ABS()绝对值

PI() π值

RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

ROUND(x,y)返回参数x的四舍五入的有y位小数的值

MOD(x,y) 返回x/y的模（余数）

2.聚合函数

AVG(col)返回指定列的平均值

COUNT(col)返回指定列中非NULL值的个数

SUM(col)返回指定列的所有值之和

3.字符串函数

UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

TRIM(str)去除字符串首部和尾部的所有空格

RTRIM(str) 返回字符串str尾部的空格

LTRIM(str) 从字符串str中切掉开头的空格

LENGTH(s)返回字符串str中的字符数

ASCII(char)返回字符的ASCII码值

4.日期和时间函数

CURDATE()或CURRENT_DATE() 返回当前的日期

CURTIME()或CURRENT_TIME() 返回当前的时间

FROM_UNIXTIME(时间戳) 格式化传入的时间戳，转成日期格式

UNIX_TIMESTAMP()获取系统当前的时间戳

NOW()返回当前的时间的日期

5.加密函数

MD5() 计算字符串str的MD5校验和

PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

SHA() 计算字符串str的安全散列算法(SHA)校验和

6.控制流程函数

7.格式化函数

8.类型转化函数

9.系统信息函数

DATABASE() 返回当前数据库名

USER()或SYSTEM_USER() 返回当前登陆用户名

VERSION() 返回MySQL服务器的版本

CONNECTION_ID() 返回当前客户的连接ID

BENCHMARK(count,expr) 将表达式expr重复运行count次

优化

步骤：
1.开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,并将它抓取出来;
2.EXPLAIN+慢SQL分析;
3.SHOW profile,查询SQL在MySQL服务器里面的执行细节和生命周期情况
4.具体优化

explain

语法explain select * from xxl_job_log l where l.job_id in (select id from xxl_job_info)

字段说明

id：表示查询中执行select子句或操作表的顺序

id相同,执行顺序由上至下;
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行;
id相同不同,都存在

select_type：表示查询的类型，主要用于区别普通查询，联合查询，子查询等复杂查询
https://www.cnblogs.com/danhuangpai/p/8475458.html

SIMPLE:简单的select查询，查询中不包括子查询或者UNION

PRIMARY：查询中若包括任何复杂的子部分，最外层查询则被标记为PRIMARY

SUBQUERY:在select或where列表中包含了子查询

DERIVED: 在FROM列表中,包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,
把结果放在临时表里

UNION: 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,
外侧SELECT将被标记为 DERIVE

UNION RESULT: 从UNION表获取结果的SELECT

table：查询的表

type：在表中找到所需行的方式 ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：全部扫描，效率低

index：Full Index Scan，index与ALL区别为index类型只遍历索引树

range：只检索给定范围的行，使用一个索引来选择行
explain select * from employee where rec_id < 3其中rec_id为主键

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
ef_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL： MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

key：显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好

ref：列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows：估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered：

Extra

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句

缺点：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划