mysql 函数

统计函数

1. count 行统计函数

   count 返回行的总数

   select count(*)|count(列名) from 表名 where where_definition

   count(*) 和 count(列) 的区别:

   count(*) 返回满足条件的记录的行数, count(列) 统计满足条件的某列有多少个，但是会排除为 null 的情况

2. sum 列合计函数

   sum 函数返回满足 where 条件的列的和， 一般使用在能计算的数值列

   select sum(列名), [,...sum(列名)] from 表名 where where_definition

3. avg 列合计函数

   avg 函数返回满足 where 条件的一列平均值

   select avg(列名) [,...avg(列名)] from 表名 where where_definition

4. max, min 合计函数 最大值，最小值

   max / min 函数返回满足 where 条件的一列的 最大/最小值

   select max(列名) from 表名 where where_definition
   select (列名) from 表名 where where_definition

字符串函数

函数名	函数描述
charset (str)	返回字符串的字符集
concat (string1 [, ...string2])	连接字符串
instr(string, substring)	返回 substring在string 中出现的位置，没有返回0
ucase(string)	转成大写
lcase(string)	转成小写
left(string, length)	从 string中的左边起，取 length 个字符
length(string)	返回 string 长度 (按照字节，一个中文==3字节)
replace(str, search_str, replace_str)	在 str 中用 replace_str 替换 search_str
strcmp(string1, string2)	逐字符比较两个字符串大小
substring(str, positon, [, length])	从 str的position 开始（从1开始计算）, 取 length 个字符，length默认不写取到结尾
ltrim(string)	去除左边空格
rtrim(string)	去除右边空格
trim(string)	去除左右两端空格

数学函数

函数名	函数描述
abs	绝对值
bin(number)	十进制转二进制
hex(number)	转十六进制
conv(number,'进制A', '进制B')	进制转换, 进制A 的number 转成 进制B
ceiling(number)	向上取整
floor(number)	向下取整
format(number, '小数位数')	保留小数位数
least(num, num1 [, ...num2])	求最小值
greatest(num, num1 [, ...num2])	求最大值
mod(num, num1)	求余; num: 被除数， num1: 除数， 得到的值为 余数
rand([send])	生成随机数， 生成范围 0<= v <=1, send 如果传入生成的为定值

时间日期

`date_add` 参数介绍： (参数中只有 `date 后边存在逗号`)
• date: 操作的日期
• interval: 关键字
• d_value: 日期的值 (可正可负)
• d_type: 日期的类型 (year, month, week, day,  hour, minute, secoud)

加密函数和系统函数

# 注意点：
1. select * from mysql.user  -- 作用：在数据库A下，查询数据库名为 mysql下的user表；这样就不用切换数据库查表
2. md5 加密后的字符串长度为 32

流程控制函数

# 注意点
select ename, if(comm is null, 0.0, comm)  from emp;  // 在控制语句中判断是否为空, 使用 is 
SELECT ename,(SELECT CASE WHEN job='董事长' THEN 'boss' ELSE job END) AS 'job' FROM emp; // 赋值使用 `=`

select 增强语句

where 语句

1. 日期可以直接比较 SELECT * FROM emp WHERE hiredate > '1992-01-01';

2. like 操作符：

   • %： 表示0到多个字符

   • _: 表示单个字符

order by 语句

1. 排序可以多重连接排序 SELECT * FROM emp ORDER BY deptno ASC, sal DESC;

limit 分页查询

1. limit 基本语法： select ... limit startRows, endRows

   • startRows： 开始行，从该行还开始取，从0开始

   • endRows： 结束行，到该行结束

2. 推导分页公式

   • select * from emp order by empno limit 每页显示记录数 * (第几页-1)，每页显示记录数

数据分组总结

如果 select 语句中分组函数的排序一定要按照如下执行，否则会报错

`group by -> having -> order by -> limit`

mysql 多表查询

多表查询之笛卡尔集

多表查询的条件不能少于表的个数 -1 ， 否则会出现笛卡尔集

自连接

自连接是指在同一张表的连接查询 (将同一张表看作两张表)

自连接特点：

• 把同一张表当作两张表使用

• 需要给表取别名, 表名 新表名；(不使用as)

• 列名不明确，也可以给列起别名, 通过 as

SELECT worker.ename, boss.ename 
	FROM emp worker, emp boss 
	WHERE worker.mar=boss.empno

子查询

