MySQL（待更新）

Mysql八股文链接

 

MySQL八股文连环45问,你能坚持第几问？_IT邦德的博客-CSDN博客_mysql45问

DDL数据定义语言

create 创建 drop 删除 alter 修改

创建

create database 数据库名; //在数据库中创建一个database

create table 表格名字(//在database中创建一个表格

列名 数据类型(长度),

列名 数据类型(长度),

列名 数据类型(长度)

)[charactor set utf8];

create database dbname [default charactor set = 'utf8'];

修改

alter table 原表名 rename [to] 新表名;//修改表名

alter table 原表名 change 原列名 新列名 新类型 新长度;//修改原有的列

alter table 原表名 add 新列名 新类型 新长度;//新增一个列

alter table 原表名 drop 原列名;//删除一个列

删除

drop table 表名;

drop database 数据库名字;

数据库中的数据类型

数值型

整数 tinyint smallint mediumint int bigint

小数 float double decimal numeric

float(5,2)//长度为5 小数占2位

字符串

char varchar varible

binary二进制 varbinary可变二进制

blob二进制大文本 text 正常字符大文本

日期/时间

date time datetime timestamp

DML数据操作语言

insert delete update

insert into tablename(colname,colname,colname) values(value,value,value);

insert into tablename values(value...);//当值的个数类型和表一致时 可省略列名

insert into tablename valuse(value...),(value...);//可以同时插入多行数据

delete from table [where...];

update tablename set col=value....[where...];

show table status from dbnaem like 'tablename';

DQL数据查询语言

select

select schema_name,default_charactor_set_name from information_schema.schemata where schema_name = 'tablename';

//查询数据库的字符集

select * from student where chinese >= 95 and english >=95;

数据库底层执行where语句

解析SQL

从表格把全部数据读出来 放在数据库缓存

每个条件都要执行一次循环 从左到右顺序

所以尽量将条件苛刻的放在前面

常用关键字

distinct----去重复 可以理解为分组

group by 分组 一旦搭配了分组条件之后 最终显示结果只包含两部分内容：分组函数和分组条件

先条件筛选 再分组 where group by

先分组 再条件筛选 group by where

优先级 where > group by > having > order by

in 后面可以是固定值也可以是 查询结果

下面这三个都不能加固定值

any 满足（）内结果集某一个条件即可 可使用< > ! !=运算符

some 和any一致

all 满足查询子集中的全部才可以

union 并集 将两个sql语句查询结果放在一块

要求：列数相同 （类型可以不同） 拼接后显示前一个自己默认的列名

拼接完会做去重管理 效率较慢 union all不会做任何处理 效率较快

嵌套

可以将查询的结果作为条件再次查询

可以将查询的结果作为一个新的表格 再查询 此时需要给新表格起别名

列的约束

alter table tableName add constraint constraintName constraintType (colName)添加一个约束

主键约束 Primary Key

添加主键约束可以简写alter table tableName add primary key (colName)

//修改为主键自增

alter table tableName modify colName colType(len) auto_increment; //可以修改列名

alter table tableName change colName colName colType(len) auto_increment;

通过下面语句查看表结构

desc tableName;或者 show keys from tableName;

或者show create table tableName;

一个表格只有一个列被设置为主键

主键约束不能为null且唯一存在不重复

删除主键约束 alter table tableName drop primary key;

唯一约束 unique

不能重复 可以为空

alter table tableName add unique key (colName)

删除约束 alter table tableName drop index 约束名（默认是列名）;

非空约束 not null

alter table tableName modify colName colType(len) not null;

//设置一个默认值

alter table tableName modify colName colType(len) not null default '';

外键约束

alter table tableName add constraint fk_srctableName_tartableName foreign key(col) references 另一个表（列）

alter tableName drop foreign key 约束名字

上述语句已经把外键约束删掉 但会自动在当前表新增一个key 需要再删一次

alter tableName drop key 约束名字

联合查询

广义笛卡尔积

select * from A,B where 条件

外连接

