Mysql入門實戰中

Mysql入門實戰中

前面一章主要講解了mysql的入門學習，包括數據庫，表的管理，以及對數據的增刪改，本章主要介紹mysql最重要的語句select的用法，將select的大部分用法進行分別講解。

1.select語句簡單介紹：

select語句從語義上就可以知道主要是用來進行查詢的

數據表都已經創建起來了，我們已經插入了許多的數據，我們可以用自己喜歡的方式對數據表裡面的信息進行檢索和顯示了。比如說顯示我們建立的表student所有的數據：

select * from student;

顯示如下：

select語法格式：

1. 簡單語法：

            select  [字段] from 表名 where 條件判斷

2. 復雜的語法：

 SELECT select_list --描述結果集的列
INTO new_table_name --指定使用結果集來創建新表
FROM table_list  --包含從中檢索到結果集數據的表的列表[返回結果集的對象]。
[ WHERE search_conditions ] --WHERE 子句是一個篩選，它定義了源表中的行要滿足 SELECT 語句的要求所必須達到的條件
[ GROUP BY group_by_list ] --根據 group_by_list 列中的值將結果集分成組
[ HAVING search_conditions ]  --結果集的附加篩選
[ ORDER BY order_list [ ASC | DESC ] ]  --結果集的附加篩選

2.簡單查詢：

2.1查詢指定字段

第一部分介紹的 select * from student;中的*號是通配符，意思查詢的全部列，平時我們經常要查詢的可能只是名字或者分數，這是就可以指定字段進行查找

語法格式：

SELECT 字段名 FROM 表名；

演示如下：選擇指定字段為sname，mark的兩列

select sname,mark from student;

可以使用as關鍵字為字段定義別名：

如：

-- 查詢指定的列 並指定別名as--

select sname,sid as id from student;

顯示如下：

查詢時可以添加常量列，比如某個學生都屬於2014級自動化班：

-- 增加一個班級列，內容為：自動化班  字段名稱為2014級 --

select sid,sname,'自動化班' as '2014級' from student;

2.2限制查詢行數：也叫分頁查詢；

當我們數據庫很大時為了顯示或處理方便可以使用分頁查詢：

1.語法格式：

select *  from  student  limit [index],[count];行數不足，返回行數為實際值

或者：

select *  from  student  limit [count] OFFSET  [index]; 

index數為開始位置的前一行，count數為要檢索的行數；

2. 演示如下：選出student的從第二行開始的3行

-- 限制查詢行數 --

select sid,sname from student limit 1,3;

3. 還可以選擇倒敘或者升序：desc降序，asc升序

-- 查詢排序  Desc降序排序 --

select * from student order by sid Desc limit 1,3;

4. 注意：

在給出order by子句時，應該保證他位於 from子句之後。如果使用limit，他必須位於order by子句之後，使用子句的次序不對將產生錯誤消息

3.復雜查詢：

3.1查詢之過濾：

數據庫表一般包含大量的數據，很少需要檢索表中所有行。通常會根據特定操作或報告的需要提取表數據的子集。

1.語法格式：

select * from student where 條件;

如：查詢sid=2的學生：

-- 檢查單個值--

select * from student where sid=002;

select * from student where sid<>002;

2.支持的子句操作符：

<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxjb2RlIGNsYXNzPQ=="hljs sql">3.范圍過濾：

為了檢查某個范圍的值，可使用between操作符：

語法：select from***where***between***and***

如：sid在002到004之間

演示：select * from student where sid between 002 and 004;

4.空值檢查：

在創建表時，表設計人員可以指定其中的列是否可以不包含值。在一個列不包含值時，被稱作包含空值null：

