1 #进阶4：常见函数2 /*
3 概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名4 好处：1、隐藏了实现细节 2、提高代码的重用性5 调用：select 函数名(实参列表) 【from 表】;6 特点：7 ①叫什么(函数名)8 ②干什么(函数功能)9 分类：10 1、单行函数11 如concat,length,ifnull等12 分类：字符函数 数学函数 日期函数 其他函数【补充】 流程控制函数【补充】13 2、分组函数14 功能：做统计使用，又称统计函数、聚合函数、组函数15
16 常见函数:17 字符函数：length18 concat19 substr20 instr21 trim22 upper23 lower24 lpad25 rpad26 replace27
28 数学函数：29 round30 ceil31 floor32 truncate33 mod34
35 日期函数：36 now37 curdate38 curtime39 year40 month41 monthname42 day43 hour44 minute45 second46 str_to_date47 date_format48
49 其他函数50 version51 database52 user53
54 控制函数55 if56 case57 */
58
59 #一、字符函数60 #1.length 获取参数值的字节个数61 SELECT LENGTH('john');#列名，常量62 SELECT LENGTH('张三丰hahaha');63
64 SHOW VARIABLES LIKE '%char%';65
66 #2.concat 拼接字符串67 SELECT CONCAT(last_name,'_',first_name) 姓名68 FROMemployees;69
70 #3.upper,lower
71 SELECT UPPER('john');72 SELECT LOWER('joHn');73 #示例：将姓变大写，名小写，然后拼接74 SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名75 FROMemployees;76
77 #4.substr、subtring78 注意：索引从1开始79 #截取从指定索引处后面所有字符80 SELECT SUBSTR('李莫愁爱上了陆展元',7) output;81
82 #截取从指定索引处指定长度的字符83 SELECT SUBSTR('李莫愁爱上了陆展元',1,3) output;84
85 #案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来86 SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) output87 FROMemployees;88
89 #5.instr 返回子串第一次出现的索引，如果找不到返回090 SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') ASout_put;91
92 #6.trim93 SELECT LENGTH(TRIM('张翠山')) ASou_out;94 SELECT TRIM('a' FROM 'aaaaaaa张aaaaa翠山aaaaaaaaaa') ASout_put;95
96 #7.lpad用指定的字符实现左填充指定长度97 SELECT LPAD('殷素素',2,'*') ASout_put;98
99 #8.rpad用指定的字符实现左填充指定长度100 SELECT RPAD('殷素素',12,'*') ASout_put;101
102 #replace替换103 SELECT REPLACE('张无忌爱上周芷若了周芷若周芷若周芷若周芷若','周芷若','赵敏') ASout_put;104
105 #二、数学函数106
107 #round四舍五入108 SELECT ROUND(1.65);109 SELECT ROUND(1.567,2);110
111
112 #ceil 向上取整，返回>=该参数的最小值113 SELECT CEIL(-1.02);114
115 #floor 向下取整，返回<=该参数的最大整数116 SELECT FLOOR(-9.99);117
118 #truncate截断119 SELECT TRUNCATE(1.65,1);120
121 #mod 取余122 /*
123 mod(a,b); a-a/b*b124
125 mod(-10,-3) -10-(-10)/(-3)*(-3)126 */
127 SELECT MOD(10,3);128 SELECT 10%3;129
130 #三、日期函数131
132 #now 返回当前系统日期+时间133 SELECTNOW();134
135 #curdate 返回当前系统日期，不包含时间136 SELECTCURDATE();137
138 #curtimes 返回当前时间，不包含日期139 SELECTCURTIME();140
141 #可以获取指定部分，年、月、日、小时、分钟、秒142 SELECT YEAR(NOW()) 年;143 SELECT YEAR('1998-1-1') 年;144
145 SELECT YEAR(hire_date) 年 FROMemployees;146
147 SELECT MONTH(NOW()) 月;148 SELECTMONTHNAME(NOW()) 月;149
150 #str_to_date将字符通过指定的格式转换成日期151 SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') ASoutput;152
153 #查询入职日期为1992--4-3的员工信息；
154 SELECT * FROM employees WHERE hire_date = '1992-3-4';155
156 SELECT * FROM employees WHERE hire_date = STR_TO_DATE('3-4 1992','%c-%d %Y');157
158 #date_format 将日期转换为字符159 SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');160
161 #查询有奖金员工的名和入职日期(xx月/xx日 xx年)162 SELECT last_name,DATE_FORMAT(hire_date,'%m月/%d日 %y年') 入职日期163 FROMemployees164 WHERE commission_pet IS NOT NULL;165
166 #四、其他函数167
168 SELECTVERSION();169 SELECT DATABASE();170 SELECT USER();171
172 #五、流程控制函数173 #1.if函数： ifelse的效果174 SELECT IF(10>5,'大','小');175
176 SELECT last_name,commission_pet,IF(commission_pet IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注177 FROMemployees;178
179 #2.case函数的使用一:switch case的效果180 /*
181 java中182 switch(变量或者表达式){183 case 常量1:语句1;break;184 ...185 default:语句n;break;186 }187 mysql中188 case 要判断的字段或表达式189 when 常量1 then 要显示的值1或语句1；190 when 常量2 then 要显示的值2或语句2；191 ...192 else 要显示的值n或语句n；193 end194 */
195
196 /*案例：查询员工的工资，要求197 部门编号=30，显示的工资为1.1倍198 部门编号=40，显示的工资为1.2倍199 部门编号=50，显示的工资为1.3倍200 其他部门，显示的工资为原工资201
202 */
203
204 SELECTsalary 原始工资,department_id,205 CASEdepartment_id206 WHEN 30 THEN salary*1.1
207 WHEN 40 THEN salary*1.2
208 WHEN 50 THEN salary*1.3
209 ELSEsalary210 END AS新工资211 FROMemployees;212
213 #3.case函数的使用二，类似于多重if214 /*
215 java中:216 if(条件1){217 语句1;218 }else if(条件2){219 语句2;220 }221 ...222 else{223 语句n;224 }225 mysql中：226 case227 when 条件1 then 要显示的值1或语句1;228 when 条件2 then 要显示的值2或语句2;229 ...230 else 要显示的值n或语句n231 end232 */
233
234 #案例：查询员工的工资的情况235 如果工资>20000,显示A级别236 如果工资>15000,显示B级别237 如果工资>10000,显示c级别238 否则，显示D级别239 SELECTsalary,240 CASE
241 WHEN salary>20000 THENA242 WHEN salary>15000 THENB243 WHEN salary>10000 THENC244 ELSE D AS 工资级别;
