零基礎學MySQL 筆記目錄：(https://www.cnblogs.com/wenjie2000/p/16378441.html) 一個問題淘寶網，京東、微信，抖音都有各自的功能,那麼當我們退出系統的時候，下次再訪問時，為什麼信息還存在? =》資料庫解決之道-文件、資料庫為瞭解決上述問題， ...

零基礎學MySQL

筆記目錄：(https://www.cnblogs.com/wenjie2000/p/16378441.html)

一個問題

淘寶網，京東、微信，抖音都有各自的功能,那麼當我們退出系統的時候，下次再訪問時，為什麼信息還存在? =》資料庫

解決之道-文件、資料庫

為瞭解決上述問題，使用更加利於管理數據的東東-資料庫，它能更有效的管理數據。

舉一個生活化的複列說明
如果說圖書館是保存書籍的,那麼資料庫就是保存數據的。

資料庫的簡單原理圖

MySQL安裝和配置

Mysql下載地址：https://downloads.mysql.com/archives/community/

我安裝的版本為MySQL5.7。如果是安裝其他版本，請自行百度。不同版本的MySql安裝過程存在差別。

特別說明

如果安裝過Mysql過程中，出錯了或者想重新安裝
sc delete mysql 【會刪除已經安裝好的mysql服務。慎重使用】

安裝步驟

請註意，zip 安裝文件是壓縮文件，和.exe安裝文件是不一樣的, 要嚴格的下麵的步驟來執行，否則安裝很可能不會成功。

下載後會得到zip 安裝文件
解壓的路徑最好不要有中文和空格
這裡我解壓到 D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64 目錄下【可自行指定目錄,目錄不能有中文，儘量不安裝在系統盤】
添加環境變數 : 電腦-屬性-高級系統設置-環境變數，在Path 環境變數增加mysql的安裝目錄\bin目錄。如下圖所示

在D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64目錄下下創建 my.ini 文件, 需要我們自己創建（其他非5.7版本會有差異）。用記事本打開該文件，寫入以下內容並保存（註意要根據自己的安裝位置更改文本中的目錄）。

[client]
port=3306
default-character-set=utf8
[mysqld]
# 設置為自己MYSQL的安裝目錄
basedir=D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64\
# 設置為MYSQL的數據目錄
datadir=D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64\data\
port=3306
character_set_server=utf8
#跳過安全檢查（登錄不需要密碼）
skip-grant-tables

使用管理員身份打開 cmd , 並切換到 D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64\bin 目錄下, 執行mysqld -install
```
d:
cd D:\ZhuangYeRuanJian\mysql\mysql-5.7.38-winx64
mysqld -install
```
如果入到下麵兩種情況，是由於你的電腦缺失了相關的系統文件。下載並安裝：https://www.microsoft.com/zh-CN/download/details.aspx?id=40784

初始化資料庫: mysqld --initialize-insecure --user=mysql

如果執行成功，會生成 data目錄:
啟動mysql 服務: net start mysql 【停止mysql服務指令 net stop mysql】, 如果成功:

任務管理器中也會出現MySQL服務
進入mysql 管理終端: mysql -u root -p 【當前root 用戶密碼為空，下一行要輸入密碼時直接回車】

修改root 用戶密碼

use mysql;  
update user set authentication_string=password('123456') where user='root' and Host='localhost';

解讀: 上面的語句就是修改 root用戶的密碼為 123456
註意：在後面需要帶 分號，回車即可執行該指令
執行: flush privileges; 刷新許可權
退出: quit

修改my.ini , 再次進入就會進行許可權驗證了

#跳過安全檢查（登錄不需要密碼）
#skip-grant-tables

重新啟動mysql

net stop mysql
net start mysql

提示: 該指令需要退出mysql ， 在Dos下執行.

再次進入Mysql, 輸入正確的用戶名和密碼

mysql -u root -p

密碼正確，進入mysql

密碼錯誤，提示如下信息
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

安裝過程中，一定要按照老師的步驟來，不然會錯誤.

如果真的錯誤了，清除mysql服務, 再次安裝.

連接到Mysql服務(Mysql資料庫)的指令

mysql -h 主機IP -P 埠 -u 用戶名 -p密碼

提醒:

-p密碼不要有空格
-p後面沒有寫密碼，回車會要求輸入密碼
如果沒有寫-h主機，預設就是本機
如果沒有寫-P埠，預設就是3306
在實際工作中，3306一般修改

安裝Navicat

http://www.navicat.com.cn/download/navicat-for-mysql

安裝過程很簡單，此處省略

如果想破解，百度，教程很多

安裝後，打開，點擊左側的“連接”，進行如下配置。

資料庫

註意：navicat只是方便手動操作和查看，光是會使用它沒有太大意義。實際開發中程式員操作資料庫還是要通過指令。指令是必須要認真學的。

資料庫三層結構-破除 MySQL 神秘

所謂安裝Mysql資料庫，就是在主機安裝一個資料庫管理系統(DBMS),這個管理程式可以管理多個資料庫。DBMS(database manage system)
一個資料庫中可以創建多個表,以保存數據(信息)。
資料庫管理系統(DBMS)、資料庫和表的關係如圖所示:示意圖

數據在資料庫中的存儲方式

SQL 語句分類

DDL:數據定義語句[create表，庫...]

DML:數據操作語句[增加insert,修update,刪除delete]

DQL:數據查詢語句[select ]

DCL:數據控制語句[管理資料庫:比如用戶許可權 grant revoke ]

創建

註意：

在mysql資料庫中，sql關鍵字和大小寫是不區分大小寫的。

windows下mysql中的資料庫名、表名、列名預設是不區分大小寫的，但是linux會區分大小寫，所以當執行的sql語句有大小寫區別時需要註意。

因此，mysql創建資料庫、表、列時，儘量使用小寫命名，如果名字由多個單片語成可以使用“_”作為間隔符。如果名字和關鍵字重名可以用反引號“`”括起來。

具體情況可以看這篇博客：https://www.cnblogs.com/chenhaoblog/p/13604727.html

CREATE DATABASE [IF NOT EXISTS] db_name
[create_specification [,create_specification]...]

create_specification:
[DEFAULT]CHARACTER SET charset_namel
[DEFAULT]COLLATE collation_name
#中括弧中的內容為可寫可不寫的，根據實際情況決定。實際語句的使用中不要寫上中括弧

CHARACTER SET:指定資料庫採用的字元集，如果不指定字元集，預設utf&,

COLLATE:指定資料庫字元集的校對規則(常用的utf8 bin[區分大小寫]、utf8 general ci[不區分大小寫（例：A==a）]註意預設是utf8_general_ci)

# 演示資料庫的操作
#創建一個名稱為 hsp_db01 的資料庫。[圖形化和指令 演示]
#使用指令創建資料庫
CREATE DATABASE hsp_db01;
#刪除資料庫指令
DROP DATABASE hsp_db01
#創建一個使用 utf8 字元集的 hsp_db02 資料庫
CREATE DATABASE hsp_db02 CHARACTER SET utf8
#創建一個使用 utf8 字元集，並帶校對規則的 hsp_db03 資料庫
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
#校對規則 utf8_bin 區分大小 預設 utf8_general_ci 不區分大小寫

#下麵是一條查詢的 sql , select 查詢 * 表示所有欄位 FROM 從哪個表
#WHERE 從哪個欄位 NAME = 'tom' 查詢名字是 tom
SELECT * FROM t1 WHERE NAME = 'tom'

查看、刪除資料庫

顯示資料庫語句:
SHOW DATABASES

顯示資料庫創建語句:
SHOW CREATE DATABASE db_name

資料庫刪除語句[一定要慎用]:
DROP DATABASE [IF EXISTS] db_name

#演示刪除和查詢資料庫
#查看當前資料庫伺服器中的所有資料庫
SHOW DATABASES
#查看前面創建的 hsp_db01 資料庫的定義信息
SHOW CREATE DATABASE `hsp_db01` 
#老師說明 在創建資料庫,表的時候，為了規避關鍵字，可以使用反引號解決
CREATE DATABASE `INT`
#刪除前面創建的 hsp_db01 資料庫
DROP DATABASE hsp_db01

備份恢複數據庫

備份資料庫(註意:在DOS執行)

mysqldump -u 用戶名 -p[密碼] -B 資料庫1 資料庫2 資料庫n > 路徑\\文件名.sql
-p後面可以加上密碼。也可以不寫，回車後再輸入密碼

恢複數據庫(註意:cmd進入MySQL命令行再執行)

Source 文件名.sql

#練習 : database03.sql 備份 hsp_db02 和 hsp_db03 庫中的數據，並恢復
#備份, 要在 Dos 下執行 mysqldump 指令其實在 mysql 安裝目錄\bin
#這個備份的文件，就是對應的 sql 語句
mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sql
DROP DATABASE ecshop;
#恢複數據庫(註意：在DOS界面，先進入 Mysql 命令行再執行)
source d:\\bak.sql
#第二個恢復方法， 直接將 bak.sql 的內容放到查詢編輯器中，執行

備份恢複數據庫的表

mysqldump -u 用戶名 -p[密碼] 資料庫 表1 表2 表n > 路徑\\文件名.sql
-p後面可以加上密碼。也可以不寫，回車後再輸入密碼
恢復方式和上面一樣

表

創建

CREATE TABLE table_name
(
	field1 datatype,
    field2 datatype,
    field3 datatype
)character set 字元集 collate 校對規則 engine 引擎

field:指定列名 datatype:指定列類型(欄位類型)
character set:如不指定則為所在資料庫字元集
collate:如不指定則為所在資料庫校對規則
engine:引擎(這個涉及內容較多，後面單獨講解)

刪除

drop table 表名;

修改

如果看了b站韓順平老師的課程，註意他的PPT這部分的指令有問題，下麵的才是對的。

#添加列
ALTER TABLE 表名
ADD 列名 datatype [DEFAULT expr];

#修改列類型
ALTER TABLE 表名
MODIFY 列名 datatype [DEFAULT expr];

#修改列類型及名稱
ALTER TABLE 表名 
CHANGE 列名 新列名 datatype;

#刪除列
ALTER TABLE 表名;
DROP 列名;
查看表的結構: desc 表名; --可以查看表的列

#修改表名:
rename table 表名 to 新表名;

#修改表字元集:
alter table 表名 character set 字元集;

應用實例：

員工表emp增加一個image列,varchar類型(要求在resume後面).
修改job列，使其長度為60。
刪除sex列。
表名改為employee。
修改表的字元集為utf8
列名name修改為user_name

ALTER TABLE emp
ADD image varchar(32) NOT NULL DEFAULT '' AFTER resume;

ALTER TABLE emp
MODIFY job varchar(60);

ALTER TABLE emp
DROP sex;
DESC emp;

rename table emp to employee;

ALTER TABLE employee character set utf8;

ALTER TABLE employee CHANGE `name` `user_name` varchar(20);

Mysql數據類型

數值型（整數）的基本使用

說明：在能夠滿足需求的情況下，儘量選擇占用空間小的類型（節省資源）

類型	位元組	最小值	最大值
		(帶符號的/無符號的)	(帶符號的/無符號的)
TINYINT	1	-128	127
[unsigned]		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

#演示整型的是一個
#使用 tinyint 來演示範圍 有符號 -128 ~ 127 如果沒有符號 0-255
#說明： 表的字元集，校驗規則, 存儲引擎，老師使用預設
#1. 如果沒有指定 unsinged , 則 TINYINT 就是有符號
#2. 如果指定 unsinged , 則 TINYINT 就是無符號 0-255
CREATE TABLE t3 (id TINYINT);
CREATE TABLE t4 (id TINYINT UNSIGNED);

INSERT INTO t3 VALUES(127); #這是非常簡單的添加語句
SELECT * FROM t3;

INSERT INTO t4 VALUES(255);
SELECT * FROM t4;

數值型(bit)的使用

基本使用

create table t02 (num bit(8));
insert into t02 (1,3);
insert into t02 values(2,65);

細節說明

bit欄位顯示時，按照位的方式顯示.查詢的時候仍然可以用使用添加的數值
如果一個值只有0,1可以考慮使用bit(1),可以節約空間位類型。M指定位數,預設值1，範圍1-64
使用不多.

#演示 bit 類型使用
#說明
#1. bit(m) m 在 1-64
#2. 添加數據 範圍 按照你給的位數來確定，比如 m = 8 表示一個位元組 0~255
#3. 顯示按照 bit
#4. 查詢時，仍然可以按照數來查詢
CREATE TABLE t05 (num BIT(8));
INSERT INTO t05 VALUES(255);
SELECT * FROM t05;
SELECT * FROM t05 WHERE num = 1;

數值型(小數)的基本使用

FLOAT/DOUBLE [UNSIGNED]

Float單精度精度，Double 雙精度

DECIMAL[M,D] [UNSIGNED]

可以支持更加精確的小數位。M是位數(整數位數+小數位數)的總數，D是小數點(標度)後面的位數。
如果D是0，則值沒有小數點或分數部分。M最大65。D最大是30。如果D被省略，預設是0。如果M被省略，預設是10。
建議:如果希望小數的精度高，推薦使用decimal

#演示 decimal 類型、float、double 使用

#創建表
CREATE TABLE t06 (
    num1 FLOAT,
    num2 DOUBLE,
    num3 DECIMAL(30,20));
#添加數據
INSERT INTO t06 VALUES(88.12345678912345, 88.12345678912345,88.12345678912345);
SELECT * FROM t06;

#decimal 可以存放很大的數
CREATE TABLE t07 (
num DECIMAL(65));
INSERT INTO t07 VALUES(8999999933338388388383838838383009338388383838383838383);

字元串的基本使用

CHAR(size)

固定長度字元串最大255字元
VARCHAR(size)

可變長度字元串最大65532位元組【utf8編碼最大21844字元（因為(65535-2)/3)=21844.3），1-2個位元組用於記錄存儲數據長度。如果允許為null也要占用一個位元組，不允許為空則不占用這一個位元組】

字元串使用細節

細節1

char(4)//這個4表示字元數(最大255)，不是位元組數,不管是中文還是字母都是放四個,按字元計算.

varchar(4)//這個4表示字元數,不管是字母還是中文都以定義好的表的編碼來存放數據.

不管是中文還是英文字母，都是最多存放4個，是按照字元來存放的.
細節2

char(4)是定長(固定的大小)，就是說，即使你插入'aa'，也會占用分配的4個字元的空間.

varchar(4)是變長，就是說，如果你插入了'aa',實際占用空間大小並不是4個字元，而是按照實際占用空間來分配(說明:varchar本身還需要多占用1-3個位元組)
細節3

什麼時候使用char,什麼時候使用varchar
1. 如果數據是定長,推薦使用char，比如md5的密碼,郵編,手機號，身份證號碼等. char(32)
2. 如果一個欄位的長度是不確定，我們使用varchar，比如留言,文章
查詢速度:char > varchar
細節4
在存放文本時，也可以使用Text 數據類型。可以將TEXT列視為VARCHAR列,註意Text不能有預設值，大小0~2¹⁶位元組
如果希望存放更多字元，可以選擇 MEDIUMTEXT (0~2²⁴位元組) 或者LONGTEXT (0~2³²位元組)

#演示字元串類型的使用細節
#char(4) 和 varchar(4) 這個 4 表示的是字元，而不是位元組, 不區分字元是漢字還是字母
CREATE TABLE t11(`name` CHAR(4));
INSERT INTO t11 VALUES('韓順平123');

SELECT * FROM t11;

CREATE TABLE t12(`name` VARCHAR(4));
INSERT INTO t12 VALUES('韓順平212');
INSERT INTO t12 VALUES('ab 北京');
SELECT * FROM t12;
#如果 varchar 不夠用，可以考試使用 mediumtext 或者 longtext, 
#如果想簡單點，可以使用直接使用 text
CREATE TABLE t13( content TEXT, content2 MEDIUMTEXT , content3 LONGTEXT);
INSERT INTO t13 VALUES('韓順平教育', '韓順平教育 100', '韓順平教育 1000~~');
SELECT * FROM t13;

日期類型的基本使用

CREATE TABLE birthday( t1 DATE, t2 DATETIME,
t3 TIMESTAMP NOT NULL DEFAULTCURRENT TIMESTAMP ON UPDATE
CURRENT TIMESTAMP ); 

mysql> INSERT INTO birthday(t1,t2)
VALUES('2022-11-11','2022-11-11 10:10:10');

#timestamp時間戳

日期類型的細節說明

TimeStamp在Insert和update時，自動更新

#演示時間相關的類型
#創建一張表, date , datetime , timestamp
CREATE TABLE t14 (
    birthday DATE , -- 生日
    job_time DATETIME, -- 記錄年月日 時分秒
    login_time TIMESTAMP
        NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP); -- 登錄時間, 如果希望 login_time 列自動更新, 需要配置
SELECT * FROM t14;
INSERT INTO t14(birthday, job_time)
VALUES('2022-11-11','2022-11-11 10:10:10');
-- 如果我們更新 t14 表的某條記錄，login_time 列會自動的以當前時間進行更新

練習題

-- 自己一定要練習一把
CREATE TABLE `emp` (id INT,
                    `name` VARCHAR(32),
                    sex CHAR(1),
                    brithday DATE,
                    entry_date DATETIME,
                    job VARCHAR(32),
                    salary DOUBLE,
                    `resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB; 
-- 添加一條
INSERT INTO `emp`
	VALUES(100, '小妖怪', '男', '2000-11-11',
		'2010-11-10 11:11:11', '巡山的', 3000, '大王叫我來巡山');
SELECT * FROM `emp`;

CRUD

C[creat]R[read]U[update]D[delete]

lnsert(添加數據)

INSERT INTO 表名 [(column1 [, column2. . .])]
VALUES (value1 [, value2...]);

快速入門案例:

創建一張商品表goods(id int , goods_name varchar(10),price double);

添加2條記錄

create table goods(id int,goods_name varchar(10),price double);

INSERT INTO goods 
VALUES (10,'可樂',4.5);

INSERT INTO goods (id,goods_name,price) 
VALUES (11,'可樂2',4.6);

細節說明

插入的數據應與欄位的數據類型相同。

比如把'abc'添加到int類型會錯誤
數據的長度應在列的規定範圍內，例如:不能將一個長度為80的字元串加入到長度為40的列中。
在values中列出的數據位置必須與被加入的列的排列位置相對應。
字元和日期型數據應包含在單引號中。
列可以插入空值[前提是該欄位允許為空]，insert into table value(null)

insert into 表名 (列名...) values (),(),()形式添加多條記錄

INSERT INTO `goods` (id,goods_name,price)
VALUES(50,'三星手機',2300),(60,'海爾手機',1800);

如果是給表中的所有欄位添加數據,可以不寫前面的欄位名稱
```
INSERT INTO goods 
VALUES (10,'可樂',4.5);
```
預設值的使用，當不給某個欄位值時，如果有預設值就會添加,否則報錯

Update（更新數據）

UPDATE 表名
SET 列名1=expr1 [, 列名2=expr2 ...][WHERE where_definition]

基本使用：

要求:在上面創建的employee表中修改表中的紀錄

將所有員工薪水修改為5000元。
將姓名為小妖怪的員工薪水修改為3000元。

將老妖怪的薪水在原有基礎上增加1000元。

-- 1. 將所有員工薪水修改為 5000 元。[如果沒有帶 where 條件，會修改所有的記錄，因此要小心]
UPDATE employee SET salary=5000;
-- 2
UPDATE employee SET salary=3000 WHERE `user_name`='小妖怪';
-- 3
UPDATE employee SET salary=salary+1000 WHERE `user_name`='老妖怪';

使用細節:

UPDATE語法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要給予哪些值。
WHERE子句指定應更新哪些行。如沒有WHERE子句，則更新所有的行。

如果需要修改多個欄位，可以通過 set 欄位1=值1,欄位2=值2…….

UPDATE employee
SET salary =salary +1000,
	job ='出主意的' WHERE user_name ='老妖怪';

Delete（刪除數據）

delete from 表名
[WHERE where_definition]

快速入門案例(使用employee測試)

刪除表中名稱為'老妖怪'的記錄

刪除表中所有記錄。

-- 1
DELETE FROM `employee` WHERE user_name='老妖怪';
-- 2
DELETE FROM `employee`;

使用細節

如果不使用where子句，將刪除表中所有數據。
Delete語句不能刪除某一列的值(可使用update設為null或者'')
使用delete語句僅刪除記錄，不刪除表本身。如要刪除表，使用droptable語句。drop table 表名;

Select（查找數據-單表）

SELECT [DISTINCT] * | {column1, column2, column3... }
FROM 表名;

註意事項

Select 指定查詢哪些列的數據。
column指定列名。
*號代表查詢所有列。
From指定查詢哪張表。
DISTINCT可選,指顯示結果時,去掉重覆數據（此處重覆指的是兩行內容完全相同）

練習題

先使用以下指令創建表，不只是這題需要用，後面還要用：

create table student(
	id int not null default 1,
	name varchar(20) not null default '',
	chinese float not null default 0.0,
	english float not null default 0.0,
	math float not null default 0.0
);

insert into student(id,name,chinese,english,math) values(1,'韓順平',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'張飛',67,98,56);
insert into student(id,name,chinese,english,math) values(3,'宋江',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'關羽',88,98,90);
insert into student(id,name,chinese,english,math) values(5,'趙雲',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'歐陽鋒',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黃蓉',75,65,30);
insert into student(id,name,chinese,english,math) values(8,'李明',80,65,30);

查詢表中所有學生的信息。
查詢表中所有學生的姓名和對應的英語成績。
過濾表中重覆數據distinct 。

要查詢的記錄，每個欄位都相同，才會去重

-- 1
SELECT * FROM `student`;
-- 2
SELECT `name`,`english` FROM student;
-- 3
SELECT DISTINCT * FROM `student`;
-- 4
SELECT DISTINCT * FROM `student`;

使用表達式對查詢的列進行運算

SELECT * | { column1 | expression,column2 | expression，...}
FROM tablename;

在select語句中可使用as語句

SELECT column_name [as] 別名 from 表名;
-- 此處as可加可不加。如果不加就必須中間有空格

練習

統計每個學生的總分
在所有學生總分加10分的情況

使用別名錶示學生分數。

-- 1
SELECT `name`,(chinese+english+math) FROM `student`;
-- 2
SELECT `name`,(chinese+english+math+10) FROM `student`;
-- 3
SELECT `name` AS '名字',(chinese+english+math) AS total_score FROM `student`;

在where子句中經常使用的運算符

練習

使用where子句，進行過濾查詢select

查詢姓名為趙雲的學生成績
查詢英語成績大於90分的同學

查詢總分大於200分的所有同學

-- 1
SELECT * FROM `student` WHERE `name`='趙雲';
-- 2
SELECT * FROM `student` WHERE `english`>90;
-- 3
SELECT * FROM `student` WHERE (chinese+english+math)>200;

練習2

使用where子句，練習[5min]:

查詢math大於60並且(and) id大於4的學生成績
查詢英語成績大於語文成績的同學

查詢總分大於200分並且數學成績小於語文成績,的姓趙的學生.

-- 1
SELECT * FROM `student` WHERE `math`>60 and `id`>4;
-- 2
SELECT * FROM `student` WHERE `english`>`chinese`;
-- 3
#韓% 表示名字以韓開頭的就可以，%表示0個到多個字元，可以和Like搭配使用
SELECT * FROM `student` WHERE (chinese+english+math)>200 and `math`<`chinese` and `name` LIKE '趙%';

練習3

查詢英語分數在80 - 90之間的同學。
查詢數學分數為89,90,91的同學。
查詢所有姓李的學生成績。
查詢數學分>80，語文分>80的同學。
查詢總分為189,190,233的同學。
查詢所有姓李或者姓宋的學生成績。

查詢數學比語文多30分的同學。

-- 1
SELECT * FROM `student` WHERE `english`>=80 and `english`<=90;
SELECT * FROM `student` WHERE `english` BETWEEN 80 and 90;-- between and是一個閉區間，[80,90]
-- 2
SELECT * FROM `student` WHERE `math`=89 OR `math`=90 or `math`=91;
SELECT * FROM `student` WHERE `math` IN (89,90,91);
-- 3
SELECT * FROM `student` WHERE `name` Like '李%';
-- 4
SELECT * FROM `student` WHERE `math`>80 and `chinese`>80;
-- 5
SELECT * FROM `student` WHERE (chinese+english+math)=189 OR (chinese+english+math)=190 or (chinese+english+math)=233;
SELECT * FROM `student` WHERE (chinese+english+math) in (189,190,233);
-- 6
SELECT * FROM `student` WHERE `name` Like '李%' or `name` Like '宋%';
-- 7
SELECT * FROM `student` WHERE `math`-`chinese`>30;

使用order by子句排序查詢結果

SELECT column1, column2, column3...
	FROM table
	order by column4 asc|desc,column5 asc|desc,...

Order by指定排序的列，排序的列既可以是表中的列名，也可以是select語句後指定的列名。
Asc升序[預設]、Desc降序
ORDER BY子句應位於SELECT語句的結尾。

練習:

對數學成績排序後輸出【升序】。
對總分按從高到低的順序輸出

對姓李的學生成績[總分]排序輸出(升序)

-- 1
SELECT * FROM `student` ORDER BY `math`;
-- 2
#可以使用別名排序
SELECT *,(chinese+english+math) AS total_score FROM `student` ORDER BY total_score DESC;
-- 3
SELECT `name`,(chinese+english+math) AS total_score FROM `student` WHERE `name` Like '李%' ORDER BY total_score;

函數

統計函數

合計/統計函數- count

Count返回行的總數

Select count(*) | count(列名) from table_name
[WHERE where_definition]

練習:

統計一個班級共有多少學生?
統計數學成績大於90的學生有多少個?
統計總分大於250的人數有多少?

count(*)和count(列)的區別

-- 1
Select count(*) FROM `student`;
-- 2
Select count(*) FROM `student` WHERE `math`>90;
-- 3
SELECT count(*) FROM `student` WHERE (chinese+english+math)>250;
-- 4
#count(*)和count(列)的區別
-- 解釋: count(*)返回滿足條件的記錄的行數
-- count(列):統計滿足條件的某列有多少個，但是會排除為null的情況

合計函數-sum

Sum函數返回滿足where條件的行的和一般使用在數值列

select sum(列名){, sum(列名)...} from tablename
[WHERE where_definition]

練習

統計一個班級數學總成績?
統計一個班級語文、英語、數學各科的總成績
統計一個班級語文、英語、數學的成績總和

統計一個班級語文成績平均分

註意: sum僅對數值起作用，否則沒有意義。

註意:對多列求和，“,”號不能少。

-- 1
SELECT SUM(math) from `student`;
-- 2
select sum(chinese),sum(english),sum(math) from `student`;
-- 3
SELECT SUM(chinese+english+math) from `student`;
-- 4
SELECT SUM(chinese)/count(*) from `student`;

合計函數- avg

AVG函數返回滿足where條件的一列的平均值

Select avg(列名){,avg(列名)...]from tablename
[WHERE where_definition]

練習:

求一個班級數學平均分?

求一個班級總分平均分

-- 1
select avg(math) from `student`;
-- 2
select avg(chinese+english+math) from `student`;

合計函數-Max/min

Max/min函數返回滿足where條件的一列的最大/最小值

select max(列名) from tablename
[WHERE where_definition]

練習:

求班級最高分和最低分(數值範圍在統計中特別有用)

select max(chinese+english+math),min(chinese+english+math) from `student`;

分組統計GROUP BY和HAVING

使用group by子句對列進行分組[先創建測試表]

SELECT column1, column2. column3... FROM table
group by column1 [,column2...];

使用having子句對分組後的結果進行過濾

SELECT column1, column2, column3 ...
FROM table
group by column having ...

group by用於對查詢的結果分組統計

having子句用於限制分組顯示結果.(註意：where是對原始每行的數據過濾，having是對經過分組集成的數據進行進行限制，一般和group by配套使用)

練習

先使用下麵的指令創建表

CREATE TABLE dept( /*部門表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');

#創建表EMP雇員
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*編號*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上級編號*/
hiredate DATE NOT NULL,/*入職時間*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*紅利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號*/
);
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);

#工資級別表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

如何顯示每個部門的平均工資和最高工資
顯示每個部門的每種崗位的平均工資和最低工資

顯示平均工資低於2000的部門號和它的平均工資

-- 1
select deptno,avg(sal),max(sal) from emp group by deptno;
-- 2
select deptno,job,avg(sal),min(sal) from emp group by deptno,job;
-- 3
-- 分析〔寫sql語句的思路是化繁為簡，各個擊破]
-- 3.1 顯示各個部門的平均工資和部門號
-- 3.2 在1的結果基礎上，進行過濾，保留AVG(sal) <2000
select deptno,avg(sal) from emp group by deptno;
select deptno,avg(sal) from emp group by deptno HAVING AVG(sal) <2000;

字元串函數

-- 演示字元串相關函數的使用 ， 使用 emp 表來演示
-- CHARSET(str) 返回字串字元集
SELECT CHARSET(ename) FROM emp; 

-- CONCAT (string2 [,... ]) 連接字串, 將多個列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp; 

-- INSTR (string ,substring ) 返回 substring 在 string 中出現的位置,沒有則返回 0
-- 註意：和java不一樣 ，mysql是從1開始計算順序的
-- dual 亞元表, 系統表 可以作為測試表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL; 

-- UCASE (string2 ) 轉換成大寫
SELECT UCASE(ename) FROM emp; 

-- LCASE (string2 ) 轉換成小寫
SELECT LCASE(ename) FROM emp; 

-- LEFT (string2 ,length )從 string2 中的左邊起取 length 個字元
-- RIGHT (string2 ,length ) 從 string2 中的右邊起取 length 個字元
SELECT ename,LEFT(ename, 2),RIGHT(ename,2) FROM emp;

-- LENGTH (string )string 長度[按照位元組]
SELECT LENGTH(ename) FROM emp; 

-- REPLACE (str ,search_str ,replace_str )
-- 在 str 中用 replace_str 替換所有的 search_str
-- 如果是 manager 就替換成 經理
SELECT ename, REPLACE(job,'MANAGER', '經理') FROM emp;

-- STRCMP (string1 ,string2 ) 逐字元比較兩字串大小(不區分大小寫) 返回數字0(一樣)，-1(string2大),1(string1大)
SELECT STRCMP('hsp', 'jsp') FROM DUAL;#輸出 -1

-- SUBSTRING (str , position [,length ])
-- 從 str 的 position 開始【從 1 開始計算】,取 length 個字元
-- 從 ename 列的第一個位置開始取出 2 個字元
SELECT SUBSTRING(ename, 1, 2) FROM emp; 

-- LTRIM (string2) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或後端或前後兩端空格
SELECT LTRIM(' 韓順平教育') FROM DUAL;
SELECT RTRIM('韓順平教育 ') FROM DUAL;
SELECT TRIM(' 韓順平教育 ') FROM DUAL;

練習:

以首字母小寫的方式顯示所有員工emp表的姓名

SELECT ename,CONCAT(LCASE(LEFT(`ename`,1)),SUBSTRING(ename,2)) from emp;

數學函數

-- 演示數學相關函數

-- ABS(num) 絕對值
SELECT ABS(-10) FROM DUAL; 

-- BIN (decimal_number )十進位轉二進位
SELECT BIN(10) FROM DUAL; 

-- CEILING (number2 ) 向上取整, 得到 >=num2 的最小整數
SELECT CEILING(-1.1) FROM DUAL; 

-- CONV(number2,from_base,to_base) 進位轉換
-- 下麵的 8 是十進位的 8, 轉成 2 進位輸出
SELECT CONV(8, 10, 2) FROM DUAL; 
-- 下麵的 16 是 16 進位的 16, 轉成 10 進位輸出
SELECT CONV(16, 16, 10) FROM DUAL; 

-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整數
SELECT FLOOR(-1.1) FROM DUAL; 

-- FORMAT (number,decimal_places ) 保留小數位數(四捨五入)
SELECT FORMAT(78.125458,2) FROM DUAL; 

-- HEX (DecimalNumber ) 轉十六進位  (括弧內的數是十進位)
SELECT HEX(46) FROM DUAL;#2E

-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL; 

-- MOD (numerator ,denominator ) 求餘
SELECT MOD(10, 3) FROM DUAL; 

-- RAND([seed]) 返回隨機數 其範圍為 0 ≤ v ≤ 1.0
-- 說明
-- 1. 如果使用 rand() 每次返回不同的隨機數 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回隨機數, 範圍 0 ≤ v ≤ 1.0, 如果 seed 不變，該隨機數也不變了
SELECT RAND() FROM DUAL;

-- ROUND(number)  四捨五入
select round(5.5);

時間日期

上面函數的細節說明:

DATE_ADD()中的interval後面可以是year、month、day、hour、minute、second。
DATE SUB()中的interval後面可以是year、month、day、hour、minute、second。
DATEDIFF(date1,date2)得到的是天數，而且是date1-date2的天數，因此可以取負數
DATE、DATE_ADD、DATE_SUB、DATEDIFF這四個函數的日期類型可以是date, datetime或者timestamp

-- 日期時間相關函數【1】

-- CURRENT_DATE ( ) 當前日期
SELECT CURRENT_DATE() FROM DUAL; #2022-09-15
-- CURRENT_TIME ( )當前時間
SELECT CURRENT_TIME() FROM DUAL; #20:23:10
-- CURRENT_TIMESTAMP ( ) 當前時間戳
SELECT CURRENT_TIMESTAMP() FROM DUAL; #2022-09-15 20:23:19
-- NOW( ) 當前時間
-- CURRENT_TIMESTAMP()和NOW()沒區別
SELECT NOW() FROM DUAL;  #2022-09-15 20:23:20

-- LAST_DAY(datetime) 返回datetime當月最後一天的日期
LAST_DAY(NOW()); -- 2022-09-15

實例

-- 創建測試表 信息表
CREATE TABLE mes(
    id INT ,
	content VARCHAR(30), 
    send_time DATETIME);
-- 添加記錄
INSERT INTO mes VALUES(1, '北京新聞', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新聞', NOW());
INSERT INTO mes VALUES(3, '廣州新聞', NOW());
SELECT * FROM mes;

顯示所有留言信息,發佈日期只顯示日期，不用顯示時間.
請查詢在10分鐘內發佈的帖子
請在mysql 的sql語句中求出2011-11-11和1990-1-1相差多少天
請用mysql的sql語句求出你活了多少天?[練習]

如果你能活80歲,求出你還能活多少天.[練習]

-- 日期時間相關函數【2】

-- 1
SELECT id,content,DATE(send_time) FROM mes;
-- 2 (兩種方式)
select * FROM mes WHERE TIMEDIFF(NOW(),send_time)<'00:10:00';
SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
-- 3
SELECT DATEDIFF('2011-11-11','1990-1-1');
-- 4
SELECT DATEDIFF(NOW(), '2000-08-11'); 
-- 5
SELECT DATEDIFF(DATE_ADD('2000-08-11', INTERVAL 80 YEAR),NOW());

-- 日期時間相關函數【3】

-- YEAR|Month|DAY|DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;#2022
SELECT MONTH(NOW()) FROM DUAL;#9
SELECT DAY(NOW()) FROM DUAL;#15
SELECT MONTH('2013-11-10');#11

-- unix_timestamp() : 返回的是 1970-1-1 到現在的秒數
SELECT UNIX_TIMESTAMP() FROM DUAL;

-- FROM_UNIXTIME() : 可以把一個 unix_timestamp 秒數[時間戳]，轉成指定格式的時間
-- %Y-%m-%d 格式是規定好的，表示年月日
-- 意義：在開發中，可以存放一個整數，然後表示時間，通過 FROM_UNIXTIME 轉換
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;

加密和系統函數

-- 演示加密函數和系統函數

-- USER() 查詢用戶
-- 可以查看登錄到 mysql 的有哪些用戶，以及登錄的 IP
SELECT USER(); -- 用戶@IP 地址

-- DATABASE()查詢當前使用資料庫名稱
SELECT DATABASE();

-- MD5(str) 為字元串算出一個 MD5 32 的字元串，常用(用戶密碼)加密
-- root 密碼是 hsp -> 加密 md5 -> 在資料庫中存放的是加密後的密碼
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL; 

-- 演示用戶表，存放密碼時，是 md5
CREATE TABLE hsp_user(id INT , `name` VARCHAR(32) NOT NULL DEFAULT '', pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user VALUES(100, '韓順平', MD5('hsp'));
SELECT * FROM hsp_user;
SELECT * FROM hsp_user WHERE `name`='韓順平' AND pwd = MD5('hsp');

-- PASSWORD(str)
-- 加密函數, MySQL 資料庫的用戶密碼就是 PASSWORD 函數加密SELECT PASSWORD('123456') FROM DUAL; 

-- 資料庫中存儲的密碼是 *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
-- select * from mysql.user \G 從原文密碼 str 計算並返回密碼字元串
-- 通常用於對 mysql 資料庫的用戶密碼加密
-- mysql.user 表示 '資料庫.表'  這樣不不需要切換到mysql表再查user
SELECT user,user.authentication_string FROM mysql.user;#可看到用戶名和加密後的密碼
select password('123456'); #*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 和資料庫中存儲的密碼對應

流程式控制制

先看兩個需求:

查詢emp表,如果comm是null，則顯示0.0
如果emp表的job是CLERK則顯示職員，如果是 MANAGER 則顯示經理如果是SALESMAN則顯示銷售人員,其它正常顯示.

# 演示流程式控制制語句
# IF(expr1,expr2,expr3) 如果 expr1 為 True ,則返回 expr2 否則返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;

# IFNULL(expr1,expr2) 如果 expr1 為空 NULL,返回 expr2,否則返回 expr1
SELECT IFNULL( NULL, '韓順平教育') FROM DUAL;

# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [類似多重分支.]
# 如果 expr1 為 TRUE,則返回 expr2,如果 expr2 為 t, 返回 expr4, 否則返回 expr5
SELECT CASE 
WHEN TRUE THEN 'jack'
WHEN FALSE THEN 'tom' 
ELSE 'mary' END; -- jack

練習

查詢 emp 表, 如果 comm 是 null , 則顯示 0.0
如果 emp 表的 job 是 CLERK 則顯示職員，如果是 MANAGER 則顯示經理-- 如果是 SALESMAN 則顯示銷售人員，其它正常顯示

-- 1
-- 說明，判斷是否為 null 要使用 is null, 判斷不為空 使用 is not
SELECT ename, IF(comm IS NULL , 0.0, comm) FROM emp;
SELECT ename, IFNULL(comm, 0.0) FROM emp;
-- 2
SELECT ename, CASE
WHEN job = 'CLERK' THEN '職員' WHEN job = 'MANAGER' THEN '經理' WHEN job = 'SALESMAN' THEN '銷售人員' ELSE job END AS 'job' FROM emp;
SELECT * FROM emp;

MySQL表查詢--加強

先執行以下語句創建表，如果看過函數部分已經創建過（emp、dept、salgrade這三張表）則忽略

CREATE TABLE dept( /*部門表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');

#創建表EMP雇員
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*編號*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上級編號*/
hiredate DATE NOT NULL,/*入職時間*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*紅利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號*/
);
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);

#工資級別表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

使用where子句

如何查找1992.1.1後入職的員工

如何使用like操作符

%:表示O到多個字元:表示單個字元
如何顯示首字元為S的員工姓名和工資
如何顯示第三個字元為大寫O的所有員工的姓名和工資

如何顯示沒有上級的雇員的情況

查詢表結構

-- 查詢加強

-- ■ 使用 where 子句
-- ?如何查找 1992.1.1 後入職的員工
-- 老師說明： 在 mysql 中,日期類型可以直接比較, 需要註意格式
SELECT * FROM emp WHERE hiredate > '1992-01-01'

-- ■ 如何使用 like 操作符(模糊)
-- %: 表示 0 到多個任意字元 _: 表示單個任意字元
-- ?如何顯示首字元為 S 的員工姓名和工資
SELECT ename, sal FROM emp WHERE ename LIKE 'S%' 
-- ?如何顯示第三個字元為大寫 O 的所有員工的姓名和工資
SELECT ename, sal FROM emp WHERE ename LIKE '__O%' 

-- ■ 如何顯示沒有上級的雇員的情況
SELECT * FROM emp WHERE mgr IS NULL; -- 註意這裡不能用“=”，要用“is”

-- ■ 查詢表結構
DESC emp;

-- 使用 order by 子句
-- ?如何按照工資的從低到高的順序[升序]，顯示雇員的信息
SELECT * FROM emp ORDER BY sal;
-- ?按照部門號升序而同一部門中的雇員的工資降序排列, 顯示雇員信息
SELECT * FROM emp ORDER BY deptno ASC , sal DESC;

分頁查詢

按雇員的id號升序取出,每頁顯示3條記錄，請分別顯示第一頁，第二頁，第三頁
基本語法:select ... limit start, rows

表示從start+1行開始取,取出rows行, start 從0開始計算

練習題:

按雇員的id號降序取出，每頁顯示5條記錄。請分別顯示第3頁，第5頁對應的sql語句

-- 分頁查詢
-- 第 1 頁
SELECT * FROM emp ORDER BY empno LIMIT 0, 3; 
-- 第 2 頁
SELECT * FROM emp ORDER BY empno LIMIT 3, 3; 
-- 第 3 頁
SELECT * FROM emp ORDER BY empno LIMIT 6, 3;

-- 導一個公式
SELECT * FROM emp
ORDER BY empno
LIMIT 每頁顯示記錄數 * (第幾頁-1) , 每頁顯示記錄數;

分組函數和分組子句group by

顯示每種崗位的雇員總數、平均工資。
顯示雇員總數，以及獲得補助(comm非空)的雇員數。
顯示管理者的總人數。(即mgr有多少種)

顯示雇員工資的最大差額。

-- 1
select job,count(*),avg(sal) from  emp group by job;
-- 2
-- 思路: 獲得補助的雇員數 就是 comm 列為非 null, 就是 count(列).如果此處的值為null,不會統計
select count(*),count(comm) from emp;
-- 擴展要求：統計沒有獲得補助的雇員數
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL)) FROM emp;
SELECT COUNT(*), COUNT(*) - COUNT(comm) FROM emp
-- 3
SELECT COUNT(DISTINCT mgr) FROM emp;
-- 4
SELECT MAX(sal) - MIN(sal) FROM emp;

