no-image

SQL基礎入門

                                    

SQL學習

常用的資料庫

關係型資料庫

MySQL,Oracle,SqlServer

非關係型資料庫

Redis,MongoDB

兩者關係

為什麼要使用NoSQL

關係型資料庫和非關係型資料庫

資料庫的資料型別

時間型別區別

資料型別 含義
date 日期 ‘2008-12-2’
time 時間 ’12:25:36′
datetime 日期時間 ‘2008-12-2 22:06:44’
timestamp 自動儲存記錄修改時間 格式同上

DATETIM和TIMESTAMP型別所佔的儲存空間不同,前者8個位元組,後者4個位元組。

這樣造成的後果是兩者能表示的時間範圍不同。

前者範圍為1000-01-01 00:00:00 ~ 9999-12-31 23:59:59,後者範圍為1970-01-01 08:00:01到2038-01-19 11:14:07。

所以可以看到TIMESTAMP支援的範圍比DATATIME要小,容易出現超出的情況。

更多資料

mysql中timestamp,datetime,int型別的區別與優劣

MySQL資料型別詳解

MySQL關鍵字

關鍵字 含義
NULL 資料列可包含NULL值
NOT NULL 資料列不允許包含NULL值
DEFAULT 預設值
PRIMARY KEY 主鍵
AUTO_INCREMENT 自動遞增,適用於整數型別
UNSIGNED 無符號
CHARACTER SET name 指定一個字符集

須知

SQL 對大小寫不敏感

''

USER_ID USER_NAME CLASS_ID
1 frank 16052502
2 barry 16031023
3 peter 16021314

select user_id,user_name from user

USER_ID USER_NAME
1 frank
2 barry
3 peter

SELECT DISTINCT

把返回結果中重複的值去掉

SELECT DISTINCT 列名稱 FROM 表名稱

select user_id from grade

USER_ID
1
2
3
3
2
1
2

select distinct user_id from grade

USER_ID
1
2
3

WHERE

增加篩選的條件

SELECT 列名稱 FROM 表名稱 WHERE 列 運算子 值

運算子 描述
= 等於
<> 不等於
> 大於
< 小於
>= 大於等於
<= 小於等於
BETWEEN..AND.. 在某個範圍內 (字母順序或數字順序)
NOT BETWEEN..AND.. 不在某個範圍內 (字母順序或數字順序)
LIKE ‘%..%’ 以某種模式模糊匹配(字串)
NOT LIKE ‘%..%’ 除了模糊匹配的以外的(字串)
IN ( .. , .. , .. ) 在列表裡
NOT IN ( .. , .. , .. ) 不在列表裡

select user_id from user where user_name = 'frank'

USER_ID
1

select user_id,user_name from user where user_name like '%r%'

USER_ID USER_NAME
1 frank
2 barry
3 peter

select user_id from user where user_name in ('frank','barry')

USER_ID
1
2

AND&OR

AND 和 OR 可在 WHERE 子語句中把兩個或多個條件結合起來

select score from grade where course_id = 2 and user_id = 2

SCORE
90

select score from grade where course_id = 2 and (user_id = 1 or user_id = 2)

SCORE
73
90

ORDER BY

用於根據指定的列對結果集進行排序,預設是升序,字母順序或數字順序

順序 符號
升序 ASC
降序 DESC

select score from grade order by score desc

SCORE
93
90
88
86
73
69
54

select user_name from user order by user_name

USER_NAME
barry
frank
peter

select user_id,score from grade order by user_id desc , score asc

USER_ID SCORE
3 54
3 88
2 86
2 90
2 93
1 69
1 73

INSERT INTO

用於向表格中插入新的行

INSERT INTO 表名稱 VALUES (值1, 值2,….)

INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,….)

insert into user values (4,'tom',16063385);
select user_name from user

USER_NAME
frank
barry
peter
tom

insert into user (user_id,user_name) values (5,'jerry');
select * from user

USER_ID USER_NAME CLASS_ID
1 frank 16052502
2 barry 16031023
3 peter 16021314
4 tom 16063385
5 jerry

UPDATE

用於修改表中的資料

UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

update grade set score = 100 where user_id = 1 and course_id = 3;
select user_id,course_id,score from grade where user_id = 1

USER_ID COURSE_ID SCORE
1 2 73
1 3 100

update grade set course_id = 1,score = 99 where user_id = 1 and course_id = 2;
select user_id,course_id,score from grade where user_id = 1

USER_ID COURSE_ID SCORE
1 1 99
1 3 100

DELETE

用於刪除表中的行

DELETE FROM 表名稱 WHERE 列名稱 = 值

DELETE FROM table_name 刪除表內所有的行

DELETE * FROM table_name 刪除表內所有的行

delete from user where user_name = 'peter';
select user_id,user_name from user

USER_ID USER_NAME
1 frank
2 barry

進階語法

LIMIT

用於規定要返回的記錄的數目

SELECT column_name(s)
FROM table_name
LIMIT number

select user_name from user limit 2

USER_NAME
frank
barry

LIKE

用於在 WHERE 子句中搜尋列中的指定模式

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

提示:”%” 可用於定義萬用字元(模式中缺少的字母)。

select user_id,user_name from user like '%r'   --結尾是r

USER_ID USER_NAME
3 peter

select user_id,user_name from user not like 'f%'  --開頭不是f

USER_ID USER_NAME
2 barry
3 peter

萬用字元

在搜尋資料庫中的資料時,SQL 萬用字元可以替代一個或多個字元。

SQL 萬用字元必須與 LIKE 運算子一起使用。

萬用字元 描述
% 替代一個或多個字元
_ 僅替代一個字元
[charlist] 字元列中的任何單一字元
1或者[!charlist] 不在字元列中的任何單一字元

