编辑代码

-- 创建数据库
CREATE DATABASE IF NOT EXISTS bms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE bms;

-- 创建用户表
CREATE TABLE IF NOT EXISTS user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    state TINYINT DEFAULT 0 COMMENT '0-正常 1-禁用'
);

-- 创建图书表
CREATE TABLE IF NOT EXISTS book (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    upload_time DATETIME NOT NULL,
    state TINYINT DEFAULT 0 COMMENT '0-可借阅 1-已借出 2-下架',
    borrower_id INT NULL,
    borrow_time DATETIME NULL,
    FOREIGN KEY (borrower_id) REFERENCES user(id)
);

-- 插入用户数据
INSERT INTO user (name, state) VALUES 
('张三', 0),
('李四', 0);

-- 插入图书数据
INSERT INTO book (name, price, upload_time, state) VALUES
('Java基础入门（第3版）', 59.00, CURRENT_TIMESTAMP, 0),
('三国演义', 69.00, CURRENT_TIMESTAMP, 2),
('MySQL数据库入门', 40.00, CURRENT_TIMESTAMP, 0),
('JavaWeb程序开发入门', 49.00, CURRENT_TIMESTAMP, 0),
('西游记', 59.00, CURRENT_TIMESTAMP, 2),
('红楼梦', 33.00, CURRENT_TIMESTAMP, 2),
('水浒传', 66.66, CURRENT_TIMESTAMP, 2);

-- 更新数据操作
UPDATE book SET state = 0 WHERE name = '西游记';
UPDATE book SET price = 66.00 WHERE name = '水浒传';
UPDATE book SET price = price * 0.9;  -- 所有图书打9折
DELETE FROM book WHERE name = '红楼梦';

-- 记录借阅信息
UPDATE book SET 
    borrower_id = (SELECT id FROM user WHERE name = '张三' LIMIT 1),
    borrow_time = CURRENT_TIMESTAMP,
    state = 1
WHERE name = 'MySQL数据库入门';

ALTER TABLE book ADD CONSTRAINT FK_ID FOREIGN KEY(borrower_id)
REFERENCES user (id);

CREATE TABLE dept_index(
	id INT,
	deptno  INT ,
	dname   VARCHAR(20),
	introduction VARCHAR(200),
	address GEOMETRY NOT NULL SRID 4326,
	PRIMARY KEY(id),        -- 创建主键索引
	UNIQUE INDEX (deptno),  -- 创建唯一性索引
	INDEX (dname),          -- 创建普通索引
	FULLTEXT (introduction),-- 创建全文索引
	SPATIAL INDEX (address) -- 创建空间索引
) ;
CREATE TABLE index_multi(
    id INT not null,
    name VARCHAR(20) NOT NULL,
    score FLOAT,
    INDEX multi(id,name)
);
SHOW CREATE table index_multi;
CREATE TABLE dept_index02(
id INT,
deptno  INT ,
dname   VARCHAR(20),
introduction VARCHAR(200)
);
CREATE UNIQUE INDEX unique_id ON dept_index02(id);
-- SHOW CREATE TABLE dept_index02\G
CREATE INDEX multi_index ON dept_index02(deptno,dname);
ALTER TABLE book ADD INDEX index_bookname(name);
ALTER TABLE book ADD INDEX index_bookname_state(name(20),state(1));
ALTER TABLE book DROP INDEX index_bookname_state;
START TRANSACTION;
DELETE FROM book;
INSERT INTO book(name,price,upload_time,borrower_id,borrow_time，state)
VALUES
('Java基础入门',59.00,CURRENT_TIMESTAMP,NULL,NULL,0),
('三国演义',69.00,CURRENT_TIMESTAMP,NULL,NULL,0),
('MySQL数据库入门',40.00,CURRENT_TIMESTAMP,1,'2021-08-06 11:16:05',1),
('JavaWeb程序开发入门',49.00,CURRENT_TIMESTAMP,NULL,NULL,0),
('西游记',59.00,CURRENT_TIMESTAMP,NULL,NULL,0),
('水浒传',66.66,CURRENT_TIMESTAMP,NULL,NULL,0),
('唐诗三百首',39.00,CURRENT_TIMESTAMP,NULL,NULL,0),
('Python数据可视化',49.80,CURRENT_TIMESTAMP,NULL,NULL,0);
SELECT * FROM book;
COMMIT;
-- SHOW CREATE TABLE book;
-- SHOW book;
-- SELECT name, price FROM book WHERE price>50;
-- SELECT name FROM book WHERE name LIKE '%入门';
-- SELECT name,price FROM book WHERE name IN('西游记','红楼梦') ;
-- SELECT u.id,u.name borrower,b.name bookname,b.borrow_time FROM book b,user u
-- WHERE b.borrower_id=u.id AND u.name='张三';
-- SELECT name,price FROM book
-- WHERE price>(SELECT price FROM book WHERE name='西游记');
--  SELECT name,price FROM book
-- WHERE price<(SELECT AVG(price) FROM book);
-- SELECT name,price,state FROM book
-- WHERE state=(SELECT state FROM book WHERE name='三国演义');
-- SELECT name,price,state FROM book
-- WHERE state=1 AND price<ANY(SELECT price FROM book WHERE state=0);
-- SELECT name,price,state FROM book
-- WHERE price>ALL(SELECT price FROM book WHERE state=1);