Create, manage, and operate database tables (experiment 1)

Create, manage, and operate database tables (experiment 1)

Today, we will learn how to create, manage, and operate tables in an experiment.

[PURPOSE ]:Understand the use of the SQL language, further understand relational operations, and consolidate the basic knowledge of the database.
[Lab requirements ]:Perform operations on database tables using the SQL language:
1. create, modify, and delete database tables.
2. Insert, delete, and modify data in a table.
[Experiment content]
1. Use Data Definition statements to create three tables of the Student management system in the stu_DB database created in Experiment 1: Student, Course, and SC.
2. Use the INSERT, UPDATE, and DELETE statements to INSERT, UPDATE, and DELETE data in the preceding three tables.
3. add Teacher and TC tables to the stu_DB database, and add sufficient data (no less than 20 entries per table) to the tables in the established student management system to complete subsequent operations of this experiment.
Lab procedure]
I. Data Definition
(1) create a table
In the stu_DB database created in experiment 1, use the Enterprise Manager and query analyzer to create tables of the student management system according to the following table structure.

1. Create a table using the Enterprise Manager

(1) Open the Enterprise Manager.

(2) Select the database stu_DB created in experiment 1, click the table object in the database, and right-click the new table on the right side of the window to bring up the form.

 

(3) In this form, the column name indicates the field name of the table. You can select the data type and length for the field in this form and whether it can be null.

(4) You can select a file group for the table, right-click the table, and select Properties. If a secondary group has been created for the database, you can select a file group for this table in the table File Group of the attribute form. Some competing tables should be placed in different file groups and the file groups should belong to different disks. This will improve the concurrency performance when the tables compete for read/write operations.

(5) Click the disk icon and name the table. Note that a meaningful name should be given to the table.

2. Use the Transact_ SQL statement to create a table

Syntax:

