DQL数据库查询语言
2020 年 09 月 27 日 1279 2114 字 暂无评论

01.基础查询

1.1 语法

 select 查询列表  from 表名;  

1.2 特点

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

1.3 示例

  1. 查询单个字段

    1. select 字段名 from 表名;
  2. 查询多个字段

    1. select 字段名,字段名 from 表名;
  3. 查询所有字段

    1. select * from 表名
  4. 查询常量

    1. select 常量值;
    2. 注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
  5. 查询函数

    1. select 函数名(实参列表);
  6. 查询表达式

    1. select 100/1234;
  7. 起别名

    1. as
    2. 空格
  8. 去重

    1. select distinct 字段名 from 表名;
  9. +

    1. 作用:做加法运算
    2. select 数值+数值; 直接运算
    3. select 字符+数值;先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算
    4. select null+值;结果都为null
  10. 【补充】concat函数

    1. 功能:拼接字符
    2. select concat(字符1,字符2,字符3,...);
  11. 【补充】ifnull函数

    1. 功能:判断某字段或表达式是否为null,如果为null返回指定的值,否则返回原本的值
    2. select ifnull(commission_pct,0) from employees;
  12. 【补充】isnull函数

    1. 功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0

02.条件查询

2.1 语法

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

2.2 筛选条件的分类

  1. 简单条件运算符

    1. > < = <> != >= <= <=>安全等于
  2. 逻辑运算符

    1. && and
    2. || or
    3. ! not
  3. 模糊查询

    1. like:一般搭配通配符使用,可以判断字符型或数值型
    2. 通配符:%任意多个字符,_任意单个字符
    3. between and
    4. in
    5. is null /is not null:用于判断null值
  4. is null PK <=>
​             普通类型的数值              null值            可读性

is null        ×                         √                  √

<=>            √                         √                  ×

03.排序查询

3.1 语法

select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc

3.2 特点

  1. asc :升序,如果不写默认升序
  2. desc:降序
  3. 排序列表 支持 单个字段、多个字段、函数、表达式、别名
  4. order by的位置一般放在查询语句的最后(除limit语句之外)

04.常见函数

4.1 概述

  • 功能:类似于方法函数
  • 好处:提高重用性和隐藏实现细节
  • 调用:select 函数名(实参列表);

4.2 单行函数

4.2.1 字符函数

  • concat:连接

    • select concat(字符1,字符2,字符3,...);
  • substr:截取子串

    • select substr(字符,5);#从第5个字符开始截取,取后面部分。
    • select substr(字符,1,5); #从第1个字符开始截取,取5个字符。
  • upper:变大写

    • select upper(字符);
  • lower:变小写

    • select lower(字符);
  • replace:替换

    • select replace('zfhll','zfh','lll')#输出lllll。
  • length:获取字节长度

    • select length(字符);
  • trim:去前后空格

    • select trim(' 1111 ');#结果1111。
  • lpad:左填充

    • select lpad('zdsfd',10,'1');#结果11111zdsfd。
  • rpad:右填充

    • select rpad('zdsfd',10,'1');#结果zdsfd11111。
  • instr:获取子串第一次出现的索引

    • select instr('zxcvb','x');#结果2。

4.2.2 数学函数

  • ceil:向上取整

    • select ceil(1.23); #输出2。
  • round:四舍五入

    • select round(1.23);#输出1。
  • mod:取模

    • select mod(2,3);#输出2。
  • floor:向下取整

    • select floor(1.23);#输出1。
  • truncate:截断(取几位小数)

    • select truncate(1.2323,2);#输出1.23。
  • rand:获取随机数,返回0-1之间的小数

    • select rand();

4.2.3 日期函数

  • now:返回当前日期+时间

    • select now();
  • year:返回年

    • select year(now());
  • month:返回月

    • select month(now());
  • day:返回日

    • select day(now());
  • date_format:将日期转换成字符

    • select date_format(now(),'%Y年%c月%d日');
  • curdate:返回当前日期

    • select curdate();
  • str_to_date:将字符转换成日期

    • select str_to_date('1997-2-9','%Y-%c-%d');
  • curtime:返回当前时间

    • select curtime();
  • hour:小时

    • select hour(now());
  • minute:分钟

    • select minute(now());
  • second:秒

    • select second(now());
  • datediff:返回两个日期相差的天数

    • SELECT DATEDIFF('2020-01-01', '2019-01-01') AS Result;
  • monthname:以英文形式返回月

    • select monthname(now());

4.2.4 流程控制函数

  • if(条件表达式,表达式1,表达式2):

    • 如果条件表达式成立,返回表达式1,否则返回表达式2
  • case情况1

    • case 变量或表达式或字段
    • when 常量1 then 值1
    • when 常量2 then 值2
    • ...
    • else 值n
    • end
  • case情况2

    • case
    • when 条件1 then 值1
    • when 条件2 then 值2
    • ...
    • else 值n
    • end

4.2.5 其他函数

  • version 当前数据库服务器的版本

    • select version();
  • database 当前打开的数据库

    • select database();
  • user当前用户

    • select user();
  • password('字符'):返回该字符的密码形式

    • select password('ssdfsdf');
  • md5('字符'):返回该字符的md5加密形式

    • select md5('3223');

