SQL

TheSadPoison

于 2018-10-17 11:49:59 发布

阅读量88

点赞数

分类专栏： basa

本文链接：https://blog.csdn.net/qq_42741973/article/details/83107062

版权

basa 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SQL

约束

1:主键约束  
	索引类型的存在,方便用户用来检索,主键的特性有两点: 不能为空 不能重复 ;oracle 一个表中可以有一个列(唯一 )或多个列(复合主键)一起作为主键存在
    使用复合主键之间需要注意,数据库会把多个复合主键的值拼凑一起,然后检查是都重复,而不是单独检查 
    由于一个创建表的sql中primary KEY 只能出现一次,所以用复合主键的时候不能再列后面声明主键
2:唯一约束    UNIQUE   
3:非空约束    NOT NULL 是列级约束,所以必须声明在列后面,而其他约束都是表级约束
4:外键约束   
	外键约束一般定义在具有父子关系的表中,外键约束是的子表中的列,对应于父表的主键,用于约束,子表的添加:子表添加的时候父表必须有对应的主键值 
    父表的删除: 父表在删除的时候,如果对应的子表有数据无法删除,可以在外键中设置级联删除  使用  ON DELETE CASCADE 关键字 在删除父表数据的时候会删除对应子表数据;如果删除父表数据   想保留对应子表数据,可以在外键中设置 ON DELETE SET NULL,当删除父表记录时,对应子表外键会变成null;
5:检查约束(重点)  
	CHECK 检查约束,可以用来约束具体的值,可以用于字符串及数字,并可以使用in或between AND 等进行约束,如果表加了约束之后,进行插入数据的时候会变得缓慢,所以在大量数据插入的时候,我们一般选择在插入数据之后才声明约束

约束状态:

很多时候由于业务需要,会进行大量的老数据与新数据的合并,当前表存在约束,造成无法合并
数据库两类约束状态:
	1:启用 ENABLE    2:禁用   DISABLE
   	1:验证  VALIDATE    2: 非验证   NOVALIDATE   
数据库 两类四种状态组合的情况:
	1:默认 ENABLE VALIDATE  默认约束组合,会校验新增数据,并且表中不存在违反约束的数据
	2: ENABLE  NOVALIDATE  会校验新增数据, 但表中老数据可以存在违反约束的数据
    3: DISABLE VALIDATE     对新增数据不做验证, 但是对表中已有数据进行校验,是一种自相矛盾的组合,会导致DML失败
    4: DISABLE NOVALIDATE   对新增数据不做校验,对表中原有数据也不做校验
    改变数据库的约束状态可以使用如下sql:
              ALTER TABLE  表名  MODIFY  CONSTRAINT  约束名  DISABLE  NOVALIDATE

SELECT 中的 WHERE , GROUP BY , HAVING , ORDER BY
1: 四个字句顺序不能颠倒
2: WHERE 针对的是表和列发挥作用,查出加过
3: 如果对中间结果再次筛选,需要用 HAVING
伪列

ROWNUM 伪列  限制查询结果集的数量,可以使用伪列进行行的排列,其生成在order BY 之前 ;伪列的生成是从1开始开始,如果取值大于1 则会取到空的
结果集,可以使用子查询, 先生成全部伪列,然后外层进行取值来进行分页操作  (page-1)*5 每页展示5条
RANk() OVER ( partition BY ....... ORDER BY  ......   )             dense_RANk() OVER ( partition BY ....... ORDER BY  ......   )       row_number() OVER ( partition BY ....... ORDER BY  ......   )      
partition BY   用于给结果集进行分组,如果没有进行制定则整个结果集作为一个分组
ORDER BY    ORDER BY  本身是堆整张表进行排序,单如果用在这里是针对分组进行排名
 三种分组用法相同,不同之处是: row_number() 显示的是行数,而非名次
 SELECT  ROWNUM B,ENAME,SAL FROM(SELECT ROWNUM A,ENAME,SAL FROM SCOTT.EMP)WHERE A>3 and ROWNUM <6;  分页

