创建用户并授予权限

  1.     create user chengyejing@localhost identified by 'chengyejing';
  2.     create user liuyingjie@localhost identified by 'liuyingjie';
  4.   grant all privileges on taste_explosion.* to liuyingjie@'localhost';
  5.   grant all privileges on taste_explosion.* to chengyejing@'localhost' ;

建表

user表

  1. DROP TABLE IF EXISTS `user`;
  2. CREATE TABLE `user`  (
  3.   `user_id` int(0) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  4.   `username` varchar(30)  NOT NULL COMMENT '登录账号',
  5.   `password` varchar(255)  NOT NULL COMMENT '登录密码',
  6.   `avatar` varchar(255)  NULL DEFAULT 'https://lionkliu-typore.oss-cn-shanghai.aliyuncs.com/2022/06/01/4f4649906edd418bbf0c6684f929d5f5MofTqghgaoEQ6477495682f214bd4c7510b41e8c6b5a.jpg' COMMENT '用户头像地址',
  7.   `nickname` varchar(255) NULL DEFAULT NULL COMMENT '用户名',
  8.   `email` varchar(255)  NULL DEFAULT NULL COMMENT '邮箱',
  9.   `address` varchar(255)  NULL DEFAULT NULL COMMENT '居住地址',
  10.   `type` tinyint(0) NULL DEFAULT 1 COMMENT '管理员:0,普通用户:1',
  11.   `status` tinyint(0) NULL DEFAULT 1 COMMENT '禁用状态：0，启用状态：1',
  12.   PRIMARY KEY (`user_id`) USING BTREE
  13. )ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

食谱表

  1. DROP TABLE IF EXISTS `recipe`;
  2. CREATE TABLE `recipe`  (
  3.   `id` int(0) NOT NULL AUTO_INCREMENT,
  4.   `img` varchar(255)  NOT NULL COMMENT '简略图',
  5.   `title` varchar(255)  NOT NULL COMMENT '标题',
  6.   `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  7.   `detail` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '文章详情',
  8.   `user_id` int(0) NOT NULL COMMENT '对应发表该食谱的用户id',
  9.   `likes` int(0) NULL DEFAULT 0 COMMENT '点赞数',
  10.   `is_swiper` int(0) NOT NULL DEFAULT 0 COMMENT '是否为轮播图，0假1真',
  11.   `category_id` int(0) NULL DEFAULT NULL COMMENT '食谱分类',
  12.   `views` int(0) NULL DEFAULT 1 COMMENT '浏览量',
  13.   `collections` int(0) NULL DEFAULT 0 COMMENT '收藏数',
  14.   PRIMARY KEY (`id`) USING BTREE
  15. ) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

评论表

  1. DROP TABLE IF EXISTS `comment`;
  2. CREATE TABLE `comment`  (
  3.   `id` int(0) NOT NULL AUTO_INCREMENT,
  4.   `recipe_id` int(0) NOT NULL COMMENT '对应是哪个食谱下的评论',
  5.   `root_id` int(0) NOT NULL DEFAULT -1 COMMENT '根评论-1',
  6.   `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  7.   `to_comment_user_id` int(0) NOT NULL DEFAULT -1 COMMENT '所回复的目标评论的userid',
  8.   `to_comment_id` int(0) NOT NULL DEFAULT -1 COMMENT '回复目标评论id',
  9.   `created_by` int(0) NOT NULL COMMENT '发表评论的用户id',
  10.   `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '评论发布时间',
  11.   PRIMARY KEY (`id`) USING BTREE
  12. ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

分类表

  1. DROP TABLE IF EXISTS `category`;
  2. CREATE TABLE `category`  (
  3.   `id` int(0) NOT NULL AUTO_INCREMENT,
  4.   `category_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  5.   `category_img` varchar(455) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  6.   `parent_id` int(0) NOT NULL DEFAULT 0 COMMENT '父级分类id，根评论为0',
  7.   PRIMARY KEY (`id`) USING BTREE
  8. )

