数据库学习记录02

DQL【数据查询语言】

1.基础查询

1.1语法

select * | {[DISTINCT] column | expression[alias], ...} from table;

特点

查询列表可以是表中的字段、常量值、表达式、函数。

查询的结果是一个虚拟的表格。

#1.查询表中的单个字段
select name from employees;

#2.查询表中的多个字段
select name, salary, gender from employee;

#3.查询表中的所有字段，能查字段尽量别用
select * from employees;

#4.查询常量值
select 100;
select 'join';

#5.查询表达式
select 100%98;

#6.查询函数
select version();

#7.起别名，如果要查询的字段有重名额情况，使用别名可以区分开来
#方式一：使用as
select 100%98 as 结果;
select name as 姓名, salary as 薪资 from employees;

#方式二：使用空格
select name 姓名, salary 薪资 from employees;

#8.DISTINCT 去重
select distinct department_id from employees;

1.2 +号的作用

#两个操作数都为数值型，则做加法运算
select 100+90;
#只要其中一方为字符型，则试图将字符型数值转换成数值型。如果转换成功，则继续做加法运算
select '123' + 90
#如果转换失败，则将字符型数值转换成0
select 'john'+90
#只要其中一方为null,则结果肯定为null
select null+10;

1.3 CONCAT、ISNULL、IFNULL

concat：合并两个字符串

select concat('a','b','c') as 结果;
#结果为：abc

ISNULL: 判断某字段或表达式是否为null，是返回1，否则返回0

select isnull('a') as 结果;
# 结果是： 0

IFNULL：如果第一个参数的表达式 expression 为 null，则返回第二个参数的备用值，可嵌套使用

select ifnull(commission , 0 ) as 奖金率, commission from employees;

2.条件查询

语法

select 查询列表 from 表名 where 筛选条件;

• 根据条件表达式筛选

        用于筛选数据

• 按逻辑表达式筛选

        用于连接条件表达式

范围查询

        不确定条件进行范围查询，或者模糊查询

#按条件表达式筛选
/*
    一般和通配符搭配使用
    通配符：
    % 任意多个字符，包含0个字符
    _任意单个字符
*/

#案例1：查询员工名中包含字符a的员工信息
select * from employees where last_name like '%a%'; #abc

#案例2：查询员工名中第三个字符为e,第五个字符为a的员工名和工资
select last_name,salary from employees where last_name LIKE '__e_a%';

#案例3:查询员工名中第二个字符为_的员工名
select last_name from employees where last_name like '_$_%' escape '$'; #指定$为转移字符
#where last_name like '_\_%';

#2.between and 
/* 使用between and 可以提高语句的简洁度，包含临界值，
    两个临界值不要调换顺序
*/
#案例： 查询员工编号在100到120之间的员工信息
select * from employees where employee_id >=100 and employee_id <=120
select * from employees where employee_id between 100 and 120;

#3.in
/*含义：判断某字段的值是否属于in列表中的某一项*/
特点：使用in提高语句简洁度，in列表的值类型必须一致或兼容，in列表中不支持通配符
select last_name,job_id from employees where job_id = 'IT_PROT' or job_id = 'AD_VP' or job_id = 'AD_PRES';
select last_name, job_id from employees where job_id in ('IT_PROT','AD_VP','AD_PRES');

#4.is null
# =或<>不能用于判断null值 is null 或 is not null 可以判断null值

#案例1：查询没有奖金的员工名和奖金率
select last_name, commission_pct from employees where commission_pct is null;

#案例2: 查询有奖金的员工名和奖金率
select last_name, commission_pct from employees where commission_pct is not null;

#5.<=>
#案例1：查询没有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct <=> null;

#案例2:查询工资为12000的员工信息
select last_name,salary from employees where salary <=> 12000;

3.排序查询

语法

select 要查询的东西 from 表 where 条件 order by 排序的字段|表达式|函数|别名 [asc|desc];

• asc代表的是升序，为默认值，可以省略，desc代表的是降序
• order by 字句可以支持单个字段、别名、表达式、函数、多个字段
• order by字句在查询语句的最后面，除了limit字句

案例 

#1.按单个字段排序
select * from employees order by salary desc;

#2.添加筛选条件再排序
#案例：查询部门编号>=90的员工信息，并按员工编号降序
select * from employees where departemnt_id >=90 order by employee_id desc;

#3.按表达式排序
#案例：查询员工信息 按年薪降序
select *, salary*12*(1+ifnull(commission_pct,0)) from employees
order by salary*12*(1+ifnull(commission_pct,0)) desc;

#4.按别名排序
#案例：查询员工信息，按年薪升序
select *, salary*12*(1+ifnull(commision_pct,0)) 年薪 from employees order by 年薪;

#5.按函数排序
#案例：查询员工名，并且按名字的长度降序
select length(last_name), last_name from employees order by length(last_name) desc;

#6.按多个字段排序
#案例：查询员工信息，要求先按照工资降序，再按employee_id升序
select * from employees order by salary desc, employee_id asc;

#7.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
select last_name, departemnt_id, salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees
order by 年薪 desc, last_name asc;

#8.选择工资不在8000到17000的员工的姓名和工资，按工资排序
select last_name, salary from employees where salary not bewteen 8000 and 17000 order by salary desc;

#9.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
select *, length(email) from employees where email like '%e%' order by length(email) desc, department_id asc;

