mysql-day02筆記

1、把查詢結果去除重復記錄

去重需要使用一個關鍵字：distinct

	select distinct (欄位名) from (表名);

注意：

原表資料不會被修改，只是查詢結果去重，
distinct只能出現在所有欄位的最前方，

distinct出現在兩個欄位之前，則表示兩個欄位聯合起來去重，

	select distinct (欄位1),(欄位2) from (表名);

2、連接查詢

2.1、什么是連接查詢？

從一張表中單獨查詢，稱為單表查詢，
表1和表2聯合起來查詢資料，從表1中取員工名字，從表2中取部門名字，
這種跨表查詢，多張表聯合起來查詢資料，被稱為連接查詢，

2.2、連接查詢的分類

根據語法的年代分類：

SQL92：1992年的時候出現的語法
SQL99：1999年的時候出現的語法

現在用的多數都是SQL99語法

根據表連接的方式分類：

內連接：
等值連接
非等值連接
自連接

外連接：
左外連接（左連接）
右外連接（右連接）

全連接

2.3、笛卡爾積現象

當兩張表進行連接查詢時，沒有任何條件的限制，如下：

	select (欄位1),(欄位2) from (表1),(表2);

以上查詢陳述句會導致出現"笛卡爾積現象"

當兩張表進行連接查詢，沒有任何條件限制的時候，最終查詢結果條數，是
兩張表條數的乘積，這種現象被稱為：笛卡爾積現象，（笛卡爾發現的，這是
一個數學現象，）

2.4、避免笛卡爾積現象

連接時加條件，滿足這個條件的記錄被篩選出來！

select 表1.(欄位1),表2.(欄位2)
from (表1),(表2)
where 表1.(部門表) = 表2.(部門表);

以上的連接條件是 表1 跟 表2 的部門相同，獲取兩表同一部門的欄位資料，可以避免出現笛卡爾積現象

最終查詢的結果條數是正確的條數，但是匹配的程序中，匹配的次數并沒有減少，還是跟沒有條件
的比較的次數一樣，只不過進行了四選一進行了篩選，匹配次數并沒有減少，

注意：

通過笛卡爾積現象得出，表的連接次數越多效率越低，盡量避免表的
連接次數，

2.5、內連接之等值連接

例：查詢某公司的員工名稱與部門名稱

SQL92語法：

	select 
		e.(員工名稱),d.(部門名稱)
	from 
		(員工所在部門表) e,(部門表) d
	where 
		e.(員工部門) = d.(部門); // 條件是等量關系，所以被稱為等值連接，

注意：取別名很重要，增加效率
sql92的缺點：

結構不清晰，表的連接條件，和后期進一步篩選的條件，都放到了where后面，

SQL99語法：

	select 
		e.(員工名稱),d.(部門名稱)
	from
		(員工所在部門表) e
	inner join
		(部門表) d
	on
		e.(員工部門) = d.(部門表); // 條件是等量關系，所以被稱為等值連接，

inner關鍵字可以省略（帶著inner可讀性會更好，一眼就能看出來是內連接）
sql99優點：

表連接的條件是獨立的，連接之后，如果還需要進一步篩選，再往后繼續添加where

SQL99語法：

	select 
	...
	from
	a
	join
	b
	on
	a和b的連接條件
	where
	篩選條件

2.6、內連接之非等值連接

例：找出每個員工的薪資等級，要求顯示員工名、薪資、薪資等級？

	select 
		e.(工名),e.(薪資),s.(薪資等級)
	from 
		(員工表) e
	inner join 
		(薪資等級表) s
	on 
		e.(員工薪資) between s.(最低薪資) and s.(最高薪資);
		// 條件不是一個等量關系，稱為非等值連接，

2.7、內連接之自連接

例：查詢員工的上級領導，要求顯示員工名和對應的領導名？
自連接：

把一張表看作兩張表來查詢

員工表李包含了 領導的員工編號 與 每個員工的 上級領導編號
把一張員工表看作兩張表，使用別名區分
第一張：(員工表) a 員工
第二章：(員工表) b 領導表

	select
		a.(名稱) as '員工名',b.(名稱) as '領導名';
	from 
		(員工表) a
	inner join 
		(員工表) b
	on 
		a.(員工領導編號) = b.(領導的員工編號);

