寫在前面:
本篇文章是根據 https://sqlbolt.com/ 課程内容編寫的
此site有習題可練習,亦可根據給定的table線上寫SQL語句,實時檢視傳回結果,推薦使用。
每道Exercise Task 下方有 Read this task’s SOLUTION,即檢視正确答案。
Table ↘
Table ——→ Database(資料庫)
Table ↗ ↘
Table(表) ——→ Server(伺服器/伺服器)
Table ↘ ↗
Table ——→ Database(資料庫)
Table ↗
SQL類型與JAVA類型的對應:
SQL JAVA
Decimal BigDecimal
Long Integer
TEXT String
tinyint Boolean
datetime java.util.date
bigint java.math.BigInteger
自增長的id Java要用Long
快速引索:
檢視版本或打開
xxxx$
mysql -u root -p
Enter password:
8位密碼
退出目前這步操作
mysql>
control鍵+C
退出MySQL
mysql>
exit
增删改查
檢視目前MySQL版本
1.登入時會傳回版本資訊;
2.登入後:
mysql>
select version();
+-----------+
| version() |
+-----------+
| 8.0.15 |
+-----------+
檢視所有資料庫
mysql>
show databases;
//此刻可面對Tables了
使用某個資料庫
mysql>
use 庫名;
綜合以上兩項,檢視有哪些資料庫,標明一個資料庫,檢視此資料庫中所有表
mysql>
show databases;
mysql>
use 庫名;
mysql>
show tables;
檢視表中:列的資訊
mysql>
desc 表名;
檢視表中:列與參數的資訊(與上條語句的差別是:若此條語句中沒有儲存參數,會傳回
Empty set (0.00 sec)
)
mysql>
select * from 表名;
表改名
mysql>
rename table 舊名 to 新名;
删除一個表
mysql>
drop table 表名;
// 一定要聲明drop的是table,不然系統不知是庫還是表
檢視一個表中有幾行資料
mysql>
select count(*) from 表名;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
建立一個資料庫
mysql>
create database 資料庫名 charset utf8;
如忘了聲明charset,如下操作:
mysql>
set names gbk;
删除一個資料庫(謹慎操作)
mysql>
drop database 資料庫名;
// 一定要聲明drop的是database,不然系統不知是庫還是表
庫改名?
Database不能改名,隻有Tables和Query可改。
TRUNCATE和DELETE的差別?
TRUNCATE :是一個資料定義語言,會被隐式送出,執行後不能復原;而DELETE是從表中删除一行資料,同時把删除的操作以日志的形式儲存,以便将來復原;
TRUNCATE删除資料後,會立即釋放占用的資料空間,資料不能被恢複;DELETE删除的資料占用空間還在,還可以恢複;
TRUNCATE執行速度比DELETE快;
清空表資料
mysql> truncate 表名;
// 一定要聲明drop的是database,不然系統不知是庫還是表
一句簡單的SQL文法:
select(column) from (table)where(限制條件);
如:
select * from table_name where 1;
意思為選擇所有column,來自xx表,條件無。
一段包含大多數格式的SQL語句:
SELECT 列名 , COUNT (列名) AS xxx //SELECT 預設參數:*
FROM 表名 //此處可再添加:表A LEFT ON 表B ON 表A.col1 = 表B.col1
WHERE 限制數字/文字條件 ( 或添加exists(...) ) //WHERE 預設參數:1
GROUP BY 列名
ORDER BY 列名 ASC/DESC LIMIT xxx; //ORDER BY 要在最後一行
或者:有HAVING(HAVING與WHERE的關系要注意):
SELECT 列名 , COUNT (列名) AS xxx //SELECT 預設參數:*
FROM 表名 //此處可再添加:表A LEFT ON 表B ON 表A.col1 = 表B.col1
GROUP BY 列名
HAVING 條件
ORDER BY 列名 ASC/DESC LIMIT xxx; //ORDER BY 要在最後一行
HAVING與WHERE的差別!
- 聯系:他們都是篩選;
-
本質差別:
where的原理是(
先where再select
)先篩選再select,篩選的是資料庫表裡面本來就有的字段;
而,having的原理是(
)先select,然後從select出來的内容進行篩選。having篩選的字段是從前篩選的字段篩選的。先select再having
-
聚合函數:原本不存在于原始語句裡的内容,通過SQL語句而臨時生成的函數;
有where和having都可以使用(但不并存)的場景;
有隻可以使用where,不可以使用having的情況;
有隻可以使用having,不可以使用where的情況;
INSERT
一段 INSERT 語句:
INSERT INTO
VALUES
INSERT INTO 表名 (col1 , col2 , col3)
VALUES(col1值 , col2值 , col3值 , col4值 );
批量INSERT:
insert into 表名(col1 , col2 , col3)
values(col1值 , col2值 , col3值 , col4值),
(col1值 , col2值 , col3值 , col4值);
一段 UPDATE 語句:
UPDATE
SET
WHERE
UPDATE 表名
SET 列名 = "更新後的參數"
WHERE ID = "需更新參數的行ID" ;
一段 DELETE 語句:
DELETE FROM
WHERE
delete from 表名
where 列的限制條件;
一段 CREATING TABLE 語句:
CREATE TABLE 表名 (
列名 資料類型 限制 , //每一行後都以一個逗号結尾
列名 資料類型 限制 ,
列名 資料類型 限制 //最後一行不要加逗号
);
一段 DROPPING TABLE 語句 (注!實際工作中謹慎操作!):
DROP TABLE 表名;
增加一條列
ALTER TABLE
ADD COLUMN
ALTER TABLE 表名
ADD COLUMN 列名 資料類型 ;
修改列類型
ALTER TABLE
MODIFY
ALTER TABLE 表名
MODIFY 列名 資料類型 ;
修改列名
ALTER TABLE
CHANGE
ALTER TABLE 表名
CHANGE 列名 新列名 資料類型 ;
删除一條列(不能同時删除多條)
ALTER TABLE
DROP
ALTER TABLE 表名
DROP 列名 ;
————————————————————————————————————
如何批量插入?
例子
insert into 表名
(id,last_name,gender,email,d_id)
values(null,'Fred','0','[email protected]','1'),
(null,'Harry','0','[email protected]','1');
注意事項:
id為自增,是以values裡寫NULL;
value裡若需要批量插入,隻需要寫:
(),(),();
(最外圍不需要再加一個括号)
————————————————————————————————————
需要注意:
-
基本語句(select/from/where…)、table name、column name不區分大小寫
但參數區分大小寫;
- 文字參數需要加上雙引号,數字參數直接寫。
-
操作資料庫時,需要先進入此資料庫(use 資料庫名)才能操作下一步;
操作資料庫中的表時,無需進入表,進入了所在資料庫即可指定表來操作。
- 内含子運作式時:因為運作式整體隻能出現一個"
“在最尾;是以不論子運作式有多少個,其括号結尾都不需要加”;
;
"。
————————————————————————————————————
x,y
和
x offset y
意思是相反的;
①
limit x, y
表示: 跳過 x 條資料,讀取 y 條資料;
②
limit x offset y
表示: 跳過 y 條資料,讀取 x 條資料;
比如:從第0個開始,擷取20條資料**(不包括第0個)**
selete * from testtable limit 0, 20;
selete * from testtable limit 20 offset 0;
比如:從第40個開始,擷取20條資料**(不包括第40個)**
selete * from testtable limit 40, 20;
selete * from testtable limit 20 offset 40;
易錯點:千萬注意!
比如有組合為
1,2,3,4
要選取
3
和
4
——————————————
應該寫
limit 2,2
還是
limit 3,2
??
答案為:
limit 2,2
————————————————————————————————————
附習題目錄:
All Lessons
Introduction to SQL
SQL Lesson 1: SELECT queries 101 選擇查詢
SQL Lesson 2: Queries with constraints (Pt. 1) 帶限制的查詢
SQL Lesson 3: Queries with constraints (Pt. 2)
SQL Lesson 4: Filtering and sorting Query results 過濾和排序查詢結果
SQL Review: Simple SELECT Queries
SQL Lesson 6: Multi-table queries with JOINs 帶JOINs的多表查詢
SQL Lesson 7: OUTER JOINs
SQL Lesson 8: A short note on NULLs 關于空值的簡短說明
SQL Lesson 9: Queries with expressions 帶表達式的查詢
SQL Lesson 10: Queries with aggregates (Pt. 1) 使用聚合的查詢
SQL Lesson 11: Queries with aggregates (Pt. 2)
SQL Lesson 12: Order of execution of a Query 查詢的執行順序
SQL Lesson 13: Inserting rows 增行
SQL Lesson 14: Updating rows 改行
SQL Lesson 15: Deleting rows 删行
SQL Lesson 16: Creating tables 創表
SQL Lesson 17: Altering tables 改表
SQL Lesson 18: Dropping tables 删表
SQL Lesson X: To infinity and beyond!
以這張表作範本:
Table: Movies
Id Title Director Year Length_minutes
1 Toy Story John Lasseter 1995 81
2 A Bug's Life John Lasseter 1998 95
3 Toy Story 2 John Lasseter 1999 93
4 Monsters, Inc. Pete Docter 2001 92
5 Finding Nemo Andrew Stanton 2003 107
6 The Incredibles Brad Bird 2004 116
7 Cars John Lasseter 2006 117
8 Ratatouille Brad Bird 2007 115
9 WALL-E Andrew Stanton 2008 104
10 Up Pete Docter 2009 101
顯示3行
SELECT * FROM movies limit 3;
——————————————————————
Id Title Director Year Length_minutes
1 Toy Story John Lasseter 1995 81
2 A Bug's Life John Lasseter 1998 95
3 Toy Story 2 John Lasseter 1999 93
限定column與行數
select id,title,year from movies limit 3;
//注:limit與3間必須有空格
——————————————————————
Id Title Year
1 Toy Story 1995
2 A Bug's Life 1998
3 Toy Story 2 1999
列出電影清單
select title from movies where 1;
//注:where 1 表示沒有條件
——————————————————————
Title
Toy Story
A Bug's Life
Toy Story 2
Monsters, Inc.
Finding Nemo
The Incredibles
Cars
...
列出電影+年份的清單
select title,year from movies where 1;
——————————————————————
Id Title Year
1 Toy Story 1995
2 A Bug's Life 1998
3 Toy Story 2 1999
...
數字 Where
select * from table where 條件 = 1;
Operator Condition SQL Example
「!=」「<」「<=」「>」「>=」 不等于 小于 大于 col !=4
between...and... 在...和...之間 col between 1.4 and 2.5 //包括1.4和2.5
not between...and... 不在...和...之間 col not between 1 and 10
in(...) 在(...)集合内 col in (2,4,6) //這是特指,而不是指範圍!!
not in(...) 不在(...)集合内 col not in (1,3,5)
exists(...)
not exists(...) 詳情參考 文本 Where中的介紹。。。
若現在有條件1=aabb;條件2=bbcc;
and 求交集 1 and 2 = bb
or 求并集 1 or 2 = aabbbbcc
列出1995-2000年的電影ID+名字+年份
- 方法(1):使用 between and
select id,title,year
from movies
where year between 1995 and 2000;
——————————————————————
Id Title Year
1 Toy Story 1995
2 A Bug's Life 1998
3 Toy Story 2 1999
- 方法(2):使用 and
select id,title,year
from movies
where year >= 1995 and year <= 2000;
注:and後不能直接寫 <= 2000,必須寫 year <= 2000;
- 方法(3):使用 in(…)
select id,title,year
from movies
where year in(1995,1996,1997,1998,1999,2000);
注:in(...)隻會選擇集合中提到的數字,而不是範圍!誤寫in(1995,2000)隻會顯示這兩年的資料!!
文本 Where
需要注意:寫文本參數需要加上雙引号
Operator Condition SQL Example
= 等于 col = "abc"
「!=」「<>」 不等于 col != "abc"
LIKE 等于 col LIKE "abc"
NOT LIKE 不等于 col NOT LIKE "abc"
% 模糊比對 col LIKE "%AT%" 比對結果: matches "BATS" , "ATTIC" or even "AT"
col LIKE "AT%" 比對結果: matches "ATS", but not "BAT"
_ 模糊比對(單字元) col LIKE "AN_" 比對結果: matches "AND" , but not "AN"
IN(...) 在集合内 col IN("A","B","C")
NOT IN(...) 不在集合内 col NOT IN("D","E","F")
EXISTS(...)
NOT EXISTS(...)
EXISTS / NOT EXISTS (where)
EXISTS
和
NOT EXISTS
是個非常重要的查詢關鍵詞;總是拿來和
IN
和
NOT IN
做比較;
他強調的是是否傳回結果集,不要求資料傳回,個人了解他像Boolean類型的if語句,隻産生邏輯真值“
true
”或邏輯假值“
false
”。
他們的差別還在于
in
引導的子句隻能傳回一個字段,比如:
select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)
,in子句隻允許傳回一個字段,而exists子句是允許的(類似循環)。
例子:
SELECT sname
FROM student
WHERE exists
(
SELECT *
FROM sc
WHERE sc.sno=student.sno AND cno=1;
)
我們可以這樣了解上面的SQL語句做的事情:
本查詢涉及
student表
和
sc表
;外部查詢先進行一次,再看到内部查詢:若内部條件運作一次後結果符合
EXISTS或NOT EXISTS
的預期(即SC表中SNO = Student表中SNO,且CNO=1,是存在的,與EXISTS期望一緻),得到
true
值,則會将此條結果(即
*
)傳遞給外部查詢。
EXISTS或NOT EXISTS
隻關心Boolean值,不關心内部是如何運作的,若完成了判斷,他的任務就結束了。
由exists引出的子查詢,其目标清單達式通常都用
*
(例子中為
SELECT * FROM sc
);因為EXISTS的子查詢隻傳回真值或者假值,不傳回選擇出來的結果,是以,你給什麼樣的列名最後傳回的都是true或者false,是以給出實際列名無意義。
對于适用于EXISTS查詢的環境了解:
1.首先子查詢中必須要有依賴父查詢的條件,即我們單獨把子查詢的select語句提出來不能正常運作。
2.每次查詢時,父查詢表中的一個元組對子查詢所有的元組進行判定,如果為true則父查詢中的這個元組允許放入結果表,否則進行父查詢下一個元組的判定。
-
關于EXISTS和IN的效率高低之分講解:
https://blog.csdn.net/baidu_37107022/article/details/77278381
——————————————————
列出ToyStory系列的電影ID+名字+年份
select id,title,year
from movies
where title like "toy story%" ;
——————————————————————
Id Title Year
1 Toy Story 1995
3 Toy Story 2 1999
11 Toy Story 3 2010
排序 Rows
需要注意:ORDER必須寫在最後一行!!!
需要注意:ORDER必須寫在最後一行!!!再次重複
寫文本參數需要加上雙引号
Operator Condition SQL Example
ORDER BY 按col排序 ORDER BY col ASC/DESC
ASC 升序 Ascending ORDER BY col ASC
DESC 降序 Descending ORDER BY col DESC
LIMIT OFFSET 從offset取limit LIMIT num_limit OFFSET num_offset
ORDER BY 多列排序 ORDER BY col1 ASC,col2 DESC
列出John Lasseter導演的電影,要ID+名字+年份+導演,按年份降序排列最後三部。
select id,title,year,director
from movies
where director = "John Lasseter"
order by year desc limit 3;
注:年份降序 與 最後三部 = 年份最新的三部
order要寫在最後行;where col="參數" 其中的參數區分大小寫
——————————————————————
Id Title Year Director
12 Cars 2 2011 John Lasseter
7 Cars 2006 John Lasseter
3 Toy Story 2 1999 John Lasseter
Select
重要筆記:
select
from 表名
where
select distinct xxx // 比如:select導演,表中column有重複的導演行,但要求結果不能有重複的導演行時
from 表名
order by xxx asc/desc;
select xxx
from 表名
order by xxx
limit 數字
select xxx
from 表名
order by xxx
limit 2,4; // 指:跳過2個參數,選取4個參數;(若參數為1、2、3、4、5、6、7,意思選取3、4、5、6)
或 limit 4 offset 2; // 指:跳過2個參數,選取4個參數
select xxx
from 表1
join 表2
on id.表1 = id.表2
// join...on... 指和另一個表關聯起來
/ /join後寫另一個表名
// on寫兩個表中對應的column,比如:id.table1 = id.table2
select xxx
from 表名
where xxx is null; //選取某column為空的值
或者 where xxx is not null;
select xxx, (xxx + xxx) / xxx as 新column名
from xxx
// select行可以進行加減乘除,as表示建立一個column名
// + - * /
select max(xxx) 或 avg(xxx) 或 sum(xxx) 或
from xxx;
select max(xxx) 或 min(xxx) 或 avg(xxx) 或 sum(xxx) //選出最大/最小/計算平均數/計算總和
from xxx;
select xxx,count(1) //統計行數,也可以寫成count(*)
from xxx;
select xxx
from xxx
group by xxx
having xxx;
// 比如group by了Role列,在這個基礎上,再選擇隻顯示Role中的"Engineer"這行,就用having
需要注意:
因為“統計”實為對column的操作,是以需要寫在首行(select行),加逗号與column操作區分;
但GROUP BY和HAVING寫在單獨的GROUP BY行;
Operator Condition SQL Example
以下操作寫在Select行(與column操作在同一行,加逗号區分)
COUNT(*), 計數 count(col)
COUNT(col)
MIN(col) 最小 min(col)
MAX(col) 最大 max(col)
AVG(col) 平均 avg(col)
SUM(col) 求和 sum(col)
以下操作寫在單獨一行:GROUP BY行
GROUP BY 分組 group by col1,col2
HAVING 分組後條件 HAVING col>100
詳細解釋:
COUNT(col):COUNT必須經過GROUP BY歸類後再使用;意思是統計col列的數量,生成一條新列;
GROUP BY:若以GROUP BY Directer來分組,則相同導演名會排在一起;比如John在一堆,Pete在一堆;
COUNT (AS) (必須經過GROUP BY歸類後再使用!!!)
HAVING COUNT(*) (必須經過GROUP BY歸類後再使用)
模版:
篩選後每組的行數等于10才輸出
...
group by ...
having count(*) = 10
比如:從tb表中找到classId出現超過5次的紀錄
select classId
from tb
group by classId
having count(*) > 5;
SPECIAL EXAMPLE :
列出:每單個導演以及他的電影數量(不允許有重複導演名的行數出現)
select *,count(director)
from movies
where 1
group by director; //此句代表把所有相同導演名歸類到一行,不會出現重複姓名的行
——————————————————————
Id Title Director Year Length_minutes Count(Director)
9 WALL-E Andrew Stanton 2008 104 2
8 Ratatouille Brad Bird 2007 115 2
13 Brave Brenda Chapman 2012 102 1
14 Monsters University Dan Scanlon 2013 110 1
12 Cars 2 John Lasseter 2011 120 5
11 Toy Story 3 Lee Unkrich 2010 103 1
10 Up Pete Docter 2009 101 2
特别提醒:
如表所示,count(director)操作 得到的新column名為:count(director)
可以自定義改名
方式為:select *,count(director) AS 新名,由此得到新column名即為:新名
列出電影數量最多的三位導演
select director ,count(director) as count
from movies
where 1
group by director
order by count desc limit 3;
——————————————————————
Director Count
John Lasseter 5
Andrew Stanton 2
Brad Bird 2
—————————————————————————————————————————
SQL Review: Simple SELECT Queries
以這張表作範本:
Table: North_american_cities
City Country Population Latitude Longitude
Guadalajara Mexico 1500800 20.659699 -103.349609
Toronto Canada 2795060 43.653226 -79.383184
Houston United States 2195914 29.760427 -95.369803
New York United States 8405837 40.712784 -74.005941
Philadelphia United States 1553165 39.952584 -75.165222
Havana Cuba 2106146 23.05407 -82.345189
Mexico City Mexico 8555500 19.432608 -99.133208
Phoenix United States 1513367 33.448377 -112.074037
Los Angeles United States 3884307 34.052234 -118.243685
Ecatepec de Morelos Mexico 1742000 19.601841 -99.050674
Montreal Canada 1717767 45.501689 -73.567256
Chicago United States 2718782 41.878114 -87.629798
List all the Canadian cities and their populations
列出所有加拿大的城市和人口
select city,country,population
from north_american_cities
where country = "Canada";
——————————————————————
City Country Population
Toronto Canada 2795060
Montreal Canada 1717767
Order all the cities in the United States by their latitude from north to south
列出所有美國城市,根據latitude,從北到南
select city,country,latitude
from north_american_cities
where country = "United States"
order by latitude desc;
——————————————————————
省略
INSERT
INSERT INTO 表名
VALUES(col1值 , col2值 , col3值 , col4值 );
SQL Lesson 6: Multi-table queries with JOINs
JOIN 連表 (table)
當查找的資料在多張關聯table裡
通俗地說:把多張表合并成一張表
select *
from table1 left join table2 on table.id = table2.id
where 1 ;
需要注意:。。。
Operator Condition SQL Example
JOIN .. ON .. 依據ID組成1個表 t1 JOIN t2 ON t1.id = t2.id
INNER JOIN 隻保留2個表相同ID的ROW t1 INNER JOIN t2 後面可接 ON t1.id = t2.id
LEFT JOIN 保留t1所有的ROW t1 LEFT JOIN t2 後面可接 ON t1.id = t2.id // 讓t1表和t2表的id相等作為依據
RIGHT JOIN 保留t2所有的ROW t1 RIGHT JOIN t2 後面可接 ON t1.id = t2.id // 讓t1表和t2表的id相等作為依據
IS/IS NOT NULL col是否為NULL col IS / IS NOT NULL
詳細解釋:
A LEFT JOIN B:以左邊這張表為标準;即上方的select col是以左邊的A表為标準。
ON後寫兩個表的column相等,兩個column前記得要添加表名+"."!!
以這2張表作範本:
Table: Movies (Read-Only)
Id Title Director Year Length_minutes
1 Toy Story John Lasseter 1995 81
2 A Bug's Life John Lasseter 1998 95
3 Toy Story 2 John Lasseter 1999 93
4 Monsters, Inc. Pete Docter 2001 92
...
Table: Boxoffice (Read-Only)
Movie_id Rating Domestic_sales International_sales
5 8.2 380843261 555900000
14 7.4 268492764 475066843
8 8 206445654 417277164
12 6.4 191452396 368400000
3 7.9 245852179 239163000
6 8 261441092 370001000
...
Find the domestic and international sales for each movie
找到每一部電影的國内和國際銷售額
SELECT title,Domestic_sales,International_sales
FROM movies left join Boxoffice on movies.id = Boxoffice.movie_id
where 1;
——————————————————————
Title Domestic_sales International_sales
Toy Story 191796233 170162503
A Bug's Life 162798565 200600000
Toy Story 2 245852179 239163000
。。。
——————————————————————
UPDATE
UPDATE 表名
SET 列名 = "更改後的參數" , 列名2 = "更改後的參數" // 這裡不能用AND
WHERE ID = 需更改參數的行ID ;
注意!
update多個參數時,set語句中:每個參數要寫完整的一句,不能求友善而統一寫,比如:
update 表名
set title = "A",dir = "B"
where id = X;
// 不能寫 set title,dir = "A","B"
// update的set中隻能用逗号分割,不能用and分割!!
以這張表作範本:
Id Title Director Year Length_minutes
1 Toy Story John Lasseter 1995 81
2 A Bug's Life El Directore 1998 95
3 Toy Story 2 John Lasseter 1899 93
4 Monsters, Inc. Pete Docter 2001 92
5 Finding Nemo Andrew Stanton 2003 107
6 The Incredibles Brad Bird 2004 116
7 Cars John Lasseter 2006 117
8 Ratatouille Brad Bird 2007 115
9 WALL-E Andrew Stanton 2008 104
10 Up Pete Docter 2009 101
The director for A Bug’s Life is incorrect, it was actually directed by John Lasseter
把A Bug’s Life的導演名改成John Lasseter
UPDATE movies
SET director = "John Lasseter"
WHERE id = 2;
——————————————————————
Id Title Director Year Length_minutes
1 Toy Story John Lasseter 1995 81
2 A Bug's Life John Lasseter 1998 95
3 Toy Story 2 John Lasseter 1999 93
4 Toy Story 4 El Directore 2015 90
Both the title and directory for Toy Story 8 is incorrect! The title should be “Toy Story 3” and it was directed by Lee Unkrich
把Toy Story 3的導演名改成Lee Unkrich
UPDATE movies
SET title = "Toy Story 3", director = "Lee Unkrich"
WHERE id = 11;
——————————————————————
省略
注意!!大坑!!
在一條UPDATE語句中,如果要更新多個字段,字段間不能使用
AND
,而應該用
逗号
分隔!!!!!!!!!!!!!!!
在一條UPDATE語句中,如果要更新多個字段,字段間不能使用
AND
,而應該用
逗号
分隔!!!!!!!!!!!!!!!
在一條UPDATE語句中,如果要更新多個字段,字段間不能使用
AND
,而應該用
逗号
分隔!!!!!!!!!!!!!!!
在一條UPDATE語句中,如果要更新多個字段,字段間不能使用
AND
,而應該用
逗号
分隔!!!!!!!!!!!!!!!
在一條UPDATE語句中,如果要更新多個字段,字段間不能使用
AND
,而應該用
逗号
分隔!!!!!!!!!!!!!!!
在一條UPDATE語句中,如果要更新多個字段,字段間不能使用
AND
,而應該用
逗号
分隔!!!!!!!!!!!!!!!
——————————————————————
DELETE
DELETE FROM 表名
where 列的限制條件;
以movies表作範本:
This database is getting too big, lets remove all movies that were released before 2005.
把2005年前發行的所有電影都删除
delete from movies
where year < 2005;
——————————————————————
省略
注意!!大坑!!
删除方法無需再寫
delete * from ...
,直接寫
delete from ...
就好
删除方法無需再寫
delete * from ...
,直接寫
delete from ...
就好
删除方法無需再寫
delete * from ...
,直接寫
delete from ...
就好
删除方法無需再寫
delete * from ...
,直接寫
delete from ...
就好
删除方法無需再寫
delete * from ...
,直接寫
delete from ...
就好
——————————————————————
Creating tables
資料類型 Data type :
INTEGER, BOOLEAN
The integer datatypes can store whole integer values like the count of a number or an age. In some implementations, the boolean value is just represented as an integer value of just 0 or 1.
FLOAT, DOUBLE, REAL
The floating point datatypes can store more precise numerical data like measurements or fractional values. Different types can be used depending on the floating point precision required for that value.
CHARACTER(num_chars), VARCHAR(num_chars), TEXT
The text based datatypes can store strings and text in all sorts of locales. The distinction between the various types generally amount to underlaying efficiency of the database when working with these columns.
Both the CHARACTER and VARCHAR (variable character) types are specified with the max number of characters that they can store (longer values may be truncated), so can be more efficient to store and query with big tables.
DATE, DATETIME
SQL can also store date and time stamps to keep track of time series and event data. They can be tricky to work with especially when manipulating data across timezones.
BLOB
Finally, SQL can store binary data in blobs right in the database. These values are often opaque to the database, so you usually have to store them with the right metadata to requery them.
限制:保證資料的完整性
Table Constraints (Constraint & Description)
PRIMARY KEY
This means that the values in this column are unique, and each value can be used to identify a single row in this table.
表示參數隻能唯一存在,可以用來确定單個行(常用于定義ID列),PRIMARY KEY的組不可為NULL,一張表隻能有一個PRIMARY KEY;
AUTOINCREMENT
For integer values, this means that the value is automatically filled in and incremented with each row insertion. Not supported in all databases.
對于INTEGER來說,他代表參數可以自增長(不适用于所有資料庫,如PGsql);
UNIQUE
This means that the values in this column have to be unique, so you can't insert another row with the same value in this column as another row in the table. Differs from the `PRIMARY KEY` in that it doesn't have to be a key for a row in the table.(可定義電話号碼)
表示唯一參數,與PRIMARY KEY不同之處:
PRIMARY = UNIQUE + NOT NULL
一張表隻能有一個PRIMARY KEY;而UNIQUE可有多個,可為NULL值。
NOT NULL
This means that the inserted value can not be `NULL`.
CHECK (expression)
This is allows you to run a more complex expression to test whether the values inserted are value. For example, you can check that values are positive, or greater than a specific size, or start with a certain prefix, etc.
FOREIGN KEY
This is a consistency check which ensures that each value in this column corresponds to another value in a column in another table.
For example, if there are two tables, one listing all Employees by ID, and another listing their payroll information, the `FOREIGN KEY` can ensure that every row in the payroll table corresponds to a valid employee in the master Employee list.
FORMAT:
CREATE TABLE 表名 (
列名 資料類型 限制 , //每一行後都以一個逗号結尾
列名 資料類型 限制 ,
列名 資料類型 限制 //最後一行不要加逗号
);
EXAMPLE:
Movies table schema
CREATE TABLE movies (
id INTEGER PRIMARY KEY,
title TEXT,
director TEXT,
year INTEGER,
length_minutes INTEGER
);
Create a new table named Database with the following columns:
– Name A string (text) describing the name of the database
– Version A number (floating point) of the latest version of this database
– Download_count An integer count of the number of times this database was downloaded
This table has no constraints.
CREATE TABLE Database (
Name TEXT,
Version FLOAT,
Download_count INTEGER
);
——————————————————————
Name Version Download_count
SQLite 3.9 92000000
MySQL 5.5 512000000
Postgres 9.4 384000000
——————————————————————
Altering tables(列的增加、修改、删除)
改變表格:比如列的增加、修改、删除
FORMAT:
ALTER TABLE 表名
ADD COLUMN 列名 資料類型 DEFAULT 2.39;
增加一條列
ALTER TABLE 表名
ADD COLUMN 列名 資料類型 ;
修改列類型
ALTER TABLE 表名
MODIFY 列名 資料類型 ;
修改列名
ALTER TABLE 表名
CHANGE 列名 新列名 資料類型 ;
删除一條列(不能同時删除多條)
ALTER TABLE 表名
DROP 列名 ;
以movies表作範本:
Table: Movies
Id Title Director Year Length_minutes
1 Toy Story John Lasseter 1995 81
2 A Bug's Life John Lasseter 1998 95
3 Toy Story 2 John Lasseter 1999 93
Add a column named Aspect_ratio with a FLOAT data type to store the aspect-ratio each movie was released in.
ALTER TABLE Movies
ADD COLUMN Aspect_ratio FLOAT DEFAULT 2.39;
——————————————————————
Id Title Director Year Length_minutes Aspect_ratio
1 Toy Story John Lasseter 1995 81 2.39
2 A Bug's Life John Lasseter 1998 95 2.39
3 Toy Story 2 John Lasseter 1999 93 2.39
Add another column named Language with a TEXT data type to store the language that the movie was released in. Ensure that the default for this language is English.
ALTER TABLE Movies
ADD COLUMN Language TEXT DEFAULT "English";
——————————————————————
省略
——————————————————————
Distinct
在表中,可能會包含重複值,這并不成問題。
不過,有時您也許希望僅僅列出不同(distinct)的值。關鍵詞 distinct用于傳回唯一不同的值。
select distinct name from A;
——————————————————————
name
a
b
c
如果select distinct id,name from A;?出現的是唯一的id還是唯一的name?
都不是,是唯一的id+name組合:
select distinct id,name from A;
——————————————————————
name id
a 1
b 2
b 5
c 3
c 4
注!不可以寫select id,distinct name
distinct隻可以寫在參數最前面。
——————————————————————
Dropping tables (注!實際工作中謹慎操作)
FORMAT :
DROP TABLE 表名;
——————————————————————
1.NOT IN和NOT EXISTS的差別
無論哪個表,用not exists都比not in 要快;
且如果資料中有null,not in是查不到的,用not exists就可以;
——————————————————————
踩坑記錄
-
1.建立成績表( SC):參數為SNO,CNO,SCGRADE (代表:學号,課号,成績);
一名學生需要填寫至少2門課号(後續需要“分别比對課号和成績”進行操作),建表和填資料的格式是什麼?
一開始我了解的重點是:需要結合課号和成績,于是産生了兩種思維:
一個CNO空格内并排放入兩個參數;SCGRADE也一樣,互相對應。
——————————————————
SNO CNO SCGRADE
1 A/B 90/95
2 A/C 87/96
但從沒見過一個空格内可以存在多個參數這種說法,這樣也可能導緻與成績比對時的邏輯混亂;
第二種格式:
建立兩個CNO和兩個SCGRADE,互相對應。
——————————————————
SNO CNO1 CNO2 SCGRADE1 SCGRADE2
1 A B 90 95
2 A C 87 96
但這樣就需要把CNO1和SCGRADE1對應起來分析,但也沒見過這種說法。
想了很久也沒有頭緒,後來經過朋友的分析才知道,這兩種方法都不對。
應該為:
SNO CNO SCGRADE
1 A 90
1 B 95
2 A 87
2 C 96
——————————————————————
習題
C 課程;
+------+------------+----------+
| cno | cname | cteacher |
+------+------------+----------+
| 105 | Language | Liu |
| 106 | Literature | Liu |
| 107 | Art | Cheung |
| 108 | IT | Lee |
| 109 | Sport | Ho |
+------+------------+----------+
S 學生;
+------+------+------+
| sno | sname| sage |
+------+------+------+
| 1 | wang | 20 |
| 2 | kwok | 19 |
| 3 | chan | 18 |
| 5 | lam | 22 |
| 4 | pang | 22 |
+------+------+------+
SC 學生成績;
+------+------+------+---------+
| id | sno | cno | scgrade |
+------+------+------+---------+
| 1 | 1 | 106 | 55 |
| 2 | 2 | 107 | 80 |
| 3 | 3 | 108 | 55 |
| 4 | 4 | 109 | 66 |
| 5 | 5 | 105 | 90 |
| 6 | 1 | 105 | 55 |
| 7 | 2 | 105 | 59 |
| 8 | 3 | 105 | 80 |
| 9 | 4 | 105 | 90 |
| 10 | 5 | 106 | 83 |
| 11 | 1 | 107 | 66 |
| 12 | 1 | 108 | 85 |
| 13 | 1 | 109 | 40 |
+------+------+------+---------+
問題與答案:
1)找出沒選過“cheung”老師的所有學生姓名。
select sname
from S
where sno not in(
select sno
from SC
left join C
on SC.cno = C.cno
where C.cteacher = ’cheung‘
);
或者
select sname
from S
where S.sno not in(
select sno
from SC
where cno not in(
select cno
from C
where cteacher not like 'Cheung'
)
);
2) 列出既學過105号課程又學過106号課所有學生的姓名。
正确答案:
select S.sno,S.sname
from SC,S
where SC.cno=105 and SC.sno in(
select sno
from SC
where cno=106) and S.sno=SC.sno;
或(個人更推薦)
select S.sno,S.sname
from S
left join SC
on S.sno = SC.sno
where SC.cno=105 and SC.sno in(
select sno
from SC
where cno=106
);
要點在于:
cno要滿足=105和106時,隻能直接寫一個條件,另一個條件要寫在in()中;
且in()條件的指定屬性要根據括号内的條件來寫,比如括号内寫cno=106,那麼指定屬性就不能寫cno,可以寫sno;
我寫的:
select S.name
from S left join SC on S.sno=SC.sno
where SC.cno in(105,106);
差別:
我寫的:直接把105和106當作合集,限定課号在這裡面;
正确答案:限定課号為105,“and”課号限定在(一組新的完整select from where語句:限定課号為106)。
3) 列出2門(含2)以上不及格課程的學生姓名;及其平均成績(要求有兩門(含)以上不及格的人)。
select sname,avg(SC.scgrade) as 新列名
from S
left join SC
on S.sno = SC.sno
where SC.scgrade<60
group by S.sname
having count(1)>=2;
易錯點:
容易忘掉:
新列的命名/改名方式:
avg(SC.scgrade) as 新名字
left join
on XX = XX(這裡寫成了where)
where…
以下這兩個搭配易搞錯:
group by
having count(*)
group by
order by desc/asc limit ?
4).所有課程都選修的學生學号和姓名
select sno,sname
from S
where not exists(
select *
from C
where not exists(
select *
from SC
where SC.sno = S.sno and SC.cno = C.cno)
);
易錯點:
not exists 後面沒有 in!!!
答案:
1 wang
5)檢索選修課程包含LIU老師所授課程的學生學号。
select distinct SC.sno
from SC,C
where SC.cno = C.cno and C.cteacher="LIU";
不寫distinct的話會有多名重複學生;
自己寫的:
select distinct sname
from S
where sno in(
select sno
from SC
left join C
on SC.cno = C.cno
where C.cteacher = "Liu"
);
select S.sno
from S
left join SC
on S.sno = SC.sno
where SC.cno in(
select cno
from C
where cteacher like 'Liu'
);
6)檢索學号比WANG同學大,而年齡比他小的學生姓名。
select sname
from S
where sno > (
select sno
from S
where sname like'wang'
)and sage < (
select sage
from S
where sname like 'wang'
);
7)找到sno為1的學生沒有選修的課程
我寫的:
select distinct cname
from C
left join SC
on C.cno = SC.cno
where sno not like '1';
——————————————————————
遇到的一些特殊情況:
1.
select count(1) from user where id=2
count(1)括号中填1或2或3都不影響結果,這幾話的意思是select出符合标準的多行資料;
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
——————————————————————
一些附錄:
## 資料庫
建立庫之前,先檢視資料庫中是否已存在,若有便删除。
if exists(select * from sysobjects where name =‘ConstructionDB’) --查找指令
drop DATABASE ConstructionDB --删除 指令