目录
1.Select——显示表格中的一个或多个字段的所有数据记录
前言
Mysql数据库常用的操作无非就是增、删、改、查,那么工作的时候我们需要对查询的结果进行处理,我们就要用到Mysql的高级SQL语句,下面在虚拟环境中举举例子再温习一下
1.Select——显示表格中的一个或多个字段的所有数据记录
select 字段(*) from 表明;
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> select id,name,address from class;
+----+------+----------+
| id | name | address |
+----+------+----------+
| 1 | cxk | nanjing |
| 2 | wyb | beijing |
| 3 | zyx | wuhan |
| 4 | xzq | shanghai |
| 5 | zs | yunnan |
| 6 | zjl | beijing |
+----+------+----------+
6 rows in set (0.00 sec)
2.Distinct——不显示重复的数据记录
select distinct 字段 from 表明;
#去重
mysql> select distinct cardid from class;
+--------+
| cardid |
+--------+
| 1 |
| 2 |
| 3 |
| 4 |
+--------+
4 rows in set (0.10 sec)
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
3.Where——条件判断
select 字段 from 表明 where 条件;
mysql> select * from class where name='cxk';
+----+------+-------+---------+--------+
| id | name | score | address | cardid |
+----+------+-------+---------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
+----+------+-------+---------+--------+
1 row in set (0.00 sec)
4.In——显示已知的值的数据记录
select 字段 from 表名 where 字段 in(值1,值2...);
mysql> select * from class where name in('cxk','wyb');
+----+------+-------+---------+--------+
| id | name | score | address | cardid |
+----+------+-------+---------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
+----+------+-------+---------+--------+
2 rows in set (0.00 sec)
5.Between——显示两个值范围内的数据
select 字段 from 表名 where 字段 between 值1 and 值2;
mysql> select * from class where id between 3 and 5;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
+----+------+-------+----------+--------+
3 rows in set (0.11 sec)
一、排序——Order by
1.按关键字排序
使用select语句可以将需要的数据从Mysql数据库中查询出来,如果对查询的结果进行排序,可以使用Order by语句来对语句实现排序,并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段,也可以针对多个字段。
SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC];
#ASC 是按照升序进行排序的,是默认的排序方式。
#DESC 是按降序方式进行排序。
1.1准备操作环境
mysql> create table class(id int(6)primary key not null,name char(8),score decimal(5,2),address varchar(40),cardid int(6));
Query OK, 0 rows affected (0.01 sec)
mysql> desc class;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(6) | NO | PRI | NULL | |
| name | char(8) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| cardid | int(6) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> insert into class values(1,'cxk',80,'nanjing',1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into class values(2,'wyb',80,'beijing',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into class values(3,'zyx',95,'wuhan',2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into class values(4,'xzq',88,'shanghai',2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into class values(5,'zs',90,'yunnan',3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into class values(6,'zjl',90,'beijing',4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
1.2Asc——升序排列
mysql> select id,name,score from class order by score asc;
#mysql数据库默认的是asc升序排列
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | cxk | 80.00 |
| 2 | wyb | 80.00 |
| 4 | xzq | 88.00 |
| 5 | zs | 90.00 |
| 6 | zjl | 90.00 |
| 3 | zyx | 95.00 |
+----+------+-------+
6 rows in set (0.00 sec)
1.3Desc——降序排列
mysql> select id,name,score from class order by score desc;
#查询 id,name,score字段 从class数据表中 根据分数降序排列
+----+------+-------+
| id | name | score |
+----+------+-------+
| 3 | zyx | 95.00 |
| 5 | zs | 90.00 |
| 6 | zjl | 90.00 |
| 4 | xzq | 88.00 |
| 1 | cxk | 80.00 |
| 2 | wyb | 80.00 |
+----+------+-------+
6 rows in set (0.00 sec)
mysql> select id,name,score from class where cardid=2 order by score desc;
#查询 id,name,score字段 从class数据表中 判断caraid为2的字段 根据分数降序排列
+----+------+-------+
| id | name | score |
+----+------+-------+
| 3 | zyx | 95.00 |
| 4 | xzq | 88.00 |
+----+------+-------+
2 rows in set (0.00 sec)
1.4Order by语句
Order by语句也可以使用多个字段来进行排序,当排序的第一个字段的记录有多条的情况下,这些多条的记录再按照第二个字段进行排序;后面如果跟多个字段时,字段之间使用英文逗号隔开,优先级是根据先后的顺序而定
Order by之后的第一个参数只有在出现相同值时,第二个字段才有意义
mysql> select id,name,cardid from class order by cardid desc,id;
#查询id,name,cardid字段列 class数据表 使cardid降序排列 再按id升序排列
+----+------+--------+
| id | name | cardid |
+----+------+--------+
| 6 | zjl | 4 |
| 5 | zs | 3 |
| 3 | zyx | 2 |
| 4 | xzq | 2 |
| 1 | cxk | 1 |
| 2 | wyb | 1 |
+----+------+--------+
6 rows in set (0.00 sec)
二、且或嵌套——and、or、()
SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;
1.and——且
mysql> select * from class where score > 70 and score<=90;
#查询class数据表中 判断条件大于70分的 并且 小于等于90分(这个范围)的字段
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
5 rows in set (0.00 sec)
2.or——或
mysql> select * from class where score > 90 or cardid=2;
#查询class数据表中 判断条件满足score字段大于90分 或者 cardid=2的数据(满足任意一个即可)
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
+----+------+-------+----------+--------+
2 rows in set (0.00 sec)
mysql> select * from class where score > 90 or cardid=3;
#查询class数据表中 判断条件 score字段 大于90分 或者 cardid=3的数据(满足大于90分或者cardid=3都显示)
+----+------+-------+---------+--------+
| id | name | score | address | cardid |
+----+------+-------+---------+--------+
| 3 | zyx | 95.00 | wuhan | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
+----+------+-------+---------+--------+
2 rows in set (0.00 sec)
3.()——嵌套
mysql> select * from class where score > 88 or(score > 90 and score <=99);
#查询class数据表中 条件判断 先判断score分数大于90分且小于99分满足条件的打印出,或者score分数大于88分的(两个条件满足其一即可打印)
+----+------+-------+---------+--------+
| id | name | score | address | cardid |
+----+------+-------+---------+--------+
| 3 | zyx | 95.00 | wuhan | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+---------+--------+
3 rows in set (0.00 sec)
mysql> select * from class where score < 88 or(score > 90 and score <=99);
#查询class数据表 条件判断 先判断score分数大于90分 且 小于99分的数据打印 或者 score分数小于88分的数据(两个条件满足其一即可打印)
+----+------+-------+---------+--------+
| id | name | score | address | cardid |
+----+------+-------+---------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
+----+------+-------+---------+--------+
3 rows in set (0.00 sec)
三、对结果进行分组——Group by
通过SQL查询出来的结果,还可以对其进行分组,使用Group by语句来实现,Group by通常都是结合聚合函数一起使用
常用的聚合函数包括:
- Count:计数
- Sum:求和
- Avg:求平均数
- Max:最大值
- Min:最小值
GROUP BY 有一个原则,凡是在 GROUP BY 后面出现的字段,必须在 SELECT 后面出现; 凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段,必须出现在 GROUP BY 后面
1.聚合函数——Count计数
mysql> select count(id),cardid from class group by cardid;
#查询class数据表中 按照cardid进行分组 统计cardid字段的数量进行分组count计数 查询id计数字段列和cardid列 先分组后计数
+-----------+--------+
| count(id) | cardid |
+-----------+--------+
| 2 | 1 |
| 2 | 2 |
| 1 | 3 |
| 1 | 4 |
+-----------+--------+
4 rows in set (0.00 sec)
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> select count(name),cardid from class where score>90 group by cardid;
#查询class数据表 name分组字段列 cardid字段列 判断条件是否score得分大于90分的数据
+-------------+--------+
| count(name) | cardid |
+-------------+--------+
| 1 | 2 |
+-------------+--------+
1 row in set (0.00 sec)
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> select count(name),cardid from class where score>=90 group by cardid;
#查询class数据表 name分组字段列 cardid字段列 判断条件是否score得分大于等于90分的数据
+-------------+--------+
| count(name) | cardid |
+-------------+--------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
+-------------+--------+
3 rows in set (0.00 sec)
mysql> select count(id),score,cardid from class where score>=90 group by cardid order by count(id) desc;
#查询class数据表 查询id计数的列 score字段列 cardid列 判断条件是大于等于90分的 按照id组字段列降序排列
+-----------+-------+--------+
| count(id) | score | cardid |
+-----------+-------+--------+
| 1 | 95.00 | 2 |
| 1 | 90.00 | 3 |
| 1 | 90.00 | 4 |
+-----------+-------+--------+
3 rows in set (0.00 sec)
mysql> select count(id),score,cardid from class where score>=90 group by cardid order by score;
#查询class数据表 查询id计数分组的字段列 score字段列 cardid字段列 判断条件是大于等于90分的 按照分数升序排列
+-----------+-------+--------+
| count(id) | score | cardid |
+-----------+-------+--------+
| 1 | 90.00 | 3 |
| 1 | 90.00 | 4 |
| 1 | 95.00 | 2 |
+-----------+-------+--------+
3 rows in set (0.00 sec)
mysql> select count(id),score,cardid from class where score>=90 group by cardid order by score desc;
#查询class数据表 查询id计数分组的字段列 score字段列 cardid字段列 判断条件是大于等于90分的 按照分数降序排列
+-----------+-------+--------+
| count(id) | score | cardid |
+-----------+-------+--------+
| 1 | 95.00 | 2 |
| 1 | 90.00 | 3 |
| 1 | 90.00 | 4 |
+-----------+-------+--------+
3 rows in set (0.00 sec)
2.拓展
2.1如何统计数据表有多少数据
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> select count(*) from class;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
mysql> select count(id) from class;
+-----------+
| count(id) |
+-----------+
| 6 |
+-----------+
1 row in set (0.00 sec)
2.聚合函数——Sum求和
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> select sum(score) from class;
+------------+
| sum(score) |
+------------+
| 523.00 |
+------------+
1 row in set (0.00 sec)
3.聚合函数——Max求最大值
mysql> select max(score) from class;
+------------+
| max(score) |
+------------+
| 95.00 |
+------------+
1 row in set (0.09 sec)
4.聚合函数——Min求最小值
mysql> select min(score) from class;
+------------+
| min(score) |
+------------+
| 80.00 |
+------------+
1 row in set (0.00 sec)
5.聚合函数——Avg求平均值
mysql> select avg(score) from class;
+------------+
| avg(score) |
+------------+
| 87.166667 |
+------------+
1 row in set (0.00 sec)
四、 限制结果条目——Limit
使用select语句查询时,结果集返回的是所有匹配的记录(行);有时仅需要返回第一行或者前几行再或者第几行不要只取其他几行,
Limit的第一个参数是位置偏移量(可选参数),是设置Mysql从哪一行开始显示。如果不设定第一个参数,将会从表中的第一条记录开始显示;
mysql> select * from class limit 3;
#查询class数据表前三行的数据
+----+------+-------+---------+--------+
| id | name | score | address | cardid |
#位置偏移量0
+----+------+-------+---------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
#位置偏移量1
| 2 | wyb | 80.00 | beijing | 1 |
#位置偏移量2
| 3 | zyx | 95.00 | wuhan | 2 |
#位置偏移量3
+----+------+-------+---------+--------+
3 rows in set (0.00 sec)
mysql> select * from class limit 3,2;
#查询class数据表 不显示第三行 从第三行往后显示两行(显示第四行和第五行)
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
+----+------+-------+----------+--------+
2 rows in set (0.00 sec)
mysql> select * from class order by id limit 3;
#查询class数据表 先按id排序升序排列 显示升序排列后的前三行
+----+------+-------+---------+--------+
| id | name | score | address | cardid |
+----+------+-------+---------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
+----+------+-------+---------+--------+
3 rows in set (0.00 sec)
mysql> select * from class order by id desc limit 3;
#查询class数据表 先降序排列 显示降序排列后的前三行(显示第六、第五、第四行)
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 6 | zjl | 90.00 | beijing | 4 |
| 5 | zs | 90.00 | yunnan | 3 |
| 4 | xzq | 88.00 | shanghai | 2 |
+----+------+-------+----------+--------+
3 rows in set (0.00 sec)
mysql> select * from (select * from class limit 3)as a order by id;
#查询class数据表 先查询class数据表的前三行 将查询到的前三行作为别名 再将查询到的数据进行升序排列
+----+------+-------+---------+--------+
| id | name | score | address | cardid |
+----+------+-------+---------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
+----+------+-------+---------+--------+
3 rows in set (0.00 sec)
五、别名——alias
在使用SQL语句select查询的时候,当表的名字比较长或者表内某些字段比较长时,可以使用alias别名为长字段的列或者表设置别名,方便使用和查询。简洁明了,增强可读性。
1.查询结果别名
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> select address as 地址,score as 分数,name as 名字 from class;
+----------+--------+--------+
| 地址 | 分数 | 名字 |
+----------+--------+--------+
| nanjing | 80.00 | cxk |
| beijing | 80.00 | wyb |
| wuhan | 95.00 | zyx |
| shanghai | 88.00 | xzq |
| yunnan | 90.00 | zs |
| beijing | 90.00 | zjl |
+----------+--------+--------+
6 rows in set (0.00 sec)
as可以想象为替代,把长字段的内容替换成想要得到的内容,其中的表结构和数据内容没有变动(只改变查询结果,不改变表结构)
mysql> select c.name 姓名,c.score 成绩,c.address 地址 from class as c;
#as可以省略
+--------+--------+----------+
| 姓名 | 成绩 | 地址 |
+--------+--------+----------+
| cxk | 80.00 | nanjing |
| wyb | 80.00 | beijing |
| zyx | 95.00 | wuhan |
| xzq | 88.00 | shanghai |
| zs | 90.00 | yunnan |
| zjl | 90.00 | beijing |
+--------+--------+----------+
6 rows in set (0.00 sec)
使用场景:对复杂的表进行查询的时候,别名可以缩短查询语句的长度;多表相连查询的时候可以常用别名来替代,通俗易懂,简化SQL语句
mysql> select score from class;
+-------+
| score |
+-------+
| 80.00 |
| 80.00 |
| 95.00 |
| 88.00 |
| 90.00 |
| 90.00 |
+-------+
6 rows in set (0.00 sec)
mysql> select sum(score) 总成绩 from class;
+-----------+
| 总成绩 |
+-----------+
| 523.00 |
+-----------+
1 row in set (0.00 sec)
2.克隆表别名
mysql> create table test as select * from class;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| class |
| test |
+-----------------+
2 rows in set (0.00 sec)
mysql> select * from test;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> create table test2 as select * from class where score >88;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test2;
+----+------+-------+---------+--------+
| id | name | score | address | cardid |
+----+------+-------+---------+--------+
| 3 | zyx | 95.00 | wuhan | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+---------+--------+
3 rows in set (0.00 sec)
六、通配符——%、_
通常通配符都是和like连用,适用于模糊查询
- %:表示零个、单个或者多个字符(像*);
- _:表示单个字符
举例:
- 'A_Z':模糊查询所有以'A'开头,第二个值为任意一个值,且以'Z'结尾的字符串。例如,"ABZ"和"AZZ"均符合该条件判断,均可查询得到;
- 'ABC%':模糊查询所有以ABC三个字符开头的字符串。例如“ABCD”和“ABCZ”均符合该条件判断,均可查询得到;
- '%XYZ':模糊查询所有以“XYZ”结尾的字符串。例如,“WXYZ”和“UVWXYZ”均符合该条件判断,均可查询得到;
- '%AN%':模糊查询所有含有'AN'的字符串。例如,“QANQ”和“SANS”均符合该条件判断,均可查询得到;
- '_AN%':模糊查询所有第一个字母为任意字符,第二个字母为'A',第三个字母为'N'且以这三个字符串为开头的的字符串。例如,“SAN ABC”和“QAN SPORTING”均符合该条件判断,均可查询得到;
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> select name from class where name like 'z%';
+------+
| name |
+------+
| zyx |
| zs |
| zjl |
+------+
3 rows in set (0.00 sec)
mysql> select address from class where address like '%ng';
+---------+
| address |
+---------+
| nanjing |
| beijing |
| beijing |
+---------+
3 rows in set (0.00 sec)
mysql> select * from class;
+----+------+-------+----------+--------+
| id | name | score | address | cardid |
+----+------+-------+----------+--------+
| 1 | cxk | 80.00 | nanjing | 1 |
| 2 | wyb | 80.00 | beijing | 1 |
| 3 | zyx | 95.00 | wuhan | 2 |
| 4 | xzq | 88.00 | shanghai | 2 |
| 5 | zs | 90.00 | yunnan | 3 |
| 6 | zjl | 90.00 | beijing | 4 |
+----+------+-------+----------+--------+
6 rows in set (0.00 sec)
mysql> select address from class where address like '_u_%';
+---------+
| address |
+---------+
| wuhan |
| yunnan |
+---------+
2 rows in set (0.00 sec)
mysql> select address from class where address like '%nn__';
+---------+
| address |
+---------+
| yunnan |
+---------+
1 row in set (0.00 sec)
七、总结
1.Mysql常用SQL语句
- Select:显示数据表中单个或多个字段列的数据内容
- Distinct:去重
- Where:条件判断
- In:根据已知的数据和字段列进行查询
- Between:介于两个字段列或者两个值之间的数据
2.Mysql高级SQL语句
2.1排序——Order by
- Asc:升序
- Desc:降序
2.2且或嵌套
- And:且,并列,需要同时满足两个判断条件
- Or:或,满足一个判断条件即可
- ():嵌套,优先满足嵌套内的判断条件,再判断嵌套外的条件判断是否满足
2.3分组——Group by
聚合函数
- Count:计数
- Sum:求和
- Max:最大值
- Min:最小值
- Avg:平均值
2.4限制行——Limit
2.5别名——alias
2.6通配符
- %:任意字符
- _:单个字符