MySQL基础及需要注意的问题

一、MySQL语法

1、如何查看一个已经建好表的表结构或表创建语句？

查看表结构：

desc employee；

查看创建语句：

show create table employee；

2、char和varchar有什么区别，varchar(255)和varchar(256)有什么区别 ？

char和varchar的区别

1、char类型是存储定长数据，长度是固定的，例如存储身份证号。而varchar类型是存储变长数据，长度是可变的，例如用户的名字，因为经常会有改动。
2、char类型的效率比varchar的效率稍高。

varchar(255)和varchar(256)的区别

varchar类型的值，会有一个长度标识位来存值长度。
当定义varchar类型的长度小于等于255时，长度标识位仅需要一个字节，即我们定义一个varchar(255)的字段时，其实它真实使用的空间是256（255+1）字节；
当大于255时，长度标识位则需要两个字节，当我们定义一个varchar(256)字段时，它真实使用的空间是258（256+2）字节。

3、text和blob有什么区别？

text
text分为4种类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，分别对应不同的长度。
text是非二进制字符串，并且需要指定字符集，并按照该字符集进行校验和排序。
text只能存储纯文本，可以看作是VARCHAR在长度不足时的扩展。
blob
blob也分为4种类型：TINYBLOB,BLOB,mediumblob和LongBlob，分别对应不同的长度.
blob存储的是二进制数据，因此无需字符集校验。
blob除了存储文本信息外，由于二进制存储格式，所以还可以保存图片等信息。
blob可以看作是VARBINARY在长度不足时的扩展。
总结：
text是非二进制字符串，并且需要指定字符集，并按照该字符集进行校验和排序。blob存储的是二进制数据，因此无需字符集校验，但blob除了存储文本信息外，由于二进制存储格式，所以还可以保存图片等信息。

4、使用LIKE关键字时’%‘和’_'有什么作用？

like用于在where字句搜索指定的模式，用来判断字符串中是否包含指定的字符串。
'_'是匹配符，表示匹配单个字符。
‘%’ 是通配符，表示可以匹配任意长度的字符串，指代位置匹配。
like ‘Li%’ 表示以Li开头的字符串，like ‘%chen%’ 表示含有chen的字符串。
like ‘_m%’ 表示匹配第二个字符为m的字符串。

5、delete、 drop、truncate有什么区别和联系？

truncate和delete只删除数据，drop则删除整个表（结构和数据）。
delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中
保存以便进行进行回滚操作。
truncate则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，
删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
整体对比：
（1）在速度上，一般来说，drop> truncate > delete。
（2）在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
（3）如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；
（4）如果想删除表，当然用drop；
（5）如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；
（6）如果和事务有关，或者想触发trigger，还是用delete；
（7）如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

6、为什么不建议使用’select * '？

1、如果不查询表中所有的列，尽量避免使用 SELECT *，因为它会进行全表扫描，虽然性能没啥区别。
2、但是不能有效利用索引，增大了数据库服务器的负担，以及它带来了与应用程序客户端之间的网络IO开销。
3、select * 会查询出不需要的、额外的数据，那么这些额外的数据在网络上进行传输，必定会造成性能延迟。

7、如何查询一个表中第100行到第200行之间的数据？

1、select * from 表名 limit n, m
n：表示起始的偏移量，从0开始（所以第100条数据，n=99）
m：表示返回记录的最大个数（第200条数据，m=101）
2、查询n到m行记录的通用公式：

select top m * from table where id is not in (select top n * from table)

8、如何查看某个字段在表中唯一值的数量？

select t.col1,count(t.col1) as col1_count from table1 t group by t.col1;

9、group by的作用是什么？

针对表里面的某个字段进行分类
根据一个或多个列对结果集进行分组，再计算。

10、如何对两个字段进行排序查询？

