–创建表空间–create tablespace LOUISdatafile’f:\Louis.dbf’size 80Mautoextend on next 20M maxsize 200M;
–创建用户–create user louisidentified by tigerdefault tablespace LOUIS;
–授权–grant dba to louis;————————————
create table emp(
eid number(20)primary key,–员工主键id
ename varchar(20),–姓名
esal number(20),–工资
esex varchar(20),–性别
did number(20),–部门编号
eage number(20)–年龄
)
create table dept(
did number(20)primary key,–部门主键id
dname varchar2(20)–部门名
)
alter table emp add constraint a foreign key (did) references dept(did);
–添加dept元素–
insert into dept values(1,’deparment’);
insert into dept values(2,’administra’);
–添加EMP元素–
insert into emp values(1,’A’,1818,’man’,1,25);
insert into emp (eid,ename,esal,eage)values(2,’B’,2484,17);
insert into emp (eid,ename,esex,esal,eage)values(3,’C’,’woman’,2236,30);
insert into emp values(4,’D’,2500,’man’,1,14);
insert into emp values(5,’E’,2500,’man’,2,18);
insert into emp values(6,’F’,1622,’man’,1,27);
insert into emp (eid,ename,esal,eage)values(7,’G’,3565,19);
insert into emp (eid,ename,esex,esal,eage)values(8,’H’,’woman’,5501,36);
insert into emp values(9,’I’,3622,’man’,1,17);
insert into emp values(10,’J’,852,’man’,2,20);
—基础SQL语句—
select * from emp e;
–新增
insert into emp values(11,’K’,892,’man’,2,15);
–新增(insert into 表名 （字段1，字段2，字段3，字段4）values(内容……))
insert into emp (eid,ename,esal,esex) values (12,’L’,3213,’man’);
–删除(delete from 表名 where 条件)–
delete from emp where eid = 8;—
修改（update 表名 set 需要修改的字段名 = 新赋值的内容 where 条件）
-update emp set ename = ‘Z’ where eid = 1;
–查看(查看全部和某一个)–
select * from emp e where e.eid=7;
–数据库中常用的函数—-
1聚合函数—-求EMP表中所有员工的工资总和
select sum(e.esal) from emp e;
–求EMP表中具有多少对象（成员）
select count(*) from emp;
–求EMP表中所有员工的工资平均数
select avg(esal) from emp;
–求EMP表中工资的最大值和最小值
select max(esal) from emp;select min(esal) from emp;
–2模糊查询—-查询姓名为A???的所有员工–
select ename from emp where (ename like ‘A%’);
–查询姓名为???A的所有员工–
select ename from emp where (ename like ‘%A’);
–查询姓名为???A???的所有员工–
select ename from emp where (ename like ‘%A%’);
–单行函数–查询O在HELLO中的位置(它将从该单词首字母进行查询，且首字母下标为1)
select instr(‘HELLO’,’E’)from dual;
–截取(截取从第2位到第2+5位。且包含2不包含2+5)
select substr(‘Helloword’,2,5)from dual;
–省略小数点（保留小数点后3位,视第四位情况而四舍五入）
select round(3.14159,3)from dual;–获取当前时间select sysdate from dual;
–3分组查询以及过滤–根据部门来计算（且部门平均工资大于2000）这些几个部门的工资和–
select sum (e.esal)from emp e group by e.did having avg(e.esal)>2000;
–组合查询–
–内连接–
select e.ename, d.dname from emp e ,dept d where e.eid=1 and e.did=d.did;
–左连接–
select e.ename,d.dnamefrom dept dleft join emp e on d.did=e.did where e.eid=1;
–自然连接–
select e.ename,d.dname from emp e natural join dept d where e.eid=1;
—子查询–
select from emp e,dept d where e.esal=(select max(e.esal) from emp) and e.did=d.did;
–分页查询(其中42表示当前页每页记录数)；(4-1)2表示(当前-1)每页记录数）–
select from (select e.,rownum r from emp ewhere rownum<=(42))where r >(4-1)*2;
—PLSqL语句的练习—-1.步骤声明 delclare ;2.执行 begin;3.end 结束;4.Exception 异常处理(可省略)
—-demo1——————–
declare –声明 msg char(15); –定义变量
sal number(7,2);–数字类型
begin –开始
msg:=’HelloWorld’;–赋值
dbms_output.put_line(msg); –输出
end; –结束
—-demo2——————–
declare –声明
emprows emp%Rowtype; –定义变量
begin –开始
select e. into emprows –赋值
from emp e
where e.eid=1;
dbms_output.put_line(emprows.ename);
end;
—-demo3————- ——-
declare – 声明
a number; –定义变量
b number;
begin
a:=30;
b:=30;
if(a>b) then
dbms_output.put_line(‘YES’);
else
dbms_output.put_line(‘NO’);
end if;
end;
—-demo4(综合例题)——————–
declare
ename emp.ename%type;
lname dept.dname%type;
nn number;
begin
select count() into nn
from emp e
where e.eid=1;
if nn>0 then
select e.ename into ename
from emp e
where e.eid=1;
select d.dname into lname
from dept d ,emp e
where d.did=e.did and e.eid=1;
dbms_output.put_line(‘ename:’||ename);
dbms_output.put_line(‘dname:’||lname);
else
dbms_output.put_line(‘no people！’);
end if;
end;
—-demo4(显示游标)————- ——-
declare
cursor YB is select *from emp ;
newemp emp%rowtype;
begin open YB;
loop
fetch
YB into newemp;
dbms_output.put_line(‘ename:’||newemp.ename);
dbms_output.put_line(‘esql’||newemp.esal);
dbms_output.put_line(‘~~~~‘);
exit when YB%notfound;
end loop;
close YB;
end;
–提交
commit;