MySQL高级语句
- 一、按关键字排序
- 1.1 按单字段降序排序
- 1.2 按单字段排序升序
- 1.3 按多字段排序
- 二、对结果进行分组
- 三、限制结果条目
- 四、设置别名
- 五、通配符
- 六、子查询
- 七、NULL值
- 正则表达式
- 八、运算符
- 8.1 算术运算符
- 8.2 比较运算符
- 8.3 逻辑运算符
- 8.4 位运算符
- 九、连接查询
- 9.1 内连接
- 9.2 外连接
- 十、数据库函数
- 10.1常用的数学函数
- 10.2 聚合函数
- 10.3 字符串函数
- 10.4 日期时间函数
一、按关键字排序
- 使用ORDER BY 语句来实现排序
- 排序可针对一个或多个字段
- ASC：升序，默认排序方式
- DESC：降序
- ORDER BY的语法结构
- SELECT column1，column2,…FROM table_name
1.1 按单字段降序排序
mysql> select id,name,score from student order by score desc;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 3 | xiaobai | 100 |
| 2 | xiaohong | 60 |
+----+----------+-------+
3 rows in set (0.00 sec)1.2 按单字段排序升序
mysql> select id,name,score from student order by score asc;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 2 | xiaohong | 60 |
| 1 | xiaoming | 100 |
| 3 | xiaobai | 100 |
+----+----------+-------+
3 rows in set (0.00 sec)1.3 按多字段排序
mysql> select id,score from student order by score desc,id asc;
+----+-------+
| id | score |
+----+-------+
| 1 | 100 |
| 3 | 100 |
| 2 | 60 |
+----+-------+
3 rows in set (0.00 sec)
## 先比较分数，分数若相同，比较辅助字段id，升序排序二、对结果进行分组
- 使用GROUP BY 虞姬来实现分组
- 通常结合聚合函数一起使用
- 可以按一个或多个字段对结果进行分组
- GROUP BY 的语法结构
- select column_name,aggregate_function(column_name) from table_name where column_name operator value GROUP BY column_name;
mysql> select count(name),score from student where score>=80 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 1 | 80 |
| 2 | 100 |
+-------------+-------+
2 rows in set (0.00 sec)
## 对分数进行分组，显示每个分数段的人有几个mysql> select count(name),score from student where score>60 group by score order by score desc;
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 2 | 100 |
| 1 | 80 |
| 1 | 70 |
+-------------+-------+
3 rows in set (0.00 sec)
## 对分数大于60的进行降序排序，并显示每个分段有多少人三、限制结果条目
- 只返回select查询结果的第一行或前几行
- 使用limit语句限制条目
-select column1,column2,…from table_name limit [offset]
## 显示三行索引
mysql> select * from student limit 3；
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
+----+----------+-------+
3 rows in set (0.00 sec)
## 显示从4开始的，显示两行索引
mysql> select * from student limit 4,2;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 5 | zhangsan | 60 |
| 6 | xiaoliu | 70 |
+----+----------+-------+
2 rows in set (0.00 sec)
## 结合排序，显示前三名
mysql> select * from student order by score desc limit 3;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 3 | xiaobai | 100 |
| 7 | wangwu | 80 |
+----+----------+-------+四、设置别名
- 使用AS语句设置别名，关键字AS可省略
- 设置别名时，保证不能与库中其他表或字段名称冲突
- 别名的语法结构
- select column_name AS alias_name from table_name;
- select column_name(s) from table_name AS alias_name;
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) as totle from student;
+-------+
| totle |
+-------+
| 7 |
+-------+
1 row in set (0.00 sec)
## 对表设置别名，student表的别名为a，查询a表的name和score字段
mysql> select a.name,a.score from student as a;
+----------+-------+
| name | score |
+----------+-------+
| xiaoming | 100 |
| xiaohong | 60 |
| xiaobai | 100 |
| lisi | 40 |
| zhangsan | 60 |
| xiaoliu | 70 |
| wangwu | 80 |
+----------+-------+
7 rows in set (0.00 sec)五、通配符
- 用于替换字符串中的部分字符
- 通常配合LIKE一起使用，并协同where完成查询
- 常用通配符
- %表示零个，一个或多个
- _表示单个字符
## 查询student表中name字段是以x开头的
mysql> select * from student where name like 'x%';
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
| 6 | xiaoliu | 70 |
+----+----------+-------+
4 rows in set (0.00 sec)
## 单个字符匹配
mysql> select * from student where name like 'lis_';
+----+------+-------+
| id | name | score |
+----+------+-------+
| 4 | lisi | 40 |
+----+------+-------+
1 row in set (0.00 sec)
## 多个字符匹配，几个字符几个_
mysql> select * from student where name like 'z_______';
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 5 | zhangsan | 60 |
+----+----------+-------+
1 row in set (0.00 sec)
## 第一个字符任意匹配，中间字符为iao，结尾任意字符结尾
mysql> select * from student where name like '_iao%';
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
| 6 | xiaoliu | 70 |
+----+----------+-------+
4 rows in set (0.00 sec)六、子查询
- 也称作内查询或嵌套查询
- 先于主查询被执行，其结果将作为外层主查询条件
- 在增删改查中都可以使用子查询
- 支持多层嵌套
- IN语句时用来判断某个值是否在给定的结果集中
## 查看id为6和7的是否在给定的集合中
mysql> select id,name,score from student where id in(6,7);
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 6 | xiaoliu | 70 |
| 7 | wangwu | 80 |
+----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from student where id in(select id from student where score>60);
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 3 | xiaobai | 100 |
| 6 | xiaoliu | 70 |
| 7 | wangwu | 80 |
+----+----------+-------+
4 rows in set (0.00 sec)
mysql> select * from student where id=(select id from student where name='lisi');
+----+------+-------+
| id | name | score |
+----+------+-------+
| 4 | lisi | 40 |
+----+------+-------+
1 row in set (0.00 sec)
## 查询除了lisi以外的信息 ！=等同<>
mysql> select * from student where id!=(select id from student where name='lisi');
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
| 5 | zhangsan | 60 |
| 6 | xiaoliu | 70 |
| 7 | wangwu | 80 |
+----+----------+-------+
6 rows in set (0.00 sec)
## 多层嵌套
mysql> select * from student where id in (select student.id from (select id from student where score>80)student);
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 3 | xiaobai | 100 |
+----+----------+-------+
2 rows in set (0.00 sec)七、NULL值
- 表示缺失的值
- 与数字0或者空白（Spaces）是不同的
- 使用IS NULL 或 IS NOT NULL 进行判断
- NULL值和空值的区别
- 空值长度为0，不占空间；NULL值的长度为NULL，占用空间
- IS NULL无法判断空值
- 空值使用“=”护着+“<>”来处理
- COUNT()计算时，NULL会忽略，空值会加入计算
mysql> select * from student;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
| 4 | lisi | 40 |
| 5 | zhangsan | 60 |
| 6 | xiaoliu | 70 |
| 7 | wangwu | 80 |
| 8 | lili | NULL |
+----+----------+-------+
8 rows in set (0.00 sec)
## count（）计算时，空值NULL会忽略不计
mysql> select count(score) from student;
+--------------+
| count(score) |
+--------------+
| 7 |
+--------------+
1 row in set (0.00 sec)
## 查询student表中，score字段空值
mysql> select * from student where score is null;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 8 | lili | NULL |
+----+------+-------+
1 row in set (0.00 sec)
## 查询student表中，score字段不是空值
mysql> select * from student where score is not null;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
| 4 | lisi | 40 |
| 5 | zhangsan | 60 |
| 6 | xiaoliu | 70 |
| 7 | wangwu | 80 |
+----+----------+-------+
7 rows in set (0.00 sec)正则表达式
- 根据指定的匹配模式匹配记录中符合要求的特殊字符
- 使用REGEXP关键字指定匹配模式
- 常用的匹配模式
- ^匹配开始字符
- p1|p2匹配p1或p2
- $匹配结束字符
- […] 匹配字符集中的任意一个字符
- .匹配任意单个字符
- [^…]匹配不在中括号内的任何字符
- *匹配任意个前面的字符
- {n}匹配前面的符
- +匹配前面字符至少1次
- {n,m}匹配前面的字符
## 查询以l开头的记录
mysql> select * from student where name regexp '^l';
+----+------+-------+
| id | name | score |
+----+------+-------+
| 4 | lisi | 40 |
| 8 | lili | NULL |
+----+------+-------+
2 rows in set (0.00 sec)
## 查询以u结尾的记录
mysql> select * from student where name regexp 'u$';
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 6 | xiaoliu | 70 |
| 7 | wangwu | 80 |
+----+---------+-------+
2 rows in set (0.00 sec)
## 查询包含指定字符串‘xiao’的记录
mysql> select * from student where name regexp 'xiao';
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
| 6 | xiaoliu | 70 |
+----+----------+-------+
4 rows in set (0.01 sec)
## 查询name字符为xiaom缺少的字符用.表示
mysql> select * from student where name regexp 'xiaom...';
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
+----+----------+-------+
1 row in set (0.00 sec)
## 查询name字段中有liu和wu的记录
mysql> select * from student where name regexp 'liu|wu';
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 6 | xiaoliu | 70 |
| 7 | wangwu | 80 |
+----+---------+-------+
2 rows in set (0.00 sec)
##“*”匹配前面字符的任意多次
mysql> select * from student where name regexp 'xiao*';
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
| 6 | xiaoliu | 70 |
+----+----------+-------+
4 rows in set (0.00 sec)
## “+”匹配前面字符至少一次
mysql> select * from student where name regexp 'xiaoo*';
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
| 6 | xiaoliu | 70 |
+----+----------+-------+
4 rows in set (0.00 sec)
## 匹配指定字符集中的任意一个
mysql> select * from student where name regexp '^[a-z]';
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | xiaoming | 100 |
| 2 | xiaohong | 60 |
| 3 | xiaobai | 100 |
| 4 | lisi | 40 |
| 5 | zhangsan | 60 |
| 6 | xiaoliu | 70 |
| 7 | wangwu | 80 |
| 8 | lili | NULL |
+----+----------+-------+
8 rows in set (0.00 sec)八、运算符
用于对记录中的字段值进行运算
MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符
8.1 算术运算符
## 加减乘除
mysql> select 1+1,1-1,1*4,4/2,7%3;
+-----+-----+-----+--------+------+
| 1+1 | 1-1 | 1*4 | 4/2 | 7%3 |
+-----+-----+-----+--------+------+
| 2 | 0 | 4 | 2.0000 | 1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)8.2 比较运算符
1）等于运算符
用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。其中字符的比较是根据 ASCII 码来判断的。
mysql> select 1=1,1=3,'a'='a','a'='z';
+-----+-----+---------+---------+
| 1=1 | 1=3 | 'a'='a' | 'a'='z' |
+-----+-----+---------+---------+
| 1 | 0 | 1 | 0 |
+-----+-----+---------+---------+
1 row in set (0.00 sec)2） 不等于运算符
不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0。
需要注意的是不等于运算符不能用于判断 NULL。
mysql> select 1!=1, 'a'!='ab',1<>2,'a'<>null;
+------+-----------+------+-----------+
| 1!=1 | 'a'!='ab' | 1<>2 | 'a'<>null |
+------+-----------+------+-----------+
| 0 | 1 | 1 | NULL |
+------+-----------+------+-----------+3） 大于、大于等于、小于、小于等于运算符
mysql> select 'a'>'z',2<1,3>1;
+---------+-----+-----+
| 'a'>'z' | 2<1 | 3>1 |
+---------+-----+-----+
| 0 | 0 | 1 |
+---------+-----+-----+
1 row in set (0.01 sec)4） IS NULL、IS NOT NULL
IS NULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回 0。
IS NOT NULL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回 0。
mysql> select 1 is null ,'a' is not null ,null is null,null is not null;
+-----------+-----------------+--------------+------------------+
| 1 is null | 'a' is not null | null is null | null is not null |
+-----------+-----------------+--------------+------------------+
| 0 | 1 | 1 | 0 |
+-----------+-----------------+--------------+------------------+5） between and
用于判断一个值是否落在某两个值之间
mysql> select 's' between 'a' and 'z','a' between 'v' and 'z';
+-------------------------+-------------------------+
| 's' between 'a' and 'z' | 'a' between 'v' and 'z' |
+-------------------------+-------------------------+
| 1 | 0 |
+-------------------------+-------------------------+6） least、greatest
LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。
GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL， 则返回结果就为 NULL。
mysql> select least(11,43,112),greatest(11,43,112),least('f','s','r'),greatest('t','s','p');
+------------------+---------------------+--------------------+-----------------------+
| least(11,43,112) | greatest(11,43,112) | least('f','s','r') | greatest('t','s','p') |
+------------------+---------------------+--------------------+-----------------------+
| 11 | 112 | f | t |
+------------------+---------------------+--------------------+-----------------------+
1 row in set (0.00 sec)7） in、not in
IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。
NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0。
mysql> select 's' in('a','s',1),1 in (1,2,'a'),3 not in (1,2,3);
+-------------------+----------------+------------------+
| 's' in('a','s',1) | 1 in (1,2,'a') | 3 not in (1,2,3) |
+-------------------+----------------+------------------+
| 1 | 1 | 0 |
+-------------------+----------------+------------------+8） like、not like
LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0；NOT LIKE 正好跟 LIKE 相反。
LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符，而’_’只能匹配一个字符。
mysql> select 'abc' like 'a%','abc' like 'a__';
+-----------------+------------------+
| 'abc' like 'a%' | 'abc' like 'a__' |
+-----------------+------------------+
| 1 | 1 |
+-----------------+------------------+
1 row in set (0.00 sec)8.3 逻辑运算符
1）逻辑非
逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真
mysql> select !0,not (1-1);
+----+-----------+
| !0 | not (1-1) |
+----+-----------+
| 1 | 1 |
+----+-----------+
1 row in set (0.00 sec)2）逻辑与
如果所有值都是真返回 1，否则返回 0。（全1出1，有0出0）
mysql> select 0 and 0,1 and 1,1&&0;
+---------+---------+------+
| 0 and 0 | 1 and 1 | 1&&0 |
+---------+---------+------+
| 0 | 1 | 0 |
+---------+---------+------+
1 row in set (0.00 sec)3）逻辑或
任意一个为非零值并且不是 NULL 值时，返回 1，否则返回0。（全0出0，有1出1）
mysql> select 1 or 0, 1 or 1 , 0 or 0;
+--------+--------+--------+`mysql> select 1 xor 0, 1 xor 1, 0 xor 0;
+---------+---------+---------+
| 1 xor 0 | 1 xor 1 | 0 xor 0 |
+---------+---------+---------+
| 1 | 0 | 0 |
+---------+---------+---------+
1 row in set (0.00 sec)
`
| 1 or 0 | 1 or 1 | 0 or 0 |
+--------+--------+--------+
| 1 | 1 | 0 |
+--------+--------+--------+
1 row in set (0.00 sec)4）逻辑异或
两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0， 另一个为非 0，则返回结果为 1；
当任意一个值为 NULL 时，返回值为 NULL。
8.4 位运算符
运算符 | 描述 |
& | 按位与 |
I | 按位或 |
~ | 按位取反 |
^ | 按位异或 |
<< | 按位左移 |
>> | 按位右移 |
位运算符，化为二进制进行运算，对二进制数进行计算的运算符。
九、连接查询
通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。
要先确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。
9.1 内连接
在from子句中使用关键字 inner join 来连接多张表，并使用 on子句设置连接条件。
mysql> select info.name,hob.hobbyname from info inner join hob on info.hobby=hob.id;
内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。
9.2 外连接
1）左连接，主表在左边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来
mysql> select student.name,teacher.name from student left join teacher on student.id=teacher.id;
+----------+------+
| name | name |
+----------+------+
| xiaoming | kai |
| xiaohong | li |
| xiaohong | tian |
| xiaobai | NULL |
| lisi | NULL |
| zhangsan | NULL |
| xiaoliu | NULL |
| wangwu | NULL |
| lili | NULL |
+----------+------+
9 rows in set (0.00 sec)2）右连接，主表在右边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来
mysql> select student.name,teacher.name from student right join teacher on student.id=teacher.id;
+----------+------+
| name | name |
+----------+------+
| xiaoming | kai |
| xiaohong | li |
| xiaohong | tian |
+----------+------+
3 rows in set (0.00 sec)十、数据库函数
- MySQL提供了实现各种功能的函数
- 常用的函数分类
数学函数
• 聚合函数
• 字符串函数
• 日期时间函数10.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位小数的值
• ceil（x）返回大于或等于x的最小整数
• floor（x）返回小于或等于x的最大整数
• greatest（x1，x2，…）返回集合中最大的值10.2 聚合函数
- 对表中数据记录进行集中概括而设计的一类函数
- 常用的函数聚合
avg（）返回指定列的平均值
• count（）返回指定列中非NULL值的个数
• min（）返回指定列的最小值
• max（）返回指定列的最大值
• sum（）返回指定列的所有值之和10.3 字符串函数
- 常用的字符串函数
• length（x）返回字符串x的长度
• trim（）返回去除指定格式的值
• concat（x，y）将提供的参数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个空格
• replace（x，y，z）将字符串z替代字符串x中的字符串y
• strcmp（x，y）比较x和y，返回的值可以为-1,0,1
• substring（x，y，z）获取从字符串x中的第y个位置开始长度为z的字符串
• reverse（x）将字符串x反转10.4 日期时间函数
- 常用的日期时间函数
• curdate（）返回当前时间的年月日
• curtime（）返回当前时间的时分秒
• now（）返回当前时间的日期和时间
• month（x）返回日期x中的月份值
• week（x）返回日期x是年度第几个星期
• hour（x）返回x中的小时值
• minute（x）返回x中的分钟值
• second（x）返回x中的秒钟值
• dayofweek（x）返回x是星期几，1是星期日，2是星期一
• dayofmonth（x）计算日期x是本月的第几天
• dayofyear（x）计算日期x是本年的第几天
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
