sql练习题

启动oracle：
su -
su - oracle
sqlplus '/as sysdba'
startup
exit
lsnrctl start
lsnrctl status

关闭oracle：
shutdown normal:NORMAL关闭方式是正常关闭方式，是默认关闭方式。在这种关机方式下，数据库服务器必须等待所有客户端从Oracle中正常退出时，才可以关闭数据库。
shutdown immediate:系统将连接到数据库的所有用户尚未提交的事务全部回退，中断连接，然后关闭数据库。
shutdown abort:ABORT关闭方式为异常方式，使用这种方式关闭数据库时，系统立即将数据库实例关闭，对事务膛数据完整性不作检查。是最快的关机方式。
shutdown transactional:数据库必须等待所有的客户运行的事务结束，提交或回退。

 

创建用户：
su -
su - oracle
sqlplus '/as sysdba'
create user briup identified by briup;
grant connect,resource to briup
grant create any index to resource;

给表、列加注释
SQL>comment on table 表 is '表注释';
SQL>comment on column 表.列 is '列注释';
SQL> select * from user_tab_comments where comments is not null;
SQL> select * from user_col_comments where comments is not null;

取系统时间：
select sysdate from dual;
select systimestamp from dual;(带毫秒)

在字符串里加回车
select 'Welcome to visit'||chr(10)||'www.briup.com' from dual;

修改oracel数据库的默认日期
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
OR
alter session set nls_date_format='DD-MON-YY'
可以在init.ora中加上一行
nls_date_format='yyyy-mm-dd hh24:mi:ss'

设置日期：
alter session set nls_date_language='american';
设置人民币符号
alter session set nls_currency='RMB';
select to_char(14324324323233.09,‘l999g999g999g999g999g999g999d99’) from dual;这样将返回”RMB14,324,324,323,233.09“如何将那个RMB换为￥人民币符号？？我查了以下SELECT * FROM nls_session_parameters其中NLS_CURRENCY的值为RMB，如何修改该字符？

select to_char(14324324323233.09,'l999g999g999g999g999g999g999d99' ,'nls_currency=￥') from dual;

 

查看oracle版本
SQL>select * from v$version;
查看oracle数据库名
SQL>select name from v$database;
查看用户下所有的表
SQL>select * from user_tables;

查看活动用户
用select sid,serial#,status,username from v$session查找状态为INACTIVE的session,用
alter system kill session '14,3534' 查杀。

解除用户锁定：
alter user briup account unlock;

show user：显示用户
set pause on/off/text（暂停时提示的文本内容） 指定显示时是否暂停输出
set echo on/off：是否显示脚本内容
set verify on/off：是否显示新旧变量值
set feedback on/off：打开/关掉select后的提示”有多少行选中”
set linesize 80 设置行宽为80个字符
show linesize 查看变量linesize 的设置情况
set pagesize 24 设置每页显示多少行
column 字段名format a数字[word_wrapped]; 说明: 一行只显示数字位的长度, 超过长度折行,加word_wrapped后, 单词不会折行
一、非等连接
非等连接一般用在没有明确的等量关系的两个表；
最简单的说：非等连接就是在连接中没有“=”出现的连接。
SQL> SELECT e.ename, e.job, e.sal, s.grade
2 FROM emp e, salgrade s
3 WHERE e.sal BETWEEN s.losal AND s.hisal;
说明：Create a non-equijoin to evaluate an employee’s salary grade. The salary 必须在另一个表中最高和最低之间。
其他操作符<= >= 也可以实现，但是BETWEEN是非常简单实用的。
BETWEEN ....AND是指闭区间的，这点要注意 ，请大家测试。
二、外连接
语法结构：SELECT table.column, table.column
FROM table1, table2
WHERE table1.column = table2.column(+);
实例：
SQL> SELECT e.last_name, e.id, c.name,c.sales_rep_id
FROM s_emp e, s_customer c
WHERE e.id(+)= c.sales_rep_id
ORDER BY e.id;
显示.....，即使有的客户没有销售代表。
* 可以理解为有+号的一边出现了NULL，也可以做为合法的条件。
外连接的限制：
1、外连接符只能出现在信息缺少的那边。
2、在条件中，不能用 IN 或者 OR做连接符。
三、自连接
同一个表中使用连接符进行查询；
FROM 的后面用同一个表的两个别名。
实例：
SQL> SELECT worker.last_name||’ works for ’||
2 manager.last_name
3 FROM s_emp worker, s_emp manager
4 WHERE worker.manager_id = manager.id;
意味着：一个员工的经理ID匹配了经理的员工号，但这个像绕口令的连接方式并不常用。
以后我们会见到一种子查询：
select last_name from s_emp where salary=(select max(salary) from s_emp)
也可以看作是一种变向的自连接

rownum ：
用>号不起作用
用=号只能对1操作
用>=号只能对1操作，表示选出表中所有数据
用<,<=号没有限制

create table student(
 id  number(5) primary key,
 name varchar2(10),
 age  number(3)
);

查看表中第2行：
1）
select * from student where rownum<=2
minus
select * from student where rownum<=1;
2)
select * from (
 select s.*,rownum r from student s) where r=2;
3)
select * from (
 select * from student
 where rownum<=2
 order by rownum desc) where rownum=1;

删除表中第2行：
1）：
delete from student
where rowid =(
 select rowid from student where rownum<=2
 minus
 select rowid from student where rownum<=1);
2）：
delete from student where rowid =(
 select rid from (
  select rowid rid,rownum rnum
  from student )
 where rnum=2);
3）：
delete from student where id=(
 select id from (
  select s.id,rownum r from student s)
 where r=2);

找出表中相同记录:
select name,age,count(*)
from student
group by name,age
having count(*)>1;

删除表中相同记录:
delete from student where rowid in(
 select s1.rowid,s1.* from student s1,student s2
 where s1.rowid>s2.rowid and s1.name=s2.name and s1.age=s2.age);

删除表中相同记录并留一条：
delete from student a
where a.rowid!=( 
 select max(b.rowid) from student b
 where a.name=b.name and a.age=b.age);

 

select r.id,r.name,count(d.id) "# OF DEPT"
from s_dept d,s_region r
where d.region_id=r.id
group by r.id,r.name;