SERVER数据库学习总计,存储进度和触发器的采纳

实验案例一:验证索引的功用

  对于SQL SERFVER数据库也学了有说话了,自己也对友好所学做了有些总计。

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#SERVER数据库学习总计,存储进度和触发器的采纳。=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 <score;
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&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)

Student(S#,Sname,Sage,Ssex) 学生表

1、首先创建一个数据量大的表,名称为“学生表”,分别有三列,学号,姓名和班级,如下图所示,学号为活动编号,班级为默许值“一班”。

   大家率先学习数据库设计的有些知识点和用SQL语句建库。

  • 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语句大全
    一、基础
    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],..)
    基于已有的表成立新表: A:create table tab_new like tab_old
    (使用旧表创造新表)B:create table tab_new as select col1,col2… from
    tab_old definition only5、表明:删除新表drop table tabname
    6、表达:增添一个列Alter table tabname add column col
    type注:列伸张后将无法去除。DB2中列加上后数据类型也无法更改,唯一能更改的是增多varchar类型的长度。7、表达:添加主键:
    Alter table tabname add primary key(col) 表明:删除主键: Alter table
    tabname drop primary key(col) 8、表明:创制索引:create [unique] index
    idxname on tabname(col….) 删除索引:drop index
    idxname注:索引是不行更改的,想改变必须删除重新建。9、表明:创制视图:create
    view viewname as select statement 删除视图:drop view
    viewname10、表达:几个简易的中坚的sql语句拔取:select * from table1
    where 范围插队:insert into table1(field1,field2)
    values(value1,value2)删除:delete from table1 where 范围更新:update
    table1 set field1=value1 where 范围查找:select * from table1 where
    field1 like ’%value1%’ —like的语法很精细,查资料!排序:select * from
    table1 order by field1,field2 [desc]总数:select count as totalcount
    from table1求和:select sum(field1) as sumvalue from table1平均:select
    avg(field1) as avgvalue from table1最大:select max(field1) as maxvalue
    from table1最小:select min(field1) as minvalue from
    table111、表达:多少个高级查询运算词A: UNION 运算符 UNION
    运算符通过整合其他三个结实表(例如 TABLE1 和
    TABLE2)并消去表中其余重复行而派生出一个结实表。当 ALL 随 UNION
    一起利用时(即 UNION
    ALL),不清除重复行。三种景况下,派生表的每一行不是发源 TABLE1就是发源
    TABLE2。 B: EXCEPT 运算符 EXCEPT运算符通过包涵持有在 TABLE1 中但不在
    TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT
    一起利用时 (EXCEPT ALL),不清除重复行。C: INTERSECT
    运算符INTERSECT运算符通过只包罗 TABLE1 和 TABLE2
    中都一些行并消除所有重复行而派生出一个结实表。当 ALL随 INTERSECT
    一起利用时 (INTERSECT ALL),不免除重复行。
    注:使用运算词的多少个查询结果行必须是同等的。 12、表达:使用外连接
    A、left (outer) join:
    左外连接(左连接):结果集几囊括连接表的匹配行,也囊括左连接表的有着行。
    SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a =
    b.cB:right (outer) join:
    右外接连(右连接):结果集既蕴含连接表的匹配连接行,也囊括右连接表的保有行。
    C:full/cross (outer) join:
    全外连接:不仅包罗符号连接表的匹配行,还包涵五个三番五次表中的所有记录。12、分组:Group
    by: 一张表,一旦分组 完结后,查询后只好获得组相关的信息。
    组相关的信息:(总括音信) count,sum,max,min,avg 分组的正儿八经)
    在SQLServer中分组时:不可能以text,ntext,image类型的字段作为分组依据在selecte计算函数中的字段,不可以和一般性的字段放在一起;
    13、对数据库举行操作: 分离数据库:
    sp_detach_db;附加数据库:sp_attach_db
    后接注解,附加须求完整的门径名14.什么修改数据库的称谓:sp_renamedb
    ‘old_name’, ‘new_name’
    二、提升
    1、表达:复制表(只复制结构,源表名:a 新表名:b) (Access可用)法一:select
    * into b from a where 1<>1(仅用于SQlServer)法二:select top 0
    * into b from a2、表达:拷贝表(拷贝数据,源表名:a 目的表名:b)
    (Access可用)insert into b(a, b, c) select d,e,f from b;
    3、表明:跨数据库之间表的正片(具体数量利用相对路径) (Access可用)insert
    into b(a, b, c) select d,e,f from b in ‘具体数据库’ where
    条件例子:..from b in ‘”&Server.MapPath(“.”)&”\data.mdb” &”‘ where..
    4、说明:子查询(表名1:a 表名2:b)select a,b,c from a where a IN (select
    d from b ) 或者: select a,b,c from a where a IN (1,2,3)
    5、表明:突显作品、提交人和尾声回复时间select
    a.title,a.username,b.adddate from table a,(select max(adddate) adddate
    from table where table.title=a.title) b
    6、表明:外接连查询(表名1:a 表名2:b)select a.a, a.b, a.c, b.c, b.d,
    b.f from a LEFT OUT JOIN b ON a.a = b.c
    7、表明:在线视图查询(表名1:a )select * from (SELECT a,b,c FROM a) T
    where t.a > 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
    三、技巧
    1、1=1,1=2的运用,在SQL语句组合时用的较多
    “where 1=1” 是意味着拔取一切 “where 1=2”全部不选,如:if @strWhere !=”
    beginset @strSQL = ‘select count() as Total from [‘ + @tblName + ‘]
    where ‘ + @strWhere endelse beginset @strSQL = ‘select count(
    ) as Total
    from [‘ + @tblName + ‘]’ end
    咱俩得以一贯写成
    漏洞极度多!未找到目录项。set @strSQL = ‘select count() as Total from [‘ +
    @tblName + ‘] where 1=1 安定 ‘+ @strWhere 2、减弱数据库–重建索引DBCC
    REINDEXDBCC INDEXDEFRAG–裁减数据和日志DBCC SHRINKDBDBCC SHRINKFILE
    3、压缩数据库dbcc shrinkdatabase(dbname)
    4、转移数据库给新用户以已存在用户权限exec sp_change_users_login
    ‘update_one’,’newname’,’oldname’go
    5、检查备份集RESTORE VERIFYONLY from disk=’E:\dvbbs.bak’
    6、修复数据库ALTER DATABASE [dvbbs] SET SINGLE_USERGODBCC
    CHECKDB(‘dvbbs’,repair_allow_data_loss) WITH TABLOCKGOALTER DATABASE
    [dvbbs] SET MULTI_USERGO
    7、日志清除SET NOCOUNT ONDECLARE @LogicalFileName sysname, @马克斯Minutes
    INT, @NewSize INT
    USE tablename — 要操作的数据库名SELECT @LogicalFileName =
    ‘tablename_log’, — 日志文件名@MaxMinutes = 10, — Limit on time
    allowed to wrap log. @NewSize = 1 — 你想设定的日记文件的尺寸(M)
    Setup / initializeDECLARE @OriginalSize intSELECT @OriginalSize = size
    FROM sysfiles WHERE name = @LogicalFileNameSELECT ‘Original Size of ‘ +
    db_name() + ‘ LOG is ‘ + CONVERT(VARCHAR(30),@OriginalSize) + ‘ 8K
    pages or ‘ + CONVERT(VARCHAR(30),(@OriginalSize
    8/1024)) + ‘MB’ FROM
    sysfiles WHERE name = @LogicalFileNameCREATE TABLE DummyTrans
    (DummyColumn char (8000) not null)
    DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)SELECT
    @StartTime = GETDATE(), @TruncLog = ‘BACKUP LOG ‘ + db_name() + ‘ WITH
    TRUNCATE_ONLY’
    DBCC SHRINKFILE (@LogicalFileName, @NewSize)EXEC (@TruncLog)– Wrap the
    log if necessary.WHILE @MaxMinutes > DATEDIFF (mi, @StartTime,
    GETDATE()) — time has not expired AND @OriginalSize = (SELECT size FROM
    sysfiles WHERE name = @LogicalFileName) AND (@OriginalSize * 8 /1024)
    > @NewSize BEGIN — Outer loop.SELECT @Counter = 0 WHILE ((@Counter
    < @OriginalSize / 16) AND (@Counter < 50000)) BEGIN — update
    INSERT DummyTrans VALUES (‘Fill Log’) DELETE DummyTrans SELECT @Counter
    = @Counter + 1 END EXEC (@TruncLog) ENDSELECT ‘Final Size of ‘ +
    db_name() + ‘ LOG is ‘ + CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +
    CONVERT(VARCHAR(30),(size8/1024)) + ‘MB’ FROM sysfiles WHERE name =
    @LogicalFileNameDROP TABLE DummyTransSET NOCOUNT OFF
    8、表达:更改某个表exec sp_changeobjectowner ‘tablename’,’dbo’
    9、存储更改所有表
    CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch@OldOwner as
    NVARCHAR(128),@NewOwner as NVARCHAR(128)AS
    DECLARE @Name as NVARCHAR(128)DECLARE @Owner as NVARCHAR(128)DECLARE
    @OwnerName as NVARCHAR(128)
    DECLARE curObject CURSOR FOR select ‘Name’ = name, ‘Owner’ =
    user_name(uid)from sysobjectswhere user_name(uid)=@OldOwnerorder by
    name
    OPEN curObjectFETCH NEXT FROM curObject INTO @Name,
    @OwnerWHILE(@@FETCH_STATUS=0)BEGIN if @Owner=@OldOwner begin set
    @OwnerName = @OldOwner + ‘.’ + rtrim(@Name) exec sp_changeobjectowner
    @OwnerName, @NewOwnerend– select @name,@NewOwner,@OldOwner
    FETCH NEXT FROM curObject INTO @Name, @OwnerEND
    close curObjectdeallocate curObjectGO
    10、SQL SERVER中一向循环写入数据declare @i intset @i=1while
    @i<30begin insert into test (userid) values(@i) set
    @i=@i+1end案例:有如下表,要求就裱中具有沒有及格的成績,在每便增長0.1的基礎上,使他們剛好及格:
    Name score
    Zhangshan 80
    Lishi 59
    Wangwu 50
    Songquan 69
    while((select min(score) from tb_table)<60)
    begin
    update tb_table set score =score
    1.01
    where score<60
    if (select min(score) from tb_table)>60
    break
    else
    continue
    end
    多少开发-经典
    1.按姓氏笔画排序:Select * From TableName Order By CustomerName Collate
    Chinese_PRC_Stroke_ci_as //从少到多
    2.数据库加密:select encrypt(‘原始密码’)select
    pwdencrypt(‘原始密码’)select pwdcompare(‘原始密码’,’加密后密码’) =
    1–相同;否则不均等 encrypt(‘原始密码’)select
    pwdencrypt(‘原始密码’)select pwdcompare(‘原始密码’,’加密后密码’) =
    1–相同;否则不雷同
    3.取回表中字段:declare @list varchar(1000),@sql nvarchar(1000) select
    @list=@list+’,’+b.name from
    sysobjects a,syscolumns b where
    a.id=b.id and
    a.name=’表A’set
    @sql=’select ‘+right(@list,len(@list)-1)+’ from 表A’ exec (@sql)
    4.查看硬盘分区:EXEC master..xp_fixeddrives
    5.比较A,B表是或不是等于:if (select checksum_agg(binary_checksum()) from
    A) = (select checksum_agg(binary_checksum(
    )) from B)print
    ‘相等’elseprint ‘不相等’
    6.杀掉所有的轩然大波探察器进度:DECLARE hcforeach CURSOR GLOBAL FOR SELECT
    ‘kill ‘+RTRIM(spid) FROM master.dbo.sysprocessesWHERE program_name
    IN(‘SQL profiler’,N’SQL 事件探查器’)EXEC sp_msforeach_worker ‘?’
    7.笔录搜索:开头到N条记录Select Top N * From
    表——————————-N到M条记录(要有主索引ID)Select Top M-N
    * From 表 Where ID in (Select Top M ID From 表) Order by ID
    Desc———————————-N到最终记录Select Top N * From 表
    Order by ID Desc案例例如1:一张表有一万多条记下,表的率先个字段 RecID
    是自增进字段, 写一个SQL语句, 找出表的第31到第40个记录。
    select top 10 recid from A where recid not in(select top 30 recid from
    A)
    浅析:假诺那样写会发出一些难点,如若recid在表中留存逻辑索引。
    select top 10 recid from A where……是从索引中找找,而后边的select top 30
    recid from
    A则在数据表中检索,这样由于索引中的顺序有可能和数目表中的差别,那样就招致查询到的不是自然的欲得到的数目。
    解决方案
    1,用order by select top 30 recid from A order by ricid
    即使该字段不是自增进,就会现身难点
    2,在更加子查询中也加条件:select top 30 recid from A where
    recid>-1
    例2:查询表中的最终以条记下,并不知道那一个表共有多少数量,以及表结构。set
    @s = ‘select top 1 * from T where pid not in (select top ‘ +
    str(@count-1) + ‘ pid from T)’
    print @s exec sp_executesql @s
    9:获取当前数据库中的所有用户表select Name from sysobjects where
    xtype=’u’ and status>=0
    10:获取某一个表的拥有字段select name from syscolumns where
    id=object_id(‘表名’)
    select name from syscolumns where id in (select id from sysobjects where
    type = ‘u’ and name = ‘表名’)
    两种办法的法力等同
    11:查看与某一个表相关的视图、存储进度、函数select a.* from sysobjects
    a, syscomments b where a.id = b.id and b.text like ‘%表名%’
    12:查看当前数据库中兼有存储进度select name as 存储进程名称 from
    sysobjects where xtype=’P’
    13:查询用户创制的装有数据库select * from master..sysdatabases D where
    sid not in(select sid from master..syslogins where name=’sa’)或者select
    dbid, name AS DB_NAME from master..sysdatabases where sid <>
    0x01
    14:查询某一个表的字段和数据类型select column_name,data_type from
    information_schema.columnswhere table_name = ‘表名’
    15:分化服务器数据库之间的数量操作
    –创造链接服务器
    exec sp_addlinkedserver ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘,
    ‘远程服务器名或ip地址 ‘
    exec sp_addlinkedsrvlogin ‘ITSV ‘, ‘false ‘,null, ‘用户名 ‘, ‘密码 ‘
    –查询示例
    select * from ITSV.数据库名.dbo.表名
    –导入示例
    select * into 表 from ITSV.数据库名.dbo.表名
    –未来不再采纳时去除链接服务器
    exec sp_dropserver ‘ITSV ‘, ‘droplogins ‘
    –连接远程/局域网数据(openrowset/openquery/opendatasource)
    –1、openrowset
    –查询示例
    select * from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘; ‘密码
    ‘,数据库名.dbo.表名)
    –生花费地表
    select * into 表 from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名
    ‘; ‘密码 ‘,数据库名.dbo.表名)
    –把地面表导入远程表
    insert openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘; ‘密码
    ‘,数据库名.dbo.表名)
    select from 本地表
    –更新本地表
    update b
    set b.列A=a.列A
    from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘; ‘密码
    ‘,数据库名.dbo.表名)as a inner join 本地表 b
    on a.column1=b.column1
    –openquery用法须求创设一个三番五次
    –首先创制一个接连创设链接服务器
    exec sp_addlinkedserver ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘,
    ‘远程服务器名或ip地址 ‘
    –查询
    select \

    FROM openquery(ITSV, ‘SELECT * FROM 数据库.dbo.表名 ‘)
    –把本地表导入远程表
    insert openquery(ITSV, ‘SELECT * FROM 数据库.dbo.表名 ‘)
    select * from 本地表
    –更新本地表
    update b
    set b.列B=a.列B
    FROM openquery(ITSV, ‘SELECT * FROM 数据库.dbo.表名 ‘) as a
    inner join 本地表 b on a.列A=b.列A
    –3、opendatasource/openrowset
    SELECT *
    FROM opendatasource( ‘SQLOLEDB ‘, ‘Data Source=ip/ServerName;User
    ID=登陆名;Password=密码 ‘ ).test.dbo.roy_ta
    –把地面表导入远程表
    insert opendatasource( ‘SQLOLEDB ‘, ‘Data Source=ip/ServerName;User
    ID=登陆名;Password=密码 ‘).数据库.dbo.表名
    select * from 本地表
    SQL Server基本函数
    1.字符串函数 长度与分析用
    1,datalength(Char_expr)
    重回字符串蕴含字符数,但不含有前面的空格2,substring(expression,start,length)
    取子串,字符串的下标是从“1”,start为开始地方,length为字符串长度,实际运用中以len(expression)取得其尺寸3,right(char_expr,int_expr)
    再次来到字符串左边第int_expr个字符,还用left于之相反4,isnull(
    check_expression , replacement_value
    )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类
    5,Sp_addtype自定義數據類型例如:EXEC sp_addtype birthday, datetime,
    ‘NULL’
    6,set nocount {on|off}
    使重返的结果中不包括关于受 Transact-SQL
    语句影响的行数的信息。假设存储进程中涵盖的一些话语并不回来许多实际上的数码,则该装置由于大量滑坡了网络流量,因而可分明增强质量。SET
    NOCOUNT 设置是在实施或运行时设置,而不是在解析时设置。SET NOCOUNT 为 ON
    时,不回去计数(表示受 Transact-SQL 语句影响的行数)。
    SET NOCOUNT
    为 OFF 时,再次来到计数
    常识
    在SQL查询中:from后最多可以跟多少张表或视图:256在SQL语句中出现 Order
    by,查询时,先排序,后取在SQL中,一个字段的最大容量是8000,而对于nvarchar(4000),由于nvarchar是Unicode码。
    SQLServer2000
    共同复制技术完结步骤
    一、 预备工作
    1.发表服务器,订阅服务器都创立一个同名的windows用户,并安装同样的密码,做为公布快照文件夹的有效性访问用户–管理工具–总括机管理–用户和组–右键用户–新建用户–建立一个隶属于administrator组的登陆windows的用户(SynUser)2.在发布服务器上,新建一个共享目录,做为公布的快照文件的存放目录,操作:
    自我的总计机–D:\ 新建一个目录,名为: PUB
    –右键这几个新建的目录–属性–共享–采纳”共享该文件夹”–通过”权限”按纽来设置具体的用户权限,有限援助第一步中开创的用户(SynUser)
    具有对该公文夹的具有权限
    –确定3.安装SQL代理(SQLSERVERAGENT)服务的启航用户(发表/订阅服务器均做此设置)
    始于–程序–管理工具–服务
    –右键SQLSERVERAGENT–属性–登陆–选取”此账户”–输入或者选拔第一步中开创的windows登录用户名(SynUser)–“密码”中输入该用户的密码4.装置SQL
    Server身份验证情势,解决连接时的权杖难点(发表/订阅服务器均做此设置)
    商厦管理器
    –右键SQL实例–属性–安全性–身份验证–选拔”SQL Server 和
    Windows”–确定5.在揭破服务器和订阅服务器上相互注册
    店铺管理器
    –右键SQL Server组–新建SQL
    Server注册…–下一步–可用的服务器中,输入你要登记的长途服务器名
    –添加–下一步–连接使用,拔取首个”SQL
    Server身份验证”–下一步–输入用户名和密码(SynUser)–下一步–接纳SQL
    Server组,也可以创立一个新组–下一步–已毕6.对于只好用IP,不能够用计算机名的,为其注册服务器别名(此步在执行中没用到)
    (在连接端配置,比如,在订阅服务器上布署来说,服务器名称中输入的是揭发服务器的IP)
    发端–程序–Microsoft SQL Server–客户端互连网实用工具
    –别名–添加–互联网库拔取”tcp/ip”–服务器别名输入SQL服务器名–连接参数–服务器名称中输入SQL服务器ip地址–如果你改改了SQL的端口,打消选取”动态控制端口”,并输入相应的端口号
    二、 正式配置
    1、配置发布服务器
    打开集团管理器,在发布服务器(B、C、D)上进行以下步骤:
    (1)
    从[工具]下拉菜单的[复制]子菜单中挑选[安顿公布、订阅服务器和散发]并发表局公布和散发向导(2)
    [下一步] 选取分发服务器
    可以选用把宣布服务器自己视作分发服务器或者其余sql的服务器(选取自己)(3)
    [下一步] 设置快照文件夹
    使用默认\servername\Pub
    (4) [下一步] 自定义配置
    可以接纳:是,让自己设置分发数据库属性启用发布服务器或设置发表设置否,使用下列默许设置(推荐)
    (5) [下一步] 设置分发数据库名称和职位 拔取默许值(6) [下一步]
    启用揭橥服务器 接纳作为公布的服务器(7) [下一步]
    选拔需求表露的数据库和揭穿项目(8) [下一步] 接纳注册订阅服务器(9)
    [下一步] 落成安顿
    2、创造出版物
    揭橥服务器B、C、D上
    (1)从[工具]菜单的[复制]子菜单中拔取[创制和治本公布]一声令下(2)选用要创立出版物的数据库,然后单击开创发表在[创立发布先河]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的始末是复制的几个品类。大家前几日选第四个也就是默认的快照发表(其余多个大家可以去探访接济)(4)单击[下一步]系统要求指定可以订阅该公告的数据库服务器类型,SQLSERVER允许在分歧的数据库如
    orACLE或ACCESS之间开展多少复制。
    不过在那边我们挑选运行”SQL SERVER 2000″的数据库服务器
    (5)单击[下一步]系统就弹出一个概念小说的对话框也就是挑选要出版的表
    小心: 若是面前选拔了事情发表 则再这一步中只可以采取带有主键的表
    (6)选拔揭橥名称和描述
    (7)自定义发表属性 向导提供的精选:
    是 我将自定义数据筛选,启用匿名订阅和或任何自定义属性否
    按照指定方式创立发布 (提议采纳自定义的艺术)
    (8)[下一步] 选取筛选发布的方法
    (9)[下一步]
    能够拔取是不是同意匿名订阅1)假设选用署名订阅,则要求在揭橥服务器上添加订阅服务器
    方法:
    [工具]->[复制]->[配置公布、订阅服务器和分发的品质]->[订阅服务器]
    中添加否则在订阅服务器上呼吁订阅时会出现的升迁:改公布差别意匿名订阅如若仍旧需求匿名订阅则用以下解决办法
    [店铺管理器]->[复制]->[颁发内容]->[属性]->[订阅选项]
    拔取允许匿名请求订阅2)纵然选拔匿名订阅,则安顿订阅服务器时不会并发上述提醒(10)[下一步]
    设置快照 代理程序调度(11)[下一步] 完毕安排
    当成功出版物的创导后创建出版物的数据库也就成为了一个共享数据库有多少
    srv1.库名..author有字段:id,name,phone,
    srv2.库名..author有字段:id,name,telphone,adress
    要求:
    srv1.库名..author增加记录则srv1.库名..author记录伸张srv1.库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新
    /
    –大致的处理步骤–1.在 srv1 上创办连接服务器,以便在 srv1 中操作
    srv2,完毕同步exec sp_addlinkedserver
    ‘srv2′,”,’SQLOLEDB’,’srv2的sql实例名或ip’ exec sp_addlinkedsrvlogin
    ‘srv2′,’false’,null,’用户名’,’密码’
    go
    –2.在 srv1 和 srv2 那两台电脑中,启动
    msdtc(分布式事务处理服务),并且安装为自发性启动。我的电脑–控制面板–管理工具–服务–右键
    Distributed Transaction
    Coordinator–属性–启动–并将开行项目设置为电动启动go
    –然后创设一个功课定时调用地方的一路处理存储进度就行了
    商家管理器
    –管理–SQL
    Server代理–右键作业–新建作业–“常规”项中输入作业名称–“步骤”项–新建–“步骤名”中输入步骤名–“类型”中接纳”Transact-SQL
    脚本(TSQL)” –“数据库”接纳执行命令的数据库–“命令”中输入要履行的言语:
    exec p_process
    –确定–“调度”项–新建调度–“名称”中输入调度名称–“调度项目”中甄选你的功课执行安顿–要是采取”反复出现”
    –点”更改”来安装你的时刻布置
    然后将SQL
    Agent服务启动,并安装为自发性启动,否则你的课业不会被实施设置方法:我的总括机–控制面板–管理工具–服务–右键
    SQLSERVERAGENT–属性–启动项目–选取”自动启动”–确定.
    –3.贯彻联机处理的方法2,定时同步
    –在srv1中成立如下的联手处理存储进程
    create proc p_processas
    –更新修改过的数码
    update b set name=i.name,telphone=i.telphone
    from srv2.库名.dbo.author b,author i
    where b.id=i.id and(b.name <> i.name or b.telphone <>
    i.telphone)
    –插入新增的多少insert srv2.库名.dbo.author(id,name,telphone)
    select id,name,telphone from author iwhere not exists(
    select \
    from srv2.库名.dbo.author where id=i.id)
    –删除已经去除的数量(即使需求的话)
    delete b
    from srv2.库名.dbo.author b
    where not exists(select * from author where id=b.id)
    go

