文章目录
合计/统计函数
count
count返回行的总数
案例练习
-- 统计一个班级共有多少学生?
select count(*) as '学生数' from student
-- 统计数学成绩大于60的学生有多少个?
select count(*) as '数学成绩大于60' from student where math > 60
-- 统计总分大于200的人数有多少?
select count(*) as '总分大于200' from student where (chinese + math + english) > 200
-- count(*)和count(列)的区别
-- 解释：count（*）返回满足条件的记录的行数
-- count（列）：统计满足条件的某列有多少个，但是会排除 为null的数据
create table t15(`name` VARCHAR(20));
insert into t15 values(null);
insert into t15 values('1');
insert into t15 values('2');
-- 3
select count(*) as '统计数' from t15
-- 2
select count(name) as '统计数' from t15
sum
Sum函数返回满足where条件的行的和
案例练习
-- 统计一个班级数学总成绩?
select sum(math) as '数学总成绩' from student
-- 统计一个班级语文、英语、数学各科的总成绩
select sum(math) as '数学总成绩', sum(chinese) as '语文总成绩' ,sum(english) as '英语总成绩' from student
-- 统计一个班级语文、英语、数学的成绩总和
select sum(math + chinese + english) as '班级所有成绩总和' from student
-- 统计一个班级语文成绩平均分
-- 第一种
select avg(chinese) as '语文成绩平均分' from student
-- 第二种
select (sum(chinese)) / count(*) as '语文成绩平均分' from student
-- 注意: sum仅对数值起作用，否则会报错。注意:对多列求和，“，”号不能少。
-- 0
select sum(NAME) from student
avg
案例练习
-- 求一个班级数学平均分?
select avg(math) as '数学平均分' from student
-- 求一个班级总分平均分
select avg(math + chinese + english) as '总分平均分' from student
Max/Min
案例练习
-- 练习:求班级最高分和最低分(数值范围在统计中特别有用)
select max(math + chinese + english)as '最高分' ,min(chinese + math + english) as '最低分'from student
分组查询
group by
使用group by 子句对列进行分组
having
使用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,'ACOUNTING ','NEW YORK'),
(20,'RESEARCh' , 'DALAS '),
(30,'SALRS', 'CHICGO '),
(40,'OPERATION', 'BoTo ');
#创建表EMP雇员
CREATE TABLE emp(empno MEDIUMINT UNSIGNED NOT NULL,/*编号*/
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,'TONES','AANACEN', 7098,'1991-2-22 ',1250.00,500.00,30),
(7654,'MARTIN ','SALESMAN ', 7698,'1991-9-28', 1250.00,1400.00,30),
(7698,'BLAKE','MANAGER', 7839,'1991-5-1', 2850.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);
delete from emp
delete from dept
delete from salgrade
select * from emp
案例练习
-- 演示group by + having
-- having子句用于限制分组显示结果
-- ？如何显示每个部门的平均工资和最高工资
-- 分析：1.显示每个部门的平均工资和最低工资
select avg(sal),max(sal),deptno from emp group by deptno;
-- 分析：2.显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),deptno,job from emp group by deptno,job;
-- 显示平均工资低于2000的部门号和它的平均工资//别名
-- 分析avg(sal) < 2000
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
字符串相关函数
函数表格
| CHARSET（str） | 返回字串字符集 |
|---|---|
| CONCAT（string2[,…]） | 连接字串 |
| INSTR（string，substring） | 返回substring在string中出现的位置，没有返回0 |
| UCASE（string2） | 转换成大写 |
| LCASE（string2） | 转换成小写 |
| LEFT（string2，length） | 从string2中的左边起取length个字符 |
| LENGTH（string） | string长度【按照字节】 |
| REPLACE（str,search_str,replace_str） | 在str中用replace_str替换search_str |
| STRCMP（string1,string2） | 逐字符比较两字串大小 |
| SUBSTRING（str,positon[,length]） | 从str的position开始【从1开始计算】，取length个字符 |
| LTRIM(string2) RTRIM(string2)trim | 去除前端空格或后端空格 |
案例演示
-- 演示字符串相关函数的使用 使用group by练习中的emp表演示
-- CHARSET(str)返回字串字符集
select charset (ename) from emp; -- utf8
-- CONCAT (string2[ ,... ])连接字串//多个列拼接成一行
select concat (ename,' job is ', job)from emp; -- SMITH job is CLERK
-- INSTR(string ,substring )返回substring在string中出现的位置,没有返回0
select instr ('liuhongtao','tao') from dual; -- 返回 8 // dual(测试表，亚元表)
-- UCASE (string2) 转换成大写
select ucase (ename) from emp
-- LCASE (string2) 转换成小写
select lcase (ename) from emp
-- LEFT(string2 ,length ) 从string2中的左边起取1ength个字符
select left(ename,2) from emp -- 返回前两位
-- LENGTH (string ) string长度[按照字节]
select length(ename) from emp -- 返回字符串长度
-- REPLACE(str ,searah_str ,replace_str) 在str中用replace_str替换search_str
select ename,replace(job,'MANAGER','经理') from emp;
-- STRCMP(string1 ,string2 )逐字符比较两字串大小
select strcmp('鸿涛','刘鸿涛') from dual; -- 1,否则返回-1
-- SUBSTRING (str , positon ,length) 从str的position开始【从1开始计算】，取length个字符（注意：如果后面不写length，则q）
-- 从ename 列的第一个位置开始取出2个字符
select substring(ename,1,2) from emp;
-- LTRIM(string2) RTRIM(string2)trim 去除前端空格或后端空格
select ltrim(ename) from emp
select * from emp
案例练习
-- 以首字母小写的方式显示所有员工emp表的姓名
-- 1.先取出ename 的第一个字符，转成小写的
select lcase(left(ename,1)) from emp
-- 2.拼接后面字符
select substring(ename,2) from emp
-- 3.得出
select concat(lcase(left(ename,1)) ,substring(ename,2))from emp
dual表
dual(测试表，亚元表) ，不需要创建表，直接使用就可以
数学函数
函数表格
| ABS（num） | 绝对值 |
|---|---|
| BIN（decimal_number） | 十进制转二进制 |
| CEILING（number2） | 向上取整，得到比num2大的最小整数 |
| CONV（number2，from_base,to_base） | 进制转换 |
| FLOOR（number2） | 向下取整，得到比num2小的最大整数 |
| FORMAT（number，decimal_places） | 保留小数位数 |
| HEX（DecimalNumber） | 转十六进制 |
| LEAST（number，number2[,…]） | 求最小值 |
| MOD（numerrator，denominator） | 求余 |
| RAND（[seed]） | RAND（[seed]）其范围为0 <= v <= 1.0 |
案例演示
-- 演示数学相关函数
-- ABS（num）绝对值
select abs(-10) from dual; -- 10
-- BIN（decimal_number）十进制转二进制
select 10 from dual;
select bin(10) from dual; -- 1010
-- CEILING（number2）向上取整，得到比num2大的最小整数
select ceiling (1.2) from dual; -- 2
-- CONV（number2，from_base,to_base）进制转换
select conv(50,10,2) from dual; -- 把10进制50转为2进制 ，110010
-- FLOOR（number2）向下取整，得到比num2小的最大整数
select floor(1.9) from dual; -- 1
-- FORMAT（number，decimal_places）保留小数位数
select format(1.1215454,2) from dual; -- 1.12
-- HEX（DecimalNumber）转十六进制
select hex(15) from dual; -- F
-- LEAST（number，number2[,...]）求最小值
select least(1,5,7,0,8) from dual; -- 0
-- MOD（numerrator，denominator）求余
select mod(10,3) from dual; -- 1
-- RAND（[seed]）其范围为0 <= v <= 1.0
select rand() from dual; -- 其范围为0 <= v <= 1.0
select rand(3) from dual; -- 写入种子数后固定为0.9057697559760601
时间日期函数
函数表格
| CURRENT_DATE() | 当前日期 |
|---|---|
| CURRENT_TIME() | 当前时间 |
| CURRENT_TIMESTAMP() | 当前时间戳 |
| DATE(datetime) | 返回datetime的日期部分 |
| DATE_ADD(date2,INTERVAL d_valued_type) | 在date2中加上日期或时间 |
| DATE_SUB(date2,INTERVAL d_valued_type) | 在date2上减去一个时间 |
| DATEDIFF(date1,date2) | 两个日期差（结果是天） |
| TIMEDIFF(date1,date2) | 两个时间差（多少小时，多少分钟，多少秒） |
| NOW() | 当前时间 |
| YEAR|Month|DATE(datetime) | 年月日 |
| unix_timestamp() | 返回的是1970-1-1到现在的秒数 |
| FROM_UNIXTIME() | 可以把一个unix_timestamp 秒数，转成指定格式的日期 |
创建测试表
create table mes(
id int,
content VARCHAR(30),
sendtime datetime);
insert into mes values(1,'北京新闻',current_time)
insert into mes values(2,'上海新闻',now())
案例演示
# 演示时间函数
-- | CURRENT_DATE() | 当前日期 |
select current_date() from dual; -- 2022-02-14
-- | CURRENT_TIME() | 当前时间 |
select current_time() from dual; -- 12:00:34
-- | CURRENT_TIMESTAMP() | 当前时间戳 |
select current_timestamp() from dual; -- 2022-02-14 12:01:00
-- | DATE(datetime) | 返回datetime的日期部分 |
select date(current_time()) from dual; -- 2022-02-14
-- | NOW() | 当前时间 |
select now() from dual; -- 2022-02-14 12:11:05
-- | TIMEDIFF 返回时分秒
select timediff('10:11:11','06:10:10') from dual; -- 04:01:01
应用实例演示1
-- 显示所有留言信息，发布日期只显示日期，不用显示时间
select id,content,date(sendtime) from mes;
-- 请查询在10分钟内发布的帖子
select * from mes where date_add(sendtime,interval 10 minute) >= now()
select * from mes where sendtime >= date_sub(now(),interval 10 minute)
-- 请在mysql 的sql语句中求出2011-11-11 和1990-1-1相差多少天
select datediff('2011-11-11','1990-1-1')
-- 请用mysql的sqli语句求出你活了多少天?[练习]
select datediff(now(),'1997-05-06') as '我已在地球生存了' from dual;
-- 如果你能活80岁,求出你还能活多少天﹒[练习]
select datediff(date_add('1997-05-06',interval 80 year),now()) from dual;
应用实例演示2
-- | NOW() | 当前时间 |
-- | YEAR\|Month\|DATE(datetime) | 年月日 |
select year(now()) from dual;-- 2022
select month(now()) from dual; -- 2
select year('2013-1-1')from dual; -- 2013
-- unix_timestamp() 返回的是1970-1-1到现在的秒数
select unix_timestamp() from dual; -- 1644820922
select unix_timestamp() / (24 * 3600 * 365) from dual; -- 52.1569年
-- FROM_UNIXTIME() 可以把一个unix_timestamp 秒数，转成指定格式的日期
select FROM_UNIXTIME(1644820922,'%Y-%m-%d') from dual; -- 2022-02-14
select FROM_UNIXTIME(1644820922,'%Y-%m-%d %H:%i:%s') from dual; -- 2022-02-14 14:42:02
-- 意义：在开发中，可以存放一个整数，然后表示
-- 在实际开发中，我们也经常使用int来保存一个unix时间戳,
-- 然后使用from_unixtime()进行转换,还是非常有实用价值的
加密和系统函数
函数表格
| USER（） | 查询用户 |
|---|---|
| DATABASE（） | 查询当前使用的数据库名称 |
| MD5（str） | 为字符串算出一个MD5 32的字符串，（用户密码）加密 |
| PASSWORD（str） | 从原文密码str 计算并返回密码字符串，通常用于对mysql数据库的用户密码加密 |
案例演示
-- 演示加密函数和系统函数
-- | USER（） | 查询用户
-- 可以查看登录到mysql的有哪些用户，一级登录的ip
select user()from dual; -- 用户@ip地址 -- root@localhost
-- | DATABASE（） | 查询当前使用的数据库名称
select database();
-- | MD5（str） | 为字符串算出一个MD5 32的字符串，（用户密码）加密
-- root密码是 *** -> 加密md5 -> 在数据库中存放的是加密后的密码
select md5('gangqixi') from dual; -- md5加密后为（618372821f2b093c02fda1317fab65a1）
select length(md5('gangqixi')) from dual; -- 32 位
-- | PASSWORD（str） -- 加密函数，mysql数据库的用户密码就是password函数加密
select password('gangqixi') from dual; -- *4886447239D8C351DCE92FC82981B5ADB009AA6E
-- mysql.user 表示 数据库.表
select password('gangqixi') from mysql.user -- *4886447239D8C351DCE92FC82981B5ADB009AA6E
演示md5加密
create table tao(id int,`name` VARCHAR(32) not null default '',
pwd char(32) not null default '');
insert into tao values(1,'涛涛',md5('gangqixi'));
select * from tao; -- 618372821f2b093c02fda1317fab65a1
-- 查询数据要加md5
select * from tao where `name`= '涛涛' and pwd=md5('gangqixi'); -- sql注入问题
演示password加密
-- | PASSWORD（str） -- 加密函数，mysql数据库的用户密码就是password函数加密
select password('gangqixi') from dual; -- *4886447239D8C351DCE92FC82981B5ADB009AA6E
-- mysql.user 表示 数据库.表
select password('gangqixi') from mysql.user -- *4886447239D8C351DCE92FC82981B5ADB009AA6E
流程控制函数
引出流程控制函数（需求）
函数表格
| 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 |
案例演示
# 演示流程控制语句
-- | IF(expr1,expr2,expr3) 如果expr1为True，则返回 expr2 否则返回expr3
select if(true,'北京','上海') from dual; -- 北京
-- | IFNULL(expr1,expr2) 如果expr1不为空NULL，则返回expr1，否则返回expr2
select ifnull(null,'涛涛')from dual; -- 涛涛
-- | SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5
-- |如果expr1为TRUE，则返回expr2，如果expr2为t，返回expr4，否则返回expr5 |
-- END;[类似多重分支]
select case
when true then 'jack' -- jack
when false then 'tom'
else 'marry' END
案例练习
-- 1.查询emp表,如果comm是null，则显示0.0
-- 判断是否为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.如果emp表的job是CLERK则显示职员，如果是 MANAGER 则显示经理
-- 如果是SALESMAN则显示销售人员,其它正常显示.
select ename,(select CASE
WHEN job = 'CLERK' then '职员'
WHEN job = 'MANAGER' then '经理'
WHEN job = 'SALESMAN' then '销售人员'
else job END)
from emp;
395
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
