数据库基础篇 《10.创建和管理表DDL》

目录

1. 基础知识

1.1 一条数据存储的过程

1.2 标识符命名规则

​1.3 MySQL中的数据类型

​ 2. 创建和管理数据库 

2.1 创建数据库

2.2 使用数据库

2.3 修改数据库

2.4 删除数据库

3. 创建表

3.1 创建方式1

3.2 创建方式2  

​3.3 查看数据表结构

4. 修改表

4.1 追加一个列

4.2 修改一个列

​4.3 重命名一个列

​4.4 删除一个列 

5. 重命名表

6. 删除表  

7. 清空表

8. 内容拓展

9. 课后练习

9.1 练习1

1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作

2. 创建表dept01

3. 将表departments中的数据插入新表dept02中

4. 创建表emp01

5. 将列last_name的长度增加到50

6. 根据表employees创建emp02

7. 删除表emp01

8. 将表emp02重命名为emp01

9.在表dept02和emp01中添加新列test_column，并检查所作的操作

10.直接删除表emp01中的列 department_id

9.2 练习2

1 创建数据库 test02_market

2 创建数据表 customers

3 将 c_contact 字段移动到 c_birth 字段后面

4 将 c_name 字段数据类型改为 varchar(70)

5 将c_contact字段改名为c_phone

6 增加c_gender字段到c_name后面，数据类型为char(1) 

7 将表名改为customers_info

8 删除字段c_city

9.2 练习3

1 创建数据库test03_company

2 创建表offices

3 创建表employees 

4 将表employees的mobile字段修改到code字段后面

5 将表employees的birth字段改名为birthday

6 修改sex字段，数据类型为char(1)

7 删除字段note

8 增加字段名favoriate_activity，数据类型为varchar(100)

9 将表employees的名称修改为 employees_info

---

1. 基础知识

1.1 一条数据存储的过程

1.2 标识符命名规则

1.3 MySQL中的数据类型

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

 2. 创建和管理数据库 

2.1 创建数据库

方式1：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）
 

CREATE DATABASE IF NOT EXISTS 数据库名;

2.2 使用数据库

查看当前所有的数据库

SHOW DATABASES; #有一个S，代表多个数据库

查看当前正在使用的数据库

SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

使用/切换数据库

USE 数据库名;

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”

2.3 修改数据库

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等

2.4 删除数据库

方式 1 ：删除指定的数据库

DROP DATABASE 数据库名;

方式 2 ：删除指定的数据库（ 推荐 ）

DROP DATABASE IF EXISTS 数据库名;

3. 创建表

3.1 创建方式1

-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);

DESC emp;

CREATE TABLE dept(
-- int类型，自增
deptno INT(2) AUTO_INCREMENT,
dname VARCHAR(14),
loc VARCHAR(13),
-- 主键
PRIMARY KEY (deptno)
);

DESCRIBE dept;

3.2 创建方式2  

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

DESCRIBE dept80;

3.3 查看数据表结构

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

4. 修改表

4.1 追加一个列

语法格式如下：

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

举例

ALTER TABLE dept80
ADD job_id varchar(15);

4.2 修改一个列

可以修改列的数据类型，长度、默认值和位置

修改字段数据类型、长度、默认值、位置的语法格式如下

ALTER TABLE 表名 
MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

4.3 重命名一个列

4.4 删除一个列 

5. 重命名表

6. 删除表  

7. 清空表

SET autocommit = FALSE;
DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

阿里开发规范：

【参考】 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER ，有可能造成事故，故不建议在开发代码中使用此语句。

说明： TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。  

8. 内容拓展

CREATE DATABASE mytest;
USE mytest;
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);
SHOW TABLES;

9. 课后练习

9.1 练习1

1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作

CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf-8';

2. 创建表dept01

/*

字段 类型

id INT(7)

NAME VARCHAR(25)

*/

USE test01_office;
CREATE TABLE dept01(
    id INT(7),
    NAME VARCHAR(26)
);

3. 将表departments中的数据插入新表dept02中

CREATE TABLE dept02
AS SELECT *
FROM atguigudb.departments;

4. 创建表emp01

/*

字段 类型

id INT(7)

first_name VARCHAR (25)

last_name VARCHAR(25)

dept_id INT(7)

*/

CREATE TABLE emp1(
    id INT(7),
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    dept_id INT(7)
);

5. 将列last_name的长度增加到50

ALTER TABLE emp1
MODIFY last_name VARCHAR(50);

6. 根据表employees创建emp02

CREATE TABLE emp02
AS SELECT *
FROM atguigudb.employees;

7. 删除表emp01

DROP TABLE IF EXISTS emp01;

8. 将表emp02重命名为emp01

RENAME TABLE emp02 TO emp01;

9.在表dept02和emp01中添加新列test_column，并检查所作的操作

ALTER TABLE dept02 
    ADD test_column VARCHAR(10);
ALTER TABLE emp01 
    ADD test_column VARCHAR(10);
DESC dept02;
DESC emp01;

10.直接删除表emp01中的列 department_id

ALTER TABLE emp01
    DROP COLUMN department_id;

9.2 练习2

1 创建数据库 test02_market

CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';
USE test02_market;

2 创建数据表 customers

CREATE TABLE IF NOT EXISTS customers(
    c_num INT,
    c_name VARCHAR(50),
    c_contact VARCHAR(50),
    c_city VARCHAR(50),
    c_birth DATE
);

3 将 c_contact 字段移动到 c_birth 字段后面

ALTER TABLE customers
    MODIFY c_contact VARCHAR(50) After c_birth;

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;

9.2 练习3

1 创建数据库test03_company

CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';
USE test03_company;

2 创建表offices

CREATE TABLE offices(
    offices INT,
    city VARCHAR(30),
    address VARCHAR(50),
    county VARCHAR(50),
    postalCode VARCHAR(25)
);

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;

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 note;

8 增加字段名favoriate_activity，数据类型为varchar(100)

ALTER TABLE employees
    ADD favorite_activity VARCHAR(100);

9 将表employees的名称修改为 employees_info

RENAME TABLE employees
TO employees_info;