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)