mysql入門筆記2

標籤：

模糊查詢 like

用%代替任一字元

Where goods_name like ‘諾基亞%’;

明確幾個字元就底線
底線統配單個字元，%統配任意

習題

把一堆10幾，20幾的30幾的數變成10,20,30
也就是去掉個位元

查詢出來 select出來

用到了mysql的函數

Floor 返回不大於x的最大整數

先讓一堆數除10 ，然後floor 就是一個數了，也就取到了那個十位元，再乘10

就變成了10,20,30

也就是說可以這樣更新資料~~~

Set的num =那個參與計算的值

2

把字串 諾基亞改為 HTC

SUBSTRING

同樣去那個mysql的文檔去查對應的函數

第12課 奇怪的NULL

建立表

mysql> create table tmp(
-> id int,
-> name varchar(20)
-> )charset utf8 engine myisam;

寫進去

mysql> insert into tmp
-> values
-> (1,’lili’),
-> (2,NULL);

定義了NULL
後
去查詢，where name= NULL
結果沒有，，，查詢不等於NULL，也是沒有

mysql> select * from tmp;
+——+——+
| id | name |
+——+——+
| 1 | lili |
| 2 | NULL |
+——+——+
2 rows in set (0.00 sec)

mysql> select * from tmp where name=NULL;
Empty set (0.00 sec)

mysql> select * from tmp where name!=NULL;
Empty set (0.00 sec)

NULL就是沒有的意思，沒法去比，也就是後面的where比較語句，不行

有專門查NULL的

Where name is NULL

第13課，group分組與統計函數

Max() 最大
Count() 求行數
Avg()
Min()
Sum() 求總和

以上是統計函數

其中
Avg是平均值

mysql> select avg(shop_price) from goods;
+—————–+
| avg(shop_price) |
+—————–+
| 1232.526774 |
+—————–+
1 row in set (0.00 sec)

取行數也就是有多少個

mysql> select count(shop_price) from goods
+——————-+
| count(shop_price) |
+——————-+
| 31 |
+——————-+
1 row in set (0.00 sec)

mysql> select count(*) from goods;
+———-+
| count(*) |
+———-+
| 31 |
+———-+
1 row in set (0.00 sec)

mysql> select count(1) from goods;
+———-+
| count(1) |
+———-+
| 31 |
+———-+
1 row in set (0.00 sec)

以上是簡單的統計

下面是分組統計

既然是分組統計，那麼就有一個按什麼統計，比如說是按小標題，那麼就要先給這小標題先拍個序，然後在算，所以用group還是有點耗費資源的，
也有可能利用索引

命令： from user group by 按的啥分組

記得是group by

先按如 cat_id

mysql> select cat_id , avg(shop_price) from goods group by cat_id;
+——–+—————–+
| cat_id | avg(shop_price) |
+——–+—————–+
| 2 | 823.330000 |
| 3 | 1746.066667 |
| 4 | 2297.000000 |
| 5 | 3700.000000 |
| 8 | 75.333333 |
| 11 | 31.000000 |
| 13 | 33.500000 |
| 14 | 54.000000 |
| 15 | 70.000000 |
+——–+—————–+
9 rows in set (0.00 sec)

每個欄目下剩的庫存量 用的count
mysql> select cat_id , count(*) from goods group by cat_id;
+——–+———-+
| cat_id | count(*) |
+——–+———-+
| 2 | 1 |
| 3 | 15 |
| 4 | 3 |
| 5 | 1 |
| 8 | 3 |
| 11 | 2 |
| 13 | 2 |
| 14 | 2 |
| 15 | 2 |
+——–+———-+
9 rows in set (0.00 sec)

就是按了那個分組！！！！！！！！！！！！！

14課 having篩選

就是說，如果把用計算的列了出來，後面的條件又是這個的話，就要計算兩次，所以可以向語言那樣進行變數賦值，把這個計算的賦值給另一個變數~~~

就是

Selecct goods_name , (market_price-shop_price) as sheng from goods having sheng >200;

