Java基础入门day39

最新推荐文章于 2024-05-06 21:32:45 发布
最新推荐文章于 2024-05-06 21:32:45 发布
阅读量232 收藏 9
点赞数 3
文章标签： java mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zpz2001/article/details/138208637
版权

day39

DQL

查询结果去重

语法：

distinct 列明

select distinct sex from tb_stu;        //  性别去重，最终只保留两个结果boy和girl
select distinct name, sex from tb_stu;  //  姓名和性别同时去重，sex部分字段值相同，不能去重，去重只能是所有字段都相同才可以实现去重效果，只有                                          //      name和sex都相同才会实现去重效果
mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
+------+--------------+------+------+-------+------+
3 rows in set (0.01 sec)
​
mysql> select sex from tb_stu;
+------+
| sex  |
+------+
| boy  |
| girl |
| girl |
+------+
3 rows in set (0.00 sec)
​
mysql> select distinct sex from tb_stu;
+------+
| sex  |
+------+
| boy  |
| girl |
+------+
2 rows in set (0.00 sec)
​
mysql> select name, sex from tb_stu;
+--------------+------+
| name         | sex  |
+--------------+------+
| zhouxingxing | boy  |
| qiuxiang     | girl |
| shiliu       | girl |
+--------------+------+
3 rows in set (0.00 sec)
​
mysql> select distinct name, sex from tb_stu;
+--------------+------+
| name         | sex  |
+--------------+------+
| zhouxingxing | boy  |
| qiuxiang     | girl |
| shiliu       | girl |
+--------------+------+
3 rows in set (0.00 sec)
​
mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
+------+--------------+------+------+-------+------+
3 rows in set (0.00 sec)
​
mysql> insert into tb_stu values(9530, 'qiuxiang', 'girl', 110, 80, 18);
Query OK, 1 row affected (0.01 sec)
​
mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)
​
mysql> select name, sex from tb_stu;
+--------------+------+
| name         | sex  |
+--------------+------+
| zhouxingxing | boy  |
| qiuxiang     | girl |
| shiliu       | girl |
| qiuxiang     | girl |
+--------------+------+
4 rows in set (0.00 sec)
​
mysql> select distinct name, sex from tb_stu;
+--------------+------+
| name         | sex  |
+--------------+------+
| zhouxingxing | boy  |
| qiuxiang     | girl |
| shiliu       | girl |
+--------------+------+
3 rows in set (0.00 sec)

排序

语法：

select 列名 from 表名 order by 排序列 [排序规则]

单列排序

select * from tb_stu order by score;                //  根据成绩来排序，默认排序规则是升序排列 
select * from tb_stu order by score asc;            //  asc是升序关键字，默认就是升序，可以不写
select * from tb_stu order by score desc;           //  desc降序排序的关键字
排序规则描述
asc对前面排序列做升序排序，也是默认值
desc对前面排序列做降序排列

多列排序

select * from tb_stu order by sex desc, score;
select * from tb_stu order by sex desc, score asc;
select * from tb_stu order by sex desc, score desc;
mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)
​
mysql> select * from tb_stu order by sex;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)
​
mysql> select * from tb_stu order by sex asc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)
​
mysql> select * from tb_stu order by sex desc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)
​
mysql> select * from tb_stu order by sex desc, score;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)
​
mysql> select * from tb_stu order by sex desc, score desc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)
​
mysql> select * from tb_stu order by sex desc, score asc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

条件查询

语法：
​
select 列名 from 表名 where 条件
关键字描述
where条件在查询结果中，筛选符合条件的查询结果，条件为布尔表达式

  • 等值判断

select * from tb_stu where age = 30;        //  查询age为30的学生
注意： 与Java的==不同，mysql中等值判断使用=

  • 逻辑判断（and or not）

