一、创建数据
MySQL数据库默认创建的test数据库下实践
/*创建两张表 users 和 products*/
use test;
CREATE TABLE `test`.`users`( `user_id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR(30), PRIMARY KEY (`user_id`) );
CREATE TABLE `test`.`products`( `P_id` INT NOT NULL AUTO_INCREMENT, `user_id` INT, `p_name` VARCHAR(40), PRIMARY KEY (`P_id`) );
/**插入users表数据**/
INSERT INTO `test`.`users` ( `username`) VALUES ('user1');
INSERT INTO `test`.`users` ( `username`) VALUES ('user2');
INSERT INTO `test`.`users` ( `username`) VALUES ('user3');
INSERT INTO `test`.`users` ( `username`) VALUES ('user4');
INSERT INTO `test`.`users` ( `username`) VALUES ('user5');
INSERT INTO `test`.`users` ( `username`) VALUES ('user6');
INSERT INTO `test`.`users` ( `username`) VALUES ('user7');
INSERT INTO `test`.`users` ( `username`) VALUES ('user8');
INSERT INTO `test`.`users` ( `username`) VALUES ('user9');
INSERT INTO `test`.`users` ( `username`) VALUES ('user10');
INSERT INTO `test`.`users` ( `username`) VALUES ('user11');
INSERT INTO `test`.`users` ( `username`) VALUES ('user12');
INSERT INTO `test`.`users` ( `username`) VALUES ('user13');
/*插入products表数据*/
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('1', 'p1');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('2', 'p2');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('3', 'p3');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('4', 'p4');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('5', 'p5');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('6', 'p6');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('7', 'p7');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('8', 'p8');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('9', 'p9');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('10', 'p10');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('11', 'p11');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('12', 'p12');
INSERT INTO `test`.`products` (`user_id`, `p_name`) VALUES ('13', 'p13');
二、查询语句
1、查询产品最多的五个用户名,以及产品数目
/*查出每个产品对应的用户名的记录*/
SELECT p_id,username FROM users u , products p WHERE u.`user_id` = p.user_id;
/*将查询出来的结果创建一个视图p_u_view,存储到数据库中*/
CREATE VIEW p_u_view AS (SELECT username,p_id FROM users u , products p WHERE u.`user_id` = p.user_id)
/*从视图中查询出产品最多的前三名用户名以及产品数量*/
SELECT username,COUNT(username) pcount FROM p_u_view
GROUP BY username
ORDER BY pcount DESC
LIMIT 0,3;
/*完整的sql语句*/
SELECT username,COUNT(username) pcount FROM (SELECT username,p_id FROM users u , products p WHERE u.`user_id` = p.user_id) name_count
GROUP BY username
ORDER BY pcount DESC
LIMIT 0,3;
/*********DESC 递减 ,ASC 递增 *********/
/*Oracle中不支持limit语法,可以用top语法 或者使用Oracle的隐藏字段rownum来实现*/
查询结果: