创建表 stuInfo
create table stuInfo(stuNO char(6) not null,stuName varchar2(20) not null,stuAge number(3,0) not null ,stuID number(18,0), stuSeat number(2,0))
插入数据
INSERT INTO “SCOTT”.”STUINFO” (STUNO, STUNAME, STUAGE, STUSEAT) VALUES (‘1’, ‘张三’, ‘18’, ‘1’)
INSERT INTO “SCOTT”.”STUINFO” (STUNO, STUNAME, STUAGE, STUSEAT) VALUES (‘2’, ‘李四’, ‘20’, ‘2’)
INSERT INTO “SCOTT”.”STUINFO” (STUNO, STUNAME, STUAGE, STUSEAT) VALUES (‘3’, ‘王五’, ‘15’, ‘3’)
INSERT INTO “SCOTT”.”STUINFO” (STUNO, STUNAME, STUAGE, STUSEAT) VALUES (‘4’, ‘张三’, ‘18’, ‘4’)
INSERT INTO “SCOTT”.”STUINFO” (STUNO, STUNAME, STUAGE, STUSEAT) VALUES (‘5’, ‘张三’, ‘20’, ‘5’)
不重复显示所有学员的姓名和年龄
select distinct stuName,stuAge from stuINfo
按照姓名升序排序,如果姓名相同,则按照年龄降序排序
select stuNo,stuName,stuAge from stuinfo where stuage > 17 order by stuname asc, stuage desc
使用别名显示姓名、年龄和身份证号列
select stuname as “姓名” , stuage as “年 龄”,stuid as 身份证号 from stuinfo
复制stuinfo表
create table newStuInfo1 as > select * from stuinfo
复制stuinfo表 (指定列名)
create table newStuInfo2 as > select stuage from stuinfo
复制stuinfo表(只复制表结构)
create table newStuInfo3 as > select * from stuinfo where 1 > 2
查看表中行数
select count(*) from stuinfo ###效率低
select count(1) from stuinfo ###效率高
取出stuName ,stuAge 列不存在重复数据的记录
select stuName,stuAge from stuinfo group by stuName,stuAge having(count(stuName || stuAge) < 2)
删除stuName stuAge 列重复的行(保留一行)
delete from stuinfo
where rowId Not in(
select Max(rowid) from stuinfo GROUP by stuname, stuage having (count(stuage || stuname) > 1)
union
select max(rowid) from stuinfo GROUP by stuname,stuage having (count(stuAge || stuname) = 1)
)
查看当前用户所有数据量>100万的表的信息
select table_name from user_all_tables a where a.num_rows > 1000000
事物控制语句应用举例,创建部门表(dept),插入部门记录
执行步骤一:创建dept表
create table dept(
deptno number(2) primary key, ###部门编号
dname varchar2(14), ###部门名称
loc varchar2(13) ###地址
);
执行步骤二:插入数据
执行步骤三:操作dept表
INSERT into dept values(50,’a’,null);
INSERT into dept values(60,’b’,null);
savepoint a; ###设置保存点
INSERT into dept values(70,’c’,null);
ROLLBACK to SAVEPOINT a; ### 回滚至 a 保存点
执行步骤四:查看dept表,有50,60号部门
select * from dept
执行步骤五:回滚
rollback; ###没有50,60号部门
执行步骤六:查看dept表
select * from dept;
上机1
创建员工信息表
create table employee(
empmp number(4) not null, ###员工编号
ename varchar(10), ###员工姓名
job varchar2(9), ###员工工种
mgr number(4), ###上机经理编号
hredate date, ###受雇日期
sal number(7,2), ###受雇薪水
comm number(7,2), ###福利
depton number(2) ###部门编号
)
插入数据(scott用户下emp表中的数据)
INSERT into employee > select * from scott.emp
添加约束。员工编号作为主键,部门编号作为外键与部门表相关联
alter table employee add constraint FK_depton FOREIGN KEY(DEPTON) REFERENCES dept(DEPTNO)
向employee表添加empTel_no 和 empAddress 两列。创建empTel_no 列以存储员工的电话号码,empAddress 列 以存储地址
alter table employee add(empTel_no varchar2(12) , empAddress varchar2(20));
select * from employee
删除列
alter table employee drop column emptel_no
alter table employee drop column empAddress
按薪水高低查询数据
select * from employee order by sal desc
上机3
分页查询
select * from (> select e.,rownum rn from (> select from employee order by sal) e ) where rn >= 5 and rn < 10
使用union获取所有在公司工作过的员工编号
select empno from employee
UNION
select empno from Emp
示例13
select empno from employee
UNION
select empno from emp
order by empno
查找已经退休了但是被公司返聘仍在继续工作的员工编号
select empno FROM employee
intersect
select empno from emp
查找没有退休的员工编号
select empno from employee
minus
select empno from emp
输出岗位和员工姓名组合在一起的信息
select job||’_’||ename from employee
select job from employee
select ename from employee
示例17 根据格式模型中指定的格式来显示日期
select to_char (sysdate,’YYYY”年”fmMM”月”fmDD”日” HH24:MI:SS’) from dual
示例18 to_char 还可以用来格式化数值
select to_char(1210.7,’$9,999,00’) from dual
示例19 将字符串 转换为日期格式
select to_Date(‘2013-02-13’,’YYYY-MM-DD’) from dual
示例20 to_number() :将数字的字符串转换为number数据类型
select sqrt(to_number(‘100’)) from dual
示例21 查询员工的所有收入和入职月份
select ename,
sal+NVL(comm,0) sall,
nvl2(comm,sal+comm,sal) sal2,
decode(to_char(hiredate,’MM’),’01’,’一月’,’02’,’二月’,’03’,’三月’,’04’,’四月’,’05’,’五月’,’06’,’六月’,’下半年’) mon from employee
示例22 分析函数举例
select ename,deptno,sal,
rank() over(partition by deptno order by sal desc) “rank”,
dense_rank() over(partition by deptno order by sal desc) “dense_rank”,
row_number() over(partition by deptno order by sal desc) “row_number”
from employee