Mysql 练习题

来源：互联网发布：淘宝关键词设置顺序编辑：程序博客网时间：2024/05/15 15:42

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,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 setmysql> 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 setmysql> 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 setmysql> 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 0