视图

  1. -- 1、 创建show_index_card_view视图,用于展示首页食谱及发布人的信息
  2. CREATE VIEW show_index_card_view AS SELECT
  3. a.id,
  4. a.img,
  5. a.title,
  6. a.create_time,
  7. b.nickname,
  8. b.avatar,
  9. a.likes 
  10. FROM
  11.     recipe AS a,
  12.     `user` AS b 
  13. WHERE
  14.     a.user_id = b.user_id;
  16. --     2、用于展示首页食谱及发布人的信息，并通过发布时间降序排序
  17. CREATE VIEW show_index_card_by_create_time_desc_view AS SELECT
  18. a.id,
  19. a.img,
  20. a.title,
  21. a.create_time,
  22. b.nickname,
  23. b.avatar,
  24. a.likes 
  25. FROM
  26.     recipe AS a,
  27.     `user` AS b 
  28. WHERE
  29.     a.user_id = b.user_id 
  30. ORDER BY
  31.     a.create_time DESC;
  33. --     3、用于展示首页食谱及发布人的信息，并通过浏览量降序排序
  34. CREATE VIEW show_index_card_by_views_desc_view AS SELECT
  35. a.id,
  36. a.img,
  37. a.title,
  38. a.create_time,
  39. b.nickname,
  40. b.avatar,
  41. a.likes,
  42. a.views 
  43. FROM
  44.     recipe AS a,
  45.     `user` AS b 
  46. WHERE
  47.     a.user_id = b.user_id 
  48. ORDER BY
  49.     a.views DESC;
  51. --     4、用于展示首页食谱及发布人的信息，并通过点赞量降序排序
  52. CREATE VIEW show_index_card_by_likes_desc_view AS SELECT
  53. a.id,
  54. a.img,
  55. a.title,
  56. a.create_time,
  57. b.nickname,
  58. b.avatar,
  59. a.likes 
  60. FROM
  61.     recipe AS a,
  62.     `user` AS b 
  63. WHERE
  64.     a.user_id = b.user_id 
  65. ORDER BY
  66.     a.likes DESC;
  68. --     通过视图来查询
  69. SELECT
  70.     * 
  71. FROM
  72.     show_index_card_view;
  73. SELECT
  74.     * 
  75. FROM
  76.     show_index_card_by_likes_desc_view;
  77. SELECT
  78.     * 
  79. FROM
  80.     show_index_card_by_create_time_desc_view;
  82. --     查看创建的视图结构
  83. DESCRIBE show_index_card_view;
  84. SHOW CREATE VIEW show_index_card_view;
  86. -- 查看创建的所有视图
  87. show table status where comment='view';
  90. --     删除视图
  91. DROP VIEW show_index_card;

存储过程

  1. CREATE DEFINER=`root`@`%` PROCEDURE `pro_insert_into_star`( IN in_recipe_id INT, IN in_user_id INT )
  2. BEGIN
  3.     INSERT INTO star ( recipe_id, user_id )
  4.     VALUES
  5.         ( in_recipe_id, in_user_id );
  7. END
  1. CREATE DEFINER=`root`@`%` PROCEDURE `pro_getWorksById`(in uid int,out workId int ,out workImg VARCHAR(255),out workTitle VARCHAR(50),out userId int)
  2. begin
  3.     SELECT id,img,title，user_id into workId,workImg,workTitle,userId from recipe where user_id=uid;
  4. END
  1. CREATE DEFINER=`root`@`%` PROCEDURE `pro_count_works`(out out_count_work INT)
  2. BEGIN
  3.     select count(*) into out_count_work from recipe;
  5. END

触发器

1、定义deleted_user触发器，实现删除自动备份功能

  2. create table deleted_user as select * from `user` where 1=2;
  4. delimiter $
  5. CREATE TRIGGER delete_user_trigger BEFORE DELETE ON `user` FOR EACH ROW
  6. BEGIN
  7.         INSERT INTO deleted_user VALUES (
  8.             old.user_id,
  9.             old.username,
  10.             old.password,
  11.             old.avatar,
  12.             old.nickname,
  13.             old.email,
  14.             old.address,
  15.             old.type 
  16.         );
  18. END $ 
  19. delimiter;
  22. -- 查看所有触发器
  23. show TRIGGERS from `taste_explosion`;
  24. SHOW TRIGGERS;
  26. -- 测试触发deleted_user这个触发器
  27. delete from `user` where user_id=10;
  28. SELECT * from `user`;
  29. SELECT * from deleted_user;
  30. -- 删除触发器
  31. drop trigger delete_user_trigger;
  1. -- 当collection表添加一条数据时，recipe表中likes字段自动 +1
  2. delimiter $
  3. CREATE TRIGGER add_collection_trigger BEFORE INSERT ON collection FOR EACH ROW
  4. BEGIN
  5.         UPDATE recipe set collections=collections+1 where id=new.recipe_id;
  6. END $
  7. delimiter;
  9. -- 当collection表中删除一条数据时，recipe表中likes字段自动 -1
  10. delimiter $
  11. CREATE TRIGGER del_collection_trigger BEFORE DELETE ON collection FOR EACH ROW
  12. BEGIN
  13.         UPDATE recipe set collections=collections-1 where id=old.recipe_id;
  14. END $
  15. delimiter;
  17. drop TRIGGER del_collection_trigger;
  18. drop TRIGGER add_collection_trigger;
  20. show TRIGGERS;
  22. INSERT into collection(recipe_id,user_id) VALUES(5,5);
  23. DELETE from collection where user_id=5 and recipe_id=5;
  1.   -- 当star表添加一条数据时，recipe表中likes字段自动 +1
  2. delimiter $
  3. CREATE TRIGGER add_star_trigger BEFORE INSERT ON star FOR EACH ROW
  4. BEGIN
  5.         UPDATE recipe set likes=likes+1 where id=new.recipe_id;
  6. END $
  7. delimiter;
  9. -- 当star表中删除一条数据时，recipe表中likes字段自动 -1
  10. delimiter $
  11. CREATE TRIGGER del_star_trigger BEFORE DELETE ON star FOR EACH ROW
  12. BEGIN
  13.         UPDATE recipe set likes=likes-1 where id=old.recipe_id;
  14. END $
  15. delimiter;
  17. show TRIGGERS;