目录
1.单行查询 (2)
2.基本查询 (3)
3.限定返回的行 (4)
4.逻辑运算 (5)
5.排序 (6)
6.函数 (7)
7.分组查询 (9)
8.多表查询 (10)
9.多表连接 (13)
10.子查询 (14)
11.创建和管理表 (19)
12.约束 (22)
13.视图、序列、索引 (24)
14.其他数据库对象 (26)
15.plsql基础 (28)
1.单行查询
--查询所有员工的姓
select https://www.doczj.com/doc/944698677.html,st_name as 姓from employees e;
--消除重复的姓
select distinct https://www.doczj.com/doc/944698677.html,st_name as 姓氏from employees e;
--计算员工的月收入（工资+佣金）
select salary,salary*(nvl(commission_pct,0)+1) as 工资佣金from employees;
--计算员工的年收入
select salary*(nvl(commission_pct,0)+1)*12 as 年收入from employees;
--查询员工的姓名
select e.first_name||' '||https://www.doczj.com/doc/944698677.html,st_name "name" from employees e;
--查询位置为1700的部门名称（不重复）
select distinct d.department_name as 部门名称from departments d where d.location_id=1700;
--查询工资高于10000的员工
select * from employees where salary>10000;
--查询工资低于3000的员工
select * from employees where salary<3000;
--查询在1998年入职的员工
select * from employees e where to_char(e.hire_date,'yyyy')='1998';
--查询没有佣金的员工
select * from employees e where commission_pct is null;
--查询姓以b开头的员工
select * from employees e where https://www.doczj.com/doc/944698677.html,st_name like 'b%';
--查询部门号为10或者20或者30的员工
select * from employees e where e.department_id in(10,20,30);
2.基本查询
--查询所有员工的姓
select last_name from employees;
--消除重复的姓
select distinct last_name from employees;
--创建一个查询，以显示employees表中的唯一职务代码
select distinct t.job_id from employees t;
--创建一个查询，使其显示每位员工的姓氏、职务代码、聘用日期和员工编号，并且首先显示员工编号。为hire_date列提供一个别名：startdate
select t.employee_id,https://www.doczj.com/doc/944698677.html,st_name,t.job_id,t.hire_date as startdate from employees t;
--计算员工的月收入（工资+佣金）
select t.salary+t.salary*nvl(https://www.doczj.com/doc/944698677.html,mission_pct,0) from employees t;
--计算员工的年收入
select 12*(t.salary+t.salary*nvl(https://www.doczj.com/doc/944698677.html,mission_pct,0)) from employees t;
--查询员工的姓名
select t.first_name||' '||https://www.doczj.com/doc/944698677.html,st_name from employees t;
--显示与职务标识连接的姓氏，用逗号和空格分隔，命名为employee and title
select t.job_id||', '||https://www.doczj.com/doc/944698677.html,st_name as "employee and title" from employees t;
--创建一个查询，使其显示employees表的所有数据，用逗号分隔各列，命名为the_output select t.employee_id||','||t.first_name||','||https://www.doczj.com/doc/944698677.html,st_name||','||
t.email||','||t.phone_number||','||t.hire_date||','||t.job_id||','||
t.salary||','||https://www.doczj.com/doc/944698677.html,mission_pct||','||t.manager_id||','||t.department_id
as "the_output" from employees t;
3.限定返回的行
--查询位置为1700的部门名称（不重复）
select distinct t.department_name from departments t
where t.location_id=1700;
--创建一个查询，显示员工编号为176的员工的姓氏和部门编号
select https://www.doczj.com/doc/944698677.html,st_name,t.department_id from employees t
where t.employee_id=176;
--查询工资高于10000的员工的姓氏和薪资
select https://www.doczj.com/doc/944698677.html,st_name,t.salary from employees t
where t.salary>10000;
--查询工资低于3000的员工
select t.* from employees t
where t.salary<3000;
--查询在1998-2-20和1998-5-1之间入职的员工的姓氏、职务标识和起始日期select https://www.doczj.com/doc/944698677.html,st_name,t.job_id,t.hire_date from employees t
where t.hire_date between to_date('19980220','yyyymmdd')
and to_date('19980501','yyyymmdd');
--显示在1994年聘用的每位员工的姓氏和聘用日期
select https://www.doczj.com/doc/944698677.html,st_name,t.hire_date from employees t
where t.hire_date between to_date('19940101','yyyymmdd')
and to_date('19950101','yyyymmdd');
--*查询没有佣金的员工
select t.* from employees t where https://www.doczj.com/doc/944698677.html,mission_pct is null;
--查询姓以b开头的员工
select t.* from employees t where https://www.doczj.com/doc/944698677.html,st_name like 'b%';
--查询部门号为10或者20或者30的员工
select t.* from employees t where t.department_id in (10,20,30);
--查询没有经理的所有员工的姓氏和职称
select https://www.doczj.com/doc/944698677.html,st_name,t.job_id from employees t where t.manager_id is null;
--显示员工名字中的第三个字母为”a”的所有员工的姓氏
select https://www.doczj.com/doc/944698677.html,st_name from employees t where https://www.doczj.com/doc/944698677.html,st_name like '__a%';
4.逻辑运算
--找出有佣金的员工的都做什么工作（无重复）
select distinct t.job_id from employees t
where https://www.doczj.com/doc/944698677.html,mission_pct is not null;
--找出不收取佣金或收取的佣金高于100的员工
select * from employees t
where https://www.doczj.com/doc/944698677.html,mission_pct is null or t.salary*https://www.doczj.com/doc/944698677.html,mission_pct>100;
--找出部门10中所有的经理（manager）和部门20中所有办事员（clerk）和既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料(需用子查询，暂不做)
--显示员工姓氏中有”a”和”e”的所有员工的姓氏
select * from employees t
where https://www.doczj.com/doc/944698677.html,st_name like '%a%' or https://www.doczj.com/doc/944698677.html,st_name like '%e%'
--显示职务为销售代表（sa_rep）或仓库管理员（st_clerk）并且薪金不等于2500,3500,7000的所有员工的姓氏、职务和薪金
select https://www.doczj.com/doc/944698677.html,st_name,t.job_id,t.salary from employees t
where (t.job_id='sa_rep' or t.job_id='st_clerk') and t.salary not in (2500,3500,7000);
--显示薪金不在5000-1200这个范围之间的所有员工的姓氏和薪金
select https://www.doczj.com/doc/944698677.html,st_name,t.salary from employees t
where t.salary<5000 or t.salary>1200;
5.排序
--查询员工资料，按薪资升序排列
select t.* from employees t
order by t.salary;
--查询员工资料，在部门号从大到小的情况下按按薪资升序排列
select t.* from employees t
order by t.department_id desc ,t.salary;
--按姓名的字母顺序显示部门20和部门50中的所有员工的姓氏和部门编号
select https://www.doczj.com/doc/944698677.html,st_name , t.department_id from employees t
where t.department_id in(20,50)
order by https://www.doczj.com/doc/944698677.html,st_name;
--显示可以赚取佣金的所有员工的姓氏、薪金和佣金，按薪金和佣金的降序对数据进行排序select https://www.doczj.com/doc/944698677.html,st_name , t.salary,https://www.doczj.com/doc/944698677.html,mission_pct from employees t
where https://www.doczj.com/doc/944698677.html,mission_pct is not null
order by t.salary desc,https://www.doczj.com/doc/944698677.html,mission_pct desc;
6.函数
--显示当前日期
select sysdate from dual;
--显示当前日期，格式为****年**月**日，别名为hday
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') hday from dual;
--编写一个查询，显示姓名以j、a或m开始的所有员工的姓氏（第一个字母大写，其余字母小写）和姓氏的长度，给每列一个合适的标签
select initcap(https://www.doczj.com/doc/944698677.html,st_name) lname,length(https://www.doczj.com/doc/944698677.html,st_name) len from employees t
where substr(upper(last_name),0,1) in('j','a','m');
--计算每位员工截止到当前时间入职的星期数，别名为weeks_worked。按聘用的星期数对结果进行排序。该星期数舍入到最接近的整数。同时显示员工的名字;
elect t.first_name,round((sysdate-t.hire_date)/7) as weeks_worked from employees t;
--计算每位员工截止到当前时间入职的月数，别名为months_worked。该星期数舍入到最接近的整数。同时显示员工的名字
select t.first_name,round(months_between(sysdate,t.hire_date)) as months_worked
from employees t;
--查询在1998-2-20和1998-5-1之间入职的员工的姓氏、职务标识和起始日期
select https://www.doczj.com/doc/944698677.html,st_name,t.job_id,t.hire_date from employees t
where t.hire_date between to_date('19980220','yyyymmdd')
and to_date('19980501','yyyymmdd');
--创建一个查询。显示所有员工的姓氏和薪金。规定薪金为15个字符长，左边填充$ select lpad(t.salary,15,'$') sal from employees t;
--显示每位员工的姓氏、聘用日期和薪金复核日期，薪金复核日期是服务六个月之后的第一个星期一。将该列标记为review，此日期格式类似于：”monday,the thirty-first of july,2000”select https://www.doczj.com/doc/944698677.html,st_name,t.hire_date,next_day(add_months(t.hire_date,6),2) from employees t
select https://www.doczj.com/doc/944698677.html,st_name,t.hire_date,
to_char(next_day(add_months(t.hire_date,6),2),'fmday,"
the "ddspth" of "month,yyyy',
'nls_date_language=english' ) review
from employees t;
注：to_char的第三个参数用于设置查询使用的国家和地区，ddspth中spth为后缀，表示spelled, ordinal number
--显示员工的姓氏、聘用日期和该员工在星期几开始工作的
select https://www.doczj.com/doc/944698677.html,st_name,t.hire_date,to_char(t.hire_date,'day') as "开始" from employees t;
--计算员工的月收入（工资+佣金）
select t.salary+t.salary*nvl(https://www.doczj.com/doc/944698677.html,mission_pct,0) from employees t;
--创建一个查询，使其显示员工的姓氏，并用星号指明他们的年薪。每个星号代表一千美元，按薪资降序排列数据。
select https://www.doczj.com/doc/944698677.html,st_name||lpad(' ',trunc(t.salary/1000)+1,'*'),t.salary from employees t
order by t.salary desc;
--创建一个查询。使其显示员工的姓氏和佣金额。如果某位员工不赚取佣金则显示”no commission”，将该列标记为comm
select https://www.doczj.com/doc/944698677.html,st_name,nvl(to_char(t.salary*https://www.doczj.com/doc/944698677.html,mission_pct,'999,999.99'),'no commission') comm from employees t
--使用decode函数编写一个查询，使其按照以下数据根据job_id列的值显示所有员工的级别，同时显示员工的姓氏
job grade ad_pres a st_man b it_prog c sa_rep d st_clerk e 其它0
select https://www.doczj.com/doc/944698677.html,st_name,t.job_id job,
decode(t.job_id,'ad_pres','a' ,'st_man','b' ,'it_prog','c' ,'sa_rep','d' ,'st_clerk','e' ,'0' )
grade from employees t;
select https://www.doczj.com/doc/944698677.html,st_name,t.job_id job,
case t.job_id
when 'ad_pres' then 'a'
when 'st_man' then 'b'
when 'it_prog' then 'c'
when 'sa_rep' then 'd'
when 'st_clerk' then 'e'
else '0'
end as grade from employees t;
--显示当前日期，本月最后一天的日期，以及本月还剩多少天
select sysdate, last_day(sysdate) "last", last_day(sysdate) - sysdate "days left" from dual;
--显示今年的第一天
select trunc(sysdate,'year') from dual;
--显示本月的第一天
select trunc(sysdate,'month') from dual;
--最近一个星期四是哪天(不含今日)
select next_day(sysdate,5) from dual;
7.分组查询
--求所有员工的平均工资、最高工资、最低工资和工资总和，给予适当的别名select avg(t.salary),max(t.salary),min(t.salary),sum(t.salary) from employees t;
--求每种工作的平均工资
select avg(t.salary) from employees t
group by t.job_id;
--求每个部门中同一种工作的平均工资,同时显示部门号,按部门号升序显示
select t.department_id,avg(t.salary) from employees t
group by t.department_id,t.job_id
order by t.department_id;
--查询出各部门的部门编号以及各部门的总工资和平均工资,按部门编号升序排列。select t.department_id,sum(t.salary),avg(t.salary) from employees t
group by t.department_id
order by t.department_id;
--显示每种工作的人数
select t.job_id,count(*) from employees t
group by t.job_id;
--显示员工最高工资超过10000的部门的id及其员工最高工资
select t.department_id,max(t.salary) m from employees t
group by t.department_id
having max(t.salary)>10000;
--显示平均工资最高的部门id及其平均工资
select * from
(select t.department_id,avg(t.salary) from employees t
group by t.department_id
order by avg(t.salary) desc)
where rownum<2;
8.多表查询
多表查询，基本知识：
什么是多表查询?
什么是笛卡尔积？
怎样避免笛卡尔积？
要将n个表联结在一起，至少需要多少个联结条件？
什么是等值联结？
如何在联结条件外附加限制条件？
如何限定模糊的列名？
如何进行非等值联结？
什么是外联结？
什么是自联结？
如何用sql1999标准表达：笛卡尔积、自然联结、等值联结、内联结、左外联结、右外联结和全外联结？
联结的分类：
joins that are compliant with the sql:1999 standard include the following:
natural joins:
natural join clause
usingclause
on clause
outer joins:
left outer join
right outer join
full outer join
cross joins
sql：1999联结语法
select table1.column, table2.column from table1
[natural join table2] |
[join table2 using(column_name)] |
[join table2 on (table1.column_name = table2.column_name)]|
[left|right|full outer join table2 on (table1.column_name = table2.column_name)]|
[cross join table2];
知识总结：
in this lesson, you should have learned how to use joins to display data from multiple tables by using:equijoins、nonequijoins、outer joins、self-joins、cross joins、natural joins、outer joins、full (or two-sided)
--1.write a query for the hr department to produce the addresses of all the departments. use the locations and countries tables. show the location id, street address, city, state or province, and
country in the output. use a natural join to produce the results.
select lct.location_id,lct.street_address,lct.state_province,cty.country_name from locations lct natural join countries cty;
--2.the hr department needs a report of all employees. write a query to display the last name, department number, and department name for all the employees.
select https://www.doczj.com/doc/944698677.html,st_name,department_id,dpt.department_name from employees emp natural join departments dpt;
--3.the hr department needs a report of employees in toronto. display the last name, job, department number, and the department name for all employees who work in toronto.
select https://www.doczj.com/doc/944698677.html,st_name,emp.job_id,dpt.department_id,dpt.department_name from employees emp join departments dpt on emp.department_id=dpt.department_id
join locations lct on dpt.location_id=lct.location_id
where lct.city='toronto';
--4.create a report to display employees’ last name and employee number along with their manager’s last name and manager number.
label the columns employee, emp#, manager, and mgr#, respectively. save your sql statement as lab_06_04.sql. run the query.
--自联结
select https://www.doczj.com/doc/944698677.html,st_name employee,emp.employee_id emp#,https://www.doczj.com/doc/944698677.html,st_name manager, mgr.employee_id mgr# from employees emp
join employees mgr on emp.manager_id=mgr.employee_id;
--5.modify lab_06_04.sql to display all employees including king, who has no manager. order the results by the employee number. save your sql statement as lab_06_05.sql. run the query in lab_06_05.sql.
select https://www.doczj.com/doc/944698677.html,st_name employee,emp.employee_id emp#,
https://www.doczj.com/doc/944698677.html,st_name manager,mgr.employee_id mgr# from employees emp
left outer join employees mgr on emp.manager_id=mgr.employee_id
order by emp#;
--6.create a report for the hr department that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. give each column an appropriate label. save the script to a file named lab_06_06.sql.
select https://www.doczj.com/doc/944698677.html,st_name employee,emp.department_id,https://www.doczj.com/doc/944698677.html,st_name colleague
from employees emp
join employees colleague on emp.department_id=colleague.department_id
where emp.employee_id<>colleague.employee_id
order by employee;
--7.the hr department needs a report on job grades and salaries. to familiarize yourself with the job_grades table, first show the structure of the job_grades table. then create a query that displays the name, job, department name, salary, and grade for all employees.
--建表
create table job_grades
(
lowest_sal number(6),
highest_sal number(6),
grade_level char(1)
)
--插入数据
insert into job_grades (lowest_sal, highest_sal, grade_level) values (30000, 40000, 'f');
insert into job_grades (lowest_sal, highest_sal, grade_level) values (20000, 30000, 'e');
insert into job_grades (lowest_sal, highest_sal, grade_level) values (15000, 20000, 'd');
insert into job_grades (lowest_sal, highest_sal, grade_level) values (8500, 15000, 'c');
insert into job_grades (lowest_sal, highest_sal, grade_level) values (5500, 8500, 'b');
insert into job_grades (lowest_sal, highest_sal, grade_level) values (2000, 5000, 'a');
commit;
select https://www.doczj.com/doc/944698677.html,st_name employee,emp.salary,g.grade_level from employees emp join job_grades g on emp.salary between g.lowest_sal and g.highest_sal;
--8.the hr department wants to determine the names of all the employees who were hired after davies. create a query to display the name and hire date of any employee hired after employee davies.
select https://www.doczj.com/doc/944698677.html,st_name employee,emp.hire_date from employees emp
join employees clg on emp.hire_date > clg.hire_date
where https://www.doczj.com/doc/944698677.html,st_name='davies'
order by emp.hire_date;
--9.the hr department needs to find the names and hire dates of all the employees who were hired before their managers, along with their managers’ names and hire dates. save the script to a file named lab_06_09.sql.
select https://www.doczj.com/doc/944698677.html,st_name employee,emp.hire_date,https://www.doczj.com/doc/944698677.html,st_name manager,
mgr.hire_date mgr_hire_day from employees emp
join employees mgr
on emp.manager_id=mgr.employee_id and emp.hire_date < mgr.hire_date;
9.多表连接
--using 子句
select e.employee_id, https://www.doczj.com/doc/944698677.html,st_name, d.location_id from employees e
join departments d using(department_id);
--on子句
select e.employee_id, https://www.doczj.com/doc/944698677.html,st_name, e.department_id, d.department_id, d.location_id from employees e
join departments d on (e.department_id = d.department_id);
--使用on子句创建多表连接
select employee_id, city, department_name
from employees e
join departments d on d.department_id = e.department_id
join locations l on d.location_id = l.location_id;
--左外连接
select https://www.doczj.com/doc/944698677.html,st_name, e.department_id, d.department_name
from employees e
left outer join departments d on (e.department_id = d.department_id);
--右外连接
select https://www.doczj.com/doc/944698677.html,st_name, e.department_id, d.department_name
from employees e
right outer join departments d on (e.department_id = d.department_id) ;
--满外连接
select https://www.doczj.com/doc/944698677.html,st_name, e.department_id, d.department_name
from employees e
full outer join departments d on (e.department_id = d.department_id) ;
--增加条件连接
select e.employee_id, https://www.doczj.com/doc/944698677.html,st_name, e.department_id,d.department_id,d.location_id from employees e
join departments d on (e.department_id = d.department_id) and e.manager_id=149 ;
10.子查询
什么是子查询?
子查询能解决什么类型的问题?
子查询可以用在什么位置?
子查询有哪些类型?
核心知识:
--1.子查询概念
子查询就是按顺序执行系列查询并将前一个查询的结果作为下一个查询使用的值;
--2.子查询语法
Select select_list from table
Where expr operator (select select_list from table);
注:operator包含比较表达式,如: >, =, in ,等
2.1 子查询先于主查询执行;2.2 子查询的结果用于外查询
--3.子查询可以用于什么位置?
where/having/from子句
--4.举例:查询工资比abel高的员工的姓氏和工资
select last_name, salary from employees
where salary > (select salary from employees where last_name = 'abel');
5.使用指南:
5.1 子查询用括号包含
5.2 将子查询放在比较运算符右边以增加可读性
5.3 单行子查询使用单行运算符,多行子查询使用多行运算符(in,any,all)
6.区分单行与多行子查询
6.1 单行子查询:返回单行数据,适用的比较运算符为=,>,<,<>,>=,<=
6.2 多行子查询:返回多行数据,适用的比较运算符为in,any,all
select employee_id, last_name, job_id, salary from employees
where salary < any (select salary from employees where job_id = 'it_prog')
and job_id <> 'it_prog';
7.子查询中的空值:
in(...)的含义等价于:= any(...),所以子查询中是否有空值,对结果没有影响
但是,not in(...)的含义等价于:<> all(...),如果子查询中出现空值,整个表达式为空
自然:任意比较all(...),如果子查询中出现空值,整个表达式为空
select * from employees e where e.department_id =
(select d.department_id from departments d
where d.department_name in('marketing','it'));
--查询部门名称为marketing和it的员工信息
select * from employees e where e.department_id in
(select d.department_id from departments d
where d.department_name in('marketing','it'));
--查询不是经理的员工的信息
select * from employees e where e.employee_id not in
(select distinct e1.manager_id from employees e1
where e1.manager_id is not null);
--查询出所有经理的信息
select https://www.doczj.com/doc/944698677.html,st_name,e.department_id from employees e
where e.employee_id in
(select distinct e1.manager_id from employees e1
where e1.manager_id is not null);
--查询工资比10号部门中其中一个员工低的员工信息
select * from employees e where e.salary<
any (select e1.salary from employees e1
where e1.department_id=10);
--查询工资比10号部门都要低的员工信息
select * from employees e where e.salary<
(select min(e1.salary) from employees e1
where e1.department_id=10);
--如果要显示这个最低工资
select https://www.doczj.com/doc/944698677.html,st_name,e.salary,s1.ms from employees e,
(select min(e1.salary) ms from employees e1
where e1.department_id=10) s1
where e.salary --列出与”sewall”(指的是last_name)从事相同工作的所有员工及部门名称select https://www.doczj.com/doc/944698677.html,st_name,d.department_name from employees e,departments d where e.department_id=d.department_id and e.job_id=(select job_id from employees where last_name='sewall') and https://www.doczj.com/doc/944698677.html,st_name<>'swall'; --显示和austin同部门，工资低于baer的雇员有哪些 select https://www.doczj.com/doc/944698677.html,st_name from employees e where e.department_id= (select department_id from employees where last_name='austin') and e.salary<(select salary from employees where last_name='baer'); --找出部门90中所有的经理（manager）和部门20中所有办事员（**_clerk） select * from employees where department_id=90; select https://www.doczj.com/doc/944698677.html,st_name,e.job_id from employees e where ( e.department_id=90 and e.employee_id in (select distinct e1.manager_id from employees e1 where e1.manager_id is not null) )or( e.department_id=20 and e.job_id like '%clerk%' ) ; --显示每个部门的名称、地点、员工人数以及该部门所有员工的平均薪资，将平均薪资舍入到小数点后两位。 select d.department_name 部门, d.location_id 地点, s.empnum 部门员工数, s.avgsal 部门平均工资from departments d, (select e1.department_id dptid,count(e1.employee_id) empnum ,avg(e1.salary) avgsal from employees e1 group by e1.department_id) s where d.department_id=s.dptid; --列出薪金高于公司平均薪金的所有员工,薪资,所在部门名称,上级领导姓名,工资等级select d.department_name 部门, https://www.doczj.com/doc/944698677.html,st_name 员工,https://www.doczj.com/doc/944698677.html,st_name 主管,g.grade_level 工资等级from employees e join employees mgr on e.manager_id=mgr.employee_id join departments d on e.department_id=d.department_id join job_grades g on e.salary between g.lowest_sal and g.highest_sal where e.salary>(select avg(salary) from employees); --查询出部门名称，部门员工数，部门平均工资，部门最低工资雇员的姓名，其工资,及工资等级 --方法1 select d.department_name 部门,s.empnum 部门员工数, s.avgsal 部门平均工资,e.salary 最低工资工资, https://www.doczj.com/doc/944698677.html,st_name 部门最低工资雇员,g.grade_level 工资等级 from employees e, departments d, job_grades g (select e1.department_id dptid,count(e1.employee_id) empnum ,avg(e1.salary) avgsal, min(e1.salary) minsal from employees e1 group by e1.department_id) s, where e.department_id=d.department_id and d.department_id=s.dptid and e.salary=s.minsal and e.salary between g.lowest_sal and g.highest_sal; --方法2: select d.department_name 部门, d.department_id 部门号, s1.empcount 部门员工数, s1.avgsal 部门平均工资, s1.minsal 部门最低工资, https://www.doczj.com/doc/944698677.html,st_name 部门最低工资雇员, g.grade_level 工资等级from employees e join departments d on e.department_id=d.department_id join job_grades g on e.salary between g.lowest_sal and g.highest_sal join (select e1.department_id dptid,count(*) empcount,avg(e1.salary) avgsal, min(e1.salary) minsal from employees e1 group by department_id) s1 on e.department_id=s1.dptid and e.salary=s1.minsal; --1.the hr department needs a query that prompts the user for an employee last name. the query then displays the last name and hire date of any employee in the same department as the employee whose name they supply (excluding that employee). for example, if the user enters zlotkey, find all employees who work with zlotkey (excluding zlotkey). select https://www.doczj.com/doc/944698677.html,st_name,e.hire_date,e.department_id from employees e where e.department_id= (select e1.department_id from employees e1 where https://www.doczj.com/doc/944698677.html,st_name=&last_name) and https://www.doczj.com/doc/944698677.html,st_name<>&last_name; --2.create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. sort the results in order of ascending salary. select e.employee_id, https://www.doczj.com/doc/944698677.html,st_name, e.salary from employees e where e.salary>(select avg(salary) from employees) order by e.salary; --3.write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains the letter “u.”save your sql statement as lab_07_03.sql. run your query. select e.employee_id,https://www.doczj.com/doc/944698677.html,st_name from employees e where e.department_id in (select department_id from employees where last_name like '%u%'); --4.the hr department needs a report that displays the last name, department number, and job id of all employees whose department location id is 1700. select https://www.doczj.com/doc/944698677.html,st_name,e.department_id,e.job_id from employees e where department_id in (select department_id from departments where location_id=1700); --5.create a report for hr that displays the last name and salary of every employee who reports to king. select https://www.doczj.com/doc/944698677.html,st_name,e.salary from employees e where e.manager_id in (select employee_id from employees where last_name='king'); --6.create a report for hr that displays the department number, last name, and job id for every employee in the executive department. select e.department_id,https://www.doczj.com/doc/944698677.html,st_name,e.job_id from employees e where e.department_id in (select department_id from departments d where d.department_name='executive') --7.modify the query in lab_07_03.sql to display the employee number, last name, and salary of all employees who earn more than the average salary, and who work in a department with any employee whose last name contains a “u.”resave lab_07_03.sql as lab_07_07.sql. run the statement in lab_07_07.sql. select e.employee_id, https://www.doczj.com/doc/944698677.html,st_name, e.salary from employees e where e.department_id in (select department_id from employees where last_name like '%u%') and e.salary> (select avg(salary) from employees); 11.创建和管理表 创建和管理表,预习自检: 1.有哪些数据库对象? 表：用于存储数据 视图：一个或者多个表中的数据的子集 序列：数字值生成器 索引：提高某些查询的性能 同义词：给出对象的替代名称 2.建表是要指定哪些内容? 3.如何建表时为列指定默认值? 4.如何使用子查询语法创建表? 5.如何为已有表新增列,删除列,修改列,为新增列定义默认值? 6.如何标记列为"unused" 7.如何批量删除"unused"列 8.如何删除表 9.如何更改表名? 10.如何舍去表中的内容? 11.如何为表,列添加注释? 12.oracle有哪些常用的数据类型? --使用sql语句完成以下练习: --1.显示当前用户拥有的表 select table_name from user_tables; --2.显示当前用户拥有的表,视图,同义词和序列 select * from user_catalog; --或者 select * from cat; --3.创建dept表,结构如下: 列名id name 数据类型number varchar2 长度7 25 create table dept2 ( id number(7), name varchar(25) ); --4.使用departments表中的数据填充dept表,只包含所需列insert into dept(id,name) select department_id,department_name from departments; --5.创建emp表,结构如下: 列名id last_name first_name dept_id 数据类型number varchar2 varchar2 number 长度7 25 25 7 create table emp (id number(7), last_name varchar2(25), first_name varchar2(25), det_id number(7) ); --6.修改emp表,从而允许更长的员工姓氏(50),并通过查询数据字典来确认 alter table emp modify (last_name varchar2(50)); --7.确认dept和emp表都在数据字典中 select table_name from user_tables where table_name='dept' or table_name='emp'; --8.根据employees表的结构创建employees2表,仅包含: employee_id,first_name,last_name,salary,department_id列, 将新表中的各列分别命名为: id,first_name,last_name,salary,dept_id create table employees2(id,first_name,last_name,salary,dept_id) as select employee_id,first_name,last_name,salary,department_id from employees; --9.删除emp表 drop table emp; --10.将employees2重命名为emp rename employees2 to emp; --11.向emp表添加一列名为birthday,日期类型,并通过查询数据字典来确认 alter table emp add(birthday date); --12.向emp表的列birthday添加备注(生日),并通过查询数据字典来确认 --要注意comment on column emp.birthday is'生日'; select * from user_col_comments where column_name='birthday'; --13.修改表emp,将salary的默认值设置为0 alter table emp modify(salary default(0)); oracle常用命令及格式 一：关于日志管理的 1.切换日志：sql> alter system switch logfile; 2.切换checkpoints：sql> alter system checkpoint; 3.增加日志组：sql> alter database add logfile [group 4] sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size1m; 4.增加日志成员 sql> alter database add logfile member sql> '/disk3/log1b.rdo' to group 1, sql> '/disk4/log2b.rdo' to group 2; 5.改变日志文件名字或路径 sql> alter database rename file 'c:/oracle/oradata/oradb/re do01.log' sql> to 'c:/oracle/oradata/redo01.log'; （此处注意，那个文件路径的输入格式） 6.删除日志文件组：sql> alter database drop logfile group 3; 7.删除日志文件成员 sql> alter database drop logfile member 'c:/oracle/oradata/ redo01.log'; 8.清除日志文件内容 sql> alter database clear [unarchived] logfile 'c:/oracle/l og2a.rdo'; 二、关于表空间管理的 1.创建表空间 sql> create tablespace tablespace_name datafile 'c:\oracle\ oradata\file1.dbf' size100m, sql> 'c:\oracle\oradata\file2.dbf' size100mminimum extent 5 50k [logging/nologging] sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0) sql> [online/offline] [permanent/temporary] [extent_managem ent_clause] 数据库（Oracle）运维工作内容及常用脚本命令2013-08-09 0个评论来源：LHDZ_BJ的专栏 收藏我要投稿数据库（Oracle）运维工作内容及常用脚本命令 1、系统资源状况： --内存及CPU资源 --linux,solaris,aix vmstat 5 --说明： 1）观察空闲内存的数量多少，以及空闲内存量是否稳定，如果不稳定就得想办法来解决，怎么解决还得看具体情况，一般可以通过调整相关内存参数来解决，各种操作系统输出指标、解释及内存调整参数及方法不完全一样； 2)观察CPU资源利用情况，首先，需要观察CPU上运行的任务数，也就是vmstat输出中位于第一列上的指标，如果该指标持续大于CPU核心数，应该引起注意；如果该指标持续大于CPU核心数的两倍，那么应该引起重视；如果持续为CPU 核心数的多倍，系统一般会出现应用可感知的现象，必须立刻想办法解决。当然，在观察该指标的同时，还要结合CPU利用率的指标情况，如：用户使用百分比,系统使用百分比,空闲百分比等指标，如果空闲百分比持续低于20%，应该引起注意；如果持续低于10%，应该引起重视；如果持续为0，系统一般会出现应用可感知的现象，应该立刻想办法解决问题； 3）CPU用户使用百分比和系统使用百分比的比例，也是应该注意的。一般来说，在一个状态正常的系统上，用户使用百分比应该比系统使用百分比大很多，几倍到十几倍甚至更高，如果系统使用百分比持续接近用户使用百分比，甚至大于用户使用百分比，说明系统的状态是不正常的，可能是硬件或者操作系统问题，也可能是应用问题。 --IO状况 --linux,solaris iostat -dx 5 --aix iostat 5 --说明： Oracle SQLPlus 常用命令及解释 1.@ 执行位于指定脚本中的SQLPlus语句。可以从本地文件系统或Web服务器中调用脚本。可以为脚本中的变量传递值。在iSQL*Plus中只能从Web服务器中调用脚本。 2.@@ 执行位于指定脚本中的SQL*Plus语句。这个命令和@（“at”符号）命令功能差不多。在执行嵌套的命令文件时它很有用，因为它会在与调用它的命令文件相同的路径或url中查找指定的命令文件。在iSQL*Plus中只支持url形式。 3./ 执行保存在SQL缓冲区中的最近执行的SQL命令或PL/SQL块。在SQL*Plus命令行中，可在命令提示符或行号提示符使用斜线（/）。也可在iSQL*Plus的输入区中使用斜线（/）。斜线不会列出要执行的命令。 4.ACCEPT 可以修改既有变量，也可定义一个新变量并等待用户输入初始值,读取一行输入并保存到给出的用户变量中。ACCEPT在iSQL*Plus中不可用。 5.APPEND 把指定文本添加到SQL缓冲区中当前行的后面。如果text的最前面包含一个空格可在APPEND和text间输入两个空格。如果text的最后是一个分号，可在命令结尾输入两个分号（SQL*Plus会把单个的分号解释为一个命令结束符）。APPEND 在iSQL*Plus中不可用。 6.ARCHIVE LOG 查看和管理归档信息。启动或停止自动归档联机重做日志，手工（显示地）归档指定的重做日志，或者显示重做日志文件的信息。 7.ATTRIBUTE 为对象类型列的给定属性指定其显示特性，或者列出单个属性或所有属性的当前显示特性。 8.BREAK 分开重复列。指定报表中格式发生更改的位置和要执行的格式化动作（例如，在列值每次发生变化时跳过一行）。只输入BREAK而不包含任何子句可列出当前的BREAK定义。 9.BTITLE 在每个报表页的底部放置一个标题并对其格式化，或者列出当前BTITLE定义。 Oracle数据库操作命令 1.登录数据库： SQL Window 与Command Window Sqlplus system/密码@orcl 2.关闭数据库： Sqlplus/as sysdba Shutdown immediate 3.启动数据库： Sqlplus/as sysdba(已登录时可不用再写) Startup 4.查看参数 ①查看数据库：show parameter db_name; ②查看实例：show parameter instance; ③查看实例名：show parameter instance_name; ④查看Oracle数据库中当前用户所拥有的表： select table_name from user_tables; ⑤查看Oracle数据库中当前用户所能访问的表： Select user，table_name from all_tables; ⑥查看Oracle数据库中本用户下所有的列： Select table_name,column_name from user_tab_columns; Oracle表空间： 5.在数据库orcl中，创建表空间的命令： Create tablespace 表空间名datafile ‘C:\表空间名.dbf’ size 20M; 6.为表空间中添加数据文件命令格式： Alter tablespace 表空间名add datafile ‘D:\表空间名.dbf’ size 100M; 7.创建表空间后，在数据字典中获得其相关信息（查看表空间的数据文件）： Select tablespace_name,file_name from dba_data_files; 8.创建表空间时，还可指定数据文件自动扩展机制(指定每次增长尺寸为5M) Create tablespace 表空间名datafile ‘C:\表空间名.dbf’ size 20M autoextend on next 5M; 允许物理文件无限制增长存在一定风险，此时可在创建时设定表空间的最大大小(如500M) Create tablespace 表空间名datafile ‘C:\表空间名.dbf’ size 20M autoextend on next 5M maxsize 500M; 9.修改数据库的默认表空间： Alter database default tablespace 表空间名； 查看某用户的缺省表空间： Select username,default_tablespace from dba_users where username=’用户名’； 修改某用户的缺省表空间： Alter user 用户名default tablespace 表空间名； 10.创建数据库用户： Create users 用户名identified by 密码default tablespace 表空间名; 修改用户的密码： Oracle的历史 ?Oracle 公司( 甲骨文) 创始人: Larry Ellison 32岁,公司提供数据库服务. ?公司成立于1977 年, 83 年公司更名为Oracle ,原名为”软件开发实验室”. ?Oracle 数据库适用于大型企业 ?竞争对手 –微软的SQLServer –IBM 的DB2 ?目前的版本 – 2.0~7.0 , 8.0 , 8i , 9i , 10g Oracle的服务： 我的电脑右键选择管理--服务和应用程序—服务 或 sqlplus 用户名@orcl 如果用户是管理员要在sqlplus 用户名/密码@主机字符串as sysdba “/”是用户名和密码分隔符号 “@”是密码和数据库的分隔符号 “orcl”是数据库的名称，在安装时指定 常用命令（互动） connect 切换用户 show user 显示当前用户 set linesize 1000 设置行显示长度 set pagesize 1000 设置分页长度 desc dept 查看表结构 select table_name from user_tables 查询当前用户的表 / 运行上一条SQL语句 clear screen 清除屏幕 edit 编辑 spool d:/a 保存输出结果到某个位置 spool off 保存结束 quit 退出 list 查看最后一条语句 @ 文件名.sql 运行外部文件中的SQL语句 oracle常用命令 命令解释 $Ps –ef|grep oracle 查看oracle进程是否启动 $ sqlplus "/as sysdba" 以sysdba角色登陆oracle数据库 SQL>startup 显示当前系统中已登录的人员。 SQL>shutdown immediate 关闭数据库 SQL>select * from v$version; 查看oracle数据库版本 SQL>select name from v$database; 查看数据库SID SQL>truncate table table_name 快速清空一个表 SQL>select * from all_users;查看数据库中所有用户 SQL>alter tablespacename offline;将表空间offline SQL> alter tablespacename online ;将表空间online $oerr ora 2236 查错误 alert_{ORACLE_SID}.log 数据库告警日志文件 *.TRC 数据库跟踪文件 Oracle说明 1、数文件：SPFILE不能直接阅读是二进制文件，需要转为文本 2、oracle数据库后，可以查看数据库状态是否open，如果open会显示open字样 SQL> select status, instance_role from v$instance; 3、PFILE：SQL> connect / as sysdba 从spfile创建pfile：SQL> create pfile from spfile; 从pfile创建spfile：CREA TE SPFILE FROM PFILE='/home/oracle/admin/pfile/init.ora'; 4、names是客户端或应用程序需要连接数据库时必须配置的，使用$tnsping service_aliasname可以测试出tns配置的是否正确 5、要文件listener.ora、Tnsnames.ora、Sqlnet.ora，这三个位置在$ORACLE_HOME/network/admin目录下。 6、库启动时要先启动listener Network配置：监听程序lsnrctl oracle数据库基本命令 oracle安装后 sys：超级管理员（dba），默认密码为：change_on_install system：系统管理员（dbaoper），默认密码为：manager; sys与system的不同在于sys能够create datebase而system则不能。scott：普通用户，默认密码：tiger 1.切换用户：conn 用户名/密码; SQL> conn system/manager; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as system SQL> conn sys/change_on_install as sysdba; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as SYS 注意：sys与其他用户在命令窗口切换时的不同。 2.修改密码：passw username；（普通用户可以修改自己密码，管理员可以修改其他人的密码） 3.显示当前用户。show user； 4.断开数据库同时推出：exit； 文件操作 5.运行sql脚本，start d：\a.sql; 6.编辑指定的sql脚本。Edit d：\a.sql； 7.将屏幕上指定的内容输出到指定文本中去。spool e：\b.sql;执行语句；spool off; 8.显示设置环境变量； 可以用来控制输出的各种格式，如果希望永久保存可以修改glogin.sql脚本。 Linesize（行宽）： show linesize；显示行宽 set linesize 90；设置行宽为90个字符。 Pagesize（页面大小）： Show pagesize；显示页面大小 Set pagesize 180；设置页面的小。 （做报表时可以用。一页设定几行。） oracle中常用函数大全 1、数值型常用函数 函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual; 第一章:日志管理 1. forcing log switches sql> alter system switch logfile; 2. forcing checkpoints sql> alter system checkpoint; 3. adding online redo log groups sql> alter database add logfile [group 4] sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo' size 1m; 4. adding online redo log members sql> alter database add logfile member sql> '/disk3/log1b.rdo' to group 1, sql> '/disk4/log2b.rdo' to group 2; 5. changes the name of the online redo logfile sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log' sql> to 'c:/oracle/oradata/redo01.log'; 6. drop online redo log groups sql> alter database drop logfile group 3; 7. drop online redo log members sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log'; 8.clearing online redo log files sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo'; https://www.doczj.com/doc/944698677.html,ing logminer analyzing redo logfiles a. in the init.ora specify utl_file_dir = ' ' b. sql> execute dbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log'; c. sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log', sql> dbms_logmnr.new; d. sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log', sql> dbms_logmnr.addfile; e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora '; f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters sql> v$logmnr_logs; g. sql> execute dbms_logmnr.end_logmnr; 第二章:表空间管理 1. create tablespaces sql> create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf' size 100m, oracle set命令详解 SET命令参数详解 我好如容易录入的,希望对大家有帮助,如果有错误也请指正.有更好的也请分享. SQL*PLUS维护系统变量,也称SET变量,利用它可为SQL*PLUS交互建立一个特殊的环境,如:设 置NUMBER数据的显示宽度;设置每页的行数;设置列的宽度等。可用SET命令改变这些系统变 量,也可用SHOW命令列出它们. 使用set命令的语法如下: SET 系统变量值 其中系统变量及其可选值如下: ARRAY[SIZE] {20(默认值)|n} AUTO[COMMIT] {OFF(默认值)|ON|IMM[EDIATE]} BLO[CKTERMINATOR] {.(默认值)|C} CMDS[EP] {;|C|OFF(默认值)|ON} COM[PATIBILITY] {V5|V6|V7|NATIVE(默认值)} CON[CAT] {.(默认值)|C|OFF|ON(默认值)} COPYC[OMMIT] {0(默认值)|n} CRT crt DEF[INE] {&|C|OFF|ON(默认值)} ECHO {OFF|ON} EMBEDDED {OFF(默认值)|ON} ESC[APE] { (默认值)|C|OFF(默认值)|ON} FEED[BACK] {6(默认值)|n|OFF|ON} FLU[SH] {OFF|ON(默认值)} HEA[DING] {OFF|ON(默认值)} HEADS[EP] {|(默认值)|C|OFF|ON(默认值)} LIN[ESIZE] {80(默认值)|n} LONG {80(默认值)|n} LONGC[HUNKSIZE] {80(默认值)|n} MAXD[ATA] n NEWP[AGE] {1(默认值)|n} NULL text NUMF[ORMAT] 格式 NUM[WIDTH] {10(默认值)|n} SQL*PLUS命令的使用大全[zt] Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中，可以运行sql*plus命令与sql*plus语句。 我们通常所说的DML、DDL、DCL语句都是sql*plus语句，它们执行完后，都可以保存在一个被称为sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。 除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。 下面就介绍一下一些常用的sql*plus命令： 1. 执行一个SQL脚本文件 SQL>start file_name SQL>@ file_name 我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。 2. 对当前的输入进行编辑 SQL>edit 3. 重新运行上一次运行的sql语句 SQL>/ 4. 将显示的内容输出到指定文件 SQL> SPOOL file_name 在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。 5. 关闭spool输出 SQL> SPOOL OFF 只有关闭spool输出，才会在输出文件中看到输出的内容。 6．显示一个表的结构 SQL> desc table_name 7. COL命令： 主要格式化列的显示形式。 该命令有许多选项，具体如下： COL[UMN] [{ column|expr} [ option ...]] Option选项可以是如下的子句: ALI[AS] alias CLE[AR] FOLD_A[FTER] FOLD_B[EFORE] FOR[MA T] format HEA[DING] text JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} LIKE { expr|alias} NEWL[INE] NEW_V[ALUE] variable NOPRI[NT]|PRI[NT] NUL[L] text OLD_V[ALUE] variable ON|OFF WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED] 1). 改变缺省的列标题 SQL*PLUS命令的使用大全 Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中，可以运行sql*plus命令与sql*plus 语句。 我们通常所说的DML、DDL、DCL语句都是sql*plus语句，它们执行完后，都可以保存在一个被称为sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。 除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。 下面就介绍一下一些常用的sql*plus命令： 1. 执行一个SQL脚本文件 SQL>start file_name SQL>@ file_name 我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。 2. 对当前的输入进行编辑 SQL>edit 3. 重新运行上一次运行的sql语句 SQL>/ 4. 将显示的内容输出到指定文件 SQL> SPOOL file_name 在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。 5. 关闭spool输出 SQL> SPOOL OFF 只有关闭spool输出，才会在输出文件中看到输出的内容。 6．显示一个表的结构 SQL> desc table_name 7. COL命令： 主要格式化列的显示形式。 该命令有许多选项，具体如下： COL[UMN] [{ column|expr} [ option ...]] Option选项可以是如下的子句: ALI[AS] alias CLE[AR] FOLD_A[FTER] FOLD_B[EFORE] FOR[MA T] format Oracle 命令大全 底部为环境变量配置路径。 1 运行SQLPLUS工具 sqlplus 2 以OS的默认身份连接 / as sysdba 3 显示当前用户名 show user 4 直接进入SQLPLUS命令提示符 sqlplus /nolog 5 在命令提示符以OS身份连接 connect / as sysdba 6 以SYSTEM的身份连接 connect system/xxxxxxx@服务名 7 显示当然用户有哪些表 select * from tab; 8 显示有用户名和帐户的状态 select username,account_status from dba_users; 9 将SCOTT帐号解锁(加锁) alter user scott account unlock(lock); 10 以SCOTT的身份连接并且查看所属表 connect scott/tiger select * from tab; 11 查看EMP的表结构及记录内容 desc emp select empno,ename from emp; 12 以OS的身份登看SGA,共享池,CACHE的信息 connect / as sysdba show sga select name,value/1024/1024 from v$sga; show parameter shared_pool_size select value/1024/1024 from v$parameter where name ='shared_pool_size'; show parameter db_cache_size select value/1024/1024 from v$parameter where name ='db_cache_size'; 13 查看所有含有SIZE的信息 show parameter size bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_block_size integer 4096 db_cache_size big integer 33554432 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0 NAME TYPE V ALUE ------------------------------------ ----------- ------------- global_context_pool_size string hash_area_size integer 1048576 java_max_sessionspace_size integer 0 java_pool_size big integer 33554432 large_pool_size big integer 8388608 max_dump_file_size string UNLIMITED object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 olap_page_pool_size integer 33554432 oracle_trace_collection_size integer 5242880 parallel_execution_message_size integer 2148 NAME TYPE V ALUE ------------------------------------ ----------- ------------- sga_max_size big integer 143727516 shared_pool_reserved_size big integer 2516582 shared_pool_size big integer 50331648 sort_area_retained_size integer 0 sort_area_size integer 524288 workarea_size_policy string AUTO 14 显示SGA的信息 select * from v$sgastat; POOL NAME BYTES oracle系统默认的用户和密码是 创建数据库是创建的用户 scott 密码是 tiger sys 密码是 change_on_install system 密码是 manager sysman 密码是 oem_temp 也可以 sqlplus / as sysdba 不用密码登录！！ 登录oracle数据库时常用的操作命令整理 1、su – oracle 不是必需，适合于没有DBA密码时使用，可以不用密码来进入sqlplus界面。 2、sqlplus /nolog 或sqlplus system/manager 或./sqlplus system/manager@ora9i; 3、SQL>connect / as sysdba ;（as sysoper）或 connect internal/oracle AS SYSDBA ;(scott/tiger) conn sys/change_on_install as sysdba; 4、SQL>startup; 启动数据库实例 5、查看当前的所有数据库: select * from v$database; select name from v$database; desc v$databases; 查看数据库结构字段 7、怎样查看哪些用户拥有SYSDBA、SYSOPER权限: SQL>select * from V_$PWFILE_USERS; Show user;查看当前数据库连接用户 8、进入test数据库：database test; 9、查看所有的数据库实例：select * from v$instance； 如：ora9i 10、查看当前库的所有数据表： SQL> select TABLE_NAME from all_tables; oracle查询语句大全--基本命令大全一 1.create user username identified by password;//建用户名和密码oracle ,oracle 2.grant connect,resource,dba to username;//授权grant connect,resource,dba,sysdba to username; 3.connect username/password//进入。 4.select table_name,column_name from user_tab_columns where table_name='mview_log';//查询表中的表名，字段名等等。 5. 如何执行脚本SQL文件? SQL>@PATH/filename.sql; 6.Oracle oledb 提供者在command中执行多条SQL语句与SQL SERVER有少许差别,SQL Server只需使用";"分割多条SQL语句,而Oracle需要遵守ORACLE调用规范,即除分号分割外,还需以begin /end;包围语句体. 使用C#描述应如下所示: https://www.doczj.com/doc/944698677.html,mandText = "begin INSERT INTO GROUP_INFO (GROUP_ID, GROUP_NAME) V ALUES (1, \'2\'); INSERT INTO GROUP_INFO(GROUP_ID, GROUP_NAME) V ALUES (2, \'2\'); end;"; 7.查询用户下的表的信息select distinct table_name from user_tab_columns; 8.如何搜索出前N条记录？Select a.*,rownum from (select * from cardkind order by cardkind ) a where rownum Oracle数据库的日常使用命令 1.基本知识 (2) 2.启动和关闭数据库 (3) 3.控制监听 (3) 4.数据库用户管理 (3) 5.Oracle的权限管理 (4) 6.更改字符集为中文 (5) 7.查询语句 (5) 8.表空间管理 (6) 9.数据文件被误删后的处理 (7) 10.查询当前系统的配置参数 (7) 11.显示当前用户 (8) 12.Oracle排错处理 (8) 13.查看表结构 (8) 14.查看数据库文件 (8) 15.将select查询出的结果保存至一个文件 (9) 16.存储过程 (9) 17.数据库的备份与恢复 (10) Export 转入程序 (10) Import 恢复程序 (12) 增量卸出/装入 (14) 18.如何查看各个表空间占用磁盘情况？ (15) 19.如何知道数据裤中某个表所在的tablespace? (15) 20.内核参数的应用 (15) 21.如何单独备份一个或多个表？ (16) 22.如何单独备份一个或多个用户？ (16) 23.如何显示当前连接用户? (16) 24.如何外连接? (16) 25.如何执行脚本SQL文件? (17) 26.如何搜索出前N条记录？ (18) 27.为表创建序列 (18) 28.查看本用户下的各种对象的SQL脚本 (18) 29.SQL*Plus系统环境变量有哪些？如何修改？ (20) 30.如何在PL/SQL中读写文件？ (20) 31.某个数据文件损坏，如何打开数据库？ (21) 1. 基本知识 一个表空间只能属于一个数据库 每个数据库最少有一个控制文件（建议3个，分别放在不同的磁盘上） 每个数据库最少有一个表空间（SYSTEM表空间） 建立SYSTEM表空间的目的是尽量将目的相同的表存放在一起，以提高使用效率，只应存放数据字典 每个数据库最少有两个联机日志组，每组最少一个联机日志文件 一个数据文件只能属于一个表空间 一个数据文件一旦被加入到一个表空间中，就不能再从这个表空间中移走，也不能再加入到其他表空间中 建立新的表空间需要建立新的数据文件 SPOOL将屏幕所有的输出输出到指定文件 -- spool 文件路径名; spool g:\mysql.sql; --业务操作 --结束输出 spool off; 执行一个SQL脚本文件 我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。 --start file_name -- @ file_name start g:\mysql.sql; @ g:\mysql.sql; 对当前的输入进行编辑 edit ed 重新运行上一次运行的sql语句 / 显示一个表的结构 desc table_name ; 清屏 clear screen; 退出 exit; 置当前session是否对修改的数据进行自动提交 --SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n} set autocommit on; 在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句 -- SET ECHO {ON|OFF}; set echo on; 是否显示当前sql语句查询或修改的行数 --SET FEED[BACK] {6|n|ON|OFF} -- 默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数 set feedback 1; 是否显示列标题 --当set heading off 时，在每页的上面不显示列标题，而是以空白行代替 --SET HEA[DING] {ON|OFF} set heading on; 设置一行可以容纳的字符数 -- 如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示 --SET LIN[ESIZE] {80|n} set linesize 100; 设置页与页之间的分隔 -- SET NEWP[AGE] {1|n|NONE} --当set newpage 0 时，会在每页的开头有一个小的黑方框。 --当set newpage n 时，会在页和页之间隔着n个空行。 --当set newpage none 时，会在页和页之间没有任何间隔 set newpage 1; 设置一页有多少行数 --如果设为0，则所有的输出内容为一页并且不显示列标题 --SET PAGES[IZE] {24|n} set pagesize 20; 是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。 --SET SERVEROUT[PUT] {ON|OFF} set serveroutput on; 是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。 --在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，--设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度 --SET TERM[OUT] {ON|OFF} set termout off; 在dos里连接oracle数据库 CONNECT user_name/passwd@l_jiayou 1.create user username identified by password;//建用户名和密码oracle ,oracle 2.grant connect,resource,dba to username;//授权 grant connect,resource,dba,sysdba to username; 3.connect username/password//进入。 4.select table_name,column_name from user_tab_columns where table_name='mview_log';//查询表中的表名，字段名等等。 5. 如何执行脚本SQL文件? SQL>@PATH/filename.sql; 6.Oracle oledb 提供者在command中执行多条SQL语句与SQL SERVER有少许差别,SQL Server 只需使用";"分割多条SQL语句,而Oracle需要遵守ORACLE调用规范,即除分号分割外,还需以begin /end;包围语句体. 使用C#描述应如下所示: https://www.doczj.com/doc/944698677.html,mandText = "begin INSERT INTO GROUP_INFO (GROUP_ID, GROUP_NAME) VALUES (1, \'2\'); INSERT INTO GROUP_INFO(GROUP_ID, GROUP_NAME) VALUES (2, \'2\'); end;"; 7.查询用户下的所有表 select distinct table_name from user_tab_columns; 8.如何搜索出前N条记录？Select a.*,rownum from (select * from cardkind order by cardkind ) a where rownumoracle常用命令及格式
数据库(Oracle)运维工作内容及常用脚本命令
Oracle SQLPlus 常用命令及解释
Oracle数据库操作命令
Oracle基本语法
数据库常用命令
oracle数据库基本命令
oracle中常用函数大全
oracle11g常用命令.
oracle set命令大全
PL_SQL命令的使用大全
oracle命令行大全
oracle常用命令大全和环境变量路径
登录oracle数据库时常用的操作命令整理
Oracle查询语句基本命令一
Oracle数据库的日常使用命令(SAG_考核)
Oracle 数据库 常用命令
oracle命令大全
相关主题
文本预览