数据库基本知识

创建视图：

create view myviewname(MemberNameEmail) as

select lastname, firstname,email,dateofjoining

from memberdetails;

 

创建试图后可以用一句select语句来使用它

select  * from MemberNameEmail

 

**************************************

事务：原子性  一致性  隔离性 持久性

关键字：commit  表示已经写入磁盘不可回滚

                rollback  回滚，commit之前的内容不可回滚

 

start transaction  开始一个事务  (mysql)   回滚整个事务

begin transaction 。。。commit transaction      (sql server)事务开始结束

 

保留点（占位符） mysql 用savepoint  delete1； rollback to delete1；回滚到delete1

sql server 用save transaction delete1；  rollback transaction delete1；

 

*******************************************

存储过程：

create procedure test_data(rows int)

begin

declare val1 float;

declare val2 float;

repeat

select rand() into val1;

select rand() into val2;

insert into tdata values(null,val1,val2);

set rows=rows-1;

until rows=0

end repeat;

end//

 

call test_data;

select * from tdata //

*****************************

外键：

create table orders(

order_num integer not null primary key,

order_date datetime not null,

cust_id char(10) not null references customers(cust_id)

);

 

alter table customers 

add constraint

forign key (cust_id) references customers (cust_id)

 

********************************************************

触发器：

delimiter  //

create trigger ad_delete_manufacturer_all

after delete on country

for each row

begin

delete from manfacturer where countryid=old.countyid;

end;//

delimiter //

create tigger ad_delete_product_all

after delete on manufacturer

for each row 

begin 

delete from product where manufacturerid=old.manufacturerid;

end;//