正常的查詢，就是先看後面的條件，先把磁碟中的資料按著條件取出來放在臨時的儲存中。。。。。

也就是說提出來的資料中的有那些運算的，但是磁碟的真實資料中是沒有的。

然後想在這個提出來的暫存資料表的基礎上再進行資料的篩選就要用having了~~~

Where是針對磁碟的，having是針對取出來的暫存資料表

就是說先
Selecct goods_name , (market_price-shop_price) as sheng from goods where 1

把這些都去取出來，然後在對取出來的臨時資料表進行操作就用到having了。

Having sheng >200;

和上面的那個直接用having的結果是一樣的。

Cout是數的行數 ，score<60，無論什麼，一定會有結果，0或1，那麼用count的話就會去不管0或1都去數行了。應該用sum！！！！求那個和，然後用group by name 分組，
用where取出來，在用having在取 sum那個大於2的

mysql> select name,subject,sum(score<60) as gk, avg(score) from result group by
name ;
+——+———+——+————+
| name | subject | gk | avg(score) |
+——+———+——+————+
| 張三 | 數學 | 2 | 60.0000 |
| 李四 | 語文 | 2 | 50.0000 |
| 王五 | 政治 | 1 | 30.0000 |
+——+———+——+————+
3 rows in set (0.00 sec)

mysql> select name,subject,sum(score<60) as gk, avg(score) from result group by
name ;
+——+———+——+————+
| name | subject | gk | avg(score) |
+——+———+——+————+
| 張三 | 數學 | 2 | 60.0000 |
| 李四 | 語文 | 2 | 50.0000 |
| 王五 | 政治 | 1 | 30.0000 |
+——+———+——+————+
3 rows in set (0.00 sec)

mysql> select name,subject,sum(score<60) as gk, avg(score) from result group by
name
-> having gk>=2;
+——+———+——+————+
| name | subject | gk | avg(score) |
+——+———+——+————+
| 張三 | 數學 | 2 | 60.0000 |
| 李四 | 語文 | 2 | 50.0000 |
+——+———+——+————+
2 rows in set (0.00 sec)

mysql>

15、order by 排序

就是order by 後面加排序的條件，以什麼排序！
很簡單額
如
Select goods_id goods_name from goods order by shop_price;

就是以商品價格的去排序，如果在磁碟上是沒有排序的，那麼就要在記憶體中排序

但是吧有正序，倒敘，等

預設是升序 asc

直接後面加desc就變成了降序了

select goods_id,goods_name,shop_price from goods order by shop_price;

但是可以能會有，就是先按cat_id 排序，然後發現商品的價格是亂的，那麼讓價格也是
排序的。。

第一個欄位沒有比較完，直接在後面加個逗號加比較的欄位。

並且可以第一個比較是升序，第二個是降序也可以。

mysql> select goods_id,goods_name,shop_price from goods order by shop_price desc
,shop_price desc;
+———-+——————————+————+
| goods_id | goods_name | shop_price |
+———-+——————————+————+
| 22 | 多普達touch hd | 5999.00 |
| 23 | 諾基亞n96 | 3700.00 |
| 32 | 諾基亞n85 | 3010.00 |
| 18 | 夏新t5 | 2878.00 |

還可以繼續去加

也就是分列排序。

17課 limit 限制取出條目

如，取出價格前三高，

選擇性的拿出幾條，先排序，然後就可以跳過幾個還可以直接取

有，兩個參數 ，1 位移量 就是跳過幾個

2取幾行。

mysql> select goods_id,goods_name,shop_price from goods order by shop_price limit 0,3;
+———-+———————–+————+
| goods_id | goods_name | shop_price |
+———-+———————–+————+
| 30 | 移動20元儲值卡 | 18.00 |
| 26 | 小靈通/固話20元儲值卡 | 19.00 |
| 5 | 索愛原裝m2卡讀卡機 | 20.00 |
+———-+———————–+————+
3 rows in set (0.00 sec)