數據分組的總結

如果select語句同時包含有where,group by ,having , limit,order by那麼他們的順序是where,group by,having , order by,limit

SELECT column1, column2, column3... FROM table
	where where_definition
    group by column
    having condition
    order by column
    limit start, rows;

應用案例：

請統計各個部門的平均工資，並且是大於 1000 的，並且按照平均工資從高到低排序，取出前兩行記錄
```
select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal>1000 order by avg_sal desc limit 0,2;
```

MySQL多表查詢

多表查詢是指基於兩個和兩個以上的表查詢.在實際應用中,查詢單個表可能不能滿足你的需求,(如下麵的課堂練習)，需要使用到(dept表和emp表)

多表查詢練習

顯示雇員名,雇員工資及所在部門的名字【笛卡爾集】

小技巧:多表查詢的條件不能少於表的個數-1，否則會出現笛卡爾集?
如何顯示部門號為10的部門名、員工名和工資

顯示各個員工的姓名，工資,及其工資的級別

-- 多表查詢
-- 1. 顯示雇員名,雇員工資及所在部門的名字 【笛卡爾集】
/*
分析
    1. 雇員名,雇員工資 來自 emp 表
    2. 部門的名字 來自 dept 表
    3. 需求對 emp 和 dept 查詢 ename,sal,dname,deptno
    4. 當我們需要指定顯示某個表的列時，需要 表名.列名
*/
SELECT ename,sal,dname,emp.deptno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno;
    
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade; 
-- 小技巧：多表查詢的條件不能少於 表的個數-1, 否則會出現笛卡爾集
-- 2. 如何顯示部門號為 10 的部門名、員工名和工資
SELECT ename,sal,dname,emp.deptno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno AND emp.deptno = 10
    
