Ai分享|
1 2 3 4 5 6 7 8 9 |
转换小写 : lower() 转换大写 : upper() 取子串 : substr() 字符串拼接 : concat() 取长度 : length() 去空格 : trim() 四舍五入 : round() 生成随机数 : rand() null转换 : ifnull() |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
//整个表信息 mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ mysql> select * from t_student; +----+-----------+ | ID | name | +----+-----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | | 4 | jack_son | +----+-----------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
//转换小写 : lower() mysql> select lower(ename) as ename from emp; +--------+ | ename | +--------+ | smith | | allen | | ward | | jones | | martin | | blake | | clark | | scott | | king | | turner | | adams | | james | | ford | | miller | +--------+ |
|
1 2 3 4 5 6 7 8 9 10 |
//转换大写 : upper() mysql> select upper(name) as name from t_student; +-----------+ | name | +-----------+ | ZHANGSAN | | LISI | | WANGWU | | JACK_SON | +-----------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
//取子串 : substr() //substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)） //注意：起始下标从1开始，没有0 mysql> select substr(ename, 1, 1) as ename from emp; +-------+ | ename | +-------+ | S | | A | | W | | J | | M | | B | | C | | S | | K | | T | | A | | J | | F | | M | +-------+ //找出员工名字第一个字母是A的员工信息？ //第一种方式：模糊查询 mysql> select ename from emp where ename like 'A%'; +-------+ | ename | +-------+ | ALLEN | | ADAMS | +-------+ //第二种方式：substr函数 mysql> select ename from emp where substr(ename,1,1) = 'A'; +-------+ | ename | +-------+ | ALLEN | | ADAMS | +-------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
//字符串拼接 : concat() mysql> select concat(empno,ename) from emp; +---------------------+ | concat(empno,ename) | +---------------------+ | 7369SMITH | | 7499ALLEN | | 7521WARD | | 7566JONES | | 7654MARTIN | | 7698BLAKE | | 7782CLARK | | 7788SCOTT | | 7839KING | | 7844TURNER | | 7876ADAMS | | 7900JAMES | | 7902FORD | | 7934MILLER | +---------------------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
//取长度 : length() mysql> select length(ename) enamelength from emp; +-------------+ | enamelength | +-------------+ | 5 | | 5 | | 4 | | 5 | | 6 | | 5 | | 5 | | 5 | | 4 | | 6 | | 5 | | 5 | | 4 | | 6 | +-------------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
//多函数应用:查找返回首字母大写 //1.使用 substr 取出 name 第一个子串; //2.使用 length 取出 name 总长度-1(已取),再使用 substr 取出 name 剩余字符串 //3.使用 concat 将前后字符串拼接在一起. mysql> select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student; +-----------+ | result | +-----------+ | Zhangsan | | Lisi | | Wangwu | | Jack_son | +-----------+ |
|
1 2 3 4 5 6 7 8 9 10 11 |
//去空格 : trim() //直接查询,返回记录空 mysql> select * from emp where ENAME = ' KING'; Empty set (0.00 sec) mysql> select * from emp where ename = trim(' KING'); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | +-------+-------+-----------+------+------------+---------+------+--------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
//四舍五入 : round() //round(要舍入的字段,规定要返回的小数位数) mysql> select round(1236.567, 2) as result from emp; +---------+ | result | +---------+ | 1236.57 | | 1236.57 | | 1236.57 | | 1236.57 | +---------+ //round 第二参数为负数,则从整数开始四舍五入 mysql> select round(1236.567, -1) as result from emp; +--------+ | result | +--------+ | 1240 | | 1240 | | 1240 | | 1240 | +--------+ |
|
1 2 3 4 5 6 7 8 |
//生成随机数 : rand() //返回0~1之间的随机数 mysql> select RAND(); +--------------------+ | RAND() | +--------------------+ | 0.6724663625214867 | +--------------------+ |
|
1 2 3 4 5 6 7 |
//实例:取随机整数 mysql> select round(rand()*100,0); +---------------------+ | round(rand()*100,0) | +---------------------+ | 98 | +---------------------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
//null转换 : ifnull() //ifnull是空处理函数。专门处理空的。 //在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。 //计算每个员工的年薪？ 年薪 = (月薪 + 月补助) * 12 mysql> select ename, (sal + comm) * 12 as yearsal from emp; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | NULL | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | NULL | | MARTIN | 31800.00 | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | TURNER | 18000.00 | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+----------+ /* 注意：NULL只要参与运算，最终结果一定是NULL。为了避免这个现象，需要使用ifnull函数。 ifnull函数用法：ifnull(数据, 被当做哪个值) 如果“数据”为NULL的时候，把这个数据结构当做哪个值。 补助为NULL的时候，将补助当做0 */ mysql> select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | 35700.00 | | MARTIN | 31800.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ |
