MySQL DDL (data definition language)

The notebook

    SQL Language is divided into 4 Categories: ： DQL（ Data query language ）、 DDL（ Data definition language ）、 DML（ Data manipulation language ）、 DCL （ Data control language ）      This article introduces one of them DDL（Data Definition Language）, Data definition language , For example, building a database 、 Table building, etc , All belong to data definition language

One 、 Library management

1、 Library creation

Basic grammar ：

CREATE DATABASE  Library name 

     When using this grammar , There will be a problem , If this library exists , Then the result will be wrong , To avoid this problem , The following syntax can be used ： That is, judge whether the library exists before creating

CREATE DATABASE IF NOT EXISTS  Library name ;

2、 Modification of the library

    MySQL Direct modification of library name is not supported in , And the process of modification is troublesome . But its character set can be modified .
grammar ：

ALTER DATABASE  Library name  CHARACTER SET  Modified character set ;

For example, change it to GBK

ALTER DATABASE  Library name  CHARACTER SET GBK;

3、 Deletion of Library

DROP DATABASE IF EXISTS  Library name ; 

Two 、 The management of the table

1、 The creation of a table

create table  Table name (
	 Name   The type of the column  【 constraint 】),
	 Name   The type of the column  【( constraint 】),
	 Name   The type of the column  【 constraint 】),
	...
	 Name   The type of the column  【 constraint 】
)

2、 The modification of table

① Change column names

ALTER TABLE   Table name  CHANGE COLUMN  Old column names   New column names   type ;

② Modify the type or constraint of the column name

ALTER TABLE  Table name  MODIFY  Name   new type  【 New constraint 】;

③ Add columns

ALTER TABLE  Table name  ADD COLUMN  Name   type ;

If you want to add a field to the first field or after the specified field , You can use this syntax ：

ALTER TABLE  Table name  ADD COLUMN  Field name   type  FIRST;
ALTER TABLE  Table name  ADD COLUMN  Field name   type  AFTER  Field name ;

④ Delete column

ALTER TABLE  Table name  DROP COLUMN  Name ;

⑤ Modify the name of the table

ALTER TABLE  Table name  RENAME TO  The new name of the table ;

among COLUMN Can be omitted

3、 The deletion of the table

DROP TABLE IF EXISTS  Table name ;

4、 Replication of tables

① Just copy the structure of the table

CREATE TABLE  Copied table name  LIKE  Table name to be copied ;

② Copy the structure of the table + data

CREATE TABLE  Copied table name  SELECT *FROM  Table name ;

3、 ... and 、 Common data types

1、 integer

MySQL The main types of integers are TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT, Its attribute field can be added with AUTO_INCREMENT Self increasing constraints , The following table lists MySQL The value type in .

2、 Decimal type

It can be subdivided into
     Floating point type ：FLOAT（4 byte ）、DOUBLE（8 byte ）
     Fixed point number type ：DEC、DECIMAL
The accuracy of fixed-point type is relatively high , If the accuracy of the inserted value is required to be high, consider using , Such as currency calculation

3、 Character type

     Shorter text ：char、varchar
Their differences ： among char Characters representing a fixed length ,varchar Represents variable length characters , Use char Efficient than varchar higher , but char Than varchar More space consuming .
     Longer text ：text、blob

And then there is BINARY and VARBINARY（） Used to hold shorter binary ）、ENUM type （ Also known as enumeration type ）、SET type （ Save collection ）

4、 The date type

DATE（ Occupy 4 byte ）、DATETIME（8 byte ）、TIMESTAMP（4 byte ）、TIME（3 byte ）、YEAR（1 byte ）

Four 、 Common constraints

Six constraints ：
①NOT NULL ： Non empty constraint , To ensure that the value of this field cannot be empty
②DEFAULT： Default constraint , Used to ensure that the field has a default value
③PRIMARY KEY： Primary key constraint , Used to ensure that the value of this field is unique , And not empty
④UNIQUE： Unique constraint , Used to ensure that the value of this field is unique , Can be null
⑤CHECK： Check constraint 【MySQL I won't support it 】
⑥FOREIGN KEY： Foreign key constraints , Used to restrict the relationship between two tables , Add foreign key constraint from table , Used to reference the value of a column in the main table

notebook