mysql> select goods_id,goods_name,shop_price from goods order by shop_price asc limit 0,3;
+———-+———————–+————+
| goods_id | goods_name | shop_price |
+———-+———————–+————+
| 30 | 移動20元儲值卡 | 18.00 |
| 26 | 小靈通/固話20元儲值卡 | 19.00 |
| 5 | 索愛原裝m2卡讀卡機 | 20.00 |
+———-+———————–+————+
3 rows in set (0.00 sec)

mysql> select goods_id,goods_name,shop_price from goods order by shop_price desc limit 0,3;
+———-+—————-+————+
| goods_id | goods_name | shop_price |
+———-+—————-+————+
| 22 | 多普達touch hd | 5999.00 |
| 23 | 諾基亞n96 | 3700.00 |
| 32 | 諾基亞n85 | 3010.00 |
+———-+—————-+————+
3 rows in set (0.00 sec)

記住兩個參數，一個位移量，一個取出條目數

取出最新的

就是先按goods_id 倒敘，然後0，1 就是直接取出了最新的漏洞e

在oracle中沒有
在分頁中發揮作用

18、子句的查詢陷阱

查詢每個欄目下最大最新的產品。Goods_id 作為最大最新

綜合運用

每個欄目下的最新的就是id最大的。

這5種子句是有順序也就是我們現在學習的順序。

子查詢了就~~~

19課 where型子查詢

內層的查詢結果作為外面查詢的條件

就是上面的題，不用排序和limist

假如只取直接就最大的話，不是按cat_id分的話，

可以 select goods_id ,goods_name from goods where =32;

但是不知道最大的是32，所以
可以
先查 select max(goods_id) from goods;
返回了最大的，然後在就可以了。

那麼讓返回的這個最大值給那麼32的位置

子查詢 ：

Select goods_id ,goods_name from goods where goods_id =

(select max(goods_d) from goods);

mysql> select goods_id goods_name from goods where goods_id = (select max(goods_id) from goods);
+————+
| goods_name |
+————+
| 32 |
+————+
1 row in set (0.03 sec)

解決上面的問題，就是先查出每個標題下最大的也就是

Select max(goods_id) from goods group by cat_id ;
返回每個標題下最大的goods_Id；
那麼在此基礎上查詢他們的資訊就行了但是他們沒有規律
那麼用in語句去查

就是

Select goods_id ,goods_name where goods_id in
(select max(goods_id) from goods group by cat_id);

不用group by的話就是整個的最大的goods_id ，用了group by來分組

就在每個標題下的最大的goods_id ~~~~

mysql> select goods_id,goods_name from goods where goods_id in
-> (select max(goods_id) from goods group by cat_id )
-> ;
+———-+——————————+
| goods_id | goods_name |
+———-+——————————+
| 6 | 勝創kingmax記憶卡 |
| 7 | 諾基亞n85原裝立體聲耳機hs-82 |
| 16 | 恒基偉業g101 |
| 18 | 夏新t5 |
| 23 | 諾基亞n96 |
| 26 | 小靈通/固話20元儲值卡 |
| 28 | 聯通50元儲值卡 |
| 30 | 移動20元儲值卡 |
| 32 | 諾基亞n85 |
+———-+——————————+
9 rows in set (0.00 sec)

如果不用子查詢返回的序號和名字會亂

20課 from型子查詢

正常的查詢後，會返回一個列表，也就是一個表，然後可以
在用from來對這個表進行查詢

就是把第一次的查詢的結果是個列表 as tmp 作為一個暫存資料表，然後

From 這個暫存資料表，後面還可以正常的加條件，就是單純的在臨時的表上進行查詢

As 就是起名

Select goods_id,goods_name from goods where goods_id<25 ;

Select goods_id ,goods_name from ( Select goods_id,goods_name from goods where goods_id<25 ) as tmp order by goods_id;

21課 exists 子查詢

查出所有有商品的欄目，

