Database-create databases, basic tables, and views, Use triggers, and view triggers

Database-create databases, basic tables, and views, Use triggers, and view triggers

I. Create a database

Create database database_name; // create a database

Drop database database_name; // delete a database

Ii. Create a basic table

1. Create a table

Create table Name

(

Field name 1 Data Type [not null] [default value],

Field name 2 data type,

Field name 3 Data Type

);

For example:

Create table student

(

Stu_num int auto_increment, // auto Increment

Stu_name char (10) not null,

Stu_gender char (2 ),

Stu_score int,

Primary key (stu_num), // primary key

Foreign key (stu_name) references another table (stu_name) // foreign key

);

2. Common constraints

Primary key constraint: primary key (field name); // or append the primary key directly to the end when defining a field

Foreign key constraint: foreign key (field name) references table name (field name );

Non-empty constraint: field name data type not null

Uniqueness constraint: field name data type unique

Default constraint: field name data type default Value

Auto increment: The field name data type auto_increment // The default initial value is 1 and the incremental value is 1. To start from 100, you can set it to auto_increment = 100

3. modify a table

Alter table old table name rename new table name // Modify table Name

Alter table name modify Field name data type; // modify the field data type

Alter table name change old field name new data type // Modify Field name

Alter table name add new field name data type // add Field

Alter table Name drop field name // Delete Field

4. delete a table

Drop table [if exist] table Name;

3. Create a view

Create view view_name (field name, field name, field name)

As <select query statement>

For example:

Create view stu_sub (stu_num, stu_name) // create a view

As select stu_num, stu_name

From student;

Drop view stu_sub; // delete a view

4. triggers

Create trigger trigger_name // create a trigger

On table (table name)

For insert | delete | update

As <SQL _statement> (SQL statement)

For example:

Create trigger student_backup // The Student backup table changes with the change of the student table

On student

For insert, delete, update

As

Begin

If (select count (*) from deleted)> 0)

Begin

Delete from student_backup

Where stu_name in (select stu_name from deleted)

End

If (select count (*) from inserted)> 0)

Begin

Insert into student_backup

Select *

From inserted

End

End