Oracle

Oracle：

1. 常用命令：

    	sqlplus system/521314@192.168.3.12:1521/orcl;	//远程登录数据库
    	lsnrctl start、stop;	//启动和停止监听
    	host cls;	//清屏
    	show user;	//显示当前用户
    	select *from tab;	//当前用户下的表
    	desc table;		//查看表的结构
    	show linesize;	//显示行宽；setlinesize 100;	//设置行宽为100
    	select concat('aaa','bbb') from dual;	//字符串拼接
    	spool C:\a.txt：spool off;	//将查询语句及结果保存到文件中
    	select distinct name from t_user;	//去除重复
    	col name for a20;		//表示字段name列显示20个字符
    		select *from v$nls_parameters;	//查看当前的一些参数类型(Date,Time,字符集等)
    		alter session set NLS_DATE_FORMAT='yyyy-mm-dd';	//修改当前日期格式
    	*** select sysdate from dual; 	//获取当前系统的日期
    	*** select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;	//格式化时间	
   

2. 过滤和排序

    select *from t_user where id between 1 and 10;	//查询id在1到10之间用户		
    select *from t_user where id in(1,2,3);		//查询在集合(in)或者不在集合(not in)中的数据
    select *from t_user where name like '%张%';	//模糊查询，‘----’一个下划线表示一个字符
    select *from t_user order by id asc/desc;		//根据id升序/降序排序
    set pagesize 20;	//一页显示的信息数目
   

3. 字符串函数

    select lower(name) from t_user where id=1;		//将name转成小写(或大写upper；或首字母大写initcap)
    select trim(‘ ’ from '  hello ') from dual;	//去除前后的空格
    select substr(name,a,b) from t_user where id=1;	//	从name的第a个字符开始截取b个字符
    select instr(name,a) from t_user where id=1;	//a字符串在name中的位置
    select length(name) from t_user where id=1;		//字符串的字符数或字节数(lengthb)
    select lpad(name,5,a) from t_user where id=1;	//如果name的长度不够5个字符，用a左填充(或右填充rpad)
    select replace(name,a,b) from t_user wnere id=1;	//用b字符串替换name中的a字符串
   

4. 浮点数

    select round(3.123456,5) from dual;	//保留小数点后5位，四舍五入
    select trunc(3.123456,5) from dual;	//保留小数点后5位，后面舍去
   

5. 空操作

    select name,nvl(birthday,a) from t_user;	//如果birthday为空，则值为a
    select name,nvl2(birthday,a,b) from t_user;	//如果birthday为空，则值为b，否则值为a
    select nullif(2,1) from dual;		//当a=b时返回null,否则返回a
    select id,age,coalesce(id,age) from t_user;	//从左到右查询返回第一个不为null的值
   

6. 条件查询

    select case id when 6 then id+1 end from t_user;	//查询id，如果id=6，则id+1
    select decode(id,6,id+1,5,id+2,id-1) from t_user;	//查询id，如果等于6，则id+1，如果id=5，则id+2，否则id-1；
   

7. 分组查询

    select avg(nvl(id,0)),name from T_user group by name;	//根据name分组，然后对id求平均值
    select avg(nvl(age,0)),name from T_user group by name having avg(nvl(age,0)) > 1;		//根据name分组，查询平均年龄大于1的
    select id ,age from T_user group by rollup(id,age);	//报表汇总，根据id分组，然后求出平均值
   

8. 集合查询

   select *from t_user where id<15 union select *from t_user where id>20;	//查询结果的并集，(两个查询结果列数需要相同)
   

9. 创建表

    create table user as select *from t_user where age=20;	//创建一个表，将查询的数据插入
    alter table t_user add address varchar(20) ;	//添加字段删除线格式 
    alter table t_user modify address varchar(40) ;		//修改字段信息
    alter table t_user drop column address;		//删除字段 
    alter table t_user rename column address to dizhi;	//修改字段名
    rename t_user to tuser;		//更改表名
    drop table t_user;		//删除表
    show recyclebin;		//显示回收站
    purge recyclebin;		//清空回收站
    flashback table t_user to before drop;		//闪回删除，找回删除的表
   

