天天看點
傳回

Mysql必知必會！

資料庫

1. 資料庫概述

1.1 資料庫概述

什麼是資料庫

資料庫就是存儲資料的倉庫，其本質是一個檔案系統，資料按照特定的格式将資料存儲起來，使用者可以對資料庫中的資料進行增加，修改，删除及查詢操作

一個小例子

假設我們現在沒有資料庫,我們想開發一個本地的電話本軟體,也就是手機中通訊錄,這個軟體有記錄的功能,需要記錄聯系人姓名,電話号碼,生日,性别等資訊,由于是要持久化資料,是以我們隻能寫到檔案中,比如phone.txt.例如:

Mysql必知必會！

第一行是表頭,其他行是内容,資料之間用逗号分隔,每行是一條資料,這樣設計完成之後就可以按行讀取,并且能夠按照逗号進行拆分存入到JavaBean中去了,現在的程式架構是這個樣子的:

Mysql必知必會！

功能被完美的實作了,但是随着程式的演進發現了一些問題,例如想要找到所有的男性電話,或者查找今天過生日的人來給他們發一些過節短信等等的功能,需要不斷的編碼,而本質上都是在讀取其中的檔案資訊,并且最要命的是無論什麼功能都需要把所有的硬碟資料先加載進記憶體當中,即使這些資料時不需要的,那麼如何能做到屏蔽掉檔案系統,隻讀取一些需要的資料呢,編碼界有一句老話”所有的計算機問題都可以通過增加一個中間層來解決”,于是你覺定增加一個中間層,這個中間層上有邏輯的資料結構,其實就是[編号,姓名,性别,電話号碼,生日]這些東西, 這些東西被叫做表,而其中的每一項被稱為”列”,每一列都要有類型,例如字元型,日期型,數字型等等,并且可以使用專業的語句來進行查詢,我們決定叫它SQL,即Structured Query Language 結構化查詢語言,那麼現在軟體的架構變成了這種

Mysql必知必會！

同時由于在程式和實體層之間抽象了一層,是以在優化實體層存儲的時候,可以不影響上層應用程式的邏輯,可以使用索引,B+樹等緩存手段了

這就是關系型資料庫

資料庫管理系統

  • 什麼是資料庫管理系統

    資料庫管理系統（DataBase Management System，DBMS）：指一種操作和管理資料庫的大型軟體，用于建立、使用和維護資料庫，對資料庫進行統一管理和控制，以保證資料庫的安全性和完整性。使用者通過資料庫管理系統通路資料庫中表内的資料。

  • 常見的資料庫管理系統
  • MYSQL：開源免費的資料庫，小型的資料庫.已經被Oracle收購了.MySQL6.x版本也開始收費。
  • Oracle：收費的大型資料庫，Oracle公司的産品。Oracle收購SUN公司，收購MYSQL。
  • DB2：IBM公司的資料庫産品,收費的。常應用在銀行系統中
  • SQLServer：MicroSoft 公司收費的中型的資料庫。C#、.net等語言常使用
  • SQLite: 嵌入式的小型資料庫，應用在手機端
  • 資料庫與資料庫管理系統的關系
    Mysql必知必會！

1.2 資料庫表

資料庫中以表為組織機關存儲資料。

表類似我們的Java類，每個字段都有對應的資料類型。

那麼用我們熟悉的java程式來與關系型資料對比，就會發現以下對應關系。

Java 資料庫
類 表
類中屬性 表中字段
對象 記錄

1.3 資料表

