《php知识点大全【精心总结版】》由会员分享，可在线阅读，更多相关《php知识点大全【精心总结版】（161页珍藏版）》请在金锄头文库上搜索。
1、主表create table teacher(class_name varchar(20) primary key,tea_name varchar(20) not null,t_time datetime default(getdate()go-创建学生表从表create table student(stu_id int identity(1,1) primary key,stu_name varchar(20) not null,sex char(2) check(sex=男 or sex=女),score int default(0),class_name varchar(20) for
2、eign 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(t45
3、6,赵老师)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 in
4、to 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,
5、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=孙津-删除语句de
6、lete 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 stud
7、ent where score!=60select * from student where score60 and sex=女-排序 order byselect * from student order by score -升序select * from student order by score desc -降序select * from student order by stu_nameselect * from student order by stu_name,score-desc 降序排序的语法 select * from 表名 order by 字段名 +排序方式-聚合函数
8、sum、avg、max 、min、count-所有学生成绩总和select sum(score) as 总和 from student-所有学生成绩平均分select avg(score) as 平均分 from student-s123 班级所有学生成绩的平均分select avg(score) as 平均分 from student where class_name=s123-最高分select max(score) as 最高分 from student-最低分select min(score) as 最低分 from student-参加考试学生人数select count(*) as
9、 总数 from studentselect count(*) as 总数 from student where score=60-查询每个班级所有学生的平均分select avg(score) from student group by class_name-查询班级的个数，平均分不小于 60 的班级的个数select count(*) as 学生人数,class_name from student group by class_name having avg(score)=60-注意：where、having 后边都可以跟条件-where: 普通的条件查询-having: 一般都是配合着聚
10、合函数、分组函数-模糊查询 like- % 代表任意长度的字符- _ 代表任意一个字符- 代表任意一个位置，可以是多个字符-查询所有姓李的学生select * from student where stu_name like 李%-查找所有姓李的学生，名字有两个字组成select * from student where stu_name like 李_-查找所有姓张和姓李的学生select * from student where stu_name like 李郭%-名字中代“小”select * from student where stu_name like %小%-多表查询-内联接 i
11、nner join.on (将多张表的字段合为一张表)-查询张老师班级所有的学生select * from student inner join teacher on student.class_name=teacher.class_name and teacher.tea_name=张老师-外联接 outer join.on-左外连接 left outer join.on (以左表为主)-查询张老师班级所有的学生select * from student s left outer join teacher tons.class_name=t.class_name and t.tea_name
12、=张老师-右外连接 right outer join.on (右表为主)select * from student s right outer join teacher tons.class_name=t.class_name and t.tea_name=张老师-全外联接 full outer join.on select * from student s full outer join teacher tons.class_name=t.class_name and t.tea_name=张老师-外键的关键字 foreign key references 涉及外键的语法格式 字段名+类型+
13、foreign key+ references +表名（字段）create table student(stu_id int primary key identity(1,1) not null,stu_name varchar(20) not null,sex char(2) not null,score float not null,class_name varchar(10) foreign key references teacher(class_name)-子查询 (在查询条件中还包含另外一个 sql 语句 )select * from student where class_nam
14、e=(select class_name from teacher where tea_name=张老师)select * from student where class_name in(select class_name from teacher where tea_name=张老师 or tea_name=李老师)select * from student where class_name !=(select class_name from teacher where tea_name=张老师)select * from student where class_name not in(s
15、elect class_name from teacher where tea_name=张老师 or tea_name=李老师)-联合 union (把多张表的记录合为一张表)-去掉重复记录select class_name from studentunionselect class_name from teacher-所有记录全部显示select class_name from studentunion allselect class_name from teacher-多表查询 (相同的字段相等 )select * from student,teacher where teacher.tea_name=张老师 and student.class_name=teacher.class_name-pubs 案例数据库 图书作者sp_tables -显示该数据库所有的表名select * from authors -作者select * from titles -图书select * from titleauthor -图书作者-查询 Johnson 作者的所有的图书select a.au_id,b.title from authors a,titles b,titleauthor
