#頭條創作挑戰賽#
上一篇記錄：記錄01 資料庫、MySQL、SQL簡介
1.4 DDL:操作資料庫
DDL操作資料庫，主要是對資料庫的增删查操作。
1.4.1 查詢
查詢所有的資料庫
SHOW DATABASES; 運作上面語句效果如下：
上述查詢到的是的這些資料庫是mysql安裝好自帶的資料庫，以後不要操作這些資料庫。
1.4.2 建立資料庫
- 建立資料庫：
CREATE DATABASE 資料庫名稱; 運作語句效果如下：
而在建立資料庫的時候，我并不知道db1資料庫有沒有建立，直接再次建立名為db1的資料庫就會出現錯誤。
為了避免上面的錯誤，在建立資料庫的時候先做判斷，如果不存在再建立。
- 建立資料庫(判斷，如果不存在則建立)
CREATE DATABASE IF NOT EXISTS 資料庫名稱; 運作語句效果如下：
從上面的效果可以看到雖然db1資料庫已經存在，再建立db1也沒有報錯，而建立db2資料庫則建立成功。
1. 4.3 删除資料庫
- 删除資料庫
DROP DATABASE 資料庫名稱; - 删除資料庫(判斷，如果存在則删除)
DROP DATABASE IF EXISTS 資料庫名稱; 運作語句效果如下：
1.4.4 使用資料庫
資料庫建立好了，要在資料庫中建立表，得先明确在哪兒個資料庫中操作，此時就需要使用資料庫。
- 使用資料庫
USE 資料庫名稱; - 檢視目前使用的資料庫
SELECT DATABASE(); 運作語句效果如下：
1.5 DDL:操作表
操作表也就是對表進行增（Create）删（Retrieve）改（Update）查（Delete）。
1. 5.1 查詢表
- 查詢目前資料庫下所有表名稱
SHOW TABLES; 我們建立的資料庫中沒有任何表，是以我們進入mysql自帶的mysql資料庫，執行上述語句檢視
- 查詢表結構
DESC 表名稱; 檢視mysql資料庫中func表的結構，運作語句如下：
1.5.2 建立表
- 建立表
CREATE TABLE 表名 (
字段名1 資料類型1,
字段名2 資料類型2,
…
字段名n 資料類型n
);
注意：最後一行末尾，不能加逗号
知道了建立表的語句，那麼我們建立建立如下結構的表
create table tb_user (
id int,
username varchar(20),
password varchar(32)
); 運作語句如下：
1.5.3 資料類型
MySQL 支援多種類型，可以分為三類：
- 數值
-
tinyint : 小整數型，占一個位元組
int ： 大整數類型，占四個位元組
eg ： age int
double ： 浮點類型
使用格式： 字段名 double(總長度,小數點後保留的位數)
eg ： score double(5,2)
- 日期
-
date ： 日期值。隻包含年月日
eg ：birthday date ：
datetime ： 混合日期和時間值。包含年月日時分秒
- 字元串
-
char ： 定長字元串。
優點：存儲性能高
缺點：浪費空間
eg ： name char(10) 如果存儲的資料字元個數不足10個，也會占10個的空間
varchar ： 變長字元串。
優點：節約空間
缺點：存儲性能底
eg ： name varchar(10) 如果存儲的資料字元個數不足10個，那就資料字元個數是幾就占幾個的空間
注意：其他類型參考資料中的《MySQL資料類型].xlsx》
案例：
需求：設計一張學生表，請注重資料類型、長度的合理性
1. 編号
2. 姓名，姓名最長不超過10個漢字
3. 性别，因為取值隻有兩種可能，是以最多一個漢字
4. 生日，取值為年月日
5. 入學成績，小數點後保留兩位
6. 郵件位址，最大長度不超過 64
7. 家庭聯系電話，不一定是手機号碼，可能會出現 - 等字元
8. 學生狀态（用數字表示，正常、休學、畢業...） 語句設計如下：
create table student (
id int,
name varchar(10),
gender char(1),
birthday date,
score double(5,2),
email varchar(15),
tel varchar(15),
status tinyint
); 1.5.4 删除表
- 删除表
DROP TABLE 表名; - 删除表時判斷表是否存在
DROP TABLE IF EXISTS 表名; 運作語句效果如下：
1.5.5 修改表
- 修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
-- 将表名student修改為stu
alter table student rename to stu; - 添加一列
ALTER TABLE 表名 ADD 列名 資料類型;
-- 給stu表添加一列address，該字段類型是varchar(50)
alter table stu add address varchar(50); - 修改資料類型
ALTER TABLE 表名 MODIFY 列名 新資料類型;
-- 将stu表中的address字段的類型改為 char(50)
alter table stu modify address char(50); - 修改列名和資料類型
ALTER TABLE 表名 CHANGE 列名 新列名 新資料類型;
-- 将stu表中的address字段名改為 addr，類型改為varchar(50)
alter table stu change address addr varchar(50); - 删除列
ALTER TABLE 表名 DROP 列名;
-- 将stu表中的addr字段 删除
alter table stu drop addr; 1. 6 navicat使用
通過上面的學習，我們發現在指令行中寫sql語句特别不友善，尤其是編寫建立表的語句，我們隻能在記事本上寫好後直接複制到指令行進行執行。那麼有沒有剛好的工具提供給我們進行使用呢？ 有。
1.6.1 navicat概述
- Navicat for MySQL 是管理和開發 MySQL 或 MariaDB 的理想解決方案。
- 這套全面的前端工具為資料庫管理、開發和維護提供了一款直覺而強大的圖形界面。
- 官網： http://www.navicat.com.cn
1.6.2 navicat安裝
參考 : 資料\navicat安裝包\navicat_mysql_x86\navicat安裝步驟.md
1.6.3 navicat使用
1. 建立和mysql服務的連接配接
第一步： 點選連接配接，選擇MySQL
第二步：填寫連接配接資料庫必要的資訊
以上操作沒有問題就會出現如下圖所示界面：
2. 操作
連接配接成功後就能看到如下圖界面：
- 修改表結構
通過下圖操作修改表結構：
點選了設計表後即出現如下圖所示界面，在圖中紅框中直接修改字段名，類型等資訊：
- 編寫SQL語句并執行
按照如下圖所示進行操作即可書寫SQL語句并執行sql語句。
1.7 DML
DML主要是對資料進行增（insert）删（delete）改（update）操作。
1.7.1 添加資料
- 給指定列添加資料
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…); - 給全部列添加資料
INSERT INTO 表名 VALUES(值1,值2,…); - 批量添加資料
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…; - 練習
為了示範以下的增删改操作是否操作成功，故先将查詢所有資料的語句介紹給大家：
select * from stu; -- 給指定列添加資料
INSERT INTO stu (id, NAME) VALUES (1, '張三');
-- 給所有列添加資料，列名的清單可以省略的
INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1);
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1);
-- 批量添加資料
INSERT INTO stu VALUES
(2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1),
(2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1),
(2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1); 1.7.2 修改資料
- 修改表資料
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 條件] ; 注意：
修改語句中如果不加條件，則将所有資料都修改！
像上面的語句中的中括号，表示在寫sql語句中可以省略這部分
- 練習
- 将張三的性别改為女
- update stu set sex = '女' where name = '張三';
- 将張三的生日改為 1999-12-12 分數改為99.99
- update stu set birthday = '1999-12-12', score = 99.99 where name = '張三';
- 注意：如果update語句沒有加where條件，則會将表中所有資料全部修改！
- update stu set sex = '女';
- 上面語句的執行完後查詢到的結果是：
1.7.3 删除資料
- 删除資料
DELETE FROM 表名 [WHERE 條件] ; - 練習
-- 删除張三記錄
delete from stu where name = '張三';
-- 删除stu表中所有的資料
delete from stu; 1.8 DQL
下面是展示試題庫資料的頁面
頁面上展示的資料肯定是在資料庫中的試題庫表中進行存儲，而我們需要将資料庫中的資料查詢出來并展示在頁面給使用者看。上圖中的是最基本的查詢效果，那麼資料庫其實是很多的，不可能在将所有的資料在一頁進行全部展示，而頁面上會有分頁展示的效果，如下：
當然上圖中的難度字段當我們點選也可以實作排序查詢操作。從這個例子我們就可以看出，對于資料庫的查詢時靈活多變的，需要根據具體的需求來實作，而資料庫查詢操作也是最重要的操作，是以此部分需要大家重點掌握。
接下來我們先介紹查詢的完整文法：
SELECT
字段清單
FROM
表名清單
WHERE
條件清單
GROUP BY
分組字段
HAVING
分組後條件
ORDER BY
排序字段
LIMIT
分頁限定 為了給大家示範查詢的語句，我們需要先準備表及一些資料：
-- 删除stu表
drop table if exists stu;
-- 建立stu表
CREATE TABLE stu (
id int, -- 編号
name varchar(20), -- 姓名
age int, -- 年齡
sex varchar(5), -- 性别
address varchar(100), -- 位址
math double(5,2), -- 數學成績
english double(5,2), -- 英語成績
hire_date date -- 入學時間
);
-- 添加資料
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date)
VALUES
(1,'馬運',55,'男','杭州',66,78,'1995-09-01'),
(2,'馬花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'馬斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'劉德花',57,'男','香港',99,99,'1998-09-01'),
(7,'張學右',22,'女','香港',99,99,'1998-09-01'),
(8,'德瑪西亞',18,'男','南京',56,65,'1994-09-02'); 接下來咱們從最基本的查詢語句開始學起。
1.8.1 基礎查詢
1. 文法
- 查詢多個字段
SELECT 字段清單 FROM 表名;
SELECT * FROM 表名; -- 查詢所有資料 - 去除重複記錄
SELECT DISTINCT 字段清單 FROM 表名; - 起别名
AS: AS 也可以省略 2. 練習
- 查詢name、age兩列
- select name,age from stu;
- 查詢所有列的資料，列名的清單可以使用*替代
- select * from stu;
- 上面語句中的*不建議大家使用，因為在這寫*不友善我們閱讀sql語句。我們寫字段清單的話，可以添加注釋對每一個字段進行說明
- 而在上課期間為了簡約課程的時間，老師很多地方都會寫*。
- 查詢位址資訊
- select address from stu;
- 執行上面語句結果如下：
- 從上面的結果我們可以看到有重複的資料，我們也可以使用 distinct 關鍵字去重重複資料。
- 去除重複記錄
- select distinct address from stu;
- 查詢姓名、數學成績、英語成績。并通過as給math和english起别名（as關鍵字可以省略）
-
select name,math as 數學成績,english as 英文成績 from stu;
select name,math 數學成績,english 英文成績 from stu;
1.8.2 條件查詢
1. 文法
SELECT 字段清單 FROM 表名 WHERE 條件清單; - 條件
條件清單可以使用以下運算符
2. 條件查詢練習
- 查詢年齡大于20歲的學員資訊
- select * from stu where age > 20;
- 查詢年齡大于等于20歲的學員資訊
- select * from stu where age >= 20;
- 查詢年齡大于等于20歲 并且 年齡 小于等于 30歲 的學員資訊
-
select * from stu where age >= 20 && age <= 30;
select * from stu where age >= 20 and age <= 30;
- 上面語句中 && 和 and 都表示并且的意思。建議使用 and 。
- 也可以使用 between ... and 來實作上面需求
- select * from stu where age BETWEEN 20 and 30;
- 查詢入學日期在'1998-09-01' 到 '1999-09-01' 之間的學員資訊
- select * from stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';
- 查詢年齡等于18歲的學員資訊
- select * from stu where age = 18;
- 查詢年齡不等于18歲的學員資訊
-
select * from stu where age != 18;
select * from stu where age <> 18;
- 查詢年齡等于18歲 或者 年齡等于20歲 或者 年齡等于22歲的學員資訊
-
select * from stu where age = 18 or age = 20 or age = 22;
select * from stu where age in (18,20 ,22);
- 查詢英語成績為 null的學員資訊
- null值的比較不能使用 = 或者 != 。需要使用 is 或者 is not
-
select * from stu where english = null; -- 這個語句是不行的
select * from stu where english is null;
select * from stu where english is not null;
3. 模糊查詢練習
模糊查詢使用like關鍵字，可以使用通配符進行占位:
（1）_ : 代表單個任意字元
（2）% : 代表任意個數字元
- 查詢姓'馬'的學員資訊
- select * from stu where name like '馬%';
- 查詢第二個字是'花'的學員資訊
- select * from stu where name like '_花%';
- 查詢名字中包含 '德' 的學員資訊
- select * from stu where name like '%德%';
1.8.3 排序查詢
1. 文法
SELECT 字段清單 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …; 上述語句中的排序方式有兩種，分别是：
- ASC ： 升序排列 （預設值）
- DESC ： 降序排列
注意：如果有多個排序條件，目前邊的條件值一樣時，才會根據第二條件進行排序
2. 練習
- 查詢學生資訊，按照年齡升序排列
- select * from stu order by age ;
- 查詢學生資訊，按照數學成績降序排列
- select * from stu order by math desc ;
- 查詢學生資訊，按照數學成績降序排列，如果數學成績一樣，再按照英語成績升序排列
- select * from stu order by math desc , english asc ;
1.8.4 聚合函數
1. 概念
将一列資料作為一個整體，進行縱向計算。
如何了解呢？假設有如下表
現有一需求讓我們求表中所有資料的數學成績的總和。這就是對math字段進行縱向求和。
2. 聚合函數分類
| 函數名 | 功能 |
| count(列名) | 統計數量（一般選用不為null的列） |
| max(列名) | 最大值 |
| min(列名) | 最小值 |
| sum(列名) | 求和 |
| avg(列名) | 平均值 |
3. 聚合函數文法
SELECT 聚合函數名(列名) FROM 表; 注意：null 值不參與所有聚合函數運算
4. 練習
- 統計班級一共有多少個學生
-
select count(id) from stu;
select count(english) from stu;
- 上面語句根據某個字段進行統計，如果該字段某一行的值為null的話，将不會被統計。是以可以在count(*) 來實作。* 表示所有字段資料，一行中也不可能所有的資料都為null，是以建議使用 count(*)
- select count(*) from stu;
- 查詢數學成績的最高分
- select max(math) from stu;
- 查詢數學成績的最低分
- select min(math) from stu;
- 查詢數學成績的總分
- select sum(math) from stu;
- 查詢數學成績的平均分
- select avg(math) from stu;
- 查詢英語成績的最低分
- select min(english) from stu;
1.8.5 分組查詢
1. 文法
SELECT 字段清單 FROM 表名 [WHERE 分組前條件限定] GROUP BY 分組字段名 [HAVING 分組後條件過濾]; 注意：分組之後，查詢的字段為聚合函數和分組字段，查詢其他字段無任何意義
2. 練習
- 查詢男同學和女同學各自的數學平均分
- select sex, avg(math) from stu group by sex;
- 注意：分組之後，查詢的字段為聚合函數和分組字段，查詢其他字段無任何意義
- select name, sex, avg(math) from stu group by sex; -- 這裡查詢name字段就沒有任何意義
- 查詢男同學和女同學各自的數學平均分，以及各自人數
- select sex, avg(math),count(*) from stu group by sex;
- 查詢男同學和女同學各自的數學平均分，以及各自人數，要求：分數低于70分的不參與分組
- select sex, avg(math),count(*) from stu where math > 70 group by sex;
- 查詢男同學和女同學各自的數學平均分，以及各自人數，要求：分數低于70分的不參與分組，分組之後人數大于2個的
- select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*) > 2;
where 和 having 差別：
- 執行時機不一樣：where 是分組之前進行限定，不滿足where條件，則不參與分組，而having是分組之後對結果進行過濾。
- 可判斷的條件不一樣：where 不能對聚合函數進行判斷，having 可以。
1.8.6 分頁查詢
如下圖所示，很多網站都見過類似的效果，如京東、百度、淘寶等。分頁查詢是将資料一頁一頁的展示給使用者看，使用者也可以通過點選檢視下一頁的資料。
1. 文法
SELECT 字段清單 FROM 表名 LIMIT 起始索引 , 查詢條目數; 注意： 上述語句中的起始索引是從0開始
2. 練習
- 從0開始查詢，查詢3條資料
- select * from stu limit 0 , 3;
- 每頁顯示3條資料，查詢第1頁資料
- select * from stu limit 0 , 3;
- 每頁顯示3條資料，查詢第2頁資料
- select * from stu limit 3 , 3;
- 每頁顯示3條資料，查詢第3頁資料
- select * from stu limit 6 , 3;
從上面的練習推導出起始索引計算公式：
起始索引 = (目前頁碼 - 1) * 每頁顯示的條數