（1）order by column1,column2;
表示：column1和column2默认都是升序
（2）order by column1,column2 desc;
表示：column1 升序，column2是降序；
（3）order by column1 desc,column2;
表示：column1降序，column2升序
（4）order bycolumn1 desc,column2 desc;
表示：column1和column2都是降序。

11、MySQL中有哪些常用的内置函数？

1、字符串函数

字符串合并concat，concat_ws
concat(s1, s2…,sn)：将多个字符串合并起来。

select concat("sunny " , " is" , " a", " teacher") as `desc`;

concat不仅可以接受字符串还能接受其他类型。
concat_ws(sep, s1, …sn): 用分隔符将字符串连接。

select concat_ws(" ", "sunny" , "is" , "a", "teacher") as `desc`;

字符串比较strcmp

select strcmp(str1, str2)

如果str1大于str2 返回结果1， 如果str1小于str2 返回‐1， str1等于str2返回0

字符串长度 length、char_length

length(str) 获取字符串长度
如果想正确获取中文的个数使用char_length

大小写转换 lower、upper

select "sunny", upper("sunny")
select "SUNNY", lower("sunny")

字符串查找
1、 find_in_set(str1, str2)
返回str2余str1相匹配的位置， str2中包含若干个用逗号隔开的字符串。
2、field(str, str1, str2, ... strn)
返回和第一个字符串str匹配的字符串的位置。

字符串截取子串
1、left(str, num)
获取str字符串的前num个字母
2、right(str, num)
获取str的后面num个字母
3、substring(str, num, len)
获取str的从num开始的len个字母

替换字符串
1、 insert(str, pos, len, newstr)
str：带插入字符串
pos：开始位置
len：长度
newstr：新字符串
如果参数post的值超过字符串长度，则返回原始字符串，如果len的长度大于str的剩余的长度，则从post 开始全部替换，任何参数为null都返回null。
2、 replace(str, substr, newstr)
用newstr替换str中的substr

2、数值函数

随机数：
rand()返回0‐1之间的随机数。

获取整数：
1、ceil(x) 返回大于或者等于x的最小整数。
2、floor(x) 返回小于等于x的最大整数。

截取数值：
truncate(x, y) 返回x保留到小数点后y位的值

select truncate(101.26853, 2)

四舍五入：
round(x) x经过四舍五入后的值
round(x, y) x保留到小数点后y位的值，在具体截取的时候进行四舍五入

3、时间和日期函数

获取当前日期： curdate()
获取当前时间： curtime()
获取当前的日期和时间： now()
返回日期为某一年中的第几周： week(date)
返回日期的年份： year(date)
返回time的小时值： hour(time)
返回time的分钟数： minute(time)
返回time的月份名称： monthname(date)
current_timestamp/date/time()

时间转换函数：

date(‘yyyy-mm-dd HH:ii:ss’)
date_format(‘yyyy-mm-dd HH:ii:ss’,’%D%y%a%d%m%b%j)
时间戳转化为日期：from_unixtime(pay_time,‘yyyy-mm-dd HH:ii:ss’)
日期间隔函数：date_diff(dt,to_dt)

条件函数：

case when /if

二、多表查询

1、什么是多表查询，多表查询的过程是什么?

什么是多表查询：

一个查询结果需要从两个或两个以上表中提取字段数据，就是要使用多表关联查询。
多表查询如果不加任何条件，得到的结果称为笛卡尔积。

多表查询执行过程：
from–on–join–where–group by–having-- select–distinct–order by

2、什么是笛卡尔积？

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积，又称直积，表示为X×Y，第一个对象是X
的成员而第二个对象是Y的所有可能有序对的其中一个成员 。

3、什么是内连接、外连接、交叉连接、自身连接？

1、内连接(inner join)： 只连接匹配的行。
2、外连接(outer join)：
（1）left join： 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
（2）right join： 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
（3）full join： 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
3、交叉连接(cross join)： 笛卡尔乘积（所有可能的行对）
交叉连接不带ON子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到 结果集合中的
数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查 询条件的数据行数。
4、自连接：
连接的表是同一张表，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到
一些特殊的数据。

