数据库的一些知识点

Where 过滤行  having 过滤组
union
作用： 把两次或多次查询结果合并起来
要求：两次查询的列数一致

可以来自于多张表
多次sql语句取出的列名可以不一致，此时已第一个sql列名为准

如果不同的语句中取出的行有完全相同的（每个列的值都相同）
那么相同的行会合并

如果不去重复可以加all  union all

如果子句中有order by limit需加（）推荐放到所有子句之后，对最终合并结果排序

在子句中order by 与limit 一起使用才有意义 order by不配合limit一起使用会被语法优化器去除
select goods.name from goods where price >5000 or price<20;
select goodsname from goods where price>5000 union select goodsname from goods where price<20;

左连接、右连接、

左连接以左表为准，去右表找匹配数据，找不到匹配用null补齐、、

内连接：查询左右标都有的数据，即左右连接的交集

查询左右连接的并集可以用union

增加列
alter table 表名 add 列声明（列名称 列类型 【列属性】【默认值】）
默认放在最后。。
想要放在某个列后面用after
alter table 表名 add 列声明 after 列名
新增列放在最前面
alter table 表名 add 列声明 first

修改列

alter table 表名 change 被改变的列 列声明

删除列
alter table 表名 drop  列名

视图 view 是由查询结果形成的一张虚拟表

视图的创建语法
create view 视图名 as select 语句

视图的删除

drop view 视图名

视图的修改

alter view as select ....

为什么要视图
1：可以简化查询
2：可以进行权限控制 、
把表的权限封闭 但是开放相应的视图权限 视图里只放部分数据
3大数据分表时可以用到

分表 把一张表的数据分散到4张表里
最常用的可以用ID取模

ID%4+1=[1,2,3,4]
还可以用视图 把 4张表形成一张视图
create view biao as select from n1 union select from n2 union select from n3...

视图与表的关系
 视图是表的查询结果 表的数据改变了 影响视图的结果。

视图增删改也会影响表
但是视图并不是总能影响表

视图的数据与表的数据一一对应时可以修改
对于视图insert还应注意 视图必须包含表中没有默认值的列

查看表结构
desc 表名

触发器：trigger

作用：监视某种情况并出发某种操作

触发器 能监视三种操作 增删改 触发操作 ：增删改

触发器四大要素
监视地点（table）、 监视事件insert update delete、触发时间after before、触发事件

创建触发器
create trigger  triggerName
after \before insert \delete\ update on 表名//你要做的动作
for each row//这一句话是固定的

 begin 

sql 语句 //触发的动作

end

删除触发器

drop trigger 触发器的名字

查看所有的触发器
show triggers;

如何在触发器中引用行的值

对于insert 而言 新增的行 用new 来表示
行中的每一列用 new.列名来表示

sql语句一定是用；结束

整个语句结束符可以用delimiter 符号 定义

删除一个订单 库存相应增加

ctreate trigger tg2
after delete on o
for each row
begin
update g set num = num + old.much where id=old.gid
end $

对于delete来说 原本有一行 后来被删除
想引用被删除的这一行用old来表示 old。列名就可以引用被删除行的值
修改一个订单

create trigger tg4
after update on o
for each row
begin
update g set num=num+old.much-new.much where id=old.gid;
end$

触发器里after 和before 的区别

after 是先完成数据的增删改，然后再触发
before 是先完成触发 再增删改
触发的语句先于监视的增删改 的发生 我们有机会判断即将发生的操作

典型案例
订单不能超过5单
create trigger tg6
before insert on o
for each row
 begin
if new.much>5 then
set new.much=5;
update g set num=num-new.much where id=new.gid;
end$

存储引擎
事务的存储引擎分为两种
1，myisam (赵马虎)批量插入速度高、不支持事务、一开始就支持全文索引  他的锁机制是表锁
InnoDB（李小心）批量插入速度低、支持事务安全、5.5版本才开始支持全文索引   他的是行锁

事务

acid 特性
原子性 隔离性 一致性 持久性

事务的语法

开启事务
start transaction
sql语句

提交事务/回滚事务
commit /rollback
当一个事务提交或回滚就说明事务已经结束了

数据库的备份与恢复

目前是系统自带的备份功能，mysqldump

导出库里的表
mysqldump -u用户名 -p密码 数据库名 表名1 表名2 > 地址 备份文件名称
导出一个库里的所有表
mysqldump -u用户名 -p密码 库名 >地址

导出 以库为单位
mysqldump -uroot -p密码 -B 库1 库2> 地址、备份文件

导出所有库
mysqldump -u用户名 -p密码 -A>地址备份文件

恢复

sourse 文件存储地址

 

索引

是针对数据所建立起来的目录

好处加快了查询速度
坏处
降低了增删改的速度
增大了表的文件大小

索引分为

普通索引 仅仅是加快查询速度
主键索引 不能重复
唯一索引 行上的值不能重复
主键必唯一  但是唯一索引不一定是主键
一张表上只能有一个主键、但是可以用一个或多个唯一索引

全文索引 fulltext
match(全文索引名)against （要匹配的词）
不针对非常频繁的词做索引
全文索引 在MySQL 的默认情况下对于中文意义不大
英文单词有空格 标点符号来拆成单词 进而对单词进行索引
而对于中文 没有标点空格来隔开单词 MySQL 无法识别每个中文词

查看一个表上的所有索引
show index from 表名
建立索引
alter table 表名 add index /unique/fulltext 【索引名】（列名）

主键索引
alter table 表名 add primary key(列名）

删除索引

alter table 表名 drop index 索引名

删除主键索引
alter table 表名 drop primary key

存储

查看所有的存储过程
show  procedure status

删除存储过程
drop procedure 存储过程的名字

创建一个存储过程
create procedure p1()
begin
select * from g;
end$

调用存储过程

call 存储过程的名字（）

第二个存储过程 参数
create procedure p2(n int)

begin
select * from g where num>n;
end$

call p2(10)

第三个存储过程 控制结构

create procedure p3(n int,j char(1))

begin
if j='h' then
select * from g where num>n;
else
select * from g where num<n;
end if;
end$