MySQL database: Basic consolidation of DDL statements

MySQL database: Basic consolidation of DDL statements

The SQL language includes four major programming languages: Data Definition Language (DDL), data operation language (DML), Data Control Language (DCL), and transaction control language.

Data Definition Language (DDL) is a Language used to describe the real-world entities to be stored in a database. A database mode contains the description definitions of all entities in the database. The definition language of the database mode is not a programming language. The definition language of the DDL database mode is an integral part of the SQL language (structured programming language.

The following shows a complete DDL statement practice process. The test passed in MySQL 6:

// Create database if not exists mydb; // delete database drop database mydb; // use database use mydb; // create table tb1 (id int, name varchar (255) default 'anonymity ', info text); // view the table show tables of the database; // view the table structure desc tb1; // truncation table: delete all data in the table but retain the table structure. (DDL) is more efficient than delete (DML). truncate tb1; // Add the primary key constraint alter table tb1 modify id int primary key; // Add a unique constraint, that is, duplicate values are not allowed, but multiple null values can appear, because null is not equal to null alter table tb1 modify name varchar (255) unique; // create table tb2 (id int primary key auto_increment, fid int, name varchar (255) default 'anonymity 'not null, info text, # create a foreign key constraint with the name tb2_fk and constraint tb2_fk foreign key (fid) references tb1 (id); // Delete the foreign key constraint alter table tb2 drop foreign key tb2_fk; // add the foreign key constraint alter table tb2 add foreign key (fid) references tb1 (id );

MySQL does not support the check constraint very well. Although this constraint can be defined, it does not have any function.

// Add the check constraint, which is successfully added but does not take effect. alter table tb1 add check (id> 5); // insert into tb1 values (1, 'A', 'one ');

Oracle databases can use the check constraint normally. The following figure shows the Running Effect of Oracle 10 GB:

// Oracle Database supports the check constraint create table 255 (id number (8), name varchar (100), check (id> )); // if the check constraint is not met, an error occurs: insert into fig (600, 'six'); insert into fig (6, 'six ');

The following is the output of the console:

-------------------------------------------------------------------------------------SQL> create table tb3  2  (  3  id number(8),  4  name varchar(255),  5  check(id>100)  6  );Table createdSQL> insert into tb3 values(6,'six');insert into tb3 values(6,'six')ORA-02290: check constraint (SCOTT.SYS_C005457) violatedSQL> insert into tb3 values(600,'six');1 row inserted-------------------------------------------------------------------------------------