一些SQL Server基本操作（一）

初学，第一次碰……其实是因为作业要写实验报告，所以顺便写写文章，也算留一个存档。

#实验要求具体就不描述了

实验要求：

1,创建Student数据库，包括Students,Courses,SC表，表结构如下…

　　#1. 先手动用SQL Server 2014 Management Studio添加新表，新列

　　#2. 遇到第一个报错：

　　　　不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的标进行了更改或者启用了“阻止保存要求重新创建表的更改”选项

　　　解决：http://www.jb51.net/article/30416.htm

　　　工具-〉选项-〉左侧有个 设计器-〉表设计器和数据库设计器 -> 阻止保存要求重新创建表的更改(右侧) 把钩去掉即可。

　　#2. 手打了Students表和Courses表

　　#3. 嫌SC表内容太多，一个个手打太麻烦，然后直接复制了4行下去，作了个大死！开始报错：

　　　　SQL 已更新或删除的行值要么不能使该行成为唯一行(sqlserver中有多行所有的列重复的数据，无法更新或删除问题)

　　　参考：http://www.cnblogs.com/jhxk/articles/1614963.html

　　　试了好久，并没有成功【主要因为自己一开始不知道怎么搞】，然后干脆删了表（反正我就写了那么几行，删了就删了）重新建一个新的，

　　　然后开始批量生产，就是很普通的插入语句，大致如下：

USE [Student]
GO

INSERT INTO [dbo].[SC]
           ([SNO]
           ,[CNO]
           ,[GRADE])
     VALUES
           ('0409011','EE-006',87);
GO

 

　　　妈妈再也不用担心我不能复制粘贴了。差不多第一个题勉强这么解决。

2.1.查询身高大于1.80m的男生的学号和姓名；

 

 　　#1. 直接SELECT语句

USE [Student]
GO

SELECT [SNO]
            ,[SNAME]
    FROM [dbo].[Students]
    WHERE HEIGHT>1.80 AND SEX='男';
GO

　　　　截图如上

2.2.查询计算机系秋季所开课程的课程号和学分数

　　#1. 由于计算机系课程默认以SC开头，所以查找字段中包含某字符串的结果

　　　参考：http://blog.itpub.net/22392018/viewspace-772268/

 

USE [Student]
GO

SELECT [CNO]
      ,[CREDIT]
    FROM [dbo].[Courses]
    WHERE PATINDEX('%CS%',CNO)>0 AND SEMESTER='秋'
GO

 

　　　　截图如上

2.3查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩

　　#1. 有关计算机秋的查询如上题，本次是三表联查

　　　参考：http://zhidao.baidu.com/question/465019816.html

USE [Student]
GO

SELECT [dbo].[Courses].[CNO]
      ,[dbo].[Courses].[CREDIT]
      ,[dbo].[Students].[SNAME]
      ,[dbo].[SC].[GRADE]
  FROM [dbo].[SC]
  LEFT JOIN[dbo].[Courses]ON[dbo].[SC].[CNO]=[dbo].[Courses].[CNO]
  LEFT JOIN[dbo].[Students]ON[dbo].[SC].[SNO]=[dbo].[Students].[SNO]

  WHERE PATINDEX('%CS%',[dbo].[SC].[CNO])>0 AND SEMESTER='秋' AND [dbo].[Students].[SEX]='男'

GO

　　　　截图如上

2.4查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）

　　#1. 并没有用到新的知识，直接贴代码

 

USE [Student]
GO

SELECT [dbo].[Students].[SNAME]
  FROM [dbo].[Students]
  LEFT JOIN[dbo].[SC]ON[dbo].[SC].[SNO]=[dbo].[Students].[SNO]

  WHERE PATINDEX('%CS%',[dbo].[SC].[CNO])>0 AND [dbo].[Students].[SEX]='女'

GO

 

