SQL

explain


1
explain select * from basetab_sg where RoleID in (select roleid from basetab_sg);

查询版本


1
select version();

order by


1
2
asc 升序
desc 降序

group by


1
2
3
SELECT length, count(id) FROM `db_mydomain_8` where length > 20 GROUP BY length;

SELECT length, count(id) FROM `db_mydomain_8` where length > 20 GROUP BY length ORDER BY length desc;

limit


1
SELECT length, count(id) FROM `db_mydomain_8` where length > 20 GROUP BY length ORDER BY length desc limit 2, 1;

inner join


1
SELECT * FROM `basetab_sg` as b inner join users as u on u.ID = b.AccountID;

left join

左连接，包括左表全部记录


1
select u.`name`, b.ProtectInfo from users as u left JOIN safetab_sg as b on u.ID = b.AccountID;

right join

右连接，包括右表全部记录

通配符

_ 匹配单个字符

% 匹配任意字符


1
SELECT * FROM `basetab_sg` as b inner join users as u on u.ID = b.AccountID where b.name like '_i%';

子查询


1
select * from basetab_sg where name in (SELECT name from users where name in ('oppo', 'vivo'));

exists


1
select * from basetab_sg where EXISTS (SELECT name from users where name in ('nokia'));

count

count(field) 忽略NULL 3

count(*) 全部 4


1
2
3
SELECT COUNT(truth) FROM `tbl_test` ;

SELECT COUNT(truth) FROM `tbl_test` where truth is not null ;

regexp


1
2
3
SELECT * FROM `users` where name regexp 'pp';

SELECT * FROM `users` where name regexp '^[o]';

运算符

+、-、*、/、%


1
select 2*profession from basetab_sg limit 3;

比较运算符

= 等于

> 大于

< 小于

>= 大于等于

<= 小于等于

!=、<> 不等于

is null 判断是否null

is not null 判断是否不为null

between and 两者之间

in 在集合中

like 模糊匹配

not like 不像

greatest 返回最大参数

least 返回最小参数，有null返回null

regexp 正则匹配，有null返回null


1
2
// 3 a
select GREATEST(1, 2, 3), LEAST('a', 'b', 'c');

逻辑运算符

not、! 非

and、&& 与

or、|| 或

xor 异或，有null返回null，相同返回0


1
select 1 xor null, 2 xor 2, 0 xor 1;

位运算符

对二进制进行计算

& 位与

| 位或

~ 位反

^ 位异或

<< 位左移

>> 位右移