重點技巧：

一張表看做兩張表，

2.8、外連接

外連接（右外連接）：

	select
		e.(名稱),d.(名稱)
	from 
		(員工表) e
	right outer join  // outer 是可以省略的，帶著可讀性強，
		(部門表) d
	on 
		e.(員工表部門) = d.(部門表部門);

right代表什么：

表示將join關鍵字右邊的這張表看成主表，主要是為了將
這張表的資料全部查詢出來，捎帶著關聯查詢左邊的表，
在外連接當中，兩張表連接，產生了主次關系，

外連接（左外連接）：

	select
		e.(名稱),d.(名稱)
	from 
		(員工表) e
	left outer join  // outer 是可以省略的，帶著可讀性強，
		(部門表) d
	on 
		e.(員工表部門) = d.(部門表部門);

總結：

帶有right的是右外連接，又叫做右連接，
帶有left的是左外連接，又叫做左連接，
任何一個右連接都有左連接的寫法，
任何一個左連接都有右連接的寫法，

2.9、三，四張表連接

語法：

		select 
			...
		from
			a
		join
			b
		on
			a和b的連接條件
		join
			c
		on
			a和c的連接條件
		right join
			d
		on
			a和d的連接條件

一條SQL中內連接和外連接可以混合，都可以出現！

3、子查詢

3.1、什么是子查詢

select陳述句中嵌套select陳述句，被嵌套的select陳述句稱為子查詢，

3.2、子查詢都可以出現在哪里

	select
		..(select).
	from
		..(select).
	where
		..(select).

可以出現在 select 后面
可以出現在 from 后面
可以出現在 where 后面

3.3、where 子句中的子查詢

例：找出比最低工資高的員工姓名和工資？以下錯誤案例：

		select 
			(姓名),(工資)
		from
			（員工表) 
		where
			(工資) > min(工資);

以上是錯誤的，因為在 where 子句中不能直接使用分組函式，可以使用子查詢

先分析題目：
	第一步：查詢最低工資是多少
	select min(工資) from (表);
	第二步：找出>最低工資的
	select (姓名),(工資) from (員工表) where (工資) > (第一步的結果)//最低工資
	第三步：合并
	select 
		(姓名),(工資) 
	from 
		(員工表) 
		where 
			(工資) > (select min(工資) from (員工表)); // > 最低工資

3.4、from 子句中的子查詢

注意：

from后面的子查詢，可以將子查詢的查詢結果當做一張臨時表，（技巧）

例：找出每個崗位的平均工資的薪資等級，

	第一步：找出每個崗位的平均工資（按照崗位分組求平均值）
	select 
		(崗位),avg((工資)) 
	from 
		(員工表) 
	group by 
		(崗位); //按照崗位分組
		
	第二步：把以上的查詢結果就當做一張真實存在的表t，
	```SQL
		select * from salgrade;//工資等級 s表

第三步：t表和s表進行表連接，
條件：t表avg(工資) between s.(最低工資) and s.(最高工資);

select
	t.*,s.(等級)
from 
	(select (崗位),avg(工資) as avgsal from (員工表) group by (崗位)) t	// 別名 t
join 
	(工資等級表) s
on 
	t.avgasl between s.(最低工資) and s.(最高工資);

#### 3.5、select 后面出現的子查詢（了解）
例：找出每個員工員工名稱，部門編號，部門名稱
```SQL
	select 
		e.(員工姓名),e.(部門編號),
		(select d.(部門名稱) from (部門表) d 
		where e.部門編號 = d.(部門編號)) as dname
	from 
		(員工表) e;

以上 select 出現子查詢查出了 部門名稱
以下錯誤案例：

	select 
		e.(員工姓名),e.(部門編號),
		(select (部門名稱) from (部門表)) as dname
	from 
		(員工表) e;

注意：

對于select后面的子查詢來說，這個子查詢只能一次回傳1條結果，
多于1條，就會報錯

4，union 合并查詢結果集

例：查詢作業崗位是MANAGER和SALESMAN的員工
平常寫法：

	select 
		(姓名),(崗位) 
	from 
		(員工表) 
	where 
		(崗位) = 'MANAGER' or (崗位) = 'SALESMAN';
	//或者
	select 
		(姓名),(崗位) 
	from 
		(員工表)  
	where 
		(崗位) in('MANAGER','SALESMAN');

