sql语句例子2

2017/9/6
TDD:测试驱动开发
teambition:团队协作开发工具
given一个情景 when一个条件 then一个结果
csdn：每天更新笔记
trello:看板[卡片列表]
个人信息[三级联动下拉列表,js]
framset
下午：
database
noSql:不要sql语句的非关系型数据库,代表mongoDB,
OceanBase:海量数据高性能
cmd命令：
net start mysql
net stop mysql
show databases:显示所有数据库
use databasename:使用某个数据库
show tables:显示某个数据库中的表
oracle:
sql语言分类：


Power Designer：数据库设计
flag:标记删除（字段标记）
修改：UPDATE user set password='pwd',sex=1 where flag=1 and sex=2
修改多个字段用逗号间隔，where后面的条件用and连接.
delete:DELETE from user where userId=2 and username='躲喵喵'
查询语句：
select * from user查询所有字段
SELECT username,password from user查询某几个字段
给查询的字段取别名：
SELECT username as 用户名,password as 密码 from user
去掉某一列的重复值：distinct
SELECT DISTINCT flag as 状态码 from user
where条件：
不等于
SELECT username from user where flag<>0
SELECT username,password from user where flag!=0
like:
like如果不配合%和_，否则就是等于
%a:以a结尾
SELECT * from user where password LIKE '%7'
SELECT * from user where password LIKE '%p%'
_a:a前面占一个字符，即第二个字符是a
SELECT * from user where password like '_p%'
SELECT * from user where password like '__3%'
SELECT * from user where password like '__%'密码至少2位
SELECT * from user where password like '%\%%'语句中有百分号的
SELECT * from user where password like '%\\%%'
SELECT * from user where username like '%\雪%%' ESCAPE '雪'汉字


逻辑条件复合 not and or
SELECT * from user where username not like '%snow%'
SELECT * from user where sex=2 or flag=0
in关键字：
SELECT * from user where userId IN(1,2,3)
SELECT * from user where userId not IN(2,3)
SELECT * from user where flag not in(1)




对查询结果排序:
倒序：
SELECT * from user where userId not in(2,3) ORDER BY userId DESC
按两个字段排序：
SELECT * from user where userId not in(1,3) ORDER BY sex DESC,userId DESC
先按照第一个条件排序，满足后内部相同的再按第二个条件排序




2017/9/7
改变字段大小写：
SELECT userId,LOWER(name) from userinfo
SELECT LOWER(password) as '密码' from userinfo
SELECT UPPER(password) as '密码' from userinfo
截取字段
SELECT userId,SUBSTR(name,1,3) from userinfo
SELECT userId,SUBSTR(str,start,len) from userinfo
分组函数（针对组）
平均值：SELECT AVG(sal) from emp
最大值：SELECT MAX(sal) from emp;
最小值：
SELECT MIN(sal) from emp;
求和：
SELECT SUM(sal) from emp;
计算个数：
SELECT COUNT(ename) from emp;
SELECT COUNT(job) from emp;
对分组过滤条件：
SELECT deptno,MAX(sal) from emp GROUP BY deptno;
SELECT job from emp GROUP BY job;


SELECT deptno,MAX(sal) 最高工资,SUM(sal) 所有人总工资,AVG(sal) as '平局工资',COUNT(empno) as '总人数' from emp GROUP BY deptno;
组条件having:(类似针对个人的where)
SELECT deptno,MAX(sal) as '最高工资',SUM(sal) as '所有人总工资',AVG(sal) as '平局工资',COUNT(empno) as '总人数' from emp GROUP BY deptno HAVING MAX(sal)>2900;
SELECT deptno,MAX(sal) 最高工资,SUM(sal) 所有人总工资,AVG(sal) as '平局工资',COUNT(empno) as '总人数' from emp GROUP BY deptno HAVING 最高工资>2900;
SELECT deptno,MAX(sal) 最高工资,SUM(sal) 所有人总工资,AVG(sal) as '平局工资',COUNT(empno) as '总人数' from emp GROUP BY deptno HAVING 最高工资>2900 ORDER BY deptno DESC;


子查询：（嵌套查询）结果：一张临时的表
SELECT AVG(sal) from emp;
SELECT * from emp where sal>(SELECT AVG(sal) from emp) ORDER BY sal DESC;


SELECT * from (SELECT * from emp) e;
SELECT * from (SELECT * from emp LIMIT 5) as e LIMIT 3;


SELECT empno from emp;
SELECT empno from emp WHERE empno=7369 OR empno=7499 OR empno=7782;
SELECT * from emp where empno in (SELECT empno from emp WHERE empno=7369 OR empno=7499 OR empno=7782);
SELECT * from emp where empno in (SELECT empno from emp where empno NOT IN (7369,7499));


#连表查询（多表查询,同时查询两个表，两表有一个变量关联）
#92年语法
SELECT e.*,d.dname from emp e,dept d WHERE e.deptno=d.deptno;
SELECT e.*,d.dname,d.loc from emp e,dept d WHERE e.deptno=d.deptno;
#99年语法：
#表1 Inner join 表2 ON 关联条件
SELECT e.*,d.dname FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
#左边有的就出现
SELECT e.*,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;
#右边有的就显示
SELECT e.*,d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;


#分页
#6~8条记录，改变limit后面的值，每次访问数据库
#另：PreparStatement
SELECT * FROM emp LIMIT 5,3;