Course(C#,Cname,T#) 课程表

997755.com澳门葡京 1

  设计数据库步骤:需求分析阶段,概要设计阶段,详细设计阶段,

SC(S#,C#,score) 成绩表

2、向表中插入大量数据,数据越来越多,验证索引的功能越好。

   建数据库的SQL语句如下(包蕴了一旦有该数据库先删除在建立数据库)

Teacher(T#,Tname) 教师表

采纳语句达成:While 1>0  Insert
into 学生表(姓名)  values(‘于美丽’)

use master
GO
if exists(select * from sysdatabases where name=’Wages’)
DROP database Wages
CREATE DATABASE Wages
 ON
 (
  NAME=’Wages_data’,
  FILENAME=’e:\project\Wages_data.mdf’,
  SIZE=5mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= ‘Wages_log’,
  FILENAME=’e:\project\Wages_log.ldf’,
  SIZE=3mb,
  FILEGROWTH=15%
 )
GO

问题:

地点语句是一个死循环,除非强制为止,假若1大于0就会一贯向表中插入姓名

为了创立优质的数据库需满意三大范式。

1、查询“001”课程比“002”课程成绩高的装有学生的学号;

如下图所示:

上边是创造表的SQL语句(包蕴了一旦有该表先删除在建表)

select a.S# from (select s#,score from SC where C#=’001′) a,(select
s#,score

997755.com澳门葡京 2

USE Wages
GO
if exists(select * from sysobjects where name=’WageInfo’)
DROP table WageInfo
CREATE TABLE WageInfo 
(
  CompanyID INT primary key IDENTITY(1,1),
  CompanyName varchar(50) NOT NULL,
  Poste varchar(50) NOT NULL,
)
GO
if exists(select * from sysobjects where name=’StudentInfo’)
DROP table StudentInfo
CREATE TABLE StudentInfo
(
  ID  INT primary key IDENTITY(1,1),
  Name  VARCHAR(50) NOT NULL,
  CompanyID INT ,
  PosteDate  DATETIME ,
  Wage  int,
)
GO

from SC where C#=’002′) b

等候5分钟左右,打开表的品质,查看表的行数,当前为1032363,如下图所示:

上边是开创约束

where a.score>b.score and a.s#=b.s#;

997755.com澳门葡京 3

语法如下

2、查询平均战绩超越60分的同窗的学号和平均成绩;

3、 使用语句查询第900000行的多少,Select
* from 学生表 Where 学号=900000

alter table 表名

select S#,avg(score)

997755.com澳门葡京 4

add constraint 约束名 约束类型 具体的自律表明

from sc

4、打开“sql server
profiler ”工具举办跟踪,如下图所示:

示例

group by S# having avg(score) >60;

打开“sql server
profiler ”工具查看跟踪的音信,发现查询时间很长,cpu工作了265飞秒,reads:读了8649次,writes:写了10次,duration:计算费用2336阿秒完毕查询。

创办外键约束

3、查询所有同学的学号、姓名、选课数、总成绩;

997755.com澳门葡京 5

alter table StudentInfo
add constraint pk_CompanyID foreign key(CompanyID) references
WageInfo(CompanyID)
GO

select Student.S#,Student.Sname,count(SC.C#),sum(score)

为了上边分析文件更确切,多执行四次Select*
from 学生表 Where 学号=900000

布置数据说话如下

from Student left Outer join SC on Student.S#=SC.S#

接下来把跟踪的结果保存在桌面上:

insert into WageInfo(CompanyName, Poste)values
(‘微软’,’职员’),
(‘百度’,’职员’),
(‘腾讯’,’职员’),
(‘爱奇艺’,’职员’),
(‘华为’,’职员’)
insert into StudentInfo(Name, CompanyID, PosteDate, Wage)values
(‘张三’,1,’2016-05-05′,2500),
(‘李四’,2,’2016-05-06′,2600),
(‘王五’,3,’2016-05-07′,3000),
(‘赵二’,4,’2016-05-08′,4000),
(‘钱一’,5,’2016-05-09′,5000)
insert into StudentInfo(Name, CompanyID, PosteDate,
Wage)values(‘钱二’,null,’2016-05-09′,NULL)

group by Student.S#,Sname

 997755.com澳门葡京 6

下一场大家学习了变量,变量分全局变量和一部分变量。

4、查询姓“李”的老师的个数;

5、打开“数据库引擎优化顾问”,添加跟踪文件,进行剖析,发现索引提议,需求树立目录。

创办变量语法如下是

select count(distinct(Tname))

 997755.com澳门葡京 7

declare @变量名 数据类型

from Teacher

留神拔取benet数据库中的学生表,然后点击“初叶分析”

有些变量有两种赋值方法

where Tname like ‘李%’;

 997755.com澳门葡京 8

set @变量名=value

5、查询没学过“叶平”老师课的同桌的学号、姓名;

索引类型为clusterd(聚集索引),索引列为“学号”。

select @变量名=value

select Student.S#,Student.Sname

6、根据“数据库引擎优化顾问”的目录提议创制聚集索引,并且拔取“唯一”

分化是select可三遍对多少个变量赋值,set只好一次对一个变量赋值。

from Student

997755.com澳门葡京 9

全局变量只可以用不能友好创造和赋值!!!

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=’叶平’);

7、再一次实施Select *
from 学生表Where 学号=900000

出口语句

6、查询学过“001”并且也学过数码“002”课程的校友的学号、姓名;

 

print 和select

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′);

8、打开sql server
profiler查看跟踪的光阴,发现查询时间大幅升高,表明索引可以增进查询速度。

use MySchool

7、查询学过“叶平”老师所教的所有课的校友的学号、姓名;

997755.com澳门葡京 10

go

select S#,Sname

发现一起时间为1微秒,大致忽略不计

select * from StuInfos

from Student

 

1.班级表   班级编号 (主键)   班级名   (长度固定3位)   班级人数 (默许30)

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=’叶平’));

