select语言
MySQL进阶查询
按关键字排序
-
使用order by语句来实现排序
-
排序可针对一个或多个字段
-
ASC:升序,默认排序方式
-
DESC:降序
-
ORDER BY的语法结构
- select 字段1,字段2 from 表名 order by 字段1 desc|asc,字段2 desc|asc;
mysql> create database ddd;
Query OK, 1 row affected (0.01 sec)
mysql> use ddd;
Database changed
mysql> create table ddd(name varchar(128),score int(10));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into ddd(name,score)values('aa','11');
Query OK, 1 row affected (0.01 sec)
mysql> insert into ddd(name,score)values('bb','11');
Query OK, 1 row affected (0.01 sec)
mysql> insert into ddd(name,score)values('cc','22');
Query OK, 1 row affected (0.01 sec)
mysql> insert into ddd(name,score)values('dd','33');
Query OK, 1 row affected (0.01 sec)
mysql> select * from ddd;
+------+-------+
| name | score |
+------+-------+
| aa | 11 |
| bb | 11 |
| cc | 22 |
| dd | 33 |
+------+-------+
4 rows in set (0.01 sec)
降序
mysql> select name,score from ddd order by score desc;
+------+-------+
| name | score |
+------+-------+
| dd | 33 |
| cc | 22 |
| aa | 11 |
| bb | 11 |
+------+-------+
4 rows in set (0.00 sec)
升序
mysql> select name,score from ddd order by score;
+------+-------+
| name | score |
+------+-------+
| aa | 11 |
| bb | 11 |
| cc | 22 |
| dd | 33 |
+------+-------+
4 rows in set (0.00 sec)
多字段排序
mysql> create table eee(id int(2),name varchar(128),score int(10));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into eee(id,name,score)values(1,'aa','11');
Query OK, 1 row affected (0.01 sec)
mysql> insert into eee(id,name,score)values(2,'bb','11');
Query OK, 1 row affected (0.01 sec)
mysql> insert into eee(id,name,score)values(3,'cc','22');
Query OK, 1 row affected (0.01 sec)
mysql> insert into eee(id,name,score)values(4,'dd','33');
Query OK, 1 row affected (0.00 sec)
mysql> select * from eee;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | aa | 11 |
| 2 | bb | 11 |
| 3 | cc | 22 |
| 4 | dd | 33 |
+------+------+-------+
4 rows in set (0.00 sec)
mysql> select id,name,score from eee order by id desc,score asc;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 4 | dd | 33 |
| 3 | cc | 22 |
| 2 | bb | 11 |
| 1 | aa | 11 |
+------+------+-------+
4 rows in set (0.00 sec)
对结果进行分组
- 使用GROUP BY语句来实现分组
- 通常结合聚合函数一起使用
- 可以按一个或多个字段对结果进行分组
- GROUP BY的语法结构
select id,name,score from eee where score>=22 group by score;
#按score进行分组,count统计次数,score大于等于22的score相同的name数量
mysql> select id,name,score from eee where score>=22 group by score;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 3 | cc | 22 |
| 4 | dd | 33 |
+------+------+-------+
2 rows in set (0.01 sec)
增加按分数降序:
mysql> select id,name,score from eee where score>=22 group by score order by score desc;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 4 | dd | 33 |
| 3 | cc | 22 |
+------+------+-------+
2 rows in set (0.00 sec)
限制结果条目
-
只返回select查询结果的第一行或前几行
-
使用limit语句限制条目
-
limit语法结构:
- select 字段1,字段2 from 表名 limit [offset,] number;
- offset:位置偏移量,从0开始
- number:返回记录行的最大数目
mysql> select * from eee;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | aa | 11 |
| 2 | bb | 11 |
| 3 | cc | 22 |
| 4 | dd | 33 |
| 5 | ee | 44 |
| 6 | ff | 55 |
+------+------+-------+
6 rows in set (0.00 sec)
mysql> select * from eee limit 3; #显示三行数据
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | aa | 11 |
| 2 | bb | 11 |
| 3 | cc | 22 |
+------+------+-------+
3 rows in set (0.00 sec)
mysql> select * from eee limit 3,3; #从第三行开始,显示三行,从0开始计数
+------+------+-------+
| id | name | score |
+------+------+-------+
| 3 | cc | 22 |
| 4 | dd | 33 |
| 5 | ee | 44 |
+------+------+-------+
3 rows in set (0.00 sec)
mysql> select * from eee order by score desc limit 3; #显示score最大的三行
+------+------+-------+
| id | name | score |
+------+------+-------+
| 6 | ff | 55 |
| 5 | ee | 44 |
| 4 | dd | 33 |
+------+------+-------+
3 rows in set (0.00 sec)
设置别名
- 使用as语句设置别名,关键字as可省略
- 设置别名时,保证不能与库中其他表或字段名称冲突
- 别名的语法结构
- 字段别名:
- select 字段 as 别名 from 表名;
- 表的别名:
- select 字段 from 表名 as 别名;
- 字段别名:
mysql> select name as xingming from eee; #name别名xingming
+----------+
| xingming |
+----------+
| aa |
| bb |
| cc |
| dd |
| ee |
| ff |
+----------+
6 rows in set (0.00 sec)
通配符
- 用于替换字符串中的部分字符
- 通常配合LIKE一起使用,并协同WHERE完成查询
- 常用通配符
- %表示零个、一个或多个即任意字符
- _表示单个字符
select * from eee where name like 'a%'; #匹配a开头的任意字符
select * from eee where name like 'e_'; #匹配e开头1个未知字符
select * from eee where name like '_f%'; #匹配一位未知字符,i后任意字符
mysql> select * from eee where name like 'a%';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | aa | 11 |
+------+------+-------+
1 row in set (0.01 sec)
mysql> select * from eee where name like 'e_';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 5 | ee | 44 |
+------+------+-------+
1 row in set (0.00 sec)
mysql> select * from eee where name like '_f%';
+------+------+-------+
| id | name | score |
+------+------+-------+
| 6 | ff | 55 |
+------+------+-------+
1 row in set (0.00 sec)
子查询
- 也称作内查询或者嵌套查询
- 先于主查询被执行,其结果将作为外层主查询的条件
- 在增删改查中都可以使用子查询
- 支持多层嵌套
- IN语句是用来判断某个值是否在给定的结果集中
mysql> select id,name from eee where id in(1,2); #查看id,name,只看id为1和2的值
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
+------+------+
2 rows in set (0.01 sec)
mysql> select id,name from eee where id in(select id from eee where id>3);
+------+------+
| id | name |
+------+------+
| 4 | dd |
| 5 | ee |
| 6 | ff |
+------+------+
3 rows in set (0.01 sec)
mysql> select score,name from eee where score in(select score from eee where name='aa'); #查看score、name,只看name为aa的score,score为11,bb的score也为11,所以有两项值
+-------+------+
| score | name |
+-------+------+
| 11 | aa |
| 11 | bb |
+-------+------+
2 rows in set (0.00 sec)
mysql> select score,name from eee where score =(select score from eee where name='aa'); #这里=和in同理
+-------+------+
| score | name |
+-------+------+
| 11 | aa |
| 11 | bb |
+-------+------+
2 rows in set (0.01 sec)
mysql> select score,name from eee where score !=(select score from eee where name='aa'); #不等于
+-------+------+
| score | name |
+-------+------+
| 22 | cc |
| 33 | dd |
| 44 | ee |
| 55 | ff |
+-------+------+
4 rows in set (0.00 sec)
mysql> select score,name from eee where score <>(select score from eee where name='aa'); #<>与!=均为不等于
+-------+------+
| score | name |
+-------+------+
| 22 | cc |
| 33 | dd |
| 44 | ee |
| 55 | ff |
+-------+------+
4 rows in set (0.00 sec)
多嵌套模式
select、update、delete都支持多层嵌套
mysql> select id,name,score from eee where score in (select score from (select score from eee where name='aa') eee);
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | aa | 11 |
| 2 | bb | 11 |
+------+------+-------+
2 rows in set (0.00 sec)
exists的用法:
mysql> select * from eee where exists (select * from eee where score=11);
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | aa | 11 |
| 2 | bb | 11 |
| 3 | cc | 22 |
| 4 | dd | 33 |
| 5 | ee | 44 |
| 6 | ff | 55 |
+------+------+-------+
6 rows in set (0.01 sec)
NULL值
- 表示缺失的值
- 与数字0或者空白(spaces)是不同的
- 使用IS NULL或IS NOT NULL进行判断
- NULL值和空值的区别:
- 空值长度为0,不占空间;NULL值的长度为NULL,占用空间
- IS NULL无法判断空值
- 空值使用“="或者“>"来处理
- COUNT()计算时,NULL会忽略,空值会加入计算
mysql> insert into eee (id,name) values(7,'gg');
Query OK, 1 row affected (0.01 sec)
mysql> select * from eee;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | aa | 11 |
| 2 | bb | 11 |
| 3 | cc | 22 |
| 4 | dd | 33 |
| 5 | ee | 44 |
| 6 | ff | 55 |
| 7 | gg | NULL |
+------+------+-------+
7 rows in set (0.00 sec)
mysql> select count(score) from eee; #count()计算时,null会忽略
+--------------+
| count(score) |
+--------------+
| 6 |
+--------------+
1 row in set (0.00 sec)
mysql> select * from eee where score is null; #查询score字段为null的记录
+------+------+-------+
| id | name | score |
+------+------+-------+
| 7 | gg | NULL |
+------+------+-------+
1 row in set (0.00 sec)
mysql> select * from eee where score is not null; #查询score字段不为null的记录
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | aa | 11 |
| 2 | bb | 11 |
| 3 | cc | 22 |
| 4 | dd | 33 |
| 5 | ee | 44 |
| 6 | ff | 55 |
+------+------+-------+
6 rows in set (0.00 sec)
正则表达式
- 根据指定的匹配模式匹配记录中符合要求的特殊字符
- 使用REGEXP关键字指定匹配模式
常用匹配模式
字符 | 作用 |
---|---|
^ | 匹配文本的开始字符 |
$ | 匹配文本的结束字符 |
. | 匹配任何单个字符 |
* | 匹配前面的字符零次或多次 |
+ | 匹配前面的字符一次或多次 |
字符串 | 匹配包含指定的字符串 |
p1lp2 | 匹配p1或p2 |
[…] | 匹配字符集合中任一字符 |
[^…] | 匹配不在括号中的任一字符 |
{n} | 匹配前面的字符串n次 |
{n,m} | 匹配前面的字符串至少n次,之多m次 |
运算符
算术运算符
以select命令来实现最基础的加减乘除运算
mysql> select 1+2,2+2,3-1,3-5,2*6,4/2,7%5;
+-----+-----+-----+-----+-----+--------+------+
| 1+2 | 2+2 | 3-1 | 3-5 | 2*6 | 4/2 | 7%5 |
+-----+-----+-----+-----+-----+--------+------+
| 3 | 4 | 2 | -2 | 12 | 2.0000 | 2 |
+-----+-----+-----+-----+-----+--------+------+
1 row in set (0.01 sec)
整数相除,出来的结果是浮点型的
在除法运算和求余数运算中,除数不能为 0,若除数是 0,返回的结果则为 NULL
需要注意的是,如果有多个运算符,按照先乘除后加减的优先级进行运算
mysql> select 1+2*3;
+-------+
| 1+2*3 |
+-------+
| 7 |
+-------+
1 row in set (0.00 sec)
mysql> select (1+2)*3;
+---------+
| (1+2)*3 |
+---------+
| 9 |
+---------+
1 row in set (0.00 sec)
- 某些字符串类型的字段存储的数字型字符串,这些字段在进行算术运算时将会被自动转换为数字的值。如果字符串的开始部分是数字,在转换时将被转换为这个数字。如果是既包含字符又包含数字得的混合字符串,无法转换为数字时,将被转换为 0。
mysql> select 2*'4int';
+----------+
| 2*'4int' |
+----------+
| 8 |
+----------+
1 row in set, 1 warning (0.01 sec)
mysql> select 2*'int4';
+----------+
| 2*'int4' |
+----------+
| 0 |
+----------+
1 row in set, 1 warning (0.00 sec)
比较运算符
运算符 | 描述 |
---|---|
= | 等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
!=或<> | 不等于 |
is null | 判断一个值是否为NULL |
is not null | 判断一个值是否不为NULL |
between and | 两者之间 |
in | 在集合中 |
like | 通配符匹配 |
greatest | 两个或多个参数时返回最大值 |
least | 两个或多个参数时返回最小值 |
regext | 正则表达式 |
等于运算符
用来判断数字、字符串和表达式是否相等的,如果相等则返回 1,如果不相等则返回 0。其中字符的比较是根据 ASCII 码来判断的
ASCII码表转换:
0-48,1-49,…9-57
A-65,B-66
a-97,b-98
比较规则:
- 如果两者都是整数,则按照整数值进行比较。
- 如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比较。
- 如果两者都是字符串,则按照字符串进行比较。
- 如果两者中至少有一个值是 NULL,则比较的结果是 NULL。
mysql> select 2=2,3=2,1='1','a'='b','b'='b','a'=null;
+-----+-----+-------+---------+---------+----------+
| 2=2 | 3=2 | 1='1' | 'a'='b' | 'b'='b' | 'a'=null |
+-----+-----+-------+---------+---------+----------+
| 1 | 0 | 1 | 0 | 1 | NULL |
+-----+-----+-------+---------+---------+----------+
1 row in set (0.01 sec)
不等于运算符
- 不等于号有两种写法,分别是<>或者!=,用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1,如果相等则返回 0。
- 需要注意的是不等于运算符不能用于判断 NULL。
mysql> select 1!=2,1<>2,'a'!='ab','ab'<>'abc',1<>null;
+------+------+-----------+-------------+---------+
| 1!=2 | 1<>2 | 'a'!='ab' | 'ab'<>'abc' | 1<>null |
+------+------+-----------+-------------+---------+
| 1 | 1 | 1 | 1 | NULL |
+------+------+-----------+-------------+---------+
1 row in set (0.01 sec)
大于、大于等于、小于、小于等于运算符
mysql> select 1>2,1>=2,1<2,1<=2,2>null; #不能用于判断NULL
+-----+------+-----+------+--------+
| 1>2 | 1>=2 | 1<2 | 1<=2 | 2>null |
+-----+------+-----+------+--------+
| 0 | 0 | 1 | 1 | NULL |
+-----+------+-----+------+--------+
1 row in set (0.00 sec)
IS NULL、IS NOT NULL
- IS NULL 判断一个值是否为 NULL,如果为 NULL 返回 1,否则返回 0
- IS NOT NULL 判断一个值是否不为 NULL,如果不为 NULL 返回 1,否则返回 0
mysql> select '1' is null, '1' is not null, null is null, null is not null;
+-------------+-----------------+--------------+------------------+
| '1' is null | '1' is not null | null is null | null is not null |
+-------------+-----------------+--------------+------------------+
| 0 | 1 | 1 | 0 |
+-------------+-----------------+--------------+------------------+
1 row in set (0.00 sec)
between and
用于判断一个值是否落在某两个值之间
判断某数字是否在另外两个数字之间,也可以判断某英文字母是否在另外两个字母之间
mysql> select 1 between 2 and 3, 'b' between 'a' and 'c';
+-------------------+-------------------------+
| 1 between 2 and 3 | 'b' between 'a' and 'c' |
+-------------------+-------------------------+
| 0 | 1 |
+-------------------+-------------------------+
1 row in set (0.00 sec)
least、greatest
- LEAST:当有两个或者多个参数时,返回其中的最小值。如果其中一个值为 NULL,则返回结果就为 NULL
- GREATEST:当有两个或者多个参数时,返回其中的最大值。如果其中一个值为 NULL, 则返回结果就为 NULL
mysql> select least(1,2), greatest(1,2);
+------------+---------------+
| least(1,2) | greatest(1,2) |
+------------+---------------+
| 1 | 2 |
+------------+---------------+
1 row in set (0.01 sec)
mysql> select least('a','b'), greatest('a','b');
+----------------+-------------------+
| least('a','b') | greatest('a','b') |
+----------------+-------------------+
| a | b |
+----------------+-------------------+
1 row in set (0.00 sec)
mysql> select least(1,2,null), greatest(1,2,null);
+-----------------+--------------------+
| least(1,2,null) | greatest(1,2,null) |
+-----------------+--------------------+
| NULL | NULL |
+-----------------+--------------------+
1 row in set (0.00 sec)
in、not in
- IN 判断一个值是否在对应的列表中,如果是返回 1,否则返回 0
- NOT IN 判断一个值是否不在对应的列表中,如果不是返回 1,否则返回 0
mysql> select 1 in (1,2,3), 'a' not in (1,2,3), 1 in ('a','b','c');
+--------------+--------------------+--------------------+
| 1 in (1,2,3) | 'a' not in (1,2,3) | 1 in ('a','b','c') |
+--------------+--------------------+--------------------+
| 1 | 1 | 0 |
+--------------+--------------------+--------------------+
1 row in set, 4 warnings (0.00 sec)
like、not like
- LIKE 用来匹配字符串,如果匹配成功则返回 1,反之返回 0;NOT LIKE 正好跟 LIKE 相反
- LIKE 支持两种通配符:’%’ 用于匹配任意数目的字符,而’_’只能匹配一个字符
mysql> select 'abc' like 'a%','abc' like 'ab_', 'ab' not like 'a_';
+-----------------+------------------+--------------------+
| 'abc' like 'a%' | 'abc' like 'ab_' | 'ab' not like 'a_' |
+-----------------+------------------+--------------------+
| 1 | 1 | 0 |
+-----------------+------------------+--------------------+
1 row in set (0.00 sec)
逻辑运算符
又被称为布尔运算符
用来判断表达式的真假
运算符 | 描述 |
---|---|
NOT或! | 逻辑非 |
AND或&& | 逻辑与 |
OR或 | |
XOR | 逻辑异或 |
逻辑非
- 逻辑非将跟在它后面的逻辑测试取反,把真变为假,把假变为真
- 如果 NOT 后面的操作数为 0 时,所得值为 1;如果操作数为非 0 时,所得值为 0;如果操作数为 NULL 时,所得值为 NULL
mysql> select ! 0, ! null, not 1, not 0; #非0值都是1
+-----+--------+-------+-------+
| ! 0 | ! null | not 1 | not 0 |
+-----+--------+-------+-------+
| 1 | NULL | 0 | 1 |
+-----+--------+-------+-------+
1 row in set (0.00 sec)
逻辑与
如果所有值都是真返回 1,否则返回 0
mysql> select 1 and 2, 1 && 1, 1 and null, 1 and 0, 0 && null;
+---------+--------+------------+---------+-----------+
| 1 and 2 | 1 && 1 | 1 and null | 1 and 0 | 0 && null |
+---------+--------+------------+---------+-----------+
| 1 | 1 | NULL | 0 | 0 |
+---------+--------+------------+---------+-----------+
1 row in set (0.00 sec)
逻辑或(最好用or)
逻辑或表示包含的操作数,任意一个为非零值并且不是 NULL 值时,返回 1,否则返回0
mysql> select 1 or 1, 1 or 0, 1 or null, 0 or null, 0 or 0;
+--------+--------+-----------+-----------+--------+
| 1 or 1 | 1 or 0 | 1 or null | 0 or null | 0 or 0 |
+--------+--------+-----------+-----------+--------+
| 1 | 1 | 1 | NULL | 0 |
+--------+--------+-----------+-----------+--------+
1 row in set (0.00 sec)
逻辑异或
- 两个非 NULL 值的操作数,如果两者都是 0 或者都是非 0,则返回 0;如果一个为 0, 另一个为非 0,则返回结果为 1
- 当任意一个值为 NULL 时,返回值为 NULL
mysql> select 0 xor 1, 0 xor null, 1 xor 1,0 xor 0;
+---------+------------+---------+---------+
| 0 xor 1 | 0 xor null | 1 xor 1 | 0 xor 0 |
+---------+------------+---------+---------+
| 1 | NULL | 0 | 0 |
+---------+------------+---------+---------+
1 row in set (0.00 sec)
and运算,只要碰到0就是0,(非0和null是null)
or运算,只要碰到非0值就是1,(0和null是null)
异或运算,只要碰到null都是null
位运算符
位运算符实际上是对二进制数进行计算的运算符
mysql> select 10&15,10|15,10^15,15&~10;
+-------+-------+-------+--------+
| 10&15 | 10|15 | 10^15 | 15&~10 |
+-------+-------+-------+--------+
| 10 | 15 | 5 | 5 |
+-------+-------+-------+--------+
1 row in set (0.00 sec)
常用的位运算符
运算符 | 说明 |
---|---|
& | 按位与 |
按位或 | |
~ | 按位取反 |
^ | 按位异或 |
<< | 按位左移 |
>> | 按位右移 |
连接查询
- 通常都是将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接。
要先确定一个主表作为结果集,然后将其他表的行有选择性的连接到选定的主表结果集上。 - 使用较多的连接查询包括:内连接、左连接和右连接
内连接
在from子句中使用关键字 inner join 来连接多张表,并使用 on子句设置连接条件
mysql> create table score (score char(16) not null, 判定 char(48) default'');
Query OK, 0 rows affected (0.02 sec)
mysql> insert into score values(100,'满分'),(59,'不及格');
\Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from score;
+-------+-----------+
| score | 判定 |
+-------+-----------+
| 100 | 满分 |
| 59 | 不及格 |
+-------+-----------+
2 rows in set (0.00 sec)
mysql> select ddd.name, score.判定 from ddd inner join score on ddd.score=score.score;
内连接是系统默认的表连接,所以在 FROM 子句后可以省略 INNER 关键字,只使用关键字 JOIN。同时有多个表时,也可以连续使用 INNER JOIN 来实现多表的内连接,不过为了更好的性能,建议最好不要超过三个表
外连接
- 左连接
- 主表在左边,主表内容会全部显示出来,在从表中没匹配到的以NULL显示出来
mysql> select eee.name, score.判定 from eee left join score on eee.score=score.score;
+------+--------+
| name | 判定 |
+------+--------+
| aa | NULL |
| bb | NULL |
| cc | NULL |
| dd | NULL |
| ee | NULL |
| ff | NULL |
| gg | NULL |
+------+--------+
7 rows in set (0.00 sec)
- 右连接
- 主表在右边,主表内容会全部显示出来,在从表中没匹配到的以NULL显示出来
mysql> select eee.name, score.判定 from eee right join score on eee.score=score.score;
+------+-----------+
| name | 判定 |
+------+-----------+
| NULL | 满分 |
| NULL | 不及格 |
+------+-----------+
2 rows in set (0.00 sec)