单表查询

1.单表查询

1.基础查询

1.查询所有列

SELECT * FROM stu;

2.查询指定列

SELECT sid, sname, age FROM stu;

3.条件查询

SELECT sid, sname, age FROM stu where sid = 3;

2.条件查询介绍

1.运算符及关键字

WHERE子句中可以使用如下运算符及关键字：

=、!=、<>、<、<=、>、>=；

BETWEEN…AND；

IN(set)；

IS NULL；  

AND；

OR；

NOT；

2. AND

查询性别为女，并且年龄小于50的记录

SELECT * FROM stu 

WHERE gender='female' AND  age<50;

3.OR

查询学号为S_1001，或者姓名为liSi的记录

SELECT * FROM stu 

WHERE sid ='S_1001' OR sname='liSi';

4.IN和Exits

查询学号为S_1001，S_1002，S_1003的记录

SELECT * FROM stu 

WHERE sid IN ('S_1001','S_1002','S_1003');

注意：sid可以是 sid，也可以是（sid，sname）多个

两者区别：

in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists

注意：所以无论那个表大，用not exists都比not in要快，因为not exists可以用到索引

5.between and

SELECT * FROM stu 

WHERE sid between 1 and 2

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

3.模糊查询

1.关键字

LIKE

2.通配符

_ 任意一个字母

%：任意0~n个字母

‘张%’

3.实例

查询姓名由5个字母构成的学生记录

SELECT * 

FROM stu

WHERE sname LIKE 'abc';

模糊查询必须使用LIKE关键字。其中 “”匹配任意一个字母，5个“”表示5个任意字母。

4.字段控制查询

1.去除重复记录

当只查询表的某字段时，会出现重复记录，去除重复记录时，需要使用DISTINCT：

SELECT DISTINCT sal FROM emp;

2.判空

查看雇员的月薪与佣金之和

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROM emp;

3.给列名添加别名

AS

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

**给列起别名时，是可以省略AS关键字的：**

SELECT *,sal+IFNULL(comm,0)  total FROM emp;

4.排序

ORDER BY 升序 加 DESC 降序

a.查询所有学生记录，按年龄升序排序

SELECT *

FROM stu

ORDER BY sage;

b.查询所有学生记录，按年龄降序排序

SELECT *

FROM stu

ORDER BY age DESC;

按照两个的排序

c.查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

SELECT * FROM emp

ORDER BY sal DESC,empno ASC;

5.聚合函数

sum avg max min count

聚合函数是用来做纵向运算的函数：

COUNT()：统计指定列不为NULL的记录行数；

MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

1.COUNT

当需要纵向统计时可以使用COUNT()。

a.查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

b.查询emp表中有佣金的人数：

SELECT COUNT(comm) cnt FROM emp;

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

2.SUM和AVG

当需要纵向求和时使用sum()函数。

a.查询所有雇员月薪和

SELECT SUM(sal) FROM emp;

b.查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

3.MAX和MIN

查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

6.分组查询

GROUP BY

//注：凡和聚合函数同时出现的列名，则一定要写在group by 之后

1.分组查询

查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno;

2.HAVING子句

查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)

FROM emp  

GROUP BY deptno

HAVING SUM(sal) > 9000;

注：having与where的区别:

​ 1.having是在分组后对数据进行过滤.

​ where是在分组前对数据进行过滤

​

2.having后面可以使用分组函数(统计函数)

where后面不可以使用分组函数。

WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

**7.**LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。

1.查询限制

查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0, 5;

注意，起始行从0开始，即第一行开始！

2.分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？

第一页记录起始行为0，一共查询10行；

第二页记录起始行为10，一共查询10行；

第三页记录起始行为20，一共查询10行；

​ 查询语句书写顺序：select – from- where- group by- having- order by-limit

​ 查询语句执行顺序：from - where -group by - having - select - order by-limit

8.其他

1.as的使用

使用as定义可以进化操作，例如查询的结果或者是表

例如

SELECT
	a.*,
	b.s_score AS 01_score, //别名为01_score
	c.s_score AS 02_score 
FROM
	student a //别名为a
	JOIN score b ON a.s_id = b.s_id 

2.case when

MySQL 的 case when 的语法有两种：

1. 简单函数
   CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
2. 搜索函数
   CASE WHEN [expr] THEN [result1]…ELSE [default] END

这两种语法有什么区别呢？

简单函数

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END： 枚举这个字段所有可能的值

[](javascript:void(0)?

SELECT
    NAME '英雄',
    CASE NAME
        WHEN '德莱文' THEN
            '斧子'
        WHEN '德玛西亚-盖伦' THEN
            '大宝剑'
        WHEN '暗夜猎手-VN' THEN
            '弩'
        ELSE
            '无'
    END '装备'
FROM
    user_info;

[](javascript:void(0)?

[](javascript:void(0)?

SELECT
    NAME '英雄',
    CASE NAME
        WHEN '德莱文' THEN
            '斧子'
        WHEN '德玛西亚-盖伦' THEN
            '大宝剑'
        WHEN '暗夜猎手-VN' THEN
            '弩'
        ELSE
            '无'
    END '装备'
FROM
    user_info;

搜索函数

CASE WHEN [expr] THEN [result1]…ELSE [default] END：搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略

[](javascript:void(0)?

# when 表达式中可以使用 and 连接条件
SELECT
    NAME '英雄',
    age '年龄',
    CASE
        WHEN age < 18 THEN
            '少年'
        WHEN age < 30 THEN
            '青年'
        WHEN age >= 30
        AND age < 50 THEN
            '中年'
        ELSE
            '老年'
    END '状态'
FROM
    user_info;

select @@global.var_name;
show global variables like “%var%”;

例子：

.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT s.c_id,SUM(case when s.s_score >= 60 then 1 else 0 end)/sum(case when s.s_score then 1 else 0 end) *100 as'及格率'
from score s
left JOIN course c on s.c_id = c.c_id
GROUP BY s.c_id

3.format

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products

4.Round() 函数

如 Round(@num,2) ，其中参数 2 表示 保留两位有效数字。

缺点：Round() 只是负责四舍五入到两位小数，但是不负责去掉后面的0。

print ROUND(13.145, 2);

或者select ROUND(13.145, 2);

结果为：13.150。

5.Convert

Convert(decimal(18,2),@num) 实现转换，其中参数 2 表示 保留两位有效数字。

print Convert(decimal(18,2),13.145)

结果为：13.15。

6.cast

cast(@num as decimal(18,2)) 实现转换，其中参数 2 表示 保留两位有效数字。

print cast(13.145 as decimal(18,2))

结果为：13.15。