mysql题目(视图、事务)

mysql题目(视图、事务)

直接po代码和案例

#mysql题目(视图、事务)

#1.创建Book表，字段如下：
/*bid 整型 主键
bName 字符型，要求唯一并且非空
price 浮点数，要求有默认值10
bTypeId 类型编号，要求引用BookType表的id字段

已知BookType表，字段如下：
id
name

*/

CREATE TABLE BookType(
	id INT PRIMARY KEY,
	`name` VARCHAR(40) UNIQUE NOT NULL
);

CREATE TABLE Book(
	bid INT PRIMARY KEY,
	bName VARCHAR(50) UNIQUE NOT NULL,
	price FLOAT DEFAULT 10,
	bTypeId INT,
	CONSTRAINT fk_Book_bTypeId_BookType_id FOREIGN KEY(bTypeId) REFERENCES BookType(id)
	#FOREIGN KEY(bTypeId) REFERENCES BookType(id)
);
#
SELECT * FROM Book;
#
SHOW VARIABLES LIKE '%autocommit%';

#2.开启事务，向表中插入1行数据，并结束
SET autocommit=0;
START TRANSACTION;
INSERT INTO Book(bid, bName, price, bTypeId) VALUES(2, '射雕英雄传', 39.62, 10);
COMMIT;
#rollback;
#


#3.创建视图，实现查询价格大于100的书名和类型名
CREATE OR REPLACE VIEW my_book_view
AS
SELECT b.bname, bt.name FROM Book b
INNER JOIN BookType bt
ON b.bTypeId = bt.id
WHERE price > 100;
#
SELECT * FROM my_book_view;


#4.修改视图，实现查询价格在90-120之间的书名和价格
#修改视图(方式一)
CREATE OR REPLACE VIEW my_book_view
AS
SELECT bname, price FROM Book
WHERE price BETWEEN 90 AND 120;
#
SELECT * FROM my_book_view;

#修改视图(方式二)
ALTER VIEW my_book_view
AS
SELECT bname, price FROM Book
WHERE price BETWEEN 90 AND 130;
#
SELECT * FROM my_book_view;


#5.删除刚才创建的视图
DROP VIEW IF EXISTS my_book_view;
DROP VIEW my_book_view;