444.sql

SQL server 中常用的语句

/*
    PL/SQL练习
*/
sys用户具有“SYSDBA”或者“SYSOPER”权限，登陆em也只能用这两个身份，不能用normal。
“SYSOPER”权限，即数据库操作员权限，权限包括：
  打开数据库服务器   关闭数据库服务器
  备份数据库       恢复数据库
  日志归档         会话限制
“SYSDBA”权限，即数据库管理员权限，权限包括：
  打开数据库服务器   关闭数据库服务器
  备份数据库       恢复数据库
  日志归档       会话限制
  管理功能       创建数据库
host regedit;
truncate table XS;      --初始化一个表
clear scr;
--查看拥护下对象
select * from tabs[tab];
select rownum as a from XS;     --自动编号字段
declare 
    v_ename varchar2(30);
begin
    select stu_name into v_ename from XS where stu_num=&no;
    dbms_output.put_line('该同学的姓名为:'||v_ename);
    exception                         --处理异常
    when no_data_found then
         dbms_output.put_line('没找到记录');
end;
 select stu_num||stu_name as "名单" from XS;    --通常用于连接字段
 insert into XS values('001105','鲁小明','外国语',0,to_date('1984-3-4','yy-mm-dd'),60,'先学数据结构');
/*循环插入数据*/
select * from   user_indexes      --查询现有索引
 declare
      no number :=10;
begin
      for i in 1..20 loop
          insert into lu values(no,'测试数据');
          no:=no+i;
      end loop;
end;
/*更改001104号学生信息(总学分)*/
select * from XS;
declare 
       xf_add constant number:=10;
       begin
       update XS set stu_xf=stu_xf+xf_add where stu_num='001104';
       end;
/*写成命名程序块(存储过程)*/
create or replace procedure stu_update 
       (v_stunum in varchar2,xf_add in number default 10 ) is
       begin
       update XS
       set stu_xf=stu_xf+xf_add where stu_num=v_stunum;
       end;
execute stu_update('001104',15);       //执行存储过程,在sql/plus下执行
/*在另一个程序块中执行存储过程,则不需要execute关键字*/
declare
        v_stunum varchar2;
        begin
        select a.stu_num into v_stunum from XS_KC a where kc_num='001';
        stu_update(v_stunum);
        end;

/*变量的声明和使用*/
declare
        变量名  [constant] 数据类型 [not null]:=|default
   如: declare constant v_no number:=20;
        declare v_no number default 20;
//给变量赋值有两种情况
1.x:=10;          y=y+(x*20);          --直接赋值
2.select sum(total) into x;            --通过SQL语句赋值
/*为可减少程序的修改,防止把变量插入到表中数据类型不一致*/
declare v_num XS.stu_num%TYPE或stu_num%rowtype;
/*PL/sql数据类型*/
number,binary_integer,dec,double precision,integer,int,numberic,real
smallint varchar2,char,long,date,boolean,rowid;
/*对于具有共同特性的功能模块最好使用存储过程,存储过程不能由SQL语句直接使用,只能通过execute或pl/sql内部调用*/
//创建存储过程,存储过程没有返回值
//创建一个存储过程,显示指定学号的学生信息和选课信息
drop procedure stu_info_kc;
create or replace procedure stu_info_kc
       ( v_stunum varchar2,out_num out varchar2) is
       begin
         select b.kc_num into out_num from XS a,XS_KC b where a.stu_num(+)=b.stu_num and a.stu_num=v_stunum;
         --print out_num;
       end;
variable vnum varchar2(20);
execute stu_info_kc('001104',:vnum);            --执行
print vnum;
select * from XS_KC;
/*将一个表中的数据插入到另一个表中*/
create or replace procedure get_name
         (num varchar2)
  is
  begin
         select stu_name into v_name  from XS where stu_num=num;
  end;
  declare 
         cursor rao_lu_cursor is select stu_num,stu_name,stu_majar,stu_sex,stu_birthday,stu_xf,stu_bz from XS;
         lu_rao_record rao_lu_cursor%ROWTYPE ;
  begin
       open rao_lu_cursor;
       loop
         fetch rao_lu_cursor into lu_rao_record;
         exit when rao_lu_cursor%NOTFOUND;
         insert into lu values(lu_rao_record.stu_num);
        end loop;
        close rao_lu_cursor;
        
 end;
/*创建同义词*/
create synonym name for username.tablename;
 /*创建触发器(要限制触发器的行数,不要创建递归的触发器)*/
 /*格式:
        create [or replace ] trigger trigger_name {before|after} 
        {update,delete,insert} on table [for each]
 */
 //删除某个学生信息时,同时删除学生课程表中该同学的选课信息
 create or replace trigger del_KC_XS
        before delete on XS
        for each row
        begin
        delete from XS_KC where stu_num=:old.stu_num;
        end;
delete from XS where stu_num='001104'        --测试
select * from XS_KC;
 //删除触发器
drop trigger 触发器名 on 你的表;
/*使用游标*/
/*
     显式游标使用步骤:
     1.声明游标
     2.打开游标
     3.从游标中去数据
     4.关闭游标
     
*/
//格式
     declare cursor c1 is select view_name from all_views where rownum<=10;
