MySQL的查询语句详解

作者: leelian | 来源:发表于2018-11-27 17:03 被阅读0次

2018-03-20
MySQL的查询语句详解
6.MySQL架构详解
MySQL慢查询设置
mysql慢查询语句分析
mysql json 查询
MySQL查询语句的45道练习
Oracle、MySql、SQLServer 数据库分页查询语句
Oracle、MySql、SQLServer 数据库分页查询语句
数据库分页查询

MySQL的查询

（1）概览SELECT语句的语法：

SELECT
    列1 as '别名1',
    列2 as '别名2',
    ....
FROM
    table_1（表1）
WHERE
    条件1 AND/OR 条件2
GROUP BY    子句
ORDER BY    子句
HIVING      子句
LIMT 子句

（2） SELECT语句各子句的描述：

SELECT  
    之后是逗号分隔列或星号(*)的列表，表示要返回所有列。
FROM    
    指定要查询数据的表或视图。
    JOIN    根据某些连接条件从其他表中获取数据。
WHERE   
    过滤结果集中的行。
GROUP BY    将一组行组合成小分组，并对每个小分组应用聚合函数。
HAVING      过滤器基于GROUP BY子句定义的小分组。
ORDER BY    指定用于排序的列的列表。
LIMIT       限制返回行的数量。

具体释义：

图片.png

一：select语句：

下载示例数据库：http://www.yiibai.com/downloads/yiibaidb.zip

1.1：查询名字，姓氏，职位，并设置别名

SELECT
    employees.lastName  AS '名',
    employees.firstName AS  '姓',   
    employees.jobTitle  AS  '职位'
FROM
    employees;

1.2：select 用户case替换查询结果中的数据

SELECT
    CASE
        WHEN employees.employeeNumber <= 1100   THEN    '老员工'
        WHEN employees.employeeNumber BETWEEN 1100 AND 1500 THEN    '新员工'
    ELSE    '实习生'
    END AS '员工编号',
  employees.lastName AS '姓',
    employees.firstName AS '名'
FROM
    employees;

可以和其他语句搭配，语法遵循：

case 
    when 条件1 then 执行结果1 
    when 条件2 then 执行结果2 
    else 执行结果3 
    end

1. 3：计算列的值

SELECT
    CASE
        WHEN employees.employeeNumber <= 1100 THEN  '老员工'
        WHEN employees.employeeNumber BETWEEN 1100 AND 1500 THEN    '新员工'
    ELSE    '实习生'
    END  AS '员工编号',
employees.lastName AS '姓',
employees.firstName AS '名',
employees.officeCode as '旧办公室编号',
employees.officeCode + 100 as   '新办公室编号' #对字段进行计算
FROM
    employees;

运行结果:

image

1.4：消除结果中重复的行

SELECT
    DISTINCT    employees.officeCode  AS '办公室'

FROM
    employees;

只能单独使用，不能和其他查询条件一起使用。

1.5：聚合函数

聚合函数对一列或者一组值进行计算，然后返回单个值，其中除了COUNT函数外，其他函数都会忽略空值。

补充：

如果SELECT代码中有GROUP BY语句，那么聚合函数会依次对每一分组都产生作用，
如果没有那么只会生成一行作为结果。

示例：

(1)：无GROUP BY语句

image

(2)：：有GROUP BY语句

image

如果使用count(*)结果就是23，因为包含了NULL

1.6：聚合函数的基本计算（SUM、AVG、MAX、MIN）

公式为：MAX / MIN ALL / DISTINCT 表达式:

SELECT
    COUNT(DISTINCT products.productCode) AS '去重商品数',
    COUNT(products.productCode) AS '商品数',
    MAX(products.buyPrice) AS '最大商品价格',
    MIN(products.buyPrice) AS '最小商品价格',
    AVG(products.buyPrice) AS '商品价格平均值'
FROM
products

运行结果：

image