试验案例二:分别磨炼创设种种索引

  if exists(select * from sysobjects where name=’Classes’)

8、查询课程编号“002”的成就比课程编号“001”课程低的所有同学的学号、姓名;

1、创设聚集索引

drop table Classes

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

脚下tstudent表中绝非其余索引也没有主键

  go  

from Student,SC where Student.S#=SC.S# and C#=’001′) S_2 where
score2

 997755.com澳门葡京 11

 create table Classes  

9、查询所有科目战绩小于60分的校友的学号、姓名;

为tstudent表创造聚集索引

 (   clsid int identity(1,1), –班级编号  

select S#,Sname

997755.com澳门葡京 12

‘ clsname varchar(3), –班级名称   clsnums int –班级人数   )   

from Student

入选studentID,单击左上侧的主键按钮

go   

where S# not in (select Student.S# from Student,SC where S.S#=SC.S#
and score>60);

 997755.com澳门葡京 13

‘alter table Classes

10、查询没有学全所有课的同室的学号、姓名;

为Tstuden表的studentID创设主键就同时创立了聚集索引

add constraint pk_clsid_Classes   primary key(clsid)  

select Student.S#,Student.Sname

997755.com澳门葡京 14

 alter table Classes

from Student,SC

2、创建组合索引

为成绩表创立组合索引,因为一个学童不可以为一门学科录入四遍成绩,所以将培养表中的studentID和subjectID创制组合索引

 997755.com澳门葡京 15

997755.com澳门葡京 16

997755.com澳门葡京 17

997755.com澳门葡京 18

解决办法:

菜单栏—-工具—-选项

找到设计器(designers),将标记处的勾去掉,单击“确定”

 997755.com澳门葡京 19

诸如此类组合索引就创办成功了。

 997755.com澳门葡京 20

add constraint ck_clsname_Classes   check(len(clsname)=3)  

