1. 查詢user表所有數(shù)據(jù)：
select * from user
2. 查詢user表中name字段所有數(shù)據(jù)：
select name from user
3. 查詢user表中符合條件的所有name字段：
select name from user where password in (he,hong,qian)
4. 增加一條數(shù)據(jù)：
insert into user (id,name,passowrd) values (1,hehongqian,123456)
5. 刪除一條數(shù)據(jù)：
delete from user where id=1
6: 修改一條數(shù)據(jù)
update user set name=何紅乾 where id=1
7. 分頁查詢（page：頁數(shù)-1猾警，offset：一頁的數(shù)據(jù)）
select * from user limit #{page},#{offset}
8. 分頁排序查詢 （page：頁數(shù)-1羽利，offset：一頁的數(shù)據(jù)系谐，根據(jù)name排序）
select * from user where 1=1 order by time desc limit #{page},#{offset}
9. 根據(jù)傳過來的字段amount進行分頁排序查詢：
select * from user where 1=1 order by
<if test="amount == 'desc'">
name desc
</if>
<if test="amount == 'asc'">
name asc
</if>
limit #{page},#{offset}
10. 批量查詢
? ? select * from user where name in
? ? <foreach item="name" collection="list" separator="," open="(" close=")" index="">
? ? ? #{name}
? ? </foreach>
11. 批量刪除
? ? delete from user where id in
? ? <foreach item="id" collection="list" separator="," open="(" close=")" index="">
? ? ? #{id}
? ? </foreach>
12. 批量修改
? ? ? update user set name='hehongqian' where id in
? ? <foreach item="id" collection="list" separator="," open="(" close=")" index="">
? ? ? #{id}
? ? </foreach>
13. 查詢最前面2行數(shù)據(jù)
select top 2 * from user
14. like查詢 (%代表缺少的字符,可以是很多字母組成）
select * from user where name like 'h%'
select * from user where name like '%h%'
15.? ”_“通配符查詢（_代表缺少的一個字母）
select * from user where name like '_on'
16.? 查詢name以H,K,N開頭的數(shù)據(jù)
select * from user where name like '[HKN]%'
17. 查詢name不以H,K,N開頭的數(shù)據(jù)
select * from user where name like '[!HKN]%'
18. 查詢指定范圍的數(shù)據(jù)（左包右不包）
select * from user where name between 'he' and 'qian'
19. 查詢指定范圍外的數(shù)據(jù)
select * from user where name not between 'he' and 'qian'
20. 指定別名查詢
select * from user u where u.name='he' and u.name='hong' and u.name='qian'
21. 多表聯(lián)查（user表陋守，person表）
select user.name,person.name from person,user where person.id=user.id
22.? join 內(nèi)連接多表聯(lián)查(person表中沒有沒有匹配user表的id式撼，就不會返回該行數(shù)據(jù)）
select user.name,person.name from user inner join orders on user.id=person.id order by user.name
23. join 左連接多表查詢（person表中沒有匹配user表的id，也會返回該行數(shù)據(jù)，以user表為主艳馒，不對應(yīng)的字段為null）
select user.name,person.name where user left join person on user.id=person.id order by user.name
24. join 右連接多表查詢 （person表中沒有匹配user表的id拦惋，也會返回該行數(shù)據(jù)匆浙，以person表為主，不對應(yīng)的字段為null）
select user.name,person.name where user right join person on user.id=person.id order by user.name
25. join 全連接多表查詢 （只要有一個id存在厕妖，就返回該行數(shù)據(jù)）
select user.name,person.name where user full join person on user.id=person.id
26. 多表合并查詢（返回所有數(shù)據(jù)首尼，包括重復(fù)的）
select name from user union all select name from person
27. 多表合并查詢（返回不重復(fù)的數(shù)據(jù)）
select name from user union select name from person
28.? 從一個表將指定數(shù)據(jù)插入到另一個表
select * into new_user from user
29. 將另一個數(shù)據(jù)庫的user表數(shù)據(jù)插入到user表中
select * into user in 'aa.mdb' from user
30. 查詢表中某個字段最大值,取名為max_id
select max(id) max_id from user
31. 查詢表中某個字段最小值
select min(id) from user
32. 查詢表中最后一行某個字段的值
select last(name) from user
33. 查詢表中第一行某個字段的值
select first(name) from user
34. 查詢表中某個字段的列總和
select sum(id) from user
35. 查詢所有不同name的在表中某個字段的總數(shù)量
select name,sum(id) from user group by name
36. 查詢所有不同name的在表中某個字段的總數(shù)量( 并且總數(shù)量小于200）(having出現(xiàn)是由于where和合計函數(shù)不能同時使用）
select name,sum(id) from user group by name having sum(id)<200
37. 將name字段的值改成大寫
select ucase(name) as name,password from user
38. 將name字段的值改成小寫
select lcase(name) as name,password from user
39. 從name字段中提取前三個字符
select mid(name,1,3) as name from user
40. 獲取name字段長度
select len(name) from user
41. 將A表中price舍去小數(shù)位
select round(price,0) as price from A
42. 獲取當(dāng)前數(shù)據(jù)庫時間
select now() from user
43. 格式化查詢的數(shù)據(jù)(A表：name String，price String）
select name ,price, format(now(),'YYYY-MM-DD') as now_time from A
