MySQL_Z10创建和管理表

#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作
#2. 创建表dept01
/*
字段类型
id INT(7)
NAME VARCHAR(25)
*/
#3. 将表departments中的数据插入新表dept02中
#4. 创建表emp01
/*
字段类型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/
#5. 将列last_name的长度增加到50
#6. 根据表employees创建emp02
#7. 删除表emp01
#8. 将表emp02重命名为emp01
#9.在表dept02和emp01中添加新列test_column，并检查所作的操作
#10.直接删除表emp01中的列department_id

create database test01_office character set 'utf8';
use test01_office;
create table dept01(
			id int(7),
			name varchar(25),
);
create table dept02 
as
select *
from atguigudb.departments;

create table emp01(
			id int(7),
			first_name varchar(25),
			last_name varchar(25),
			dept_id int(7)
);

alter table emp01
modify last_name varchar(50);

create table emp02
as
select *
from employees;

drop table emp01;

rename table emp02 to emp01;

alter table dept01
add test_column varchar(10);

alter table emp01
add test_column varchar(10);

alter table emp01
drop column department_id;

# 1、创建数据库test02_market
# 2、创建数据表customers
create database if not exists test02_market character set 'utf8';
use test02_market;

create table customers(
							c_num int,
							c_name varchar(50),
							c_contact varchar(50),
							c_city varchar(50),
							c_birth date);

select *
from customers;

desc customers;
show create table customers;

# 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);  #必须带上varchar(50)

# 6、增加c_gender字段到c_name后面，数据类型为char(1)
alter table customers
add c_gender char(1) after c_name;
desc customers;


# 7、将表名改为customers_info
rename table customers to customers_info;

# 8、删除字段c_city
alter table customers_info
drop c_city;