Mysql数据库的结果集排序、分页查询、分组查询、唯一性结果集、联合查询、连接查询
-- 准备练习使用的表
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--------------------------------------
