sql学习 sqlite

两张表

empno    ename    job    mgr    hiredate    sal    comm    deptno
7333    SMITH    CLERK        1899-12-30    800        50
7499    ALLEN    SALESMAN    7333    1981-02-20    1600    300    30
7521    WARD    SALESMAN    7499    1981-02-22    1250    500    10
7566    JONES    MANAGER    7499    1981-04-02    2975        20

deptno    dname    loc
10    ACCOUNTING    NEW
20    RESERARCH    DALL
30    SALES    CHICA
40    OPERATINS    boston

UPDATE employee SET xxxx='ttttttt' WHERE name='xiaoming'

DELETE FROM employee  WHERE name='xiaoming'
ALTER TABLE emp ADD COLUMN job VARCHAR(9) //增加一列
SELECT ename|| LENGTH(job) from emp   //字符串连接

SELECT * FROM emp WHERE ename LIKE '_A%' //模糊查找

SELECT * FROM emp WHERE deptno IN(20, 30 )

SELECT * FROM emp WHERE deptno Between 20 and 30

SELECT * FROM emp WHERE deptno  >= max(20 , 30)

SELECT * FROM emp WHERE (comm IS NULL) and ( sal > 50 ) and job = 'CLERK'

SELECT * FROM emp WHERE julianday(hiredate) >= julianday('1981-02-21') and sal *12 > 500


SELECT DISTINCT job FROM emp  //删除重复的值

SELECT deptno,sal FROM emp ORDER by sal DESC

SELECT deptno,sal FROM emp ORDER by deptno DESC, sal  //排序


SELECT COUNT(comm), AVG(IFNULL(comm,0)) FROM emp  //聚和函数 AVG COUNT不算null值。

SELECT MAX(sal), deptno FROM emp GROUP BY deptno

where后边不能加聚合函数 ，可以用having，紧接在GROUP BY,过滤分组

SELECT AVG(sal) , job FROM emp GROUP BY job HAVING AVG(sal) > 900
SELECT MAX(sal) , MIN(sal),job FROM emp GROUP BY job HAVING AVG(sal) > 900

执行顺序 1from 表先找右边的表 。2where 从右到左 3 group by 左到右 4  select 少用*

关联查询,内连接
SELECT ename, dname FROM dept, emp WHERE dept.deptno=emp.deptno
//用别名
SELECT ename, dname FROM dept d, emp e WHERE d.deptno = e.deptno AND dname = 'SALES'

SELECT AVG(e.sal), d.dname FROM  emp e JOIN dept d ON  d.deptno = e.deptno  GROUP BY e.deptno HAVING AVG(sal) > 900


外连接
SELECT e.ename ,d.dname  FROM  emp e  LEFT OUTER JOIN dept d ON  d.deptno = e.deptno


自连接
SELECT e.ename ,COUNT(*)  FROM  emp e, emp d  WHERE e.empno = d.mgr GROUP BY e.empno //领导有多少员工

三个表联查，jone的上级在哪一个城市。
SELECT l.loc  FROM  emp e, emp d, dept l   WHERE  d.deptno = l.deptno     and e.mgr = d.empno and e.[ename] = 'JONES'