風行草偃

陪伴,是最长情的告白。

Mysql数据库的结果集排序、分页查询、分组查询、唯一性结果集、联合查询、连接查询

Hung:




-- 准备练习使用的表
use test;
drop table if exists users;
create table `users`(
    `id` int auto_increment,
    `username` char(20) not null ,
    `userpasswd` char(20) not null ,
    `usertype` char(10) not null default '普通会员' check(`usertype` in ('普通会员' ,'高级会员' ,'白银会员' ,'黄金会员')) ,
    `createtime` datetime not null default current_timestamp ,
    unique key(`username`) ,
    unique index unique_username (`username`) ,
    primary key(`id`)
);
insert into users values(null ,'aung' ,'aungpass' ,default ,'2015-09-1');
insert into users values(null ,'bung' ,'bungpass' ,'高级会员' ,default);
insert into users values(null ,'cung' ,'cungpass' ,default ,'2015-10-15');
insert into users values(null ,'dung' ,'dungpass' ,default ,'2015-12-16');
insert into users values(null ,'eung' ,'eungpass' ,'白银会员' ,default);
insert into users values(null ,'fung' ,'fungpass' ,default ,'2015-03-11');
insert into users values(null ,'gung' ,'gungpass' ,default ,default);
insert into users values(null ,'hung' ,'hungpass' ,'黄金会员' ,'2015-10-02');
insert into users values(null ,'iung' ,'iungpass' ,'高级会员' ,default);
insert into users values(null ,'jung' ,'jungpass' ,default ,'2015-08-12');
insert into users values(null ,'kung' ,'kungpass' ,default ,default);
desc users;
select * from users;
            mysql> select * from users;
            +----+----------+------------+--------------+---------------------+
            | id | username | userpasswd | usertype     | createtime          |
            +----+----------+------------+--------------+---------------------+
            |  1 | aung     | aungpass   | 普通会员     | 2015-09-01 00:00:00 |
            |  2 | bung     | bungpass   | 高级会员     | 2015-11-17 11:04:07 |
            |  3 | cung     | cungpass   | 普通会员     | 2015-10-15 00:00:00 |
            |  4 | dung     | dungpass   | 普通会员     | 2015-12-16 00:00:00 |
            |  5 | eung     | eungpass   | 白银会员     | 2015-11-17 11:04:08 |
            |  6 | fung     | fungpass   | 普通会员     | 2015-03-11 00:00:00 |
            |  7 | gung     | gungpass   | 普通会员     | 2015-11-17 11:04:08 |
            |  8 | hung     | hungpass   | 黄金会员     | 2015-10-02 00:00:00 |
            |  9 | iung     | iungpass   | 高级会员     | 2015-11-17 11:04:08 |
            | 10 | jung     | jungpass   | 普通会员     | 2015-08-12 00:00:00 |
            | 11 | kung     | kungpass   | 普通会员     | 2015-11-17 11:04:08 |
            +----+----------+------------+--------------+---------------------+
            11 rows in set (0.00 sec)

------------------------------------排序----------------------------------------
/*
    关键字:order by
    顺序: asc desc
*/
-- 按照创建时间正序排序
select * from users order by createtime asc;
            mysql> select * from users order by createtime asc;
            +----+----------+------------+--------------+---------------------+
            | id | username | userpasswd | usertype     | createtime          |
            +----+----------+------------+--------------+---------------------+
            |  6 | fung     | fungpass   | 普通会员     | 2015-03-11 00:00:00 |
            | 10 | jung     | jungpass   | 普通会员     | 2015-08-12 00:00:00 |
            |  1 | aung     | aungpass   | 普通会员     | 2015-09-01 00:00:00 |
            |  8 | hung     | hungpass   | 黄金会员     | 2015-10-02 00:00:00 |
            |  3 | cung     | cungpass   | 普通会员     | 2015-10-15 00:00:00 |
            |  2 | bung     | bungpass   | 高级会员     | 2015-11-17 11:04:07 |
            |  5 | eung     | eungpass   | 白银会员     | 2015-11-17 11:04:08 |
            |  7 | gung     | gungpass   | 普通会员     | 2015-11-17 11:04:08 |
            |  9 | iung     | iungpass   | 高级会员     | 2015-11-17 11:04:08 |
            | 11 | kung     | kungpass   | 普通会员     | 2015-11-17 11:04:08 |
            |  4 | dung     | dungpass   | 普通会员     | 2015-12-16 00:00:00 |
            +----+----------+------------+--------------+---------------------+
            11 rows in set (0.14 sec)


