mysql入門筆記3

標籤：

26課 union查詢

就是把兩條或者多條的查詢結果合并成一個結果

兩條語句的where 都比較複雜，在一起寫就很麻煩，分成兩個簡單的

mysql> select * from goods where cat_id =3
-> union
-> select * from goods where cat_id = 4;
+———-+————————+——–+———-+———–+-
—+————+————–+————-+
| goods_id | goods_name | cat_id | brand_id | goods_sn |
er | shop_price | market_price | click_count |
+———-+————————+——–+———-+———–+-
—+————+————–+————-+
| 8 | 飛利浦[email protected] | 3 | 4 | ecs000008 |
1 | 399.00 | 478.79 | 10 |
| 9 | 諾基亞e66 | 3 | 1 | ecs000009 |
4 | 2298.00 | 2757.60 | 20 |
| 10 | 索愛c702c | 3 | 7 | ecs000010 |
7 | 1328.00 | 1593.60 | 11 |
| 11 | 索愛c702c | 3 | 7 | ecs000011 |
1 | 1300.00 | 0.00 | 0 |
| 12 | 摩托羅拉a810 | 3 | 2 | ecs000012 |
8 | 983.00 | 1179.60 | 13 |
| 13 | 諾基亞5320 xpressmusic | 3 | 1 | ecs000013 |
8 | 1311.00 | 1573.20 | 13 |
| 15 | 摩托羅拉a810 | 3 | 2 | ecs000015 |
3 | 788.00 | 945.60 | 8 |
| 17 | 夏新n7 | 3 | 5 | ecs000017 |
1 | 2300.00 | 2760.00 | 2 |
| 19 | 三星sgh-f258 | 3 | 6 | ecs000019 |
12 | 858.00 | 1029.60 | 7 |
| 20 | 三星bc01 | 3 | 6 | ecs000020 |
12 | 280.00 | 336.00 | 14 |
| 21 | 金立 a30 | 3 | 10 | ecs000021 |
40 | 2000.00 | 2400.00 | 4 |
| 22 | 多普達touch hd | 3 | 3 | ecs000022 |
1 | 5999.00 | 7198.80 | 16 |
| 24 | p806 | 3 | 9 | ecs000024 |
00 | 2000.00 | 2400.00 | 35 |
| 31 | 摩托羅拉e8 | 3 | 2 | ecs000031 |
1 | 1337.00 | 1604.39 | 5 |
| 32 | 諾基亞n85 | 3 | 1 | ecs000032 |
4 | 3010.00 | 3612.00 | 9 |
| 1 | kd876 | 4 | 8 | ecs000000 |
1 | 1388.00 | 1665.60 | 9 |
| 14 | 諾基亞5800xm | 4 | 1 | ecs000014 |
1 | 2625.00 | 3150.00 | 6 |
| 18 | 夏新t5 | 4 | 5 | ecs000018 |
1 | 2878.00 | 3453.60 | 0 |
+———-+————————+——–+———-+———–+-
—+————+————–+————-+
18 rows in set (0.08 sec)

Union取出來的一定要列相同，，才能合并，而且可以跨表查詢

列名稱未必要一致，列名稱會使用第一個的列名稱

注意 使用union 完全相等的行將會被合并
合并是比較耗時的工作，一般不讓union進行合并，使用union all 可以避免
合并，可以讀一速度有一個很好的提升。

mysql> select * from a
-> union
-> select * from b;
+——+——+
| id | num |
+——+——+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| b | 5 |
| d | 20 |
| e | 99 |
+——+——+
7 rows in set (0.00 sec)

mysql> select * from a
-> union all
-> select * from b;
+——+——+
| id | num |
+——+——+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+——+——+
8 rows in set (0.00 sec)

Union 的子句中不用寫order by 就算寫了也沒有效果
因為sql合并後得到的總的結果集可以order ，子句沒有意義去order by
最後還會合并在一起

例題，把兩個表中同id的加和顯示出來

mysql> select id,sum(num) from
-> (
-> select * from a
-> union all
-> select * from b
-> )
-> as tmp
-> group by id;
+——+———-+
| id | sum(num) |
+——+———-+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
+——+———-+
5 rows in set (0.00 sec)

不要忘了運用學到的東西。

如果不用union 用串連查詢做。。。。

學習了sql的操作語言。

下面學習資料定義語言 (Data Definition Language)

28課 建立table

建表的過程就是聲明表頭的過程，也就是列就是一個聲明列的過程。

列聲明什麼類型，給什麼屬性，即放得下內容，又不浪費空間

列的類型與其屬性

