检索数据Select语句
2019-07-19 09:34:30 26 举报AI智能生成
SQL-Select基础
作者其他创作
大纲/内容
两个顺序
关键字顺序
SELECT...FROM...WHERE...GROUP BY...HAVING...ORDER BY...
Select执行顺序
FROM > WHERE > GROUP BY > HAVING > SELECT 字段 > DISTINCT > ORDER BY > LIMIT
示例
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7
提升Select查询效率
对数据表进行探索
可以使用Select *
使用必要列名
减少表查询的网络传输量
生产环境
基础语法
Select * from xxx
起别名
Select name as n from xxx
查询常数
select ‘王者荣耀’ as platform, name from heros
字符串用单引号
数字不需要引号
去除重复行
distinct
distinct
select distinct attack_range from heros
放在所有列之前
对后面所有列名的组合进行去重
排序检索
order by
后跟多个列名
排序顺序
排序按照列次序排序
默认ASC
位置
select语句最后
示例
显示英雄名称及最大生命值，按照生命值从高到低排序
select name, hp_max from heros Order by hp_max DESC
select name, hp_max from heros Order by hp_max DESC
显示英雄名称及最大生命值，按照第一排序最大法力从低到高，第二排序按照生命值从高到低
select name, hp_max from heros order by mp_max, hp_max DESC
select name, hp_max from heros order by mp_max, hp_max DESC
约束返回结果数量
MySql, SQLite, MariaDB, PostgreSQL
LIMIT
返回5条以上记录
select name, hp_max from heros order by hp_max DESC LIMIT 5
select name, hp_max from heros order by hp_max DESC LIMIT 5
SQL Server, Access
Top
返回5条以上记录
select TOP 5 name, hp_max from heros order by hp_max DESC
select TOP 5 name, hp_max from heros order by hp_max DESC
DB2
FETCH FIRST 5 ROWS ONLY
返回5条以上记录
select name, hp_max from heros order by hp_max DESC FETCH FIRST 5 ROWS ONLY
select name, hp_max from heros order by hp_max DESC FETCH FIRST 5 ROWS ONLY
Oracle
ROWNUM进行统计行数
返回5条以上记录
select name, hp_max from heros where ROWNUM <= 5 order by hp_max DESC
select name, hp_max from heros where ROWNUM <= 5 order by hp_max DESC
0 条评论
下一页
