MySQL database: Base consolidation of DDL statements

The SQL language includes statements from four main programming language categories: Data definition Language (DDL), Data Manipulation language (DML), Data Control Language (DCL), and transaction control language.

The database schema definition language DDL (Data definition Language) is the language used to describe the real-world entities to be stored in the database. A database schema contains a description definition for all entities in the database. The database schema definition language is not a programming language, and the DDL database schema definition language is an integral part of the SQL language (Structured programming language).

The following is a complete procedure for the DDL statement practice, which is passed in the MySQL6 test:       

Create database if not EXISTS mydb;//Delete database mydb;//use database using mydb;//CREATE TABLE tb1 (id int, name varchar (255) Default ' anonymity ', info text);//view the table of the database show tables; View table Structure desc TB1; TRUNCATE TABLE: Deletes all data in a table but retains the table structure. (DDL) performs more efficiently truncate TB1 relative to delete (DML); Add PRIMARY KEY constraint ALTER TABLE TB1 modify ID int primary key; Add a unique constraint, that is, duplicate values are not allowed, but multiple nulls can occur, because null is not equal to null ALTER TABLE TB1 modify name varchar (255) unique;  Build table and add primary key, FOREIGN KEY constraint CREATE TABLE TB2 (ID int primary key auto_increment, FID int, name varchar (255) Default ' anonymity ' not nu ll, info text, #建立外键约束, constraint named TB2_FK, constraint TB2_FK foreign key (FID) references tb1 (ID)); Delete FOREIGN KEY constraint ALTER TABLE TB2 drop foreign key tb2_fk; Add FOREIGN KEY constraint ALTER TABLE TB2 add foreign key (FID) references tb1 (ID);

MySQL support for check constraints is not very good, although you can define this constraint, it does not have any effect

Adding a CHECK constraint will add success, but will not take effect ALTER TABLE TB1 add check (id>5); The following can be performed normally insert into TB1 values (1, ' A ', ' one ');

While the Oracle database can normally use check constraints, the following are the operating effects of Oracle 10g:

The Oracle database supports the CHECK Constraint CREATE TABLE tb3 (ID number (8), name varchar (255), check (id>100));//failure to satisfy the check constraint will cause an error insert into TB3 values (' six '); INSERT into TB3 values (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 ') and insert into TB3 values (6, ' six ') Ora-02290:check constraint (SCOTT. sys_c005457) violatedsql> insert into TB3 values ("six"); 1 row Inserted-------------------------------------------------------------------------------------

MySQL database: Base consolidation of DDL statements