项目Mysql建数据库知识点汇总，涵盖：六大约束（非空、默认、唯一、检查、主键、外键）、视图、存储过程、触发器、内置函数、级联删除、正则表达式…

CREATE DATABASE ypkj1;
USE ypkj1;
CREATE TABLE vip_info(
v_id INT PRIMARY KEY AUTO_INCREMENT,
v_username VARCHAR(40) NOT NULL UNIQUE,
v_password VARCHAR(20) NOT NULL CHECK(LENGTH(v_password)>=6),
v_sex ENUM('男','女'),
v_phone CHAR(11) CHECK(v_phone REGEXP'1[0-9]{10}'),
v_address VARCHAR(100)
);
-- alter table vip_info modify v_username VARCHAR(40) NOT NULL;
-- alter table vip_info modify v_address varchar(100);
-- 添加中间列字段
-- alter table vip_info add v_sex ENUM('男','女') after v_password;
-- alter table vip_info drop v_sex;
-- alter table goods_info add g_imglink varchar(50) not null default'img-default.png' after g_desc;
-- 修改字段
-- alter table vip_info modify v_phone char(11) CHECK(v_phone REGEXP'1[0-9]{10}');
-- 添加非空约束
-- alter table vip_info modify v_username varchar(20) not null;
-- 删除非空约束
-- alter table vip_info modify v_username varchar(20);
-- 删除唯一约束（索引）
-- alter table vip_info drop index v_username;
-- 添加唯一约束（索引）的两种方法（第二种方法可用于复合唯一约束（索引））
-- alter table vip_info modify v_username varchar(20) not null unique;
-- alter table vip_info add unique(v_username);
-- 删除主键
-- alter table vip_info2 drop primary key;
-- 添加主键的两种方法（第二种方法可用于复合主键）
-- ALTER TABLE vip_info2 MODIFY v_id INT primary key;
-- ALTER TABLE Vip_info2 add PRIMARY KEY(v_id);
-- 修改约束
-- alter table vip_info drop constraint vip_info_chk_2;
-- alter table vip_info add check(v_phone regexp'1[0-9]{10}');
-- 重新排序主键自增
-- 自增函数是以最大值+1自动写入的，只有先删除此列，再添加才能重新排序
-- alter table vip_info1 drop v_id;
-- alter table vip_info1 add v_id int primary key auto_increment first;
-- 此方法只能改成大于已有数据值的本值开始（无数据时可重新排序）
-- alter table vip_info1 auto_increment=1;
CREATE TABLE goods_info1(
g_id INT PRIMARY KEY AUTO_INCREMENT,
g_name VARCHAR(40) NOT NULL,
g_desc VARCHAR(200) NOT NULL DEFAULT'该商品还没有任何描述！',
g_imglink VARCHAR(100) NOT NULL DEFAULT'/BootStrap/img/goods/default.png',
g_price DOUBLE NOT NULL CHECK(g_price>=0),
g_store INT NOT NULL CHECK(g_store>=0),
g_category ENUM('晓 Spark 系列','御 Mavic 系列','悟 Inspire系列',
'精灵 Phantom 系列','农业 T 系列','行业 M 系列','专业 Matrice 系列'),
g_state ENUM('在售','已下架') NOT NULL DEFAULT'在售'
);
-- alter table goods_info modify g_imglink varchar(100) not null default'/BootStrap/img/goods/default.png';
-- alter table goods_info modify g_name VARCHAR(40) NOT NULL;
-- alter table goods_info modify g_category ENUM('晓 Spark 系列','御 Mavic 系列','悟 Inspire 系列',
-- '精灵 Phantom 系列','农业 T 系列','行业 M 系列','专业 Matrice 系列') after g_store;
-- 修改商品表，加状态属性
-- alter table goods_info modify g_state enum('在售','已下架') not null default'在售';
CREATE TABLE comment_info(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_content VARCHAR(200) NOT NULL DEFAULT'该会员没有留下任何评论！',
c_date DATETIME NOT NULL DEFAULT NOW(),
v_id INT NOT NULL,
FOREIGN KEY(v_id) REFERENCES vip_info(v_id) ON DELETE CASCADE,
g_id INT NOT NULL,
FOREIGN KEY(g_id) REFERENCES goods_info(g_id) ON DELETE CASCADE
);
-- 查看表格约束名称
-- show create table comment_info;
-- SHOW CREATE TABLE shop_goods_info;
-- SHOW CREATE TABLE order_goods_info;
-- 重建商品级联删除外键
-- alter table comment_info drop foreign key comment_info_ibfk_1;
-- alter table comment_info add FOREIGN KEY(g_id) REFERENCES goods_info(g_id) ON DELETE CASCADE;
-- ALTER TABLE shop_goods_info DROP FOREIGN KEY shop_goods_info_ibfk_2;
-- ALTER TABLE shop_goods_info ADD FOREIGN KEY(g_id) REFERENCES goods_info(g_id) ON DELETE CASCADE;
-- ALTER TABLE order_goods_info DROP FOREIGN KEY order_goods_info_ibfk_1;
-- ALTER TABLE order_goods_info ADD FOREIGN KEY(g_id) REFERENCES goods_info(g_id) ON DELETE CASCADE;
-- 修改评论表和订单表，实现时间字段默认生成为插入数据当前时间
-- alter table comment_info modify c_date datetime not null default now();
-- alter table order_info modify o_date datetime not null default now();
-- 关联表添加会员表级联删除约束（注销会员或删除订单）
-- ALTER TABLE comment_info drop foreign key fk_c_v;
-- alter table comment_info ADD FOREIGN KEY(v_id) REFERENCES vip_info(v_id) on delete cascade;
-- 添加外键约束
-- alter table comment_info add FOREIGN KEY(g_id) REFERENCES goods_info(g_id);
CREATE TABLE shop_info(
s_id INT PRIMARY KEY AUTO_INCREMENT,
v_id INT NOT NULL,
FOREIGN KEY(v_id) REFERENCES vip_info(v_id) ON DELETE CASCADE
);
CREATE TABLE order_info(
o_id INT PRIMARY KEY AUTO_INCREMENT, 
o_date DATETIME NOT NULL DEFAULT NOW(),
o_paymethod ENUM('微信','支付宝') NOT NULL DEFAULT'微信',
o_payorder VARCHAR(40) NOT NULL,
o_deliver ENUM('已发货','未发货') NOT NULL DEFAULT'未发货',
v_id INT NOT NULL,
FOREIGN KEY(v_id) REFERENCES vip_info(v_id) ON DELETE CASCADE
);
-- alter table order_info modify o_paymethod enum('微信','支付宝') NOT NULL default'微信' after o_date;
-- alter table order_info add o_payorder varchar(40) not null after o_paymethod;
-- alter table order_info add o_deliver enum('已发货','未发货') not null default'未发货' after o_payorder; 
CREATE TABLE shop_goods_info(
s_id INT,
g_id INT,
FOREIGN KEY(s_id) REFERENCES shop_info(s_id) ON DELETE CASCADE,
FOREIGN KEY(g_id) REFERENCES goods_info(g_id) ON DELETE CASCADE,
PRIMARY KEY(s_id,g_id),
sg_count INT NOT NULL CHECK(sg_count>=1)
);
CREATE TABLE order_goods_info(
o_id INT,-- 主键约束=效果=唯一约束+非空约束
g_id INT,
FOREIGN KEY(o_id) REFERENCES order_info(o_id) ON DELETE CASCADE,
FOREIGN KEY(g_id) REFERENCES goods_info(g_id) ON DELETE CASCADE,
PRIMARY KEY(o_id,g_id),
og_price DOUBLE NOT NULL CHECK(og_price>=0),
og_count INT NOT NULL CHECK(og_count>=1)
);