-- 3.顯示各個員工的姓名，工資，及其工資的級別

-- 思路 姓名，工資 來自 emp 13
-- 工資級別 salgrade 5
-- 寫 sql , 先寫一個簡單，然後加入過濾條件...
select ename, sal, grade
    from emp , salgrade
    where sal between losal and hisal;
    
-- 練習:顯示雇員名(ename),雇員工資(sal)及所在部門的名字(dname),並按部門排序[降序排].
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno order by dname desc;

自連接

自連接是指在同一張表的連接查詢[將同一張表看做兩張表]。

思考題:

顯示公司員工和他的上級的名字（給表取別名，使其能當兩張表用）

select worker.ename as '員工名',boss.ename as '上級' from emp worker,emp boss where worker.mgr=boss.empno;

子查詢

什麼是子查詢：

子查詢是指嵌入在其它sql語句中的select語句,也叫嵌套查詢

單行子查詢：

單行子查詢是指只返回一行數據的子查詢語句

請思考:如何顯示與SMITH同一部門的所有員工?

多行子查詢：

多行子查詢指返回多行數據的子查詢使用關鍵字in

-- 子查詢的演示

-- 單行子查詢
-- 請思考：如何顯示與 SMITH 同一部門的所有員工?
/*
    1. 先查詢到 SMITH 的部門號得到
    2. 把上面的 select 語句當做一個子查詢來使用
*/
SELECT deptno FROM emp WHERE ename = 'SMITH' ;
-- 下麵的答案. SELECT *
FROM emp WHERE deptno = (
SELECT deptno FROM emp WHERE ename = 'SMITH' );

