# Sql 常用語句

MarlonBrando1998 2022-01-08 05:01:08 阅读数:390

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_"';
版权声明:本文为[MarlonBrando1998]所创,转载请带上原文链接,感谢。 https://gsmany.com/2022/01/202201080501079529.html