1.7：聚合函数计算标准差，方差

SELECT
    COUNT(DISTINCT products.productCode) AS '去重商品数',
    COUNT(products.productCode) AS '商品数',
    VARIANCE(products.buyPrice) AS '商品价格方差',
    STDDEV(products.buyPrice) AS '商品价格标准差',
    AVG(products.buyPrice) AS '商品价格平均值'
FROM
    products

variance:方差，
stddev：标准偏差
运行结果：

image

二：FROM子句

select看的查询表对象由from子句指定

2.1：全链接

基本语法："左表 ,右边"

#查询每个办公室的员工名字和办公室电话和地址
SELECT
    offices.officeCode AS '办公室名字',
    employees.firstName AS '姓',
    employees.lastName AS '名',
    offices.phone AS '电话',
    offices.addressLine1 AS '地址1',
    offices.addressLine2 AS '地址2'
FROM
    employees,
    offices

2.2：交叉链接

基本语法：左表 cross join 右边;


SELECT
     employees.employeeNumber,
FROM
    items cross join employees;

最终结果：

图片.png

交叉连接和全连接都是一样的，原理是：从第一张表中循环取出第一行，都去另外一张表的每一行进行匹配，匹配的结果都保留，最终中间表会变得非常大，这样的结果成为笛卡尔积。

可以看到第一行的Diane员工跟6个办公室信息发生了匹配，也就是23行 *6行刚好是161条信息。

这样的结果是没有实际意义的。我们需要的是编号相等的部分。
emp.deptno = dept.deptno

设定emp.deptno = dept.deptno，又叫等值链接


#查询每个办公室的员工名字和办公室电话和地址
SELECT
    offices.officeCode AS '办公室名字',
    employees.firstName AS '姓',
    employees.lastName AS '名',
    offices.phone AS '电话',
    offices.addressLine1 AS '地址1',
    offices.addressLine2 AS '地址2'
FROM
    employees cross join offices
WHERE
    employees.officeCode = offices.officeCode
···

通过where条件筛选剔除了很多无效的值，虽然这样能用，但是where条件效率没有内连接高。
运行结果：

image

2.3：内连接

基本语法：左表 + [inner + join]+ 右表 + on + 左表.字段 = 右表.字段;

用内连接查询每个办公室的员工名字和办公室电话和地址

···
SELECT
    offices.officeCode AS '办公室名字',
    employees.firstName AS '姓',
    employees.lastName AS '名',
    offices.phone AS '电话',
    offices.addressLine1 AS '地址1',
    offices.addressLine2 AS '地址2'
FROM
    employees
INNER JOIN offices ON employees.officeCode = offices.officeCode
····

内连接是将左边的表的一行取出，然后和右表的每一行匹配，可以想象是生成了一个中间表，如果ON的条件是相等的，就将两个表匹配行留下，不相等就都丢弃。跟上面的等值链接一个道理，归根结底都是笛卡尔积，都是不保留字段值不相等的部分，但是效率高。

运行结果：

image

2. 4：外连接

基本语法：
左查询：
左表 + [left join ] + 右表 + on + 左表 . 字段 + 右表 . 字段；

left：以左表为主表里面的记录全部取出，将右表拿去匹配，如果字段 ON相等则留下，如果匹配失败则留NULL。
左表 + [ right join ] + 右表 + on + 左表 . 字段 + 右表 . 字段
right：与left相反。

（1）左查询示例：

image

（2）右查询示例：

image

三：where限制查询：

3.1单一条件限制查询

查询销售岗位的员工名字

SELECT 
    employees.lastName,employees.firstName,employees.jobTitle
FROM    
    employees
WHERE   
    jobTitle = 'sales Rep'

where 不能条件语句中不能使用别名，因为他是直接从磁盘中读取，不要的丢弃，只有HIVING GROUP 才可以。
运行结果：

image

3.2 多条件限制查询

在语句中使用 ’AND‘或者 'OR'连接起来。