使用 union 合并查詢

	select (姓名),(崗位) from (員工表) where (崗位) = 'MANAGER'
    union
    select (姓名),(崗位) from (員工表) where (崗位) = 'SALESMAN';

相比較：

union的效率要高一些，對于表連接來說，每連接一次新 表，
則匹配的次數滿足笛卡爾積，成倍的翻，，，
但是union可以減少匹配的次數，在減少匹配次數的情況下，
還可以完成兩個結果集的拼接，

	匹配次數：
          a 連接 b 連接 c
          a 10條記錄
          b 10條記錄
          c 10條記錄
          匹配次數是：1000

          a 連接 b一個結果：10 * 10 --> 100次
          a 連接 c一個結果：10 * 10 --> 100次
          使用union的話是：100次 + 100次 = 200次，
          （union把乘法變成了加法運算）

union注意事項：
1、union在進行結果集合并的時候，要求兩個結果集的列數相同，以下錯誤案例：

	select (姓名),(崗位) from (員工表) where (崗位) = 'MANAGER'
    union
    select (姓名) from (員工表) where (崗位) = 'SALESMAN';

2、結果集合并時列和列的資料型別也要一致，

在MySQL里面可以，oracle 語法比較嚴格，不可以

5、limit(重要)

5.1、limit作用

作用：

將查詢結果集的一部分取出來，通常使用在分頁查詢當中，
例如：
百度默認：一頁顯示10條記錄，

分頁的作用：

為了提高用戶的體驗，因為一次全部都查出來，用戶體驗差，
分頁可以一頁一頁翻頁看，(類似百度的頁數)

5.2、limit的用法

完整用法：

	limit startIndex, length
	//startIndex是起始下標，length是長度，
	//起始下標從0開始，

預設用法：

	limit 5; //取出前5個值

例：按照薪資降序，取出排名在前5名的員工

	select
		(姓名),(工資)
	from 
		(員工表)
	order by	//工資排序
		(工資) desc
	limit 5;	//取出前5個

以下的結果相同：

	select
		(姓名),(工資)
	from 
		(員工表)
	order by	//工資排序
		(工資) desc
	limit 0,5;	//取出0到5個

5.3、注意：mysql當中limit在order by之后執行

5.4、取出工資排名在[3-5]名的員工

	select
		(姓名),(工資)
	from 
		(員工表)
	order by	//工資排序
		(工資) desc
	limit 
		2,3;	//取出3到5個

注意：

2表示起始位置從下標2開始，就是第三條記錄，
3表示長度，

5.5、分頁

每頁顯示3條記錄：

第1頁：limit 0,3 [0 1 2]
第2頁：limit 3,3 [3 4 5]
第3頁：limit 6,3 [6 7 8]
第4頁：limit 9,3 [9 10 11]

記分頁的公式：

	limit ((第幾頁)-1) * (每頁顯示的條數) , (每頁顯示的條數)

6、關于DQL陳述句的大總結：

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

執行順序：

1、from
2、where
3、group by
4、having
5、select
6、order by
7、limit…

7、表的創建(建表)

7.1、建表的語法格式：(建表屬于DDL陳述句，DDL包括：create drop alter)

語法：

create table ((表名))((欄位名1) 資料型別,(欄位名2) 資料型別,(欄位名3) 資料型別)

可以這樣寫：

create table 表名(
		欄位名1 資料型別, 
		欄位名2 資料型別, 
		欄位名3 資料型別
	);

命名方式：

表名：建議以t_ 或者 tbl_開始，可讀性強，見名知意，
欄位名：見名知意，
表名和欄位名都屬于識別符號，

7.2、mysql中的資料型別

常見的資料型別：

1、varchar(最長255)
2、char(最長255)
3、int(最長11)
4、bigint
5、float
6、double
7、date
8、datetime
9、clob
10、blob

說明：
varchar(最長255)：

可變長度的字串
比較智能，節省空間，
會根據實際的資料長度動態分配空間，

優點：節省空間
缺點：需要動態分配空間，速度慢，

char(最長255)：

定長字串
不管實際的資料長度是多少，
分配固定長度的空間去存盤資料，
使用不恰當的時候，可能會導致空間的浪費，