select * from tb_stu where age = 30 and sex = 'girl';			//	and表示交集，得同时满足，查询age为30并且sex同时为girl结果
select * from tb_stu where age = 30 or sex = 'girl';			//	or表示并集，任何条件满足都可以，查询age为30或者sex为girl的所有结果
select * from tb_stu where not age = 30;						//	not取反，对于现有结果取反操作，年龄不为30的所有结果
mysql> select * from tb_stu where age = 30 and sex = 'girl';
+------+--------+------+------+-------+------+
| sid  | name   | sex  | tel  | score | age  |
+------+--------+------+------+-------+------+
| 9529 | shiliu | girl | 114  |    59 |   30 |
+------+--------+------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from tb_stu where age = 30 or sex = 'girl';
+------+----------+------+------+-------+------+
| sid  | name     | sex  | tel  | score | age  |
+------+----------+------+------+-------+------+
| 9528 | qiuxiang | girl | 110  |    80 |   18 |
| 9529 | shiliu   | girl | 114  |    59 |   30 |
| 9530 | qiuxiang | girl | 110  |    80 |   18 |
+------+----------+------+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from tb_stu where not age = 30;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

  • 不等值判断（> >= < <= != <>）

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu where sex < 60;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set, 5 warnings (0.00 sec)

mysql> select * from tb_stu where score < 60;
+------+--------+------+------+-------+------+
| sid  | name   | sex  | tel  | score | age  |
+------+--------+------+------+-------+------+
| 9529 | shiliu | girl | 114  |    59 |   30 |
+------+--------+------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from tb_stu where score >= 60;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu where score = 100;
Empty set (0.00 sec)

mysql> select * from tb_stu where score != 100;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu where score <> 100;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)
mysql> select * from tb_stu where score <= 100 and score >= 80;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
3 rows in set (0.00 sec)

  • 区间判断（between and）

mysql> select * from tb_stu where score between 60 and 100;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu where score between 80 and 100;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from tb_stu where score between 80 and 99;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from tb_stu where score between 180 and 99;
Empty set (0.00 sec)

注意：在区间判断的语法中，小值在前，大值在后，反之将得不到结果

  • null值判断（is null， is not null）

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select * from tb_stu where name is null;
Empty set (0.00 sec)

mysql> select * from tb_stu where sex is null;
+------+-----------+------+------+-------+------+
| sid  | name      | sex  | tel  | score | age  |
+------+-----------+------+------+-------+------+
| 9532 | chunxiang | NULL | NULL |  NULL | NULL |
+------+-----------+------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from tb_stu where sex is not null;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

判断某个字段是否为空，或者是否不为空

  • 枚举查询（in（值1， 值2， ...））

select * from tb_stu where sid in (9527, 9528, 9599);			//	查询sid分别为9527， 9528和9599的值，9599不存在，则不显示，也不报																  //		错，只列出9527 和9528的值

注意：in的查询效率较低，可以通过多条件拼接

  • 模糊查询 like

like _（单个字符）

like %（任意长度的字符）
select * from tb_stu where name like '%ng'						//	查询name以ng结尾的所有结果
select * from tb_stu where name like '_ng'						//	查询name以ng结尾，并且ng前面只有一个字符的结果
select * from tb_stu where name like 'xiang'					//	查询name跟xiang相像的结果，没有使用任何通配符，类似于等值查询
select * from tb_stu where name like '___xiang'					//	查询name以xiang结尾，并且xiang之前由三个字符
mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select * from tb_stu where name like '%ng';
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu where name like '_ng';
Empty set (0.00 sec)

mysql> select * from tb_stu where name like 'qiuxiang';
+------+----------+------+------+-------+------+
| sid  | name     | sex  | tel  | score | age  |
+------+----------+------+------+-------+------+
| 9528 | qiuxiang | girl | 110  |    80 |   18 |
| 9530 | qiuxiang | girl | 110  |    80 |   18 |
+------+----------+------+------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from tb_stu where name like 'xiang';
Empty set (0.00 sec)

mysql> select * from tb_stu where name like '___xiang';
+------+----------+------+------+-------+------+
| sid  | name     | sex  | tel  | score | age  |
+------+----------+------+------+-------+------+
| 9528 | qiuxiang | girl | 110  |    80 |   18 |
| 9530 | qiuxiang | girl | 110  |    80 |   18 |
+------+----------+------+------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from tb_stu where name like '____xiang';
+------+-----------+------+------+-------+------+
| sid  | name      | sex  | tel  | score | age  |
+------+-----------+------+------+-------+------+
| 9532 | chunxiang | NULL | NULL |  NULL | NULL |
+------+-----------+------+------+-------+------+
1 row in set (0.00 sec)

注意：