-- 多行子查詢
-- 練習:如何查詢和部門 10 的工作相同的雇員的
-- 名字、崗位、工資、部門號, 但是不含 10 號部門自己的雇員.
/*
    1. 查詢到 10 號部門有哪些工作
    2. 把上面查詢的結果當做子查詢使用
*/
select distinct job from emp where deptno=10;
select ename, job,sal,deptno from emp where job in (
    select distinct job from emp where deptno=10) and
    deptno != 10;

子查詢當做臨時表使用

練習題：

先使用ecshop.sql文件創建資料庫---- 下載

查詢ecshop中各個類別(cat_id)中，價格最高的商品.結果如下:

提示，可以將子查詢當做一張臨時表使用

select * from ecshop.ecs_goods;
select goods_id,cat_id,goods_name,shop_price from ecshop.ecs_goods;

-- 1
-- 先得到 各個類別中，價格最高的商品 max + group by cat_id, 當做臨時表
select cat_id,Max(shop_price) from ecshop.ecs_goods group by cat_id;
-- 把子查詢當做一張臨時表可以解決很多很多複雜的查詢
select goods_id,cat_id,goods_name,shop_price from ecshop.ecs_goods;

select temp.cat_id,goods_id,goods_name,shop_price 
from (
    select cat_id,Max(shop_price) as max_price  
    from ecshop.ecs_goods 
    group by cat_id
	) temp,ecs_goods 
    where temp.cat_id=ecs_goods.cat_id 
    and temp.max_price=ecs_goods.shop_price;

