MySQL学习笔记——20170812

练习

建库建表

CREATE DATABASE bookstoreDB;
USE bookstoreDB;

CREATE TABLE t_booktype(
	type_id int PRIMARY KEY NOT NULL,
	type_name VARCHAR(20) UNIQUE NOT NULL
) ENGINE=InnoDB;

CREATE TABLE t_book(
	book_id int PRIMARY KEY NOT NULL,
	book_name VARCHAR(40) UNIQUE NOT NULL,
	type_id int NOT NULL,
	FOREIGN KEY (type_id) REFERENCES t_booktype(type_id),
	author char(10),
	price DECIMAL(5,1) NOT NULL,
	publisher VARCHAR(30) NOT NULL,
	page int,
	publish_date DATE NOT NULL
) ENGINE=InnoDB;

CREATE TABLE t_users(
	user_id int PRIMARY KEY  NOT NULL,
	password char(8) NOT NULL,
	name char(10) NOT NULL,
	email char(20) NOT NULL,
	telephone char(15) NOT NULL,
	address VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE t_order(
	order_id int PRIMARY KEY NOT NULL,
	user_id int NOT NULL,
	FOREIGN KEY (user_id) REFERENCES t_users(user_id),
	ordertime DATE NOT NULL,
	sendtime DATE,
	consignment tinyint NOT NULL DEFAULT '0'
) ENGINE=InnoDB;

CREATE TABLE t_orderdetail(
	book_id int NOT NULL,
	FOREIGN KEY (book_id) REFERENCES t_book(book_id),
	book_number int  NOT NULL,
	order_id int NOT NULL,
	FOREIGN KEY (order_id) REFERENCES t_order(order_id)
) ENGINE=InnoDB;

插入数据

INSERT INTO t_booktype 
VALUES 
	(1,'计算机类'),
	(2,'经济类'),
	(3,'管理类'),
	(4,'外语类');
	
INSERT INTO t_book
VALUES
	(100001,'计算机基础',1,'李肖',17,'清华大学出版社',250,'2008-9-1'),
	(100002,'程序设计方法学',1,'吴永辉',25,'北大出版社',450,'2009-9-0'),
	(200003,'经济学',2,'王林',16,'社会科学出版社',300,'2003-4-0'),
	(100114,'数据库系统',1,'夏以琳',12,'复旦大学出版社',450,'2002-7-0'),
	(300021,'企业管理',3,'许戈福',21,'江西出版社',500,'2007-12-0'),
	(300006,'管理模式',3,'张千',14,'社会科学出版社',350,'2008-7-0'),
	(400001,'大学英语',4,'肖红',35,'上海外语出版社',550,'2006-5-0'),
	(400002,'英语翻译技巧',4,'周阳',16,'华东师范大学出版社',350,'2008-9-0');
	
INSERT INTO t_users
VALUES
	(230001,12345678,'张丹','acbd@hotmail.com',61234901,'通江路230号'),
	(367009,12345678,'刘红','Ssss@hotmail.com',62235678,'中山北路3663号'),
	(128901,12345678,'赵明','qwer@hotmail.com',61111111,'东川路1000号'),
	(330002,12345678,'李芳','aaaaa@hotmail.com',6222222,'怒江路2300号');
	
INSERT INTO t_order
VALUES
	(2009001189,230001,'2009-11-20','2009-11-24',1),
	(2009001190,330002,'2009-11-25','2009-11-28',1),
	(2009001191,128901,'2009-11-30',NULL,1),
	(2009001192,367009,'2009-12-05',NULL,1);
	
INSERT INTO t_orderdetail
VALUES
	(100001,2,2009001189),
	(100002,1,2009001189),
	(400001,2,2009001189),
	(200003,1,2009001190),
	(300021,2,2009001190),
	(400002,1,2009001191),
	(300006,2,2009001191),
	(100001,1,2009001192);

查询

-- 3
SELECT
	book_id,
	book_name,
	publisher,
	(SELECT AVG(price) FROM t_book) '平均价',
	price - (SELECT AVG(price) FROM t_book) '平均单价之差'
FROM
	t_book;
	
-- 4
SELECT * FROM t_book
WHERE price>(SELECT AVG(price) FROM t_book);

-- 5
SELECT book_name,publisher,page,price 
FROM t_book 
WHERE 
	page>=400 
	&& page<=600 
	&& price>(SELECT AVG(price) FROM t_book);

-- 6
SELECT book_id,book_name,author,publish_date
FROM t_book
WHERE 
	YEAR(publish_date)>=2007 
	&& YEAR(publish_date)<=2009 
	&& book_id>=300000 
	&& book_id<=500000;

-- 7
SELECT book_id,book_name,author,price,page
FROM t_book
WHERE 
	book_name LIKE '%学%' 
	&& page>(SELECT AVG(page) FROM t_book);

-- 8
SELECT book_name,author,price,publisher,publish_date
FROM t_book
WHERE 
	YEAR(publish_date)<2005 
	&& price<(SELECT AVG(price) FROM t_book) 
	&& publisher='社会科学出版社';