MySQL基礎知識點串講PART01

lr1descent發表於2024-11-03
MySql

MySQL--DAY01

基本概念

資料庫：英文單詞DataBase，簡稱DB。按照一定格式儲存資料的一些檔案的組合。

顧名思義：儲存資料的倉庫，實際上就是一堆檔案。這些檔案中儲存了具有特定格式的資料。

資料庫管理系統：DataBaseManagement，簡稱DBMS。

資料庫管理系統是專門用來管理資料庫中資料的，資料庫管理系統可以對資料庫當中的資料進行增刪改查。

常見的資料庫管理系統：MySQL、Oracle、MS SqlServer、DB2、sybase等....

SQL：結構化查詢語言。程式設計師需要學習SQL語句，程式設計師透過編寫SQL語句，然後DBMS負責執行SQL語句，最終來完成資料庫中資料的增刪改查操作。

SQL是一套標準，程式設計師主要學習的就是SQL語句，這個SQL在mysql中可以使用，同時在Oracle中也可以使用，在DB2中也可以使用。

它們的關係：DBMS--執行--> SQL --操作--> DB

安裝

需要注意的事項？

  1. 埠號：
    埠號port是任何一個軟體/應用都會有的，埠號是應用的唯一代表。
    埠號通常和IP地址在一塊，IP地址用來定位計算機的，埠號port
    是用來定位計算機上某個服務的/某個應用的！
    在同一臺計算機上，埠號不能重複。具有唯一性。

​ mysql資料庫啟動的時候，這個服務佔有的預設埠號是3306。記住。

  1. 字元編碼方式？設定mysql資料庫的字元編碼方式為 UTF8

  2. 服務名稱？預設是：MySQL不用改。

    1. 選擇配置環境變數path：
      如果沒有選擇怎麼辦？可以手動配置
      path=其它路徑;C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin

    mysql超級管理員使用者名稱不能改，一定是：root
    需要設定mysql資料庫超級管理員的密碼。

基本命令

連線資料庫

// 配置好環境變數後可以透過cmd視窗進行資料庫的連線
mysql -uroot -p
>>enter your password:

退出

exit

檢視資料庫

show databases;
// mysql 自帶四個資料庫
	| Database           |
	+--------------------+
	| information_schema |
	| mysql              |
	| performance_schema |
	| test               |
	+--------------------+

使用某個資料庫

use test;
// 表示正在使用一個名字叫做test的資料庫

建立資料庫

create database test;

檢視某個資料庫下的表

show tables;

檢視MySQL版本號

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.40    |
+-----------+
1 row in set (0.00 sec)

檢視當前使用的是哪個資料庫

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

查詢某張表的資料

// 檢視table1的資料
select * from table1;

檢視某張表的結構（不看資料）

// 檢視table1的資料 describe -> desc
desc table1;

簡單查詢

查詢一個欄位

select 欄位名 from 表名

-- 		其中要注意：
--			select和from都是關鍵字。
--			欄位名和表名都是識別符號。

--		強調：
--			對於SQL語句來說，是通用的，
--			所有的SQL語句以“;”結尾。
--			另外SQL語句不區分大小寫，都行。

查詢多個欄位

--		使用逗號隔開“,”
--		查詢部門編號和部門名？
select deptno, dname from dept;
			+--------+------------+
			| deptno | dname      |
			+--------+------------+
			|     10 | ACCOUNTING |
			|     20 | RESEARCH   |
			|     30 | SALES      |
			|     40 | OPERATIONS |
			+--------+------------+

查詢所有欄位

--		第一種方式：可以把每個欄位都寫上
			select a,b,c,d,e,f... from tablename;

--		第二種方式：可以使用*
			select * from dept;
			+--------+------------+----------+
			| DEPTNO | DNAME      | LOC      |
			+--------+------------+----------+
			|     10 | ACCOUNTING | NEW YORK |
			|     20 | RESEARCH   | DALLAS   |
			|     30 | SALES      | CHICAGO  |
			|     40 | OPERATIONS | BOSTON   |
			+--------+------------+----------+

這種方式的缺點：

  1. 效率低
  2. 可讀性差。

在實際開發中不建議，可以自己玩沒問題。你可以在DOS命令視窗中想快速的看一看全表資料可以採用這種方式。

給查詢的列起別名

mysql> select deptno, dname as deptname from dept;
+--------+------------+
| deptno | deptname   |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

使用as關鍵字起別名。

注意：只是將顯示的查詢結果列名顯示為deptname，原表列名還是叫：dname

住：select語句是永遠都不會進行修改操作的。（因為只負責查詢）

as關鍵字可以省略嗎？可以的

mysql> select deptno,dname deptname from dept;

假設起別名的時候，別名裡面有空格，怎麼辦？

