按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 from table as t
数据库别名-列
select id as t from table
select name as n,country as c from table
select startdate as kaishi,enddate as jieshu from `new-parking-manage`.p_ipass_car
查询两个表中id相同且name指定,并对表取别名的数据
SELECT v.id, v.parkname, i.id, i.username FROM `new-parking-manage`.p_vip_car AS v, `new-parking-manage`.p_ipass_car AS i WHERE v.carno = i.carno and i.mchid = 157
连接两个表重合部分查询-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