建表
：
Create table 表名（
列1 列類型 列屬性 預設值， 可以唯寫到列類型
列2 列類型 列屬性 預設值

）；

29課整型列

大的分

數值型——-整型，浮點，定點

字串—-char varchar ,text

日期時間類型
2012- 12-13
14:26:23

整型
Int 佔據磁碟4個位元組

Bingint 8個位元組
Mediumint 3個位元組

Smallint 2個位元組

Tinyint 1個位元組 -128 ~127
都有兩種可能全正和有正有負
一個位元組8位2進位

30課 整型列的選擇性參數

Unsigned 無符號，就是使他為0-255那種全是正的也就是無符號的

Alter table 表名 add unum tinyint unsigned
修改表，增加 列 就是增加個unnum的列，且類型類tinyint ，屬性為unsigned就是無符號

Zerofill 適合用於學號，編碼等，固定寬度的，可以用0進行填充長度一樣的

填充到多寬，M 就是寬度的意思

Alert table t2 sn tinyint(5) zerofill

那個括弧中的5就是長度或者說寬度。

用0進行填充位。

他不會等於負數，不用寫他也是unsigned的。預設是無符號的

那個M也就是類型括弧裡的是要和zerofill一起結合用的。

就是用0填充到多少。

31課 浮點列與定點列

Float、double、decimal

Float（M,D）M是精度，總位元，D是標度小數點後面的位元

Double同float，就是很大。

Decimal是定點型，也有M,D

主要是decimal是定點型，比之前兩個更精確

32課 字元型列

Char varchar

他們兩個的區別

Char(10) 無論給多少都佔10個字元，定長的

Varchar（10）
最多能放10個字元，只放一個字元，他佔多一個字元加1到2個位元組，會用部分空間來儲存
佔了多少，結尾是多少~~

不是很大的話，空間又很大的話，用charf會更快一點。

Char 位不夠的話用個空格來補齊

Concat 拼接字串

如

Select concat (‘asdasd’,n1,’asd’) from t4

在char 的後面加空格在串連別的，出來後就把空格弄沒了，因為是用空格填充串連或，區分不了，然後就省略了。但是varchar不會，因為有一部分是用來記錄資料的

Text 可以存很多。特大文本。

Blob 是二進位類型，用來存映像，音頻等二進位資訊。
Enum 枚舉類型 是定義好值就在某幾個枚舉範圍內。
像性別 ，只可能是男或者女

Create tables t7 (
Xingbie enum(‘男’,女’)
)；

只能是男或女。

SET 與枚舉的區別就是可以選幾個，不止一個枚舉只能是男或女

日期時間 year 可以存 1901-2155年 1970
Date 1000/01/01 —-9999/12/12 1998-12-31
Time -838:59:59 —–9999:12:31 23:59:59
Datetime 1000、01、01 00:00:00 —-9999:12:31 23:59:59
（就是上面兩個的結合）

寫入資料的時候就是直接寫字串。

年是直接寫年

還有timestmp 類型

不對他進行輸入的話會自動的輸入目前時間

不建議用，效率並不高。

34課 列的預設值

1、NULL 查詢不便，
2、NULL的效率不高
所以避免不預設為NULL
聲明列為not NULL default 預設值
聲明不預設為NULL,而是預設default

要寫建立表的時候在建立每一列的後面加
如：

Id int not NULL default 0,

35課 主鍵與自增

主鍵 能夠區分每一行的列

Auto_increment

聲明主鍵

Create table t11 (
Id int primary key,
………
)；

或者在寫完列後再寫誰是主鍵
……….
Primary key (id)
);

Auto_increment 自增，只能有一列是自增列
此列必須加索引
Key（那個列）
或者index
Oracle 沒有自增，

36課 綜合建表案例

一個表如果全是定長的話可以提高效率

定長與變長分離，常用與不常用列分離

37課 列的刪除增加與修改
改名 ： rename tablle 現表名 to 改的表名
新增一個列

Alertr table 表名 修改表
Add hight tinyint unsigned not null default 0; 增加列
預設在表的最後。
放在指定位置
Alertr table 表名 修改表
Add hight tinyint unsigned after 另一個列名 ; 增加列到另一個列名後

Alertr table 表名 修改表
Drop column 列名；

修改

Alert table 表名 change 現列名 更改列名 類型 屬性

就像聲明一個新列，然後替代他。

Alert table 表名 modify 列名 類型 屬性

Desc 表名，查看這個表的各個列的類型，屬性

38課 視圖

知道視圖的概念，
會建視圖

有些東西查詢出來經常用，不如把他存起來

建立視圖

Create view 視圖名 as 查詢的語句

