三、DQL语言

一、基础查询

(一)语法

​ select 查询列表 from 表名;

(二)特点

  1. 查询列表可以是字段、常量、表达式、函数,也可以是多个
  2. 查询结果是虚拟的表

(三)示例

  • 查询单个字段

    • select 字段名 from 表名;
  • 查询多个字段

    • select 字段名, 字段名 from 表名;
  • 查询所有字段

    • select * from 表名;
  • 查询常量

    • select 100 + 90;
  • 查询函数

    • select 函数名();
  • 查询表达式

    • select 1000 / 102;
  • 起别名

    • as 关键字,比如:select user_id as "用户编号" from user_info;
    • 空格 比如:select user_id "用户编号" from user_info;
  • 去重

    • select distinct 字段名 from 表名;
  • "+"号的含义

    • 作用就是:做加法运算
      • select 数值+数值; 直接运算
      • select 字符+数值; 先试图将字符转换为数值,如果转换成功,则继续运算;否则转换成"0",再运算
      • select null + "任意值"; 结果都为 null
  • concat() 函数

    • select concat(字符1, 字符2, 字符3 )

二、条件查询

(一)语法

​ select 字段 from 表名 where 筛选条件;

​ 如果条件不成立,数据不会显示

(二)筛选条件的分类

  1. 简单条件的运算符

    1. >, <, =, <>, <=>, !=, <=, >=
  2. 逻辑运算符

    1. &&(and), ||(or), !(not)
  3. 模糊查询

    1. like

      1. 一般搭配通配符使用("%", "_"),可以判断字符型数值或者数值型
      2. %:任意多个字符
      3. _:任意单个字符
    2. between and,

    3. in, not in,

    4. is null, is not null

is null 和 <=> 区别

普通类型数值 null值 可读性
is null ×
<=> ×

三、排序查询

(一)语法

select 字段 from 表名 where 条件 order by 排序列表【asc/desc】(默认 asc排序)

(二)特点

  1. asc代表的是升序,desc代表的是降序,如果不写,默认是升序
  2. order by子句中可以支持单个字段、多个字段、表达式、函数、别名
  3. order by一般放在查询语句最后面,如果有 limit 的话,limit 在最后面

四、常见函数

(一)介绍

  1. 概念:类似于 编程语言 中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名

  2. 好处

    1. 隐藏了实现细节
    2. 提高了代码复用性
  3. 调用

    1. select 函数名(实参) from 表名;
    2. select 函数名();

(二)分类

  1. 单行函数

    1. 如:concat(), length(), is null 等等
  2. 分组函数

    1. 功能:做统计使用,又称为统计函数、聚合函数、组函数

五、单行函数

(一)字符函数

length()

  1. select length("hello")
  2. 获取参数值的字节个数

concat()

  1. 拼接字符
  2. select concat("a", "b", "c");

upper()、lower()

  1. 转为大写、小写

substr(索引从“1”开始)

  1. 切割字符

instr(返回字符第一次出现的索引)

trim(去前后空格)

  1. select trim('a' from 'aaaaaaaMaaaaYaaaSQLaaaa'); 结果为:MaaaaYaaaSQL

lpad(左填充指定长度的)

  1. select lpad('哈哈哈', 10, ''); 结果:哈哈哈

rpad(右填充指定长度的)

  1. select rpad('哈哈哈', 10, ''); 结果:哈哈哈

replace(替换字符)

  1. select replace('哈嘿嘻', '嘻', ''); 结果:哈嘿

(二)数学函数

round(四舍五入)

  1. select round(1.65); 结果:2
  2. select round(1.65547, 2); 结果:1.66

ceil(向上取整,返回 >= 该参数最小整数)

  1. select ceil(1.001); 结果:2
  2. select ceil(1.000); 结果:1

floor(向下取整,返回 <= 该参数最大整数)

  1. select floor(1.2154); 结果 1
  2. select floor(-9.99); 结果 -10

truncate(截断,小数点后保留几位)

  1. select truncate(1.65, 1); 结果:1.6

mod(取余,取余公式:a-a/b*b)

  1. select mod(10, 3); 结果:1

rand(随机数)

  1. select rand();

(三)日期函数

now(返回当前系统日期+时间)

  1. select now(); 结果:2019-08-21 23:19:52

curdate(返回当前日期,不包含时间)

  1. select curdate(); 结果:2019-08-21

curtime(返回当前时间,不包含日期)

  1. select curtime(); 结果:23:22:37

year、month(获取指定部分,年、月、日、时、分、秒)

  1. select year(now()); 结果:2019
  2. select month(now()); 结果:8

str_to_date(将字符通过指定的格式转换成日期)

  1. select str_to_date('2018-8-1', '%Y-%m-%d'); 结果:2018-08-01

