您的位置:新葡亰496net > 网络数据库 > 数据库重要知识点总结,SERVER数据库学习总结

数据库重要知识点总结,SERVER数据库学习总结

发布时间:2019-06-21 13:02编辑:网络数据库浏览(119)

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

      对于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#=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) 学生表

    首先感谢博客园,创建了这个专属于开发者的知识共享平台,需要强调的是我并不是一个大牛级别的人物,只是一个初入园子还未见过世面的IT小菜鸟,本着一颗向园内大牛们学习的心,加入了这个圈子,借此机会,对自己日常所学进行实时整理,还望各位大神多多指教。

    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 数据库重要知识点总结,SERVER数据库学习总结。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:mssql7backupMyNwind_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, @MaxMinutes 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) [下一步] 设置快照文件夹
      采用默认servernamePub
      (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#) 课程表

    我的邮箱是:zhangshuang_0909@126.com

    新葡亰496net 1

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

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

    以下是近期对SQL Server数据库所学知识的相关总结,希望可以在一些地方帮助初入门者,对于相关的基础名词我就不一一介绍了,就从SQL语言操作说起吧。

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

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

    Teacher(T#,Tname) 教师表

    1.数据库入门之SQL语句总结

    使用语句完成: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:projectWages_data.mdf',
      SIZE=5mb,
      FILEGROWTH=15%
     )
     LOG ON
     (
      NAME= 'Wages_log',
      FILENAME='e:projectWages_log.ldf',
      SIZE=3mb,
      FILEGROWTH=15%
     )
    GO

    问题:

    首先先对几个数据库中的数据类型进行下了解:

    上面语句是一个死循环,除非强制结束,如果1大于0就会一直向表中插入姓名

    为了创建良好的数据库需满足三大范式。

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

    nchar ----------------存储数据类型为宽字节字符数组,最多4000字节

    如下图所示:

    下面是创建表的SQL语句(包含了如果有该表先删除在建表)

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

    nvarchar-------------存储数据类型为宽字节字符串,最多4000字节

    新葡亰496net 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

    char------------------存储数据类型为多字节字符数组,最多8000字节

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

    下面是创建约束

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

    varchar--------------存储数据类型为多字节字符串,最多8000字节

    新葡亰496net 3

    语法如下

    2、查询平均成绩大于60分的同学的学号和平均成绩;

    举个例子:

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

    alter table 表名

    select S#,avg(score)

    用char(10)和varchar(10)分别对”hello”字符串进行存储,char(10)中该字符串所占字节数为10个,varchar(10)中所占字节数为6个。

    新葡亰496net 4

    add constraint 约束名 约束类型 具体的约束说明

    from sc

    如果有超大文本存储,此时选用text,ntext类型

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

    示例

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

    1.1SQL语句之定义功能

    打开“sql server profiler ”工具查看跟踪的信息,发现查询时间很长,cpu工作了265毫秒,reads:读了8649次,writes:写了10次,duration:总计花费2336毫秒完成查询。

    创建外键约束

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

    此项包括对基本表、索引和视图的定义和撤销。针对于表,我们可以对其进行创建、删除以及修改,而针对于视图、索引,我们可以创建和删除。

    新葡亰496net 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)

    1.1.1创建、修改、删除表

    为了下面分析文件更准确,多执行几次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

    create  table  表名

     新葡亰496net 6

    然后我们学习了变量,变量分全局变量和局部变量。

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

    {

    5、打开“数据库引擎优化顾问”,添加跟踪文件,进行分析,发现索引建议,需要建立索引。

    创建变量语法如下是

    select count(distinct(Tname))

    <列名>  <数据类型>  <约束条件>

     新葡亰496net 7

    declare @变量名 数据类型

    from Teacher

    }

    注意选择benet数据库中的学生表,然后点击“开始分析”

    局部变量有两种赋值方法

    where Tname like '李%';

    如:

     新葡亰496net 8

    set @变量名=value

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

    create table studentinfo

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

    select @变量名=value

    select Student.S#,Student.Sname

    (

    6、按照“数据库引擎优化顾问”的索引建议建立聚集索引,并且选择“唯一”

    区别是select可一次对多个变量赋值,set只能一次对一个变量赋值。

    from Student

    number int primary key,    --主键,唯一标识,不许重不许空

    新葡亰496net 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='叶平');

    name varchar(10) unique,   --唯一约束,除主键外唯一标识,可重可空

    7、再次执行Select * from 学生表Where 学号=900000

    输出语句

    6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

    sex varchar(5) check(sex='男' or sex='女'),--检查约束,保证值在一定范围内

     

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

    age int default(18)         --默认约束

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

    use MySchool

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

    )

    新葡亰496net 10

    go

    select S#,Sname

    create table studentcourse

    发现总计时间为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='叶平'));

    number int foreign key references studentinfo(number),--外键

    实验案例二:分别练习创建各种索引

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

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

    course varchar(20)

    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

     

     新葡亰496net 11

     create table Classes  

    9、查询所有课程成绩小于60分的同学的学号、姓名;

    修改:

    为tstudent表创建聚集索引

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

    select S#,Sname

    如:

    新葡亰496net 12

    ' clsname varchar(3), --班级名称   clsnums int --班级人数   )   

    from Student

    alter  table 表名

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

    go   

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

    {

     新葡亰496net 13

    'alter table Classes

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

    add <新列名> <数据类型> <约束条件>

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

    add constraint pk_clsid_Classes   primary key(clsid)  

    select Student.S#,Student.Sname

    drop<约束条件>

    新葡亰496net 14

     alter table Classes

    from Student,SC

    alter  column  <列名>  <数据类型>

    2、创建组合索引

    为成绩表创建组合索引,因为一个学生不能为一门学科录入两次成绩,所以将成绩表中的studentID和subjectID创建组合索引

     新葡亰496net 15

    新葡亰496net 16

    新葡亰496net 17

    新葡亰496net 18

    解决办法:

    菜单栏----工具----选项

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

     新葡亰496net 19

    这样组合索引就创建成功了。

     新葡亰496net 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)

    新葡亰496net 21

     alter table Classes

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

     

    4、创建非聚集索引---可以有重复值

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

    新葡亰496net 22

    使用命令查看表上的索引

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

    Indid中1代表聚集索引

    Indid中2代表唯一非聚集索引

    Indidz中3代表非聚集索引

    新葡亰496net 23

    使用sp_help  Tstudent也可以查看到相关表的信息

    新葡亰496net 24

    实验案例三:创建视图

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

    创建一个视图,分别来自三个的表的三个列,并重命名列,生成的视图名为student_info,如下图所示:

     新葡亰496net 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

    新葡亰496net 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

    drop  table  表名

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

    drop  table  studentinfo

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

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

    1.1.2创建、删除视图

    删除约束   

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

    创建:

    alter table StuInfos drop constraint fk_StuInfos_Classes

    from SC SC_2

    Create view 视图名 as

    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 view 视图名

    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

    create view myview as 

    name varchar(10) not null, --名称

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

    (select * from student)

    sex char(2) check(sex='男' or sex='女') default '男', --性别

    16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2

     

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

    号课的平均成绩;

    select * from myview

    tel char(11) unique, --电话

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

    update myview set ssex='女' where s#='01'

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

    insert into myview values('09','aa','1994-07-09','男')

      go

    17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

    delete from myview where s#='09'

    一:操作变量 --1-- 声明变量@temp值为1并打印出来 声明变量关键字:

    SELECT S# as 学生ID

    update myview set ssex='男' where s#='01'

    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 英语

    create view view1 as

      declare @temp int=1 --声明的同时赋值  

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

    select s.*,sc.score,sc.c# from student s inner join

     print @temp         --输出   

    FROM SC AS t

    sc on s.s#=sc.s#

     

    GROUP BY S#

     

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

    ORDER BY avg(t.score)

    select * from view1

    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

    1.1.2创建、删除索引

    --赋值 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#

    Create  index  索引名  on  表名(列名)

    --两个转型函数:   1.convert -- 语法:  convert(类型,数据)   2.cast   

    GROUP BY IL.C#)

    删除:

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

    AND

    Drop  index  索引名

    --3-- select * from StuInfo

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

     

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

    FROM SC AS IR

    1.2SQL语句之查询功能

    --声明 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#

    Select  [all | distinct] 列名

    --输出 print '姓名:' @name print '性别:' @sex print '年龄:' cast(@age as varchar)

    );

    From 表名/视图名

    --4-- 查询李文才的左右同桌的信息 declare @seat int select @seat=stuSeat from stuinfo where stuname='李文才' select * from stuinfo  where stuSeat=@seat-1 or stuSeat=@seat 1

    19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

    [where  条件表达式]

    --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 平均成绩

    [group by 列名][having  条件表达式]

    总结: set和select的区别  

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

    [order by  列名][asc | desc]

     1. set一次只能为一个变量赋值,而select能同时为多个变量赋值  

    FROM SC T,Course

     

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

    where t.C#=course.C#

    1.2.1单表查询

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

    GROUP BY t.C#

    (1)查询列

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

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

    Select  列名(多列用“,”隔开,所有列用“*”)  from  表名

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

    20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004

    (2)查询行

    --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 企业管理平均分

    A.消除重复行

    -2-- 根据输入的年龄显示人的生长阶段 未成年人<18,青年人<30,成年人<60,老年人<100,超人>=100

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

    Select  distinct  列名  from  表名

    declare @age int set @age=21

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

    B.比较大小:=,>,>=,!=,!>,!<等

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

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

    Select  sname from student where  cname=’english’

    print '青年人'

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

    C.确定范围:between…and…,not  between…and…

    else if(@age<60)

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

    Select   sname,sage from student where sage between 20 and 23

    print '成年人'

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

    D.确定集合:in,not in

    else if(@age<100)

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

    Select sname  from  student where sdept  in  (‘IS’,’MA’,’CS’)

    print '老年人'

    FROM SC

    E.模糊查询:like

    else print '超人'

    21、查询不同老师所教不同课程平均分从高到低显示

    Select  s#,sname from student where sname like ‘刘%’

    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

    F.涉及空值的:isnull

    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  *,isnull(ssex,’未知’)  from  student

    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#

    注:ssex项若为空,则将其置为“未知”

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

    ORDER BY AVG(Score) DESC

    G.多重条件查询:and,or

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

    Select   sname,sage from student where sage >= 20 and ssex=’男’

    --3-- 不停的提高学生笔试成绩2分,让所有学生的笔试成绩都及格

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

    H.聚合函数:count,sum,max,min,avg等

    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

    Select  avg(score)  from sc where c#=’01’

    四:多分支语句

    SC.S# As 学生学号,

    I.查询表中前几条:top

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

    Student.Sname AS 学生姓名 ,

    Select top 2 * from student

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

    T1.score AS 企业管理,

    J.条件:

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

    T2.score AS 马克思,

    select 列名 case when 条件

    --输出 print @name '是一个' @result

    T3.score AS UML,

    then 语句块

    --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 数据库,

    when条件

    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)

    then语句块

    --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 总分

    else语句块

    查询

    FROM Student,SC LEFT JOIN SC AS T1

    end

    子查询:查询中再查询,通常是以一个查询作为条件来供另一个查询使用
    语法:
       select 列表 from 表名 
     where >(子查询)
    注意:
     使用比较运算符时,要求子查询只能返回一条或空的记录!
    示例:
     要求查出比我小的学生信息
     select * from student
     where studentno in
     (select studentno from student
     where studentname='我')

    ON SC.S# = T1.S# AND T1.C# = '001'

    from 表名

    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

    select *,datediff(yy,birth,getdate()),

    1:非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。  2:相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。数据是否存在,如果不存在返回真

    ON SC.S# = T3.S# AND T3.C# = '003'

    case when month(getdate())<month(birth)

    示例:检索出在work表中每一个部门的最高基本工资的职工资料  select * from work a where 基本工资=(select max(基本工资) from work b where a.部门名称=b.部门名称)

    LEFT JOIN SC AS T4

    then datediff(yy,birth,getdate())-1

     事物视图索引

    ON SC.S# = T4.S# AND T4.C# = '004'

    when day(getdate())<day(birth)

    语法

    WHERE student.S#=SC.S# and

    then datediff(yy,birth,getdate())-1

    begin transaction

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

    else datediff(yy,birth,getdate())

    commit transaction

    • ISNULL(T4.score,0)

    end

    rollback transaction

    NOT IN

    from teacherinfo

    视图

    (SELECT

     

    创建视图索引

    DISTINCT

    K.类型转换

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

    TOP15WITH TIES

    Select cast()

    drop view 视图名

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

    Select cast(‘12’ as int)   1

    create view  视图名

    • ISNULL(T4.score,0)

    Select convert(int, ‘12’) 1       //convert类型转换有风格选项

    as

    FROM sc

     

    SQL语句

    LEFT JOIN sc AS T1

    1.2.2多表查询

    索引

    ON sc.S# = T1.S# AND T1.C# = 'k1'

    多表查询中,有以下5种查询方式:内连,左连,右连,笛卡尔积,嵌套

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

    LEFT JOIN sc AS T2

    A.取两表的交集,内连  inner join

    drop index 表名。索引名

    ON sc.S# = T2.S# AND T2.C# = 'k2'

    select * from student inner join SC on student.S#=sc.s#

    create 索引类型 index 索引名

    LEFT JOIN sc AS T3

    B.以左表为主,左连  left join

    on 表名(列名)

    ON sc.S# = T3.S# AND T3.C# = 'k3'

    select * from student left join SC on student.S#=sc.s#

    with fillfactor=30

    LEFT JOIN sc AS T4

    C.以右表为主,右连  right join

    GO

    ON sc.S# = T4.S# AND T4.C# = 'k4'

    select * from student right join SC on student.S#=sc.s#

    一 存储过程的概念

    ORDER BY ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0) DESC);

    D.将两表行乘积,即笛卡尔积

    存储过程是在数据库管理系统保存的,预先编译的,能实现某种功能的SQL程序,它是数据库应用中运用比较广泛的一种数据对象。

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

    select * from student,sc where student.S#=sc.s#

    为什么需要存储过程?

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

    E.嵌套查询

    1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时,可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

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

    如:查询"01"课程比"02"课程成绩高的学生的信息及课程分数

    存储过程的优点:

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

    select * from 

    1.模块化程序设计

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

    (select *,(select score from sc where c#='01' and student.s#=sc.s#) sc01,

    2.执行速度块,效率高

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

    (select score from sc where c#='02' and student.s#=sc.s#) sc02 from student) a

    3.减少网络流量

    FROM SC,Course

    where sc01>sc02

    4.具有良好的安全性

    where SC.C#=Course.C#

     

     

    GROUP BY SC.C#,Cname;

    嵌套查询中有带in谓词的子查询,比较运算符的子查询,带有any或or的谓词子查询,exists谓词子查询,还有集合查询的union,这里就不一一举例了。

    二 系统存储过程

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

    1.3SQL语句之操作功能

    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

    Insert  into  表名  values(值,值)

    1.创建不带参数的存储过程

    GROUP BY S#

    如:

    Create proc usp_selectstu

    ) AS T1

    insert into studentinfo values(5,'ee','男',35)

    As

    WHERE 平均成绩 > T2.平均成绩) as 名次,

    insert into studentinfo(number, age) values(6,60)

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

    S# as 学生学号,平均成绩

    修改:

     

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

    Update  表名  set  列名= 值

    调用存储过程:exec usp_selectstu

    FROM SC

    如:

    2.创建带入参数的存储过程

    GROUP BY S#

    update studentinfo set age=60 where number=6

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

    ) AS T2

    update studentinfo set name='gg',sex='女' where number=6

    As

    ORDER BY 平均成绩 desc;

    删除:

    Select * from student where gradeId=@gradeid

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

    Delete  from  表名

    调用存储过程:exec usp_stuInfo 2

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

    delete from studentinfo where number=6;

    3.创建带出参数的存储过程

    FROM SC t1

    2.数据库入门之建表规范

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

    WHERE score IN (SELECT TOP3score

    若表与表的关系为n:n时,创建的第三个表的主键为两个表的组合主键;若关系为1:n时,则把1的主键放到n里

    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、查询男生、女生人数

    2.数据库入门之存储过程

     

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

    说道存储过程,我认为可以这样去理解,在SQL语句中,为了避免多次进行相同的操作,将这样相同操作的语句进行一次封装的过程,相当于函数的作用吧。

    exec usp_one '李%'

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

    那么在说存储过程前,就先来看一些语法规则吧:

       5、 不缓存存储过程

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

    定义变量:(局部变量用1个@,全局变量用2个@)

    缓存就是数据交换的缓冲区(称作Cache),当某一硬件要读取数据时,会首先从缓存中查找需要的数据,如果找到了则直接执行,找不到的话则从内存中找。由于缓存的运行速度比内存快得多,故缓存的作用就是帮助硬件更快地运行。

    SELECT Sname FROM Student WHERE Sname like '张%';

    Declare   @变量名   变量的类型,@变量名   变量的类型

    Sql Server系统内存管理在没有配置内存最大值,很多时候我们会发现运行Sql Server的系统内存往往居高不下。这是由于他对于内存使用的策略是有多少闲置的内存就占用多少,直到内存使用虑达到系统峰值时(预留内存根据系统默认预留使用为准,至少4M),才会清除一些缓存释放少量的内存为新的缓存腾出空间。

    30、查询同名同性学生名单,并统计同名人数

    如:

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

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

    declare @sum int,@i int

    1. 数据缓存:执行个查询语句,Sql Server会将相关的数据页(Sql Server操作的数据都是以页为单位的)加载到内存中来,下一次如果再次请求此页的数据的时候,就无需读取磁盘了,大大提高了速度。

    31、1981年出生的学生名单(注:Student表中Sage列的类型是datetime)

    变量赋值:

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

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

    Set:只能给一个变量进行赋值;select:可以给多个变量进行赋值

     

    from student

    如:

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

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

    select @sum=1,@i=9

     

    32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

    打印变量:

    6,加密存储过程

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

    Print  @变量名

    exec sp_helptext 储存过程名      可以查看储存过程代码

    33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

    条件:

    create proc proc_temp_encryption

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

    If    条件

    with encryption

    from Student,SC

    Begin    语句块

    as

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

    End

        select * from student;

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

    Else

    go

    Select Sname,isnull(score,0)

    Begin     语句块

    --存储过程的内容不会被轻易看到(虽然解密也是有可能的)。

    from Student,SC,Course

    End

    --应用这个,我们可以对某些关键的存储过程进行加密。

    where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;

    如:

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

    35、查询所有学生的选课情况;

    declare @number int,@size int

    exec proc_temp_encryption;

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

    select @number=100,@size=200

    exec sp_helptext 'proc_temp'

    FROM SC,Student,Course

    if @number<50

    exec sp_helptext 'proc_temp_encryption'

    where SC.S#=Student.S# and SC.C#=Course.C# ;

    begin

    (注意:加密存储过程前应该备份原始存储过程,且加密应该在部署到生产环境前完成。) 

    36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

    print @number

     

    SELECT distinct student.S#,student.Sname,SC.C#,SC.score

    end

    一 存储过程的概念

    FROM student,Sc

    else

    存储过程是在数据库管理系统保存的,预先编译的,能实现某种功能的SQL程序,它是数据库应用中运用比较广泛的一种数据对象。

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

    begin

    为什么需要存储过程?

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

    print @size

    1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时,可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

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

    end

    存储过程的优点:

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

    循环:(循环语句中只有while,死循环即为while  1=1)

    1.模块化程序设计

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

    While    条件

    2.执行速度块,效率高

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

    Begin    语句块

    3.减少网络流量

    select count(*) from sc;

    End

    4.具有良好的安全性

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

    如:

     

    select Student.Sname,score

    declare @sum int ,@i int

    二 系统存储过程

    from Student,SC,Course C,Teacher

    select @sum=1,@i=9

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

    while @i>0

    常用的系统存储过程

    系统存储过程

    说明

    sp_databases

    列出服务上的所有数据库

    sp_helpdb

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

    sp_renamedb

    更改数据库的名称

    sp_tables

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

    sp_columns

    返回某个表列的信息

    sp_help

    返回某个表的所有信息

    sp_helpconstraint

    查看某个表的约束

    sp_helpindex

    查看某个表的索引

    sp_stored_procedures

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

    sp_password

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

     

    41、查询各个课程及相应的选修人数

    begin

    三 用户自定义的存储过程

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

    set @sum=(@sum 1)*2

    1.创建不带参数的存储过程

    42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

    set @i=@i-1

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

    end

    As

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

    print @sum

    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

    Create  procedure  存储过程名   @变量名   变量类型,@变量名   变量类型

    2.创建带入参数的存储过程

    FROM SC

    As

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

    WHERE t1.C#= C#

    Begin

    As

    ORDER BY score DESC

    语句块

    Select * from student where gradeId=@gradeid

    )

    End

    调用存储过程:exec usp_stuInfo 2

    ORDER BY t1.C#;

    如:

    3.创建带出参数的存储过程

    44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列

    alter procedure monkey @sum int , @i int , @value int output

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

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

    as

    As

    from sc

    begin

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

    group by C#

    --declare @sum int,@i int

    print @gradeid

    order by count(*) desc,c#

    --select @sum=1,@i=9

     

    45、检索至少选修两门课程的学生学号

    while @i>0

    调用存储过程:

    select S#

    begin

    declare @id int

    from sc

    set @sum=(@sum 1)*2

    exec usp_selectGrade '李小龙',@id output 

    group by s#

    set @i=@i-1

     

    having count(*) > =2

    end

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

    46、查询全部学生都选修的课程的课程号和课程名

    --print @sum

    Create proc usp_one  @name nvarchar(10)

    select C#,Cname

    --return @sum

    as

    from Course

    set @value=@sum

    select * from dbo.Student where StudentName like @name

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

    end

     

    47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

    --print的

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

    execute monkey 1,9                                    //执行存储过程的语句

       5、 不缓存存储过程

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

    --return的

    缓存就是数据交换的缓冲区(称作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#;

    declare @sum int

    Sql Server系统内存管理在没有配置内存最大值,很多时候我们会发现运行Sql Server的系统内存往往居高不下。这是由于他对于内存使用的策略是有多少闲置的内存就占用多少,直到内存使用虑达到系统峰值时(预留内存根据系统默认预留使用为准,至少4M),才会清除一些缓存释放少量的内存为新的缓存腾出空间。

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

    execute @sum=monkey 1,9                           //执行存储过程的语句

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

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

    print @sum

    1. 数据缓存:执行个查询语句,Sql Server会将相关的数据页(Sql Server操作的数据都是以页为单位的)加载到内存中来,下一次如果再次请求此页的数据的时候,就无需读取磁盘了,大大提高了速度。

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

    --output的

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

    delete from Sc where S#='001'and C#='001';

    declare @sum int

     

    经典SQL语句大全

    execute monkey 1,9,@sum output              //执行存储过程的语句

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

    一、基础

    print @sum

     

    1、说明:创建数据库

     

    6,加密存储过程

    CREATE DATABASE database-name

    删除存储过程:

    exec sp_helptext 储存过程名      可以查看储存过程代码

    2、说明:删除数据库

    Drop   procedure   存储过程名

    create proc proc_temp_encryption

    drop database dbname

    3.数据库入门之触发器,游标,临时表,复制表

    with encryption

    3、说明:备份sql server

    3.1触发器

    as

    --- 创建 备份数据的 device

    触发器是一种特殊的存储过程,当指定的事件发生时,自动调用,用白话的理解就是比如说有学生表还有选课表,那么学号为一号的同学删除时,选课表中还存有一号同学的选课信息等需要一同删除,而触发器则能完成这一功能。

        select * from student;

    USE master

    触发器的种类有3种:插入、修改、删除触发器。在创建触发器时同时会创建两个表,inserted和deleted表

    go

    EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'

    删除:

    --存储过程的内容不会被轻易看到(虽然解密也是有可能的)。

    --- 开始 备份

    Create   trigger   触发器名

    --应用这个,我们可以对某些关键的存储过程进行加密。

    BACKUP DATABASE pubs TO testBack

    On   表名

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

    4、说明:创建新表

    After   delete

    exec proc_temp_encryption;

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

    As

    exec sp_helptext 'proc_temp'

    根据已有的表创建新表:

    Begin

    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

    End

     

    5、说明:删除新表

    如:

    drop table tabname

    create trigger mytrigger

    6、说明:增加一个列

    on student

    Alter table tabname add column col type

    after delete

    注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。

    as

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

    begin

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

    declare @s# varchar(3)

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

    set @s#=(select s# from deleted)

    删除索引:drop index idxname

    delete from sc where s#=@s#

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

    end

    9、说明:创建视图:create view viewname as select statement

     

    删除视图:drop view viewname

    delete from student where s#='01'

    10、说明:几个简单的基本的sql语句

     

    选择:select * from table1 where 范围

    插入:

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

    Create   trigger   触发器名

    删除:delete from table1 where 范围

    On   表名

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

    After   insert

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

    As

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

    Begin

    总数:select count as totalcount from table1

    语句块

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

    End

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

    如:

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

    create trigger myinsert

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

    on student

    11、说明:几个高级查询运算词

    after insert

    A: UNION 运算符

    as

    UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1就是来自 TABLE2。

    begin

    B: EXCEPT 运算符

    declare @s# varchar(3)

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

    set @s#=(select s# from inserted)

    C: INTERSECT 运算符

    insert into SC values(@s# , '01' , 80)

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

    insert into SC values(@s# , '02' , 90)

    注:使用运算词的几个查询结果行必须是一致的。

    insert into SC values(@s# , '03' , 99)

    12、说明:使用外连接

    end

    A、left (outer) join:

     

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

    insert into student values('01' , '赵雷' , '1990-01-01' , '男')

    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:

    修改:

    右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。

    Create   trigger   触发器名

    C:full/cross (outer) join:

    On   表名

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

    After  update

    12、分组:Group by:

    As

    一张表,一旦分组 完成后,查询后只能得到组相关的信息。

    Begin

    组相关的信息:(统计信息) count,sum,max,min,avg  分组的标准)

    语句块

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

    End

    在selecte统计函数中的字段,不能和普通的字段放在一起;

    如:

    13、对数据库进行操作:

    create trigger myupdate

    分离数据库: sp_detach_db;附加数据库:sp_attach_db 后接表明,附加需要完整的路径名

    on student

    14.如何修改数据库的名称:

    after update

    sp_renamedb 'old_name', 'new_name'

    as

    二、提升

    begin

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

    declare @s#old varchar(3),@s#new varchar(3)

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

    select @s#old=s# from deleted

    法二:select top 0 * into b from a

    select @s#new=s# from inserted

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

    update sc set s#=@s#new where s#=@s#old

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

    end

    3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

     

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

    update student set s#='01' where s#='11'

    例子:..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

     

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

    3.2游标

    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、说明:显示文章、提交人和最后回复时间

    游标的创建需要经历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

    declare @s# varchar(10),@sname varchar(50)

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

    --定义游标

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

    declare mycursor cursor for 

    8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括

    select s#,sname from student

    select * from table1 where time between time1 and time2

    --打开游标

    select a,b,c, from table1 where a not between 数值1 and 数值2

    open mycursor

    9、说明:in 的使用方法

    --遍历游标

    select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

    fetch next from mycursor into @s#,@sname

    10、说明:两张关联表,删除主表中已经在副表中没有的信息

    while @@FETCH_STATUS=0       //fetch函数的返回值,为0时有值

    delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

    begin

    11、说明:四表联查问题:

    print @s# '---------' @sname

    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 .....

    fetch next from mycursor into @s#,@sname

    12、说明:日程安排提前五分钟提醒

    end 

    SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

    --关闭游标

    13、说明:一条sql 语句搞定数据库分页

    close mycursor

    select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

    --释放内存

    具体实现:

    deallocate mycursor

    关于数据库分页:

    3.3创建临时表

    declare @start int,@end int

    Create  table  #临时表名---------------创建

    @sql  nvarchar(600)

    Drop   table   #临时表名---------------删除

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

    3.4复制表

    exec sp_executesql @sql

    不存在的表:

    注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引)

    Select  *  into  新起的一个不存在的表名   from   已有的表名

    14、说明:前10条记录

    如:

    select top 10 * form table1 where 范围

    Select   *   into   aaa    from    student

    15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

    存在的表:

    select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

    Insert  into   表名(存在的表名)  内容

    16、说明:包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行而派生出一个结果表

    如:

    (select a from tableA ) except (select a from tableB) except (select a from tableC)

    Insert   into   aaa    select   *   from   student

    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、说明:更改某个表

    新葡亰496net,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) [下一步] 设置快照文件夹

    采用默认\servernamePub

    (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

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

    本文由新葡亰496net发布于网络数据库,转载请注明出处:数据库重要知识点总结,SERVER数据库学习总结

    关键词: