數(shù)據(jù)庫 查詢 修改 刪除 增加 排序 等命令.doc

數(shù)據(jù)庫 查詢 修改 刪除 增加 排序 等命令insert into Register values(張三) /添加語句select * from Register /查詢語句 查詢整個表delete from Register where Loadname=張三 /刪除Register表中的Loadname字段=張三的行信息select * from UserLoad order by id desc /*利用ID進行排序desc降序排序 將UserLoad中的所有id都進行排序select * from UserLoad order by id asc利用ID進行排序asc 升序排序alter table UserLoad drop column Text 刪除Userload表中Text字段這列select count(*) from Register where sex=男 查找Register表中sex=男的所有人數(shù)select BrithDay ,Phone from Register where Loadname=張三 在Register表中查詢Loadname=張三的BrithDay和Phoneupdate UserLoad set Text=11111 where id=1 修改UserLoad表中ID=1的Text文本內(nèi)容select Passworld from Register where Loadname=張三 在Register表中查找Loadname=張三 的密碼id int identity(1,1)/使ID值由1自動增加1當(dāng)字段的長度大于5個字符在其后加select case when len(FCatalog)5 then LEFT(FCatalog,5)+. else FCatalog end from Fiction 多 表 查 詢/*查詢所有選課學(xué)生的學(xué)號，姓名，課程號，成績*/select stu.sno,sname,cno,score from stu inner join scon stu.sno=sc.sno/*查詢所有選課學(xué)生的姓名，課程名，成績*/select sname,cname,score from stu inner join scinner join courseon o=o on sc.sno=stu.sno1.查詢姓名為jack的學(xué)生選修的課程號。/*1.多表查詢*/select cno from sc,stuwhere sc.sno=stu.sno and sname=jack/*2.子查詢*/select cno from sc where sno=(select sno from stu where sname=jack)思考：查詢姓名為jack的學(xué)生選修的課程名/*1.多表查詢*/select cname from course,sc,stuwhere sc.sno=stu.sno and o=oand sname=jack/*2.子查詢*/select cname from course where cno in(select cno from sc where sno =(select sno from stu where sname=jack)2.查詢所有選課學(xué)生的學(xué)號、姓名、選課名稱及成績。3.查詢每個選課學(xué)生的學(xué)號、姓名、平均成績。select stu.sno,sname,avg(score) as avg_score from stu,sc where stu.sno=sc.snogroup by stu.sno,sname4.查詢每門課程的課程名和考試平均成績select cname,avg(score) as 平均成績 from course,scwhere o=ogroup by cname5.求選修C1課程學(xué)生的平均年齡select avg(age) as 平均年齡 from stuwhere sno in(select sno from sc where cno=c1)6.求LI老師所授課程的每門課程的課程名和平均成績select cname,avg(score) as 平均成績 from course,scwhere o=o and teacher=ligroup by cname7. 查詢與jack同學(xué)年齡相同的學(xué)號、姓名。select sno,sname from stuwhere age=(select age from stu where sname=jack)8.查詢學(xué)號比mary同學(xué)大，而年齡比他小的學(xué)生姓名select sname from stuwhere sno(select sno from stu where sname=mary)and age(select avg(age) from stu where sex=W)10.查詢和Rom同學(xué)選修了一樣課程的學(xué)生的學(xué)號，姓名。select sno,sname from stu where sno in(select sno from sc where cno in(select cno from sc where sno=(select sno from stu where sname=Rom)group by sno having count(cno)=(select count(cno) from sc where sno=(select sno from stu where sname=Rom)11.查詢沒有被選修課程的課程號和課程名。select cno,cname from coursewhere cno not in (select cno from sc)查詢沒有選課的學(xué)生的學(xué)號和姓名select sno,sname from stuwhere sno not in(select sno from sc)設(shè)置約束條件Constraint pk_表名_字段名 primary key (字段名)Constraint uk_表名_字段名 unique (字段名)Constraint ck_表名_字段名check (字段數(shù)and 字段1 and 字段1 or 字段 1 Where 字段110 and 字段2 20 字段1不是10并且字段2不是20Where 字段 like % 查找所有的Select * from 表名 Where 字段 like %s% 含有s的內(nèi)容 where 字段 like A-D%s_ 首字符是A-D任意數(shù)并且倒數(shù)第三個數(shù)要是s select * from 表名order by 字段 排序 select * fr