MySQL 必知必会---数据检索

目录

1. 查询结果去重 distinct

2. 限定数据的数量   limit

3. 对数据进行排序   order by 子句

4. 数据过滤    where 子句

---

主要记录基本sql语法

1. 查询结果去重 distinct

select distinct name，age from user;

distinct必须放在所有的被选中字段的前面

distinct 作用于所有被选字段（例子中指name和age字段），保证返回的数据没有重复的（所有选中的字段为一个整体，进行比对）

 

2. 限定数据的数量   limit

select * from user limit 5;

select * from user limit 5 offset 1;

select * from user limit 0,5;

将limit至于语句的最后，后面跟要返回的数据的最大数量（可能返回的数量小于设定值）。

也可以使用offset设定从第几行开始返回，需要注意的是offset是0的时候，从第一行开始返回。

也可以使用简化语法 limit  offset, count

limit不是子句

 

3. 对数据进行排序   order by 子句

select name,age,sex from people order by name;

select name,age from people order by sex;

select name,age,sex from people order by sex limit 3

order by必须是最后一个子句，第三个例子中的limit不是子句

排序的字段，不一定非得是要显示的字段，例如第二个例子

 

select name,age,sex from people order by name, age;

可以按照多列进行排序，但是要注意，以上面的例子为例，只有当name值相同时，才会使用age进行排序。

 

还有一种简化方法，select name,age,sex from people order by 1, 2;   它的效果与上例相同， 1就是代表name，2代表age。这种方式写法简单，但是依赖字段清单的顺序，容易在更新脚本时出问题。

 

order by子句排序默认是升序排列，1--100， a----z。 

我们可以指定是升序还是降序， 升序使用关键字 ASC  降序使用 DESC，因为升序是默认值，所以ASC我们很少用。

select name,age,sex from people order by name desc, age desc;

select name,age,sex from people order by name desc, age;

需要注意的是desc仅对它前面的一个字段起作用，其他未用desc的字段依然使用升序

 

4. 数据过滤    where 子句

where子句位于from子句之后，用来过滤出符合条件的数据集

where子句又叫做条件子句，所以肯定需要一些比较操作符来测试数据是否符合要求：

操作符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<> 或 !=	不等于
<=>	mysql特有的等于操作符：当两个值相等或者当两者都等于空值时返回true，否则返回false
between...and...	在一个取值范围之内
in(set)	匹配值列表中的任意一个值
like	匹配一个字符串
is null	测试值是否为空值
regexp	测试值是否满足正则表达式

这些操作符的含义和我们平常在各种编程语言中见到的大部分是相同的，我们只对特殊的地方进行说明：

#假如我们有一个people表，如下
+----------+-----+------+----+
| name     | age | sex  | id |
+----------+-----+------+----+
| zhangsan |  10 | NULL |  1 |
| zhangsan |  10 |    0 |  2 |
| zhangsan |   8 |    1 |  3 |
| wang1    |   1 |    1 |  4 |
| wang2    |   2 |    1 |  5 |
| wang3    |   3 |    2 |  6 |
+----------+-----+------+----+


# 当与字符串进行相等比较时，需要使用单引号，而与数值比较时不需要
select * from people where name='zhangsan';
select * from people where age=10;

# 与空值比较必须使用is null, 而不能使用=， 或者可以使用<=>
select * from people where sex is null;   -- 可以返回正确的结果集
select * from people where sex<=>null;    -- 可以返回正确的结果集
select * from people where sex=null;      -- 返回的结果集为空


#between N and M
select * from people where age between 1 and 3;   -- age>=1 并且 age<=3
select * from people where age between 3 and 1;   -- 返回空数据集


#当被过滤列包含值为NULL的时候， NULL代表未知，不管是匹配过滤还是不匹配过滤，都不会返回包含NULL的行
select * from people where sex!=2;


#进行列值的成对比较 要慎用
select * from people where (age,sex)=(8,1);    -- 相对于 age=8 and sex=1
#要注意使用不同操作符进行成对比较时，转换成标量表达式的方式是不同的
select * from people where (age,sex)>(8,1);    -- 相当于 age > 8 or (age=8 and sex>1)


#验证列的值是否在给定的列表内，列表内可以是字面量，列，标量子查询
select * from people where age in (8,1);  -- age是8或者1的行
select * from people where 1 in (age,sex);  -- age或sex为1的行
select * from people where (age,sex) in ((8,1),(10,0));  -- (age=8 and sex=1) or (age=10 and sex=0)
select * from people where age not in (1,8);  -- age!=1 and age!=8
#在mysql中，in 被转换为or， not in被转换为and


#使用like进行字符串模糊匹配
#有2个通配符可以使用： %表示0或任意多个字符   _表示一个任意字符
select * from people where name like 'z%';     -- 以z开头
select * from people where name like '%3';     -- 以3结尾
select * from people where name like '_a%';    -- 以任意字符开头，第二个字符为a
select * from people where name like 'wang_';  -- 以wang开头，之后是任意一个字符

当使用like时，我们可以使用通配符，但是当字符串中包含统配符以及\时，我们要进行特殊的处理

+-----------+-----+------+----+
| name      | age | sex  | id |
+-----------+-----+------+----+
| zhang%san |  10 | NULL |  1 |
| zhang_san |  10 |    0 |  2 |
| zhang\san |   8 |    1 |  3 |
+-----------+-----+------+----+

select * from people where name like "zhang\%%";
select * from people where name like "zhang\_%";
select * from people where name like "zhang\\\%";   -- 此处需要3个哦

#对于字符串中% _ \， 当我要匹配他们时，需要进行转义，转义使用\

 

还剩一个操作符没有说到，那就是无比强大的 ---- 正则

正则模式	描述
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
.	匹配除\n之外的任何单个字符。如果连\n也要匹配，请用[.\n]
[...]	字符集合。匹配所包含的任意一个字符
[^...]	字符集合。匹配所不包含的任意一个字符
p1|p2|p3	匹配p1或p2或p3
*	匹配前面的子表达式零次或多次
+	匹配前面的子表达式1次或多次
{n}	n是个非负数，匹配前面子表达式n次
{n,m}	n和m都是非负数，n<=m， 最少匹配n次，最多匹配m次

#正则太灵活了，此处随便举几个例子
+-----------+-----+------+----+
| name      | age | sex  | id |
+-----------+-----+------+----+
| zhang%san |  10 | NULL |  1 |
| zhang_san |  10 |    0 |  2 |
| zhang\san |   8 |    1 |  3 |
| wang1     |   1 |    1 |  4 |
| wang2     |   2 |    1 |  5 |
| wang3     |   3 |    2 |  6 |
+-----------+-----+------+----+

select * from people where name regexp '.*n$'; -- 以n结尾
select * from people where name regexp 'wang[123]'; -- 匹配包含 wang1 或 wang2 或 wang3

 

注意：虽然正则和like很强大，但是他们都无法匹配到值为NULL的情况

select * from people where name like '%';

select * from people where name regexp '.*';

他们都无法匹配到值为空的name

 

 

上面我们介绍的都是单个条件过滤数据，如果有多个条件呢，那就得用逻辑运算符进行连接了。

and	逻辑与
or	逻辑或
not	逻辑非
xor	逻辑异或，左右都为true或false时，结果为false

and优先级高于or，可以用小括号改变运算顺序

select * from people where name like 'zhang%' or name regexp 'wang[123]';