mysql数据库学习笔记之常用操作命令(二)
ws affected (0.00 sec)
16、字符串连接函数---concat("string1","string2") 别名
mysql> select concat("li","haichao") myname;
+-----------+
| myname |
+-----------+
| lihaichao |
+-----------+
1 row in set (0.00 sec)
17、大写转换成小写的函数---lcase(string1)
mysql> select lcase("LHC");
+--------------+
| lcase("LHC") |
+--------------+
| lhc |
+--------------+
1 row in set (0.00 sec)
18、将字符串转换成大写的函数----ucase(string1);
mysql> select ucase("lhc");
+--------------+
| ucase("lhc") |
+--------------+
| LHC |
+--------------+
1 row in set (0.00 sec)
19、判断字符串长度的函数length(string1);
mysql> select length("lhc");
+---------------+
| length("lhc") |
+---------------+
| 3 |
+---------------+
1 row in set (0.02 sec)
20、去除前端和后端的空格函数 ltrim()和rtrim()
21、将指定的字符串重复n次，repeat(string ,count)
mysql> select repeat("linux",3);
+-------------------+
| repeat("linux",3) |
+-------------------+
| linuxlinuxlinux |
+-------------------+
1 row in set (0.02 sec)
22、字符串替换函数
在"linux is very good"中寻找linux，并将其替换成php
mysql> select replace("linux is very good","linux","php");
+---------------------------------------------+
| replace("linux is very good","linux","php") |
+---------------------------------------------+
| php is very good |
+---------------------------------------------+
1 row in set (0.01 sec)
23、字符串截取函数substring("str",int 1,int 2)
在str字符串中从int1开始(从1计)到int2结束(包含),取其字段
mysql>
select substring("linux is very good",1,5);
+-------------------------------------+
| substring("linux is very good",1,5) |
+-------------------------------------+
| linux |
+-------------------------------------+
1 row in set (0.00 sec)
24、space(）函数：生成空格的函数，通常与concat函数一起使用
mysql> select concat(space(50),"linux");
+---------------------------------------------------------+
| concat(space(50),"linux") |
+---------------------------------------------------------+
| linux |
+---------------------------------------------------------+
1 row in set (0.02 sec)
25、十进制转二进制函数BIN()
mysql> select BIN(255);
+----------+
| BIN(255) |
+----------+
| 11111111 |
+----------+
1 row in set (0.00 sec)
26、向上取整函数CEILING()，比如5.6则为6，向下取整floor(),比如5.6则为5
mysql> select ceiling(5.6);
+--------------+
| ceiling(5.6) |
+--------------+
| 6 |
+--------------+
1 row in set (0.01 sec)
************************************************************************
mysql> select floor(5.6);
+------------+
| floor(5.6) |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
27、取最大值和最小值
select sutdent_name,MIN(test_score),MAX(test_score) from student group by student_name;
28、返回随机数:RAND()
mysql> select ceiling( 10*RAND());
+---------------------+
| ceiling( 10*RAND()) |
+---------------------+
| 4 |
+---------------------+
1 row in set (0.00 sec)