-- 插入内容
INSERT INTO vip_info(v_username,v_password,v_sex,v_phone,v_address)
VALUES('admin','Aa123456','男','13178587326','广东省湛江市吴川市梅菉镇1号');
INSERT INTO goods_info(g_name,g_price,g_store)
VALUES('无人机1',9999.99,1111);
INSERT INTO comment_info(v_id,g_id)
VALUES(1,1);
INSERT INTO shop_info(v_id)
VALUES(1);
INSERT INTO order_info(v_id)
VALUES(1);
INSERT INTO shop_goods_info
VALUES(1,1,1);
INSERT INTO order_goods_info
VALUES(1,1,8888.88,1);

-- 创建视图
-- 1、查询订单信息的视图
CREATE VIEW view_orders
AS
SELECT order_goods_info.*,o_date,v_id 
FROM order_info,order_goods_info
WHERE order_info.o_id=order_goods_info.`o_id`;
-- 2、查询购物车信息的视图
CREATE VIEW view_shops
AS
SELECT shop_goods_info.*,v_id 
FROM shop_info,shop_goods_info
WHERE shop_info.`s_id`=shop_goods_info.`s_id`;
-- 3、查询admin管理员购买了什么商品信息的视图
CREATE VIEW view_orders_admin
AS
SELECT order_goods_info.*,o_date,v_username 
FROM order_info,order_goods_info,vip_info
WHERE order_info.o_id=order_goods_info.`o_id`
AND order_info.`v_id`=vip_info.`v_id`
AND v_username='admin';
-- 调用视图
-- select *from view_orders_admin

