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

Source: Internet
Author: User

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

        

  

 

    

    

    

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.