- 检查Oracle安装
- 首先,以超级管理员的身份登录oracle
-
sqlplus sys/bjsxt as sysdba -
- 然后,解除对scott用户的锁
-
alter user scott account unlock; - 那么这个用户名就能使用了。
- (默认全局数据库名orcl)
-
- 1、select
ename, sal * 12 from emp; //计算年薪 - 2、select
2*3 from dual; //计算一个比较纯的数据用dual表 - 3、select
sysdate from dual; //查看当前的系统时间 - 4、select
ename, sal*12 anuual_sal from emp; //给搜索字段更改名称(双引号 keepFormat 别名有特殊字符,要加双引号)。 - 5、任何含有空值的数学表达式,最后的计算结果都是空值。
- 6、select
ename||sal from emp; //(将sal的查询结果转化为字符串,与ename连接到一起,相当于Java中的字符串连接) - 7、select
ename||'afasjkj' from emp; //字符串的连接 - 8、select
distinct deptno from emp; //消除deptno字段重复的值 - 9、select
distinct deptno , job from emp; //将与这两个字段都重复的值去掉 - 10、select
* from emp where deptno=10; //(条件过滤查询) - 11、select
* from emp where empno > 10; //大于 过滤判断 - 12、select
* from emp where empno <> 10 //不等于 过滤判断 - 13、select
* from emp where ename > 'cba'; //字符串比较,实际上比较的是每个字符的AscII值,与在Java中字符串的比较是一样的 - 14、select
ename, sal from emp where sal between 800 and 1500; //(between and过滤,包含800 1500) - 15、select
ename, sal, comm from emp where comm is null; //(选择comm字段为null的数据) - 16、select
ename, sal, comm from emp where comm is not null; //(选择comm字段不为null的数据) - 17、select
ename, sal, comm from emp where sal in (800, 1500,2000); //(in 表范围) - 18、select
ename, sal, hiredate from emp where hiredate > '02-2月-1981'; //(只能按照规定的格式写) - 19、select
ename, sal from emp where deptno =10 or sal >1000; - 20、select
ename, sal from emp where deptno =10 and sal >1000; - 21、select
ename, sal, comm from emp where sal not in (800, 1500,2000); //(可以对in指定的条件进行取反) - 22、select
ename from emp where ename like '%ALL%'; //(模糊查询) - 23、select
ename from emp where ename like '_A%'; //(取第二个字母是A的所有字段) - 24、select
ename from emp where ename like '%/%%'; //(用转义字符/查询字段中本身就带%字段的) - 25、select
ename from emp where ename like '%$%%' escape '$'; //(用转义字符/查询字段中本身就带%字段的) - 26、select
* from dept order by deptno desc; (使用order by desc字段 对数据进行降序排列 默认为升序asc); - 27、select
* from dept where deptno <>10 order by deptno asc; //(我们可以将过滤以后的数据再进行排序) - 28、select
ename, sal, deptno from emp order by deptno asc, ename desc; //(按照多个字段排序 首先按照deptno升序排列,当detpno相同时,内部再按照ename的降序排列) - 29、select
lower(ename) from emp; //(函数lower() 将ename搜索出来后全部转化为小写); - 30、select
ename from emp where lower(ename) like '_a%'; //(首先将所搜索字段转化为小写,然后判断第二个字母是不是a) - 31、select
substr(ename, 2, 3) from emp; //(使用函数substr() 将搜素出来的ename字段从第二个字母开始截,一共截3个字符) - 32、select
chr(65) from dual; //(函数chr() 将数字转化为AscII中相对应的字符) - 33、select
ascii('A') from dual; //(函数ascii()与32中的chr()函数是相反的 将相应的字符转化为相应的Ascii编码) ) - 34、select
round(23.232) from dual; //(函数round() 进行四舍五入操作) - 35、select
round(23.232, 2) from dual; //(四舍五入后保留的小数位数 0 个位 -1 十位) - 36、select
to_char(sal, '$99,999.9999')from emp; //(加$符号加入千位分隔符,保留四位小数,没有的补零) - 37、select
to_char(sal, 'L99,999.9999')from emp; //(L 将货币转化为本地币种此处将显示¥人民币) - 38、select
to_char(sal, 'L00,000.0000')from emp; //(补零位数不一样,可到数据库执行查看) - 39、select
to_char(hiredate, 'yyyy-MM-DD HH:MI:SS') from emp; //(改变日期默认的显示格式) - 40、select
to_char(sysdate, 'yyyy-MM-DD HH:MI:SS') from dual; //(用12小时制显示当前的系统时间) - 41、select
to_char(sysdate, 'yyyy-MM-DD HH24:MI:SS') from dual; //(用24小时制显示当前的系统时间) - 42、select
ename, hiredate from emp where hiredate > to_date('1981-2-20 12:24:45','YYYY-MM-DD HH24:MI:SS'); //(函数to-date 查询公司在所给时间以后入职的人员) - 43、select
sal from emp where sal > to_number('$1,250.00', '$9,999.99'); //(函数to_number()求出这种薪水里带有特殊符号的) - 44、select
ename, sal*12 + nvl(comm,0) from emp; //(函数nvl() 求出员工的"年薪 + 提成(或奖金)问题") - 45、select
max(sal) from emp; // (函数max() 求出emp表中sal字段的最大值) - 46、select
min(sal) from emp; // (函数max() 求出emp表中sal字段的最小值) - 47、select
avg(sal) from emp; //(avg()求平均薪水); - 48、select
to_char(avg(sal), '999999.99') from emp; //(将求出来的平均薪水只保留2位小数) - 49、select
round(avg(sal), 2) from emp; //(将平均薪水四舍五入到小数点后2位) - 50、select
sum(sal) from emp; //(求出每个月要支付的总薪水) -
- /组函数(共5个):将多个条件组合到一起最后只产生一个数据//min()
max() avg() sum() count()/ - 51、select
count(*) from emp; //求出表中一共有多少条记录 - 52、select
count(*) from emp where deptno=10; //再要求一共有多少条记录的时候,还可以在后面跟上限定条件 - 53、select
count(distinct deptno) from emp; //统计部门编号前提是去掉重复的值 - 聚组函数group
by() // - 54、select
deptno, avg(sal) from emp group by deptno; //按照deptno分组,查看每个部门的平均工资 - 55、select
max(sal) from emp group by deptno, job; //分组的时候,还可以按照多个字段进行分组,两个字段不相同的为一组 - 56、select
ename from emp where sal = (select max(sal) from emp); //求出 - 57、select
deptno, max(sal) from emp group by deptno; //搜素这个部门中薪水最高的的值 - //having函数对于group
by函数的过滤 不能用where// - 58、select
deptno, avg(sal) from emp group by deptno having avg(sal) >2000; (order by )//求出每个部门的平均值,并且要 > 2000 - 59、select
avg(sal) from emp where sal >1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;//求出sal>1200的平均值按照deptno分组,平均值要>1500最后按照sal的倒序排列 - 60、select
ename,sal from emp where sal > (select avg(sal) from emp); //求那些人的薪水是在平均薪水之上的。 - 61、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); //查询每个部门中工资最高的那个人 - ///等值连接//
- 62、select
e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; //自连接,把一张表当成两张表来用 - 63、select
ename, dname from emp, dept; //92年语法 两张表的连接 笛卡尔积。 - 64、select
ename, dname from emp cross join dept; //99年语法 两张表的连接用cross join - 65、select
ename, dname from emp, dept where emp.deptno = dept.deptno; // 92年语法 表连接 + 条件连接 - 66、select
ename, dname from emp join dept on(emp.deptno = dept.deptno); // 新语法 - 67、select
ename,dname from emp join dept using(deptno); //与66题的写法是一样的,但是不推荐使用using : 假设条件太多 - ///非等值连接///
- 68、select
ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); //两张表的连接 此种写法比用where更清晰 - 69、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%'; //三张表的连接 - 70、select
e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno); //自连接第二种写法,同62 - 71、select
e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno); //左外连接 把左边没有满足条件的数据也取出来 - 72、select
ename, dname from emp e right join dept d on(e.deptno = d.deptno); //右外连接 - 73、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);//求每个部门平均薪水的等级 - 74、select
ename from emp where empno in (select mgr from emp); // 在表中搜索那些人是经理 - 75、select
sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal)); // 面试题 不用组函数max()求薪水的最大值 - 76、select
deptno, max_sal from -
(select avg(sal) max_sal,deptno from emp group by deptno) -
where max_sal = -
(select max(max_sal) from -
(select avg(sal) max_sal,deptno from emp group by deptno) -
);//求平均薪水最高的部门名称和编号。 - 77、select
t1.deptno, grade, avg_sal from -
(select deptno, grade, avg_sal 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) -
) t1 -
join dept on (t1.deptno = dept.deptno) -
where t1.grade = -
( -
select min(grade) from -
(select deptno, grade, avg_sal 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) -
) -
)//求平均薪水等级最低的部门的名称 哈哈 确实比较麻烦 - 78、create
view v$_dept_avg_sal_info as -
select deptno, grade, avg_sal 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); -
//视图的创建,一般以v$开头,但不是固定的 -
-
-
-
-
- 79、select
t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1 -
join dept on (t1.deptno = dept.deptno) -
where t1.grade = -
( -
select min(grade) from -
v$_dept_avg_sal_info t1 -
) -
)//求平均薪水等级最低的部门的名称 用视图,能简单一些,相当于Java中方法的封装 -
- 80、---创建视图出现权限不足时候的解决办法:
-
conn sys/admin as sysdba; -
显示:连接成功 Connected -
grant create table, create view to scott; -
显示: 授权成功 Grant succeeded - 81、-------求比普通员工最高薪水还要高的经理人的名称
------- -
select ename, sal from emp where empno in -
(select distinct mgr from emp where mgr is not null) -
and sal > -
( -
select max(sal) from emp where empno not in -
(select distinct mgr from emp where mgr is not null) -
) - 82、---面试题:比较效率
-
select * from emp where deptno = 10 and ename like '%A%';//好,将过滤力度大的放在前面 -
select * from emp where ename like '%A% and deptno = 10; - 83、-----表的备份
-
create table dept2 as select * from dept; - 84、-----插入数据
-
insert into dept2 values(50,'game','beijing'); -
----只对某个字段插入数据 -
insert into dept2(deptno,dname) values(60,'game2'); - 85、-----将一个表中的数据完全插入另一个表中(表结构必须一样)
-
insert into dept2 select * from dept; - 86、-----求前五名员工的编号和名称(使用虚字段rownum
只能使用 < 或 = 要使用 > 必须使用子查询) -
select empno,ename from emp where rownum <= 5; - 86、----求10名雇员以后的雇员名称--------
-
select ename from (select rownum r,ename from emp) where r > 10; - 87、----求薪水最高的前5个人的薪水和名字---------
-
select ename, sal from (select ename, sal from emp order by sal desc) where rownum <=5; - 88、----求按薪水倒序排列后的第6名到第10名的员工的名字和薪水--------
-
select ename, sal from -
(select ename, sal, rownum r from -
(select ename, sal from emp order by sal desc) -
) -
where r>=6 and r<=10 - 89、----------------创建新用户---------------
-
1、backup scott//备份 -
exp//导出 -
2、create user -
create user guohailong identified(认证) by guohailong default tablespace users quota(配额) 10M on users -
grant create session(给它登录到服务器的权限),create table, create view to guohailong -
3、import data -
imp - 90、-----------事务回退语句--------
-
rollback; -
- 91、-----------事务确认语句--------
-
commit;//此时再执行rollback无效 -
- 92、当正常断开连接的时候例如exit,事务自动提交。
当非正常断开连接,例如直接关闭dos窗口或关机,事务自动提交 - 93、有3个表S,C,SC
-
S(SNO,SNAME)代表(学号,姓名) -
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师) -
SC(SNO,CNO,SCGRADE)代表(学号,课号成绩) -
问题: -
1,找出没选过“黎明”老师的所有学生姓名。 -
2,列出2门以上(含2门)不及格学生姓名及平均成绩。 -
3,即学过1号课程有学过2号课所有学生的姓名。 -
答案: -
1、 -
select sname from s join sc on(s.sno = sc.sno) join c on (sc.cno = c.cno) where cteacher <> '黎明'; -
2、 -
select sname where sno in (select sno from sc where scgrade < 60 group by sno having count(*) >=2); -
3、 -
select sname from s where sno in (select sno, from sc where cno=1 and cno in -
(select distinct sno from sc where cno = 2); -
) -
Oracle之SQL基础
最新推荐文章于 2023-11-29 16:23:33 发布