数据库基本语句大全

主外键

create table class

(

     cid integer primary key autoincrement,

     cname  varchar(20)

)

create table student

(

    stuid integer primary key autoincrement,

    stuname varchar(20),

    stupwd varchar(20),

    stuaddr varchar(200),

    cid  int ,

   foreign key (cid) references class(cid)

)

增加

insert into student(stuname,stupwd,stuaddr,cid)values('zhangsan','123','aaa',1)

联合查询

select * from class,student  where class.cid=student.cid

左外连接

select s.stuid,s.stuname,s.stupwd,s.stuaddr,c.cid

 from class  c,student  s  where c.cid=s.cid

select s.stuid,s.stuname,s.stupwd,s.stuaddr,c.cid

 from class  c  left  join    student  s  on c.cid=s.cid

 

交叉连接

select s.stuid,s.stuname,s.stupwd,s.stuaddr,c.cid

 from student s  cross  join    class  c

 

创建视图

create view  v_class_student

as

select s.stuid,s.stuname,s.stupwd,s.stuaddr,c.cid

 from student s  left  join    class  c  on  s.cid=c.cid

 

查询视图

select * from   v_class_student where stuid=1

登录查询语句

select count( *) from student  where stuname='zhangsan' and stupwd='123456'

select stupwd from student where stuid=1 and stuname='zhangsan'

三表联合查询

select   stu.stuname,c.cname,s.sname  from school   s  left join class   c   on   s.sid=c.sid   left join student  stu  on stu.cid=c.cid

select * from student stu,class c,school s

 where  stu.cid=c.cid  and s.sid=c.sid

 

select stu.stuname,c.cname,s.sname from student stu,class c,school s

 where  stu.cid=c.cid  and s.sid=c.sid

 

select stu.stuname,c.cname,s.sname from student stu left join   class c

 on stu.cid=c.cid   left join school s  on s.sid=c.sid

select * from student where cid  in

 (select cid from class  where cname='php')

 

select * from student where cid  in

 (select cid from class  where sid  in

(select sid from school where sname='myschool'))

 

update  student set stuname='qq' ,stuaddr='bj'

,stupwd='123456789' where stuid=1

删除数据表

drop table student

 

先删除子表  再删除 主表