-- 创建存储过程
-- 1、查看指定会员全部评论信息的存储过程
DELIMITER $$
CREATE PROCEDURE proc_comments_vip(IN vusername VARCHAR(20))
BEGIN
SELECT comment_info.*,vusername FROM comment_info,vip_info
WHERE vip_info.`v_id`=comment_info.`v_id`
AND v_username=vusername;
END$$
DELIMITER ;
-- 2、查看指定商品全部评论信息的存储过程
DELIMITER //
CREATE PROCEDURE proc_comments_goods(IN gname VARCHAR(20))
BEGIN
SELECT comment_info.*,gname FROM comment_info,goods_info
WHERE goods_info.`g_id`=comment_info.`g_id`
AND g_name=gname;
END//
DELIMITER ;
-- 3、查看指定会员购买过全部商品信息的存储过程
DELIMITER &&
CREATE PROCEDURE proc_goods_vip(IN vusername VARCHAR(20))
BEGIN
SELECT order_goods_info.*,o_date,vusername
FROM vip_info,order_info,order_goods_info
WHERE vip_info.`v_id`=order_info.`v_id`
AND order_info.`o_id`=order_goods_info.`o_id`
AND v_username=vusername;
END&&
DELIMITER ;
-- 删除存储过程
-- drop procedure proc_comments_vip;
-- 调用存储过程的两种方法
-- call proc_goods_vip('admin');
-- set @g_name='无人机2';
-- call proc_comments_goods(@g_name);

-- 创建触发器
-- 1、当订单-商品表中插入订单商品时，商品库存减购买数量的触发器
DELIMITER $$
CREATE TRIGGER trig_order_goods_ins
BEFORE INSERT
ON order_goods_info FOR EACH ROW
BEGIN
UPDATE goods_info
SET goods_info.`g_store`=goods_info.`g_store`-new.og_count
WHERE goods_info.`g_id`=new.g_id;
END$$
DELIMITER ;
-- 2、当订单-商品表中更新订单时，商品库存更新数量差的触发器
DELIMITER //
CREATE TRIGGER trig_order_goods_upd
BEFORE UPDATE
ON order_goods_info FOR EACH ROW
BEGIN
UPDATE goods_info
SET goods_info.`g_store`=goods_info.`g_store`-new.og_count+old.og_count
WHERE goods_info.`g_id`=new.g_id;
END//
DELIMITER ;
-- 3、当订单-商品表中删除订单时，商品库存加取消数量的触发器
DELIMITER &&
CREATE TRIGGER trig_order_goods_del
AFTER DELETE
ON order_goods_info FOR EACH ROW
BEGIN
UPDATE goods_info
SET goods_info.`g_store`=goods_info.`g_store`+old.og_count
WHERE goods_info.`g_id`=old.g_id;
END&&
DELIMITER ;
-- 4、当商品表中插入商品时，商品库存为0自动已下架状态的触发器
DELIMITER ||
CREATE TRIGGER trig_goods_ins
BEFORE INSERT
ON goods_info FOR EACH ROW
BEGIN
IF new.g_store=0
THEN
SET new.g_state='已下架';
END IF;
END||
DELIMITER ;
-- 5、当商品表中更新商品时，商品库存为0自动已下架状态的触发器
DELIMITER ||
CREATE TRIGGER trig_goods_upd
BEFORE UPDATE
ON goods_info FOR EACH ROW
BEGIN
IF(new.g_store=0)
THEN
SET new.g_state='已下架';
END IF;
END||
DELIMITER ;
-- 6、当订单表中删除商品时，如果订单仅有一条商品即自动删除订单
DELIMITER &&
CREATE TRIGGER tri_order_goods_del1
AFTER DELETE
ON order_goods_info FOR EACH ROW
BEGIN
IF(NOT EXISTS(SELECT * FROM order_goods_info WHERE o_id=old.o_id))
THEN
DELETE FROM order_info WHERE o_id=old.o_id;
END IF;
END &&
DELIMITER ;
-- 添加购物车的商品，如果有此商品则增加数量即可（通过后端查询判断实现）
-- 数据库触发器无法实现（MySQL的触发器是原表锁定操作，触发器不能作用于原表）
-- 测试触发器
-- delete from order_goods_info
-- where o_id=27 and g_id=4;
-- INSERT INTO goods_info
-- VALUES(11,'无人机11','fdofusifdfsf第三方','wewrrerew二位翁',5555.76,0,'农业 T 系列','在售');
-- UPDATE goods_info
-- SET g_store=0
-- WHERE g_id=10;
-- 综合测试多触发器
-- UPDATE order_goods_info
-- SET og_count=2
-- WHERE o_id=10
-- AND g_id=10;
-- 测试触发器
-- insert into order_goods_info
-- values(11,1,8888.88,1);
-- update order_goods_info
-- set og_count=4
-- where o_id=10;
-- delete from order_goods_info
-- where o_id=10;

-- 测试级联注销会员或删除订单
-- delete from vip_info
-- where v_id=10;