MySQL知识总结2

一.常见函数

1.概念

类似于Java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

好处：
①隐藏了实现细节
②提高代码的重用性

2.调用

SELECT 函数名（实参列表） [from 表];

特点：
①叫什么（函数名）
②干什么（函数功能）

分类
1.单行函数
如 concat，length,ifnull等
2.分组函数
功能：做统计使用，又称为统计函数，聚合函数，组函数

3.字符函数

1.length:获取参数值的字节个数
SELECT LENGTH(‘john’);

2.concat 拼接字符串
SELECT CONCAT（last_name,‘_’,first_name) 姓名 FROM employees;

3.upper,lower 变大写，变小写
SELECT UPPER（’john’);

4.substr,substring
注意：索引从1开始

截取从指定索引处后面所有字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) out_put;

截取从指定索引处指定字符长度的字符
SUBSTR(‘李莫愁爱上了陆展元’,1，3) out_put;

5.instr
返回字串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠‘,‘殷六侠’) AS out_put FROM employees;

6.trim
去除前后空格
SELECT TRIM(’ 张翠山 ’） AS out_put;
SELECT TRIM(‘a’ FROM ‘aaaaaaaaaaaaaaaaaaaaaaa张aaaaaaaaaa翠山aaaaaaaaaaaaaaa’) AS out_put;

7.lpad
用指定的字符实现左填充指定长度
SELECT LPAD(‘殷素素‘，10，’*') AS out_put;

8.Rpad
用指定的字符实现左填充指定长度
SELECT RPAD('殷素素‘，12，‘ab’) AS out_put;

9.REPLACE 替换
SELECT REPLACE（’张无忌爱上了周芷若’,‘周芷若’,‘赵敏’) AS out_put;

4.数学函数

1.round 四舍五入
SELECT ROUND(1.16);
SELECT ROUND(1.567，2);

2.ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL（1.52）;

3.floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR（-9.99);

4.truncate 截断
SELECT TRUNCATE(1.69999，1);

5.mod 取余
SELECT MOD（10，3）;

5.日期函数

1.now
返回当前系统日期+时间
SELECT NOW（);

2.curdate
返回当前系统日期，不包含时间
SELECT CURDATE();

3.curtime
返回当前时间，不包含日期
SELECT CURTIME();

4.可以获取指定的部分，年，月，日，小时，分钟，秒
SELECT YEAR（NOW（)) 年;
SELECT MONTH（NOW（)) 月;

5.str_to_date
将字符通过指定的格式转换成日期
SELECT STR_TO_DATE（‘1998-3-2’,‘%Y-%c-%d’) AS out_put;

6.date_format
将日期转换成字符
SELECT DATE_FORMAT（NOW(）,'%y年%m月%d月‘） AS out_put;

6.其它函数

1.SELECT VERSION();
2.SELECT DATABASE();
3.SELECT USER();

7.流程控制函数

1.if函数
if else 的效果
SELECT IF(10>5,‘大’,‘小’);

SELECT last_name,commission_pct,IF(commission_pct IS NULL,‘没奖金，呵呵‘，’有奖金，嘻嘻’) 备注 FROM employees;

2.case函数
①switch case 的效果
Java中：
switch(变量或表达式）
{
case 常量一：
语句一；
break;
…
default:
语句;
break;
}
mysql中：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n；
end

②类似于 多重if
case
when 条件1then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n
end

8.分组函数

1.功能
用作统计使用，又称为聚合函数或统计函数或组函数

2.分类
sum，avg,max,min,count

3.简单的使用
①SELECT SUM（salary) FROM employees;
②SELECT AVG（salary) FROM employees;
③SELECT SUM（salary),AVG(salary) FROM employees;

4.特点
参数类型支持哪些类型
SELECT SUM(last_name),AVG(last_name) FROM employees;不支持

SELECT MAX(last_name),MIN(last_name) FROM employees;支持

SELECT COUNT(commission_pct) FROM employees;
计算数值不为空的个数

1.sum,avg一般用于处理数值型
2.max,min，count可以处理任何类型
3.以上分组函数都忽略null值
4.可以和distinct搭配实现去重的运算

5.count函数的单独介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT( * ) FROM employees;
一般使用COUNT(*) 统计行数

6.和分组函数一同查询的字段有限制
要求是group by 后的字段

---

二.分组查询

1.语法

select 分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】

注意：
查询列表必须特殊，要求是分组函数和group by后出现的字段

2.特点

1.
①分组函数做条件放在having子句中
②能用分组前筛选的优先使用分组前筛选

group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求，表达式或函数（用的较少））

也可以添加排序（排序放在整个分组排序的最后）

3.按表达式或函数分组

4.按多个字段分组

---

三.连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行 ，表2有n行，结果=m*n行

分类：
按年代分类：
sql92标准：仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

 按功能分类：
		内连接：
					等值连接
					非等值连接
					自连接
		  外连接：
		  			左外连接
		  			右外连接
		  			全外连接
		  	交叉连接

1.sql92标准

①等值连接

②为表起别名
好处：提高语句的简洁度
区别多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

③可以起别名

④可以分组

⑤可以排序

⑥可以实现三表连接

①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序，分组，筛选

⑦非等值连接

⑧自连接

2.sql99语法介绍

①语法
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：
内连接：inner
外连接：
左外：left 【outer】
右外：right 【outer】
全外：full【outer】
交叉连接：cross

②内连接

特点：
①添加排序，分组，筛选
②Inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

③自连接

④外连接
应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1.外连接的查询结果为主表中的所有记录
如果从表中没有和它匹配的，则显示NULL
外连接查询结果=内连接结果+主表中有而从表没有的记录
2.左外连接，left join左边的是主表
右外连接，right join右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果
4.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

⑤交叉连接

3.sql92和sql99pk

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高