mysql 临时字段 where_你必须要知道的Mysql 基础知识点

什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗地说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引有哪些优缺点？

索引的优点

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；

空间方面：索引需要占物理空间。

索引使用场景

where后的字段加索引，查询效率会有明显的提升

order by后的字段加索引，查询效率会有明显的提升，建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。

对join语句匹配关系(on)涉及的字段建立索引能够提高效率

注意：如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据(否则只要有一个字段没有建立索引就会做全表扫描)，这叫索引覆盖。因此我们需要尽可能地在select后只写必要的查询字段，以增加索引覆盖的几率。但值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

索引类型

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

创建索引的原则

1) 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2)较频繁作为查询条件的字段才去创建索引

3)更新频繁字段不适合创建索引

4)若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5)尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6)定义有外键的数据列一定要建立索引。

7)对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8)对于定义为text、image和bit的数据类型的列不要建立索引。

题外：百万级别或以上的数据如何删除

所以我们想要删除百万数据的时候可以先删除索引(此时大概耗时三分多钟)

然后删除其中无用数据(此过程需要不到两分钟)

删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。

与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

六种关联查询

交叉连接(CROSS JOIN)

内连接(INNER JOIN)

等值连接：ON A.id=B.id

不等值连接：ON A.id > B.id

自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外连接(LEFT JOIN、RIGHT JOIN)

左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN

右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

联合查询(UNION、UNION ALL)

就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并

如果使用UNION ALL，不会合并重复的记录行

效率 UNION 高于 UNION ALL

全连接(FULL JOIN)

MySQL不支持全连接，Oracle支持

可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用

子查询

注：一般在子查询中，程序先运行在嵌套在最内层的语句，再运行外层。因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。否则多层的嵌套使语句可读性很低。

一、where型子查询(指把内部查询的结果作为外层查询的比较条件)

如果是 where 列 =(内层 sql) 则内层 sql 返回的必须是单行单列，单个值。

select name,age,sex from student where age = 20; select name,age,sex from student where age = (select max(age) from student);

二、form型子查询(把内层的查询结果当成临时表，供外层sql再次查询)

select name,age from student select * from (select name,age from student) as 自定义临时表名

三、in子查询(内层查询语句仅返回一个数据列，这个数据列的值将供外层查询语句进行比较)

select name,age,sex from student id in (select id from student where sex='男')

四、exists子查询(遍历外层的查询结果，拿到内层，看内层是否成立，内层查询至少返回一条数据就为true，外层的该条遍历数据就会存入结果集，如果内层返回false，外层的该条遍历数据被丢弃)

select name,age,sex from student where exists (select id from student where age=22) select name,age,sex from student where sex='男' and exists (select id from student where age=22)

五、any子查询(只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件)

select name,age,sex from student id > any (select id from student where age=22)

六、all子查询(内层子查询返回的结果需同时满足所有内层查询条件)

select name,age,sex from student id > all(select id from student where age=22)

exists和in区别

1、exists：

对外表用loop遍历查询，每次遍历的时候都会去执行子查询，如果子查询未查询到至少一行数据，则返回false，遍历外表的这条数据就会被丢弃，如果遍历的时候执行子查询查询到至少一行数据，不在乎是否是什么数据，只要有数据查询出来就返回true，遍历外表的这条数据就会被放到结果集中，直到遍历完外表。

not exists与exists相反，也就是当exists条件有结果集返回时，loop到的记录将被丢弃，否则将loop到的记录加入结果集

-- 返回年龄大于20且性别为男的数据select * from student where sex='男' and exists(select id from student where age>20);

总结：如果外表结果集有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件 。

2、in：

先执行子查询，将子查询结果放到临时表中，然后通过临时表的数据去遍历外表，通过关联关系得到外表需要的数据，可以理解为多个or条件拼接

select * from student where id in(1,2,3) ;-- 等同于 select * from student where id = 1 or id = 2 or id = 3 ; -- not exists与exists相反 select * from student where id not in(1,2,3) ;-- 等同于 select * from student where id != 1 or id != 2 or id != 3 ;

总的来说，in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后在将子查询条件的结果集分解成m个，再进行m次主查询

注意：in查询的子条件返回结果必须只有一个字段，但exists没有这个限制

select * from student where id in(select id from student where name='张三') ;-- 如果是下面这样则会报错 select * from student where id in(select id,name from student where name='张三') ; select * from student where id exists(select id from student where name='张三') ;-- 也可以写成下面这样，不影响结果 select * from student where id exists(select id,name from student where name='张三');

3、in与exists选择：

其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，

如果是exists，那么以外层表为驱动表，先被访问；如果是IN，那么先执行子查询，

所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。

总结：从查询上来说，数据量小的返回查询结果会更快，如果外表数据量大，内表数据量小，那么内表查询结果会更快，选择使用IN；

相反，如果外表数据量小，内表数据量大，那么外表查询结果会更快，选择以外表驱动为主的exists

varchar与char的区别

char的特点

char表示定长字符串，长度是固定的；

如果插入数据的长度小于char的固定长度时，则用空格填充；

因为长度固定，所以读写速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

varchar表示可变长字符串，长度是可变的；varchar(50)中的50表示最多存放50个字符，但是在varchar(50)和varchar(100)存储hello所占用的空间是一样的，但是varchar(100)在排序时会消耗更多内存；

插入的数据是多长，就按照多长来存储；

varchar在读写方面与char相反，它读写慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；

对于varchar来说，最多能存放的字符个数为65532；

总结：性能方面char更优，节省磁盘空间varchar更优，视项目具体需求设计。

mysql中int(10)和char(10)以及varchar(10)的区别

int(10)中10表示展示数据的长度，不是指存储数据的大小，存储范围不变，不影响内部存储，只是影响当int类型字段带有zerofill定义的时候，如果值不等于x长度，会在前面自动补0，易于展示，如展示为0001；

chart(10)中10表示10位固定字符串，不足补空格 最多10个字符，空格表示占位不算一个字符，占用更多的存储空间；

varchar(10) 中10表示10位可变字符串，最多10个字符，空格也按一个字符存储；

FLOAT和DOUBLE的区别

FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。

DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

drop、delete与truncate的区别