每天一练(MySQL 5.7版本)

-- 创建Market数据库
create database Market
-- 选中Market
use Market
-- 创建Customers表
create table customers(
num int(11) primary key unique auto_increment,
c_name varchar(50),
c_contact varchar(50),
c_city varchar(50),
c_birth datetime not null
)
-- 查看表结构
desc Customers
-- 将c_contact字段插入到c_birth后面
alter table Customers modify c_contact  varchar(50) after c_birth
-- 查看表结构
desc Customers   
-- 将c_name 字段类型改为Varchar(70)
alter table Customers modify c_name Varchar(70)
-- 查看表结构
desc Customers   
-- 将c_contact字段名改为c_phone
alter table Customers change c_contact  c_phone varchar(50)
-- 增加c_gender字段，类型为 char(1)
alter table Customers add  c_gender Char(1)
-- 将表名改成 customer_info
alter table Customers rename customers_info
--  删除字段 c_city
alter table customers_info drop c_city
-- 修改存储引擎为MyIsam

-- 先查再改
show create table customers_info 
-- 改成Mysam
alter table customers_info engine=Mysam

-- 创建Orders表
create table Orders(
o_num int(11) primary key auto_increment unique not null,
o_date date,
c_id varchar(50),
CONSTRAINT fk foreign key (o_num) references customers_info(num) -- 添加外键
)
-- 第二种方式添加外键
alter table Orders add foreign key (o_num) REFERENCES customers_info(num)
-- 删除外键
alter table Orders drop foreign key fk