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