练习题:p75、76、77、86、94、93,96,97,98,99
查询时为字段起别名(如果别名中有特殊符号,建议将别名用双引号/单引号引起来)
select 列名1 as 别名1, 列名2 as 别名2 from 表名;
select 列名1 别名1, 列名2 别名2 from 表名;
查询员工表中的部门编号,去重 distinct
select distinct department_id from employees;
mysql中(加号) + 的作用
1:mysql中加号只能做为运算符,不能做连接符
2:‘123’+90 : 其中一方为字符型,mysql会试图将字符型转换为数值型,转换成功,正常做加法运算,转换失败,将字符型数值转换为0,然后做加法运算
3:null+10:只要有一方为null,结果就肯定为null.
mysql拼接 concat(字段1,字段2,,,字段n) 函数
employees表中查询员工名(last_name)和姓(first_name)连接成一个字段,并显示为 姓名
select concat(first_name,last_name) as 姓名 from employees;
注意: 如果concat函数中有一个字段为null,则查询结果为null
具体视频
ifnull(字段名,返回值) 函数
查询表employees中所有员工的奖金率(commission_pct),如果奖金率为null,则奖金率显示为0
select concat(last_name,first_name) as 姓名 , ifnull(commission_pct,0) as 奖金率 from employees
显示出表employees的全部列(名,姓,奖金率),如果奖金率为null则显示为null,各个列之间用逗号连接,列名显示成out_put
select
concat(first_name, ',' , last_name, ',' , ifnull(commission_pct,0)) as 'out_put'
from employees;
isnull(字段名) 函数
如果每一个字段的值是null,返回1,字段值不为null,返回0
条件查询
1:按条件表达式筛选:
> < = !=或者<> >= <=
2:按逻辑表达式:
&&或者and ||或者or !或者not
查询部门编号不是在90到110之间,或者工资高于15000的员工信息
select * from employees where not(department_id >= 90 and department_id <= 110) or salary > 15000;
3:模糊查询
like , between and , in , is null , is not null
与like息息相关的通配符:
% : 匹配任意多个字符
_ : 匹配一个字符
模糊查询会过滤掉空值
查询 employees 表中名含有a的员工的所有信息
select * from employees where last_name like '%a%';
查询 employees 表中员工名中第二个字符为o,第五个字符为a的员工所有信息
select * from employees where last_name like '_o__a%';
查询员工id(employee_id)在100到120区间内的信息
select * from employees where employee_id>=100 and employee_id<=120;
select * from employees where employee_id>=100 && employee_id<=120;
select * from employees where employee _id between 100 and 120;
查询没有奖金率(commission_pct)的员工名和奖金率
select last_name,commission_pct from employees where commission_pct = null
错误:等于号不能判断null值
select last_name,commission_pct from employees where commission_pct is null
order by():排序查询(默认为升序)
查询员工信息,要求工资从低到高
select * from employees order by salary asc;
查询员工信息,要求工资从高到低
select * from employees order by salary desc;
查询员工信息,要求聘用时间从高到低
select * from employees order by hiredate desc;
查询员工名字并按首字母升序排列
select last_name from employees order by substr(last_name,1,1) asc
查询员工表的 employee_id,job_id,last_name ,按照 department_id 降序,salary升序
select employee_id,job_id,last_name from employees order by department_id desc, salary asc;
求长度 length(字段名) 函数
查询员工id, 姓名,姓名长度,并按姓名长度升序排列
select employee_id 员工id, concat(last_name,first_name) 姓名 , length(last_name)+length(first_name) 姓名长度 from employees order by 姓名长度 asc;
查询员工id, 姓名,姓名长度,并按姓名长度升序排列,如果长度相同,按id升序排列
select employee_id 员工id, concat(last_name,first_name) 姓名 , length(last_name)+length(first_name) 姓名长度 from employees order by 姓名长度 asc ,员工id asc;
查询员工表中员工工资不在8000~17000之间的全部信息,并按工资排序
select * from employees where not (salary>=8000 && salary<=17000) order by salary asc
单行函数
- 字符函数
- 数字函数
- 日期函数
- 其他函数
- 流程控制函数
字符函数
length():获取参数值的字节个数
concat():拼接字符串
upper(): 将参数值大写
lower():将参数值小写
查询员工表中所有员工的姓名,将姓大写,名字小写
select concat(lower(first_name),upper(last_name)) 姓名 from employees ;
substr() / substring() :字符截取函数
select substr('我爱你没道理!' , 4) 字符截取
select substr('我爱你没道理!' , 2 , 6) 字符截取
查询员工表中所有员工的名字,并将首字母大写
select
concat(
upper(
substr(last_name,1,1)
),
lower(
substr(last_name,2)
)
) as 名字
from employees ;
instr() : 返回一个字符串在另一个字符串第一次出现的起始索引,如果找不到,返回0
select instr('OK兄弟们,全体目光向我看齐,看我看我,我宣布个事儿,我是个傻逼,没毛病啊' , '我是个傻逼') as 起始索引
rand(): 获取一个0到1之间的随机浮点数
select rand();
trim(): 去掉字符串头尾的空格
select trim(' 社会你虎哥 ');
trim( ~ from ~):去掉字符串中的某个字符
select trim( 'q' from 'qqq社会你龙哥q');
lpad():用指定字符,将某一字符串填充到指定长度 ,从左填充
select lpad('你好',5,'*');
rpad():用指定字符,将某一字符串填充到指定长度 ,从左填充
select rpad('你好',5,'*');
replace():替换
select replace('社会你虎哥' , '虎哥' , '虎弟');
round() : 四舍五入 进位
select round(1.5); #结果: 2
select round(1.4); #结果: 1
select round(-0.7); #结果:-1
select round(1.225,2); #结果:1.23
ceil():向上取整 返回>=该参数的最小整数
select ceil(1.02); #结果:2
select ceil(-1.02); #结果:-1
floor():向下取整 返回<=该参数的最小整数
select floor(1.02); #结果:1
select floor(-1.02); #结果:-2
truncate():截断(小数点后保留几位,不进位)
select truncate(1.9999,1); #结果:1.9
mod():取余/求模
select mod(10,3); #结果:1
select mod(10,-3); #结果:1
select mod(-10,3); #结果:-1
select mod(-10,-3); #结果:-1
now():返回当前系统日期+时间
select now(); #结果:2021-11-28 16:05:09
curdate():返回当前系统日期
select curdate(); #结果:2021-11-28
curtime():返回当前系统时间
select curtime(); #结果:16:09:12
datediff():查询两个日期之间存在多少时间间隔
查询出生到现在活了多少天
select datediff(now(),'2000-10-28');
获取年,月,日
select year(new());
select year('1999-10-11');
select year('1999/10/11');
select year('1999.10.11');
select year(表中带有年份时间的字段);
month(~):获取月份
day(~):获取日
time(~):获取时
str_to_date():将字符通过指定的格式转换为日期
其他函数:
version():查看当前数据库版本
database():查看当前所在库
user():查看当前所登录的用户
流程控制函数:
if():点击观看视频
分组(聚合)函数
分类:
- sum():求和 不计算为null的值
- avg():求平均 不计算为null的值
- max():求最大 不将为null的值包括在内
- min():求最小 不将为null的值包括在内
- count():计算个数 不将为null的值包括在内
sum():求和
查询员工表中所有员工一个月工资的总和(不算奖金)
select sum(salary) from employees;
查询员工表中所有员工一个月工资的总和(算奖金)
select sum(salary*(1+ifnull(commission_pct,0))) from employees;
avg():平均
查询所有员工薪资的平均值
select avg(salary) from employees;
max():返回最大值
select max(salary) from employees;
min():返回最小值
select min(salary) from employees;
查询所有员工的平均值,最大值,最小值,工资总和
select avg(salary) 平均值,max(salary) 最大值,min(salary) 最小值,sum(salary) 工资总和 from employees;
count():返回当前字段不为null的个数/总数
select count(commission_pct) from employees;
分组查询:group by
查询每个工种的最高工资
select max(salary) , job_id from employees group by job_id;
查询邮箱中包含a字符的,每个部门的平均工资
select avg(salary) ,department_id from employees where email like '%a%' group by department_id ;
查询有奖金(commission_pct为奖金率)的每个领导手下员工的最高工资
select max(salary) 最高工资 ,manager_id from employees where commission_pct is not null group by manager_id;
筛选:having (位于group by字句后面)
注意:
- having与where类似,用于筛选数据
- where用于分组(group by)前
select id,name from employees where id>10 group by age
- having用于分组(group by)后
- where后跟的字段为表中原有字段
- having后的字段可以为分组函数
查询哪些部门的员工数大于2
#错误写法:
select count(*) 员工个数, department_id from employees where count(*) > 2 group by department_id;
#正确写法:
select count(*) 员工个数, department_id from employees group by department_id having count(*) > 2;
查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary) 最高工资 , job_id from employees where commission_pct is not null group by job_id having 最高工资 > 12000
查询每个部门每个工种的员工的平均工资
select avg(salary),job_id,department_id from employees group by department_id,job_id;
连接查询(多表查询) 点击观看视频
-
内连接(sql92,sql99)
- 等值连接
- 非等值连接
- 自连接
-
外连接
-
左外连接:查询表一中有,表二没有的数据
-
右外连接:查询表二中有,表一中没有的数据
-
全外连接/全连接(mysql不支持):查询表一,表二共有的数据+查询表一中有,表二没有的数据+查询表二中有,表一中没有的数据
MySQL虽然语法不支持全连接,但是可以通过左连接+union(联合查询)+右连接实现功能select * from tbl_dept left join tbl_emp on tbl_dept.id=tbl_emp.deptId union select * from tbl_dept right join tbl_emp on tbl_dept.id=tbl_emp.deptId;
-
select * from tbl_dept left join tbl_emp on tbl_dept.id=tbl_emp.deptId where tbl_emp.deptId is null
union
select * from tbl_dept right join tbl_emp on tbl_dept.id=tbl_emp.deptId where tbl_dept.id is null;
- 交叉连接:笛卡尔乘积
(内连接) 等值连接(sql92)
查询女神对应的男神名
select beauty.name ,boys.boyname from boys,beauty where boys.id=beauty.boyfriend_id;
查询员工名,工种号,工种名
select employees.employee_id ,jobs.job_id,jobs.job_title from employees,jobs where jobs.job_id=employees.job_id
查询城市名中第二个字符为o的部门名和城市名
select
d.department_id,l.city
from
departments d,locations l
where
d.location_id = l.location_id
and
substr(l.city,2,1) = 'o';
查询每个城市的部门个数
select
count(department_id) ,locations.city
from
departments,locations
where
departments.location_id = locations.location_id
group by
locations.city;
查询有奖金的 每个部门的部门名 和 部门的领导编号 以及 该部门的最低薪资
select
departments.department_name,departments.manager_id,min(employees.salary)
from
employees,departments
where
employees.department_id = departments.department_id
and
employees.commission_pct is not null
group by
departments.department_id;
实现三表连接查询(多表连接,多表查询)
查询员工名,部门名和所在城市
select
employees.last_name,departments.department_name,locations.city
from
employees,departments,locations
where
employees.department_id = departments.department_id
and
departments.location_id = locations.location_id;
(内连接)非等值连接
查询员工的工资和工资级别
select
employees.last_name,employees.salary ,job_grades.grade_level
from
employees,job_grades
where
employees.salary
between
job_grades.lowest_sal
and
job_grades.highest_sal;
自连接(自己连接自己)
查询员工名,员工id和对应的上级领导名,领导id
select
e.last_name,e.employee_id,m.last_name,m.employee_id
from
employees as e ,employees as m
where
e.manager_id = m.employee_id;
sql99语法:
等值连接
内连接:( 表一 inner join 表二 on 连接条件)
- 等值连接
- 非等值连接
- 自连接
查询员工名以及对应的部门名
#sql99语法
select
e.last_name,d.department_name
from
employees e
inner join
departments d
on
e.department_id = d.department_id;
#sql92语法
select
e.last_name,d.department_name
from
employees e , departments d
where
e.department_id = d.department_id;
查询名字中包含e的员工名和与之对应的工种名
select
employees.last_name,jobs.job_title
from
employees
inner join
jobs
on
employees.job_id = jobs.job_id
where
employees.last_name
like
'%e%';
查询部门个数大于3个的城市名和部门个数
select
l.city,count(department_id) 部门个数
from
locations l
inner join
departments d
on
l.location_id = d.location_id
group by
l.city
having
部门个数 > 3
等值连接(三个表)
查询员工名,部门名,工种名,并按部门名降序
select
e.last_name,d.department_name,j.job_title
from
employees e
inner join
departments d
on
e.department_id = d.department_id
inner join
jobs j
on
e.job_id = j.job_id
order by
d.department_name
desc;
非等值连接
查询员工工资级别
#sql99语法
select e.salary,j.grade_level from employees e inner join job_grades j on e.salary between j.lowest_sal and j.highest_sal
#sql92语法
select e.salary,j.grade_level from employees e ,job_grades j where e.salary between j.lowest_sal and j.highest_sal;
查询每个工资级别对应的员工个数,并且按工资级别降序
select j.grade_level,count(*) from employees e inner join job_grades j on e.salary between j.lowest_sal and j.highest_sal group by j.grade_level order by grade_level desc;
自连接
查询员工的名字,以及员工所对应的上级的名字
select
e.last_name,m.last_name
from
employees e
inner join
employees m
on
e.manager_id = m.employee_id;
外连接
点击观看尚硅谷视频
查询没有男朋友的女神
select
b.`name`
from
beauty b
left join
boys bo
on
b.boyfriend_id = bo.id
where
bo.id
is null
交叉连接:笛卡尔乘积
#sql92语法
select * from beauty,boys;
#sql99语法
select * from beauty cross join boys;
子查询(嵌套查询)
谁的工资比abel高
select
last_name
from
employees
where
salary > (
select
salary
from
employees
where
last_name = 'abel'
);
查询location_id是1400或1700的部门中的所有员工姓名 (in)
select
last_name
from
employees
where
department_id
in(
select
distinct
department_id
from
departments
where
location_id
in(1400,1700)
)
any/some(功能相同)
返回其他部门中比job_id为it_prog部门任一工资低的员工的员工号,姓名,job_id以及salary
select
employee_id,last_name,job_id,salary
from
employees
where
salary < some(select distinct salary from employees where job_id = 'it_prog')
and
job_id != 'it_prog';
all
返回其他部门中比job_id为it_prog部门所有工资低的员工的员工号,姓名,job_id以及salary
select
employee_id,last_name,job_id,salary
from
employees
where
salary < all(select distinct salary from employees where job_id = 'it_prog')
and
job_id != 'it_prog';
查询每个部门的员工个数
select
d.department_id ,
(select count(*) from employees e where e.department_id = d.department_id) 个数
from
departments d;
查询每个部门的平均工资的工资等级
select
ag_dep.department_id , ag_dep.a,j.grade_level
from
(select department_id , avg(salary) a from employees e group by e.department_id) ag_dep
inner join
job_grades j
on
ag_dep.a
between
j.lowest_sal
and
j.highest_sal
分页查询: limit 起始位置(从0开始),页面条数
查询员工表的前五条
select * from employees limit 0,5;
联合查询: union
查询部门编号 大于90 或者邮箱包含a的员工信息
select * from employees where email like '%a%' or department_id > 90;
select * from employees where email like '%a%' union select * from employees where department_id > 90;
联合查询特点:
- 要求多条查询语句的查询列数是一致的
- 要求多条查询语句的查询的每一列的类型和顺序是一致的
- union关键字默认去重,如果使用union all 可以包含重复项
正则表达式