----------------------------------分页查询--------------------------------------
/*
    关键字:limit
    格式:limlt [start] , [length]
*/
select * from users order by id limit 0 ,5;
            mysql> select * from users order by id limit 0 ,5;
            +----+----------+------------+--------------+---------------------+
            | id | username | userpasswd | usertype     | createtime          |
            +----+----------+------------+--------------+---------------------+
            |  1 | aung     | aungpass   | 普通会员     | 2015-09-01 00:00:00 |
            |  2 | bung     | bungpass   | 高级会员     | 2015-11-17 11:04:07 |
            |  3 | cung     | cungpass   | 普通会员     | 2015-10-15 00:00:00 |
            |  4 | dung     | dungpass   | 普通会员     | 2015-12-16 00:00:00 |
            |  5 | eung     | eungpass   | 白银会员     | 2015-11-17 11:04:08 |
            +----+----------+------------+--------------+---------------------+
            5 rows in set (0.00 sec)
            
select * from users order by id limit 5 ,5;
            mysql> select * from users order by id limit 5 ,5;
            +----+----------+------------+--------------+---------------------+
            | id | username | userpasswd | usertype     | createtime          |
            +----+----------+------------+--------------+---------------------+
            |  6 | fung     | fungpass   | 普通会员     | 2015-03-11 00:00:00 |
            |  7 | gung     | gungpass   | 普通会员     | 2015-11-17 11:04:08 |
            |  8 | hung     | hungpass   | 黄金会员     | 2015-10-02 00:00:00 |
            |  9 | iung     | iungpass   | 高级会员     | 2015-11-17 11:04:08 |
            | 10 | jung     | jungpass   | 普通会员     | 2015-08-12 00:00:00 |
            +----+----------+------------+--------------+---------------------+
            5 rows in set (0.00 sec)


----------------------------------分组查询--------------------------------------
/*
    通常结合聚合函数一起使用
    关键字:group by
    格式:group by [字段]
*/
-- 查看共有多少会员
select count(*) from users;
            mysql> select count(*) from users;
            +----------+
            | count(*) |
            +----------+
            |       11 |
            +----------+
            1 row in set (0.06 sec)
select count(*) as '人数' ,usertype as '会员类型' from users group by usertype;
            mysql> select count(*) as '人数' ,usertype as '会员类型' from users group by usertype;
            +--------+--------------+
            | 人数   | 会员类型     |
            +--------+--------------+
            |      7 | 普通会员     |
            |      1 | 白银会员     |
            |      2 | 高级会员     |
            |      1 | 黄金会员     |
            +--------+--------------+
            4 rows in set (0.01 sec)


---------------------------------唯一性结果集------------------------------------
/*
    关键字:distinct
    格式:紧跟select之后
    一般只用于查询某一列
    distinct [字段],[字段] 这种情况表示多列同时重复时才生效
*/
-- 查看一共有几种会员
select distinct usertype from users;
            mysql> select distinct usertype from users;
            +--------------+
            | usertype     |
            +--------------+
            | 普通会员     |
            | 高级会员     |
            | 白银会员     |
            | 黄金会员     |
            +--------------+
            4 rows in set (0.00 sec)