%代表任意长度字符

_代表任意的单个字符

没有任何通配符，即使使用like也与等值查询效果一致

  • 分支结构查询

基本语法：
case 
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
else 结果n
end
select sid, name, 
	case 
		when score >= 90 then 'A' 
		when score >= 80 then 'B'
		when score >= 70 then 'C'
		when score >= 60 then 'D'
		else 'E'
		end
from tb_stu;
select sid, name, 
	case 
		when score >= 90 then 'A' 
		when score >= 80 then 'B'
		when score >= 70 then 'C'
		when score >= 60 then 'D'
		else 'E'
		end as 'level'
from tb_stu;

时间查询

语法：

select 时间函数（参数列表）
执行时间函数，会自动生成一张虚拟表，一行一列
时间函数描述
now()当前系统时间（y M d h m s）,不是所有的版本都支持
sysdate()当前系统时间（y M d h m s）
curdate()当前的日期
curtime()当前时间
week(date)当前日期是一年中的第几周
year(date)获取指定日期的年份
hour(date)获取指定时间的小时值
minute(date)获取指定时间的分钟值
adddate(date, n)计算指定date加上n天后的值，n也可以为负值
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-04-24 11:21:05 |
+---------------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| saas       |
+------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-04-24 11:36:28 |
+---------------------+
1 row in set (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2024-04-24 11:38:30 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2024-04-24 |
+------------+
1 row in set (0.00 sec)

mysql> select curtiem();
ERROR 1305 (42000): FUNCTION saas.curtiem does not exist
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 11:39:30  |
+-----------+
1 row in set (0.00 sec)

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          16 |
+-------------+
1 row in set (0.00 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2024 |
+-------------+
1 row in set (0.00 sec)

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            42 |
+---------------+
1 row in set (0.00 sec)

mysql> select adddate(now(), 5)
 -> ;
+---------------------+
| adddate(now(), 5)   |
+---------------------+
| 2024-04-29 11:42:47 |
+---------------------+
1 row in set (0.00 sec)

mysql> select adddate(now(), 8);
+---------------------+
| adddate(now(), 8)   |
+---------------------+
| 2024-05-02 11:43:17 |
+---------------------+
1 row in set (0.00 sec)

mysql> select adddate(now(), -8);
+---------------------+
| adddate(now(), -8)  |
+---------------------+
| 2024-04-16 11:43:45 |
+---------------------+
1 row in set (0.00 sec)

字符串查询

语法：
select 字符串函数
字符串函数描述
concat(str1, str2, .. )将多个字符串拼接
insert(str, pos, len, newStr)将str中指定pos位置开始len个长度的内容替换为newStr
lower(str)将指定字符串转换为小写
upper(str)将指定的字符串转换为大写
substring(str, num, len)将str字符串指定num位置开始截取len个内容
mysql> select concat("hello", "world");
+--------------------------+
| concat("hello", "world") |
+--------------------------+
| helloworld               |
+--------------------------+
1 row in set (0.00 sec)

mysql> select concat("hello", "world", "saas");
+----------------------------------+
| concat("hello", "world", "saas") |
+----------------------------------+
| helloworldsaas                   |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select concat("My", "SQL");
+---------------------+
| concat("My", "SQL") |
+---------------------+
| MySQL               |
+---------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 3, 0, "MySQL");
+-----------------------------------------+
| insert("这是一个数据库", 3, 0, "MySQL") |
+-----------------------------------------+
| 这是MySQL一个数据库                     |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 3, 2, "MySQL");
+-----------------------------------------+
| insert("这是一个数据库", 3, 2, "MySQL") |
+-----------------------------------------+
| 这是MySQL数据库                         |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 3, 2, "关系");
+----------------------------------------+
| insert("这是一个数据库", 3, 2, "关系") |
+----------------------------------------+
| 这是关系数据库                         |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 3, 0, "关系");
+----------------------------------------+
| insert("这是一个数据库", 3, 0, "关系") |
+----------------------------------------+
| 这是关系一个数据库                     |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 5, 0, "关系");
+----------------------------------------+
| insert("这是一个数据库", 5, 0, "关系") |
+----------------------------------------+
| 这是一个关系数据库                     |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select lower("MySQL");
+----------------+
| lower("MySQL") |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