select * from A left/right [outer] join B on 条件

两张表格AB 取决于谁得数据在左边显示

left和right控制以哪一个表格数据为基准

作为基准的表格数据必须全部显示出来

非基准的表格数据按照on条件与之拼接

能拼上就显示 否则为null

内连接

select * from A inner join B on 条件

特殊查询

行列互换

需要用到分组函数和if的搭配使用

分页查询

limit(a,b) a号索引显示b行

DCL数据控制语言

事务

四大特性 ACID

A：Atomicity --- 原子性 ：一个事务中的所有操作是一个整体 不可再分 事务中的所有操作要么都成功 要么都失败

B：Consistency --- 一致性：一个用户操作了数据，提交以后，另一个用户看到的数据与之前用户看到的效果是一致的

I：Isolation --- 隔离性：指的是多个用户并发访问数据库时，一个用户操作数据库，另一个用户不能有所干扰，多个用户之间的数据事务操作要相互隔离

D：Durability --- 持久性：指的是一个用户操作数据的事务一旦被提交，他对数据库底层的真实改变是不可逆的

数据库的隔离级别

serializable 最高 可以避免所有问题 性能很慢

repeatable read 可重复读 避免脏读和不可重复读

read committed 读已提交 避免脏读

read uncommitted 读取未提交 什么都避免不了 不安全

修改隔离级别

set session transaction isolation level xxx；

查看隔离级别

select @@tx_isolation;

数据的安全隐患

脏读：一个人读到了另一个人还没提交的数据

不可重复读：A先读取了一些数据，B将数据修改或者删除（非新增），A再去读的时候 会发现两次数据不一致

幻读：A先读取一些数据 B新增了一些数据 A再去读的时候 会发现与第一次不一致

索引

mysql里的数据结构；能够提高查找效率；提高排序效率；

索引规则：

全值匹配

最左前缀法则：最左边没有 后面索引直接失效。

不要在列上进行计算，会索引失效

范围右边索引失效

尽量使用覆盖索引 只访问索引的查询

使用不等于、is null、is not null的时候索引失效

like以通配符开头的会导致索引失效 即应遵循最左前缀匹配（当使用覆盖索引时 不会失效）like范围索引不会导致后面的索引失效

字符串不加单引号会导致索引失效

or链接索引失效 除非or条件中每个条件都加索引

explain

*id：sql语句的执行顺序。id相同 从上到下执行；id不同 id越大越先执行；子查询会先执行

select_type：查询的类型。simple：简单的查询；primary：包含子查询，最后加载的那个；subquery：select或where中包含子查询；derived：from中包含子查询：union：第二个select出现在union之后；union result：union执行结果的表。

*type：从好到坏：system-const-eq_ref-ref-range-index-all

possible_key：可能被使用的索引

*key：实际使用的索引

key_len：索引字节数 ，索引字段的最大可能长度，并非实际使用长度。

ref：使用索引的值（字段），有可能是一个常数const

*rows：找到所需要记录所需要读取的行数

*Extra：Using filesort：使用了外部索引排序，而不是表内的索引顺序进行读取，无法利用索引完成的排序称为文件排序，效率不好。Using temporary：使用临时表保存中间结果，对查询结果排序时使用，常见于order by 和group by，效果更不好。Using index：使用了覆盖索引，效率不错。

锁

show open tables 查看上锁的表

读锁 表级别：只能读自己锁上的表，不能读写其他表，会报错；别人只能读，改的话会被阻塞

写锁 表级别：可以读自己，写自己，不能读写其他表；别人读写会阻塞

行锁：会阻塞一行的写操作，默认隔离级别读已提交，避免可重复读；索引失效导致行锁变表锁

间隙锁的危害：当用范围条件时，会把范围内所有数据索引都加锁，包括不存在的数据。mysql有参数控制是否启用间隙锁

for update --锁定 一行数据

Mysql的事务是怎么实现的

binlog和redo log的区别

binlog和redo log的区别_wh柒八九的博客-CSDN博客_binlog和redolog区别