1.desc tablename---描述一张表(字段,类型,长度)
2.select empno,sal*12 "All money" from emp;
---给结果项起别名。如含特殊字符或者要保持原有格式,用双引号括起来。
3.任何含空值的数学表达式最后的结果都是空值(NULL)
4.字符串连接符:||
select ename||sal from emp;
单引号包起来字符串。如果字符串内含有单引号,用两个单引号来替代。
5.select distinct deptno , job from emp;
--去掉结果中的重复值。
6.select * from emp where deptno = 10;
--取特定的值的记录。过滤条件,过滤一些记录。
7.select ename,sal,deptno from emp where deptno<>10;
--取值不等于的记录
8.select ename,sal from emp where sal between 800 and 1500;
--取值在什么范围的记录(包含头尾)
等同于select ename,sal from emp where sal >=800 and sal <=1500;
9.select ename,sal,comm from emp where comm is null;
--选空值的记录(is not null;非空值)
10.select ename,sal,comm from emp where sal in(800,1500,2000);
--取出值为800,1500或者2000的记录。
11.select ename,sal,hiredate from emp where hiredate > '20-2月-81';
--日期的选择。
12.and,or,not逻辑操作符都可用。
13.模糊查询:(通配符%)
select ename from emp where ename like '%ALL%';
select ename from emp where ename like '_A%';
14.如字符串中含%,用转义字符'\'或者自定义转义字符
select emane from emp where ename like '%\%%';
select ename from emp where ename like '%$%%' escape '$';
15.select * from dept order by deptno desc;
---降序。默认为升序。
select empno, ename from emp order by empno asc;
--升序,默认的。可不带asc.
16.select ename,sal,deptno from emp order by deptno asc,ename desc;
--多重排序,先按deptno排序,deptno相同时内部再按ename倒序排。
17.select ename, sal*12 annual_sal from emp
where ename not like '_A%' and sal >800 order by sal desc;
--以上总结性Slect语句。
18.SQL函数:
lower(string)
--select lower(ename) from emp;
--select ename from emp where lower(ename) like '_a%';
substr(string,start,all)
--sletect substr(ename,1,3) from emp;--从一个字符开始截取,共截取三个字符。
chr(number) ,ascII(char)
--select chr(65) from dual;把数字转换成ASCII码;
--selcet ascii('A') from dual;
round(number) round(number,num) 还可以是负数哦。--四舍五入。
select round(23.652) from dual; --24
select round(23.652,2) from dual;--23.65
to_char(sal,'$99,999.9999')
--9代表一位数字。数字或者日期转换成其他形式,位数上无数字则不显示。
--可以用0来表示,也代表一位数字。无数字也显示,用0显示。
--select to_char(sal,'$99,999.9999') from emp;
select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
select to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') from emp; -24小时制
--记住它。日期转换为特定格式。
select ename, hiredate from emp
where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
--转换日期
select sal from emp where sal > to_number('$1,250.00','$9,999.99');
--转换数字。
select ename,sal*12+nvl(comm,0) from emp;
--算术运算中怎样处理空值,nvl如为空值,用0来替代,如不为空值返回其本身的值。
19.多行函数。---多条输入,一条输出。
* select max(sal) from emp;
* select min(sal) from emp;
* select avg(sal) from emp;
select to_char(avg(sal),'9999999.99') from emp;
select round(avg(sal),2) from emp;
* select sum(sal) from emp;
* select count(*) from emp;---求有多少条记录。
select count(distinct deptno) from emp;
select deptno ,job,max(sal) from emp group by deptno,job;
--按照***来分组,组内用函数处理。
* 规则:出现在select列表里面的字段没有出现在组函数里,必须出现在group by里面。
select ename,max(sal) from emp group by deptno;---错误表达式。
select deptno,max(sal) from emp group by deptno;--正确。
子查询:select ename from emp where sal = (select max(sal) from emp);
where--对单条语句进行过滤。
having--对分组进行过滤。
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
--以上为单条select语句。总结如下(执行顺序也不能错):
select * from emp --选取数据
where sal>1000 --选择条件
group by deptno --对选取的数据进行分组
having --对分组后的数据进行过滤限制
order by --对最后的结果进行排序。
子查询:
把中间过程查询出来的结果当成一张表,示例如下(表连接):
select ename, sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
自连接(为表起不同的别名,当成两张表来用):
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
sql1999---连接条件和数据过滤条件分开:
select ename,dname from emp cross join dept;---交叉连接
条件不写在where里面,改在join on()里面
select ename,dname from emp join dept on(emp.deptno = dept.deptno);
select ename,dname from emp join dept using (deptno);--不推荐。
连三张表:
select ename,dname,grade from emp e join dept d on (e.deptno = d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like '_A%';
外连接:
select e1.ename,e2.ename from emp e1
left outer join emp e2 on (e1.mgr = e2.empno);
---outer可省略。如下:
select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
--左外连接,会把左边的表的多余数据显示出来。
类似,也有右外连接,会把右边多余的数据显示出来。
全外连接(sql1992不支持):
select ename,dname from emp e full join dept d on (e.deptno = d.deptno);
--左右多余的数据都取出来--显示出来。
1.部门平均薪水的等级:
select deptno,avg_sal,grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal);
2.部门平均的薪水等级:
select deptno, avg(grade) from
(select deptno,ename,grade from emp
join salgrade s on(emp.sal between s.losal and s.hisal)) t
group by deptno;
3.求雇员中有哪些人是经理人
select ename from emp where empno in(select distinct mgr from emp);
4.不准用组函数,求薪水的最高值
select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal));
5.平均薪水最高的部门编号
select deptno ,avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno));
组函数嵌套:(最多两层)
select deptno ,avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) avg_sal,deptno from emp group by deptno);
6.平均薪水最高的部门名称
select dname from dept where deptno =
(select deptno from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno)));
7.平均薪水的等级最低的部门名称
select dname from dept
where deptno =
(select deptno from
(selcet deptno, avg_sal,grade from
(select avg(sal) avg_sal,deptno from emp group by dept) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
) t1
where t1.grade =
(select min(grade) from
(selcet deptno, avg_sal,grade from
(select avg(sal) avg_sal,deptno from emp group by dept) t
join salgrade s on(t.avg_sal between s.losal and s.hisal))))
DML语句:
insert into:
insert into tablename values(values);
insert into tablename(列名) values(values);--插入的数据是部门列有数据。
insert into tablename select * from tablename2;(表的数据结构要一样)
DDL语句:
create table tablename(a varchar2(10));
--定义tablename的表,里面含一个字段a.
drop table tablename;--删除表。
drop user username;--删除用户。
create table stu
(
id number(6) primary key,---主键约束
name varchar2(20) not null,
--非空 name varchar2(20) constraint stu_name_nn not null,给约束条件起名字
sex number(1),
age number(3), --数字类型,几位数字。
sdate date, --日期类型
grade number(2) default 1,--默认值
class number(4) references class(id), --外键
email varchar2(50) unique
--唯一约束,null值不认为唯一冲突。
constraint stu_name_email_uni unique(email,name)
--表级字段组合约束。可自己取名。
constraint stu_class_fk foreign key (class) references class(id)
);
create table class
(
id number(4) primary key,
name varchar2(20) not null
)
五个约束条件:
非空(constraint),
唯一(unique),
主键(primary key),语法:非空,唯一。逻辑:代表一条记录。
--constraint stu_id_pk primary key(id)--加在表级
外键(references tablename(字段名)),
--constraint stu_class_fk foreign key(字段名) references tablename(字段名)
被参考的字段必须是主键。
check
一个事务起始于一条DML语句,结束于commit。
DML语句碰到DDL,DCL语句,事务结束,自动提交。rollback不起作用。
DCL:授权,事务控制语句。
正常断开,自动提交,非正常断开,自动回滚。
虚表(视图)--以v$开头:
create view v$_dept_avg_sal_info as
selcet deptno, avg_sal,grade from
(select avg(sal) avg_sal,deptno from emp group by dept) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
修改已经存在的表的结构(alter):
新增一个字段:alter table stu add(addr varchar2(100));
修改一个字段:alter table stu modify(addr varchar2(150));--更改的精度需要能够容纳里面的数据。
删除一个字段:alter table stu drop(addr);
删除一个约束条件:alter table stu constraint stu_class_fk;---约束条件的名称。
增加一个约束条件:alter table stu constraint stu_class_fk foreign key (class) references class(id);
索引:index
建索引:create index idx_stu_email on stu(email);---也可以为字段建组合索引。
删除索引:drop index idx_stu_email;
查询有多少索引:select index_name from user_indexes;
--有加主键,唯一约束时,数据库默认会建立索引。
查询效率变高,修改数据效率降低。
数据库设计三范式:
1.要有主键,列不可分。---设计任何表都要有主键。
2.不能存在部分依赖。不是主键的字段不能依赖于部分主键。
3.不能存在非关键字段决定另一非关键字段。即:关键字段->非关键字段X->非关键字段Y
Oracle下特有的一些东西。
rownum 认识:(oracle)
只能和小于<,小于等于<=搭配使用。
记录刚刚取出来的数据的行号,排序过后的数据不管用。
解决办法,对数据先进行排序,再把结果当作一个表来用,可把rownum显示的调用出来
对于大于或者区间取值,只能把rownum调出来,再进行比较。
update:
update emp set sal = sal*2, ename = ename||'-' where deptno = 10;
连接一个'-'。
delete:
delete from emp;删除所有的数据。
delete from dept where deptno < 25;
rollback;回滚操作。--回退到初始状态,回退所有的DML语句。
commit; 提交-提交后rollback不起作用,DML生效。
数据字典表:Oracle下当前用户的信息
多少张表:user_tables ---select table_name from user_tables;
多少张视图:user_views ---select view_name from user_views;
多少约束条件:user_constraints ---select constraint_name from user_constraints;
Oracle里面装数据字典表的表:
dictionary
select table_name from dictionary(超多)
Oracle--序列:sequence
专门用来产生一个独一无二的数:自动递增1或者N。
create sequence seq;
select seq.nextval from dual;---产生下一个数。
insert into article values(seq.nextval,'a','b');
drop sequence seq;