4、left join和rignht join有什么区别，left join和left outer join有什么区别？

left join： 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行。
right join： 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行。
left outer join： 一个表满足条件的行，和另一个表的所有行。

5、什么是嵌套查询，集合查询？

嵌套查询：
（1）指在一个外层查询中包含有另一个内层查询。其中外层查询称为主查询，内层查询称为子查询。
（2）SQL允许多层嵌套，由内而外地进行分析，子查询的结果作为主查询的查询条件
（3）子查询中一般不使用order by子句，只能对最终查询结果进行排序
集合查询：
对两个查询或多个查询结果进行"并(union)操作",“交(Intersect)操作"和"差(except)操作”。

三、索引

1、什么是索引，为什么要有索引？

什么是索引：
索引是MySQL中也叫做‘键’，是存储引擎用于快速找到记录的一种数据结构。
索引的作用：
索引相当于字典的音序表，可以根据音序表中的页码快速找到所需的内容。

2、索引有哪些类型？

根据数据类型可以分为：B+数索引（innodb）、哈希索引（memory）.
mysql的索引分别为：普通索引，唯一索引，全文索引。

3、索引如何加快查询速度？

索引的通俗理解：
数据库的索引类似书籍的索引，在数据库中索引允许数据库恒旭迅速找到表中数据。
有无索引的区别：
无索引，直接去读表数据存放的磁盘块，读到数据缓冲区中再查找需要的数据。
有索引，先读入索引表，通过索引表直接找到所需数据的物理地址，并把数据读入数据缓冲区中
索引和表都是以数据块的形式存储，但是二者存储结构不同。
索引是b-tree结构的一组有序的数据，里面存放的是 key值和行地址（oracle 是rowid）。
比如一张表中id字段，有1-100 行，查询id=5这行，那么只需要遍历B-tree树，找到5这个节点，
就可以确定一行。但是如果没有索引，就需要遍历整个表的数据块，来确定id=5的这一行。
遍历有序的b-tree数据块，确定此行所在位置之后，再一次读取数据，比扫描表所有数据块要快很多。

4、一个表的索引是不是越多越好，为什么？

（1）首先数据量小的表不需要建立索引,因为小的表即使建立索引也不会有大的用处,还会增加额外的索引开销。
（2）不经常引用的列不要建立索引,因为不常用,即使建立了索引也没有多大意义。
（3）经常频繁更新的列不要建立索引,因为肯定会影响插入或更新的效率。
（4）索引并不是一劳永逸的,用的时间长了需要进行整理或者重建。

5、什么场景下应当少建或者不建索引？

数据量小、不常引用的列、经常频繁使用的列。
（因为建立索引的表，插入一条数据就要对该记录【按索引排序】。因此，导入大量数据的时候速度会很慢，解决办法：在没有任何索引的情况下插入数据，然后再建立索引。而更新就无法避免了。经常更新的列就不要建立索引。）

6、什么是主键索引、唯一索引、覆盖索引？

主键索引： 特殊的唯一索引，不允许有空值
主键索引的三个条件：1.主键键值必须是唯一2.不能含有null值3.保证主键是自增的
自增列作主键可以使写入的顺序也是自增的，这个可以提高存储效率
唯一索引： 索引列中的值必须是唯一的，可以为空值。
覆盖索引： 指同过通过索引文件就可以返回查询所需要的数据，不必通过回表操作得到数据，即查询列要被所使用的索引覆盖。建立索引的字段正好是覆盖查询语句中所涉及的字段，也即，索引包含了查询正在查找的所有数据.减少大量的I/O操作，提升查询效率

7、什么是联合索引，联合索引中多个字段的先后顺序如何确定？

联合索引：
两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。
先后顺序：
是根据字段的数据量来确定，例如A字段有1，2
B字段有1，2，3…100,一般建立复合索引是将A字段放前面。

8、回表查询是如何产生的？

待解