查询销售岗位并且办公室代码为1的员工。

SELECT 
    lastname, firstname, jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' AND officeCode = 1;

运行结果：

image

3.3运算符查询

运算符语法：

image

（1）查询使用不等于(!=)运算符来获取不是销售代表的其它所有员工：

SELECT 
    lastname, firstname, jobtitle
FROM
    employees
WHERE
    jobtitle != 'Sales Rep';

运行结果：

image

（2）替换查询结果中的数据

SELECT
    employeeNumber AS '员工编号',
    lastName AS '姓',
    firstName AS '名',
    officeCode AS '办公室名称',
    CASE
WHEN employees.officeCode IS NULL THEN
    '未分配'
WHEN employees.officeCode = 1 THEN
    '第一间'
WHEN employees.officeCode = 2 THEN
    '第二间'
WHEN employees.officeCode = 3 THEN
    '第三间'
ELSE
    '第四间'
END AS '办公室名称',
    jobTitle AS '职位'
FROM
    employees
WHERE
    jobTitle = 'sales Rep'

运行结果：

image

扩展查询：

BETWEEN选择在给定范围值内的值。

LIKE匹配基于模式匹配的值。

IN指定值是否匹配列表中的任何值。

IS NULL检查该值是否为NULL。

Between查询

（3）查找价格在90和100(含90和100)元范围内的商品

SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 90 AND 100;

运行结果：

image

（4）查找购买价格不在20到100(含20到100)之间的产品，可将BETWEEN运算符与NOT运算符组合使用，如下：


SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice NOT BETWEEN 20 AND 100;

运行结果：

image

（5）计算2013-01-01到2013-01-31日期之间的订单。

当使用 BETWEEN运算符 计算 DATE数据类型时，应把计算的字符串类型转换成DATE类型

select 
orders.orderNumber,orders.requiredDate,orders.`status`
FROM    
orders
WHERE
        requiredDate BETWEEN CAST('2013-01-01' AS DATE) AND  CAST('2013-01-31' AS DATE);

#值得注意的是在这里，运行的时候报错，排查时发现原来status在MySQL中是关键字。

运行结果：

image

四：MySQL的相似查询'LIKE'

LIKE运算符有两种通配符：
"%" 匹配若干字符
“_” 匹配单个字符

4.1 搜索名字以字符a开头的员工信息

SELECT 
    employeeNumber, lastName, firstName
FROM
    employees
WHERE
    firstName LIKE 'a%';

运行结果：

image

4.2 查找名字以arry结尾的员工

SELECT employees.employeeNumber,employees.lastName,employees.firstName
FROM
        employees
WHERE
    employees.firstName LIKE '_arry';

运行结果：

image

4.3 通配符的转义：

有时想要匹配的数据包含通配符，例如10%，_20等这样的字符串时。可以使用“\”进行转义，如要转移成特定数据类型，可以使用ESCAPE语句。

不指定类型：

select products.productCode,products.productName
from 
        products
WHERE
        productCode LIKE '%\_20%';

运行结果：

image

4.4 使用ECASPE指定新的转义字符"$"

查询包含“_20”的商品编号：

select products.productCode,products.productName
from 
        products
WHERE
        productCode LIKE '%$_20%' ESCAPE '$';

运行结果：

image

4.4 “IN”查询

比OR代码更简洁

SELECT  offices.officeCode,offices.city,offices.phone,offices.country
from 
    offices
WHERE
        country   IN ('USA','china');

运行结果：

image

五：分组语句 GROUP BY

GROUP BY语句是根据表中字段进行分组，如果字段的值相同，那么就聚合在一个组，不同的放在另一个组，也就是按值分组。与count，max，min,avg，sum等统计函数组合使用。

    cout()：统计分组后，每组的总记录数；
    max()：统计每组中的最大值；
    min()：统计每组中的最小值；
    avg()：统计每组中的平均值；
    sum()：统计每组中的数据总和。

