1.表的基本操作
部门表：
//部门表CREATE TABLE DEPT(DEPTNO int(2) not null PRIMARY KEY, //部门编号DNAME VARCHAR(14) , //部门名称LOC VARCHAR(13) //办公地点) ;
雇员表：
//雇员表CREATE TABLE EMP(EMPNO int(4) not null PRIMARY KEY,//员工编号ENAME VARCHAR(10), //员工姓名JOB VARCHAR(9), //工作岗位MGR int(4), //上司编号HIREDATE DATE, //入职日期SAL float(7,2), //工资COMM float(7,2), //奖金【佣金】DEPTNO int(2), //所属部门编号CONSTRAINT FK_DEPTNO foreign key(deptno) REFERENCES DEPT(deptno));
工资等级表：
//工资等级表CREATE TABLE SALGRADE(GRADE int, //级别LOSAL float, //最低工资HISAL float ); //最高工资
向表中插入数据：
INSERT INTO DEPT VALUES(10,'ACCOUNTING','NEW YORK');INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');INSERT INTO DEPT VALUES(30,'SALES','CHICAGO');INSERT INTO DEPT VALUES(40,'OPERATIONS','BOSTON');INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-2-4',2975,NULL,20);INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-7-13',3000,NULL,20);INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-6-13',1100,NULL,20);INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1982-1-13',1300,NULL,10);INSERT INTO SALGRADE VALUES (1,700,1200);INSERT INTO SALGRADE VALUES (2,1201,1400);INSERT INTO SALGRADE VALUES (3,1401,2000);INSERT INTO SALGRADE VALUES (4,2001,3000);INSERT INTO SALGRADE VALUES (5,3001,9999);
2.对表的一些操作命令
1.select * from emp;
2.条件查询
select * from emp where deptno=20;
3.多条件
select * from emp where deptno=20 and sal>2000;
部门20工资大于2000或者小于1000的
select * from emp where (sal>2000 or sal<1000) and deptno=20;
查询每个人的编号，姓名,年工资
select empno,ename,sal*12 from emp;
4.查询每个人的编号，姓名,月收入
NULL值的处理
select empno,ename,sal+ifnull(comm,0) from emp;
5.查询所有没有奖金的员工
select * from emp where comm is null;
6.查询所有有奖金的员工
select * from emp where comm is not null;
7.查询员工工资不为3000的员工
select * from emp where sal != 3000;select * from emp where sal <> 3000;
8.查询工资为1250,3000,5000的员工
select * from emp where sal=1250 or sal=3000 or sal=5000;select * from emp where sal in (1250,3000,5000);
9查询工资不为1250,3000,5000的员工
select * from emp where sal not in (1250,3000,5000);
10. 别名查询
给字段取别名
select empno as 编号,ename as 姓名,sal as 工资 from emp;select empno 编号,ename 姓名,sal 工资 from emp;
给表取别名
select empno 编号,ename 姓名,sal 工资 from emp as dd;
11.模糊查询
%表示n个字符 ， n是0到多个，_表示一个字符
select * from emp where ename='JONES';
查询姓名中包含字母'M'的,最常用的场景
select * from emp where ename like '%M%';
查询姓名以M开头的
select * from emp where ename like 'M%';
查询以J开头，以S结尾的
select * from emp where ename like 'J%S';
查询第二个字母是A的
select * from emp where ename like '_A%';
12.对查询的结果集排序
order by 默认是升序排序,按照sal从低到高排序
select * from emp order by sal;select * from emp order by sal asc;
按照工资从高到低排序 降序
select * from emp order by sal desc;
13.去掉结果集中的重复数据
结果集中所有的列数据都相同，就叫重复
select mgr from emp;//查询所有的管理人员的编号select distinct mgr from emp;
//两列的值都一样，才叫重复
select distinct mgr,deptno from emp;
14.聚合函数
count() 结果集中的行数sum() 对结果集中某一列的数据求和max() 获得该列的最大值min() 获得改列的最小值avg() 求该列的平均值
查询 部门10有多少员工
select count(empno) from emp where deptno=10;
查询部门10的员工总的月工资
select count(sal) from emp where deptno=10;
查询部门10的最高工资和最低工资
select max(sal),min(sal) from emp where deptno=10;
统计部门10的平均工资
select avg(sal) from emp where deptno=10;
15 分组查询 group by
统计每个部门分别有多少员工
select deptno,count(empno) from emp group by deptno;
分组查询的字段必须是和分组条件保持一致
empno和分组条件冲突，没有意义，Oracle中会出错
select empno,deptno,count(empno) from emp group by deptno;
统计每个部门的总工资，最高工资，最低工资和平均工资
select deptno,sum(sal),max(sal),min(sal),avg(sal) from emp group by deptno;
16.分组之后的条件 having
查询总工资少于10000的部门
select deptno,sum(sal) s from emp group by deptno having s <10000;
按部门查询工资不低于1000的所有人的总工资
select deptno,sum(sal) from emp where sal>=1000 group by deptno;
按部门查询工资不低于1000的所有人的总工资,并且只统计总工资小于10000的部门，按照总工资降序排序
select deptno,sum(sal) s from emp where sal>=1000 group by deptno having s<10000 order by s desc;
17.限定结果集的行数
select * from emp;查询前5条数据a表示起始下标位置，0表示第一条b表示最多查询的条数select * from emp limit a,b;
总结：
select 字段 from 表名 where 条件 group by 字段 having 条件 order by 字段 asc/desc limit a,b;