where Student.S#=SC.S# group by Student.S#,Student.Sname having
count(C#) <(select count(C#) from Course);

3、创立唯一索引

创办唯一性约束的时候就会创设唯一性索引,不可能有重复值

为Tstudent表创造唯一非聚集索引

create unique nonclustered index U_cardID on TStudent(cardID)

997755.com澳门葡京 21

 alter table Classes

11、查询至少有一门课与学号为“1001”的校友所学相同的同桌的学号和人名;

4、创造非聚集索引—可以有重复值

为Tstudent表的姓名列创立非聚集索引

997755.com澳门葡京 22

利用命令查看表上的目录

Select * from sys.sysindexes where id=(select object_id from sys.all_objects where name=’Tstudent’)

Indid中1表示聚集索引

Indid中2象征唯一非聚集索引

Indidz中3意味着非聚集索引

997755.com澳门葡京 23

使用sp_help 
Tstudent也可以查看到相关表的新闻

997755.com澳门葡京 24

试行案例三:创造视图

办法一:在图形界面下创制视图(以Myschool数据库为例)

成立一个视图,分别来自四个的表的多个列,同样器重命名列,生成的视图名为student_info,如下图所示:

 997755.com澳门葡京 25

经过查询语句查看视图:select   *   
from  student_info

 

格局二:使用语句创设视图(以schoolDB数据库为例)

拓展数据库设计的时候,一个表有很多列,我们可以在表上创立视图,只显示指定的列。

Select语句可以用作一个视图

select Sname,sex,Class from dbo.TStudent where Class=’网络班’

add constraint df_clsnums_Classes   default 30 for clsnums   

select S#,Sname from Student,SC where Student.S#=SC.S# and C# in
select C# from SC where S#=’1001′;

1、成立视图,筛选行和列

create view netstudent

as

select Sname,sex,Class from dbo.TStudent where Class=’网络班’

从视图中追寻数据:

select * from netstudent where sex=’男’

开创视图,更改列的表头,计算列,暴发总括列

selectStudentID,Sname,sex,cardID,Birthday,Email,Class

from dbo.TStudent

997755.com澳门葡京 26

go  

12、查询至少学过学号为“001”同学所有一门课的其余同学学号和姓名;

2、创造视图,更改列的表头

create view V_Tstudent1

as

select StudentID  学号,Sname姓名,sex  性别,cardID  身份证编号,Birthday  生日,Class  班级 from dbo.TStudent

 

select * from V_Tstudent1

 

自此再去询问的时候就格外有利于了。

 

尝试案例四:存储进度

1、常用的体系存储进程

exec sp_databases     
–列出脚下系统中的数据库

exec sp_renamedb  ‘mybank’,’bank’  
–改变数据库名称(单用户访问)

use MySchool

go

exec sp_tables     
                 –当前数据库中可查询对象的列表

exec sp_columns student           
–查看表student中列的新闻

exec sp_help student              
–查看表student的享有音信

exec sp_helpconstraint student      
–查看表student表的自律

exec sp_helptext view_student_result  
–查看视图的话语文本

exec sp_stored_procedures     
–再次来到当前数据库中的存储进度列表

 

 

2、常用的扩展存储进程(在C盘下开创一个文件夹bank)

exec xp_cmdshell  ‘mkdir 
c:\bank’,no_output  –创建文件夹c:\bank

exec xp_cmdshell  ‘dir
c:\bank\’               –查看文件

若是举办不断下边的说话,请开启下边的功力。然后重新实施下边的两条语句。

 

若xp_cmdshell作为服务器安全布署的一部分而被关门,请使用如下语句启用:

exec sp_configure  ‘show advanced
options’, 1   –显示高级配置选项(单引号中的只可以一个空格隔开)

go

reconfigure                                   
–重新配置

go

exec sp_configure 
‘xp_cmdshell’,1                 –打开xp_cmdshell选项

go

reconfigure                          
         –重新配置

go

 

 

3、用户自定义的存储进度(以schoolDB数据库为例,计算互连网管理标准的平均分)

use schoolDB

go

if exists  (select *  from  sysobjects
where name=’usp_getaverageresult’)

drop  procedure
 usp_getaverageresult

go

create  procedure
 usp_getaverageresult

as

declare  @subjectid  nvarchar(4)

select @subjectid=subjectid  from 
dbo.TSubject where subJectName=’互联网管理’

declare  @avg decimal (18,2)

select  @avg=AVG(mark) from  dbo.TScore
wheresubJectID=@subjectid

print ‘网络管理专业平均分是:’+convert(varchar(5),@avg)

go

exec usp_getaverageresult

 

试行案例五:触发器

 

(Myschool数据库为例)

开创触发器(禁止修改admin表中数据):

create trigger  reminder

on  admin

for update

as

print ‘禁止修改,请联系DBA’

rollback transaction

go

 

实施语句,查看错误信息:

update Admin set  LoginPwd=’123′ 
where LoginId=’benet’

select *  from  Admin

 

试验案例六:创制触发器

(Myschool数据库为例)

渴求:创制一个触发器,以确保student表中的数据不会被剔除。

create  trigger stu_del

on  student

for  delete

as

print
  ‘你不抱有删除管理员新闻的权杖’

rollback  transaction

go

 

履行一条delete语句,测试结果。

delete   from  Student where 
StudentName=’喜洋洋

 

小说参考微信公众号:L宝宝聊IT

 insert into Classes select ‘t10’,25 union select ‘t11’,18 union select
‘t12’,23

select distinct SC.S#,Sname

2.外键约束   为学生表添加一个clsid列来表示其班级   alter table StuInfos
add clsid int   外键约束   

from Student,SC

alter table StuInfos with nocheck –不对表现一些数据作限制操作

where Student.S#=SC.S# and C# in (select C# from SC where
S#=’001′);

add constraint fk_StuInfos_Classes    foreign key(clsid) references
Classes(clsid)   

13、把“SC”表中“叶平”老师教的课的成就都改变为此课程的平均成绩;

剔除约束   

update SC set score=(select avg(SC_2.score)

alter table StuInfos drop constraint fk_StuInfos_Classes

from SC SC_2

3.建表同时建约束,老师表   编号     (主键 标识列)   名称     (非空)

where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and
Course.T#=Teacher.T# and Teacher.Tname=’叶平’);

性别     (男或女,默许男)   年龄     (12-60)   电话     (唯一)   班级编号
(外键)   

14、查询和“1002”号的同桌学习的科目完全相同的其余同学学号和人名;

if exists(select * from sysobjects where name=’TeaInfos’)

select S# from SC where C# in (select C# from SC where S#=’1002′)

drop table TeaInfos  

group by S# having count(*)=(select count(*) from SC where
S#=’1002′);

 go  

15、删除学习“叶平”老师课的SC表记录;

 create table TeaInfos  

Delect SC

 ( id int primary key identity(1,1), –编号

from course ,Teacher

name varchar(10) not null, –名称

where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=’叶平’;

sex char(2) check(sex=’男’ or sex=’女’) default ‘男’, –性别

16、向SC表中插入一些记录,那几个记录须要符合以下原则:没有上过编号“003”课程的同学学号、2

age int check(age>=12 and age<=60), –年龄

号课的平均战表;

tel char(11) unique, –电话

Insert SC select S#,’002′,(Select avg(score)

clsid int foreign key references Classes(clsid) –班级   )

from SC where C#=’002′) from Student where S# not in (Select S# from
SC where C#=’002′);

  go

17、按平均战表从高到低显示所有学生的“数据库”、“公司管理”、“日语”三门的课程成绩,按如下格局显示:
学生ID,,数据库,集团管理,丹麦语,有效课程数,有效平均分

一:操作变量 –1– 注脚变量@temp值为1并打印出来 申明变量关键字:

SELECT S# as 学生ID

declare   declare @temp int –声明   

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’004′) AS 数据库

set @temp=1       –赋值  

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’001′) AS 集团管理

 print @temp       –输出   

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=’006′) AS 英语

  declare @temp int=1 –申明的还要赋值  

,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均战表

 print @temp         –输出   

FROM SC AS t

 

GROUP BY S#

赋值格局一:用set 输出方式一:用print   declare @temp int –声明   

ORDER BY avg(t.score)

select @temp=1 –赋值   select @temp   –输出

18、查询各科战表最高和压低的分:以如下情势浮现:课程ID,最高分,最低分

赋值格局二:用select 输出形式二:用select —

SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分

2– 声明多个变量存储’姓名、性别、年龄’,再打印新闻,格式如下:
姓名:杰达姆,性别:男,年龄:18 –注脚

FROM SC L ,SC AS R

declare @name varchar(10),         @sex char(2),         @age int

WHERE L.C# = R.C# and

–赋值 select @name=’杰达姆’,@sex=’男’,@age=18

