【MySQL】5、CRUD、内置函数与聚合函数

最新推荐文章于 2024-08-19 20:48:40 发布

浮生146

最新推荐文章于 2024-08-19 20:48:40 发布

阅读量269

点赞数 4

分类专栏： JavaSE学习文章标签： mysql java 学习

本文链接：https://blog.csdn.net/qq_56040271/article/details/141001694

版权

JavaSE学习专栏收录该内容

25 篇文章 0 订阅

订阅专栏

8. MySQL中对数据进行CRUD操作

8.1 增

# 向表中添加一条包含所有列的值的数据
insert into table_name value (value1, value2, ...);
# 向表中添加一条包含部分列的值的数据
insert into table_name (column1, column2, ...) value (value1, value2, ...);

8.2 删

# 删除表中的行
delete from table_name where condition;

8.3 改

# 更新表中的行
update table_name set column1 = value1, column2 = value2, ... where condition;

8.4 查

# 查询表中的所有行和列
select * from table_name;
# 查询特定列
select column1, column2 from table_name;
# 查询满足特定条件的行
select * from table_name where condition;

9. MySQL内置函数

9.1 字符串处理函数

9.1.1 返回字符串长度

mysql> select length('hello');
+-----------------+
| length('hello') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.01 sec)

9.1.2 返回字符的ascii码的值

mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
|         97 |
+------------+
1 row in set (0.00 sec)

9.1.3 返回指定ascii码的值对应的字符

mysql> select char(97);
+----------+
| char(97) |
+----------+
| a        |
+----------+
1 row in set (0.00 sec)

9.1.4 转换指定字符串为大小写

# 转换为大写
mysql> select upper('hello');
+----------------+
| upper('hello') |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.01 sec)

# 转换为小写
mysql> select lower('HELLO');
+----------------+
| lower('HELLO') |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)

9.1.5 连接多个字符串

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

9.1.6 去掉左右空格

# 去掉左边空格
mysql> select concat(ltrim('      hello   '),'world');
+-----------------------------------------+
| concat(ltrim('      hello   '),'world') |
+-----------------------------------------+
| hello   world                           |
+-----------------------------------------+
1 row in set (0.00 sec)

# 去掉右边空格
mysql> select concat(rtrim('      hello   '),'world');
+-----------------------------------------+
| concat(rtrim('      hello   '),'world') |
+-----------------------------------------+
|       helloworld                        |
+-----------------------------------------+
1 row in set (0.00 sec)

# 去掉左右两边空格
mysql> select concat(trim('      hello   '),'world');
+----------------------------------------+
| concat(trim('      hello   '),'world') |
+----------------------------------------+
| helloworld                             |
+----------------------------------------+
1 row in set (0.00 sec)

9.1.7 返回某个字符第一次出现的位置

mysql> select instr('helloworld','o');
+-------------------------+
| instr('helloworld','o') |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set (0.00 sec)

9.2 数学函数

9.2.1 返回圆周率的值

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

9.2.2 返回绝对值

mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

9.2.3 返回平方根

mysql> select sqrt(2);
+--------------------+
| sqrt(2)            |
+--------------------+
| 1.4142135623730951 |
+--------------------+
1 row in set (0.00 sec)

9.2.4 返回n次幂

mysql> select pow(2,3);
+----------+
| pow(2,3) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

9.2.5 返回四舍五入(加上0.5取整)

mysql> select round(3.556);
+--------------+
| round(3.556) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

9.2.6 返回比指定数小的最大整数

mysql> select floor(3.14);
+-------------+
| floor(3.14) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

9.2.7 返回比指定数大的最小整数

mysql> select ceil(3.14);
+------------+
| ceil(3.14) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

9.2.8 返回一个[0, 1)的随机小数

mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.10672098966941329 |
+---------------------+
1 row in set (0.00 sec)

9.2.9 返回给定角度的弧度值

mysql> select radians(45);
+--------------------+
| radians(45)        |
+--------------------+
| 0.7853981633974483 |
+--------------------+
1 row in set (0.00 sec)

9.2.10 返回给定弧度的正弦值

mysql> select sin(pi()/3);
+--------------------+
| sin(pi()/3)        |
+--------------------+
| 0.8660254037844386 |
+--------------------+
1 row in set (0.00 sec)

9.3 日期处理函数

9.3.1 获取当前日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-07-23 11:51:15 |
+---------------------+
1 row in set (0.00 sec)

9.3.2 获取当前日期

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

9.3.3 获取当前时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 11:51:48  |
+-----------+
1 row in set (0.00 sec)

9.3.4 获取日期的某一部分

mysql> select concat(year(now()),'年',month(now()),'月',day(now()),'日');
+------------------------------------------------------------+
| concat(year(now()),'年',month(now()),'月',day(now()),'日') |
+------------------------------------------------------------+
| 2024年7月23日                                              |
+------------------------------------------------------------+
1 row in set (0.00 sec)

9.3.5 添加一个指定日期片段

# 使用date_add()添加一个月
mysql> select date_add(now(),interval 1 month);
+----------------------------------+
| date_add(now(),interval 1 month) |
+----------------------------------+
| 2024-08-23 11:54:28              |
+----------------------------------+
1 row in set (0.00 sec)

# 使用date_sub()添加一个月
mysql> select date_sub(now(),interval -1 month);
+-----------------------------------+
| date_sub(now(),interval -1 month) |
+-----------------------------------+
| 2024-08-23 11:55:18               |
+-----------------------------------+
1 row in set (0.00 sec)

9.3.6 减少一个指定日期片段

# 使用date_sub()减少一个月
mysql> select date_sub(now(),interval 1 month);
+----------------------------------+
| date_sub(now(),interval 1 month) |
+----------------------------------+
| 2024-06-23 11:56:07              |
+----------------------------------+
1 row in set (0.00 sec)

# 使用date_add()减少一个月
mysql> select date_add(now(),interval -1 month);
+-----------------------------------+
| date_add(now(),interval -1 month) |
+-----------------------------------+
| 2024-06-23 11:56:23               |
+-----------------------------------+
1 row in set (0.00 sec)

9.3.7 返回两个日期之差(天数之差)

mysql> select datediff(now(),'2022-7-23');
+-----------------------------+
| datediff(now(),'2022-7-23') |
+-----------------------------+
|                         731 |
+-----------------------------+
1 row in set (0.00 sec)

9.3.8 返回两个指定日期部分之差

mysql> select timestampdiff(month,'2020-10-1',now());
+----------------------------------------+
| timestampdiff(month,'2020-10-1',now()) |
+----------------------------------------+
|                                     45 |
+----------------------------------------+
1 row in set (0.00 sec)

9.3.9 添加一个日期(使用时间戳函数)

mysql> select timestampadd(day,10,now());
+----------------------------+
| timestampadd(day,10,now()) |
+----------------------------+
| 2024-08-02 12:20:07        |
+----------------------------+
1 row in set (0.00 sec)

9.3.10 对日期和时间进行格式化处理

mysql> select date_format(now(),'%Y-%m-%d %H:%i:%s');
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %H:%i:%s') |
+----------------------------------------+
| 2024-07-23 12:20:50                    |
+----------------------------------------+
1 row in set (0.00 sec)

10. 聚合函数

mysql> select count(*),max(age),min(age),avg(age),sum(age) from student;
+----------+----------+----------+----------+----------+
| count(*) | max(age) | min(age) | avg(age) | sum(age) |
+----------+----------+----------+----------+----------+
|        3 |       23 |       21 |  22.0000 |       66 |
+----------+----------+----------+----------+----------+
1 row in set (0.00 sec)