关于sql的一些记录

 create table myemp as select * from emp ; (创建一个表 将emp表中的所有东西复制到 myemp 表)(oracle中起作用)

 create table myemp as select * from emp where 1=2;(只复制表的结构,不复制表的内容,因为后面的条件无法实现)

 

 create table person(
 pid varchar2(18),
 name  varchar2(200),
 age   number(3),
 birthday date,
 sex  varchar2(2) default '男'
 ); 生成一个person表 sex 默认是男

 

 drop table myemp; (删除该表)

 

 delete myemp;(删除该表的所有数据)

 

 delete from myemp where ...

 

 每建一个数据库连接都会产生一个session,你可以通过commit提交事务,将改变持久化到数据库,也可以通过rollback 撤销 先前做的改变. 事务会产生死锁.

 

 

 

select count(empno),job from emp where job in(select job from myemp group by job having min(sal)>1500) group by job;列出薪金大于1500的全部工作和从事此工作的全部雇员人数.

 

 

select e.*,d.dname,d.loc from myemp e,dept d where sal >(select avg(sal) from emp) and e.deptno=d.deptno;列出薪金高于公司平均薪金的所有员工所在部门

其中 进行多表连接查询的时候要注意去除笛卡儿积.

 

 

 

四个国家相互比赛的一个题目:

  create table nation(
  name  varchar2(20)
  );

 

  insert into nation (name) values ('中国');

  insert into nation (name) values (美国');

  insert into nation (name) values ('德国');

  insert into nation (name) values ('巴西');

 

select a.name,b.name from nation a,nation b where a.name <> b.name;

 

结果:

NAME             NAME
-------------------- -------------
美国                 中国
美国                 德国
美国                 巴西
中国                 美国
中国                 德国
中国                 巴西
德国                 美国
德国                 中国
德国                 巴西
巴西                 美国
巴西                 中国

NAME                 NAME
-------------------- -------------
巴西                 德国

 

 

 

 在实际中,约束主要分为以下五种约束:

    1 主键约束:主键表示是一个唯一的标识,本身不能为空 CONSTRAINT person_pid_pk PRIMARY KEY (pid)

    2 唯一约束: 在一个表中只允许建立一个主键约束,而其他如果不希望出现重复值的话,则就可以使用唯一约束.

    3 检查约束: 检查一个列的内容是否合法

              例如 : 年龄,只能在0~150  CHECK(age between 0 and 150)

                        性别,只能是男、女  CHECK (sex IN ('男','女'))

     4 非空约束: 姓名这样的字段里面的内容就不能为空 NOT NULL

     5 外键约束: 在两张表中进行约束操作 CONSTRAINT person_name_uk UNIQUE(name)

 

   例子:

              CREATE TABLE person

    ( 

       pid  varchar2(18),

       name varchar2(200) NOT NULL,

       age  NUMBER(3)   NOT NULL,

       birthday   DATE,

       sex  VARCHAR2(2) DEFAULT '男',

       CONSTRAINT person_pid_pk  PRIMARY KEY (pid),

       CONSTRAINT person_name_uk UNIQUE (name),

       CONSTRAINT person_age_ck  CHECK(age BETWEEN 0 and 150),

       CONSTRAINT person_sex_ck   CHECK (sex  IN ('男','女'))

     );

 

 

   create table book
   (
    bid number primary key not null,
    bname varchar(30),
    bprice number(5,2),
    pid varchar2(10),
    constraint person_book_pid_fk foreign key(pid) references person(pid)
   );

 

 

     create table book
   (
    bid number primary key not null,
    bname varchar(30),
    bprice number(5,2),
    pid varchar2(10),
    constraint person_book_pid_fk foreign key(pid) references person(pid)  ON DELETE  CASCADE
   ); 加上这句话的话 就可以级联删除了 红色字体必须大写

 

  添加约束类型的命名一定要统一:

     PRIMARY KEY: 主键字段_pk

     UNIQUE: 字段_uk

     check:  字段_pk

     FOREIGN KEY : 父字段_子字段_fk

 

修改约束的语法:

alter table 表名称 add constraint 约束名称  约束类型(约束字段);

删除约束的语法:

alter table 表名称 drop constraint 约束名称;

 

 

rownum 使用

查出1-5的数据

select rownum,empno,ename,job,sal from emp where rownum<=5;

要查5-10的数据 得用到子查询

select * from (select rownum rn,empno,ename,job,sal from emp  where rownum<=10) temp where temp.rn>5;

要查最后4条数据 也得用子查询

select * from (select rownum rn,empno,ename,job,sal from emp  where rownum<=14) temp where temp.rn>10;

 

 

 

 

视图的功能: 一个视图实际上就是封装了一条非常复杂的语句

create or replace view emp20 as select  * from emp;

在创建视图的时候是有条件的,你一旦修改之后,则此条件就破坏了,所以在创建视图的时候SQL中提供了两个重要的参数:

   WITH CHECK OPTION : 不能更新视图的创建条件

   WITH READ ONLY : 不能修改视图

 

创建一个序列: create squence 序列名称;

如：create squence sq

      start with 10000

      increment by 1;

 

查询一个序列：select sq.NEXTVAL FROM dual; dual是系统中的一个表

查询序列的当前值： select sq.CURRVAL FROM dual;

 

创建一个用户的语法:(要创建用户 必须要在sys下)

create user 用户名 identified by 密码;

如创建一个名为test的用户,密码为 test123

create user test identified by test123;

但是这样还不够,还得为用户添加权限

 

删除一个用户的语法:

drop user jnjcdcp_prod cascade

 

给用户添加权限语法:

grant 权限1,权限2....to 用户

如给test用户添加session权限:

grant create session to test;

给test用户添加增加用户的权限；

grant create user to test;

给test用户添加删除用户的权限；

grant drop user to test;

 

查看oracle数据库版本的语句:

select banner from sys.v_$version;

 

 

实际上一个新的用户所有的权限都要分别赋予,如果现在假设要想把多个权限一次性赋予一个用户,则可以将这些权限定义成一组角色.在oracle中提供了两个主要角色:connect,resource,我们可以直接把这两个角色赋予test 如:

grant connect,resource to test;

 

创建一个角色：

Create ROLE myrole;

然后可以给这个角色分配权限：

如  grant select on scott.emp to myrole;

然后可以把该角色分配给用户：

如： grant myrole to test;

 

超级管理员可以更改普通用户的密码: 格式如下

 alter user 用户名 identified by 密码 例子:alter user test identified by hello

一般的系统中,在用户第一次登陆的时候可以修改密码,所以要想完成此功能,可以手动让一个密码失效,格式如下:

alter user 用户名 password expire; 例子:alter user test password expire;

 

可以使用一个命令将一个用户锁住:

alter user 用户名 ACCOUNT LOCK; 例子: alter user test ACCOUNT LOCK;

解锁: alter user 用户名 ACCOUNT UNLOCK;例子 alter user test ACCOUNT UNLOCK;

 

给test用户授权scott用户下的emp表的查询及删除权限:

GRANT SELECT,DELETE ON scott.emp TO test;

给test用户授予scott用户下的emp表的所有权限；

GRANT ALL ON scot.emp to test;

回收test用户select和delete 权限:

REVOKE SELECT,DELETE ON scott.emp FROM test;

 

创建同步：

  创建同步主要用来在一个用户名在访问另一个用户名的数据表：

  例子：CREATE SYNONYM dep FOR scott.dept;

  然后 你就可以查询scott的dept表

  如：select * from dept;

 

oracle中的数据库备份:

    两个命令:

           数据库备份:exp

           数据库恢复:imp

exp:

  备份步骤: 在D盘上建立一个data的文件夹(名字随便),然后进入命令行模式,进入d:/data文件夹之中

                输入exp,然后回车,输入要备份的用户的用户名和密码,然后按回车执行.

   imp :

   回复步骤:在D盘的data文件夹下输入imp,然后按提示执行.

 

 

数据库设计的三个范式:

第一范式: 每个字段不可再分;

第二范式:

 

oracle 普通用户查看自己的权限,角色和拥有的表:

 select * from user_tab_privs;//查看用户拥有的权限

 select * from user_roles_privs;//查看用户拥有的角色

 select * from user_tables;//查看用户自己都有些什么表

 select * from user_constraints;//查看用户表的所有约束

 

 sys 用户查看任一用户的权限,角色和拥有的表;

  select * from dba_users where username='SCOTT';//查看scott用户帐户的状态

  select * from dba_tables where owner='SCOTT';//查看用户scott都有些什么表

  select * from dba_constraints where owner='JNJCDCP_PROD';//查看用户JNJCDCP_PROD的所有表的约束

 

 使用临时变量:

  select product_id,name,price from products where product_id=&product_id;

  当你执行这条sql语句的时候,会提示你输入参数

  使用define命令定义变量:

  define product_id = 7

  select product_id,name,price from products where product_id=&product_id;

  删除定义的变量用undefine

 

  自动生成sql语句(使用sql语句来生成其他sql语句的技术)

   select 'DROP TABLE' || table_name|| ';' from user_tables;

  这条语句能生成许多用来删除表的sql语句;

 

  使用集合操作符:

  UNION ALL: 返回各个查询检索出的所有行,包括重复的行

  UNION:返回各个查询检索出的所有行,不包括重复行

  INTERSET:返回两个查询检索出的共有行

  MINUS:返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的记录

 

  使用TRANSLATE(x,from_string,to_string)函数 来改变x的值:

  SELECT TRANSLATE('SELECT MESSAGE: MEET ME IN THE PARK','ABCDEFGHIJKLMNOPQRSTUVWXYZ','efghijklmnopqrstuvwxyzabcd')FROM DUAL;

//这句话的意思是将SELECT MESSAGE: MEET ME IN THE PARK话中所有的字母改变,规则是按后面两句话的规定,

如:将A变为e...

 

 使用merge合并行:

 merge into product p

    using product_change pc on(

   p.product_id=pc.product_id

) when matched then

   update

   set

        p.product_type_id=pc.product_type_id,

        p.name=pc.name,

        p.description = pc.description,

        p.price=pc.price

 

   when not match tehn

     insert (

       p.product_id,p.product_type_id,p.name,p.description,p.price

      ) values(

       pc.product,pc.product_type_id,pc.name,pc.description,pc.price

    );

关于pl/sql的一些例子：

 

1 、编写一个 pl/sql 块；

1 ）在 emp 表中查询姓名为 FORD 的工资，并显示在屏幕上。

2 ）在 emp 表中查询代号为 7934 的信息并显示在屏幕上。

参考答案：

Declare

              Tsal emp.sal%type;   //这里的%type表示变量 Tsal的类型参照 emp表中sal的类型

              Remp emp%rowtype;

Begin

              Select sal into tsal from emp where ename=’FORD’;   //表示将emp表中ename为‘FORD’的sal赋值给变量 tsal

              Dbms_output.put_line(tsal);

              Select * into remp from emp where empno=7934;

              Dbms_output.put_line(remp.empno||’ ‘||remp.ename||’ ‘||remp.sal||’ ‘||remp.deptno);

End;

 

2 、编写一个 pl/sql 块，计算 1+2+3+4+5+……+100 的值，并显示在屏幕上。

              参考答案：

Declare

                     Tsum number:=0;

              Begin

                     For I in 1..100

                     Loop

                            Tsum:=tsum+I;

                     End loop;

                     Dbms_output.put_line(tsum);

              End;

3 、编写一个 pl/sql 块；

                     查询姓名为 CLARK 的工资

工资低于 2000 涨 1500 ；

工资介于 2000-3200 之间涨 30% ；

工资高于 3200 不变。

                     用 if 和 case 分别实现。

              参考答案：

              Declare

                     Tsal emp.sal%type;

              Begin

                     Select sal into tsal from emp where ename=’CLARK’;

dbms_output.put_line(tsal);

                     Case

when Tsal<2000 then tsal:=tsal+1500;

when tsal>=2000 and tsal<3200 then tsal:=tsal+tsal*0.3;

when tsal>=3200 then tsal:=tsal;

                     end case;

                     update emp set sal=tsal where ename=’CLARK’;

                     dbms_output.put_line(tsal);

              end;

      

4 、建立一个表“ student_ 班号”，编写 pl/sql 块来实现如下功能。

1 ）字段：

 

Sid   学生代码 必须为六位整型数字

Sname  学生姓名 10 位字符型

Ssex  学生性别 1 位字符

 

2 ）插入 100 条数据

       学生姓名分别是 s001,s002,s003,s004……s100 ，

       性别分别是：如果 sid 能被 2 整除，为男性，否则为女性。

 

3 ）提示：在 oracle 中如何实现类似自增长字段？ sid 就是。

 

       参考答案：

       a) 首先建立一个表：

create table student_40(sid number(6),sname varchar2(10),ssex char(1));

       b) 建立一个序列

create sequence SEQ1
start with 100000
increment by 1;

       c)pl/sql 块

              declare

                     tsid student_40.sid%type;

                     tsname student_40.sname%type;

                     tssex student_40.ssex%type;

              begin

                     for I in 1..100

                            loop

                                   select seq1.nextval into tsid from dual;

                                   if(tsid mod 2 =0) then

                                          tssex:=’m’;

                                   else

                                          tssex:=’f’;

                                   end if;

                                   case

                                          when i<10 then tsname:=’s00’||I;

                                          when i>=10 and i<100 then tsname:=’s0’||I;

                                          when i>=100 then tsname:=’s’||I;

                                   end case;

                                   insert into student_40(sid,sname,ssex) values(tsid,tsname,tssex);

                            end loop;

                     end;