MySQL DDL (data definition language)

One 、 brief introduction

  DDL(Data Definition Language) Data definition language , Scope of application ： For some objects in the database ( for example ,database,TABLE) Conduct management , Such as Create,ALTER and Drop,DDL The operation is implicitly submitted , You can't rollback！

Two 、 database

   Operate the database , First you have to log in .

mysql -u  user name  -p  User password 

[email protected]:/# mysql -u alian -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2.1、 Display database

   Be careful not to lose that s

show databases;

2.2、 Create database

   Simple writing , Adopt the default settings of the database

CREATE DATABASE  Database name ;

   Recommend writing , Set up Base character set and Database collation , The name of the database cannot be changed

CREATE DATABASE IF NOT EXISTS  Database name 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_general_ci;

2.3、 Select database

USE  Database name 

2.4、 Delete database

DROP database  Database name 

3、 ... and 、 data type

3.1、 value type

3.2、 Date and time type

3.3、 String type

3.4、 About int( length )

• If a field has the unsigned and fill zero attributes , So no matter what value this field stores , The length of the value will be consistent with the set display width , For example, insert 1, Is shown as 00000000001, The left side is mended 10 Zero until the length reaches 11 position
• Setting the display width of a field does not limit the range of values stored in the field , If you set int(6), But it can still store 99999999
• The set character width is only valid when the numerical length does not meet the width , Setting up int(6), Insert 1 when , Insufficient length 6, So add... On the left 5 A zero , If you insert 888888888, The display width doesn't work

Four 、 Data sheet

4.1、 Create table

   Create the correct posture instructions for the table

CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ' Primary key ',
  `user_id` INT UNSIGNED NOT NULL DEFAULT '1000' COMMENT ' Student number ',
  `user_name` VARCHAR(10) NOT NULL DEFAULT '' COMMENT ' The student's name ',
  `gender` CHAR(1) NOT NULL DEFAULT '1' COMMENT ' Gender ',
  `birthday` DATE NOT NULL DEFAULT '1970-01-01' COMMENT ' Birthday ',
  `home_address` VARCHAR(100) NOT NULL DEFAULT '' COMMENT ' Home address ',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ' Creation time ',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' Update time ',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=' Student information sheet ';

• mysql Create a table and try to keep a self growing id, And set to UNSIGNED
• The naming of fields should be meaningful , Combined with programming language, the hump mode can be changed to Underline Segmentation will automatically map
• The type of field must be reasonable , And set the appropriate length , It is suggested to add field description COMMENT
• Set the default value for any field , Try to avoid a value of NULL The situation of
• create_time and update_time General business table will have , One is the time of creation , Then it won't change , One is as the data changes , Time will also be shown , Very practical
• Set the index according to the situation , Naming conventions for indexes （ primary key ：pk_ namely primary key; unique index ：uk_ namely unique key; General index ：idx_ namely index For short ）
• The business table engine is generally InnoDB, Because it supports transactions
• The character set of the table is set to utf8mb4, Sort rule utf8mb4_general_ci
• Finally, remember to add the notes of the table COMMENT

4.2、 Display table

show TABLEs;

4.3、 View table structure

desc  Table name of the database ;

4.4、 Show the statement that created the table

show create TABLE  Table name of the database ;

4.5、 Modify table

4.5.1、 Modify table name or comment

ALTER TABLE  Original database table name  rename to  New database table name ;
ALTER TABLE  Database table name  COMMENT=' This is the note of the table ';

4.5.2、 Add fields

ALTER TABLE  Database table name  add  The name of the field to add   data type   constraint condition   Field description  after  Field name ;
# For example student Add one at the end of the table age Field 
ALTER TABLE student add `age` TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT ' Age ';
# For example student In the table gender Add a... After the field age Field 
ALTER TABLE student add `age` TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT ' Age ' after gender;
# If it's in student In the table gender Add a age Field 
ALTER TABLE student add `age` TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT ' Age ' before gender;

4.5.3、 Modify field name

ALTER TABLE  Database table name  CHANGE  Old field name   new field name   New field type ;
# If we put student In the table age Change the field to user_age, The default value is changed to 1( In fact, it is to create a field definition and rewrite )
ALTER TABLE student change age user_age TINYINT UNSIGNED NOT NULL DEFAULT '1' COMMENT ' Age ';

4.5.4、 Delete field

ALTER TABLE  Database table name  DROP  Field name to delete ;
# For example, we delete user_age Field （ Because of the attribute of birthday ）
ALTER TABLE student DROP user_age;

4.5.5、 Set the default value