mysql> select deptno,dname dept name from dept;

DBMS看到這樣的語句，進行SQL語句的編譯，不符合語法，編譯報錯。

怎麼解決？

select deptno,dname 'dept name' from dept; //加單引號
select deptno,dname "dept name" from dept; //加雙引號

			+--------+------------+
			| deptno | dept name  |
			+--------+------------+
			|     10 | ACCOUNTING |
			|     20 | RESEARCH   |
			|     30 | SALES      |
			|     40 | OPERATIONS |
			+--------+------------+

注意：在所有的資料庫當中，字串統一使用單引號括起來，單引號是標準，雙引號在oracle資料庫中用不了。但是在mysql中可以使用。

再次強調：資料庫中的字串都是採用單引號括起來。這是標準的。雙引號不標準。

表(table)

資料庫當中最基本的單元是表

什麼是表table？為什麼用表來儲存資料呢？

​ 姓名 性別 年齡(列：欄位)

​ 張三 男 20 ------->行（記錄）
​ 李四 女 21 ------->行（記錄）
​ 王五 男 22 ------->行（記錄）

資料庫當中是以表格的形式表示資料的。因為表比較直觀。

任何一張表都有行和列：

  • 行（row）：被稱為資料/記錄。
  • 列（column）：被稱為欄位。例如姓名欄位、性別欄位、年齡欄位。

瞭解一下：

  • 每一個欄位都有：欄位名、資料型別、約束等屬性。
  • 欄位名可以理解，是一個普通的名字，見名知意就行。
  • 資料型別：字串，數字，日期等，後期講。
  • 約束：約束也有很多，其中一個叫做唯一性約束，這種約束新增之後，該欄位中的資料不能重複。

匯入絕對路徑下的sql

source D:\download\.....

SQL語句的分類

SQL語句有很多，最好進行分門別類，這樣更容易記憶。
分為：

  1. DQL(Data Query Language)：

    ​ 資料查詢語言（凡是帶有select關鍵字的都是查詢語句）

    ​ select...

  2. DML(Data Manipulate Language)：
    資料操作語言（凡是對錶當中的資料進行增刪改的都是DML）
    insert delete update
    insert 增
    delete 刪
    update 改

    ​ 這個主要是操作表中的資料data。

    1. DDL(Data Defination Language)：
      資料定義語言
      凡是帶有create、drop、alter的都是DDL。
      DDL主要操作的是表的結構。不是表中的資料。
      create：新建，等同於增
      drop：刪除
      alter：修改
      這個增刪改和DML不同，這個主要是對表結構進行操作。
      4. TCL(Transaction Control Language)：
      事務控制語言
      包括：
      事務提交：commit;
      事務回滾：rollback;

  3. DCL(Data Control Language)：
    是資料控制語言。
    例如：授權grant、撤銷許可權revoke....

條件查詢

什麼是條件查詢？

不是將表中所有資料都查出來。是查詢出來符合條件的。

語法格式：
		select
			欄位1,欄位2,欄位3....
		from 
			表名
		where
			條件;

條件有哪些？

= 等於

-- 查詢薪資等於800的員工姓名和編號？
select empno,ename from emp where sal = 800;

-- 查詢SMITH的編號和薪資？
select empno,sal from emp where ename = 'SMITH'; //字串使用單引號

<>或!= 不等於

-- 查詢薪資不等於800的員工姓名和編號？
select empno,ename from emp where sal != 800;
select empno,ename from emp where sal <> 800; // 小於號和大於號組成的不等號

< 小於

-- 查詢薪資小於2000的員工姓名和編號？
mysql> select empno, ename, sal from emp where sal < 2000;

<= 小於等於

-- 查詢薪資小於等於3000的員工姓名和編號？
mysql> select empno, ename, sal from emp where sal <= 3000;

>大於

-- 查詢薪資大於3000的員工姓名和編號？
mysql> select empno, ename, sal from emp where sal > 3000;

>= 大於等於

-- 
mysql> select empno, ename, sal from emp where sal >= 2450;

between...and...在...和...之間

	查詢薪資在2450和3000之間的員工資訊？包括2450和3000
		第一種方式：>= and <= （and是並且的意思。）
			select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;
			+-------+-------+---------+
			| empno | ename | sal     |
			+-------+-------+---------+
			|  7566 | JONES | 2975.00 |
			|  7698 | BLAKE | 2850.00 |
			|  7782 | CLARK | 2450.00 |
			|  7788 | SCOTT | 3000.00 |
			|  7902 | FORD  | 3000.00 |
			+-------+-------+---------+
		第二種方式：between … and …
			select 
				empno,ename,sal 
			from 
				emp 
			where 
				sal between 2450 and 3000;
			
			注意：
				使用between and的時候，必須遵循左小右大。
				between and是閉區間，包括兩端的值。