L.score = (SELECT MAX(IL.score)

–输出 print  ‘姓名:’+@name+’,性别:’+@sex+’,年龄:’   +cast(@age as
varchar)   

FROM SC AS IL,Student AS IM

–convert(varchar,@age)

WHERE L.C# = IL.C# and IM.S#=IL.S#

–五个转型函数:   1.convert — 语法:  convert(类型,数据)   2.cast   

GROUP BY IL.C#)

— 语法:  case(数据 as 类型)

AND

–3– select * from StuInfo

R.Score = (SELECT MIN(IR.score)

打印张秋丽的新闻(来自于student数据库的数码),格式如下: 姓名:张秋丽
性别:男 年龄:18

FROM SC AS IR

–声明 declare  @name varchar(10)=’张秋丽’, @sex char(2), @age int

WHERE R.C# = IR.C#

–赋值:来自于表内 select @sex=stuSex,@age=stuAge from stuinfo  where
stuName=@name

GROUP BY IR.C#

–输出 print ‘姓名:’+@name print ‘性别:’+@sex print ‘年龄:’+cast(@age as
varchar)

);

–4– 查询李文才的左右同班的新闻 declare @seat int select @seat=stuSeat
from stuinfo where stuname=’李文才’ select * from stuinfo  where
stuSeat=@seat-1 or stuSeat=@seat+1

19、按各科平均战表从低到高和及格率的比例从高到低依次

–5– 查询比张秋丽大10岁以上的学习者音信 declare @age int  select
@age=stuAge from stuinfo where stuName=’张秋丽’ select * from stuinfo
where stuAge>=10+@age 

SELECT t.C# AS 课程号,max(course.Cname)AS
课程名,isnull(AVG(score),0) AS 平均成绩

总结: set和select的区别  

,100* SUM(CASE
WHEN isnull(score,0)>=60THEN1ELSE0END)/COUNT(*) AS
及格百分数

 1. set四次只好为一个变量赋值,而select能而且为多个变量赋值  

FROM SC T,Course

 2. set只好赋一个固定的值,而select能动态的赋值(值来自于表内)
select的功力  

where t.C#=course.C#

 1. 查询SQL语句,如: select * from 表名  

GROUP BY t.C#

 2. 赋值操作,   如: select 变量名=值  

ORDER BY100* SUM(CASE
WHEN isnull(score,0)>=60THEN1ELSE0END)/COUNT(*) DESC

 3. 输出操作,   如: select 变量名 二:控制流程结构:if,else

20、查询如下课程平均成绩和及格率的比例(用”1行”突显):
公司管理(001),马克思(002),OO&UML
003),数据库(004

–1– 讲明变量number,并赋值,然后判断是偶数依然奇数,结果如下:
当前值为11,它是一个奇数 declare @number int set @number=12
if(@number%2=0) print ‘该数为偶数’ else print ‘该数为奇数’ –

SELECT SUM(CASE WHEN C# =’001′ THEN score ELSE0END)/SUM(CASE C#
WHEN ‘001’ THEN1ELSE0END) AS 公司管理平均分

-2– 按照输入的岁数显得人的生长阶段
未成年人<18,青年人<30,成年人<60,老年人<100,超人>=100

,100* SUM(CASE WHEN C# = ‘001’ AND score
>=60THEN1ELSE0END)/SUM(CASE WHEN C# = ‘001’
THEN1ELSE0END) AS 企业管理及格百分数

declare @age int set @age=21

,SUM(CASE WHEN C# = ‘002’ THEN score ELSE0END)/SUM(CASE C# WHEN
‘002’ THEN1ELSE0END) AS 马克思平均分

if(@age<18) print ‘未成年人’ else if(@age<30)

,100* SUM(CASE WHEN C# = ‘002’ AND score
>=60THEN1ELSE0END)/SUM(CASE WHEN C# = ‘002’
THEN1ELSE0END) AS 马克思及格百分数

print ‘青年人’

,SUM(CASE WHEN C# = ‘003’ THEN score ELSE0END)/SUM(CASE C# WHEN
‘003’ THEN1ELSE0END) AS UML平均分

else if(@age<60)

,100* SUM(CASE WHEN C# = ‘003’ AND score
>=60THEN1ELSE0END)/SUM(CASE WHEN C# = ‘003’
THEN1ELSE0END) AS UML及格百分数

print ‘成年人’

,SUM(CASE WHEN C# = ‘004’ THEN score ELSE0END)/SUM(CASE C# WHEN
‘004’ THEN1ELSE0END) AS 数据库平均分

else if(@age<100)

,100* SUM(CASE WHEN C# = ‘004’ AND score
>=60THEN1ELSE0END)/SUM(CASE WHEN C# = ‘004’
THEN1ELSE0END) AS 数据库及格百分数

print ‘老年人’

FROM SC

else print ‘超人’

21、查询不同老师所教分裂学科平均分从高到低突显

select * from StuInfo select * from StuExam

SELECT max(Z.T#) AS 教授ID,MAX(Z.Tname) AS 教授姓名,C.C# AS
课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均战表

–3– 计算笔试平均分,若是平均分当先70分打印’突出’
同时出示前三名学员的考查音讯否则      打印’较差’显示后三名学员音信

FROM SC AS T,Course AS C ,Teacher AS Z

declare @avgScore int select @avgScore=AVG(writtenExam) from StuExam
if(@avgScore>=70) begin print ‘本班成绩良好’

where T.C#=C.C# and C.T#=Z.T#

select top 3 * from StuExam order by writtenExam desc end else begin
print ‘本班战绩较差’ select top 3 * from StuExam order by writtenExam
end

GROUP BY C.C#

三:循环结构 –1– 输出九九次’我爱你’ declare @i int=1  while(@i<=99)
begin print ‘第’+convert(varchar,@i)+’我爱你’ set @i+=1 end

ORDER BY AVG(Score) DESC

–2– 总结1-100的拉长和,结果格式:1-100的增进和为:xxx declare  @i int=1,
@sum int=0 while(@i<=100) begin set @sum+=@i set @i+=1 end print
‘1-100的增加和为:’+convert(varchar,@sum)

22、查询如下课程战绩第3名到第6名的学童成绩单:公司管理(001),马克思(002),UML
003),数据库(004

–3– 不停的增强学员笔试战绩2分,让抱有学生的笔试战绩都过关

[学生ID],[学生姓名],集团管理,马克思,UML,数据库,平均成绩

declare @count int –用来记录不及格的人头 while(1=1) begin
–计算不及格的人口 select @count=COUNT(*) from StuExam   where
writtenExam<60 –判断 if(@count=0)   break –退出死循环 else   update
StuExam set writtenExam+=2 end select * from StuExam

SELECT DISTINCT top3

四:多分支语句

SC.S# As 学生学号,

–1– 请宣示变量@name表示学生姓名,对该学员年龄举行划等级 具体如下:
12岁前显示:’小学生’ 12-17显示’初中生’  18-22显得’高中生’
23-28显得’博士’ 28以上’超人’ 结果格式:xxx是一个xxx

Student.Sname AS 学生姓名 ,

declare  @name varchar(20)=’小强’, @age int=23, @result varchar(10)

T1.score AS 集团管理,

–多分支 set @result=case     when @age<12 then ‘小学生’     when
@age<17 then ‘初中生’     when @age<22 then ‘高中生’     when
@age<28 then ‘大学生’     else ‘超人’    end

T2.score AS 马克思,

–输出 print @name+’是一个’+@result

T3.score AS UML,

–2– 突显学号、笔试战绩、等级,数据如下:  笔试战表:90以上的–A等     
   80之上的– B等         70之上的– C等         60上述的– D等     
   60以下的– E等 stuNo   writtenExam   grade s25303  60            D等
s25302  40            E等 s25301  77            C等 s25318  45          
 E等 select  stuNo, writtenExam, grade=case    when writtenExam>=90
then ‘A等’    when writtenExam>=80 then ‘B等’    when
writtenExam>=70 then ‘C等’    when writtenExam>=60 then ‘D等’  
 else ‘E等’     end from StuExam –3–
请依据平均分和底下的评分规则,编写T-SQL语句询问学员的战绩 优 :90分以上
   良 :80-89分     中 :70-79分     差 :60-69分     极差 :60分以下
        

T4.score AS 数据库,

select AVG(writtenExam),A=case      when Avg(writtenExam)>90 then
‘优’      when Avg(writtenExam)>80 and Avg(writtenExam)<89 then
‘良’      when Avg(writtenExam)>70 and Avg(writtenExam)<79 THEN
‘中’      when Avg(writtenExam)>60 and Avg(writtenExam)<69 then
‘差’      when Avg(writtenExam)<60 then ‘极差’      end      from
stuExam    

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0)

–4– 难题: 依据如下规则对机试成绩举行很多次加分,     
 直到机试平均分超越85分成止       请编写T-SQL语句已毕,注:(循环+多分支)
90分以上:  不加分 80-89分:   加1分 70-79分:   加2分 60-69分:   加3分
60分以下:  加5分 declare @a int   while(1=1) begin select
@a=AVG(labExam)from dbo.stuExam if(@a<85) begin update dbo.stuExam
set labExam =case          when labExam>=90 then  labExam        
 when labExam>=80 then labExam+1          when labExam>=70 then
labExam+2          when labExam>=60 then labExam+3          else     
    labExam+5         end end else break end select AVG(labExam) AS
机试成绩平均分 from dbo.stuExam

  • ISNULL(T4.score,0) as 总分

查询

FROM Student,SC LEFT JOIN SC AS T1

子查询:查询中再查询,平日是以一个询问作为条件来供另一个查询利用
语法:
   select 列表 from 表名 
 where >(子查询)
注意:
 使用相比运算符时,须求子查询只好回到一条或空的笔录!
示例:
 需求获知比我小的学童消息
 select * from student
 where studentno in
 (select studentno from student
 where studentname=’我’)

ON SC.S# = T1.S# AND T1.C# = ‘001’

NOT IN:确定给定的值是或不是与子查询或列表中的值相匹配,要是不匹配则反回真。
采纳办法:
 在须要子查询再次回到多数据时使用。
语法:
 select 列表 from 表名 
 where 列名 not in(子查询)
示例:
 查询未到位过考试的学生信息
 select  * from student 
 where studentno not in
 (select studentno from Result)

LEFT JOIN SC AS T2

1.EXISTS 子查询
 EXISTS: exists 关键字可以检测数据是或不是留存,若是存在再次来到真。
语法
 if exists(子查询)
 语句
示例:
 查询本校学生有没有叫张三的
 if exists( select * from student
 where studentname=’张三’ )
 begin
 print ‘有叫张三的’
 end

ON SC.S# = T2.S# AND T2.C# = ‘002’

  1. NOT EXISTS 子查询
     NOT EXISTS: exists 关键字能够检测数据是或不是存在,若是不存在重返真
    语法
     if not exists(子查询)
     语句
    示例:
     查询本校学生是还是不是未曾叫张三的
     if not exists( select * from student
     where studentname=’张三’ )
     begin
     print ‘没有叫张三的’
     end

LEFT JOIN SC AS T3

1:非相关子查询是单身于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。
 2:相关子查询的实施依赖于表面查询的多寡,外部查询执行一行,子查询就实施三遍。数据是不是存在,如果不存在再次来到真

ON SC.S# = T3.S# AND T3.C# = ‘003’

示范:检索出在work表中每一个机关的参天基本报酬的员薪给料  select * from
work a where 基本薪给=(select max(基本薪水) from work b where
a.部门名称=b.部门名称)

LEFT JOIN SC AS T4

 事物视图索引

ON SC.S# = T4.S# AND T4.C# = ‘004’

语法

WHERE student.S#=SC.S# and

begin transaction

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0)

commit transaction

  • ISNULL(T4.score,0)

rollback transaction

NOT IN

视图

(SELECT

创办视图索引

DISTINCT

if exists(select * from sysobjects where name=’视图名’)

TOP15WITH TIES

drop view 视图名

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0)

create view  视图名

  • ISNULL(T4.score,0)

as

FROM sc

SQL语句

LEFT JOIN sc AS T1

索引

ON sc.S# = T1.S# AND T1.C# = ‘k1’

if exists(select name from sysindex where name=’索引’)

LEFT JOIN sc AS T2

drop index 表名。索引名

ON sc.S# = T2.S# AND T2.C# = ‘k2’

create 索引类型 index 索引名

LEFT JOIN sc AS T3

on 表名(列名)

ON sc.S# = T3.S# AND T3.C# = ‘k3’

with fillfactor=30

LEFT JOIN sc AS T4

GO

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);

储存进度是在数据库管理种类保留的,预先编译的,能落到实处某种意义的SQL程序,它是数据库应用中拔取比较常见的一种多少对象。

23、总计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[
<60]

为何需求仓储进度?

SELECT SC.C# as 课程ID, Cname as 课程名称

1.储存进程只在开立刻展开编译,未来每回执行存储进程都不需再重新编译,而貌似SQL语句每执行三回就编译三次,所以采用存储进度可进步数据库执行进度。
2.当对数据库进行复杂操作时,可将此复杂操效率存储进程封装起来与数据库提供的事务处理结合一起行使。
3.存储进程能够重复使用,可收缩数据库开发人士的工作量。 4.安全性高,可设定只有少数用户才具备对点名存储进度的使用权

,SUM(CASE WHEN score BETWEEN85AND100THEN1ELSE0END) AS
[100 – 85]

储存进程的独到之处:

,SUM(CASE WHEN score BETWEEN70AND85THEN1ELSE0END) AS
[85 – 70]

1.模块化程序设计

,SUM(CASE WHEN score BETWEEN60AND70THEN1ELSE0END) AS
[70 – 60]

2.举行进程块,效用高

,SUM(CASE WHEN score <60THEN1ELSE0END) AS [60 -]

3.减去互连网流量

FROM SC,Course

4.兼有非凡的安全性

where SC.C#=Course.C#

 

GROUP BY SC.C#,Cname;

二 系统存储过程

24、查询学一生均成绩及其名次

SQL_SERVER 提供系统存储过程,它们是一组预编译的T-SQL语句,系统存储进度提供了管住数据库和更新表的体制,并担任从系统表中检索新闻的飞速格局。