关键字

去重: DISTINCT
UNION 用于表拼接,会去掉两表中完全一样的数据,如果需要则使用 UNION ALL
Oracle dual虚拟表临时表计算器
一般的数据库查询 SELECT
1:列上的值是用来当做变量的值来运算
2:where 后面是表达式,如果表达式为真,则取出对应行
恒为真条件,sql在内部优化的时候,会直接过滤掉恒为真的条件,并不会影响sql效率
%代表匹配N位字符,而_代表一个字符
CONSTRAINT FK_EMP_1_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT_1(DEPTNO) on DELETE CASCADE
定义外键外键名本表中的引用 XX表XX键

通用函数

oracle 常用函数
1: 单行函数:每一个函数值输入一行结果,返回一个结果
        1)字符函数     
        	I.返回对应ASCII码表中的位置 II. 拼接 CONCAT('HELLO',' ORACLE')   或  'HELLO'||' ORACLE' III.返回字符串的长度  
        	length('hel li')  
        	IV.INSTr('hello oracle','or')返回or 在字符串中出现的位置,索引从1开始; V.LOWER ('HELLO') 返回小写 VI. UPPER ('hello')  
        	返回大写
        	VII.LTRIM('QWHELLO','QW')截取以QW开头的字符VIII.RTRIM ('HELLOZW','ZW')截取字符串结尾的ZWIX.TRIM('-' FROM '-
        	HELLO-') 
        	截取字符串前后的字段VII,VIII和IX 三个函数比较类似,如果不填入第二个参数的话,默认参数为空格 , 所以为截取空格     X. 
        	REPLACE ('ABCGG','G','DEF') 
        	字符串替换,把第一个字符的G换成DEF 注意:替换所以的G    XI. SUBSTR ('abcde',2,3) 从字符串的第2位开始取出3位  
        2)数字函数
        	I.ABS(X)  绝对值  II. ACOS(x)  反余弦   III. COS(X) 余弦 IV. CEIL(X) 大于或等于X的最小值  V.FLOOR(X) 小于或等于X的最
        	大值
        	VI. LOG(X,Y) X为底Y为对数 VII. MOD(X,Y)类似于取余 X/Y VIII.POWER(X,Y) X的Y的次幂   IX.ROUND(X,[Y]) 把数字X在Y
        	的位置上四舍五入  X. SQRT(X) X的平方根  XI.TRUNC(X,[Y]) 数字X在Y的位置上截断(舍去)
        3)转换函数      
        	I. TO_CHAR(SYSDATE , 'YYYY"年"MM"月"DD"日" HH24:MI:SS' )   TO_CHAR(SYSDATE , 'YYYY-MM-DD HH24:MI:SS' ) 
				返回: 2018-10-16 17:48:54
            II. TO_DATE('1990-12-12  15:24:20',''YYYY-MM-DD HH24:MI:SS' )
        4)日期函数     
            I. ADD_MONTHS(SYSDATE,X)  在某个时间点增加X个月  SYSDATE :当前时间  II.  ROUND(SYSDATE,'day' )  可以对年 月  
            日的四舍五入 
            III.LAST_DAY(SYSDATE) 返回当前月份的最后一天 IV. EXTRACT(YEAR from SYSDATE)     提取时间戳的年 月 日 V. 
            MONTHs_between(Y,X)返回两个时间点X,Y相差的月份(Y-X)  VI. NEXT_DAY(SYSDATE,1) 返回下个星期的星期几,注意国	
            外与国内相差1天 (1 对应周日)
        5)其他单行函数  
       		I. NVL(COMM,100) 空替换 把COMM列的NULL替换为100;                    
        	II.NVL2(COMM,COMM*2,200)   升级版, 如果comm有值则值*2,如果没值则变成200;                     
2: 聚合函数 :  可以对多行进行操作.返回一个结果集  例如 SUM()
                I. SUM();  II.AVG();  III. MIN();     IV.  MAX();  V. COUNT()

