数据库视图索引查询

1.插入

INSERT INTO`book`VALUES('5','1','2','158','1','3','4','1000','97870',NOW(),NULL);

2.查询

用户小影的借书记录

SELECT u.user_name,b.book_name,b.publish,w.borrow_time,w.return_time FROM USER u, book b, book_borrow w WHERE w.book_id = b.id AND w.user_id = u.id AND u.login_name = '小影';

 

4.视图

针对用户表user、图书表book、图书借阅表book_borrow建立一个用户借阅图书信息查询视图

CREATE OR REPLACE VIEW user_book_borrow_view

AS

SELECT u.id,u.login_name,u.user_name,b.book_name,b.publish,w.borrow_time,w.return_time。FROM USER u, book b, book_borrow w WHERE w.book_id = b.id AND w.user_id = u.id ;

 

5.索引

按book_name字段建立图书索引。索引名为：booknameindex;

CREATE INDEX booknameindex ON `book`(`book_name`)

按publish，book_name字段建立图书表复合索引。索引名为：

CREATE INDEX bookpublishnameindex ON `book`(`publish`,`book_name`)

6.更新

小刚用户的部门为运维部。

UPDATE `user` SET `dept_id`=(SELECT `id` FROM `dept` WHERE `dept_name`='运维部')

WHERE `user_name`='小刚'

7.删除

图书名称包含：“经济学”的所有图书

delete from `book` where `book_name` like '%经济学%'

删除用户小影的全部借书记录。

DELETE FROM `book_borrow` WHERE`user_id`=(SELECT `id` FROM `user` WHERE `user_name`='小影')

10.查询所有用户丢失的图书名称。

SELECT `user_name`,`book_name` FROM `book`,`book_borrow`,`user`,`book_lose`

WHERE `book_lose`.`borrow_id`=`book_borrow`.`id` AND `book_borrow`.`user_id`=`user`.id AND `book_borrow`.`book_id`=`book`.`id`