数据在本专栏的第一篇博客里
题目汇总
14.1.按要求创建表
创建表Book表,字段如下:
bid 整型,要求主键
bname 字符型,要求设置唯一键,并非空
price 浮点型,要求有默认值10
btypeID 类型编号,要求引用bookType表的id字段
已知booktype表(不用创建),字段如下:
id
name
#代码:
CREATE TABLE Book (
bid INT PRIMARY KEY,
bname VARCHAR ( 20 ) UNIQUE NOT NULL,
price FLOAT DEFAULT 10,
btypeId INT,
FOREIGN KEY ( btypeId ),
REFERENCES bookType ( id ));
14.2.开启事务,向表中插入一行数据,并结束
SET autocommit = 0;
INSERT INTO book ( bid, bname, price, btypeId )
VALUES
(
1,
'小李飞刀', 100,1)
14.3.创建视图,实现查询价格大于100的书名和类型名
CREATE VIEW myv1 AS SELECT
bname,
NAME
FROM
book b
JOIN bookType t ON b.btype = t.id
WHERE
price > 100;
14.4.修改视图,实现查询价格在90-120之间的书名和价格
CREATE
OR REPLACE VIEW myv1 AS SELECT
bname,
price
FROM
book
WHERE
price BETWEEN 90
AND 120;
14.5.删除刚才建的视图
DROP VIEW myv1;