目录
MySQL进阶查询
在 MySQL 中,可以使用 SELECT 语句来查询数据。查询数据是指从数据库中根据需求,使用不同的查询方式来获取不同的数据,是使用频率最高、最重要的操作。
SELECT * FROM 表名;
SELECT 列名 FROM 表名;
一、关键字排序
- 使用ORDER BY语句来实现排序排序
- 可针对一个或多个字段
- ASC:升序,默认排序方式
- DESC:降序
- ORDER BY的语法结构
order by的语法结构:
select 字段1,字段2 from 表名 order by 字段1 desc|asc,字段2 desc|asc;
mysql> create database vvv; #创建库
create table vvv(xuehao char(10) not null,nianling int(3) not null,xingming char(36) not null,chengji varchar(3) not null) #建表
insert into test values(201001,17,'zhangsan',60),(201002,18,'lisi',70),(201003,19,'wangwu',80),(201004,18,'zhaoliu',88),(201005,17,'lilei',55); #输入数据
默认升序:
mysql> select chengji from vvv order by chengji; #vvv库chengji字段默认升序
降序:
mysql> select chengji from vvv order by chengji desc; #vvv库chengji字段降序
加入条件的排序:
mysql> select xingming,chengji from vvv where chengji>=60 order by chengji desc; #显示vvv库里xingming和chengji字段chengji大于60并chengji字段降序
多条件的排序:
mysql> select * from vvv order by nianling desc,chengji desc; #显示vvv库所有字段并nianling和chengji降序
二、对结果进行分组
- 使用GROUP BY语句来实现分组
- 通常结合聚合函数一起使用
- 可以按一个或多个字段对结果进行分组
- GROUP BY的语法结构
select count(name),score from cj where score>=85 group by score;
#按score进行分组,count统计次数,score大于等于85的score相同的name数量
增加按分数降序:
mysql> select count(name),score from cj where score>=80 group by score order by score desc; #统计显示score大于80的的那么,score降序
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 1 | 95 |
| 1 | 80 |
+-------------+-------+
2 rows in set (0.00 sec)
三、限制结果条目
-
只返回select查询结果的第一行或前几行
-
使用limit语句限制条目
-
limit语法结构:
select 字段1,字段2 from 表名 limit [offset,] number; offset:位置偏移量,从0开始 number:返回记录行的最大数目
select * from cj limit 3; #显示三行数据
select * from cj limit 1,3; #从第2行开始,显示三行,从0开始计数
select * from cj order by score desc limit 3; #显示score最大的三行
四、设置别名
-
使用AS语句设置别名,关键字AS可省略
-
设置别名时,保证不能与库中其他表或字段名称冲突
-
别名的语法结构
字段别名: select 字段 as 别名 from 表名; 表的别名: select 字段 from 表名 as 别名;
select name as mingzi from cj; #cj表的name别名mingzi
五、通配符
-
用于替换字符串中的部分字符
-
通常配合LIKE一起使用,并协同WHERE完成查询
-
常用通配符
%表示零个、一个或多个 _表示单个字符
select * from cj where name like 'w%'; #匹配w开头的任意字符
select * from cj where name like 'zhaoli_'; #匹配zhaoli后一位字符
select * from cj where name like 't_____'; #匹配t开头5个未知字符
select * from cj where name like '_i%'; #匹配一位未知字符,i后任意字符
六、子查询
- 也称作内查询或者嵌套查询
- 先于主查询被执行,其结果将作为外层主查询的条件
- 在增删改查中都可以使用子查询
- 支持多层嵌套
- IN语句是用来判断某个值是否在给定的结果集中
附:
- 子查询上也可以使用比较运算符(=、<、>、>=、<=),这些运算符主要是对运算符前面的表达式和后面的子查询进行比较运算。例如,查询出名字是shirley的记录,并输出其ID、名字和等级信息。
- EXIST这个关键字在子查询时,主要用于判断子查询的结果集是否为空。如果不为空,则返回TRUE;反之,则返回FALSE。例如,先通过子查询判断返回是否为TRUE,如果用户shirley存在,则计算整个tmp表的总记录数量。
select id,name from cj where id in(1,2); #查看id,name,只看id为1和2的值
select id,name from cj where id in(select id from cj where id>3);
select score,name from cj where score in(select score from cj where name='ada');
#查看score、name,只看name为ada的score,score为85,mengwu的score也为85,所以有两项值
select score,name from cj where score =(select score from cj where name='ada');
#这里=和in同理
select score,name from cj where score !=(select score from cj where name='ada');
#不等于
select score,name from cj where score <>(select score from cj where name='ada');
#<>与!=均为不等于
!](https://img-blog.csdnimg.cn/20201228225435304.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2thbmdfMDE=,size_16,color_FFFFFF,t_70)
多层嵌套
select、update、delete都支持多层嵌套
select id,name from cj where id in (select id from (select id from cj where name='zhaosan') cj); #最内层的值传递给上层,一次上传
exists的用法:
select * from cj where exists (select * from cj where score=85); #exists与if类似,后面的语句执行成功,在执行前面的语句
七、NULL值
-
表示缺失的值
-
与数字0或者空白(spaces)是不同的
-
使用IS NULL或IS NOT NULL进行判断
-
NULL值和空值的区别
1、空值长度为0,不占空间;NULL值的长度为NULL,占用空间 2、IS NULL无法判断空值 3、空值使用“="或者“>"来处理 4、COUNT()计算时,NULL会忽略,空值会加入计算
insert into cj (id,age,name) values(6,20,'hhh'); #表内添加数据
select * from cj;
select count(score) from cj; #count()计算时,null会忽略
select * from cj where score is null; #查询score字段为null的记录
select * from cj where score is not null; #查询score字段不为null的记录
八、正则表达式
- 根据指定的匹配模式匹配记录中符合要求的特殊字符
- 使用REGEXP关键字指定匹配模式
常用匹配模式
字符 | 作用 |
---|---|
^ | 匹配文本的开始字符 |
$ | 匹配文本的结束字符 |
. | 匹配任何单个字符 |
* | 匹配前面的字符零次或多次 |
+ | 匹配前面的字符一次或多次 |
字符串 | 匹配包含指定的字符串 |
p1lp2 | 匹配p1或p2 |
[…] | 匹配字符集合中任一字符 |
[^…] | 匹配不在括号中的任一字符 |
{n} | 匹配前面的字符串n次 |
{n,m} | 匹配前面的字符串至少n次,之多m次 |
mysql> select * from test where xingming regexp '^z'; #以什么开头
mysql> select * from test where xingming regexp 'i$'; #以什么结尾
mysql> select * from test where xingming regexp 'zha';
mysql> select * from test where xingming regexp 'zha.gsan'; #匹配单个字符
mysql> select * from test where xingming regexp '^[z|l]'; #以z或l开头
mysql> select * from test where xingming regexp '^[^z|l]'; #不以z或l开头
mysql> select * from test where xingming regexp 'e{3,5}';
mysql> select * from test where xingming regexp 'ee*';
mysql> select * from test where xingming regexp '^[d-f]';
select * from cj where name regexp 'tao|shao'; #name字段包含tao或shao的记录
select * from cj where name regexp 'z+'; #匹配z至少一次
select * from cj where name regexp '[a-z]'; #匹配a-z字符集中的任意一个
九、运算符
1.算术运算符
以select命令来实现最基础的加减乘除运算
select 2+2,2+3,3-2,3-4,3*3,6/2,7%5;
整数相除,出来的结果是浮点型的。
在除法运算和求余数运算中,除数不能为 0,若除数是 0,返回的结果则为 NULL。
需要注意的是,如果有多个运算符,按照先乘除后加减的优先级进行运算。
select 1+3*3;
select (2+2)*2;
注:
某些字符串类型的字段存储的数字型字符串,这些字段在进行算术运算时将会被自动转换为数字的值。如果字符串的开始部分是数字,在转换时将被转换为这个数字。如果是既包含字符又包含数字得的混合字符串,无法转换为数字时,将被转换为 0。
select 3*'3int';
select 3*'int3';
2.比较运算符
运算符 | 描述 |
---|---|
= | 等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
!=或<> | 不等于 |
is null | 判断一个值是否为NULL |
is not null | 判断一个值是否不为NULL |
between and | 两者之间 |
in | 在集合中 |
like | 通配符匹配 |
greatest | 两个或多个参数时返回最大值 |
least | 两个或多个参数时返回最小值 |
regext | 正则表达式 |
1.等于运算符
用来判断数字、字符串和表达式是否相等的,如果相等则返回 1,如果不相等则返回 0。其中字符的比较是根据 ASCII 码来判断的。
ASCII码表转换:
0-48,1-49,…9-57
A-65,B-66
a-97,b-98
比较规则:
如果两者都是整数,则按照整数值进行比较。
如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比较。
如果两者都是字符串,则按照字符串进行比较。
如果两者中至少有一个值是 NULL,则比较的结果是 NULL。
select 2=2,3=2,1='1','a'='b','b'='b','a'=null;
2.不等于运算符
不等于号有两种写法,分别是<>或者!=,用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1,如果相等则返回 0。
需要注意的是不等于运算符不能用于判断 NULL。
mysql> select 1!=1, 'a'!='ab',1<>2,'a'<>null;
+------+-----------+------+-----------+
| 1!=1 | 'a'!='ab' | 1<>2 | 'a'<>null |
+------+-----------+------+-----------+
| 0 | 1 | 1 | NULL |
+------+-----------+------+-----------+
1 row in set (0.00 sec)
3.大于、大于等于、小于、小于等于运算符
mysql> select 1<2,1<=2,1>2,1>=2,2>null;
+-----+------+-----+------+--------+
| 1<2 | 1<=2 | 1>2 | 1>=2 | 2>null |
+-----+------+-----+------+--------+
| 1 | 1 | 0 | 0 | NULL |
+-----+------+-----+------+--------+
1 row in set (0.00 sec)
注:
都不能用于判断NULL。
4.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)
5.between and
用于判断一个值是否落在某两个值之间
例如,判断某数字是否在另外两个数字之间,也可以判断某英文字母是否在另外两个字母之间
mysql> select 2 between 1 and 3, 'd' between 'a' and 'c';
+-------------------+-------------------------+
| 2 between 1 and 3 | 'd' between 'a' and 'c' |
+-------------------+-------------------------+
| 1 | 0 |
+-------------------+-------------------------+
1 row in set (0.00 sec)
6.least、greatest
LEAST:当有两个或者多个参数时,返回其中的最小值。如果其中一个值为 NULL,则返回结果就为 NULL。
GREATEST:当有两个或者多个参数时,返回其中的最大值。如果其中一个值为 NULL, 则返回结果就为 NULL。
select least(1,2), greatest(1,2);
select least('a','b'), greatest('a','b');
select least(1,2,null), greatest(1,2,null);
7.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)
8.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)
3.逻辑运算符
- 又称为布尔运算符
- 用来判断表达式的真假
常用的逻辑运算符
运算符 | 说明 |
---|---|
NOT或! | 逻辑非 |
AND或&& | 逻辑与 |
OR或 | |
XOR | 逻辑异或 |
逻辑非
逻辑非将跟在它后面的逻辑测试取反,把真变为假,把假变为真。
如果 NOT 后面的操作数为 0 时,所得值为 1;如果操作数为非 0 时,所得值为 0;如果操作数为 NULL 时,所得值为 NULL。
mysql> select ! 0, ! null, not 1, not 0;
+-----+--------+-------+-------+
| ! 0 | ! null | not 1 | not 0 |
+-----+--------+-------+-------+
| 1 | NULL | 0 | 1 |
+-----+--------+-------+-------+
1 row in set (0.00 sec)
注:
非0值都是1
逻辑与
如果所有值都是真返回 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
4.位运算符
- 对二进制进行计算的运算符
常用的位运算符
运算符 | 说明 |
---|---|
& | 按位与 |
按位或 | |
~ | 按位取反 |
^ | 按位异或 |
<< | 按位左移 |
>> | 按位右移 |
mysql> select 4&5,4|5,4&~3,3^4,2<<2,2>>1;
+-----+-----+------+-----+------+------+
| 4&5 | 4|5 | 4&~3 | 3^4 | 2<<2 | 2>>1 |
+-----+-----+------+-----+------+------+
| 4 | 5 | 4 | 7 | 8 | 1 |
+-----+-----+------+-----+------+------+
1 row in set (0.00 sec)
十、连接查询
通常都是将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接。
要先确定一个主表作为结果集,然后将其他表的行有选择性的连接到选定的主表结果集上。
使用较多的连接查询包括:内连接、左连接和右连接
内连接
在from子句中使用关键字 inner join 来连接多张表,并使用 on子句设置连接条件。
create table score (score char(16) not null, 判定 char(48) default'');
insert into score values(100,'满分'),(59,'不及格');
select * from score;
select cj.name, score.判定 from cj inner join score on cj.score=score.score;
内连接是系统默认的表连接,所以在 FROM 子句后可以省略 INNER 关键字,只使用关键字 JOIN。同时有多个表时,也可以连续使用 INNER JOIN 来实现多表的内连接,不过为了更好的性能,建议最好不要超过三个表。
外连接
左连接
主表在左边,主表内容会全部显示出来,在从表中没匹配到的以NULL显示出来
select cj.name, score.判定 from cj left join score on cj.score=score.score;
右连接
主表在右边,主表内容会全部显示出来,在从表中没匹配到的以NULL显示出来
select cj.name, score.判定 from cj right join score on cj.score=score.score;