4.3 分组函数

4.3.1 分类

  • max 最大值
  • min 最小值
  • sum 和
  • avg 平均值
  • count 计算个数

4.3.2 特点

  • 语法

    • select max(字段) from 表名;
  • 支持的类型

    • sum和avg一般用于处理数值型
    • max、min、count可以处理任何数据类型
  • 以上分组函数都忽略null
  • 都可以搭配distinct使用,实现去重的统计

    • select sum(distinct 字段) from 表;
  • count函数

    • count(字段):统计该字段非空值的个数
    • count(*):统计结果集的行数
    • count(1):统计结果集的行数
  • 效率上:

    • MyISAM存储引擎,count(*)最高
    • InnoDB存储引擎,count(*)和count(1)效率>count(字段)
  • 和分组函数一同查询的字段,要求是group by后出现的字段

05.分组查询

5.1 语法

select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

5.2 特点


​                    使用关键字      筛选的表          位置

分组前筛选      where       原始表        group by的前面

分组后筛选      having    分组后的结果    group by 的后面

06.连接查询

6.1 含义

当查询中涉及到了多个表的字段,需要使用多表连接

select 字段1,字段2
from 表1,表2,...;

笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接

如何解决:添加有效的连接条件

6.2 分类

  • 按年代分类:
  • sql92:

    • 等值
    • 非等值
    • 自连接
    • 也支持一部分外连接(用于oracle、sqlserver,mysql不支持)
  • sql99【推荐使用】

    • 内连接

      • 等值
      • 非等值
      • 自连接
      • 外连接

        • 左外
        • 右外
        • 全外(mysql不支持)
      • 交叉连接

6.3 SQL92语法

6.3.1 等值连接

语法:

select 查询列表
    from 表1 别名,表2 别名
    where 表1.key=表2.key
    【and 筛选条件】
    【group by 分组字段】
    【having 分组后的筛选】
    【order by 排序字段】

特点:

  • 一般为表起别名
  • 多表的顺序可以调换
  • n表连接至少需要n-1个连接条件
  • 等值连接的结果是多表的交集部分

6.3.2 非等值连接

语法:

select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

6.3.3 自连接

语法:

select 查询列表
    from 表 别名1,表 别名2
    where 等值的连接条件
    【and 筛选条件】
    【group by 分组字段】
    【having 分组后的筛选】
【order by 排序字段】

6.4 SQL99语法

6.4.1 内连接

语法:

select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

特点:

①表的顺序可以调换

②内连接的结果=多表的交集

③n表连接至少需要n-1个连接条件

分类:

等值连接

非等值连接

自连接

6.4.2 外连接

语法:

select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

特点:

①查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null

②left join 左边的就是主表,right join 右边的就是主表

full join 两边都是主表

③一般用于查询除了交集部分的剩余的不匹配的行

6.4.3 交叉连接

语法:

select 查询列表
from 表1 别名
cross join 表2 别名;

特点:

  • 类似于笛卡尔乘积

07.子查询

7.1含义

  • 嵌套在其他语句内部的select语句称为子查询或内查询,
  • 外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多
  • 外面如果为select语句,则此语句称为外查询或主查询

7.2分类

  1. 按出现位置
select后面:
    仅仅支持标量子查询
from后面:
    表子查询
where或having后面:
    标量子查询
    列子查询
    行子查询
exists后面:
    标量子查询
    列子查询
    行子查询
表子查询
  1. 按结果集的行列
标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集为多行多列
表子查询:结果集为多行多列

7.3示例

where或having后面

  1. 标量子查询

案例:查询最低工资的员工姓名和工资

①最低工资

select min(salary) from employees

②查询员工的姓名和工资,要求工资=①

select last_name,salary
from employees
where salary=(
    select min(salary) from employees
);
  1. 列子查询

案例:查询所有是领导的员工姓名

①查询所有员工的 manager_id

select manager_id
from employees;

②查询姓名,employee_id属于①列表的一个

select last_name
from employees
where employee_id in(
    select manager_id
    from employees
);

08.分页查询

8.1应用场景

  • 当要查询的条目数太多,一页显示不全

8.2语法

select 查询列表
from 表
limit 【offset,】size;
注意:
offset代表的是起始的条目索引,默认从0卡死
size代表的是显示的条目数

公式:

  • 假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;

09.联合查询

9.1含义

  • union:合并、联合,将多次查询结果合并成一个结果

9.2语法

查询语句1
union 【all】
查询语句2
union 【all】
...

9.3意义

  1. 将一条比较复杂的查询语句拆分成多条语句
  2. 适用于查询多个表的时候,查询的列基本是一致

9.4特点

  1. 要求多条查询语句的查询列数必须一致
  2. 要求多条查询语句的查询的各列类型、顺序最好一致
  3. union 去重,union all包含重复项

10.查询语法总结

语法:

select 查询列表    ⑦
from 表1 别名       ①
连接类型 join 表2   ②
on 连接条件         ③
where 筛选          ④
group by 分组列表   ⑤
having 筛选         ⑥
order by排序列表    ⑧
limit 起始条目索引,条目数;  ⑨

版权属于:zfh

本文链接:http://zfhblog.com/index.php/archives/157/



评论已关闭