北京軟件開發(fā)公司排紹興seo
高級語句 第一部分
- 一、MySQL進階查詢語句
- 1.1 select ----顯示表格中一個或數(shù)個字段的所有數(shù)據(jù)記錄
- 1.2 distinct ----不顯示重復的數(shù)據(jù)記錄
- 1.3 where ----有條件查詢
- 1.4 and or ----且 或
- 1.5 in----顯示已知的值的數(shù)據(jù)記錄
- 1.6 between----顯示兩個值范圍內的數(shù)據(jù)記錄
- 1.7 通配符
- 1.8 like ----模糊匹配
- 1.9 order by
- 1.10 group by ----匯總分組
- 1.11 having
- 1.12 別名 ----字段別名 表格別名
- 1.13 子查詢語句
- 1.14 exists
- 1.15 練習
- 二、MySQL數(shù)據(jù)庫函數(shù)
- 2.1 數(shù)學函數(shù)
- 2.2 聚合函數(shù)
- 2.3 字符串函數(shù)
- 1）trim
- 2）concat
- 3）substr
- 4）length
- 5）replace
- 三、連接查詢
- 3.1 表連接
- 3.2 union語句
- 3.3 多表查詢之求交集值
- 3.4 多表查詢之求無交集值
- 四、SQL語句執(zhí)行順序
- 小結
一、MySQL進階查詢語句
先建立byyb數(shù)據(jù)庫，再建立location 表和Store_Info 表，用于測試和演示。
create database byyb;use byyb;
create table location (Region char(20),Store_Name char(20));
insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');create table store_info (Store_Name char(20),Sales int(10),Date char(10));
insert into store_info values('Los Angeles','1500','2020-12-05');
insert into store_info values('Houston','250','2020-12-07');
insert into store_info values('Los Angeles','300','2020-12-08');
insert into store_info values('Boston','700','2020-12-08');
1.1 select ----顯示表格中一個或數(shù)個字段的所有數(shù)據(jù)記錄
語法
select "字段" from "表名";
舉個例子
select * from store_info;
select store_name from store_info;
1.2 distinct ----不顯示重復的數(shù)據(jù)記錄
語法
select distinct "字段" from "表名"
#舉個例子
select distinct store_name from store_info;
1.3 where ----有條件查詢
where是對源語句進行條件查詢。
語法
select "字段" from "表名" where "條件";
#舉個例子
select store_name from store_info where sales > 1000;
1.4 and or ----且 或
語法
select "字段" from "表名" where "條件1" {[and|or] "條件2"}+ ;
#舉個例子
select store_name from store_info where sales > 1000 or (sales < 500 and sales > 200);
1.5 in----顯示已知的值的數(shù)據(jù)記錄
語法
select "字段" from "表名" where "字段" in ('值1', '值2', ...);
#舉個例子
select * from store_info where store_name in ('los angeles', 'houston');
1.6 between----顯示兩個值范圍內的數(shù)據(jù)記錄
語法：select "字段" from "表名" where "字段" between '值1' and '值2';
#舉個例子
select * from store_info where date between '2020-12-06' and '2020-12-10';
1.7 通配符
% ：百分號表示零個、一個或多個字符
_ ：下劃線表示單個字符'A_Z'：所有以 'A' 起頭，另一個任何值的字符，且以 'Z' 為結尾的字符串。例如，'ABZ' 和 'A2Z' 都符合這一個模式，而 'AKKZ' 并不符合 (因為在 A 和 Z 之間有兩個字符，而不是一個字符)。
'ABC%': 所有以 'ABC' 起頭的字符串。例如，'ABCD' 和 'ABCABC' 都符合這個模式。
'%XYZ': 所有以 'XYZ' 結尾的字符串。例如，'WXYZ' 和 'ZZXYZ' 都符合這個模式。
'%AN%': 所有含有 'AN'這個模式的字符串。例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合這個模式。
'_AN%'：所有第二個字母為 'A' 和第三個字母為 'N' 的字符串。例如，'SAN FRANCISCO' 符合這個模式，而 'LOS ANGELES' 則不符合這個模式。
1.8 like ----模糊匹配
一般和通配符配合使用。
模糊匹配默認會掃描全表，索引不生效。
語法
select "字段" from "表名" where "字段" like {模式};
#舉個例子
select * from store_info where store_name like '%os%';
1.9 order by
按關鍵字排序
語法
select "字段" from "表名" [where "條件"] order by "字段" [asc, desc];
#asc 是按照升序進行排序的，是默認的排序方式。
#desc 是按降序方式進行排序。
#舉個例子
select store_name,sales,date from store_info order by sales desc;
1.10 group by ----匯總分組
對group by后面的字段的查詢結果進行匯總分組，通常是結合聚合函數(shù)一起使用的。
group by 有一個原則，凡是在 group by 后面出現(xiàn)的字段，必須在 select 后面出現(xiàn)；
凡是在 select 后面出現(xiàn)的、且未在聚合函數(shù)中出現(xiàn)的字段，必須出現(xiàn)在 group by 后面。
語法
select "字段1", sum("字段2") from "表名" group by "字段1";
舉個例子
select store_name, sum(sales) from store_info group by store_name order by sales desc;
select store_name,count(store_name) from store_info group by store_name;
1.11 having
對group by語句的結果，進行條件篩選。
用來過濾由 group by 語句返回的記錄集，通常與 group by 語句聯(lián)合使用.
having 語句的存在彌補了 where 關鍵字不能與聚合函數(shù)聯(lián)合使用的不足。
語法
select "字段1", sum("字段2") from "表格名" group by "字段1" having (函數(shù)條件);
#舉個例子
select store_name, sum(sales) from store_info group by store_name having sum(sales) > 1500;
1.12 別名 ----字段別名 表格別名
as可省略，僅在當前SQL語句生效。
語法
select "表格別名"."字段1" [as] "字段別名" from "表格名" [as] "表格別名";
#舉個例子
select a.store_name store, sum(a.sales) as "total sales" from store_info as a group by a.store_name;
1.13 子查詢語句
連接表格，在where 子句或 having 子句中插入另一個 sql 語句。
語法
select "字段1" from "表格1" where "字段2" [比較運算符] (select "字段1" from "表格2" where "條件");
#外查詢 (#內查詢)
#內查詢的結果，作為外查詢的參數(shù)[比較運算符]
#可以是符號的運算符，例如 =、>、<、>=、<=
#也可以是文字的運算符，例如 like、in、between
#舉個例子
select sum(sales) from store_info where store_name in (select store_name from location where region = 'West');
#舉個例子2
select sum(A.sales) from store_info as A where A.store_name in (select store_name from location as B where B.store_name = A.store_name);
#store_info表 別名為A表，在當前語句中，可以直接用a代替store_info使用
#location表 別名為B表
1.14 exists
用來測試內查詢有沒有產生任何結果。
如果有，系統(tǒng)就會執(zhí)行外查詢中的sql語句;
如果沒有，那整個 SQL語句就不會產生任何結果。
語法
select "字段1" from "表格1" where exists (select * from "表格2" where "條件";
#舉個例子
select sum(sales) from store_info where exists (select * from location where region = 'West');select sum(sales) from store_info where exists (select store_name from location where region ='Westt');
1.15 練習
通過SQL語句，查找到門店數(shù)大于等于2的地區(qū)
group by store_name having count(store_name) >=2;select store_name from store_info group by store_name having count(store_name) >=2;select store_name,count(store_name) from store_info group by store_name having count(store_name) >=2;
二、MySQL數(shù)據(jù)庫函數(shù)
2.1 數(shù)學函數(shù)
| 數(shù)學函數(shù) | 功能 |
|---|---|
| abs(x) | 返回 x 的絕對值 |
| rand() | 返回 0 到 1 的隨機數(shù) |
| mod(x,y) | 返回 x 除以 y 以后的余數(shù) |
| power(x,y) | 返回 x 的 y 次方 |
| round(x) | 返回離 x 最近的整數(shù) |
| round(x,y) | 保留 x 的 y 位小數(shù)四舍五入后的值 |
| sqrt(x) | 返回 x 的平方根 |
| truncate(x,y) | 返回數(shù)字 x 截斷為 y 位小數(shù)的值 |
| ceil(x) | 返回大于或等于 x 的最小整數(shù) |
| floor(x) | 返回小于或等于 x 的最大整數(shù) |
| greatest(x1,x2…) | 返回集合中最大的值，也可以返回多個字段的最大的值 |
| least(x1,x2…) | 返回集合中最小的值，也可以返回多個字段的最小的值 |
select abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
select round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
2.2 聚合函數(shù)
| 聚合函數(shù) | 功能 |
|---|---|
| avg() | 返回指定列的平均值 |
| count( 字段 ) | 返回指定列中非 NULL 值的個數(shù)（行數(shù)） |
| count(*) | 返回指定列中所有行數(shù)，不忽略NULL值 |
| min( ) | 返回指定列的最小值 |
| max( ) | 返回指定列的最大值 |
| sum(x) | 返回指定列的所有值之和 |
avg
select avg(sales) from store_info;
count
select count(store_name) from store_info;
select count(distinct store_name) from store_info;
max 和 min
select max(sales) from store_info;
select min(sales) from store_info;
sum
select sum(sales) from store_info;
2.3 字符串函數(shù)
| 字符串函數(shù) | 功能 |
|---|---|
| trim() | 返回去除指定格式的值 |
| concat(x,y) | 將提供的參數(shù) x 和 y 拼接成一個字符串 |
| substr(x,y) | 獲取從字符串 x 中的第 y 個位置開始的字符串，跟substring()函數(shù)作用相同 |
| substr(x,y,z) | 獲取從字符串 x 中的第 y 個位置開始長度為 z 的字符串 |
| length(x) | 返回字符串 x 的長度 |
| replace(x,y,z) | 替換，將字符串 z 替代字符串 x 中的字符串 y |
| upper(x) | 將字符串 x 的所有字母變成大寫字母 |
| lower(x) | 將字符串 x 的所有字母變成小寫字母 |
| left(x,y) | 返回字符串 x 的前 y 個字符 |
| right(x,y) | 返回字符串 x 的后 y 個字符 |
| repeat(x,y) | 將字符串 x 重復 y 次 |
| space(x) | 返回 x 個空格 |
| strcmp(x,y) | 比較 x 和 y，返回的值可以為-1,0,1 |
| reverse(x) | 將字符串 x 反轉 |
1）trim
#示例1：從名字開頭的開始，移除Sun Dasheng中的Sun顯示
select trim(leading ‘Sun’ from ‘Sun Dasheng’);
select trim([ [位置] [要移除的字符串] from ] 字符串);
#[位置]：的值可以為 leading (起頭), trailing (結尾), both (起頭及結尾)。
#[要移除的字符串]：從字串的起頭、結尾，或起頭及結尾移除的字符串。缺省時為空格。
#子查詢語句，select 嵌套selectselect trim(leading 'Los' from (select store_name from location where store_name='Los Angeles'));select trim( trailing 'York' from (select store_name from location where store_name='New York'));
2）concat
字段名 不要加 ' '
字符串 要加' '
select concat (region ,' ',store_name) from location;
select region|| ' ' || store_name from location;
3）substr
select substr(store_name,5) from location where store_name ='Los Angeles';select substr(store_name,5,6) from location where store_name ='Los Angeles';
4）length
select replace(region,'stern','st'),store_name,length(store_name) from location;
5）replace
select replace (region,'st','stern') from location;
三、連接查詢
3.1 表連接
| 表連接 | 概述 | |
|---|---|---|
| inner join | 內連接 | 只返回兩個表中聯(lián)結字段相等的行記錄 |
| left join | 左連接 | 返回包括左表中的所有記錄和右表中聯(lián)結字段相等的記錄，不相等的部分返回NULL |
| right join | 右連接 | 返回包括右表中的所有記錄和左表中聯(lián)結字段相等的記錄，不相等的部分返回NULL |
| union | 聯(lián)集 | 將兩個select查詢語句的結果合并，并去重 |
| union all | 聯(lián)集 | 將兩個select查詢語句的結果合并，不去重 |
3.2 union語句
聯(lián)集，將兩個sql語句的結果合并起來，兩個sql語句所產生的字段需要是同樣的數(shù)據(jù)記錄種類。
union ：生成結果的數(shù)據(jù)記錄值將沒有重復，且按照字段的順序進行排序。
語法
[select 語句 1] union [select 語句 2];
select store_name from location union select store_name from store_info;
union all ：將生成結果的數(shù)據(jù)記錄值都列出來，無論有無重復
語法
[select 語句 1] union all [select 語句 2];
select store_name from location union all select store_name from store_info;
3.3 多表查詢之求交集值
取兩個SQL語句結果的交集。
基本語法
select A.字段 from 左表 A inner join 右表 B on A.字段 = B.字段;
select A.字段 from 左表 A inner join 右表 B using(同名字段);select A.字段 from 左表 A, 右表 B where A.字段 = B.字段;select A.字段 from 左表 A where A.字段 in (select B.字段 from 右表 B);select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is not null;
select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is not null;
舉個例子
#求交集
#方式一
select A.store_name from location A inner join store_info B on A.store_name = B.store_name;
#方式二
select A.store_name from location A inner join store_info B using (store_name);
#取兩個SQL語句結果的交集，且沒有重復
select distinct A.store_name from location A inner join store_info B on A.store_name = B.store_name;select distinct A.store_name from location A inner join store_info B using (store_name);
select distinct A.store_name from location A inner join store_info B using (store_name) where B.store_name is not NULL;
select A.store_name from (select B.store_name from location B inner join store_info C on B.store_name = C.store_name) A group by A.store_name;
select A.store_name from (select distinct store_name from location union all select distinct store_name from store_info) A group by A.store_name having count( *) > 1;。#首先，子查詢`select distinct store_name from location`從“l(fā)ocation”表中選擇所有不重復的店鋪名稱。
#然后，子查詢`select distinct store_name from store_info`從“store_info”表中選擇所有不重復的店鋪名稱。
#使用`union all`將兩個子查詢的結果合并，并作為臨時表A。
#最后，對臨時表A按照店鋪名稱進行分組，使用`having count(*) > 1`篩選出出現(xiàn)次數(shù)大于1的店鋪名稱。
3.4 多表查詢之求無交集值
顯示第一個SQL語句的結果，且與第二個SQL語句沒有交集的結果，且沒有重復。
求左表無交集
select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null;select 字段 from 左表 where 字段 not in (select 字段 from 右表);求右表無交集
select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;select 字段 from 右表 where 字段 not in (select 字段 from 左表);求多表的無交集
select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(A.字段)=1;
舉個例子
select distinct store_name from location where (store_name) not in ( select store_name from store_info);#子查詢`select store_name from store_info`從"store_info"表中選擇所有的店鋪名稱。
#主查詢`select distinct store_name from location`從"location"表中選擇所有不重復的店鋪名稱。
#使用`where (store_name) not in`條件將主查詢中的店鋪名稱過濾掉那些在子查詢結果中出現(xiàn)的店鋪名稱。
select distinct A.store_name from location A left join store_info B using (store_name) where B.store_name is NULL;#使用`left join`將"location"表（作為左表，記為A）和"store_info"表（作為右表，記為B）按照店鋪名稱進行連接。
#使用`using (store_name)`條件指定以店鋪名稱為連接的字段。
#使用`where B.store_name is NULL`條件過濾掉在連接結果中，店鋪名稱在"location"表中出現(xiàn)但在"store_info"表中沒有匹配的記錄。
#最后，使用`distinct`關鍵字來返回不重復的店鋪名稱。
select A.store_name from (select distinct store_name from location union all select distinct store_name from store_info) as A group by A.store_name having count(*)=1;#子查詢`select distinct store_name from location`從"location"表中選擇所有不重復的店鋪名稱。
#子查詢`select distinct store_name from store_info`從"store_info"表中選擇所有不重復的店鋪名稱。
#使用`union all`將兩個子查詢的結果合并。
#將合并結果作為臨時表A，并使用`as A`來給臨時表起一個別名。
#在臨時表A的基礎上，使用`group by A.store_name`對店鋪名稱進行分組。
#使用`having count(*) = 1`篩選出出現(xiàn)次數(shù)為1的店鋪名稱。
四、SQL語句執(zhí)行順序
FROM
<left table>ON
<join_condition>
<join_type>JOIN
<right_table>WHERE
<where condition>GROUP BY
<group_by_list>HAVING
<having_condition>SELECTDISTINCT
<select list>ORDER BY
<order_by_condition>LIMIT
<limit number>########################################################################################################
在SQL中，一般而言，SQL查詢語句的執(zhí)行順序如下：1. FROM：指定要查詢的數(shù)據(jù)表或視圖。
2. JOIN：根據(jù)指定的條件連接多個表。
3. WHERE：基于指定的條件篩選出符合要求的行。
4. GROUP BY：按照指定的列進行分組。
5. HAVING：對分組后的結果進行條件篩選。
6. SELECT：選擇要返回的列。
7. DISTINCT：去除重復的行。
8. ORDER BY：按照指定的列進行排序。
9. LIMIT/OFFSET：限制返回的結果數(shù)量和起始位置。
小結
order by 字段 ASC|DESC #排序
group by 字段 #分組
group by 字段 having 條件表達式 #根據(jù)group by分組后的結果再進行條件過濾表連接
inner join 內連接，只返回兩個表的字段相等的行記錄
left join 左連接，返回左表所有的行記錄和右表字段相等的行記錄，不相等的行返回NULL
right join 右連接，返回右表所有的行記錄和左表字段相等的行記錄，不相等的行返回NULL
union 聯(lián)集，將兩個select查詢語句的結果合并，并去重
union all 聯(lián)集，將兩個select查詢語句的結果合并，不去重求交集
select A.字段 from 左表 A inner join 右表 B on A.字段 = B.字段;
select A.字段 from 左表 A inner join 右表 B using(同名字段);select A.字段 from 左表 A, 右表 B where A.字段 = B.字段;select A.字段 from 左表 A where A.字段 in (select B.字段 from 右表 B);select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is not null;
select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is not null;求左表無交集
select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null;select 字段 from 左表 where 字段 not in (select 字段 from 右表);求右表無交集
select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;select 字段 from 右表 where 字段 not in (select 字段 from 左表);求多表的無交集
select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(A.字段)=1;