mariadb数据库查询（select）
2024-10-09 22:00:45
查询基本使用(条件，排序，聚合函数，分组，分页)
示例：
--创建学生表
create table students (
id int unsigned not null auto_increment primary key,
name varchar(20) default '',
age tinyint unsigned default 0,
high decimal(5,2),
gender enum('男', '女', '中性', '保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
); --创建班级表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(20) not null
); --往students表里插入数据
insert into students values
(0,'小明',18,180.00,1,1,0),
(0,'小月月',19,180.00,1,2,0),
(0,'彭于晏',28,185.00,1,1,0),
(0,'刘德华',58,175.00,1,2,0),
(0,'黄蓉',108,160.00,2,1,0),
(0,'凤姐',44,150.00,4,2,1),
(0,'王祖贤',52,170.00,2,1,1),
(0,'周杰伦儿',34,null,1,1,0),
(0,'程坤',44,181.00,1,2,0),
(0,'和珅',55,166.00,1,2,0),
(0,'刘亦菲',29,162.00,2,3,0),
(0,'金星',45,180.00,3,4,0),
(0,'静香',18,170.00,2,4,0),
(0,'郭靖',22,167.00,1,5,0),
(0,'周杰',33,178.00,1,1,0),
(0,'钱小豪',56,178.00,1,1,0),
(0,'谢霆锋',38,175.00,1,1,0),
(0,'陈冠希',38,175.00,1,1,0);
查询所有
select * from 表名；
MariaDB [testdb]> select * from students;
+----+--------------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+--------------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 58 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | |
| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |
| 9 | 程坤 | 44 | 181.00 | 男 | 2 | |
| 10 | 和珅 | 55 | 166.00 | 男 | 2 | |
| 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | |
| 12 | 金星 | 45 | 180.00 | 中性 | 4 | |
| 13 | 静香 | 18 | 170.00 | 女 | 4 | |
| 14 | 郭靖 | 22 | 167.00 | 男 | 5 | |
| 15 | 周杰 | 33 | 178.00 | 男 | 1 | |
| 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | |
| 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | |
| 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | |
+----+--------------+------+--------+--------+--------+-----------+
18 rows in set (0.00 sec)
按条件查询（where）
MariaDB [testdb]> select * from students where id=5;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | |
+----+--------+------+--------+--------+--------+-----------+
1 row in set (0.00 sec)
查询制定列
MariaDB [testdb]> select id,name from students;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 小明 |
| 2 | 小月月 |
| 3 | 彭于晏 |
| 4 | 刘德华 |
| 5 | 黄蓉 |
| 6 | 凤姐 |
| 7 | 王祖贤 |
| 8 | 周杰伦儿 |
| 9 | 程坤 |
| 10 | 和珅 |
| 11 | 刘亦菲 |
| 12 | 金星 |
| 13 | 静香 |
| 14 | 郭靖 |
| 15 | 周杰 |
| 16 | 钱小豪 |
| 17 | 谢霆锋 |
| 18 | 陈冠希 |
+----+--------------+
18 rows in set (0.00 sec)
使用as给字段起别名
MariaDB [testdb]> select id,name as '姓名',age,gender from students;
+----+--------------+------+--------+
| id | 姓名 | age | gender |
+----+--------------+------+--------+
| 1 | 小明 | 18 | 男 |
| 2 | 小月月 | 19 | 男 |
| 3 | 彭于晏 | 28 | 男 |
| 4 | 刘德华 | 58 | 男 |
| 5 | 黄蓉 | 108 | 女 |
| 6 | 凤姐 | 44 | 保密 |
| 7 | 王祖贤 | 52 | 女 |
| 8 | 周杰伦儿 | 34 | 男 |
| 9 | 程坤 | 44 | 男 |
| 10 | 和珅 | 55 | 男 |
| 11 | 刘亦菲 | 29 | 女 |
| 12 | 金星 | 45 | 中性 |
| 13 | 静香 | 18 | 女 |
| 14 | 郭靖 | 22 | 男 |
| 15 | 周杰 | 33 | 男 |
| 16 | 钱小豪 | 56 | 男 |
| 17 | 谢霆锋 | 38 | 男 |
| 18 | 陈冠希 | 38 | 男 |
+----+--------------+------+--------+
18 rows in set (0.00 sec)
通过表名字段查询
MariaDB [testdb]> select students.name from students;
+--------------+
| name |
+--------------+
| 小明 |
| 小月月 |
| 彭于晏 |
| 刘德华 |
| 黄蓉 |
| 凤姐 |
| 王祖贤 |
| 周杰伦儿 |
| 程坤 |
| 和珅 |
| 刘亦菲 |
| 金星 |
| 静香 |
| 郭靖 |
| 周杰 |
| 钱小豪 |
| 谢霆锋 |
| 陈冠希 |
+--------------+
18 rows in set (0.00 sec)
给表起别名查询
MariaDB [testdb]> select s.id,s.name,s.age from students as s;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 1 | 小明 | 18 |
| 2 | 小月月 | 19 |
| 3 | 彭于晏 | 28 |
| 4 | 刘德华 | 58 |
| 5 | 黄蓉 | 108 |
| 6 | 凤姐 | 44 |
| 7 | 王祖贤 | 52 |
| 8 | 周杰伦儿 | 34 |
| 9 | 程坤 | 44 |
| 10 | 和珅 | 55 |
| 11 | 刘亦菲 | 29 |
| 12 | 金星 | 45 |
| 13 | 静香 | 18 |
| 14 | 郭靖 | 22 |
| 15 | 周杰 | 33 |
| 16 | 钱小豪 | 56 |
| 17 | 谢霆锋 | 38 |
| 18 | 陈冠希 | 38 |
+----+--------------+------+
18 rows in set (0.00 sec)
消除重复行 distinct
MariaDB [testdb]> select distinct age from students;
+------+
| age |
+------+
| 18 |
| 19 |
| 28 |
| 58 |
| 108 |
| 44 |
| 52 |
| 34 |
| 55 |
| 29 |
| 45 |
| 22 |
| 33 |
| 56 |
| 38 |
+------+
15 rows in set (0.00 sec)
条件查询
--比较运算符
-- 查询年纪大于18岁的信息
MariaDB [testdb]> select * from students where age >18;
+----+--------------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+--------------+------+--------+--------+--------+-----------+
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 58 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | |
| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |
| 9 | 程坤 | 44 | 181.00 | 男 | 2 | |
| 10 | 和珅 | 55 | 166.00 | 男 | 2 | |
| 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | |
| 12 | 金星 | 45 | 180.00 | 中性 | 4 | |
| 14 | 郭靖 | 22 | 167.00 | 男 | 5 | |
| 15 | 周杰 | 33 | 178.00 | 男 | 1 | |
| 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | |
| 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | |
| 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | |
+----+--------------+------+--------+--------+--------+-----------+
16 rows in set (0.00 sec)
--查寻18到28岁之间(and)
select * from students where age >= 18 and age =< 28;
MariaDB [testdb]> select * from students where age >=18 and age <=28;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 13 | 静香 | 18 | 170.00 | 女 | 4 | |
| 14 | 郭靖 | 22 | 167.00 | 男 | 5 | |
+----+-----------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)
select * from students where age between 18 and 28
MariaDB [testdb]> select * from students where age between 18 and 28;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 13 | 静香 | 18 | 170.00 | 女 | 4 | |
| 14 | 郭靖 | 22 | 167.00 | 男 | 5 | |
+----+-----------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)
查询在18岁以上或者身高180以上的人(or)
MariaDB [testdb]> select * from students where age >18 or high >180;
+----+--------------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+--------------+------+--------+--------+--------+-----------+
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 58 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | |
| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |
| 9 | 程坤 | 44 | 181.00 | 男 | 2 | |
| 10 | 和珅 | 55 | 166.00 | 男 | 2 | |
| 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | |
| 12 | 金星 | 45 | 180.00 | 中性 | 4 | |
| 14 | 郭靖 | 22 | 167.00 | 男 | 5 | |
| 15 | 周杰 | 33 | 178.00 | 男 | 1 | |
| 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | |
| 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | |
| 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | |
+----+--------------+------+--------+--------+--------+-----------+
16 rows in set (0.00 sec)
模糊查询 like
% 替代1个或者多个甚至是没有
查询姓名中有‘小’的所有名字
select * from students where name like '%小%';
MariaDB [testdb]> select * from students where name like '%小%';
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | |
+----+-----------+------+--------+--------+--------+-----------+
3 rows in set (0.00 sec)
查询两个字人的名字
select * from students where name like '__';
MariaDB [testdb]> select * from students where name like '__';
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | |
| 9 | 程坤 | 44 | 181.00 | 男 | 2 | |
| 10 | 和珅 | 55 | 166.00 | 男 | 2 | |
| 12 | 金星 | 45 | 180.00 | 中性 | 4 | |
| 13 | 静香 | 18 | 170.00 | 女 | 4 | |
| 14 | 郭靖 | 22 | 167.00 | 男 | 5 | |
| 15 | 周杰 | 33 | 178.00 | 男 | 1 | |
+----+--------+------+--------+--------+--------+-----------+
9 rows in set (0.00 sec)
查询至少有2个字的名字
select * from students where name like '%__%';
MariaDB [testdb]> select * from students where name like '%__%';
+----+--------------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+--------------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 58 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | |
| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |
| 9 | 程坤 | 44 | 181.00 | 男 | 2 | |
| 10 | 和珅 | 55 | 166.00 | 男 | 2 | |
| 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | |
| 12 | 金星 | 45 | 180.00 | 中性 | 4 | |
| 13 | 静香 | 18 | 170.00 | 女 | 4 | |
| 14 | 郭靖 | 22 | 167.00 | 男 | 5 | |
| 15 | 周杰 | 33 | 178.00 | 男 | 1 | |
| 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | |
| 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | |
| 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | |
+----+--------------+------+--------+--------+--------+-----------+
18 rows in set (0.00 sec)
范围查询
in (1,3,8)表示在一个非连续的范围内
-- 查询 年纪为18和34的人
select * from students where age in (18, 34);
MariaDB [testdb]> select * from students where age in (18,34);
+----+--------------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+--------------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |
| 13 | 静香 | 18 | 170.00 | 女 | 4 | |
+----+--------------+------+--------+--------+--------+-----------+
3 rows in set (0.00 sec)
-查询 年龄在17岁到34岁之间的信息
select * from students where age between 17 and 34;
--查询 年纪不在18到34岁的信息
select * from students where age not between 17 and 34;
空判断
-- 判断is null
-- 查询身高为空的信息
select * from students where high is null;
-- 判断非空is not null
select * from students where high is not null;
- 排序
-- order by 字段
-- asc从小到大排列,即升序
-- desc从大到小排序，即降序
-- 查询年纪在18到34岁之间的男性，按照年纪从小到大
select * from students where gender=1 and age between 18 and 34 order by age;
MariaDB [testdb]> select * from students where gender=1 and age between 18 and 34 order by age;
+----+--------------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+--------------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 14 | 郭靖 | 22 | 167.00 | 男 | 5 | |
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 15 | 周杰 | 33 | 178.00 | 男 | 1 | |
| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |
+----+--------------+------+--------+--------+--------+-----------+
6 rows in set (0.00 sec)
-- 查询年纪在18到34岁之间的女性，身高从高到矮
select * from students where gender=2 and age between 18 and 34 order by high desc;
MariaDB [testdb]> select * from students where gender=2 and age between 18 and 34 order by high desc;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 13 | 静香 | 18 | 170.00 | 女 | 4 | |
| 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | |
+----+-----------+------+--------+--------+--------+-----------+
2 rows in set (0.00 sec)
-- order by 多字段
-- 查询年纪在18到34岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序
select * from students where age between 18 and 34 and gender=2 order by high desc,age desc;
MariaDB [testdb]> select * from students where age between 18 and 34 and gender=2 order by high desc，age desc;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 13 | 静香 | 18 | 170.00 | 女 | 4 | |
| 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | |
+----+-----------+------+--------+--------+--------+-----------+
2 rows in set (0.00 sec)
-- 查询年纪在18到34岁的男性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序，如果年龄也相等那么按照id从小到大排序；
select * from students where age between 18 and 34 and gender=1 order by high desc, age desc, id desc;
MariaDB [testdb]> select * from students where age between 18 and 34 and gender=1 order by high desc, age desc, id desc;
+----+--------------+------+--------+--------+--------+-----------+
| id | name | age | high | gender | cls_id | is_delete |
+----+--------------+------+--------+--------+--------+-----------+
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 15 | 周杰 | 33 | 178.00 | 男 | 1 | |
| 14 | 郭靖 | 22 | 167.00 | 男 | 5 | |
| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |
+----+--------------+------+--------+--------+--------+-----------+
6 rows in set (0.00 sec)
聚合函数
-- 总数
-- count
-- 查询男性有多少人
select count(*) from students where gender=1;
MariaDB [testdb]> select count(*) from students where gender=1;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
-- 最大值
-- max
-- 查询最大的年纪
select max(age) from students;
MariaDB [testdb]> select max(age) from students;
+----------+
| max(age) |
+----------+
| 108 |
+----------+
1 row in set (0.00 sec)
-- 查询女性的最高 身高
select max(high) from students where gender=2;
MariaDB [testdb]> select max(high) from students where gender=2;
+-----------+
| max(high) |
+-----------+
| 170.00 |
+-----------+
1 row in set (0.00 sec)
-- 最小值
-- min
select min(high) from students;
MariaDB [testdb]> select min(high) from students;
+-----------+
| min(high) |
+-----------+
| 150.00 |
+-----------+
1 row in set (0.00 sec)
-- 求和
-- sum
-- 计算所有人的年龄总和
select sum(age) from students;
MariaDB [testdb]> select sum(age) from students;
+----------+
| sum(age) |
+----------+
| 739 |
+----------+
1 row in set (0.01 sec)
-- 平均值
-- avg
-- 计算平均年纪
-- 计算平均年纪 sum(age)/count(*)
select sum(age)/count(*) from students;
MariaDB [testdb]> select sum(age)/count(*) from students;
+-------------------+
| sum(age)/count(*) |
+-------------------+
| 41.0556 |
+-------------------+
1 row in set (0.00 sec)
select avg(age),2 from students;
MariaDB [testdb]> select avg(age),2 from students;
+----------+---+
| avg(age) | 2 |
+----------+---+
| 41.0556 | 2 |
+----------+---+
1 row in set (0.00 sec)
-- 保留2位小数
select round(avg(age),2) from students;
MariaDB [testdb]> select round(avg(age),2) from students;
+-------------------+
| round(avg(age),2) |
+-------------------+
| 41.06 |
+-------------------+
1 row in set (0.00 sec)
-- 分组
-- group by
-- 按照性别分组，查询所有的性别
select gender from students group by gender;
MariaDB [testdb]> select gender from students group by gender;
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 中性 |
| 保密 |
+--------+
4 rows in set (0.00 sec)
-- 计算每组性别的人数
select gender, count(*) from students group by gender;
MariaDB [testdb]> select gender, count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 12 |
| 女 | 4 |
| 中性 | 1 |
| 保密 | 1 |
+--------+----------+
4 rows in set (0.00 sec)
-- 查询男性组中的姓名 group_concat
select gender,group_concat(name) from students where gender=1 group by gender;
MariaDB [testdb]> select gender,group_concat(name) from students where gender=1 group by gender;
+--------+-------------------------------------------------------------------------------------------------------------+
| gender | group_concat(name) |
+--------+-------------------------------------------------------------------------------------------------------------+
| 男 | 小明,谢霆锋,钱小豪,周杰,郭靖,和珅,程坤,周杰伦儿,刘德华,彭于晏,小月月,陈冠希 |
+--------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- having
-- 查询每个性别平均年纪超过30岁的性别，以及姓名 having avg(age) > 30
select gender, group_concat(name) from students group by gender having avg(age) > 30;
MariaDB [testdb]> select gender, group_concat(name) from students group by gender having avg(age) > 30;
+--------+-------------------------------------------------------------------------------------------------------------+
| gender | group_concat(name) |
+--------+-------------------------------------------------------------------------------------------------------------+
| 男 | 小明,谢霆锋,钱小豪,周杰,郭靖,和珅,程坤,周杰伦儿,陈冠希,小月月,彭于晏,刘德华 |
| 女 | 黄蓉,静香,刘亦菲,王祖贤 |
| 中性 | 金星 |
| 保密 | 凤姐 |
+--------+-------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
-- 查询每种性别中的人数多于4个的组的信息
select gender,group_concat(name) from students group by gender having count(*)>4;
MariaDB [testdb]> select gender,group_concat(name) from students group by gender having count(*)>4;
+--------+-------------------------------------------------------------------------------------------------------------+
| gender | group_concat(name) |
+--------+-------------------------------------------------------------------------------------------------------------+
| 男 | 小明,谢霆锋,钱小豪,周杰,郭靖,和珅,程坤,周杰伦儿,陈冠希,小月月,彭于晏,刘德华 |
+--------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
-- 分页
-- 显示5页
select * from students limit 5; -- 分页显示，每页显示2条数据
select * from students limit 0, 2; -- 按照身高从高到矮排序，查找出所有女性，并且分页显示，每页显示2条数据
select * from students where gender=2 order by high desc limit 0,2;
关联查询
内关联 inner join .. on
两个表连接查询
select * from students inner join classes;
查询能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
MariaDB [testdb]> select * from students inner join classes on students.cls_id=classes.id;
+----+--------------+------+--------+--------+--------+-----------+----+------------+
| id | name | age | high | gender | cls_id | is_delete | id | name |
+----+--------------+------+--------+--------+--------+-----------+----+------------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | | 1 | 运维1期 |
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | | 1 | 运维1期 |
| 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | | 1 | 运维1期 |
| 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | | 1 | 运维1期 |
| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | | 1 | 运维1期 |
| 15 | 周杰 | 33 | 178.00 | 男 | 1 | | 1 | 运维1期 |
| 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | | 1 | 运维1期 |
| 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | | 1 | 运维1期 |
| 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | | 1 | 运维1期 |
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | | 2 | 运维2期 |
| 4 | 刘德华 | 58 | 175.00 | 男 | 2 | | 2 | 运维2期 |
| 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | | 2 | 运维2期 |
| 9 | 程坤 | 44 | 181.00 | 男 | 2 | | 2 | 运维2期 |
| 10 | 和珅 | 55 | 166.00 | 男 | 2 | | 2 | 运维2期 |
| 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | | 3 | 运维3期 |
| 12 | 金星 | 45 | 180.00 | 中性 | 4 | | 4 | 运维4期 |
| 13 | 静香 | 18 | 170.00 | 女 | 4 | | 4 | 运维4期 |
+----+--------------+------+--------+--------+--------+-----------+----+------------+
17 rows in set (0.00 sec)
按照要求显示姓名，班级
select students.name,classes.name from students inner join classes on students.cls_id=classes.id;
MariaDB [testdb]> select students.name,classes.name from students inner join classes on students.cls_id=classes.id;
+--------------+------------+
| name | name |
+--------------+------------+
| 小明 | 运维1期 |
| 彭于晏 | 运维1期 |
| 黄蓉 | 运维1期 |
| 王祖贤 | 运维1期 |
| 周杰伦儿 | 运维1期 |
| 周杰 | 运维1期 |
| 钱小豪 | 运维1期 |
| 谢霆锋 | 运维1期 |
| 陈冠希 | 运维1期 |
| 小月月 | 运维2期 |
| 刘德华 | 运维2期 |
| 凤姐 | 运维2期 |
| 程坤 | 运维2期 |
| 和珅 | 运维2期 |
| 刘亦菲 | 运维3期 |
| 金星 | 运维4期 |
| 静香 | 运维4期 |
+--------------+------------+
17 rows in set (0.00 sec)
给数据表起别名
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
MariaDB [testdb]> select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
+--------------+------------+
| name | name |
+--------------+------------+
| 小明 | 运维1期 |
| 彭于晏 | 运维1期 |
| 黄蓉 | 运维1期 |
| 王祖贤 | 运维1期 |
| 周杰伦儿 | 运维1期 |
| 周杰 | 运维1期 |
| 钱小豪 | 运维1期 |
| 谢霆锋 | 运维1期 |
| 陈冠希 | 运维1期 |
| 小月月 | 运维2期 |
| 刘德华 | 运维2期 |
| 凤姐 | 运维2期 |
| 程坤 | 运维2期 |
| 和珅 | 运维2期 |
| 刘亦菲 | 运维3期 |
| 金星 | 运维4期 |
| 静香 | 运维4期 |
+--------------+------------+
17 rows in set (0.01 sec)
查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
MariaDB [testdb]> select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
+----+--------------+------+--------+--------+--------+-----------+------------+
| id | name | age | high | gender | cls_id | is_delete | name |
+----+--------------+------+--------+--------+--------+-----------+------------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | | 运维1期 |
| 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | | 运维1期 |
| 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | | 运维1期 |
| 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | | 运维1期 |
| 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | | 运维1期 |
| 15 | 周杰 | 33 | 178.00 | 男 | 1 | | 运维1期 |
| 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | | 运维1期 |
| 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | | 运维1期 |
| 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | | 运维1期 |
| 2 | 小月月 | 19 | 180.00 | 男 | 2 | | 运维2期 |
| 4 | 刘德华 | 58 | 175.00 | 男 | 2 | | 运维2期 |
| 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | | 运维2期 |
| 9 | 程坤 | 44 | 181.00 | 男 | 2 | | 运维2期 |
| 10 | 和珅 | 55 | 166.00 | 男 | 2 | | 运维2期 |
| 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | | 运维3期 |
| 12 | 金星 | 45 | 180.00 | 中性 | 4 | | 运维4期 |
| 13 | 静香 | 18 | 170.00 | 女 | 4 | | 运维4期 |
+----+--------------+------+--------+--------+--------+-----------+------------+
17 rows in set (0.00 sec)
在以上查询中将班级姓名显示在第一列
select classes.name,students.* from students inner join classes on students.cls_id=classes.id;
MariaDB [testdb]> select classes.name,students.* from students inner join classes on students.cls_id=classes.id;
+------------+----+--------------+------+--------+--------+--------+-----------+
| name | id | name | age | high | gender | cls_id | is_delete |
+------------+----+--------------+------+--------+--------+--------+-----------+
| 运维1期 | 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 运维1期 | 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 运维1期 | 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | |
| 运维1期 | 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | |
| 运维1期 | 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |
| 运维1期 | 15 | 周杰 | 33 | 178.00 | 男 | 1 | |
| 运维1期 | 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | |
| 运维1期 | 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | |
| 运维1期 | 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | |
| 运维2期 | 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 运维2期 | 4 | 刘德华 | 58 | 175.00 | 男 | 2 | |
| 运维2期 | 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | |
| 运维2期 | 9 | 程坤 | 44 | 181.00 | 男 | 2 | |
| 运维2期 | 10 | 和珅 | 55 | 166.00 | 男 | 2 | |
| 运维3期 | 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | |
| 运维4期 | 12 | 金星 | 45 | 180.00 | 中性 | 4 | |
| 运维4期 | 13 | 静香 | 18 | 170.00 | 女 | 4 | |
+------------+----+--------------+------+--------+--------+--------+-----------+
17 rows in set (0.00 sec)
查询有能够对应班级的学生以及班级信息，按照班级进行排序
select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id;
MariaDB [testdb]> select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id;
+----+----+--------------+------+--------+--------+--------+-----------+
| id | id | name | age | high | gender | cls_id | is_delete |
+----+----+--------------+------+--------+--------+--------+-----------+
| 1 | 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 1 | 17 | 谢霆锋 | 38 | 175.00 | 男 | 1 | |
| 1 | 16 | 钱小豪 | 56 | 178.00 | 男 | 1 | |
| 1 | 15 | 周杰 | 33 | 178.00 | 男 | 1 | |
| 1 | 18 | 陈冠希 | 38 | 175.00 | 男 | 1 | |
| 1 | 8 | 周杰伦儿 | 34 | NULL | 男 | 1 | |
| 1 | 7 | 王祖贤 | 52 | 170.00 | 女 | 1 | |
| 1 | 5 | 黄蓉 | 108 | 160.00 | 女 | 1 | |
| 1 | 3 | 彭于晏 | 28 | 185.00 | 男 | 1 | |
| 2 | 6 | 凤姐 | 44 | 150.00 | 保密 | 2 | |
| 2 | 2 | 小月月 | 19 | 180.00 | 男 | 2 | |
| 2 | 4 | 刘德华 | 58 | 175.00 | 男 | 2 | |
| 2 | 10 | 和珅 | 55 | 166.00 | 男 | 2 | |
| 2 | 9 | 程坤 | 44 | 181.00 | 男 | 2 | |
| 3 | 11 | 刘亦菲 | 29 | 162.00 | 女 | 3 | |
| 4 | 12 | 金星 | 45 | 180.00 | 中性 | 4 | |
| 4 | 13 | 静香 | 18 | 170.00 | 女 | 4 | |
+----+----+--------------+------+--------+--------+--------+-----------+
17 rows in set (0.00 sec)
最新文章
- IOS遍历网页获取网页中<img>标签中的图片url
- jquery例子
- linq之where子句
- swift基础二
- MongoDB 3.0.6的主,从,仲裁节点搭建
- 排队（BZOJ1731：[Usaco2005 dec]Layout 排队布局）
- Android数字签名解析(三)
- Android’s HTTP Clients (httpClient 和 httpURLConnect 区别)
- 福州大学W班 软件工程课中期调查
- 201771010134杨其菊《面向对象程序设计(java）》第十六周学习总结
- jira6.3.6创建问题不自动发邮件通知的问题
- Struts 2.5.20 在Eclipse IDE中的配置和开发实例
- SkylineGlobe 如何使用二次开发接口创建粒子效果
- poj 2031 给出每个结点的3维坐标 以及结点的半径 （MST）
- 【2017-2-20】C#运算符
- 卷积神经网络 CNN 笔记
- Python3的bytes和str之别
- Oracle中对数字加汉字的排序(完好)
- Python使用SSL方式发送QQ邮箱
- 通过HTTP协议发送远程消息