MySQL数据库之select查询语句select查询语句
语法
select [选项] 列名 [from 表名] [where 条件] [group by 分组] [order by 排序][having 条件] [limit 限制]
select字段表达式
可以直接输出内容
MariaDB [sel]> select 'Sunny is a boy!';
+-----------------+
| Sunny is a boy! |
+-----------------+
| Sunny is a boy! |
+-----------------+
# `1 row in set (0.000 sec)`
输出表达式
MariaDB [sel]> select 10*27;
+-------+
| 10*27 |
+-------+
| 270 |
+-------+
# `1 row in set (0.000 sec)`
MariaDB [sel]> select name,chinese,math,chinese+math from grades;
+-------+---------+------+--------------+
| name | chinese | math | chinese+math |
+-------+---------+------+--------------+
| Sunny | 93 | 96 | 189 |
| Jerry | 97 | 91 | 188 |
| Marry | 95 | 94 | 189 |
| Tommy | 98 | 94 | 192 |
+-------+---------+------+--------------+
# `4 rows in set (0.000 sec)`
输出函数表达式
MariaDB [sel]> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.294372641755615 |
+-------------------+
# `1 row in set (0.007 sec)`
通过as给字段取别名
as可以省略
MariaDB [sel]> select 'Sunny' as 'name';
+-------+
| name |
+-------+
| Sunny |
+-------+
# `1 row in set (0.000 sec)`
MariaDB [sel]> select name,chinese,math,chinese+math as '总分' from grades;
+-------+---------+------+------+
| name | chinese | math | 总分 |
+-------+---------+------+------+
| Sunny | 93 | 96 | 189 |
| Jerry | 97 | 91 | 188 |
| Marry | 95 | 94 | 189 |
| Tommy | 98 | 94 | 192 |
+-------+---------+------+------+
# `4 rows in set (0.007 sec)`
MariaDB [sel]> select name,chinese,math,chinese+math '总分' from grades;
+-------+---------+------+------+
| name | chinese | math | 总分 |
+-------+---------+------+------+
| Sunny | 93 | 96 | 189 |
| Jerry | 97 | 91 | 188 |
| Marry | 95 | 94 | 189 |
| Tommy | 98 | 94 | 192 |
+-------+---------+------+------+
# `4 rows in set (0.001 sec)`
from⼦句
from [表名]
from后⾯跟的是数据源
数据源可以有多个，返回笛卡尔积
-- 创建数据表
MariaDB [sel]> create table stu1(
-> name varchar(20)
-> )charset=gbk;
# `Query OK, 0 rows affected (0.026 sec)`
MariaDB [sel]> insert into stu1 values ('Sunny'),('Jerry');
# `Query OK, 2 rows affected (0.012 sec)`
# `Records: 2 Duplicates: 0 Warnings: 0`
MariaDB [sel]> create table stu2(
-> age int
-> )charset=gbk;
# `Query OK, 0 rows affected (0.023 sec)`
MariaDB [sel]> insert into stu2 values (20),(24);
# `Query OK, 2 rows affected (0.012 sec)`
# `Records: 2 Duplicates: 0 Warnings: 0`
-- from⼦句查询
MariaDB [sel]> select * from stu1;
+-------+
| name |
+-------+
| Sunny |
| Jerry |
+-------+
# `2 rows in set (0.000 sec)`
-- from返回笛卡尔积
MariaDB [sel]> select * from stu1,stu2;
+-------+------+
| name | age |
+-------+------+
| Sunny | 20 |
| Jerry | 20 |
| Sunny | 24 |
| Jerry | 24 |
+-------+------+
# `4 rows in set (0.000 sec)`
dual表
概念
dual表是⼀个伪表
在有些特定情况下，没有具体的表的参与
为了保证select语句的完整⼜必须要⼀个表名，这时候就使⽤伪表MariaDB [sel]> select 10*27 as '结果' from dual;
+------+
| 结果 |
+------+
| 270 |
+------+
# `1 row in set (0.007 sec)`
where⼦句
概念
where后⾯跟的是条件，在数据源中进⾏筛选
返回条件为真记录
MySQL⽀持的运算符
⽐较运算符
>⼤于
<⼩于
>=⼤于等于
<=⼩于等于
=等于
!=不等于
逻辑运算符
and与
or或
not⾮
其他
in | not in字段的值在枚举范围内
is null | is not null字段的值不为空
-- ⽐较运算判断
MariaDB [sel]> select * from grades where math=94;
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Marry | 95 | 94 |
| Tommy | 98 | 94 |
+-------+---------+------+
# `2 rows in set (0.007 sec)`
-- 输出所有数据
MariaDB [sel]> select * from grades where 1;
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Sunny | 93 | 96 |
| Jerry | 97 | 91 |
| Marry | 95 | 94 |
| Tommy | 98 | 94 |
+-------+---------+------+
# `4 rows in set (0.000 sec)`
-- 不输出数据
MariaDB [sel]> select * from grades where 0;
# `Empty set (0.000 sec)`
-- 逻辑运算判断
MariaDB [sel]> select * from grades where math=96 or math=91;
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Sunny | 93 | 96 |
| Jerry | 97 | 91 |
+-------+---------+------+
# `2 rows in set (0.001 sec)`
MariaDB [sel]> select * from grades where math in (91,96);
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Sunny | 93 | 96 |
| Jerry | 97 | 91 |
+-------+---------+------+
# `2 rows in set (0.000 sec)`
MariaDB [sel]> select * from grades where math not in (91,96);
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Marry | 95 | 94 |
| Tommy | 98 | 94 |
+-------+---------+------+
# `2 rows in set (0.000 sec)`
查年龄在20~25之间
-- ⽅法⼀：
mysql> select * from stu where stuage>=20 and stuage<=25;
-- ⽅法⼆：
mysql> select * from stu where not(stuage<20 or stuage>25);
-- ⽅法三：
mysql> select * from stu where stuage between 20 and 25;
-- 年龄不在20~25之间
mysql> select * from stu where stuage not between 20 and 25;
查缺考的学⽣
mysql> select * from stu where ch is null or math is null;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 |
+--------+----------+--------+--------+---------+------------+------+------+
查没有缺考的学⽣
mysql> select * from stu where ch is not null and math is not null;
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25302 | 李⽂才 | 男 | 31 | 3 | 上海 | 77 | 76 |
| s25303 | 李斯⽂ | ⼥ | 22 | 2 | 北京 | 55 | 82 |
| s25305 | 诸葛丽丽 | ⼥ | 23 | 7 | 河南 | 72 | 56 |
| s25318 | 争青⼩⼦ | 男 | 26 | 6 | 天津 | 86 | 92 |
| s25319 | 梅超风 | ⼥ | 23 | 5 | 河北 | 74 | 67 |
| s25320 | Tom | 男 | 24 | 8 | 北京 | 65 | 67 |
| s25321 | Tabm | ⼥ | 23 | 9 | 河北 | 88 | 77 |
+--------+----------+--------+--------+---------+------------+------+------+
# `7 rows in set (0.00 sec)`
查需要补考的学⽣
mysql> select * from stu where ch<60 or math<60 or ch is null or math is null; +--------+----------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
| s25303 | 李斯⽂ | ⼥ | 22 | 2 | 北京 | 55 | 82 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | NULL | 74 |
| s25305 | 诸葛丽丽 | ⼥ | 23 | 7 | 河南 | 72 | 56 |
+--------+----------+--------+--------+---------+------------+------+------+
4 rows in set (0.00 sec)
查学号是s25301,s25302,s25303的学⽣
mysql> select * from stu where stuno in ('s25301','s25302','s25303');
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
| s25302 | 李⽂才 | 男 | 31 | 3 | 上海 | 77 | 76 |
| s25303 | 李斯⽂ | ⼥ | 22 | 2 | 北京 | 55 | 82 |
+--------+---------+--------+--------+---------+------------+------+------+
# `3 rows in set (0.00 sec)`
查年龄是18~20的学⽣
mysql> select * from stu where stuage between 18 and 20;
+--------+---------+--------+--------+---------+------------+------+------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+---------+--------+--------+---------+------------+------+------+
| s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL |
+--------+---------+--------+--------+---------+------------+------+------+
1 row in set (0.00 sec)
group by [分组查询]
概念
将查询的结果分组，分组查询⽬的在于统计数据
如果是分组查询，查询字段是分组字段和聚合函数
查询字段是普通字段，只取第⼀个值
group_concat()将同⼀组的数据连接起来
-- 查询男⽣和⼥⽣的各⾃语⽂平均分
mysql> select stusex,avg(ch) '平均分' from stu group by stusex;
+--------+---------+
| stusex | 平均分 |
+--------+---------+
| ⼥ | 72.2500 |
| 男 | 77.0000 |
+--------+---------+
# `2 rows in set (0.00 sec)`
-- 查询男⽣和⼥⽣各⾃多少⼈
mysql> select stusex,count(*) ⼈数 from stu group by stusex;
+--------+------+
| stusex | ⼈数 |
+--------+------+
| ⼥ | 4 |
| 男 | 5 |
+--------+------+
# `2 rows in set (0.00 sec)`
-- 查询每个地区多少⼈
mysql> select stuaddress,count(*) from stu group by stuaddress;
+------------+----------+
| stuaddress | count(*) |
+------------+----------+
| 上海 | 1 |
| 北京 | 3 |
| 天津 | 2 |
| 河北 | 2 |
| 河南 | 1 |
+------------+----------+
# `5 rows in set (0.00 sec)`
-- 每个地区的数学平均分
mysql> select stuaddress,avg(math) from stu group by stuaddress;
+------------+-----------+
| stuaddress | avg(math) |
+------------+-----------+
| 上海 | 76.0000 |
| 北京 | 74.5000 |
| 天津 | 83.0000 |
| 河北 | 72.0000 |
| 河南 | 56.0000 |
+------------+-----------+
# `5 rows in set (0.00 sec)`
group_concat()函数
将同⼀组的值连接起来显⽰
mysql> select group_concat(stuname),stusex,avg(math) from stu group by stusex; +-------------------------------------+--------+-----------+
| group_concat(stuname) | stusex | avg(math) |
+-------------------------------------+--------+-----------+
| 李斯⽂，诸葛丽丽，梅超风,Tabm | ⼥ | 70.5000 |
| 张秋丽，李⽂才，欧阳俊雄，争青⼩⼦,Tom | 男 | 77.2500 |
+-------------------------------------+--------+-----------+
# `2 rows in set (0.00 sec)`
多列分组
mysql> select stuaddress,stusex,avg(math) from stu group by stuaddress,stusex; +------------+--------+-----------+
| stuaddress | stusex | avg(math) |
+------------+--------+-----------+
| 上海 | 男 | 76.0000 |
| 北京 | ⼥ | 82.0000 |
| 北京 | 男 | 67.0000 |
| 天津 | 男 | 83.0000 |
| 河北 | ⼥ | 72.0000 |
| 河南 | ⼥ | 56.0000 |
+------------+--------+-----------+
mysql中select# `6 rows in set (0.00 sec)`
order by [排序]
asc升序 [默认]
desc降序
MariaDB [sel]> select * from grades order by math desc;
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Sunny | 93 | 96 |
| Marry | 95 | 94 |
| Tommy | 98 | 94 |
| Jerry | 97 | 91 |
+-------+---------+------+
# `4 rows in set (0.001 sec)`
-- 按总分降序排列
MariaDB [sel]> select *,chinese+math from grades order by math+chinese desc; +-------+---------+------+--------------+
| name | chinese | math | chinese+math |
+-------+---------+------+--------------+
| Tommy | 98 | 94 | 192 |
| Sunny | 93 | 96 | 189 |
| Marry | 95 | 94 | 189 |
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论