database table creation, management, and data manipulation (experiment one), database creation

database table creation, management, and data manipulation (experiment one), database creation

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

"Experimental Purpose": 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. Use the data definition statement to establish the student management system in the STU_DB library created by the experimental one three tables: Student, Course, SC.
2. Inserting, updating, and deleting data from the above three tables using INSERT, UPDATE, and DELETE statements.
3. Add the teacher and TC tables to the STU_DB library to add sufficient data to the tables in the established Student management system (not less than 20 per table) in order to complete the subsequent operation of this experiment.
"Experimental Steps"
First, the data definition
(i) Creation of tables
In the database stu_db created by the experiment, we use Enterprise Manager and Query Analyzer respectively to create a table of student management system according to the following table structure.

1. Creating Tables with Enterprise Manager

(1) Open Enterprise Manager.

(2) Select the experiment one created database stu_db, click the Table object in the database, and then right-click on the right side of the window to select New table, pop up the form.

(3) In this form, column names show the field names of the table, in which 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 Properties, and if you have already created a secondary group for the database, you can select the filegroup for this table in the table file group of the Properties form. Some competing tables should be placed in different filegroups and the filegroups belong to different disks, which can improve concurrency when the table competes to read and write.

(5) Once created, click the disk icon and take a name for the table. Note that you should take a meaningful name for the table.

2. Create 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 (TEN) not NULL, Ssex char (2) Check (ssex= ' male ' or ssex= ' female '), Sage SM Allint, Sdept CHAR ("Computer Department"),

Example 2 creates a table named Ta1, which has 3 columns, the first column PID is defined as the primary key, and it grows automatically. The second column name defaults to unknown, the third column defines a constraint (the date cannot be greater than the date entered on the day), inserts a record and queries it, as shown in.

CREATE TABLE ta1 (PID int identity (primary) key, [name] CHAR (' Unknow '), Birthday datetime CHECK (Birthday <getdate ()))

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

2. Modifying tables with Transact-SQL statements
Add a new field to the table: A class field is added to the student table, and the data type is 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 a parameter list and its data type)
Add the "Grade" field to the Student table and add a check constraint so that it is not 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's table, and the date cannot be after the date of the entry 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 of the day of entry.
ALTER TABLE student ADD matriculationday smalldatetime NULL
CONSTRAINT Adddateflt DEFAULT getdate ()

Please refer to the example given above:
Self-designed table structure modification operation (not less than 8 statements), and experimental validation.
Modify the table student, as shown in table 1-4.
CREATE table 1-5, table 1-6, to establish the basis for subsequent experiments.

(iii) Deletion of tables
You can delete the table you want to delete in Enterprise Manager, or you can delete the table definition and all data, indexes, departures, 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 above example to complete the deletion of the table student, Course, SC, they delete the order? and the experimental verification.

Second, data operation

(i) viewing, renaming, and deleting user-defined data types
1. Use Enterprise Manager to create a custom data type named Newtype1, length 6, variable long characters, and allow Nulls.
Enter the Enterprise Manager, enter the stu_db database, right-click on "User defined data type" and select "New User defined data type" in the "Action" menu or click "New" button, the form will pop up as shown:

Enter the data name to be defined Newtype1, select the data type varchar, enter a length of 6, and in the Allow null value check box, click OK.
2. Use the T-SQL statement to create a custom data type named Newtype2, with a data length of 6, a fixed-length character type, and not allowed to be empty.

Use stu_dbexec sp_addtype newtype2, ' CHAR (6) ', ' NOT NULL '

3. Naming 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 Enterprise Manager to rename the custom data type A1 to Newtype1.
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) Use Enterprise Manager to delete user-defined data types
Enter Enterprise Manager, enter the stu_db database, click "User Defined Data type", the right window will appear all user-defined data types, then right-click Newtype2, click the "Delete" button on the popup shortcut menu.
Note: Can I 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 the Enterprise Manager, right-click on the table you want to modify, select Open Table, click Return all rows, the form shown in 3-5 pops up, where you can modify the data content in the table. The changes here are automatically stored, after the modification, close the form directly, the data content is modified successfully.

2. Updating data with Query Analyzer
In a relational database, there are three statements of common data updates: INSERT, update, and delete
(1) INSERT statement
Insert a row of data into the student table, with the following data:
School Number: 04265005, Name: Liu Hui, gender: male, Age: 21, Department: Computer Department

Use Stu_dbinsert into student (Sno, Sname, Ssex, Sage, sdept) VALUES (' 04265005 ', ' Liu Hui ', ' Male ', 21, ' computer Department ') INSERT into Studen T (Sno, Sname, Ssex, Sage) VALUES (' 04265006 ', ' Li Hui ', ' female ', 21)

Insert 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, the design of the data in different cases (not less than 10 statements), and experimental verification, the verification requirements are as follows:
The insertion of data that does not satisfy the uniqueness constraint.
Insert of data that does not meet user-defined constraints.
The insertion of data that does not satisfy the foreign key constraint.
The insertion of data using the default value.
Whether unreasonable data can enter the database.

(2) UPDATE statement
Change the data of the (student) table and change the department of the student with the number "04265005" to the Department of Mechanical Engineering.

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

According to the above example, the design of data modification statements (not less than 10 statements), and experimental validation, validation requirements with the INSERT statement.

(3) DELETE statement deletes rows
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 the 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, the data deletion statements (not less than 10 statements) are designed in different situations, and the data deletion operation that does not satisfy the FOREIGN KEY constraint is verified experimentally.

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

Today's experimental class is over, small tape everyone general understanding of the table creation, management and data manipulation, the next time to carry out practical exercises, we continue to study together.

The above is the table of the creation and management of all the contents of the experiment, I hope that everyone's learning has helped.

database table creation, management, and data manipulation (experiment one), database creation