文章目录
一、MySQL进阶查询
1.order by——按关键字排序
- 使用ORDER BY 语句来实现排序
- 排序可针对一个或多个字段
- ASC:升序,默认排序方式
- DESC:降序
语法结构:
- SELECT column1, column2,…FROM table_name ORDER BY column1, column2,…ASC|DESC;
1.1 单字段排序
基于score字段 升序
mysql> select * from nb order by score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 1 |
| 2 | wangwu | 2 |
| 3 | lisi | 3 |
| 4 | xiao | 4 |
| 5 | hu | 5 |
| 8 | ff | 8 |
| 10 | gg | 10 |
+----+----------+-------+
7 rows in set (0.00 sec)
基于score字段 降序
mysql> select * from nb order by score desc;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 10 | gg | 10 |
| 8 | ff | 8 |
| 5 | hu | 5 |
| 4 | xiao | 4 |
| 3 | lisi | 3 |
| 2 | wangwu | 2 |
| 1 | zhangsan | 1 |
+----+----------+-------+
7 rows in set (0.00 sec)
1.2 多字段排序
多个字段排序时,优先排序前一个字段
score字段先降序排序,然后和score字段相同的在对id字段进行排序
mysql> select * from nb order by score desc,id desc;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 10 | gg | 10 |
| 8 | ff | 8 |
| 5 | hu | 5 |
| 4 | xiao | 4 |
| 3 | lisi | 3 |
| 2 | wangwu | 2 |
| 1 | zhangsan | 1 |
+----+----------+-------+
7 rows in set (0.00 sec)
注意:
oredr by 前面可以加where,只有满足条件时,where才会生效,所以 2=2,条件生效,但是一般不加。
mysql> select id,score from nb where 2=2 order by score desc,id desc;
+----+-------+
| id | score |
+----+-------+
| 10 | 10 |
| 8 | 8 |
| 5 | 5 |
| 4 | 4 |
| 3 | 3 |
| 2 | 2 |
| 1 | 1 |
+----+-------+
7 rows in set (0.00 sec)
2.group by——对结果进行分组
- 使用 GROUP BY 来实现分组
- 通常结合聚合函数一起使用
- 可以按一个或多个字段对结果进行分组
语法结构
- SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;
2.1 单个分组
mysql> select score,count(name) from nb group by score;
+-------+-------------+
| score | count(name) |
+-------+-------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 8 | 1 |
| 10 | 1 |
+-------+-------------+
7 rows in set (0.00 sec)
2.2 分组结合order by使用
mysql> select score,count(name) from nb group by score order by count(name);
+-------+-------------+
| score | count(name) |
+-------+-------------+
| 4 | 1 |
| 5 | 1 |
| 10 | 1 |
| 8 | 1 |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+-------+-------------+
7 rows in set (0.00 sec)
3.limit——限制结果条目
- 只返回SELECT查询结果的第一行或前几行
- 使用LIMIT语句限制条目
语法结构
- SELECT column1, column2, … FROM table_name LIMIT [offset,] number
1.limit 查询前三行
mysql> select * from nb limit 3;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 1 |
| 2 | wangwu | 2 |
| 3 | lisi | 3 |
+----+----------+-------+
3 rows in set (0.00 sec)
2.limit查询 从第二行开始,往下数4行
mysql> select * from nb limit 2,4;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 3 | lisi | 3 |
| 4 | xiao | 4 |
| 5 | hu | 5 |
| 10 | gg | 10 |
+----+------+-------+
4 rows in set (0.00 sec)
4.as——设置别名
- 使用AS语句设置别名时,关键字AS可省略。
- 设置别名时,保证不能与库中其他表或字段名起冲突
语法结构
- SELECT column_name AS alias_name FROM table_name;
1.给字段设置别名
mysql> select id as 学号,name as 姓名 from nb;
+--------+----------+
| 学号 | 姓名 |
+--------+----------+
| 1 | zhangsan |
| 2 | wangwu |
| 3 | lisi |
| 4 | xiao |
| 5 | hu |
| 10 | gg |
| 8 | ff |
+--------+----------+
7 rows in set (0.00 sec)
2.不加as语法也可以设置别名
mysql> select id 学号,name 姓名 from nb;
+--------+----------+
| 学号 | 姓名 |
+--------+----------+
| 1 | zhangsan |
| 2 | wangwu |
| 3 | lisi |
| 4 | xiao |
| 5 | hu |
| 10 | gg |
| 8 | ff |
+--------+----------+
7 rows in set (0.00 sec)
对于表设置,一般在多表查询时使用
给nb表设置别名t,然后在id和name和score前面也要加t使用,不加as也可以使用
mysql> select t.id as 学号,t.name as 姓名,t.score as 成绩 from nb as t;
+--------+----------+--------+
| 学号 | 姓名 | 成绩 |
+--------+----------+--------+
| 1 | zhangsan | 1 |
| 2 | wangwu | 2 |
| 3 | lisi | 3 |
| 4 | xiao | 4 |
| 5 | hu | 5 |
| 10 | gg | 10 |
| 8 | ff | 8 |
+--------+----------+--------+
7 rows in set (0.00 sec)
4.1 as作为连接语句
创建新表tmp,并把nb表的结构和数据导入进新表中
mysql> create table tmp as select * from nb;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from tmp;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 1 |
| 2 | wangwu | 2 |
| 3 | lisi | 3 |
| 4 | xiao | 4 |
| 5 | hu | 5 |
| 10 | gg | 10 |
| 8 | ff | 8 |
+----+----------+-------+
7 rows in set (0.01 sec)
可以对比一下原表与新生成的表,可以看到新生成的表中原来的主键与自增长都没有
5.通配符
- 用于替换字符串中的部分字符
- 通常与like一起使用,并协同where完成查询
- 常用通配符
- %:表示零个、一个或多个
- _:表示单个字符
5.1 基于 % 号通配符使用的方法
mysql> select * from nb where name like 'wang%';
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 2 | wangwu | 2 |
+----+--------+-------+
1 row in set (0.00 sec)
5.2 基于 " _ " 通配符使用的方法
mysql> select * from nb where name like 'w_ng_u';
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 2 | wangwu | 2 |
+----+--------+-------+
1 row in set (0.00 sec)
6.子查询
- 也称为内查询或嵌套查询
- 先于主查询被执行,其结果将作为外层主查询的条件
- 在增删改查中都可以使用子查询
- 支持多层嵌套
- IN语句是用来判断某个值是否在给定的结果集中
1.先查nb表中id,然后反馈给where后的id,在进行外层的查询。
子查询是由内到外的查询
mysql> select * from tmp where id in (select id from nb);
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 1 |
| 2 | wangwu | 2 |
| 3 | lisi | 3 |
| 4 | xiao | 4 |
| 5 | hu | 5 |
| 10 | gg | 10 |
| 8 | ff | 8 |
+----+----------+-------+
7 rows in set (0.00 sec)
2.多层嵌套
当多层嵌套(从内部括号到外面匹配),每一层的内外字段名要一致才可以,否则报错
mysql> select * from tmp where id in (select id from nb where name in (select name from nb));
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 1 |
| 2 | wangwu | 2 |
| 3 | lisi | 3 |
| 4 | xiao | 4 |
| 5 | hu | 5 |
| 10 | gg | 10 |
| 8 | ff | 8 |
+----+----------+-------+
7 rows in set (0.00 sec)
3.判断有误lisi这个值,如有,则执行前面的查询语句,查看tmp表中有多少行
mysql> select count(*) from tmp where exists (select * from tmp where name='lisi');
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
7.NULL值
- 表示缺失的值
- 与数字0或者空白是不同的
- 使用 IS NULL 或 IS NOT NULL 进行判断
NULL值和空值的区别
- 空值长度为0,不占空间;NULL值的长度为NULL,占用空间
- IS NULL 无法判断空值
- 空值使用 “=” 或 “<>” 来处理(“<>”不等于)
- COUNT()计算,NULL会忽略,空值加入计算
1.向tmp表中添加一个addr字段
mysql> alter table tmp add column addr varchar(48);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tmp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| score | int(10) | YES | | NULL | |
| addr | varchar(48) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2.查看tmp表,可以看到addr字段值全是NULL
mysql> select * from tmp;
+----+----------+-------+------+
| id | name | score | addr |
+----+----------+-------+------+
| 1 | zhangsan | 1 | NULL |
| 2 | wangwu | 2 | NULL |
| 3 | lisi | 3 | NULL |
| 4 | xiao | 4 | NULL |
| 5 | hu | 5 | NULL |
| 10 | gg | 10 | NULL |
| 8 | ff | 8 | NULL |
+----+----------+-------+------+
7 rows in set (0.00 sec)
用count()计算addr出现多少次,可以看到是0次,如上述所说的NULL计算会被忽略吊
mysql> select count(addr) from tmp;
+-------------+
| count(addr) |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
8.正则表达式
- MySQL 正则表达式通常是在检索数据库记录的时候,根据指定的匹配模式匹配记录中符合要求的特殊字符串。
- MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式,REGEXP 操作符所支持的匹配模式
1.查询指定字符串开头的信息记录
mysql> select * from tmp where name regexp '^Z';
+----+----------+-------+------+
| id | name | score | addr |
+----+----------+-------+------+
| 1 | zhangsan | 1 | NULL |
+----+----------+-------+------+
1 row in set (0.00 sec)
9.运算符
9.1 MySQL分为四种运算符
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
9.2 算术运算符
mysql> select 1+2 as 加法,3-2 as 减法,1*3 as 乘法,6/2 as 除法,9%2 as 取余;
+--------+--------+--------+--------+--------+
| 加法 | 减法 | 乘法 | 除法 | 取余 |
+--------+--------+--------+--------+--------+
| 3 | 1 | 3 | 3.0000 | 1 |
+--------+--------+--------+--------+--------+
1 row in set (0.00 sec)
9.3 比较运算符
- 比较运算符是查询数据记录时经常使用的一类运算符。
- 通过使用比较运算符可以判断出 表中有哪些记录是符合条件的,如果比较的结果为真则返回 1,如果为假则返回 0,比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的,如果要区分大小写可以通过 binary 关键字来实现。常用的比较运算符
例:- 等号(=)是用来判断数字、字符串和表达式是否相等的,如果相等则返回1,如果不相等则返回0。
- 如果比较的两者有一个值是 NULL,则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的,如果 ASCII 码相等,则表示两个字符相同;如果ASCII码不相等,则表示两个字符不相同。
mysql> select 2=2,2='3','z'='z',(2+1)=(4-1),'r'=NULL;
+-----+-------+---------+-------------+----------+
| 2=2 | 2='3' | 'z'='z' | (2+1)=(4-1) | 'r'=NULL |
+-----+-------+---------+-------------+----------+
| 1 | 0 | 1 | 1 | NULL |
+-----+-------+---------+-------------+----------+
1 row in set (0.01 sec)
从以上查询可以看出来:
- 如果两者都是整数,则按照整数值进行比较。
- 如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比较。
- 如果两者都是字符串,则按照字符串进行比较。
- 如果两者中至少有一个值是 NULL,则比较的结果是 NULL。
9.4 逻辑运算符
逻辑运算符又被称为布尔运算符,通常用来判断表达式的真假,如果为真返回1,否则返回0,真和假也可以用TRUE和FALSE表示。MySQL中支持使用的逻辑运算符有四种
9.4.1 逻辑非
- 逻辑运算符中最简单的运算符就是逻辑非,逻辑非使用NOT或!表示。逻辑非将跟在它后面的逻辑测试取反,把真变为假,把假变为真。如果NOT后面的操作数为0时,所得值为1;如果操作数为非0时,所得值为0;如果操作数为NULL时,所得值为NULL。例如,对非0值和0值分别作逻辑非运算。
mysql> select not 2,!3,not 0,!0,not (6-5),!(2-2);
+-------+----+-------+----+-----------+--------+
| not 2 | !3 | not 0 | !0 | not (6-5) | !(2-2) |
+-------+----+-------+----+-----------+--------+
| 0 | 0 | 1 | 1 | 0 | 1 |
+-------+----+-------+----+-----------+--------+
1 row in set (0.00 sec)
9.4.2 逻辑与
- 逻辑与通常用于判断两个值或多个值的有效性,如果所有值都是真返回1,否则返回O逻辑与使用AND或者&&表示。例如,对非О值、О值和NULL值分别作逻辑与运算,
0 && NULL 为 0, 1 && NULL 为 NULL
mysql> select 1 && 2,3 and 4,5 && null,0 and 2;
+--------+---------+-----------+---------+
| 1 && 2 | 3 and 4 | 5 && null | 0 and 2 |
+--------+---------+-----------+---------+
| 1 | 1 | NULL | 0 |
+--------+---------+-----------+---------+
1 row in set (0.00 sec)
9.4.3 逻辑或
- 逻辑或表示包含的操作数,任意一个为非零值并且不是NULL值时,返回1,否则返回0。逻辑或通常使用OR或者|来表示。例如,对非0值、0值和NULL值分别作逻辑或运算。
mysql> select 0 or 1,1 or 2,0 or 0;
+--------+--------+--------+
| 0 or 1 | 1 or 2 | 0 or 0 |
+--------+--------+--------+
| 1 | 1 | 0 |
+--------+--------+--------+
1 row in set (0.00 sec)
注意!!!
数值之间做 或 的判断时,用 or,不要用 ||,会出问题
例:
mysql> select 1 or 2,3 || 4;
+--------+--------+
| 1 or 2 | 3 || 4 |
+--------+--------+
| 1 | 34 |
+--------+--------+
1 row in set (0.00 sec)
9.4.4 逻辑异或
- 两个非NULL值的操作数,如果两者都是0或者都是非0,则返回0;如果一个为0,另一个为非0,则返回结果为1;当任意一个值为NULL时,返回值为NULL。例如,对非0值、0值和NULL值分别作逻辑异或运算
mysql> select 1 xor 0,2 xor 2,0 xor 0,4 xor null,2 xor null;
+---------+---------+---------+------------+------------+
| 1 xor 0 | 2 xor 2 | 0 xor 0 | 4 xor null | 2 xor null |
+---------+---------+---------+------------+------------+
| 1 | 0 | 0 | NULL | NULL |
+---------+---------+---------+------------+------------+
1 row in set (0.00 sec)
9.5 位运算符
最后在将计算结果从二进制变回到十进制格式,方便用户查看。MySQL支持6种位运算符位运算符实际上是对二进制数进行计算的运算符。MySQL内位运算会先将操作数变成二进制格式,然后进行位运算
10转换为二进制数是1010,15转换为二进制数是1111。
- 1、按位与运算(&),是对应的二进制位都是1的,它们的运算结果为1,否则为0,所以10&15的结果为10。
- 2、按位或运算(|),是对应的二进制位有一个或两个为1的,运算结果为1,否则为0,所以10|15的结果为15。
- 3、按位异或运算( ^ ),是对应的二进制位不相同时,运算结果1,否则为0,所以10^15的结果为5。
- 4、按位取反(~),是对应的二进制数逐位反转,即1取反后变为0,0取反后变为1。数字1的二进制是0001,取反后变为1110,数字5的二进制是0101,将1110和0101
9.5.1 & 按位与运算
mysql> select 10 & 15;
+---------+
| 10 & 15 |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)
9.5.2 | 按位或运算
mysql> select 10 | 15;
+---------+
| 10 | 15 |
+---------+
| 15 |
+---------+
1 row in set (0.00 sec)
9.5.3 ^ 按位异或运算
mysql> select 10 ^ 15;
+---------+
| 10 ^ 15 |
+---------+
| 5 |
+---------+
1 row in set (0.00 sec)
9.5.4 按位反运算
按位与和按位反运算相结合
mysql> select 10 &~ 5;
+---------+
| 10 &~ 5 |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)
9.5.5 按左位移运算
mysql> select 1 << 3;
+------+
| 1<<3 |
+------+
| 8 |
+------+
1 row in set (0.00 sec)
转换成二进制进行运算
1是 0001
左移3位 1000 =8
9.5.6 按右位移运算
mysql> select 15 >> 3;
+---------+
| 15 >> 3 |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
转换成二进制进行运算
15是 1111
右移3位 0001 =1
10.内连接
两张或多张表中同时符合某种条件的数据记录组合
- FROM子句中使用INNERJOIN关键字连接多张表,并使用ON设置连接条件
- 是系统默认的表连接方式,可以省略INNER关键字
- 多表支持连续使用INNERJOIN,建议不超过三个表
语法结构:
- SELECT column name(s) FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
实现原理:
mysql> select * from nb;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 1 |
| 2 | wangwu | 2 |
| 3 | lisi | 3 |
| 4 | xiao | 4 |
| 5 | hu | 5 |
| 10 | gg | 10 |
| 8 | ff | 8 |
+----+----------+-------+
7 rows in set (0.00 sec)
mysql> select * from xueke;
+----+-----------+
| id | name |
+----+-----------+
| 1 | java |
| 2 | 前端 |
| 3 | UI |
| 4 | 云计算 |
| 5 | IT |
| 6 | 体育 |
| 7 | 数学 |
| 10 | 英语 |
| 11 | 语文 |
+----+-----------+
9 rows in set (0.00 sec)
mysql> select i.id,i.name,i.score,x.name from nb i inner join xueke x on i.score=x.id;
+----+----------+-------+-----------+
| id | name | score | name |
+----+----------+-------+-----------+
| 1 | zhangsan | 1 | java |
| 2 | wangwu | 2 | 前端 |
| 3 | lisi | 3 | UI |
| 4 | xiao | 4 | 云计算 |
| 5 | hu | 5 | IT |
| 10 | gg | 10 | 英语 |
+----+----------+-------+-----------+
6 rows in set (0.00 sec)
10.1 左连接
也被称为左外连接
- 在FROM子句中使用LEFTJOIN关键字来表示
- 匹配左表中所有行及右表中符合条件的行
实现原理:
用的是内连接的表1和表2
没有匹配的项用 NULL值代替
mysql> select i.id,i.name,i.score,x.name from nb i left join xueke x on i.score=x.id;
+----+----------+-------+-----------+
| id | name | score | name |
+----+----------+-------+-----------+
| 1 | zhangsan | 1 | java |
| 2 | wangwu | 2 | 前端 |
| 3 | lisi | 3 | UI |
| 4 | xiao | 4 | 云计算 |
| 5 | hu | 5 | IT |
| 10 | gg | 10 | 英语 |
| 8 | ff | 8 | NULL |
+----+----------+-------+-----------+
7 rows in set (0.00 sec)
10.2 右连接
也被称为右外连接
- 在FROM子句中使用RIGHTJOIN关键字来表示
- 匹配右表中所有行及左表中符合条件的行
实现原理:
用的是内连接的表1和表2
没有匹配的项用 NULL值代替
mysql> select i.id,i.name,i.score,x.name from nb i right join xueke x on i.score=x.id;
+------+----------+-------+-----------+
| id | name | score | name |
+------+----------+-------+-----------+
| 1 | zhangsan | 1 | java |
| 2 | wangwu | 2 | 前端 |
| 3 | lisi | 3 | UI |
| 4 | xiao | 4 | 云计算 |
| 5 | hu | 5 | IT |
| 10 | gg | 10 | 英语 |
| NULL | NULL | NULL | 体育 |
| NULL | NULL | NULL | 数学 |
| NULL | NULL | NULL | 语文 |
+------+----------+-------+-----------+
9 rows in set (0.00 sec)