mysql学习记录-CSDN博客

本文链接：https://blog.csdn.net/qq_35306443/article/details/99406514

初级部分

安装---计算机上的服务需要启动

什么是存储引擎？

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不
同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
MySQL 的核心就是存储引擎。提示：InnoDB 事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定
和外键。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。

如何选择 MySQL 存储引擎

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。

功能	MylSAM	MEMORY	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持树索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

可以根据以下的原则来选择 MySQL 存储引擎：

如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

记录：
 WHERE NAME IS NOT NULL---》mysql中判断字段非空
delete 支持事务回滚，truncate 不支持事务回滚
limit 用法
SELECT * FROM dept LIMIT 1; //第一个开始，默认选取一个
SELECT * FROM dept LIMIT 1,2; //从第二个开始 ，选取两个.

可以与group by 联合使用选取最大，最小值

select 语句

1用distinct关键字，如果结果中有完全相同的行，就去除重复行

SELECT DISTINCT alogin FROM account

2起别名用as，可省略as

SELECT id xx FROM account

3加号的作用：仅仅只有一个作用运算符

SELECT 100+90 结果100--》两个都为数值类型作加法运算

SELECT 'a'+90 结果90--》其中有字符型，将字符型转换为数值型，成功，做加法，失败，字符型的值为0

SELECT 'a'+90 结果90 SELECT null+90 结果null

mysql查询

条件查询 where

between ...and ....在....之间

not....between ...and ... 不在之间

mysql 函数

select 函数名（实参列表）【from 表】--实参列表用到

1字符函数

UPPER(s)

将字符串转换为大写

TRIM(s)

去掉字符串 s 开始和结尾处的空格

2数字函数

ABS(x)	返回 x 的绝对值
CEIL(x)	返回大于或等于 x 的最小整数

FLOOR(x)

返回小于或等于 x 的最大整数　　

COUNT(expression)

返回查询的记录总数，expression 参数是一个字段或者 * 号

SELECT ROUND(1.23456,3) -->保留三位小数，四舍五入

3日期函数

now() -->select now()-->返回当前日期和时间

CURTIME() 返回当前时间,不包含日期

CURDATE()

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

YEAR(d) 返回年 -->select year(now())

str_to_date(str, format) 函数 --》日期格式字符串转化为指定格式的日期

date_format(date, format) 函数--》日期转为字符串

月	%m	两位数字表示月份（01,02, ...,12）
月	%c	数字表示月份（1,2, ...,12）
年	%Y	四位数字表示的年份（2015,2016...）
年	%y	两位数字表示的年份（15,16...）

天

两位数字表示月中天数（01,02, ...,31）

秒	%S、%s	两位数字形式的秒（ 00,01, ..., 59）
分	%I、%i	两位数字形式的分（ 00,01, ..., 59）
小时	%H	24小时制，两位数形式小时（00,01, ...,23）
	%h	12小时制，两位数形式小时（00,01, ...,12）

SELECT STR_TO_DATE('1998-1-2', '%Y-%m-%d')//字符串转为日期

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); //时间转字符串

其他函数：

SELECT USER()--》当前用户
SELECT VERSION()--》版本号

SELECT ISNULL(NULL)--》值为1，否则SELECT ISNULL(‘a’)-->值为0

IFNULL(v1,v2)

如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

IF(expr,v1,v2)

如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。

CASE：函数

常见的分组函数：
   sum（字段）：求该字段的所有值的和
   avg（字段）：求该字段的平均值
   max（字段）：求最大值
   min（字段）：求最小值
   count(字段)：计算该字段中的非空值的个数

sum和avg只支持数值型做参数max、min、count可以支持任意类型做参数，分组函数都忽略null，可以和distinct 使用--》select sum(distinct salary) from emp;

分组函数对应分组查询:

语法：select 分组函数，column from table [where condition] [group by ] column having 条件

列必须出现在group by的后面

比如-->查询每个部门的平均工资--》select sum(salary),dept_id from dept group by dept_id.（相当于只有个不同的部门存活）

1.多个字段分组，比如按照部门，和工资分组--》group by dept_id,salary; -->用逗号隔开。

2group by 后面支持函数---如按照名字长度分组 group by length(name);

3 支持排序 group by length(name) order by datetime---放在group by的后面

多表查询

多个表查询会用到连接条件，如果没有有效的连接条件，就会用其中一张表的数据，区另一张表区匹配所有。

如果第一个表有m行，第二个表n行，就会查出m*n，-----》这是错的。解决办法----添加有效的连接条件。

1等值连接：连接条件用"=" 表示如：where a.id=b.id;

SELECT * FROM tbl_dept a ,tbl_emp b WHERE a.id = b.deptId;

2内连接 SELECT * FROM tbl_dept a INNER JOIN tbl_emp b ON a.id = b.deptId;

3非等值连接 where 后面条件已经不用等号了

4自连接：一张表自己连接自己--需要一张表查询两边以上，例如，查出一个员工的名字，和他上级的名字

可以先查出员工的名字和他上级的编号，再根据上级的编号进行查询-

