《SQL必知必会》随书学习笔记

此为本人随书学习笔记：

1.sql不区分大小写，多条语句可用 ； 分号分隔，单条语句可不用分号
2.select
第一个被检索的行是第0行，
select默认检测所有行

第一课：
USE mydb1;
–检索表products的prod_name列
SELECT prod_name FROM products;

–检索多列
SELECT prod_id,prod_name FROM products;

–检索所有列
SELECT * FROM products;

–只返回不同的值
SELECT DISTINCT vend_id FROM products;
SELECT DISTINCT vend_id,prod_price FROM products;

–返回不少于一定数量行，SQL SERVER和Access:SELECT TOP 5 prod_name FROM products;
–DB2:SELECT prod_name FROM products FETCH FIRST 5 ROWS ONLY;
–Oracle:SELECT prod_name FROM products WHERE ROWNUM <= 5
–各数据库管理系统语句不同，下面是mysql/mariaDB/postgreSQL/SQLite
SELECT prod_name FROM products LIMIT 3;

–指定从哪行开始检索的多少行数，从第3行开始返回此列4行数据
SELECT prod_name FROM products LIMIT 4 OFFSET 3;

第二课：对检索的数据排序

–排序 SELECT ORDER BY

–对prod_name按字母排序,应保证ORDER BY 子句是最后的子句
SELECT prod_name FROM products ORDER BY prod_name;

–按多个列排序,检索三个列，对两个列排序先price再NAME,也就是price相同时才对NAME排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;

–按列的位置排序，也就是按某一列来排序,下面main是按第SELECT 检索的
–第2、3列排序,还可以使用混合列名和相对位置排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY 2,3;

–指定排序方向，默认升序，使用DESC 降序，DESC–>descending,ASC-ascending
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC;

–DESC 只对在它前面的那个列名有效,即只对prod_price有效
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC ,prod_name;

第三课：WHERE 数据过滤

–WHERE 数据过滤,
–在同时使用WHERE 和ORDER BY 时应该让ORDER BY 位于WHERE后面
SELECT prod_name,prod_price FROM products WHERE prod_price = 3.49;

SELECT prod_name,prod_price FROM products WHERE prod_price < 10;

SELECT vend_id,prod_price FROM products WHERE vend_id <> ‘DLL01’
SELECT vend_id,prod_name FROM products WHERE vend_id != ‘DLL01’;

–BETWEEN AND
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

–WHERE 子句操作符
/*,有些操作符并非左右DBMS都支持
= 等于，<>和!=都是不等于，<,<=,>,>=,
!