数据库基本命令

创建数据库（主数据文件只能创建一个）

        Create database 数据库名称

        ON

        ( Name=数据文件逻辑名称,

        Filename=’路径+数据文件名’,

        Size=数据文件初始大小,

        Maxsize=数据文件最大容量,

        Filegrowth=数据文件自动增长容量,)

        LOG ON

        ( Name=日志文件逻辑名称,

          Filename=’路径+日志文件名’,

         Size=日志文件初始大小,

        Maxsize=日志文件最大容量,

        Filegrowth=日志文件自动增长容量,)

例：创建library数据库

1. 主数据文件逻辑名为：library_data1，物理文件名library_data1.mdf
2. 日志文件逻辑名为：library_log，物理文件名library_log.ldf

CREATE DATABASE library

ON

(NAME=library_data1,

 FILENAME='D:\library_data1.mdf',

 SIZE=10,

 MAXSIZE=500,

 FILEGROWTH=10)

LOG ON

(NAME=library_log,

 FILENAME='D:\library_log.ldf',

 SIZE=10,

 MAXSIZE=500,

FILEGROWTH=5)

修改数据库

        Alter database：在数据库中添加或删除文件和文件组、更改数据库属性或其文件和文件组、更改数据库排序规则和设置数据库选项。

        Add file：向数据库中添加数据文件。

        Add log file：向数据库中添加日志文件。

        Remove file：从数据库中删除逻辑文件，并删除物理文件。如果文件不为空，则无法删除。

        Modify file：指定要修改的文件。

        Add filegroup：向数据库中添加文件组。

        Remove filegroup：从数据库中删除文件组。若文件组非空，无法将其删除，需要先从文件组中删除所有文件。

        Modify filegroup：修改文件组名称、设置文件组的只读（read_only）或者读写（read_write）属性、指定文件组为默认文件组（default）。

例：1.修改library数据库增加一个辅助数据文件，逻辑名library_data2，物理文件名library_data2.ndf，初始大小5MB，最大尺寸为30MB，增长速度1MB。

ALTER DATABASE library

   ADD FILE

(NAME=library_data2,

 FILENAME='D:\202106034119\library_data2.mdf',

 SIZE=5,

 MAXSIZE=30,

 FILEGROWTH=1)

        2.修改library数据库中library_data1文件增容方式为一次增加20MB

Alter database library

Modify file

( name=library_data1,

Filedrowth=20)

        3.从library数据库中删除增加的辅助数据文件。

Alter database library

Remove file library_data2.mdf

        4.删除数据库library

Drop database library

查看数据库

        1.显示library数据库结构

        Exec Sp_helpdb library

        2.显示library中sc表文件信息

        Exec Sp_helpfile sc

创建表

        数据表的约束：NULL/NOT NULL、UNIQUE（唯一约束）、PRIMARY Key（主键约束）、FOPEIGN Key（外键约束）、check（检查约束）

Use library

GO

create table 学生表

( 学号 char(4) primary key,

姓名 varchar(20) not null,

性别 char(2) check(性别='男'or 性别='女') not null,

专业班级 char(24) not null,

出生日期 smalldatetime not null,

联系电话 char(11) 

)



create table 课程表

( 课程号 char(4) primary key,

课程名 varchar(20) not null,

学分数 numeric(3,1) not null,

学时数 int not null,

任课教师 varchar(20) not null

)



create table 学生作业表

( 课程号 char(4) not null,

学号 char(4) not null,

作业1成绩 int,

作业2成绩 int,

作业3成绩 int,

primary key(课程号,学号)

)

​​​​修改数据表

        1.Add方式     Alter table 表名 add 列定义或完整性约束

               例： 在S表中增加班号列

                        Alter table S add Class_No varchar(6)

                        在SC表中增加完整性约束，使Score在0-100之间

                        Alter table SC add constraint Scoer_Chk check(Score between 0 and 100)

        2.Alter方式   Alter table 表名 alter column 列名 数据类型 NULL/NOT NULL

                例：将作业1成绩数据类型修改为int，并将非空约束修改为允许为空

                        Alter table 学生作业表 alter column 作业1成绩 int null

        3.drop方式（只用于删除完整性约束）  alter table 表名 drop constraint 约束名

                例：删除S表中的主键

                        Alter table S drop constraint S_Prim

        4.删除表

                drop table 表名

        5.往表中添加数据

                Insert into 表名(列名) values(要添加的数据)

        6.修改数据

                Update 表名 set 列名=表达式 where 条件

        7.删除数据

                Delete from 表名 where 条件

