MYSQL 进阶查询 高级语句
程序员文章站
2024-03-21 17:58:28
...
目录
一、按关键字排序
1.1、使用ORDER BY语句来实现排序
1.2、排序可针对一个或多个字段
1.3、ASC：升序，默认排序方式
1.4、DESC：降序
1.5、ORDER BY的语法结构
语法：select column1，column2，… from 库名 order by column1,column,… asc|desc;
语句使用
mysql -uroot -p123123
mysql> create database score;
mysql> use score;
mysql> create table aaa(xuehao int(6) not null primary key,nianling int(3) not null,xingming char(20) not null,chengji int(3) not null);
mysql> insert into aaa values(100,17,'zhaosi',60),(102,17,'liuneng',95),(103,18,'guangkun',70),(104,18,'dajiao',80),(105,19,'feiji',55);
mysql> select * from aaa;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 100 | 17 | zhaosi | 60 |
| 102 | 17 | liuneng | 95 |
| 103 | 18 | guangkun | 70 |
| 104 | 18 | dajiao | 80 |
| 105 | 19 | feiji | 55 |
+--------+----------+----------+---------+
5 rows in set (0.01 sec)
升序
mysql> select chengji from aaa order by chengji asc; 默认是asc升序，可以不加
+---------+
| chengji |
+---------+
| 55 |
| 60 |
| 70 |
| 80 |
| 95 |
+---------+
5 rows in set (0.00 sec)
降序
mysql> select chengji from aaa order by chengji desc;
+---------+
| chengji |
+---------+
| 95 |
| 80 |
| 70 |
| 60 |
| 55 |
+---------+
5 rows in set (0.00 sec)
按单字段排序
mysql> select xuehao,xingming,chengji from aaa order by chengji;
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 105 | feiji | 55 |
| 100 | zhaosi | 60 |
| 103 | guangkun | 70 |
| 104 | dajiao | 80 |
| 102 | liuneng | 95 |
+--------+----------+---------+
5 rows in set (0.00 sec)
按多字段排序
mysql> select xuehao,chengji from aaa order by chengji,nianling;
+--------+---------+
| xuehao | chengji |
+--------+---------+
| 105 | 55 |
| 100 | 60 |
| 103 | 70 |
| 104 | 80 |
| 102 | 95 |
+--------+---------+
5 rows in set (0.00 sec)
二、对结果进行分组
2.1、使用GROUP BY语句来实现分组
2.2、通常结合聚合函数一起使用
2.3、可以按一个或多个字段对结果进行分组
2.4、GROUP BY分组
mysql> select count(xingming),nianling from aaa group by nianling;
+-----------------+----------+
| count(xingming) | nianling |
+-----------------+----------+
| 2 | 17 |
| 2 | 18 |
| 1 | 19 |
+-----------------+----------+
3 rows in set (0.00 sec)
GROUP BY结合ORDER BY
mysql> select count(xingming),nianling from aaa group by nianling order by nianling desc;
+-----------------+----------+
| count(xingming) | nianling |
+-----------------+----------+
| 1 | 19 |
| 2 | 18 |
| 2 | 17 |
+-----------------+----------+
3 rows in set (0.01 sec)
三、限制结果条目
3.1、只返回select查询结果的第一行或第几行
3.2、使用limit语句限制条目
3.3、limit语法结构
mysql> select * from aaa limit 3;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 100 | 17 | zhaosi | 60 |
| 102 | 17 | liuneng | 95 |
| 103 | 18 | guangkun | 70 |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)
mysql> select * from aaa limit 1,3;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 102 | 17 | liuneng | 95 |
| 103 | 18 | guangkun | 70 |
| 104 | 18 | dajiao | 80 |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)
从第一行开始，显示后3行
四、设置别名
4.1、使用AS语句设置别名，关键字AS可省略
4.2、设置别名时，保证不能与库中其他表或字段名称冲突
4.3、别名的语法结构
mysql> select t.xuehao as 学号,t.nianling as 年龄,t.xingming as 姓名,t.chengji as 成绩 from aaa as t;
+--------+--------+----------+--------+
| 学号 | 年龄 | 姓名 | 成绩 |
+--------+--------+----------+--------+
| 100 | 17 | zhaosi | 60 |
| 102 | 17 | liuneng | 95 |
| 103 | 18 | guangkun | 70 |
| 104 | 18 | dajiao | 80 |
| 105 | 19 | feiji | 55 |
+--------+--------+----------+--------+
5 rows in set (0.00 sec)
4.4、as作为连接语句
mysql> create table bbb as select * from aaa;
Query OK, 5 rows affected (0.41 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from bbb;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 100 | 17 | zhaosi | 60 |
| 102 | 17 | liuneng | 95 |
| 103 | 18 | guangkun | 70 |
| 104 | 18 | dajiao | 80 |
| 105 | 19 | feiji | 55 |
+--------+----------+----------+---------+
5 rows in set (0.00 sec)
五、通配符的使用
5.1、用于替换字符串中的部分字符
5.2、通常配合like一起使用，并协同where完成查询
5.3、常用通配符
5.3.1、%：表示0个，1个或多个
5.3.2、_：表示单个字符
mysql> select xingming,xuehao from aaa where xingming like 'l%';
+----------+--------+
| xingming | xuehao |
+----------+--------+
| liuneng | 102 |
+----------+--------+
1 row in set (0.00 sec)
六、子查询
6.1、也称作内查询或者嵌套查询
6.2、先于主查询被执行，其结果将作为外层查询的条件
6.3、在增删改查中都可以使用子查询
6.4、支持多层嵌套
6.5、IN语句是用来判断某个值是否在给定的结果集中
6.6、子查询的用法
查询：
mysql> select xuehao as 学号,chengji as 成绩 from aaa where chengji in (select chengji from aaa where chengji >=60);
+--------+--------+
| 学号 | 成绩 |
+--------+--------+
| 100 | 60 |
| 102 | 95 |
| 103 | 70 |
| 104 | 80 |
+--------+--------+
4 rows in set (0.00 sec)
查询结合降序使用：
mysql> select xuehao as 学号,chengji as 成绩 from aaa where chengji in (select cheng aaa where chengji >=60) order by chengji desc;
+--------+--------+
| 学号 | 成绩 |
+--------+--------+
| 102 | 95 |
| 104 | 80 |
| 103 | 70 |
| 100 | 60 |
+--------+--------+
4 rows in set (0.00 sec)
插入：
mysql> create table score as select * from aaa;
mysql> delete from score;
mysql> select * from score;
mysql> insert into score select * from aaa where chengji in (select chengji from aaa where chengji >=80);
mysql> select * from score;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 102 | 17 | liuneng | 95 |
| 104 | 18 | dajiao | 80 |
+--------+----------+----------+---------+
2 rows in set (0.00 sec)
修改：
mysql> alter table score add column num int(3);
mysql> desc score;
mysql> update score set num=101 where chengji in (select chengji from aaa where chengji >=80);
mysql> select * from score;
+--------+----------+----------+---------+------+
| xuehao | nianling | xingming | chengji | num |
+--------+----------+----------+---------+------+
| 102 | 17 | liuneng | 95 | 101 |
| 104 | 18 | dajiao | 80 | 101 |
+--------+----------+----------+---------+------+
删除：
mysql> delete from aaa where chengji in(select chengji from (select *from aaa where chengji >=75)a);
mysql> select * from aaa;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 100 | 17 | zhaosi | 60 |
| 103 | 18 | guangkun | 70 |
| 105 | 19 | feiji | 55 |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)
七、NULL值
7.1、表示缺失的值
7.2、与数字0或者空白（spaces）是不同的
7.3、使用IS NULL或IS NOT NULL进行判断
7.4、NULL值和空值的区别
7.4.1、空值长度为0，不占空间；NULL值的长度为NULL，占用空间
7.4.2、IS NULL无法判断空值
7.4.3、空值使用“=”或者“<>”来处理
7.4.4、COUNT（）计算时，NULL会忽略，空值会加入计算
插入空值：
mysql> alter table score add column class varchar(16);
mysql> select * from score;
mysql> insert into score values(108,19,'xiaohei',80,102,'');
mysql> select * from score;
+--------+----------+----------+---------+------+-------+
| xuehao | nianling | xingming | chengji | num | class |
+--------+----------+----------+---------+------+-------+
| 102 | 17 | liuneng | 95 | 101 | NULL |
| 104 | 18 | dajiao | 80 | 101 | NULL |
| 108 | 19 | xiaohei | 80 | 102 | |
+--------+----------+----------+---------+------+-------+
3 rows in set (0.00 sec)
null的用法：
mysql> select * from score where class is null;
+--------+----------+----------+---------+------+-------+
| xuehao | nianling | xingming | chengji | num | class |
+--------+----------+----------+---------+------+-------+
| 102 | 17 | liuneng | 95 | 101 | NULL |
| 104 | 18 | dajiao | 80 | 101 | NULL |
+--------+----------+----------+---------+------+-------+
2 rows in set (0.00 sec)
mysql> select * from score where class is not null;
+--------+----------+----------+---------+------+-------+
| xuehao | nianling | xingming | chengji | num | class |
+--------+----------+----------+---------+------+-------+
| 108 | 19 | xiaohei | 80 | 102 | |
+--------+----------+----------+---------+------+-------+
1 row in set (0.00 sec)
八、正则表达式
8.1、根据指定的匹配模式匹配记录中符合要求的特殊字符
8.2、使用REGEXP关键字指定匹配模式
8.3、常用匹配模式
以x开头的姓名：(^)
mysql> select xuehao,xingming,chengji from score where xingming regexp '^x';
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 108 | xiaohei | 80 |
+--------+----------+---------+
1 row in set (0.00 sec)
以i结尾的姓名：($)
mysql> select xuehao,xingming,chengji from score where xingming regexp 'i$';
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 108 | xiaohei | 80 |
+--------+----------+---------+
1 row in set (0.00 sec)
匹配单个字符（.）
mysql> select xuehao,xingming,chengji from score where xingming regexp 'xiaohe.';
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 108 | xiaohei | 80 |
+--------+----------+---------+
1 row in set (0.00 sec)
匹配前面字符至少1次（+）
```javascript
insert into score values(108,20,'lio',75,103,''),(109,20,'lioo',85,104,''),(20109,20,'liooo',55,105,'');
mysql> select xuehao,xingming,chengji from score where xingming regexp 'lioo+';
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 109 | lioo | 85 |
| 20109 | liooo | 55 |
+--------+----------+---------+
2 rows in set (0.00 sec)
匹配任意个前面的字符(*)
mysql> select xuehao,xingming,chengji from score where xingming regexp 'lio*';
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 102 | liuneng | 95 |
| 108 | lio | 75 |
| 109 | lioo | 85 |
| 20109 | liooo | 55 |
+--------+----------+---------+
4 rows in set (0.00 sec)
匹配p1或p2(p1|p2)
mysql> select xuehao,xingming,chengji from score where xingming regexp 'l|n';
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 102 | liuneng | 95 |
| 108 | lio | 75 |
| 109 | lioo | 85 |
| 20109 | liooo | 55 |
+--------+----------+---------+
4 rows in set (0.00 sec)
匹配字符集中括号内的任何字符([...])
mysql> select xuehao,xingming,chengji from score where xingming regexp '[hz]';
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 108 | xiaohei | 80 |
+--------+----------+---------+
1 row in set (0.00 sec)
匹配前面的字符串n次{n}
mysql> select xuehao,xingming,chengji from score where xingming regexp 'o{2,3}';
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 109 | lioo | 85 |
| 20109 | liooo | 55 |
+--------+----------+---------+
2 rows in set (0.00 sec)
九、算术运算符
9.1、MySQL支持的算术运算符
mysql> select 5+2 as addition,8-5 as subtraction,6*7 as multiplication,8/2 as division,9%4 as remainder;
+----------+-------------+----------------+----------+-----------+
| addition | subtraction | multiplication | division | remainder |
+----------+-------------+----------------+----------+-----------+
| 7 | 3 | 42 | 4.0000 | 1 |
+----------+-------------+----------------+----------+-----------+
1 row in set (0.00 sec)
9.2、比较运算符
9.2.1、字符串的比较默认不区分大小写，可使用binary来区分
9.2.2、常用比较运算符
mysql> select 2=4,2='2','e'='e',(4+4)=(5+3),'n'=NULL;
+-----+-------+---------+-------------+----------+
| 2=4 | 2='2' | 'e'='e' | (4+4)=(5+3) | 'n'=NULL |
+-----+-------+---------+-------------+----------+
| 0 | 1 | 1 | 1 | NULL |
+-----+-------+---------+-------------+----------+
1 row in set (0.01 sec)
从以上查询可以看出：
①如果两者都是整数，则按整数值进行比较
②如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较
③如果两者都是字符串，则按照字符串进行比较
④如果两者中至少有一个值是NULL，则比较的结果是NULL
mysql> select 2>4,2<'2', 'e'>='e',(4+4)<=(5+3);
+-----+-------+----------+--------------+
| 2>4 | 2<'2' | 'e'>='e' | (4+4)<=(5+3) |
+-----+-------+----------+--------------+
| 0 | 0 | 1 | 1 |
+-----+-------+----------+--------------+
1 row in set (0.00 sec)
mysql> select 2!=4,null is null,null is not null,2 between 1 and 4;
+------+--------------+------------------+-------------------+
| 2!=4 | null is null | null is not null | 2 between 1 and 4 |
+------+--------------+------------------+-------------------+
| 1 | 1 | 0 | 1 |
+------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> select greatest (5,8,12),least (1,5,4);
+-------------------+---------------+
| greatest (5,8,12) | least (1,5,4) |
+-------------------+---------------+
| 12 | 1 |
+-------------------+---------------+
1 row in set (0.00 sec)
十、逻辑运算符
10.1、又称为布尔运算符
10.2、用来判断表达式的真假
10.3、常用的逻辑运算符
逻辑非
mysql> select not 2,!3,not 0,!(4-4);
+-------+----+-------+--------+
| not 2 | !3 | not 0 | !(4-4) |
+-------+----+-------+--------+
| 0 | 0 | 1 | 1 |
+-------+----+-------+--------+
1 row in set (0.00 sec)
逻辑与
mysql> select 2 and 3,4 && 0,0 && NULL,1 and NULL;
+---------+--------+-----------+------------+
| 2 and 3 | 4 && 0 | 0 && NULL | 1 and NULL |
+---------+--------+-----------+------------+
| 1 | 0 | 0 | NULL |
+---------+--------+-----------+------------+
1 row in set (0.00 sec)
十一、位运算符
11.1、对二进制数进行计算的运算符
11.2、常用的位运算符
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)
十二、连接查询
MySQL的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接，首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上，使用较多的连接查询包括：内连接、左连接和右连接
12.1、内连接及示意图
mysql> select t.xuehao,t.xingming,t.chengji from score t inner join bbb t1 on t.xingming=t1.xingming;
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 102 | liuneng | 95 |
| 104 | dajiao | 80 |
+--------+----------+---------+
2 rows in set (0.00 sec)
12.2、左连接及示意图
mysql> select t.xuehao,t.xingming,t.chengji from score t left join bbb t1 on t.xingming=t1.xingming;
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 102 | liuneng | 95 |
| 104 | dajiao | 80 |
| 108 | xiaohei | 80 |
| 108 | lio | 75 |
| 109 | lioo | 85 |
| 20109 | liooo | 55 |
+--------+----------+---------+
6 rows in set (0.00 sec)
12.2、右连接及示意图
mysql> select t.xuehao,t.xingming,t.chengji from score t right join bbb t1 on t.xingming=t1.xingming;
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 102 | liuneng | 95 |
| 104 | dajiao | 80 |
| NULL | NULL | NULL |
| NULL | NULL | NULL |
| NULL | NULL | NULL |
+--------+----------+---------+
5 rows in set (0.00 sec)