mysql 高级查询语句（1））
mysql 查询语句
目录
一 基本匹配条件
1.1 数值比较
要求字段必须是数值类型
| 类型 | 比较 | 例子 |
|---|---|---|
| = | 相等 | uid = 3 |
| > | 大于 | id > 3 |
| >= | 大于等于 | id >= 3 |
| < | 小于 | id < 3 |
| <= | 小于等于 | id<= 3 |
| != | 不相等 | id != 3 |
#查找passwd表中uid字段值 等于 2 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid=2;
#查找passwd表中uid字段值 小于 2的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid<2;
#查找passwd表中uid字段值 小于等于 2的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid<=2;
#查找passwd表中uid字段值 大于1000 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid>1000;
#查找passwd表中uid字段值 大于等于1000 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid>=1000;
#查找passwd表中uid字段值 不等于1000 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid!=1000;
#查找passwd表中uid字段值 不等于0 的表记录，显示user_name ,uid,gid字段的值
select user_name,uid,gid from school.passwd where uid <> 0;
1.2 字符比较/匹配空/非空/null值
字段类型为字符类型
| 类型 | 比较 | 例子 |
|---|---|---|
| = | 相等 | city='Beijing' |
| != | 不相等 | city != 'Nanjing' |
| <> | 不相等 | city <> 'Nanjing' |
| is null | null值 | city is null |
| is not null | 不是null值 | city is not null |
1.2.1 null值和空值的区别
空值（无值） 的长度为0，是不占用空间的。 Null 值的长度为Null，是占用空间的
空值（无值）可以参与运算，Null值不可以参与运算
insert into city values ('Nanjing');
insert into city values ('Beijing');
#添加空值
insert into city values ('');
#添加null 值
insert into city values ();
#查看空值长度
select length('');
#查看Null值长度
select length(Null);
1.2.2 字符串匹配/比较
#查找name字段是'Nanjign'
select * from city where name = 'Nanjing';
#查找name字段 不是'Nanjign'
select * from city where name != 'Nanjing';
select * from city where name <> 'Nanjing';
#查找name字段值为空值
select * from city where name = '';
#查找name字段值不为空值
select * from city where name <> '';
#查找name字段值为Null值
select * from city where name is null;
#查找name字段值 不为 Null值
select * from city where name is not null;
1.3 逻辑匹配
多个判断条件时使用
| 类型 | 用途 | 格式 |
|---|---|---|
| or | 逻辑或 | 条件1 or 条件2 or 条件3..... |
| and | 逻辑与 | 条件1 and 条件2 and 条件3..... |
| ! 或 not | 逻辑非 |
#查找 uid小于2 且 gid 小于 2 的表记录
mysql> select user_name,uid,gid from school.passwd where uid<2 and gid<2;
+-----------+------+------+
| user_name | uid | gid |
+-----------+------+------+
| root | 0 | 0 |
| bin | 1 | 1 |
+-----------+------+------+
2 rows in set (0.00 sec)
#查看 uid 小于 2 或gid大于1000 的表记录
mysql> select user_name,uid,gid from school.passwd where uid<2 or gid >1000;
+-----------+-------+-------+
| user_name | uid | gid |
+-----------+-------+-------+
| root | 0 | 0 |
| bin | 1 | 1 |
| nfsnobody | 65534 | 65534 |
| mysql | 1001 | 1001 |
+-----------+-------+-------+
" NOT " 和 " ! "都是逻辑非运算符，返回和操作数相反的结果，具体语法规则为：
- 当操作数为 0（假）时，返回值为 1；
- 当操作数为非零值时，返回值为 0；
- 当操作数为 NULL 时，返回值为 NULL。
# not和 ！ 返回操作数相反的结果，0则返回1，非0 则返回0
mysql> select not 1;
+-------+
| not 1 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
mysql> select not 0;
+-------+
| not 0 |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql> select ! 1;
+-----+
| ! 1 |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)
mysql> select ! 0;
+-----+
| ! 0 |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
#当 not 和！ 分别和数学运算符一起用。
#not 和 ！ 优先级不同. not 的优先级低于 + ，而！ 的优先级则高于+
#not 1+1 , 先计算1+1 ，在将结not 运算 ,即等同于 not 2，非零值取反则结果为 0
mysql> select not 1 + 1 ;
+-----------+
| not 1 + 1 |
+-----------+
| 0 |
+-----------+
1 row in set (0.01 sec)
# ! 1 + 1 先计算 ！1 ，在计算 ！1 结果和 1 相加。相当于 (!1) +1 ,(!1)结果为0，(!1)+1，等于0+1
mysql> select ! 1 + 1 ;
+---------+
| ! 1 + 1 |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
#当操作数为Null 时，依旧返回Null
mysql> select ! NUll ;
+--------+
| ! NUll |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> select not NUll ;
+-----------+
| not NUll |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
mysql>
1.4 范围匹配
| 类型 | 比较 |
|---|---|
| in (值列表） | 在。。。里 |
| not in (值列表） | 不在。。。。里 |
| between 。。。and 。。。。 | 在 。。。。之间 |
#查找uid 值时 0，1，2，3，4，5的表记录
select user_name,uid,gid from school.passwd where uid in (0,1,2,3,4,5);
#查找uid 值不是 0,1,2,3,4,5的表记录
select user_name,uid,gid from school.passwd where uid not in (0,1,2,3,4,5);
#查找uid 值在 0到 5 之间的表记录
select user_name,uid,gid from school.passwd where uid between 0 and 5 ;
+---------------------+-------+-------+
二 高级匹配条件
2.1 like 模糊匹配
用法:
where 字段名 like ‘字符串或通配符’
通配符 "_" :表示一个字符
通配符"%" : 表示0-n个字符
#模糊匹配，user_name字段值包含st 字符串
select user_name,uid,gid from school.passwd where user_name like '%st%';
#模糊匹配，user_name字段值以字符串ro 开头
select user_name,uid,gid from school.passwd where user_name like 'ro%';
#模糊匹配，user_name字段值以 字符串 st 结尾
select user_name,uid,gid from school.passwd where user_name like '%st';
#模糊匹配，user_name字段值是 4 个字符串的
select user_name,uid,gid from school.passwd where user_name like '____';
#模糊匹配，user_name字段是 4 个字符串，且以st 结尾
select user_name,uid,gid from school.passwd where user_name like '__st';
2.2 正则表达式
用法:
where 字段名 regexp ‘正则表达式';
| 匹配模式 | 描述 |
|---|---|
| ^ | 匹配文本的开始字符 |
| $ | 匹配文本的结束字符 |
| . | 匹配任意单个字符 |
| + | 匹配前面字符1此或者多次 |
| * | 匹配前面字符任意次数（可以是0次） |
| 字符串 | 匹配包含指定的字符串 |
| a|b | 匹配a或b |
| [.....] | 匹配字符集中任意一个字符 |
| [^....] | 不匹配字符集中的字符 |
| 匹配前面字符串n次 | |
| 匹配前面字符串 n次以上 | |
| 匹配前面字符串 n次到 m次 |
#匹配user_name字段ro开头的行
select user_name from school.passwd where user_name regexp '^ro';
#匹配user_name字段以st结尾的行
select user_name from school.passwd where user_name regexp 'st$';
#匹配user_name字段包含x或y或z的行
select user_name from school.passwd where user_name regexp '[xyz]';
#匹配user_name字段不包含字母a-w的行
select user_name from school.passwd where user_name regexp '[^a-w]';
#匹配user_name字段 包含字符ro，o的个数为任意个数，可以为0
select user_name from school.passwd where user_name regexp 'ro*';
#匹配user_name字段，包含字符ro的行
select user_name from school.passwd where user_name regexp 'ro.*';
#匹配包含o的行，o为连续的1个或连续的2个
select user_name from school.passwd where user_name regexp 'o{1,2}';
#匹配包含o的行，o为连续的2个
select user_name from school.passwd where user_name regexp 'o{2}';
三：操作查询结果
3.1 数学函数
| 数学函数 | 返回的结果 |
|---|---|
| abs(x) | 返回 x 的绝对值 |
| rand() | 返回 0 到 1 的随机数 |
| mod(x,y) | 返回 x 除以 y 以后的余数 |
| power(x,y) | 返回 x 的 y 次方 |
| round(x) | 返回离 x 最近的整数 |
| round(x,y) | 保留 x 的 y 位小数四舍五入后的值 |
| sqrt(x) | 返回 x 的平方根 |
| truncate(x,y) | 返回数字 x 截断为 y 位小数的值(直接接续小y位，不四舍五入） |
| ceil(x) | 返回大于或等于 x 的最小整数 |
| floor(x) | 返回小于或等于 x 的最大整数 |
| greatest(x1,x2…) | 返回集合中最大的值 |
| least(x1,x2…) | 返回集合中最小的值 |
mysql> select abs(-1), rand(),
-> mod(10,2),power(2,5),
-> round(1.234),round(1.234,2),
-> sqrt(4),truncate(1.892,2),
-> ceil(1.23),floor(1.985),
-> greatest(1,2,3,4),least(1,2,3,4) \G
*************************** 1. row ***************************
abs(-1): 1 #取绝对值
rand(): 0.7493982471054403 #返回0到1,之间任意值
mod(10,2): 0 #取余
power(2,5): 32 #返回次方结果
round(1.234): 1 #四舍五入取整
round(1.234,2): 1.23 #精确到多少位小数
sqrt(4): 2 #返回平方根
truncate(1.892,2): 1.89 #直接截取多少位小数（不进行四舍五入）
ceil(1.23): 2 #返回大于或等于该数的整数
floor(1.985): 1 #返回小于或等于该数的整数
greatest(1,2,3,4): 4 #返回集合中最大的值
least(1,2,3,4): 1 #返回集合中最小的值
3.2 聚合函数
| 聚合函数 | 返回的结果 |
|---|---|
| avg() | 返回指定列的平均值 |
| count() | 返回指定列中非 NULL 值的个数 |
| min() | 返回指定列的最小值 |
| max() | 返回指定列的最大值 |
| sum(x) | 返回指定列的所有值之和 |
#school.class 表
mysql> select * from class;
+----+-------------+-------+
| id | name | score |
+----+-------------+-------+
| 1 | zhangsan | 100 |
| 2 | lisi | 70 |
| 3 | wanglei | 80 |
| 4 | dingcong | 90 |
| 5 | linrui | 60 |
| 6 | zhoujiazhen | 75 |
| 7 | houlu | 55 |
+----+-------------+-------+
#查看socre 字段平均值
mysql> select avg(score) from school.class;
+------------+
| avg(score) |
+------------+
| 75.7143 |
+------------+
1 row in set (0.00 sec)
#查看非null的表记录总数
mysql> select count(score) from school.class;
+--------------+
| count(score) |
+--------------+
| 7 |
+--------------+
1 row in set (0.00 sec)
#查看你socre 字段最小值
mysql> select min(score) from school.class;
+------------+
| min(score) |
+------------+
| 55 |
+------------+
1 row in set (0.00 sec)
#查看score字段最大值
mysql> select max(score) from school.class;
+------------+
| max(score) |
+------------+
| 100 |
+------------+
1 row in set (0.00 sec)
#查看score字段总和
mysql> select sum(score) from school.class;
+------------+
| sum(score) |
+------------+
| 530 |
+------------+
1 row in set (0.00 sec)
3.3 字符串函数
| 字符串函数 | 返回的结果 |
|---|---|
| trim() | 返回去除指定格式的值 |
| concat(x,y) | 将提供的参数 x 和 y 拼接成一个字符串 |
| substr(x,y) | 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同 |
| substr(x,y,z) | 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串 |
| length(x) | 返回字符串 x 的长度 |
| replace(x,y,z) | 将字符串 z 替代字符串 x 中的字符串 y |
| upper(x) | 将字符串 x 的所有字母变成大写字母 |
| lower(x) | 将字符串 x 的所有字母变成小写字母 |
| left(x,y) | 返回字符串 x 的前 y 个字符 |
| right(x,y) | 返回字符串 x 的后 y 个字符 |
| repeat(x,y) | 将字符串 x 重复 y 次 |
| space(x) | 返回 x 个空格 |
| strcmp(x,y) | 比较 x 和 y，返回的值可以为-1,0,1 |
| reverse(x) | 将字符串 x 反转 |
#从字符串'my class' 中去除 字符串’ss‘
mysql> select trim( 'ss' from 'my class');
+-----------------------------+
| trim( 'ss' from 'my class') |
+-----------------------------+
| my cla |
+-----------------------------+
#将字符创 'my',字符串'class' 拼接
mysql> select concat('my','class');
+----------------------+
| concat('my','class') |
+----------------------+
| myclass |
+----------------------+
#截取字符串'myclass',从第三个字符截取到末尾
mysql> select substr('myclass',3);
+---------------------+
| substr('myclass',3) |
+---------------------+
| class |
+---------------------+
1 row in set (0.00 sec)
#截取字符串’myclass‘ 从第3个字符开始截取，截取2位
mysql> select substr('myclass',3,2);
+-----------------------+
| substr('myclass',3,2) |
+-----------------------+
| cl |
+-----------------------+
1 row in set (0.00 sec)
#获取字符串'myclass'的长度
mysql> select length('myclass');
+-------------------+
| length('myclass') |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)
#将字符串'myclass'中的 ’my' 使用 'your' 替换
mysql> select replace('myclass','my','your');
+--------------------------------+
| replace('myclass','my','your') |
+--------------------------------+
| yourclass |
+--------------------------------+
1 row in set (0.00 sec)
3.4 四则运算
运算操作
| 符号 | 用途 | 例子 |
|---|---|---|
| + | 加法 | uid + gid |
| - | 减法 | uid - gid |
| * | 乘法 | uid * gid |
| / | 除法 | uid / gid |
| % | 取余（求模） | uid % gid |
| （） | 提高优先级 | (uid +gid) / 2 |
#显示socre字段值 +100 后的结果
mysql> select score+100 from school.class;
+-----------+
| score+100 |
+-----------+
| 200 |
| 170 |
| 180 |
| 190 |
| 160 |
| 175 |
| 155 |
+-----------+
7 rows in set (0.00 sec)
#显示score字段值除以2后的结果
mysql> select score/2 from school.class;
+---------+
| score/2 |
+---------+
| 50.0000 |
| 35.0000 |
| 40.0000 |
| 45.0000 |
| 30.0000 |
| 37.5000 |
| 27.5000 |
+---------+
7 rows in set (0.00 sec)
#显示score字段值取余2 的结果
mysql> select score%2 from school.class;
+---------+
| score%2 |
+---------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 1 |
| 1 |
+---------+
7 rows in set (0.00 sec)
#显示score字段值先加100，在除以2 的结果
mysql> select (score+100)/2 from school.class;
+---------------+
| (score+100)/2 |
+---------------+
| 100.0000 |
| 85.0000 |
| 90.0000 |
| 95.0000 |
| 80.0000 |
| 87.5000 |
| 77.5000 |
+---------------+
7 rows in set (0.00 sec)
3.5 order by 查询结果排序
用法
sql 查询 order by 字段名 [asc|desc];
asc ： 升序排列（默认，可以省略）
desc：降序排列
#将查询结果，按照字段 score 的值，升序排列
mysql> select * from school.class order by score;
+----+-------------+-------+
| id | name | score |
+----+-------------+-------+
| 7 | houlu | 55 |
| 5 | linrui | 60 |
| 2 | lisi | 70 |
| 6 | zhoujiazhen | 75 |
| 3 | wanglei | 80 |
| 4 | dingcong | 90 |
| 1 | zhangsan | 100 |
+----+-------------+-------+
7 rows in set (0.00 sec)
#将查询结果，按照字段score的值降序排列
mysql> select * from school.class order by score desc;
+----+-------------+-------+
| id | name | score |
+----+-------------+-------+
| 1 | zhangsan | 100 |
| 4 | dingcong | 90 |
| 3 | wanglei | 80 |
| 6 | zhoujiazhen | 75 |
| 2 | lisi | 70 |
| 5 | linrui | 60 |
| 7 | houlu | 55 |
+----+-------------+-------+
7 rows in set (0.00 sec)
3.6group by 查询结果分组
用法：
sql 查询 group by 字段名；
对GROUP BY 后面的查询结果进行汇总分组，通常是结合聚合函数一起使用
GROUP BY有个原则，select 后面的所有列中，没有用聚合函数的列，必须出现在GROUP BY后面
#数据表内容如下
mysql> select * from project;
+---------+-------------+-------+
| project | name | score |
+---------+-------------+-------+
| chanese | houlu | 100 |
| chanese | linrui | 80 |
| chanese | wanglei | 40 |
| chanese | zhoujiazhen | 60 |
| math | zhoujiazhen | 60 |
| math | linrui | 60 |
| math | wanglei | 100 |
| math | houlu | 90 |
| english | houlu | 80 |
| english | wanglei | 60 |
| english | zhoujiazhen | 80 |
| english | linrui | 100 |
+---------+-------------+-------+
12 rows in set (0.00 sec)
#以name字段分组，查看name字段和score字段的总和，
mysql> select name,sum(score) from project group by name;
+-------------+------------+
| name | sum(score) |
+-------------+------------+
| houlu | 270 |
| linrui | 240 |
| wanglei | 200 |
| zhoujiazhen | 200 |
+-------------+------------+
4 rows in set (0.00 sec)
3.7 distinct 去重显示
distinct 不显示重复的资料
格式:
select distinct 字段名 from 表名 ;
#从project表中，查询project字段的值，并且不显示重复的
mysql> select distinct project from project;
+---------+
| project |
+---------+
| chanese |
| math |
| english |
+---------+
3 rows in set (0.00 sec)
3.8 having 查询结果过滤
用来过滤由GROUP BY语句返回的记录集，通常与GROUP BY语句联合使用
HAVING的存在弥补了WHERE关键字不能和聚合函数联合使用的不足，如果被select的只有函数栏，那就不需要GROUP BY子句
格式：
sql查询 having 条件表达式
#按照 name字段分组，查询name字段，和score字段值最小值，然后显示score最小值，小于或等于60的数据
mysql> select name,min(score) from project group by name having min(score) <= 60;
+-------------+------------+
| name | min(score) |
+-------------+------------+
| linrui | 60 |
| wanglei | 40 |
| zhoujiazhen | 60 |
+-------------+------------+
3 rows in set (0.00 sec)
#先查询socre字段值小于等于60的表记录，然后，在查找name字段是'zhoujiazhen'的
mysql> select * from project where score <= 60 having name='zhoujiazhen';
+---------+-------------+-------+
| project | name | score |
+---------+-------------+-------+
| chanese | zhoujiazhen | 60 |
| math | zhoujiazhen | 60 |
+---------+-------------+-------+
3.9 as 别名
栏位別名 表格別名
语法：SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";
#将 avg_score作为avg(score)的别名
mysql> select project,avg(score) avg_score from project group by project;
+---------+-----------+
| project | avg_score |
+---------+-----------+
| chanese | 70.0000 |
| english | 80.0000 |
| math | 77.5000 |
+---------+-----------+
3 rows in set (0.00 sec)
