Mysql 2

#1 设置SQL编码格式
SET NAMES UTF8;

#2 删除数据库dangdang，如果存在的话
DROP DATABASE IF EXISTS dangdang;

#3 创建数据库dangdang，指定存储数据所用的编码
CREATE DATABASE dangdang CHARSET=UTF8;

#4 进入数据库
USE dangdang;

#5 创建表 dd_category(cid,cname,count)
CREATE TABLE dd_category(
cid INT PRIMARY KEY,
cname VARCHAR(8),
count INT
);

#6 添加三行记录，三个书籍分类（10/20/30)
INSERT INTO dd_category VALUES
(10,'计算机','3'),
(20,'历史','3'),
(30,'美食','3');

#7 查询出所有的书籍分类
SELECT * FROM dd_category;

#8 创建表 dd_book(bid,title,pic,price,pubDate,isOnsale,categoryId)
CREATE TABLE dd_book(
bid INT PRIMARY KEY AUTO_INCREMENT, #主键
title VARCHAR(64),
pic VARCHAR(32),
price FLOAT(10,2),
pubDate BIGINT,
isOnsale BOOLEAN,
categoryId INT #外键
);

#9 为每种分类添加两三条记录
INSERT INTO dd_book VALUES
(NULL,'Java编程指南','img/1.jpg','35.5','1234567897654','1','10'),
(NULL,'JS编程指南','img/1.jpg','35.5','1234567897654','1','10'),
(NULL,'JS编程指南','img/1.jpg','35.5','1234567897654','1','10'),
(NULL,'三国志','img/1.jpg','35.5','1234567897654','1','20'),
(NULL,'三国志','img/1.jpg','35.5','1234567897654','1','20'),
(NULL,'三国志','img/1.jpg','35.5','1234567897654','1','20'),
(NULL,'美食','img/1.jpg','35.5','1234567897654','0','30'),
(NULL,'美食','img/1.jpg','35.5','1234567897654','0','30'),
(NULL,'美食','img/1.jpg','35.5','1234567897654','0','30');

#10 查询出所有的书籍
SELECT * FROM dd_book;

#11 查询所有计算机类的书籍;
SELECT * FROM dd_book
WHERE categoryId=(
SELECT cid FROM dd_category WHERE cname='计算机'
);

#11 删除10号分类及其下所有的书籍
DELETE FROM dd_category WHERE cid=10;
DELETE FROM dd_book WHERE categoryId=10;

#12 删除编号为6的书籍，需要修改对应分类的书籍数量
UPDATE dd_category SET count=count-1
WHERE cid=(
SELECT categoryId FROM dd_book
WHERE bid=6
);
DELETE FROM dd_book WHERE bid=(6);