mysql> select upper("MySQL");
+----------------+
| upper("MySQL") |
+----------------+
| MYSQL          |
+----------------+
1 row in set (0.00 sec)

mysql> select substring("javaMySQLOracle", 5, 5)
 -> ;
+------------------------------------+
| substring("javaMySQLOracle", 5, 5) |
+------------------------------------+
| MySQL                              |
+------------------------------------+
1 row in set (0.00 sec)

聚合函数

语法：

select 聚合函数() from 表名
聚合函数描述
count()求总行数
max()求最大值
min()求最小值
sum()求和
avg()求平均值
mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select count(*) from tb_stu;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(sid) from tb_stu;
+------------+
| count(sid) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)

mysql> select count(1) from tb_stu;
+----------+
| count(1) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select max(score) from tb_stu;
+------------+
| max(score) |
+------------+
|         99 |
+------------+
1 row in set (0.00 sec)

mysql> select min(score) from tb_stu;
+------------+
| min(score) |
+------------+
|         59 |
+------------+
1 row in set (0.00 sec)

mysql> select sum(score) from tb_stu;
+------------+
| sum(score) |
+------------+
|        395 |
+------------+
1 row in set (0.00 sec)

mysql> select avg(score) from tb_stu;
+------------+
| avg(score) |
+------------+
|         79 |
+------------+
1 row in set (0.00 sec)

注意：聚合函数自动忽略null值，所有的null将不进行统计

分组查询

语法：

select 列名 form表名 where 条件 group by 分组依据

group by根据指定分组依据进行分组

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select max(score) from tb_stu;
+------------+
| max(score) |
+------------+
|         99 |
+------------+
1 row in set (0.00 sec)

mysql> select max(score) from tb_stu group by sex;
+------------+
| max(score) |
+------------+
|       NULL |
|         99 |
|         80 |
+------------+
3 rows in set (0.00 sec)

mysql> select min(score) from tb_stu group by sex;
+------------+
| min(score) |
+------------+
|       NULL |
|         77 |
|         59 |
+------------+
3 rows in set (0.00 sec)


mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.43-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use saas;
Database changed
mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
+------+--------------+------+------+-------+------+
3 rows in set (0.01 sec)

mysql> select sex from tb_stu;
+------+
| sex  |
+------+
| boy  |
| girl |
| girl |
+------+
3 rows in set (0.00 sec)

mysql> select distinct sex from tb_stu;
+------+
| sex  |
+------+
| boy  |
| girl |
+------+
2 rows in set (0.00 sec)

mysql> select name, sex from tb_stu;
+--------------+------+
| name         | sex  |
+--------------+------+
| zhouxingxing | boy  |
| qiuxiang     | girl |
| shiliu       | girl |
+--------------+------+
3 rows in set (0.00 sec)

mysql> select distinct name, sex from tb_stu;
+--------------+------+
| name         | sex  |
+--------------+------+
| zhouxingxing | boy  |
| qiuxiang     | girl |
| shiliu       | girl |
+--------------+------+
3 rows in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
+------+--------------+------+------+-------+------+
3 rows in set (0.00 sec)

mysql> insert into tb_stu values(9530, 'qiuxiang', 'girl', 110, 80, 18);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select name, sex from tb_stu;
+--------------+------+
| name         | sex  |
+--------------+------+
| zhouxingxing | boy  |
| qiuxiang     | girl |
| shiliu       | girl |
| qiuxiang     | girl |
+--------------+------+
4 rows in set (0.00 sec)

