sql数据库操作2

SELECT PASSWORD,UPPER(PASSWORD) from user1

将大写转换小写

SELECT PASSWORD,SUBSTR(PASSWORD,2,4) FROM user1

查询2到4的字符
SELECT empno,ename,avg(sal) from emp

查询平均
SELECT deptno from emp GROUP BY deptno

分组
SELECT deptno 编号,sum( sal) 平均工资,max(sal) 最高工资,count(empno) 总人数 FROM emp GROUP BY deptno HAVING avg(sal) > 2000 ORDER BY deptno asc

分组查询并且升降排序
SELECT * from emp WHERE sal >(SELECT avg(sal) FROM emp) ORDER BY sal

SELECT * from(SELECT * from emp) e

SELECT empno from emp  WHERE empno = 7396 or empno=7499 or empno= 7521

SELECT * from emp WHERE empno in (SELECT empno FROM emp WHERE empno=7369 or empno=7499 or empno=7521)

SELECT e.*,d.dname,d.loc from emp e,dept d WHERE e.deptno=d.deptno

连表操作
SELECT e.*, d.deptno,d.loc FROM emp e right JOIN dept d ON e.deptno=d.deptno

SELECT * from emp LIMIT 2,5

SELECT e.*,d.deptno,d.loc from emp e left  join dept d on e.deptno=d.deptno