在多行子查詢中使用all操作符

請思考:如何顯示工資比部門30的所有員工的工資高的員工的姓名工資和部門號

-- 使用max()
select ename,sal,deptno from emp where sal >(select max(sal) from emp where deptno=30);
-- 使用all()
select ename,sal,deptno from emp where sal >all(select sal from emp where deptno=30);

在多行子查詢中使用any操作符

請思考:如何顯示工資比部門30的其中一個員工的工資高的員工的姓名、工資和部門號

-- 使用min()
select ename,sal,deptno from emp where sal >(select min(sal) from emp where deptno=30);
-- 使用any()
select ename,sal,deptno from emp where sal >any(select sal from emp where deptno=30);

多列子查詢

多列子查序則是指查詢返回多個列數據的子查詢語句

(欄位1,欄位2…)=(select 欄位1,欄位2 from ...)

練習題：

請查詢student表中和宋江數學，英語，語文完全相同的學生

請思考如何查詢emp表中與allen的部門和崗位完全相同的所有雇員(並且不含smith本人)

-- 1
select math,english,chinese from student where name='宋江';
select * from student where (math,english,chinese)=(select math,english,chinese from student where name='宋江') and name!='宋江';
-- 2
select deptno,job from emp where LCASE(ename)='allen';
SELECT * FROM emp WHERE (deptno,job)=(select deptno,job from emp where LCASE(ename)='allen') and LCASE(ename)!='allen';

