db + oracle scott / emp / dept / salgrade / bonus
s
注意事项 | 示例或解释 |
---|---|
列的选择 | 在 SELECT 语句中,除了聚合函数外的每个列都必须在 GROUP BY 子句中。 |
聚合函数 | 通常需要包含聚合函数,例如 COUNT() , SUM() , AVG() , MAX() , 或 MIN() 。 |
HAVING 子句 | 如果需要进一步过滤结果,可以使用 HAVING 子句。 |
列的选择 | 在 ORDER BY 子句中可以按照查询中的任意列进行排序。 |
排序顺序 | 默认情况下,排序是升序的(ASC)。可以使用 DESC 进行降序排序。 |
数字和字符串排序 | SQL 对数字和字符串的排序方式可能会不同。确保理解你的数据类型,并根据需要选择正确的排序方式。 |
列的选择 | GROUP BY 和 ORDER BY 可以引用不同的列,但是在 SELECT 子句中引用的列必须在 GROUP BY 子句中。 |
别名问题 | 在 ORDER BY 子句中不能使用 SELECT 中定义的列别名。 |
聚合函数和排序 | 如果在 SELECT 子句中使用了聚合函数,而且同时也使用了 ORDER BY ,则应该按照聚合函数的别名或索引进行排序。 |
多列排序 | 可以同时按照多个列进行排序。在 ORDER BY 子句中,列的顺序决定了优先级。 |
jdbc 驱动包下载
https://repo1.maven.org/maven2/com/oracle/database/jdbc/
- Oracle 10g 数据库环境
1 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod 2 PL/SQL Release 10.2.0.3.0 - Production 3 CORE 10.2.0.3.0 Production 4 TNS for 32-bit Windows: Version 10.2.0.3.0 - Production 5 NLSRTL Version 10.2.0.3.0 - Production
- 服务端创建用户表空间,创建txj账户
C:\Documents and Settings\Administrator>sqlplus / as sysdba --表空间默认50M,自动扩展10M,最大空间100M。 先建好文件夹testspace_datafile。 create tablespace testspace logging datafile 'c:\testspace_datafile\108.ora' size 50M autoextend on next 10M maxsize 100M; --创建用户,指定表空间testspace,临时表空间temp,并解锁txj账户 create user txj profile default identified by txj default tablespace testspace temporary tablespace temp account unlock; --给普通用户txj赋权,CONNECT表示连库权限 , RESOURCE表示开发人员权限,DBA表示管理员权限 grant connect,resource,dba to txj;
- dba权限txj用户复制scott用户表,防止捣烂scoot表数据
-- dba权限txj用户复制scott用户表,防止捣烂scoot表数据 create table salgrade as select * from scott.emp; create table salgrade as select * from scott.dept; create table salgrade as select * from scott.bonus; create table salgrade as select * from scott.salgrade;
- scott.emp员工表结构 , https://www.cnblogs.com/huyong/archive/2011/06/03/2071228.html
Name Type Nullable Default Comments -------- ------------ -------- ------- -------- EMPNO NUMBER(4) 员工号 ENAME VARCHAR2(10) Y 员工姓名 JOB VARCHAR2(9) Y 工作 MGR NUMBER(4) Y 上级编号 HIREDATE DATE Y 雇佣日期 SAL NUMBER(7,2) Y 薪金 COMM NUMBER(7,2) Y 佣金 DEPTNO NUMBER(2) Y 部门编号
- scott.dept部门表 , 提示:工资=薪金+佣金
Name Type Nullable Default Comments ------ ------------ -------- ------- -------- DEPTNO NUMBER(2) 部门编号 DNAME VARCHAR2(14) Y 部门名称 LOC VARCHAR2(13) Y 地点
- scott.emp表的现有数据
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 102 EricHu Developer 1455 2011-5-26 1 5500.00 14.00 10 104 huyong PM 1455 2011-5-26 1 5500.00 14.00 10 105 WANGJING Developer 1455 2011-5-26 1 5500.00 14.00 10 17 rows selected
- Scott.dept表的现有数据
SQL> select * from dept; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 50abc 50def 60 Developer HaiKou 6 rows selected
- 作业列表1
1.列出至少有一个员工的所有部门。 2.列出薪金比“SMITH”多的所有员工。 3.列出所有员工的姓名及其直接上级的姓名。 4.列出受雇日期早于其直接上级的所有员工。 5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 6.列出所有“CLERK”(办事员)的姓名及其部门名称。 7.列出最低薪金大于1500的各种工作。 8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。 9.列出薪金高于公司平均薪金的所有员工。 10.列出与“SCOTT”从事相同工作的所有员工。 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 13.列出在每个部门工作的员工数量、平均工资和平均服务期限。 14.列出所有员工的姓名、部门名称和工资。 15.列出所有部门的详细信息和部门人数。 16.列出各种工作的最低工资。 17.列出各个部门的MANAGER(经理)的最低薪金。 18.列出所有员工的年工资,按年薪从低到高排序。
- 参考答案
--------1.列出至少有一个员工的所有部门。--------- SQL> select dname from dept where deptno in(select deptno from emp); DNAME -------------- RESEARCH SALES ACCOUNTING --------或-------- SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1); DNAME -------------- ACCOUNTING RESEARCH SALES --------2.列出薪金比“SMITH”多的所有员工。---------- SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 102 EricHu Developer 1455 2011-5-26 1 5500.00 14.00 10 104 huyong PM 1455 2011-5-26 1 5500.00 14.00 10 105 WANGJING Developer 1455 2011-5-26 1 5500.00 14.00 10 16 rows selected --------3.列出所有员工的姓名及其直接上级的姓名。---------- SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a; ENAME BOSS_NAME ---------- ---------- SMITH FORD ALLEN BLAKE WARD BLAKE JONES KING MARTIN BLAKE BLAKE KING CLARK KING SCOTT JONES KING TURNER BLAKE ADAMS SCOTT JAMES BLAKE FORD JONES MILLER CLARK EricHu huyong WANGJING 17 rows selected --------4.列出受雇日期早于其直接上级的所有员工。---------- SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr); ENAME ---------- SMITH ALLEN WARD JONES BLAKE CLARK 6 rows selected --------5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门---------- SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno 2 from dept a left join emp b on a.deptno=b.deptno; DNAME EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO -------------- ----- ---------- --------- ----- ----------- --------- ------ RESEARCH 7369 SMITH CLERK 7902 1980-12-17 800.00 20 SALES 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 30 SALES 7521 WARD SALESMAN 7698 1981-2-22 1250.00 30 RESEARCH 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 SALES 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 30 SALES 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 ACCOUNTING 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 RESEARCH 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20 ACCOUNTING 7839 KING PRESIDENT 1981-11-17 5000.00 10 SALES 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 30 RESEARCH 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 SALES 7900 JAMES CLERK 7698 1981-12-3 950.00 30 RESEARCH 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 ACCOUNTING 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 ACCOUNTING 102 EricHu Developer 1455 2011-5-26 1 5500.00 10 ACCOUNTING 104 huyong PM 1455 2011-5-26 1 5500.00 10 ACCOUNTING 105 WANGJING Developer 1455 2011-5-26 1 5500.00 10 50abc OPERATIONS Developer 20 rows selected --------6.列出所有“CLERK”(办事员)的姓名及其部门名称。---------- SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK'; ENAME DNAME ---------- -------------- SMITH RESEARCH ADAMS RESEARCH JAMES SALES MILLER ACCOUNTING --------7.列出最低薪金大于1500的各种工作。---------- SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500; HIGHSALJOB ---------- ANALYST Developer MANAGER PM PRESIDENT --------8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。---------- SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES'); ENAME ---------- ALLEN WARD MARTIN BLAKE TURNER JAMES 6 rows selected --------9.列出薪金高于公司平均薪金的所有员工。---------- SQL> select ename from emp where sal>(select avg(sal) from emp); ENAME ---------- JONES BLAKE SCOTT KING FORD EricHu huyong WANGJING 8 rows selected --------10.列出与“SCOTT”从事相同工作的所有员工。-------- SQL> select ename from emp where job=(select job from emp where ename='SCOTT'); ENAME ---------- SCOTT FORD --------11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。--------- SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal 2 from emp b where b.deptno=30) and a.deptno<>30; ENAME SAL ---------- --------- --------12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。--------- SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30); ENAME SAL ---------- --------- JONES 2975.00 SCOTT 4000.00 KING 5000.00 FORD 3000.00 EricHu 5500.00 huyong 5500.00 WANGJING 5500.00 7 rows selected --------13.列出在每个部门工作的员工数量、平均工资和平均服务期限。--------- SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal 2 from emp a group by deptno; DEPTNAME DEPTCOUNT DEPTAVGSAL -------------- ---------- ---------- ACCOUNTING 6 4208.33333 RESEARCH 5 2375 SALES 6 1566.66666 --------14.列出所有员工的姓名、部门名称和工资。--------- SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a; ENAME DEPTNAME SAL ---------- -------------- --------- SMITH RESEARCH 800.00 ALLEN SALES 1600.00 WARD SALES 1250.00 JONES RESEARCH 2975.00 MARTIN SALES 1250.00 BLAKE SALES 2850.00 CLARK ACCOUNTING 2450.00 SCOTT RESEARCH 4000.00 KING ACCOUNTING 5000.00 TURNER SALES 1500.00 ADAMS RESEARCH 1100.00 JAMES SALES 950.00 FORD RESEARCH 3000.00 MILLER ACCOUNTING 1300.00 EricHu ACCOUNTING 5500.00 huyong ACCOUNTING 5500.00 WANGJING ACCOUNTING 5500.00 17 rows selected --------15.列出所有部门的详细信息和部门人数。--------- SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a; DEPTNO DNAME LOC DEPTCOUNT ------ -------------- ------------- ---------- 10 ACCOUNTING NEW YORK 6 20 RESEARCH DALLAS 5 30 SALES CHICAGO 6 40 OPERATIONS BOSTON 50 50abc 50def 60 Developer HaiKou 6 rows selected --------16.列出各种工作的最低工资。--------- SQL> select job,avg(sal) from emp group by job; JOB AVG(SAL) --------- ---------- ANALYST 3500 CLERK 1037.5 Developer 5500 MANAGER 2758.33333 PM 5500 PRESIDENT 5000 SALESMAN 1400 7 rows selected --------17.列出各个部门的MANAGER(经理)的最低薪金。-------- SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno; DEPTNO MIN(SAL) ------ ---------- 10 2450 20 2975 30 2850 --------18.列出所有员工的年工资,按年薪从低到高排序。--------- SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal; ENAME SALPERSAL ---------- ---------- SMITH 9600 JAMES 11400 ADAMS 13200 MILLER 15600 TURNER 18000 WARD 21000 ALLEN 22800 CLARK 29400 MARTIN 31800 BLAKE 34200 JONES 35700 FORD 36000 SCOTT 48000 KING 60000 EricHu 66168 huyong 66168 WANGJING 66168 17 rows selected
- 作业列表2
/* 作业: 1.在EMP表中,增加一名员工,员工信息参照现有员工构造. 2.员工SMITH部门调动到SALES部门,请编写SQL语句更新员工信息. 3.员工JAMES已经离职,请编写SQL语句更新数据库. 4.按照职位分组,求出每个职位的最大薪水 5.求出每个部门中的每个职位的最大薪水 6.列出SMITH的薪水和职位 7.查询EMP表,输出每个部门的各个职位的平均工资,并按部门编号升序、平均工资降序排序。 8.创建一张新表empbak,表数据和表结构均来自emp表 9.给有奖金的涨薪15%,没有的奖金的薪资不变 10.给MANAGER和SALESMAN职位的员工涨薪20% */ -- 1.在EMP表中,增加一名员工,员工信息参照现有员工构造. select * from emp; insert into emp values('8002','张四','MANAGER',7902,to_date('2023/2/21','yyyy-mm-dd'),900.00,'',20); -- 2.员工SMITH部门调动到SALES部门,请编写SQL语句更新员工信息. update emp set deptno=30 where empno=7369; -- 3.员工JAMES已经离职,请编写SQL语句更新数据库. delete from emp where empno=7900; -- 4.按照职位分组,求出每个职位的最大薪水 select job,max(sal) from emp group by job order by max(sal) asc; -- 5.求出每个部门中的每个职位的最大薪水 select deptno,job,max(sal) from emp group by deptno,job; -- 6.列出SMITH的薪水和职位 select ename,sal,job from emp where ename='SMITH'; -- 7.查询EMP表,输出每个部门的各个职位的平均工资,并按部门编号升序、平均工资降序排序。 select deptno,avg(sal) from emp group by deptno,job order by deptno asc,avg(sal) desc; -- 8.创建一张新表empbak,表数据和表结构均来自emp表 create table empbak as select * from emp; -- 9.给有奖金的涨薪15%,没有的奖金的薪资不变 update emp set sal=sal*(1+0.15) where comm is not null; select * from emp; -- 10.给MANAGER和SALESMAN职位的员工涨薪20% update emp set sal=sal*(1+0.2) where job='MANAGER' or job='SALESMAN'; commit; select * from emp; update emp set sal=sal*(1+0.2) where job in ('MANAGER','SALESMAN'); commit; select * from emp;
- 作业列表3
/* 1、给emp表添加身份证号码card属性,类型为int型。 2、修改card字段类型为字符型,长度为50 3、删除emp表奖金字段 4、查看学生表表 结构 5、查询员工SMITH详细信息 6、修改SMITH的奖金为500,升职为经理且部门改成30部门。 7、删除ALLEN员工信息 8、新增一个新员工信息至员工表,数据自己构造 9、给没有奖金的员工涨薪20% 10、给有奖金的员工降薪15% create table txj.emp as select * from scott.emp; select * from txj.emp; drop table emp; */ -- 1 alter table emp add card int; select * from txj.emp; -- 2 alter table emp drop column card; alter table emp add card int; -- 3 alter table emp drop column comm; alter table emp add comm int; -- 4 alter table emp drop column comm; select * from emp where ename='SMITH'; -- 5 update emp set comm=500,job='MANAGER',deptno=30 where ename='SMITH'; commit; -- 6 select * from emp where ename='SMITH'; -- 7 delete from emp where ename='ALLEN'; commit; -- 8 insert into emp(ename) values('txj'); commit; -- 9 update emp set sal=sal*(1+0.2) where comm is null; commit; -- 10 update emp set sal=sal*(1-0.15) where comm is not null; commit; select * from emp;
- 20230316 , primary key , foreign key
-
-- 建表Person,字段IDCard,姓名pname,性别sex,年龄age,日期 drop table person; create table person( IDCard number primary key, pname varchar2(20) not null, sex varchar2(2), age int, birthday date ) -- 主键写法二 create table person(IDCard number pname varchar2(20) not null, sex varchar2(2), age int, birthday date, primary key(IDCard) ) alter table person modify IDCard varchar2(20); insert into person values('123456789012345678','张三','男',20,''); insert into person values('223456789012345678','张三','男',20,''); commit; select * from person; delete from person where pname='张三'; commit; alter table person drop constraint SYS_C005491; --删除系统自动主键名称约束 alter table person add constraint primary_key_name primary key(IDCard); --手工添加主键并指定主键名称为primary_key_name alter table person add mobile varchar2(11); --添加手机号字段 alter table person add constraint unique_mobile unique(mobile); --添加唯一性约束 create table person( IDCard number primary key, pname varchar2(20) not null, sex varchar2(2), age int, birthday date, mobile varchar2(11) unique ) create table person( IDCard number pname varchar2(20) not null, sex varchar2(2), age int, birthday date, primary key(IDCard), constraint person_ps_uq unique(pname) --也可以这样给名字增加唯一性约束 ) -- check 检查约束 alter table person add constraint check_sex check(sex in('男','女')); select * from person; insert into person values('123456789012345677','李四','男',30,'','13851897759'); insert into person values('123456789012345676','李四','女',30,'','13851897758'); -- 外键(FOREIGN KEY)约束:用来维护从表(Child Table)和主表(Parent Table)之间的引用完整性。 select * from orders; drop table orders; create table orders( order_id varchar2(20) primary key, price number(10,2), pname varchar2(2) -- 一个汉字2个字节,不够插 ); alter table orders modify pname varchar2(20); select * from order_detail; drop table order_detail; create table order_detail( order_detail_id varchar2(20) primary key, order_id varchar2(20), item_name varchar2(20), constraint foreign_key_order_id foreign key(order_id) references orders(order_id) ); alter table order_detail drop constraint foreign_key_order_id; --删除子表外键约束 -- 删除主表数据时,相关子表数据一并删除 alter table order_detail add constraint fk_order_id foreign key(order_id) references orders(order_id)on delete cascade; -- 子表order_detail有了on delete cascade,主表删除order_id=100则自动删除子表order_id=100的行数据 delete from orders where order_id=(100); insert into orders values('100',20.05,'苹果'); insert into orders values('101',20,'香蕉'); insert into orders values('102',99.99,'榴莲'); select * from orders; insert into order_detail values('10001','100','通信类'); commit; select * from order_detail; drop table master_bank; create table master_bank( bankid number primary key, bankname varchar2(20) ) drop table sub_bank; create table sub_bank( fbankid number primary key, bankid number, fbank_name varchar2(20), address varchar2(20), manager varchar2(20), tel number, constraint fk_bankid foreign key(bankid) references master_bank(bankid) ) select * from sub_bank; alter table sub_bank modify fbankid varchar2(10); alter table sub_bank drop constraint fk_bankid; --删除外键约束 -- 删除主表数据时,相关子表数据一并删除 alter table sub_bank add constraint fk_bankid foreign key(bankid) references master_bank(bankid) on delete cascade; insert into master_bank values (1001,'中国银行'); insert into master_bank values (1002,'建设银行'); insert into master_bank values (1003,'交通银行'); insert into master_bank values (1004,'工商银行'); insert into master_bank values (1005,'浦发银行'); insert into master_bank values (1006,'南京银行'); insert into master_bank values (1007,'农业银行'); insert into master_bank values (1008,'邮政银行'); insert into master_bank values (1009,'光大银行'); insert into sub_bank values ('f_001',1001,'中国银行南京分行','中南京路1号','黄经理',833123333); insert into sub_bank values ('f_002',1001,'中国银行合肥分行','中合肥路1号','李经理',833123334); insert into sub_bank values ('f_003',1001,'中国银行苏州分行','中苏州路1号','李经理',833123335); insert into sub_bank values ('f_004',1002,'建设银行南京分行','建南京路1号','黄经理',833123336); insert into sub_bank values ('f_005',1002,'建设银行合肥分行','建合肥路1号','黄经理',833123337); insert into sub_bank values ('f_006',1002,'建设银行苏州分行','建南京路1号','黄经理',833123338); insert into sub_bank values ('f_007',1002,'建设银行镇江分行','建南京路1号','黄经理',833123339); insert into sub_bank values ('f_008',1003,'工商银行南京分行','工南京路1号','黄经理',833123340); insert into sub_bank values ('f_009',1003,'工商银行北京分行','工北京路1号','黄经理',833123341); insert into sub_bank values ('f_010',1003,'工商银行云南分行','工云南路1号','黄经理',833123341); -- insert into sub_bank values ('f_011',8888,'工商银行云南分行','工云南路1号','黄经理',833123341); -- 插入异常,data异常 select * from master_bank; select * from sub_bank; delete from master_bank; delete from sub_bank; /* 1、给学生表的学号添加主键约束; 2、给学生表的年龄添加默认值20; 3、给学生表的出生日期添加不为空约束 4、给学生表的姓名添加唯一约束 5、给学生表的性别添加check约束 */ -- 方式一,建表时就添加约束 drop table student3; create table student4( sno number primary key, sname varchar2(20) unique, sex varchar2(2) check (sex in ('男','女')), age int default 20, birth date not null ) -- 方式二,建表后,另外加约束 create table student5( sno number, sname varchar2(20), sage number default 20, ssex varchar2(2), birth date ) alter table student5 add constraint pk_sno primary key(sno); alter table student5 add constraint unique_sname unique(sname); alter table student5 add constraint check_ssex check(ssex in('男','女')); -- 删除外键等 alter table student5 drop constraint pk_sno; alter table student5 drop constraint unique_sname; alter table student5 drop constraint check_ssex; alter table student5 modify birth not null; alter table student5 modify sage default 30; -- 连接查询,内连接(等值连接),左外连接,右外连接,全外连接, 自连接 -- 注意:测试角度,三表以上联查是为bug,撑爆了。 drop table emp; drop table dept; create table emp as select * from scott.emp; create table dept as select * from scott.dept; create table salgrade as select * from scott.salgrade; select * from emp; select * from dept; select * from salgrade; -- 内连接,写法一 select * from emp e,dept d where e.deptno = d.deptno order by empno; select e.*,d.* from emp e,dept d where e.deptno = d.deptno order by empno; -- 内连接,写法二,表1 inner join 表2 on 表1.字段 = 表2.字段 select * from emp e inner join dept d on e.deptno = d.deptno;
- 作业列表
/* 1、查询员工编号,员工名称,薪水和员工所在的部门名称 2、查询员工编号,员工名称,薪水,和员工所在部门名称,以及每个员工的薪资等级 3、查询员工薪资等级 4、查询有员工的部门(不是空部门)名称 5、请查出SMITH的薪水等级和他所在部门所在地 */ -- 查询员工编号,员工名称,薪水和员工所在的部门名称 select e.deptno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno order by sal; -- 查询员工编号,员工名称,薪水,和员工所在部门名称,以及每个员工的薪资等级 select e.empno,e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal; --薪资比较等级 -- 查询员工薪资等级 select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal; -- 查询有员工的部门(不是空部门)名称 select * from emp,dept; select * from dept; -- 肉眼能看出40空部门编号不在emp中,下一步剔除 select distinct(e.deptno) from emp e; select distinct(e.deptno),d.dname from emp e,dept d where e.deptno = d.deptno; --剔除不相等的条件,即剔除空部门的 -- 请查出SMITH的薪水等级和他所在部门所在地 select * from emp e where e.ename='SMITH'; select * from emp e,dept d where e.ename='SMITH'; select e.ename,s.grade,d.loc from emp e,salgrade s,dept d where e.sal between s.losal and s.hisal and d.deptno = e.deptno and e.ename = 'SMITH'; --不带where条件容易产生笛卡尔积,无意义 select count(1) from emp e,dept d;
- 作业列表
/* 1、对emp表中sal、comm进行加计算,并使用别名命令为员工的月总收入,同时展示出员工部门编号、员工姓名信息。 2、使用连接符查询emp表中员工的姓名和工资,并以如下格式列出且字段名展示为 TOTAL INCOME: SMITH total income is XXXXX 3、使用distinct排重查询emp中的job类型 4、从emp表中找出奖金高于 薪水60%的员工 5、找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料。 6、从emp和dept中联合查询,并将员工编号、姓名、职位、地址信息列出。 7、统计各部门的薪水总和。 8、找出部门10中所有理(MANAGER),部门20中所有办事员(CLERK)以及既不是经理又不是办事员但其薪水大于或等2000的所有员工的详细资料。 9、列出各种工作的最低工资。 10、列出各个部门的MANAGER(经理)的最低薪水。 11、列出有奖金的员工的不同工作。 12、找出无奖金或奖金低于300的员工。 13、显示所有员工的姓名,并使姓名首字母大写。 14、显示正好为5个字符的员工的姓名。 15、显示不带有“R”的员工姓名。 16、列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水。 17、列出在每个部门工作的员工数量、平均工资和平均服务期限。 18、列出从事同一种工作但属于不同部门的员工的一种组合。 19、列出薪水比“SMITH”多的所有员工。 20、列出至少有一个员工的所有部门。 21、对于每个员工,显示其加入公司的天数、月数、年数。 22、对21中的天数、月数、年数取整显示。 23、找出在每年5月受聘的所有员工。 24、显示在一个月为30天的情况下所有员工的日薪水,取整。 25、显示所有员工的姓名和加入公司的年份和月份,并将员工入职年月从低到高排序。 */ -- 1 select sal+nvl(comm,0) as 员工的月总收入,e.deptno 员工部门编号,e.ename 员工姓名 from emp e; -- 2 select 'TOTAL INCOME:' || ename || ' total income is ' || sal from emp; -- 3 select distinct(e.job) from emp e; -- 4 select e.ename from emp e where comm > sal*0.6; -- 5 select e.* from emp e where (e.deptno = 10 and job='MANAGER') or (e.deptno = 20 and job='CLERK'); -- 6 select e.empno,e.ename,e.job,d.loc from emp e,dept d where e.deptno = d.deptno; -- 7 select e.* from emp e; select e.deptno,sum(e.sal) from emp e group by e.deptno; -- 8 select * from emp e where (e.deptno = 10 and e.job = 'MANAGER') or (e.deptno = 20 and e.job = 'CLERK') or (job not in('MANAGER','CLERK') and sal >= 2000); -- 9 select job,min(sal) from emp group by job; -- 10 select e.deptno,min(e.sal) from emp e where e.job = 'MANAGER' group by e.deptno; -- 11 select e.job from emp e where comm is not null group by job; select distinct(e.job) from emp e where comm is not null; -- 12 select e.ename,e.comm from emp e where comm is null or comm < 300; -- 13 select ename,initcap(e.ename) from emp e; -- 14 select ename from emp e where length(e.ename)=5; -- 15 select e.ename from emp e where e.ename not like '%R%'; -- 16 select ename,sal from where sal > (select max(sal) from emp where deptno=30); -- 查无数据,造数据核实下 -- select * from emp for update; -- 17 select deptno,count(1) 员工数量,round(avg(sal))平均工资 ,round(avg(round((sysdate - hiredate)/365))) 入职年限 from emp e group by deptno; -- 18 自连接 -- 19 select * from emp where sal > (select sal from emp where ename = 'SMITH'); -- 20 select distinct(d.dname) from emp e, dept d where e.deptno = d.deptno -- 21 select (sysdate-hiredate) as 天数, (sysdate-hiredate)/365 年数,(sysdate-hiredate)/365*12 月数 from emp e; -- 22 select round((sysdate-hiredate)) as 天数, round((sysdate-hiredate)/365) 年数,round((sysdate-hiredate)/365*12) 月数 from emp e; -- 23 select * from emp where to_char(hiredate,'mm') = '05'; -- 24 select round((sal+nvl(comm,0))/30) from emp; -- 25 select ename,to_char(hiredate,'yyyy') 年份, to_char(hiredate,'mm')月份 from emp order by 年份,月份;
-
oracle 练习题
https://www.cnblogs.com/liyuelian/p/16782510.html
-
1.查询emp表,显示薪水大于2000,且工作类别是MANAGER的雇员信息 2.查询emp表,显示年薪大于30000,工作类别不是MANAGER的雇员信息 3.查询emp表, 显示薪水在1500到3000之间,工作类别以“M”开头的雇员信息 4.查询emp表,显示佣金为空并且部门号为20或30的雇员信息 5.查询emp表,显示佣金不为空或者部门号为20的雇员信息,要求按照薪水降序排列 6.查询emp表,显示年薪大于30000工作类别不是MANAGER,且部门号不是10或40的雇员信息,要求按照雇员姓名进行排列 7.查询EMP表,输出每个部门的平均工资,并按部门编号降序排列. 8.查询EMP表,输出每个职位的平均工资,按平均工资升序排列. 9.查询EMP表,输出每个部门的各个职位的平均工资,并按部门编号升序、平均工资降序排序。 10.使用子查询,找出哪个部门下没有员工 11.使用子查询,找出那些工资低于所有部门的平均工资的员工 12.使用子查询,找出那些工资低于任意部门的平均工资的员工,比较一下与第2题输出的结果是否相同? 13.查询姓“刘”的老师的个数 14.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分 15.查询每门课程被选修的学生数; 16.查询同名同姓学生名单,并统计同名人数 17.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 18.查询课程名称为“SSH”,且分数低于60 的学生姓名和分数 19.查询所有学生的选课情况 20.查询任何一门课程成绩在70 分以上的姓名、课程名称和分数; 21.查询不及格的课程,并按课程号从大到小排列 22.查询课程编号为c001 ,且课程成绩在80 分以上,学生的学号和姓名 23.查询不同课程,成绩相同的学生的学号、课程号、学生成绩 24.统计每门课程的学生选修人数(超过10 人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列; 25.检索至少选修两门课程的学生学号 26.查询全部学生都选修的课程的课程号和课程名 27.检索“c003”课程分数小于60,按分数降序排列的同学学号 28.删除“s002”同学的“c001”课程的成绩 29.查询“c001”课程比“c002”课程成绩高的所有学生的学号 30.查询所有同学的学号、姓名、选课数、总成绩 31.查询没学过“谌燕”老师课的同学的学号、姓名 32.查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名 33.查询学过“谌燕”老师所教的所有课的同学的学号、姓名 34.查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名 35.查询没有学全所有课的同学的学号、姓名; 36.查询平均成绩大于85 的所有学生的学号、姓名和平均成绩; 37.查询两门以上不及格课程的同学的学号及其平均成绩。
-
-- 1 select e.ename,e.sal from emp e where sal+nvl(comm,0) > 2000 and job='MANAGER'
-
使用课程中初始化的表,完成练习: 1、查询平均成绩大于60 分的同学的学号和平均成绩;group-by-having-avg 2、查询姓“刘”的老师的个数;count-like 3、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;distinct-max-mingroup 4、查询每门课程被选修的学生数; 5、查询男生、女生人数; 6、查询姓“张”的学生名单;like select sno, avg(score) from sc group by sno HAVING avg(score) > 60; select count(*) from teacher where tname LIKE '刘%'; select distinct(cno) as 课程ID,max(score) as 最高分,min(score) as 最低分 from sc GROUP BY cno; select distinct(dname) as 课程ID,count(*) as 学生人数 from student GROUP BY dname; select count(*) as 男生人数 from student where ssex='男'; select count(*) as 女生人数 from student where ssex='女'; # 分组 select ssex,count(*)from student group by ssex; 7、查询同名同姓学生名单,并统计同名人数;count,group-by,having 8、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列; avg,order-by(asc,desc),多排序条件 9、查询课程名称为“SSH”,且分数低于60 的学生姓名和分数;三张表的连表查询 10、查询所有学生的选课情况; 11、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数; select 姓名,课程名称,分数 from 学生表 join 学生成绩表 on 学生编号 大于70分; select * from student where sname like '张%'; select sname,count(*) as snum from student group by sname having count(*) > 1; select cno,avg(score) as score from sc group by cno order by score asc,cno desc; select st.sname,sc.score from student AS st join sc on st.sno = sc.sno join course on sc.cno = course.cno where sc.score < 60 and course.cname='SSH'; select st.sname,st.dname,sc.cno from student as st join sc on st.sno = sc.sno; 12、查询不及格的课程,并按课程号从大到小排列;order-by desc,where 小于分数 13、查询课程编号为c001 ,且课程成绩在80 分以上,学生的学号和姓名;多表连接查询 14、求选了课程的学生人数;基于第10题,count 15、查询不同课程,成绩相同的学生的学号、课程号、学生成绩;where 课程不同 和 成绩相同 16、统计每门课程的学生选修人数(超过10 人的课程才统计)。要求输出课程号和选修人数,查询结果 按人数降序排列,若人数相同,按课程号升序排列; select st.sname,course.cname,sc.score from student as st join sc on sc.sno = st.sno join course on sc.cno = course.cno where sc.score > 70; select cno,score from sc where score < 60 ORDER BY cno desc; select st.sno,st.sname from student st join sc on st.sno=sc.sno where sc.cno='c001' and sc.score > 80; select cno,count(sno) from sc group by cno; select s1.sno,s1.cno,s1.score from sc as s1 join sc as s2 on s1.sno=s2.sno where s1.cno != s2.cno and s1.score=s2.score; select cno,count(*) from sc group by cno order by count(*) desc,cno asc; 17、检索至少选修两门课程的学生学号;count(*) > 1 18、查询全部学生都选修的课程的课程号和课程名;多表连接查询 19、检索“c003”课程分数小于60,按分数降序排列的同学学号;where 小于60,order-by-desc; 20、删除“s002”同学的“c001”课程的成绩; 21、查询“c001”课程比“c002”课程成绩高的所有学生的学号;group-by,where 比较 22、查询所有同学的学号、姓名、选课数、总成绩;join,count,sum(score) select sno,count(*) from sc group by sno having count(*) > 1; # 查询课程表的编号--不存在--查询所有学生--不存在--查询学生成绩(成绩表学生编号=学生表编号,并 且,成绩课程编号=课程表编号) select cno from course where not exists(select * from student where not exists(select * from sc where sc.sno=student.sno and course.cno=sc.cno)); select st.sno from student as st join sc on sc.sno=st.sno where sc.cno='c003' and sc.score < 60 order by sc.score desc; delete from sc where sno='s002' and cno='c001'; select st.sno from student st join sc a on st.sno=a.sno join sc b on st.sno=b.sno where a.cno='c002' and b.cno='c001' and a.score > b.score 23、查询没学过“谌燕”老师课的同学的学号、姓名;多表连接查询,not 24、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名; 学生表和学生成绩表连表, where 多条件,and 25、查询学过“谌燕”老师所教的所有课的同学的学号、姓名; 同23,不用取反 26、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名; 连表 min(c002) where cno=c001 and score < min(成绩) select a.*,s.sname from ( select sno,sum(score) as 总成绩,count(cno) as 选课数 from sc group by sno ) as a ,student as s where a.sno=s.sno; select st.sno,st.sname from student st where st.sno not in ( select distinct sno from sc s join course c on s.cno=c.cno join teacher t on c.tno=t.tno where tname='谌燕' ) select st.sno,st.sname from sc as a join sc as b on a.sno=b.sno join student as st on st.sno=a.sno where a.cno='c001' and b.cno='c002' and st.sno=a.sno; select distinct st.sno,st.sname from student as st join sc as s on st.sno=s.sno join course as c on s.cno=c.cno join teacher as t on c.tno=t.tno where t.tname='谌燕'; 27、查询所有课程成绩小于60 分的同学的学号、姓名;连表,where 28、查询没有学全所有课的同学的学号、姓名; 29、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名; 30、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名; select st.sno,st.sname from student as st join sc as a on st.sno=a.sno join sc as b on st.sno=b.sno where a.cno='c002' and b.cno='c001' and a.score < b.score; select st.sno,st.sname,s.score from student as st join sc as s on st.sno=s.sno join course as c on s.cno=c.cno where s.score < 60; select st.sno,st.sname,count(sc.cno) from student as st left join sc on st.sno=sc.sno group by st.sno,st.sname having count(sc.cno)<(select count(distinct cno)from course); select st.sno,st.sname from student as st, (select distinct a.sno from (select * from sc) as a, (select * from sc where sc.sno='s001') as b where a.cno=b.cno ) as c where st.sno=c.sno and st.sno<>'s001' select st.sno,st.sname from sc left join student as st on st.sno=sc.sno where sc.sno<>'s001' and sc.cno in (select cno from sc where sno='s001') 31、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、 “c002”号课的平均成绩; insert into 32、按各科平均成绩从低到高和及格率的百分数从高到低顺序; 33、查询不同老师所教不同课程平均分从高到低显示; 34、统计列出各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]; 比较运算符,分组 35、查询出只选修了一门课程的全部学生的学号和姓名; select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002') from student as st,sc where not exists (select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002'; select cno,avg(score),sum(score>=60)/count(*)*100 as 及格率 from sc group by cno order by avg(score) , 及格率 desc; select max(t.tno),max(t.tname),max(c.cno),max(c.cname),c.cno,avg(score) from sc , course as c,teacher as t where sc.cno=c.cno and c.tno=t.tno group by c.cno order by avg(score) desc; select sc.cno,c.cname, sum(score between 85 and 100) AS "[100-85]", sum(score between 70 and 85) AS "[85-70]", sum(score between 60 and 70) AS "[70-60]", sum(score <60) AS "[<60]" from sc, course as c where sc.cno=c.cno group by sc.cno ,c.cname; 36、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩; 学生表和成绩表连接查询 avg(成绩) 37、查询选修“谌燕”老师所授课程的学生中,成绩最高的学生姓名及其成绩; 38、查询两门以上不及格课程的同学的学号及其平均成绩。 select sc.sno,st.sname,count(cno) from student as st left join sc on sc.sno=st.sno group by st.sname,sc.sno having count(cno)=1; select st.sno,st.sname,avg(score) from student as st left join sc on sc.sno=st.sno group by st.sno,st.sname having avg(score)>85; select st.sname,score from student as st,sc,course as c,teacher as t where st.sno=sc.sno and sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕' and sc.score=(select max(score)from sc where sc.cno=c.cno); select sno,avg(score)from sc where sno in ( select sno from sc where sc.score<60 group by sno having count(sno)>1 ) group by sno;
-
oracle sql,查询员工表emp月工资是2000或3000的员工 SELECT * FROM emp WHERE sal = 2000 OR sal = 3000; oracle sql,查询员工表emp每个员工的领导姓名 你可以使用自连接查询来实现。 SELECT e.ename, m.ename AS leader_name FROM emp e LEFT JOIN emp m ON e.mgr = m.empno ORDER BY e.ename; 上面的查询语句将获取emp表中每个员工的名字和他们的领导名字。e.mgr列与m.empno列进行连接,并将结果按照员工姓名进行排序。 oracle sql,查询员工每个员工的薪资等级 你可以使用Oracle提供的带有Analytic Function的查询语句,例如: SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS salary_grade FROM emp; 上面的查询语句将获取emp表中每个员工的名字和薪资,并使用DENSE_RANK() Over (order by sal desc)当做分析函数,将结果按照薪资排序并分为不同的薪资等级。 注意,这里使用了DENSE_RANK()函数,因为薪资可能存在相同的情况,所以要确保相同的薪资得到相同的薪资等级。 oracle sql,查询员工表emp cleark 职位的每个部门中的人数 你可以使用GROUP BY子句和聚合函数COUNT()来获取每个部门中Clerk职位的人数,例如: SELECT deptno, COUNT() AS clerk_num FROM emp WHERE job = 'CLERK' GROUP BY deptno; 上面的查询语句将获取emp表中职位为Clerk的员工所在的每个部门的编号以及Clerk人数,使用WHERE子句过滤职位为Clerk的员工,然后使用GROUP BY子句按照部门编号分组,最后使用COUNT()函数统计每个组内的行数(即Clerk人数)。
end