第10章_创建和管理表

1. 基础知识

1.1 一条数据存储的过程

在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

1.2 标识符命名规则

数据库名、表名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
必须保证字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

1.3 MySQL中的数据类型 

其中，常用的几类类型介绍如下 

 2. 创建和管理数据库

2.1 创建数据库

#1.1 如何创建数据库
#方式1：
 CREATE DATABASE mytest1; #使用的是默认的字符集
 
SHOW  CREATE DATABASE mytest1;
#方式2：创建数据库并指定的字符集
CREATE DATABASE mytest2  CHARACTER SET 'gbk';

SHOW  CREATE DATABASE mytest2; #使用的是指定的字符集

#方式3：创建数据库如果不存在，就创建。如果存在就创建不成功
CREATE DATABASE IF NOT EXISTS mytest3  CHARACTER SET 'utf8';

SHOW DATABASES;

2.2 使用数据库

查看当前连接的数据库
SHOW DATABASES;

#切换数据库
USE mytest1;

#查看当前数据库有哪些表
SHOW TABLES;

#查看当前使用的数据库
SELECT DATABASE() FROM DUAL;

#查看指定数据库保存的数据表
SHOW TABLES FROM mytest1;

2.3 修改数据库

#修改数据库
SHOW CREATE DATABASE mytest2;

ALTER DATABASE  mytest2 CHARACTER SET 'utf8';

2.4 删除数据库

#1.3删除数据库
#方式1：
DROP DATABASE mytest3;

SHOW DATABASES;

#方式2：如果数据库存在，就删除，不存在就结束
DROP  DATABASE IF EXISTS mytest2;

3. 创建表

3.1 创建方式1

#2.如何创建数据表
USE atguigudb;
SHOW TABLES ;

#方式1：
CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),#定义字符串吗，并指明其长度
hire_date DATE
);

#查看表结构
DESC myemp1;

#查看创建表的结构
SHOW CREATE TABLE  myemp1 ;#没有特别指明表的字符集，默认使用数据库的字符集

#查看表的数据
SELECT *FROM myemp1;

3.2 创建方式2

#方式2：基于现有的表创建新的表,同时导入数据

CREATE TABLE mymp2
AS  
SELECT employee_id,last_name,salary
FROM employees;

DESC mymp2;
DESC employees;

SELECT * FROM mymp2;

#说明1：查询语句字段的别名，可以作为新表的名称
#说明2：查询语句结构比较丰富，可以使用前面的各种select
CREATE TABLE myemp3
AS 
SELECT e.employee_id emp_id,e.last_name  lname,d.department_id
FROM employees e JOIN departments d
ON e.department_id=d.department_id;


DESC myemp3;
SELECT * FROM myemp3;

#练习：创建一个employees_copy,实现对表的复制，包括表的数据
   CREATE TABLE employees_copy
   AS 
   SELECT * 
   FROM employees;
   
   SELECT * FROM employees_copy;

#练习: 创建一个employees_blank,实现对表的复制，不包括表的数据
    CREATE TABLE employees_blank
    AS   
    SELECT  * 
    FROM employees
    WHERE department_id =NULL;
    
    SELECT * FROM employees_blank;

4. 修改表

4.1 追加一个列

DESC myemp1;
#3.1 添加一个字段
  ALTER TABLE  myemp1
  ADD salary DOUBLE (10,2);#默认添加到最后一个
 
  ALTER TABLE myemp1
  ADD phone_number VARCHAR(20) FIRST ;
 
  ALTER TABLE  myemp1
  ADD  email VARCHAR(45) AFTER emp_name;

4.2 修改一个列

#3.4 删除一个字段
ALTER TABLE myemp1
DROP COLUMN  my_email; 

#3.2 修改一个字段：数据类型，长度，默认值
   ALTER TABLE myemp1
   MODIFY  emp_name VARCHAR(35) ;  
  
  ALTER TABLE myemp1
  MODIFY  emp_name VARCHAR(45) DEFAULT 'aaa';

4.3 重命名一个列

#3.3 重命名一个字段
 ALTER TABLE  myemp1
 CHANGE salary  mothly_salary  DOUBLE (10,2);

 ALTER TABLE myemp1
 CHANGE email my_email VARCHAR(30);

