DQL语言的学习
目录
基础查询
DQL DML DDL DCL TCL(事务和事务处理)
显示表结构:desc +表名;
语法:select 查询列表
from 表名;
特点:
1.查询列表可以是:表中的字段、常量值、表达式、函数,也可以是多个
2.查询的结果是一个虚拟的表格
示例:
1.查询表中的单个字段:select 字段名 from 表名;
2.查询表中的多个字段:select 字段名1,字段名2 from 表名;
3.查询表中的所有字段:
a、select 字段名1,2,3······ from 表名;(F12 格式化 F9 执行)
b、select *from 表名
4.查询常量值:select 常量值;
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
5.查询表达式:select 100%98;
不支持++ --
6.查询函数:select 函数名(实参列表);
select version();
7.起别名:
方式一:使用as
select 100%98 as 结果;
select last_name as 姓,first_name as 名 from 表名;
方式二:使用空格
select last_name 姓,first_name 名 from 表名;
(1)便于理解(2)如果要查询的字段有重名的情况,使用别名可以区分开来
8.去重
select distinct 字段名 from 表名;
select distinct department_id from employees;
9.+号的作用
功能:运算符,做加法运算
select 100+90;
select 数值+数值:直接运算, 两个操作数都为数值型,则做加法运算
select '123'+90; select 'john'+90;
select 字符+数值:其中一方为字符型,试图将字符型数值转换成数值型。
如果转换成功,则继续做加法运算 ;
如果转换失败,则将字符型数值转换成0。
select null+10;
select null+值;
只要其中一方为null,则结果肯定为null
NULL和任何字符段拼接最终结果都为null
10.concat函数 功能:拼接字符 select concat(字符1,字符2,字符3,········);
select conact('a','b','c')as 结果
select last_name+first_name as 姓名
from 表名;
11.ifnull函数
功能:判断某字段或表达式是否为null,如果为null返回指定的值,否则返回原本的值
select ifnull(commission_pct,0)from employees;
12.isnull函数
功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0
selsect isnull(commission_pct) from employees;
条件查询
语法:
select 查询列表
from 表名
where 筛选条件
分类:
1.按条件表达式筛选
条件运算符:> < = != <> >= <= <=>安全等于
2.按逻辑表达式筛选
逻辑运算符:&& || !
and or not
作用:用于连接条件表达式
3.模糊查询
like between and in is null is not null
使用转义字符
“\”,\_
&(任意一个字符)_ escape '&(任意一个字符)'
like:一般和通配符搭配使用,可以判断字符型或数值型
通配符:% 任意多个字符,包含0个字符
_任意单个字符
select *
from employees
where department_id like '1__';
between and:
1.使用between and 可以提高语句的简洁度2.包含两个临界值3.两个临界值不能调换
in
含义:判断某字段的值是否属于int列表中的某一项
特点:1.使用in提高语句简洁度2.in列表的类型必须一致或兼容
is null/is not null:用于判断null值
注意:=或<>不能用于判断null值
<=>安全等与:判断是否为返回数:可读性较差
is null: 仅仅可以判断null值,可读性较高,建议使用
<=>:既可以判断null值,又可以判断普通的数值,可读性较低
查询员工名中第二个字符为_的员工名
select
last_name
from
employees
where
last_name like '_$_%' escape '$';(转义字符)
is null
总结:
一、数据库的好处
1.可以持久化数据到本地
2.结构化查询
二、数据库的常见概念
1.DB:数据库,存储数据的容器
2.DBMS:数据库管理系统,又成为数据库软件或数据库产品,用于创建或管理DB
3.SQL语言:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流数据库软件通用的语言
三、数据库存储数据的特点
1.数据库存放到表中,然后再放到库中
2.一个库中可以有多张表,每张表具有唯一的表名来标识自己
3.表中有一个或多个列,列又成为“字段”,相当于java中“属性”
4.表中的每一行数据,相当于java中“对象”
四、常见的数据库管理系统:mysql oracle db2 sqlserver
一、MYSQL的背景
前身属于瑞典的一家公司,MYSQL AB
08年被sun公司收购,09年被oracle收购
二、MYSQL的优点
1.开源、免费、成本低
2.性能高、移植性也好
3.体积小,便于安装
三、MYSQL服务的启动和停止
方式一:net start 服务名
net stop 服务名
方式二:计算机--右击--管理--服务
四、MYSQL服务的登录和退出
登录:mysql【-h 主机名 -p 端口号】 -u 用户名 -p密码
退出:exit/ctrl+c
排序查询
排序查询
语法:
select 查询列表 3
from 表 1
【where 筛选条件】 2
order by 排序列表 【asc | desc】 4
特点:
1.asc代表的是升序,desc代表的是降序 如果不写,默认是升序
2.order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
3.order by子句一般是放在查询语句的最后面,limit子句除外
select * from employees order by salary desc;
按年薪的高低显示员工的信息和年薪【按表达式排序】
select *,salary*12*(1+ifnull(commission_pct,0)) 年薪
from employees
order by salary*12*(1+ifnull(commission_pct,0)) desc;
【按别名排序】
select *,salary*12*(1+ifnull(commission_pct,0)) 年薪
from employees
order by 年薪 desc;
按姓名的长度显示员工的姓名和工资【按函数排序】
select length(last_name) 字节长度,last_name,salary
from employees
order by length(last_name) desc;
查询员工信息,要求先按工资升序,再按员工编号降序【按多个字段排序】
select *
from employees
order by salary asc,employees_id desc;
查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
select *,length(email)
from employees
where email like '%e%'
order by length(email) desc,department_id asc;
常见函数
概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1.隐藏了实现细节.2.提高了代码的重用性
调用:
select 函数名(实参列表)【from 表】
特点:1.叫什么(函数名)2.干什么(函数功能)
分类:1.单行函数,如concat、length、ifnull等2.分组函数
功能:做统计使用,又成为统计函数、聚合函数、组函数。
单行函数
一、字符函数
1.length:获取参数值的字节个数
字母一个字节,汉字三个字节
2.concat拼接字符串
SELECT CONCAT(last_name,'_',first_name)姓名 FROM employees;
3.upper lower
将姓变大写,名变小写,然后拼接
select concat(upper(last_name),lower(first_name)) 姓名 from employees;
4.substr
注意:索引从1开始
一个数字:截取从指定索引处后面所有字符
二个数字:截取从指定索引处指定字符长度的字符
select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) out_put
from employees;
5.instr:返回子串中第一次出现的索引,如果找不到返回0
6.trim
7.lpad 用指定的字符实现左填充指定长度
8.rpad 用指定的字符实现右填充指定长度
9.replace 替换
10.rand 获取随机数,返回0-1之间的小数
二、数学函数
round 四舍五入
ceil:向上取整,返回>=该参数的最小整数
floor 向下取整,返回<=该参数的最大整数
truncate 截断
mod 取余:mod(a,b):a-a/b*b
三、日期函数
now 返回当前系统日期+日期
获取当前日期
str_to_date:将日期格式的字符转换成指定格式的日期(1999-09-13)
date_format:将日期转换成字符(2018年06月06日)
curdate 返回当前系统日期,不包含时间
curtime 返回当前时间,不包含日期
可以获取指定的部分,年、月、日、小时、分钟、秒
datediff 返回两个日期相差的天数
monthname 以英文形式返回月
四、其他函数
select version();当前数据库服务器的版本
select database();当前打开的数据库
select user();当前用户
password('字符'):返回该字符的密码形式
md5('字符'):返回该字符的md5加密形式
五、流程控制函数
1.if函数 if else 效果
if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
2.case函数的使用一:switch case的效果
java中:
switch(变量或表达式)
{
case 常量1:语句1;break;
·····································
default:语句n;break;
}
mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
···············································
else 要显示的值n或语句n
end
查询员工的工资,要求
部门号=30,显示的工资为1.1倍;
部门号=40,显示的工资为1.2倍;
部门号=50,显示的工资为1.3倍;
其他部门,显示的工资为原工资;
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;
case函数的使用二:类似于 多重if
java中:
if(条件1)
{
语句1;
}
else if(条件2)
{
语句2;
}
··············
else
{
语句n;
}
mysql中:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
······························
else 要显示的值n或语句n
end
分组函数
功能:用作统计使用,又成为聚合函数或统计函数或组函数
分类:sum求和 avg求平均值 max最大值 min最小值 count计算个数
特点:
1.sum avg一般用于处理数值类型 max min count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算
select sum(distinct salary),sum(salary) from employees;
select count(distinct salary),count(salary) from employees;
4.count函数的介绍:
count(字段):统计该字段非空值的个数
一般使用count(*) 统计结果集的行数
select count(*) from employees;#统计行数
select count(1) from employees;
select count(salary) from employees;
效率:
MYISUM存储引擎,count(*)的效率高
INNODB存储引擎下,count(*)和count(1)的效率差不多,比count(字段)要高一些
5.和分组函数一同查询的字段要求是group by之后的字段
datediff(日期一,日期二):日期一 一 日期二。
分组查询
分组数据:group by子句语法
可以使用group by 子句将表中的数据分成若干组
select column,group_function(column)
from table
[where condition]
[group group_by_expression]
[order by column]
注意:where一定放在from后面
语法:
select 分组函数,列(要求出现在group by的后面) 5
from 表 1
【where 筛选条件】 2
group by 分组的字段 3
【having 分组后的筛选】 4
【order by 排序列表子句】 6
注意:查询列表必须特殊,要求是分组函数和group by后出现的字段
特点:
1.分组查询中的筛选条件分为两类
数据源 | 位置 | 关键字 | |
分组前筛选 | 原始表 | group by 子句前面 | where |
分组后查询 | 分组后的结果值 | group by 子句后面 | having |
分组函数做条件肯定是放在having子句中
能用分组前筛选的,就优先考虑使用分组前筛选
2.group by 子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数(用的较少)
3.也可以添加排序(排序放在整个分组查询的最后)
查询邮箱中包含a字符的,每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;
查询有奖金的每个领导手下员工的最高工资
select max(salary),manage_id
from employees
where commission_pct is not null
group by manage_id;
查询哪个部门的员工个数>2
1.查询每个部门的员工个数
select count(*),department_id
from employees
group by department_id;
2.根据1的结果进行筛选,查询哪个部门的员工个数>2
select count(*),department_id
from employees
group by department_id
having count(*)>2;
查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
1.查询每个工种有奖金的员工的最高工资
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
2.根据1结果继续筛选,最高工资>12000
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;
查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及最低工资
1.查询每个领导手下的员工固定最低工资
select min(salary),manager_id
from empoyees
group by manager_id
2.添加筛选条件:编号>102
select min(salary),manager_id
from employees
where manager_id>102
group by manager_id
3.添加筛选条件:最低工资大于5000
select min(salary),manager_id
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;
连接查询
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1 有m行,表2有n行,结果m*n行
发生原因:没有有效的连接条件
如何避免:没有有效的连接条件
分类:
按年代分类:
sql92标准【仅仅支持内连接】
sql99标准【推荐】:支持内连接+外连接(左外+右外)+交叉连接 可读性高
按功能分类:
1.内连接:等值连接 非等值连接 自连接
2.外连接:左外连接 右外连接 全外连接
3.交叉连接
为表起别名:1.提高语句的简洁度2.区分多个重名的字段
注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
两个表的顺序可以交换
可以实现三表连接
一、sql92标准
1.等值连接
多表等值连接的结果为多表的交集部分
n表连接,至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点:1.一般为表起别名2.多表的顺序可以调换3.n表连接至少需要n-1个连接条件4.等值连接的结果是多表的交集部分
2.非等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3.自连接
语法:
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
二、sql99语法
语法:
select 查询列表
from 表1 别名
【连接类型】join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 分组后的筛选条件】
【order by 排序列表】
limit 子句
分类:
内连接:inner
【特点:1.添加排序、分组、筛选2.inner可以省略3.筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读4.inner join 连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
表的顺序可以调换
内连接的结果=多表的交集
n表连接至少需要n-1个连接条件】
外连接
【应用场景:用于查询一个表中有,另一个表没有的记录
特点:
1.外连接的查询结果为主表中的所有记录,
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2.左外连接,left join左边的是主表
右外连接,right join右边的是主表
3.左外和右外交换两个表的顺序,可以实现同样的发展
4.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
全外连接,两边都是主表
5.一般用于查询除了交集部分的剩余的不匹配的行】
左外:left[outer]
右外:right[outer]
全外:full[outer]
交叉连接:cross
特点:类似于笛卡尔乘积
语法:
select 查询列表
from 表1 别名
cross join 表2 别名;
子查询
含义:出现在其他语句内部的select语句,称为子查询或内查询
内部嵌套其他select语句的查询,称为外查询或主查询
外面的语句可以是insert update delete select等,一般select作为外面语句较多,外部的查询语句为select,则此语句称为主查询或外查询
分类:
按子查询出现的位置:
select后面
仅仅支持标量子查询
from后面
支持表子查询
where或having后面
标量子查询、(单行子查询)
列子查询、(多行子查询)
行子查询(多列多行)
exists后面(相关子查询)
标量子查询
列子查询
行子查询
表子查询
【特点:
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询,一般搭配着单行操作符使用
> < >= <= <>
列子查询,一般搭配着多行操作符使用
in any/some all
返回多行。】
操作符 | 含义 |
in/not in | 等于列表中的任意一个 |
any/some | 和子查询返回的某一个值比较(<max) |
all | 和子查询返回的所有值比较 |
4.子查询的执行优先于主查询的执行,主查询000的条件用到了子查询的结果】
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询/嵌套查询(结果集一般为多行多列)
where或having后面
返回公司工资最少的员工last_name,job_id,salary
1.查询公司的最低工资
select min(salary)
from employees
2.查询last_name,job_id和salary,要求salary=1
select last_name,job_id,salary
from employees
where salary=(
select min(salary)
from employees
);
返回location_id是1400或1700的部门中的所有员工姓名
1.查询location_id是1400或1700的部门编号
select distinct department_id
from departments
where location_id in(1400,1700)
2.查询员工姓名,要求部门号是1列表中的某一个
select last_name
from employees
where department_id in(
select distinct department_id
from departments
where location_id in(1400,1700)
);
返回其他工种中比job_id为‘it_prog'工种任一工资低的员工号、姓名、job_id以及salary
1.查询job_id为'it_prog'部门任一工资
select distinct salary
from employees
where job_id='it_prog'
2.查询员工号、姓名、job_id以及salary,salary<1的任一一个
select last_name,employee_id,job_id,salary
from employees
where salary<any(
select distinct salary
from employees
where job_id='it_prog'
)and job_id<>'it_prog';
或者:
select last_name,employee_id,job_id,salary
from employees
where salary<(
select max(salary)
from employees
where job_id='it_prog'
)and job_id<>'it_prog';
查询员工编号最小并且工资最高的员工信息
select *
from employees
where(employee_id,salary)=(
select min(employee_id),max(salary)
from employees
);
1.查询最小的员工编号
select min(employee_id)
from employees
2.查询最高工资
select max(salary)
from employees
3.查询员工信息
select *
from employees
where employee_id=(
select min(employee_id)
from employees
)and salary=(
select max(salary)
from employees
);
select 后面:仅仅支持标量子查询
查询每个部门的员工个数
select d.*,(
select count(*)
from employees
where e.department_id=d.department_id
)个数
from departments d;
查询员工号=102的部门名
select(
select department_name
from departments d
inner join employees e
on d.department_id=e.department_id
where e.employee_id=102
)部门名;
from 后面:将子查询结果充当一张表,要求必须起别名
查询每个部门的平均工资的工资等级
1.查询每个部门的平均工资
select avg(salary),department_id
from employees
group by department_id
select *
from job_grades;
2.连接1的结果集和job_grades表,筛选条件平均工资between lowest_sal and highest_sal
select ag_dep.*,'grade_level'
from(
select avg(salary) ag,department_id
from employees
group by department_id
) ag_dep
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal;
exists后面(相关子查询)
语法:
exists(完整的查询语句)
结果:1或0
查询有员工的部门名
select department_name
from departments d
where exists(
select *
from employees e
where d.department_id=e.department_id
);
select department_name
from departments d
where d.department_id in(
select department_id
from employees
)
分页查询
应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select 查询列表 7
from 表 1
【join type join 表2 2
on 连接条件 3
where 筛选条件 4
group by 分组字段 5
having 分组后的筛选 6
order by 排序的字段】 8
limit 【offset】,size; 9
offset要显示条目的起始索引(起始索引从0开始)
size要显示的条目个数
特点:
1.limit语句放在查询语句的最后
2.公式
要显示的页数 page,每页的条目数size
select 查询列表
from 表
limit [page-1]*size,size
联合查询
union:联合 合并 将多条查询语句的结果合并成一个结果
语法:
查询语句1
union【all】
查询语句2
union【all】
········
应用场景:要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
将一条比较复杂的查询语句拆分成多条语句
特点:
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好一致
3.union关键字默认去重,如果使用union all可以包含重复项
#查询部门编号>90或邮箱包含a的员工信息
SELECT *
FROM `employees`
WHERE `department_id`>90
OR `email`LIKE'%a%';
SELECT * FROM `employees` WHERE `department_id`>90
UNION
SELECT * FROM `employees` WHERE `email` LIKE '%a%'