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

數(shù)據(jù)庫(kù) 查詢(xún) 修改 刪除 增加 排序 等命令insert into Register values(張三) /添加語(yǔ)句select * from Register /查詢(xún)語(yǔ)句 查詢(xún)整個(gè)表delete from Register where Loadname=張三 /刪除Register表中的Loadname字段=張三的行信息select * from UserLoad order by id desc /*利用ID進(jìn)行排序desc降序排序 將UserLoad中的所有id都進(jìn)行排序select * from UserLoad order by id asc利用ID進(jìn)行排序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表中查詢(xún)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自動(dòng)增加1當(dāng)字段的長(zhǎng)度大于5個(gè)字符在其后加select case when len(FCatalog)5 then LEFT(FCatalog,5)+. else FCatalog end from Fiction 多 表 查 詢(xún)/*查詢(xún)所有選課學(xué)生的學(xué)號(hào)，姓名，課程號(hào)，成績(jī)*/select stu.sno,sname,cno,score from stu inner join scon stu.sno=sc.sno/*查詢(xún)所有選課學(xué)生的姓名，課程名，成績(jī)*/select sname,cname,score from stu inner join scinner join courseon o=o on sc.sno=stu.sno1.查詢(xún)姓名為jack的學(xué)生選修的課程號(hào)。/*1.多表查詢(xún)*/select cno from sc,stuwhere sc.sno=stu.sno and sname=jack/*2.子查詢(xún)*/select cno from sc where sno=(select sno from stu where sname=jack)思考：查詢(xún)姓名為jack的學(xué)生選修的課程名/*1.多表查詢(xún)*/select cname from course,sc,stuwhere sc.sno=stu.sno and o=oand sname=jack/*2.子查詢(xún)*/select cname from course where cno in(select cno from sc where sno =(select sno from stu where sname=jack)2.查詢(xún)所有選課學(xué)生的學(xué)號(hào)、姓名、選課名稱(chēng)及成績(jī)。3.查詢(xún)每個(gè)選課學(xué)生的學(xué)號(hào)、姓名、平均成績(jī)。select stu.sno,sname,avg(score) as avg_score from stu,sc where stu.sno=sc.snogroup by stu.sno,sname4.查詢(xún)每門(mén)課程的課程名和考試平均成績(jī)select cname,avg(score) as 平均成績(jī) 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老師所授課程的每門(mén)課程的課程名和平均成績(jī)select cname,avg(score) as 平均成績(jī) from course,scwhere o=o and teacher=ligroup by cname7. 查詢(xún)與jack同學(xué)年齡相同的學(xué)號(hào)、姓名。select sno,sname from stuwhere age=(select age from stu where sname=jack)8.查詢(xún)學(xué)號(hào)比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.查詢(xún)和Rom同學(xué)選修了一樣課程的學(xué)生的學(xué)號(hào)，姓名。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.查詢(xún)沒(méi)有被選修課程的課程號(hào)和課程名。select cno,cname from coursewhere cno not in (select cno from sc)查詢(xún)沒(méi)有選課的學(xué)生的學(xué)號(hào)和姓名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ù)第三個(gè)數(shù)要是s select * from 表名order by 字段 排序 select * fr