date_format(将日期转换成字符)

  1. select date_format('2018/06/01', '%Y-%m-%d'); 结果:2018-06-01

datediff(日期查计算)

  1. select datediff('2019-08-23', '2019-08-10'); 结果:13
    在这里插入图片描述

(四)其他函数

version(查看数据库版本号)

  1. select version(); 结果:5.7.26

database(查看当前数据库)

  1. select database(); 结果:test

user(代表当前用户)

  1. select user(); 结果:root@localhost

(五)流程控制函数

if函数

  1. select if(10 > 5,'大','小'); 结果:大

case函数

  1. case 要判断的字段或表达式 when 常量值1 then 要显示的值或语句; when 常量2 then 要显示的值或语句; else 要显示的值或者语句;
  2. select (case a.id when 5 then '等于5' else '不等于5' end) as id from (select 10 as id) a
  3. select (case when a.id > 5 then '大于5' when a.id < 5 then '小于5' else '未知' end) as id from (select 10 as id) a

六、分组函数

(一)分类

  1. sum 求和
  2. avg 平均值
  3. max 最大值
  4. min 最小值
  5. count 计算个数

(二)简单使用

sum(求和)

  1. select sum(字段) from 表名;

avg(平均值)

  1. select avg (字段) from 表名;

max(最大值)

  1. select max(字段) from 表名;

min(最小值)

  1. select min(字段) from 表名;

count(计算个数)

  1. select count(字段) from 表名;

  2. select count(1) from 表名; 意思是:在查询时候 新建了一个虚拟表,对应实际表的行数,实际上统计的是 1 的行数。

  3. 效率:

    1. MyISAM存储引擎下:count(*) 比 count(1) 效率高,因为这个引擎内部有计数器,直接返回了个数。
    2. InnoDB存储引擎下:count(*) 和 count(1) 差不多,比 count('字段') 效率高一些

和 distinct 关键字搭配使用

  1. select sum(distinct('字段')), sum('字段') from 表名;

注意

和分组函数一同查询的字段要求是 group by 后的字段。

七、分组查询

数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having

group by (分组函数)

  1. select count(*), user_id from user_info group by user_id; 查询每个user_id的个数

having(查询结果后的筛选函数)

  1. select count(*) as cou, user_id as userId from user_info group by userId having cou>2;

注意

  1. 分组函数做条件肯定是放在 having 字句中
  2. 能用分组前筛选的,就优先考虑使用分组前筛选
  3. group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求)
  4. 也可以添加排序(排序放在整组语句的最后)

八、连接查询

含义

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

错误示例

笛卡尔乘积的错误情况

select count(*) from a; 假设输出12行

select count(*) from b; 假设输出4行

最终结果:12×4=48(行)

查询分类

按年代分类

  1. sql 92标准:MySQL中仅支持内连接
  2. sql 99标准【推荐使用】:MySQL中支持内连接、外连接(左外、右外)、交叉间接

按功能分类

  1. 内连接

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

    1. 左外连接
    2. 右外连接
    3. 全外连接
  3. 交叉连接

sql92标准

内连接>等值连接

简单示例

select a.id,b.age from user_info a,user_ageb where a.id= b.user_id

加筛选条件示例

select a.id,b.age from user_info a,user_ageb where a.id= b.user_id and b.age > 18;

总结

  1. 多表等值连接的结果为多表的交集部分
  2. n表连接,至少需要n - 1个连接条件
  3. 多表的顺序没有要求
  4. 一般需要为表加别名,便于操作
  5. 可以搭配子句使用,比如:排序、分组等待
内连接>非等值连接

简单示例

job_grade:员工工资等级表,每个等级都用工资范围值

employess:员工表

select e.salary,g.grade_level from employess e, job_grade g

where e.salary between g.lowest_sal and g.highest_sal

内连接>自连接

简单示例

查询员工名和上级名称

select e.employee_id, e.last_name,a.employee_id,a.last_name

from employees e, employees a where e.manager_id = a.employee_id

sql99标准

简单示例

select 查询列表

from 表1 别名【连接类型】join 表2 别名 on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】

内连接:inner

外连接:

左外:left 【outer】

右外:right【outer】

全外:full【outer】

交叉连接:cross

内连接

语法

select * from user_info u inner join user_like ui on u.user_id = ui.user_id;

特点

  1. 添加排序、分组、筛选
  2. inner join 可以省略为 join
  3. 筛选条件放在 where 后面,连接条件放在 on 后面,提高分离性、可读性
  4. inner jion 和 sql92 的等值连接效果一样,查询多表的交集
等值连接

select * from user_info u inner join user_like ui on u.user_id = ui.user_id;

非等值连接

job_grade:员工工资等级表,每个等级都用工资范围值

employess:员工表

select e.salary,g.grade_level from employess e inner join job_grade g

