#頭條創作挑戰賽#
上一篇記錄:記錄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) * 每頁顯示的條數