mysql> select distinct name, sex from tb_stu;
+--------------+------+
| name         | sex  |
+--------------+------+
| zhouxingxing | boy  |
| qiuxiang     | girl |
| shiliu       | girl |
+--------------+------+
3 rows in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu order by score;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu order by score asc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu order by score desc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> insert into tb_stu values(9531, 'zhuzhishan', 'boy', '112', 77, 22);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu order by sex;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu order by sex asc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu order by sex desc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu order by sex desc, score;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu order by sex desc, score desc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu order by sex desc, score asc;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu where age = 30;
+------+--------+------+------+-------+------+
| sid  | name   | sex  | tel  | score | age  |
+------+--------+------+------+-------+------+
| 9529 | shiliu | girl | 114  |    59 |   30 |
+------+--------+------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from tb_stu where age = 30 and sex = 'girl';
+------+--------+------+------+-------+------+
| sid  | name   | sex  | tel  | score | age  |
+------+--------+------+------+-------+------+
| 9529 | shiliu | girl | 114  |    59 |   30 |
+------+--------+------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from tb_stu where age = 30 or sex = 'girl';
+------+----------+------+------+-------+------+
| sid  | name     | sex  | tel  | score | age  |
+------+----------+------+------+-------+------+
| 9528 | qiuxiang | girl | 110  |    80 |   18 |
| 9529 | shiliu   | girl | 114  |    59 |   30 |
| 9530 | qiuxiang | girl | 110  |    80 |   18 |
+------+----------+------+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from tb_stu where not age = 30;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu where sex < 60;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set, 5 warnings (0.00 sec)

mysql> select * from tb_stu where score < 60;
+------+--------+------+------+-------+------+
| sid  | name   | sex  | tel  | score | age  |
+------+--------+------+------+-------+------+
| 9529 | shiliu | girl | 114  |    59 |   30 |
+------+--------+------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from tb_stu where score >= 60;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu where score = 100;
Empty set (0.00 sec)

