Database principle and application experiment one [SQL statement to create database and table]


1. The purpose of the experiment

1. Familiar with the SQL Server 2012 operation interface and understand the logical components of the database system;
2. Familiar with the SQL Server management console operating environment;
3. Master the method of using SQL Server Management Studio and SQL statements to create databases and tables;
4. Master the database and Table structure modification method;
5. Master the basic operations of database management.


2. Experimental steps

1.启动SSMS(SQL Server Management Studio)
2.通过向导创建数据库
3.通过T-SQL语句创建数据库
4.通过向导创建数据库表
5.通过T-SQL语句创建数据库表

3. Experimental content

1. Start SSMS (SQL Server Management Studio)

(1) Open SQL Server Management Studio, and the login window as shown in the figure below will pop up.

Insert picture description here
There are two authentication methods: The first method chooses Windows authentication login, the server name is the local user name (such as: WIN-E1G5CJSSRP5) or (local) login; the second method chooses SQL Server authentication login, user The name is sa, and the password is the password set when the database is installed. Click the Connect button to connect (Note: If the server cannot be connected, open the service under the management tool in the control panel and check whether the SQL Server service is started).

(2) Right-click the SQL Server server, and the menu command as shown in the figure below will pop up. Click [Disconnect], and the connection and communication between the current database management tool application and the SQL Server server will be disconnected.

Insert picture description here


(3) Click [Pause] in the menu command to pause the current SQL Server service. Click [Stop] in the menu command to stop the currently running SQL Server service. Click [Restart] in the menu command to restart the currently stopped SQL Server service.

(4) Click [Properties] in the menu command, and the dialog box shown below will pop up. This dialog box defines many SQL Server instance-level configuration parameters. From this dialog box, you can also modify these configuration parameters.

Insert picture description here

2. Create a database through the wizard

(1) Create a database in the "Object Explorer" window, the database name is "EDUC + the last 2 digits of the student ID", such as EDUC01. Right-click on the [Database] node and select [New Database], and set various parameters of the database according to your own needs in the pop-up dialog box.
(2) After setting the parameters, click the [OK] button. In the "Object Explorer" window, under the [Database] node, a database named "EDUC + the last 2 digits of the student number" will appear.
(3) Select the newly created database, click the right mouse button, and complete operations such as modification, deletion, renaming, expansion, attachment, and separation of the database by selecting different menu items.

Insert picture description here

3. Create a database through T-SQL statements

(1) Click the [New Query] button on the SQL Server Management Studio toolbar to open the query analyzer window.
(2) Enter the T-SQL statement in the query analyzer window to complete the creation of the database named "EDUC + the last 2 digits of the student number", and set the parameters for creating the database according to your own needs.

4. Create a database table through the wizard

(1) Create 3 tables in the database just created, namely the student table, the course table, and the sc (course selection) table.

Insert picture description here


Insert picture description here


Insert picture description here

5. Create database tables through T-SQL statements

//(1)根据上述的表的结构,在查询分析器窗口中输入T-SQL语句来创建各个表。
Create table student1
(
    Sno char(10) NOT NULL PRIMARY KEY,
    Sname varchar(10) NOT NULL ,
    Ssex char(2) NOT NULL default '男',
    Sage smallint check(Sage BETWEEN 16 AND 25),
    Sdept char(12) NULL 
)
Create table course1
(
    Cno char(12) NOT NULL PRIMARY KEY,
    Cname varchar(30) NOT NULL UNIQUE(Cname),
    Cteacher varchar(10) NOT NULL,
    Csemester smallint NULL,
    Ccredit float NULL  
)
Create table sc1
(
    Sno char(10) NOT NULL,
    Cno char(12) NOT NULL,
    Score decimal(3,1) NULL check(Score BETWEEN 0 AND 100),
    Constraint M PRIMARY KEY(Sno,Cno) 
)
//(2)根据自身需求,在查询分析器窗口中输入T-SQL语句来修改一个表的结构。
//添加一列:
alter table Student add1 Phone char(11)
//删除一列:
alter table Student1 drop column sdept
//修改字段的数据类型:
alter table Student1 alter column ssex char
//(3) NOT NULL 

Fourth, the experiment summary

Through the study of this lesson, I am familiar with the SQL Server 2012 operating interface and the logical components of the database system; familiar with the SQL Server management console operating environment; master the method of using SQL Server Management Studio and SQL statements to create databases and tables; master databases and tables Structure modification method; master the basic operations of database management. The use of SQL statements is not yet skilled.