課後練習(先自己做)

emp表中，查找每個部門工資高於本部門平均工資的人的資料
emp表中，查找每個部門工資最高的人的詳細資料
查詢每個部部門的信息(包括:部門名,編號,地址(dept表中))和人員數量,

-- 1
select deptno,avg(sal) as avg_sal from emp group by deptno;
select ename,sal,avg_sal,temp.deptno from emp,(select deptno,avg(sal) as avg_sal from emp group by deptno) temp where emp.deptno=temp.deptno and emp.sal>avg_sal;
-- 2
select deptno,max(sal) as max_sal from emp group by deptno;
select empno,ename,job,sal,emp.deptno from emp,(select deptno,max(sal) as max_sal from emp group by deptno) temp where emp.deptno=temp.deptno and sal=max_sal;
-- 3
-- 表名.* 表示將該表所有列都顯示出來, 可以簡化 sql 語句
select deptno,count(*) as count_dept from emp group by deptno;
select dept.*,count_dept from dept,(select deptno,count(*) as count_dept from emp group by deptno) temp where dept.deptno=temp.deptno;

合併查詢

有時在實際應用中，為了合併多個select語句的結果，可以使用集合操作符號

union all

該操作符用於取得兩個結果集的並集。當使用該操作符時，不會取消重覆行。（註意：可以理解為把第二個表的內容接在第一個表的下麵。不需要數據類型一致，但列數必須相同）
```
select ename,sal,job from emp where sal>2500 
union 
select ename,job,sal from emp where job='MANAGER';
```