根據表字段所規定的資料類型，我們可以向其中填入一條條的資料，而表中的每條資料類似類的執行個體對象。表中的一行一行的資訊我們稱之為記錄

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-j98syN7S-1601379903511)(http://47.93.60.69:88/img/pics/574F150A2A7B4697A68AD2B228F8B9F2.png?x-oss-process=style/CfyInfo)]

2. SQL語句

資料庫是不認識JAVA語言的，但是我們同樣要與資料庫互動，這時需要使用到資料庫認識的語言SQL語句，它是資料庫的代碼。

結構化查詢語言(Structured Query Language)簡稱SQL，是一種資料庫查詢和程式設計語言，用于存取資料以及查詢、更新和管理關系資料庫系統。

建立資料庫、建立資料表、向資料表中添加一條條資料資訊均需要使用SQL語句

3.1 SQL分類

  • 資料定義語言：簡稱DDL(Data Definition Language)，用來定義資料庫對象：資料庫，表，列等。關鍵字：create，alter，drop等
  • 資料操作語言：簡稱DML(Data Manipulation Language)，用來對資料庫中表的記錄進行更新。關鍵字：insert，delete，update等
  • 資料控制語言：簡稱DCL(Data Control Language)，用來定義資料庫的通路權限和安全級别，及建立使用者
  • 資料查詢語言：簡稱DQL(Data Query Language)，用來查詢資料庫中表的記錄。關鍵字：select，from，where等

3.2 SQL通用文法

  • SQL語句可以單行或多行書寫，以分号結尾
  • 可使用空格和縮進來增強語句的可讀性
  • MySQL資料庫的SQL語句不區分大小寫，建議使用大寫，例如：SELECT * FROM user
  • 同樣可以使用/**/的方式完成注釋
  • MySQL中常用的資料類型如下:
類型 描述
int 整型
double 浮點型
varchar 字元串類型
date 日期類型,格式為yyyy-MM-dd,隻有年月日,沒有時分秒

詳細的資料類型

MySQL支援多種類型，大緻可以分為三類：數值、日期/時間和字元串(字元)類型

數值類型

MySQL支援所有标準SQL數值資料類型。

這些類型包括嚴格數值資料類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值資料類型(FLOAT、REAL和DOUBLE PRECISION)。

關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

BIT資料類型儲存位字段值，并且支援MyISAM、MEMORY、InnoDB和BDB表。

作為SQL标準的擴充，MySQL也支援整數類型TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數類型的存儲和範圍。

類型 大小 範圍（有符号） 範圍（無符号） 用途
TINYINT 1 位元組 (-128，127) (0，255) 小整數值
SMALLINT 2 位元組 (-32 768，32 767) (0，65 535) 大整數值
MEDIUMINT 3 位元組 (-8 388 608，8 388 607) (0，16 777 215) 大整數值
INT或INTEGER 4 位元組 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整數值
BIGINT 8 位元組 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 極大整數值
FLOAT 4 位元組 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 單精度
浮點數值
DOUBLE 8 位元組 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 雙精度
浮點數值
DECIMAL 對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2 依賴于M和D的值 依賴于M和D的值 小數值
日期和時間類型

表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間類型有一個有效值範圍和一個”零”值，當指定不合法的MySQL不能表示的值時使用”零”值。

TIMESTAMP類型有專有的自動更新特性，将在後面描述。

| 類型 | 大小

(位元組) 範圍 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD
TIME 3 ‘-838:59:59’/’838:59:59’ HH:MM:SS
YEAR 1 1901/2155 YYYY
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS
TIMESTAMP 4

1970-01-01 00:00:00/2038

結束時間是第 2147483647 秒，中原標準時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日 淩晨 03:14:07

| YYYYMMDD HHMMSS | 混合日期和時間值，時間戳 |

字元串類型

字元串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些類型如何工作以及如何在查詢中使用這些類型。

類型 大小 用途
CHAR 0-255位元組 定長字元串
VARCHAR 0-65535 位元組 變長字元串
TINYBLOB 0-255位元組 不超過 255 個字元的二進制字元串
TINYTEXT 0-255位元組 短文本字元串
BLOB 0-65 535位元組 二進制形式的長文本資料
TEXT 0-65 535位元組 長文本資料
MEDIUMBLOB 0-16 777 215位元組 二進制形式的中等長度文本資料
MEDIUMTEXT 0-16 777 215位元組 中等長度文本資料
LONGBLOB 0-4 294 967 295位元組 二進制形式的極大文本資料
LONGTEXT 0-4 294 967 295位元組 極大文本資料

CHAR和VARCHAR類型類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。

BINARY和VARBINARY類類似于CHAR和VARCHAR，不同的是它們包含二進制字元串而不要非二進制字元串。也就是說，它們包含位元組字元串而不是字元字元串。這說明它們沒有字元集，并且排序和比較基于列值位元組的數值值。

BLOB是一個二進制大對象，可以容納可變數量的資料。有4種BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們隻是可容納值的最大長度不同。

有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB類型，有相同的最大長度和存儲需求。

3 SQL

3.1 DDL

資料庫操作

  • 建立資料庫 
create database 資料庫名;
create database if not exists 資料庫名; -- 如果不存在則建立
  • 檢視資料庫

檢視MySQL伺服器中的所有資料庫:

show databases;
  • 檢視某個資料庫的定義資訊 
show create database 資料庫名;
  • 删除資料庫 
drop database 資料庫名稱;
  • 切換資料庫 
use 資料庫名;

表結構相關語句

  • 建立表

格式:

create table 表名(
   字段名 類型(長度) 限制,
   字段名 類型(長度) 限制
);

例如:

CREATE TABLE sort (
  sid INT, #分類ID 
  sname VARCHAR(100) #分類名稱
);
CREATE TABLE sort (
  sid INT, #分類ID 
  sname VARCHAR(100) #分類名稱
)CHARSET=utf8;

  • 主鍵限制

    主鍵是用于辨別目前記錄的字段。它的特點是非空，唯一。在開發中一般情況下主鍵是不具備任何含義，隻是用于辨別目前記錄

格式:

1. 在建立表時建立主鍵,在字段後面加上 primary key

CREATE TABLE 表名(
    id int primary key,
    ....
);      
在建立表時建立主鍵,在表建立的最後來指定主鍵      
CREATE TABLE 表名(
    id int,
    ....,
    primary key(id)
);      
删除主鍵:      
alter table 表名 drop primary key;
  1. 主鍵自增長(隻适用于MySQL)

一般主鍵是自增長的字段,不需要指定,實作添加自增長語句,主鍵字段後加auto_increment,例如:

CREATE TABLE sort (
  sid INT PRIMARY KEY auto_increment, #分類ID 
  sname VARCHAR(100) #分類名稱
);
  • 檢視表

檢視資料庫中所有的表

show tables;
  • 檢視表結構 
desc 表名;
Mysql必知必會！
  • 檢視建表語句 
show create table 表名;
  • 删除表

格式:

drop table 表名;
  • 修改表結構

删除列:

alter TABLE 表名 DROP 列名;

修改表名:

RENAME TABLE 表名 TO 新表名;

修改表的字元集

alter TABLE 表名 CHARACTER SET 字元集

修改列名

alter TABLE 表名 CHANGE 列名 新列名 列類型;

添加列

alter table 表名 add 列名 列類型;

3.2 DML操作

首先先知道查詢表中所有資料的語句:

SELECT * FROM 表名;

DML是對表中的資料進行增、删、改的操作。不要與DDL混淆了,包含:

  • INSERT: 插入
  • UPDATE: 更新
  • DELETE: 删除

小知識:

在mysql中,字元串類型和日期類型都要用單引号括起來: ‘tom’ ‘2015-09-04’

空值：null

3.2.1 插入操作: INSERT:

文法:

INSERT INTO 表名（列名1，列名2 ...）VALUES(列值1，列值2...);

注意:

  • 列名與與列值的類型、個數、順序要一一對應
  • 可以把列名當做java中的形參，把列值當做實參
  • 值不要超出列定義的長度
  • 如果插入空值，請使用null
  • 插入的日期和字元一樣，都使用引号括起來
練習

建立表 emp 并插入資料,表結構如下

列名 列類型
id int
name varchar(100)
gender varchar(10)
birthday date
salary float(10,2)
entry_date date
resume text 
create table emp(
id int,
name varchar(100),
gender varchar(10),
birthday date,
salary float(10,2),
entry_date date,
resume text
);

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','good girl');

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(2,'lisi','male','1995-5-10',10000,'2015-5-5','good boy');

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(3,'wangwu','male','1995-5-10',10000,'2015-5-5','good boy');

-- 批量插入
INSERT INTO emp VALUES
(4,'zs','m','2015-09-01',10000,'2015-09-01',NULL),
(5,'li','m','2015-09-01',10000,'2015-09-01',NULL),
(6,'ww','m','2015-09-01',10000,'2015-09-01',NULL);

3.2.2 修改操作: UPDATE

文法:

UPDATE 表名 SET 列名1=列值1，列名2=列值2... WHERE 列名=值
練習
  • 将所有員工薪水修改為5000元。 
UPDATE emp SET salary=5000
  • 将姓名為​

    ​zhangsan​

     ​的員工薪水修改為3000元 
UPDATE emp SET salary=3000 WHERE name=’ zhangsan’;
  • 将姓名為​

    ​lisi​

     ​的員工薪水修改為4000元,job改為ccc 
UPDATE emp SET salary=4000,gender='female' WHERE name='lisi';
  • 将所有男性的薪水在原有基礎上加1000 
UPDATE emp SET salary=salary+1000 WHERE gender='male';

删除操作

文法

DELETE FROM 表名 [WHERE 列名=值]
練習
  • 删除表中姓名為zhangsan的記錄 
DELETE FROM emp WHERE name = 'zhangsan';
  • 删除表中所有記錄 
DELETE FROM emp;

3.3 DQL 操作

DQL資料查詢語言 （重要）

資料庫執行DQL語句不會對資料進行改變，而是讓資料庫發送結果集給用戶端。

查詢傳回的結果集是一張 虛拟表。

文法

SELECT 列名 FROM表名 
[WHERE -> GROUP BY -> HAVING -> ORDER BY]

SELECT selection_list /要查詢的列名稱/

FROM table_list /要查詢的表名稱/

WHERE condition /行條件/

GROUP BY grouping_columns /對結果分組/

HAVING condition /分組後的行條件/

ORDER BY sorting_columns /對結果分組/

LIMIT offset_start, row_count /結果限定/

準備工作

建立表stu

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-eZntEeUY-1601379903514)(http://47.93.60.69:88/img/pics/1B9ABC334FAB44B88306CA96D3F8416B.png?x-oss-process=style/CfyInfo)]

CREATE TABLE stu (
    sid CHAR(6),
    sname       VARCHAR(50),
    age     INT,
    gender  VARCHAR(50)
);
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

建立 雇員表:emp

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-lB46tHZ2-1601379903514)(http://47.93.60.69:88/img/pics/C93B8F81D252459A8A3236E599CBCFCA.png?x-oss-process=style/CfyInfo)]

CREATE TABLE emp(
    empno       INT,
    ename       VARCHAR(50),
    job     VARCHAR(50),
    mgr     INT,
    hiredate    DATE,
    sal     DECIMAL(7,2),
    comm        decimal(7,2),
    deptno      INT
);
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

部分表:dept

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-AyFyFVaX-1601379903515)(http://47.93.60.69:88/img/pics/E89AE08AD402411C8B667216B6DC94E9.png?x-oss-process=style/CfyInfo)]

CREATE TABLE dept(
    deptno      INT,
    dname       varchar(14),
    loc     varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

1. 基礎查詢

1.1 查詢所有列 
SELECT * FROM stu;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-suBMlMPz-1601379903516)(http://47.93.60.69:88/img/pics/89A6FAC7CD0A419CBF49AA2D21C59745.png?x-oss-process=style/CfyInfo)]

1.2 查詢指定列 
SELECT sid, sname, age FROM stu;
Mysql必知必會！

2. 條件查詢

2.1 條件查詢介紹
  • 條件查詢就是在查詢時給出WHERE子句，在WHERE子句中可以使用如下運算符及關鍵字:
  • =、!=、<>、<、<=、>、>=；
  • BETWEEN…AND；
  • IN(set)；
  • IS NULL； IS NOT NULL
  • AND；
  • OR；
  • NOT；
2.2 查詢性别為女,并且年齡為50的學生資訊 
SELECT * FROM stu
WHERE gender='female' AND age<50;
Mysql必知必會！
2.3 查詢學号為S_1001，或者姓名為liSi的記錄 
SELECT * FROM stu
WHERE sid ='S_1001' OR sname='liSi';
Mysql必知必會！
2.4 查詢學号為S_1001，S_1002，S_1003的記錄 
SELECT * FROM stu 
WHERE sid IN ('S_1001','S_1002','S_1003');
Mysql必知必會！
2.5 查詢學号不是S_1001，S_1002，S_1003的記錄 
SELECT * FROM tab_student 
WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-KjOjMNfH-1601379903519)(http://47.93.60.69:88/img/pics/0EDDC5A2AB214007BA48D362B32C23AE.png?x-oss-process=style/CfyInfo)]

2.6 查詢年齡為null的記錄 
SELECT * FROM stu
WHERE age IS NULL;
2.7 查詢年齡在20-40之間學生的記錄 
SELECT * 
FROM stu
WHERE age>=20 AND age<=40;

或者

SELECT * 
FROM stu 
WHERE age BETWEEN 20 AND 40;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-h74NmfMF-1601379903519)(http://47.93.60.69:88/img/pics/C5DF552B275F4645A245CAC80271DE6C.png?x-oss-process=style/CfyInfo)]

2.8 查詢性别非男的學生記錄 
SELECT * 
FROM stu
WHERE gender!='male';

或

SELECT * 
FROM stu
WHERE gender<>'male';

或

SELECT * 
FROM stu
WHERE NOT gender='male';
Mysql必知必會！
2.9 查詢姓名不為null的學生記錄 
SELECT * 
FROM stu
WHERE sname IS NOT NULL;

或

SELECT * 
FROM stu
WHERE NOT sname IS NULL;

3 模糊查詢

前面介紹的所有操作符都是針對已知值進行過濾的,不管是比對一個還是多個值,測試大于還是小于已知值,或者檢查摸個範圍的值,共同點是過濾中使用的值都是已知的.但是,這種過濾方法并不是任何時候都好用,例如當想查詢中包含a字母的學生時就需要使用模糊查詢了。模糊查詢需要使用關鍵字LIKE

在使用like關鍵字時,通常和通配符配合使用

  • 通配符: 用來比對一部分的特殊字元
  • _ : 比對任意一個字元
  • % : 任意0~n個字元
3.1 查詢姓名由5個字母構成的學生記錄 
SELECT * 
FROM stu
WHERE sname LIKE '_____'
-- 模糊查詢必須使用LIKE關鍵字。其中 “_”比對任意一個字母，5個“_”表示5個任意字母
3.2 查詢姓名由5個字母構成，并且第5個字母為“i”的學生記錄 
SELECT * 
FROM stu
WHERE sname LIKE '____i';
3.3 查詢姓名以“z”開頭的學生記錄 
SELECT * 
FROM stu
WHERE sname LIKE 'z%';
-- 其中“%”比對0~n個任何字母。
3.4 查詢姓名中第二個字母是i的學生記錄 
SELECT * 
FROM stu
WHERE sname LIKE '_i%';
3.5 查詢姓名中包含“a”字母的學生記錄 
SELECT * 
FROM stu
WHERE sname LIKE '%a%';

4. 字段控制查詢

4.1 去除重複記錄

去除重複記錄（兩行或兩行以上記錄中系列的上的資料都相同），例如emp表中sal字段就存在相同的記錄。當隻查詢emp表的sal字段時，那麼會出現重複記錄，那麼想去除重複記錄，需要使用DISTINCT

SELECT DISTINCT sal FROM emp;
Mysql必知必會！

資料是沒有重複的

4.2 檢視雇員的月薪和傭金之和

因為sal和comm兩列的類型都是數值類型，是以可以做加運算。如果sal或comm中有一個字段不是數值類型，那麼會出錯

SELECT *,sal+comm FROM emp;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-J3fSLy0p-1601379903521)(http://47.93.60.69:88/img/pics/373E38DCD1364154ADCAF9246C454972.png?x-oss-process=style/CfyInfo)]

而comm列有很多記錄的值為NULL，因為任何東西與NULL相加結果還是NULL，是以結算結果可能會出現NULL。下面使用了把NULL轉換成數值0的函數IFNULL:

SELECT *,sal+IFNULL(comm,0) FROM emp;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-rjbiYwo6-1601379903522)(http://47.93.60.69:88/img/pics/418E1DE12D254163BCA5399B03CD46DA.png?x-oss-process=style/CfyInfo)]

4.3 給列添加别名

在上面查詢中出現列名為sal+IFNULL(comm,0)，這很不美觀，現在我們給這一列給出一個别名，為total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-BhEnQ3t3-1601379903522)(http://47.93.60.69:88/img/pics/F8EFF5C68E6D48B8907A24685E1E1F34.png?x-oss-process=style/CfyInfo)]

給列起别名時，是可以省略AS關鍵字的：

SELECT *,sal+IFNULL(comm,0)  total FROM emp;

5. 排序

排序使用 ​

​order by 列名 asc/desc​

​ 作為文法

預設是asc(升序) 可以指定 desc 降序

5.1 查詢所有學生記錄，按年齡升序排序 
SELECT *
FROM stu
ORDER BY age ASC;

或者

SELECT *
FROM stu
ORDER BY age;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-FlfKpZOf-1601379903523)(http://47.93.60.69:88/img/pics/AB19814F736C4380A4D8407FAA8383AF.png?x-oss-process=style/CfyInfo)]

5.2 查詢所有學生記錄，按年齡降序排序 
SELECT *
FROM stu
ORDER BY age DESC;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-yrjBGcCk-1601379903523)(http://47.93.60.69:88/img/pics/6A787C8A116445529007686652D70D87.png?x-oss-process=style/CfyInfo)]

5.3 查詢所有雇員，按月薪降序排序，如果月薪相同時，按編号升序排序 
SELECT * FROM emp
ORDER BY sal DESC,empno ASC;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-sej69qu9-1601379903524)(http://47.93.60.69:88/img/pics/2EFC18B3290E4C009955BCCFC05AA26F.png?x-oss-process=style/CfyInfo)]

6. 聚合函數

聚合函數是用來做縱向運算的函數

  • COUNT()：統計指定列不為NULL的記錄行數；
  • MAX()：計算指定列的最大值，如果指定列是字元串類型，那麼使用字元串排序運算；
  • MIN()：計算指定列的最小值，如果指定列是字元串類型，那麼使用字元串排序運算；
  • SUM()：計算指定列的數值和，如果指定列類型不是數值類型，那麼計算結果為0；
  • AVG()：計算指定列的平均值，如果指定列類型不是數值類型，那麼計算結果為0；
6.1 COUNT

當需要縱向統計時使用COUNT(),COUNT小括号中可以放入指定列名,和* 如果是* 則代表查詢的是結果集的行數,如果是列名,則是指定列的行數

查詢emp表中記錄數

SELECT COUNT(*) AS cnt FROM emp;
Mysql必知必會！

查詢emp表中有傭金的人數

SELECT COUNT(comm) cnt FROM emp;
Mysql必知必會！
注意，因為count()函數中給出的是comm列，那麼隻統計comm列非NULL的行數

查詢emp表中月薪大于2500的人數

SELECT COUNT(*) AS '人數' FROM emp
WHERE sal > 2500;
Mysql必知必會！

統計月薪與傭金之和大于2500元的人數

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;
Mysql必知必會！

查詢有傭金的人數，有上司的人數

SELECT COUNT(comm), COUNT(mgr) FROM emp;
Mysql必知必會！
6.2 SUM和AVG

當需要縱向求和時使用sum()函數。當需要求平均值時使用avg()函數

  • 查詢所有雇員的月薪和 
SELECT SUM(sal) FROM emp;
Mysql必知必會！
  • 查詢所有雇員月薪和，以及所有雇員傭金和 
SELECT SUM(sal), SUM(comm) FROM emp;
Mysql必知必會！
  • 查詢所有雇員月薪+傭金和 
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
Mysql必知必會！
  • 統計所有員工的平均工資 
SELECT AVG(sal) FROM emp;
Mysql必知必會！
6.3 MAX 和 MIN

MAX和MIN 是用來查詢最大值和最小值的

  • 查詢員工的最高工資和最低工資: 
SELECT MAX(sal), MIN(sal) FROM emp;
Mysql必知必會！

7. 分組查詢

當需要分組查詢時需要使用GROUP BY子句，例如查詢每個部門的工資和，這說明要使用部門來分組

注意:

凡是和聚合函數同時出現的列名，一定要寫在group by 之後

分組時候是無法展現單個資料的

group by 一般會合聚合函數配合使用,單獨使用的時候意義不大

7.1 分組查詢
  • 查詢每個部門編号和每個部門的工資和: 
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;
Mysql必知必會！
  • 查詢每個部門的部門編号以及每個部門的人數 
SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;
Mysql必知必會！
  • 查詢每個部門的編号以及每個部門工資大于1500的人數: 
SELECT deptno,COUNT(*)
FROM emp
WHERE sal>1500
GROUP BY deptno;
Mysql必知必會！
7.2 HAVING字句
  • 查詢工資總和大于9000的部門編号以及工資總和: 
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;
Mysql必知必會！

having和where的差別

1. having是在分組後對資料進行過濾,而where是在分組前對資料進行過濾

2. having後面可以使用聚合函數(統計函數),where後面不可以使用聚合函數

WHERE是對分組前記錄的條件，如果某行記錄沒有滿足WHERE子句的條件，那麼這行記錄不會參加分組；而HAVING是對分組後資料的限制

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-YELjCp4s-1601379903534)(http://47.93.60.69:88/img/pics/D986B892FBA746EF8DA7B39EAF7DB549.png?x-oss-process=style/CfyInfo)]

  • 統計出各個部門的各個崗位中,平均工資>1000的資訊 
SELECT
  job,
  deptno,
  avg(sal)
FROM emp
GROUP BY job, deptno
HAVING avg(sal) > 1000;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-WKDpqV4L-1601379903535)(http://47.93.60.69:88/img/pics/03873B9867B2427E9F2622C90A0347BF.png?x-oss-process=style/CfyInfo)]

8. LIMIT

LIMIT用來限定查詢結果的起始行，以及總行數。

  • 查詢5行記錄，起始行從0開始 
SELECT * FROM emp LIMIT 0, 5;

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-rerGTXtA-1601379903536)(http://47.93.60.69:88/img/pics/3E16D398683A4D01A5EFAA66D11AACED.png?x-oss-process=style/CfyInfo)]

查詢語句的書寫順序:

select – from- where- group by- having- order by-limit

查詢語句的執行順序:

from - where -group by - having - select - order by-limit
sql mysql 資料庫 資料 表名
上一篇: mysql 我的學習
下一篇: 小白學MySQL，這點基礎你都不知道？

繼續閱讀