# Sql 常用语句

常用工具Sql

explain 查看Sql执行计划

explain select * from testone order by age asc limit 0,3;

查看所有进程

show full processlist;

查询事物

select * from information_schema.INNODB_TRX it ;

• 结束进程：kill {id}

---

DDL

创建数据库

create database ldtest;

删除数据库

drop databases test;

创建新表

create table `ldtest`.`ldtest`(
	`id` int not null auto_increment,
	`name` varchar(20) default null comment '姓名',
	primary key (`id`)
)

创建新表（如果已存在则删除创建）

drop table if exists `test`;
create table `test`(
	`id` int not null auto_increment,
	`name` varchar(20) default null comment '姓名',
	primary key (`id`)
)

根据已有的表新建

• 方法一

create table `ldtest`.`test1` like `ldtest`.`ldtest`;

• 方法二

create table `ldtest`.`test2` as select * from `ldtest`.`ldtest`;

增加一个列

alter table `ldtest`.`ldtest` add column `age` int;

添加主键

alter table `ldtest`.`ldtest` add primary key(age);

删除主键

alter table `ldtest`.`ldtest` drop primary key(age);

添加索引

create unique index uk_name on `ldtest`.ldtest (name);

删除索引

drop index uk_name on `ldtest`.ldtest ;

创建视图

create view `ldtest`.`view_test` as select * from `ldtest`.`ldtest`;

删除视图

drop view `ldtest`.`view_test`;

定义变量赋值并使用

• demo1

set @id = '1001', @name='测试';
insert into test (id,name) values (@id,@name);

• demo2

set @id = '1001', @name=(select name from user limit 1);
insert into test (id,name) values (@id,@name);

---

常用Sql

limit

分页从0页开始，第0页是第一页，每页3条数据

select * from testone order by age asc limit 0,3

---

left join

以左边的表为基础，如果右表没有匹配到则这条记录的左边列信息全为空。

select * from testone x left join testtwo y on x.sno = y.sno ;

---

right join

与left join 语句相反，以右表为基础。

---

case_when用法

select
	*,
	case
		gender when 1 then '男'
		when 0 then '女'
		else '其他'
	end as xingbie
from
	t_user tu

---

concat 字符串连接

select id,concat(name,":",age) as info from ldtest ;

---

group_concat 分组字符串连接

• 将多行数据中的值合并到一个单元格中

select group_concat(name) as name from ldtest  group by name 

---

regexp 正则表达式查询

-- 匹配subjects列用逗号分隔的数据
select * from testtwo where subjects regexp ',';

---

组内排序

• 根据降序排序给查询的结果增加一列显示排序的编号：排序编号的参数需要用:=进行赋值，不能使用=因为在select语句中等号是比较运算符。

SELECT x.*,@rank :=@rank + 1 AS rank_no
FROM
	(
		SELECT * FROM jobs ORDER BY max_salary DESC
) x,(SELECT @rank := 0) b

查询结果如下：

• 对统计的结果进行降序排序并且显示相应的排序编号

SELECT
	x.*,@rank :=@rank + 1 AS rank_no
FROM
	(
		SELECT z.* FROM
		(SELECT count(*) cnt,y.salary,y.job_id FROM employee y GROUP BY y.job_id) z
		ORDER BY z.cnt DESC
	)x,(SELECT @rank:=0)a

---

分组内排序

• 对相同的科目内进行排序如下：对Math、English、pe的分数进行排序显示

SELECT
	x.*,
    IF (@g = x.item ,@rank :=@rank + 1 ,@rank := 1) AS rank,
    @g := x.item AS 'group'
FROM
	(SELECT z.id,z.item,z.score,z.time FROM test1 z
	ORDER BY z.item,z.score DESC) x,
	(SELECT @rank := 0 ,@g := NULL) y

实现的组内排序结果如下：

• 分组排序的结果限制如下：

select a.* from(
SELECT
	x.*,
  if(@g=x.item,@rank:=@rank+1,@rank:=1) as rank,
	@g:=x.item as 'group'
FROM
	(select z.id,z.item,z.score,z.time from test1 z order by z.item,z.score desc) x,
	(SELECT @rank := 0,@g:=NULL) y
)a WHERE a.rank<=2

---

Count函数：Count(*) 会统计值为null的行Count(列名字)不会统计值为NULL的行

select  count(*) as cnt  from hr.employee x
//查询速度较快
select  count(1) as cnt  from hr.employee x
select  count(x.id) as cnt  from hr.employee x
//Count的时候会去除uname值为NULL的行
SELECT count(x.uname) as cnt,x.uname FROM user x GROUP BY x.uname

---

Sum函数

SELECT SUM(y.cnt) as sum,y.uname  from(
SELECT count(*) as cnt,x.uname FROM user x GROUP BY x.uname
)y

---

Ifnull判断填值函数

//如果y.uname为空则添加为总数
SELECT SUM(y.cnt) as sum,IFNULL(y.uname,"总数") as tag  from(
SELECT count(*) as cnt,x.uname FROM user x GROUP BY x.uname
)y

---

WIthROLLUP函数

SELECT  COALESCE(x.uname,"总数") ,x.uname,SUM(x.id) as sum  
FROM  user x where 1=1 and x.uname is not null  GROUP BY x.uname with ROLLUP

---

