公众号 CppCoding
show databases;//显示所有数据库
use 库;//进入库
show tables;//查看库中的表
select database();//查看当前所在的库
desc table;//查看表结构
select version();//查看mysql版本
注释 # 单行
/* */多行
起别名
select 100%98 as 结果;//as可以省略
去重 distinct
select distinct department_id from employees;
+的作用(在mysql中+的作用只有算数加和作用)
select 10+11;//21
select '100'+10;//110
select 'from'+90;//90
select null+any;//只要其中一个为null,结果为null
//加和中出现字符型,则自动转为数值型,若转换成功,则使用加法,若转换失败,则字符串值为0
拼接字符串 concat
select concat(last_name,first_name) as 姓名;
函数 ifnull
ifnull(str1,str2)//判断str1字段里的值是否是null,若是则输出str2,若不是则输出原来的值
显示表employees的全部列,各个列之间使用,连接,列头显示为out_put
select concat(`first_name`,',',ifnull(last_name,0)) as out_put
from employees;
** 注意:字段使用`为反引号,字符使用单引号 **
条件查询
结构:
where
条件运算符:> < = !=(<>) >= <=
逻辑运算符:&&(and) ||(or) !(not)
模糊查询:like(not like) between and in is null(is not null)
查询工资大于12000员工的信息
select *
from employees
where salary>12000;
部门编号不等于90的员工名和部门编号
select last_name,department_id
from employees
where department_id != 90;
查询工资在10000-20000之间的员工的名字,工资和奖金
select last_name,salsry,commission_pct
from employees
where salary>=10000 and salary<=20000;
部门编号不在90到110之间,或工资高于15000的员工信息
select *
from employees
where department_id<90 and department_id>110 or salary>15000;
##### 模糊查询
_ 代表代替一个字符
% 通配符
员工名中包含字符a的员工信息
select *
from empolyees
where last_name like '%a%';
查询员工名第三个字母为a,第五个为a的员工信息
select *
from employees
where last_name like '__a_a%';
查询员工名第二个字符为_的信息
select *
from employees
where last_name like '_\_%'; \为转义字符
也可以写为
'_$_%' escape '$'; escape是指定一个字符转义字符
员工编号在100-200之间
where employee_id between 100 and 200;
查询员工的工种编号是IT_PROG,AD_UP中的一个的信息
where iob_id in ('IT_PROG','AD_UP');
查询没有奖金的员工名和奖金率
where commission_pct is null;
where commission_pct is not null; 有奖金率
**=或者<>不能用于判断null值
<=> 安全等于,可以判断null值也可以判断数值=
排序查询
语法
select
from
where
order by//(排序)按照某一个字段 asc 升序 desc 降序
工资从高到低
select * from empolyees order by salary desc;
部门编号>=90的员工信息,按照时间先后
select * from empolyees where department_id >=90 order by hireday asc;
按照年薪高低显示员工信息和年薪
select *,salary*12(1+ifnull(cpmmission_pct,0)) as 年薪 from employees order by 年薪 desc;
按照姓名长度显示姓名和工资
select length(last_name) 姓名长度,last_name,salary from employees order by 姓名长度 desc;
查询员工信息要求先按照工资升序再按照员工编号降序
select *
from employees
order by salary asc,employee_id desc;
常见函数
length('张三丰abc');//3*3+3=12 utf-8的汉字为3个字节
upper('abc');//ABC
lower('AFv');//afv
将姓变大写,将名变小写然后拼接
select concat(upper(last_name),lower(first_name));
==字符串函数的第一个索引从1开始==
substr//字符截取
用法1:substr('字符串',num);//表示在第num个字符处截取,从第num个字符处显示,第num个也显示
用法2:substr('字符串',num,count);//表示在num开始截取,总共count个字符。
instr//查找子串在主串的第一个索引,找不到为0
select instr('123456','1');//返回1
==注意:只去掉前后的,中间不去==
trim //去掉前后的空格
select length(trim(' abc '));//返回3
select length(trim(' a bds '));//返回5
lpad//用指定的字符实现左填充
select lpad('abc',10,'+');//+++++++abc
select lpad('abc',2,'_');//ab
rpad//右填充
replace//替换
select replace('ababab','a','b');//bbbbbb
round//四舍五入
round(-1.45);//-1
round(1.65);//2
round(-1.657,2);//-1.66
ceil//向上取整(大于或者等于该参数的最小整数)
select ceil(1.002);//2
floor//向下取整
truncate//截断
select truncate(1.299,1);//1.2 注意,后面的数字是要保留的小数后面的位数,截断不能进位
mod//取余
select mod(-10,3);//-1
now//返回当前系统时间日期
select now();
curdate//返回日期
curtime//返回时间
获取指定时间的年、月、日、时分秒
select year(now());//剩下的类似
sum//求和
select sum(salary) from employees;
avg//求平均值
max/min //求最大或者最小
count//求非非空值有多少
sum和avg支持数值型
max、min、count支持日期、名字、函数等
max、min、count、sum和avg忽略null值
count//可用来统计行数
select count(*) from employees;
流控制函数
if//实现if else的效果
select if(10<5,'true','false');//如果第一个语句是正确的则返回第二个,否则将返回第三个
case//实现switch case效果
case 要判断的表达式
when 常量 then 要显示的
else 要显示的值
end
select salary,
case department_id
when 30 then salary*1.1
when 40 then salary*2
else salary
end as 新工资
from employees;
case//第二种用法,类似多重if
case后面不加字段
select salary
case
when salary>2000 then 'A'
when salary>3000 then 'B'
else 'C'
end;
=总结:两者的不同在于,第一种是等值查询,第二种是条件查询
#### 分组语句
语法:
select 显示的列,group by后面出现的字段
from 表
where 条件
group by 列
order by 字段
查询每个位置上部门的个数
select count(*),location_id
from departmentsgroup
group by location_id;
查询邮箱中包含a的每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;
查询哪个部门员工数>2
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
having max(salary)>12000;
==having的使用场景是在已经查询完的基础上再次查询==
按照多个字段分组
查询每个部门每个工种的员工平均工资
select avg(salary),department_id,job_id
from employees
group by department_id,job_id;
连接查询
查询女孩表中有男朋友的名字
select girls,boys from gid,boyid;//最后显示3*4=12行 假设girls为3行,boys为4行
正确的查询是
select name,boyname
from girls,boys
where girls_id=boys_id;
SQL99语法支持内、外、交叉连接(不支持全外连接)
内连接:支持等值连接,非等值连接,自连接
外连接:支持左外连接,右外连接,全外连接
==等值连接:==
查询有奖金的员工名和部门名
select last_name,department_id
from employees e,department_id d
where e.department_id=d.department_id
and e.commission is not null;
查询每个城市的部门个数
select count(*),city
from departments d,location l
where d.location_id=l.location_id
group by city;
三表连查
查询员工名,部门名和所在的城市
select last_name,department_name,city
from employees e,departments d,location l
where e.department_id=d.department_id
and d.location_id=l.location_id;
==非等值连接==
查询员工的工资和工资级别
select salary,grade_level
from employees e,job_grades g
where salary between g.lowest and g.highest;
[非等值的详细介绍](https://blog.csdn.net/Dear_mango/article/details/80612757)
==自连接==指从一张表中筛选等值条件
select e.employees_id,e.last_name,m.employees_id,m.last_name
from employees e,employees m
where e.manager_id=m.employee_id;
SQL99语法
select 查询列表
from 表1
[连接类型] join 表2
on 连接条件
where 筛选条件
group by
order by
内连接 inner
左外连接 left outer
右外连接 right outer
全外连接 full outer不支持
交叉连接 cross
==内连接==对表的顺序没有要求
查询员工名和部门名
select last_name,department_name
from departments d
inner join employees e
on e.department_id=d.department_id;
查询员工名,部门名,工种名,并按照部门名降序
select last_name,department_name,job_title
from employees e
inner join departments d
on e.department_id=d.department_id
inner join jobs j
on j.job_id=e.job_id
order by departtment_name desc;
**需要注意多表连接的时候是一个inner后面跟着自己的on(筛选条件)
==外连接==
用于查询一个表有另一个表没有的记录
外连接的查询结果是主表中所有记录
如果从表中有匹配的则显示从表内容
如果没有,则显示null
左外连接的主表是左边的
外连接对表的顺序有要求
哪个表当主表哪个表就在前面
子查询
子查询是出现在其他语句内部的select语句
出现的位置:
select后//支持标量子查询
from后//表子查询
where或者having //标量子查询、列子查询、行子查询
exists//相关子查询
按行列数不同分为:标量子查询(一行一列)
列子查询(一行多列)
行子查询(多行一列)
表子查询(多行多列)表子就是相关子查询
特点:
1.子查询在小括号内
2.在条件的右侧
==标量子查询==
使用单行操作符(大于,小于,等于,不等于,大于等于,小于等于)
查询谁的工资比Abel高
select *
from employees
where salary>(
select salary
from employees
where last_name='Abel'
);
返回job_id与141号工种相同salary比143号员工多的工资姓名job_id和工资
select salary,last_name,job_id
from employees
where job_id=(
select job_id
from employees
where employee_id=141
)and salary>(
select salary
from employees
where employee_id=143
);
==列子查询==
使用多行操作符
in/not in(查询列表 等于列表中的一个)
any/some(和查询返回的某一个值比较)
all(和所有的比较)
返回location_id是1400或者1700的部门中所有员工的姓名
select last_name
from employees
where department_id in(
select distinct department_id
from departments
where location_id in(1400,1700)
);
查询员工编号最小且工资最高的员工信息
select *
from employees
where(employee_id,salary)=(
select min(employee_id),max(salary)
from employees
);
from后面
exists后面
分页查询
应用场景:当要显示的数据一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by分组字段
having 分组后的筛选
order by排序的字段】
limit offset,size;
offset是要显示的条目的起始索引(从0开始)
size是要显示的条目个数
特点:
1,limit语句在查询语句最后
2,公式
要查询的页数 page,每页的条目数 size
select 查询列表
from 表
limit (page-1)*size,size;
案例1:查询前五条员工信息
select * from employees limit 0,5;
案例2:查询第11条到第25条
select * from emploees limit 10,15;
案例3:有奖金的员工信息,并且工资较高的前十名显示
select *
from employees
where commission_pct if not null
order by salary desc
limit 10;
案例4:查询所有学员邮箱的用户名
select substr(email,1,instr(email,'@')-1)
from stuinfo;
联合查询
union 联合 合并:将多个查询结果合并为一个结果
语法:
查询语句
union
查询语句
union
...
特点:
1.要求查询的列数(字段数)一样
2.使用union会自动去重,使用union all会包含重复
应用场景:要查询的结果来自多个表,且多个表没有直接的连接关系,但是查询的信息(字段数)一致时就可以使用