sql 语句基本使用 笔记

sql 常用函数

SELECT userid,username,`password`,LOWER(username) FROM USER //查询结果username为小写

SELECT userid,username,`password`,UPPER(username) FROM USER //查询结果username为大写

SELECT userid,username,`password`,CONCAT(username,PASSWORD) FROM USER  //查询结果将username和password连接

SELECT userid,username,`password`,LENGTH(PASSWORD) FROM USER //查询结果有password的字段长度

SELECT userid,username,`password`,SUBSTR(PASSWORD,2,1) FROM USER 查询结果password从位置2开始截取一个字符

分组函数
SELECT userid,username,`password`,AVG(PASSWORD) FROM USER //avg求平均数
SELECT userid,username,`password`,MIN(userid) FROM USER //选择最小值

SELECT userid,username,`password`,MAX(userid) FROM USER //选择最大值

SELECT userid,username,`password`,SUM(userid) FROM USER //求sum的总和

SELECT userid,username,`password`,count(userid) FROM USER //求总数

对分组的过滤

SELECT username from user GROUP BY username //根据username 来分组，相同名字的为一组

SELECT username,AVG(`password`) from user GROUP BY username//根据username分组，求出每一组password的平均值

SELECT username,sum(`password`) from user GROUP BY username//根据username分组，求出每一组password的总和
SELECT username,count(`password`) from user GROUP BY username//根据username分组，求出每一组password的个数
SELECT username,min(`password`) from user GROUP BY username//根据username分组，求出每一组password的最小值
SELECT username,max(`password`) from user GROUP BY username//根据username分组，求出每一组password的最大值
SELECT * from user WHERE PASSWORD in (SELECT PASSWORD from user where PASSWORD=12345 or PASSWORD=123456 or PASSWORD=13456)
链表语句：

SELECT * from user WHERE PASSWORD in (SELECT PASSWORD from user where PASSWORD=12345 or PASSWORD=123456 or PASSWORD=13456)
SELECT username,max(`password`),AVG(`password`) from user GROUP BY username HAVING AVG(`password`) >14444//where(是主语句) 与 having （一般用于groupby 和order by 后面）
SELECT username,max(`password`),AVG(`password`) from user GROUP BY username HAVING AVG(`password`) >14444 order by max(`password`) asc 
子查询：
SELECT * from user where password>(SELECT AVG(PASSWORD) from `user`)//子查询，查询出来的数据为一张临时(是不存在的)的表
例：SELECT * from (SELECT * from `user`) u//一定要取别名，不然会报错

SELECT * from user JOIN person on `user`.userid=person.userid//两张表都存在的（left:左边张表有的显示出来；right:右边张表有的显示出来）

分页：
SELECT*from user ORDER BY sex limit 0,3//第一个参数为起始位置，第二条为查询条数