is null/is not null為空/不為空

	查詢哪些員工的津貼/補助為null？
		mysql> select empno,ename,sal,comm from emp where comm = null;
		Empty set (0.00 sec)

		mysql> select empno,ename,sal,comm from emp where comm is null;
		+-------+--------+---------+------+
		| empno | ename  | sal     | comm |
		+-------+--------+---------+------+
		|  7369 | SMITH  |  800.00 | NULL |
		|  7566 | JONES  | 2975.00 | NULL |
		|  7698 | BLAKE  | 2850.00 | NULL |
		|  7782 | CLARK  | 2450.00 | NULL |
		|  7788 | SCOTT  | 3000.00 | NULL |
		|  7839 | KING   | 5000.00 | NULL |
		|  7876 | ADAMS  | 1100.00 | NULL |
		|  7900 | JAMES  |  950.00 | NULL |
		|  7902 | FORD   | 3000.00 | NULL |
		|  7934 | MILLER | 1300.00 | NULL |
		+-------+--------+---------+------+
		10 rows in set (0.00 sec)

		注意：在資料庫當中null不能使用等號進行衡量。需要使用is null
		因為資料庫中的null代表什麼也沒有，它不是一個值，所以不能使用
		等號衡量。


	查詢哪些員工的津貼/補助不為null？
		select empno,ename,sal,comm from emp where comm is not null;
		+-------+--------+---------+---------+
		| empno | ename  | sal     | comm    |
		+-------+--------+---------+---------+
		|  7499 | ALLEN  | 1600.00 |  300.00 |
		|  7521 | WARD   | 1250.00 |  500.00 |
		|  7654 | MARTIN | 1250.00 | 1400.00 |
		|  7844 | TURNER | 1500.00 |    0.00 |
		+-------+--------+---------+---------+

and 並且

	查詢工作崗位是MANAGER並且工資大於2500的員工資訊？
		select 
			empno,ename,job,sal 
		from 
			emp 
		where 
			job = 'MANAGER' and sal > 2500;
		
		+-------+-------+---------+---------+
		| empno | ename | job     | sal     |
		+-------+-------+---------+---------+
		|  7566 | JONES | MANAGER | 2975.00 |
		|  7698 | BLAKE | MANAGER | 2850.00 |
		+-------+-------+---------+---------+

or 或者

	查詢工作崗位是MANAGER和SALESMAN的員工？
		select empno,ename,job from emp where job = 'MANAGER';
		select empno,ename,job from emp where job = 'SALESMAN';

		select 
			empno,ename,job
		from
			emp
		where 
			job = 'MANAGER' or job = 'SALESMAN';
		
		+-------+--------+----------+
		| empno | ename  | job      |
		+-------+--------+----------+
		|  7499 | ALLEN  | SALESMAN |
		|  7521 | WARD   | SALESMAN |
		|  7566 | JONES  | MANAGER  |
		|  7654 | MARTIN | SALESMAN |
		|  7698 | BLAKE  | MANAGER  |
		|  7782 | CLARK  | MANAGER  |
		|  7844 | TURNER | SALESMAN |
		+-------+--------+----------+

and和or同時出現的話，有優先順序問題嗎？

查詢工資大於2500，並且部門編號為10或20部門的員工？
	select 
		*
	from
		emp
	where
		sal > 2500 and deptno = 10 or deptno = 20;

分析以上語句的問題？

and優先順序比or高。
以上語句會先執行and，然後執行or。

以上這個語句表示什麼含義？
找出工資大於2500並且部門編號為10的員工，或者20部門所有員工找出來。

	select 
		*
	from
		emp
	where
		sal > 2500 and (deptno = 10 or deptno = 20);

and和or同時出現，and優先順序較高。如果想讓or先執行，需要加“小括號”
以後在開發中，如果不確定優先順序，就加小括號就行了。

in 包含，相當於多個 or （not in 不在這個範圍中）

		查詢工作崗位是MANAGER和SALESMAN的員工？
			select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
			select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');
			+-------+--------+----------+
			| empno | ename  | job      |
			+-------+--------+----------+
			|  7499 | ALLEN  | SALESMAN |
			|  7521 | WARD   | SALESMAN |
			|  7566 | JONES  | MANAGER  |
			|  7654 | MARTIN | SALESMAN |
			|  7698 | BLAKE  | MANAGER  |
			|  7782 | CLARK  | MANAGER  |
			|  7844 | TURNER | SALESMAN |
			+-------+--------+----------+
			注意：in不是一個區間。in後面跟的是具體的值。


		查詢薪資是800和5000的員工資訊？
			select ename,sal from emp where sal = 800 or sal = 5000;
			select ename,sal from emp where sal in(800, 5000); //這個不是表示800到5000都找出來。
			+-------+---------+
			| ename | sal     |
			+-------+---------+
			| SMITH |  800.00 |
			| KING  | 5000.00 |
			+-------+---------+
			select ename,sal from emp where sal in(800, 5000, 3000);

			// not in 表示不在這幾個值當中的資料。
			select ename,sal from emp where sal not in(800, 5000, 3000);
			+--------+---------+
			| ename  | sal     |
			+--------+---------+
			| ALLEN  | 1600.00 |
			| WARD   | 1250.00 |
			| JONES  | 2975.00 |
			| MARTIN | 1250.00 |
			| BLAKE  | 2850.00 |
			| CLARK  | 2450.00 |
			| TURNER | 1500.00 |
			| ADAMS  | 1100.00 |
			| JAMES  |  950.00 |
			| MILLER | 1300.00 |
			+--------+---------+

not 可以取非，主要用在 is 或 in 中
is null
is not null
in
not in

like

稱為模糊查詢，支援%或下劃線匹配

%匹配任意多個字元

下劃線：任意一個字元。

（%是一個特殊的符號，_ 也是一個特殊符號）

		找出名字中含有O的？
		mysql> select ename from emp where ename like '%O%';
		+-------+
		| ename |
		+-------+
		| JONES |
		| SCOTT |
		| FORD  |
		+-------+

		找出名字以T結尾的？
			select ename from emp where ename like '%T';
			
		找出名字以K開始的？
			select ename from emp where ename like 'K%';

		找出第二個字每是A的？
			select ename from emp where ename like '_A%';
		
		找出第三個字母是R的？
			select ename from emp where ename like '__R%';


		找出名字中有“_”的？
			select name from t_student where name like '%_%'; //這樣不行。

			mysql> select name from t_student where name like '%\_%'; // \跳脫字元。
			+----------+
			| name     |
			+----------+
			| jack_son |
			+----------+

排序

升序

查詢所有員工薪資，排序？
	select 
		ename,sal
	from
		emp
	order by
		sal; // 預設是升序！！！
--------------------------------

	指定升序？
	select 
		ename,sal
	from
		emp
	order by
		sal asc;

降序

	指定降序：
	select 
		ename,sal
	from
		emp
	order by
		sal desc;

多個欄位排序

	查詢員工名字和薪資，要求按照薪資升序，如果薪資一樣的話，
	再按照名字升序排列。
	select 
		ename,sal
	from
		emp
	order by
		sal asc, ename asc; // sal在前，起主導，只有sal相等的時候，才會考慮啟用ename排序。

根據欄位的位置也可以排序

select ename,sal from emp order by 2; // 2表示第二列。第二列是sal

按照查詢結果的第2列sal排序。

瞭解一下，不建議在開發中這樣寫，因為不健壯。

因為列的順序很容易發生改變，列順序修改之後，2就廢了。

綜合案例

	綜合一點的案例：
	找出工資在1250到3000之間的員工資訊，要求按照薪資降序排列。
	select 
		ename,sal
	from
		emp
	where
		sal between 1250 and 3000
	order by
		sal desc;

關鍵字順序不能變：
select
...
from
...
where
...
order by
...

以上語句的執行順序必須掌握：
	第一步：from
	第二步：where
	第三步：select
	第四步：order by（排序總是在最後執行！）

資料處理函式

單行處理函式

資料處理函式又被稱為單行處理函式

單行處理函式的特點：一個輸入對應一個輸出。

和單行處理函式相對的是：多行處理函式。（多行處理函式特點：多個輸入，對應1個輸出！）

常見的單行處理函式

lower 轉換小寫

		mysql> select lower(ename) as ename from emp;
		+--------+
		| ename  |
		+--------+
		| smith  |
		| allen  |
		| ward   |
		| jones  |
		| martin |
		| blake  |
		| clark  |
		| scott  |
		| king   |
		| turner |
		| adams  |
		| james  |
		| ford   |
		| miller |
		+--------+
		14個輸入，最後還是14個輸出。這是單行處理函式的特點。

upper 轉換大寫

		mysql> select * from t_student;
		+----------+
		| name     |
		+----------+
		| zhangsan |
		| lisi     |
		| wangwu   |
		| jack_son |
		+----------+

		mysql> select upper(name) as name from t_student;
		+----------+
		| name     |
		+----------+
		| ZHANGSAN |
		| LISI     |
		| WANGWU   |
		| JACK_SON |
		+----------+

substr取子串

