第五周学习笔记

课堂上的内容可归纳为sql server 2008 r2 的实际应用

首先我们需要打开sql server

选择开始菜单中→程序→【Management SQL Server 2008】→【SQL Server Management Studio】命令，打开【SQL Server Management Studio】窗口，并使用Windows或 SQL Server身份验证建立连接

亦可通过快捷键WIN+R，输入SMSS打开

然后，我们进行数据库建立的工作

因为课程原因，所有建库建表的操作全部由代码进行。

例：新建数据库，名为EduBase2018，

存储位置为E盘下的EduBase2018文件夹，代码如下：

CREATE DATABASE EduBase2018                                              

       ON                                    

              (NAME='DataFile_1'                                

              ,FILENAME='E:\EduBase2018\DataFile_1.mdf')                            

       LOGON                                   

              (NAME='LogFile_1'                                 

              ,FILENAME='E:\EduBase2018\LogFile_1.ldf');                               

执行成功后，刷新即可看到数据库已经建立

若要删除数据库，此时执行如下代码即可删除：

USE master;                              

DROP DATABASE EduBase2018;

 

若要建立数据库EduBase2018，并可能存在已经建立的情况下，执行：

IF DB_ID('EduBase2018') IS NOT NULL                                        

       DROPDATABASE EduBase2018;                           

CREATE DATABASE EduBase2018;                                      

或                                     

IF DB_ID('EduBase2018') IS NULL                                  

       CREATEDATABASE EduBase2018;                                                           

 

下面进行建表的操作：

在习惯上，我们使用大写进行代码的编辑，并将分号移至下一行来提醒逻辑顺序

例：在数据库EduBase2018中建立一个学生表，表名为tb_Student

其中包含学号（10位数字，非空，主键）、姓名（至多10汉字，非空）、性别（男为1，女为0）                                                        

出生日期（有效范围1990-01-01至2010-12-31）、                                                           

所在专业（至多10汉字，默认“未分配”）                                                            

USE EduBase2018;                                                       

IF OBJECT_ID('tb_Student') IS NOT NULL                                                          

       DROPTABLE tb_Student;                                                    

GO                                                         

CREATE TABLE tb_Student                                                         

       (No                                                       

              CHAR(10)                                              

              NOTNULL                                              

              CONSTRAINTpk_Student_No                                             

                     PRIMARYKEY(No)                                   

              CONSTRAINTck_Student_No                                              

                     CHECK(NoLIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')                                     

       ,Name                                                  

              VARCHAR(20)                                              

              NOTNULL                                              

       ,Gender                                                  

              BIT                                           

              NOTNULL                                              

       ,BirthDate                                                      

              DATE                                               

              CONSTRAINTck_Student_BirthDate                                           

                     CHECK(BirthDate BETWEEN '1990-01-01' AND '2010-12-31')                                      

       ,Major                                                    

              VARCHAR(20)                                         

              NOTNULL                                              

              CONSTRAINTdf_Student_Major                                          

                     DEFAULT('未分配'));                                       

完成后结果如下

当需要插入一条新的信息的时候，我们执行如下代码：

例：在EduBase2018中，将一名新学生记录

（学号3180707001，姓名张三，性别男，生日2001-5-1，所在专业信管）

插入到tb_Student表中                                                      

INSERT INTO tb_Student                                                     

       (No       

       ,Name

       ,Gender 

       ,BirthDate

       ,Major)  

       VALUES 

       ('3180707001'

       ,'张三'

       ,1

       ,'2001-05-01'

       ,'信管');  

若专业未分配，则将

信管改为DEFAULT（未分配），

由于其余课程并未结束，我们将在下周的学习笔记中继续sql操作的详细记录。