優點：不需要動態分配空間，速度快，
缺點：使用不當可能會導致空間的浪費，

varchar和char怎么選擇：

假如儲存性別使用char，因為性別是固定長度的字串，所以選擇char，
假如儲存姓名使用varchar，每一個人的名字長度不同，所以選擇varchar，

int(最長11)：

數字中的整數型，等同于java的int，

bigint：

數字中的長整型，等同于java中的long，

float：

單精度浮點型資料

double：

雙精度浮點型資料

date：

短日期型別

datetime:

長日期型別

clob：

字符大物件
最多可以存盤4G的字串，
比如：存盤一篇文章，存盤一個說明，
超過255個字符的都要采用CLOB字符大物件來存盤，
Character Large OBject:CLOB

blob：

二進制大物件
Binary Large OBject
專門用來存盤圖片、聲音、視頻等流媒體資料，
往BLOB型別的欄位上插入資料的時候，例如插入一個圖片、視頻等，
你需要使用IO流才行，

7.3、創建一個學生表

學生表有欄位：學號、姓名、年齡、性別、郵箱地址

create table t_student(
	no int,
    name varchar(32),
    sex char(1),
    age int(3),
    email varchar(255)
);

7.4、洗掉表

洗掉 t_student 表

drop table t_student; // 當這張表不存在的時候會報錯！
// 如果這張表存在的話，洗掉
drop table if exists t_student;

7.5、插入資料 insert (DML)

語法格式：

insert into 表名(欄位名1,欄位名2,欄位名3…) values(值1,值2,值3);

注意：

欄位名和值要一一對應，數量要對應，資料型別要對應，

insert陳述句但凡是執行成功了，那么必然會多一條記錄，
沒有給其它欄位指定值的話，默認值是NULL，

insert陳述句中的“欄位名”可以省略

	insert into t_student values(2,'zhangsan','f',20,'zhangsan@qq.com');

注意：

前面的欄位名省略的話，等于都寫上了！所以值也要全部寫上

7.5、插入日期

數字格式化：format(數字,‘格式’)

select (姓名),format((工資),'$999.999') from (表名); //結果保留三位小數

str_to_date(‘字串日期’, ‘日期格式’)：將字串varchar型別轉換成date型別
date_format(日期型別資料, ‘日期格式’)：將date型別轉換成具有一定格式的>varchar>字串型別，

資料庫命名規范：所有的識別符號都是全部小寫，單詞和單詞之間使用下劃線進行銜接，

mysql的日期格式：

%Y 年
%m 月
%d 日
%h 時
%i 分
%s 秒

str_to_date：

函式可以把字串varchar轉換成日期date型別資料，
通常使用在插入insert方面，因為插入的時候需要一個日期型別的資料，
需要通過該函式將字串轉換成date，

如果提供的日期字串是這個格式，str_to_date函式就不需要了

%Y-%m-%d

date_format：

date_format 函式可以將日期型別轉換成特定格式的字串，

注意：

mysql默認的日期格式：’%Y-%m-%d’

7.6、date 和 datetime 兩個型別的區別

區別：

date是短日期：只包括年月日資訊，
datetime是長日期：包括年月日時分秒資訊，

默認格式：

mysql短日期默認格式：%Y-%m-%d
mysql長日期默認格式：%Y-%m-%d %h:%i:%s

獲取當前時間：

在mysql中獲取系統當前時間可以使用 now() 函式，獲取的時間帶有：時分秒資訊，是>datetime型別的，

	insert into 
		t_user(id,name,birth,create_time) 
	values 
		(2,'zhangsan','1991-10-01',now())//now()當前系統時間

7.7、修改update(DML)

語法：

update 表名 set 欄位名1=值1,欄位名2=值2,欄位名3=值3… where 條件;

注意：

沒有條件限制會導致所有資料全部更新，
帶條件的話修改的就是與條件相符合的欄位
= 號右邊的值更新左邊的欄位

7.8、洗掉資料 delete (DML)

語法：

delete from 表名 where 條件;

注意：

沒有條件，整張表的資料會全部洗掉！
帶條件的話洗掉的就是與條件相符合的記錄
mysql默認的日期格式：’%Y-%m-%d’

---

整理寫了很久的筆記，如有錯誤還請大佬指出，
嘿嘿，三連不過分吧！