一些常用的sql语句:
-
查询user表所有数据:
select * from user -
查询user表中name字段所有数据:
select name from user -
查询user表中符合条件的所有name字段:
select name from user where password in (he,hong,qian) -
增加一条数据:
insert into user (id,name,passowrd) values (1,hehongqian,123456) -
删除一条数据:
delete from user where id=1 -
修改一条数据:
update user set name=何红乾 where id=1 -
分页查询(page:页数-1,offset:一页的数据)
select * from user limit #{page},#{offset} -
分页排序查询 (page:页数-1,offset:一页的数据,根据name排序)
select * from user where 1=1 order by time desc limit #{page},#{offset} -
查询最前面2行数据
select top 2 * from user -
like查询 (%代表缺少的字符,可以是很多字母组成)
select * from user where name like ‘h%’
select * from user where name like ‘%h%’ -
”_“通配符查询(_代表缺少的一个字母)
select * from user where name like ‘_on’ -
查询name以H,K,N开头的数据
select * from user where name like ‘[HKN]%’ -
查询name不以H,K,N开头的数据
select * from user where name like ‘[!HKN]%’ -
查询指定范围的数据(左包右不包)
select * from user where name between ‘he’ and ‘qian’ -
查询指定范围外的数据
select * from user where name not between ‘he’ and ‘qian’ -
指定别名查询
select * from user u where u.name=‘he’ and u.name=‘hong’ and u.name=‘qian’ -
多表联查(user表,person表)
select user.name,person.name from person,user where person.id=user.id -
join 内连接多表联查(person表中没有没有匹配user表的id,就不会返回该行数据)
select user.name,person.name from user inner join orders on user.id=person.id order by user.name -
join 左连接多表查询(person表中没有匹配user表的id,也会返回该行数据,以user表为主,不对应的字段为null)
select user.name,person.name where user left join person on user.id=person.id order by user.name -
join 右连接多表查询 (person表中没有匹配user表的id,也会返回该行数据,以person表为主,不对应的字段为null)
select user.name,person.name where user right join person on user.id=person.id order by user.name -
join 全连接多表查询 (只要有一个id存在,就返回该行数据)
select user.name,person.name where user full join person on user.id=person.id -
多表合并查询(返回所有数据,包括重复的)
select name from user union all select name from person -
多表合并查询(返回不重复的数据)
select name from user union select name from person -
从一个表将指定数据插入到另一个表
select * into new_user from user -
将另一个数据库的user表数据插入到user表中
select * into user in ‘aa.mdb’ from user -
查询表中某个字段最大值,取名为max_id
select max(id) max_id from user -
查询表中某个字段最小值
select min(id) from user -
查询表中最后一行某个字段的值
select last(name) from user -
查询表中第一行某个字段的值
select first(name) from user -
查询表中某个字段的列总和
select sum(id) from user -
查询所有不同name的在表中某个字段的总数量
select name,sum(id) from user group by name -
查询所有不同name的在表中某个字段的总数量( 并且总数量小于200)(having出现是由于where和合计函数不能同时使用)
select name,sum(id) from user group by name having sum(id)<200 -
将name字段的值改成大写
select ucase(name) as name,password from user -
将name字段的值改成小写
select lcase(name) as name,password from user -
从name字段中提取前三个字符
select mid(name,1,3) as name from user -
获取name字段长度
select len(name) from user -
将A表中price舍去小数位
select round(price,0) as price from A -
获取当前数据库时间
select now() from user -
格式化查询的数据(A表:name String,price String)
select name ,price, format(now(),‘YYYY-MM-DD’) as now_time from A