Database Experiment 2

Experiment 2: create and manage database

Experimental content

1. Create database
Assume that the SQL Server service has been started and log in to the computer as Administrator (the file name is customized); Please use the Management interface mode and T-SQL statement respectively to realize the following operations:

  1. It is required to create a student course database (named student) on local disk D, with only one data file and log file, and the file names are stu and stu respectively_ Log, physical name stu_data.mdf and stu_log.ldf, the initial size is 3MB, the growth mode is 10% and 1MB respectively, the maximum data file is 500MB, and the log file size is unlimited.
CREATE DATABASE student  
ON  
(NAME = stu,  
FILENAME = 'D:\stu_data.mdf',  
SIZE = 3MB,  
MAXSIZE = 500MB,  
FILEGROWTH = 10%)  
LOG ON  
(NAME = stu_log,  
FILENAME = 'D:\stu_log.ldf',  
SIZE = 3MB,  
MAXSIZE = unlimited,  
FILEGROWTH = 1MB) 
  1. Create a Company database whose master data file logical name is Company_data, the physical file is Company.mdf, the initial size is 10MB, the maximum size is infinite, and the growth rate is 10%; The logical name of the log file for the database is Company_log, the physical file name is Company.ldf, the initial size is 1MB, the maximum size is 50MB, and the growth rate is 1MB.
CREATE DATABASE Company  
ON  
(NAME = Company_data,  
FILENAME = 'C:\Company.mdf',  
SIZE = 10MB,  
MAXSIZE = unlimited,  
FILEGROWTH = 10%)  
LOG ON  
(NAME = Company_log,  
FILENAME = 'C:\Company.ldf',  
SIZE = 1MB,  
MAXSIZE = 50MB,  
FILEGROWTH = 1MB)  
  1. Create a database DB with 2 data files with logical names of DB_data1 and DB_data2, the initial size of the files is 5MB, and the maximum is 100MB, increasing by 10%; There is only one log file, with an initial size of 3MB, increasing by 10%; All files are stored in the D disk folder ceshi.
CREATE DATABASE DB  
ON  
(NAME = DB_data1,  
FILENAME = 'D:\ceshi\DB_data1.mdf',  
SIZE = 5MB,  
MAXSIZE = 100MB,  
FILEGROWTH = 10%),  
(NAME = DB_data2,  
FILENAME = 'D:\ceshi\DB_data2.mdf',  
SIZE = 5MB,  
MAXSIZE = 100MB,  
FILEGROWTH = 10%)  
LOG ON  
(NAME = Company_log,  
FILENAME = 'D:\ceshi\DB_log.ldf',  
SIZE = 3MB,  
MAXSIZE = unlimited,  
FILEGROWTH = 10%)  

2. Modify database

  1. Add the data file db2 in the database student, with the initial size of 10MB and the maximum size of 50MB, increasing by 10%;
ALTER DATABASE student  
ADD FILE  
( NAME = db2,  
SIZE = 10MB,  
MAXSIZE = 50MB,  
FILENAME = 'D:\db2.mdf',  
FILEGROWTH = 10%)  
  1. Add log files in the database student and save them in disk D. the initial size is 1MB, the maximum is unlimited, and the growth mode is 1MB;
ALTER DATABASE student  
ADD LOG FILE  
( NAME = df,  
SIZE = 1MB,  
MAXSIZE = unlimited,  
FILEGROWTH = 1MB,  
FILENAME = 'D:\df.ldf')  
  1. Modify the size of the master data file of the database student, and modify the initial size of the master data file to 10Mb, with a growth mode of 20%;
ALTER DATABASE student  
MODIFY FILE  
( NAME = stu,  
SIZE = 10MB,  
FILEGROWTH = 20%)  

3. Delete database

  1. Delete the database student auxiliary data file and the second log file;
ALTER DATABASE student  
remove FILE db2  
go  
ALTER DATABASE student  
remove FILE df  
  1. Delete the database company.
DROP DATABASE Company  

4. Create and delete mode (framework)

  1. Create a stu schema in the student database for dbo users.
CREATE SCHEMA stu AUTHORIZATION dbo 
  1. Create a new user test in the student database and create a default schema for the user.
CREATE SCHEMA AUTHORIZATION test  
  1. Delete the stu schema from the student database.
DROP SCHEMA stu  

Thinking questions

Please describe in detail how to move all the physical files of the database student to'D:\xxx 'and ensure that the logical database is not affected.

First separate the database, then move the database file, and finally attach it.

Tags: Database SQL

Posted on Sat, 20 Nov 2021 07:02:17 -0500 by The Swedish Tower