SELECT1+(SELECT COUNT( distinct 平均成绩)

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

FROM (SELECT S#,AVG(score) AS 平均成绩

三 用户自定义的仓储进度

FROM SC

1.创制不带参数的储存进程

GROUP BY S#

Create proc usp_selectstu

) AS T1

As

WHERE 平均战表 > T2.平分战绩) as 名次,

Select StudentName,Gender,GradeId,Phone from dbo.Student

S# as 学生学号,平均战表

 

FROM (SELECT S#,AVG(score) 平均成绩

调用存储进度:exec
usp_selectstu

FROM SC

2.创立带入参数的囤积进程

GROUP BY S#

Create proc usp_stuInfo @gradeid int=2       (默认)

) AS T2

As

ORDER BY 平均战绩 desc;

Select * from student where gradeId=@gradeid

25、查询各科战绩前三名的记录:(不考虑成绩并列情形)

调用存储进度:exec usp_stuInfo
2

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

3.创建带出参数的蕴藏进度

FROM SC t1

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

WHERE score IN (SELECT TOP3score

As

FROM SC

Select @gradeid=gradeid from student where  studentname=@name

WHERE t1.C#= C#

print @gradeid

ORDER BY score DESC

 

)

调用存储进程:

ORDER BY t1.C#;

declare @id int

26、查询每门科目被选修的学习者数

exec usp_selectGrade ‘李小龙’,@id output 

select c#,count(S#) from sc group by C#;

 

27、查询出只选修了一门课程的百分之百学童的学号和人名

  4、 带通配符参数存储进程

select SC.S#,Student.Sname,count(C#) AS 选课数

Create proc usp_one  @name nvarchar(10)

from SC ,Student

as

where SC.S#=Student.S# group by SC.S# ,Student.Sname having
count(C#)=1;

select * from dbo.Student where StudentName like @name

28、查询男生、女孩子人数

 

Select count(Ssex) as 男生人数 from Student group by Ssex having
Ssex=’男’;

exec usp_one ‘李%’

Select count(Ssex) as 女孩子人数 from Student group by Ssex having
Ssex=’女’;

   5、 不缓存存储进程

29、查询姓“张”的学生名单

缓存就是数据交流的缓冲区(称作Cache),当某一硬件要读取数据时,会率先从缓存中找寻要求的数目,假诺找到了则直接实施,找不到的话则从内存中找。由于缓存的运作速度比内存快得多,故缓存的功力就是扶持硬件更快地运行。

SELECT Sname FROM Student WHERE Sname like ‘张%’;

Sql
Server系统内存管理在并未安排内存最大值,很多时候我们会发觉运行Sql
Server的种类内存往往居高不下。那是由于她对于内存使用的方针是有多少闲置的内存就占据多少,直到内存使用虑达到系统峰值时(预留内存按照系统默许预留使用为准,至少4M),才会消除一些缓存释放少量的内存为新的缓存腾出空间。

30、查询同名同性学生名单,并计算同有名气的人数

这几个内存一般都是Sql Server运行时候作为缓存的,例如你运行一个select语句,
执行个存储进度,调用函数;

select Sname,count(*) from Student group by Sname
having count(*)>1;;

  1. 数码缓存:执行个查询语句,Sql Server会将相关的数据页(Sql
    Server操作的数目都是以页为单位的)加载到内存中来,下一回即使再度恳请此页的数额的时候,就无需读取磁盘了,大大升高了进程。

31、1981年降生的学生名单(注:Student表中Sage列的体系是datetime)

2.执行命令缓存:在实施存储进度,自定函数时,Sql
Server须求先二进制编译再运行,编译后的结果也会缓存起来,
再一次调用时就无需重新编译。

select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age

 

from student

create proc proc_temp with recompile as     select * from student
exec proc_temp

where CONVERT(char(11),DATEPART(year,Sage))=’1981′;

 

32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均战表一样时,按学科号降序排列

6,加密存储进程

Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC
;

exec sp_helptext 储存进程名      可以查阅储存进度代码

33、查询平均战表超乎85的具有学员的学号、姓名和平均战绩

create proc proc_temp_encryption

select Sname,SC.S# ,avg(score)

with encryption

from Student,SC

as

where Student.S#=SC.S# group by SC.S#,Sname
having    avg(score)>85;

    select * from student;

34、查询课程名称为“数据库”,且分数低于60的学童姓名和分数

go

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;

–但此时,存储过程依然能被execute、alter和drop。

35、查询所有学员的选课情状;

exec proc_temp_encryption;

SELECT SC.S#,SC.C#,Sname,Cname

exec sp_helptext ‘proc_temp’

FROM SC,Student,Course

exec sp_helptext ‘proc_temp_encryption’

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

储存进程是在数据库管理体系保留的,预先编译的,能兑现某种意义的SQL程序,它是数据库应用中利用相比较常见的一种多少对象。

WHERE SC.score>=70AND SC.S#=student.S#;

干什么须求仓储进度?

37、查询不及格的学科,并按学科号从大到小排列

1.仓储进程只在创立时展开编译,将来每回执行存储进度都不需再重新编译,而相似SQL语句每执行一遍就编译五次,所以利用存储进度可提升数据库执行进程。
2.当对数据库举办复杂操作时,可将此复杂操成效存储进度封装起来与数据库提供的事务处理结合一起行使。
3.存储进程能够重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定唯有某些用户才享有对点名存储进度的使用权

select c# from sc where scor e <60order by C# ;

存储进程的长处:

38、查询课程编号为003且课程成绩在80分以上的学习者的学号和人名;

1.模块化程序设计

select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and
Score>80and C#=’003′;

2.进行进程块,作用高

39、求选了课程的学员人数

3.缩减网络流量

select count(*) from sc;

4.独具优良的安全性

40、查询选修“叶平”老师所授课程的学习者中,战表最高的学生姓名及其成绩

 

select Student.Sname,score

二 系统存储过程

from Student,SC,Course C,Teacher

SQL_SERVER 提供系统存储进程,它们是一组预编译的T-SQL语句,系统存储进程提供了管住数据库和更新表的机制,并充当从系统表中检索新闻的快捷格局。

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# );

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

41、查询各样科目及相应的选修人数

三 用户自定义的囤积进程

select count(*) from sc group by C#;

1.创立不带参数的存储进度

42、查询分裂科目成绩同样的学童的学号、课程号、学生战绩

Create proc usp_selectstu

select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and
A.C# <>B.C# ;

As

43、查询每门功战绩最好的前两名

Select StudentName,Gender,GradeId,Phone from dbo.Student

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

 

FROM SC t1

调用存储进程:exec
usp_selectstu

WHERE score IN (SELECT TOP2score

2.创办带入参数的贮存进度

FROM SC

Create proc usp_stuInfo @gradeid int=2       (默认)

WHERE t1.C#= C#

As

ORDER BY score DESC

Select * from student where gradeId=@gradeid

)

调用存储进度:exec usp_stuInfo
2

ORDER BY t1.C#;

3.创设带出参数的仓储进程

44、计算每门课程的学习者选修人数(当先10人的学科才总结)。须要输出课程号和选修人数,查询结果按人头降序排列,查询结果按人头降序排列,若人数相同,按学科号升序排列

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

select C# as 课程号,count(*) as 人数

As

from sc

Select @gradeid=gradeid from student where  studentname=@name

group by C#

print @gradeid

order by count(*) desc,c#

 

45、检索至少选修两门学科的学习者学号

调用存储进度:

select S#

declare @id int

from sc

exec usp_selectGrade ‘李小龙’,@id output 

group by s#

 

having count(*) > =2

  4、 带通配符参数存储进度

46、查询任何学生都选修的学科的课程号和学科名

Create proc usp_one  @name nvarchar(10)

select C#,Cname

as

from Course

select * from dbo.Student where StudentName like @name

where C# in (select c# from sc group by c#)

 

47、查询没学过“叶平”老师教师的任一门学科的学习者姓名

exec usp_one ‘李%’

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=’叶平’);

   5、 不缓存存储进程

48、查询两门以上不及格课程的同学的学号及其平均战表

缓存就是数据交流的缓冲区(称作Cache),当某一硬件要读取数据时,会首先从缓存中追寻须求的数量,借使找到了则平昔实施,找不到的话则从内存中找。由于缓存的运行速度比内存快得多,故缓存的成效就是赞助硬件更快地运行。

select S#,avg(isnull(score,0)) from SC where S# in (select S#
from SC where score <60group by S# having
count(*)>2)group by S#;

Sql
Server系统内存管理在未曾配置内存最大值,很多时候我们会意识运行Sql
Server的系统内存往往居高不下。那是出于他对此内存使用的政策是有微微闲置的内存就占用多少,直到内存使用虑达到系统峰值时(预留内存按照系统默许预留使用为准,至少4M),才会消除一些缓存释放少量的内存为新的缓存腾出空间。

49、检索“004”课程分数小于60,按分数降序排列的校友学号

这么些内存一般都是Sql Server运行时候作为缓存的,例如你运行一个select语句,
执行个存储进度,调用函数;

select S# from SC where C#=’004’and score <60order by score
desc;

  1. 数量缓存:执行个查询语句,Sql Server会将有关的数据页(Sql
    Server操作的数量都是以页为单位的)加载到内存中来,下五回即便重新请求此页的数码的时候,就无需读取磁盘了,大大提升了进程。

50、删除“002”同学的“001”课程的成就

2.执行命令缓存:在实践存储进度,自定函数时,Sql
Server要求先二进制编译再运行,编译后的结果也会缓存起来,
再一次调用时就无需另行编译。

delete from Sc where S#=’001’and C#=’001′;

 

经文SQL语句大全

create proc proc_temp with recompile as     select * from student
exec proc_temp

一、基础

 

1、表明:创造数据库

6,加密存储进程

CREATE DATABASE database-name

exec sp_helptext 储存进度名      能够查看储存进程代码

2、表明:删除数据库

create proc proc_temp_encryption

drop database dbname

with encryption

3、说明:备份sql server

as

— 创造 备份数据的 device

    select * from student;

USE master

go

EXEC sp_addumpdevice ‘disk’, ‘testBack’,
‘c:\mssql7backup\MyNwind_1.dat’

–存储进程的始末不会被随机看出(即便解密也是有可能的)。

— 开始 备份

–应用这几个,大家得以对少数重点的囤积进程进行加密。

BACKUP DATABASE pubs TO testBack

–但此时,存储过程如故能被execute、alter和drop。

4、表明:创制新表

exec proc_temp_encryption;

create table tabname(col1 type1 [not null] [primary key],col2 type2
[not null],..)

exec sp_helptext ‘proc_temp’

根据已有些表创设新表:

exec sp_helptext ‘proc_temp_encryption’

A:create table tab_new like tab_old (使用旧表成立新表)

(注意:加密仓储进度前应该备份原始存储进度,且加密应该在配备到生产条件前形成。) 

B:create table tab_new as select col1,col2… from tab_old definition
only

 

5、表达:删除新表

drop table tabname

6、表达:伸张一个列

Alter table tabname add column col type

注:列增添后将无法去除。DB2中列加上后数据类型也不可能更改,唯一能更改的是增加varchar类型的长短。

