常用的sql查詢語句-ag真人国际官网
1.檢索數據
select prod_namefrom procts;
#檢索單列
select prod_id, prod_name, prod_pricefromprocts;
#檢索多列
select * from procts;
#檢索所有列
select distinctvend_id fromprocts;
#檢索不同的值
selectprod_name from procts limit 5;
#返回不超過5行數據
selectprod_name from procts limit 5 offset 5;
#返回從第5行起的5行數據。limit指定返回的行數，limit帶的offset指定從哪兒開始。
2.排序檢索數據
selectprod_name
fromprocts
order byprod_name;
#排序數據
select prod_id, prod_price, prod_name
fromprocts
order by prod_price, prod_name;
#按多個列排序
select prod_id, prod_price, prod_name
fromprocts
order by 2, 3;
#按列位置排序，第三行表示先按prod_price, 再按prod_name進行排序
select prod_id, prod_price, prod_name
fromprocts
order by prod_pricedesc, prod_name;
#prod_price列以降序排序，而prod_name列（在每個價格內）仍然按標準的升序排序
3.過濾數據
select prod_name, prod_price
fromprocts
where prod_price< 10;
#檢查單個值
select prod_name, prod_price
fromprocts
where vend_id <> 『dll01』;
#不匹配檢查
select prod_name, prod_price
fromprocts
where prod_pricebetween 5 and 10;
#范圍值檢查
select cust_name
fromcustomers
where cust_emailis null;
#空值檢查
4.高級數據過濾
selectprod_id, prod_price, prod_name
fromprocts
where vend_id = 『dll01』andprod_price <= 4;
#and操作符
selectprod_name, prod_price
fromprocts
wherevend_id=』dll01』 or vend_id=』brs01』;
#or操作符
selectprod_name, prod_price
fromprocts
where (vend_id = 』dll01』orvend_id=』brs01』)
andprod_price >= 10；
#求值順序 and的優先順序高於or
selectprod_name, prod_price
fromprocts
where vend_idin (『dll01』,』brs01』)
order by prod_name;
#in操作符
select prod_name
fromprocts
where notvend_id = 『dll01』
order by prod_name;
#not 操作符
select prod_name
fromprocts
wherevend_id <> 『dll01』
order by prod_name;
#not 操作符
② sql查詢語句格式是什麼
sql查詢語句格式是：
③ 資料庫常用sql語句有哪些
資料庫常用sql語句有哪些
sql語句有哪些？sql語句無論是種類還是數量都是繁多的，很多語句也是經常要用到的，下文我為大家分享的就是sql的常用語句，僅供參考！
50個常用的sql語句
student(s#,sname,sage,ssex) 學生表
course(c#,cname,t#) 課程表
sc(s#,c#,score) 成績表
teacher(t#,tname) 教師表
問題：
1、查詢“001”課程比“002”課程成績高的所有學生的學號；
select a.s# from (select s#,score from sc where c#='001') a,(select s#,score
from sc where c#='002') b
where a.score>b.score and a.s#=b.s#;
2、查詢平均成績大於60分的同學的學號和平均成績；
select s#,avg(score)
from sc
group by s# having avg(score) >60;
3、查詢所有同學的學號、姓名、選課數、總成績；
select student.s#,student.sname,count(sc.c#),sum(score)
from student left outer join sc on student.s#=sc.s#
group by student.s#,sname
4、查詢姓“李”的老師的個數；
select count(distinct(tname))
from teacher
where tname like '李%';
5、查詢沒學過“葉平”老師課的同學的學號、姓名；
select student.s#,student.sname
from student
where s# not in (select distinct( sc.s#) from sc,course,teacher where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname='葉平');
6、查詢學過“001”並且也學過編號“002”課程的同學的學號、姓名；
select student.s#,student.sname from student,sc where student.s#=sc.s# and sc.c#='001'and exists( select * from sc as sc_2 where sc_2.s#=sc.s# and sc_2.c#='002');
7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名；
select s#,sname
from student
where s# in (select s# from sc ,course ,teacher where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname='葉平' group by s# having count(sc.c#)=(select count(c#) from course,teacher where teacher.t#=course.t# and tname='葉平'));
8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名；
select s#,sname from (select student.s#,student.sname,score ,(select score from sc sc_2 where sc_2.s#=student.s# and sc_2.c#='002') score2
from student,sc where student.s#=sc.s# and c#='001') s_2 where score2
9、查詢所有課程成績小於60分的同學的學號、姓名；
select s#,sname
from student
where s# not in (select student.s# from student,sc where s.s#=sc.s# and score>60);
10、查詢沒有學全所有課的同學的學號、姓名；
select student.s#,student.sname
from student,sc
where student.s#=sc.s# group by student.s#,student.sname having count(c#) <(select count(c#) from course);
11、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名；
select s#,sname from student,sc where student.s#=sc.s# and c# in select c# from sc where s#='1001';
12、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名；
select distinct sc.s#,sname
from student,sc
where student.s#=sc.s# and c# in (select c# from sc where s#='001');
13、把“sc”表中“葉平”老師教的課的成績都更改為此課程的平均成績；
update sc set score=(select avg(sc_2.score)
from sc sc_2
where sc_2.c#=sc.c# ) from course,teacher where course.c#=sc.c# and course.t#=teacher.t# and teacher.tname='葉平');
14、查詢和“1002”號的同學學習的課程完全相同的其他同學學號和姓名；
select s# from sc where c# in (select c# from sc where s#='1002')
group by s# having count(*)=(select count(*) from sc where s#='1002');
15、刪除學習“葉平”老師課的sc表記錄；
delect sc
from course ,teacher
where course.c#=sc.c# and course.t#= teacher.t# and tname='葉平';
16、向sc表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“003”課程的同學學號、2、
號課的平均成績；
insert sc select s#,'002',(select avg(score)
from sc where c#='002') from student where s# not in (select s# from sc where c#='002');
17、按平均成績從高到低顯示所有學生的“資料庫”、“企業管理”、“英語”三門的課程成績，按如下形式顯示： 學生id,,資料庫,企業管理,英語,有效課程數,有效平均分
select s# as 學生id
,(select score from sc where sc.s#=t.s# and c#='004') as 資料庫
,(select score from sc where sc.s#=t.s# and c#='001') as 企業管理
,(select score from sc where sc.s#=t.s# and c#='006') as 英語
,count(*) as 有效課程數, avg(t.score) as 平均成績
from sc as t
group by s#
order by avg(t.score)
18、查詢各科成績最高和最低的分：以如下形式顯示：課程id，最高分，最低分
select l.c# as 課程id,l.score as 最高分,r.score as 最低分
from sc l ,sc as r
where l.c# = r.c# and
l.score = (select max(il.score)
from sc as il,student as im
where l.c# = il.c# and im.s#=il.s#
group by il.c#)
and
r.score = (select min(ir.score)
from sc as ir
where r.c# = ir.c#
group by ir.c#
);
19、按各科平均成績從低到高和及格率的百分數從高到低順序
select t.c# as 課程號,max(course.cname)as 課程名,isnull(avg(score),0) as 平均成績
,100 * sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) as 及格百分數
from sc t,course
where t.c#=course.c#
group by t.c#
order by 100 * sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) desc
20、查詢如下課程平均成績和及格率的百分數(用"1行"顯示): 企業管理（001），馬克思（002），oo¨ （003），資料庫（004）
select sum(case when c# ='001' then score else 0 end)/sum(case c# when '001' then 1 else 0 end) as 企業管理平均分
,100 * sum(case when c# = '001' and score >= 60 then 1 else 0 end)/sum(case when c# = '001' then 1 else 0 end) as 企業管理及格百分數
,sum(case when c# = '002' then score else 0 end)/sum(case c# when '002' then 1 else 0 end) as 馬克思平均分
,100 * sum(case when c# = '002' and score >= 60 then 1 else 0 end)/sum(case when c# = '002' then 1 else 0 end) as 馬克思及格百分數
,sum(case when c# = '003' then score else 0 end)/sum(case c# when '003' then 1 else 0 end) as uml平均分
,100 * sum(case when c# = '003' and score >= 60 then 1 else 0 end)/sum(case when c# = '003' then 1 else 0 end) as uml及格百分數
,sum(case when c# = '004' then score else 0 end)/sum(case c# when '004' then 1 else 0 end) as 資料庫平均分
,100 * sum(case when c# = '004' and score >= 60 then 1 else 0 end)/sum(case when c# = '004' then 1 else 0 end) as 資料庫及格百分數
from sc
21、查詢不同老師所教不同課程平均分從高到低顯示
select max(z.t#) as 教師id,max(z.tname) as 教師姓名,c.c# as 課程id,max(c.cname) as 課程名稱,avg(score) as 平均成績
from sc as t,course as c ,teacher as z
where t.c#=c.c# and c.t#=z.t#
group by c.c#
order by avg(score) desc
22、查詢如下課程成績第 3 名到第 6 名的學生成績單：企業管理（001），馬克思（002），uml （003），資料庫（004）
[學生id],[學生姓名],企業管理,馬克思,uml,資料庫,平均成績
select distinct top 3
sc.s# as 學生學號,
student.sname as 學生姓名 ,
t1.score as 企業管理,
t2.score as 馬克思,
t3.score as uml,
t4.score as 資料庫,
isnull(t1.score,0) isnull(t2.score,0) isnull(t3.score,0) isnull(t4.score,0) as 總分
from student,sc left join sc as t1
on sc.s# = t1.s# and t1.c# = '001'
left join sc as t2
on sc.s# = t2.s# and t2.c# = '002'
left join sc as t3
on sc.s# = t3.s# and t3.c# = '003'
left join sc as t4
on sc.s# = t4.s# and t4.c# = '004'
where student.s#=sc.s# and
isnull(t1.score,0) isnull(t2.score,0) isnull(t3.score,0) isnull(t4.score,0)
not in
(select
distinct
top 15 with ties
isnull(t1.score,0) isnull(t2.score,0) isnull(t3.score,0) isnull(t4.score,0)
from sc
left join sc as t1
on sc.s# = t1.s# and t1.c# = 'k1'
left join sc as t2
on sc.s# = t2.s# and t2.c# = 'k2'
left join sc as t3
on sc.s# = t3.s# and t3.c# = 'k3'
left join sc as t4
on sc.s# = t4.s# and t4.c# = 'k4'
order by isnull(t1.score,0) isnull(t2.score,0) isnull(t3.score,0) isnull(t4.score,0) desc);
23、統計列印各科成績,各分數段人數:課程id,課程名稱,[100-85],[85-70],[70-60],[ <60]
select sc.c# as 課程id, cname as 課程名稱
,sum(case when score between 85 and 100 then 1 else 0 end) as [100 - 85]
,sum(case when score between 70 and 85 then 1 else 0 end) as [85 - 70]
,sum(case when score between 60 and 70 then 1 else 0 end) as [70 - 60]
,sum(case when score < 60 then 1 else 0 end) as [60 -]
from sc,course
where sc.c#=course.c#
group by sc.c#,cname;
24、查詢學生平均成績及其名次
select 1 (select count( distinct 平均成績)
from (select s#,avg(score) as 平均成績
from sc
group by s#
) as t1
where 平均成績 > t2.平均成績) as 名次,
s# as 學生學號,平均成績
from (select s#,avg(score) 平均成績
from sc
group by s#
) as t2
order by 平均成績 desc;
25、查詢各科成績前三名的記錄:(不考慮成績並列情況)
select t1.s# as 學生id,t1.c# as 課程id,score as 分數
from sc t1
where score in (select top 3 score
from sc
where t1.c#= c#
order by score desc
)
order by t1.c#;
26、查詢每門課程被選修的學生數
select c#,count(s#) from sc group by c#;
27、查詢出只選修了一門課程的全部學生的學號和姓名
select sc.s#,student.sname,count(c#) as 選課數
from sc ,student
where sc.s#=student.s# group by sc.s# ,student.sname having count(c#)=1;
28、查詢男生、女生人數
select count(ssex) as 男生人數 from student group by ssex having ssex='男';
select count(ssex) as 女生人數 from student group by ssex having ssex='女'；
29、查詢姓“張”的.學生名單
select sname from student where sname like '張%';
30、查詢同名同性學生名單，並統計同名人數
select sname,count(*) from student group by sname having count(*)>1;;
31、1981年出生的學生名單(註：student表中sage列的類型是datetime)
select sname, convert(char (11),datepart(year,sage)) as age
from student
where convert(char(11),datepart(year,sage))='1981';
32、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列
select c#,avg(score) from sc group by c# order by avg(score),c# desc ;
33、查詢平均成績大於85的所有學生的學號、姓名和平均成績
select sname,sc.s# ,avg(score)
from student,sc
where student.s#=sc.s# group by sc.s#,sname having avg(score)>85;
34、查詢課程名稱為“資料庫”，且分數低於60的學生姓名和分數
select sname,isnull(score,0)
from student,sc,course
where sc.s#=student.s# and sc.c#=course.c# and course.cname='資料庫'and score <60;
35、查詢所有學生的選課情況；
select sc.s#,sc.c#,sname,cname
from sc,student,course
where sc.s#=student.s# and sc.c#=course.c# ;
36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數；
select distinct student.s#,student.sname,sc.c#,sc.score
from student,sc
where sc.score>=70 and sc.s#=student.s#;
37、查詢不及格的課程，並按課程號從大到小排列
select c# from sc where scor e <60 order by c# ;
38、查詢課程編號為003且課程成績在80分以上的學生的學號和姓名；
select sc.s#,student.sname from sc,student where sc.s#=student.s# and score>80 and c#='003';
39、求選了課程的學生人數
select count(*) from sc;
40、查詢選修“葉平”老師所授課程的學生中，成績最高的學生姓名及其成績
select student.sname,score
from student,sc,course c,teacher
where student.s#=sc.s# and sc.c#=c.c# and c.t#=teacher.t# and teacher.tname='葉平' and sc.score=(select max(score)from sc where c#=c.c# );
41、查詢各個課程及相應的選修人數
select count(*) from sc group by c#;
42、查詢不同課程成績相同的學生的學號、課程號、學生成績
select distinct a.s#,b.score from sc a ,sc b where a.score=b.score and a.c# <>b.c# ;
43、查詢每門功成績最好的前兩名
select t1.s# as 學生id,t1.c# as 課程id,score as 分數
from sc t1
where score in (select top 2 score
from sc
where t1.c#= c#
order by score desc
)
order by t1.c#;
44、統計每門課程的學生選修人數（超過10人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，查詢結果按人數降序排列，若人數相同，按課程號升序排列
select c# as 課程號,count(*) as 人數
from sc
group by c#
order by count(*) desc,c#
45、檢索至少選修兩門課程的學生學號
select s#
from sc
group by s#
having count(*) > = 2
46、查詢全部學生都選修的課程的課程號和課程名
select c#,cname
from course
where c# in (select c# from sc group by c#)
47、查詢沒學過“葉平”老師講授的任一門課程的學生姓名
select sname from student where s# not in (select s# from course,teacher,sc where course.t#=teacher.t# and sc.c#=course.c# and tname='葉平');
48、查詢兩門以上不及格課程的同學的學號及其平均成績
select s#,avg(isnull(score,0)) from sc where s# in (select s# from sc where score <60 group by s# having count(*)>2)group by s#;
49、檢索“004”課程分數小於60，按分數降序排列的同學學號
select s# from sc where c#='004'and score <60 order by score desc;
50、刪除“002”同學的“001”課程的成績
delete from sc where s#='001'and c#='001';
;④ sql server鏁版嵁搴撴煡璇㈣鍙ヤ嬌鐢ㄦ柟娉曡︾粏璁茶в
涓銆 綆鍗曟煡璇
綆鍗曠殑transact-sql鏌ヨ㈠彧鍖呮嫭閫夋嫨鍒楄〃銆丗rom瀛愬彞鍜學here瀛愬彞銆傚畠浠鍒嗗埆璇存槑鎵鏌ヨ㈠垪銆佹煡璇㈢殑琛ㄦ垨瑙嗗浘銆佷互鍙婃悳緔㈡潯浠剁瓑銆
渚嬪傦紝涓嬮潰鐨勮鍙ユ煡璇testtable琛ㄤ腑濮撳悕涓衡滃紶涓夆濈殑nickname瀛楁靛拰email瀛楁點
select nickname,email
from testtable
where name='寮犱笁'
(涓) 閫夋嫨鍒楄〃
閫夋嫨鍒楄〃(select_list)鎸囧嚭鎵鏌ヨ㈠垪錛屽畠鍙浠ユ槸涓緇勫垪鍚嶅垪琛ㄣ佹槦鍙楓佽〃杈懼紡銆佸彉閲(鍖呮嫭灞閮ㄥ彉閲忓拰鍏ㄥ矓鍙橀噺)絳夋瀯鎴愩
1銆侀夋嫨鎵鏈夊垪
渚嬪傦紝涓嬮潰璇鍙ユ樉紺簍esttable琛ㄤ腑鎵鏈夊垪鐨勬暟鎹:
select *
from testtable
2銆侀夋嫨閮ㄥ垎鍒楀苟鎸囧畾瀹冧滑鐨勬樉紺烘″簭
鏌ヨ㈢粨鏋滈泦鍚堜腑鏁版嵁鐨勬帓鍒楅『搴忎笌閫夋嫨鍒楄〃涓鎵鎸囧畾鐨勫垪鍚嶆帓鍒楅『搴忕浉鍚屻
渚嬪:
select nickname,email
from testtable
3銆佹洿鏀瑰垪鏍囬
鍦ㄩ夋嫨鍒楄〃涓錛屽彲閲嶆柊鎸囧畾鍒楁爣棰樸傚畾涔夋牸寮忎負:
鍒楁爣棰=鍒楀悕
鍒楀悕 鍒楁爣棰
濡傛灉鎸囧畾鐨勫垪鏍囬樹笉鏄鏍囧噯鐨勬爣璇嗙︽牸寮忔椂錛屽簲浣跨敤寮曞彿瀹氱晫絎︼紝渚嬪傦紝涓嬪垪璇鍙ヤ嬌鐢ㄦ眽瀛楁樉紺哄垪鏍囬:
select 鏄電о=nickname,鐢靛瓙閭浠=email
from testtable
4銆佸垹闄ら噸澶嶈
select璇鍙ヤ腑浣跨敤all鎴朌istinct閫夐」鏉ユ樉紺鴻〃涓絎﹀悎鏉′歡鐨勬墍鏈夎屾垨鍒犻櫎鍏朵腑閲嶅嶇殑鏁版嵁琛岋紝榛樿や負all銆備嬌鐢―istinct閫夐」 鏃訛紝瀵逛簬鎵鏈夐噸澶嶇殑鏁版嵁琛屽湪select榪斿洖鐨勭粨鏋滈泦鍚堜腑鍙淇濈暀涓琛屻
5銆侀檺鍒惰繑鍥炵殑琛屾暟
浣跨敤top n [percent]閫夐」闄愬埗榪斿洖鐨勬暟鎹琛屾暟錛孴op n璇存槑榪斿洖n琛岋紝鑰孴op n percent鏃訛紝璇存槑n鏄琛ㄧず涓鐧懼垎鏁幫紝鎸囧畾榪斿洖鐨勮屾暟絳変簬鎬昏屾暟鐨勭櫨鍒嗕箣鍑犮
渚嬪:
select top 2 *from testtable select top 20 percent * from testtable
(浜)from 瀛愬彞
from瀛愬彞鎸囧畾select璇鍙ユ煡璇㈠強涓庢煡璇㈢浉鍏崇殑琛ㄦ垨瑙嗗浘銆傚湪from瀛愬彞涓鏈澶氬彲鎸囧畾256涓琛ㄦ垨瑙嗗浘錛屽畠浠涔嬮棿鐢ㄩ楀彿鍒嗛殧銆
鍦‵rom瀛愬彞鍚屾椂鎸囧畾澶氫釜琛ㄦ垨瑙嗗浘鏃訛紝濡傛灉閫夋嫨鍒楄〃涓瀛樺湪鍚屽悕鍒楋紝榪欐椂搴斾嬌鐢ㄥ硅薄鍚嶉檺瀹氳繖浜涘垪鎵灞炵殑琛ㄦ垨瑙嗗浘銆備緥濡傚湪usertable鍜 citytable琛ㄤ腑鍚屾椂瀛樺湪cityid鍒楋紝鍦ㄦ煡璇涓や釜琛ㄤ腑鐨刢ityid鏃跺簲浣跨敤涓嬮潰璇鍙ユ牸寮忓姞浠ラ檺瀹:
select username,citytable.cityid
from usertable,citytable
where usertable.cityid=citytable.cityid
鍦‵rom瀛愬彞涓鍙鐢ㄤ互涓嬩袱縐嶆牸寮忎負琛ㄦ垨瑙嗗浘鎸囧畾鍒鍚:
琛ㄥ悕 as 鍒鍚
琛ㄥ悕 鍒鍚
渚嬪備笂闈㈣鍙ュ彲鐢ㄨ〃鐨勫埆鍚嶆牸寮忚〃紺轟負:
select username,b.cityid
from usertable a,citytable b
where a.cityid=b.cityid
select涓嶄粎鑳戒粠琛ㄦ垨瑙嗗浘涓媯緔㈡暟鎹錛屽畠榪樿兘澶熶粠鍏跺畠鏌ヨ㈣鍙ユ墍榪斿洖鐨勭粨鏋滈泦鍚堜腑鏌ヨ㈡暟鎹銆
渚嬪:
select a.au_fname a.au_lname
from authors a,titleauthor ta
(select title_id,title
from titles
where ytd_sales10000
) as t
where a.au_id=ta.au_id
and ta.title_id=t.title_id
姝や緥涓錛屽皢select榪斿洖鐨勭粨鏋滈泦鍚堢粰浜堜竴鍒鍚峵錛岀劧鍚庡啀浠庝腑媯緔㈡暟鎹銆
(涓) 浣跨敤where瀛愬彞璁劇疆鏌ヨ㈡潯浠
where瀛愬彞璁劇疆鏌ヨ㈡潯浠訛紝榪囨護鎺変笉闇瑕佺殑鏁版嵁琛屻備緥濡備笅闈㈣鍙ユ煡璇㈠勾榫勫ぇ浜20鐨勬暟鎹:
select *
from usertable
where age20
where瀛愬彞鍙鍖呮嫭鍚勭嶆潯浠惰繍綆楃:
姣旇緝榪愮畻絎(澶у皬姣旇緝):銆=銆=銆併=銆併!銆!
鑼冨洿榪愮畻絎(琛ㄨ揪寮忓兼槸鍚﹀湪鎸囧畾鐨勮寖鍥):between鈥and鈥
not between鈥and鈥
鍒楄〃榪愮畻絎(鍒ゆ柇琛ㄨ揪寮忔槸鍚︿負鍒楄〃涓鐨勬寚瀹氶」):in (欏1,欏2鈥︹)
not in (欏1,欏2鈥︹)
妯″紡鍖歸厤絎(鍒ゆ柇鍊兼槸鍚︿笌鎸囧畾鐨勫瓧絎﹂氶厤鏍煎紡鐩哥):like銆丯ot like
絀哄煎垽鏂絎(鍒ゆ柇琛ㄨ揪寮忔槸鍚︿負絀):is null銆丯ot is null
閫昏緫榪愮畻絎(鐢ㄤ簬澶氭潯浠剁殑閫昏緫榪炴帴):not銆丄nd銆丱r
1銆佽寖鍥磋繍綆楃︿緥:age between 10 and 30鐩稿綋浜巃ge=10 and age=30
2銆佸垪琛ㄨ繍綆楃︿緥:country in ('germany','china')
3銆佹ā寮忓尮閰嶇︿緥:甯哥敤浜庢ā緋婃煡鎵撅紝瀹冨垽鏂鍒楀兼槸鍚︿笌鎸囧畾鐨勫瓧絎︿覆鏍煎紡鐩稿尮閰嶃傚彲鐢ㄤ簬char銆乿archar銆乼ext銆乶text銆 datetime鍜宻malldatetime絳夌被鍨嬫煡璇銆
涓婁竴欏12 涓嬩竴欏
⑤ 怎樣用sql語句查詢一個資料庫中的所有表
查詢資料庫里所有表名和欄位名的語句
sql 查詢所有表名：
select name from sysobjects where type='u'
select * from information_schema.tables
結構化查詢語言(structured query language)簡稱sql，結構化查詢語言是一種資料庫查詢和程序設計語言，用於存取數據以及查詢、更新和管理關系資料庫系統；
sql 語句就是對資料庫進行操作的一種語言。
(5)常用的sql查詢語句擴展閱讀：
sql語句常見語句：
1、更新：update table1 set field1=value1 where 范圍；
2、查找：select * from table1 where field1 like 』%value1%』 （所有包含『value1』這個模式的字元串）；
3、排序：select * from table1 order by field1,field2 [desc]；
4、求和：select sum(field1) as sumvalue from table1；
5、平均：select avg(field1) as avgvalue from table1；
6、最大：select max(field1) as maxvalue from table1；
7、最小：select min(field1) as minvalue from table1[separator]。
參考資料來源：網路-sql語句
⑥ 常見的sql語句有哪些
(1)更新數據記錄：
sql="update數據表set欄位名=欄位值where條件表達式"
sql="update數據表set欄位1=值1,欄位2=值2……欄位n=值nwhere條件表達式"
(2)添加數據記錄：
sql="insertinto數據表(欄位1,欄位2,欄位3…)values(值1,值2,值3…)"
sql="insertinto目標數據表select*from源數據表"(把源數據表的記錄添加到目標數據表)
(3)刪除數據記錄：
sql="deletefrom數據表where條件表達式"
sql="deletefrom數據表"(將數據表所有記錄刪除)
(4)數據記錄篩選：
sql="select*from數據表where欄位名=欄位值orderby欄位名[desc]"
sql="select*from數據表where欄位名like'%欄位值%'orderby欄位名[desc]"
sql="selecttop10*from數據表where欄位名orderby欄位名[desc]"
sql="select*from數據表where欄位名in('值1','值2','值3')"
sql="select*from數據表where欄位名between值1and值2"
(5)數據表的建立和刪除：
createtable數據表名稱(欄位1類型1(長度),欄位2類型2(長度)……)
例：createtabletab01 (namevarchar (50), datetimedefaultnow ())
droptable數據表名稱(永久性刪除一個數據表)
(6)數據記錄統計函數：
avg(欄位名)得出一個表格欄平均值
count(*|欄位名)對數據行數的統計或對某一欄有值的數據行數統計
max(欄位名)取得一個表格欄最大的值
min(欄位名)取得一個表格欄最小的值
sum(欄位名)把數據欄的值相加
引用以上函數的方法：
sql="selectsum(欄位名)as別名from數據表where條件表達式"
setrs=conn.excute(sql)
用rs("別名")獲取統的計值，其它函數運用同上。
（6）復制資料庫的表
select * into 新表名 from 要復制的表的表名 where 1=2
要完全復制把where 1=2 去了就可以了
（7）刪除某個表的一個列
alter table bankbill drop column zsl
alter table xx alter/add/drop column xx
（8）模糊查詢
sql="select top 10 * from 數據表 where 欄位名 order by 欄位名 [desc]"
查找資料庫中前10記錄
sql="select top n * form 數據表 order by newid()"
隨機取出資料庫中的若干條記錄的方法
top n，n就是要取出的記錄數
sql="select * from 數據表 where 欄位名 in ('值1','值2','值3')"