**
sql学习随笔1–简单查询
**
若无特殊说明,以下的默认环境都是SQL Server
- SELECT语句基本结构:
SELECT select-list
[INTO new_table]
FROM table-name
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_exception [ASC|DESC]]
NOTE: - select_list:指定要返回的列,多个列用逗号分开,可用通配符“*”;
- INTO new_table:创建新表并插入,new_table为新表名;
- FROM table_ name:指定要查询的表;
- WHERE search_condition:指定查询条件;
- GROUP BY group_by_expression:根据group_by_expression将查询的结果分组;
- HAVING search_condition:对FROM,WHERE,GROUP BY等创建的中间结果限定条件;
- ORDER BY order_exception [ASC|DESC]:根据条件排序,ASC为顺序,DESC为逆序;
别名
SELECT name "名字" FROM table1;
SELECT name AS "名字" FROM table1;
/*在查询时将字段名‘name’用“名字”代替;*/
删除重复数据
/*SELECT [DISTINCT|ALL] select_list FROM table_name;*/
SELECT DISTINCT name,number FROM table1;
/*在SELECT语句中只能使用一次DISTINCT关键字,且必须放在第一个字段名的前面*/
限制查询结果
/*SQL Server*/
/*SELECT TOP n FROM table;*/
SELECT TOP 5 name,number FROM table1;
/*查询前五条数据*/
/*MySQL*/
SELECT name ,number FROM table1 LIMIT 5;/*查询前5条*/
SELECT name ,number FROM table1 LIMIT 2,5;/*查询从第3条开始的5条数据(下标从0开始)*/
SELECT name ,number FROM table1 LIMIT 2 OFFSET 3;/*查询从第3+1条开始的2条数据*/
/*Oracle*/
SELECT name,number FROM table1 WHERE ROWNUM <=5;
/*查询前五条*/