4.4 删除一个列

#3.4 删除一个字段
ALTER TABLE myemp1
DROP COLUMN  my_email; 

5. 重命名表

#4。重命名表    
#方式1：
RENAME TABLE myemp1
TO myemp11; 

DESC myemp11;

#方式2：
ALTER TABLE myemp11
RENAME TO  myemp12;

DESC  myemp12;

6. 删除表

#5.删除表
#将表结构和表数据都进行删除
DROP  TABLE IF EXISTS myemp12;

SHOW TABLES;

7. 清空表

#6.清空表
#清空表的所有数据，但是表的结构保留

SELECT *FROM employees_copy;

TRUNCATE TABLE employees_copy;

 8. 内容拓展 

#7.DCL中COMMIT和ROLLBACK
#COMMIT:提交数据，一旦执行了COMMIT ,数据就永久保存在数据库中了
#        ，意味着不可以进行回滚了
#ROLLBACK:回滚数据，一旦执行ROLLBACK,可以回滚，回滚到最近的COMMIT之后  

拓展2：如何理解清空表、删除表等操作需谨慎？！

#8。对比：TRUNCATE TABLE 和 DELETE FROM
# 相同点：都是可以实现对表中所有数据进行删除，保留表的结构
# 不同点： 
#        TRUNCATE TABLE :一旦执行此操作，表里的数据全部清除，
#                        同时不能进行回滚(里面自动进行提交)
#         DELETE FROM ：一旦执行此操作，表里的数据可以全部清除
#                       数据可以回滚（可以将自动提交去除）
 

#9.DDL 和 DML 的说明
#DDL：操作一旦执行，就不可以回滚，指令SET autocommit=false 对DDL无效
#DML：操作一旦执行，就不可以回滚，但在执行DML之前，
#       指令SET autocommit=false，指令就可以进行回滚 

#演示：delete from
#1)
COMMIT;
#2
SELECT *
FROM myemp3;
#3
SET autocommit=FALSE;
#4
DELETE FROM myemp3;
#5
SELECT *FROM myemp3;
#6
ROLLBACK;
#7
SELECT *FROM myemp3;

#演示 TRUNCATE table
#1)
COMMIT;
#2
SELECT *
FROM myemp3;
#3
SET autocommit=FALSE;
#4
TRUNCATE TABLE myemp3;
#5
SELECT *FROM myemp3;
#6
ROLLBACK;
#7
SELECT *FROM myemp3;

拓展3：MySQL8新特性—DDL的原子化 

1.在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚

2.而在MYSQL57中的直接进行，没有回滚

##############
#MYSQL8.0的新特性：DDL的原子化

CREATE  DATABASE mytest;

USE mytest;

CREATE TABLE book1(
book_id INT,
book_name VARCHAR(244)
);

SHOW TABLES;

DROP TABLE book1,book2;

课后练习

#第10章 创建和管理表练习题
#1. 创建数据库test01_office,指明字符集为utf8。
#并在此数据库下执行下述操作
  CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8';
#2. 创建表dept01x
 CREATE TABLE dept01(
 id  INT(7),
 NAME  VARCHAR(25) 
 );
 
 SHOW TABLES;
 DROP TABLE dept02;
 /*
字段      
类型
id       
NAME     
*/
 INT(7)
 VARCHAR(25)
 #3. 将表departments中的数据插入新表dept02中
   CREATE TABLE  dept02
   AS 
   SELECT *
   FROM atguigudb.`departments`;
   

#4. 创建表emp01
   CREATE TABLE emp01(
       id          INT(7),
 first_name  VARCHAR (25),
 last_name   VARCHAR(25),
 dept_id     INT(7) 
     );
   
 /*
字段         
类型
id          
INT(7)
 first_name  VARCHAR (25)
 last_name   VARCHAR(25)
 dept_id     
INT(7)
 */
 #5. 将列last_name的长度增加到50
  ALTER TABLE emp01
  MODIFY  last_name VARCHAR(50);
  
  DESC emp01;
 #6. 根据表employees创建emp02
  CREATE TABLE emp02
  AS 
  SELECT *
  FROM atguigudb.`employees`;
 #7. 删除表emp01
  DROP TABLE emp01;
  
  SHOW TABLES;
  SELECT *FROM emp01;
 #8. 将表emp02重命名为emp01
  RENAME  TABLE emp02  TO emp01 ;
 #9.在表dept02和emp01中添加新列test_column，并检查所作的操作
   ALTER TABLE dept02
   ADD  test_column INT(20);
   ALTER TABLE emp01 
  ADD test_column INT (20) ;

   
