MySQL基础(3)--韩顺平老师课

24.23合计/统计函数

24.23.1 count

 

-- 统计一个班级共有多少学生？

 

-- 统计数学成绩大于 90 的学生有多少个？

 

-- 统计总分大于 250 的人数有多少？

 

-- count(*) 和 count(列) 的区别

-- 解释 :count(*) 返回满足条件的记录的行数

-- count(列): 统计满足条件的某列有多少个，但是会排除 为 null 的情况

 

 

24.23.2 sum

 

--演示 sum 函数的使用

--统计一个班级数学总成绩？

 

-- 统计一个班级语文、英语、数学各科的总成绩

-- 统计一个班级语文、英语、数学的成绩总和

 

-- 统计一个班级语文成绩平均分

 

24.23.3 avg

 

-- 演示 avg 的使用

-- 练习：

-- 求一个班级数学平均分？

 

-- 求一个班级总分平均分

 

24.23.4 max/min

 

-- 演示 max 和 min 的使用

-- 求班级最高分和最低分（数值范围在统计中特别有用）

 

-- 求出班级数学最高分和最低分

 

24.23.5 使用 group by 子句对列进行分组 [先创建测试表]

 

24.23.6 使用 having 子句对分组后的结果进行过滤 往往与group by结合使用

  

创建测试表，测试group by

 

三张测试表：

【第一张】-- 员工表

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;

【第三张】--部门表

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;

--1 ?如何显示每个部门的平均工资和最高工资

 

--为什么有10、20、30、40这几个部门编号，但查询结果只显示三个部门的？

--因为40号部门没有人！

-- 2？显示每个部门的每种岗位的平均工资和最低工资

 

上一题只按部门来分组，就只写一个depto就可以了，现在这题先按照部门，再按照岗位两个标准来分组，所以要写这两个depto,job

--3 ?显示平均工资低于2000的部门号和它的平均工资 // 别名

 

-- 使用数学方法，对小数点进行处理

SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno

FROM emp GROUP BY deptno;

-- ?显示每个部门的每种岗位的平均工资和最低工资

-- 老师分析 1. 显示每个部门的平均工资和最低工资

-- 2. 显示每个部门的每种岗位的平均工资和最低工资

SELECT AVG(sal), MIN(sal) , deptno, job

FROM emp GROUP BY deptno, job;

-- ?显示平均工资低于 2000 的部门号和它的平均工资 // 别名

-- 老师分析 [写 sql 语句的思路是化繁为简,各个击破]

-- 1. 显示各个部门的平均工资和部门号

-- 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000

-- 3. 使用别名进行过滤

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;

24.24字符串相关函数

 

-- 演示字符串相关函数的使用 ， 使用 emp 表来演示

 

--1 CHARSET(str)

返回字串字符集

 

--2 CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列

 

--3 INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0

-- dual 亚元表, 系统表 可以作为测试表使用

 

--4 UCASE (string2 ) 转换成大写

--5 LCASE (string2 ) 转换成小写

 

--6 LEFT (string2 ,length )从 string2 中的左边起取 length 个字符

-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符

--7 LENGTH (string )string 长度[按照字节]

 

--8 REPLACE (str ,search_str ,replace_str )

-- 在 str 中用 replace_str 替换 search_str

-- 如果是 manager 就替换成 经理

 

--9 STRCMP (string1 ,string2 )

逐字符比较两字串大小

 

-- SUBSTRING (str , position [,length ])

-- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符

-- 从 ename 列的第一个位置开始取出 2 个字符

 

-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string) 左右两边都可以去掉的

-- 去除前端空格或后端空格

 

-- 练习: 以首字母小写的方式显示所有员工 emp 表的姓名

-- 方法 1

-- 思路先取出 ename 的第一个字符，转成小写的

-- 把他和后面的字符串进行拼接输出即可

 

24.25数学相关函数

 

-- 演示数学相关函数

--1 ABS(num) 绝对值

 

