MySQL 常用语句-蒲公英云

MySQL 常用语句

基本操作

case

MySQL的CASE表达式有2中形式：
1、像是编程语言当中的CASE语句，拿一个给定的值（变量）跟一系列特定的值作比较。

CASE value
WHEN compare_value_1 THEN result_1
WHEN compare_value_2 THEN result_2
…
ELSE result END

2、像是编程语言中的if语句，当满足某些条件的时候取特定值。

CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
…
ELSE result END

如果省略了ELSE语句，并且各个条件都没满足，则返回NULL

IFNULL

-- 如果expression_1 不为null,则返回expression_1，否则返回expression_2. IFNULL(expression_1,expression_2);

IF

IFNULL的通用形式是IF表达式，类似于Java中的三元操作符，其逻辑为：如果condition为true，返回expression_1，否则返回expression_2。

IF(condition,expression_1,expression_2)

统计SQL 执行时间

set @d:=now();
-- TODO 具体的查询
select timestampdiff(microsecond, @d, now())/1000 '耗时（ms）';

查询表有多少列

SELECT COUNT(1) FROM information_schema.COLUMNS WHERE table_schema='数据库' AND table_name='表名';

查询

排序

MySQL 通过Order by语句即能达到将指定的结果集排序的目的，其实不仅仅是Order by语句，Group by语句，Distinct语句都会隐含使用排序。

排序优化与索引使用
参考

为了优化SQL语句的排序性能，最好的情况是避免排序，改为索引。因为索引本身也是有序的，如果在需要排序的字段上面建立了合适的索引，那么就可以跳过排序的过程，提高SQL的查询速度。
无法使用索引时，数据库就得自己实现排序，SQL执行计划中会出现“Using filesort”

null排序

MySQL 的 ORDER BY 默认将 null 算作最小值。
当然也可以强行控制：
1、null 强制放最前

if(isnull(name), 0, 1) asc
// 或
if(isnull(name), 1, 0) desc

以 if(isnull(name), 0, 1) asc 为例：它的意思是将该字段根据是否为null值分成两部分，值为null行相当于得到了一个隐含的排序属性0，值为非null的行相当于得到了一个隐含的排序属性1，在排序的时候，先根据这一隐含属性进行升序排列，排序字段为null的那些行（隐含排序属性）会在最前。

2、null 强制放最后

if(isnull(name), 1, 0) asc
// 或
if(isnull(name), 0, 1) desc

varchar排序

MySQL 的 ORDER BY 默认对数字和日期类型排序较符合我们的使用习惯。但对于 varchar 并不是按汉字拼音的顺序来排序，要想按汉字的拼音来排序，需要把数据库的字符集设置为UTF8，然后在order by 时候强制把该字段信息转换成GBK，这样出来的结果就是按拼音顺序排序的。
若想按照首字母汉语拼音排序：

// 适用数字开头的varchar：varchar -> int
order by (name+0) asc;
order by (name*1) asc;
order by cast(name as SIGNED) asc;
order by convert(name, SIGNED) asc;
// 适用汉字开头的varchar（前提：数据库字符集UTF8）：
order by convert(name using gbk) asc;

2、

修改

update

修改或更新 MySQL 中的数据。

UPDATE table_name 
SET field1=new-value1, 
    field2=new-value2
[WHERE Clause]

alter

修改数据表名或者修改数据表字段。

-- 修改表名称
alter tablename a1 rename b1;
-- 添加变量
alter tablename a1 add column score float; 
-- 修改变量
alter tablename a1 change column score score s_score int (10); 
-- 删除变量
alter tablename a1 drop s_score ; 
-- 修改列名
alter tablename change a1 a1_1 varchar(20);

删除

删除 c_name 字段（字符串类型）结尾的换行符号：\r\n

update A set c_name=replace(c_name,'\r\n','') where c_name like '%\r\n';
-- 下面去除空格可以，但去除换行做不到
update A set c_name=trim(c_name) where c_name like '%\r\n';