MySql學習筆記1 1.SQL 分類 1. DDL(Data Definition Language)數據定義語言 2. DML(Data Manipulation Language)數據操作語言 3. DQL(Data Query Language)數據查詢語言 4. DCL(Data Cont ...

MySql學習筆記一

1.SQL 分類

DDL(Data Definition Language)數據定義語言
DML(Data Manipulation Language)數據操作語言
DQL(Data Query Language)數據查詢語言
DCL(Data Control Language)數據控制語言
TCL(Transaction Control Language)事務控制語言

2.常見指令

查看當前所有資料庫: show databases
打開指定的庫：use 庫名
查看當前庫的所有表：show tables
查看其它庫的所有表：show tables from 庫名
創建表：create table 表名(

列名列類型,

列名列類型,

......

);
查看表結構：DESC 表名
查看伺服器版本：select version() || mysql --V
查看字元集：Show variables like '%char%'
查看當前用戶：Select USER();

3.DQL（數據查詢語言）

3.1基礎查詢

select 查詢列表 from 表名

查詢列表可以是：表中欄位、常量、表達式、函數

查詢的結果是一個虛擬機的表格

3.1.1.常用關鍵字

3.1.1.1.起別名

as 或者空格例如 select 欄位名 as a from 表名 as b

對欄位的別名最好加上雙引號，可以在別名裡加空格

3.1.1.2.去重

利用關鍵字DISTINCT

select distinct 欄位名 from 表名

3.1.1.3.'+'只有計算功能

1 + 1 = 2

'1' + 1 = 2

'a' + 1 = 1

null + 1 = null

3.1.1.4.CONCAT

select 欄位名 CONCAT 欄位名（AS 別名）from 表名

CONCAT相當於java中的 '+'

3.2.條件查詢

select 查詢列表 from 表名 where 篩選條件

3.2.1.按條件表達式篩選

條件運算符：>, < , =, !=, >=, <= ; != MySql建議寫為 <>

3.2.2.按邏輯表達式篩選

and or not

3.2.3.模糊查詢

like

%任意多個字元

_任意單個字元

如果查詢含有轉移符號的數據，要用\轉義如\%和\_

也可以用ESCAPE 如 like '_$%' ESCAPLE '$'
between...and... 兩邊都包含
in

in列表中的值類型必須一致或者相容

不支持通配符
is null

null值判斷不能用 = null 要用 is null, is not null

3.2.4.安全等於

<=> 可以判斷Null值，也可以判斷普通數值

但是可讀性較差

3.3.排序查詢

示例： select 查詢列表 from 表名 order by 排序列表 ASC(DESC)

ASC是升序排列，DESC是降序排列，預設是ASC

排序列表可以是欄位名，表達式，別名，函數

支持按多個欄位排序，例 order by 排序列表1 ASC(DESC), 排序列表2 ASC(DESC)....

先按按排序列表1進行排序，然後排序列表1相同的按排序列表進行排序，以此類推。

3.4.常見函數

函數類似於java中的方法，將一組邏輯語句封裝在方法體中，對外暴露方法名

好處：1.隱藏了實現細節 2.提高代碼重用性

3.4.1.單行函數

如count, length, IFNULL等

3.4.1.1.字元函數

length

統計參數值的位元組個數，utf-8中文占三個位元組, gbk中文占兩個位元組

CONCAT

拼接字元串例CONCAT(str1,str2,str3.....)

和null拼接會編程null

upper,lower

upper變大寫，lower變小寫

substr, subString

截取字元串

substr(str, index) 截取從index開始後面所有的字元

substr(str, pos, len) 截取從pos到len的字元

sql中索引是從1開始的

instr

instr(str1, str2)

判斷str2在str1中第一次出現的索引，如果找不到返回0

trim

trim(str)

去除str中的前後空格

trim(str1 from str2)

去除str2中開頭結尾重覆出現的str1

LPAD、RPAD

LPAD(STR1, LEN, STR2)

在STR1的前面重覆填充STR2直到長度為LEN，如果LEN < STR1的LENGTH，會截斷STR1

RPAD同理

Replace

Replace(STR1, STR2, STR3)

將STR1中的所有的STR2都替換為STR3

3.4.1.2.數學函數

round

round(double) 四捨五入為整數

round(double, num) 四捨五入保留num位小數

ceil

ceil(double) 返回>=num的最小整數

floor

floor(double) 返回<=num的最大整數

truncate

truncate(double, num) 小數點num位以後截斷捨去

mod

mod(num1, num2) 取餘相當於%

rand

獲取0-1之間的隨機數

3.4.1.3.日期函數

now

now() 返回當前系統日期+時間

CURDATE

CURDATE() 返回當前系統日期，不包含時間

CURTIME

CURTIME() 返回當前系統時間，不包含日期

獲取日期指定部分

YEAR(date) 獲取年

MONTH(date) 獲取月

MONTHNAME(date) 獲取月名

DAY, HOUR, MINUTE, SECOND同上

字元串與日期的相互轉換

STR_TO_DATE('STRDATE', 'Format') 例STR_TO_DATE('9-13-2019', '%m-%d-%Y') m是月份，必須用m

將字元串按照format格式轉換成日期

date_format(date, format) 例 date_format(now(), '%m月%d日%Y年')

將日期按照format格式轉換成字元串

DateDiff

DateDiff(date1, date2)

返回的是date1日期減去date2日期的天數差

3.4.1.4 流程式控制制函數

if函數

實現類似於if else 的效果

例如 if(experssion, trueResult, falseResult)

case函數

使用1 實現類似switch case的效果

case 要判斷的欄位或表達式
when 常量1 then 要顯示的值1或語句1;
when 常量2 then 要顯示的值2或語句2;
when 常量3 then 要顯示的值3或語句2;
...
else 要顯示的值或語句
end

使用2 實現類似於if ,else if...的效果

case
when 條件1 then 要顯示的值1或語句1;
when 條件2 then 要顯示的值2或語句2;
when 條件3 then 要顯示的值3或語句3;
...
else 要顯示的值或語句
end

3.4.2.分組函數

做統計使用，又稱為統計函數，聚合函數，組函數

sum(): 求和
avg(): 平均值
max(): 最大值
min(): 最小值
count(): 計算個數

count(欄位名) 統計欄位名非空的行數

count(*) 統計總行數等價於count(常量) 如count(1), count('a')等

效率：MYISAM引擎下：count(*)效率最高

INNODB引擎下：count(*)和count(1)效率差不多,比cout('str')高

分組函數參數支持類型：

	字元型	日期型	數值型	忽略null值	與DISTINCT搭配
sum()	X	X	√	√	√
avg()	X	X	√	√	√
max()	√	√	√	√	√
min()	√	√	√	√	√
count()	√	√	√	√	√

與分組函數一同查詢的欄位有限制，一般都是group by之後的欄位

3.5.分組查詢

Select 分組函數, 欄位(要求該欄位出現在group by的後面)
from 表
(where 分組前的篩選條件)
group by 分組的欄位1 (分組的欄位2.....)
(having 分組後的篩選條件)
(order by 排序)

where篩選的是分組前的原來的表中的數據

having篩選的是分組後的數據

分組函數的條件一定是放在having子句中

為了性能，能用分組前篩選的條件儘量放在where子句中

當group by之後有多個條件時，條件的先後順序不影響查詢結果，條目分組時必須滿足每一個條件都相等