CREATE TABLE  [ database_name.[owner].] table_name ( {column_name data_type [DEFAULT ‘default_value']| [CONSTRAINT CONSTRAINT_name] }, […n] [IDENTITY [(seed, increment )]] ) [ON { filegroup | DEFAULT }] [TEXTIMAGE_ON {filegroup | DEFAULT }]

[Example 1] generate a table named student.

Create table student (sno CHAR (8) primary key, sname CHAR (10) not null, ssex CHAR (2) check (ssex = 'male' or ssex = 'female '), sage smallint, sdept CHAR (20) default ('computer system '),)

[Example 2] Create a table named Ta1, which has three columns. The first Pid column is defined as the primary key and automatically increases. The default Name of the second column is Unknown, and the third column defines a constraint (the date cannot be later than the date of the input day). Insert a record and query it, as shown in.

CREATE TABLE ta1 ( pid int identity(1,1) primary key, [name] CHAR(10) default ('unknow'),birthday datetime CHECK( birthday <getdate() ))

(2) Table Modification
1. Use the Enterprise Manager to modify the table
(1) Right-click the student table to be modified and select "design table". The form shown in 3-4 is displayed. In this form, you can change the fields of the data table. Right-click the blank area and select "properties" to bring up another form. Here, you can change or add constraints.

2. Use a Transact-SQL statement to modify a table
Add a new field to the table: Add a "class" field to the student table. The data type is classified.
Alter table student ADD class CHAR (6)

Delete the old column in the Table: Delete the "Sdept" field in the student table.
Alter table student drop column Sdept

Change the table to add columns with constraints. (Sp_help: used to display the parameter list and its data type)
Add the "grade" field to the student table and add the CHECK constraint so that it cannot exceed 100.
Alter table student ADD grade int CONSTRAINT ch_grade CHECK (grade <100)
EXEC sp_help ch_grade

Add the "birthday" field to the student table, and this date cannot be entered after the date of the day.
Alter table student ADD birthday DATETIME NULL
CONSTRAINT ch_birthday CHECK (birthday <getdate ())

Add columns with default values that can be empty:
Add the "matriculationday" field to the student table, and the default value of this field is the date of the day.
Alter table student ADD matriculationday smalldatetime NULL
CONSTRAINT adddateflt DEFAULT getdate ()

Please refer to the example given above:
Independently design the table structure modification operation (no less than 8 statements) and perform Experiment verification.
Modify the student table. The result is shown in Table 1-4.
Create Tables 1-5 and 1-6 to create a foundation for subsequent experiments.

(3) Delete a table
You can select the table to be deleted from the Enterprise Manager and delete it directly, you can also DROP the table definition and all data, indexes, starters, constraints, and permission specifications in the table using the Transact-SQL statement DROP.
To delete a TABLE named ta1, the statement is as follows: drop table ta1
Refer to the example above to delete Student, Course, and SC tables. What is the order of their deletion? And conduct lab verification.

Ii. Data Operations

(1) viewing, renaming, and deleting user-defined data types
1. Use the Enterprise Manager to create a custom data type named newtype1, with a length of 6, variable length characters, and null allowed.
Go to the Enterprise Manager, go to the stu_DB database, right-click "user-defined data type", select "create user-defined data type" in the "operations" menu, or click "new, the following window is displayed:

Enter newtype1 as the data name to be defined, select varCHAR as the data type, and set the input length to 6. In the "allow NULL value" check box, enter "√" and click "OK.
2. Use a T-SQL statement to create a custom data type named newtype2 with a Data Length of 6 and a fixed-length struct type that is not allowed to be empty.

USE stu_DBEXEC sp_addtype newtype2, ‘CHAR(6)', ‘not null'

3. Name User-Defined Data Types
(1) Use the system stored procedure sp_rename to rename the Custom Data Type newtype1 to a1.
EXEC sp_rename newtype1, a1
(2) Use the Enterprise Manager to rename the custom data type a1 to newtype1.
4. delete user-defined data types
(1) Use the system stored procedure sp_droptype to delete user-defined data types.
EXEC sp_droptype newtype1
(2) Use the Enterprise Manager to delete user-defined data types
Enter the Enterprise Manager, enter the stu_DB database, and click "user-defined data types". All user-defined data types will appear in the right window, right-click newtype2, click Delete in the shortcut menu.
Note: Can user-defined types used by tables or other database objects be deleted? Perform lab verification.
(2) Data Update
1. Update data using the Enterprise Manager
Open Enterprise Manager, right-click the table to be modified, select Open table, and click return to all rows. The form shown in Figure 3-5 is displayed, in this form, you can modify the data content in the table. The modified content is automatically stored. After modification, the form is directly closed, and the data content is modified successfully.

2. Update data using the query Analyzer
In relational databases, there are three common data UPDATE statements: INSERT, UPDATE, and DELETE.
(1) INSERT statement
Insert a row of data into the student table. The specific data is as follows:
Student ID: 04265005, name: Liu Hui, Gender: male, age: 21, Department: Computer

USE stu_DBINSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('20140901', 'liuhui', 'male', 21, 'computer system ') insert into student (Sno, Sname, Ssex, Sage) VALUES ('20140901', 'Li hui', 'female, 21)

Insert data to the table ta1 as follows:

INSERT ta1([name],birthday) values (‘lan','1977-03-02')INSERT ta1 values (‘lan','1977-03-02')INSERT ta1(birthday) values (‘1977-03-02')

Based on the preceding example, you can design data insert statements (no less than 10 statements) in different situations and perform Experiment verification. The verification requirements are as follows:
Insert data that does not meet the uniqueness constraint.
Insert data that does not meet user-defined constraints.
Insert data that does not meet the foreign key constraint.
Use the default value to insert data.
Whether unreasonable data can enter the database.

(2) UPDATE statement
Change the data in the (student) Table and change the department of the student whose student ID is 04265005 to the Department of Mechanical Engineering '.

UPDATE student SET Sdept = 'mechanical system' WHERE Sno = '20140901'

Based on the preceding example, you must design a data modification Statement (no less than 10 statements) and perform Experiment verification. The verification must be the same as the INSERT statement.

(3) DELETE a row using the DELETE statement
Delete the student ID 04265005 in the student table.

DELETE FROM student WHERE Sno =‘04265005'

If you want to clear all the data in the TABLE but do not delete the TABLE, you can use the truncate table statement. This statement is equivalent to a DELETE statement without conditions and does not record logs.

Based on the preceding example, we recommend that you design data deletion statements (no less than 10 statements) for different situations and perform experiments to verify that data deletion does not meet the foreign key constraints.

Add sufficient data (no less than 20 entries per table) to each table of the Student management system (Student, Course, SC, Teacher, TC) created in the data definition ), to complete subsequent operations of this experiment. The specific table information can be referred to as follows:

Today's Lab Course is over. Editor gave you a general understanding of table creation, management, and data operations. Next time we will conduct practical drills. We will continue to study together.

The above is all about table creation and management experiments. I hope this will help you with your learning.