sql学习，包含sqlserver、mysql、oracle

----------------------------------------------sqlserver

select * from Table_Test

select top 3 Id from Table_Test /*测试查询最大条数*/

/*insert into Table_Test(Id, Name, Num, Sex) VALUES(8, 'ASDASF', 123456, '男')*/

select Name from Table_Test

select * from Table_Test where Name Like '%D%'/*模糊查询*/

select * from Table_Test where Name Like 'D%'

select * from Table_Test where Name Like '%D'

select * from Table_Test where Name Like '__D'/*_  代表一个字符*/

select * from Table_Test where Name Like '_D_'/*_  代表一个字符*/

select * from Table_Test where Name IN('aaa', 'bbb', 'ddd')/*多条件*/

select * from Table_Test where Name BETWEEN 'aaa' AND 'e'/*BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期*/

select * from Table_Test1

select Table_Test.Id, Table_Test1.Id from Table_Test INNER JOIN Table_Test1 ON Table_Test.Id = Table_Test1.Id/*多表查询

INNER JOIN：如果表中有至少一个匹配，则返回行

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

FULL JOIN：只要其中一个表中存在匹配，则返回行*/

select Table_Test.Id, Table_Test1.Id from Table_Test LEFT JOIN Table_Test1 ON Table_Test.Name = Table_Test1.Name

select Table_Test.Id, Table_Test1.Id from Table_Test LEFT JOIN Table_Test1 ON Table_Test.Id > Table_Test1.Id

select * from Table_Test UNION select * from Table_Test1/*合并多个表查询结果*/

/* 复制表数据插入新表

SELECT *

INTO newtable [IN externaldb]

FROM table1;

复制指定列插入新表

SELECT column_name(s)

INTO newtable [IN externaldb]

FROM table1;*/

/*从一个表中复制所有的列插入到另一个已存在的表中

INSERT INTO table2

SELECT * FROM table1;

只复制希望的列插入到另一个已存在的表中

INSERT INTO table2

(column_name(s))

SELECT column_name(s)

FROM table1;*/

/// 索引

select * from Lerning_test

select * from Lerning_test where CaiLiaoName = 'sdfgds'

CREATE INDEX Lerning_test_Id ON Lerning_test(Id)

select * from Lerning_test where Id = '1234' and CaiLiaoName = 'cailiao12'

CREATE INDEX Lerning_test_Id_CaiLiaoName ON Lerning_test(Id, CaiLiaoName)

CREATE INDEX Lerning_test_CaiLiaoName ON Lerning_test(CaiLiaoName)

--查看使用情况

EXPLAIN

SELECT * FROM Lerning_test

WHERE Id = '1234' and CaiLiaoName = 'cailiao12'

select * from Lerning_test where Id=upper('123');

SELECT * FROM sys.sysindexes

WHERE id=object_id('Lerning_test')

insert into Lerning_test(CaiLiaoName, XingHao, DanZuShu, DanWei, ZhongShu, LingYongShu, TuiHuanShu, TuiHuanRiQi)

values('sdfgds', 'sdfs', 'sdfss', 'sdfdsf', 'sdf', 'sdfd', 'sdfd', 'sdfsdf')

drop index Lerning_test_CaiLiaoName on Lerning_test

sqlseever 触发器

/*create trigger Test_insert

on Table_Test

after insert

as

begin

    if object_id(N'Table_Test',N'U') is null--判断student_sum表是否存在

        create table Table_Test(stuCount int default(0));--创建存储学生人数的student_sum表

    declare @stuNumber int;

    select @stuNumber = count(*)from Table_Test;

    if not exists (select * from Table_Test)--判断表中是否有记录

        insert into Table_Test(Name, Password, emil) values('123', 'test', 'test')

    update Table_Test set emil = @stuNumber; --把更新后总的学生数插入到student_sum表中

end*/

create trigger Test_Select

on Table_Test1

FOR update

as

begin

    if object_id(N'Table_Test',N'U') is null--判断student_sum表是否存在

        create table Table_Test1(stuCount int default(0));--创建存储学生人数的student_sum表

    declare @stuNumber int;

    select @stuNumber = count(*)from Table_Test;

    if not exists (select * from Table_Test1)--判断表中是否有记录

        insert into Table_Test1(Name, Password, emil) values('123', 'test'， 'test')

    update Table_Test1 set emil = '这是更新数据的插入'; --把更新后总的学生数插入到student_sum表中

