ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL学习笔记

SQL学习笔记

原创 Linux操作系统 作者:winnylirr 时间:2009-04-01 09:35:43 0 删除 编辑
解锁用户:alert user scott account unlock
用系统管理员账号登陆数据库:conn sys/sys as sysdba
conn scott/tiger 或者connect user/pwd 切换用户
ed或者edit//打开一个缓冲文件
操作回滚:rollback,一旦提交则不可回滚;
数据操纵语言dml,数据定义语句ddl,数据控制语言dcl

显示表结构:desc emp
--单行注释
/*
*/多行注释
SQL>@d:\student.sql //执行sql文件
--------------------------------------------------
--创建雇员表emp
create table emp
(
empno number(4) not null ,--员工号
ename varchar(10),--员工姓名
job varchar(9),--工种
mgr number(4),--所属经理编号
hiredate date,--入职日期
sal number(7,2),--薪水
comm number(7,2),--奖金
deptno number(2),--部门号
primary key(empno)
);
--创建部门表dept
create table dept
(
deptno number(2) not null ,--部门号
dname varchar(14),--部门名称
loc varchar(13),--部门位置
primary key(deptno)
);
--创建工资等级表salgrade
create table salgrade
(
grade number not null ,--等级
losal number,--最低薪水
hisal number,--最高薪水
primary key(grade)
);
---------------------------------------------------------------------
select ename,sal*12 from emp;
select 2*3 from emp;
select 2*3 from dual;--计算表达式
select sysdate from dual;--获取系统当前时间
-----------------------------------------------------------------------
--字段取别名
select ename, sal*12 anuual_sal from emp;--取别名
select ename, sal*12 as anuual_sal from emp;--取别名
select ename, sal*12 "anuual sal" from emp;--取别名,可以显示空格,保持大小写
-------------------------------------------------------------------------------
--连接符
select ename||'aa''aa' from emp;--"||"连接符号,两个单引号表示一个单引号
-----------------------------------------------------------------------------
--distinct,不重复
select distinct deptno,job from emp;--先查询,再排除重复记录
------------------------------------------------------------------------------
--between,范围之内
select * from emp where sal between 800 and 1500;--between相当于>= and <=
--------------------------------------------------------------------------------
--null,空
空值是指不可用来分配的值
空值不等于空或空格
任何类型的数据都有空值
空值相加任何数据还为空
select ename,sal from emp where comm=null; --没有记录,没有实际用处
select ename,sal from emp where comm is null; --为空,有记录
select ename,sal from emp where comm is not null;--不为空
-------------------------------------------------------------------------
--in,在一个枚举的范围内
select ename,sal from emp where sal in(800,1500,1300);
select ename,sal from emp where sal not in(800,1500,1300);
--------------------------------------------------------------------------
--系统默认的日期格式
select ename,hiredate from emp where hiredate>'20-2月 -81';
--------------------------------------------------------------------------
--通配符,"_"匹配一个字符,"%"匹配任意的字符串
select ename,sal from emp where ename like '%a%';
select ename,sal from emp where ename like '_a%';
select ename,sal from emp where ename like '%\%%';--系统默认的转义字符为"\"
select ename,sal from emp where ename like '%$%%' escape '$';--escape,指定转义字符
--------------------------------------------------------------------------------------
--order by,先查询,后排序,desc:降序,asc:升序(系统默认)
select * from dept order by deptno desc;
select * from dept where deptno<>10 order by deptno asc;
select ename,sal,deptno from emp order by sal desc,deptno asc;--先按工资降序排列,工资相同的

列,再按升序排列
---------------------------------------------------------------------------------------
--单行函数
--lower(),将字符串转化为小写
select ename from emp where lower(ename) like '%y%';
select ename from emp where ename like '%y%' or ename like '%Y%';
--upper(),将字符串转化为大写
select ename from emp where upper(ename) like '%Y%';
--substr()字符串截取
select substr(ename,1,3) from emp;--从ename中的第一个字符开始截取三个字符
--chr(),将数字转化其对应的ascii码字符
select chr(65) from dual;
--ascii(),将对应的字符转化为其ascii码对应的数字
select ascii('A') from dual;
--round()四舍五入,默认精确到各位
select round(23.652) from dual;
select round(23.652,1) from dual;
select round(23.652,-1) from dual;
--to_char()将数字或日期转化为字符串
select to_char(sal) from emp;
select to_char(sal,'$999,999,999.99') from emp;--将sal,安装相应的格式转化为字符串
select to_char(sal,'L999,999,999.99') from emp;
select to_char(sal,'L000,000,000.00') from emp;
$:美元,L或者l:本地货币
select to_char(hiredate) from emp;
select to_char(hiredate,'yyyy-mm-dd hh:mi:ss') from emp;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from emp;
--to_date(),将当前固定格式的字符串转化为日期
select ename,hiredate from emp where hiredate>=to_date('2008-09-03 00:00:00','yyyy-mm-dd

hh24:mi:ss');
--to_number(),将当前的固定格式的字符串转化为数字
select sal from emp where sal>to_number('$1,050.00','$9,999.99');
--nvl(),对为空的字段进行处理
select ename,sal*12+nvl(comm,0) from emp;--不会空,则加comm,为空则加0;
-------------------------------------------------------------------------------
--组函数
--max():最大值,min():最小值,avg():平均值,sum():求和,count():统计记录数
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;
select count(*) from emp;
select count(comm) from emp;--不统计字段为空的记录
--group by分组
select deptno,avg(sal) from emp group by deptno;
select deptno,job,avg(sal) from emp group by deptno,job;--deptno,job相同的记录为一组
-------------------------------------------------------------------------------------------

---
--having,对分组进行过滤
select deptno,avg(sal) from emp group by deptno having avg(sal)>1000;
-------------------------------------------------------------------------------------------

------
select deptno,avg(sal) from emp
where sal>1000
group by deptno
having avg(sal)>1200
order by avg(sal) asc;


--case when,分支
统计empno<1003,empno<1005,和其他的记录的个数
select count(*),
(case when empno<1003 then 1
when empno<1005 then 2
else 3
end) no
from emp
group by
(
case when empno<1003 then 1
when empno<1005 then 2
else 3
end
);
----------------------------------------------------------------
--sql,1999新标准
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);
--交叉连接
select ename,dname from emp cross join dept;
--等值连接
select * from emp,salgrade where emp.sal between salgrade.losal and
salgrade.hisal;
select ename,dname from emp join dept on(emp.deptno=dept.deptno);
select ename,dname from emp join dept using(deptno);
--非等值连接
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%';
--自连接
select e1.ename ,e2.ename from emp e1
join emp e2 on (e1.mgr=e2.empno)
--外连接
select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr=e2.empno);
select e1.ename,e2.ename from emp e1 right outer join emp e2 on(e1.mgr=e2.empno);
select e1.ename,e2.ename from emp e1 full outer join emp e2 on(e1.mgr=e2.empno);
---------------------------------------------------------------------------------------
--导出导出数据
drop user liuchao cascade;--删除用户liuchao
exp--导出当前用户的表结构,和数据,在cmd中执行
--创建一个用户liuchao,密码liuchao,数据存在默认的表空间users,在表空间users分配大小为10M的

