数据库大师成长日记:经典SQL语句大全,查询基础,敬请收藏!
今天小编给各位分享sql基本语句大全的知识,文中也会对其通过数据库大师成长日记:经典SQL语句大全,查询基础,敬请收藏!和求一些sql数据库数据查询的一些经典语句等多篇文章进行知识讲解,如果文章内容对您有帮助,别忘了关注本站,现在进入正文!
内容导航:
一、数据库大师成长日记:经典SQL语句大全,查询基础,敬请收藏!
朋友们,对数据库的操作,离不开对SQL语句的掌握。SQL本身是一种通用的数据库语言,如果对常用的SQL语句不了解,每次操作都要在网上查,那可就费劲了。
我这里把常用的SQL语句列出来,可以作为您的日常参考。SQL语法基于T-SQL,如果您用的是PL/SQL,稍微修改也是基本兼容的。
举例方便,我们预设两个表,分别是MyTable1和MyTable2,结构和示例数据如下:
MyTable1:
MyTable1(FSerialID int not null primary key, FName varchar(100), FAge int, FSex varchar(2))1001 张三 18 男1002 李四 20 女
MyTable2:
MyTables(FSerialID int not null primary key, FAddress varchar(255), FPhone varchar(20))1001 广东深圳 133133000001003 北京 13813888888
两个表通过主键FSerialID可关联。
Join的使用Join的用法比较复杂,如果您想全盘掌握,可以参看下图。我这里着重说一下常用的左关联、右关联和内关联三种。
1、Left Jion左关联。
left join叫做左关联,返回的结果集包含主表的所有记录和关联表符合条件的记录。这是最常用的一种关联模式。
select 字段列表 from 主表 t1 left join 关联表 t2 on t1.某个字段=t2.某个字段 where 条件eg. select t1.FSerialID, t1.FName, t2.FAddress from MyTable1 t1 left join MyTable2 t2 on t1.FSerialID=t2.FSerialID --返回MyTable1中的所有记录,和能够按照匹配条件t1.FSerialID=t2.FSerialID匹配到的MyTable2中的记录。即MyTable1的所有记录,以及MyTalbe2中的FSerialID存在与MyTable1的中的部分。也就是FSerialID是1001、1002的两条记录,其中1001匹配到广东深圳、而1002无法匹配返回NULL。
2、Right Join右关联。
right join叫右关联,返回的结果集包括关联表的所有记录和主表符合条件的记录。这种关联模式一般比较少用。
select 字段列表 from 主表 t1 right join 关联表 t2 on t1.某个字段=t2.某个字段 where 条件eg. select t2.FSerialID,t1.FName,t2.FAddress from MyTable1 t1 right join MyTable2 t2 on t1.FSerialID=t2.FSerialID --返回MyTable2中的所有记录,和能够按照匹配条件t1.FSerialID=t2.FSerialID匹配到的MyTable1中的记录。即FSerialID是1001和1003的部分。
3、Inner Join内关联。
inner join叫内关联,只返回共同满足匹配条件的记录。这也是比较常用的关联模式。
select 字段列表 from 主表 t1 inner join 关联表 t2 on t1.某个字段=t2.某个字段 where 条件eg. select t1.FSerialID,t1.FName,t2.FAddress from MyTable1 t1 inner join MyTable2 t2 on t1.FSerialID=t2.FSerialID--返回MyTable1和MyTable2中,存在t1.FSerialID=t2.FSerialID的所有记录。即两个表含有共同的FSerialID的部分。即FSerialID是1001的一条记录。Like及通配符的使用
1、Like的基本用法
Like的含义是包含,简单的用法是 %字符串A%,含义是任何位置包含字符串A即满足。其中%字符串A的含义是结尾是字符串A;字符串A%的含义是开头是字符串A。
select 字段列表 from 表 where 字段 like %字符串A%eg. select * from MyTable1 where FName like '张%' --这是要查出来所有姓张的。eg. select * from MyTable2 where FAddress like '%深圳%' --这是要查出地址中包含深圳的。eg. select * from MyTable2 where FPhone like '%888' --这是要查出所有手机号码888结尾的。
2、常用通配符
常用的通配符主要包括下划线_、一个“_”标识一个任意字符;方括号[],包起来的字符标识多个字符的包含逻辑或;方括弧包起来里面开头是!,即[!ABC]这种,标识逻辑非。
eg. select * from MyTable2 where FPhone like '13[38][!47]%' --这是要查找开头是13,第三位是3或者8,第四位不是4或者7的所有手机号码。Union的使用
1、Union,可理解简单为两个表的数据放一起。多表数据互联查询返回记录集,相同数据的记录只显示一次
select 字段列表 from 表1 union select 相同字段列表 from 表2 ...... --注意强制要求字段列表必须一致,包括字段名(如果不同也要as成相同)、顺序、设置大致的数据类型都要一致。eg. select FSerialID from MyTable1 union select FSerialID from MyTable2 --返回两个表的FSerialID字段,这是返回的是1001、1002、1003,相同的1001只返回一条。
2、Union All,多表互联,不消除重复记录
select 字段列表 from 表1 union all select 相同字段列表 from 表2 ...... eg. select FSerialID from MyTable1 union all select FSerialID from MyTable2 --返回两个表的FSerialID字段,这是返回的是1001、1002、1001、1003,相同的1001也要返回。Top、Distinct、Count、Exists、Between的使用
1、Top,返回满足条件的前多少条记录,一般与Order By子句配合使用
select top 记录条数 字段列表 from 表select top 百分比 percent 字段列表 from 表select top 1000 FSerialID, FName from MyTable1 order by FSerialID asc --返回MyTable1的前1000条记录。select top 100 percent FSerialID, FName from MyTable1 order by FSerialID desc --返回MyTable1前100%条记录。
2、Distinct,返回记录不重复的记录集。
select distinct 字段列表 from 表eg. select distinct FName from MyTable1 --返回MyTable1表中不重复的名称。
3、Count,返回字段或记录条数。
select Count(*) from 表select Count(字段) from 表eg. select count(*) from MyTable1 --返回MyTable1的记录条数。eg. select count(distinct FName) from MyTable1 --返回MyTable1中不重复的姓名个数。
4、Exists(子查询),包含之意,即圆括弧内的条件有返回至少一条记录即认为条件满足,查询中用的不太多,主要用在操作语句中。
select 字段列表 from 表 where exists (另一条查询语句)eg. select t1.* from MyTable1 where exists( select t2.* from MyTable2 t2 where t1.FSerialID>t2.FSerialID) --返回存在满足MyTable1的FSerialID大于MyTable2的FSerialID的部分记录)
5、Between ... And ...,列出...与...之间的部分、同时包含上下标部分。上标和下标可以是数字、也可以是字符串。
select 字段列表 from 表 where 字段 Between 值1 And 值2select * from MyTable1 where FSerialID between 1001 and 1003 --返回FSerialID在1001与1003之间的记录,包含1001和1003两条。
以上都是一些日常大家可能会经常用到的SQL语法,希望对您有所帮助。
下一篇,我将着重说说与Group By相关的查询应用,敬请期待。
*本文部分图片来源于网络,如有侵权,请及时联系我们删除
一、求一些sql数据库数据查询的一些经典语句
1、说明:创建数据库CREATE DATABASE database-name 2、说明:删除数据库drop database dbname3、说明:备份sql server--- 创建 备份数据的 deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'--- 开始 备份BACKUP DATABASE pubs TO testBack 4、说明:创建新表create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)--建表、建约束、关系
create table tableok
(
col1 int, col2_notnull int not null,
col3_default nchar(1) not null default('男'),
--默认男
col4_default datetime not null default(getdate()),
--默认得到系统时间
col5_check int not null check(col5_check>=18 and col5_check 1;
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明:in 的使用方法select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明:两张关联表,删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明:四表联查问题:select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明:一条sql 语句搞定数据库分页select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段具体实现:关于数据库分页:
declare @start int,@end int
@sql nvarchar(600)
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql
注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引)
14、说明:前10条记录select top 10 * form table1 where 范围
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明:随机取出10条数据select top 10 * from tablename order by newid()
18、说明:随机选择记录select newid()
19、说明:删除重复记录1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)2),select distinct * into temp from tablename delete from tablename insert into tablename select * from temp评价: 这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作3),例如:在一个外部表中导入数据,由于某些原因第一次只导入了一部分,但很难判断具体位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段
alter table tablename--添加一个自增列add column_b int identity(1,1) delete from tablename where column_b not in(select max(column_b) from tablename group by column1,column2,...)alter table tablename drop column column_b
20、说明:列出数据库里所有的表名select name from sysobjects where type='U' // U代表用户
21、说明:列出表里的所有的列名select name from syscolumns where id=object_id('TableName')
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type显示结果:type vender pcs电脑 A 1电脑 A 1光盘 B 2光盘 A 2手机 B 3手机 C 3
23、说明:初始化表table1
TRUNCATE TABLE table1
24、说明:选择从10到15的记录select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
二、数据库常用sql语句有哪些
数据库常用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 score260);
10、查询没有学全所有课的同学的学号、姓名;
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) =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&UML (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],[ 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(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=70 AND SC.S#=student.S#;
37、查询不及格的课程,并按课程号从大到小排列
select c# from sc where scor e80 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 score2)group by S#;
49、检索“004”课程分数小于60,按分数降序排列的同学学号
select S# from SC where C#='004'and score
爱资源吧版权声明:以上文中内容来自网络,如有侵权请联系删除,谢谢。