数据库基本语句大全
主外键
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
先删除子表 再删除 主表