1.数据字段加和,合并
select ename, ( sal+ comm) * 12 年薪 from emp;
select CONCAT( ename, '的年薪是' , sql ) 员工薪资 from emp;
update emp set ename = 'JAM%ES' where empno = 7900 ;
2.模糊查询
select * from emp WHERE ename LIKE '%N' ;
select * from emp WHERE ename LIKE 'M%' ;
select * from emp WHERE ename LIKE '%M%' ;
SELECT * FROM emp WHERE ename LIKE '__M%' ;
update emp set ename = 'JAM%ES' where empno = 7900 ;
select * from emp WHERE ename LIKE '%\%%' ;
mysql> select * from emp WHERE ename LIKE '%N' ;
select * from emp WHERE ename LIKE 'M%' ;
select * from emp WHERE ename LIKE '%M%' ;
+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+
| 7499 | ALLEN | SALESMAN | 7698 | 1981 - 02 - 20 | 1600 | 300 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981 - 09 - 28 | 1250 | 1400 | 30 |
+
2 rows in set
+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+
| 7654 | MARTIN | SALESMAN | 7698 | 1981 - 09 - 28 | 1250 | 1400 | 30 |
+
1 row in set
+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+
| 7369 | SMITH | CLERK | 7902 | 1980 - 12 - 17 | 800 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981 - 09 - 28 | 1250 | 1400 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987 - 07 - 13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981 - 12 - 03 | 950 | NULL | 30 |
+
4 rows in set
mysql> SELECT * FROM emp WHERE ename LIKE '__M%'
- > ;
+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+
| 7900 | JAMES | CLERK | 7698 | 1981 - 12 - 03 | 950 | NULL | 30 |
+
1 row in set
mysql> update emp set ename = 'JAM%ES' where empno = 7900 ;
Query OK, 1 row affected
Rows matched : 1 Changed: 1 Warnings : 0
mysql> select * from emp;
+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+
| 7369 | SMITH | CLERK | 7902 | 1980 - 12 - 17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981 - 02 - 20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981 - 02 - 22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981 - 04 - 02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981 - 09 - 28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981 - 05 - 01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981 - 06 - 09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987 - 07 - 13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981 - 11 - 17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981 - 09 - 08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987 - 07 - 13 | 1100 | NULL | 20 |
| 7900 | JAM% ES | CLERK | 7698 | 1981 - 12 - 03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981 - 12 - 03 | 3000 | NULL | 20 |
+
13 rows in set
mysql> select * from emp WHERE ename LIKE '%\%%' ;
+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+
| 7900 | JAM% ES | CLERK | 7698 | 1981 - 12 - 03 | 950 | NULL | 30 |
+
1 row in set
3.升序降序排序,分页
select * from emp order by sal desc ;
select * from emp limit 3 ;
select * from emp order by deptno, sal desc ;
select * from emp where sal> 1000 order by sal desc limit 0 , 5 ;
mysql> select * from emp order by deptno, sal desc ;
+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+
| 7839 | KING | PRESIDENT | NULL | 1981 - 11 - 17 | 5000 | NULL | 10 |
| 7782 | CLARK | MANAGER | 7839 | 1981 - 06 - 09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987 - 07 - 13 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981 - 12 - 03 | 3000 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981 - 04 - 02 | 2975 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987 - 07 - 13 | 1100 | NULL | 20 |
| 7369 | SMITH | CLERK | 7902 | 1980 - 12 - 17 | 800 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981 - 05 - 01 | 2850 | NULL | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981 - 02 - 20 | 1600 | 300 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981 - 09 - 08 | 1500 | 0 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981 - 02 - 22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981 - 09 - 28 | 1250 | 1400 | 30 |
| 7900 | JAM% ES | CLERK | 7698 | 1981 - 12 - 03 | 950 | NULL | 30 |
+
13 rows in set
mysql> select * from emp where sal> 1000 order by sal desc limit 0 , 5 ;
+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+
| 7839 | KING | PRESIDENT | NULL | 1981 - 11 - 17 | 5000 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987 - 07 - 13 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981 - 12 - 03 | 3000 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981 - 04 - 02 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981 - 05 - 01 | 2850 | NULL | 30 |
+
5 rows in set
4.单行函数
1. 函数 字符串,日期,数字
select lower( ename) , length( ename) froom emp;
select lower( ename) , length( ename) , lpad( ename, 8 , '#' ) , rpad( ename, 8 , '#' ) , ltrim( ' 123 ' ) , rtrim( ' 123 ' ) from emp;
select lower( ename) , length( ename) , lpad( ename, 8 , '#' ) , rpad( ename, 8 , '#' ) , ltrim( ' 123 ' ) , rtrim( ' 123 ' ) , replace ( ename, 'S' , '@' ) , SUBSTR( ename, 2 , 3 ) from emp;
select lower( ename) , length( ename) , lpad( ename, 8 , '#' ) , rpad( ename, 8 , '#' ) , ltrim( ' 123 ' ) , rtrim( ' 123 ' ) , replace ( ename, 'S' , '@' ) , SUBSTR( ename, 2 , 3 ) from emp where SUBSTR( ename, 2 , 1 ) = 'A' ;
mysql> select lower( ename) , length( ename) froom emp;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'emp' at line 1
mysql> select lower( ename) , length( ename) from emp;
+
| lower( ename) | length( ename) |
+
| smith | 5 |
| allen | 5 |
| ward | 4 |
| jones | 5 |
| martin | 6 |
| blake | 5 |
| clark | 5 |
| scott | 5 |
| king | 4 |
| turner | 6 |
| adams | 5 |
| jam% es | 6 |
| ford | 4 |
+
13 rows in set
mysql> select lower( ename) , length( ename) , lpad( ename, 8 , '#' ) , rpad( ename, 8 , '#' ) , ltrim( ' 123 ' ) , rtrim( ' 123 ' ) from emp;
+
| lower( ename) | length( ename) | lpad( ename, 8 , '#' ) | rpad( ename, 8 , '#' ) | ltrim( ' 123 ' ) | rtrim( ' 123 ' ) |
+
| smith | 5 |
| allen | 5 |
| ward | 4 |
| jones | 5 |
| martin | 6 |
| blake | 5 |
| clark | 5 |
| scott | 5 |
| king | 4 |
| turner | 6 |
| adams | 5 |
| jam% es | 6 |
| ford | 4 |
+
13 rows in set
mysql> select lower( ename) , length( ename) , lpad( ename, 8 , '#' ) , rpad( ename, 8 , '#' ) , ltrim( ' 123 ' ) , rtrim( ' 123 ' ) , replace ( ename, 'S' , '@' ) , SUBSTR( ename, 2 , 3 ) from emp;
+
| lower( ename) | length( ename) | lpad( ename, 8 , '#' ) | rpad( ename, 8 , '#' ) | ltrim( ' 123 ' ) | rtrim( ' 123 ' ) | replace ( ename, 'S' , '@' ) | SUBSTR( ename, 2 , 3 ) |
+
| smith | 5 |
| allen | 5 |
| ward | 4 |
| jones | 5 |
| martin | 6 |
| blake | 5 |
| clark | 5 |
| scott | 5 |
| king | 4 |
| turner | 6 |
| adams | 5 |
| jam% es | 6 |
| ford | 4 |
+
13 rows in set
mysql> select lower( ename) , length( ename) , lpad( ename, 8 , '#' ) , rpad( ename, 8 , '#' ) , ltrim( ' 123 ' ) , rtrim( ' 123 ' ) , replace ( ename, 'S' , '@' ) , SUBSTR( ename, 2 , 3 ) from emp where SUBSTR( ename, 2 , 1 ) = 'A' ;
+
| lower( ename) | length( ename) | lpad( ename, 8 , '#' ) | rpad( ename, 8 , '#' ) | ltrim( ' 123 ' ) | rtrim( ' 123 ' ) | replace ( ename, 'S' , '@' ) | SUBSTR( ename, 2 , 3 ) |
+
| ward | 4 |
| martin | 6 |
| jam% es | 6 |
+
3 rows in set
mysql>
floor向下取整 ceil 向上取整 power 平方 round 四舍五入 dual 虚拟表
mysql> select abs( - 10 ) , floor( 3.2 ) , floor( 3.8 ) , CEIL( 3.2 ) , CEIL( 3.8 ) , power( 2 , 3 ) from dual;
+
| abs( - 10 ) | floor( 3.2 ) | floor( 3.8 ) | CEIL( 3.2 ) | CEIL( 3.8 ) | power( 2 , 3 ) |
+
| 10 | 3 | 3 | 4 | 4 | 8 |
+
1 row in set
获取日期和时间 now()当前的时间 extract (unit from hiredata)from 表名; 拆封截,抽取数据
mysql> select hiredate, now ( ) from emp;
+
| hiredate | now ( ) |
+
| 1980 - 12 - 17 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 02 - 20 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 02 - 22 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 04 - 02 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 09 - 28 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 05 - 01 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 06 - 09 | 2022 - 01 - 05 12 :22 :00 |
| 1987 - 07 - 13 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 11 - 17 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 09 - 08 | 2022 - 01 - 05 12 :22 :00 |
| 1987 - 07 - 13 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 12 - 03 | 2022 - 01 - 05 12 :22 :00 |
| 1981 - 12 - 03 | 2022 - 01 - 05 12 :22 :00 |
+
13 rows in set
mysql> select extract( day from hiredate) from emp;
+
| extract( day from hiredate) |
+
| 17 |
| 20 |
| 22 |
| 2 |
| 28 |
| 1 |
| 9 |
| 13 |
| 17 |
| 8 |
| 13 |
| 3 |
| 3 |
+
13 rows in set
mysql> select extract( year from hiredate) from emp;
mysql> select extract( month from hiredate) from emp;
mysql>
获取时间的一些基础操作 CURRENT_DATE() 当前的时间年月日 CURRENT_TIME() 当前的时间时分秒 CURRENT_TIMESTAMP() 时间戳 年月日时分秒
select hiredate,CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP() from emp;
mysql> select hiredate, CURRENT_DATE ( ) , CURRENT_TIME ( ) , CURRENT_TIMESTAMP ( ) from emp;
+
| hiredate | CURRENT_DATE ( ) | CURRENT_TIME ( ) | CURRENT_TIMESTAMP ( ) |
+
| 1980 - 12 - 17 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 02 - 20 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 02 - 22 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 04 - 02 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 09 - 28 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 05 - 01 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 06 - 09 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1987 - 07 - 13 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 11 - 17 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 09 - 08 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1987 - 07 - 13 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 12 - 03 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
| 1981 - 12 - 03 | 2022 - 01 - 05 | 12 :33 :23 | 2022 - 01 - 05 12 :33 :23 |
+
13 rows in set
数据化格式化年月日的不同大小写显示示例 时间加减 select hiredate,adddate(hiredate,9),adddate(hiredate,-9) from emp;
last_day :last_day(hiredate)-3
这个月的倒数第三天
mysql> select hiredate, date_format( hiredate, '%Y年%M月%D日' ) from emp;
+
| hiredate | date_format( hiredate, '%Y年%M月%D日' ) |
+
| 1980 - 12 - 17 | 1980 年December月17 th日 |
| 1981 - 02 - 20 | 1981 年February月20 th日 |
| 1981 - 02 - 22 | 1981 年February月22 nd日 |
| 1981 - 04 - 02 | 1981 年April月2 nd日 |
| 1981 - 09 - 28 | 1981 年September月28 th日 |
| 1981 - 05 - 01 | 1981 年May月1 st日 |
| 1981 - 06 - 09 | 1981 年June月9 th日 |
| 1987 - 07 - 13 | 1987 年July月13 th日 |
| 1981 - 11 - 17 | 1981 年November月17 th日 |
| 1981 - 09 - 08 | 1981 年September月8 th日 |
| 1987 - 07 - 13 | 1987 年July月13 th日 |
| 1981 - 12 - 03 | 1981 年December月3 rd日 |
| 1981 - 12 - 03 | 1981 年December月3 rd日 |
+
13 rows in set
mysql> select hiredate, date_format( hiredate, '%Y年%m月%d日' ) from emp;
+
| hiredate | date_format( hiredate, '%Y年%m月%d日' ) |
+
| 1980 - 12 - 17 | 1980 年12 月17 日 |
| 1981 - 02 - 20 | 1981 年02 月20 日 |
| 1981 - 02 - 22 | 1981 年02 月22 日 |
| 1981 - 04 - 02 | 1981 年04 月02 日 |
| 1981 - 09 - 28 | 1981 年09 月28 日 |
| 1981 - 05 - 01 | 1981 年05 月01 日 |
| 1981 - 06 - 09 | 1981 年06 月09 日 |
| 1987 - 07 - 13 | 1987 年07 月13 日 |
| 1981 - 11 - 17 | 1981 年11 月17 日 |
| 1981 - 09 - 08 | 1981 年09 月08 日 |
| 1987 - 07 - 13 | 1987 年07 月13 日 |
| 1981 - 12 - 03 | 1981 年12 月03 日 |
| 1981 - 12 - 03 | 1981 年12 月03 日 |
+
13 rows in set
mysql> select hiredate, date_format( hiredate, '%y年%m月%d日' ) from emp;
+
| hiredate | date_format( hiredate, '%y年%m月%d日' ) |
+
| 1980 - 12 - 17 | 80 年12 月17 日 |
| 1981 - 02 - 20 | 81 年02 月20 日 |
| 1981 - 02 - 22 | 81 年02 月22 日 |
| 1981 - 04 - 02 | 81 年04 月02 日 |
| 1981 - 09 - 28 | 81 年09 月28 日 |
| 1981 - 05 - 01 | 81 年05 月01 日 |
| 1981 - 06 - 09 | 81 年06 月09 日 |
| 1987 - 07 - 13 | 87 年07 月13 日 |
| 1981 - 11 - 17 | 81 年11 月17 日 |
| 1981 - 09 - 08 | 81 年09 月08 日 |
| 1987 - 07 - 13 | 87 年07 月13 日 |
| 1981 - 12 - 03 | 81 年12 月03 日 |
| 1981 - 12 - 03 | 81 年12 月03 日 |
+
13 rows in set
mysql> select hiredate, date_format( now ( ) , '%y年%m月%d日 %H时%i分%s秒' ) from emp;
+
| hiredate | date_format( now ( ) , '%y年%m月%d日 %H时%i分%s秒' ) |
+
| 1980 - 12 - 17 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 02 - 20 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 02 - 22 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 04 - 02 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 09 - 28 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 05 - 01 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 06 - 09 | 22 年01 月05 日 14 时11 分41 秒 |
| 1987 - 07 - 13 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 11 - 17 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 09 - 08 | 22 年01 月05 日 14 时11 分41 秒 |
| 1987 - 07 - 13 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 12 - 03 | 22 年01 月05 日 14 时11 分41 秒 |
| 1981 - 12 - 03 | 22 年01 月05 日 14 时11 分41 秒 |
+
mysql> select hiredate, adddate( hiredate, 9 ) , adddate( hiredate, - 9 ) from emp;
+
| hiredate | adddate( hiredate, 9 ) | adddate( hiredate, - 9 ) |
+
| 1980 - 12 - 17 | 1980 - 12 - 26 | 1980 - 12 - 08 |
| 1981 - 02 - 20 | 1981 - 03 - 01 | 1981 - 02 - 11 |
| 1981 - 02 - 22 | 1981 - 03 - 03 | 1981 - 02 - 13 |
| 1981 - 04 - 02 | 1981 - 04 - 11 | 1981 - 03 - 24 |
| 1981 - 09 - 28 | 1981 - 10 - 07 | 1981 - 09 - 19 |
| 1981 - 05 - 01 | 1981 - 05 - 10 | 1981 - 04 - 22 |
| 1981 - 06 - 09 | 1981 - 06 - 18 | 1981 - 05 - 31 |
| 1987 - 07 - 13 | 1987 - 07 - 22 | 1987 - 07 - 04 |
| 1981 - 11 - 17 | 1981 - 11 - 26 | 1981 - 11 - 08 |
| 1981 - 09 - 08 | 1981 - 09 - 17 | 1981 - 08 - 30 |
| 1987 - 07 - 13 | 1987 - 07 - 22 | 1987 - 07 - 04 |
| 1981 - 12 - 03 | 1981 - 12 - 12 | 1981 - 11 - 24 |
| 1981 - 12 - 03 | 1981 - 12 - 12 | 1981 - 11 - 24 |
+
13 rows in set
mysql> select date_format( last_day( hiredate) - 3 , '%y年%m月%d日 %H时%i分%s秒' ) , date_format( now ( ) - 3 , '%y年%m月%d日 %H时%i分%s秒' ) , now ( ) - 3 from emp;
+
| date_format( last_day( hiredate) - 3 , '%y年%m月%d日 %H时%i分%s秒' ) | date_format( now ( ) - 3 , '%y年%m月%d日 %H时%i分%s秒' ) | now ( ) - 3 |
+
| 80 年12 月28 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年02 月25 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年02 月25 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年04 月27 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年09 月27 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年05 月28 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年06 月27 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 87 年07 月28 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年11 月27 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年09 月27 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 87 年07 月28 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年12 月28 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
| 81 年12 月28 日 00 时00 分00 秒 | 22 年01 月05 日 14 时36 分55 秒 | 20220105143655 |
+
13 rows in set
mysql>
2.其他函数
select sal+ comm from emp;
select sal+ comm from emp;
mysql> select sal+ comm from emp;
+
| sal+ comm |
+
| NULL |
| 1900 |
| 1750 |
| NULL |
| 2650 |
| NULL |
| NULL |
| NULL |
| NULL |
| 1500 |
| NULL |
| NULL |
| NULL |
+
13 rows in set
mysql> select sal+ IFNULL ( comm, 0 ) from emp;
+
| sal+ IFNULL ( comm, 0 ) |
+
| 800 |
| 1900 |
| 1750 |
| 2975 |
| 2650 |
| 2850 |
| 2450 |
| 3000 |
| 5000 |
| 1500 |
| 1100 |
| 950 |
| 3000 |
+
13 rows in set
mysql>
2.MD5 非可逆加密 dual 续表试验 select MD5('hello') from dual
mysql> select MD5( 'hello' ) from dual;
+
| MD5( 'hello' ) |
+
| 5 d41402abc4b2a76b9719d911017c592 |
+
1 row in set
3.转换函数
日期到字符串穿 data_format()
转换格式整齐的 年月日,时分秒 select extract(month from '10月2021年11日');
字符串到日期str_to_data(),
转换格式不整齐的 select str_to_date('10月2022年11日','%m月%Y年%d日');
数字到字符串
select concat(123,'456','aaa');
select lpad(123,10,'#');
mysql> select extract( month from '10月2021年11日' ) , adddate( '2021-10-11' , - 9 ) ;
+
| extract( month from '10月2021年11日' ) | adddate( '2021-10-11' , - 9 ) |
+
| NULL | 2021 - 10 - 02 |
+
1 row in set
mysql> select str_to_date( '10月2022年11日' , '%m月%Y年%d日' ) ;
+
| str_to_date( '10月2022年11日' , '%m月%Y年%d日' ) |
+
| 2022 - 10 - 11 |
+
1 row in set
mysql> select lpad( 123 , 10 , '#' ) ;
+
| lpad( 123 , 10 , '#' ) |
+
|
+
1 row in set
mysql> select concat( 123 , '456' , 'aaa' ) ;
+
| concat( 123 , '456' , 'aaa' ) |
+
| 123456 aaa |
+
1 row in set
5.多行函数
1.多行函数介绍使用
max() min() count() avg() sum()
mysql> select max ( sal) , min ( sal) , count ( sal) , count ( job) , avg ( sal) , sum ( sal) from emp;
+
| max ( sal) | min ( sal) | count ( sal) | count ( job) | avg ( sal) | sum ( sal) |
+
| 5000 | 800 | 13 | 13 | 2132.692308 | 27725.00 |
+
1 row in set
2.数据分组
mysql> select job, max ( sal) from emp group by job;
+
| job | max ( sal) |
+
| CLERK | 1100 |
| SALESMAN | 1600 |
| MANAGER | 2975 |
| ANALYST | 3000 |
| PRESIDENT | 5000 |
+
按照部门分组,对薪资小于3000的员工计算平均值并且按照部门编号的降序查询
mysql> select deptno, avg ( sal) from emp where sal< 3000 group by deptno order by deptno desc ;
+
| deptno | avg ( sal) |
+
| 30 | 1566.666667 |
| 20 | 1625 |
| 10 | 2450 |
+
3 rows in set
按照部门分组,对薪资小于3000员工,计算平均薪高于1800的部门(注:对分组后的计算平均值不能使用where 需要使用having ) having对分组过后的数据进行过滤可以使多行函数等 where称之为行级过滤,处理的是表中每一行数据的过滤 having称之为组级过滤。处理的是分组之后的每一组数据
mysql> select deptno, avg ( sal) from emp where sal< 3000 group by deptno having avg ( sal) > 1800 ;
+
| deptno | avg ( sal) |
+
| 10 | 2450 |
+
1 row in set
mysql> select deptno, sum ( comm) from emp where comm is not null group by deptno having sum ( comm) > 1000 ;
+
| deptno | sum ( comm) |
+
| 30 | 2200.00 |
+
1 row in set
根据部门分组,查询出部门编号为 20和30的平均薪资,优先使用行级过滤
where称之为行级过滤,处理的是表中每一行数据的过滤 having称之为组级过滤。处理的是分组之后的每一组数据
mysql> select deptno, avg ( sal) from emp where deptno in ( 20 , 30 ) group by deptno;
+
| deptno | avg ( sal) |
+
| 20 | 2175 |
| 30 | 1566.666667 |
+
2 rows in set
mysql> select deptno, avg ( sal) from emp group by deptno having deptno in ( 20 , 30 ) ;
+
| deptno | avg ( sal) |
+
| 20 | 2175 |
| 30 | 1566.666667 |
+
2 rows in set
mysql>
mysql> select deptno, avg ( sal) from emp where ename like '%A%' group by deptno;
+
| deptno | avg ( sal) |
+
| 10 | 2450 |
| 20 | 1100 |
| 30 | 1580 |
+
3 rows in set
查询10,20部门中,并且在十一月份入职的员工,每个部门中平均薪资高于1500的工作是什么,并按照部门,工作平均薪资进行排序。
mysql> select deptno, avg ( sal) from emp where deptno in ( 10 , 20 ) and extract( month from hiredate) = 11 group by deptno, job having avg ( sal) > 1500 ;
+
| deptno | avg ( sal) |
+
| 10 | 5000 |
+
1 row in set
mysql>
总结