substr( 被擷取的字串, 起始下標,擷取的長度)

		select substr(ename, 1, 1) as ename from emp;
		注意：起始下標從1開始，沒有0.
		找出員工名字第一個字母是A的員工資訊？
			第一種方式：模糊查詢
				select ename from emp where ename like 'A%';
			第二種方式：substr函式
				select 
					ename 
				from 
					emp 
				where 
					substr(ename,1,1) = 'A';

		首字母大寫？
			select name from t_student;
			select upper(substr(name,1,1)) from t_student;
			select substr(name,2,length(name) - 1) from t_student;
			select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;
			+----------+
			| result   |
			+----------+
			| Zhangsan |
			| Lisi     |
			| Wangwu   |
			| Jack_son |
			+----------+

concat 連線子串

		select concat(empno,ename) from emp;
		+---------------------+
		| concat(empno,ename) |
		+---------------------+
		| 7369SMITH           |
		| 7499ALLEN           |
		| 7521WARD            |
		| 7566JONES           |
		| 7654MARTIN          |
		| 7698BLAKE           |
		| 7782CLARK           |
		| 7788SCOTT           |
		| 7839KING            |
		| 7844TURNER          |
		| 7876ADAMS           |
		| 7900JAMES           |
		| 7902FORD            |
		| 7934MILLER          |
		+---------------------+

length取長度

		select length(ename) enamelength from emp;
		+-------------+
		| enamelength |
		+-------------+
		|           5 |
		|           5 |
		|           4 |
		|           5 |
		|           6 |
		|           5 |
		|           5 |
		|           5 |
		|           4 |
		|           6 |
		|           5 |
		|           5 |
		|           4 |
		|           6 |
		+-------------+

trim 去空格

		mysql> select * from emp where ename = '  KING';
		Empty set (0.00 sec)

		mysql> select * from emp where ename = trim('   KING');
		+-------+-------+-----------+------+------------+---------+------+--------+
		| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
		+-------+-------+-----------+------+------------+---------+------+--------+
		|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
		+-------+-------+-----------+------+------------+---------+------+--------+

str_to_date 將字串轉換成日期

date_format 格式化日期

format 設定千分位

