mysql

大于、小于、等于、不等于、大于等于、小于等于

SELECT * FROM tb_name WHERE user_id >10;

逻辑运算符是用来拼接其他条件的。用and或者or来连接两个条件，如果用or来连接的时候必须使用小括号

SELECT * FROM tb_name WHERE user_id > 10 AND sex = '男'

通配符

sql

IN (value1,value2,value3,....)

SELECT   *   FROM   tb_name  WHERE   user_id  IN (1,3,5,7,9,11);查询user_id是1，3，5，7，9，11的所有数据

field  BETWEEN  value1  AND   value2

SELECT * FROM user WHERE user_id  BETWEEN  2 AND 9;查询user表中user_id大于等于2小于等于9的所有值

count(field)获取符合条件出现的非null值的次数

SUM(field)获取所有符合条件的数据的总和

AVG(field)或者平均值

ORDER BY field DESC;降序查询

ORDER BY field ASC;升序查询

LIMIT 后边可以跟两个参数，如果只写一个表示从零开始查询指定长度，
如果两个参数就是从第一个参数开始查询查询长度是第二个参数的值，俩个参数必须是整形。

左关联

中关联

右关联

内管理的关联条件是用where来说关联的，多张表之间用AND来拼接where条件

SELECT   *   FROM   tb_name1,tb_name2,....   WHERE   tb_name1.t2_id = tb_name2.t2_id   AND  ...

主表关联副表，如果副表数据不够用NULL来补全，但是中关联的时候，如果不够了，左边的数据或者右边的数据不会显示。直接去掉

union会去重，union all不会

BEGIN开启事务

ROLLBACK;事务回滚

COMMIT;事务提交

表的引擎为InnoDB

脏读/不可重复读/幻读都可能

脏读不可能，不可重复读/幻读都可能

不可重复读/脏读不可能，幻读都可能

脏读/不可重复读/幻读都不可能

CREATE TABLE employees (
id INT NOT NULL,
NAME VARCHAR ( 30 ),
hired DATE NOT NULL DEFAULT '2018-12-01',
job VARCHAR ( 30 ) NOT NULL,
dept_id INT NOT NULL
)
PARTITION BY RANGE ( dept_id )(
PARTITION p0 VALUES LESS THAN ( 6 ),
PARTITION p1 VALUES LESS THAN ( 11 ),
PARTITION p2 VALUES LESS THAN ( 16 ),
PARTITION p3 VALUES LESS THAN ( 21 ) );

CREATE TABLE employees (
         id INT NOT NULL,
         NAME VARCHAR ( 30 ),
         hired DATE NOT NULL DEFAULT '2015-12-10',
         job_code INT,
         store_id INT )
PARTITION BY LIST ( store_id )(
PARTITION pQY VALUES IN ( 3, 5, 6, 17 ),
PARTITION pJN VALUES IN ( 1, 10, 11, 19 ),
PARTITION pCH VALUES IN ( 4, 12, 14, 18 ),
PARTITION pJJ VALUES IN ( 2, 9, 13, 16 ),
PARTITION pGX VALUES IN ( 7, 8, 15, 20 ));

常规hash分区使用的是取模算法，对应一个表达式expr是可以计算出它被保存到哪个分区中，N = MOD(expr, num)

线性hash分区使用的是一个线性的2的幂运算法则

partition by key(expr) partitions num;
-- 不指定默认首选主键作为分区键，在没有主键的情况下会选择非空唯一键作为分区键
partition by key() partitions num;
-- linear key
partition by linear key(expr)

create table ts (
id int,
purchased date
)
partition by range(year(purchased))
subpartition by hash(to_days(purchased)) subpartitions 2
(
partition p0 values less than (1990),
partition p0 values less than (2000),
partition p0 values less than maxvalue
);

NOT NULL

UNSIGNED

PRIMARY KEY

AUTO_INCREMENT

DEFAULT

COMMENT

TINYINT

SAMLLINT

INT

FLOAT

CHAR

VARCHAR

TEXT

添加

删除

添加

删除

添加

删除

添加

删除

ALTER TABLE tb_name ADD address VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';

ALERT TABLE tb_name ADD 添加字段 字段类型  非空约束  默认  注释

ALTER TABLE tb_name MODIFY address VARCHAR (50) NOT NULL DEFAULT '' COMMENT '地址';

ALERT TABLE tb_name MODIFY 字段名称 新的字段类型  非负  非空  默认 注释

ALTER TABLE tb_name CHANGE address addr VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';

ALTER TABLE tb_name CHANGE 旧的字段名  新的字段名  新的类型  约束  默认  注释

DESC tb_name;

ALTER TABLE tb_name DROP addr;

ALTER TABLE 表名 DROP 删除的字段名

ALTER TABLE tb_name RENAME TO new_tb_name;