end

update Table_Test set emil = '';

insert into Table_Test(Name, Password, emil) values('123', 'test', 'test');

//存储过程书写   mysql

create PROCEDURE test()

BEGIN

SELECT NAME from test;

END

----------------------------------------------------------------

CREATE PROCEDURE test(in sum INT)//不区分大小写

BEGIN

SELECT name from test WHERE id = sum;

//set sum = 10;//内部参数值会被改变

END

----------------------------------------------------------------

CREATE PROCEDURE test(out result int)

BEGIN

SELECT COUNT(*) INTO result FROM test;

END

SELECT COUNT(*) FROM test;

SELECT @result;

CALL test(@result);

SELECT @result;//得到结果

----------------------------------------------------------------

CREATE PROCEDURE test (IN sum INT, OUT result INT)

BEGIN

SELECT

COUNT(*) INTO result

FROM

test

WHERE

id = sum;

END

SET@id = 1;

CALL test(@id, @result);

SELECT @result;

----------------------------------------------------------------

CREATE PROCEDURE TEST (INOUT in_out INT)

BEGIN

SELECT

COUNT(*) INTO in_out

FROM

test

WHERE

1 = in_out;

END

set @id = 1;

CALL test(@id);

SELECT @id;

----------------------------------------------------------------

if _ then _ elseif/else end if _ ;

CREATE PROCEDURE inser (

INOUT _name VARCHAR (50),

INOUT emil VARCHAR (50)

)

BEGIN

DECLARE

temp INT;

SELECT

COUNT(*) INTO temp

FROM

test

WHERE

NAME = _name;

IF temp > 0 THEN

SET _name = 2;

ELSE

SET _name = 5;

END

IF;

SELECT

_name;

END

set @name = 'xxx';

set @emil = 'xxx';

CALL inser(@name, @emil);

----------------------------------------------------------------

case _ when _ then _ end case;

DROP PROCEDURE

IF EXISTS inser;

/*如果存在则先删除*/

CREATE PROCEDURE inser (

INOUT _name VARCHAR (50),

INOUT emil VARCHAR (50)

)

BEGIN

DECLARE

temp INT;

SELECT

COUNT(*) INTO temp

FROM

test

WHERE

NAME = _name;

IF temp > 0 THEN

SET _name = 2;

ELSE

SET _name = 5;

END

IF;

CASE _name

WHEN 2 THEN

SET _name = 1;

WHEN 5 THEN

SET _name = 0;

END CASE;

/*相当于while循环*/

WHILE _name = 1 DO

SET _name = 10;

END

WHILE;

WHILE _name = 0 DO

SET _name = 20;

END

WHILE;

/*相当于do-wehile循环*/

REPEAT

SET _name = _name + 1;

UNTIL _name = 30

END

REPEAT

;

loop_l: LOOP

SET _name = _name + 1;

IF _name = 40 THEN

/*离开循环 BREAK*/

LEAVE loop_l;

ELSE

/*循环继续  CONTINUE*/

ITERATE loop_l;

END

IF;

END

LOOP

;

SELECT

_name;

END

set @name = 'xxx';

set @emil = 'xxx';

CALL inser(@name, @emil);

------------------------------------------------------

-- 查看数据库里的存储过程

SELECT

*

FROM

mysql.proc

WHERE

db = 'test';

-- 这个语句是MySQL的扩展，它返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。PROCEDURE和FUNCTION分别表示查看存储过程和函数

SHOW PROCEDURE STATUS

WHERE

db = 'test';

--查看存储过程

SHOW CREATE PROCEDURE inser

----------------------------------------------------------

----------------------------------------------redis缓存

exists key 测试指定key是否存在

del key1 key2 ⋯ keyN 删除指定的key

type key 返回给定key的value类型

keys pattern 返回匹配指定模式的所有key keys * * 代表所有，?代表一个字符

rename oldkey newkey 改名字

dbsize 返回当前数据库key数量

expire key seconds 为key指定过期时间

ttl key 返回key剩余的过期秒数

select db-index 选择数据库

move key db-index 将key从当前数据库移动到指定数据库