mysql> select * from category where exists(select * from goods where goods.cat_i
d = category.cat_id);
+——–+——————-+———–+
| cat_id | cat_name | parent_id |
+——–+——————-+———–+
| 2 | CDMA手機 | 1 |
| 3 | GSM手機 | 1 |
| 4 | 3G手機 | 1 |
| 5 | 雙模手機 | 1 |
| 8 | 耳機 | 6 |
| 11 | 讀卡機和記憶卡 | 6 |
| 13 | 小靈通/固話儲值卡 | 12 |
| 14 | 移動手機儲值卡 | 12 |
| 15 | 聯通手機儲值卡 | 12 |
+——–+——————-+———–+
9 rows in set (0.00 sec)

兩個表之間的的查詢，對應的讓他們顯示都存在的cat_id 號顯示出來。

Esists 存在的意思。

22課，新手1+N模式查詢

查詢價格大於2000元的商品及欄目名稱。

一個goods表的話，根本查不出來欄目名稱的，但是有欄目的id ，

先查出來商品價格大於2000的，包含欄目id的，然後查出N條資料，由這N條資料
可以再次去另一個表中進行查詢。代碼實現。

這樣很麻煩，是新手嘛，然後就可以引出串連查詢

23課 內串連查詢

刪除表，，，， drop table 表的名字； 就可以刪除了

truncate 和 delete 只刪除資料不刪除表的結構(定義)

速度，一般來說: drop> truncate > delete

查詢時兩張表串連是 inner join 後面 on 是條件 串連規則

想刪除部分資料行用 delete，注意帶上where子句. 復原段要足夠大.
想刪除表,當然用 drop
想保留表而將所有資料刪除，如果和事務無關，用truncate即可

http://www.cnblogs.com/8765h/archive/2011/11/25/2374167.html

SQL truncate 、delete與drop區別
相同點：
1.truncate和不帶where子句的delete、以及drop都會刪除表內的資料。

2.drop、truncate都是DDL語句(資料定義語言 (Data Definition Language)),執行後會自動認可。

不同點：
1. truncate 和 delete 只刪除資料不刪除表的結構(定義)
drop 語句將刪除表的結構被依賴的約束(constrain)、觸發器(trigger)、索引(index)；依賴於該表的預存程序/函數將保留,但是變為 invalid 狀態。

1. delete 語句是資料庫操作語言(dml)，這個操作會放到 rollback segement 中，事務提交之後才生效；如果有相應的 trigger，執行的時候將被觸發。
   truncate、drop 是資料庫定義語言(ddl)，操作立即生效，原資料不放到 rollback segment 中，不能復原，操作不觸發 trigger。

3.delete 語句不影響表所佔用的 extent，高水線(high watermark)保持原位置不動
drop 語句將表所佔用的空間全部釋放。
truncate 語句預設情況下見空間釋放到 minextents個 extent，除非使用reuse storage；truncate 會將高水線複位(回到最開始)。

4.速度，一般來說: drop> truncate > delete

5.安全性：小心使用 drop 和 truncate，尤其沒有備份的時候.否則哭都來不及
使用上,想刪除部分資料行用 delete，注意帶上where子句. 復原段要足夠大.
想刪除表,當然用 drop
想保留表而將所有資料刪除，如果和事務無關，用truncate即可。如果和事務有關,或者想觸發trigger,還是用delete。
如果是整理表內部的片段，可以用truncate跟上reuse stroage，再重新匯入/插入資料。

6.delete是DML語句,不會自動認可。drop/truncate都是DDL語句,執行後會自動認可。

7、TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和交易記錄資源少。DELETE 語句每次刪除一行，並在交易記錄中為所刪除的每行記錄一項。TRUNCATE TABLE 通過釋放儲存表資料所用的資料頁來刪除資料，並且只在交易記錄中記錄頁的釋放。

8、TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數值重設為該列的種子。如果想保留標識計數值，請改用 DELETE。如果要刪除表定義及其資料，請使用 DROP TABLE 語句。

9、對於由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由於 TRUNCATE TABLE 不記錄在日誌中，所以它不能啟用觸發器。

10、TRUNCATE TABLE 不能用於參與了索引檢視表的表。
…………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………….

| hid | bname |
+——+——–+
| A | 屌絲 |
| B | 楊過 |
| C | 陳冠希 |
+——+——–+
3 rows in set (0.00 sec)

