Creation, management and data operation of database table (Experiment 1)

Time:2021-3-1

Today, we will study the creation, management and data operation of tables in the form of experiments. Let’s have a class.

The purpose of the experiment was to investigate the effect of the treatmentUnderstand the use of SQL language, further understand the relationship operation, consolidate the basic knowledge of database.
[experimental requirements]:SQL language is used for various operations of database tables
1. Creation, modification and deletion of database table.
2. Insert, delete and modify data in the table.
[experiment content]
1. Using data definition statement to create stu in Experiment 1_ Three tables of student management system are established in DB database: student, course and SC.
2. Use insert, update and delete statements to insert, update and delete the data of the above three tables.
3. In stu_ Add teacher and TC tables in DB database, and add enough data to the tables in the established student management system (each table is no less than 20), so as to complete the subsequent operation of the cost experiment.
[experimental steps]
1、 Data definition
(1) Table creation
Database stu created in Experiment 1_ In dB, enterprise manager and query analyzer are used to create the table of student management system according to the following table structure.

1Using enterprise manager to create tables

1)Open enterprise manager.

2)SelectExperiment 1Create a good databasestu_DB, click the table object in the database, then right-click the right side of the window and select new table to pop up the window as shown in the figure.

 

3)In this form, the column name column shows the field name of the table. You can select the data type, length and null value for the field in this form.

(4) Here, you can select a file group for the table, right-click in 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 properties form. We should put some competing tables in different file groups, and let the file groups belong to different disks, so that we can improve the concurrency performance when the tables compete for reading and writing.

(5) When you are finished, click the disk icon and give the table a name. Note that you should give the table a meaningful name.

2. useTransact_SQLStatement to create a table

  The syntax is as follows:


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] create 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,
)

[example 2] create a table named TA1. There are three columns in the table. The first column, PID, is defined as the primary key and grows automatically. The default value of the second column name is unknown, and the third column defines a constraint (the date cannot be greater than the date of the input day). Insert a record and query it, as shown in the figure below.


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

(2) Table modification
1. Modify the table with enterprise manager
(1) right click the student table to be modified and select “design table”. The window as shown in Figure 3-4 will pop up. In this window, you can change the fields of the data table. Right click in the blank and select “properties” to pop up another window where you can change or add constraints.

2. Modifying tables with transact SQL statements
Add a new field to the table: add a “class” field in the student table, and the data type is character type.
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 parameter list and its data type)
Add the “grade” field to the student table and add the check constraint, so that it can not be greater than 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 the date cannot be after the date of the input day.
ALTER TABLE student ADD birthday DATETIME NULL
CONSTRAINT ch_birthday CHECK(birthday<getdate())

Add nullable columns with default values:
Add “matriculation day” field in the student table, and the default value of this field is the date of the entry day.
ALTER TABLE  student ADD matriculationday smalldatetime NULL
CONSTRAINT adddateflt  DEFAULT getdate() 

Please refer to the example given above:
The table structure modification operation (no less than 8 statements) is designed and verified by experiments.
Modify the table student, and the results are shown in table 1-4.
Table 1-5 and table 1-6 were created to establish the basis for the follow-up experiments.

(3) Table deletion
You can select the table to be deleted in the enterprise manager and delete it directly. You can also delete the definition of the table and all the data, indexes, starters, constraints and permission specifications in the table through the transact SQL statement drop.
To delete the TA1 table, the statement is as follows: drop table TA1
Please refer to the example above to delete the student, course and SC tables. What is the order of deletion? And experimental verification.

2、 Data operation

(1) View, rename, and delete user-defined data types
1. Use enterprise manager to create a user-defined data type named newtype1, with length of 6, variable length characters and allowed to be empty.
Enter enterprise manager, enter stu_ DB database, right-click “user defined data type” and select “new user defined data type” in “operation” menu or click “new” button to pop up the window as shown in the figure below:

Enter the data name newtype1 to be defined, select the data type varchar, enter the length of 6, mark “√” in the “allow null value” check box, and click “OK”.
2. Use T-SQL statement to create a user-defined data type named newtype2 with data length of 6 and fixed length character type, which is not allowed to be empty.


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

3. Name the user-defined data type
(1) Using system stored procedure sp_ Rename renames the custom data type newtype1 to A1.
            EXEC sp_rename newtype1, a1 
(2) Use enterprise manager to rename the custom data type A1 to newtype1.
4. Delete user defined data type
(1) Using system stored procedure sp_ Drop type to delete user-defined data types.
             EXEC sp_droptype newtype1
(2) Using enterprise manager to delete user defined data types
Enter enterprise manager, enter stu_ DB database. After clicking “user defined data types”, all user-defined data types will appear in the right window. Then right click newtype2 and click “delete” on the shortcut menu.
Note: can the user-defined types being used by tables or other database objects be deleted? Please carry out experimental verification.
(2) Data update
1. Update data with enterprise manager

Open the enterprise manager, right-click the table to be modified, select “open table”, and click “return to all rows”. The window as shown in Figure 3-5 will pop up, in which you can modify the data content in the table. The modified content here is stored automatically. After modification, close the window directly, and the data content is modified successfully.

2. Update data with query analyzer
In relational database, 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 number: 04265005, name: Liu Hui, gender: male, age: 21, Department: Computer Science

USE stu_DB
INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) 
Values ('04265005 ','liu Hui','male ', 21','computer department ')
INSERT INTO student (Sno, Sname, Ssex, Sage) 
Values ('04265006 ','li Hui','female ', 21)

Insert data into 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')

According to the above examples, please design data insertion statements in different situations (no less than 10 statements) and carry out experimental verification. The verification requirements are as follows:
The insertion of data that does not satisfy the uniqueness constraint.
Insertion of data that does not meet user-defined constraints.
Insertion of data that does not satisfy the foreign key constraint.
The insertion of data using default values.
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 number is’ 04265005 ‘to’ Department of mechanical engineering ‘.

Update student set dept ='mechanical department 'where SnO ='04265005'

According to the above examples, please design data modification statements (no less than 10 statements) and carry out experimental verification. The verification requirements are the same as insert statements.

(3) Delete statement to delete a row
Delete the record with student number ‘04265005’ in the student table.


DELETE FROM student WHERE Sno =‘04265005'

If you want to clear all the data in the table without deleting the table, you can use the truncate table statement. This statement is equivalent to a delete statement without conditions, and the statement does not log.

According to the above examples, please design different data deletion statements (no less than 10 statements) and carry out experiments to verify the data deletion operations that do not meet the foreign key constraints.

Add enough data to each table of the student management system (student, course, SC, teacher, TC table) created in the data definition (each table is no less than 20), so as to complete the subsequent operation of the cost experiment. The specific table information can be referred to as follows:

Today’s experiment class is over. We have a general understanding of table creation, management and data operation. Next time, we will have a practical drill. Let’s continue to learn together.

The above is the table to create and manage all the content of the experiment, I hope to help you learn.