TABLE 表

DDL 数据定义语言

1. CREATE TABLE 新建表

#查看表字段
DESC employee_bak;

#显示表字段和编码
SHOW CREATE TABLE employee;

#定位到数据库
USE database_name;

 

#1.1
CREATE TABLE IF NOT EXISTS employee (
 　　id int,
 　　name varchar(20),
 　　phone varchar(20)
 );

 

 #1.2 复制已有表结构+数据，到一张新表
 CREATE TABLE IF NOT EXISTS employee_bak
 AS
 SELECT name, phone
 FROM employee;

 

 #1.3 复制一个表结构到一张新表
 CREATE  TABLE IF NOT EXISTS employee_bak
 AS
 SELECT *
 FROM employee
 WHERE 1 = 2; 

 

#1.4 通过copy旧表结构的方式，新建新表，仅结构，不含数据
CREATE TABLE new_table LIKE old_table1;    

2. ALTER TABLE 修改表

 #2.1 新增一个字段
 ALTER TABLE employee ADD phone varchar(20);
 
 ALTER TABLE employee ADD gid varchar(36) FIRST;
 
 ALTER TABLE employee ADD salary double(10, 2) AFTER name;

 

 #2.2 修改一个字段，（数据类型（略）、长度、默认值等）
 ALTER TABLE employee MODIFY name varchar(25);

 ALTER TABLE employee MODIFY gid varchar(36) NOT NULL DEFAULT(UUID()); 

 ALTER TABLE employee MODIFY salary double(10, 2) NOT NULL DEFAULT 2800.00;

 

#2.3 重命名一个字段
ALTER TABLE employee CHANGE phone telephone varchar(25);

 

#2.4 删除一个字段
ALTER TABLE employee DROP COLUMN address;

3. RENAME TABLE 重命名表

#3.1 
RENAME TABLE employee TO my_employee;

 

#3.2 
ALTER TABLE my_employee RENAME TO employee;

4. DROP TABLE 删除表

#4.1 
DROP TABLE IF EXISTS employee2;

5. TRUNCATE TABLE 清空表

#5.1
TRUNCATE TABLE employee_bak2;

*6.TRUNCATE TABLE 清空 & DELETE FROM  清空 表数据探讨

#6.1 DCL中 COMMIT & ROLLBACK
    COMMINT # 一旦执行，意味着数据将永久的保存在数据库里，不能修改，数据不可回滚；
    ROLLBACK  # 一旦执行，意味着数据可以回滚，但仅回滚到最近的一次COMMIT之后；

 

#6.2 TRUNCATE TABEL & DELETE FROM
    #相同点：都可以全部清空数据
    #不同点：
        TRUNCATE TABLE #执行后，数据全部清空，不可以回滚；
        DELETE FROM #执行后，数据全部清空（不带where条件），但是可以回滚；

 

#6.3 DDL & DML 的说明
    #DDL 操作一旦执行，就不可以回滚；对于指令：SET AUTOCOMMIT = FALSE; 失效；
    #DML 操作一旦执行，默认情况下，不可能回滚；但是在执行前，执行了 SET AUTOCOMMIT = FALSE; 在执行DML，则可以回滚； 
        
    #演示1
    COMMIT;
    SELECT * FROM employee;
    SET AUTOCOMMIT  = FALSE;
    DELETE FROM employee;
    SELECT * FROM employee;
    ROLLBACK;
    SELECT  * FROM employee;
   
    #演示2
    COMMIT;
    SELECT * FROM employee;
    SET AUTOCOMMIT  = FALSE; #可以不要，无效指令
    TRUNCATE TABLE employee;
    SELECT * FROM employee;
    ROLLBACK;
    SELECT  * FROM employee;

 7. MySQL8.0 版本及以上版本的新特性：DDL原子化

#举例：数据库里有一张book1的表，没有一张book2的表
#8.0 版本及以上版本：同时删除两张表，如果其中部分失败，则都失败，即：执行下面指令，报错误，book1 没有被删除（同理事务的作用）；
#8.0 以下版本，执行下面指令，报错误，但是book1表任然被删除；
    DROP TABLE book1, book2;

 

DML 数据操作语音

8. INSERT INTO  ... VALUES...

#8.1 
#单条插入数据
INSERT INTO employee0819 VALUES 
(1, 'kit', 2900, '2024-8-21');

#单条，定义字段顺序插入数据
INSERT  INTO  employee0819(id, salary, name, create_date)
VALUES(6, 2300, 'mark', '2024-8-21');

#多条，定义字段顺丰插入多条数据
INSERT  INTO  employee0819(id, salary, name, create_date)
VALUES
(7, 2300, 'lily', '2024-2-21'),
(8, 8300, 'luncy', '2024-1-21');

 

#8.2 
#查询数据插入表
#注意：往employee0821表添加数据前，应该先去比较 employee0821 和 employee0819 表字段大小是否合适，
#如果目标表 employee0821字段小，应该先修改表字段大小。

INSERT INTO employee0821(id, name,salary, create_date) SELECT id, name, salary, create_date FROM employee0819 WHERE 1=1;

9. UPDATE...SET...WHERE...

#9.1 修改字段，有可能失败，因为约束的存在，同理上面的插入也有可能失败，也是因为约束存在。
UPDATE employee0819 SET salary= 9000 WHERE name='frank';

10. DELETE FROM...WHERE...

DELETE  FROM  employee0819  WHERE name ='kit';

 11.MySQL8.0 新特性：计算列

#11.1 计算列：该列是通过表里的其他字段计算而来的; CREATE/ALTER 表的时候可以创建。
# 修改a或者b的值的时候，c同步修改的。
CREATE  TABLE test1
(
    a int,
    b varchar(10),
    c varchar(50) GENERATED ALWAYS AS (CONCAT(a, '.', b)) VIRTUAL
);