Mysql 练习题

最新推荐文章于 2019-10-28 21:46:37 发布

小白鼠丶

最新推荐文章于 2019-10-28 21:46:37 发布

阅读量500

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_33366229/article/details/74770704

版权

Mysql 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

mysql> create table aa(
    -> A int unsigned primary key,
    -> B int not null,
    -> C int 
    -> );
Query OK, 0 rows affected
mysql> insert into aa values(3,7,9);
mysql> insert into aa values(5,6,9),(4,8,4);

①：选出A B的字段，并按B的升序排列

mysql> select A,B from aa order by B asc;
+---+---+
| A | B |
+---+---+
| 5 | 6 |
| 3 | 7 |
| 4 | 8 |
+---+---+
3 rows in set

② 选出C的值，并按C降序排列，消除重复值

mysql> select distinct C from aa order by C desc ;
+---+
| C |
+---+
| 9 |
| 4 |
+---+
2 rows in set

=============================================

mysql> CREATE TABLE department(
  id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
  name varchar(20) not null );
insert into department (name) value('财务'),('销售'),('客服');

 CREATE TABLE employee(
  id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
  name varchar(20) not null, 
  id_department smallint unsigned not null);

mysql> insert into employee (name,id_department) value('张三',1),('李四',1),('王五',3),('赵六',3);

①：获得部门（department）编号，名称，以及每个部门员工数量

最先的想法：

mysql> select id_department,count(id_department) AS num from employee group by id_department;
+---------------+-----+
| id_department | num |
+---------------+-----+
|             1 |   2 |
|             3 |   2 |
+---------------+-----+
2 rows in set

mysql> select id_department,count(id_department) AS num from employee;
+---------------+-----+
| id_department | num |
+---------------+-----+
|             1 |   4 |
+---------------+-----+
1 row in set


mysql> select d.*,e.num from department as d left join
 (select id_department,count(*) as num from employee group 
 by id_department) as e on e.id_department =d.id;
+----+------+------+
| id | name | num  |
+----+------+------+
|  1 | 财务 |    2 |
|  2 | 销售 | NULL |
|  3 | 客服 |    2 |
+----+------+------+
3 rows in set

mysql> select id_department,count(*) as num from employee group by id_department;
+---------------+-----+
| id_department | num |
+---------------+-----+
|             1 |   2 |
|             3 |   2 |
+---------------+-----+
2 rows in set

思考：如何把字段num列的null值换为0？

====================================

无限级分类表

：连接