图片.png

六：HIVING 语句

HIVING能做where的所有事情，并且能使用别名：

#查询办公室中人数大于4人的编号
SELECT
    employees.officeCode AS '办公室编号',
    COUNT(DISTINCT employeeNumber) AS '人数'
FROM
    employees
GROUP BY
    officeCode
HAVING 人数> 4

运行结果：

图片.png

七：排序语句 ORDER BY

SELECT
    payments.customerNumber AS '顾客编号',
    COUNT(payments.customerNumber) AS '顾客购买次数',
    avg(payments.amount) '顾客购买单价',
    sum(payments.amount) '顾客购买总数'
FROM
    payments
GROUP BY
    payments.customerNumber 
ORDER BY
    顾客购买次数 DESC,customerNumber DESC;

升序：ASC（ascend：上升），降序DESC（descend：下降）
运行结果：

image

八：查询范围语句 LIMT

限制输出的范围，降低服务器的消耗。
（1）限制多少条结果：

-- 查询表 student 中的全部记录
select * from student;
-- 查询表 student 中的 3 条记录
select * from student limit 3;

运行结果：

image
（2）限制范围：

-- 查询表 student 中的记录
select * from student limit 0,2;
-- 查询表 student 中的记录
select * from student limit 2,2;

运行结果：

image

2018-03-20
MYSQL查询语句 MYSQL复杂操作语句 MYSQL多表查询方法函数部分
MySQL的查询语句详解
MySQL的查询（1）概览SELECT语句的语法：（2） SELECT语句各子句的描述：具体释义：一：se...
6.MySQL架构详解
目录：1.MySQL架构详解2.MyISAM和InnoDB的比较3.SQL查询语句执行路径4.优化查询缓存 1.M...
MySQL慢查询设置
linux下开启mysql慢查询，分析查询语句
mysql慢查询语句分析
如何查找mysql中查询比较慢的语句mysqldumpslow分析慢查询语句一：查看Mysql是否开启了慢查...
mysql json 查询
mysql json 查询 MySQL 5.7新增对JSON支持 1. 普通 json 查询数据查询语句数...
MySQL查询语句的45道练习
MySQL查询语句的45道练习
Oracle、MySql、SQLServer 数据库分页查询语句
（一）、**** mysql的分页查询 mysql的分页查询是最简单的，借助关键字limit即可实现查询，查询语句...
Oracle、MySql、SQLServer 数据库分页查询语句
（一）、**** mysql的分页查询 mysql的分页查询是最简单的，借助关键字limit即可实现查询，查询语句...
数据库分页查询
sqlserver分页查询语句 mysql分页 oracle分页查询语句 EF Lambda Skip跳过多少条...

MySQL的查询语句详解

MySQL的查询

一：select语句：

1.1：查询名字，姓氏，职位，并设置别名

1.2：select 用户case替换查询结果中的数据

1. 3：计算列的值

1.4：消除结果中重复的行

1.5：聚合函数

(1)：无GROUP BY语句

(2)：：有GROUP BY语句

1.6：聚合函数的基本计算（SUM、AVG、MAX、MIN）

1.7：聚合函数计算标准差，方差

二：FROM子句

2.1：全链接

2.2：交叉链接

2.3：内连接

2. 4：外连接

（1）左查询示例：

（2）右查询示例：

三：where限制查询：

3.1单一条件限制查询

3.2 多条件限制查询

3.3运算符查询

四：MySQL的相似查询'LIKE'

4.1 搜索名字以字符a开头的员工信息

4.2 查找名字以arry结尾的员工

4.3 通配符的转义：

4.4 使用ECASPE指定新的转义字符"$"

4.4 “IN”查询

五：分组语句 GROUP BY

六：HIVING 语句

七： 排序语句 ORDER BY

八：查询范围语句 LIMT

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

七：排序语句 ORDER BY