SQL重拾

SQL分为两个部分：数据操作语言DML和数据控制语言DDL
DML主要包括 （增删改查）
insert into：插入数据
delete： 删除数据
update：更新数据
select ： 查询数据
DDL主要包括：
create database：创建新数据库
alter database ：修改数据库
create table：创建新表
alter table：修改表
drop table：删除表
create index ：创建索引
drop index ：删除索引

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

select 列名 from 表名称 大小写不敏感
select LastName from person
(*) 选取所有列

select distinct 列名称 from 表名称 -- 关键词distinct 用于返回唯一不同的值

select 列名称 from 表名称 where 列 运算符 值
运算符包括(= <> > < >= <= between like)

and 和 or 运算符可在where 子语句中把两个或多个条件结合起来。

order by 语句：用于指定根据指定的列对结果进行排序，默认升序asc。 降序使用desc 关键字。

insert into 表名称 values(值1，值2，...) -- 插入行
insert into table_name (列1，列2，...) values (值1，值2，...) --插入列

update 表名称 set 列名称 = 新值 where 列名称 = 某值
e.g. update person set firstname = 'bing' where lastname='xu'

delete from 表名称 where 列名称 = 值
delect from person where latename = 'xu' --删除一行

在不删除表的情况下删除所有行，表结构、属性、索引完整。
delete from table_name
或delete * from table_name

top
select top number | percent column_name(s) from table_name
mysql:select * from persons limit 5
oracle:select * from person where rownum <= 5
sql:select top 2 * from persons
select top 50 percent * from persons

%为通配符。
select * from persons where city like 'B%'--以B开始
select * from person where city like '%j' --以j结尾
select * from person where city like '%lon%' --包含ion
select * from person where city not like '%lon%' --不包含ion

% 替代一个或多个字符
-   仅替代一个字符
[charlist] 字符中的任何单一字符
[^charlist]或[!charlist] 不在字符列中的任一字符

in
select * from persons where lastname in ('bing','ting')
between
select * from persons where lastname between 'adams' and 'carter'

Alias
select po.id,p.lastname,p.firstnam from person as p where p.lastname = 'adam' and p.firstname = 'john'
select lastname as family ,firstname as Name from persons

join
select p.lastname,p.firstname,o.orederno 
from 
person as p 
inner join
order as o
on p.id_p == 0.id_p
order by p.lastename
inner join 与 join 相同
select person.lastnam,person.firstname,order.orderno
from persons
inner join orders
on person.id_p = order.id_p
order by person.lastname

left join
select person.lastname,person.fristname,order.orderno
from persons
left join order
on person.id = order.id
order by person.lastname

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

select customer,sum(orderprice) from orders 
select customer,sum(orderprice) from orders  group by customer,...

未完，待续