database table creation, management, and data manipulation (experiment i) _mssql

Today we will study the creation, management and data operation of tables in the form of experiments.

"experimental purposes": understand the use of SQL language, further understanding of relational operations, consolidate the basic knowledge of the database.
"Experimental Requirements": use SQL language for various operations of database tables:
1. database table creation, modification, and deletion operations.
2. Inserts, deletes, and modifies data into a table.
"Experimental content"
1. Using the data definition statement, the three tables of the student management system are established in the STU_DB Library created by experiment: Student, Course, SC.
2. Inserts, updates, and deletes data from these three tables using the INSERT, UPDATE, and DELETE statements.
3. Add teacher and TC tables to the STU_DB library and add sufficient data to the tables in the established Student management system (no less than 20 per table) to complete the subsequent operation of this experiment.
"Experimental Steps"
I. Definition of data
(i) Creation of tables
in the database stu_db created by experiment, the Enterprise Manager and Query Analyzer are used to create a table of student management system in the following table structure.

1 . Create a table with Enterprise Manager

( 1 ) to open Enterprise Manager.

( 2 ) Check Experiment One Create a good database stu_db , click the Table object in the database, and then right click on the right side of the window to select a new table to pop up the form shown in the figure.

( 3 in this form, the column names are the field names for the table, where you can select the data type and length for the field and whether it can be null.

(4) Here you can select a filegroup for the table, right-click in the table, select a property, or select a filegroup in the table filegroup of the property form for the table if you have created a secondary group for the database. Some competing tables should be placed in different filegroups, and the filegroups belong to different disks, which can increase concurrency performance when the table competes for reading and writing.

(5) When the creation is complete, click the disk icon and give the table a name. Note that a meaningful name should be taken for the table.

2 . Creating a table with the transact_sql statement

The syntax is:

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" generates a table with a table named student

CREATE TABLE Student
(
 Sno char (8) Primary key,
 sname char not NULL,
 Ssex char (2) Check (ssex= ' man ' O R ssex= ' female '),
 sage smallint,
 sdept CHAR (' computer system '),

Example 2 creates a table named TA1 that has 3 columns, the first column of which is defined as the primary key, and automatically grows. The second column name defaults to unknown, and the third column defines a constraint (the date cannot be greater than the date of the input day), inserts a record and queries it, and displays the following figure.

CREATE TABLE ta1 
( 
pid int identity (1,1) primary key, 
[name] CHAR (a) default (' Unknow '),
birthday DateTime CHECK (Birthday <getdate ())

(ii) Modification of the table
1. Modify the table with Enterprise Manager
(1) Right click on the student table to be modified, select "Design Table", will pop up as shown in Figure 3-4 form, in this form you can change the data table fields. Right-click in the margin, select Properties, pop another form, where you can change the constraint, or you can increase the constraint.

2, the use of Transact-SQL statements to modify the table
To add a new field to a table: Add a Class field to the student table with the data type as a character type.
ALTER TABLE Student ADD class CHAR (6)

Delete old columns from the table: delete the "sdept" field in the student table.
ALTER TABLE student DROP COLUMN sdept

Change the table to add a column with constraints. (sp_help: Used to display the parameter list and its data type)
Add the "Grade" field to the Student table and add a check constraint so that it cannot 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 this date cannot be entered after the date of the day.
ALTER TABLE student ADD birthday DATETIME NULL
CONSTRAINT ch_birthday CHECK (Birthday<getdate ())

To add a nullable column with a default value:
The "Matriculationday" field is added to the student table, and the default value for this field is the date on which the day was entered.
ALTER TABLE student ADD matriculationday smalldatetime NULL
CONSTRAINT Adddateflt DEFAULT getdate ()

Please refer to the example given above:
The table structure modification operation (not less than 8 statements) is designed and validated experimentally.
Modify table student, as shown in table 1-4.
Create tables 1-5, table 1-6, and build a foundation for subsequent experiments.

(iii) Deletion of the table
You can select a table to delete directly from the Enterprise Manager, or you can delete the table definition and all data, indexes, departures, constraints, and permission specifications from the table by using 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 complete the deletion of table student, Course, SC, and what is the order of deletion? and verified by experiments.

Second, data operation

(i) View, rename, and delete user-defined data types
1. Use Enterprise Manager to create a custom data type named Newtype1, length 6, variable-length characters, and allowed to be empty.
Enter the Enterprise Manager, enter the STU_DB database, right-click the user-defined data type, select New user-defined data type from the Action menu, or click the New button to eject the form shown in the following illustration:

Enter the name of the data you want to define NEWTYPE1, select the data type varchar, enter a length of 6, click "√" in the "Allow null value" check box, and clicking the OK button.
2. Use T-SQL statements to create a custom data type named Newtype2, with a data length of 6, fixed-length characters, and not allowed to be empty.

Use stu_db
EXEC sp_addtype newtype2, ' CHAR (6) ', ' NOT NULL '

3. Naming user-defined data types
(1) Rename the custom data type newtype1 to A1 using the system stored procedure sp_rename.
EXEC sp_rename newtype1, A1
(2) Rename the custom data type A1 to Newtype1 using Enterprise Manager.
4. To delete a user-defined data type
(1) Use system stored procedure sp_droptype to remove user-defined data types.
EXEC Sp_droptype Newtype1
(2) Remove user-defined data types using Enterprise Manager
Into Enterprise Manager, into the stu_db database, click User-defined data type, the right window will appear all user-defined data types, and then right-click Newtype2, click the Delete button on the pop-up shortcut menu.
Note: Can you delete a user-defined type that is being used by a table or other database object? Please verify the experiment.
(ii) updating of data
1. Update data with Enterprise Manager
Open Enterprise Manager, right-click the table you want to modify, select Open Table, click Return all rows, and pop up the form shown in Figure 3-5, where you can modify the contents of the data in the table. The changes here are automatically stored, after the modification, directly close the form, the data content is modified successfully.

2. Update data with Query Analyzer
in a relational database, there are three common statements for data updates: INSERT, update, and delete
(1) INSERT statement
insert a row of data into the student table, with the following specific data:
School No.: 04265005, Name: meter, Sex: Male, Age: 21, Department: Computer Department

Use stu_db
inserts into student (Sno, sname, Ssex, Sage, sdept) 
VALUES (' 04265005 ', ' meter ', ' men ', 21, ' computer Systems ')
Inse RT into student (Sno, sname, Ssex, Sage) 
VALUES (' 04265006 ', ' Li Hui ', ' female ', 21)

Insert the data into the TA1 table 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 example, design the data insert statement (not less than 10 statements) under different circumstances, and carry on the experiment verification, the verification request is as follows:
The insertion of data that does not meet uniqueness constraints.
Inserts of data that do not meet user-defined constraints.
Inserts of data that do not meet the foreign key constraint.
Inserts the data using the default value.
Whether the unreasonable data can enter the database.

(2) UPDATE statement
Change (student) table data, the student number of ' 04265005 ' is changed to the Department of Mechanical Engineering.

UPDATE student SET sdept= ' mechanical system ' WHERE sno= ' 04265005 '

According to the above example, you design the data modification statement (not less than 10 statements), and experiment verification, verification requires the same INSERT statement.

(3) Delete statement deletes row
Delete the record in the student table with the number ' 04265005 '.

DELETE from student WHERE Sno = ' 04265005 '

If you want to clear all the data in a table without deleting the table, you can use the TRUNCATE TABLE statement. The statement is equivalent to a DELETE statement without a condition, and the statement does not log.

According to the above example, design the data deletion statement (no less than 10 statements) of different situations, and experiment to verify the data deletion operation that does not satisfy the foreign key constraint.

Add sufficient data for each table in the Student management system (Student, COURSE,SC,TEACHER,TC table) created in the data definition (no less than 20 per table) to complete the subsequent operation of this experiment. Detailed table information can be consulted as follows:

Today's experiment class is over, small knitting bring us general understanding of table creation, management and data operations, the next time to carry out the actual combat exercise, we continue to study together.

The above is the table of the creation and management of all the contents of the experiment, I hope to help you learn.