2.1 MySQL 基礎 (二)- 表操作
#學習内容#
-
MySQL表資料類型
MySQL支援多種類型,大緻可以分為三類:數值、日期/時間和字元串(字元)類型。
-
用SQL語句建立表
語句解釋:建立MySQL資料表需要以下資訊:表名,表字段名,定義每個表字段,通用文法為:CREATE TABLE table_name (column_name column_type);
設定列類型 、大小、限制和主鍵可以參考下面示例:
-
用SQL語句向表中添加資料
語句解釋:MySQL 表中使用 INSERT INTO SQL語句來插入資料,通用文法為:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
多種添加方式可參考如下示例:
-
用SQL語句删除表
語句解釋:主要有三種方式 delete, drop, truncate, 這三個的差別及用法如下:
-
用SQL語句修改表
修改列名 :ALTER TABLE 表名 RENAME COLUMN 舊列名 TO 新列名;
修改表中資料:UPDATE 表名 SET column1=value1,column2=value2,… WHERE 條件;
删除行:DELETE FROM 表名 WHERE some_column=some_value;
删除列:ALTER TABLE 表名 DROP COLUMN 列名;
建立列:
(1)alter table 表名 add column 列名 varchar(20) not null;–插入在最後一列
(2)alter table 表名 add column 列名 varchar(20) not null after column1;–插在column1列後面
(3)alter table 表名 add column 列名 varchar(20) not null first;–插入到第一列
建立行:INSERT INTO 表名 VALUES (value1,value2,value3,…);
作業
項目三:超過5名學生的課(難度:簡單)
建立如下所示的courses 表 ,有: student (學生) 和 class (課程)。
例如,表:
±--------±-----------+
| student | class |
±--------±-----------+‘’
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
| A | Math |
±--------±-----------+
編寫一個 SQL 查詢,列出所有超過或等于5名學生的課。
應該輸出:
±--------+
| class |
±--------+
| Math |
±--------+
Note:
學生在每個課中不應被重複計算。
答案
項目四:交換工資(難度:簡單)
建立一個 salary表,如下所示,有m=男性 和 f=女性的值 。
例如:
id | name | sex | salary |
---|---|---|---|
1 | A | m | 2500 |
2 | B | f | 1500 |
3 | C | m | 5500 |
4 | D | f | 500 |
交換所有的 f 和 m 值(例如,将所有 f 值更改為 m,反之亦然)。要求使用一個更新查詢,并且沒有中間臨時表。
運作你所編寫的查詢語句之後,将會得到以下表:
id | name | sex | salary |
---|---|---|---|
1 | A | f | 2500 |
2 | B | m | 1500 |
3 | C | f | 5500 |
4 | D | m | 500 |
答案
2.2 MySQL 基礎 (三)- 表聯結
#學習内容#
MySQL别名:
(1):為表取别名:select * from 表名 as 别名;
(2):為字段取别名:select 字段名 as 别名, 字段名 as 别名 from 表名;
INNER JOIN:内連接配接同join。 常用表達式為: select * from 表名1 inner join 表名2 on 表名1.列名 = 表名2.列名;
LEFT JOIN:顧名思義,左連接配接,以左邊的為準
CROSS JOIN:CROSS JOIN 把表A和表B的資料進行一個N*M的組合,即笛卡爾積。
SELECT * FROM TableA CROSS JOIN TableB ;
假設集合A={a,b},集合B={0,1,2},則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),
(b,2)}。可以擴充到多個集合的情況。類似的例子有,如果A表示某學校學生的集合,B表示該學校所有課程的集合,則A與B的笛卡爾積表示所有可能的選課情況。
自連接配接:自連接配接就是一個表和它自身進行連接配接,是多表連接配接的特殊情況。在自連接配接查詢中,要先在FROM字句中為表分别定義兩個不同的别名,然後使用這兩個别名寫出一個連接配接條件,條件比較在同一個表中的情況。
SELECT * FROM Course c1 JOIN Course c2 ON c1.Kind=c2.Kind AND c1.DepartNo!=c2.DepartNo
ORDER BY c1.CouNo;
UNION:union 組合出來的沒有重複項, union all 組合出來的是全部包含重複項。
作業
項目五:組合兩張表 (難度:簡單)
在資料庫中建立表1和表2,并各插入三行資料(自己造)
表1: Person
±------------±--------+
| 列名 | 類型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主鍵
表2: Address
±------------±--------+
| 列名 | 類型 |
±------------±--------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主鍵
編寫一個 SQL 查詢,滿足條件:無論 person 是否有位址資訊,都需要基于上述兩表提供 person 的以下資訊:FirstName, LastName, City, State
答案
項目六:删除重複的郵箱(難度:簡單)
編寫一個 SQL 查詢,來删除 email 表中所有重複的電子郵箱,重複的郵箱裡隻保留 Id 最小 的那個。
±—±--------+
| Id | Email |
±—±--------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
±—±--------+
Id 是這個表的主鍵。
例如,在運作你的查詢語句之後,上面的 Person表應傳回以下幾行:
±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | [email protected] |
| 2 | [email protected] |
±—±-----------------+
答案