时间格式化函数

SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %h:%m:%s')
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')

---

求出某列字段的最大长度

• Mysql：使用Length()

SELECT max(LENGTH(time)) as  max_length from result

• SqlServer：使用datalength()

SELECT MAX (DATALENGTH(time)) FROM result

• Oracle：使用length()

SELECT max(LENGTH(time)) as  max_length from result

• PostgreSQL：length('字符串') char_length('字符串')
  length()、char_length()只能测字符串的长度，所以不能直接统计列的长度
• Greenplum：length('字符串') char_length('字符串')
  length()、char_length()只能测字符串的长度，所以不能直接统计列的长度
• Db2：使用length()

select max(length(test1)) maxlength,min(length(test2)) minlength from test

---

判断非空

• Mysql：max（）和min（）不能筛选null的值，所以我们应该判断如果记录中有null的时候记录的min（）应该是0。使用ifnull(column，defaultvalue)进行判断,如果column的值为null,那么赋给column为0。

select max(ifnull(length(column1),0)) maxlength,min(ifnull(length(column1),0)) minlength from test

• Sqlserver：使用isnull()进行空值的时候赋值

select max(isnull(datalength(column1),0)) maxlength,min(isnull(datalength(column1),0)) minlength from test

• Postgresql：不需要判断空值，max() min()可以计算null的值为0

select max(test3) maxlength,min(test3) minlength from test

• Greenplum：不需要判断空值，max() min()可以计算null的值为0

select max(test3) maxlength,min(test3) minlength from test

• Oracle：使用nvl()函数，当为空值时候，给空值赋值

select max(nvl(length(test1),0)) maxlength,min(nvl(length(test1),0)) from test

• Db2：使用coalesce()，当该列的内容为空值时候，给空值赋值

SELECT max(coalesce(length(column1),0)),min(coalesce(length((column1),0)) FROM test

---

处理列名、表名含有单引号

• 建的表含有单引号、列含有单引号如下
  

Mysql

​ 当mysql查询语句字段为mysql特殊符号时候，或者出现单双引号的情况的时候，使用Mysql的封闭符号加上封闭符号后问题解决。

查询的列含有单引号

• 加上Mysql的封闭符号

select `'rewrwe'rwqre'rewq'_'`  from `'fdsa'_'fdsa_'`

查询的列含有双引号

• 加上Mysql的封闭符号

select `"name"_"a"`  from `"dsds"a_"dasd"`

表名含有单、双引号

• 加上Mysql的封闭符号

select * from `'fdsa'_'fdsa_'`;
select * from `"dsds"a_"dasd"`;

Where条件中含有单引号

• 使用转义符，两个单引号代表一个单引号：例如name的实际值是'a'b_'c'

select `'rewrwe'rwqre'rewq'_'`,`name`  from `'fdsa'_'fdsa_'` where `name`='''a''b_''c'''

---

Oracle

查询的列含有单引号

• 加上封闭符号

select "li'si'" from t1;

查询的列含有双引号

表名含有单、双引号

• 加上封闭符号：表名称为a'b'_'c'

select  name  from  "a'b'_'c'"

Where条件中含有单引号

• 用转义符：查找name='zh'angs'an的数据。

select name from test1 where name='''zh''angs''an';

---

Db2

查询的列含有单引号

• 加上封闭符号

select "'name'_'a'_'" from tabletest;

表名含有单、双引号

• 加上封闭符号：表名称为a'b'_'c'

select  name  from  "a'b'_'c'"

Where条件中含有单引号

• 用转义符：查找name='zh'angs'an的数据。

select name from test1 where name='''zh''angs''an';

---

PostGreSql

查询的列含有单引号

• 加上封闭符号

select "'name'_'a'_'" from tabletest;

查询的列含有双引号

• 加上封闭符号并且使用转义符号：两个双引号代表一个双引号

select """a_""_b""" from test1;

表名含有单、双引号

• 加上封闭符号：表名称为a'b'_'c'

select  name  from  "a'b'_'c'";
select * from """a""_b""_c"""; # 表名含有双引号

Where条件中含有单引号

• 加上封闭符号：两个单引号代表一个单引号，字段的值最外面用单引号括起来

select "'rewrwe'rwqre'rewq'_'",name  from  "a'b'_'c'" where "'rewrwe'rwqre'rewq'_'"='''rewrwe''rwqre''rewq''_'''

Where条件中含有双引号

• 直接查询不用处理

select * from test where name='"b_"f_"';

---

GreenPlum

查询的列含有单引号

• 加上封闭符号

select "'name'_'a'_'" from tabletest;

查询的列含有双引号

• 加上封闭符号并且使用转义符号：两个双引号代表一个双引号

select """a_""_b""" from test1;

表名含有单、双引号

• 加上封闭符号：表名称为a'b'_'c'

select  name  from  "a'b'_'c'";
select * from """a""_b""_c"""; # 表名含有双引号

Where条件中含有单引号

• 加上封闭符号：两个单引号代表一个单引号，字段的值最外面用单引号括起来

select "'rewrwe'rwqre'rewq'_'",name  from  "a'b'_'c'" where "'rewrwe'rwqre'rewq'_'"='''rewrwe''rwqre''rewq''_''';

Where条件中含有双引号

• 直接查询不用处理

select * from test where name='"b_"f_"';