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

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 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 字段名 +排序方式-聚合函数 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 总数 from studentselect count(*) as 总数 from student where score0-分组函数 group by 配合着聚合函数一起使用-注意：查询结果中出现的列，要么出现在聚合函数中，要么出现在group by中-所有男生和女生的平均分select avg(score)as 平均分,sex from student group by sex-查询所有学生的成绩总和，但是去掉不及格的学生成绩select sum(score) 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: 一般都是配合着聚合函数、分组函数-模糊查询 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 %小%-多表查询-内联接 inner 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=张老师 -右外连接 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涉及外键的语法格式 字段名+类型+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_name=(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(select 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 cwhere a.au_id=c.au_id and b.title_id=c.title_id and a.au_fname=Marjorie-northwind 订单查询sp_tablesselect * from customers -客户表select * from orders -订单表select * from products -产品表select * from order details -订单明细表-查询Alfreds Futterkiste订购的所有商品select a.CustomerID,c.ProductName from Customers a,Orders b,Products c,Order Details dwhere a.CustomerID=b.CustomerID and b.OrderID=d.OrderID andc.ProductID=d.ProductID and a.CompanyName=Alfreds Futterkiste-视图 view 视图一旦创建，始终存在 -表-创建视图create view myviewasselect a.CustomerID,c.ProductName from Customers a,Orders b,Products c,Order Details dwhere a.CustomerID=b.CustomerID and b.OrderID=d.OrderID andc.ProductID=d.ProductID and a.CompanyName=Alfreds Futterkistego-修改视图alter view myviewasselect * from ordersgo-删除视图drop view myviewselect * from myview where productName=Spegesild-触发器 trigger-创建create trigger mytrion studentfor deleteasselect * from studentgo-修改alter trigger mytrion studentfor insert,delete,updateasselect * from studentgo-删除drop trigger mytridelete from student where stu_id=1update student set score=100 where stu_name=李小明-存储过程 proc-创建create proc myprocasselect * from studentgoalter proc myproc stuId intasselect * from stude