例

--  1)字符函数  
SELECT ASCII('A') FROM Dual;                                    
SELECT CONCAT('HELLO',' ORACLE') FROM DUal;
SELECT 'HELLO'||' ORACLE'  FROM DUal;
SELECT length('hel li') from dual;
SELECT INSTr('hello oracle','or') FROM dual;
SELECT LOWER ('HELLO') FROM DUAL;
SELECT UPPER ('hello') FROM DUAL;
SELECT LTRIM ('QWHELLO','QW') FROM DUAL;  
SELECT RTRIM ('HELLOQW','QW') FROM DUAL;  
SELECT TRIM('-' FROM '-HELLO-') from DUAL;
SELECT RTRIM ('            HELLOQW            ') FROM DUAL;  
SELECT REPLACE ('ABCGG','G','DEF') FROM DUAL;  
SELECT SUBSTR ('abcde',2,3) FROM DUAL;  
-- 2)数字函数
SELECT ABS(-1) FROM DUAL;   SELECT ACOS(1) FROM DUAL; SELECT COS(0) FROM DUAL; SELECT CEIL(-1) FROM DUAL; SELECT MOD(-9,2) FROM DUAL; 
SELECT ROUND(3.525,2) FROM DUAL; 
SELECT TRUNC(3.525) FROM DUAL;  SELECT TRUNC(3.525,2) FROM DUAL; 
--  4)日期函数 
-- SYSDATE 当前时间 
SELECT SYSDATE , ADD_MONTHS(SYSDATE,5) FROM DUAL;  
SELECT ROUND(SYSDATE,'day' ) FROM DUAL;          -- 相距最近的星期日
SELECT ROUND(SYSDATE,'YEAR' ) FROM DUAL;     -- 对上半年进行舍去,下半年进一
SELECT ROUND(SYSDATE,'MONTH' ) FROM DUAL;       -- 类似四舍五入 
SELECT ROUND(SYSDATE ) FROM DUAL;                      -- 时间上的四舍五入
SELECT LAST_DAY(SYSDATE) from DUAL;
SELECT EXTRACT(YEAR from SYSDATE) from DUAL;
SELECT EXTRACT(MONTH from SYSDATE) from DUAL;
SELECT EXTRACT(DAY from SYSDATE) from DUAL;
SELECT MONTHs_between(ADD_MONTHS(SYSdate,5),sysdate )from DUAL;
SELECT NEXT_DAY(SYSDATE,2) FROM DUAL;
--  3)转换函数
SELECT TO_CHAR(SYSDATE , 'YYYY"年"MM"月"DD"日" HH24:MI:SS' ) from DUAL; 
SELECT TO_CHAR(SYSDATE , 'YYYY-MM-DD HH24:MI:SS' ) from DUAL; 
SELECT TO_DATE ('1990-12-12  15:24:20','YYYY-MM-DD HH24:MI:SS' ) from DUAL; 
-- 空替换
SELECT EMPNO,ENAME NVL(COMM,100) FROM SCOTT.EMP;

主键

-- ORACLE 中主键没有自增长,但是有SEQUENCE 序列可以用来填充主键
-- SEQUENCE 的属性有: increment  BY :  步长 指定该序列每次增加的值   START with: 从几开始   MAXVALUE : 最大值   	
MINVALUE : 最小值   CYCLE/nocycle : 是否循环  CACHE/nocache  缓存个数 SEQUENCE 除了上面的属性外,还有2个伪列:  
NEXTVAL:  下一个值    CURRVAL:  当前值 
-- 同义词 SYNONYM  FOR  可以为数据库对象创建同义词用于简化查询    简单理解 就是别名的意思
-- 视图 VIEW 我们可以把一个查询结果集封装成一个视图,方便查询; 视图本身不保存任何数据,只是储存一条sql  (动态的也可以查) 		
单表查询叫简单视图,多表查询叫复杂视图,复杂视图不能进行DML操作
-- 事务: COMMIT ROLLBACK ACID 隔离级别