union

該操作賦與union all相似，但是會自動去掉結果集中重覆行

select ename,sal,job from emp where sal>2500 
union all 
select ename,sal,job from emp where job='manager';

mysql表內連接

select ... from 表1 [inner] join 表2 on 條件 
-- inner加不加都一樣

獲取兩個表中欄位匹配關係的記錄。

個人認為內連接用處不大，它能處理的問題用之前的自連接知識也能處理。

mysql表外連接

提出一個問題

前面我們學習的查詢，是利用where子句對兩張表或者多張表，形成的笛卡爾積進行篩選，根據關聯條件，顯示所有匹配的記錄，匹配不上的，不顯示
比如:列出部門名稱和這些部門的員工名稱和工作，同時要求顯示出那些沒有員工的部門。
使用我們學習

Java學習-第一部分-第三階段-第三節：MySQL基礎

零基礎學MySQL

MySQL安裝和配置

安裝Navicat

資料庫

創建

查看、刪除資料庫

備份恢複數據庫

表

創建

刪除

修改

Mysql數據類型

數值型（整數）的基本使用

數值型(bit)的使用

數值型(小數)的基本使用

字元串的基本使用

日期類型的基本使用

CRUD

lnsert(添加數據)

Update（更新數據）

Delete（刪除數據）

Select（查找數據-單表）

在where子句中經常使用的運算符

使用order by子句排序查詢結果

函數

統計函數

合計/統計函數- count

合計函數-sum

合計函數- avg

合計函數-Max/min

分組統計GROUP BY和HAVING

字元串函數

數學函數

時間日期

加密和系統函數

流程式控制制

MySQL表查詢--加強

使用where子句

如何使用like操作符

查詢表結構

分頁查詢

分組函數和分組子句group by

數據分組的總結

MySQL多表查詢

自連接

子查詢

子查詢當做臨時表使用

在多行子查詢中使用all操作符

在多行子查詢中使用any操作符

多列子查詢

合併查詢

mysql表內連接

mysql表外連接