mysql> select * from tb_stu where score != 100;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu where score <> 100;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu where score <= 100 and score >= 80;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from tb_stu where score between 60 and 100;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu where score between 80 and 100;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from tb_stu where score between 80 and 99;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from tb_stu where score between 180 and 99;
Empty set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> insert into tb_stu (sid, name) values(9532, 'chunxiang');
Query OK, 1 row affected (0.02 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select * from tb_stu where name is null;
Empty set (0.00 sec)

mysql> select * from tb_stu where sex is null;
+------+-----------+------+------+-------+------+
| sid  | name      | sex  | tel  | score | age  |
+------+-----------+------+------+-------+------+
| 9532 | chunxiang | NULL | NULL |  NULL | NULL |
+------+-----------+------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from tb_stu where sex is not null;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
+------+--------------+------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select * from tb_stu where sid in (9527, 9528, 9599);
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
+------+--------------+------+------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select * from tb_stu where name like '%ng';
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_stu where name like '_ng';
Empty set (0.00 sec)

mysql> select * from tb_stu where name like 'qiuxiang';
+------+----------+------+------+-------+------+
| sid  | name     | sex  | tel  | score | age  |
+------+----------+------+------+-------+------+
| 9528 | qiuxiang | girl | 110  |    80 |   18 |
| 9530 | qiuxiang | girl | 110  |    80 |   18 |
+------+----------+------+------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from tb_stu where name like 'xiang';
Empty set (0.00 sec)

mysql> select * from tb_stu where name like '___xiang';
+------+----------+------+------+-------+------+
| sid  | name     | sex  | tel  | score | age  |
+------+----------+------+------+-------+------+
| 9528 | qiuxiang | girl | 110  |    80 |   18 |
| 9530 | qiuxiang | girl | 110  |    80 |   18 |
+------+----------+------+------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from tb_stu where name like '____xiang';
+------+-----------+------+------+-------+------+
| sid  | name      | sex  | tel  | score | age  |
+------+-----------+------+------+-------+------+
| 9532 | chunxiang | NULL | NULL |  NULL | NULL |
+------+-----------+------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select sid, name,
 ->  case
 ->          when score >= 90 then 'A'
 ->          when score >= 80 then 'B'
 ->          when score >= 70 then 'C'
 ->          when score >= 60 then 'D'
 ->          else 'E'
 ->          end
 -> from tb_stu;
+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| sid  | name         | case
             when score >= 90 then 'A'
             when score >= 80 then 'B'
             when score >= 70 then 'C'
             when score >= 60 then 'D'
             else 'E'
             end |
+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| 9527 | zhouxingxing | A
                                     |
| 9528 | qiuxiang     | B
                                     |
| 9529 | shiliu       | E
                                     |
| 9530 | qiuxiang     | B
                                     |
| 9531 | zhuzhishan   | C
                                     |
| 9532 | chunxiang    | E
                                     |
+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select sid, name,
 ->  case
 ->          when score >= 90 then 'A'
 ->          when score >= 80 then 'B'
 ->          when score >= 70 then 'C'
 ->          when score >= 60 then 'D'
 ->          else 'E'
 ->          end as 'level'
 -> from tb_stu;
+------+--------------+-------+
| sid  | name         | level |
+------+--------------+-------+
| 9527 | zhouxingxing | A     |
| 9528 | qiuxiang     | B     |
| 9529 | shiliu       | E     |
| 9530 | qiuxiang     | B     |
| 9531 | zhuzhishan   | C     |
| 9532 | chunxiang    | E     |
+------+--------------+-------+
6 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-04-24 11:21:05 |
+---------------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| saas       |
+------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-04-24 11:36:28 |
+---------------------+
1 row in set (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2024-04-24 11:38:30 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2024-04-24 |
+------------+
1 row in set (0.00 sec)

mysql> select curtiem();
ERROR 1305 (42000): FUNCTION saas.curtiem does not exist
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 11:39:30  |
+-----------+
1 row in set (0.00 sec)

mysql> select week();
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 ')' at line 1
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          16 |
+-------------+
1 row in set (0.00 sec)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2024 |
+-------------+
1 row in set (0.00 sec)

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            42 |
+---------------+
1 row in set (0.00 sec)

mysql> select adddate(now(), 5)
 -> ;
+---------------------+
| adddate(now(), 5)   |
+---------------------+
| 2024-04-29 11:42:47 |
+---------------------+
1 row in set (0.00 sec)

mysql> select adddate(now(), 8);
+---------------------+
| adddate(now(), 8)   |
+---------------------+
| 2024-05-02 11:43:17 |
+---------------------+
1 row in set (0.00 sec)

mysql> select adddate(now(), -8);
+---------------------+
| adddate(now(), -8)  |
+---------------------+
| 2024-04-16 11:43:45 |
+---------------------+
1 row in set (0.00 sec)

mysql> select concat("hello", "world");
+--------------------------+
| concat("hello", "world") |
+--------------------------+
| helloworld               |
+--------------------------+
1 row in set (0.00 sec)

mysql> select concat("hello", "world", "saas");
+----------------------------------+
| concat("hello", "world", "saas") |
+----------------------------------+
| helloworldsaas                   |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select concat("My", "SQL");
+---------------------+
| concat("My", "SQL") |
+---------------------+
| MySQL               |
+---------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 3, 0, "MySQL");
+-----------------------------------------+
| insert("这是一个数据库", 3, 0, "MySQL") |
+-----------------------------------------+
| 这是MySQL一个数据库                     |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 3, 2, "MySQL");
+-----------------------------------------+
| insert("这是一个数据库", 3, 2, "MySQL") |
+-----------------------------------------+
| 这是MySQL数据库                         |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 3, 2, "关系");
+----------------------------------------+
| insert("这是一个数据库", 3, 2, "关系") |
+----------------------------------------+
| 这是关系数据库                         |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 3, 0, "关系");
+----------------------------------------+
| insert("这是一个数据库", 3, 0, "关系") |
+----------------------------------------+
| 这是关系一个数据库                     |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select insert("这是一个数据库", 5, 0, "关系");
+----------------------------------------+
| insert("这是一个数据库", 5, 0, "关系") |
+----------------------------------------+
| 这是一个关系数据库                     |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select lower("MySQL");
+----------------+
| lower("MySQL") |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

mysql> select upper("MySQL");
+----------------+
| upper("MySQL") |
+----------------+
| MYSQL          |
+----------------+
1 row in set (0.00 sec)

mysql> select substring("javaMySQLOracle", 5, 5)
 -> ;
+------------------------------------+
| substring("javaMySQLOracle", 5, 5) |
+------------------------------------+
| MySQL                              |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select count(*) from tb_stu;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(sid) from tb_stu;
+------------+
| count(sid) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)

mysql> select count(1) from tb_stu;
+----------+
| count(1) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select max(score) from tb_stu;
+------------+
| max(score) |
+------------+
|         99 |
+------------+
1 row in set (0.00 sec)

mysql> select min(score) from tb_stu;
+------------+
| min(score) |
+------------+
|         59 |
+------------+
1 row in set (0.00 sec)

mysql> select sum(score) from tb_stu;
+------------+
| sum(score) |
+------------+
|        395 |
+------------+
1 row in set (0.00 sec)