4.内置函数

类似于java中的方法，将一组逻辑语句封装再方法体中，对外暴露方法名

优点：隐藏了实现细节，提高了代码的重用性

调用

select 函数名（实参列表） [from 表];

分类

• 单行函数

        如concat、length、ifnull等

• 分组函数

        功能：做统计使用，又称为统计函数、聚合函数、组函数

 单行函数

字符函数

数学函数

日期函数

其他函数

流程控制函数

都是处理分支情况

分组函数

• sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型
• 以上五个分组函数都忽略null值，都可以搭配distinct使用，用于统计去重后的结果
• count的参数都可以支持：字段、*、常量值（一般放1），建议使用count(*)
• 和分组函数一同查询多的字段要求是group by后的字段

 分组查询

语法

select 分组函数 groupby后的字段 from 表 [where 筛选条件]
group by 分组的字段
[having 筛选条件] [order by 排序的字段];

特点

• 和分组函数一同查询的字段最好是分组后的字段，或聚合函数
• 分组查询筛选分为两类

• 分组函数做条件放在having字句中
• 能用分组前筛选的，就优先考虑分组前筛选，处于性能考虑。

•  支持单个字段分组，也支持多个字段分组，字段之间用逗号隔开，没有顺序要求，也支持函数和表达式
• 可以添加排序，放在分组查询的最后
• group by 和 having 后可以支持别名，但不能用，因为Oracle等不支持。

#1.案例1：查询每个工种的员工平均工资
select avg(salary), job_id from employees group by job_id;

#2.案例2：查询邮箱中包含a字符的 每个部门的最高工资
select max(salary), department_id from employees where email like '%a%'
group by department_id;

#3.案例：查询哪个部门的员工个数>5
select count(*),department_id from employees group by department_id having count(*) > 5;

多表连接查询

含义及现象

含义

又称多表查询， 当查询的字段来自于多个表时，就会用到连接查询。

•  笛卡尔积乘积现象：表1 有m行，表2有n行，结果= m*n行。原因是因为没有有效的连接条件，需要添加有效的连接分类条件。
• 为表取别名：提高语句的简洁度，区分多个重名的字段，需要注意的是：如果表起了别名，则查询的字段就不能使用原来的表名去限定。

分类

内连接

关键字：inner join

1.等值连接 2.非等值连接 3.自连接

外连接

关键字：left join、right join

1.左外连接 2.右外连接 3.全外连接（MySQL不支持）

交叉连接

关键字：cross join

用来返回连接表的笛卡尔积

语法

inner join 内连接时，inner 可以省略，默认join就是指内连接

select 字段, ... from 表1
[inner | left outer | right outer | cross] join 表2 on 连接条件
[inner | left outer | right outer | cross] join 表3 on 连接条件
[where 筛选条件]
[group by 分组字段]
[having 分组后的筛选字段]
[order by 排序的字段后表达式]

子查询

含义：

出现在其他语句中的select语句，称为子查询或 内查询

子查询出现的位置 

select 后面 仅仅支持标量子查询 from 后面 支持表子查询
where 或 having后面 标量子查询（单行） 列子查询（单列多行） 行子查询 表子查询 （多列）
exists 后面 （相关子查询）表子查询

特点

• 子查询放在小括号内
• 子查询一般放在条件的右侧
• 标量子查询，一般搭配着单行操作符使用

• >、<、>=、<=、=、<>
• in、any/some、all
• 列子查询，一般搭配着多行操作符使用

非相关子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

 例如：

#in后面（）里的就是子查询
select A.key from A where A.key in (select B.key from B);

分页查询

语法

select 查询列表 from 表 join 表2 on 连接条件 where 筛选条件
group by 分组条件 having 分组后的 筛选 order by 排序的字段
limit [offset,] size;

• offset表示要显示条目的起始索引（起始索引从0开始）
• size表示要显示的条目个数

特点 

起始条目索引从0开始

limit字句放在查询语句的最后

公式： select * from 表 limit (page-1)*pageSize, pageSize

• pageSize: 每页显示条目数
• page:要显示的页数

 例子：

#案例1：查询前五条员工信息
select * from employees limit 0, 5;
select * from employees limit 5;

#案例2：查询第11条-第25条
select * from employees limit 10,15;

#案例3：查询带有奖金的员工信息，并且显示工资较高的前10名
select * from employees where commission_pct is not null 
order by salary desc limit 10;

联合查询

语法

查询语句1 union 查询语句 2 union ... 查询语句 n

特点

• 要求多条查询语句的查询列数是一致的
• 要求多条查询语句的查询的每一列的类型和顺序最好一致
• union关键字默认去重，如果使用union all 可以包含重复项

#案例1：查询部门编号>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;

#案例2：查询中国用户男性的信息和外国用户中年男性的用户信息
select id, cname from t_ca where csex = '男'
union all
select t_id, tname from t_ua where tGender = 'male';

UNION和UNION ALL的区别

区别1：取结果的并集

• union： 对两个结果集进行并集操作，不包括重复行，相当于distinct，同时进行默认规则排序，
• union all: 对两个结果集进行并集操作，包括重复行，即所有的结果全部显示不管是不是重复。

区别2：获取结果后的操作

• union:会对获取的结果进行排序操作；
• union all: 不会对获取的结果进行排序；

区别3：

• 相同查询SQL条件下，union all的执行效率要比union高。