数据库SQL复习

-- ----------------------------
-- Table structure for wm_channel
-- ----------------------------
DROP TABLE IF EXISTS `wm_channel`;
CREATE TABLE `wm_channel`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '频道名称',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '频道描述',
  `is_default` tinyint(1) UNSIGNED NULL DEFAULT NULL COMMENT '是否默认频道',
  `status` tinyint(1) UNSIGNED NULL DEFAULT NULL COMMENT '状态 0-停用，1-启用',
  `ord` tinyint(3) UNSIGNED NULL DEFAULT NULL COMMENT '序号',
  `created_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '频道信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for wm_news
-- ----------------------------
DROP TABLE IF EXISTS `wm_news`;
CREATE TABLE `wm_news`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` int(11) UNSIGNED NULL DEFAULT NULL COMMENT '自媒体用户ID',
  `title` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '标题',
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '图文内容',
  `type` tinyint(1) UNSIGNED NULL DEFAULT NULL COMMENT '文章布局\r\n            0 无图文章\r\n            1 单图文章\r\n            3 多图文章',
  `channel_id` int(11) UNSIGNED NULL DEFAULT NULL COMMENT '图文频道ID',
  `labels` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `created_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `submited_time` datetime(0) NULL DEFAULT NULL COMMENT '提交时间',
  `status` tinyint(2) UNSIGNED NULL DEFAULT NULL COMMENT '当前状态\r\n            0 草稿\r\n            1 提交（待自动审核）\r\n            2 审核失败\r\n（自动审核失败或人工审核失败）\r\n            3 待人工审核\r\n            4 人工审核通过\r\n            8 自动审核通过（待发布）\r\n            9 已发布',
  `publish_time` datetime(0) NULL DEFAULT NULL COMMENT '文章定时发布时间',
  `reason` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '拒绝理由',
  `article_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT 'APP端文章ID',
  `images` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '图片地址，多个图片用逗号分隔',
  `enable` tinyint(1) UNSIGNED NULL DEFAULT 1 COMMENT '0-未上架\n1-已上架',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6301 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '自媒体图文内容信息表' ROW_FORMAT = Dynamic;

insert into wm_channel values (9,"Scala","新型编程语言",0,1,9,"2022-06-27 12:00:00");

insert into wm_channel values (10,".NET","微软件编程语言",0,0,10,"2022-06-27 13:00:00"),(11,"C++","很好的编程语言",0,1,11,"2022-06-27 14:00:00");

delete from wm_channel where id=11;

update wm_channel set name="c++",description="不错的编程语言" where id=10;

select * from wm_channel where name = "java";

  select * from wm_channel where name = "java" and status = 1;

select * from wm_channel where name = "java" or status = 0;

select * from wm_channel where status != 1;

select  * from wm_channel where wm_channel.description like  "%框架%";

select * from wm_channel where ord in (6,7);

select * from wm_channel where ord > 5;

 select * from wm_channel where created_time > "2022-06-24 00:00:00";

select  * from wm_channel where created_time between "2021-06-22 00:00:00" and "2021-06-24 00:00:00";

select wn.title,wc.name channelName,wn.status,wn.created_time from wm_news wn,wm_channel wc where wn.channel_id = wc.id;

select wn.title,wc.name channelName,wn.status,wn.created_time from wm_news wn left join wm_channel wc on  wn.channel_id = wc.id;

select title,channel_id,created_time from wm_news where channel_id in (select id from wm_channel where name in ( 'java','Python'))

select * from wm_channel limit 1,3;

select * from wm_channel limit 4;

select  title,publish_time from wm_news order by publish_time desc ;

select min(ord) from wm_channel;

select max(ord) from wm_channel;

select  avg(ord) from wm_channel;

select sum(ord) from wm_channel;

select date_format(publish_time,"%Y-%m-%d") dateStr,count(*) dateCount from wm_news group by dateStr having dateCount>2 order by dateCount desc limit 5;