mysql> select avg(score) from tb_stu;
+------------+
| avg(score) |
+------------+
|         79 |
+------------+
1 row in set (0.00 sec)

mysql> select * from tb_stu;
+------+--------------+------+------+-------+------+
| sid  | name         | sex  | tel  | score | age  |
+------+--------------+------+------+-------+------+
| 9527 | zhouxingxing | boy  | 119  |    99 |   20 |
| 9528 | qiuxiang     | girl | 110  |    80 |   18 |
| 9529 | shiliu       | girl | 114  |    59 |   30 |
| 9530 | qiuxiang     | girl | 110  |    80 |   18 |
| 9531 | zhuzhishan   | boy  | 112  |    77 |   22 |
| 9532 | chunxiang    | NULL | NULL |  NULL | NULL |
+------+--------------+------+------+-------+------+
6 rows in set (0.00 sec)

mysql> select max(score) from tb_stu;
+------------+
| max(score) |
+------------+
|         99 |
+------------+
1 row in set (0.00 sec)

mysql> select max(score) from tb_stu group by sex;
+------------+
| max(score) |
+------------+
|       NULL |
|         99 |
|         80 |
+------------+
3 rows in set (0.00 sec)

mysql> select min(score) from tb_stu group by sex;
+------------+
| min(score) |
+------------+
|       NULL |
|         77 |
|         59 |
+------------+
3 rows in set (0.00 sec)

任务：

创建一个学生表（student），包含以下字段，sid int，name varchar, sex char，score double，cid（班级编号） int

  1. 查询各个班级的 总人数

  2. 查询各个班级的平级分数

  3. 查询各个班级的成绩最高分

