数据库的简单操作

学了一点点数据库额知识，贴出来自己用户忘记了复习看

create table student(
  sno     CHAR(20)   primary key,
  sname   CHAR(20)  ,
  sex     char(20)
);

insert into student (sno,sname,sex)
values ('201313137121','kesihai','man');

create VIEW is_man
AS
select sno,sex
from student
where sex='man';

create index is_sname on student(sname);
create index is_sex on student(sex);

show index from student;
drop index is_sname on student;


create table tab1(
   id CHAR(20) primary key
);

create table tab2(
   id CHAR(20) primary key
);

//建立触发器

delimiter //
create trigger is_trigger
after insert on tab1
for each row
begin
insert into tab2 values(new.id);
end//

create trigger is_trigger
after insert on tab1
for each row
insert into tab2 values(new.id);


delimiter //
create trigger iss_trigger
after update on student
for each row
begin
  call math(3,4);这是错的，trigger不能返回set
end//

drop procedure if exists math;

delimiter //
create procedure math(
 in a int,
 in b int
)
begin
 set @var1=1;
 set @var2=2;
 select @le:="helle" as le, @ri:="kesihai" as ri;  
end//

set @var=1;//设置用户变量

create view is_view(sname,age)
as 
select student.sname,S.age
from S,student;

//左连接
select student.sno, student.sname, S.age 
from student left join S 
on student.sname=S.sname;

create user 'wll'@'localhost' identified by '123456'