数据库MySQL-Day4-函数
数据库MySQL-Day4-函数
统计函数
COUNT
计数
select count(*)|count(列名) from table
[where where_definition];
#实例
#1.统计一个班有多少学生
select count(*) from student
#2.统计数学成绩大于90的学生有多少
select count(*) from student
where math>90;
#3.统计总分大于250的人数有多少
select count(*) from student
where (chinese+math+english)>250;
- count(*)和count(列)的区别
- count(*)，返回满足条件的记录数
- count(列)，返回满足条件的记录数，但会排除空值
SUM
求和
select sum(列名){.sum(列名)...} from tablename
[where where_definition];
#案例
#1.统计一个班级数学总成绩
select sum(math) from student;
#2.统计一个班，数学、英文、语文各科总成绩
select sum(math),sum(chinese),sum(english) from student;
#3.统计一个班级语文数学英语的成绩总和
select sum(math+chinese+english) from student;
#4.统计一个班级语文成绩平均分
select sum(chinese)/count(*) from student;
- sum只适用于数值型，否则报错
AVG
求平均，只适用于数值型
select avg(列名) {,avg(列名)...} from tablename
{where where_definition};
#案例
#1.求一个班级数学的平均分
select avg(math) from student;
#2.求一个班级总分平均分
select avg(math+chinese+english) from student;
MAX/MIN
求最大、最小值，只适用于数值型
select max(列名) from tablename
{where where_definition};
#案例
#1.求班级最高分
select max(math+english+chinese),min(math+english+chinese)
from student;
分组函数
select column1,column2,... from table
group by column
- group by后面的column是分组依据
#having子句对分进行过滤
select column1,column2,... from table
group by column having...
#案例
#先创建测试表
#部门表
create table dept(
deptno mediumint unsigned not null default 0,
dename 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');
#员工表
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',7968,'1991-2-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7968,'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,'1991-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,'1991-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),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);
#逐步击破
#显示每个部门的每种岗位的平均工资和最低工资
#1.按照 部门 对 平均工资 和 最低工资 分组
select avg(sal),min(sal),deptno
from emp group by deptno;
#2.按照 每个部门 的 每种岗位 的 平均工资 和 最低工资 分组
select avg(sal),min(sal),deptno,job
from emp group by deptno,job;
#显示平均工资低于2000的部门号和他的平均工资
#1.显示各个部门的平均工资和部门
select avg(sal),deptno
from emp group by dptno;
#2.+低于2000
select avg(sal),deptno
from emp group by dptno
having avg(sal)<2000;
#也可使用别名，提高效率
select avg(sal) as avg_sal,deptno
from emp group by dptno
having avg_sal<2000;
字符串函数
#实例
#1.返回字串字符集（utf8,...)
select charset(ename) from emp;
#2.连接字串，将多个列拼接成一列
select concat(ename,'job is',job) from emp;
#3.返回substring在string中出现的位置，没有则返回0
select instr('hanshunping','ping') from dual;
#dual 是亚元表，作为系统默认的测试表使用
#4.转换大小写
select ucase(ename) from emp;
select lcase(ename) from emp;
#5.从某列的字符串左/右边数起，取出对应字符
select left(ename,2) from emp;
select right(ename,2) from emp;
#6.统计长度(按字节返回)
select length(ename) from emp;
#7.如果是manager就替换成经理
select ename,replace(job,'manager','经理') from emp;
#8.strcmp比较两字符串大小，以ascii码为准
select strcmp('hsp','jsp') from dual;
#9.从ename 列的第一个位置开始取出2个字符
select substring(ename,1,2) from emp;#(只能从1开始)
#10.去空格
select ltrim(' 韩顺平教育') from dual;#去除前端空格
select rtrim('韩顺平教育 ') from dual;#去除后端空格
select trim(' 韩顺平教育 ') from dual;#去除两端空格
#用两种方式，以 首字母 小写 的方式显示所有员工的emp表的姓名
select replace(ename,left(ename,1),lcase(ename)) from emp;
select concat(lcase(substring(ename,1,1))，substring(ename,2)) from emp;
select concat(lcaseleft(ename,1))，substring(ename,2)) from emp;
数学函数
#案例
#1.
select abs(-10) from dual;
#2.
select bin(10) from dual;
#3.
select ceiling(10.5) from dual;
select floor(10.5) from dual;
#4.把十进制的8转化为二进制
select conv(8,10,2) from dual;
#5.保留小数位数（四舍五入）-》 78.12
select format(78.123458,2) from dual
#6.范围0《《1.0，给了固定seed会给固定值
select rand(6) from
日期函数
#案例
#1.
select current_date() from dual;#当前日期
select current_time() from dual;#当前时间
select current_timestamp() from dual;#当前时间戳
select now() from dual;#日期时间
#2.显示所有新闻信息，发布日期只显示日期，不显示时间
select id,content,date(send_time) from mes;
#3.请查询在10分钟内发布的新闻
#即发送新闻的时间+10min>=now
select *
from mes
where date_add(send_time,interval 10 minute) >=now();
#4.相差天数
select datediff('2011-11-11','1990-01-01') from dual;
#用sql语句求出自己活了多少天
select datadiff(now(),'2004-12-03') from dual;
#如果你活80岁，求出还能活多少天
select datediff(DATE_ADD('2004-12-03',interval 80 year),now()) from dual;
- date_add/sub 后面的interval可以是year,day,minute,second等
- date_add中的date也可以是datetime
- datediff中date1-date2，前-后，可能为负
加密函数
#用户@IP地址
select user() from dual;
#可以不带from;查看当前数据库名称
select database();
#在数据库中存放的是加密后的密码，MD5 32的字符串
#密码不要直接存明文
select password('hsp') from dual;
#select* from 数据库.表
流程控制函数
#案例
#三目运算符？：
select if(true,'北京'，'上海') from dual;
select if(false,'北京'，'上海') from dual;
#判空
select ifnull(expr1,expr2);
#判空不能用'='
#
select case
when true then 'jack'
when false then 'tom'
else 'mary' end;
#如果emp表的job是clerk，则显示职员，是manager显示经理，是salesman显示销售，其他正常显示
select enmae,(select case
when job ='clerk' then '职员'
when job ='manager' then '经理'
when job ='salesman' then '销售'
else job end
)as job
from emp;
浙公网安备 33010602011771号