zpz2001
关注
  • 3
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Java入门基础学习资料day11
10-22
此java基础课程，适合0基础小白，课程一共11天课时，目录如下： day01【前言、入门程序、常量、变量】 day02【数据类型转换、运算符、方法入门】 day03【流程控制语句】 day04【 Idea、方法】 day05【数组】 day06【类与对象、封装、构造方法】 day07【Scanner类、Random类、ArrayList类】 day08【String类、static关键字、Arrays类、Math类】 day09【继承、super、this、抽象类】 day10【接口、多态】 day11【final、权限、内部类、引用类型】 此课程全部课程资料获取： JavaEE基础学习资料地址汇总：https://blog.csdn.net/weixin_46271129/article/details/133979063
Java入门基础学习资料day09-10
10-22
此java基础课程，适合0基础小白，课程一共11天课时，目录如下： day01【前言、入门程序、常量、变量】 day02【数据类型转换、运算符、方法入门】 day03【流程控制语句】 day04【 Idea、方法】 day05【数组】 day06【类与对象、封装、构造方法】 day07【Scanner类、Random类、ArrayList类】 day08【String类、static关键字、Arrays类、Math类】 day09【继承、super、this、抽象类】 day10【接口、多态】 day11【final、权限、内部类、引用类型】 此课程全部课程资料获取： JavaEE基础学习资料地址汇总：https://blog.csdn.net/weixin_46271129/article/details/133979063
Java基础入门day35
zpz2001的专栏
04-18 1138
Java基础入门
Java基础入门-Day1
weixin_45795542的博客
11-03 459
Java基础入门-Day1JAVA 开发入门特点分类Java字节执行方式JDK的使用Java垃圾回收机制Java编译 JAVA 开发入门 Java是一种高级计算机语言。他是由Sun公司(已被Oracle公司于2009年4月20日收购)于1995年5月推出的一种可以编写跨平台应用软件丶完全面向对象的程序设计语言。 特点 资源免费 跨平台 健壮，安全 高性能 简单 面向对象 动态性 多线程 分类 Ja...
Java基础入门-Day10
weixin_45795542的博客
12-06 905
Java基础入门-Day10 访问控制符 在Java中，针对类、成员方法和属性提供了4种访问级别，分别是private、default、protected和public。接下来通过一个图将这4种控制级别由小到大依次列出 private==>default==>protected==>public privat（当前类访问级别） 如果类的成员被private访问控制符来修饰，则这个...
Java入门基础学习资料day07-08
10-22
此java基础课程，适合0基础小白，课程一共11天课时，目录如下： day01【前言、入门程序、常量、变量】 day02【数据类型转换、运算符、方法入门】 day03【流程控制语句】 day04【 Idea、方法】 day05【数组】 day06...
Java入门基础学习资料day05-06
10-22
此java基础课程，适合0基础小白，课程一共11天课时，目录如下： day01【前言、入门程序、常量、变量】 day02【数据类型转换、运算符、方法入门】 day03【流程控制语句】 day04【 Idea、方法】 day05【数组】 day06...
Java入门基础学习资料day03-04
10-22
此java基础课程，适合0基础小白，课程一共11天课时，目录如下： day01【前言、入门程序、常量、变量】 day02【数据类型转换、运算符、方法入门】 day03【流程控制语句】 day04【 Idea、方法】 day05【数组】 day06...
java基础入门day01
zpz2001的专栏
03-15 957
java基础入门
Java基础入门-Day2
weixin_45795542的博客
11-03 232
Java基础入门-Day2 Java编程基础 JAVA使用的字符集：Unicode字符集（没有乱码）：2个字节/16位 程序：代码段{}=>句=>符号=>类型 Java最小基本函数单位：类 Java中的标识符 Java中的标识符大小写区分严格 标识符可以由任意顺序的大小写字母，数字，下划线（_）和美元符号（$）组成，但标识符不能以数字开头，也不能是Java中的关键字，且不能包含空...
JAVA 学习·泛型（二）——通配泛型
wr114514的博客
05-02 1044
介绍了Java泛型中的重要知识点——通配泛型及其应用。
springboot 获取maven打包时间
I do more ，you do less
04-30 472
【代码】springboot 获取maven打包时间。
leetcode16-3Sum Closest
我的程序人生
04-29 440
这道题目和那道3Num的题目很类似，解题思路也是类似的，我们先对数组排序，然后固定一个元素，通过双指针遍历剩下的元素，用一个哨兵记录三个元素的和与target的diff，分别处理三个元素的和小于target，大于target，等于target这三种情况即可。请你从 nums 中选出三个整数，使它们的和与 target 最接近。解释：与 target 最接近的和是 2 (-1 + 2 + 1 = 2)。输入：nums = [-1,2,1,-4], target = 1。假定每组输入只存在恰好一个解。
Java---类和方法的再学习
m0_74012211的博客
05-05 1047
Java类与对象
java线上问题排查之磁盘和网络查看分析（二）
wayne_youlu的博客
04-30 523
overflowed 标识全连接队列溢出的次数,最前面是0，标识没有队列溢出的情况，网络环境正常。来查看网络是否连接。如果出现下图内容，则证明网络已经连接。
【Java基础】三大特性——多态
qq_43714449的博客
05-05 342
它的作用是测试它左边的对象是否是它右边的类的实例，返回 boolean 的数据类型。实现一个功能时，功能的一部分是确定的，一部分是不确定，确定的部分还会用到不确定的部分，那么就把不确定的部分暴露出去，让子类去实现。通常通过重写toString方法，来返回有用的数据。instanceof 是 Java 的一个二元操作符，类似于 ==，>，< 等操作符。确定的部分：记录一个开始时间，记录一个结束时间，结束时间 - 开始时间。对类存在的语法，对接口也存在。，返回对象的字符串表示形式。判断两个人是否是同龄人。
链表刷题集
yajunjiao的专栏
04-30 936
本文主要列举了一些刷的题，不多，有那么几道，也建议各位去建立自己的刷题集。积少成多。
Spring Cloud——LoadBalancer
????
05-01 1874
这里只是简单的讲解了一下LoadBalancer如何使用，因为实际上我们使用的不是很多，主要还是使用OpenFeign。
如何用Jmeter压测
最新发布
howard1209a的博客
05-06 282
可以从清华源下载，我下的是 5.6.3 版本。bin 目录打开终端，输入 Jmeter 运行Options 点 Choose language 选择中文。
java 读取.day文件
06-08
3. 如果您希望使用Java读取.day文件，可以尝试使用Java的IO类和字节流来读取二进制数据，并根据.day文件的格式和结构解析其中的内容。这需要您对.day文件的格式和结构有一定的了解和理解。 综上所述，读取.day文件...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值