--- 常用的函数
-- 日期函数
-- 用date_format将文本'2019-01-01'转换为日期型
select date_format('2019-01-01','%Y-%m-%d'); ##大写y为四位数字的年,小写为两位数字的年
-- 用last_day函数返回当前月份的最后一天的日期
select now();
select last_day(curdate());
-- 用datediff函数计算两个日期相隔天数
select datediff(last_day(curdate()),curdate()); ##第一参数为结束时间,第二位开始时间
-- 字符串函数
-- 用instr函数查找w在hello world短语中出现的字符位置
select instr('cda','c'); ##查找所在位置
-- 用lower函数将HELLO转换为小写
select lower('HELLO');
-- 用upper函数将world转换为大写
-- 用concat函数将两个文本连接在一起 只能用concat函数 ||拼接是在oracle中 &连接是在excel中
select concat('c','da');
-- 用replace函数将hello earth短语中的earth替换为world
select replace('hello.earth','earth','world');
-- 用substr函数提取hello world文本中第三位开始5个字符
select substr('hello world',3,5); ##excal中的mid()函数一样
select mid('hello world',3,5);
-- 用cast函数将fruit表中的f_id整数型转换为字符串型
select cast(f_id as char(9)) from fruit; ##转换类型有一定的局限
-- 分组合并函数
-- 查询每个客户购买了哪几种水果
-- 分组合并函数
-- 查询每个客户购买了哪几种水果
select s_name group concat (f_name)
from fruit
group by s_name;
-- 逻辑函数
-- ifnull 将空值填充为0
select * ,ifnull(f_price,0)
from fruit;
-- if函数
-- 查询f_id,f_name,f_price和价格区间(f_price<10低,10<=f_price<20中,f_price>=20高)
select f_id,f_name,f_price,if(f_price<10,'低',if(f_price>=20,'高','中') as 价格区间
from fruit;
-- 逻辑表达式 case when ... then...else ... end
-- 查询f_id,f_name,f_price和价格区间(f_price<10低,10<=f_price<20中,f_price>=20高)
select f_id,f_name,f_price,case when f_price <10 then '低' when f_price >=20 then '高' else '中' end as 价格区间
from fruit;
单表查询练习
-- 创建雇员表
CREATE TABLE emp1 (
empno INT AUTO_INCREMENT PRIMARY KEY,
ename VARCHAR(15),
job VARCHAR(10),
mgr INT,
hiredate DATE,
sal DECIMAL(7 , 2 ),
comm DECIMAL(7 , 2 ),
deptno INT
);
-- 导入雇员信息
INSERT INTO EMP1 VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP1 VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO EMP1 VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO EMP1 VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
INSERT INTO EMP1 VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO EMP1 VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO EMP1 VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO EMP1 VALUES (7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,NULL,20);
INSERT INTO EMP1 VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP1 VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO EMP1 VALUES (7876,'ADAMS','CLERK',7788,'1987-5-23',1100,NULL,20);
INSERT INTO EMP1 VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
INSERT INTO EMP1 VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
INSERT INTO EMP1 VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);
SELECT * FROM emp1;
SELECT COUNT(*) FROM emp1;
-- 1.查询基本工资大于1500的员工信息
select *
from emp
where sal >1500;
-- 2.查询员工姓名不以S开头的任意长度的员工信息
select *
from emp
where ename not like 's%';
-- 3.查询职位为president或manager的员工信息
select *
from emp
where job = 'president' and job = 'manager'
或者
select *
from emp
where job in ('president','manager');
-- 4.查询工资在2000-3000元之间的员工信息
select *
from emp
where sal between 2000 and 3000 ;
-- 5.查询emp表中没有奖金的员工信息
select *
from emp
where comm is null;
-- 6.查询各部门平均工资、最高工资、最低工资、以及员工数量
select deptno,avg(sal),max(sal),min(sal),count(empno)
from emp1
group by deptno;
-- 7.查询平均工资在2000元以上的职位信息,并按照平均工资的降序显示
select job, avg(sal)
from emp1
group by job
having avg(sal)>2000
order by avg(sal) desc;
-- 8.查询各部门manager的平均工资
select job ,acg(sal),deptno
from emp
where job = 'manager'
group by deptno;
-- 9.查询员工姓名、入职日期、试用截止日期和工龄 ##工龄的计算now()还可以用curdate()
select ename,hiredate,date_add(hiredate,interval 3 month),datediff(now(),hiredate)/365
from emp;
-- 10.查询上层管理者工号及其对应的下属员工姓名
select mgr,group_concat(ename) ##group_concat() 分组连接
from emp
group by mgr;