　　　让人忧伤的是，查询出来的是所有的记录，一直想知道怎么让它一个名字只出来一条，然而并没有找到办法。【此处未完日后待续】

　　　#第二天发现谜之答案之后得到解决——DISTINCT

USE [Student]
GO

SELECT DISTINCT [dbo].[Students].[SNAME]
  FROM [dbo].[Students]
  LEFT JOIN[dbo].[SC]ON[dbo].[SC].[SNO]=[dbo].[Students].[SNO]

  WHERE PATINDEX('%CS%',[dbo].[SC].[CNO])>0 AND [dbo].[Students].[SEX]='女'

GO

2.5查询每位学生已选修课程的门数和总平均成绩

　　#1. 查询每位学生的已修课程COUNT()，总平均成绩AVG()

USE [Student]
GO

SELECT [dbo].[Students].[SNO]
        ,AVG([dbo].[SC].[GRADE]) 平均成绩
        ,COUNT([dbo].[SC].[CNO]) 课程门数
    FROM [dbo].[SC]
  LEFT JOIN[dbo].[Courses]ON[dbo].[SC].[CNO]=[dbo].[Courses].[CNO]
  LEFT JOIN[dbo].[Students]ON[dbo].[SC].[SNO]=[dbo].[Students].[SNO]
  GROUP BY [dbo].[Students].[SNO]

2.6查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩

　　#1. 新知识为最大项MAX()和最小项MIN()

USE [Student]
GO

SELECT [dbo].[Courses].[CNO]
        ,COUNT([dbo].[SC].[SNO]) 选课人数
        ,AVG([dbo].[SC].[GRADE]) 平均成绩
        ,MAX([dbo].[SC].[GRADE]) 最高成绩
        ,MIN([dbo].[SC].[GRADE]) 最低成绩
    FROM [dbo].[Courses],[dbo].[SC]
    WHERE [dbo].[SC].[CNO]=[dbo].[Courses].[CNO]
    GROUP BY [dbo].[Courses].[CNO]

2.7查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列

　　#1. 想法是查询最低成绩在80分以上，则所有成绩都在80分以上，想当然的使用 GROUP BY MIN([dbo].[SC].[GRADE])>=80 报错，提示使用HAVING

　　　参考：http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

　　　　GROUP BY 和 HAVING 讲的很多

USE [Student]
GO

SELECT [dbo].[Students].[SNO]
        ,[dbo].[Students].[SNAME]
    FROM [dbo].[Students]
    LEFT JOIN [dbo].[SC] ON [dbo].[SC].[SNO]=[dbo].[Students].[SNO]
    GROUP BY [dbo].[Students].[SNO],[dbo].[Students].[SNAME]
    HAVING MIN([dbo].[SC].[GRADE])>=80
    ORDER BY [dbo].[Students].[SNO]

　　结果只有一个人（其实一个人都没有，后来改了数据orz）

2.8查询缺成绩的学生的姓名，缺成绩的课程号及其学分数

　　#1.新知识IS NULL 为空

USE [Student]
GO

SELECT [dbo].[Students].[SNAME]
        ,[dbo].[Courses].[CNO]
        ,[dbo].[Courses].[CREDIT]
    FROM [dbo].[SC]
    LEFT JOIN[dbo].[Courses]ON[dbo].[SC].[CNO]=[dbo].[Courses].[CNO]
    LEFT JOIN[dbo].[Students]ON[dbo].[SC].[SNO]=[dbo].[Students].[SNO]
    WHERE [dbo].[SC].[GRADE] IS NULL

　　搜不到，然后任性的加了一个试验品hhhh

2.9查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；

　　#1. 世界上最狗血的事情就是，当我想好好写数据库作业的时候，一百度发现了全套答案

　　发张图表达一下这操蛋的心情，我不抄我不抄，我就看一眼

　　链接：http://3y.uu456.com/bp_1jk0h81auc7zlrk1b2th_1.html

