字符串 A = 'a{bc}de{fghij}kl[mnop]qrst ' B= ' L[MNOP] Q ' 浮点数 C = 123456.78953
1、将A变为大写,B变为小写,并返回每个字符串的长度
select lcase('a{bc}de{fghij}kl[mnop]qrst ' );
+------------------------------------------+
| lcase('a{bc}de{fghij}kl[mnop]qrst ' ) |
+------------------------------------------+
| a{bc}de{fghij}kl[mnop]qrst |
+------------------------------------------+
select upper('a{bc}de{fghij}kl[mnop]qrst ' );
+------------------------------------------+
| upper('a{bc}de{fghij}kl[mnop]qrst ' ) |
+------------------------------------------+
| A{BC}DE{FGHIJ}KL[MNOP]QRST |
+------------------------------------------+
select char_length('a{bc}de{fghij}kl[mnop]qrst ' );
+-----------------------------------------------+
| char_length('a{bc}de{fghij}kl[mnop]qrst ' ) |
+-----------------------------------------------+
| 28 |
+-----------------------------------------------+
select char_length( ' L[MNOP] Q ' );
+------------------------------------+
| char_length( ' L[MNOP] Q ' ) |
+------------------------------------+
| 17 |
+------------------------------------+
2、A 去掉右边空格 B去掉两端空格
select rtrim('a{bc}de{fghij}kl[mnop]qrst ' );
+------------------------------------------+
| rtrim('a{bc}de{fghij}kl[mnop]qrst ' ) |
+------------------------------------------+
| a{bc}de{fghij}kl[mnop]qrst |
+------------------------------------------+
select trim(' L[MNOP] Q ');
+---------------------------+
| trim(' L[MNOP] Q ') |
+---------------------------+
| L[MNOP] Q |
+---------------------------+
3、取B中方括号中的文字,并变为小写
select lcase( substr( ' L[MNOP] Q ',
locate('[',' L[MNOP] Q ' )+1,
locate(']',' L[MNOP] Q ' )-locate('[',' L[MNOP] Q ' )-1)) as result;
+--------+
| result |
+--------+
| mnop |
+--------+
4、取B中'mn'的位置
select locate('mn',' L[MNOP] Q ' );
+-----------------------------------+
| locate('mn',' L[MNOP] Q ' ) |
+-----------------------------------+
| 5 |
+-----------------------------------+
5、生成15个字符长度的单据流水号时,显示 14324 号单据的完整单据号
(即总长度为15,不足的位数前面补0,如:23号单据号为 000000000000023 ,123号单据为 000000000000123)
select lpad('14324',15,'0');
+----------------------+
| lpad('14324',15,'0') |
+----------------------+
| 000000000014324 |
+----------------------+
6、将B中方括号中的文字颠倒过来输出
select reverse (substr(' L[MNOP] Q ',
locate ('[',' L[MNOP]
Q ')+1,locate(']',' L[MNOP] Q ' )-locate ('[',' L[MNOP] Q ')-1)) result;
+--------+
| result |
+--------+
| PONM |
+--------+
7、将B中方括号中的文字颠倒过来放回B字符串的方括号中
SELECT
concat(substr(' L[MNOP] Q ' ,
1,
locate('[',
' L[MNOP] Q ')),
reverse(substr(' L[MNOP] Q ' ,
locate('[',
' L[MNOP] Q ')+ 1,
locate(']',
' L[MNOP] Q ' )-locate('[',
' L[MNOP] Q ')-1)),
substr(' L[MNOP] Q ' ,
locate(']',
' L[MNOP] Q '),
char_length(' L[MNOP] Q ')-locate('[',
' L[MNOP] Q ')-1)) RESULT;
9、将B中方括号外的字符变为小写
SELECT
concat(lcase(substr(' L[MNOP] Q ' ,
1,
locate('[',
' L[MNOP] Q '))),
substr(' L[MNOP] Q ' ,
locate('[',
' L[MNOP]
Q ')+ 1,
locate(']',
' L[MNOP] Q ' )-locate('[',
' L[MNOP] Q ')-1),
lcase(substr(' L[MNOP] Q ' ,
locate(']',
' L[MNOP] Q '),
char_length(' L[MNOP] Q ')-locate('[',
' L[MNOP] Q ')-1))) RESULT;
10、取A中方括号中的文字与B中方括号中的文字比较
SELECT
strcmp(substr('a{bc}de{fghij}kl[mnop]qrst ',
locate('[',
'a{bc}de{fghij}kl[mnop]qrst')+ 1,
locate(']',
'a{bc}de{fghij}kl[mnop]qrst')-locate('[',
'a{bc}de{fghij}kl[mnop]qrst')-1),
substr('a{bc}de{fghij}kl[mnop]qrst ',
locate('[',
' L[MNOP] Q ' )+ 1,
locate(']',
' L[MNOP] Q ')-locate('[',
' L[MNOP] Q ' )-1));
11、取A中方括号中的文字并变为大写与B中方括号中的文字比较
select strcmp( substr( 'a{bc}de{fghij}kl[mnop]qrst ' ,locate('[', 'a{bc}de{fghij}kl[mnop]qrst ' )+1,locate(']', 'a{bc}de{fghij}kl[mnop]qrst
' )-locate('[', 'a{bc}de{fghij}kl[mnop]qrst ' )-1),substr(' L[MNOP]
Q ' ,locate('[',' L[MNOP] Q ' )+1,locate(']',' L[MNOP] Q ' )-locate('[',' L[MNOP] Q ' )-1)) l;
+------+
| l |
+------+
| 0 |
+------+
12、将 C 保留两位小数,第三位四舍五入(两个方法)
select format(123456.78953,2);
+------------------------+
| format(123456.78953,2) |
+------------------------+
| 123,456.79 |
+------------------------+
select round(123456.78953,2);
13、将 C 保留两位小数,不管第3位是什么都去掉
select truncate(123456.78953,2);
14、返回'男'这个字符串在表 student 中的第几列
select *,find_in_set('男', concat_ws(',',s_id,s_name,s_birth,s_sex))
POSITION from student ;
+------+-----------+------------+-------+----------+
| s_id | s_name | s_birth | s_sex | POSITION |
+------+-----------+------------+-------+----------+
| 2 | 钱电 | 1990-12-21 | 男 | 4 |
| 3 | 孙风 | 1990-05-20 | 男 | 4 |
| 4 | 李云 | 1990-08-06 | 男 | 4 |
| 5 | 周梅 | 1991-12-01 | 女 | 0 |
| 6 | 吴兰 | 1992-03-01 | 女 | 0 |
| 7 | 郑竹 | 1989-07-01 | 女 | 0 |
| 8 | 王菊 | 1990-01-20 | 女 | 0 |
| 10 | 李鑫 | 1992-03-01 | 男 | 4 |
| 99 | 男 | 1111-11-11 | 男 | 2 |
| 100 | 魏宇轩 | 2003-07-01 | 男 | 4 |
| 111 | 李茂 | 1991-01-01 | 男 | 4 |
+------+-----------+------------+-------+----------+
15、将学生表的各列以:为分隔返回列表
select concat_ws(':',s_id,s_name,s_sex,s_birth) from student;
+------------------------------------------+
| concat_ws(':',s_id,s_name,s_sex,s_birth) |
+------------------------------------------+
| 2:钱电:男:1990-12-21 |
| 3:孙风:男:1990-05-20 |
| 4:李云:男:1990-08-06 |
| 5:周梅:女:1991-12-01 |
| 6:吴兰:女:1992-03-01 |
| 7:郑竹:女:1989-07-01 |
| 8:王菊:女:1990-01-20 |
| 10:李鑫:男:1992-03-01 |
| 99:男:男:1111-11-11 |
| 100:魏宇轩:男:2003-07-01 |
| 111:李茂:男:1991-01-01 |
+------------------------------------------+
16、返回学生表中姓名为 '魏X轩'的学生信息
select * from student where s_name like '魏_轩';
+------+-----------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+-----------+------------+-------+
| 100 | 魏宇轩 | 2003-07-01 | 男 |
+------+-----------+------------+-------+
17、去掉B字符串所有的空格
select replace (' L[MNOP] Q ',' ','' );
+---------------------------------------+
| replace (' L[MNOP] Q ',' ','' ) |
+---------------------------------------+
| L[MNOP]Q |
+---------------------------------------+
18、用0来替换null值
select *,ifnull(s_score,0) from score;
19、根据列的值返回新值
select * ,case s_sex when '男' then '男生' else '女生' end from student;
20、根据生日返回年龄段
SELECT
s_id,
s_name,
YEAR(s_birth),
CASE
WHEN s_birth >= '1990-01-01'
AND s_birth <= '1999-12-31' THEN '九零后'
WHEN s_birth>'1999-12-31'
AND s_birth <= '2009-12-31' THEN '零零后'
ELSE '未知'
END description
FROM
student;