DROP TABLE IF EXISTS `tdb_goods_types`;
CREATE TABLE `tdb_goods_types` (
  `type_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `type_name` varchar(20) NOT NULL,
  `parent_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `child_count` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tdb_goods_types
-- ----------------------------
INSERT INTO `tdb_goods_types` VALUES ('1', '家用电器', '0', '2');
INSERT INTO `tdb_goods_types` VALUES ('2', '电脑、办公', '0', '2');
INSERT INTO `tdb_goods_types` VALUES ('3', '大家电', '1', '2');
INSERT INTO `tdb_goods_types` VALUES ('4', '生活电器', '1', '2');
INSERT INTO `tdb_goods_types` VALUES ('5', '平板电视', '3', '0');
INSERT INTO `tdb_goods_types` VALUES ('6', '空调', '3', '0');
INSERT INTO `tdb_goods_types` VALUES ('7', '电风扇', '4', '0');
INSERT INTO `tdb_goods_types` VALUES ('8', '饮水机', '4', '0');
INSERT INTO `tdb_goods_types` VALUES ('9', '电脑整机', '2', '3');
INSERT INTO `tdb_goods_types` VALUES ('10', '电脑配件', '2', '2');
INSERT INTO `tdb_goods_types` VALUES ('11', '笔记本', '9', '0');
INSERT INTO `tdb_goods_types` VALUES ('12', '超级本', '9', '0');
INSERT INTO `tdb_goods_types` VALUES ('13', '游戏本', '9', '0');
INSERT INTO `tdb_goods_types` VALUES ('14', 'CPU', '10', '0');
INSERT INTO `tdb_goods_types` VALUES ('15', '主机', '10', '0');

① 获取父类以及其子类

mysql> select p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT
    -> JOIN tdb_goods_types AS s on p.type_id = s.parent_id;
+---------+------------+-----------+
| type_id | type_name  | type_name |
+---------+------------+-----------+
|       1 | 家用电器   | 大家电    |
|       1 | 家用电器   | 生活电器  |
|       2 | 电脑、办公 | 电脑整机  |
|       2 | 电脑、办公 | 电脑配件  |
|       3 | 大家电     | 平板电视  |
|       3 | 大家电     | 空调      |
|       4 | 生活电器   | 电风扇    |
|       4 | 生活电器   | 饮水机    |
|       5 | 平板电视   | NULL      |
|       6 | 空调       | NULL      |
|       7 | 电风扇     | NULL      |
|       8 | 饮水机     | NULL      |
|       9 | 电脑整机   | 笔记本    |
|       9 | 电脑整机   | 超级本    |
|       9 | 电脑整机   | 游戏本    |
|      10 | 电脑配件   | CPU       |
|      10 | 电脑配件   | 主机      |
|      11 | 笔记本     | NULL      |
|      12 | 超级本     | NULL      |
|      13 | 游戏本     | NULL      |
|      14 | CPU        | NULL      |
|      15 | 主机       | NULL      |
+---------+------------+-----------+

②在①的基础上获取父类的子类个数

mysql> select p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT
     JOIN tdb_goods_types AS s on p.type_id = s.parent_id group by p.type_name;
+---------+------------+-----------+
| type_id | type_name  | type_name |
+---------+------------+-----------+
|      14 | CPU        | NULL      |
|      15 | 主机       | NULL      |
|       3 | 大家电     | 平板电视  |
|       1 | 家用电器   | 大家电    |
|       5 | 平板电视   | NULL      |
|      13 | 游戏本     | NULL      |
|       4 | 生活电器   | 电风扇    |
|       2 | 电脑、办公 | 电脑整机  |
|       9 | 电脑整机   | 笔记本    |
|      10 | 电脑配件   | CPU       |
|       7 | 电风扇     | NULL      |
|       6 | 空调       | NULL      |
|      11 | 笔记本     | NULL      |
|      12 | 超级本     | NULL      |
|       8 | 饮水机     | NULL      |
+---------+------------+-----------+
15 rows in set

mysql> select p.type_id,p.type_name,count(s.type_name)  FROM tdb_goods_types AS p LEFT
     JOIN tdb_goods_types AS s on p.type_id = s.parent_id group by p.type_name order by p.type_id;
+---------+------------+--------------------+
| type_id | type_name  | count(s.type_name) |
+---------+------------+--------------------+
|       1 | 家用电器   |                  2 |
|       2 | 电脑、办公 |                  2 |
|       3 | 大家电     |                  2 |
|       4 | 生活电器   |                  2 |
|       5 | 平板电视   |                  0 |
|       6 | 空调       |                  0 |
|       7 | 电风扇     |                  0 |
|       8 | 饮水机     |                  0 |
|       9 | 电脑整机   |                  3 |
|      10 | 电脑配件   |                  2 |
|      11 | 笔记本     |                  0 |
|      12 | 超级本     |                  0 |
|      13 | 游戏本     |                  0 |
|      14 | CPU        |                  0 |
|      15 | 主机       |                  0 |
+---------+------------+--------------------+
15 rows in set

③获取子类及其父类

mysql> select s.type_id,s.type_name,p.type_name FROM
 tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p on s.parent_id = p.type_id;
+---------+------------+------------+
| type_id | type_name  | type_name  |
+---------+------------+------------+
|       1 | 家用电器   | NULL       |
|       2 | 电脑、办公 | NULL       |
|       3 | 大家电     | 家用电器   |
|       4 | 生活电器   | 家用电器   |
|       5 | 平板电视   | 大家电     |
|       6 | 空调       | 大家电     |
|       7 | 电风扇     | 生活电器   |
|       8 | 饮水机     | 生活电器   |
|       9 | 电脑整机   | 电脑、办公 |
|      10 | 电脑配件   | 电脑、办公 |
|      11 | 笔记本     | 电脑整机   |
|      12 | 超级本     | 电脑整机   |
|      13 | 游戏本     | 电脑整机   |
|      14 | CPU        | 电脑配件   |
|      15 | 主机       | 电脑配件   |
+---------+------------+------------+
15 rows in set

==========================================

删除表中多余项：

DROP TABLE IF EXISTS `tdb_goods`;
CREATE TABLE `tdb_goods` (
  `goods_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(150) NOT NULL,
  `cate_id` smallint(5) unsigned NOT NULL,
  `brand_id` smallint(5) unsigned NOT NULL,
  `goods_price` decimal(15,3) unsigned NOT NULL DEFAULT '0.000',
  `is_show` tinyint(1) NOT NULL DEFAULT '1',
  `is_saleoff` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tdb_goods
-- ----------------------------
INSERT INTO `tdb_goods` VALUES ('1', 'R510VC 15.6英寸笔记本', '5', '2', '3399.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('2', 'Y400N 14.0英寸笔记本电脑', '5', '7', '4899.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('3', 'G150TH 15.6英寸游戏本', '4', '9', '8499.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('4', 'X550CC 15.6英寸笔记本', '5', '2', '2799.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('5', 'X240(20ALA0EYCD) 12.5英寸超极本', '7', '7', '4999.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('6', 'U330P 13.3英寸超极本', '7', '7', '4299.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('7', 'SVP13226SCB 13.3英寸触控超极本', '7', '6', '7999.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('8', 'iPad mini MD531CH/A 7.9英寸平板电脑', '2', '8', '1998.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('9', 'iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）', '2', '8', '3388.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('10', ' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）', '2', '8', '2788.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('11', 'IdeaCentre C340 20英寸一体电脑 ', '1', '7', '3499.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('12', 'Vostro 3800-R1206 台式电脑', '1', '5', '2899.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('13', 'iMac ME086CH/A 21.5英寸一体电脑', '1', '8', '9188.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('14', 'AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）', '1', '3', '3699.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('15', 'Z220SFF F4F06PA工作站', '3', '4', '4288.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('16', 'PowerEdge T110 II服务器', '3', '5', '5388.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('17', 'Mac Pro MD878CH/A 专业级台式电脑', '3', '8', '28888.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('18', ' HMZ-T3W 头戴显示设备', '6', '6', '6999.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('19', '商务双肩背包', '6', '6', '99.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('20', 'X3250 M4机架式服务器 2583i14', '3', '1', '6888.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('23', ' LaserJet Pro P1606dn 黑白激光打印机', '12', '4', '1849.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('24', 'X3250 M4机架式服务器 2583i14', '3', '1', '6888.000', '1', '0');
INSERT INTO `tdb_goods` VALUES ('25', ' LaserJet Pro P1606dn 黑白激光打印机', '12', '4', '1849.000', '1', '0');

①删除表中多余项保留id最大的那一项

mysql> delete t1 from tdb_goods AS t1 LEFT JOIN
 (select *  from tdb_goods group by goods_name  having count(goods_name)>=2 order by goods_id) AS t2 
  on t1.goods_name =t2.goods_name where t1.goods_id >t2.goods_id;
Query OK, 2 rows affected

小白鼠丶

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql 练习题

mysql> create table aa( -> A int unsigned primary key, -> B int not null, -> C int -> );Query OK, 0 rows affectedmysql> insert into aa values(3,7,9);mysql> insert into aa values(5,6
复制链接

扫一扫