9、什么是最左匹配

当b+树的数据项是复合的数据结构（name,age,sex），b+数是按照从左到右的顺序来建立搜索树的。
假如name相同，再一次比较age,sex。如果name缺时，就不能进行搜索了。

四、事务

1、什么是事务？

一般是指要做的或所做的事情。
在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

2、事务有哪些特性？

原子性： 强调事务的不可分割
一致性： 事务的执行的前后数据的完整性保持一致
隔离性： 一个事务执行的过程中，不应受到其他事务的打扰
持久性： 事务一旦结束，数据就持久到数据库。

3、什么是事务的隔离级别，有哪些隔离级别？

隔离级别分为：
读未提交： 一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证。
读提交： 一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生
可以重复读： 就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生。
序列化（串行）： 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

4、MySQL默认的隔离级别是什么？

Repeatable Read(可以重复读)

5、什么是幻读，不可重复读、脏读？

脏读： 读取了未提交的新事物，然后被回滚了，MySQL不支持脏读
不可重复读： 读取了提交的新事物，指更新操作
幻读： 也是读取了提交的新事物，指增删操作，幻读也是虚读

五、存储引擎

1、什么是存储引擎?

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。

2、MySQL有哪些主流的存储引擎，不同的存储引擎主要用在什么场景，默认的存储引擎是什么?

常见的存储引擎：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)等。
最常用的MyISAM与InnoDB两个引擎。
MyISAM： 拥有较高的插入，查询速度，但不支持事务，使用表级锁，并发性差，灾难恢复性不佳。
InnoDB ： 5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，灾难性好，支持事务，使用行级锁定，支持外键关联、热备份。
Merge ： 将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用。
Memory ： 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失，不支持事务与外键。
BDB： 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性
Mysql的默认数据库:InnoDB。

3、MyISAM和InnoDB有什么区别，数据分别是如何存储的？

两者的区别：
MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持事务。
MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。
两者的数据存储：
1、Innodb辅助索引的叶子节点存储的不是地址，而是主键值，这样的策略减少了当出现行移动或者数据页分裂时辅助索引的维护工作，虽然使用主键值当作指针会让辅助索引占用更多空间，但好处是，Innodb在移动行时无需更新辅助索引中的主键值，而MyISAM需要调整其叶子节点中的地址，因为MyISAM引擎下，B+树叶子节点中包含的是数据记录的地址。
2、innodb引擎下，数据记录是保存在B+树的叶子节点上，当插入新的数据时，如果主键的值是有序的，它会把每一条记录都存储在上一条记录的后面，但是如果主键使用的是无序的数值，例如UUID，这样在插入数据时Innodb无法简单地把新的数据插入到最后，而是需要为这条数据寻找合适的位置，这就额外增加了工作，这就是innodb引擎写入性能要略差于MyISAM的原因之一。

4、InnoDB如何处理主键索引？

主键索引是InnoDB 引擎中是最重要的索引结构，主键索引的数据结构是一颗“B+树”，树的子节点存储索引节点信息及关联关系，树的叶子节点存储主键索引信息+数据，一颗树的叶子节点按照主键索引有序排列开来，查找起来更便捷。

5、在InnoDB中使用自增id作为主键索引有什么好处？

使用自增列做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致，从而形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

六、性能

1、如何查看sq|语句执行性能？

Explain可以用来查看 SQL 语句的执行效果

explain select … from … [where ...]

2、导致慢查询的主要原因是什么？

（1）没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)
（2）I/O吞吐量小，形成了瓶颈效应。
（3）没有创建计算列导致查询不优化。
（4）内存不足
（5）网络速度慢
（6）查询出的数据量过大（可以采用多次查询或其他的方法降低数据量）
（7）锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
（9）返回了不必要的行和列
（10）查询语句不好，没有优化

3、什么是读写分离， 哪些场景下需要读写分离？

