select(查询一个关键字或多个关键字)
from(查询某个表)
order by(排序)
asc(在order by后面加asc变成升序)
desc(在order by后面加desc变成倒排序)
len()(进行一个字符串的长短排序)
distinct(去重复)
union(有一个去重复的效果)
union all(不去重复效果)
inner join (内连接)
left join(左连接)
right join (右连接)
insert into (向表中插入数据)
set IDENTITY_INSERT sales.promotions ON;(想指定promotion_id 数,就要进行上面语句进行打开)
set IDENTITY_INSERT sales.promotions OFF;(成功插入Id数之后,要进行一个关闭操作)
delete from sales.visits;(删除表里面的数据)
truncate table sales.visits;(完全删除数据,连ID也删除)
select @@IDENTITY (要紧跟着错误的语言查询)
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
(笛卡尔积)