1.自主导入Oracle中的scott数据库
分创三个表的原因:避免信息冗余。
具体代码实现如下(可供参考):
create table dept
(
deptno int not null primary key,
loc varchar(300),
dname varchar(100)
)
CREATE TABLE emp
(
empno INT NOT NULL PRIMARY KEY,
ename VARCHAR(30),
job VARCHAR(30),
mgr INT,
datet DATETIME,
sal FLOAT,
comm FLOAT,
deptno INT
)
CREATE TABLE salgrade
(
GRADE INT NOT NULL PRIMARY KEY,
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',1456.00,800.00,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600.00,300.00,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-2-22',1250.00,500.00,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-4-2',2975.00,2300.00,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250.00,1400.00,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850.00,1020.00,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-6-9',2450.00,200.00,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987--4-19',3000.00,2000.00,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',7765,'1981-11-17',5000.00,1500.00,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500.00,0.00,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-5-23',11000.00,2500,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-3',9500.00,600.00,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-3',3000.00,500.00,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-1-23',1300.00,400.00,10);
INSERT INTO emp VALUES(9999,'SHUNPING','CLERK',7782,'1988-5-5',2456.34,55.66,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.计算列
例:计算列结果无列名。
select ename, sal*12 from emp;
结果如图1所示:
图1
例:给计算列结果设置列名。
select ename, sal*12 as "年薪" from emp;(as可以省略,名称需用双引号括起来)
结果如图2所示:
图2
例:输出的行数是emp表的行数,每行只有一个字段,值888。
select 888 from emp;
结果如图3所示:
图3
3.distinct(不允许重复的)
tip:distinct可以过滤重复的值,包括null。
例:distinct可以过滤重复的值。
select distinct deptno from emp;
如图4所示:
图4
例:将deptno与comm的组合不重复的输出。
select distinct deptno, comm from emp;
如图5所示:
图5
4.between
例:查找工资在1500到3000之间(包括1500和3000)的员工信息。
select * from emp
where sal>=1500 and sal<=3000
等价于
select * from emp
where sal between 1500 and 3000
例:查找工资在大于1500小于3000的员工信息。
select * from emp
where sal>1500 or sal<3000
等价于
select * from emp
where sal not between 1500 and 3000
5.in(属于若干个孤立的值)
tip:数据库中不等于有两种表示分别为:!= 和 <>
例:查找工资等于1500,3000,5000的员工信息。
select * from emp
where sal=1500 or sal=3000 or sal=5000
等价于
select * from emp
where sal (not) in (1500,3000,5000)
6.top
tip:若设置百分比,小数部分向上取整。
例:查找前5行信息。
select top 5 * from emp;
例:查找前百分之15行的信息(小数向上取整)
select top 15 percent * from emp;
例:查找工资区间为1500-3000的前5行信息。
select top 5 * from emp
where sal between 1500 and 3000
order by sal desc--(desc为降序,默认是升序)
7.null(没有值,空值)
tip:(1)null可以参与is、not is运算,不可以参与 <> != =运算;
(2)0和null不一样,null表示空值,没有值,零表示确定的值;
(3)任何类型的数据都允许null;
(4)任何数字与null参与数学运算的结果永远是null。
例:输出奖金(不)为空的信息。
select * from emp where comm (not) is null
例:任何数字与null参与数学运算的结果永远是null。(解决办法isnull())
select ename, sal*12+isnull(comm, 0) "年薪" from emp; --isnull(comm, 0)如果为null则返回0
8.order by(以某个字段排序)
tip:(1)默认是asc升序排序,desc降序排序需在命令末尾添加desc。
(2)order by a desc, b, c, d (desc仅对a生效)。
(3)强烈建议对每一个字段都指定排序标准。
例:若出现order by deptno, sal则先按照deptno排序,若相同,再按照sal排序。
select * from emp order by deptno, sal;
9.模糊查询(搜索时经常使用)
tip:格式一般为
(1)select 字段的集合 from 表名 where 某个字段的名字 like 匹配的条件,匹配的条件需要用单引号括起来。
(2)匹配的条件通常含有通配符。
通配符:
%:表示0个或多个字符;
_:表示任意单个字符;(若查询第二个为A应为'_A%')
[a-f]:a到f中的任意单个字符;(第二个字母为A到F应为'_[A-F]%')
[a,f]:a或者f;
[^a-c]:^(shift+6)为取反,即不是a到c的任意字符;
(3):若想查询百分号或下划线,需要使用\和escape转义。(可自定义escape''中转义字符)
select * from student where name like '%\%%' escape'\';
例:查找含有A的ename。
select * from emp where ename like '%A%';
结果如图6所示:
图6
10.聚合函数 (多行记录返回一个值,通常用于统计分组的信息)
tip:
(1)select max(sal) "最高工资", min(sal) "最低工资", count(*) "员工人数" from emp;
运行结果如图7所示:
图7
(2)select max(sal), lower(ename) from emp;--(x)单行函数与多行函数不能混用。
函数的分类:
单行函数:
每行返回一个值。
多行函数:
多行返回一个值,聚合函数属于多行函数。
聚合函数的分类:
max()
min()
avg() 平均值
count() 求个数
(1)重复的记录记录也会被当成有效记录。
(2)null记录不会被当成有效的记录。
11.group by(重难点)
tip:
(1)使用group by后select中只能出现分组后的整体信息,不能出现组内的详细信息!
(2)功能:把表中的记录按照字段分成不同的组。
(3)理解:group by a, b, c
先按a分组,若相同,则按b分组,若还相同,则按c分组。
最终统计的是最小分组信息!
例:
select deptno, job, avg(sal) "平均工资", count(*) "部门人数", sum(sal) "部门总工资", min(sal) "部门最低工资"
from emp
group by deptno, job
order by deptno
执行结果如图8所示:
图8
12.having (对分组之后的信息进行过滤)
tip:
(1)having子句是用来对分组之后的数据进行过滤,因此使用having时通常先使用group by。
(2)having子句出现的字段必须是分组之后的组的整体信息,having子句不允许出现组内的详细信息。
(3)尽管select字段中可以出现别名,但是having子句不能出现字段别名,只能用字段最原始的名字。
(4)having与where的异同
相同点:
都是对数据过滤,只保留有效数据。
where与having一样,不能出现字段别名,只能用字段最原始的名字。
不同点:
where是对原始的记录过滤,不能使用聚合函数;
having是对分组后的记录过滤;
where必须写在having前面,不可颠倒顺序,否则报错。
例:把平均工资大于2000的分组,统计输出部门平均工资大于3000的部门的部门编号,部门的平均工资。
select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"
from emp
where sal > 2000
group by deptno
having avg(sal) > 3000