表的查询

        1．查询各位学生的学号、班级和姓名。

                Select 学号,专业班级,姓名 from 学生表

        2．查询课程的全部信息。

                Select  * from 课程表

        3．查询数据库中有哪些专业班级。（distinct 去除重复值）

                Select distinct 专业班级 from 学生表

        4.查询学时数大于60的课程信息。

                Select * from 课程表 where(学时数>60)

        5．查询在1986年出生的学生的学号、姓名和出生日期。

                Select 学号,姓名,出生日期 from 学生表 where 出生日期>='1986-1-1' and 出生日期<='1987-1-1'

                Select 学号,姓名,出生日期 from 学生表 where 出生日期 between '1986-1-1' and '1987-1-1'

        6．查询姓张的学生的学号、姓名和专业班级。(模糊查找 %代表0个或多个字符、_代表一个字符、[ ]表示在某一范围内的字符、[^]表示不在某一范围内的字符)

                Select 学号,姓名,专业班级 from 学生表  where 姓名 like '张%'

        7．查询05级的男生信息。

                Select *from 学生表 where (专业班级 like '%05') and (性别='男')

        8．查询没有作业成绩的学号和课程号。

                Select 学号,课程号 from 学生作业表 where(作业1成绩 is NULL or 作业2成绩 is NULL or 作业3成绩 is NULL)

        9．查询学号为0538的学生的作业1总分。

                Select SUM(作业1成绩) as 总分 from 学生作业表 where 学号='0538'

        10．查询选修了K001课程的学生人数。

                Select COUNT(学号) as 人数 from 学生作业表 where 课程号='K001'

        11．查询数据库中共有多少个班级。

                Select 学号,AVG(作业1成绩) as 作业1平均分,AVG(作业2成绩) as 作业2平均分,AVG(作业3成绩) as 作业3平均分 from 学生作业表 GROUP BY 学号 HAVING (COUNT(*)>=3)

        12．查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。

                连接查询

                Select 学生表.学号,学生表.姓名,课程表.课程名  from 学生表,课程表,学生作业 where 姓名='于兰兰' and 学生表.学号 = 学生作业表.学号 and 学生作业表.课程号 = 课程表.课程号

                内连接查询

                Select 学生表.学号,学生表.姓名,课程表.课程名 from 学生表 INNER JOIN 学生作业表 on 学生表.学号 = 学生作业表.学号 INNER JOIN 课程表 on 学生作业表.课程号 = 课程表.课程号 where 姓名='于兰兰'

        13.查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。

                子查询：Select * from 学生表 where 专业班级 = (Select 专业班级 from 学生表 Where 姓名='张志国')

                自连接查询：Select X.* from 学生表 as X,学生表 as Y Where X.专业班级=Y.专业班级 and Y.姓名='张志国'

        14.查询选修课程号为K002的学生的学号、姓名（使用exists关键字的相关子查询）。

                exists查询：Select 学号,姓名 from 学生表 where exists (Select 学号 from 学生作业表 where 学生表.学号=学生作业表.学号 and 课程号='K002')

索引和视图

        1．以任课教师和课程名的升序方式为课程表创建一个复合非聚集索引。（NONCLUSTERED代表非聚集索引，Create INDEX 创建索引）

                Create NONCLUSTERED INDEX 课程表I on 课程表(任课教师,课程名)

        2．创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。 Create view 创建视图

                Create view 学生作业平均成绩视图 (学号,作业1平均成绩,作业2平均成绩,作业3平均成绩) as select 学号,AVG(作业1成绩), AVG(作业2成绩), AVG(作业3成绩) from 学生作业表 group by 学号

        3.修改第3题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉。

               Alter view 学生作业视图 as select 学生表.学号,姓名,课程名,作业1成绩 from 学生表,学生作业表,课程表 where 专业班级='生物05' and 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号

        4.删除电子05的学生视图中赵亦的记录。

                Delete from 学生视图 where 姓名='赵亦'

        5.删除电子05的学生视图。

                Drop view 学生视图