mysql学习笔记（二）

mysql学习笔记（二）

联结表

联结表就是查询的表信息来自多个表，这多个表有外键的关联，使用联结，用一条select语句就可以查出多个表的数据。
如：

内部联结（等值联结）

select vendName,prodName,prodPrice
from vend,prod
where vend.vendId=prod.vendId;#联结条件

笛卡尔积：

select vendName,prodName,prodPrice
from vend,prod
#没有联结条件

自身联结

例如要查找一个产品表，已知一个物品，找出它的商人的所有其他的物品，当然可以用select嵌套，不过还可以用自连接。
如：

select p1.prodName
from product as p1,product as p2
where p1.vendId=p2.vendId and p2.prodId="刘天"；

外部联结(左外联和右外联)

在等值联结中没有保留没有关联的行，外连接就是包括这些，例如列出所有客户和订购数量，此时应该包含还没有人订购的客户。

select customers.custId,count(orders.orderName)
from customersd left outer join orders 
on custumers.custId = orders.custId;

组合查询

插入数据

插入完整行：

insert into Customers
values("Pep","北京","90096",NULL,NULL);

更安全：

insert into Customers(custName,address,custState,custZip,custCountry)
values("Pep","北京","90096",NULL,NULL),("July","北京","90096",NULL,NULL);

插入检索的数据：

insert into Sdeptage(Sdept,Sage)
select Sdept,avg(Sage) from Student group by Sdept;

更新和删除数据

更新

update customers
set custEmail="dd@sohu.com"
where custId="1005";#没有条件更新全部

删除：

delete from costemers
where custId="1003";#没有删除全部行

创建和操纵表

创建表：（create）

create table  orderItem(
ordernum int not null auto_increment,
orderitem int not null,
prodId char(10) unique
quantity int not null,default 1,
primary key(ordernum,orderitem),
foreign key(ordernum) references Prod
);

更新表（alter）

rename table oldname to new name;
drop table mytable;
describe mytable;
show table mytable;

#1. 增删属性
alter table 表名 add 属性名 类型 约束条件；
alter table 表名 drop 属性名；

#2. 增删not null 、default 、auto_increment、类型
alter table 表名 modify 属性名 类型 新的完整约束条件；
或者
alter table 表名 change 属性原名 属性新名字 类型 新的完整约束条件；

#3. 增删unique唯一可以有一个空
##增：
alter table 表名 modify 属性名 类型 新的完整约束条件；
或者
alter table 表名 change 属性原名 属性新名字 类型 新的完整约束条件；
或者
alter table  表名 add constraint 约束ID unique(属性名);
##删除：
alter table 表名 drop unique 约束ID；

#4. 主键约束增：
alter table 表名 modify 属性名 INT类型 PRIMARY KEY;
或者
alter table  表名 change  属性原名 属性新名字 类型 PRIMARY KEY;
或者
alter table 表名 add primary key(属性名);
或者
alter table 表名 add constraint 约束ID primary key(属性名);
#删除主键约束
alter table 表名 drop PRIMARY KEY;

注：主键约束相当于(唯一约束+非空约束)

一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示：

Multiple primary key defined!!!
5. FOREIGN KEY(外键约束,对应的字段只能是主键或者唯一约束修饰的字段)
#增加外键
alter table 表名 add constraint 约束ID primary key(属性名)[on语句];
#on delete cascade 删除主表中的数据时，从表中的数据随之删除
#on update cascade 更新主表中的数据时，从表中的数据随之更新
#on delete SET NULL 删除主表中的数据时，从表中的数据置为空
#删除外键
alter table 表名 drop PRIMARY KEY 约束ID;

视图

视图是一个虚表,不存放视图对应的数据，这些数据仍存放在原来的基本表中。
当基本表中的数据发生变化，从视图中查询出的数据也随之改变了。
视图定义后，
可以在一个视图之上在定义新的视图
对视图的增、删、改操作有一定限制
视图没有索引和触发器

查看创建视图语句：

show create view viewname;
describe viewname;

创建视图：

'''
格式：
create view myview[属性列，属性列……]
as select xxxx
with cascade check option;
'''

Create view S_C              
as select Sno, Cno, Grade      
from SC    
where Cno= '1' and to_char(sysdate, 'HH24') between 9 and 17  
	   and to_char(sysdate, 'D')  between 2 and 6 
with cascade check option;         

删除视图：

drop view viewname [cascade];
#如果使用CASCADE,则把该视图和由它导出的所有视图一起删除
#删除基表时，由该基表导出的所有视图定义都必须显式删除

更新视图：(update insert delete)

create or replace view viewname
as select xxx

用下面方式定义的视图不能更新：

• 分组
• 联结
• 子查询
• 并
• 聚集函数
• distinct
• 计算列

触发器

• 只有表支持触发器
• 每个表一个事件只允许一个触发器，最多六个（insert、update、delete）
• 删除触发器drop trigger mytrigger;

# 例
create trigger mytrigger after insert on products
for each row 
select "成功插入！"
# 效果每次插入一个元组后在控制台显示成功插入

#插入多条执行语句
delemiter $$
begin

create trigger mytrigger after insert on products
for each row 
select "成功插入！";
insert into vendor values (now());

end $$
delemiter ;

可以引用一个名字为new的虚拟表，访问被插入的行；
在before insert触发器，new表可以被更改；
对于auto_increment列，new在insert执行之后包含新的自动生成值。
例：

delemiter $$
begin

create trigger mytrigger after insert on products
for each row 
select new.num;
end $$
delemiter ;

管理用户

新建用户：create user 用户名 identified by “口令”；
重命名：rename user 旧 to 新；
删除用户：drop user name；
设置访问权限：
数据库.表：usage on * . *任何数据库所有表没有权限

• 整个服务器 grant all、revoke all
• 整个数据库on database.*
• 特定的表on datebase.mytable
• 列
• 存储过程

```sql
grant select on Course.* to ben;
revoke select on Course.* from ben;

改密码：
set password for ben =password(“xxx”);
set password = password(“xxx”);