Build database, build table, add, delete, modify and check DDL statements in mysql

DDL statements and DML statements

1. Define the database with DDL
2. Maintain the database with DDL
3. Use DML to add and update table data
4. Use DML to delete tables

---

DDL definition database

Data Definition Language ( Data Definition Language, DDL ) is a specialized language used to describe the real-world entities that the database needs to store. Its main job is to define and maintain the database and the structure of the data table.

Create database statement

CREATE DATABASE 数据库名

Delete database statement

DROP DATABASE 数据库名

View database

SHOW DATABASES

Create Library -> Table -> Column

Create database statement

CREATE TABLE 表名(
    column_name(字段名) date_type(字段数据类型) default_value(默认值) (数据完整性约束),
    
);

示范: 
CREATE TABLE 表名(
    UserName int  primary key  auto_increment not null,
    Sex varchar(2) default '男'
    
);

Add a foreign key to the table

-- 主键:PRIMARY KEY (外表必须有主键) 

/*
 FOREIGN KEY (当前表的列) 
 REFERENCES 外键的表 (外表的列);
*/
CREATE TABLE A(
    sid INT PRIMARY KEY -- 注意：关联的表 必有主键

);

CREATE TABLE B(
    Sid INT,
    FOREIGN KEY(Sid) REFERENCES A(Sid) -- B的sid被外键A的SID限制
    
);

Note: 1. The foreign key to be associated must be the primary key 2. The data type must be consistent.

---

Maintain the database with DDL

Modify table structure

1. add a section of field

ALTER TABLE 表名 add(字段名 类型) ;
-- 如  : ALTER TABLE A ADD(Sid INT);

2. Modify a column of fields Tips: Do not use + parentheses after modify

ALTER TABLE 表名 modify 字段名 类型  ;
-- 如  : ALTER TABLE B MODIFY SName varchar(20);

3. Change a column of fields

ALTER TABLE 表名 CHANGE 原字段 新字段 类型 ;
-- 如 : ALTER TABLE A CAHNGE phone iphone varchar(15) 

4. Update a column of fields

ALTER TABLE 表名 MODIFY 列字段 类型 ;
-- 如  ALTER TABLE A MODIFY phone int(12) ;

5. Delete field clause

ALTER TABLE 表名 DROP COLUMN 列字段
-- ALTER TABLE A DROP COLMN phone;

7. The name of the heavy life table is

RENAME TABLE 表名 TO 要改的表名
-- RENAME TABLE TEXT1 TO TEXT2

8. Add foreign key

 -- 表建完后添加外键 
/*ALTER TABLE 本表名
    ADD CONSTRAINT 约束键 FOREIGN KEY(本表的键)
    REFERENCES 被引用的别表名(被引用的表主键)*/
    
ALTER TABLE A
    ADD CONSTRAINT SID FOREIGN KEY(SID)
    REFERENCES B(SID); 
    
-- 创建表时添加外键
# FOREIGN KEY (当前表的键) REFERENCES 选择关联的外表(他的主键)

CREATE TABLE A(
    SID INT PRIMARY KEY
);
CREATE TABLE B(
    SID INT,
    FOREIGN KEY(SID) REFERENCES A(SID)
);

9 . Delete foreign key

ALTER TABLE 表 DROP FOREIGN KEY 外键
-- ALTER TABLE verdir DROP FOREIGN KEY SID

Summary: ALTER TABLE table changes data.

---

Use DML to add and update table data

The data manipulation language (Data Manipulation Language, DML) is provided by the DBMS for users or programmers to use to realize the operation of the data in the database. The basic DML is divided into two types and four types: retrieval (query) and update (insert, delete, modify) .

1. Insert

-- INSERT INTO 表名 values(数据一,数据二, )
INSERT INTO A VALUES(1,'曹操')

/* 插入多条 
INSERT INTO 表名 values
(1,'数据'),
(2,'数据'),
(3,'数据')
*/
INSERT INTO A VALUES
(1,'李白'),
(2,'杜甫'),
(3,'杜牧')

2. Modification note: Modify all by default without adding where

/* 
UPDATE 表 
SET 字段1  =  值1 , 字段2  =  值2  
WHERE 条件表达式
*/
UPDATE A
SET id = 2 sname = '李四';

---

Use DML Delete table data

3. Delete note: delete all by default without adding where

/* 
DELETE FROM 表名 where 条件表达式
*/
DELETE FORM A WHERE sName = '张三';