MySQL數據庫day7.11

一，SQL概述

1.1?SQL語句語法

MySQL 數據庫的 SQL 語句不區分大小寫，關鍵字建議使用大寫，

以分號結尾。例如：

SELECT * FROM user;

使用 /**/ 、 -- 、 # 的方式完成注釋

/*
多行注釋
*/
-- 單行注釋
# 單行注釋
SELECT * FROM user;

1.2?SQL中數據的常用數據類型

固定長度 char(n) 例如 : char(20), 最大能存放 20 個字符 . ‘aaa’,

還是占 20 個字符的空間

可變長度 varchar(n) 例如 :varchar(20), 最大能存放 20 個字符 .

‘aaa’, 占 3 個字符的空間

一般使用 varchar(n) 節省空間 ; 如果長度 ( 身份證 ) 是固定的話可

以使用 char(n) 性能高一點

二·，DDL操作數據庫

2.1 創建數據庫

? 語法

create database 數據庫名 [character set 字符集][collate校對規則] ? ?注: []意思是可選的意思

? 創建一個 day0708_1 的數據庫（默認字符集 )

create database day0708_1;

???創建一個day0708_2 的數據庫 , 指定字符集為 gbk( 了解 )

create database day0708_2 character set gbk;

2.2 查看所有的數據庫

? 語法：查看所有的數據庫

show databases;

? 語法：查看數據庫的定義結構【了解】

show create database 數據庫名;

-- 查看數據庫
-- 語法: show databases; 查詢所有的數據庫
show databases
-- 語法: show create database 數據庫名; 查看數據庫的定義
結構【了解】
show create database day0708_1
show create database day0708_2

2.3 刪除數據庫

? 語法

drop database 數據庫名;

-- 需求: 刪除day0708_2數據庫
drop database day0708_2;

2.4 修改數據庫【了解】

? 語法

alter database 數據庫名 character set 字符集;

-- 需求: 把day0708_2數據庫的字符集修改為utf8
alter database day0708_2 character set utf8

? 2.5 其他操作

? 切換數據庫：在創建表之前一定要指定數據庫。 use 數據庫名

use 數據庫名;

-- 語法: use 數據庫名; 選中數據庫
use day0708_1;
-- 語法: select database(); 查看當前選中(正在使用)的數據庫
select database();

三，DDL操作表

?3.1 創建表

創建表, 格式:create table 表名 (字段名 數據類型[長度] [約束],字段名 數據類型[長度] [約束],...); 注:[]中的內容是可選項

? 3.2 SQL約束

約束：即規則 , 規矩限制 ;

作用：數據庫中的約束 , 就是指表中的數據內容不能胡亂填寫 , 必須

按照要求填寫 . 好保證數據的完整性與安全性。

not null: 非空約束 ;

例如 : username varchar(40) not null username 這個列不

能有 null 值

unique: 唯一約束 , 后面的數據不能和前面重復 ;

例如 : cardNo char(18) unique; cardNo 列里面不可以有重

復數據

primary key ；主鍵約束 ( 非空 + 唯一 );

一般用在表的 id 列上面 . 一張表基本上都有 id 列的 , id 列作為

唯一標識的

auto_increment: 自動增長 , 必須是設置了 primary key 之后 ,

才可以使用 auto_increment

id int primary key auto_increment; id 不需要我們自己維

護了 , 插入數據的時候直接插入 null , 自動的增長進行填充進

去 , 避免重復了

需求：創建一張學生表 ( 含有 id 字段 , 姓名字段 , 性別字段 . id 為主鍵自

動增長 )

-- 需求: 創建一張學生表(含有id字段,姓名字段,性別字段. id為主
鍵自動增長)
-- 創建表之前需要選中數據庫
use day0708_1;
create table student(id int primary key auto_increment,name varchar(40),gender varchar(13) not null
);

3.3 查看所有的表

? 語法

show tables;

四，DML操作表記錄-增刪改

4.1 插入記錄