mysql> select * from girl;
+——+——–+
| hid | gname |
+——+——–+
| B | 小龍女 |
| C | 張柏芝 |
| D | 死宅女 |
+——+——–+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> select boy.hid , boy.bname ,girl.hid ,girl.gname
-> from
-> boy inner join girl
-> on boy.hid = girl.hid
-> ;
+——+——–+——+——–+
| hid | bname | hid | gname |
+——+——–+——+——–+
| B | 楊過 | B | 小龍女 |
| C | 陳冠希 | C | 張柏芝 |
+——+——–+——+——–+
2 rows in set (0.04 sec)

也就是 select 表.什麼什麼 另一個表.

From 表1 inner join 表2
On 串連的條件

24課 左右串連查詢

之前那麼查，左面的boy 沒有把，因為沒有對應的，
那麼現在讓他對應NULL

如左串連，就是以左邊的為主導

mysql> select boy.hid , boy.bname ,girl.hid ,girl.gname
-> from
-> boy left join girl
-> on boy.hid = girl.hid;
+——+——–+——+——–+
| hid | bname | hid | gname |
+——+——–+——+——–+
| A | 屌絲 | NULL | NULL |
| B | 楊過 | B | 小龍女 |
| C | 陳冠希 | C | 張柏芝 |
+——+——–+——+——–+
3 rows in set (0.00 sec)

就是先把左面的表全找出來，然後以左表資料為準，
查不到的寫NULL

右串連

就是把left改為 right

mysql> select boy.hid , boy.bname ,girl.hid ,girl.gname
-> from
-> boy right join girl
-> on boy.hid = girl.hid;
+——+——–+——+——–+
| hid | bname | hid | gname |
+——+——–+——+——–+
| B | 楊過 | B | 小龍女 |
| C | 陳冠希 | C | 張柏芝 |
| NULL | NULL | D | 死宅女 |
+——+——–+——+——–+
3 rows in set (0.00 sec)

Inner就是內串連 他們倆的交集
Left就是左面的加上交集。

Mysql不支援外串連也就是所有的並集。

例如

mysql> select goods_id,goods_name,shop_price,cat_name
-> from
-> goods left join category
-> on goods.cat_id = category.cat_id;

寫表.是因為不同的表中有相同的欄位，為了區別

mysql> select goods_id,cat_name,shop_price
-> from
-> goods left join category
-> on goods.cat_id = category.cat_id
-> where goods.cat_id = 4;
+———-+———-+————+
| goods_id | cat_name | shop_price |
+———-+———-+————+
| 1 | 3G手機 | 1388.00 |
| 14 | 3G手機 | 2625.00 |
| 18 | 3G手機 | 2878.00 |
+———-+———-+————+
3 rows in set (0.00 sec)

Select m.*,t1.tname as htame,t2.tame as gteam
From
M inner join t as t1 on m.hid = t1.tid inner join t as t2 on m.gid = t2.tid

還有 輸入 \c後就讓前面的語句沒用了，有時候輸入錯誤了，斷行符號，輸入\c 就可以結束
這個語句的書寫

mysql> select m.*,t1.tname as htname ,t2.tname as gtname
-> from
-> m inner join t as t1 on m.hid = t1.tid inner join t as t2 on m.gid = t
id;
+——+——+——+——+————+———-+———-+
| mid | hid | gid | mres | matime | htname | gtname |
+——+——+——+——+————+———-+———-+
| 1 | 1 | 2 | 2:0 | 2006-05-21 | 國安 | 申花 |
| 2 | 2 | 3 | 1:2 | 2006-06-21 | 申花 | 布爾聯隊 |
| 3 | 3 | 1 | 2:5 | 2006-06-25 | 布爾聯隊 | 國安 |
| 4 | 2 | 1 | 3:2 | 2006-07-21 | 申花 | 國安 |
+——+——+——+——+————+———-+———-+
4 rows in set (0.00 sec)

這個T表連了兩次，連了兩次就要起別名

mysql入門筆記2