flushdb 删除当前数据库中所有key

flushall 删除所有数据库中的所有的key

dump key 序列化指定key，并返回序列化的值

pttl key 以毫秒为单位返回key的剩余的过期时间

persist key 移除key的过期时间，key将持久保存

key名称不区分大小写

String 是redis的最基本的数据类型，一个key对应一个value。

String类型是二进制安全的，意思是redis的String可以包含任何数据。比如jpg图片或者序列化的对象。

String类型是redis最基本的数据类型，一个键最大能存储512Mb

二进制安全是指：在传输数据时，保证二进制数据的信息安全，也就是不被篡改，破译等，如果被攻击，能够及时检测出来。

二进制安全的特点：

1.编码，解码发生在客户端完成，执行效率高

2.不需要频繁的编解码，不会出现乱码。

------------------------------------

//只有在key不存在时设置key的值才能成功

setnx key value

//返回给定key的value类型

type key

//用于获取存储在指定key中字符串的子串   getrange keyname 0 1

GETRANGE key start end

//获取所有（一个或多个）指定key的值   mget keyname1 keyname2

MGET key1 [key2...]

//GETSET命令用于设置指定key的值，并返回key的旧值，当key不存在时，返回nil。

GETSET key_name value

//返回key所储存的字符串值的长度。

STRLEN key

//删除语法

DEL key_name

//INCR命令将key存储的数字值增1，如果key不存在，那么key的值会先初始化为0，然后再执行incr操作

INCR key_name

//incrby命令将key中存储的数字加上指定增量

INCRBY key_name 增量

///减量

DECR key_name或DECRBY key_name

//append命令用于为指定的key追加至末尾，如果不存在，为其赋值。

APPEND key_name value

/HASH  存储数据

///String类型通常用于保存单个字符串或JSON字符串数据

///因为String是二进制安全的，所以你完全可以把一个图片文案的内容作为字符串来存储。

///计数器

//存储值

HSET key FILED value

HMSET KEY FILED VALUE [FILED1,VALUE1]

//得到值

HGET key FILED

//返回HASH表中所有字段和值

HGETALL key

//获取所有哈希表中的字段

HKEYS key

//获取哈希表中字段的数量

HLEN key

//删除一个或多个HASH表字段

HDEL KEY field[field1]

//只有在字段不存在时，设置hash表字段值

HSETNX key field value

//为哈希表key中指定字段的整数加上增量increment

HINCRBY key field increment

//为哈希表key中指定字段的浮点数加上增量increment

HINCRBYFLOAT key field increment

//查看哈希表key中，指定的字段是否存在

HEXISTS key field

----------------------------------------------------------------ORACLE 数据库操作

-------添加表

create table studentinfo(

       studentid number(2) primary key,

       studentname varchar(10) not null,

       studentsex char(2) check(studentsex='男' or studentsex='女'),

       studentage number(2) not null,

       studenttel number(11) unique,

       studentaddress varchar(50) default '上海',

       classid number(2) references classinfo(classid)

       );

--alter table TEST ADD(REM VARCHAR(500));alter table TEST DROP COLUMN NAME  修改列 删除列

--select * from user_tab_columns  where table_name = 'TEST'; 查看表结构   desc table_name

--alter table student modify(stu_id number(4));修改字段         【alter table 表名 modify(属性)】

--alter table test modify(Id number(20));整形数据修改

--drop table  表名;删除表

--Insert into 表名(属性) values(属性值)；插入数据

--update 表名 set 字段名='修改后的值' where 限制条件；更新数据

--select 字段 from 表名/视图名 where 条件 group by (字段) having 条件 order by 字段   查询分组语句

------分配权限

grant connect ,resource,dba to user_name;

--connect   是链接数据库权限，可以对数据库进行增删改查

--resource  资源使用权限，用来创建表格

--dba  是数据库管理员权限

--查询权限

grant create session to

grant create table to "xiaofei"     　　   --给用户赋予表操作的权限

grant unlimited tablespace to  "xiaofei"     --给用户赋予表空间操作的权限

grant select any table to "xiaofei"         --给该用户赋予访问任务表的权限   同理可以赋予update 和delete 的

-------创建用户

create  user user_nameidentified by password;

--创建用户名为user_name为laowang设置用户密码password

