PHP重点知识数据库SQL语句学习.docx

《PHP重点------数据库知识》--创建数据库 schoolcreate database school--删除数据库drop database schoolgo--可以派出先后执行顺序--更改当前操纵的数据库use schoolgo--创建教师表--主表create table teacher(class_namevarchar(20)primary key,tea_namevarchar(20)not null,t_timedatetimedefault(getdate()))go--创建学生表--从表create table student(stu_idintidentity(1,1)primary key,stu_namevarchar(20)not null,sexchar(2)check(sex=男 or sex=女),scoreintdefault(0),class_namevarchar(20)foreign key references teacher(class_name)--class_name字段必须是teacher表的主键)go--添加语句insertinsert into teacher(class_name,tea_name)values(s123,张老师)insert into teacher values(s456,郭老师,2008-1-2 15:51:30)insert into teacher(class_name,tea_name)values(t123,李老师)insert into teacher(class_name,tea_name)values(t456,赵老师)goinsert into student(stu_name,sex,score,class_name)values(李刚,男,68,s123)insert into student(stu_name,sex,score,class_name)values(李小明,男,80,t123)insert into student(stu_name,sex,score,class_name)values(李大刚,男,97,t456)insert into student(stu_name,sex,score,class_name)values(王菲,女,100,t123)insert into student(stu_name,sex,score,class_name)values(赵刚,男,90,s123)insert into student(stu_name,sex,score,class_name)values(李宁,男,85,t123)insert into student(stu_name,sex,score,class_name)values(赵姗,女,56,s456)insert into student(stu_name,sex,score,class_name)values(郭蕊,女,78,t456)insert into student(stu_name,sex,score,class_name)values(孙小旭,男,60,s123)insert into student(stu_name,sex,score,class_name)values(孙津,女,72,t123)insert into student(stu_name,sex,score,class_name)values(丁磊,男,23,t456)--更新语句update student set score=80 where stu_name=孙津update student set score=81,class_name=t456 where stu_name=孙津--删除语句delete from student--清空表truncate table student--清空表不走日志快delete from student where stu_name=丁磊delete from student where stu_name=丁磊 and stu_name=孙津--查询语句select * from teacherselect * from studentselect * from student where stu_name=李刚select stu_name,score from student where stu_name=李刚select * from student where score!=60select * from student where score60select * from student where s