“`語法： select ***from***where***is***null““

如：

演示：select * from student where sgender is null;

5.條件邏輯過濾：

為了進行更強的過濾控制，MySQL允許給出多個where子句。這些子句可以兩種方式使用：以and子句的方式或or子句的方式使用:

語法：select * from student where 條件1 and（or） 條件2;

如：選擇sid==001和002並且mark大於1

演示：select * from student where (sid=001 or sid=002) and mark>1;

注意：and和or的組合帶來了一個問題——計算次序：and操作符的優先級高於or.所以上面演示的時候將or語句用括號包圍了

6.In操作符：

in操作符用來指定范圍，范圍中的每個條件都可以進行匹配。in取合法值的由逗號分隔的清單，全部括在圓括號中

語法：select ***from***where in (清單)

如：選擇出sid在(001,002,003)中

演示：select * from student where sid in (001,002,003);

為什麼要使用IN操作符？其優點如下

? 在使用長的合法選項清單時，In操作符的語法更加清楚而且更直觀 ? 在使用IN時計算的次序更容易管理 ? IN操作符一般比OR操作符清單執行更快

? IN的最大優點是可以包含其他select語句，似的能夠更動態的建立where子句

7.not操作符：

WHERE子句中的NOT操作符有且只有一個功能，那就是否定它之後的任何條件。

如否定剛剛的in條件：

select * from student where sid not in (001,002,003);

8.使用通配符：

用來匹配值的一部分的特殊字符，

為在搜索句子中使用通配符，必須使用like操作符。like指示mysQL後跟的搜索模式利用通配符匹配而不是直接相等匹配進行比較。

在搜索串中，%表示任何字符出現任意次數

下劃線的用途與%一樣，但下劃線只匹配單個字符而不是多個字符

演示如下：

-- 用通配符進行過濾 like % _-- 

-- 選出名字中含有eac的詞-- 

select * from student where sname like '%eac%';

-- 選出第一個字符任意，後面字符為eace的詞-- 

select * from student where sname like '_eace';

9.用正則表達式：

正則表達式的作用是匹配文本，將一個模式（正則表達式）與一個文本串進行比較。關鍵字為regexp；正則表達式搜索與like比較相似；此去只做簡單演示；

-- 正則表達式，不同於like，使用的操作符是regexp，匹配的也是含有-- 

-- 基本字符匹配-- 

select sname from student where sname regexp 'ong';-- 此去如果用like不會返回結果，因為不加通配符時，like時完全匹配； 

-- 正則書寫部分基本與其他正則語言一致--

select sname from student where sname regexp '[peac,ron]';

select * from student where sid regexp '[[:digit:]]';

3.2查詢之計算字段：

查詢可以對一些字段進行算術運算，包括加減乘除；

concat（）函數來拼接兩個列。

-- 拼接-- 

select concat(sname,'(',sid,')') as sname from student;

+,-,×，/用來計算字段：

-- 執行算法計算-- 

select sid,sname,sid*mark as imark from student;

3.3使用函數：

對獲得的數據進行處理比如對獲得的字符串處理，對日期處理，對數值處理；

1.支持的函數：

2.簡單演示：

-- 文本處理函數-- 

select sname,upper(sname) from student;

-- 日期和時間處理函數-- student

-- select c_id,order_num from orders where date(order_date)='2015-11-4';

-- 數值處理函數-- 

select sid,sname from student where mod(sid,2)=0;

3.4匯總數據（合計函數）：

1.avg max, min, sum合計函數

Sum函數返回滿足where條件的行的和

AVG函數返回滿足where條件的一列的平均值

Max/min函數返回滿足where條件的一列的最大/最小值

演示：

select avg(sid) as avg,

       min(sid) as min,

       max(sid) as max,

       sum(sid) as sum

from student;


-- 聚集不同的值 --distinct不相等的值

select avg(distinct mark) from student;

2.count函數：

Count(列名)返回某一列，行的總數

演示：

select count(*) from student;

select count(sage) from student;

3.5數據分組：

在SQL的語法裡，GROUP BY和HAVING子句用來對數據進行匯總。GROUP BY子句指明了按照哪幾個字段來分組，而將記錄分組後，用HAVING子句過濾這些記錄。

1.語法：

#以column1 為一組計算 column2 的平均值


SELECT column1, column2. column3.. FROM table group by column1

使用having 子句過濾:


SELECT column1, column2. column3..FROM  table group by column1 having ...

注意：Having和where均可實現過濾，但在having可以使用合計函數,having通常跟在group by後，它作用於組。

2. 簡單演示:

-- 數據分組 group by--

select mark,count(*) as num from student group by mark;

-- 過濾分組having num>2--  

select mark,count(*)as num from student where sid>1 group by mark having num>2;

-- 注意having有where的功能，但where不可以替代having，where用於過濾結果，having用於過濾分組結果--

3.6子句順序：

– select子句順序–

– select–from–where–group by–having–order by–limit–

4.多表查詢：

多表查詢規則：1）確定查詢哪些表 2）確定哪些哪些字段 3）表與表之間連接條件 (規律：連接條件數量是表數量-1)

4.1子查詢：

子查詢可以理解為 套查詢.子查詢是一個Select語句.

1.分為三種方式：

表達式的值與子查詢返回的單一值做比較
表達式 comparision ANY|ALL|SOME 檢查表達式的值是否匹配子查詢返回的一組值的某個值
[NOT]IN(子查詢) 做為計算字段使用子查詢

2.演示如下：

##使用子查詢

###1. 使用表達式的值與子查詢返回的單一值做比較

--主查詢返回單價比任何一個折扣大於等於25%的產品的單價要高的所有產品 

Select * FROM Products Where UnitPrice 〉ANY (Select UnitPrice FROM[Order Details] Where Discount〉0.25) 

###2. 檢查表達式的值是否匹配子查詢返回的一組值的某個值 

-- 1檢查detil的所有goods編號

-- 2檢查具有前一步驟列出的編號的所有goods商品 

select * from goods where goods_id  in (select goods from detil);

###3.做為計算字段使用子查詢 

-- 1從goods檢索商品列表 

-- 2對於檢索的每個商品id，統計其出現在detil的次數： 

select *,(select count(*) from detil where detil.goods=goods_id) as id_count  from goods; #必須寫全表名，防止混淆 

4.2內連接查詢：

通過連接運算符可以實現多個表查詢。連接是關系數據庫模型的主要特點，連接操作給用戶帶來很大的靈活性，他們可以在任何時候增加新的數據類型。為不同實體創建新的表，爾後通過連接進行查詢。
- 1.定義：

內連接（inner join或者join，也可以使用逗號）只返回兩個表中連接字段相等的行；

##內連接連接二表的例子：

select * from 表1 inner join 表2 on 表1.字段號=表2.字段號

或：select * from 表1 ，表2 where 表1.字段號=表2.字段號

##內連接連接三表的例子：
select * from (表1 inner join 表2 on 表1.字段號=表2.字段號) inner join 表3 on 表1.字段號=表3.字段號
##內連接四表的例子：
select * from ((表1 inner join 表2 on 表1.字段號=表2.字段號）inner join 表3 on表1.字段號=表3.字段號）inner join 表4 on 表1.字段號=表4.字段號

2.演示如下：

-- 使用內連接 

-- 1 列出goods_name，goods_price客戶夠買的數量 ,使用逗號寫

select detil.customer_id as id,goods.goods_name as g_name,goods.gooods_price as price,detil.count as count,(goods.gooods_price * detil.count) as expensive

    from goods,detil

    where goods_id=detil.goods order by id;#這種通過測量相等的為內部聯接；  

-- 2.使用內部方式寫 

select detil.customer_id as id,goods.goods_name as g_name,goods.gooods_price as price,detil.count as count,(goods.gooods_price * detil.count) as expensive

     from goods inner join detil

     on goods_id=detil.goods 

     order by id;

---- 自聯接，與多表內連接一樣

-- 1.先找出有問題的餅干4對應的商品id

-- 2.通過商品id找到對應id生產的所有商品； 

select p1.goods_name,p1.goods_maker from goods as p1,goods as p2

       where p1.goods_id=p2.goods_id and p2.goods_name='餅干4'; 

4.3外接查詢：

MySQL中的外連接，分為左外連接和右連接，即除了返回符合連接條件的結果之外，還要返回左表(左連接)或者右表(右連接)中不符合連接條件的結果，相對應的使用NULL對應。

1.左右連接:

左連接（left join）返回左表中所有記錄和右表中連接字段相等的記錄

右連接（right join）返回右表中所有記錄和左表中連接字段相等的記錄

如果兩個表中字段並不完全一一對應，想要那些沒有對應的字段也顯示出來就可以使用左連接和右連接查詢，一個是包括左邊所有，一個是包括右邊；

##左連接兩表的例子：
select * from 表1 left join 表2 on 表.字段號=表2.字段號;
##左連接三表查詢的例子：
select * from （表1 left join 表2 on 表1.字段號=表2.字段號） left join 表3 on
表2.字段號=表3.字段號

##右連接兩表的例子：
select * from 表1 right join 表2 on 表.字段號=表2.字段號;

##右連接三表查詢的例子：

select * from （表1 right join 表2 on 表1.字段號=表2.字段號）right join 表3 on

表2.字段號=表3.字段號

2.演示如下：

-- 外部連接： 將沒有關聯的數據頁查詢出來；left查詢出左邊表（goods）所有的，right將右邊表的所有查詢； 

select detil.customer_id as id,goods.goods_name as g_name,goods.gooods_price as price,detil.count as count,(goods.gooods_price * detil.count) as expensive

     from goods left outer join detil

     on goods_id=detil.goods 

     order by id;

4.4組合（聯合）查詢：

1.定義：

把兩次或多次的查詢結果合並起來，要求查詢的列數一致，推薦查詢的對應的列類型一致，可以查詢多張表，多次查詢語句時如果列名不一樣，則取第一次的列名！如果不同的語句中取出的行的每個列的值都一樣，那麼結果將自動會去重復，如果不想去重復則要加all來聲明，即union all。

簡單理解：union 跟where的多個or條件一樣；

語法：

##同一張表：

select 字段1，字段2  from table1 where 條件 union select 字段1，字段2 from table1 where 條件

##不同的表：要求字段1，2和字段3,4類型一致

select 字段1，字段2  from table1 where 條件 union select 字段3，字段4 from table2 where 條件

2.演示如下：

##組合查詢 

select goods_id,goods_name from goods where goods_id>2

union

select goods_id,goods_name from goods where goods_name in ('餅干1','餅干3')

order by goods_id;

4.5 笛卡爾積(交叉連接)查詢：

1.定義：

笛卡爾積返回的結果為被連接的兩個數據表的乘積:

假設現在有兩張表A和B：如下

A表：

B表：

則查詢的笛卡爾積如下：select * from A,B;

2.語法：

##在MySQL中可以為CROSS JOIN或者省略CROSS即JOIN，或者使用','  如： 

SELECT * FROM table1 CROSS JOIN table2   

SELECT * FROM table1 JOIN table2   

SELECT * FROM table1,table2