case..when..then..when..then..else..end

		當員工的工作崗位是MANAGER的時候，工資上調10%，當工作崗位是SALESMAN的時候，工資上調50%,其它正常。
		（注意：不修改資料庫，只是將查詢結果顯示為工資上調）
		select 
			ename,
			job, 
			sal as oldsal,
			(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal 
		from 
			emp;
		
		+--------+-----------+---------+---------+
		| ename  | job       | oldsal  | newsal  |
		+--------+-----------+---------+---------+
		| SMITH  | CLERK     |  800.00 |  800.00 |
		| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
		| WARD   | SALESMAN  | 1250.00 | 1875.00 |
		| JONES  | MANAGER   | 2975.00 | 3272.50 |
		| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
		| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
		| CLARK  | MANAGER   | 2450.00 | 2695.00 |
		| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
		| KING   | PRESIDENT | 5000.00 | 5000.00 |
		| TURNER | SALESMAN  | 1500.00 | 2250.00 |
		| ADAMS  | CLERK     | 1100.00 | 1100.00 |
		| JAMES  | CLERK     |  950.00 |  950.00 |
		| FORD   | ANALYST   | 3000.00 | 3000.00 |
		| MILLER | CLERK     | 1300.00 | 1300.00 |
		+--------+-----------+---------+---------+

round 四捨五入

		select 欄位 from 表名;
		select ename from emp;
		select 'abc' from emp; // select後面直接跟“字面量/字面值”

		mysql> select 'abc' as bieming from emp;
		+---------+
		| bieming |
		+---------+
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		+---------+
		
		mysql> select abc from emp;
		ERROR 1054 (42S22): Unknown column 'abc' in 'field list'
		這樣肯定報錯，因為會把abc當做一個欄位的名字，去emp表中找abc欄位去了。

		select 1000 as num from emp; // 1000 也是被當做一個字面量/字面值。
		+------+
		| num  |
		+------+
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		+------+

結論：select後面可以跟某個表的欄位名（可以等同看做變數名），也可以跟字面量/字面值（資料）。

		select 21000 as num from dept;
		+-------+
		| num   |
		+-------+
		| 21000 |
		| 21000 |
		| 21000 |
		| 21000 |
		+-------+

		mysql> select round(1236.567, 0) as result from emp; //保留整數位。
		+--------+
		| result |
		+--------+
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		+--------+

		select round(1236.567, 1) as result from emp; //保留1個小數
		select round(1236.567, 2) as result from emp; //保留2個小數
		select round(1236.567, -1) as result from emp; // 保留到十位。
		+--------+
		| result |
		+--------+
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		+--------+

		select round(1236.567, -2) as result from emp;
		+--------+
		| result |
		+--------+
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		+--------+

rand()生成隨機數

		mysql> select round(rand()*100,0) from emp; // 100以內的隨機數
		+---------------------+
		| round(rand()*100,0) |
		+---------------------+
		|                  76 |
		|                  29 |
		|                  15 |
		|                  88 |
		|                  95 |
		|                   9 |
		|                  63 |
		|                  89 |
		|                  54 |
		|                   3 |
		|                  54 |
		|                  61 |
		|                  42 |
		|                  28 |
		+---------------------+

ifnull 可以將 null 轉換成一個具體值

		ifnull是空處理函式。專門處理空的。
		在所有資料庫當中，只要有NULL參與的數學運算，最終結果就是NULL。
		mysql> select ename, sal + comm as salcomm from emp;
		+--------+---------+
		| ename  | salcomm |
		+--------+---------+
		| SMITH  |    NULL |
		| ALLEN  | 1900.00 |
		| WARD   | 1750.00 |
		| JONES  |    NULL |
		| MARTIN | 2650.00 |
		| BLAKE  |    NULL |
		| CLARK  |    NULL |
		| SCOTT  |    NULL |
		| KING   |    NULL |
		| TURNER | 1500.00 |
		| ADAMS  |    NULL |
		| JAMES  |    NULL |
		| FORD   |    NULL |
		| MILLER |    NULL |
		+--------+---------+

		計算每個員工的年薪？
			年薪 = (月薪 + 月補助) * 12
			
			select ename, (sal + comm) * 12 as yearsal from emp;
			+--------+----------+
			| ename  | yearsal  |
			+--------+----------+
			| SMITH  |     NULL |
			| ALLEN  | 22800.00 |
			| WARD   | 21000.00 |
			| JONES  |     NULL |
			| MARTIN | 31800.00 |
			| BLAKE  |     NULL |
			| CLARK  |     NULL |
			| SCOTT  |     NULL |
			| KING   |     NULL |
			| TURNER | 18000.00 |
			| ADAMS  |     NULL |
			| JAMES  |     NULL |
			| FORD   |     NULL |
			| MILLER |     NULL |
			+--------+----------+

			注意：NULL只要參與運算，最終結果一定是NULL。為了避免這個現象，需要使用ifnull函式。
			ifnull函式用法：ifnull(資料, 被當做哪個值)
				如果“資料”為NULL的時候，把這個資料結構當做哪個值。
			
			補助為NULL的時候，將補助當做0
				select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;
				+--------+----------+
				| ename  | yearsal  |
				+--------+----------+
				| SMITH  |  9600.00 |
				| ALLEN  | 22800.00 |
				| WARD   | 21000.00 |
				| JONES  | 35700.00 |
				| MARTIN | 31800.00 |
				| BLAKE  | 34200.00 |
				| CLARK  | 29400.00 |
				| SCOTT  | 36000.00 |
				| KING   | 60000.00 |
				| TURNER | 18000.00 |
				| ADAMS  | 13200.00 |
				| JAMES  | 11400.00 |
				| FORD   | 36000.00 |
				| MILLER | 15600.00 |
				+--------+----------+

分組函式

多行處理函式的特點：輸入多行，最終輸出一行。

5個：

  1. count 計數
  2. sum 求和
  3. avg 平均值
  4. max 最大值
  5. min 最小值

注意：

分組函式在使用的時候必須先進行分組，然後才能用。

如果你沒有對資料進行分組，整張表預設為一組。

	找出最高工資？
		mysql> select max(sal) from emp;
		+----------+
		| max(sal) |
		+----------+
		|  5000.00 |
		+----------+
	
	找出最低工資？
		mysql> select min(sal) from emp;
		+----------+
		| min(sal) |
		+----------+
		|   800.00 |
		+----------+
	
	計算工資和：
		mysql> select sum(sal) from emp;
		+----------+
		| sum(sal) |
		+----------+
		| 29025.00 |
		+----------+
	
	計算平均工資：
		mysql> select avg(sal) from emp;
		+-------------+
		| avg(sal)    |
		+-------------+
		| 2073.214286 |
		+-------------+
		14個工資全部加起來，然後除以14。
	
	計算員工數量？
		mysql> select count(ename) from emp;
		+--------------+
		| count(ename) |
		+--------------+
		|           14 |
		+--------------+

	分組函式在使用的時候需要注意哪些？

		第一點：分組函式自動忽略NULL，你不需要提前對NULL進行處理。
		mysql> select sum(comm) from emp;
		+-----------+
		| sum(comm) |
		+-----------+
		|   2200.00 |
		+-----------+
		
		mysql> select count(comm) from emp;
		+-------------+
		| count(comm) |
		+-------------+
		|           4 |
		+-------------+
		mysql> select avg(comm) from emp;
		+------------+
		| avg(comm)  |
		+------------+
		| 550.000000 |
		+------------+

		第二點：分組函式中count(*)和count(具體欄位)有什麼區別？
			mysql> select count(*) from emp;
			+----------+
			| count(*) |
			+----------+
			|       14 |
			+----------+

			mysql> select count(comm) from emp;
			+-------------+
			| count(comm) |
			+-------------+
			|           4 |
			+-------------+

			count(具體欄位)：表示統計該欄位下所有不為NULL的元素的總數。
			count(*)：統計表當中的總行數。（只要有一行資料count則++）
						因為每一行記錄不可能都為NULL，一行資料中有一列不為NULL，則這行資料就是有效的。
		
		第三點：分組函式不能夠直接使用在where子句中。
			找出比最低工資高的員工資訊。
				select ename,sal from emp where sal > min(sal);
				表面上沒問題，執行一下？
					ERROR 1111 (HY000): Invalid use of group function
		?????????????????????????????????????????????????????????????????????
			說完分組查詢(group by)之後就明白了了。

		第四點：所有的分組函式可以組合起來一起用。
			select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
			+----------+----------+----------+-------------+----------+
			| sum(sal) | min(sal) | max(sal) | avg(sal)    | count(*) |
			+----------+----------+----------+-------------+----------+
			| 29025.00 |   800.00 |  5000.00 | 2073.214286 |       14 |
			+----------+----------+----------+-------------+----------+

分組查詢(重點！！！)

定義

		在實際的應用中，可能有這樣的需求，需要先進行分組，然後對每一組的資料進行操作。
		這個時候我們需要使用分組查詢，怎麼進行分組查詢呢？
			select
				...
			from
				...
			group by
				...
			
			計算每個部門的工資和？
			計算每個工作崗位的平均薪資？
			找出每個工作崗位的最高薪資？
			....

執行順序

		將之前的關鍵字全部組合在一起，來看一下他們的執行順序？
		select
			...
		from
			...
		where
			...
		group by
			...
		order by
			...
		
		以上關鍵字的順序不能顛倒，需要記憶。

執行順序是什麼？

  1. from
  2. where
  3. group by
  4. select
  5. order by

為什麼分組函式不能直接使用在where後面？

select ename,sal from emp where sal > min(sal);//報錯。

因為分組函式在使用的時候必須先分組之後才能使用。

where執行的時候，還沒有分組。所以where後面不能出現分組函式。

select sum(sal) from emp;

這個沒有分組，為啥sum()函式可以用呢？

因為select在group by之後執行。

案例

		找出每個工作崗位的工資和？

		實現思路：按照工作崗位分組，然後對工資求和。
			select 
				job,sum(sal)
			from
				emp
			group by
				job;
			
			+-----------+----------+
			| job       | sum(sal) |
			+-----------+----------+
			| ANALYST   |  6000.00 |
			| CLERK     |  4150.00 |
			| MANAGER   |  8275.00 |
			| PRESIDENT |  5000.00 |
			| SALESMAN  |  5600.00 |
			+-----------+----------+
			以上這個語句的執行順序？
				先從emp表中查詢資料。
				根據job欄位進行分組。
				然後對每一組的資料進行sum(sal)
		
		select ename,job,sum(sal) from emp group by job;
		+-------+-----------+----------+
		| ename | job       | sum(sal) |
		+-------+-----------+----------+
		| SCOTT | ANALYST   |  6000.00 |
		| SMITH | CLERK     |  4150.00 |
		| JONES | MANAGER   |  8275.00 |
		| KING  | PRESIDENT |  5000.00 |
		| ALLEN | SALESMAN  |  5600.00 |
		+-------+-----------+----------+
		以上語句在mysql中可以執行，但是毫無意義。
		以上語句在oracle中執行報錯。
		oracle的語法比mysql的語法嚴格。（mysql的語法相對來說鬆散一些！）

重點結論：

在一條select語句當中，如果有group by語句的話，select後面只能跟：1. 參加分組的欄位；2. 分組函式。

其它的一律不能跟。

		找出每個部門的最高薪資

		實現思路是什麼？
			按照部門編號分組，求每一組的最大值。

			select後面新增ename欄位沒有意義，另外oracle會報錯。
			mysql> select ename,deptno,max(sal) from emp group by deptno;
			+-------+--------+----------+
			| ename | deptno | max(sal) |
			+-------+--------+----------+
			| CLARK |     10 |  5000.00 |
			| SMITH |     20 |  3000.00 |
			| ALLEN |     30 |  2850.00 |
			+-------+--------+----------+

			mysql> select deptno,max(sal) from emp group by deptno;
			+--------+----------+
			| deptno | max(sal) |
			+--------+----------+
			|     10 |  5000.00 |
			|     20 |  3000.00 |
			|     30 |  2850.00 |
			+--------+----------+

聯合分組

		找出“每個部門，不同工作崗位”的最高薪資？
		+--------+-----------+---------+--------+
		| ename  | job       | sal     | deptno |
		+--------+-----------+---------+--------+
		| MILLER | CLERK     | 1300.00 |     10 |
		| KING   | PRESIDENT | 5000.00 |     10 |
		| CLARK  | MANAGER   | 2450.00 |     10 |

		| FORD   | ANALYST   | 3000.00 |     20 |
		| ADAMS  | CLERK     | 1100.00 |     20 |
		| SCOTT  | ANALYST   | 3000.00 |     20 |
		| JONES  | MANAGER   | 2975.00 |     20 |
		| SMITH  | CLERK     |  800.00 |     20 |

		| BLAKE  | MANAGER   | 2850.00 |     30 |
		| MARTIN | SALESMAN  | 1250.00 |     30 |
		| ALLEN  | SALESMAN  | 1600.00 |     30 |
		| TURNER | SALESMAN  | 1500.00 |     30 |
		| WARD   | SALESMAN  | 1250.00 |     30 |
		| JAMES  | CLERK     |  950.00 |     30 |
		+--------+-----------+---------+--------+
		技巧：兩個欄位聯合成1個欄位看。（兩個欄位聯合分組）
		select 
			deptno, job, max(sal)
		from
			emp
		group by
			deptno, job;

		+--------+-----------+----------+
		| deptno | job       | max(sal) |
		+--------+-----------+----------+
		|     10 | CLERK     |  1300.00 |
		|     10 | MANAGER   |  2450.00 |
		|     10 | PRESIDENT |  5000.00 |
		|     20 | ANALYST   |  3000.00 |
		|     20 | CLERK     |  1100.00 |
		|     20 | MANAGER   |  2975.00 |
		|     30 | CLERK     |   950.00 |
		|     30 | MANAGER   |  2850.00 |
		|     30 | SALESMAN  |  1600.00 |
		+--------+-----------+----------+

having的使用

使用having可以對分完組之後的資料進一步過濾。

having不能單獨使用，having不能代替where，having必須和group by聯合使用。

找出每個部門最高薪資，要求顯示最高薪資大於3000的？

	第一步：找出每個部門最高薪資
		按照部門編號分組，求每一組最大值。
		select deptno,max(sal) from emp group by deptno;
		+--------+----------+
		| deptno | max(sal) |
		+--------+----------+
		|     10 |  5000.00 |
		|     20 |  3000.00 |
		|     30 |  2850.00 |
		+--------+----------+
	
	第二步：要求顯示最高薪資大於3000
		select 
			deptno,max(sal) 
		from 
			emp 
		group by 
			deptno
		having
			max(sal) > 3000;

		+--------+----------+
		| deptno | max(sal) |
		+--------+----------+
		|     10 |  5000.00 |
		+--------+----------+

思考一個問題：以上的sql語句執行效率是不是低？

比較低，實際上可以這樣考慮：先將大於3000的都找出來，然後再分組。

		select 
			deptno,max(sal)
		from
			emp
		where
			sal > 3000
		group by
			deptno;
		
		+--------+----------+
		| deptno | max(sal) |
		+--------+----------+
		|     10 |  5000.00 |
		+--------+----------+

最佳化策略：

where和having，優先選擇where，where實在完成不了了，再選擇having

where沒辦法完成怎麼辦？？？

			找出每個部門平均薪資，要求顯示平均薪資高於2500的。

			第一步：找出每個部門平均薪資
				select deptno,avg(sal) from emp group by deptno;
				+--------+-------------+
				| deptno | avg(sal)    |
				+--------+-------------+
				|     10 | 2916.666667 |
				|     20 | 2175.000000 |
				|     30 | 1566.666667 |
				+--------+-------------+

			第二步：要求顯示平均薪資高於2500的
				select 
					deptno,avg(sal) 
				from 
					emp 
				group by 
					deptno
				having
					avg(sal) > 2500;
			
			+--------+-------------+
			| deptno | avg(sal)    |
			+--------+-------------+
			|     10 | 2916.666667 |
			+--------+-------------+

總結

書寫順序

select

...

from

...

where

...

group by

...

having

...

order by

以上關鍵字只能按照這個順序來，不能顛倒。

執行順序

  1. from

  2. where

  3. group by

  4. having

  5. select

  6. order by​

案例

找出每個崗位的平均薪資，要求顯示平均薪資大於1500的，除MANAGER崗位之外，
要求按照平均薪資降序排。
	select 
		job, avg(sal) as avgsal
	from
		emp
	where
		job <> 'MANAGER'
	group by
		job
	having
		avg(sal) > 1500
	order by
		avgsal desc;

	+-----------+-------------+
	| job       | avgsal      |
	+-----------+-------------+
	| PRESIDENT | 5000.000000 |
	| ANALYST   | 3000.000000 |
	+-----------+-------------+

相關文章