select e.name,e.id,m.name,m.id from emp e,emp m where e.id=m.id;

内连接类似等值连接

外连接（左外和右外）

左外连接--左边的主表

右外连接--右边的是主表

交叉连接--就是笛卡尔积 cross

全外连接--full---目前不支持

子查询

子查询---出现在其他语句的select,称为子查询--不仅仅的出现在查询语句中。


1、where型子查询：把内层查询的结果作为外层查询的比较条件
2、from型子查询：把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待。临时表要
使用一个别名。
3.exists型子查询：把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出。内层查询是
exists后的查询。也叫相关子查询，可以用in代替
4. any, in ，all子查询
5.select 支持标量，from 表子查询

标量子查询，单行子查询---结果集只有一行一列--where having后面，> < >= <= =
列子查询---结果集只有一列多行  in,any,all
行子查询---结果集一行多列---就是结果集是一个表的一行多个字符段
表子查询----多行多列

子查询优先于主查询，子查询结果集只能是一行一列

------------
SELECT * FROM USER WHERE NAME =(
SELECT MAX(NAME) FROM USER);// 分组函数查询返回的字段默认就是原字段

联合查询 union---将查询语句的多个结果合并成一个结果集--查询两张表没有关系

SELECT * FROM USER WHERE NAME='qqq' AND addr='ccc'

SELECT * FROM USER WHERE NAME='qqq' UNION SELECT * FROM USER WHERE addr='ccc';

注意：使用时多个表的字段一致，顺序相同。使用union 去重，不想去重在union后面加上all,则是union all

dml 数据操作语言--插入，修改，删除。

insert into table values(1，‘xx’),(2,'xde');//支持插入多行

修改 update table set 列=值，列=值 where condition.

--->可以修改多表：update table1 inner join table on 连接条件 set 列=值，，，，

删除delete from table where condition

ddl 数据库定义语言

create database 库名---创建数据库

user database 使用数据库

drop database if exists 库名

表的创建

表的修改：

修改列名，

ALTER TABLE USER CHANGE COLUMN addr addx INT;// 修改列名的时候可以修改类型，COLUMN 可以省略

修改类型和约束，

ALTER TABLE USER MODIFY COLUMN addx VARCHAR(32)

添加列，

ALTER TABLE USER ADD COLUMN sal DOUBLE ;

删除列，

ALTER TABLE USER DROP COLUMN sal ;

修改表名

ALTER TABLE USER RENAME TO new_table_name

-------------总结 ALTER TABLE table_name drop |add| modify| change column 列名类型---------

表的删除 drop table table_name

表的复制：

CREATE TABLE userxc LIKE userxx //复制表的结构--没有数据

CREATE TABLE userd1 SELECT * FROM userxc //复制表的结构，有数据。后面的查询语句就是查询出结构和数据

mysql 中数据类型：

1数值类型：整形，浮点

TINYINT、MEDIUMINT和BIGINT。一样的，区别就是范围大小不一样。

创建无符号类型字段使用UNSIGNED 关键字，默认有符号值，如果插入的数据超出范围，会包out of range 异常，并插入临界值。不设置长度，会有默认长度，如int(11),这个11不是指类型的范围，而是数值显示的长度，使用时候需要zerofill 搭配，并且不支持有符号数（默认没有负数）

CREATE TABLE des(
id INT UNSIGNED
)
INSERT INTO des VALUE(-1) #可以插入，但是结果为0。

浮点型 float 4个字节 double 八个字节

DECIMAL(m,d) 定点型 m--小数部分+整数部分，d小数部分。如DECIMAL(5,2)最大值999，99 ，如果插入的数据超出范围，就用临界值代替。m,d 都可以省略，float,double一般无所谓，decimal 默认为（10,0）。--使用decimal 需要精度比较高。

demo:
CREATE TABLE des1(
age DOUBLE(5,2),
age1 FLOAT(5,2),
age2 DECIMAL(5,2)
)

选择的类型越简单越好，保存的数值越小越好。

字符串类型较短的文本char,varchar, 长文本 text,blob

日期和时间类型

DATETIME 混合日期和时间值

TIMESTAMP 混合日期和时间值，时间戳--受到时区影响。

DATE 只保存日期

time 保存时间

year 保存年份

什么是约束？

约束实际上就是表中数据的限制条件
非空约束(not null)
唯一性约束(unique)
主键约束(primary key) PK
外键约束(foreign key) FK
default 设置默认值
检查约束(目前MySQL不支持、Oracle支持)

demo:列级约束

CREATE TABLE a(
id int PRIMARY KEY,
NAME char(11) NOT NULL,
did int UNIQUE,
age int  INT DEFAULT 18
)

表级约束可以给约束起名字
demo:
CREATE TABLE a(
id int,
NAME char(11),
did int,
age int,
constraint pk_name primary key(id),
constraint uk_name unique(did)
)

主键外键，唯一键自动生成索引。show index from table_name;

主键和unique
1都可以保证唯一
2主键不能为空，unique 可以为空
3一个表只有一个主键，unique可以多个。
4都可以组合

修改表时候添加约束：

1添加非空约束