　　看了一下前面题的答案，还是不错的，至少解决了之前未完待续的问题棒棒哒！不过我前题做法比答案复杂不止一点点呢……

　　　　WHERE STUDENTS.SNO=SC.SNO 就能代替我所有的 LEFT JOIN （话说我之前是哪里百度出LEFT JOIN的写法）

　　参考了答案之后撸出这道题，然后发现其实这个题很简单orz

USE [Student]
GO

SELECT [dbo].[Students].[SNAME]

    FROM [dbo].[SC],[dbo].[Courses],[dbo].[Students]
    WHERE [dbo].[SC].[SNO]=[dbo].[Students].[SNO] 
            AND [dbo].[SC].[CNO]=[dbo].[Courses].[CNO] 
            AND [dbo].[Courses].[CREDIT]>=3 
            AND [dbo].[SC].[GRADE]<70
/*上面是答案的方法，下面是按我之前风格写的，
12   区别在于另外两个表联法不同*/
USE [Student] 
GO 

SELECT [dbo].[Students].[SNAME] 
　　FROM [dbo].[SC] 
　　LEFT JOIN[dbo].[Courses]ON[dbo].[SC].[CNO]=[dbo].[Courses].[CNO] 
　　LEFT JOIN[dbo].[Students]ON[dbo].[SC].[SNO]=[dbo].[Students].[SNO] 
　　WHERE [dbo].[Courses].[CREDIT]>=3 AND [dbo].[SC].[GRADE]<70

 

 

 

2.10查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数

　　#1. 挺简单的BETWEEN AND

 

USE [Student]
GO

SELECT [dbo].[Students].[SNAME]
        ,COUNT([dbo].[Courses].[CREDIT]) 已修学分
        ,AVG([dbo].[SC].[GRADE]) 平均成绩
    FROM [dbo].[SC]
    LEFT JOIN[dbo].[Courses]ON[dbo].[SC].[CNO]=[dbo].[Courses].[CNO]
    LEFT JOIN[dbo].[Students]ON[dbo].[SC].[SNO]=[dbo].[Students].[SNO]
    WHERE [dbo].[Students].[BDATE] BETWEEN '1984-01-01' AND '1986-12-31'
    GROUP BY [dbo].[Students].[SNAME]

 

2.11在STUDENT和SC关系中，删去SNO以’01’开关的所有记录。

　　#这个不知道什么意思呢。。。看了一眼答案发现题目的开关应该是开头orz。。。= =还以为是什么很高级的东西的说

　　而且答案还错了

 

USE [Student]
GO

DELETE FROM [dbo].[SC]
      WHERE [dbo].[SC].[SNO] LIKE '%01%'
GO

DELETE FROM [dbo].[Students]
      WHERE [dbo].[Students].[SNO] LIKE '%01%'
GO

 

 

2.12在STUDENTS关系中增加以下记录：

　　#直接INSERT跟第1题一样

 

USE [Student]
GO

INSERT INTO [dbo].[Students]
           ([SNO]
           ,[SNAME]
           ,[SEX]
           ,[BDATE]
           ,[HEIGHT]
           ,[DEPARTMENT])
     VALUES
           ('0409101','何平','女','1987-03-02',1.62,''),
           ('0408130','向阳','男','1986-12-11',1.75,'');
GO

 

2.13将课程CS-221的学分数增为3，讲课时数增为60

 

　　#UPDATE语句

 

USE [Student]
GO

UPDATE [dbo].[Courses]
   SET [CREDIT] = 3
      ,[LHOUR] = 60
 WHERE [dbo].[Courses].[CNO]='CS-211'



GO

 

/**************************************我是萌萌哒的分割线**************************************/

　　第一次写日志，并不是什么特别有意义的东西QAQ

　　但是，毕竟第一次就当鼓励自己了，应该除了我之外没人看吧，我就当日记写了orz

　　好好加油，无论现在多么凄惨，一年之后一定能遇见不一样的自己！