MySQL 语句

一、设计表，写出建表语句

#图书表
DROP TABLE IF EXISTS `book_table`;
CREATE TABLE `book_table` (
  `b_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键(自增长)',
  `b_book_id` VARCHAR(18) NOT NULL DEFAULT '' COMMENT '图书编号',
  `b_author_id` VARCHAR(18) NOT NULL DEFAULT '' COMMENT '作者编号',
  `b_book_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '图书名称',
  `b_pages` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '图书页数',
  `b_price` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '图书价格',
  `b_press` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '出版社',
  PRIMARY KEY (`b_id`),
  UNIQUE KEY `uniq_b_book_id` (`b_book_id`),
  KEY `idx_b_author_id` (`b_author_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3 COMMENT='图书表';

#奖项表
DROP TABLE IF EXISTS `cup_table`;
CREATE TABLE `cup_table` (
  `c_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键(自增长)',
  `c_book_id` VARCHAR(18) NOT NULL COMMENT '图书编号',
  `c_author_id` VARCHAR(18) NOT NULL COMMENT '作者编号',
  `c_cup_type` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '获奖名称',
  `c_cup_time` DATE NOT NULL DEFAULT '1970-01-01' COMMENT '获奖时间',
  PRIMARY KEY (`c_id`),
  FOREIGN KEY `fk_book_id` (`c_book_id`) REFERENCES book_table(`b_book_id`),
  FOREIGN KEY `fk_author_id` (`c_author_id`) REFERENCES book_table(`b_author_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3 COMMENT='奖项表';

#作者表
DROP TABLE IF EXISTS `author_table`;
CREATE TABLE `author_table` (
  `a_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键(自增长)',
  `a_author_id` VARCHAR(18) NOT NULL COMMENT '作者编号',
  `a_author_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '作者姓名',
  `a_content` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '作者介绍',
  PRIMARY KEY (`a_id`),
  FOREIGN KEY(`a_author_id`) REFERENCES book_table(`b_author_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb3 COMMENT='作者表';

---

二、设计索引,写出创建索引的语句

#添加主键约束
ALTER TABLE `book_table` ADD CONSTRAINT pk_booktable PRIMARY KEY (`b_id`) ;
#添加唯一约束
ALTER TABLE `book_table` ADD CONSTRAINT uniq_b_book_id UNIQUE (`b_book_id`);
#添加索引
ALTER TABLE `book_table` ADD INDEX idx_b_author_id (`b_author_id`);
#添加外键
ALTER TABLE `cup_table` ADD CONSTRAINT fk_book_id FOREIGN KEY(`c_book_id`) REFERENCES book_table(`b_book_id`);
#添加check约束
ALTER TABLE `cup_table` ADD CONSTRAINT ck_cuptime CHECK(`c_cup_time`>=`1970-01-01`);
#使用create创建索引
CREATE INDEX idx_b_author_id ON book_table(`b_author_id`);  

---

三、完成以下SQL

#1.查询姓王的作者有多少  
SELECT COUNT(*) FROM author_table WHERE a_author_name LIKE '王%';

#2.查询获奖作者总人数  
SELECT COUNT(DISTINCT c_author_id) FROM cup_table;

#3.查询同时获得过金奖、银奖的作者姓名   
SELECT a_author_name FROM author_table WHERE a_author_id IN (SELECT c1.c_author_id FROM cup_table AS c1, cup_table AS c2 WHERE c1.c_cup_type = '金奖' AND c2.c_cup_type = '银奖' AND c1.c_author_id = c2.c_author_id);


#4.查询获得过金奖的图书有多少本，银奖的有多少本   
SELECT COUNT(c_book_id) FROM cup_table WHERE c_cup_type = '金奖';
SELECT COUNT(c_book_id) FROM cup_table WHERE c_cup_type = '银奖';

#5.查询最近一年内获过奖的作者姓名   
SELECT a.a_author_name FROM author_table AS a JOIN cup_table AS c ON a.a_author_id = c.c_author_id WHERE c.c_cup_time BETWEEN '2018-04-30' AND '2019-04-30';  
 

---