alter table tb_name modify column stuname varchar(32) not null

2添加默认约束

alter table tb_name modify column stuage default 18

3添加主键约束

列级约束：alter table tb_name modify column id primary key

表级约束：alter table tb_name add CONSTRAINT pk primary key(id)

4唯一约束

alter table tb_name modify column studid int unique;

删除唯一约束

alter table tb_name drop index (unique_name)

事务tcl

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。（多次读取的数据不一致）

幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

脏读和幻读类似，脏读针对更新，幻读一般针对插入和删除。

MySQL事务隔离级别---默认：默认的事务隔离级别为repeatable-read，最低的隔离级别为读未提交

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

查看事务的隔离级别：select @@tx_isolation;

set session transaction isolation level read uncommitted; // 设置隔离级别

start transaction;//开启事务

脏读，读第一个事务修改了，第二个事务就可以看到了修改后的信息，但是第一个事务回滚了，第二个事务的数据又开始不一致了。

不可重复读，第一个事务修改了，第二个事务看不到，第一个事务提交了，第二个事务看到了，两次结果不一样。

幻读，针对插入，比如查看数据是两个，但是另一个事物插入一条数据，当前事务，修改数据了之后竟然是三条数据收到影响。

只有串行化才能解决数据并发问题.

set autocommit=0,
当前session禁用自动提交事物，自此句执行以后，每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。

savepoint 保存点，搭配rollback

BEGIN TRANSACTION;
INSERT INTO dept(dbname,db_nu) VALUES('5','aa');;
SAVEPOINT a;
INSERT INTO dept(dbname,db_nu) VALUES('ac','csa');
ROLLBACK TO a;

视图：

CREATE VIEW myview AS SELECT dbname FROM dept; //创建视图

CREATE OR REPLACE VIEW myview AS SELECT dbname FROM dept;//修改视图--》alter view myview as ...

删除视图

drop view 视图名，视图名

查看视图 desc 视图名

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

高级

mysql --关系型数据库

1索引优化

创建索引

CREATE INDEX idx_dept_name ON dept(dbname)//单值索引

CREATE INDEX idx_dept_name_nu ON dept(dbname，db_nu)//多值索引

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高校获取数据的数据结构。
可以得到索引的本质：索引是数据结构。

myi 文件存放表索引

数据本身之外,数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，

这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。


优势：

1类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本

2通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

劣势
1虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。
因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息

2索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

创建index

1CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

CREATE INDEX xx ON utest (NAME);

2ALTER TABLE mytable ADD [UNIQUE] INDEX [indexName] (columnname(length));
ALTER TABLE utest2 ADD INDEX xs(xc);

删除index

drop index index_name on table_name ;
查看index
SHOW INDEX FROM dept

哪些情况需要创建索引：
1.主键自动建立唯一索引
2.频繁作为查询的条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引

哪些情况不要创建索引：
1.表记录太少
2.经常增删改的表
3.数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。
注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

性能分析: MySQL常见瓶颈-----》
1 CPU:CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候
2 IO:磁盘I/O瓶颈发生在装入数据远大于内存容量时
3 服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态

explain
是什么（查看执行计划）
(如何用)explain+sql语句

EXPLAIN SELECT * FROM dept;

查看type 类型---如果为all,则进行索引优化
如果key = null，则索引没建立，建立没用，就是索引失效
possible_key  理论上用到的key


extra 包含不适合在其他列中显示但十分重要的额外信息

单表，在查询的条件字段上或者字段上加上index
两表， 左连接，在右表的字段上加index,右连接--同样左表加index，没建立index,type是all
三表，会使用join 缓存,没建立index,type是all,也是在右边的两张表上建立查询字段的index

结论:
1 尽量减少join语句的中的循环总次数，用小的结果集驱动大结果集，比如书的种类驱动书。
2 尽量优先优化内存循环
3 保证join 索引

索引失效

索引失效--》建立索引，没用上

1.最佳左前缀法则--》如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
2.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
3.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
4.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
5.is null,is not null 也无法使用索引
6.少用or,用它连接时会索引失效
7.字符串不加单引号索引失效   select * from user where name=100,index失效,但是能查出，mysql底层
做了类型装换，将数值型转为varchar类型。

8.少用or,用它连接时会索引失效
9.存储引擎不能使用索引中范围条件右边的列
10.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作；-->百分号最好卸载最右边

问题：解决like'%字符串%'索引不被使用的方法？？---使用覆盖索引解决！

题目：为字段分别建立1,2,3,4索引
1 查询顺序颠倒，索引精度不变
2 3>‘3’ 范围之后全失效，用了两个
3 4>4 and 3=3 mysql底层会优化，然后用到了四个，4后面用不到了
4 order by 3 ,3的作用排序 和四关系没了
5 1=1 2=2 order by 4 没有4 ，会出现 mysql内部排序 
6 1=1 order by 3，2 排序的时候最好出现按照索引建立的顺序 mysql内部排序 ，有个特例，如下
7 1=1 ，2=2order by 3，2 ，排序字段是个常量
8 1=1 2=2 group by 4,3   Using temporary 临时表

建议：

在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引