常用SQL语句及MySQL规约
数据库
显示数据库列表
- show databases;
建库
- create database 库名
查看字符集
-
show variables like ‘charactor%’;
-
查看数据库字符集
- show create database 数据库名
修改字符集
-
修改数据库的字符集
- alter database 库名 character set ‘utf8’
-
修改表的字符集
- alter table 表名 convert to character set ‘utf8’
查看密码合法性
- show variables like ‘validate_password’;
刪库
- drop database 库名;
表
显示库中所有表
- use 表名;show tabales;
查看所有表信息(包括视图)
- show table status;
建表
-
创建一个临时表
- create temporary table 表名 (字段名,字段类型,约束)
- 断开MySQL连接,删除临时表
-
create table 表名 (字段名,字段类型,约束)
显示表结构
- desc 表名;
- show columns from/in 表名
- explain 表名
增加表字段
- alter table 表名 add 字段名 字段类型 约束;
删除表字段
- alter table 表名 drop 字段名;
更新字段属性
- alter table 表名 alter column 字段 字段类型;
清空表中所有记录
- delete from 表名;表示把表中所有记录清空,自增id不会删除;
- truncate table 表名;成功返回0,自增id也会删除
- 区别:1、事务。truncate 不可以 rollback,delete 可以 rollback;2、truncate 不会触发 delete 触发器;3、delete 一行一行删除,可以返回删除行数。
插入多条数据
- insert into 表名 values(1,’’,’’),(2,’’,’’),(3,’’,’’);
数据复制一遍重新插入
- insert into 表名 select * from 表名
更新字段内容
- update 表名 set 字段 = replace(字段,‘旧内容’,‘新内容’)
将查询结果导入新创建的表
- create table 新表 select * from 原表;
更新表名
- rename table 原表名 to 新表名
- alter table 原表名 rename 新表名
查看表创建语句
- show create table n;
删表
- drop table 表名;
存储引擎
- show engines;
索引
查看表索引
- show index from 表名;
创建索引
- create [Unique] index 索引名 on 表名(字段名)
- alter table 表名 add index [索引名] (字段名);
删除索引
- drop index 索引名 on 表名;
视图
将一段sql查询封装成一个虚拟的表,只保留逻辑,不保留任何数据。
很多地方可以共用一组查询
- 报表
创建视图
- create view 视图名 AS (查询语句)
更新视图
- create or replace view 视图名 AS (查询语句)
查看视图创建语句
- show create view 视图名
查看视图
- desc 视图名;
联接
内连接
- 两边表同时有对应的数据才显示
左外连接
- 读取左表的全部数据,即使右表没有对应数据
右外连接
- 读取右表的全部数据,即使左表没有对应数据
交叉连接
- 会返回两个表的笛卡尔积,返回结果的行数等于两张表行数的乘积
- select * from m,n;
- select * from m cross join n;
全连接
- 类似 FULL JOIN
- union 会去重
- union all 不会去重
函数
聚合函数
-
count(id)
- count(*)会统计NULL值,count(字段) 不统计NULL值
-
sum(age)
- SUM(字段) 如果字段都为NULL,返回 NULL,注意 NPE 问题。
-
avg(age)
-
max(age)
-
min(age)
数学函数
- abs(-5)
- 进制:bin(15)、oct(15)、hex(15)
- 圆周率:pi()
- ceil(5.5)
- floor(5.5)
- greatest(3,2,3,4,4) 返回集合中的最大值
- least(3,2,3,4,4) 返回集合中的最小值
- mod(5,2) 返回余数
- rand() 随机数
- round(1314.1314,-1) 四舍五入整数位
- truncate(1314.1314,2) 截短为两位小数
- sign(-5) 符号位是-1
- sqrt(9) 平方根3
字符串函数
- concat(‘a’,‘b’,‘c’)
- insert(‘chinese’,3,2.‘IN’);
- left(‘chinese’,4)
- right(‘chinese’,4)
- substring(‘chinese’,-3) 倒数第三个字符之后
- trim(’ chinese ') 去重
- repeat(‘girl’,3) 重复三次
- reverse(‘chinese’) 反转
- length(‘chinese’)
- upper(‘chINese’)
- lower(‘chINese’)
- position(‘i’ IN ‘chinese’) 返回 i 在 chinese 的第一个位置 -3
- strcmp(‘abc’,‘abd’) 比较字符串,第一个字符串小于第二个字符串,返回-1
时间函数
- DIFF(data1,data2) 返回 date1-date2 后的值
- datetime(8B):YYYY-MM-DD HH:MM:SS
- timestamp(4B):YYYY-MM-DD HH:MM:SS
- current_date、current_time、now()
- hour(current_time)…
控制流函数
系统信息函数
- 当前数据库名:select databases;
- 当前用户id:select connection_id()
- 当前用户:select user()
- 当前mysql版本:select version()
- 上次查询的检索行数:select found_rows()
其他
数据库备份
- mysqldump -u root -p db_name > file.sql
mysqldump -u root -p db_name table_name > file.sql
数据库还原
- mysql -u root -p < C:\file.sql
参考《阿里巴巴Java开发手册》
建表规约
-
表达是否概念的字段,必须使用is_xxx命名,数据类型unsigned tinyint类型。1表示是,0表示否。
- 任何非负整数的字段,必须是unsigned类型
- POJO类中任何表达是否的字段,都不加is前缀
- 表达逻辑删除的字段是is_deleted,1表示删除,0表示未删除
-
表名、字段名必须使用小写字母或数字,禁止出现以数字开头、两个下划线中间只有数字的命名。
- 正例:aliyun_admin、rdc_config、level3_name
- 反例:AliyunAdmin、RdcConfig、level_3_name
-
小数类型使用decimal,禁止使用float和double类型
- 在存储的时候,float和double会存在精度损失的问题,很可能在比较值得时候,得到不正确的结果
- 如果存储的数据超过decimal范围,将整数和小数分开存储。
- float 是浮点数,不能指定小数位。decimal 是精确数,可以指定精度,在内存中以字符串形式保存。
- decimal(5,2)小数点默认存储两位,不足补0,如果位数超过5位,保存报错。
-
如果存储的字符串长度几乎相等,使用char定长字符串类型
- 定长字符串 char:0 ~ 255
-
varchar是可变长字符串字段,不预先分配存储空间,长度不要超过5000。如果超过此长度,定义字段类型text,独立出一张表,用主键对应,避免影响其他字段的索引效率
- 可变字符串 varchar:0 ~ 65535
- 长文本数据 text:0 ~ 65535
-
表必备三字段:id、create_time、update_time
-
推荐
-
表名最好是:业务名称_表的作用
-
库名与应用名称尽量一致
-
如果修改字段的含义,需要更新字段注释
-
单表超过500万行数据或表容量超过2GB,才推荐分库分表
- 如果预计三年后达不到这个数据量,就不推荐分库分表
-
合适的字符存储长度,节省空间,也提高了查询效率,无符号数避免了误存负数
- unsigned tinyint:0 ~ 255
- smallint unsigned:0 ~ 65535
- int unsigned:0 到 约43亿
- bigint unsigned:0 到 约10的19次方
-
索引规约(详见索引优化)
-
主键索引名为pk_字段名,唯一索引名为uk_字段名,普通索引名为idx_字段名。
-
业务上具有唯一特性的字段,即使是组合索引,也必须建成唯一索引
-
超过三个表禁止join。需要 join 的字段,数据类型必须保持一致;多表关联时,保证被关联的字段必须有索引。
- 即使是双表也需要建索引,以提高SQL性能
-
在varchar上建立索引,必须指定长度,没必要对全字段建立索引,根据实际文本的区分度建索引
-
页面搜索严禁左模糊或全模糊,索引有左前缀匹配的特性,索引会失效
-
推荐
-
如果有 order by 的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序最后,避免出现 file_sort 现象。
- 正例:where a=? and b=? order by c; 索引:a_b_c
- 索引如果存在范围查询,那么索引有序性无法利用,如:WHERE a>10 ORDER BY b; 索引a_b 无 法排序。
-
利用覆盖索引来查询数据,避免回表
- 说明:如果要知道一本书的第11章的标题是什么?那么需要翻到书的第11章节吗?并不需要,只需要浏览目录就可以了。
-
利用延迟关联或子查询优化超多分页场景。
-
SQL性能优化的目标:至少要达到 range 级别,要求是 ref 级别,最好能到 consts 级别。
- consts 最多只有一个匹配行,在优化阶段即可读取到数据。
- ref 指使用的是普通索引
- range 指使用索引进行范围检索
-
建立组合索引,区分度最高的放在最左边。
- 如果 where a = ? and b = ? ,如果a的区分度较高,只需要创建单键 idx_a 索引。
- 存在等号和非等号混合判断条件时,在建索引时,把等号条件的列前置。如 where c > ? and d = ?。即使c的区分度再高,也应该把 d 索引放在最前列。
-
防止因字段类型不同造成的隐式转换,造成索引失效。
- 索引列是字符串,查询条件无 ’ ',索引失效。
-
-
参考
-
索引宁滥勿缺。
- 认为一个查询就需要建一个索引。
-
吝啬索引的创建。
- 认为会消耗内存空间,拖慢记录的更新操作。
-
抵制唯一索引。
- 认为唯一索引一律需要在应用层通过 “先查后插” 的方式解决。
-
SQL语句
-
不要使用 count(列名) 或 count(常量) 替代count()。count() 会统计 NULL 值,count(列名)不会统计此列为 NULL 的值。
-
count(distinct col) 会统计此列除NULL之外的不重复的行数。注意 count(distinct col1,col2),如果其中一列全为 NULL,那么即使另一列有不同的值,也返回 NULL。
-
当某一列的值全为 NULL时,count(列名) 返回 0,SUM (列名) 返回 NULL,因此要注意 SUM 的 NPE问题。
-
使用 ISNULL 来判断是否为 NULL值
-
说明:NULL 值与任何值比较都为 NULL
-
NULL <> NULL 比较的结果值为 NULL,而不是 false
-
NULL = NULL 比较的结果值为 NULL,而不是 true
-
NULL <> 1 比较的结果值为 NULL,而不是 true
-
sql 语句中,如果 null 前换行,影响可读性
- select * from table where column1 is null and column3 is not null;,而ISNULL(column1)是一个整体。
- ISNULL 的执行效率比 IS NULL 的效率高。
-
-
代码中写分页查询逻辑时,若 count 为0直接返回,避免执行后面的分页语句。
-
不得使用外键和级联,一切外键应在应用层解决
- 级联更新:学生表中的 student_id 是主键,成绩表中的 student_id 是外键。如果想更新 学生表中的 student_id,同时更新成绩表中的 student_id,称为级联更新。
- 外键与级联更适用于单机的低并发,不适合分布式、高并发集群;外键影响数据的插入操作;级联更新是强阻塞,存在数据库更新风险。
-
对于数据库中表记录的查询和变更,只要设计多个表,都需要在列名前加表的别名进行限定。
-
参考:TRUNCATE 比 DELETE 速度快,使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 trigger,有可能造成事务。
ORM映射
-
在表查询时,一律不要使用 * 作为查询字段,使用哪些字段必须明确声明。
- 增加查询分析器的成本
- 增减字段容易与 resultMap 不一致
- POJO 类的布尔属性不能加 is,
-
POJO 类的布尔属性不能加 is,而数据库的字段必须加 is_,要求在 resultMap 中进行字段和属性的映射。
-
不要使用 resultClass 当返回参数,即使所有类属性名与数据库字段一一对应。
-
sql.xml 配置参数使用 #{},#param#,不要使用${},容易出现SQL注入。
-
iBATIS 自带的 queryForList(String statementName,int start,int size) 不推荐使用
-
不允许直接拿 HashMap 和 Hashtable 作为结果集直接返回结果。
-
更新数据表记录时,同时也更新记录对应的 update_time 字段值为当前时间。
-
参考:TRUNCATE 事务不要乱用。事务会影响数据库的 QPS。
Linux下的MySQL
连接远程主机
- mysql -h 主机地址 -u用户名 -p用户密码
查看mysql进程
- ps -ef|grep mysqld
查看mysql版本
- mysqladmin --version
启动服务
-
CentOS 8
- systemctl start mysql
- systemctl stop mysql
-
CentOS 7
- service mysql start
- sevice mysql stop
更新权限
- 刷新与系统相关的权限表
- flush privileges;
退出
- quit
- exit
用户
新增用户
- create user ‘test’@‘localhost’ identified by ‘test’;
- INSERT INTO mysql.user(Host, User, Password) VALUES (‘localhost’, ‘test’, Password(‘test’)); # 在用户表中插入用户信息,直接操作User表不推荐
删除用户
- DROP USER ‘test’@‘localhost’;
- DELETE FROM mysql.user WHERE User=‘test’ AND Host=‘localhost’;
- FLUSH PRIVILEGES ;
更改用户密码
- SET PASSWORD FOR ‘test’@‘localhost’ = PASSWORD(‘test’);
- UPDATE mysql.user SET Password=Password(‘t’) WHERE User=‘test’ AND Host=‘localhost’;
- FLUSH PRIVILEGES ;
用户授权
- GRANT ALL PRIVILEGES ON . TO test@localhost IDENTIFIED BY ‘test’;
- FLUSH PRIVILEGES ;
撤销用户授权
- REVOKE DELETE ON . FROM ‘test’@‘localhost’; # 取消该用户的删除权限
XMind - Trial Version