select * from 表名;
在select后面列前使用distinct可以消除重复的行
elect distinct h_gender from hero;
select * from 表名 where 条件;
mysql> select *from hero;
+------+-----------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+-----------+--------------+---------+----------+------------+----------+--------+
| 1 | 李白 | 青莲剑歌 | 1000 | 1000 | | 1 | 刺客 |
| 2 | 鲁班 | 空中支援 | 950 | 1000 | | 1 | 射手 |
| 3 | 王昭君 | 凛冬已至 | 950 | 950 | | 0 | 法师 |
| 4 | 虞姬 | 树神护佑 | 900 | 950 | | 0 | 射手 |
| 5 | 甄姬 | 洛神降临 | 900 | 900 | | 0 | 法师 |
| 6 | 庄周 | 天人合一 | 850 | 900 | | 1 | 辅助 |
| 7 | 韩信 | 国士无双 | 850 | 850 | | 1 | 刺客 |
| 8 | 孙尚香 | 穷极弩炮 | 800 | 850 | | 0 | 射手 |
| 9 | 孙策 | 长帆破浪 | 800 | 800 | | 1 | 战士 |
| 10 | 公孙离 | 孤鹜断霞 | 750 | 800 | | 0 | 射手 |
+------+-----------+--------------+---------+----------+------------+----------+--------+
10 rows in set (0.00 sec)
mysql> select *from hero where h_attack>900;
+------+-----------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+-----------+--------------+---------+----------+------------+----------+--------+
| 1 | 李白 | 青莲剑歌 | 1000 | 1000 | | 1 | 刺客 |
| 2 | 鲁班 | 空中支援 | 950 | 1000 | | 1 | 射手 |
| 3 | 王昭君 | 凛冬已至 | 950 | 950 | | 0 | 法师 |
| 4 | 虞姬 | 树神护佑 | 900 | 950 | | 0 | 射手 |
+------+-----------+--------------+---------+----------+------------+----------+--------+
5.1、查询h_attack大于950并且h_blood大于900的hero
mysql> select *from hero where h_attack>950 and h_blood>900;
+------+--------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+--------+--------------+---------+----------+------------+----------+--------+
| 1 | 李白 | 青莲剑歌 | 1000 | 1000 | | 1 | 刺客 |
| 2 | 鲁班 | 空中支援 | 950 | 1000 | | 1 | 射手 |
+------+--------+--------------+---------+----------+------------+----------+--------+
5.2、查询h_attack大于950或者h_blood大于900的hero
mysql> select *from hero where h_attack>950 or h_blood>900;
+------+-----------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+-----------+--------------+---------+----------+------------+----------+--------+
| 1 | 李白 | 青莲剑歌 | 1000 | 1000 | | 1 | 刺客 |
| 2 | 鲁班 | 空中支援 | 950 | 1000 | | 1 | 射手 |
| 3 | 王昭君 | 凛冬已至 | 950 | 950 | | 0 | 法师 |
+------+-----------+--------------+---------+----------+------------+----------+--------+
mysql> select *from hero where h_name like '孙%';
+------+-----------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+-----------+--------------+---------+----------+------------+----------+--------+
| 8 | 孙尚香 | 穷极弩炮 | 800 | 850 | | 0 | 射手 |
| 9 | 孙策 | 长帆破浪 | 800 | 800 | | 1 | 战士 |
+------+-----------+--------------+---------+----------+------------+----------+--------+
2、查询名字里面包含‘孙’的
mysql> select *from hero where h_name like '%孙%'
+------+-----------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+-----------+--------------+---------+----------+------------+----------+--------+
| 8 | 孙尚香 | 穷极弩炮 | 800 | 850 | | 0 | 射手 |
| 9 | 孙策 | 长帆破浪 | 800 | 800 | | 1 | 战士 |
| 10 | 公孙离 | 孤鹜断霞 | 750 | 800 | | 0 | 射手 |
+------+-----------+--------------+---------+----------+------------+----------+--------+
3、查询性孙的并且名只有一个字
mysql> select *from hero where h_name like '孙_';
+------+--------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+--------+--------------+---------+----------+------------+----------+--------+
| 9 | 孙策 | 长帆破浪 | 800 | 800 | | 1 | 战士 |
+------+--------+--------------+---------+----------+------------+----------+--------+
例如:查找h_id为1、3、5的英雄
mysql> select *from hero where h_id in(1,3,5);
+------+-----------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+-----------+--------------+---------+----------+------------+----------+--------+
| 1 | 李白 | 青莲剑歌 | 1000 | 1000 | | 1 | 刺客 |
| 3 | 王昭君 | 凛冬已至 | 950 | 950 | | 0 | 法师 |
| 5 | 甄姬 | 洛神降临 | 900 | 900 | | 0 | 法师 |
+------+-----------+--------------+---------+----------+------------+----------+--------+
例如:查询血量在900-950之间的应用
mysql> select *from hero where h_blood between 900 and 950;
+------+-----------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+-----------+--------------+---------+----------+------------+----------+--------+
| 2 | 鲁班 | 空中支援 | 950 | 1000 | | 1 | 射手 |
| 3 | 王昭君 | 凛冬已至 | 950 | 950 | | 0 | 法师 |
| 4 | 虞姬 | 树神护佑 | 900 | 950 | | 0 | 射手 |
| 5 | 甄姬 | 洛神降临 | 900 | 900 | | 0 | 法师 |
+------+-----------+--------------+---------+----------+------------+----------+--------+
查询isdelect没有填写的hero
select *from hero where h_isdelete is null;
为了快速得到统计数据,提供了5个聚合函数
查询hero的总数
mysql> select count(*) from hero;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
查询攻击力最大的值
mysql> select max(h_attack) from hero;
+---------------+
| max(h_attack) |
+---------------+
| 1000 |
+---------------+
查询攻击力最小的值
mysql> select min(h_attack) from hero;
+---------------+
| min(h_attack) |
+---------------+
| 800 |
+---------------+
查询所有hero的攻击力之和
mysql> select sum(h_attack) from hero;
+---------------+
| sum(h_attack) |
+---------------+
| 9000 |
+---------------+
查询所有hero的攻击力平均值
mysql> select avg(h_attack) from hero;
+---------------+
| avg(h_attack) |
+---------------+
| 900.0000 |
+---------------+
select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
mysql> select h_gender as 性别,count(*) from hero group by h_gender;
+--------+----------+
| 性别 | count(*) |
+--------+----------+
| 0 | 5 |
| 1 | 5 |
+--------+----------+
mysql> select h_type as 英雄类型,h_gender as 性别,count(*) as 个数 from hero group by h_type,h_gender;
+--------------+--------+--------+
| 英雄类型 | 性别 | 个数 |
+--------------+--------+--------+
| 刺客 | 1 | 2 |
| 射手 | 0 | 3 |
| 射手 | 1 | 1 |
| 战士 | 1 | 1 |
| 法师 | 0 | 2 |
| 辅助 | 1 | 1 |
+--------------+--------+--------+
having后面的条件运算符与where的相同
例如:查询男英雄的个数
方法一:
mysql> select count(*) from hero where h_gender = 1;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
方法二:
mysql> select h_gender as 性别,count(*) from hero group by h_gender having h_gender=1;
+--------+----------+
| 性别 | count(*) |
+--------+----------+
| 1 | 5 |
+--------+----------+
为了方便查看数据,可以对数据进行排序
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...
mysql> select *from hero where h_gender=1 order by h_attack asc;
+------+--------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+--------+--------------+---------+----------+------------+----------+--------+
| 9 | 孙策 | 长帆破浪 | 800 | 800 | | 1 | 战士 |
| 7 | 韩信 | 国士无双 | 850 | 850 | | 1 | 刺客 |
| 6 | 庄周 | 天人合一 | 850 | 900 | | 1 | 辅助 |
| 1 | 李白 | 青莲剑歌 | 1000 | 1000 | | 1 | 刺客 |
| 2 | 鲁班 | 空中支援 | 950 | 1000 | | 1 | 射手 |
+------+--------+--------------+---------+----------+------------+----------+--------+
当数据量过大时,在一页中查看数据是一件非常麻烦的事情,而且现在很多浏览器也都是分页显示数据,例如:
语法:
select * from 表名 limit start,count
mysql> select *from hero limit 0,5;
+------+-----------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+-----------+--------------+---------+----------+------------+----------+--------+
| 1 | 李白 | 青莲剑歌 | 1000 | 1000 | | 1 | 刺客 |
| 2 | 鲁班 | 空中支援 | 950 | 1000 | | 1 | 射手 |
| 3 | 王昭君 | 凛冬已至 | 950 | 950 | | 0 | 法师 |
| 4 | 虞姬 | 树神护佑 | 900 | 950 | | 0 | 射手 |
| 5 | 甄姬 | 洛神降临 | 900 | 900 | | 0 | 法师 |
+------+-----------+--------------+---------+----------+------------+----------+--------+
5 rows in set (0.00 sec)
mysql> select *from hero limit 5,5;
+------+-----------+--------------+---------+----------+------------+----------+--------+
| h_id | h_name | h_skill | h_blood | h_attack | h_isdelete | h_gender | h_type |
+------+-----------+--------------+---------+----------+------------+----------+--------+
| 6 | 庄周 | 天人合一 | 850 | 900 | | 1 | 辅助 |
| 7 | 韩信 | 国士无双 | 850 | 850 | | 1 | 刺客 |
| 8 | 孙尚香 | 穷极弩炮 | 800 | 850 | | 0 | 射手 |
| 9 | 孙策 | 长帆破浪 | 800 | 800 | | 1 | 战士 |
| 10 | 公孙离 | 孤鹜断霞 | 750 | 800 | | 0 | 射手 |
+------+-----------+--------------+---------+----------+------------+----------+--------+
5 rows in set (0.00 sec)
例子二:
select * from hero limit (n-1)*m,m