mysql

1. varchar是变长而char的长度是固定的。如果你的内容是固定大小的，你会得到更好的性能。

2. DELETE命令从一个表中删除某一行，或多行，TRUNCATE命令永久地从表中删除每一行。

3. 触发器是指一段代码，当触发某个事件时，自动执行这些代码。在MySQL数据库中有如下六种触发器：Before Insert、After Insert、Before Update、After Update、Before Delete、After Delete。

4. mysql获取当前日期： SELECT CURRENT_DATE();

5. 查询第n高的工资：
   SELECT DISTINCT(salary) from employee ORDER BY salary DESC LIMIT n-1,1

6. 以下的意义

• int(0) INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
• char(16)、varchar(16) VARCHAR用于存储可变长字符串，它比定长类型更节省空间，varchar(10)和varchar(20)占用的空间一样。 CHAR是定长的，根据定义的字符串长度分配足够的空间。它俩内容超出设置的长度时，内容都会被截断。对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
  对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
  使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
  尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。
• datetime 日期和时间类型，尽量使用timestamp，空间效率高于datetime
• text 表示字符串类型，能存储大字符串，最多存储65535字节数据。 不能像varchar一样指定字符数，不能有默认值，varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引几乎不起作用

7. MySQL存储引擎:
   InnoDB存储引擎(建议) MyISAM存储引擎

• InnoDB支持事务，MyISAM不支持；
• InnoDB数据存储在共享表空间，MyISAM数据存储在文件中；
• InnoDB支持行级锁，MyISAM只支持表锁；
• InnoDB支持崩溃后的恢复，MyISAM不支持；
• InnoDB支持外键，MyISAM不支持；
• InnoDB不支持全文索引，MyISAM支持全文索引；

8. 索引

• 普通索引：最基本的索引，没有任何约束限制。
• 唯一索引：和普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。一个表可以有多个唯一索引。
• 主键索引：特殊的唯一索引，不允许有空值。一个表只能有一个主键索引
• 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性、和实现级联操作。

创建索引

• 首先创建一个表：create table t1 (id int primary key,username varchar(20),password varchar(20));
• 创建单个索引的语法：CREATE INDEX 索引名 on 表名（字段名） 。索引名一般是：表名_字段名
• 给id创建索引：CREATE INDEX t1_id on t1(id);
• 创建联合索引的语法：CREATE INDEX 索引名 on 表名（字段名1，字段名2）
  给username和password创建联合索引：CREATE index t1_username_password ON t1(username,password)
• 其中index还可以替换成unique，primary key，分别代表唯一索引和主键索引
• 删除索引：DROP INDEX t1_username_password ON t1

索引使用场景

• 对于非常小的表，大部分情况下全表扫描效率更高。
• 中到大型表，索引非常有效。
• 特大型的表，建立和使用索引的代价会随之增大，可以使用分区技术来解决

索引创建原则

• 最适合创建索引的列是出现在WHERE或ON子句中的列，或连接子句中的列而不是出现在SELECT关键字后的列。
• 索引列的基数越大，数据区分度越高，索引的效果越好。
• 对于字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间。
• 根据情况创建联合索引，联合索引可以提高查询效率。
• 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
• 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。

MySQL索引的注意事项

• 联合索引的最左前缀匹配原则

KEY(a,b,c)
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b = 2
WHERE a = 1
#以上SQL语句可以用到索引
WHERE b = 2 AND c = 3
WHERE a = 1 AND c = 3
#以上SQL语句用不到索引

• LIKE查询，%不能在前

WHERE name LIKE "%wang%"
#以上语句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。

• 如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引，例如：
  表中只有100条数据左右。对于SQL语句WHERE id > 1 AND id < 100，MySQL会优先考虑全表扫描。
• 如果关键词or前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到。
• 列类型是字符串，查询时一定要给值加引号，否则索引失效，例如：列name varchar(16) 存储了字符串"100" ....WHERE name = 100; 该SQL语句能搜到，但无法用到索引。
• 以下语句是不会应用索引：SELECT FROM users WHERE YEAR(adddate) < 2007; 只要列涉及到运算，MySQL就不会使用索引。

mysql索引原理
MySQL索引是用一种叫做聚簇索引的数据结构实现的

9. mysql关联查询

关联查询语句

• 交叉连接（CROSS JOIN）

SELECT * FROM A,B(,C)或者
SELECT * FROM A CROSS JOIN B (CROSS JOIN C)
#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用

• 内连接（INNER JOIN） 两个的交集

SELECT * FROM A,B WHERE A.id=B.id或者
SELECT * FROM A INNER JOIN B ON A.id=B.id
#多表中同时符合某种条件的数据记录的集合，INNER JOIN可以缩写为JOIN

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

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

• 联合查询（UNION与UNION ALL）
  SELECT * FROM A UNION SELECT * FROM B UNION ...
  就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并。
  如果使用UNION ALL，不会合并重复的记录行
  效率 UNION 低于 UNION ALL
  Union因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All
  两个要联合的 SQL语句 字段个数必须一样，而且字段类型要“相容”（致）；

• 全连接（FULL JOIN） MySQL不支持全连接
  可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用

SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION
SELECT * FROM A RIGHT JOIN B ON A.id=B.id

一个小例子
根据需求要搞清楚表的结果和多表之间的关系，根据想要的结果思考使用那种关联方式，通常把要查询的列先写出来，然后分析这些列都属于哪些表，才考虑使用关联查询。
为了记录足球比赛的结果，设计表如下：
team：参赛队伍表
match：赛程表
其中，match赛程表中的hostTeamID与guestTeamID都和team表中的teamID关联，查询2006-6-1到2006-7-1之间举行的所有比赛，并且用以下形式列出：拜仁 2:0 不莱梅 2006-6-21
首先列出需要查询的列：
表team teamID teamName
表match matchID hostTeamID guestTeamID matchTime matchResult
其次列出结果列：
主队 结果 客对 时间
初步

SELECT hostTeamID,matchResult,matchTime guestTeamID from match where matchTime between "2006-6-1" and "2006-7-1";

通过外键关联 完成

select t1.teamName,m.matchResult,t2.teamName,m.matchTime from match as m left join team as t1 on m.hostTeamID = t1.teamID, left join team t2 on m.guestTeamID=t2.guestTeamID where m.matchTime between "2006-6-1" and "2006-7-1";

10. 拷贝表
    insert into b(a, b, c) select d,e,f from a;
11. 随机取出10条数据

SELECT * FROM users WHERE id >= ((SELECT MAX(id) FROM users)-(SELECT MIN(id) FROM users)) * RAND() + (SELECT MIN(id) FROM users) LIMIT 10;
#此方法效率比直接用SELECT * FROM users order by rand() LIMIT 10高很多

图片.png