mysql练习题

1.查出在10部门的员工名字,工资

select ename,salary from emp where deptno=10

2.找出工资大于1200元的员工全名、工资、职称

select ename,salary,job from emp where salary >1200

3.查出在10，30部门的员工名,薪水

select ename,salary from emp where deptno=10 and 30

4.找出工资大于1500并且没有提成的员工

select * from emp where salary >1500 and comm is null

5.查出名字是以M打头的员工

select *from emp where ename like 'M%

6.查出姓名中第三个字母是e的员工

select * from emp where substr(ename,3,1)='e'

7.找出没有提成率的员工

select * from emp where comm is null

8.找出工资在950(含)至1200(含)元的员工姓名、职称

select ename,job from emp where salary between 950 and 1200

9.找出2月份入职的员工名、入职时间、工资

select ename,hiredate,salary from emp where month(hiredate)=2

查询出所有员工的职位，不重复

select distinct job from emp 

查询出各个部门的员工数量

select deptno,count(*) from emp group by deptno

– 查询出各年份入职的员工情况（年份，员工人数）

select year(hiredate) as year,count(*) from emp group by year(hiredate) order by year(hiredate)

– 查询出每年每月入职的员工情况（年份月份，员工人数）

select year(hiredate) as year, month(hiredate) as month,count(*) from emp group by year(hiredate),month(hiredate) order by year(hiredate),month(hiredate)

【1. 字符函数】
1.获得’goodmorning’ 的字符长度

select length('goodmoring')

​ 2.将Hello全部转换成小写字母

select lower('HellOKKK')

​ 3.将Hello全部转换成大写字母

select upper('Hello')

​ 4.将hello首字母变为大写字母

select concat(upper(substring('hello',1,1)),substring('hello',2))

​ 5.将’hello’ 与 ‘tom’ ,’good’拼接成一个单词

select concat('hello','tom','good')

​ 6.获得’goodmorning’ 的第4 个字母

select substring('goodmorning',4,1)

​ 7.截取’goodmorning’ 的moring 单词

select substring('goodmorning',5)

​ 8.截取’Good morning ！I’m Tom ‘ 中的’o’ 用’A’来替代

SELECT REPLACE(SUBSTRING('Good  morning ! I\'m Tom ', LOCATE('o', 'Good  morning ! I\'m Tom ')), 'o', 'A');

【2. 日期函数】
1.获得当前日期

select current_date()

2. 获得某一个日期的年份，月份，日期

select year(date),month(date),day(date) from mytable where date='2023-02-13'

2. 获得某一个日期的星期

select datename(date) as weekday from mytable where date='2023-04-11'

2. 获得当前日期的时分秒

select time(now())

2. 获得2天后的日期

select date_add(curdate(),interval 2 day)

2. 获得前2天的日期

select date_sub(curdate(),interval 2day)

2. 获得两个日期相差的年份，月份，天数

select timestampdiff(year,date1,date2)as diff_year
       timestampdiff(month,date1,date2)as diff_month
       timestampdiff(day,date1,date2) as diff_day
       from mytable

【3. 数值函数】
1.获得38934.4383的整数部分

select floor('38934.4383')

2.将38934.4387的保留2位小数，不需要四舍五入,直接截取

select truncate(38934.4387,2)

3.将38934.4387的保留2位小数，需要四舍五入

select round(38934.4387,2)

4.获得不小于38934.4383的最小正整数

select ceiling(38934.4383)

5.获得不大于38934.4383的最大的正整数

select floor(38934.4383)