下面整理mysql修改和删除表相关的语句,以便工作查阅之用。
1 增加column
ALTER TABLE products ADD product_description CHAR(20);
2 增加column,将其作为第一列
ALTER TABLE products ADD product_description CHAR(20) first;
3 增加column,指定其在某一列之后
ALTER TABLE products ADD product_description CHAR(20) after product_name;
4 删除column
alter table products drop column product_description;
5 修改表的名称
-- 方法一
RENAME TABLE products TO new_products;
-- 方法二
ALTER TABLE customer RENAME customer2;
6 修改column的类型
alter table products modify product_description varchar(60);
7 删除表
drop table products;
8 添加外键
alter table products add FOREIGN key supplier_id_fk(supplier_id) REFERENCES suppliers(supplier_id) ON DELETE CASCADE;
9 视图相关
-- 创建视图
CREATE VIEW prodsupp AS SELECT prod_code, prod_name, supplier_name, supplier_address
FROM products, suppliers WHERE (products.supplier_id = suppliers.supplier_id);
-- 查看视图
show create view prodsupp;
-- 替换视图
create or replace view prodsupp as select ...
--删除视图
drop view prodsupp;