-- 創建一張商品表(商品id,商品名稱,商品價格,商品數量)
create table product(id int primary key auto_increment,pname varchar(100),price double,num int
）;

方式一 : 插入指定列 , 如果沒有把這個列進行列出來 , 以 null 進行

自動賦值。

insert into 表(列，列..) values(值，值..);
insert into product(pname,price)
values('Mac',18888);
insert into product(pname,price,num)
values('Mac',18888,null);

方式二：插入所有的列

insert into 表 values(值，值....);
insert into product values(null,'小米電腦',5888,10);
insert into product values(null,'華碩電腦',5888,null);
insert into product values(null,'蘋果電
腦',18000.0,10);
insert into product values(null,'華為5G手 機',30000,20);
insert into product values(null,'小米手機',1800,30);
insert into product values(null,'iPhonex',8000,10);
insert into product values(null,'蘋果電腦',8000,100);
insert into product values(null,'iPhone7',6000,200);
insert into product 
values(null,'iPhone6s',4000,1000);
insert into product values(null,'iPhone6',3500,100);
insert into product 
values(null,'iPhone5s',3000,100);
insert into product values(null,'方便面',4.5,1000);

除了整數 \ 小數類型外 , 其他字段類型的值必須使用引號引起來

( 建議單引號 )

如果要插入空值 , 可以不寫字段 , 或者插入 null

4.2 更新記錄

語法

update 表名 set 列 =值, 列 =值 [where 條件]

-- 更新記錄
-- 語法: update 表名 set 列 =值, 列 =值 [where 條件]
-- 需求:修改商品所有的價格為5000
update product set price = 5000;
-- 需求: 修改id為2的商品數量和價格
update product set price = 8000,num = 20 where id = 2;
-- 將商品名是Mac的價格修改為18000元
update product set price = 18000 where pname = 'Mac'
-- 將商品名是Mac的價格修改為17000,數量修改為5
update product set price = 17000,num = 5 where pname
= 'Mac'
-- 將商品名是方便面的商品的價格在原有基礎上增加2元
update product set price = price + 2 where pname = '方便面'

4.3 刪除記錄

delete 語法：

delete from 表 [where 條件] ?

truncate 語法:

truncate table 表;

-- 刪除記錄
-- 語法一: delete from 表名 [where 條件] ?
-- 刪除表中名稱為’Mac’的記錄
delete from product where pname = 'Mac'
-- 刪除價格小于5001的商品記錄
delete from product where price < 5001
-- 刪除表中的所有記錄
delete from product
-- 語法二: truncate table 表名;
truncate table product

delete 和 truncate 區別【面試題】

DELETE 刪除表中的數據，表結構還在 ; 刪除后的數據可以找

回 , 一條一條的刪除 .

TRUNCATE 刪除是把表直接 DROP 掉，然后再創建一個同樣

的新表。刪除的數據不能找回。執行速度比 DELETE 快。

工作里面的刪除

物理刪除 : 真正的刪除了 , 數據不在 , 使用 delete 就屬于物理刪

除

邏輯刪除 : 沒有真正的刪除 , 數據還在 . 搞一個標記 , 其實邏輯

刪除是更新例如 : state 1 啟用 0 禁用

五，基本查詢語法

select [*] [列名 ,列名] [列名 as 別名 ...] [distinct 字段] from 表名 [where 條件]

六.簡單查詢

查詢所有的列的記錄

查詢某張表特定列的記錄

去重查詢：去重針對某列 , distinct 前面不能先出現列

名

別名查詢

運算查詢 (+,-,*,/ 等 ) ：運算查詢列名與列名之間是可以運算的

-- 查詢所有的列語法:select * from 表名 
-- 需求:查詢product表中的所有數據 select * from product; -- 查詢某張表特定列:select 列名,列名,... from 表 名
-- 需求:查詢product表中的pname,price字段的值 select pname,price from product; -- 去重查詢:select distinct 列名 from 表名 
-- 需求:查詢price字段,[去重]單個字段去重 select distinct price from product;-- 需求:查詢pname,price字段,[同時去重]多個字段去重select distinct pname,price from product;-- 注意:distinct前面不能有字段名 select id,distinct price from product;-- 報 錯-- 別名查詢
-- 對字段取別名:select 字段 as 別名,字段 as 別 名,... from 表名。注意: as可以省略,一般都會省略 select pname as 商品名稱,num as 商品數量 from product;select pname 商品名稱,num 商品數量 from product;-- 對表取別名:select 表1別名.字段名,... from 表1 as 表1別名。注意: as可以省略 一般都會省略select p.pname,p.price from product as p; select p.pname,p.price from product p;-- 運算查詢(+,-,*,/等),null和其他數據進行運算得到 是null 
-- 需求:計算每個商品的總價(單價*數量) select price,num from product; select price*num from product; select price * num 總價 from product; select price * ifnull(num,0) 總價 from product;

七.條件查詢

語法：

select ... from 表 where 條件; //取出表中的每條數據，滿足條件的記錄就返回，不滿足條 件的記錄不返回

between...and... 區間查詢

where price between 1000 and 3000 相當于 1000<=price<=3000

in( 值，值 ..)

-- 查詢id為1,3,5,7的select * from t_product where id = 1select * from t_product where id = 3select * from t_product where id = 5select * from t_product where id = 7select * from t_product where id in(1,3,5,7)

like 模糊查詢。一般和 _ 或者 % 一起使用

_ 占一位

% 占 0 或者 n 位

name like '張%' --查詢姓張的用戶, 名字的字數沒有 限制name like '張_' --查詢姓張的用戶 并且名字是兩個 字的

and 多條件同時滿足

where 條件1 and 條件2 and 條件3

or 任意條件滿足

where 條件1 or 條件2 or 條件3

需求：

查詢商品價格 >3000 的商品

查詢 id=1 的商品

查詢 id<>1 的商品

查詢價格在 3000 到 6000 之間的商品

查詢 id 在 1 ， 5 ， 7 ， 15 范圍內的商品

查詢商品名以 iPho 開頭的商品 (iPhone 系列 ) 查詢商品價格大于 3000 并且數量大于 20 的商品 ( 條件

and 條件 and...)

查詢 id=1 或者價格小于 3000 的商品

-- 查詢商品價格>3000的商品select * from product where price > 3000;-- 查詢id=1的商品select * from product where pid = 1;-- 查詢id<>1的商品select * from product where pid <> 1;select * from product where pid != 1;-- 查詢價格在3000到6000之間的商品select * from product where price between 3000 and 6000;select * from product where price >= 3000 and price <= 6000;-- 查詢id在1，5，7，15范圍內的商品select * from product where pid in(1,5,7,15);-- 查詢商品名以iPho開頭的商品(iPhone系列)select * from product where pname like 'iPho%';-- 查詢商品價格大于3000并且數量大于20的商品 (條 件 and 條件 and...)select * from product where price > 3000 and num > 20;-- 查詢id=1或者價格小于3000的商品select * from product where pid = 1 or price < 3000;

八.排序查詢

有時候我們需要對查詢出來的結果排序顯示，那么就可

以通過 ORDER BY 子句將查詢出的結果進行排序。排序可

以根據一個字段排，也可以根據多個字段排序，排序只

是對查詢的結果集排序，并不會影響表中數據的順序。

環境的準備

-- 創建學生表(有sid,學生姓名,學生性別,學生年齡,分 數列,其中sid為主鍵自動增長)CREATE TABLE student(sid INT PRIMARY KEY auto_increment,sname VARCHAR(40), sex VARCHAR(10),age INT, score DOUBLE);INSERT INTO student VALUES(null,'zs','男',18,98.5);
INSERT INTO student VALUES(null,'ls','女',18,96.5);
INSERT INTO student VALUES(null,'ww','男',15,50.5); 
INSERT INTO student VALUES(null,'zl','女',20,98.5); 
INSERT INTO student VALUES(null,'tq','男',18,60.5); 
INSERT INTO student VALUES(null,'wb','男',38,98.5); 
INSERT INTO student VALUES(null,'小 麗','男',18,100); 
INSERT INTO student VALUES(null,'小 紅','女',28,28); 
INSERT INTO student VALUES(null,'小 強','男',21,95);

單列排序： 只按某一個字段進行排序，單列排序

SELECT 字段名 FROM 表名 [WHERE 條件] ORDER BY 字段名 [ASC|DESC]; //ASC: 升序，默認值; DESC: 降序

需求 : 以分數降序查詢所有的學生

select * from student order by score desc;

組合排序： 同時對多個字段進行排序，如果第 1 個字段

相等，則按第 2 個字段排序，依次類推

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

需求：以分數降序查詢所有的學生 , 如果分數一致 , 再

以 age 降序

select * from student order by score desc ,age desc;

九，聚合函數

之前我們做的查詢都是橫向查詢，它們都是根據條件一

行一行的進行判斷，而使用聚合函數查詢是 縱向查詢 ，

它是對一列的值進行計算，然后返回 一個結果值 。聚合

函數會忽略空值 NULL

需求：

求出學生表里面的最高分數

求出學生表里面的最低分數

求出學生表里面的分數的總和 ( 忽略 null 值 )

求出學生表里面的平均分

統計學生的總人數 ( 忽略 null)

-- 求出學生表里面的最高分數 
SELECT MAX(score) FROM student; -- 求出學生表里面的最低分數 
SELECT MIN(score) FROM student; -- 求出學生表里面的分數的總和(忽略null值) 
SELECT SUM(score) FROM student; -- 求出學生表里面的平均分 
SELECT AVG(score) FROM student; -- 統計學生的總人數 (忽略null) 
SELECT COUNT(sid) FROM student; 
SELECT COUNT(*) FROM student;

我們發現對于 NULL 的記錄不會統計，建議如果統計個數

則不要使用有可能為 null 的列，但如果需要把 NULL 也統

計進呢？我們可以通過 IFNULL( 列名，默認值 ) 函數來解

決這個問題 . 如果列不為空，返回這列的值。如果為

NULL ，則返回默認值。

-- 注意: 聚合函數會忽略null -- 準備添加2條數據

INSERT INTO student VALUES(null,'小 明','男',21,null); 
INSERT INTO student VALUES(null,'小 黑','男',22,98); 
-- 需求: 統計所有學生的總分數 
select sum(score) from student; -- 結果: 824 忽略null 
-- 需求:統計學生的總人數 (忽略null) 
select count(score) from student; -- 結果: 10個 忽略null 
-- 求出學生表里面的平均分 
select avg(score) from student; -- 結果: 82.4 
忽略null 問題:實際開發不能忽略null 
select avg(ifnull(score,0)) from student; - - 結果: 74.9090909090909 -- 擴展: 
-- 假設: 統計所有同學的age+score 
select sum(age+score) from student; - - 結果: 
1040 select sum(age) + sum(score) from student; - - 結果: 1061

十.分組查詢

分組查詢是指使用 GROUP BY 語句對查詢信息進行分

組。

GROUP BY 怎么分組的？將分組字段結果中相同內容作

為一組，如按性別將學生分成兩組。

GROUP BY 將分組字段結果中相同內容作為一組，并且

返回每組的第一條數據，所以單獨分組沒什么用處。分

組的目的就是為了統計，一般分組會跟聚合函數一起使

用。

分組：語法

SELECT 字段1,字段2... FROM 表名 [where 條件] GROUP BY 列 [HAVING 條件];

需求：根據性別分組 , 統計每一組學生的總人數

-- 根據性別分組, 統計每一組學生的總人數 
SELECT sex, count(*) FROM student GROUP BY sex;

分組后篩選 having

-- 練習根據性別分組, 統計每一組學生的總人數> 5的(分 組后篩選)SELECT sex, count(*) FROM student GROUP BY sex HAVING count(*) > 5-- 練習復雜: 統計sid為8之前的的, 根據性別分組, 每 一組學生的總人數 > 2的(分組后篩選) 
select sex,count(*) from student where sid < 8 group by sex having count(*) > 2

注意事項

根據某一列進行分組 , 將分組字段結果中相同內容

作為一組 ; 有幾組返回的記錄就有幾條

單獨分組沒有意義 , 返回每一組的第一條記錄

分組的目的一般為了做統計使用 , 所以經常和聚合

函數一起使用

在分組里面 , 如果 select 后面的列沒有出現在 group

by 后面展示這個組的這個列的第一個數據

where 和 having 的區別【面試】

十一.分頁查詢

LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查詢記

錄的條數 . 經常用來做分頁查詢

語法

select * from 表名 limit m,n;m是指從哪開始查；記錄開始的index，從0開始，表示第一 條記錄 n是指查多少條。

需求：分頁查詢學生 , 每一頁查詢 4 條

-- 需求: 分頁查詢學生, 每一頁查詢4條 
select * from student limit 0,4; -- 第1頁 
select * from student limit 4,4; -- 第2頁
select * from student limit 8,4; -- 第3頁m=(當前頁碼-1)*n,n