三、数据库之DQL（数据查询语言）

一、基础查询

（一）语法

SELECT 查询字段
FROM 表名;

（二）特点

  1. 查询列表可以是：字段、常量、表达式、函数（都可以是单个或者多个）
  2. 查询结果是一个虚拟表

（三）实例

  1. 查询单个字段

    SELECT email
FROM employees;
    • 英文分号结尾

  2. 查询多个字段(英文逗号隔开)

    SELECT email,job_id,last_name
FROM employees;
    • 英文逗号隔开字段

  3. 查询所有字段

    SELECT *
FROM employees;

    • 操作技巧：可以通过双击Navicat右侧的字段来实现查询（如下）

    • SELECT `employees`.`email`,`employees`.`first_name`
FROM `employees`;

  4. 查询常量值（数值型、字符型、日期型）（暂时不知道有何用？？？）

  5. 查询表达式

    SELECT 100%3;
FROM employees;

  6. 查询函数

    SELECT 函数名（实参列表）

SELECT version();
FROM employees;

  7. 起别名：在字段后面+AS或空格+别名

  8. 去重：DISTINCT关键字

    SELECT DISTINCT `employees`.`department_id`
FROM `employees`;

  9. +：mysql中“+”只有一个作用就是做加法运算

    • select 数值+数值：直接运算
    • select 字符+数值：先试图将字符转换为数值，成功则继续运算，失败就把字符转换为0再运算
    • select null+值：结果都是null

  10. 补充

    1. CONCAT函数：拼接字符

      CONCAT(str1,str2,...)

    2. IFNULL函数:判断字段或表达式(expr1)是否是null，是null返回指定值(expr2)，不是null返回原本的值

      IFNULL(expr1,expr2)

    3. ISNULL函数:判断字段或表达式是否为null，是null返回1，不是null返回0

二、条件查询

(一)、语法

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

(二)、筛选条件的分类

1、简单条件运算符
 < 
 >
 = 
 <>   !=  
 >=
 <=  
 <=>安全等于
2、逻辑运算符
&& and
|| or
!  not
3、模糊查询

  • like：查询类似的值，一般搭配通配符使用，可以判断字符型或数值型

    通配符：

    • %任意多个字符

    • _任意单个字符

    • 和关键字重复时，可以用\来转义，也可以自定义转义

      where lastname LIKE ‘_$_’ESCAPE '$';

    eg：查询姓名中第二个字母是o的员工姓名

    SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';

  • between and：用于显示在某个范围内的值，包含边界

    eg：查询工资在2500到3500之间的员工姓名

    SELECT last_name
FROM employees
WHERE salary IN(2500,3500);

    sql

  • in：查询某个字段是某个/几个确定值的

    • 好处：可以代替OR，简化代码
    • eg：查询部门号为100，101，102的员工姓名和部门号
    SELECT manager_id,last_name
FROM employees
WHERE manager_id IN(100,101,102);

  • is null /is not null：用于判断null值

    eg：查询没有分配部门的员工姓名

    SELECT last_name
FROM employees
WHERE manager_id IS NULL;

(三)、总结

1、is null和<=>安全等与的区别（待补充）

三、排序查询

（一）、语法

select 查询列表
from 表名
where 筛选条件
order by 排序列表【asc或desc】

eg：按照入职日期升序查询100号部门的员工的姓名和入职日期

SELECT last_name,hiredate
FROM employees
WHERE manager_id=100
ORDER BY hiredate
  • asc（ascend）：升序（可省略，默认升序）
  • desc（descend）：降序

（二）、特点

  • 可以查询多个字段，每个字段后面加asc或desc（查询结果是在第一个字段顺序的基础上为第二个字段排序）
  • 可以按别名查询
  • order by 放在select语句的结尾

四、常见函数

（一）、概述

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

(二)、单行函数

（本来觉得要记得详细一些，后来发现，详细的用法在开发中都会提示出来，所以只需要记住这个有函数，用的时候可以记起来就ok啦）

1、字符函数
函数作用注意
CONCAT(str1,str2,…)连接
SUBSTR(str FROM pos FOR len)截取子串下标从1开始
UPPER(str)、LOWER(str)变大写、小写
LPAD(str,len,padstr)、RPAD(str,len,padstr)左填充、右填充
REPLACE(str,from_str,to_str)替换
LENGTH(str)获取字节数
TRIM([remstr FROM] str)去前后空格
INSTR(str,substr)获取子串第一次出现的索引
2、数学函数
函数作用注意
CEIL(X)、FLOOR(X)向上、下取整
MOD(N,M)取模
ROUND(X)、ROUND(X,D)四舍五入
RAND()0~1直接的随机数
TRUNCATE(X,D)截断
3、日期函数
函数作用注意
now（）返回日期+时间
YEAR(date)、MONTH(date)、DAY(date)返回年、月、日date可以用now（）获取
date_format、str_to_date日期转字符、字符转日期
datediff日期天数差
curdate返回当前日期
curtime返回当前时间
hour、minute、second返回时、分、秒
monthname返回英文月份
4、其他函数
函数作用注意
version服务器版本
database当前打开的数据库
user当前用户
password返回字符的密码形式
md5返回字符的md5加密形式
5、流程控制函数