空间
create user liuchao identified by liuchao default tablespace users quota 10M on users
grant create session,create table,create view to liuchao;--授权liuchao用户可以连接数据库,创

建表,创建视图
imp--导入表结构
create table emp2 as select * from emp;--复制emp表的数据到新创建的表emp2
-------------------------------------------------------------------------------------------

----
--rownum,列号:每一个表都默认有一个显示列号的字段rownum
select rownum,emp.* from emp;
select rownum,ename from emp where rownum<=3;
--运用虚列选出第n-m条的记录(效率较高)
select * from
(
select rownum row_num,emp.* from emp order by empno
)
where row_num between 2 and 3;
--利用分析函数:row_number() over ( partition by col1 order by col2 )选出第n-m条的记录
select * from (
select row_number() over (order by empno) no,emp.* from emp
)
where no between 2 and 3;
-----------------------------------------------------------------------------------------
--列约束条件
create table stu
(
id number(6) primary key,--主键约束
name varchar2(20) constraint stu_name_nn not null,--不为空约束
sex number(1) check(sex in(0,1)),--检查约束
age number(3),
sdate date,
grade njmber(2) default 1,--默认约束
class number(4) references class(id),--外键约束
email varchar2(50) unique--唯一约束
)

create table stu
(
id number(6),
name varchar2(20),
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) ,
email varchar2(50),
constaint stu_id_pk primary key(id),
constaint stu_name_email_uni unique(email,name)
constaint stu_class_fk foreign key(class)
)
--添加,删除约束条件
alert table stu add(addr varchar2(100));
alert table stu add drop addr;
alert table stu modify(addr varchar2(150));
alert table stu drop constraint stu_class_fk;
alert table stu add constraint stu_class_fk foreign key(class) reference class(id) ;
-----------------------------------------------------------------------------------------
数据字典表的存储表(dictionary)
desc dictionary
select tablename from dictionary;
常用数据字典表(user_tables,user_views,user_constraints)
desc user_tables;
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user_constraints;
select constraint_name,table_name from user_constraints;
select index_name from user_indexes;
----------------------------------------------------------------
--索引(提高对经常访问的字段读取速度)
create index idx_stu_email on stu(email);--创建索引
drop index idx_stu_email;--删除索引
--视图(就是一个子查询)
create view v$_stu as select id,name,age from stu;--创建视图
desc v$_dept_avg_sal_info;--显示表结构中的所有视图
-----------------------------------------------------------------------------
--序列(一般用于作为主键)
create table article
(
id number,
title varchar2(1024),
cont long
);
create sequence seq;--创建序列
drop sequence seq;--删除序列
start with 1;--从1开始
select seq.nextval from dual;查询下一个序列值
insert into article values (seq.nextval,'a','b');--将虚列作为主键
---------------------------------------------------------------------
--思考题
部门平均薪水的等级
部门平均的薪水等级
哪些人是经理
不用组函数求最高薪水
平均薪水最高的部门编号与名称
平均薪水的等级最低的部门名称
比普通员工的最高薪水还要高的经理人名称
求部门经理人中平均薪水最低的部门名称
求比普通员工的最高薪水还要高的经理人名称
求薪水最高的前五名雇员
求薪水最高的第六名的到十名雇员
--------------------------------------
--三范式
第一范式:要有主键,列不可分
第二范式:不能存在部分依赖,
第三范式:不能存在传递依赖

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14138553/viewspace-582686/,如需转载,请注明出处,否则将追究法律责任。

上一篇: SQL笔记
请登录后发表评论 登录
全部评论

注册时间:2008-08-21

  • 博文量
    3
  • 访问量
    1620