SQL语言入门学习，这一篇就够了

1、## SQL 数据操作语言 (DML)
SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。
这些查询和更新语句都来自 SQL 的 DML 部分：

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

2、## SQL 数据定义语言 (DDL)
SQL 的数据定义语言部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。
SQL 中最重要的 DDL 语句:
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

3、## 下面从DML到DDL分别举例各场景的SQL语言应用
以下数据操作使用样例数据来源SQL语言学习样例数据

------------------------------------------------------数据库查询部分--------------------------------------------------------------

–检索单列、多列、所有列
select prod_name from Products;

select prod_id,prod_name,prod_price
from products;

select * from Products;

–检索不同的值
select vend_id from products;

select distinct vend_id
from products;

select distinct vend_id,prod_price
from products; /不能部分使用distinct/

–限制结果
select top 5 prod_name from products; --不同DBMS语句不同

–使用注释
/select prod_id,prod_name,prod_price
from products;/
select prod_name from Products;

–子句（排序数据）
select prod_name
from Products
order by prod_name; --order by 是select语句中最后一条子句

–按多个列排序
select prod_id,prod_price ,prod_name
from products
order by prod_price ,prod_name;

–按列位置排序
select prod_id,prod_price ,prod_name
from products
order by 2,3;

–指定排序方向
select prod_id,prod_price ,prod_name
from products
order by prod_price desc;

select prod_id,prod_price ,prod_name
from products
order by prod_price desc,prod_name;

–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_name
from products
where vend_id <>‘DLL01’;

select vend_id,prod_name
from products
where vend_id !=‘DLL01’;

–范围值检查
select prod_name,prod_price
from products
where prod_price between 5 and 10;

–空值检查
select prod_name
from products
where prod_price is null;

select cust_name
from Customers
where cust_email is null;

–and 操作符
select prod_id,prod_name,prod_price
from products
where vend_id=‘DLL01’ and prod_price <=4;

–or 操作符
select prod_name,prod_price
from products
where vend_id=‘DLL01’ or vend_id=‘BRS01’;

–求值顺序(列出价格为10美元及以上,且由DLL01或BRS01制造的所有产品)
select prod_name,prod_price
from products
where prod_price >=10 and (vend_id=‘DLL01’ or vend_id=‘BRS01’);

select vend_id,prod_name,prod_price
from products
where vend_id=‘DLL01’ or vend_id=‘BRS01’ and prod_price >=10 ; --and优先级比or高

–in操作符
select vend_id,prod_name,prod_price
from products
where vend_id in (‘DLL01’,‘BRS01’); --功能与or相当，且比一组or操作符执行更快

–not操作符
select prod_name
from products
where not vend_id =‘DLL01’; --此处功能与<>相同，与其他操作符组合使用，否定其后跟着的条件

–like操作符、"%"通配符
select prod_id,prod_name
from Products
where prod_name like ‘Fish%’;

select prod_id,prod_name
from Products
where prod_name like ‘%bean bag%’;

select prod_id,prod_name
from Products
where prod_name like ‘F%y’; --"%"不能匹配null

–"“通配符
select prod_id,prod_name
from Products
where prod_name like ‘__ inch teddy bear’; --”"只能匹配一个字符

–"[]"通配符–指定字符集
select cust_contact
from Customers
where cust_contact like ‘[JM]%’
order by cust_contact;

select cust_contact
from Customers
where cust_contact like ‘[^JM]%’
order by cust_contact; --前缀字符^否定

select cust_contact
from Customers
where not cust_contact like ‘[JM]%’
order by cust_contact;

–拼接字段
select vend_name+’(’+vend_country+’)’
from Vendors
order by vend_name;

select rtrim(vend_name)+’(’+rtrim(vend_country)+’)’
from Vendors
order by vend_name; --去掉右边空格,trim(),rtrim()右,ltrim()左

–使用别名
select rtrim(vend_name)+’(’+rtrim(vend_country)+’)’
as vend_title
from Vendors
order by vend_name;

–执行算数计算
select prod_id,quantity,item_price
from OrderItems
where order_num =20008;

select prod_id,
quantity,
item_price,
quantity*item_price as expanded_price
from OrderItems
where order_num =20008;

–文本处理函数
select vend_name,UPPER(vend_name) as vend_name_upcase
from Vendors
order by vend_name; --转换为大写

–soundex()函数：发音比较
select cust_name,cust_contact
from Customers
where cust_contact = ‘Micheal Green’;

select cust_name,cust_contact
from Customers
where soundex(cust_contact) = soundex(‘Micheal Green’);

–日期和时间处理函数
select order_num
from Orders
where DATEPART(YY,order_date)=2012;

/Oracle中实现
select order_num
from Orders
where to_number(to_char(order_date,‘yyyy’))=2012;/

<