select user_name from user like '[fb]%'

USER_NAME
frank
barry

AS別名

為列名稱和表名稱指定別名

表的別名

SELECT column_name(s)
FROM table_name
AS alias_name

列的別名

SELECT column_name AS alias_name
FROM table_name

select user_id, user_name as name from user where name = 'frank'

USER_ID NAME
1 frank

連線查詢

之前都是在一個表裡進行查詢,當查詢涉及兩個表以上時,就叫連線查詢

select user.user_name,course.course_name from user,course

USER_NAME COURSE_NAME
frank 資料庫
frank 作業系統
frank 資料結構
barry 資料庫
barry 作業系統
barry 資料結構
peter 資料庫
peter 作業系統
peter 資料結構

select user.user_name,class.class_name from user,class where user.class_id = class.class_id

USER_NAME CLASS_NAME
frank 電子2班
barry 軟工3班
peter 會計4班

JOIN

專門用於查詢兩張及兩張表以上的資料

INNER JOIN

兩張表裡都有匹配的話,就返回那一行

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

select user.user_name,grade.score from user inner join grade on user.user_id = grade.user_id

USER_NAME SCORE
frank 73
frank 69
barry 86
barry 90
barry 93
peter 54
peter 88
LEFT JOIN

LEFT JOIN 關鍵字會從左表那裡返回所有的行,即使在右表中沒有匹配的行

這裡的左表就是指下面的table_name1,右表指的是下面的table_name2

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

insert into user(user_id,user_name) values(4,'shell');
select user.user_name,class.class_name from user 
left join class on user.class_id = class.class_id

USER_NAME CLASS_NAME
peter 會計4班
barry 軟工3班
frank 電子2班
shell
RIGHT JOIN

RIGHT JOIN 關鍵字會右表那裡返回所有的行,即使在左表中沒有匹配的行。

這裡的左表就是指下面的table_name1,右表指的是下面的table_name2

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

insert into class values (16090134,'新建班級',50);
select user.user_name,class.class_name from user 
right join class on user.class_id = class.class_id

USER_NAME CLASS_NAME
frank 電子2班
barry 軟工3班
peter 會計4班
新建班級
FULL JOIN

只要其中某個表存在匹配,就會返回行

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

select user.user_name,class.class_name from user
full join class on user.class_id = class.class_id

USR_NAME CLASS_NAME
frank 電子2班
barry 軟工3班
peter 會計4班
shell
新建班級

子查詢

一個select語句中巢狀另一個select語句

MySQL子查詢

子查詢返回的是一個結果

SELECT column_name(s)

FROM table_name

WHERE column_name 運算子 (SELECT子查詢)

select user_name from user 
where class_id = (select class_id from class where class_name = '軟工3班')

USER_NAME
barry
IN / NOT IN

當子查詢返回的結果有多個的時候,判斷是不是在子查詢的結果裡

SELECT column_name(s)

FROM table_name

WHERE column_name IN (SELECT子查詢)

select user_name from user where user_id in (select user_id from grade where score > 80)

USER_NAME
barry
peter
EXISTS / NOT EXISTS

判斷子查詢的結果存不存在

SELECT column_name(s)

FROM table_name

WHER EXISTS (SELECT子查詢)

select user_name from user 
where exists (select 1 from grade where user.user_id = grade.user_id and grade.score > 80)

USER_NAME
barry
peter
ANY / SOME

當子查詢返回的結果有多個的時候,表示任何的意思,ANY和SOME的結果一樣,如where n > any (1,2) 等同 where n > 1 or n > 2, n>1或n>2都行

SELECT column_name(s)

FROM table_name

WHERE column_name 運算子 ANY (SELECT子查詢)

select user_name from user where class_id =  
any (select class_id from class where class_name = '軟工3班' and class_name = '會計4班')

USER_NAME
barry
peter
ALL

當子查詢返回的結果有多個的時候,表示所有的意思,如where n > all (1,2) 等同 where n > 1 and n > 2, 必須滿足n > 1 和 n > 2

SELECT column_name(s)

FROM table_name

WHERE column_name 運算子 ALL (SELECT子查詢)

select score from grade where score > all (select score from grade where user_id = 3)

SCORE
90
93

函式

AVG()

求該列的平均值

SELECT AVG(column_name) FROM table_name

select avg(score) as avg_score from grade

AVG_SCORE
79

select user_name from user where user_id 
in (select user_id from grade where score > (select avg(score) from grade))

USER_NAME
barry
peter
COUNT()

返回匹配指定條件的行數

SELECT COUNT(column_name) FROM table_name

select count(user_id) as user_sum from grade

USER_SUM
7

select count(distinct user_id) as user_sum from grade

USER_SUM
3
MAX()

返回一列中的最大值

SELECT MAX(column_name) FROM table_name

select max(score) from grade

MAX(SCORE)
93
MIN()

返回一列中的最小值

SELECT MIN(column_name) FROM table_name

select min(score) from grade

MIN(SCORE)
54
SUM()

返回數值列的總數

SELECT SUM(column_name) FROM table_name

select sum(score) from grade

SUM(SCORE)
553

GROUP BY

根據一個或多個列對結果集進行分組

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

select user_id,avg(score) as avg from grade group by user_id

USER_ID AVG
1 71.000
2 89.667
3 71.000

select user_id,course_id,sum(score) from grade group by user_id,course_id

USER_ID COURSE_ID SUM(SCORE)
1 2 73
1 3 69
2 1 86
2 2 90
2 3 93
3 1 54
3 3 88

HAVING

和where類似,不過篩選條件可以有函式

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

select user_id,avg(score) from grade where user_id > 1
group by user_id
having avg(score) > 70

USER_ID AVG(SCORE)
2 89.667
3 71.000

charlist

關聯文章