## Set mode value 
ALTER TABLE  Database table name  ALTER  Field name  SET DEFAULT  The value to set ;
## hold student Tabular gender Set the default value to 0
ALTER TABLE student ALTER gender SET DEFAULT 0;

4.5.6、 Delete default （ Don't suggest ）

## Delete mode value 
ALTER TABLE  Database table name  ALTER  Field name  DROP DEFAULT;
# For example, delete student Tabular gender The default value of 
ALTER TABLE student ALTER gender DROP DEFAULT;

4.6、 Clear the table

TRUNCATE TABLE  Table name of the database ;

4.7、 Delete table

DROP TABLE  Table name of the database ;

4.8、 Modify the self growth value

# Modify the self increasing value 
ALTER TABLE  Table name of the database  AUTO_INCREMENT= The self increasing value to be set 

4.9、 Modify the storage engine of the table

# Modify the storage engine of the table （MyISAM  perhaps  INNODB）
ALTER TABLE  Table name of the database  ENGINE= Storage engine name 
ALTER TABLE student ENGINE=INNODB;

5、 ... and 、 Primary key and index

5.1、 Add primary key

ALTER TABLE  Table name of the database  ADD PRIMARY KEY( Field name );

5.2、 Add a composite primary key

ALTER TABLE  Table name of the database  ADD PRIMARY KEY( Field name 1, Field name 2,...);

5.3、 Delete primary key

ALTER TABLE  Table name of the database  DROP PRIMARY KEY;

   exception ： If it is a self growing primary key, you need to remove the self growing setting

# use MODIFY Delete self growth attribute , Be careful MODIFY The primary key attribute cannot be removed 
ALTER TABLE student MODIFY id INT UNSIGNED NOT NULL COMMENT ' Primary key ';
# Delete primary key 
ALTER TABLE student DROP PRIMARY KEY;

5.4、 Create unique index

CREATE UNIQUE INDEX  The index name  ON  The name of the table ( Table field ) 

5.5、 Add unique index

ALTER TABLE  Table name of the database  ADD UNIQUE  The index name ( Field name );
# Such as to student Of user_id Add index to field , Index name is idx_user_id
ALTER TABLE student ADD UNIQUE uk_user_id(user_id);

5.6、 Create a normal index

# here length Indicates the first digits of the field , That is, short index 
CREATE INDEX  The index name  ON  Table name of the database ( Field name (length))  

5.7、 Add a normal index

ALTER TABLE  Table name of the database  add INDEX  The index name ( Field name );
# Such as to student Of user_name Add index to field , Index name is idx_user_name
ALTER TABLE student ADD INDEX idx_user_name(user_name);

5.8、 View the index of the table

SHOW INDEX FROM  Table name of the database ;

5.9、 Delete index

# Delete index 
ALTER TABLE  Table name of the database  DROP UNIQUE  The index name ;
# For example, delete student On the table user_name Index on field 
ALTER TABLE student DROP INDEX idx_user_name;

perhaps

# perhaps 
DROP INDEX  The index name  on  Table name of the database ;
# For example, delete student On the table user_name Index on field 
DROP INDEX idx_user_name on student;

6、 ... and 、 View

6.1、 View creation

CREATE VIEW  View name ( Field 1, Field 2,..., Field n)
AS
SELECT  Field 1, Field 2,..., Field n FROM  surface 
WITH CHECK OPTION;

For example, we put the number and name of the student table in a view

# Create a view with student numbers and names 
CREATE VIEW v_student(user_code,user_name)
AS
SELECT user_code,user_name FROM tb_student
WITH CHECK OPTION;

The following is an example of creating a multi table view

# Put the numbers and names of the student table and class table into one view 
CREATE VIEW v_class_student(class_code,class_name,user_code,user_name)
AS
SELECT c.class_code,c.class_name,s.user_code,s.user_name FROM tb_class c, tb_student s WHERE c.class_code=s.class_code
WITH CHECK OPTION;

6.2、 View query

# Query view 
select * from  View name ;
# For example, we created the student view before 
SELECT user_code,user_name FROM v_student;

6.3、 View change

# Change the view 
create or replace view  View name  as  Query statement ;

6.4、 View delete

# Delete view 
drop view if exists  View name ;
# Delete student view 
drop view if exists v_student;

Study plan ：

• CentOS 8 in Docker install MySQL8
• MySQL And DCL（ Data control language ）
• MySQL And DDL（ Data definition language ）
• MySQL And DML（ Data manipulation language ）
• MySQL And DQL（ Data query language ）- Common keywords
• MySQL And DQL（ Data query language ）- Link query
• MySQL And DQL（ Data query language ）- Common function
• MySQL Three paradigms of database design