7、表明:添加主键: Alter table tabname add primary key(col)

表明:删除主键: Alter table tabname drop primary key(col)

8、表明:创立索引:create [unique] index idxname on tabname(col….)

删去索引:drop index idxname

注:索引是不可改变的,想改变必须删除重新建。

9、表达:创立视图:create view viewname as select statement

剔除视图:drop view viewname

10、表明:多少个几乎的要旨的sql语句

选择:select * from table1 where 范围

插入:insert into table1(field1,field2) values(value1,value2)

删除:delete from table1 where 范围

更新:update table1 set field1=value1 where 范围

查找:select * from table1 where field1 like ’%value1%’
—like的语法很精致,查资料!

排序:select * from table1 order by field1,field2 [desc]

总数:select count as totalcount from table1

求和:select sum(field1) as sumvalue from table1

平均:select avg(field1) as avgvalue from table1

最大:select max(field1) as maxvalue from table1

最小:select min(field1) as minvalue from table1

11、表明:多少个高级查询运算词

A: UNION 运算符

UNION 运算符通过结合其余八个结果表(例如 TABLE1 和 TABLE2)并消去表中其余重复行而派生出一个结实表。当 ALL 随 UNION 一起利用时(即 UNION
ALL),不清除重复行。三种状态下,派生表的每一行不是根源 TABLE1就是根源 TABLE2。

B: EXCEPT 运算符

EXCEPT运算符通过包涵拥有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起利用时 (EXCEPT
ALL),不排除重复行。

C: INTERSECT 运算符

INTERSECT运算符通过只包含 TABLE1 和 TABLE2 中都一些行并消除所有重复行而派生出一个结出表。当 ALL随 INTERSECT 一起行使时 (INTERSECT
ALL),不免除重复行。

注:使用运算词的多少个查询结果行必须是平等的。

12、表明:使用外接连

A、left (outer) join:

左外连接(左连接):结果集几囊括连接表的匹配行,也囊括左连接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a =
b.c

B:right (outer) join:

右外连接(右连接):结果集既包涵连接表的匹配连接行,也囊括右连接表的具备行。

C:full/cross (outer) join:

全外连接:不仅包涵符号连接表的匹配行,还包含五个一连表中的所有记录。

12、分组:Group by:

一张表,一旦分组 已毕后,查询后只好获取组相关的音信。

组相关的新闻:(总结新闻) count,sum,max,min,avg  分组的专业)

在SQLServer中分组时:不可以以text,ntext,image类型的字段作为分组依照

在selecte计算函数中的字段,不能和常见的字段放在一起;

13、对数据库举办操作:

分开数据库: sp_detach_db;附加数据库:sp_attach_db 后接表明,附加需求总体的路径名

14.怎样修改数据库的称呼:

sp_renamedb ‘old_name’, ‘new_name’

二、提升

1、表达:复制表(只复制结构,源表名:a 新表名:b) (Access可用)

法一:select * into b from a where 1<>1(仅用于SQlServer)

法二:select top 0 * into b from a

2、表明:拷贝表(拷贝数据,源表名:a 目的表名:b) (Access可用)

insert into b(a, b, c) select d,e,f from b;

3、表达:跨数据库之间表的正片(具体数据应用相对路径) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

例子:..from b in ‘”&Server.MapPath(“.”)&”\data.mdb” &”‘ where..

4、说明:子查询(表名1:a 表名2:b)

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c
from a where a IN (1,2,3)

5、表达:突显作品、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate)
adddate from table where table.title=a.title) b

6、表达:外接连查询(表名1:a 表名2:b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、表达:在线视图查询(表名1:a )

select * from (SELECT a,b,c FROM a) T where t.a > 1;

8、表达:between的用法,between限制查询数据范围时包含了边界值,not
between不包蕴

select * from table1 where time between time1 and time2

select 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

三、技巧

1、1=1,1=2的接纳,在SQL语句组合时用的较多

“where 1=1” 是意味选用任何    “where 1=2”全体不选,

如:

if @strWhere !=”

begin

set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where
‘ + @strWhere

end

else

begin

set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]’

end

我们得以一向写成

错误!未找到目录项。

set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where
1=1 安定 ‘+ @strWhere 2、裁减数据库

–重建索引

DBCC REINDEX

DBCC INDEXDEFRAG

–减弱数据和日志

DBCC SHRINKDB

DBCC SHRINKFILE

3、压缩数据库

dbcc shrinkdatabase(dbname)

4、转移数据库给新用户以已存在用户权限

exec sp_change_users_login ‘update_one’,’newname’,’oldname’

go

5、检查备份集

RESTORE VERIFYONLY from disk=’E:\dvbbs.bak’

6、修复数据库

ALTER DATABASE [dvbbs] SET SINGLE_USER

GO

DBCC CHECKDB(‘dvbbs’,repair_allow_data_loss) WITH TABLOCK

GO

ALTER DATABASE [dvbbs] SET MULTI_USER

GO

7、日志清除

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

USE tablename — 要操作的多少库名

SELECT  @LogicalFileName = ‘tablename_log’, — 日志文件名

@MaxMinutes = 10, — Limit on time allowed to wrap log.

@NewSize = 1  — 你想设定的日记文件的尺寸(M)

Setup / initialize

DECLARE @OriginalSize int

SELECT @OriginalSize = size

FROM sysfiles

WHERE name = @LogicalFileName

SELECT ‘Original Size of ‘ + db_name() + ‘ LOG is ‘ +

CONVERT(VARCHAR(30),@OriginalSize) + ‘ 8K pages or ‘ +

CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ‘MB’

FROM sysfiles

WHERE name = @LogicalFileName

CREATE TABLE DummyTrans

(DummyColumn char (8000) not null)

DECLARE @Counter    INT,

@StartTime DATETIME,

@TruncLog   VARCHAR(255)

SELECT @StartTime = GETDATE(),

@TruncLog = ‘BACKUP LOG ‘ + db_name() + ‘ WITH TRUNCATE_ONLY’

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

— Wrap the log if necessary.

WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) — time has
not expired

AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName)

AND (@OriginalSize * 8 /1024) > @NewSize

BEGIN — Outer loop.

SELECT @Counter = 0

WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

BEGIN — update

INSERT DummyTrans VALUES (‘Fill Log’) DELETE DummyTrans

SELECT @Counter = @Counter + 1

END

EXEC (@TruncLog)

END

SELECT ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +

CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +

CONVERT(VARCHAR(30),(size*8/1024)) + ‘MB’

FROM sysfiles

WHERE name = @LogicalFileName

DROP TABLE DummyTrans

SET NOCOUNT OFF

8、表达:更改某个表

exec sp_changeobjectowner ‘tablename’,’dbo’

9、存储更改所有表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch

@OldOwner as NVARCHAR(128),

@NewOwner as NVARCHAR(128)

AS

DECLARE @Name    as NVARCHAR(128)

DECLARE @Owner   as NVARCHAR(128)

DECLARE @OwnerName   as NVARCHAR(128)

DECLARE curObject CURSOR FOR

select ‘Name’    = name,

‘Owner’    = user_name(uid)

from sysobjects

where user_name(uid)=@OldOwner

order by name

OPEN   curObject

FETCH NEXT FROM curObject INTO @Name, @Owner

WHILE(@@FETCH_STATUS=0)

BEGIN

if @Owner=@OldOwner

begin

set @OwnerName = @OldOwner + ‘.’ + rtrim(@Name)

exec sp_changeobjectowner @OwnerName, @NewOwner

end

— select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner

END

close curObject

deallocate curObject

GO

10、SQL SERVER中间接循环写入数据

declare @i int

set @i=1

while @i<30

begin

insert into test (userid) values(@i)

set @i=@i+1

end

案例:

有如下表,须求就裱中有着沒有及格的成績,在每趟增長0.1的基礎上,使他們剛好及格:

Name     score

Zhangshan   80

Lishi       59

Wangwu      50

Songquan    69

while((select min(score) from tb_table)<60)

begin

update tb_table set score =score*1.01

where score<60

if  (select min(score) from tb_table)>60

break

else

continue

end

数据开发-经典

1.按姓氏笔画排序:

Select * From TableName Order By CustomerName Collate
Chinese_PRC_Stroke_ci_as //从少到多

2.数据库加密:

select encrypt(‘原始密码’)

select pwdencrypt(‘原始密码’)

select pwdcompare(‘原始密码’,’加密后密码’) =
1–相同;否则不等同 encrypt(‘原始密码’)

select pwdencrypt(‘原始密码’)

select pwdcompare(‘原始密码’,’加密后密码’) = 1–相同;否则差异等

3.取回表中字段:

declare @list varchar(1000),

@sql nvarchar(1000)

select @list=@list+’,’+b.name from sysobjects a,syscolumns b where
a.id=b.id and a.name=’表A’

set @sql=’select ‘+right(@list,len(@list)-1)+’ from 表A’

exec (@sql)

4.查看硬盘分区:

EXEC master..xp_fixeddrives

5.相比A,B表是还是不是等于:

if (select checksum_agg(binary_checksum(*)) from A)

=

(select checksum_agg(binary_checksum(*)) from B)

print ‘相等’

else

print ‘不相等’

6.杀掉所有的轩然大波探察器进度:

DECLARE hcforeach CURSOR GLOBAL FOR SELECT ‘kill ‘+RTRIM(spid) FROM
master.dbo.sysprocesses

WHERE program_name IN(‘SQL profiler’,N’SQL 事件探查器’)

EXEC sp_msforeach_worker ‘?’

7.记录搜索:

开头到N条记录

Select Top N * From 表


N到M条记录(要有主索引ID)

Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by
ID   Desc


N到最后记录

Select Top N * From 表 Order by ID Desc

案例

比如说1:一张表有一万多条记下,表的首先个字段 RecID 是自拉长字段,
写一个SQL语句, 找出表的第31到第40个记录。

select top 10 recid from A where recid not  in(select top 30 recid from
A)

解析:若是如此写会时有发生某些难点,如若recid在表中留存逻辑索引。

select top 10 recid from A where……是从索引中摸索,而前边的select top 30
recid from
A则在数据表中搜寻,那样由于索引中的顺序有可能和数码表中的不雷同,那样就招致查询到的不是理所当然的欲得到的多少。

缓解方案

1,用order by select top 30 recid from A order by
ricid 如若该字段不是自拉长,就会出现难点

2,在老大子查询中也加条件:select top 30 recid from A where recid>-1

例2:查询表中的最后以条记下,并不知道这些表共有多少多少,以及表结构。

set @s = ‘select top 1 * from T   where pid not in (select top
‘ + str(@count-1) + ‘ pid  from  T)’

print @s      exec  sp_executesql  @s

9:获取当前数据库中的所有用户表

select Name from sysobjects where xtype=’u’ and status>=0

10:获取某一个表的具备字段

select name from syscolumns where id=object_id(‘表名’)

select name from syscolumns where id in (select id from sysobjects where
type = ‘u’ and name = ‘表名’)

二种办法的意义同样

11:查看与某一个表相关的视图、存储进程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and
b.text like ‘%表名%’

12:查看当前数据库中兼有存储进程

select name as 存储进程名称 from sysobjects where xtype=’P’