10. 主外键

    create table t_user (
    id int primary key,		//	主键约束
    order_id int references order(id),		//外键约束
    gender varchar(20) check(gender in('男','女'))
    )
    

11. 视图

    1. 先对登录的用户进行授权操作：
       sqlplus sys/manager as sysdba;
       grant creater view to system;

    2. 创建视图

       create view user_view as select *from t_user;

12. 序列(sequence)

    create  sequence mysequence;		//创建序列
    select mysequence.nextval from dual;		//查询序列的下一个值
    select mysequence.currval from dual;		//查询序列的当前值
    insert into t_user values(mysequence.nextval,'xdd',18,1);
    

13. 同义词

    1. 授权：sqlplus sys/manager as sysdba;
       grant creater synonym to system;
    2. create synonym tuser for t_user; //取别名（同义词）
    3. grant select on system.t_order to sys; //给sys查询t_order表的权限

14. PL/SQL

    1. 格式

        set serveroutput on;		//设置允许在控制台输出
        declare	
        		//声明变量
        		username t_user.name%TYPE;
        		password t_user.password%TYPE;
        begin
        		//给变量赋值
        		select name,passwrod into username,password from t_user where id=1;
        		DBMS_OUTPUT.PUT_LINE(username || '=========' || password);
        end;
       

    2. IF

        set serveroutput on;
        ACCEPT num  PROMPT '请输入一个数字：';	//接收一个字符
        declare	
        	pnum  NUMBER:= #
        begin
        IF pnum=1 THEN
        	DBMS_OUTPUT.PUT_LINE('输入的是：1');
        ELSIF pnum=2 THEN
        	DBMS_OUTPUT.PUT_LINE('输入的是：2');
        ELSE
        	DBMS_OUTPUT.PUT_LINE('输入的是：X');
        end IF;		//结束IF
        end;
       

    3. 循环

        declare	
        	pnum NUMBER :=1;
        begin
        	loop			
        	exit when pnum > 10;		//结束条件
        	DBMS_OUTPUT.PUT_LINE(pnum);
        	pnum:=pnum+1;
        	end loop;
        end;
       

    4. 光标

        set serveroutput on
        declare	
        	CURSOR user_cursor is select name,age from t_user;	//将查询的数据赋值给光标
        	tname t_user.name%TYPE;
        	tage t_user.age%TYPE;
        begin
        	open user_cursor;		//打开光标
        	loop
        	exit when user_cursor%notfound;				//跳出循环的条件，
        	fetch user_cursor into tname,tage;			//遍历光标，并赋值
        		DBMS_OUTPUT.PUT_LINE(tname||'-----'||tage);
        	end loop;
        	close user_cursor;
        end;
       

    5. 异常

        set serveroutput on
        declare	
        	CURSOR user_cursor is select name from t_user where id=7;
        	username t_user.name%TYPE;
        	not_user EXCEPTION;			
        begin
        	open user_cursor;
        		FETCH user_cursor into username;		
               if 	user_cursor%notfound 	then 			//如果光标不存在
        		RAISE not_user;			//抛出异常
        		end if;
        	close user_cursor;
        	EXCEPTION 
        		WHEN not_user then  DBMS_OUTPUT.PUT_LINE('用户不存在');		
        end;
       

    6. 存储过程

       1. 无参存储过程

           create or replace procedure AAA 
           as
           begin 
           end;
           //调用存储过程方式1
           	execute AAA();
           //调用存储过程方式2
           begin
           	AAA()；
           end;
          

       2. 无参存储过程

           create or replace procedure BBB(id in number)//in表示是参数，out表示是输出
           as
           begin 
           end;
           //调用存储过程方式1
           	execute AAA();
           //调用存储过程方式2
           begin
           	AAA()；
           end; 
          

    7. 存储函数

       create or replace function AAA(tid in number) return number
       as	//声明变量
       	tname t_user.name%type;
       	tage t_user.age%type;
       begin
       select name,age into tname,tage from t_user where id=tid;
       return age;
       end;
       

    8. 触发器

        //创建触发器
        create trigger AAA after insert on t_user	//在t_user插入数据后执行
        declare
        begin
        end;