alter  user "xiaofei" identified by ---修改密码

-------回收权限

revoke connect,resource from user_name;

--revoke    --回收

--connect,rescource   --创建以及查询等权限

--from    --从谁哪里回收  

--加锁：  alter  user user_name account lock;

--解锁：  alter  user user_name account unlock;      

--increment by 1

--start with 1

--minvalue 1

--maxvalue 999999999

--nomaxvalue

--nocycle

--nocache

oracle建立自增的步骤

1.建表

2.create sequence test_id_increment increment by 1 start with 1 nomaxvalue nocycle nocache  //创建序列

3.创建触发器

--create or replace trigger test_id_increment_trigger

--before insert on test  --before:执行DML等操作之前触发

--for each row  --行级触发器，-- WHEN (new.ID is null) 设置主键存在时，不触发触发器

--begin

-- select test_id_increment.nextval into :new.id from dual;

--end;

before：能够防止某些错误操作发生而便于回滚或实现某些业务规则，适用于实现自增字段；

after：在DML等操作发生之后发生，这种方式便于记录该操作或做某些事后处理信息；

-- 更改序列  test_id_increment

alter sequence test_id_increment

    increment by 2 

    minvalue 1

    maxvalue 999999999;

--获取序列自增ID --

select test_id_increment .Nextval 自增序列ID from dual;

-- 删除序列 --

drop sequence test_id_increment

--调用序列，插入Student数据 --

insert into Student(stuId,Stuname) values(test_id_increment .Nextval,'张三');

------------------------------

select user from dual;//查询当前连接用户

select * from product_component_version;//查询当前版本

//解锁未commit的语句进程id

SELECT object_name, machine, s.sid, s.serial#

FROM gv$locked_object l, dba_objects o, gv$session s

WHERE l.object_id　= o.object_id

AND l.session_id = s.sid and object_name = 'DOCBOX'

select s.sid, s.serial# from v$locked_object lo, dba_objects ao, v$session s where ao.object_id = lo.object_id and lo.session_id = s.sid

alter system kill session '2003, 17309';

alter system kill session '2723, 33342';

alter system kill session '2003, 17309';

alter system kill session '1303, 5529';

//删除重复的数据

select*  from bdcinfo.xs_dyaq where (bdcdyh) in (select bdcdyh from bdcinfo.xs_dyaq group by bdcdyh having count(bdcdyh) >1) and rowid not in (select min(rowid) from bdcinfo.xs_dyaq group by bdcdyh having count(*)>1) and ywh='202005190004507'

//查询回话

select username,serial#, sid from v$session;  ---查询用户会话

select count(*) from v$session;

select count(*) from v$session where status='ACTIVE';

select value from v$parameter where name = 'processes';

select username,count(username) from v$session where username is not null and status='ACTIVE' group by username;

select * from v$session where status='ACTIVE' order by username

//添加字段

alter table bi_trade_info add(LEAGUER_TYPE NUMBER(1));

//去重

DELETE from limit_user WHERE (func) IN ( SELECT func FROM limit_user GROUP BY func HAVING COUNT(func) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM limit_user GROUP BY func HAVING COUNT(*) > 1);

///前后去0

select ltrim(rtrim(to_char('00.3400','99990.000'),'0')) l from dual

SELECT OWNER, TABLE_NAME AS "表名", COMMENTS AS "表注释"  FROM  ALL_TAB_COMMENTS where owner = 'NETOBDC' ORDER BY TABLE_NAME

------查询出所有表，导出Excel ，作为基表存在

select

    B.TABLE_NAME AS "F表名",

    C.COMMENTS AS "表说明",

    B.COLUMN_ID AS "字段序号",

    B.COLUMN_NAME AS "字段名",

    B.DATA_TYPE AS "字段数据类型",

    A.COMMENTS AS "字段描述"

FROM

    ALL_COL_COMMENTS A,

    ALL_TAB_COLUMNS B,

    ALL_TAB_COMMENTS C

WHERE

    A.TABLE_NAME IN (SELECT U.TABLE_NAME FROM USER_ALL_TABLES U)

    AND A.OWNER = B.OWNER

    AND A.TABLE_NAME = B.TABLE_NAME

    AND A.COLUMN_NAME = B.COLUMN_NAME

    AND C.TABLE_NAME= A.TABLE_NAME

    AND C.OWNER = A.OWNER

    AND A.OWNER = 'NETOBDC'

/*   AND A.TABLE_NAME = 'ABNORMAL_TRAN_MONITOR'*/

ORDER BY A.TABLE_NAME ,B.COLUMN_ID

-----查询出所有表名称和每个表内容，导出Excel ，供之后使用宏拆分

--天

select TO_NUMBER(to_char(TO_NUMBER(TO_DATE(to_char(sysdate + 1, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')- TO_DATE('2021-10-09 11:20:01','yyyy-mm-dd hh24:mi:ss')), '000.9'))

AS days from dual;

--时

select TO_NUMBER(to_char(TO_NUMBER((TO_DATE((select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') ti from dual),'yyyy-mm-dd hh24:mi:ss')- TO_DATE('2021-10-20 9:13:20','yyyy-mm-dd hh24:mi:ss'))*24), '999'))

AS mons from dual;

--分

select TO_NUMBER(to_char(TO_NUMBER((TO_DATE((select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') ti from dual),'yyyy-mm-dd hh24:mi:ss')- TO_DATE('2021-10-20 10:16:20','yyyy-mm-dd hh24:mi:ss'))*24*60), '999'))

AS mins from dual;

--秒

select TO_NUMBER((TO_DATE((select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') ti from dual),'yyyy-mm-dd hh24:mi:ss')- TO_DATE('2021-10-20 11:15:20','yyyy-mm-dd hh24:mi:ss'))*24*60*60)

AS secs from dual;

//恢复数据

insert into sys_dictionary

//时间戳

Select * From  sys_dictionary As Of   timestamp to_timestamp('2021-11-10 17:00:00','yyyy-mm-dd hh24:mi:ss')

//导出数据字典

SELECT ROWNUM 序号,

       A.COLUMN_NAME AS "字段名称",

       B.comments AS "字段描述",

       A.DATA_TYPE as 字段类型,

       REPLACE(DECODE(A.CHAR_LENGTH,

                      0,

                      DECODE(A.DATA_SCALE,

                             NULL,

                             A.DATA_TYPE,

                             A.DATA_PRECISION || ',' || A.DATA_SCALE),

                      A.CHAR_LENGTH),

               ',0',

               '') 字段长度,

       REPLACE(REPLACE(A.NULLABLE, 'Y', '√'), 'N', '') AS "能否为空",

       A.DATA_DEFAULT AS "默认值"

  FROM sys.all_tab_columns A, sys.DBA_COL_COMMENTS B

WHERE A.owner = B.owner

   AND A.table_name = B.table_name

   AND A.COLUMN_NAME = B.COLUMN_NAME

   --AND A.owner = '用户名'

   AND A.TABLE_NAME IN ('UT_SMS')

ORDER BY A.TABLE_NAME

select s.*, s.rowid FROM sys.all_tab_columns A, sys.DBA_COL_COMMENTS B

select s.* from sys.all_tab_columns S where s.table_name = 'ST_SMS_SETTING'

//创建函数索引

create index index_tonumber_iid on  SYS_HJYW_REPINFO(to_number(IID));

//字符合并拆分

select replace(wm_concat(value), ',', '/')

  from sys_dictionary s

where category = '房屋用途'

   and name in

       (select s.*

          from table (Select Split('住宅/办公', '/') As sss From Dual) s)

//误删恢复

insert into ARCHIVES.ZTXX 

SELECT * FROM ARCHIVES.ZTXX  as of timestamp to_timestamp('2022-04-21 9:50:00','yyyy-MM-dd hh24:mi:ss') WHERE YWBH = '202203298000949'

//循环解除锁表

declare

strSql varchar(500);

begin

for r in (select distinct t2.sid,t2.serial#,t2.logon_time,object_name  from v$locked_object t1,v$session t2,dba_objects t3  where t1.session_id=t2.sid and t1.OBJECT_ID=t3.OBJECT_ID and  object_name='DJ_SQ_H' ) loop

  strSql := 'alter system kill session '''||r.sid||','||r.serial#||'''';

  execute immediate strSql;

end loop;

end;

//创建oralce数据库sql运行快照

call dbms_workload_repository.create_snapshot()