#10.直接删除表emp01中的列 department_id
    ALTER TABLE emp01
    DROP COLUMN department_id;

#练习2 
c_num     	  INT
 c_name   	 VARCHAR(50)
 c_contact 	VARCHAR(50)
 c_city 	VARCHAR(50)
 c_birth    	DATE
# 1、创建数据库 test02_market
    CREATE DATABASE IF NOT EXISTS test02_market;
    
 # 2、创建数据表 customers
 CREATE TABLE customers(
       c_num INT,
       c_name VARCHAR(50),
       c_contact VARCHAR(50),
       c_city   VARCHAR(50),
       c_birth   DATE
     )
     
     SHOW TABLES;
 # 3、将 c_contact 字段移动到 c_birth 字段后面
    ALTER TABLE customers
    MODIFY c_contact VARCHAR(50) AFTER  c_birth;
    
    DESC customers;
# 4、将 c_name 字段数据类型改为 varchar(70)
    ALTER TABLE customers
    MODIFY c_name  VARCHAR(70);
 # 5、将c_contact字段改名为c_phone
      ALTER TABLE customers
      CHANGE c_contact c_phone VARCHAR(50);
 # 6、增加c_gender字段到c_name后面，数据类型为char(1)
     ALTER TABLE customers
     ADD  c_gender CHAR(1) AFTER c_name;
 # 7、将表名改为customers_info
    RENAME TABLE customers TO customers_info;
 # 8、删除字段c_city
    ALTER TABLE customers_info
    DROP COLUMN c_city;
    
    DESC customers_info;
    
    
#练习3 
officeCode 	INT
 city 		VARCHAR(30)
address 	VARCHAR(50)
 country 	VARCHAR(50)
  postalCode 	VARCHAR(25)
empNum		INT
 lastName 	VARCHAR(50)



 firstName 	VARCHAR(50)
 mobile 	VARCHAR(25)
 `code` 		INT
 jobTitle 	 VARCHAR(50)
 birth		 DATE
note 		VARCHAR(255)
 sex		 VARCHAR(5)
# 1、创建数据库test03_company
  CREATE DATABASE IF NOT EXISTS test03_company  CHARACTER SET 'utf8';
# 2、创建表offices 
   CREATE TABLE offices(
      officeCode 	INT,
 city 		VARCHAR(30),
address 	VARCHAR(50),
 country 	VARCHAR(50),
  postalCode 	VARCHAR(25)

   )   
   
   DROP TABLE offices;
   
  SHOW TABLES;
# 3、创建表employees
   CREATE TABLE employees(
   empNum		INT,
 lastName 	VARCHAR(50),
 firstName 	VARCHAR(50),
 mobile 	VARCHAR(25),
 CODE 		INT,
 jobTitle 	 VARCHAR(50),
 birth		 DATE,
note 		VARCHAR(255),
 sex		 VARCHAR(5) 
   )
# 4、将表employees的mobile字段修改到code字段后面
    ALTER TABLE employees 
    MODIFY  mobile VARCHAR(25) AFTER CODE;
    
    DESC  employees;
# 5、将表employees的birth字段改名为birthday
   ALTER  TABLE employees
   CHANGE birth birthday DATE;
 # 6、修改sex字段，数据类型为char(1)
  ALTER TABLE employees
  MODIFY  sex  CHAR(1);
 # 7、删除字段note
  ALTER TABLE employees
  DROP COLUMN  note;
 # 8、增加字段名favoriate_activity，数据类型为varchar(100)
      ALTER  TABLE employees
      ADD favoriate_activity VARCHAR(100);
 # 9、将表employees的名称修改为 employees_info    
   RENAME TABLE employees TO  employees_info;