如果1大于0就会一直向表中插入姓名,为了创建良

作者: 网络时代  发布:2019-11-07

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

  对于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)

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

巴黎人游戏官网 1

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

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

   建数据库的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就会一直向表中插入姓名

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

如下图所示:

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

巴黎人游戏官网 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

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

下面是创建约束

巴黎人游戏官网 3

语法如下

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

alter table 表名

巴黎人游戏官网 4

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

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

示例

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

创建外键约束

巴黎人游戏官网 5

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

为了下面分析文件更准确,多执行几次Select* from 学生表 Where 学号=900000

插入数据语句如下

然后把跟踪的结果保存在桌面上:

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)

 巴黎人游戏官网 6

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

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

创建变量语法如下是

 巴黎人游戏官网 7

declare @变量名 数据类型

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

局部变量有两种赋值方法

 巴黎人游戏官网 8

set @变量名=value

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

select @变量名=value

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

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

巴黎人游戏官网 9

全局变量只能用不能自己创建和赋值!!!

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

输出语句

 

print 和select

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

use MySchool

巴黎人游戏官网 10

go

发现总计时间为1毫秒,几乎忽略不计

select * from StuInfos

 

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

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

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

1、创建聚集索引

drop table Classes

目前tstudent表中没有任何索引也没有主键

  go  

 巴黎人游戏官网 11

 create table Classes  

为tstudent表创建聚集索引

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

巴黎人游戏官网 12

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

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

go   

 巴黎人游戏官网 13

'alter table Classes

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

add constraint pk_clsid_Classes   primary key(clsid)  

巴黎人游戏官网 14

 alter table Classes

2、创建组合索引

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

 巴黎人游戏官网 15

巴黎人游戏官网 16

巴黎人游戏官网 17

巴黎人游戏官网 18

解决办法:

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

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

 巴黎人游戏官网 19

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

 巴黎人游戏官网 20

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

3、创建唯一索引

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

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

create unique nonclustered index U_cardID on TStudent(cardID)

巴黎人游戏官网 21

 alter table Classes

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

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

巴黎人游戏官网 22

使用命令查看表上的索引

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

Indid中1代表聚集索引

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

Indidz中3代表非聚集索引

巴黎人游戏官网 23

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

巴黎人游戏官网 24

实验案例三:创建视图

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

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

 巴黎人游戏官网 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   

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

巴黎人游戏官网 26

go  

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

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

alter table StuInfos with nocheck --不对表现有的数据作限制操作

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

删除约束   

alter table StuInfos drop constraint fk_StuInfos_Classes

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

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

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

drop table TeaInfos  

 go  

 create table TeaInfos  

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

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

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

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

tel char(11) unique, --电话

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

  go

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

declare   declare @temp int --声明   

set @temp=1       --赋值  

 print @temp       --输出   

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

 print @temp         --输出   

 

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

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

赋值方式二:用select 输出方式二:用select --

2-- 声明三个变量存储'姓名、性别、年龄',再打印信息,格式如下: 姓名:杰达姆,性别:男,年龄:18 --声明

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

--赋值 select @name='杰达姆',@sex='男',@age=18

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

--convert(varchar,@age)

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

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

--3-- select * from StuInfo

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

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

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

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

--5-- 查询比张秋丽大10岁以上的学生信息 declare @age int  select @age=stuAge from stuinfo where stuName='张秋丽' select * from stuinfo where stuAge>=10 @age 

总结: set和select的区别  

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

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

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

本文由巴黎人游戏官网发布于网络时代,转载请注明出处:如果1大于0就会一直向表中插入姓名,为了创建良

关键词: