文章目录
mysql进阶查询
使用ORDERBY语句来实现排序
排序可针对一个或多个字段
ASC:升序，默认排序方式 【升序是从小到大】
DESC:降序 【降序是从大到小】
ORDER BY的语法结构、
ORDER BY后面跟字段名
SELECT column1, column2,....FROM table_name ORDER BY column1,column2,...ASC|DESC;
按单字段排序
#创建666数据库
mysql> cerate databases 666;
#切换到tt库
mysql> use 666;
#创建表结构tt
mysql> create table 666 (id int(16) not null primary key auto_increment,name char(20) not null,score decimal(5,2),adddress varchar（40）default '未知')engine=innodb;
#查看表结构
mysql> desc tt;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(40) | YES | | 未知 | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
#插入数据记录
mysql> insert into 666 (name,score,address) values ('wangwu',70,'beijing'),('lisi',90,'nanjing');
mysql> insert into 666 (name,score,address) values ('zhangsan',80,'beijing'),('zhaoliu',60,'nanjing');
#查看数据记录
mysql> select * from 666;
+----+----------+-------+---------+
| id | name | score | address |
+----+----------+-------+---------+
| 1 | wangwu | 70.00 | beijing |
| 2 | lisi | 90.00 | nanjing |
| 3 | zhangsan | 80.00 | beijing |
| 4 | zhaoliu | 60.00 | nanjing |
+----+----------+-------+---------+
4 rows in set (0.00 sec)
筛选分数大于70分的
mysql> select name,score from tt where score>=70;
+----------+-------+
| name | score |
+----------+-------+
| wangwu | 70.00 |
| lisi | 90.00 |
| zhangsan | 80.00 |
+----------+-------+
3 rows in set (0.00 sec)
#筛选大于70分的进行升序排序 【对score字段排序】
mysql> select name,score from 666 where score>=70 order by score;
+----------+-------+
| name | score |
+----------+-------+
| wangwu | 70.00 |
| zhangsan | 80.00 |
| lisi | 90.00 |
+----------+-------+
3 rows in set (0.00 sec)
#进行降序排序
mysql> select name,score from 666 where score>=70 order by score desc;
+----------+-------+
| name | score |
+----------+-------+
| lisi | 90.00 |
| zhangsan | 80.00 |
| wangwu | 70.00 |
+----------+-------+
3 rows in set (0.00 sec)
按多字段排序
mysql> select * from info;
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 1 | zhangsan | 80 | 2 |
| 2 | lisi | 88 | 3 |
| 3 | wangwu | 90 | 1 |
| 4 | zhaoliu | 67 | 1 |
| 5 | zhaosi | 77 | 2 |
| 6 | zhouliu | 83 | 1 |
+----+----------+-------+-------+
6 rows in set (0.00 sec)
mysql> select * from info where 2=2 order by hobby desc,score desc;
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 2 | lisi | 88 | 3 |
| 1 | zhangsan | 80 | 2 |
| 5 | zhaosi | 77 | 2 |
| 3 | wangwu | 90 | 1 |
| 6 | zhouliu | 83 | 1 |
| 4 | zhaoliu | 67 | 1 |
+----+----------+-------+-------+
6 rows in set (0.00 sec)
只有第一个字段相同的情况下，第二字段排序才有意义
对结果进行分组
使用GROUP BY语句来实现分组
通常结合聚合函数一起使用
可以按一个或多个字段对结果进行分组
GROUP BY的语法结构
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;
常用的聚合函数
count （字段名称） 计数 函数
count （*） 技术
sum （*） 求和
avg （*） 平均值
max （*）最大
min （*） 最小值
GROUP BY分组
按hobby进行分组，统计每组的name个数
mysql> select count(name),hobby from info group by hobby;
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
| 3 | 1 |
| 2 | 2 |
| 1 | 3 |
+-------------+-------+
3 rows in set (0.00 sec)
GROUP BY结合ORDER BY
mysql> select count(name),hobby from info group by hobby order by count(name) desc;
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
| 3 | 1 |
| 2 | 2 |
| 1 | 3 |
+-------------+-------+
3 rows in set (0.00 sec)
限制结果条目LIMIT
只返回SELECT查询结果的第一行或前几行
使用LIMIT语句限制条目
LIMIT语法结构
SELECT column1,column2,...FROM table_name LIMIT[offset,] number;
number：返回记录行的最大数目
[offset,]：位置偏移量，从0开始
查看前三行
mysql> select * from info limit 3;
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 1 | zhangsan | 80 | 2 |
| 2 | lisi | 88 | 3 |
| 3 | wangwu | 90 | 1 |
+----+----------+-------+-------+
3 rows in set (0.00 sec)
查看4-6行
mysql> select * from info limit 3,3;
+----+---------+-------+-------+
| id | name | score | hobby |
+----+---------+-------+-------+
| 4 | zhaoliu | 67 | 1 |
| 5 | zhaosi | 77 | 2 |
| 6 | zhouliu | 83 | 1 |
+----+---------+-------+-------+
3 rows in set (0.00 sec)
设置别名
在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了
使用AS语句设置别名，关键字AS可省略
设置别名时，保证不能与库中其他表或字段名称冲突
别名的语法结构
SELECT column_name AS alias_name FROM table_name;
SELECT column_name(s)FROM table_name ASalias_name;
AS的用法
对于字段设置
给name和score设置别名
mysql> select name as 姓名,score as 成绩 from info;
+----------+--------+
| 姓名 | 成绩 |
+----------+--------+
| zhangsan | 80 |
| lisi | 88 |
| wangwu | 90 |
| zhaoliu | 67 |
| zhaosi | 77 |
| zhouliu | 83 |
+----------+--------+
6 rows in set (0.00 sec)
不加as语法也可以设置别名
mysql> select name 姓名,score 成绩 from info;
+----------+--------+
| 姓名 | 成绩 |
+----------+--------+
| zhangsan | 80 |
| lisi | 88 |
| wangwu | 90 |
| zhaoliu | 67 |
| zhaosi | 77 |
| zhouliu | 83 |
+----------+--------+
6 rows in set (0.00 sec)
表设置
给info表设置别名i，然后在name和score前面也要加i.使用，不加as也可以使用
mysql> select i.name as 姓名,i.score as 成绩 from info as i;
+----------+--------+
| 姓名 | 成绩 |
+----------+--------+
| zhangsan | 80 |
| lisi | 88 |
| wangwu | 90 |
| zhaoliu | 67 |
| zhaosi | 77 |
| zhouliu | 83 |
+----------+--------+
6 rows in set (0.00 sec)
作为连接语句
创建good新表，将info表的 score字段>=85的数据放在新表good上
mysql> create table good as select * from info where score >=85;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
可以看到主键、自增约束都没有了，
mysql> desc good;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | 0 | |
| name | varchar(10) | NO | | NULL | |
| score | int(4) | YES | | NULL | |
| hobby | int(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
数据导过去了
mysql> select * from good;
+----+--------+-------+-------+
| id | name | score | hobby |
+----+--------+-------+-------+
| 2 | lisi | 88 | 3 |
| 3 | wangwu | 90 | 1 |
+----+--------+-------+-------+
2 rows in set (0.00 sec)
通配符
通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。 通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务
用于替换字符串的部分字符
通常配合LIKE一起使用，并协同WHERE完成查询
常用的通配符：%表示零个、一个或多个
_表示单个字符
查询l开头的，%表示零个、一个或多个
mysql> select * from info where name like 'l%';
+----+------+-------+-------+
| id | name | score | hobby |
+----+------+-------+-------+
| 2 | lisi | 88 | 3 |
+----+------+-------+-------+
1 row in set (0.00 sec)
_下划线代表单个字符
mysql> select * from info where name like '_i_i';
+----+------+-------+-------+
| id | name | score | hobby |
+----+------+-------+-------+
| 2 | lisi | 88 | 3 |
+----+------+-------+-------+
1 row in set (0.00 sec)
两者结合使用
mysql> select name,score from tt where name like '_hu%';
+---------+-------+
| name | score |
+---------+-------+
| shuaige | 70.00 |
| shuaige | 76.00 |
+---------+-------+
2 rows in set (0.00 sec)
子查询
■ 也称作内查询或者嵌套查询
■ 先于主查询被执行，其结果将作为外层主查询的条件
■ 在增删改查中都可以使用子查询
■ 支持多层嵌套
■ IN语句是用来判断某个值是否在给定的结果集中
先建一个num表，里面只有id，在id字段在添加一些数据
mysql> select * from num;
+------+
| id |
+------+
| 1 |
| 3 |
| 5 |
| 7 |
+------+
4 rows in set (0.00 sec)
然后根据刚刚的info表和num表进行多表相连，按照num表的1、3、5、7显示出info表的1、3、5、7行数据
从最内的括号开始，括号内的结果是括号外的条件
注意！！！括号内作为结果输出的数据类型要与括号外的一样，不然无法查询。
mysql> select * from info where id in(select id from num);
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 1 | zhangsan | 80 | 2 |
| 3 | wangwu | 90 | 1 |
| 5 | zhaosi | 77 | 2 |
+----+----------+-------+-------+
多层嵌套，从内部括号到外面匹配
mysql> select * from info where id in(select id from num where name in(select name from num));
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 1 | zhangsan | 80 | 2 |
| 3 | wangwu | 90 | 1 |
| 5 | zhaosi | 77 | 2 |
+----+----------+-------+-------+
3 rows in set (0.00 sec)
mysql算数运算符号
mysql> select 1+5;
+-----+
| 1+5 |
+-----+
| 6 |
+-----+
1 row in set (0.00 sec)
mysql> select 1*5;
+-----+
| 1*5 |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)
mysql> select 1=2;
+-----+
| 1=2 |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)
mysql> select 1!=2;
+------+
| 1!=2 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> <> or != 用于判断数字，字符串，表达式不相等的判断,如果不相等，返回值为1，
否则返回值为0.
创建一个新表，把nanjing导入到新表中
#克隆tt表 新表命名为xi表
mysql> create table xi like tt;
#显示所有表
mysql> show tables;
+--------------+
| Tables_in_tt |
+--------------+
| gg |
| tt |
| xi |
+--------------+
3 rows in set (0.01 sec)
#xi表没有表结构 我们创建一下
mysql> select * from xi;
Empty set (0.00 sec)
#把tt表的beijing导入到xi表中
mysql> insert into xi select * from tt where id in (select id from tt where address='beijing');
mysql> select * from xi;
+----+----------+-------+---------+-------+
| id | name | score | address | hobby |
+----+----------+-------+---------+-------+
| 1 | wangwu | 70.00 | beijing | 2 |
| 2 | lisi | 80.00 | nanjing | 1 |
| 3 | zhangsan | 80.00 | beijing | 1 |
| 4 | zhaoliu | 60.00 | nanjing | 2 |
| 5 | tianqi | 80.00 | beijing | 1 |
+----+----------+-------+---------+-------+
5 rows in set (0.00 sec)
把没有达到80分的人的分数每人加5分
mysql> select * from tt;
+----+----------+-------+----------+-------+
| id | name | score | address | hobby |
+----+----------+-------+----------+-------+
| 1 | wangwu | 70.00 | beijing | 2 |
| 2 | lisi | 80.00 | nanjing | 1 |
| 3 | zhangsan | 80.00 | beijing | 1 |
| 4 | zhaoliu | 60.00 | nanjing | 2 |
| 5 | tianqi | 80.00 | beijing | 1 |
| 6 | shuaige | 70.00 | suzhou | 2 |
| 7 | tom | 87.00 | shanghai | 1 |
| 8 | shuaige | 76.00 | hangzhou | 2 |
+----+----------+-------+----------+-------+
#
mysql> update tt set score=score+5 where score<80;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from 666;
+----+----------+-------+----------+-------+
| id | name | score | address | hobby |
+----+----------+-------+----------+-------+
| 1 | wangwu | 75.00 | beijing | 2 |
| 2 | lisi | 80.00 | nanjing | 1 |
| 3 | zhangsan | 80.00 | beijing | 1 |
| 4 | zhaoliu | 65.00 | nanjing | 2 |
| 5 | tianqi | 80.00 | beijing | 1 |
| 6 | shuaige | 75.00 | suzhou | 2 |
| 7 | tom | 87.00 | shanghai | 1 |
| 8 | shuaige | 81.00 | hangzhou | 2 |
+----+----------+-------+----------+-------+
8 rows in set (0.00 sec)
NULL值
表示缺失的值
与数字0或者空白(spaces）是不同的
使用IS NULL或IS NOT NULL进行判断
NULL与空值的区别
空值长度为0，不占空间；NULL值的长度为NULL，占用空间 IS NULL无法判断空值 空值使用“=”或者“<>”来处理 COUNT（）计算时，NULL会忽略，不加入计算，空值会加入计算
正则表达式
例子
查询以wa为开头的字段
mysql> select * from tt where name regexp '^wa';
+----+--------+-------+---------+-------+
| id | name | score | address | hobby |
+----+--------+-------+---------+-------+
| 1 | wangwu | 70.00 | beijing | 2 |
+----+--------+-------+---------+-------+
1 row in set (0.01 sec)
mysql> select * from info where name regexp 'iu$';
+----+---------+-------+-------+
| id | name | score | hobby |
+----+---------+-------+-------+
| 4 | zhaoliu | 67 | 1 |
| 6 | zhouliu | 83 | 1 |
+----+---------+-------+-------+
2 rows in set (0.00 sec)
在666表中查询包含an字符串的name字段并打印
mysql> select * from 666 where name regexp 'an';
+----+----------+-------+---------+-------+
| id | name | score | address | hobby |
+----+----------+-------+---------+-------+
| 1 | wangwu | 70.00 | beijing | 2 |
| 3 | zhangsan | 80.00 | beijing | 1 |
| 5 | tianqi | 80.00 | beijing | 1 |
+----+----------+-------+---------+-------+
3 rows in set (0.00 sec)
#测试一下不连续的的是否能打印 这边我们就测试hi把 【shuai里的字符】
mysql> select * from tt where name regexp 'hi';
Empty set (0.00 sec)
#测试是必须连续的字母 不连续是不可以的
原表上做一些结构与数据的调整
mysql> select * from info;
+----+----------+-------+-------+----------+
| id | name | score | hobby | addr |
+----+----------+-------+-------+----------+
| 1 | zhangsan | 80 | 2 | NULL |
| 2 | lisi | 88 | 3 | NULL |
| 3 | wangwu | 90 | 1 | NULL |
| 4 | zhaoliu | 67 | 1 | NULL |
| 5 | zhaosi | 77 | 2 | NULL |
| 6 | zhouliu | 83 | 1 | NULL |
| 7 | woo | 79 | 2 | shanghai |
| 8 | wwwwo | 65 | 2 | hangzhou |
| 9 | woww | 84 | 3 | hangzhou |
+----+----------+-------+-------+----------+
9 rows in set (0.00 sec)
查询w任意个前面的字符，零次或多次
mysql> select * from info where name regexp 'ww*';
+----+--------+-------+-------+----------+
| id | name | score | hobby | addr |
+----+--------+-------+-------+----------+
| 3 | wangwu | 90 | 1 | NULL |
| 7 | woo | 79 | 2 | shanghai |
| 8 | wwwwo | 65 | 2 | hangzhou |
| 9 | woww | 84 | 3 | hangzhou |
+----+--------+-------+-------+----------+
4 rows in set (0.00 sec)
查询ow前面字符至少一次
mysql> select * from info where name regexp 'ow+';
+----+------+-------+-------+----------+
| id | name | score | hobby | addr |
+----+------+-------+-------+----------+
| 9 | woww | 84 | 3 | hangzhou |
+----+------+-------+-------+----------+
1 row in set (0.00 sec)
查询z开头的
mysql> select * from info where name regexp '^[z]';
+----+----------+-------+-------+------+
| id | name | score | hobby | addr |
+----+----------+-------+-------+------+
| 1 | zhangsan | 80 | 2 | NULL |
| 4 | zhaoliu | 67 | 1 | NULL |
| 5 | zhaosi | 77 | 2 | NULL |
| 6 | zhouliu | 83 | 1 | NULL |
+----+----------+-------+-------+------+
4 rows in set (0.00 sec)
查询不是z开头的
mysql> select * from info where name regexp '^[^z]';
+----+--------+-------+-------+----------+
| id | name | score | hobby | addr |
+----+--------+-------+-------+----------+
| 2 | lisi | 88 | 3 | NULL |
| 3 | wangwu | 90 | 1 | NULL |
| 7 | woo | 79 | 2 | shanghai |
| 8 | wwwwo | 65 | 2 | hangzhou |
| 9 | woww | 84 | 3 | hangzhou |
+----+--------+-------+-------+----------+
5 rows in set (0.00 sec)
匹配w及后面一个w两次
mysql> select * from info where name regexp 'ww{2}';
+----+-------+-------+-------+----------+
| id | name | score | hobby | addr |
+----+-------+-------+-------+----------+
| 8 | wwwwo | 65 | 2 | hangzhou |
+----+-------+-------+-------+----------+
运算符
MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符
算数运算符
例：以 SELECT 命令来实现最基础的加减乘除运算
mysql> select 2+3,2*4,8%2,9/2;
+-----+-----+------+--------+
| 2+3 | 2*4 | 8%2 | 9/2 |
+-----+-----+------+--------+
| 5 | 8 | 0 | 4.5000 |
+-----+-----+------+--------+
1 row in set (0.00 sec)
比较运算
等于运算符
等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同。例如，等于运算符在数字、字符串和表达式上的使用，具体操作如下所示
mysql> select 3=5,2='2','m'='m',3=NULL;
+-----+-------+---------+--------+
| 3=5 | 2='2' | 'm'='m' | 3=NULL |
+-----+-------+---------+--------+
| 0 | 1 | 1 | NULL |
+-----+-------+---------+--------+
1 row in set (0.00 sec)
不等于运算符
不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL
mysql> select 'wen'<>'tin',22!=33,NULL!=NULL;
+--------------+--------+------------+
| 'wen'<>'tin' | 22!=33 | NULL!=NULL |
+--------------+--------+------------+
| 1 | 1 | NULL |
+--------------+--------+------------+
1 row in set (0.01 sec)
字符串比的是字符的个数和顺序，例：
mysql> select 'abc'='abc';
+-------------+
| 'abc'='abc' |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select 'abc'='bca';
+-------------+
| 'abc'='bca' |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
大于、大于等于、小于、小于等于运算符
大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL。
小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL。
大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL。
小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL。
数值比较会自动转换ASCII表的数值
具体内容点击 https://baike.baidu.com/item/ASCII/309296?fr=aladdin
我们需要大体记住几个常用Dec（十进制）的就行：
0是48
大写A是65 B是66 依次往后推算
小写a是97 b是98
mysql> select 3>2,'b'>'c',5>=5,(3+4)>=(1+2),'A'<='a',5<=5.5,'n'>=NULL;
+-----+---------+------+--------------+----------+--------+-----------+
| 3>2 | 'b'>'c' | 5>=5 | (3+4)>=(1+2) | 'A'<='a' | 5<=5.5 | 'n'>=NULL |
+-----+---------+------+--------------+----------+--------+-----------+
| 1 | 0 | 1 | 1 | 1 | 1 | NULL |
+-----+---------+------+--------------+----------+--------+-----------+
1 row in set (0.00 sec)
比较字符串是或的关系，一旦开头的b比a大，后面就不再比较
mysql> select 'abc'<'baa';
+-------------+
| 'abc'<'baa' |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
IS NULL、IS NOT NULL
SNULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回 0。
IS NOT NULL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回 0
mysql> select 2 is NULL,'d' is not NULL,NULL IS NULL;
+-----------+-----------------+--------------+
| 2 is NULL | 'd' is not NULL | NULL IS NULL |
+-----------+-----------------+--------------+
| 0 | 1 | 1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)
BETWEEN AND
BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间
mysql> select 5 between 2 and 8,7 between 5 and 10,'d' between 'a' and 'z';
+-------------------+--------------------+-------------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 'd' between 'a' and 'z' |
+-------------------+--------------------+-------------------------+
| 1 | 1 | 1 |
+-------------------+--------------------+-------------------------+
1 row in set (0.00 sec)
数字能否与字符比较？
mysql> select 5 between 2 and 8,7 between 5 and 10,8 between 'a' and 'z';
+-------------------+--------------------+-----------------------+
| 5 between 2 and 8 | 7 between 5 and 10 | 8 between 'a' and 'z' |
+-------------------+--------------------+-----------------------+
| 1 | 1 | 0 |
+-------------------+--------------------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
#数字不能与字符比较的
LEAS、GREATEST
LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。
GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL， 则返回结果就为 NULL。
mysql> SELECT least(1,2,3),least('a','d','f'),greatest(1,2,3),greatest('a','d','f');
+--------------+--------------------+-----------------+-----------------------+
| least(1,2,3) | least('a','d','f') | greatest(1,2,3) | greatest('a','d','f') |
+--------------+--------------------+-----------------+-----------------------+
| 1 | a | 3 | f |
+--------------+--------------------+-----------------+-----------------------+
1 row in set (0.00 sec)
min是否能求最小值呢
mysql> select min(2,3,4),min('d','e','f');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3,4),min('d','e','f')' at line 1
IN、NOT IN
IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。
NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0。
mysql> select 2 in (1,2,3,4,5),'c' not in ('a','b','c');
+------------------+--------------------------+
| 2 in (1,2,3,4,5) | 'c' not in ('a','b','c') |
+------------------+--------------------------+
| 1 | 0 |
+------------------+--------------------------+
1 row in set (0.00 sec)
从以上结果可以看出，IN 和 NOT IN 的返回值正好相反。
LIKE、NOT LIKE
LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0。LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符，而’_’只能匹配一个字符。NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0
mysql> select 'shuai' like 'shu_','xiao' like '%k','lisi' not like '%a';
+---------------------+------------------+----------------------+
| 'shuai' like 'shu_' | 'xiao' like '%k' | 'lisi' not like '%a' |
+---------------------+------------------+----------------------+
| 0 | 0 | 1 |
+---------------------+------------------+----------------------+
1 row in set (0.00 sec)
逻辑运算符
逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回 0，真和假也可以用 TRUE 和 FALSE 表示
又被称布尔值运算符
用来判断表达式的真假
常用的逻辑运算符号
逻辑非
逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL
例如，对非 0 值和 0 值分别作逻辑非运算
mysql> select not 0,!2,!(4-4);
+-------+----+--------+
| not 0 | !2 | !(4-4) |
+-------+----+--------+
| 1 | 0 | 1 |
+-------+----+--------+
1 row in set (0.00 sec)
逻辑与
逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示
例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算
mysql> select 2&&3,0&&1,0&&null,1 and null;
+------+------+---------+------------+
| 2&&3 | 0&&1 | 0&&null | 1 and null |
+------+------+---------+------------+
| 1 | 0 | 0 | NULL |
+------+------+---------+------------+
1 row in set (0.01 sec)
逻辑或
逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算。
一旦有个非0值，出来就是非0值
注意：逻辑或不要用 ||,一定要用or，因为使用||会出现问题
mysql> select 2 or 3,2 or 0;
+--------+--------+
| 2 or 3 | 2 or 0 |
+--------+--------+
| 1 | 1 |
+--------+--------+
1 row in set (0.00 sec)
有null结果就会为null
位运算符
位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看
例：对数字进行按位与、或和取反运算
mysql> select 10&15,10|15,10^15,5 &~1;
+-------+-------+-------+-------+
| 10&15 | 10|15 | 10^15 | 5 &~1 |
+-------+-------+-------+-------+
| 10 | 15 | 5 | 4 |
+-------+-------+-------+-------+
1 row in set (0.00 sec)
按位与运算（&），是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0，所以 10 & 15 的结果为 10。
按位或运算（|），是对应的二进制位有一个或两个为 1 的，运算结果为 1，否则为 0， 所以 10 | 15 的结果为 15。
按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0，所以 10 ^ 15的结果为 5。
按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101进行求与操作，其结果是二进制的 0100，转换为十进制就是 4。
列如对数字进行左移动或右移的运算
mysql> select 1<<2;
+------+
| 1<<2 |
+------+
| 4 |
+------+
1 row in set (0.01 sec)
mysql> select 10<<2;
+-------+
| 10<<2 |
+-------+
| 40 |
+-------+
1 row in set (0.00 sec)
<<左移位运算 向左移动两位，空缺出补0
1<<2
0001
0100 左移后
10<<2
1010
101000 左移后
运算符的优先级
以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运 算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算。如果不确定所使用的运算符的优先级，可以使用（）改变优先级
连接查询
MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接
内连接
内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表
mysql> select info.id,info.name from info inner join num on info.id=num.id;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 3 | wangwu |
| 5 | zhaosi |
| 7 | woo |
+----+----------+
4 rows in set (0.00 sec
左连接
也被称为左外连接
在FROM子句中使用LEFT JOIN关键字来表示
匹配左表中所有及右表中符合条件的行
例：用左连接查询hpbby表
mysql> select i.id,i.name,h.hobby from info i left join hobby h on i.hobby=h.id;
+----+----------+--------+
| id | name | hobby |
+----+----------+--------+
| 3 | wangwu | 绘画 |
| 4 | zhaoliu | 绘画 |
| 6 | zhouliu | 绘画 |
| 1 | zhangsan | 奥数 |
| 5 | zhaosi | 奥数 |
| 7 | woo | 奥数 |
| 8 | wwwwo | 奥数 |
| 2 | lisi | 体育 |
| 9 | woww | 体育 |
+----+----------+--------+
9 rows in set (0.00 sec)
右连接
也被称为右外连接
在FROM子句中使用RIGHT JOIN 关键字来表示
匹配右表中所有行及左表中符合条件的行
mysql> select i.id,i.name,h.hobby from info i right join hobby h on i.hobby=h.id;
+------+----------+--------+
| id | name | hobby |
+------+----------+--------+
| 1 | zhangsan | 奥数 |
| 2 | lisi | 体育 |
| 3 | wangwu | 绘画 |
| 4 | zhaoliu | 绘画 |
| 5 | zhaosi | 奥数 |
| 6 | zhouliu | 绘画 |
| 7 | woo | 奥数 |
| 8 | wwwwo | 奥数 |
| 9 | woww | 体育 |
+------+----------+--------+
9 rows in set (0.00 sec)
MySQL数据库函数
常用的函数分类
数学函数
聚合函数
字符串函数
日期时间函数
数学函数
abs（x）返回x的绝对值
mysql> select abs(-2),abs(-3.14);
+---------+------------+
| abs(-2) | abs(-3.14) |
+---------+------------+
| 2 | 3.14 |
+---------+------------+
1 row in set (0.00 sec)
mysql> select abs(2),abs(-3.14);
+--------+------------+
| abs(2) | abs(-3.14) |
+--------+------------+
| 2 | 3.14 |
+--------+------------+
1 row in set (0.00 sec)
rand（）返回0到1的随机数
mysql> select rand();
±-------------------+
| rand() |
±-------------------+
| 0.9606223953521088 |
±-------------------+
1 row in set (0.00 sec)
取值范围：0<=x<1
mod（x,y）返回x除以y以后的余数
mysql> select mod(10,2);
+-----------+
| mod(10,2) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
power（x，y）返回x的y次方
mysql> select power(3,3);
+------------+
| power(3,3) |
+------------+
| 27 |
+------------+
1 row in set (0.00 sec)
3的3次方
mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
| 8 |
+------------+
1 row in set (0.00 sec)
round（x） 返回离x最近的整数
#返回的是整数 四舍五入
mysql> select round(2.49);
+-------------+
| round(2.49) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql> select round(2.59);
+-------------+
| round(2.59) |
+-------------+
| 3 |
+-------------+
1 row in set (0.01 sec)
mysql> select round(1.49,1);
+---------------+
| round(1.49,1) |
+---------------+
| 1.5 |
+---------------+
1 row in set (0.00 sec)
mysql> select round(1.49,2);
+---------------+
| round(1.49,2) |
+---------------+
| 1.49 |
+---------------+
1 row in set (0.00 sec)
sqrt（x）返回x的平方跟
mysql> select sqrt(4);
+---------+
| sqrt(4) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
truncate(x,y)返回数字x截断为y位小数的值
mysql> select truncate(3.1415926,3);
+-----------------------+
| truncate(3.1415926,3) |
+-----------------------+
| 3.141 |
+-----------------------+
1 row in set (0.00 sec)
ceil（x）返回大于或等于x的最小整数
mysql> select ceil(1.4);
+-----------+
| ceil(1.4) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
mysql> select ceil(1.9);
+-----------+
| ceil(1.9) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
floor（x）返回小于或等于x的最大整数
mysql> select floor(1.99);
+-------------+
| floor(1.99) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select floor(1.09);
+-------------+
| floor(1.09) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
greates（x1，x2…)返回集合中最大得值
least（x1,x2…)返回集合中最小的值
mysql> select greatest(11,12,13),least(10,12,14);
+--------------------+-----------------+
| greatest(11,12,13) | least(10,12,14) |
+--------------------+-----------------+
| 13 | 10 |
+--------------------+-----------------+
1 row in set (0.0 sec)
字符串函数
length（x）返回字符串x的值
#空格也算长度
mysql> select length('aaaa');
+----------------+
| length('aaaa') |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
mysql> select length('aa aa');
+-----------------+
| length('aa aa') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
trim（）返回去除指定格式的值
mysql> select trim('abcd');
+--------------+
| trim('abcd') |
+--------------+
| abcd |
+--------------+
1 row in set (0.00 sec)
mysql> select trim(' abcd');
+----------------+
| trim(' abcd') |
+----------------+
| abcd |
+----------------+
1 row in set (0.00 sec)
mysql> select trim('ab cd');
+---------------+
| trim('ab cd') |
+---------------+
| ab cd |
+---------------+
1 row in set (0.00 sec)
#只能去除前后两端空格 不能过滤中间的空格
concat（x，y）将提供的参数x和y拼接成一个字符串
mysql> select concat('abc','fff');
+---------------------+
| concat('abc','fff') |
+---------------------+
| abcfff |
+---------------------+
1 row in set (0.00 sec)
#空格是不能被过滤
mysql> select concat('abc',' fff');
+------------------------+
| concat('abc',' fff') |
+------------------------+
| abc fff |
+------------------------+
1 row in set (0.00 sec)
字符串有空格进行去除相连
mysql> select concat('asd',trim(' qwe'));
+-----------------------------+
| concat('asd',trim(' qwe')) |
+-----------------------------+
| asdqwe |
+-----------------------------+
1 row in set (0.00 sec)
upper（x）将字符串x的所有字母变为大写字母
lower（x）将字符串x的所有字母变为小写字母
mysql> select upper('abc'),lower('QWE');
+--------------+--------------+
| upper('abc') | lower('QWE') |
+--------------+--------------+
| ABC | qwe |
+--------------+--------------+
1 row in set (0.00 sec)
left（x，y）返回字符串的前y个字符
right（x，y）返回字符的后y个字符
截图前3个字符
mysql> select left('abcert',3);
+------------------+
| left('abcert',3) |
+------------------+
| abc |
+------------------+
1 row in set (0.00 sec)
截取后3
mysql> select right('abcert',3);
+-------------------+
| right('abcert',3) |
+-------------------+
| ert |
+-------------------+
1 row in set (0.00 sec)
怎么去除中间的c字母？？
mysql> select concat(left('abcert',3),right('abcert',3));
+--------------------------------------------+
| concat(left('abcert',3),right('abcert',3)) |
+--------------------------------------------+
| abcert |
+--------------------------------------------+
1 row in set (0.00 sec)
repeat（x，y）将字符串重复y次
mysql> select repeat('qwer',3);
+------------------+
| repeat('qwer',3) |
+------------------+
| qwerqwerqwer |
+------------------+
1 row in set (0.00 sec)
replace（x，y，z）将字符串z代替字符串x中的字符y
mysql> select replace('shuai','ua','xx');
+----------------------------+
| replace('shuai','ua','xx') |
+----------------------------+
| shxxi |
+----------------------------+
1 row in set (0.00 sec)
strcmp（x，y）比较x和y，返回的值可以为-1，0，1
mysql> select strcmp(14,5);
+--------------+
| strcmp(14,5) |
+--------------+
| -1 |
+--------------+
1 row in set (0.00 sec)
mysql> select strcmp(14,14);
+---------------+
| strcmp(14,14) |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql> select strcmp(27,7);
+--------------+
| strcmp(27,7) |
+--------------+
| -1 |
+--------------+
1 row in set (0.00 sec)
#比较的是第一个数
substring（x，y，z）获取从字符串的x中的第y个位置开始长度为z的字符串
mysql> select substring('abcdefg',2,3);
+--------------------------+
| substring('abcdefg',2,3) |
+--------------------------+
| bcd |
+--------------------------+
1 row in set (0.00 sec)
#substring（完整字符串，起始位置，长度）；
reverse（x）将字符串x反转
#倒过来输出
mysql> select reverse ('abc');
+-----------------+
| reverse ('abc') |
+-----------------+
| cba |
+-----------------+
1 row in set (0.00 sec)
#反过来大写
mysql> select upper(reverse ('abc'));
+------------------------+
| upper(reverse ('abc')) |
+------------------------+
| CBA |
+------------------------+
1 row in set (0.00 sec)
日期时间函数
curdate（）返回当前时间的年月日
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-11-03 |
+------------+
1 row in set (0.00 sec)
curtime（）返回当前时间的时分秒
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:34:44 |
+-----------+
1 row in set (0.00 sec)
now（）返回当前时间的日期和时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-11-03 10:35:53 |
+---------------------+
1 row in set (0.00 sec)
mount（x）返日期x中的月份值
mysql> select month('2020-11-03');
+---------------------+
| month('2020-11-03') |
+---------------------+
| 8 |
+---------------------+
1 row in set (0.00 sec)
week（x）返回日期x是年度第几个星期
mysql> select week('2020-11-03');
+--------------------+
| week('2020-11-03') |
+--------------------+
| 34 |
+--------------------+
1 row in set (0.00 sec)
hour（x）返回x中的小时值
mysql> select hour('10:35:53');
+------------------+
| hour('10:35:53') |
+------------------+
| 10 |
+------------------+
1 row in set (0.00 sec)
显示当前的小时/分钟/秒
mysql> select hour(curtime());
+-----------------+
| hour(curtime()) |
+-----------------+
| 16 |
+-----------------+
1 row in set (0.00 sec)
#小时
mysql> select minute(curtime());
+-------------------+
| minute(curtime()) |
+-------------------+
| 41 |
+-------------------+
1 row in set (0.00 sec
#显示当前的秒
dayofweek（x）返回x是星期几，1星期日，2是星期一
mysql> select dayofweek(curdate());
+----------------------+
| dayofweek(curdate()) |
+----------------------+
| 2 |
+----------------------+
1 row in set (0.00 sec)
dayofmonth（x）计算日期x是本月的第几天
mysql> select dayofmonth(curdate());
+-----------------------+
| dayofmonth(curdate()) |
+-----------------------+
| 3 |
+-----------------------+
1 row in set (0.00 sec)
dayodyear（x）计算日期x是本年的第几天
mysql> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
| 308 |
+----------------------+
1 row in set (0.00 sec)
存储过程
创建存储过程语法
使用 CREATE PROCEDURE 语句创建存储过程
声明语句结束符：
DELIMITER $$
或
DELIMITER //
声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int)
存储过程开始和结束符号:
BEGIN .... END
变量赋值:
SET @p_in=1
变量定义:
DECLARE l_int int unsigned default 4000000;
创建mysql存储过程、存储函数:
create procedure 存储过程名(参数)
创建mysql存储过程、存储函数:
create procedure 存储过程名(参数)
存储过程体:
create function 存储函数名(参数)
创建数据库
mysql> delimiter $$ #将语句的结束符从分号开始；临时改为两个$$
mysql> create procedure 666()
-> begin
-> select name,score from 666;
-> end$$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ; #将语句的结束符恢复为分号
查询存储过程
mysql> mysql> show procedure status where db='666';
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| tt | TT | PROCEDURE | root@localhost | 2020-11-03 10:55:07 | 2020-11-03 10:55:07 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
#不做为where条件筛选会输出的东西比较多
调用存储过程：
mysql> call TT();
+----------+-------+
| name | score |
+----------+-------+
| wangwu | 85.00 |
| lisi | 90.00 |
| zhangsan | 80.00 |
| zhaoliu | 75.00 |
| tianqi | 80.00 |
| shuaige | 75.00 |
| tom | 86.00 |
| shuaige | 71.00 |
+----------+-------+
8 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
存储过程的参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
查看指定用户信息
mysql> delimiter $$
mysql> create procedure PP(in my_name varchar(10))
-> begin
-> select name,score from tt where name=my_name;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
#查询存储
mysql> show procedure status where db='666';
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| tt | PP | PROCEDURE | root@localhost | 2020-11-03 11:05:04 | 2020-11-03 11:05:04 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| tt | TT | PROCEDURE | root@localhost | 2020-08-25 19:55:07 | 2020-08-25 19:55:07 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+----+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.01 sec)
#实现调用
mysql> call PP('zhangsan');
+------+-------+
| name | score |
+------+-------+
| zhangsan | 80.00 |
+------+-------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
MySQL存储过程的控制语句
变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3()
-> begin
-> declare x1 varchar(5) default 'outer';
-> begin
-> declare x1 varchar(5) default 'inner';
-> select x1;
-> end;
-> select x1;
-> end;
-> //
mysql > DELIMITER ;
if-then-else 条件语句
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc2(IN parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> if var=0 then
-> insert into t values(17);
-> end if;
-> if parameter=0 then
-> update t set s1=s1+1;
-> else
-> update t set s1=s1+2;
-> end if;
-> end;
-> //
mysql > DELIMITER ;
case语句
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3 (in parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> case var
-> when 0 then
-> insert into t values(17);
-> when 1 then
-> insert into t values(18);
-> else
-> insert into t values(19);
-> end case;
-> end;
-> //
mysql > DELIMITER ;
case
when var=0 then
insert into t values(30);
when var>0 then
when var<0 then
else
end case
循环语句
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc4()
-> begin
-> declare var int;
-> set var=0;
-> while var<6 do
-> insert into t values(var);
-> set var=var+1;
-> end while;
-> end;
-> //
mysql > DELIMITER ;
while 条件 do
--循环体
endwhile
389
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
