一次Mysql作业和练习

案例：

!!!

!!!

!!!

1） 指定所有字段名称插入第一条记录

2）不指定字段名称插入第二记录

3）同时插入多条记录（剩下的所有记录）

删除表格

drop TABLE books;

创建表格books

CREATE TABLE books (
	b_id INT ( 11 ),-- 书编号
	b_name VARCHAR ( 50 ),-- 书名
	authors VARCHAR(100),-- 作者
	price FLOAT,-- 价格
	pubdate YEAR,-- 出版日期
	note VARCHAR ( 100 ),-- 说明
	num INT ( 11 )-- 库存
	
);

增加b_id主键约束

ALTER TABLE books MODIFY b_id int PRIMARY KEY;

设置b_id为自增加

ALTER TABLE books MODIFY b_id int AUTO_INCREMENT ;

设置书名为非空

ALTER TABLE books  MODIFY b_name VARCHAR(50) NOT null;

设置作者为非空

ALTER TABLE books  MODIFY authors VARCHAR(100) NOT null;

-- 设置价格为非空

ALTER TABLE books  MODIFY price FLOAT NOT null;

-- 设置出版日期为非空

ALTER TABLE books  MODIFY pubdate YEAR NOT null;

-- 设置库存为非空

ALTER TABLE books  MODIFY num int NOT null;

-- 4.1指定所有字段名称插入第一条记录

INSERT INTO books(b_id,b_name,authors,price,pubdate,note,num) VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);

-- 4.2不指定字段名称插入第二记录

INSERT INTO books VALUES(2,'EmmaT','Jane lura',35,1993,'joke',22);

-- 4.3 同时插入多条记录（剩下的所有记录）

INSERT INTO books VALUES(3,'Story of Jane','Jane Tim',40,2001,'novel',0),(4,'Lovey Day','George Byron',20,2005,'novel',30),(5,'Old land','Honore Blade',30,2010,'law',0),(6,'The Battle','Upton Sara',30,1999,'medicine',40),(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);

-- 5、统计书名中包含a字母的书

SELECT b_name FROM books WHERE  b_name  LIKE'%a%';

-- 6、统计书名中包含a字母的书的数量和库存总量

SELECT count(b_name)'书的数量',sum(num)'库存总量'FROM books WHERE  b_name  LIKE'%a%';

-- 7、找出“novel”类型的书，按照价格降序排列

SELECT b_name,price FROM books WHERE note LIKE 'novel' ORDER BY price DESC;

-- 8、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列

SELECT * FROM books ORDER BY num DESC,note ASC;

-- 9、按照note分类统计书的数量

SELECT note,count(b_name)'书的数量' FROM books GROUP BY note ;

-- 10、按照note分类统计书的库存量，显示库存量超过30本的

SELECT b_name,note,sum(num)'库存量' FROM books  GROUP BY note  HAVING sum(num)>30;

-- 11、查询所有图书，每页显示5本，显示第二页

SELECT * FROM books LIMIT 5,5;

-- 12、按照note分类统计书的库存量，现在库存量最多的

SELECT note,max(num)'库存量' FROM books  GROUP BY note  HAVING max(num)>30;

-- 13、查询书名达到10个字符的书，不包括里面的空格

SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(b_name,' ',''))>=10;

-- 14 查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话

SELECT b_name, note, CASE note 
 WHEN 'novel' THEN '小说'
 WHEN 'law' THEN '法律'
 WHEN 'medicine' THEN '医药'
 WHEN 'cartoon' THEN '卡通'
 WHEN 'joke' THEN '笑话'
 END AS "类型"
FROM books;

-- 15查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货

SELECT b_name,num,CASE 
WHEN num>30 THEN '滞销'
WHEN num>0 AND num <10 then '畅销'
WHEN num=0 THEN '无货'
ELSE '正常'
END 
FROM books;

-- 16、统计每一种note的库存量，并合计总量

SELECT count(num) FROM books ORDER BY note;

-- 17、统计每一种note的数量，并合计总量

-- 18、统计库存量前三名的图书

-- 19、找出最早出版的一本书

-- 20、找出novel中最高的一本书

-- 21、找出书名中字数最多的一本书，不含空格