SQL基本指令

#SQL

CREATE DATABASE IF NOT EXISTS `goods` CHARACTER SET utf8 COLLATE utf8_general_ci;
DROP DATABASE IF EXISTS `goods`;

CREATE TABLE IF NOT EXISTS `book_types` (  
`type_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍类型ID',  
`book_type` varchar(32) NOT NULL DEFAULT '' COMMENT '书籍类型',  
PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `book` (  
    `book_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍ID',  
    `name` varchar(32) NOT NULL DEFAULT '' COMMENT '书籍名称', 
    `price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '单价', 
    `author` varchar(16) NOT NULL DEFAULT '' COMMENT '作家名称',              `publishing_house` varchar(32) NOT NULL DEFAULT '' COMMENT '出版社',  
    `type_id` int unsigned NOT NULL COMMENT '书籍类型ID',  
    PRIMARY KEY (`book_id`), 
    KEY `fk_typeid` (`type_id`),  
    CONSTRAINT `fk_typeid` FOREIGN KEY (`type_id`) REFERENCES `book_types` (`type_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='书籍信息';

ALTER TABLE `book` ADD COLUMN `publishing_date` date NOT NULL COMMENT '出版日期' AFTER `publishing_house`;

DROP TABLE IF EXISTS `book`;
RENAME TABLE `book` TO `books`;

INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (1, '计算机');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (2, '历史');
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (1, 'Python编程从入门到精通', 00000049.90, '张三', '图灵出版社', '2022-01-15', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (2, '数据结构与算法分析', 00000035.00, '李四', '清华大学出版社', '2021-09-30', 1);


UPDATE `books` SET `publishing_date`='2016-12-18' WHERE `name`='三体' AND `author`='刘慈欣';
DELETE FROM `books` WHERE `name`='活法' AND `author`='梁文道';

SELECT `name` AS '书名'
FROM `books`   
WHERE `author`='刘慈欣'
LIMIT 10;

SELECT `type_id`, MAX(`price`) 
FROM `books` 
GROUP BY `type_id`  //GROUP BY子句代表分组，通常和聚合函数：最大值MAX、最小值MIN、平均值AVG、个数COUNT、求和SUM 配合使用。
HAVING MAX(`price`) > 25
ORDER BY MAX(`price`) DESC
LIMIT 10;

SELECT `bt`.`book_type`,MAX( `b`.`price` ) 
FROM `books` AS `b`  
LEFT JOIN `book_types` AS `bt` ON `b`.`type_id` = `bt`.`type_id` 
GROUP BY `b`.`type_id` 
HAVING MAX( `b`.`price` ) > 25 
ORDER BY MAX( `b`.`price` ) DESC 
LIMIT 10;

SELECT `book_type`, MAX(`price`)
FROM `books`
WHERE `type_id` IN (    
    SELECT `type_id`    
    FROM `books`    
    WHERE `price` > 25)
GROUP BY `book_type`
ORDER BY MAX(`price`) DESC
LIMIT 10;

//视图
CREATE VIEW `v_booktype_maxprice`//ALTER VIEW `v_booktype_maxprice`
AS 
SELECT `bt`.`book_type`,MAX( `b`.`price` ) FROM `books` AS `b`  LEFT JOIN `book_types` AS `bt` ON `b`.`type_id` = `bt`.`type_id` GROUP BY `b`.`type_id` HAVING MAX( `b`.`price` ) > 25 ORDER BY MAX( `b`.`price` ) DESC LIMIT 10;

SELECT * FROM `v_booktype_maxprice`;

//查看已有视图
SELECT `TABLE_NAME` 
FROM `information_schema`.`VIEWS` 
WHERE `TABLE_SCHEMA`='goods';

SHOW CREATE VIEW `goods`.`v_booktype_maxprice`\G
DROP VIEW IF EXISTS `goods`.`v_booktype_maxprice`;

-- 用户下单
-- ----------------------------
CREATE TRIGGER tg_insert_o//创建名为 tg_insert_o 的触发器
AFTER INSERT//触发时机为在 order_items 表中插入新记录之后
ON order_items FOR EACH ROW
UPDATE `books` SET `stock`=`stock`-new.`quantity` WHERE `book_id`=new.`book_id`;

-- ----------------------------
-- 用户修改订单
-- ----------------------------
CREATE TRIGGER tg_update_o
AFTER UPDATE
ON order_items FOR EACH ROW
UPDATE `books` SET `stock`=old.`quantity`-new.`quantity`+`stock` WHERE                      / /old.quantity` 表示更新前的订单数量。new.quantity` 表示更新后的订单数量。`book_id`=old.`book_id`;

-- ----------------------------
-- 用户退订单
-- ----------------------------
CREATE TRIGGER tg_delete_o
AFTER DELETE
ON order_items FOR EACH ROW
UPDATE `books` SET `stock`=`stock`+old.`quantity` WHERE `book_id`=old.`book_id`;

INSERT INTO `order_items`(`order_id`,`book_id`,`quantity`)  VALUES(1,3,1),(1,7,8),(1,8,2);
UPDATE `order_items` SET `quantity`=5 WHERE `order_item_id`=2;