Database experiment report

Database experiment report


Test content

1. Establishment of data sheet

Basic table "simple" with primary key

With outer code constraints

2. Modification of the data sheet

Add removal column

Modify column attribute type

Increase removal constraints

Tuple addition, modification, removal

Remove data sheet


Experimental procedure

1, create table student

Primary key , /*sno is the primary code column level integrity constraint */

Sname char unique, /*sname takes a unique value */

Ssex char,

Sage smallint, /* type is smallint*/

Sdept char /* where */

);

Create table course

Primary key, /* column-level integrity constraint, cno is the primary code */

Cname char,

The meaning of cpno char, /*cpno is the first class*/

Ccredit smallint,

Foreign key references course

/*Table-level integrity constraints, cpno is the outer code, the referenced table is the course, and the referenced column is cno*/


);

Create table sc

,

Cno char,

Grade smallint,

Primary key

The /* master code has two attributes and must be defined as table-level integrity*/

Foreign key references student,

/*Table-level integrity constraints, sno is the outer code, the referenced table is student*/

Foreign key references course,

/*Table-level integrity constraints, cno is the outer code, referenced to indicate course*/

);

Example 1, create table s

, / * variable length string, enter 2 characters is two characters will not fill the space * /

Sname varchar,

Status int,

City varchar,

Constraint pk_sno primary key, /* constraint name is pk_sno*/

);

Create table p

,

Pname varchar,

Color varchar,

Weight int,

Constraint pk_pno primary key , the name of the /* constraint is pk_pno*/

);

Create table j

,

Jname varchar,

City varchar,

Constraint pk_jno primary key / * The name of the constraint is pk_jno*/

);

Example 2, create table spj

, /* The main code in the first table */

Pno varchar,

Jno varchar,

Qty int, /*number*/

Constraint pk_spj primary key, /* The main code consists of 3 attributes */

Foreign key references s,

/*Table-level integrity constraints, sno is the outer code, the referenced table is s*/

Foreign key references p,

/*Table-level integrity constraints, pno is the outer code, and the referenced table is p*/

Foreign key references j,

/*Table-level integrity constraints, jno is the outer code, the referenced table is j*/


);

2. Changes to the data sheet

Add a concat column to the s table

Alter table s add concat varchar

Remove the concat column in the s table

Alter table s drop column concat

Change the s table Attribute of the concat column Change the length from 20 to 30

Alter table s alter column concat varchar

Contact name concat modify attribute to unique attribute name con_concat

Alter table s add constraint con_concat unique

Remove constraint relationship con_concat

Alter table s drop constraint con_concat


/* Insert a tuple */


Insert into s valus /*20 cannot be written as '20'*/

Exclusion and summary of the problems in the test:

1, when creating spj

There are three entities, so the master code is taken from the 3 entities, and a quantity attribute is also written.

The master code is determined by the 3 master codes

2. When changing the data table in a database, you must first make the database in use.

3, constraint

Is an optional keyword that indicates the beginning of a primary key, not null, unique, foreign key, or check constraint definition. Constraints are special properties that enforce data integrity and can index tables and their columns.

4, --go can not add but pay attention to the order Note: go -- annotation prompt error

5, pay attention to add an empty element with null

Attach sql backup


--Create a database student

Create database student

Go

--Create a table in the database student student course sc note order

Use student
-------------------------------------------------- --------------
Create table student
Primary key, /*sno is the primary code column-level integrity constraint*/
Sname char unique, /*sname takes a unique value */
Ssex char,
Sage smallint, /* type is smallint*/
Sdept char /* where */
); /*; to add */
-----------

1 2