下次直接select * from 視圖名
就是那個查詢的效果了。

View 又被稱為虛擬表，view是一個sql語句的查詢結果，

有什麼用？
1、許可權控制，某幾個列允許使用者查詢，其他的不允許，
通過視圖，開放其中幾列，
2、簡化複雜的查詢，把一個表提出來可以在操作。

3、建立完視圖後，show tables 也會出來視圖，根本區分不了
那能不能更新和刪除~

改了表，視圖也會變，直接改視圖的話，如果是資料同物理表的的話，沒有進行運算
就可以改，如果是進行運算的，如，取平均值，直接修改了，那麼改了，物理表
的資料不知道怎麼變。

39課 視圖的algorithm

視圖放哪了

View 把建這個查詢語句與再次對view的查詢語句拼接起來，直接去查物理表
這種視圖儲存的是一個語句。這種是土地額演算法叫 merge(合并)

也有可能視圖的本身語句就比較複雜，很難結合，mysql就先執行建立視圖的語句，儲存為一個暫存資料表，然後在對暫存資料表進行查詢temptable

可以自己設定 運用的演算法
Create algorithm = merge view as sql查詢語句 就是拼接的。

不寫則是資料庫自己判斷。

40課 表/視圖的管理語句

刪除表 drop table 表名
刪除視圖 drop view 視圖名

查看建表過程 show create table;

查看建視圖過程 show create view;

查看所有表的詳細資料 show table status
後面加個\G就會豎著顯示了 方便看一點。

不看那麼多，單獨查某表

Show table status where name =’表名’；

去查看後最後面有個coment ：view的就是視圖

Truncate 清空一個表的資料

Delete from 表名；

用delete去刪除的話，如果是自增的話，刪除一個，再次增加一個後是會增加的，不是原來的id了。

把這個表刪除增加就又是1

Delete 是刪除資料，再次增加，自增還會增加
Truncate則不會，相當於刪除表再重建。

41課 儲存引擎的概念

儲存在磁碟的MYI是索引檔案。
Frm是說明
MYD是資料

引擎不同，有人資料資訊重要存放的位置不同。

Engine 引擎不同。
5.5 以預設innoDB

常用的引擎 Myisam InnoDb Memory

不持久的用 Memory

Myisam InnoDb 區別

Myisam 速度快 不安全。

InnoDb 安全 有日誌。資料不易丟失。

建表的時候，在後面寫 Engine = 使用的引擎。

Myisam
可以直接把目錄拷過去

InnoDb 就不行。

42課 字元集與亂碼

字元集，校對集，亂碼

文本本來的字元集與展示的字元集不一致到欄位的。

就選utf-8

如果儲存的是utf-8，但是用戶端是GBK
就可以在中間做個轉換。

Set names gbk;

就是把那3個都設為gbk 輸入，顯示 中間

在頁面中設定是utf-8
但是在這個用戶端中設定gbk；

校對集：
按什麼的規則排序~

43課 索引概念

快速查到，MVI索引檔案
以某種資料結構儲存，比如樹
高效 ，像書的一個目錄。
快速定位行，資料的位置
更改資料的同時要改索引。
索引是有代價的，降低了增刪改的速度。
有可能會產生索引檔案比資料檔案還大。
一般在重複度低的列上加索引效果好。
還有查詢頻繁時用好。

普通索引 key
唯一索引 uniquwkey
主鍵索引 primarykey
全文索引 fulltest

普通索引 key

在建完列後 key 索引的名字 （以哪個列做索引）

一般索引的名字和 以哪個列做索引 是一樣的。

唯一索引 uniquwkey

在建完列後 uniquwkeykey 索引的名字 （以哪個列做索引）

唯一索引不允許重複

主鍵索引 primarykey 與之前一樣

全文索引 fulltest
，在中文環境下無效，要分詞加索引，一般用第三方解決方案
如 sphinx

索引長度，建索引時，樂意只索引列的前一部分的內容
比如，前10個字元。

在建完列後 uniquwkeykey 索引的名字 （以哪個列做索引（長度））
就像郵箱~~

還可以建多列索引
就是把兩列或多列看成一個整體建索引

索引的名字 （以哪個列做索引，另一個列）

看看索引 show index from 表名 \G

查詢時要都寫，才能發揮索引的作用。

44課 索引操作

看看索引 show index from 表名 \G

刪除索引
Alert table 表名 drop index 索引名
Drop index 索引名 from 也可以刪除

添加還用
Alert table 表名 add index 索引名( );

添加主鍵索引不用起索引名

45課常用函數