读写分离就是将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。
大多数互联网业务，往往读多写少，这时候，数据库的读会首先称为数据库的瓶颈，这时，如果我们希望能够线性的提升数据库的读性能，消除读写锁冲突从而提升数据库的写性能，那么就可以使用“分组架构”（读写分离架构）。
读写分离是用来解决数据库的读性能瓶颈的。

4、什么是分库分表，愿些场景下需要分库，哪些场景下需要分表，如何分库分表？

分库分表：
分库分表把存于一个库的数据分散到多个库中，把存于一个表的数据分散到多个表中。
当一个数据库被创建之后，随着时间的推移和业务量的增加，数据库中的表以及表中的数据量都会越来越多，就有可能会出现两种弊端：
（1）数据库的存储资源是有限的，其负载能力也是有限的，数据的大量积累肯定会导致其处理数据的能力下降；
（2）数据量越多，那么对数据的增删改查等操作的开销也会越来越大，所以，当出现如上两种情况，分库分表势在必行。
垂直切分：
如果是因为表的个数多而让数据多，可以按照功能划分，把联系密切的表切分出来放在同一个库中（分库）。
垂直拆分是指，将一个属性较多，一行数据较大的表，将不同的属性拆分到不同的表中，以降低单库（表）大小，达到提升性能的目的的方法。
垂直切分后，各个库（表）的特点是：
（1）每个库（表）的结构都不一样
（2）一般来说，每个库（表）的属性至少有一列交集，一般是主键
（3）所有库（表）的并集是全量数据
水平切分：
水平切分：如果是因为表中的数据量过于庞大，则可以采用水平切分，按照某种约定好的规则将数据切分到不同的数据库中。
以某个字段为依据（例如uid），按照一定规则（例如取模），将一个库（表）上的数据拆分到多个库（表）上，以降低单库（表）大小，达到提升性能的目的的方法。
水平切分后，各个库（表）的特点是：
（1）每个库（表）的结构都一样
（2）每个库（表）的数据都不一样，没有交集
（3）所有库（表）的并集是全量数据

5、MySQL的主从实例是如何同步数据的？

待解

七、数据库表设计

1、设计表时有哪些注意事项(字段非空，设置默认值等， 为何不建议使用NULL值? )

1、在创建表结构时，表名、字段需要见名知意，不采用拼音。字段和表要有comment
2、一定要有主键！整形自增(一般用无业务意义的id)，少用唯一键。
3、首先明确规定：函数，触发器，视图，外键；所有逻辑业务上实现,尽量不使用存储过程。
4、一定要整形ID作为主键(没有主键的表有严重性能问题)
5、字段类型越短越好，有text字段的，最好请分表。
6、尽量不要使用default null 。

索引不会包括NULL值。影响索引的统计信息，影响优化器的判断。
复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
所以我们在数据库设计时不要让字段的默认值为NULL。
字段统一加上not null default ‘合理默认值’

关键点：
1、主键和外键
2、索引
3、关系
一对一：列的增加
一对多，多对一：有两张表
一本书由多个章节 book-chapter(id,book_id)
多对多：有3张表，第三张表来表示两张表的关系。
user
book
user_fav user_id book_id

2、什么是数据库三范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范 式，各种范式呈递次规范，越高的范式数据库冗余越小。但是有些时候一昧的追求范式减少冗余，反而会降低数 据读写的效率，这个时候就要反范式，利用空间来换时间。
第一范式： 字段不可分：原子性 字段不可再分,否则就不是关系数据库；
即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要数据库是关系型数据库

第二范式： 有主键，非主键字段依赖主键：唯一性 一个表只说明一个事物；
第即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这 样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个 字段。（涉及到表的拆分）

第三范式： 非主键字段不能相互依赖：每列都与主键有直接关系，不存在传递依赖；
即满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就 是间接依赖于主键，这被称作传递依赖于主属性。 通俗解释就是一张表最多只存两层同类型信息。

注意事项

对于数据分析师，语法和多表查询是重点
对于程序员，其他的都需要掌握