ALTER TABLE 旧表名 RENAME TO 新表名

ALTER TABLE tb_name ENGINE = InnoDB;

ALTER TABLE 表名 ENGINE = 新的引擎名称

id相同,执行顺序由上至下;
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行;
id相同不同,都存在

SIMPLE:简单的select查询，查询中不包括子查询或者UNION

PRIMARY：查询中若包括任何复杂的子部分，最外层查询则被标记为PRIMARY

SUBQUERY:在select或where列表中包含了子查询

DERIVED: 在FROM列表中,包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,
把结果放在临时表里

UNION: 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,
外侧SELECT将被标记为 DERIVE

UNION RESULT: 从UNION表获取结果的SELECT

ALL：全部扫描，效率低

index：Full Index Scan，index与ALL区别为index类型只遍历索引树
explain select * from film; file中有name字段为索引

range：只检索给定范围的行，使用一个索引来选择行
explain select * from employee where rec_id < 3其中rec_id为主键

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）
explain select * from film where name = "film1" ref name是film中的普通索引

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
eq_ref 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的
explain select * from film_actor left join film on film_actor.film_id= film.id eq_ref 根据主键或唯一索引查询film

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
select 1 from actor where id =1 const
explain extended select * from (select * from film where id =1) tmp 临时表中只有一条记录，system

NULL： MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成explain select min(id) from film id是主键

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，
这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by;distinct

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句

不同的数据类型的存储和检索方式不同，对应的性能也不同，所以说要合理的选用字段的数据类型。比如人的年龄用无符号的unsigned tinyint即可，没必要用integer

数据库最终要写到磁盘上，所以字段的长度也会影响着磁盘的I/O操作，如果字段的长度很大，那么读取数据也需要更多的I/O, 所以合理的字段长度也能提升数据库的性能。比如用户的手机号11位长度，没必要用255个长度

不同存储引擎对char和varchar的使用原则不同，myisam:建议使用国定长度的数据列代替可变长度。
innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高

金额类型的字段尽量使用long用分表示，尽量不要使用bigdecimal，严谨使用float和double因为计算时会丢失经度
如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal
如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围
不建议使用ENUM、SET类型，使用TINYINT来代替

根据实际需要选择能够满足应用的最小存储日期类型。

如果应用只需要记录年份，那么仅用一个字节的year类型。
如果记录年月日用date类型, date占用4个字节，存储范围10000-01-01到9999-12-31
如果记录时间时分秒使用它time类型
如果记录年月日并且记录的年份比较久远选择datetime，而不要使用timestamp,因为timestamp表示的日期范围要比datetime短很多
如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp, 因为日期类型值只有它能够和实际时区相对应
datetime默认存储年月日时分秒不存储毫秒fraction，如果需要存储毫秒需要定义它的宽度datetime(6)
timestamp与datetime

两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

都可以使用自动更新CURRENT_TIMESTAMP

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

timestamp占用4个字节：timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’
datetime占用8个字节 ：datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。如果需要使用到时区就必须使用timestamp，如果不使用时区就使用datetime因为datetime存储的时间范围更大

注意：

禁止使用字符串存储日期，一般来说日期类型比字符串类型占用的空间小，日期时间类型在进行查找过滤是可以利用日期进行对比，这比字符串对比高效多了，日期时间类型有丰富的处理函数，可以方便的对日期类型进行日期的计算
也尽量不要使用int来存储时间戳

MySQL字段属性应该尽量设置为NOT NULL，除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL 。

在MySql中NULL其实是占用空间的，“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理”， 而空值”“是不占用空间的。

含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替null。

联表查询的时候，例如SELECT user.username, info.introduction FROM tbl_user user LEFT JOIN tbl_userinfo info ON user.id = info.user_id; 如果tbl_userinfo.introduction设置的可以为null, 假如这条sql查询出了对应的记录，但是username有值，introduction没有值，那么就不是很清楚这个introduction是没有关联到对应的记录，还是关联上了而这个值为null，null意思表示不明确，有歧义

注意：NULL在数据库里是非常特殊的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。使用 ISNULL()来判断是否为 NULL 值，NULL 与任何值的直接比较都为 NULL。

1) NULL<>NULL的返回结果是NULL，而不是false。
2) NULL=NULL的返回结果是NULL，而不是true。
3) NULL<>1的返回结果是NULL，而不是true。

根据版本号控制

锁定表或者行，让其他数据操作等待

表锁偏向MyISAM存储引擎，开销小，加锁快，无思索，锁定粒度大，发生锁冲突的概率最高，并发度最低
当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞 

行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MYISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

当前正在等待锁定的数量

从系统启动到现在锁定总时间长度

每次等待所花平均时间

从系统启动到现在等待最长的一次所花时间

系统启动后到现在总共等待的次数