第9章：创建和管理表

一、数据库的创建修改和删除

1.SQL的分类

DDL：数据定义语言 create创建、alter修改、drop删除、rename重命名、truncate清空

DML：数据操作语言 insert、delete、update、select

DCL：数据控制语言 commit提交、rollback回滚、savepoint保存点、grant权限、revoke回收权限

2.数据的存储过程

3.标识符命名规则

①数据库名、表名不能超过30字符，变量名限制29字符

②使用A-Z,a-z,0-9,_共63个字符

③对象名中间不能包含空格

④在同一个mysql软件中数据库不能重名。在同一个库中，表不重名。同一个表中，字段不能重名

⑤如果字段有保留字，在sql语句使用`着重号引起来

⑥保持在多个表的相同的字段名和类型的一致性

4.如何创建数据库

如果数据库不存在那么创建成功

create database if not exists 数据库名;

5.管理数据库

①查看当前连接的所有数据库

show databases;

②切换数据库

use 数据库名;

③查看当前数据库的表格

show tables;

④查看当前使用的数据库

select database() from dual;

⑤查看指定的数据库下的表格

show tables from 数据库名;

6.删除数据库

drop database if exists 数据库名

如果存在删除成功，如果不存在默默结束，不会报错。

二、创建数据表

1.MySQL常用的数据类型

 2.创建数据表方式1：

create table if not exists test(
id int,
emp_name varchar(15),
hire_date date
);

①查看表结构

desc test;

②查看创建表的语句

show create table 表名

 3.创建数据表方式2：基于现有表，表中的数据也会复制过来

create table test2
as 
select employee_id,last_name,salary
from employees;

①查看表结构

desc test2;

 

应用：把查询的结果存放到另一张表中，查询语句的别名是新创建表的名称

4.练习:创建一个表emp_copy，实现对employees表的复制，包括数据

create table emp_copy
as
select *
from employees

5.练习:创建一个表emp_blank，实现对employees表的复制，不包括数据

create table emp_copy
as
select *
from employees
where 0

三、数据表（修改、重命名、删除、清空）

1.修改表

alter table

①添加一个字段

alter table test

add salary double(10,2)

②重命名一个字段

alter table test

change salary sal double(10,2)

④删除一个字段

alter table 表名

drop column 列名

2.重命名表

rename table 表名

to 新表名

3.删除表

drop table if exists 表名;

4.清空表

truncate table 表名;

四、DCL的commit和rollback的使用

1. commit:

提交数据。一旦执行commit数据永久保存在数据库中，意思着数据不可以回滚

2. rollback

回滚数据。一旦执行rollback可以实现数据的回滚。

3.对比truncate table 和 delete from

①相同点：对表中所有数据的删除，保留表结构。

②不同点：

truncate table 数据全部删除，数据不能回滚

delete from 数据全部删除，可以回滚

truncate table 比delete速度快，且使用的系统和事务日志资源少，但truncate无事务且不触发trigger，有可能造成事故。不建议使用truncate

4.DDL和DML的说明

①DDL不可回滚,执行完后会执行一次commit

②DML默认不回滚。执行之前设置了 set autocommit = false，可以实现回滚。到最近的commit之后

5.演示—引擎是InnoDB

①delete—回滚到最近的commit之后

commit;
set autocommit=false;
delete from emp_copy;
select * from emp_copy;
rollback;
select * from emp_copy;

②truncate—不能回滚

commit;
set autocommit=false;
truncate table emp_copy;
select * from emp_copy;
rollback;
select * from emp_copy;

6.MySQL8.0的新特性：DDL的原子化

要么执行成功，要么有一个失败后回滚。

五、命名规范

1.表名和字段名用小写字母或数字。用字母开头，不要用数字。单词之间用_

emp_name,level3_name

2.禁用关键字

六、练习

练习一：

#1创建数据库test01_office,指明字符集为utf8。

create database if not exists test01_office character set utf8

#2创建表dept01

create table dept01(

id int(7),

name 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)

);

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

alter table emp01

modify last_name varchar(50);

#6. 根据表employees创建emp02

create table emp02

as

select *

from atguigudb.employees

where 0

#7. 删除表emp01

drop table emp01

#8. 将表emp02重命名为emp01

rename table emp02 to emp01

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

alter table dept02

add test_column int;

alter table emp01

add test_column int;

show create table emp01;

show create table dept02;

#10.直接删除表emp02中的列 department_id

alter table emp01

drop column department_id

练习二：

# 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

);

# 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

 

练习三：

# 1 创建数据库test03_company

create database if not exists test03_company;

# 2 创建表offices

create table offices(

officeCode int,

city varchar(30),

address varchar(50),

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

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

alter table employees

add column favoriate_activity varchar(100);

# 9、将表employees的名称修改为 employees_info

rename table employees to employees_info