//示例
declare
cursor c1 select view_name from all_views
where rownum<=10 order by view_name;
vname varchar2(40);
begin
open c1;
fetch c1 into vname;
while c1%found loop
      dbms_output.put_line(to_char(c1%rowcount)||''||vname);
      end loop;
      end;
      close c1;
/*创建自定义函数显示某个雇员的工作时间*/
create or replace function Hire_day(no emp.empno%TYPE)
return number AS
v_hiredate emp.hiredate%TYPE;
v_day number;
begin
      select hiredate into v_hiredate from emp where empno=no;
      v_day:=cell(sysdate-v_hiredate);
      return v_day;
      end;
/*使用包*/
create package znz_package       //创建包头
is
man_num number;                            //男生人数
woman_num number;                          //女生人数
cursor student;
create function f_count(in_sex,in student.sex%Type)
return number;
create procedure p_count
       (in_sex in student.sex%Type,out_num out number);
end znz_package;
--包体部分
create package body znz_package
as
       cursor student is
       select stu_num,stu_name from XS;
       function f_count
       (in_sex in student.sex%Type)
       return number
       is
       out_num number;
       begin
       if in_sex='男' then
       select count(stu_sex) into out_num
       from XS where stu_sex='男';
       else
       select count(stu_sex) into out_num
       from XS where stu_sex='女';
       end if;
       return(out_num);       --返回函数值
       end f_count;            --函数定义结束
       procedure p_count
       (in_sex in student.sex%TYPE,
       out_num out number)
       as
       begin
        if in_sex='男' then
        select count(stu_sex) into out_num
        from XS where stu_sex='男';
        else
        select count(stu_sex) into out_num
        from XS where stu_sex='女';
        end if;
        end p_count;
        end znz_package;
/*oracle数据库管理*/
//创建数据库
create database luzhide [参数,参数..]
//启动数据库
startup nomount  --非安装方式启动数据库,这种启动只需要参数文件init.ora;
startup mount dbname --安装方式启动数据库, 相当与nomount+打开控制文件
startup open dbname  --在上面的基础上再打开数据文件
startup
startup restrict  约束方式启动           --只允许某种特权的用户访问
startup force  --相当与先关闭数据库,然后再执行启动数据库命令
startup pfile="E:\....";            --带初始化参数文件的启动方式
//关闭数据库
shutdown normal  --正常方式关闭
shutdown immediate  --立即方式关闭数据库
                    --这种方式并不立即关闭数据库,而是在Oracle执行某些清楚工作(如终止会话)才关闭
shutdown abort  --直接关闭数据库
                    --迫不得已的情况下才执行此操作
shutdown transactional--事务处理关闭
/*管理表空间*/
//物理上,数据库中的数据存储在数据文件中,逻辑上,数据文件存储在表空间中
//表空间的类型:字典管理方式,本地管理方式
//创建表空间
create tablespace;                  //创建一般的表空间
create temporary tablespace;        //创建临时表空间
create undo tablespace;             //创建撤消表空间
alter tablespace 表空间名称;        //修改
drop tablespace 表空间名称;         //删除
select * from v$tablespace;
select * from dba_tablespaces;      //
/*创建和管理数据文件*/
alter tablespace system
      add datafile 'E:\orace\oradata\test1.dbf' size 10M
      autoextend on
      next 512k
      maxsize 250M;
//使使用的数据文件联机或脱机
alter database datafile filename online[offline];
//查看数据文件,可以通过数据字典视图
v$datafile;控制文件中包含的数据文件的信息
/*管理重做日志文件*/
//创建重做日志组
alter database add logfile
('E:\Oracle\Oracledata\redo1.rdo','E:\Oracle\Oracledata\redo2.rdo') size 100M;
//创建成员日志文件,不能指定它的大小,因为新添加的日志文件必须和组里面其他日志文件有相同的大小
alter database add logfile member
	'E:\Oracle\Oracledata\redo1.log' to group 3;
//删除重做日志组
alter database drop logfile group 3;		//只是删除了在数据字典和控制文件中的记录信息,但并不会删除操作系统中的相应文件
//删除重做日志
alter database drop logfile member	//只是删除了在数据字典和控制文件中的记录信息,但并不会删除操作系统中的相应文件
       'E:\Oracle\oracledata\redo22.log'
//清空重做日志组,相当与删除该重做日志文件,然后在创建它
alter database clear logfile group 3;
//查询重做日志文件信息
v$log v$logfile  v$log_history
/*管理数据库对象*/
//grant赋予用户权限
系统权限权限有:connect(基本连接),dba(数据库管理)等
常用的数据对象权限有:
	all on
	select on
	update on
 	delete on
	insert on
	alter on
//删除引用该表的其他表的完整性约束
drop table lu.region cascade constraints;
//建立索引
create index index_name
	on lu(lu.stu_num) tablespace system;
//触发器的作用
	1.自动地生成导出列值
	2.防止无效事务
	3.实施复杂的安全审核
	4.实施复杂的事务规则
	5.提供透明的事件记录
	6.提供高级审计
	7.维护同步的表副本
	8.收集表存取的统计信息
/*数据完整性*/
oracle中提供了以下的完整性约束
not null 	unique(唯一关键字) primary key(主建) foreign key(外建)
前面不加constraint 则用默认的名字
举例:
		stu_num constraint stu_num_ys primary keys