one.salary between g.lowest_sal and g.highest_sal

自连接

查询员工名和上级名称

select e.employee_id, e.last_name,a.employee_id,a.last_name

from employees e inner join employees a on e.manager_id = a.employee_id;

外连接

特点

  1. 外连接的查询结果为主表中的所有记录,如果从表中有和它匹配的,显示匹配值,如果没有匹配的,则显示 null,外连接查询结果=内连接结果+主表中有而从表没有的记录
  2. 左外连接,left 左边是主表,右外连接,right join右边的是主表
  3. 左外和右外交换两表的顺序,查询结果没有区别
全外连接(MySQL暂不支持)

特点

  1. 全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有单表1中没有的

示例

select b.,bo. from beauty b full join boys bo on b.boyfriend_id = bo.id;

交叉连接

select a.,b. from A a cross join B b; 结果为:笛卡尔乘积

sql92和sql99的区别

功能上

  1. sql99支持的较多

可读性

  1. sql99实现连接条件和筛选条件的分离,可读性较高

示例

inner join 图示:
在这里插入图片描述
left join图示:
在这里插入图片描述
right join图示:
在这里插入图片描述
左、右连接过滤字段为空行
在这里插入图片描述
全连接和交叉连接
在这里插入图片描述

子查询

含义

出现在其它语句中的 select 查询,成为子查询或内查询,外部的查询语句,

成为主查询或外查询

简单示例

select * from user_info where user_id in (select user_id from user_info);

分类

按子查询出现的位置:

select 后面:仅支持标量子查询

from 后面:支持表子查询

where 或 having后面:主要支持标量子查询、列子查询,也支持行子查询

exists后面(相关子查询):表子查询

按结果集行列数不同:

标量子查询(结果集只有一行一列)

列子查询(结果集只有一列多行)

行子查询(结果集有一行多列)

表子查询(结果集一般为多行多列)

where或having后面

  1. 标量子查询(单行子查询)
  2. 列子查询(多行子查询)
  3. 行子查询(多列多行)

特点:

  1. 子查询放在小括号内
  2. 子查询一般放在条件的右侧
  3. 标量子查询,一般搭配着单行操作符使用;列子查询,一般搭配着多行操作符使用
标量子查询

select * from user where id = (select id from user where id = 12);

列子查询(多行子查询)

特点

  1. 返回多行
  2. 使用多行比较操作符:如图
    在这里插入图片描述
行子查询(结果集一行多列或多行多列)

select * from user where (user_id,user_name) = (

select user_id,user_name from user

where user_id = 10 and user_name like '%张%';

);

select 后面

select o.,(select count() from user a where a.id = o.user_id) from user_order o;

from 后面

简介:将子查询的结果充当一张临时表,要求必须起别名

示例:

select u.*,ag.grade_level from (select avg(age) as age from user) user_age

inner join age_grades ag

on user_age.age between lowest_age and highest_age;

exists后面(相关子查询)

示例:

select exists(select * from user;) 结果:1

select exists(select * from user where id = 10000000000;) 结果:0

查询:

使用in:

select bo* from boys bo where bo.id not in (select boy_id from beauty);

使用 exists:

select bo* from boys bo where

not exists (select b.boy_id from beauty b where bo.id = b.boy_id));

分页查询

应用场景:

  1. 需要显示的数据,一页显示不全,需要分页提交sql查询
  2. 海量数据,使用分页提高性能

特点:

  1. limit 语句放在查询语句的最后

  2. 公式:

    1. page:页数,size:条目数
    2. page = (page - 1) * size

执行顺序
[外链图片转存失败(img-x8gkErmO-1567441245242)(D:/Software/YoudaoNote/local_file/yliulastresort@163.com/a299ad0adde040d8ade1778efbdbdec5/clipboard.png)]

联合查询

语句

  1. union:联合、合并:将多条查询语句中的结果合并成一个结果
  2. union all:同上

应用场景

要查询的结果来自于多个表,且多个表没有直接的连接关系,单查询信息一致时

特点

  1. 要求多条查询语句的查询列数是一致的
  2. 要求多条查询语句的查询的每一列的类型和顺序最好一致
  3. 使用 union 关键字,默认去重的,如不想去重,应该使用 union all

示例

select * from user where id = 1

union

select * from user where user_name like '%张三%';

完整查询顺序

语法

select 查询列名 -------------------------- ⑥

from 表1 别名 ---------------------------- ①

连接类型 join 表2 别名 on 连接条件 - ②

where 筛选条件 ------------------------- ③

group by 分组列表 ---------------------- ④

having 筛选 ------------------------------ ⑤

order by 排序列表 ---------------------- ⑦

limit 起始条目索引, 条目数; ----------- ⑧

posted @ 2019-09-03 00:56  yliu.best  阅读(141)  评论(0编辑  收藏