5.MySQL常用函數

5.1合計/統計函數

5.1.1合計函數-count

count 返回行的總數

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

練習

-- 統計一個班級共有幾個學生
SELECT COUNT(*) FROM student

-- 統計數學成績大於90的學生有多少個
SELECT COUNT(*) FROM student
	WHERE math > 90
	
-- 統計總分大於250的人數有多少個
SELECT COUNT(*) FROM student
	WHERE chinese+english+math> 250

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

count(*)返回滿足條件的記錄的行數
count(列)統計滿足條件的某列有多少個，但是會排除為null的情況

5.1.2合計函數-sum

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

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

練習

-- sum函數
-- 統計一個班的數學總成績
SELECT SUM(math) FROM student

-- 統計一個班語文、英語、數學各科的總成績
SELECT SUM(math),SUM(english),SUM(chinese) FROM student

-- 統計一個班語文、英語、數學的成績總和
SELECT SUM(math+english+chinese) FROM student

-- 統計一個班級語文成績平均分
SELECT SUM(chinese)/COUNT(*) FROM student

註意：

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

對多列求和，“ , ” 不能少

5.1.3合計函數-avg

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

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

練習

-- avg 函數
-- 求一個班級數學平均分
SELECT AVG(math) FROM student
-- 求一個班級總分平均分
SELECT AVG(chinese+english+math) FROM student

5.1.4 合計函數-Max/min

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

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

練習

-- max和min 函數
-- 求班級最高分和最低分
SELECT MAX(chinese+english+math),MIN(chinese+english+math)FROM student

-- 求班級數學最高分和最低分
SELECT MAX(math),MIN(math)FROM student

5.1.5分組統計-group by

• 使用group by子句對列進行分組

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

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

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

練習：

-- 先創建測試表
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');

SELECT * FROM dept;

-- 員工表

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);

SELECT * FROM emp;

-- 工資級別
#工資級別表
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);

SELECT * FROM salgrade;
SELECT * FROM dept;
SELECT * FROM emp;

# 演示group by + having
# GROUP by用於對查詢的結果分組統計, having子句用於限制分組顯示結果.
-- 如何顯示每個部門的平均工資和最高工資
-- 按照部門來分組查詢
SELECT AVG(sal),MAX(sal) ,deptno
	FROM emp 
	GROUP BY deptno;

-- 顯示每個部門的每種崗位的平均工資和最低工資
# 1.先顯示每個部門的平均工資和最低工資
# 2.再顯示每個部門的每種崗位的平均工資和最低工資
SELECT AVG(sal),MIN(sal) ,deptno,job
	FROM emp 
	GROUP BY deptno,job; -- 先按照部門分組，再按照崗位分組


-- 顯示平均工資低於2000的部門號和它的平均工資
# 1.先顯示各個部門的平均工資和部門號
# 2.再顯示平均工資低於2000的（having過濾）
SELECT AVG(sal),deptno
	FROM emp 
	GROUP BY deptno
	HAVING AVG(sal)<2000
	
-- 也可以使用別名進行過濾，如下：
SELECT AVG(sal) AS avg_sal,deptno
	FROM emp 
	GROUP BY deptno
	HAVING avg_sal<2000

5.2字元串函數

字元串相關函數

練習

-- -- 演示字元串相關的操作
-- -- 使用前面創建的emp表來演示
-- charset(str)  返回字串字元集 
SELECT CHARSET(ename) FROM emp;

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

-- instr（string，substring）返回substring在string中出現的位置，沒有就返回0
SELECT INSTR('hanshunping','ping') FROM DUAL; -- 8
-- dual 亞元表，是系統表，可以作為測試表使用

-- ucase（string2）  轉換成大寫  
SELECT UCASE(ename) FROM emp; -- 將指定列的所有信息變為大寫

-- lcase（string2）  轉換成小寫   
SELECT LCASE(ename) FROM emp;-- -- 將指定列的所有信息變為小寫
 
-- left（string2，length）  從string2的左邊起，取length個字元 
SELECT LEFT(ename,2) FROM emp;

-- right（string2，length）  從string2的右邊起，取length個字元 
SELECT RIGHT(ename,2) FROM emp;

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

-- replace（str，search_str，repace_str） 在str中用replace_str替換seacher_str  
-- 將表中的job職位的MANEGER替換成‘經理’
SELECT REPLACE(job,'MANAGER','經理') FROM emp;
            
-- strcmp(string1,string 2)   逐字元比較兩字元串的大小  
SELECT STRCMP('hsp','asp') FROM DUAL;

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

-- ltrim（string2）rtrim（string2） 去除前端或者後端空格   
SELECT LTRIM('  123nihao') FROM DUAL;
SELECT RTRIM('123nihao    ') FROM DUAL;  
                  
-- trim(stirng2) 去除前後的空格
SELECT TRIM('  123nihao   ') FROM DUAL;

-- 以首字母小寫的方式顯示所有員工emp表的姓名
-- 截取第一個字元將其小寫,然後拼接剩下的字元串輸出即可
SELECT CONCAT( LCASE(LEFT(ename,1)) , SUBSTRING(ename,2)) FROM emp; -- 拼接完成

5.3數學函數

5.4時間函數

5.5流程式控制制