MYSQL表查询及其他操作
聚合函数
聚合: 将分散的聚集到一起.
聚合函数: 对列进行操作,返回的结果是一个单一的值,除了 COUNT 以外，都会忽略空值
COUNT：统计指定列不为NULL的记录行数；
SUM：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
MAX：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
AVG：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
select 聚合函数 from 表名；
例如：
select avg(age),min(age),max(age),sum(age),count(age)from person #统计人员的平均年龄，最小年龄，最大年龄，总年龄，不为空的行数
分组
分组的含义: 将一些具有相同特征的数据 进行归类.比如:性别,部门,岗位等等
怎么区分什么时候需要分组呢?
特征: 遇到 "每" 字,一般需要进行分组操作.
例如: 1. 公司每个部门有多少人.
2. 公司中有 多少男员工 和 多少女员工.
#分组查询格式: select 被分组的字段 from 表名 group by 分组字段 [having 条件字段] ps: 分组查询可以与 聚合函数 组合使用. #查询班级的平均年龄 select avg(age),name from tb GROUP BY class; #查询每个部门的平均薪资 并且看看这个部门的员工都有谁? select avg(salary),dept,GROUP_CONCAT(name) from tb1 GROUP BY dept; #GROUP_CONCAT(expr):按照分组，将expr字符串按逗号分隔，组合起来 #查询平均薪资大于10000的部门, 并且看看这个部门的员工都有谁? select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept; having avg(salary)>10000; #这里的having后面跟条件与where相似，但也有不同
where 与 having区别:
#执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
分页查询
关键字 limit
limit (起始条数),(查询多少条数)，如果limit后只有一个数字，则代表 0，（查询多少条数）
#查询前5条数据 select * from person limit 3; #查询第3条到第9条数据 select * from person limit 3,6;
SQL 语句关键字的执行顺序
1、查询中常用到的关键词有：
SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY
其中，SELECT与FROM是必需的，其他关键词则是可选的。
标准的 SQL 的解析顺序为: FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY
(1).FROM 字段语句, 组装来自不同数据源的数据
(2).WHERE 表名, 基于指定的条件对记录进行筛选
(3).GROUP BY 分组字段列表, 将数据划分为多个分组
(4).使用聚合函数进行计算
(5).使用 HAVING 子句筛选分组
(6).计算所有的表达式
(7).使用 ORDER BY 对结果集进行排序 ASC（升序，默认）、DESC（降序）
例如：
在学生成绩表中，把班级为1班的学生成绩按照学生姓名分组，并且筛选分组结果，汇总计算各个学生的总成绩，选出总成绩大于600分的学生列表，并按照总分从高到低排列。
Python代码
select sum(score)as s,name from tb1
where class_id=1 group by name having s>600
order by desc
在上面的示例中SQL语句的执行顺序如下：
①首先执行FROM子句，从tb1表检索记录；
②执行WHERE子句，筛选出tb1表中class_id=1的所有记录；
③执行GROUP BY子句，把tb1表按name列进行分组；
④计算SUM()聚集函数，求出每个name的score之和；
⑤执行HAVING子句，筛选出sum(score)大于600的分组；
⑥提取name，sum(score)两个字段，产生新的结果集；
⑦执行ORDER BY子句，把⑥的结果集按sum(score)字段降序排列。
多表联合查询
多表连接查询 select 字段1,字段2... from 表1,表2... [where 条件] select * from tb1 ,tb2 where tb1.id = tb2.did; #通过在tb2中添加与tb1中id相关联的字段did来实现两表互联 内查询和外连接查询 SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段; #这里on和where的作用是一样的后接条件语句 内连接查询 select *from tb1 inner join tb2 on tb1.id=tb2.did #内连接查询与多表联合查询的效果是一样的. 左外连接查询 (左边表中的数据优先全部显示) select *from tb1 left join tb2 on tb1.id=tb2.did 右外连接查询 (右边表中的数据优先全部显示) select *from tb1 right join tb2 on tb1.id=tb2.did 复杂多表语句查询模型 select *from tb1 ,tb2 where tb1.id=tb2.did and 条件 group by 分组条件 having 条件 order by asc(desc)
子语句查询
子查询(嵌套查询): 查多次, 多个select
注意: 第一次的查询结果可以作为第二次的查询的 条件 或者 表名 使用.
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字. 还可以包含比较运算符：= 、 !=、> 、<等.
select * from (select * from person) as 表名; 注意的是: 一条语句中可以有多个这样的子查询,在执行时,最里层括号(sql语句) 具有优先执行权 as后表名不加''
例如：select name,salary from person where salary=(select max(salary) from person);
临时表查询
什么是临时表呢?其实就是一张虚拟的表，当关闭查询的时候自动消失，我们可以把一个表的查询结果作为另个表的字段去使用
判断查询
if --只适用于两种结果的查询
select p1.*, IF( a >b,'正确','错误') as '结果' from 表名; #ps: 语法: IF(条件表达式,"结果为true",'结果为false')
case --可以用于有多种结果的查询
#语法一: SELECT CASE WHEN STATE = '1' THEN '成功' WHEN STATE = '2' THEN '失败' ELSE '其他' END FROM 表; #语法二: SELECT CASE age WHEN 23 THEN '23岁' WHEN 27 THEN '27岁' WHEN 30 THEN '30岁' ELSE '其他岁' END FROM person;
外键约束
什么是外键约束呢？
就是表与表之间的某种约定的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强。即从表收到主表的约束。
外键的创建和删除
1.创建表时,同时创建外键约束
CONSTRAINT 外键名 FOREIGN KEY 从表.从表字段名 REFERENCES 主表.主表字段名 -- 添加外键约束
CREATE 表名( id int not null auto_increment PRIMARY KEY,
CONSTRAINT fk_id FOREIGN KEY tb1.id REFERENCES tb2.did）
2.已经创建表后,追加外键约束
ALTER table person add constraint 外键名 FOREIGN 从表（从表字段名）REFERENCES 主表（主表字段名） ;
3.删除外键约束
ALTER TABLE person drop FOREIGN key 外键名;
注：插入数据时，先插入主表中的数据，再插入从表中的数据。
删除数据时，先删除从表中的数据，再删除主表中的数据。
视图
视图:是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。 目的：将常用的查询结果赋值存放在数据库，方便之后使用。
视图有如下特点;
1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
2. 视图是由基本表（实表）产生的表（虚表）。
3. 视图的建立和删除不影响基本表。
4. 对视图内容的更新（添加、删除和修改）直接影响基本表。
5. 当视图来自多个基本表时，不允许添加,修改和删除数据。
1.创建视图
create view 视图名称 as sql 查询语句
2.使用视图
select * from 视图名称;
3.更新视图
alter view 视图名称 AS SQL语句
4. 删除视图
drop view 视图名称;
触发器-trigger
触发器：监视某种情况，并触发某种操作。
触发器创建语法四要素：1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
create trigger 触发器名字 after/before insert/update/delete on 表名 for each row begin #after/before: 只能选一个 ,after 表示 后置触发, before 表示前置触发 #需要执行的sql语句 #insert/update/delete 也是只能选一个 end
存储过程
存储过程：类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等。
那么为什么要用存储过程而不用函数呢？
存储过程就是当某个SQL语句重复使用，一般SQL语句每执行一次就编译一次,所以使用存储过程只需要编译一次，之后直接调用可提高数据库执行速度
创建存储过程
create procedure 存储过程名 (IN/OUT/INOUT 参数名 数据类型);
begin
select * from account;
end
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回
显示现有的存储过程
show procrdures status
删除存储过程
drop procedure 存储过程名
调用 存储过程
call 存储过程名称()
例如:
create procedure p2(in i int,out n varchar(50)) begin select a into n from tb1 where id = i; # into 关键字 可以 将前面字段的查询结果 执行 给 into 后面的变量. end set @a =null; #设置变量 CALL p2(1,@a); # 调用 select @a;
函数
创建函数
CREATE FUNCTION 函数名(参数1 数据类型,参数2数据类型)
RETURNS INT //设置返回类型
BEGIN DECLARE sum int default 0;
set sum = i1+i2;
RETURN(sum); //返回结果
end
调用自定义函数
#直接调用自定义函数 select 函数名(参数,参数); #在sql语句中使用自定义函数 select 函数名(参数1,参数2),name from 表名
删除自定义函数
DROP FUNCTION 函数名;
函数和存储过程的不同
事物
事务用来管理 insert,update,delete 语句的执行
start transaction/BEGIN; -- 开启事物
set autocommit = 0; --挂起自动提交
set autocommit = 1; --恢复自动提交
update/insert/delete 语句 -- 执行sql语句操作
SAVEPOINT s1; -- 设置保存点update/insert/delete 语句 -- 执行sql语句操作
commit; -- 手动提交事物，提交后不可回滚，永久性的修改
rollback to s1; -- 回滚事物到s1