----------------------------------联合查询--------------------------------------
/*
    纵向联合多个表
    关键字:union ,union all
    union会覆盖重复的值,union all不会
    union结果集的列名使用的是查询语句1的列名
*/
select * from student union select * from score;
            mysql> select * from student union select * from score;
            +----+--------+------+
            | id | stu_id | name |
            +----+--------+------+
            |  1 |      1 | Hung |
            |  1 |     80 | 1    |
            +----+--------+------+
            2 rows in set (0.01 sec)


----------------------------------连接查询--------------------------------------
-- create student table
drop table if exists `student`;
create table `student`(
    `id` int auto_increment ,
    `name` char(20) not null ,
    unique key(`name`) ,
    primary key(`id`)
);

-- create score table
drop table if exists `score`;
create table `score`(
    `index` int auto_increment ,
    `score` double not null default 0 ,
    `stu_id` int unique ,
    primary key(`index`) ,
    constraint `fk_student_id` foreign key(`stu_id`) references student(`id`)
);

-- data for student table
insert into student values(null , 'Aung');
insert into student values(null , 'Bung');
insert into student values(null , 'Cung');
insert into student values(null , 'Dung');
insert into student values(null , 'Eung');
insert into student values(null , 'Fung');
-- data for score table
insert into score values(null , 80, 1);
insert into score values(null , 85, 2);
insert into score values(null , 90, 3);
insert into score values(null , 95, 4);
insert into score values(null , 97, 5);

/*
    连接查询
        – 内连接([inner] join)
            - 隐式内连接:select * from student ,score where student.id = score.stu_id;
        – 外连接(outer join)
            - 左外连接(left [outer] join)
            - 右外连接(right [outer] join)
            - 全外连接(full [outer] join )(mysql不支持)
        – 交叉连接(cross join)
    横向连接结果集
*/
/*
    结果集为:两个表同时满足条件的记录(行),横向拼接在一起的集合
*/
-- 内连接
select * from student inner join score where student.id = score.stu_id;
            +----+------+-------+-------+--------+
            | id | name | index | score | stu_id |
            +----+------+-------+-------+--------+
            |  1 | Aung |     1 |    80 |      1 |
            |  2 | Bung |     2 |    85 |      2 |
            |  3 | Cung |     3 |    90 |      3 |
            |  4 | Dung |     4 |    95 |      4 |
            |  5 | Eung |     5 |    97 |      5 |
            +----+------+-------+-------+--------+
            5 rows in set (0.01 sec)

-- 外连接
    -- 左外连接
        select * from student left join score on student.id = score.stu_id;
            +----+------+-------+-------+--------+
            | id | name | index | score | stu_id |
            +----+------+-------+-------+--------+
            |  1 | Aung |     1 |    80 |      1 |
            |  2 | Bung |     2 |    85 |      2 |
            |  3 | Cung |     3 |    90 |      3 |
            |  4 | Dung |     4 |    95 |      4 |
            |  5 | Eung |     5 |    97 |      5 |
            |  6 | Fung |  NULL |  NULL |   NULL |
            +----+------+-------+-------+--------+
    -- 右外连接
        select * from student right join score on student.id = score.stu_id;
            +------+------+-------+-------+--------+
            | id   | name | index | score | stu_id |
            +------+------+-------+-------+--------+
            |    1 | Aung |     1 |    80 |      1 |
            |    2 | Bung |     2 |    85 |      2 |
            |    3 | Cung |     3 |    90 |      3 |
            |    4 | Dung |     4 |    95 |      4 |
            |    5 | Eung |     5 |    97 |      5 |
            +------+------+-------+-------+--------+
-- 交叉连接
    -- 交叉连接(cross join)也称为笛卡尔积
    -- 结果集为:左表的每一行分别和右表的每一行横向拼接后的集合
    -- 一旦加了条件,得到的结果集将不再是两个表的笛卡尔积
    -- 获取笛卡尔积的另外两种方式
        -- select * from 表1,表2;//隐式交叉连接
        -- select * from 表1 [inner] join 表2
            
---------------------------------------END--------------------------------------




评论
热度 ( 2 )
  1. 風行草偃鸿则 转载了此文字

© 風行草偃 | Powered by LOFTER