MySQL的高级语言(select)
MySQL进阶查询
在 MySQL 中,可以使用 SELECT 语句来查询数据。查询数据是指从数据库中根据需求,使用不同的查询方式来获取不同的数据,是使用频率最高、最重要的操作。
SELECT * FROM 表名;
SELECT 列名 FROM 表名;
按关键字排序
使用order by语句来实现排序
排序可针对一个或多个字段
ASC:升序,默认排序方式
DESC:降序
order by的语法结构
select 字段1,字段2 from 表名 order by 字段1 desc|asc,字段2 desc|asc;
按单字段排序
排序前
降序
select name,score from cj order by score desc;
升序
select name,score from cj order by score asc;
多字段排序
主要参考字段写在前面,辅助参考字段写在后面。先比较主要参考字段,如果相同,再比较辅助参考字段
create table cj(id int(2), name varchar(128), score int(10));
insert into cj(id,name,score)values(1,'zhusan','88');
insert into cj(id,name,score)values(3,'taosi','80');
insert into cj(id,name,score)values(2,'mengwu','85');
insert into cj(id,name,score)values(4,'ada','85');
select id,name,score from cj order by id desc,score asc;
对结果进行分组
使用group by语句来实现分组
通常结合聚合函数一起使用
可以按一个或多个字段对结果进行分组
group by的语法结构
select count(name),score from cj where score>=85 group by score;
#按score进行分组,count统计次数,score大于等于85的score相同的name数量
增加按分数降序
select count(name),score from cj where score>=85 group by score order by score desc;
限制结果条目
只返回select查询结果的第一行或前几行
使用limit语句限制条目
limit语法结构:
select 字段1,字段2 from 表名 limit [offset,] number;
offset:位置偏移量,从0开始
number:返回记录行的最大数目
select * from cj limit 3; #显示三行数据
select * from cj limit 2,3; #从第三行开始,显示三行,从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;
通配符
用于替换字符串中的部分字符
通常配合like一起使用,并协同where完成查询
常用通配符
%表示零个、一个或多个即任意字符
_表示单个字符
select * from cj where name like 'b%'; #匹配b开头的任意字符
select * from cj where name like 'zhusa_'; #匹配zhusa后一位字符
select * from cj where name like 'z______';#匹配z开头6个未知字符
select * from cj where name like '_h%'; #匹配一位未知字符,h后任意字符
子查询
也称作内查询或者嵌套查询
先于主查询被执行,其结果将作为外层主查询的条件
在增删改查中都可以使用子查询
支持多层嵌套
in语句用来判断某个值是否在给定的结果集中
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');
#<>与!=均为不等于
多层嵌套
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值
null:真空(什么都没有)
‘’:空气(还有空气)
表示缺失的值
与数字0或者空白(spaces)是不同的
使用is null或is not null进行判断
null值和空值(’’)的区别
空值长度为0,不占空间;null值的长度为null,占用空间
is null无法判断空值
空值使用“=”或者“<>”来处理
count()计算时,null会忽略,空值会加入计算
insert into cj (id,name) values(7,'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次 |
示例
以特定字符串开头的记录
select * from cj where name regexp '^z'; #name字段以z开头的数据
以特定字符串结尾的记录
select * from cj where name regexp 'u$'; #name字段以u结尾的数据
包含指定字符串的记录
select * from cj where name regexp 'san'; #name字段中包含“san”的数据
以“.”代替字符串中的任意一个字符的记录
select * from cj where name regexp 'zh...'; #name字段“zh”后有任意三个字符的记录
匹配包含或者关系的记录
select * from cj where name regexp 'tao|shao'; #name字段包含tao或shao的记录
“*”匹配前面字符的任意多次
select * from cj where name regexp 'ao*'; #匹配ao字符一次或多次的记录
select * from cj where name regexp 'oa*'; #匹配oa字符一次或多次的记录
“+”匹配前面字符至少一次
select * from cj where name regexp 'z+'; #匹配z至少一次
匹配指定字符集中的任意一个
select * from cj where name regexp '[a-z]'; #匹配a-z字符集中的任意一个
运算符
用于对记录中的字段值进行运算
MySQL 的运算符共有四种,分别是:算术运算符、比较运算符、逻辑运算符和位运算符
算数运算符
运算符 | 描述 |
---|---|
+ | 加法 |
- | 减法 |
* | 乘法 |
/ | 除法 |
% | 取余数 |
以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';
比较运算符
运算符 | 描述 |
---|---|
= | 等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
!=或<> | 不等于 |
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。
select 2=2,3=2,1='1','a'='b','b'='b','a'=null;
不等于运算符
不等于号有两种写法,分别是<>或者!=,用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1,如果相等则返回 0。
需要注意的是不等于运算符不能用于判断 NULL。
select 1!=2,1<>2,'a'!='ab','ab'<>'abc',a<>null;
大于、大于等于、小于、小于等于运算符
select 1<2,1<=2,1>2,1>=2,2>null;
注:
都不能用于判断NULL。
IS NULL、IS NOT NULL
IS NULL 判断一个值是否为 NULL,如果为 NULL 返回 1,否则返回 0。
IS NOT NULL 判断一个值是否不为 NULL,如果不为 NULL 返回 1,否则返回 0。
select '1' is null, '1' is not null, null is null, null is not null;
between and
用于判断一个值是否落在某两个值之间
例如,判断某数字是否在另外两个数字之间,也可以判断某英文字母是否在另外两个字母之间
select 2 between 1 and 3, 'd' between 'a' and 'c';
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);
in、not in
IN 判断一个值是否在对应的列表中,如果是返回 1,否则返回 0。
NOT IN 判断一个值是否不在对应的列表中,如果不是返回 1,否则返回 0。
select 1 in (1,2,3), 'a' not in (1,2,3), 1 in ('a','b','c');
like、not like
LIKE 用来匹配字符串,如果匹配成功则返回 1,反之返回 0;NOT LIKE 正好跟 LIKE 相反。
LIKE 支持两种通配符:’%’ 用于匹配任意数目的字符,而’_’只能匹配一个字符。
select 'abc' like 'a%', 'abc' like 'ab_', 'ab' not like 'a_';
逻辑运算符
又被称为布尔运算符
用来判断表达式的真假
运算符 | 描述 |
---|---|
NOT或! | 逻辑非 |
AND或&& | 逻辑与 |
OR或 | |
XOR | 逻辑异或 |
逻辑非
逻辑非将跟在它后面的逻辑测试取反,把真变为假,把假变为真。
如果 NOT 后面的操作数为 0 时,所得值为 1;如果操作数为非 0 时,所得值为 0;如果操作数为 NULL 时,所得值为 NULL。
select ! 0, ! null, not 1, not 0;
注:
非0值都是1
逻辑与
如果所有值都是真返回 1,否则返回 0。
select 1 and 2, 1 && 1, 1 and null, 1 and 0, 0 && null;
逻辑或(最好用or)
逻辑或表示包含的操作数,任意一个为非零值并且不是 NULL 值时,返回 1,否则返回0。
select 1 or 1, 1 or 0, 1 or null, 0 or null, 0 or 0;
逻辑异或
两个非 NULL 值的操作数,如果两者都是 0 或者都是非 0,则返回 0;如果一个为 0, 另一个为非 0,则返回结果为 1;
当任意一个值为 NULL 时,返回值为 NULL。
select 0 xor 1, 0 xor null, 1 xor 1,0 xor 0;
总结
and运算,只要碰到0就是0,(非0和null是null)
or运算,只要碰到非0值就是1,(0和null是null)
异或运算,只要碰到null都是null
位运算符
位运算符实际上是对二进制数进行计算的运算符。
select 10&15,10|15,10^15,15&~10;
按位与运算
10 | 1010 | / |
---|---|---|
15 | 1111 | |
& | 1010 | 10 |
按位与运算(&),是对应的二进制位都是 1 的,它们的运算结果为 1,否则为 0
按位或运算
10 | 1010 | / |
---|---|---|
15 | 1111 | |
| | 1111 | 15 |
按位或运算(|),是对应的二进制位只要是 1 的,它们的运算结果就为 1,否则为 0
按位异或运算
10 | 1010 | / |
---|---|---|
15 | 1111 | |
^ | 0101 | 5 |
按位异或运算(^),是对应的二进制位不相同时,运算结果 1,否则为 0
按位取反运算
15 | 1111 | / |
---|---|---|
10 | 1010 | |
& | 1010 | 10 |
~ | 0101 | 5 |
按位取反(~),是对应的二进制数逐位反转,即 1 取反后变为 0, 0 取反后变为 1
select 1<<2,10>>2;
按位左移运算
1 | 0001 | 1 |
---|---|---|
1<<2 | 0100 | 4 |
按位右移运算
10 | 1010 | 10 |
---|---|---|
10>>2 | 0010 | 2 |
常用的运算符优先级
优先级 | 运算符 |
---|---|
1 | ! |
2 | ~ |
3 | ^ |
4 | *,/(DIV),%(MOD) |
5 | +,- |
6 | >>,<< |
7 | & |
8 | I |
9 | =,<=>,>=,<=,<>,!=,LIKE,REGEXP,IN |
10 | BETWEEN,CASE,WHEN,THEN,ELSE |
11 | NOT |
12 | &&,AND |
13 | ll,OR,XOR |
14 | := |
连接查询
通常都是将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接。
要先确定一个主表作为结果集,然后将其他表的行有选择性的连接到选定的主表结果集上。
使用较多的连接查询包括:内连接、左连接和右连接
内连接
在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;