【三】【SQL】时间函数和字符串函数

最新推荐文章于 2024-05-19 17:03:37 发布
最新推荐文章于 2024-05-19 17:03:37 发布
阅读量1.5k 收藏 15
点赞数 46
分类专栏： 数据库SQL 文章标签： 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_74163972/article/details/136379896
版权

时间函数

函数名称

描述

current_data()

当前日期

current_time()

当前时间

current_timetamp()

当前时间戳

data(datatime)

返回datatime参数的日期部分

data_add(date,interval d_value_type)

在date中添加日期或者时间，interval后的数值单位可以是：year minute second day

date_sub(date,interval d_value_type)

在date中减去日期或者时间，interval后的数值单位可以是：year minute second day

datediff(date1,date2)

两个日期的差，单位是天

now()

当前日期时间

 

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-02-29     |
+----------------+
1 row in set (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 12:31:11       |
+----------------+
1 row in set (0.00 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-02-29 12:31:25 |
+---------------------+
1 row in set (0.00 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-02-29 12:31:28 |
+---------------------+
1 row in set (0.00 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-02-29 12:31:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-02-29 12:31:40 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date('1949-10-01 00:00:00');
+-----------------------------+
| date('1949-10-01 00:00:00') |
+-----------------------------+
| 1949-10-01                  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select time('1949-10-01 00:00:00');
+-----------------------------+
| time('1949-10-01 00:00:00') |
+-----------------------------+
| 00:00:00                    |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-02-29 12:32:53 |
+---------------------+
1 row in set (0.00 sec)

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

mysql> select time(now());
+-------------+
| time(now()) |
+-------------+
| 12:37:10    |
+-------------+
1 row in set (0.00 sec)

mysql> select date_add('2050-01-01',interval 10 day);
+----------------------------------------+
| date_add('2050-01-01',interval 10 day) |
+----------------------------------------+
| 2050-01-11                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2050-01-01',interval 40 day);
+----------------------------------------+
| date_add('2050-01-01',interval 40 day) |
+----------------------------------------+
| 2050-02-10                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 10 day);
+---------------------------------+
| date_add(now(),interval 10 day) |
+---------------------------------+
| 2024-03-10 12:34:04             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 10 minute);
+------------------------------------+
| date_add(now(),interval 10 minute) |
+------------------------------------+
| 2024-02-29 12:44:20                |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-02-29 12:34:30 |
+---------------------+
1 row in set (0.00 sec)

mysql> select datediff('2017-10-10','2016-09-01');
+-------------------------------------+
| datediff('2017-10-10','2016-09-01') |
+-------------------------------------+
|                                 404 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2010-10-10','2016-09-01');
+-------------------------------------+
| datediff('2010-10-10','2016-09-01') |
+-------------------------------------+
|                               -2153 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff(now(),'1949-10-01');
+------------------------------+
| datediff(now(),'1949-10-01') |
+------------------------------+
|                        27179 |
+------------------------------+
1 row in set (0.00 sec)



mysql> 

 

时间函数案例
 

创建一个表记录生日信息
 

 

mysql> system clear
mysql> create table tmp(
    -> id bigint primary key auto_increment,
    -> birthday date not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc tmp;
+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | bigint(20) | NO   | PRI | NULL    | auto_increment |
| birthday | date       | NO   |     | NULL    |                |
+----------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_d1        |
+---------------------+
| duplicate_table     |
| exam_result         |
| for_delete          |
| for_truncate        |
| old_duplicate_table |
| students            |
| test_null           |
| tmp                 |
+---------------------+
8 rows in set (0.00 sec)

mysql> select *from tmp;
Empty set (0.00 sec)

mysql> insert into tmp(birthday) values ('1980-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tmp(birthday) values ('1990-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> select *from tmp;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 1980-01-01 |
|  2 | 1990-01-01 |
+----+------------+
2 rows in set (0.00 sec)

mysql> insert into tmp(birthday) values('2024-02-29');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tmp(birthday) values(current_date());
Query OK, 1 row affected (0.01 sec)

mysql> insert into tmp(birthday) values(current_time());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 12:43:41       |
+----------------+
1 row in set (0.00 sec)

mysql> insert into tmp(birthday) values(current_timestamp());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-02-29 12:44:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into tmp(birthday)  values(date(current_timestamp()));
Query OK, 1 row affected (0.01 sec)

mysql> select *from tmp;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 1980-01-01 |
|  2 | 1990-01-01 |
|  3 | 2024-02-29 |
|  4 | 2024-02-29 |
|  5 | 2024-02-29 |
|  6 | 2024-02-29 |
|  7 | 2024-02-29 |
+----+------------+
7 rows in set (0.00 sec)

mysql> 

 

创建一个留言板
 

 

mysql> create table msg(
    -> id bigint primary key auto_increment,
    -> content varchar(100) not null,
    -> sendtime datetime 
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc msg;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| content  | varchar(100) | NO   |     | NULL    |                |
| sendtime | datetime     | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into msg (content,sendtime )values ('纸上得来终觉浅，绝知此事要躬行',now());
Query OK, 1 row affected (0.01 sec)

mysql> select *from msg;
+----+-----------------------------------------------+---------------------+
| id | content                                       | sendtime            |
+----+-----------------------------------------------+---------------------+
|  1 | 纸上得来终觉浅，绝知此事要躬行                | 2024-02-29 12:49:40 |
+----+-----------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into msg (content,sendtime) values('恐惊天上人',now());
Query OK, 1 row affected (0.00 sec)

mysql> select*from tmp;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 1980-01-01 |
|  2 | 1990-01-01 |
|  3 | 2024-02-29 |
|  4 | 2024-02-29 |
|  5 | 2024-02-29 |
|  6 | 2024-02-29 |
|  7 | 2024-02-29 |
+----+------------+
7 rows in set (0.00 sec)

mysql> select*from msg;
+----+-----------------------------------------------+---------------------+
| id | content                                       | sendtime            |
+----+-----------------------------------------------+---------------------+
|  1 | 纸上得来终觉浅，绝知此事要躬行                | 2024-02-29 12:49:40 |
|  2 | 恐惊天上人                                    | 2024-02-29 12:51:03 |
+----+-----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

mysql> select content,sendtime from msg;
+-----------------------------------------------+---------------------+
| content                                       | sendtime            |
+-----------------------------------------------+---------------------+
| 纸上得来终觉浅，绝知此事要躬行                | 2024-02-29 12:49:40 |
| 恐惊天上人                                    | 2024-02-29 12:51:03 |
+-----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

mysql> select content,date(sendtime) from msg;
+-----------------------------------------------+----------------+
| content                                       | date(sendtime) |
+-----------------------------------------------+----------------+
| 纸上得来终觉浅，绝知此事要躬行                | 2024-02-29     |
| 恐惊天上人                                    | 2024-02-29     |
+-----------------------------------------------+----------------+
2 rows in set (0.00 sec)

mysql> 

 

查询在两分钟之内发布的帖子
 

 

mysql> system clear
mysql> select content ,sendtime from msg where sendtime >date_sub(now(),interval 2 minute);
Empty set (0.00 sec)

mysql> insert into msg(content ,sendtime ) values ('会当凌绝顶',now());
Query OK, 1 row affected (0.00 sec)


mysql> select content ,sendtime from msg where sendtime >date_sub(now(),interval 2 minute);
+-----------------+---------------------+
| content         | sendtime            |
+-----------------+---------------------+
| 会当凌绝顶      | 2024-02-29 12:54:58 |
+-----------------+---------------------+
1 row in set (0.01 sec)

mysql> insert into msg(content ,sendtime ) values ('会当凌绝顶',now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into msg(content ,sendtime ) values ('会当凌绝顶',now());
Query OK, 1 row affected (0.01 sec)

mysql> select content ,sendtime from msg where sendtime >date_sub(now(),interval 2 minute);
+-----------------+---------------------+
| content         | sendtime            |
+-----------------+---------------------+
| 会当凌绝顶      | 2024-02-29 12:54:58 |
| 会当凌绝顶      | 2024-02-29 12:55:31 |
| 会当凌绝顶      | 2024-02-29 12:55:33 |
+-----------------+---------------------+
3 rows in set (0.00 sec)

mysql> 

 

字符串函数
 

 
charset(str)
 		 
返回字符串字符集
 
 
concat(string2[,...])
 		 
连接字符串
 
 
instr(string,substring)
 		 
返回substring在string中出现的位置，没有返回0
 
 
ucase(string2)
 		 
转换成大写
 
 
lcase(string2)
 		 
转换成小写
 
 
left(string2 ,length)
 		 
从string2中的左边取length个字符
 
 
length(string)
 		 
string长度
 
 
replace(str,search_str,replace_str)
 		 
在str中用erplace_str替换search_str
 
 
strcmp(string1,string2)
 		 
逐字符比较两字符串的大小
 
 
substring(str,position[,length])
 		 
从str的position开始，取length个字符
 
 
ltrim(string)
 		 
去除左空格
 
 
rtrim(string)
 		 
去除右空格
 
 
trim(string)
 		 
去除左右空格
 
 

charset
 

 

mysql> select charset('asasds');
+-------------------+
| charset('asasds') |
+-------------------+
| utf8              |
+-------------------+
1 row in set (0.00 sec)

mysql> select charset('中国');
+-------------------+
| charset('中国')   |
+-------------------+
| utf8              |
+-------------------+
1 row in set (0.00 sec)

mysql> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno    | int(6) unsigned zerofill | NO   |     | NULL    |       |
| ename    | varchar(10)              | YES  |     | NULL    |       |
| job      | varchar(9)               | YES  |     | NULL    |       |
| mgr      | int(4) unsigned zerofill | YES  |     | NULL    |       |
| hiredate | datetime                 | YES  |     | NULL    |       |
| sal      | decimal(7,2)             | YES  |     | NULL    |       |
| comm     | decimal(7,2)             | YES  |     | NULL    |       |
| deptno   | int(2) unsigned zerofill | YES  |     | NULL    |       |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
+----------------+
14 rows in set (0.00 sec)

mysql> 

 

concat
 

 

mysql> select concat('a','b');
+-----------------+
| concat('a','b') |
+-----------------+
| ab              |
+-----------------+
1 row in set (0.00 sec)

mysql> select concat('a','b','c');
+---------------------+
| concat('a','b','c') |
+---------------------+
| abc                 |
+---------------------+
1 row in set (0.00 sec)

mysql> select concat('a','b','c') as res;
+------+
| res  |
+------+
| abc  |
+------+
1 row in set (0.00 sec)

mysql> select concat('a','b','c',123,3.14) as res;
+------------+
| res        |
+------------+
| abc1233.14 |
+------------+
1 row in set (0.00 sec)

mysql> 

 

instr
 

 

mysql> select instr('abcdef1234gfd','1234');
+-------------------------------+
| instr('abcdef1234gfd','1234') |
+-------------------------------+
|                             7 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select instr('abcdef1234gfd','1234a');
+--------------------------------+
| instr('abcdef1234gfd','1234a') |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> 

 

ucase、lcase
 

 

mysql> select ucase('abcdef1234ABCDef');
+---------------------------+
| ucase('abcdef1234ABCDef') |
+---------------------------+
| ABCDEF1234ABCDEF          |
+---------------------------+
1 row in set (0.00 sec)

mysql> select lcase('asdajaj1234jASHDNCAS');
+-------------------------------+
| lcase('asdajaj1234jASHDNCAS') |
+-------------------------------+
| asdajaj1234jashdncas          |
+-------------------------------+
1 row in set (0.00 sec)

mysql> 

 

left、right
 

 

mysql> select left ('abcde1234',4)
    -> ;
+----------------------+
| left ('abcde1234',4) |
+----------------------+
| abcd                 |
+----------------------+
1 row in set (0.00 sec)

mysql> select left ('abcde1234',5);
+----------------------+
| left ('abcde1234',5) |
+----------------------+
| abcde                |
+----------------------+
1 row in set (0.00 sec)

mysql> select left ('abcde1234',3);
+----------------------+
| left ('abcde1234',3) |
+----------------------+
| abc                  |
+----------------------+
1 row in set (0.00 sec)

mysql> select right ('abcde1234',3);
+-----------------------+
| right ('abcde1234',3) |
+-----------------------+
| 234                   |
+-----------------------+
1 row in set (0.00 sec)

mysql> select right ('abcde1234',4);
+-----------------------+
| right ('abcde1234',4) |
+-----------------------+
| 1234                  |
+-----------------------+
1 row in set (0.00 sec)

mysql> select right ('abcde1234',5);
+-----------------------+
| right ('abcde1234',5) |
+-----------------------+
| e1234                 |
+-----------------------+
1 row in set (0.00 sec)

mysql> 

 

length
 

 

mysql> select length('abcde1234');
+---------------------+
| length('abcde1234') |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.00 sec)

mysql> select length('');
+------------+
| length('') |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> 

 

字符串函数案例
 

获取emp表的ename列的字符集
 

 

mysql> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
+----------------+
14 rows in set (0.00 sec)

mysql> select charset(sal) from emp;
+--------------+
| charset(sal) |
+--------------+
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
| binary       |
+--------------+
14 rows in set (0.00 sec)

mysql> select *from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> 

 

要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
 

 

mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)

mysql> select name ,chinese +math+english 总分,chinese,math,english from exam_result;
+-----------+--------+---------+------+---------+
| name      | 总分   | chinese | math | english |
+-----------+--------+---------+------+---------+
| 唐三藏    |    288 |     134 |   98 |      56 |
| 猪悟能    |    364 |     176 |   98 |      90 |
| 曹孟德    |    297 |     140 |   90 |      67 |
| 孙权      |    291 |     140 |   73 |      78 |
| 宋公明    |    275 |     150 |   95 |      30 |
+-----------+--------+---------+------+---------+
5 rows in set (0.00 sec)

mysql> select concat(name ,chinese +math+english ,chinese,math,english)msg from exam_result;
+---------------------+
| msg                 |
+---------------------+
| 唐三藏2881349856    |
| 猪悟能3641769890    |
| 曹孟德2971409067    |
| 孙权2911407378      |
| 宋公明2751509530    |
+---------------------+
5 rows in set (0.00 sec)

mysql> select concat('考生姓名：',name,',总分：' ,chinese +math+english ,',语文成绩：',chinese,',数学成绩：',math,',英语成绩：',english)msg from exam_result;
+----------------------------------------------------------------------------------------------+
| msg                                                                                          |
+----------------------------------------------------------------------------------------------+
| 考生姓名：唐三藏,总分：288,语文成绩：134,数学成绩：98,英语成绩：56                           |
| 考生姓名：猪悟能,总分：364,语文成绩：176,数学成绩：98,英语成绩：90                           |
| 考生姓名：曹孟德,总分：297,语文成绩：140,数学成绩：90,英语成绩：67                           |
| 考生姓名：孙权,总分：291,语文成绩：140,数学成绩：73,英语成绩：78                             |
| 考生姓名：宋公明,总分：275,语文成绩：150,数学成绩：95,英语成绩：30                           |
+----------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> 

 

求学生表中学生姓名占用的字节数
 

length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字节字符则算作一个字符。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）。
 

 

mysql> select name ,length(name) from exam_result;
+-----------+--------------+
| name      | length(name) |
+-----------+--------------+
| 唐三藏    |            9 |
| 猪悟能    |            9 |
| 曹孟德    |            9 |
| 孙权      |            6 |
| 宋公明    |            9 |
+-----------+--------------+
5 rows in set (0.00 sec)

mysql> select name ,length(math) from exam_result;
+-----------+--------------+
| name      | length(math) |
+-----------+--------------+
| 唐三藏    |            2 |
| 猪悟能    |            2 |
| 曹孟德    |            2 |
| 孙权      |            2 |
| 宋公明    |            2 |
+-----------+--------------+
5 rows in set (0.00 sec)

mysql> select length('ab');
+--------------+
| length('ab') |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql> select length('abc1');
+----------------+
| length('abc1') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> select length('abc1你好');
+----------------------+
| length('abc1你好')   |
+----------------------+
|                   10 |
+----------------------+
1 row in set (0.00 sec)

mysql> 

 

将emp表中所有名字中有S的替换成‘上海’
 

 

mysql> select *from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select ename,replace(ename,'S','上海') from emp;
+--------+-----------------------------+
| ename  | replace(ename,'S','上海')   |
+--------+-----------------------------+
| SMITH  | 上海MITH                    |
| ALLEN  | ALLEN                       |
| WARD   | WARD                        |
| JONES  | JONE上海                    |
| MARTIN | MARTIN                      |
| BLAKE  | BLAKE                       |
| CLARK  | CLARK                       |
| SCOTT  | 上海COTT                    |
| KING   | KING                        |
| TURNER | TURNER                      |
| ADAMS  | ADAM上海                    |
| JAMES  | JAME上海                    |
| FORD   | FORD                        |
| MILLER | MILLER                      |
+--------+-----------------------------+
14 rows in set (0.00 sec)

mysql> 

 

截取emp表中ename字段的第二个到第三个字符
 

 

mysql> select ename from emp;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.00 sec)

mysql> select substring(ename,2,2),ename from emp;
+----------------------+--------+
| substring(ename,2,2) | ename  |
+----------------------+--------+
| MI                   | SMITH  |
| LL                   | ALLEN  |
| AR                   | WARD   |
| ON                   | JONES  |
| AR                   | MARTIN |
| LA                   | BLAKE  |
| LA                   | CLARK  |
| CO                   | SCOTT  |
| IN                   | KING   |
| UR                   | TURNER |
| DA                   | ADAMS  |
| AM                   | JAMES  |
| OR                   | FORD   |
| IL                   | MILLER |
+----------------------+--------+
14 rows in set (0.00 sec)

mysql> 

 

以手写字母小写的方式显示所有员工的姓名
 

 

mysql> select ename,substring(ename,1,1),substring(ename,2) from emp;
+--------+----------------------+--------------------+
| ename  | substring(ename,1,1) | substring(ename,2) |
+--------+----------------------+--------------------+
| SMITH  | S                    | MITH               |
| ALLEN  | A                    | LLEN               |
| WARD   | W                    | ARD                |
| JONES  | J                    | ONES               |
| MARTIN | M                    | ARTIN              |
| BLAKE  | B                    | LAKE               |
| CLARK  | C                    | LARK               |
| SCOTT  | S                    | COTT               |
| KING   | K                    | ING                |
| TURNER | T                    | URNER              |
| ADAMS  | A                    | DAMS               |
| JAMES  | J                    | AMES               |
| FORD   | F                    | ORD                |
| MILLER | M                    | ILLER              |
+--------+----------------------+--------------------+
14 rows in set (0.00 sec)

mysql> select ename,lcase(substring(ename,1,1)),substring(ename,2) from emp;
+--------+-----------------------------+--------------------+
| ename  | lcase(substring(ename,1,1)) | substring(ename,2) |
+--------+-----------------------------+--------------------+
| SMITH  | s                           | MITH               |
| ALLEN  | a                           | LLEN               |
| WARD   | w                           | ARD                |
| JONES  | j                           | ONES               |
| MARTIN | m                           | ARTIN              |
| BLAKE  | b                           | LAKE               |
| CLARK  | c                           | LARK               |
| SCOTT  | s                           | COTT               |
| KING   | k                           | ING                |
| TURNER | t                           | URNER              |
| ADAMS  | a                           | DAMS               |
| JAMES  | j                           | AMES               |
| FORD   | f                           | ORD                |
| MILLER | m                           | ILLER              |
+--------+-----------------------------+--------------------+
14 rows in set (0.00 sec)

mysql> select ename,concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;
+--------+--------------------------------------------------------+
| ename  | concat(lcase(substring(ename,1,1)),substring(ename,2)) |
+--------+--------------------------------------------------------+
| SMITH  | sMITH                                                  |
| ALLEN  | aLLEN                                                  |
| WARD   | wARD                                                   |
| JONES  | jONES                                                  |
| MARTIN | mARTIN                                                 |
| BLAKE  | bLAKE                                                  |
| CLARK  | cLARK                                                  |
| SCOTT  | sCOTT                                                  |
| KING   | kING                                                   |
| TURNER | tURNER                                                 |
| ADAMS  | aDAMS                                                  |
| JAMES  | jAMES                                                  |
| FORD   | fORD                                                   |
| MILLER | mILLER                                                 |
+--------+--------------------------------------------------------+
14 rows in set (0.00 sec)

mysql> 

 

清理字符串左、右空格
 

 

mysql> select ltrim('     你好      ');
+----------------------------+
| ltrim('     你好      ')   |
+----------------------------+
| 你好                       |
+----------------------------+
1 row in set (0.00 sec)

mysql> select ltrim('     你好      ') as res;
+--------------+
| res          |
+--------------+
| 你好         |
+--------------+
1 row in set (0.00 sec)

mysql> select rtrim('     你好      ') as res;
+-------------+
| res         |
+-------------+
|      你好   |
+-------------+
1 row in set (0.00 sec)

mysql> select trim('     你好      ') as res;
+--------+
| res    |
+--------+
| 你好   |
+--------+
1 row in set (0.00 sec)

mysql> select trim('     你好        hello              ') as res;
+---------------------+
| res                 |
+---------------------+
| 你好        hello   |
+---------------------+
1 row in set (0.00 sec)

mysql> select ltrim('     你好        hello              ') as res;
+-----------------------------------+
| res                               |
+-----------------------------------+
| 你好        hello                 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select rtrim('     你好        hello              ') as res;
+--------------------------+
| res                      |
+--------------------------+
|      你好        hello   |
+--------------------------+
1 row in set (0.00 sec)

mysql> 

 

结尾
 

最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。
 

 

同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。
 

 

谢谢您的支持，期待与您在下一篇文章中再次相遇！

                

        

        

    




    

      

        

            

              
                
                  妖精七七_
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                    46
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    15
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
                
  • 
                  
                    打赏
                    
                  
                  
    打赏
    
                
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                  1
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
sql字符串函数大全和使用方法示例

				
			

			

				

					09-10
				

			

		

		

			
				
本文主要讲解SQL SERVER的字符串函数使用方法，包括LEN、LOWER、UPPER (string)、LTRIM、RTRIM等函数

			
		

	



                

              
                



	

		

			

				
					
SQL Server 数据库之字符串函数

				
			

			

				

					程序员小白的博客
				

				

					05-09
					
					7312
					
				

			

		

		

			
				
字符串函数1. 概述2. ASCII 函数3. Charindex 函数4. Left 函数5. Right 函数6. Len 函数7.8.9.10.
1. 概述
用字符串函数能对输入字符串进行类型转换和长度设置等操作，是数据库处理中常用的函数类型；

2. ASCII 函数
ASCII（获取 ASCII 码）函数用于返回字符串最左侧的 ASCII 代码值；
ASCII 函数语法格式为：
ASCII(character_expression)

charracter_expression 为 char 或 

			
		

	



                


  
              

                


	

		

			

				
					
SQL 聚合函数之字符串分组合并

				
			

			

				

					Tony.Dong的专栏
				

				

					12-08
					
					7427
					
				

			

		

		

			
				
本文介绍了如何通过 SQL 函数对字符串进行聚合，也就是将多行字符串合并成单个字符串。包括 Oracle（LISTAGG 函数）、MySQL（GROUP_CONCAT 函数）、SQL Server（STRING_AGG 函数）、PostgreSQL（STRING_AGG 函数）以及 SQLite（GROUP_CONCAT 函数）的语法和差异。

			
		

	





	

		

			

				
					
sql 字符串函数

				
			

			

				

					m0_71368368的博客
				

				

					09-06
					
					1750
					
				

			

		

		

			
				
代码写出来如下：



代码有点多没截完

第二个表如下：



代码如下展示：









第三个表如下：









代码如下：







第四张表





代码如下：



字符串函数



1.charindex 用来寻找这个字在字符串中的位置

select charindex('SQl','my SQL Course' ,1);

返回 4

------------------------------------------------------

2.len...

			
		

	



		

			
		



	

		

			

				
					
oracle中sql拼接字符串函数,sql字符串连接函数

				
			

			

				

					weixin_32344641的博客
				

				

					04-08
					
					3058
					
				

			

		

		

			
				
sql 字符串连接函数在sql中字符串连接函数我们学用到CONCAT()来，CONCAT() 的语法如下：CONCAT(字串1, 字串2, 字串3, ...): 将字串1、字串2、字串3，等字串连在一起sql 字符串连接函数在sql中字符串连接函数我们学用到concat()来，concat() 的语法如下：concat(字串1, 字串2, 字串3, ...): 将字串1、字串2、字串3，等字串连在...

			
		

	





	

		

			

				
					
sql语言之常用日期函数和字符串函数

				
			

			

				

					孤行者程序之路的博客
				

				

					03-19
					
					2322
					
				

			

		

		

			
				
根据字面意思就基本上理解了，日期函数就是对日期进行处理的，字符串函数就是处理字符串的，下面一个个单独介绍。一，日期函数1，计算两个日期之间的间隔语法：datediff(参与计算的日期部分，开始日期，结束日期)参数解释：参与计算的日期部分：该参数可以为年、月、日、时、分、秒，分别是：year/month/day/hour/minute/second开始日期：时间间隔的开始日期结束日期：时间间隔的结束...

			
		

	





	

		

			

				
					
SqlServer 日期时间格式 字符串相互转换 及相关函数

				
			

			

				

					请大家直接把问题写在评论区或留言，不要只说一句"你好 或 在吗"，我会尽快回复的。
				

				

					07-09
					
					3457
					
				

			

		

		

			
				
一、sqlserver日期时间函数






 Sql Server中的日期与时间函数 
1.  当前系统日期、时间 
    select getdate()  

2. dateadd  在向指定日期加上一段时间的基础上，返回新的 datetime 值
   例如：向日期加上2天 
   select dateadd(day,2,'2004-10-15')  --返回：2004-10-17 00:00:00.000 

3. datediff 返回跨两个指定日期的日期和时间边界数。
   sel...

			
		

	





	

		

			

				
					
SQL中的字符串截取函数

					
热门推荐

				
			

			

				

					晓晓的天空
				

				

					08-08
					
					2万+
					
				

			

		

		

			
				
字符串截取函数：left()、right()、substr()、substring_index() 函数

			
		

	





	

		

			

				
					
SQL 表值函数之字符串拆分

				
			

			

				

					Tony.Dong的专栏
				

				

					12-21
					
					5767
					
				

			

		

		

			
				
本文介绍了如何利用表值函数将单个字符串拆分成多行字符串。SQL Server和PostgreSQL提供了内置的函数，Oracle可以利用PL/SQL创建自定义的表值函数、MySQL和SQLite可以使用通用表表达式实现这一功能。

			
		

	





	

		

			

				
					
SQL Server截取字符串函数操作

				
			

			

				

					wu_Dream的博客
				

				

					11-23
					
					1万+
					
				

			

		

		

			
				
Sqlserver截取字符串

			
		

	





	

		

			

				
					
SQL Server 数据库之日期和时间函数

				
			

			

				

					程序员小白的博客
				

				

					05-10
					
					8477
					
				

			

		

		

			
				
日期函数1. 概述2. Getdate 函数3. Day 函数4. Month 函数5. Year 函数6. Datediff 函数7. Dateadd 函数
1. 概述
日期和时间函数主要用于处理日期和是键数据，并返回字符串、数值或日期时间数据；
SQL Server 数据库中的日期和时间函数包括获取系统日期和时间值的函数、获取日期和时间部分的函数、获取日期和时间差的函数、修改日期和时间值的函数、设置或获取会话格式的函数和验证日期和时间值的函数；

2. Getdate 函数
Getdate 函数返回当前

			
		

	





	

		

			

				
					
Sql Server 字符串聚合函数

				
			

			

				

					12-15
				

			

		

		

			
				
利用SUM、AVG、COUNT、COUNT(*)、MAX 和 MIN是无法做到的。因为这些都是对数值的聚合。不过我们可以通过自定义函数的方式来解决这个问题。1.首先建立测试表，并插入测试数据： 代码如下:create table ...

			
		

	





	

		

			

				
					
sql函数实现去除字符串中的相同的字符串

				
			

			

				

					09-10
				

			

		

		

			
				
去除字符串中的相同的字符，此功能在开发过程中很实用，为此本文整理了一些，希望对你了解它有所帮助

			
		

	





	

		

			

				
					
SQL Server字符串切割函数

				
			

			

				

					09-11
				

			

		

		

			
				
创建用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过 EXECUTE ...

			
		

	





	

		

			

				
					
mysql事务(原理)

					
最新发布

				
			

			

				

					m0_74347016的博客
				

				

					05-19
					
					524
					
				

			

		

		

			
				
mysql事务原理

			
		

	





	

		

			

				
					
php centos选择sqlserver的驱动和扩展选择版本的说明

				
			

			

				

					Z.X的博客
				

				

					05-14
					
					444
					
				

			

		

		

			
				
2023年2月23日13:41:48首先是php php扩展 驱动 数据库的关系
官方文档说明：
https://learn.microsoft.com/zh-cn/sql/connect/php/step-1-configure-development-environment-for-php-development?view=sql-server-2017https://learn.microsoft.com/zh-cn/sql/connect/php/system-requirements-for-th

			
		

	





	

		

			

				
					
生产报表工具PI DataLInk 与 行列视之间的区别

				
			

			

				

					2401_83701843的博客
				

				

					05-17
					
					250
					
				

			

		

		

			
				
行列视，全名“行列视生产数据应用系统”，行列视品牌所面对的业务范围远远超越了PIDatalink报表工具，他首先是一款本地部署的在线Excel文档管理系统，解决个人报表在线管理问题，其次，她和PIDataLInk一样，也是面向实时数据库报表需求的一套工具，其区别在于行列视品牌兼容国内外大多数实时数据库，也可以同时连接多个数据源，也就是她完全可以替代PI-DataLINk，同时也适用于其他品牌下的实时数据库系统；

			
		

	





	

		

			

				
					
MySQL存储过程添加二十万条数据日常练习

				
			

			

				

					mi_mx的博客
				

				

					05-17
					
					305
					
				

			

		

		

			
				
【代码】MySQL日常练习。

			
		

	





	

		

			

				
					
linux中查找某个文件或文件夹

				
			

			

				

					DN金猿的博客
				

				

					05-18
					
					245
					
				

			

		

		

			
				
locate命令将会在整个系统中搜索文件和文件夹，并通过正则表达式匹配以"/folder_name"结尾的路径。这将会在指定的路径（/path/to/search）中递归地列出所有文件和文件夹，并使用grep命令匹配以"/folder_name"结尾的行。该命令将会在指定的路径（/path/to/search）中查找名称为"folder_name"的文件夹。此方法与第2种方法类似，但使用了find命令来递归搜索文件夹，并使用grep命令来匹配以"/folder_name"结尾的行。

			
		

	





	

		

			

				
					
SQL指定字符切割字符串函数

				
			

			

				

					06-08
				

			

		

		

			
				
SQL中可以使用内置函数SUBSTRING_INDEX来切割字符串，它可以根据指定的分隔符将字符串切割成多个子字符串，并返回指定位置的子字符串。该函数的语法如下：  SUBSTRING_INDEX(str,delim,count)  其中，str是要切割的...

			
		

	



              



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

  

      
    
          
  • 
              
    
                  
                  
              
    
              
    非常没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    一般
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    有帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    非常有帮助
    
          
    • 
      

      

      

          
          提交
      

      

  




          




        



    





    



      

      

        
      

      





	

		
评论 1

	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  





	
打赏作者

	

		

		
			
		
		

		

			
妖精七七_

			
你的鼓励将是我创作的最大动力

		

	

	

			

	

	

    ¥1
    ¥2
    ¥4
    ¥6
    ¥10
    ¥20
	

	

		

			

				

					扫码支付：¥1
				

				

					

					
					获取中
					

				

				

					
					
					扫码支付
				

			

		

		

			
您的余额不足，请更换扫码支付或充值

			
打赏作者

		

	



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

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

              

            

          

      

      余额充值