MySql综合案例练习<1>

• 题目一
• 题目二
• 题目三
• 题目四
• 题目五
• 题目六
• 题目七
• 题目八
• 题目九
• 题目十
• 题目十一
• 题目十二
• 题目十三
• 题目十四
• 题目十五
• 题目十六
• 题目十七
• 题目十八
• 题目十九

  题目一

  创建数据库test01_library

  创建表 books，表结构如下：

  

  CREATE DATABASE  test01_library
  CREATE TABLE books(
  id INT,
  name VARCHAR(50),
  `authors` VARCHAR(100) ,
  price FLOAT,
  pubdate YEAR ,
  note VARCHAR(100),
  num INT
  );
  

  题目二

  向books表中插入记录

  (1）不指定字段名称，插入第一条记录

  (2)指定所有字段名称，插入第二记录

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

  这里使用INSERT INTO VALUES语句来进行信息的添加

  #(1)
  INSERT INTO books
  VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);
  #(2)
  INSERT INTO books (id,name,`authors`,price,pubdate,note,num)
  VALUES(2,'EmmaT','Jane lura',35,1993,'Joke',22);
  #(3)
  INSERT INTO books (id,name,`authors`,price,pubdate,note,num) 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);
  

  题目三

  将小说类型(novel)的书的价格都增加5

  这里很明显要更新表中列的信息，我们采用UPDATE SET 语句

  UPDATE  books
  SET price = price + 5
  WHERE note = "novel"
  

  题目四

  将名称为EmmaT的书的价格改为40，并将说明改为drama。

  UPDATE books 
  SET price=40,note='drama' 
  WHERE name='EmmaT';
  

  题目五

  删除库存为0的记录。

  删除，我们采用 DELETE FROM语句

  DELETE FROM books  WHERE num = 0;
  

  题目六

  统计书名中包含a字母的书

  这题要查找包含字母a 的书, 我们使用LIKE语句进行查找

  包含 a, 可用正则表达式 “%a%” 来进行

  SELECT * FROM books  WHERE  name LIKE "%a%"
  

  题目七

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

  统计数量可用聚合函数**,COUNT函数**

  统计总量，可用SUM函数

  SELECT COUNT(*),SUM(num) FROM books WHERE name LIKE '%a%';
  

  题目八

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

  排序可用ORDER BY语句

  降序采用 DESC

  升序采用 ASC

  SELECT * 
  FROM books  WHERE note = "novel"
  ORDER BY price DESC
  

  题目九

  按照note分类统计书的数量

  实现分类的话，

  我们采用 GROUP BY 语句

  SELECT note
  FROM books GROUP BY  note
  

  题目十

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

  每页显示多少本，显示第几页

  我们通常采用LIMIT语句去实现

  SELECT * FROM books LIMIT 5,5;
  

  题目十一

  按照note分类统计书的库存量，显示库存量最多的

  SELECT note,SUM(num) sum_num 
  FROM books 
  GROUP BY note 
  ORDER BY sum_num DESC 
  LIMIT 0,1;
  

  题目十二

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

  注意这里，要查询10个字符的，但是又不算里面的空格

  我们可以把空格删了在查询,可以采用replace函数

  REPLACE 有三个参数，

  第一个参数是字符串，

  第二个参数是字符串中要被替换的部分，

  第三个参数是替换字符串的

  计算字符串长度，可以采用CHAR_LENGTH函数

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

  

  题目十三

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

  注意: 在原来的表中是没有书名和类型这两个名字的，所以我们要使用AS语句，取别名

  当note值为novel 的时候，显示小说，是存在C语言中类似if语句的关系

  我们可以用 CASE A WHEN B THEN C语句来表示 —>(if A == B {C})

  注意：以END结束

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

  

  别名一般都是作为第一行，后面END AS “类型” 这个别名，是前面THEN语句取的

  当有多条语句，CASE条件相同的话，只写一个CASE就行

  题目十四

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

  SELECT name, num, CASE 
  WHEN num > 30 THEN '滞销'
  WHEN num  > 0 AND num < 10 	
  THEN '畅销' WHEN num = 0 THEN '无货'
  END AS "库存状态"
  FROM books
  

  

  题目十五

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

  当统计每一种note的数量的时候，我们可以使用SUM聚合函数，

  不过需要注意的时候，当聚合函数和非聚合函数同时出现的时候，

  我们应该使用GROUP BY

  当要合计总量的时候，

  我们需要在GROUP BY 后面使用 WITH ROLLUP

  SELECT note AS "库存量", SUM(num) 
  FROM books
  GROUP BY note
  WITH ROLLUP
  

  

  当然这里加了个判断是否为NULL ，修饰了一下

  SELECT IFNULL(note, "合计库存量") AS note , SUM(num) 
  FROM books
  GROUP BY note
  WITH ROLLUP
  

  

  题目十六

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

  统计数量，这里采用COUNT聚合函数，

  合计总量自然是使用WITH ROLLUP

  SELECT IFNULL(note, "合计总数") AS "数量", COUNT(*)
  FROM books
  GROUP BY note
  WITH ROLLUP
  

  

  题目十七

  统计库存量前三名的图书

  显示前三名，按照降序排列 (ORDER BY )

  然后采用分页操作 （LIMIT）

  SELECT * 
  FROM books
  ORDER BY num DESC
  LIMIT 0, 3
  

  

  题目十八

  找出最早出版(pubdate) 的一本书

  SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1;
  

  题目十九

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

  这里同样，注意：替换空格的方法(REPLACE 函数 （3个参数）)

  SELECT *
  FROM books
  ORDER BY CHAR_LENGTH(REPLACE(name, " ", "")) DESC 
  LIMIT 0, 1;