①、if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2

IF(expr1,expr2,expr3)

②、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

(三)、分组函数

0、说明

​ 分组函数作用于一组数据，然后返回一个值

1、分类
函数作用注意
min、max最小、大值
sum、avg和、平均值
count计算个数
2、特点
  • 语法
select max(字段)
from 表名

  • 支持的类型
    sum和avg一般用于处理数值型
    max、min、count可以处理任何数据类型

  • 以上分组函数都忽略null

  • 都可以搭配distinct使用，实现去重的统计

    select sum(distinct dep_id)
from employees;

  • count函数

    • count(字段)：统计该字段非空值的个数
    • count(*):统计结果集的行数 (涉及到个数，用count（*）)
    • count(1):统计结果集的行数（相当于给原表加了一列1，当然1可以换做任何常量值）

    效率上：

    以前：MyISAM存储引擎，count(*)最高
    现在：InnoDB存储引擎，count(*)和count(1)效率>count(字段)

  • 和分组函数一同查询的字段，要求是group by后出现的字段（因为查询结果要求是一个规则的表格，如果查询普通字段，表格不是矩形，没意义，如下查询是没意义的）

    select avg（salary），job_id
from employees；

五、分组查询

一、语法

select 分组函数，列（分组的条件，对应“按照每个”等字眼）（要求出现在group by后面） 
from 表
【where 筛选条件】
group by 分组的字段（多个字段没有顺序）
【having 分组后的筛选】（在where筛选条件得到的结果上再次筛选）
【order by 排序列表】 (order by在最后)

  • eg：查询每个工种的最高工资

    select max(salary),job_id
from employees
group by job_id

  • 如eg：查询部门员工数大于2的部门

    select count(*),department_id
from employees
group by department_id
#到这里我查询到的结果是每个部门员工个数
having count（*）>2；

  • eg：

    #eg:查询每个工种有奖金的员工的最高工资   大于12000的员工编号和最高工资

#第一步：查询每个工种有奖金的员工的最高工资

#1-1 先把框架写出来
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id

#1-2 在根据条件写where和having，因为“有奖金”在employee表中就有，所以用紧跟from的where
SELECT MAX(salary),job_id
FROM employees
WHERE `employees`.`commission_pct` is not null
GROUP BY job_id

#第二步：大于12000的员工编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE `employees`.`commission_pct` is not null
GROUP BY job_id
HAVING MAX(salary)>12000

二、特点

关键字筛选的表位置
分组前筛选where原始表group by前
分组后筛选having分组后的中间表group by后
  • 补充
    • 分组函数做条件，一定是放在having中
    • 能用分组前，用分组前，提高性能
    • 可以按多个字段分组

六、连接查询

(一)、含义·

  1. 当查询中涉及到了多个表的字段，需要使用多表连接
    select 字段1，字段2
    from 表1，表2,…;

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

  3. 如何解决：添加有效的连接条件

(二)、分类

  1. 按年代分类

  • sql92

  • sql99（推荐使用）

  1. 按照功能分类
  • 内连接
    • 等值连接
    • 非等值
    • 自连接
  • 外连接
    • 左外
    • 右外
    • 全外
  • 交叉连接

(三)、SQL92语法

1. 等值连接

  • 语法：

    select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】(多表连接and后面可以是表2.key2=表3.key2)
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

  • 特点：

    • 一般为表起别名
      • 有别名后，查询字段不能再用原来的表名去限定
    • 多表的顺序可以调换
    • n表连接至少需要n-1个连接条件
    • 等值连接的结果是多表的交集部分
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语法

1. 内连接(重要)

  • 语法：

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

  • 特点：

    • 表的顺序可以调换（多表连接时，需要有关联列可以保证两者可以连接）
    • 内连接的结果=多表的交集
    • n表连接至少需要n-1个连接条件

  • 分类：

    • 等值连接
    • 非等值连接
    • 自连接
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 两边都是主表
    • 一般用于查询除了交集部分的剩余的不匹配的行

  • 分类

    • 左外连接
    • 右外连接
    • 全外连接（不支持mysql）
3. 交叉连接

  • 语法：

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

  • 特点：

    • 类似于笛卡尔乘积
posted @ 2020-10-10 22:02  不知老之将至  阅读(187)  评论(0)  编辑  收藏  举报