索引
- 字符函数
- 1、CONCAT 拼接字符-------concat
- 2、LENGTH 获取字节长度-----length
- 3、CHAR_LENGTH 获取字符个数-----char_length
- 4、SUBSTRING 截取子串-----substring
- 5、INSTR获取字符第一次出现的索引-----instr
- 6、TRIM去前后指定的字符,默认是去空格-----trim
- 7、LPAD/RPAD 左填充/右填充-----lpad/rpad
- 8、UPPER/LOWER 变大写/变小写-----upper/lower
- 9、STRCMP 比较两个字符大小-----strcmp
- 10、LEFT/RIGHT 截取子串-----left/right
- 数学函数
- 日期函数
- 流程控制函数
- 分组函数
字符函数
1、CONCAT 拼接字符-------concat
mysql> select concat('hello','hi')
-> from employees;
+----------------------+
| concat('hello','hi') |
+----------------------+
| hellohi |
| hellohi |
| hellohi |
| hellohi |
2、LENGTH 获取字节长度-----length
mysql> select length('hello秘密');
+-----------------------+
| length('hello秘密') |
+-----------------------+
| 11 |
+-----------------------+
1 row in set (0.00 sec)
3、CHAR_LENGTH 获取字符个数-----char_length
mysql> select char_length('hello,hi');
+-------------------------+
| char_length('hello,hi') |
+-------------------------+
| 8 |
+-------------------------+
1 row in set (0.00 sec)
4、SUBSTRING 截取子串-----substring
起始索引从1开始
substr(str,起始索引,截取的字符长度)
substr(str,起始索引)
mysql> select substr('abcdefg',1,3);
+-----------------------+
| substr('abcdefg',1,3) |
+-----------------------+
| abc |
+-----------------------+
1 row in set (0.00 sec)
mysql> select substr('abcdefg',3);
+---------------------+
| substr('abcdefg',3) |
+---------------------+
| cdefg |
+---------------------+
1 row in set (0.00 sec)
5、INSTR获取字符第一次出现的索引-----instr
mysql> select instr('abcd1234abcd5678',5);
+-----------------------------+
| instr('abcd1234abcd5678',5) |
+-----------------------------+
| 13 |
+-----------------------------+
1 row in set (0.04 sec)
6、TRIM去前后指定的字符,默认是去空格-----trim
mysql> select trim(' 6 6 6 ')as a;
+-------+
| a |
+-------+
| 6 6 6 |
+-------+
1 row in set (0.03 sec)
mysql> select trim('9'from'999 6 6 6 999')as a;
+-----------+
| a |
+-----------+
| 6 6 6 |
+-----------+
1 row in set (0.00 sec)
mysql> select trim('9'from'999 6 6 6 999');
+--------------------------------+
| trim('9'from'999 6 6 6 999') |
+--------------------------------+
| 6 6 6 |
+--------------------------------+
1 row in set (0.00 sec)
7、LPAD/RPAD 左填充/右填充-----lpad/rpad
mysql> select lpad('mc',6,6);
+----------------+
| lpad('mc',6,6) |
+----------------+
| 6666mc |
+----------------+
1 row in set (0.03 sec)
mysql> select lpad('mc',10,6);
+-----------------+
| lpad('mc',10,6) |
+-----------------+
| 66666666mc |
+-----------------+
1 row in set (0.00 sec)
mysql> select lpad('mc',10,'6');
+-------------------+
| lpad('mc',10,'6') |
+-------------------+
| 66666666mc |
+-------------------+
1 row in set (0.00 sec)
mysql> select rpad('mc',10,'6');
+-------------------+
| rpad('mc',10,'6') |
+-------------------+
| mc66666666 |
+-------------------+
1 row in set (0.00 sec)
8、UPPER/LOWER 变大写/变小写-----upper/lower
查询员工表的姓名,要求格式:姓首字符大写,其他字符小写,名所有字符大写,且姓和名之间用_分割,最后起别名“OUTPUT”
mysql> select
-> concat(upper(substr(first_name,1,1)),lower(substr(first_name,2)),'_',upper(last_name)) 'output'
-> from employees;
+-------------------+
| output |
+-------------------+
| Steven_K_ING |
| Neena_KOCHHAR |
| Lex_DE HAAN |
| Alexander_HUNOLD |
| Bruce_ERNST |
| David_AUSTIN |
| Valli_PATABALLA |
| Diana_LORENTZ |
9、STRCMP 比较两个字符大小-----strcmp
mysql> select strcmp('abc','abd');
+---------------------+
| strcmp('abc','abd') |
+---------------------+
| -1 |
+---------------------+
1 row in set (0.03 sec)
10、LEFT/RIGHT 截取子串-----left/right
mysql> select left('abcd',2);
+----------------+
| left('abcd',2) |
+----------------+
| ab |
+----------------+
1 row in set (0.00 sec)
mysql> select right('abcd',2);
+-----------------+
| right('abcd',2) |
+-----------------+
| cd |
+-----------------+
1 row in set (0.00 sec)
数学函数
1、ABS 绝对值-----abs
mysql> select abs(-6.6);
+-----------+
| abs(-6.6) |
+-----------+
| 6.6 |
+-----------+
1 row in set (0.00 sec)
2、CEIL 向上取整 返回>=该参数的最小整数-----ceil
mysql> select ceil(-6.66);
+-------------+
| ceil(-6.66) |
+-------------+
| -6 |
+-------------+
1 row in set (0.00 sec)
mysql> select ceil(0.06);
+------------+
| ceil(0.06) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> select ceil(1.00);
+------------+
| ceil(1.00) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
3、FLOOR 向下取整,返回<=该参数的最大整数-----floor
mysql> select floor(-6.66);
+--------------+
| floor(-6.66) |
+--------------+
| -7 |
+--------------+
1 row in set (0.00 sec)
mysql> select floor(0.06);
+-------------+
| floor(0.06) |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> select floor(1.00);
+-------------+
| floor(1.00) |
+-------------+
| 1 |
+-------------+
4、ROUND 四舍五入-----round
mysql> select round(3.14159);
+----------------+
| round(3.14159) |
+----------------+
| 3 |
+----------------+
1 row in set (0.00 sec)
mysql> select round(3.14159,3);
+------------------+
| round(3.14159,3) |
+------------------+
| 3.142 |
+------------------+
1 row in set (0.00 sec)
5、TRUNCATE 截断-----truncate
mysql> select truncate(3.14159,3);
+---------------------+
| truncate(3.14159,3) |
+---------------------+
| 3.141 |
+---------------------+
1 row in set (0.00 sec)
6、MOD 取余-----mod------(%)
mysql> select mod(-10,6);
+------------+
| mod(-10,6) |
+------------+
| -4 |
+------------+
1 row in set (0.00 sec)
mysql> select -10%6;
+-------+
| -10%6 |
+-------+
| -4 |
+-------+
1 row in set (0.03 sec)
被除数正负决定结果正负
日期函数
格式转换
- 秒 %S、%s 两位数字形式的秒( 00,01, …, 59)
- 分 %I、%i 两位数字形式的分( 00,01, …, 59)
- 小时 %H 24小时制,两位数形式小时(00,01, …,23)
- %h 12小时制,两位数形式小时(00,01, …,12)
- %k 24小时制,数形式小时(0,1, …,23)
- %l 12小时制,数形式小时(0,1, …,12)
- %T 24小时制,时间形式(HH:mm:ss)
- %r 12小时制,时间形式(hh:mm:ss AM 或 PM)
- %p AM上午或PM下午
- 周 %W 一周中每一天的名称(Sunday,Monday, …,Saturday)
- %a 一周中每一天名称的缩写(Sun,Mon, …,Sat)
- %w 以数字形式标识周(0=Sunday,1=Monday, …,6=Saturday)
- %U 数字表示周数,星期天为周中第一天
- %u 数字表示周数,星期一为周中第一天
- 天 %d 两位数字表示月中天数(01,02, …,31)
- %e 数字表示月中天数(1,2, …,31)
- %D 英文后缀表示月中天数(1st,2nd,3rd …)
- %j 以三位数字表示年中天数(001,002, …,366)
- 月 %M 英文月名(January,February, …,December)
- %b 英文缩写月名(Jan,Feb, …,Dec)
- %m 两位数字表示月份(01,02, …,12)
- %c 数字表示月份(1,2, …,12)
- 年 %Y 四位数字表示的年份(2015,2016…)
- %y 两位数字表示的年份(15,16…)
- 文字输出 %文字 直接输出文字内容
1、NOW-----now
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-02-01 18:11:58 |
+---------------------+
1 row in set (0.04 sec)
2、CURDATE-----curdate
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-02-01 |
+------------+
1 row in set (0.04 sec)
3、CURTIME-----curtime
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 18:13:18 |
+-----------+
4、DATEDIFF-----datediff
mysql> select datediff('1998-0-0','2019-1-31');
+----------------------------------+
| datediff('1998-0-0','2019-1-31') |
+----------------------------------+
5、DATE_FORMAT-----date_format
mysql> select date_format('2021-1-1','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期;
+-----------------------------------------+
| 出生日期 |
+-----------------------------------------+
| 2021年01月01日 00小时00分钟00秒 |
+-----------------------------------------+
1 row in set (0.00 sec)
6、STR_TO_DATE 按指定格式解析字符串为日期类型-----str_to_date
mysql> select * from employees where hiredate<STR_TO_DATE('1/5 2020','%m/%d %Y');
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
| 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | AD_VP | 17000.00 | NULL |
流程控制函数
1、IF函数
SELECT IF(1+1>2,‘好’,‘坏’);
若为真,结果为参数一,若为假,为参数二
2、CASE函数
①情况1 :类似于switch语句,可以实现等值判断
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
…
ELSE 结果n
END
mysql> select department_id,salary,
-> case department_id
-> when 30 then salary*2
-> when 50 then salary*5
-> else salary
-> end newsalary
-> from employees;
+---------------+----------+-----------+
| department_id | salary | newsalary |
+---------------+----------+-----------+
| 90 | 24000.00 | 24000.00 |
| 90 | 17000.00 | 17000.00 |
| 90 | 17000.00 | 17000.00 |
| 60 | 9000.00 | 9000.00 |
| 60 | 6000.00 | 6000.00 |
| 60 | 4800.00 | 4800.00 |
| 60 | 4800.00 | 4800.00 |
| 60 | 4200.00 | 4200.00 |
| 100 | 12000.00 | 12000.00 |
| 100 | 9000.00 | 9000.00 |
| 100 | 8200.00 | 8200.00 |
②情况2:类似于多重IF语句,实现区间判断
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
…
ELSE 结果n
END
mysql> select salary,
-> case
-> when salary>20000 then '1'
-> when salary>15000 then '2'
-> when salary>10000 then '3'
-> else '4'
-> end
-> from employees;
+----------+----------------------------------------------------------------------------------------------------+
| salary | case
when salary>20000 then '1'
when salary>15000 then '2'
when salary>10000 then '3'
else '4'
end |
+----------+----------------------------------------------------------------------------------------------------+
| 24000.00 | 1 |
| 17000.00 | 2 |
| 17000.00 | 2 |
| 9000.00 | 4 |
| 6000.00 | 4 |
| 4800.00 | 4 |
| 4800.00 | 4 |
| 4200.00 | 4 |
| 12000.00 | 3 |
| 9000.00 | 4 |
| 8200.00 | 4
分组函数
分组函数往往用于实现将一组数据进行统计计算,最终得到一个值,又称为聚合函数或统计函数
分组函数清单:
sum(字段名):求和
avg(字段名):求平均数
max(字段名):求最大值
min(字段名):求最小值
count(字段名):计算非空字段值的个数
查询员工信息表中,所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
mysql> SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;
+-------------+-------------+-------------+-------------+---------------+
| SUM(salary) | AVG(salary) | MIN(salary) | MAX(salary) | COUNT(salary) |
+-------------+-------------+-------------+-------------+---------------+
| 691400.00 | 6461.682243 | 2100.00 | 24000.00 | 107 |
+-------------+-------------+-------------+-------------+---------------+
添加筛选条件
①查询emp表中记录数:
mysql> select count(employee_id)
-> from employees;
+--------------------+
| count(employee_id) |
+--------------------+
| 107 |
+--------------------+
②查询emp表中有佣金的人数:
mysql> SELECT COUNT(salary) FROM employees;
+---------------+
| COUNT(salary) |
+---------------+
| 107 |
+---------------+
③查询emp表中月薪大于2500的人数:
mysql> SELECT COUNT(salary) FROM employees WHERE salary>2500;
+---------------+
| COUNT(salary) |
+---------------+
| 96 |
+---------------+
④查询有领导的人数:
mysql> SELECT COUNT(manager_id) FROM employees;
+-------------------+
| COUNT(manager_id) |
+-------------------+
| 106 |
+-------------------+
count的补充
1、统计结果集的行数,推荐使用count(*)
mysql> select count(*)
-> from employees;
+----------+
| count(*) |
+----------+
| 107 |
+----------+
mysql> SELECT COUNT(*) FROM employees WHERE department_id = 30;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
2、搭配distinct实现去重的统计
查询有员工的部门个数
mysql> select count(distinct department_id)
-> from employees;
+-------------------------------+
| count(distinct department_id) |
+-------------------------------+
| 11 |
+-------------------------------+