实验 2 SQL 数据定义和操作
张海
3130000923
实验目的
1. 掌握关系数据库语言 SQL 的使用。
2. 使所有的 SQL 作业都能上机通过。实验平台:
实验平台
1. 数据库管理系统：MariaDB
实验内容
1. 建立数据库。
2. 数据定义 3. 数据更新
02.sql
CREATE DATABASE library;
USE library
CREATE TABLE book (
bno char(8),
category char(10), title varchar(40), press varchar(30), year int,
author varchar(20), price decimal(7, 2), total int,
stock int);
CREATE INDEX book_bno ON book (bno);
CREATE INDEX book_title ON book (title);
DELIMITER //
CREATE PROCEDURE insert_book() BEGIN DECLARE i int DEFAULT 1;
REPEAT
INSERT INTO book VALUES (
SUBSTRING(MD5(i), 1, 8),
SUBSTRING(MD5(MD5(i)), 1, 10), SUBSTRING(MD5(MD5(MD5(i))), 1, 40),
SUBSTRING(MD5(MD5(MD5(MD5(i)))) , 1, 30),
MD5(MD5(MD5(MD5(MD5(i))))), SUBSTRING(MD5(MD5(MD5(MD5(MD5(M D5(i)))))), 1, 20),
MD5(MD5(MD5(MD5(MD5(MD5(MD5(i)) ))))),
MD5(MD5(MD5(MD5(MD5(MD5(MD5(MD5 (i)))))))),
MD5(MD5(MD5(MD5(MD5(MD5(MD5(MD5 (MD5(i))))))))));
SET i = i + 1;
UNTIL i > 100 END REPEAT;
END //
DELIMITER ;
CALL insert_book();
DROP PROCEDURE insert_book;
CREATE TABLE card ( cno char(7),
name varchar(10),
department varchar(40), type char(1),
CHECK (type IN ('T','G','U','O')));
CREATE INDEX card_cno ON card (cno);
CREATE INDEX card_name ON card (name);
DELIMITER //
CREATE PROCEDURE insert_card() BEGIN DECLARE i int DEFAULT 1;
REPEAT
INSERT INTO card VALUES (
SUBSTRING(MD5(i), 1, 7),
SUBSTRING(MD5(MD5(i)), 1, 10), SUBSTRING(MD5(MD5(MD5(i))), 1, 40),
if (MOD(i, 4) = 0, 'T', if (MOD (i, 4) = 1, 'G', if (MOD(i, 4) = 2, 'U', 'O')) ));
SET i = i + 1;
UNTIL i > 100 END REPEAT;
END //
DELIMITER ;
CALL insert_card();
DROP PROCEDURE insert_card;
CREATE TABLE borrow ( cno char(7), bno char(8),
borrow_date date, return_date date);
CREATE INDEX borrow_cno ON borrow (cno);
CREATE INDEX borrow_bno ON borrow (bno);
DELIMITER //
CREATE PROCEDURE insert_borrow() BEGIN DECLARE i int DEFAULT 1;
REPEAT
INSERT INTO borrow VALUES ( SUBSTRING(MD5(i), 1, 7), SUBSTRING(MD5(i), 1, 8), MD5(MD5(i)),
MD5(MD5(MD5(i))));
SET i = i + 1;
UNTIL i > 100 END REPEAT;
END //
DELIMITER ;
CALL insert_borrow();
DROP PROCEDURE insert_borrow;
DELETE FROM book WHERE bno LIKE '%c4%';
DELETE FROM card WHERE cno LIKE '%c4%';
DELETE FROM borrow WHERE cno LIKE '%c4%';
UPDATE book SET bno='abcdefgh' WHERE bno LIKE '%c7%';
UPDATE card SET cno='abcdefg' WHERE cno LIKE '
%c7%';
UPDATE borrow SET bno='abcdefgh' WHERE bno LIK E '%c7%';
UPDATE borrow SET cno='abcdefg' WHERE cno LIKE '%c7%';
MariaDB [none]> source 02.sql
4. 数据查询
SELECT COUNT(*) FROM book;
SELECT SUM(price) FROM book;
SELECT COUNT(*) FROM book WHERE total > 10;
SELECT * FROM book WHERE price = (SELECT MAX(p rice) FROM book);
SELECT * FROM book NATURAL JOIN borrow;
5. 视图操作
CREATE VIEW book_borrow_card AS SELECT * FROM book NATURAL JOIN borrow NATURAL JOIN card;
SELECT * FROM book_borrow_card;
UPDATE book_borrow_card SET cno = 'qwertyu' WH ERE cno LIKE '%c4%';