子查询是指嵌入在其它 sql 语句中的 select 语句，也叫嵌套查询

1. 单行子查询

   • 单行子查询是指只返回一行数据的子查询语句

2. 多行子查询

   • 多行子查询指返回多行数据的子查询， 使用关键字 in 在指定范围内

3. 临时表

   • 在一张表中查询结果，from 后边可接 sql语句，当然也可接入 sql 语句空格转成表,使用表中的字段

4. 多行子查询中使用 all | any 操作符

   • all 指满足所有的条件

     • any 指满足其中一项的条件

5. 多列子查询

   • 上边四种全是单列的查询，多列查询指的是 where 条件后边跟随多个列字段，这样匹配也就需要增加相应的列数

-- mysql 表子查询
-- 1.单行子查询:
--	如何显示与 张三 同一部门的所有员工
-- 2.多行子查询:
--	如何查询和部门号为20的工作，这些工作下的员工（查询他们的名字，岗位，工资，部门号），但是这些员工部门号不能为20号
-- 3.一张表下，临时表的使用
-- 查询 ecshop 数据库 中各个类别中，价格最高的商品 （使用一张临时表处理）
-- 展示 ecshop -> ecs_goods 表下的 goods_id, cat_id(商品类型id), goods_name, shop_price 字段

-- 问题1
-- 张三的部门编码
SELECT deptno FROM emp WHERE ename='张三' 
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='张三' )

-- 问题2
-- 查询部门号为20号有哪些工作
SELECT DISTINCT job FROM emp WHERE deptno=20
SELECT ename, job, sal, deptno 
	FROM emp 
	WHERE job IN (SELECT job FROM emp WHERE deptno=20) 
	AND deptno != 20
	
-- 问题3
-- 通过 cat_id 分组出价格最高的数据
SELECT cat_id, MAX(shop_price) FROM ecs_goods GROUP BY cat_id;
SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price 
	FROM (
		SELECT cat_id, MAX(shop_price) AS max_price
		FROM ecs_goods GROUP BY cat_id
	) tmp, ecs_goods 
		WHERE tmp.cat_id=ecs_goods.cat_id 
		AND tmp.max_price=ecs_goods.shop_price;
		
-- 问题4
-- 多行子查询中使用 `all | any` 操作符
-- 1.显示工资 比部门30号的所有员工工资高的员工姓名，工资和部门号
-- 2.显示工资 比部门30号的其中一个员工工资高的员工姓名，工资和部门号
SELECT ename, sal, deptno FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30) ORDER BY sal;
SELECT ename, sal, deptno FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=30) ORDER BY sal;

-- 问题5
-- 1.如何查询与 二五仔 的部门和岗位完全相同的所有员工（并且不含 二五仔本人）
SELECT * FROM emp 
	WHERE (deptno, job)=(SELECT deptno, job FROM emp WHERE ename='二五仔')
	AND ename !='二五仔'

表复制

自我复制数据 (蠕虫复制)

-- 将 表名2 的全部结构字段复制到 表名1中
insert into 表名1 select * from 表名2
-- 将 表名2 的部分字段复制到 表名1中
insert into 表名1 (id, name, sal, job) select 表名2.id,name,sal,job from 表名2

mysql 合并查询

-- 通过 union || union all 关键字将表进行合并
-- union 和 union all 区别： union会进行去重处理
SELECT ename,sal,job FROM emp WHERE sal>10000
UNION ALL
SELECT ename,sal,job FROM emp WHERE job LIKE '%总监'

mysql 表 外连接

1. 左外连接

   • 如果左侧的表完全显示，我们就说是左外连接

     select [列名, ...列名1] from 表1 left join 表2 on ...[匹配条件]

2. 右外连接

   • 如果右侧的表完全显示，我们就说是右外连接

     select [列名, ...列名1] from 表1 right join 表2 on ...[匹配条件]

-- 列出部门名称和这些部门的员工信息(名字和工作)
-- 同时列出那些没有员工的部门名
-- 分别使用 左|右外连接 实现

-- 左外连接
SELECT emp.deptno, dept.dname, ename, job FROM dept LEFT JOIN emp 
	ON emp.deptno=dept.deptno
	ORDER BY emp.deptno 
	
-- 右外连接	
SELECT emp.deptno, dept.dname, ename, job FROM emp RIGHT JOIN  dept 
	ON emp.deptno=dept.deptno
	ORDER BY emp.deptno 

mysql 约束