--2 BIN (decimal_number )十进制转二进制

 

--3 CEILING (number2 ) 向上取整, 得到比 num2 大的最小整数

 

--4 CONV(number2,from_base,to_base) 进制转换下面的含义是 8 是十进制的 8, 转成 2 进制输出

 

--6 下面的含义是 16 是 16 进制的 16, 转成 10 进制输出

 

--7 FLOOR (number2 ) 向下取整,得到比num2 小的最大整数

 

--8 FORMAT (number,decimal_places )

 

--9 HEX (DecimalNumber ) 转十六进制

--10 LEAST (number , number2 [,..]) 求最小值

 

--11 MOD (numerator ,denominator ) 求余

 

--12 RAND([seed])

 

24.26时间日期相关函数 date.sql

 

 

 

日期相关函数

 

 

 

-- 日期时间相关函数

--1 CURRENT_DATE ( ) 当前日期

 

--2 CURRENT_TIME ( )当前时间

 

-- CURRENT_TIMESTAMP ( ) 当前时间戳

 

-- 创建测试表 信息表

-- 日期时间相关函数

-- CURRENT_DATE ( ) 当前日期

SELECT CURRENT_DATE() FROM DUAL;

-- CURRENT_TIME ( )当前时间

SELECT CURRENT_TIME() FROM DUAL;

-- CURRENT_TIMESTAMP () 当前时间戳

SELECT CURRENT_TIMESTAMP() FROM DUAL;

-- 创建测试表 信息表

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;

SELECT NOW() FROM DUAL;

 

-- 上应用实例

--1 显示所有新闻信息，发布日期只显示 日期，不用显示时间.

 

--2 请查询在 10 分钟内发布的新闻, 思路一定要梳理一下.

方式一：

 

方式二：

 

--3 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天

 

-- 请用 mysql 的 sql 语句求出你活了多少天? [练习] 1986-11-11 出生

 

-- 如果你能活 80 岁，求出你还能活多少天.[练习] 1986-11-11 出生

-- 先求出活 80 岁 时, 是什么日期 X

-- 然后在使用 datediff(x, now()); 1986-11-11->datetime

-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒

--'1986-11-11' 可以是date、datetime、timestamp

 

-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天

 

 

-- YEAR|Month|DAY| DATE (datetime )

年

 

月

 

日

 

具体的月，11月

 

-- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数

 

-- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期

-- %Y-%m-%d 格式是规定好的，表示年月日

-- 意义：在开发中，可以存放一个整数，然后表示时间，通过 FROM_UNIXTIME 转换

 

 

SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;

SELECT * FROM mysql.user \G

24.27加密和系统函数 pwd.sql

 

-- 演示加密函数和系统函数

-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP

-- 用户@IP 地址

 

-- DATABASE()查询当前使用数据库名称

-- MD5(str) 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密

-- root 密码是 hsp -> 加密 md5 -> 在数据库中存放的是加密后的密码

 

-- 演示用户表，存放密码时，是 md5

 

 

-- 演示加密函数和系统函数

-- USER() 查询用户

-- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP

SELECT USER() FROM DUAL; -- 用户@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('hsp') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC



-- select * from mysql.user \G 从原文密码 str 计算并返回密码字符串

-- 通常用于对 mysql 数据库的用户密码加密

-- mysql.user 表示 数据库.表

SELECT * FROM mysql.user

24.28流程控制函数

 

 

# 演示流程控制语句

# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3

 

# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2

 

 

# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]

# 如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 t, 返回 expr4, 否则返回 expr5

 

-- 1. 查询 emp 表, 如果 comm 是 null , 则显示 0.0

-- 老师说明，判断是否为 null 要使用 is null, 判断不为空 使用 is not

 

方式二：

 

-- 2. 如果 emp 表的 job 是 CLERK 则显 职员， 如果是 MANAGER 则显示经理

-- 如果是 SALESMAN 则显示 销售人员，其它正常显示