sql语句学习

sql
按id倒序查询前5条数据
select top 5 * from table order by id desc

查询前百分之50
select top 50 percent * from table

查询符合多个条件
select * from table where country = 'usa' or country = 'cn'

插入行、列
insert into table values(value1,value2)
insert into table(id,name,no) values(5,'abdc',13)

更新表格数据
update table set no = 4,name = aaa where id = 3

查询where的多个值
select * from table where country in (usa,cn,ph,jan)

查询在两个数值之间的数据
select * from table where id between 1 and 10
select * from table where (id between 1 and 20) and country not in (usa,cn,ph,jan)

数据库别名-表
select id frome table as t

数据库别名-列
select id as t frome table
select name as n,country as c from table
select startdate as kaishi,enddate as jieshu from ipass_car

查询两个表中id相同且name指定，并对表取别名的数据
SELECT v.id, v.parkname, i.id, i.username FROM p_vip_car AS v,p_ipass_car AS i WHERE v.carno = i.carno and i.mid = 12

连接两个表重合部分查询-inner join
SELECT a.name, b.count, b.date FROM a INNER JOIN b ON a.id=b.site_id ORDER BY b.count;

a所有与b其中等于a的部分查询-left join
SELECT a.name, b.count, b.date FROM a LEFT JOIN b ON a.id=b.site_id

a其中等于b的部分与b所有数据查询-right join
SELECT a.name, b.count, b.date FROM a RIGHT JOIN b ON a.id=b.site_id

sql执行顺序
(1) FROM
(2) ON
(3) JOIN
(4) WHERE
(5) GROUP By
(6) CUBE|ROllUP
(7) HAVING
(8) SELECT
(9) DISTINCT
(10) ORDER BY
(11) LIMIT