一、數學函數
abs(x) 返回x的絕對值
bin(x) 返回x的二進位（oct返回八進位，hex返回十六進位）
ceiling(x) 返回大於x的最小整數值
exp(x) 傳回值e（自然對數的底）的x次方
floor(x) 返回小於x的最大整數值
greatest(x1,x2,…,xn)返回集合中最大的值
least(x1,x2,…,xn) 返回集合中最小的值
ln(x) 返回x的自然對數
log(x,y)返回x的以y為底的對數
mod(x,y) 返回x/y的模（餘數）
pi()返回pi的值（圓周率）
rand()返回０到１內的隨機值,可以通過提供一個參數(種子)使rand()隨機數產生器產生一個指定的值。
round(x,y)返回參數x的四捨五入的有y位小數的值
sign(x) 返回代表數字x的符號的值
sqrt(x) 返回一個數的平方根
truncate(x,y) 返回數字x截短為y位小數的結果

二、彙總函式(常用於group by從句的select查詢中)
avg(col)返回指定列的平均值
count(col)返回指定列中非null值的個數
min(col)返回指定列的最小值
max(col)返回指定列的最大值
sum(col)返回指定列的所有值之和
group_concat(col) 返回由屬於一組的列值串連組合而成的結果

三、字串函數
ascii(char)返回字元的ascii碼值
bit_length(str)返回字串的位元長度
concat(s1,s2…,sn)將s1,s2…,sn串連成字串
concat_ws(sep,s1,s2…,sn)將s1,s2…,sn串連成字串，並用sep字元間隔
insert(str,x,y,instr) 將字串str從第x位置開始，y個字元長的子串替換為字串instr，返回結果
find_in_set(str,list)分析逗號分隔的list列表，如果發現str，返回str在list中的位置
lcase(str)或lower(str) 返回將字串str中所有字元改變為小寫後的結果
left(str,x)返回字串str中最左邊的x個字元
length(s)返回字串str中的字元數
ltrim(str) 從字串str中切掉開頭的空格
position(substr,str) 返回子串substr在字串str中第一次出現的位置
quote(str) 用反斜線轉義str中的單引號
repeat(str,srchstr,rplcstr)返回字串str重複x次的結果
reverse(str) 返回顛倒字串str的結果
right(str,x) 返回字串str中最右邊的x個字元
rtrim(str) 返回字串str尾部的空格
strcmp(s1,s2)比較字串s1和s2
trim(str)去除字串首部和尾部的所有空格
ucase(str)或upper(str) 返回將字串str中所有字元轉變為大寫後的結果

四、日期和時間函數
curdate()或current_date() 返回當前的日期
curtime()或current_time() 返回當前的時間
date_add(date,interval int keyword)返回日期date加上間隔時間int的結果(int必須按照關鍵字進行格式化),如：selectdate_add(current_date,interval 6 month);
date_format(date,fmt) 依照指定的fmt格式格式化日期date值
date_sub(date,interval int keyword)返回日期date加上間隔時間int的結果(int必須按照關鍵字進行格式化),如：selectdate_sub(current_date,interval 6 month);
dayofweek(date) 返回date所代表的一星期中的第幾天(1~7)
dayofmonth(date) 返回date是一個月的第幾天(1~31)
dayofyear(date) 返回date是一年的第幾天(1~366)
dayname(date) 返回date的星期名，如：select dayname(current_date);
from_unixtime(ts,fmt) 根據指定的fmt格式，格式化unix時間戳記ts
hour(time) 返回time的小時值(0~23)
minute(time) 返回time的分鐘值(0~59)
month(date) 返回date的月份值(1~12)
monthname(date) 返回date的月份名，如：select monthname(current_date);
now() 返回當前的日期和時間
quarter(date) 返回date在一年中的季度(1~4)，如select quarter(current_date);
week(date) 返回日期date為一年中第幾周(0~53)
year(date) 返回日期date的年份(1000~9999)
一些樣本：
擷取當前系統時間：select from_unixtime(unix_timestamp());
select extract(year_month from current_date);
select extract(day_second from current_date);
select extract(hour_minute from current_date);
返回兩個日期值之間的差值(月數)：select period_diff(200302,199802);
在mysql中計算年齡：
select date_format(from_days(to_days(now())-to_days(birthday)),’%y’)+0 as age from employee;
這樣，如果brithday是未來的年月日的話，計算結果為0。
下面的sql語句計算員工的絕對年齡，即當birthday是未來的日期時，將得到負值。
select date_format(now(), ‘%y’) - date_format(birthday, ‘%y’) -(date_format(now(), ‘00-%m-%d’)

mysql入門筆記3