13:查询用户创设的具有数据库

select * from master..sysdatabases D where sid not in(select sid from
master..syslogins where name=’sa’)

或者

select dbid, name AS DB_NAME from master..sysdatabases where sid
<> 0x01

14:查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns

where table_name = ‘表名’

15:分裂服务器数据库之间的数目操作

–创建链接服务器

exec sp_addlinkedserver   ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘,
‘远程服务器名或ip地址 ‘

exec sp_addlinkedsrvlogin  ‘ITSV ‘, ‘false ‘,null, ‘用户名 ‘, ‘密码 ‘

–查询示例

select * from ITSV.数据库名.dbo.表名

–导入示例

select * into 表 from ITSV.数据库名.dbo.表名

–未来不再选用时去除链接服务器

exec sp_dropserver  ‘ITSV ‘, ‘droplogins ‘

–连接远程/局域网数据(openrowset/openquery/opendatasource)

–1、openrowset

–查询示例

select * from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘;
‘密码 ‘,数据库名.dbo.表名)

–生开销地表

select * into 表 from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘;
‘用户名 ‘; ‘密码 ‘,数据库名.dbo.表名)

–把当地表导入远程表

insert openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘;
‘密码 ‘,数据库名.dbo.表名)

select *from 本地表

–更新本地表

update b

set b.列A=a.列A

from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘;
‘密码 ‘,数据库名.dbo.表名)as a inner join 本地表 b

on a.column1=b.column1

–openquery用法须要创设一个接连

–首先创设一个一连创设链接服务器

exec sp_addlinkedserver   ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘,
‘远程服务器名或ip地址 ‘

–查询

select *

FROM openquery(ITSV,  ‘SELECT *  FROM 数据库.dbo.表名 ‘)

–把当地表导入远程表

insert openquery(ITSV,  ‘SELECT *  FROM 数据库.dbo.表名 ‘)

select * from 本地表

–更新本地表

update b

set b.列B=a.列B

FROM openquery(ITSV,  ‘SELECT * FROM 数据库.dbo.表名 ‘) as a

inner join 本地表 b on a.列A=b.列A

–3、opendatasource/openrowset

SELECT   *

FROM   opendatasource( ‘SQLOLEDB ‘,  ‘Data Source=ip/ServerName;User
ID=登陆名;Password=密码 ‘ ).test.dbo.roy_ta

–把当地表导入远程表

insert opendatasource( ‘SQLOLEDB ‘,  ‘Data Source=ip/ServerName;User
ID=登陆名;Password=密码 ‘).数据库.dbo.表名

select * from 本地表

SQL Server基本函数

1.字符串函数 长度与分析用

1,datalength(Char_expr) 重返字符串包括字符数,但不分包前面的空格

2,substring(expression,start,length) 取子串,字符串的下标是从“1”,start为发端地方,length为字符串长度,实际行使中以len(expression)取得其长度

3,right(char_expr,int_expr) 再次回到字符串左侧第int_expr个字符,还用left于之相反

4,isnull( check_expression , replacement_value )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类

5,Sp_addtype自定義數據類型

例如:EXEC sp_addtype birthday, datetime, ‘NULL’

6,set nocount {on|off}

使再次来到的结果中不含有关于受 Transact-SQL 语句影响的行数的信息。即使存储进度中包蕴的部分说话并不回来许多其实的数码,则该装置由于多量回落了互连网流量,由此可明明增进质量。

SET NOCOUNT 设置是在实施或运行时设置,而不是在解析时设置。SET
NOCOUNT 为 ON 时,不回来计数(表示受 Transact-SQL 语句影响的行数)。

SET NOCOUNT

为 OFF 时,再次来到计数

常识

在SQL查询中:from后最多可以跟多少张表或视图:256在SQL语句中出现 Order
by,查询时,先排序,后取在SQL中,一个字段的最大容量是8000,而对于nvarchar(4000),由于nvarchar是Unicode码。

SQLServer2000

一块复制技术达成步骤

一、 预备工作

1.发表服务器,订阅服务器都创制一个同名的windows用户,并设置同一的密码,做为揭橥快照文件夹的一蹴而就访问用户–管理工具–统计机管理–用户和组–右键用户–新建用户–建立一个专属于administrator组的登陆windows的用户(SynUser)2.在公布服务器上,新建一个共享目录,做为发布的快照文件的寄放目录,操作:

我的电脑–D:\ 新建一个目录,名为: PUB

–右键那几个新建的目录–属性–共享–选拔”共享该文件夹”–通过”权限”按纽来设置具体的用户权限,保险第一步中开创的用户(SynUser) 具有对该公文夹的持有权限

–确定3.装置SQL代理(SQLSERVERAGENT)服务的启航用户(发布/订阅服务器均做此设置)

开首–程序–管理工具–服务

–右键SQLSERVERAGENT–属性–登陆–选拔”此账户”–输入或者选用第一步中创制的windows登录用户名(SynUser)–“密码”中输入该用户的密码4.安装SQL
Server身份验证方式,解决连接时的权限难点(发布/订阅服务器均做此设置)

店铺管理器

–右键SQL实例–属性–安全性–身份验证–选用”SQL
Server 和 Windows”–确定5.在布告服务器和订阅服务器上相互注册

合营社管理器

–右键SQL Server组–新建SQL
Server注册…–下一步–可用的服务器中,输入你要注册的长距离服务器名 –添加–下一步–连接使用,选拔第四个”SQL
Server身份验证”–下一步–输入用户名和密码(SynUser)–下一步–拔取SQL
Server组,也得以成立一个新组–下一步–达成6.对于只可以用IP,不可能用计算机名的,为其注册服务器别名(此步在实施中没用到) (在连接端配置,比如,在订阅服务器上配备来说,服务器名称中输入的是公布服务器的IP)

开班–程序–Microsoft SQL Server–客户端网络实用工具

–别名–添加–网络库选拔”tcp/ip”–服务器别名输入SQL服务器名–连接参数–服务器名称中输入SQL服务器ip地址–假若您改改了SQL的端口,打消选拔”动态控制端口”,并输入相应的端口号

二、 正式配置

1、配置发布服务器

开拓集团管理器,在文告服务器(B、C、D)上执行以下步骤:

(1) 从[工具]下拉菜单的[复制]子菜单中挑选[配备发表、订阅服务器和散发]出现布局宣布和散发向导

(2) [下一步] 选用分发服务器
可以挑选把发布服务器自己看做分发服务器或者其余sql的服务器(选取自己)

(3) [下一步] 设置快照文件夹

运用默许\\servername\Pub

(4) [下一步] 自定义配置

能够挑选:是,让自己设置分发数据库属性启用揭橥服务器或设置发布设置否,使用下列默许设置(推荐)

(5) [下一步] 设置分发数据库名称和职位 接纳默许值

(6) [下一步] 启用发表服务器 采纳作为发布的服务器

(7) [下一步] 选用需求揭穿的数据库和公布项目

(8) [下一步] 选用注册订阅服务器

(9) [下一步] 达成布局

2、创造出版物

揭橥服务器B、C、D上

(1)从[工具]菜单的[复制]子菜单中甄选[开创和管制揭橥]命令

(2)接纳要开创出版物的数据库,然后单击[创设公布]

(3)在[创制公布起始]的唤醒对话框中单击[下一步]系统就会弹出一个对话框。对话框上的始末是复制的三个品种。大家现在选首个也就是默许的快照发布(其他八个我们可以去探视帮忙)

(4)单击[下一步]系统需求指定可以订阅该布告的数据库服务器类型,SQLSERVER允许在不一样的数据库如 orACLE或ACCESS之间开展多少复制。

但是在那里我们选用运行”SQL SERVER 2000″的数据库服务器

(5)单击[下一步]系统就弹出一个概念作品的对话框也就是挑选要出版的表

只顾: 假若面前选用了事情公布 则再这一步中只可以选择带有主键的表

(6)选用公布名称和讲述

(7)自定义发布属性 向导提供的挑三拣四:

是 我将自定义数据筛选,启用匿名订阅和或别的自定义属性否
依据指定格局创制揭橥 (提议选拔自定义的不二法门)

(8)[下一步] 采纳筛选公布的法子

(9)[下一步] 可以选用是不是同意匿名订阅1)若是选取署名订阅,则需求在发表服务器上添加订阅服务器

方法:
[工具]->[复制]->[配置公布、订阅服务器和散发的性质]->[订阅服务器]
中添加否则在订阅服务器上呼吁订阅时会出现的唤醒:改发布不容许匿名订阅即使如故要求匿名订阅则用以下解决办法

[公司管理器]->[复制]->[布告内容]->[属性]->[订阅选项] 选用允许匿名请求订阅2)倘若选取匿名订阅,则布署订阅服务器时不会师世上述提醒(10)[下一步] 设置快照
代理程序调度(11)[下一步] 落成布局

当成功出版物的创导后创制出版物的数据库也就变成了一个共享数据库有数量

srv1.库名..author有字段:id,name,phone, srv2.库名..author有字段:id,name,telphone,adress

要求:

srv1.库名..author增加记录则srv1.库名..author记录增添srv1.库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新

–*/

–大概的处理步骤–1.在 srv1 上创设连接服务器,以便在 srv1 中操作 srv2,完结同步exec
sp_addlinkedserver ‘srv2′,”,’SQLOLEDB’,’srv2的sql实例名或ip’ exec
sp_addlinkedsrvlogin ‘srv2′,’false’,null,’用户名’,’密码’

go

997755.com澳门葡京,–2.在 srv1 和 srv2 那两台电脑中,启动 msdtc(分布式事务处理服务),并且安装为自行启动。我的微处理器–控制面板–管理工具–服务–右键
Distributed Transaction
Coordinator–属性–启动–并将开行项目设置为自发性启动go

–然后创制一个功课定时调用地点的联手处理存储进程就行了

商店管理器

–管理–SQL
Server代理–右键作业–新建作业–“常规”项中输入作业名称–“步骤”项–新建–“步骤名”中输入步骤名–“类型”中甄选”Transact-SQL 脚本(TSQL)” –“数据库”选取执行命令的数据库–“命令”中输入要实施的言辞:
exec
p_process –确定–“调度”项–新建调度–“名称”中输入调度名称–“调度项目”中甄选你的功课执行计划–若是选拔”反复出现” –点”更改”来安装你的时日安顿

下一场将SQL
Agent服务启动,并设置为机关启动,否则你的功课不会被实践设置格局:我的微机–控制面板–管理工具–服务–右键
SQLSERVERAGENT–属性–启动项目–选取”自动启动”–确定.

–3.达成同步处理的方法2,定时同步

–在srv1中创立如下的同步处理存储进度

create proc p_processas

–更新修改过的数目

update b set name=i.name,telphone=i.telphone

from srv2.库名.dbo.author b,author i

where b.id=i.id and(b.name <> i.name or b.telphone <>
i.telphone)

–插入新增的数额insert srv2.库名.dbo.author(id,name,telphone)

select id,name,telphone from author iwhere not exists(

select * from srv2.库名.dbo.author where id=i.id)

–删除已经删除的多寡(假诺须要的话)

delete b

from srv2.库名.dbo.author b

where not exists(select * from author where id=b.id)

go

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website