SQL语句

查询表的关键字

select(查询一个关键字或多个关键字)

from（查询某个表）

order by（排序）

asc（在order by后面加asc变成升序）

desc（在order by后面加desc变成倒排序）

len()（进行一个字符串的长短排序）

select first_name,last_name,city
from sales.customers
order by 2,3

(order by 2,3 (根据select的关键字进行哪个排序))

select top 10 percent first_name,

（top 10 （查询前十条数据））
（top 10 percent（查询出前的百分之十的数据percent（百分比）））

select top 10 with ties product_name,list_price
from production.products
order by list_price

（with ties 把最后一名的相关数据都匹配出来）

select product_id,list_price
from production.products
order by list_price
offset (4-1)*10 rows
fetch next 10 rows only
(进行分页查询)

distinct (它从结果集中删除列中的重复值。)

select distinct phone 
from sales.customers;
( 查询sales.customers进行phone的一个去重复)

select *
from sales.customers
where phone is not null
(where phone is not null 表示查询phone不为null)

select *
from sales.customers
where phone in null
(where phone in null 表示查询phone为null)

select *
from sales.customers
where state='CA'and city='Campbell'

and(查询时数据要满足两个（state和city）才显示数据出来)

select * from production.products
where list_price>=3000 and list_price < 5000
（查询区域范围3000到5000）

select list_price,model_year
from production.products
where list_price>3000 or model_year=2018
order by list_price desc

(or 满足其中一个条件的任何产品都包含在结果集中)

select product_name,brand_id,list_price
from production.products
where brand_id=3 or brand_id=4 and list_price>100
order by brand_id desc
(brand_id等于3或者等于4 同时满足list_price>100)

select *
from production.products
where list_price between 3000 and 5000
（between 指一个范围值,大于3000和小于5000）

select
order_id,customer_id,order_date
from sales.orders
where order_date between '20170115' and '20170117'
order by order_date

（查询日期范围，查出结果20170116到20170117）

select *from production.products
where list_price=4499.99
or list_price=3499.99
or list_price=3599.99
order by list_price desc

select *from production.products
where list_price in (4499.99,3499.99,3599.99)

(筛选数据，相同可以得出结果)

select * from sales.customers
where first_name like 'a%'
(%是一个通配符表示零个或多个，匹配a开头的不区分大小写)

select * from sales.customers
where first_name like '_a%'
( Like 查找其值包含字符串的行,匹配第二个为a，下划线是任意一个字符)

select * from sales.customers
where first_name like '[abcde]%'
（a到e开头的后面任意字符）

select first_name
from sales.customers
where first_name like '[^a-c]%'
(不在列表内的任意开头字符)

select first_name as 名称
from sales.customers
where first_name like '[^a-c]%'
(as 名称 是列表的别名)

select * from production.products p
inner join production.brands b
on p.brand_id=b.brand_id
(内连接表)

--查询未销售给任何客户的产品
select * from sales.orders;--订单表
select * from sales.customers;--客户
select * from sales.order_items--订单记录
select * from production.products;--产品

select * from production.products t1
left join sales.order_items t2
on t1.product_id=t2.product_id
where t2.item_id is null
order by t1.product_id

(左连接 查询产品表的ID，然后在订单记录表里面查询产品ID是否存在)

select * from sales.order_items t2
right join production.products t1
on t1.product_id=t2.product_id
where t2.item_id is null
order by t1.product_id
(右连接)

一：select * from sales.customers
cross join sales.orders

二：select * from sales.customers c,sales.orders o
where c.customer_id=o.customer_id

三：select *from sales.customers c 
inner join sales.orders o on c.customer_id>0
(笛卡尔积)