外键
何为外键? 外键是指引用另一个表中的一列或者多列,被引用的列要么是主键约束要么是唯一约束. 外键用于建立和加强两张表数据之间的连接. 为表添加外键约束： alter table 表 add constraint 外键名 foreign key(本表外键id字段) references 外表(主键id字段); # 示例：alter table student add constraint wid foreign key(bid) reference A(aid); # 为表删除外键约束： alter table 表 drop foreign key 外键名称; # 示例: alter table student foreign key sid_gid; #
多表连接
交叉连接(cross join)： 返回的结果是被连接的表中的所有数据行的笛卡尔积,即行数的乘积. yf: select * from 表1 cross join 表2; #一般不用,会显示所有数据 内连接(inner join)： yf: select 所查字段 from 表1 inner join 表2 on 表1.关系字段=表2.关系字段. 外连接(left|right join)： yf: select 所查字段 from 表1 left|right [outer] join 表2 on 表1.关系字段=表2.关系字段. 左连接：左连接的结果包括left join子句中指定的左表的所有记录,和所有满足条件的记录.如果左表的某条记录在右表中不存在,则在右表中显示为空. 右连接：右连接与左连接正好相反,返回右表中所有指定的记录和所有满足连接条件的记录.如果右表的某条记录在左表中没有匹配,则左表将返回空值. #左右连接相当于全部显示已该连接边的数据为主的数据,另一半没有该边匹配的数据时就填充NULL值显示# 自连接：自己连接自己; select a.*,b.* from 表 a jion 表 b on a.id=b.id; 子查询： 释义:子查询是指一个查询语句嵌套在另一个查询语句内部的查询.在执行查询语句时,首先会执行子查询中的语句,然后将返回的结果作为外层查询的过滤条件. 一个select语句中包含另一个完整的select语句. 子查询出现的位置： 1.where后,作为条为被查询的一条件的一部分. 2.from后,作表. 当子查询出现在where后作为条件时,还可以说使用如下关键字:any,all 带in:包含其中的查询结果 带exists:后面接子查询,返回行,exists为True,外层查询继续,子查询返回Flase,exists为False,外层查询停止. 带any:满足其中一个条件,就返回结果 带all:要满足其中的所有比较条件 带运算符：<,>,>=,<=,!=,<>等等.
事务
何为事务？ 所谓的事务就是针对数据库的一组操作,它可以由一条或多条SQL语句组成,同一个事务的操作具备同步的特点,即事务中的语句要么都执行,要么都不执行. 事务的使用流程： 1.开启事务 start transaction 2.执行Sql语句 sql语句 3.提交事务 commit; 4.取选事务 rollback; #回滚# 事务的定义特性： 持久性：事务一旦提交,其所做的修改就会永久保存到数据库中,即使数据库发生故障也不应该对其有任何影响.事务的持久性不能做到100%的持久,只能从事务本身的角度来保证永久性,而一些外部原因导致数据库发生故障;如硬盘损坏,那么所有提交的数据可能 都会丢失. 原子性：原子性是指一个事务必须被视为一个不可分割的最小工作单元,只有事务中所有的数据库操作都执行成功,才算整个事务执行成功 一致性：一致性是指事务将数据库从一种状态转变为下一种一致的状态. 隔离性：隔离性还可以称为并发控制、可串行化、锁等,当多个用户并发访问数据库时,数据库为每-一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离.
视图
何为视图？ 视图是一个虚拟表(非真实存在),其本质是根据SQL语句获取动态的数据集并为其命名,用户使用时只需使用名称即可获取结果集,并可以将其当作表来使用. 视图的优点： 简化查询语句:在日常开发中我们可以将经常使用的查询定义为视图，从而使用户避免大量重复的操作. 安全性：通过视图用户只能查询和修改他们所能见到的数据，数据库中的其他数据看不到也取不到. 逻辑数据独立性：视图可以帮助用户屏蔽真实表结构变化带来的影响. yf:create [or replace] [algorithm = {undefined | merge |temptable}] view view_name [(column_list)] as select_statement [with [cascaded | local] check option] /** create: 创建视图的关键字; or replace: 表示该语句能够替换已有的视图; algorithm: 可选,表示视图选择的算法; undefined: 表示mysql将自动选择所要使用的算法; temptable: 表示将视图的结果存入临时表,然后使用临时表执行语句; view_name: 视图名称; column_list: 可选,表示属性清单; select_statement: 表示从某个表或视图中查找到满足条件的记录，并导入视图中; with check option: 可选，表示创建视图时要保证在该视图的权限范围之内; cascaded: 需要满足跟该视图有关的所有相关视图和表的文件,该参数为默认值; local: 可选,在创建视图时,只要满足视图本身定义的条件即可. **/ 简单的视图创建与查看 create view view_表 as select 字段1,2,3... from 表; select * from view_表; create view view_表(str1,str2) as select strA,strB from 表 #strA,strB在视图中更改为str1,str2; /* desc 视图名; show table status like '视图名'; #查看基本信息. show create view 视图名; 视图为虚拟表，无法对真实表进行操作，仅能作查询用. */ 视图的修改: yf1: create [or replace][algorithm ={undefined | merge | temptable}] view view_name [(column_list)] as select_statement [with [cascaded | local] check option] #使用create or replace view语句修改,例:create or replace view view_name as select * from table; yf2: alter [algorithm = (undefined | merge |temptable)] view view_name [(column_list)] as select_statement [with [cascaded | local] check option] #使用alter语句修改,例:alter view view_name as select * from table; 视图的更新: 尽管更新视图有多种方式，但是并非所有情况下都能执行视图的更新操作。 当视图中包含有如下内容时，视图的更新操作将不能被执行: 1.视图中不包含基本表中被定义为非空的列。. 2.在定义视图的SELECT语句后的字段列表中使用了数学表达式。 3.在定义视图的SEL ECT语句后的字段列表中使用聚合函数。 4.在定义视图的SELECT语句中使用了DISTINCT, UNION，TOP，GROUP BY或HAVING子句。 update:例:update view view_name set 视图字段=值 [where]; delete:例:delete from view_name where 视图字段=值; insert:真实表添加了数据，视图也会相应更新; 视图的删除: yf: drop view [if exists] view_name [,view_name2] [restrict | cascaded]
例: drop view if exists view_name;
百分比计算，例如：及格率，优秀率 select cid,convert((sum(case when score>=60 then 1 else 0 end)*1.00/count(*))*100,decimal(5,2)) 及格率 from sc group by cid; 排名计算，相同分数同名次 select cid,score,case when @pr=score then @cur when @pr:=score then @cur:=@cur+1 end 排名 from sc ,(select @cur:=0,@pr:=NULL) z order by cid asc,score desc;