显示那些最高薪水大于$10,000 的部门的部门号和最高薪水: select department_id,max(salary) from employees group by department_id having max(salary)>10000;
嵌套分组函数:SQL> select max(avg(salary)) from employees group by department_id ;
子查询:select...from... where...(select)
多行子查询:
in:等于列表中的任何成员
SQL> selecte.last_name,e.department_id,e.salary fromemployees e where e.salary in(selectmin(em.salary) from employees em group byem.department_id);
any:比较子查询返回的每个值 <ANY:小于最大值 >ANY:大于最小值
SQL> select employee_id ,last_name, job_id,salary from employees where job_id <> 'IT_PROG' and salary<any(select salary from employees where job_id ='IT_PROG');
all:比较子查询返回的全部值 <ALL:小于最小值 >ALL:大于最大值
SQL> select employee_id ,last_name, job_id,salary from employees where job_id <> 'IT_PROG' and salary<all(select salary from employees where job_id ='IT_PROG');
操纵数据DML
向表中插入数据:SQL> insert into departments(department_id,department_name,manager_id) values(280,'Teaching',180);
添加日期:25/5月/2021;to_date('2021-05-25','yyyy-mm-dd');
复制一行数据到另外一个表:insert into emp (**,**,**) select ...frmo
修改数据:update emp set name='xiaoma' ,salary=20000 where id=170;
删除数据:delete from emp where id =304;
事务控制语言(TCL)
commit.....事物提交
rollback....事物回滚
savepoint..设置回滚点
数据库定义语言(DDL)
创建表:SQL> create table dept(deptno number(2),dname varchar2(14),loc varchar2(13));
修改表信息
1、增加列:SQL> alter table dept add (salary number(8,2));
2、修改列类型:SQL> alter table dept modify(dname varchar2(20));
3、修改默认值:SQL> alter table dept modify(salary number(8,2) default 5000);
4、修改列名:alter tabledeptrename column dname toname;
5、删除列:alter table dept drop column salary;
6、修改表名:SQL> rename job_grades1 to job_grades2;
7、截断表(清空表数据):SQL> truncate table job_grades2;
8、删除表:SQL> drop table job_grades2;
非空约束:
1、创建表时:SQL> create table dept80(id number,name varchar2(20) not null,salary number constraint dept80_un not null);
2、修改表时:SQL> alter table dept80 modify location_id not null;
唯一性约束:Unique
主键约束:primary key
外键约束:foreign key SQL> create teble dept40(id number,d_id number,constraintdept40_fk foreignkey(d_id reference dept60(id)));
check约束:SQL> create table dept10 (id number,salary number(8,2) check (salary>2000));
查看约束信息:SQL> select constraint_name,constraint_type,search_condition from user_constraints where lower(table_name)='dept40';
禁用约束:SQL> alter table dept60 disable constraint 约束名 cascade; cascade为外键约束时添加
启用约束:SQL> alter table dept60 enable constraint 约束名 ;
top-n分析
显示薪水最高的3个人:select rownum,last_name,salary from(select last_name,salary from employees order by salary desc) where rownum<=3;
创建序列:
SQL> create sequence dept_seq increment by 10 start with 120 maxvalue 9999 nocache nocycle;(无,分割)
单行索引:SQL> create index emp_index on employees(last_name);
复合索引:SQL> create index dept_man_loc on departments(manager_id,location_id);
函数索引:SQL> create index dept_upper on departments (upper(department_name));
删除索引:SQL> drop index dept_upper;
同义词:SQL> create synonym em for employees;
删除同义词:SQL> drop synonym em;
导入/导出
exp|imp 用 户 名 / 密 码 @ 连 接 地 址 : 端 口 / 服
务 名 file= 路 径 / 文 件
名 .dmp full=y|tabels(tablename,tablename...)|owner(username1,username2,username3)
exp:导出命令,导出时必写。
imp:导入命令,导入时必写,每次操作,二者只能选择一个执行。
username:导出数据的用户名,必写;
password:导出数据的密码,必写;
@:地址符号,必写;
SERVICENAME:Oracle 的服务名,必写;
1521:端口号,1521 是默认的可以不写,非默认要写;
file="文件名.dmp" : 文件存放路径地址,必写;
full=y :表示全库导出。可以不写,则默认为 no,则只导出用户下的对象;
tables:表示只导出哪些表;
owner:导出该用户下对象;
full|tables|owner:只能使用一种;