--SQL结构查询语言 1 数据库定义语言（DDL）用于建立、删除和修改数据库对象 CREATE ALTER DROP TRUNCATE 2 数据库操纵语言（DML）用于改变数据库表中的数据 INSERT UPDATE DELETE 3 事务控制语言（TCL）用来维护数据一致性的语言 COMMIT ROLLBACK SAVEPOINT 4 数据查询语言（DQL）用来查询索要查询的数据 SELECT 5 数据库控制语言（DCL）Data Control Language 用于执行权限的授予和收回操作 GRANT 授予，用于给用户和角色授予权限 REVOKE 用于收回用户或角色已有的权限 CREATE USER 创建用户 -------------------------------------------------------------------------- --创建表 CREATE TABLE TABLE_NAME( id NUMBER(4) NOT NULL, name VARCHAR2(40), gender VARCHAR2(1) DEFAULT 'm' ); --查看表结构 DESC table_name; --修改表名称 RENAME old_name TO new_name; --增加列 ALTER TABLE TABLE_NAME ADD( hiredate DATE DEFAULT sysdate ); --删除列 ALTER TABLE TABLE_NAME DROP (ID); --修改列 ALTER TABLE TABLE_NAME MODIFY( ); --DML --插入记录 INSERT INTO TABLE_NAME (column1,column2 ) VALUES(value1,value2 ); --更新表中的记录 UPDATE TABLE_NAME SET column1 = value1,column2 = value2 WHERE condition; --删除记录 DELETE FROM TABLE_NAME WHERE condition; TRUNCATE TABLE TABLE_NAME; --删除全表记录，变成空表；针对数据删除，立即生效，无法回退， --字符串操作 CHAR 浪费空间，节省时间 VARCHAR2 浪费时间，节省空间 LONG varchar2加长版，存储变长字符串，最多可达2GB；每个表只能有一个；不能作为主键；不能建立索引；不能出现在查询条件中... CLOB 存储定长或变长字符串，最多可达4GB 一般建议用 CLOB 替代 LONG 类型; --连接字符串 CONCAT() || SELEcT CONCAT('Hello','World!') FROM dual; SELECT 'Hello' || 'World' from dual; --LENGTH(CHAR) SELECT NAME,LEGTH(NAME) FROM TABLE_NAME; UPPER 将字符串转换成大写 LOWER 将字符串转换成小写 INITCAP 将字符串转换成每个单词首字母大写，其余小写 select LOWER('HELLO WORLD') FROM DUAL; --截去子串 TRIM(c2 from c1) 从c1的前后截去c2 LTRIM(C1,C2) 从c1的左边截去c2 RTRIM(c1,c2)从c1的右边截去c2 如果没有c2就截去空壳 LPAD(CHAR1,N CHAR2) 左补位函数 RPAD(CHAR1,N,CHAR2) 右补位函数 SUBSTR(CHAR,START,END) 截取从start到end的字符串；start为负数则从尾部开始；如果没end则到尾部 INSTR(CHAR1,CHAR2[n[,m]]) 返回子串char2在char1中的位置；从n位开始搜索，没指定则从1开始；m指定子串m次出现，不指定则取值1 NUMBER(P,S) ROUND(n[,m]) 用于四舍五入，m指定小数后第m位 TRUNC(n[,m]) 用于截取 MOD(m,n) 返回m除以n的余数 CEIL(n) 向上取整 FLOOR(n)向下取整 --日期类型 DATE TIMESTAMP SELECT sysdate from dual; SELECT to_char(systimestamp,'YYYY-MM-dd HH24:mm:ss.ff DY DAY') from dual ; --返回指日期的当月最后一天 SELECT LAST_DAY(sysDATE) FROM dual; --指定日期加上i个月后的日期 SELECT ADD_MONTHS(sysdate,20*12) from dual; --计算两个日期间之间有多少个月MONTHS_BETWEEN SELECT MONTHS_BETWEEN('2019-12-25',sysdate) from dual; --NEXT_DAY 返回日期数据的下一个周几,周日为1 SELECT NEXT_DAY(sysdate , 1) from dual; --LEAST、GREATEST 返回最小或最大值,比较的必须是同一类型 SELECT LEAST(34,555,7999,2,3344) from dual; SELECT GREATEST(34,555,7999,2,3344) from dual; --EXTRACT 从参数中提取date指定的年月日等 SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; --数据库中任何数据类型都可以取null值 --null查询用 IS NULL --NOT NULL非空约束 desc employees select * from employees; --NVL() 若第一参数为null，则取值第二参数 SELECT employee_id,first_name,salary,salary + NVL(commission_pct,10) as "sal" from employees; --NVL2() 若第一参数为null，则取值第三参数；否则第一参数非null取值第二参数 SELECT employee_id,first_name,salary,salary + NVL2(commission_pct,10,14) as "sal2" from employees; --此处省略见 Oracle Day03(SQL) --子查询 子查询就是一个SQL语句（通常是SQL）中嵌套一个查询语句，嵌套的这个语句就是子查询语句 梓槎村也可以用于DML和DDL语句 --分页查询 ROWNUM --分支函数 --DECODE， 最后一个为default当前面都不满足则执行default； SELECT NAME ,SAL( DECODE(job, 'MANAGER',SAL*1.2, 'ANALYST',SAL*1.1, 'SALESMAN',SAL*1.5, SAL) ) FROM TABLE_NAME --CASE SELECT NAME ,SAL( CASE job WHEN 'MANAGER' THEN SAL*1.2 WHEN 'ANALYST' THEN SAL*1.1 WHEN 'SALESMAN' THEN SAL*1.5 ELSE SAL END bonus )FROM TABLE_NAME; --排序函数 ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2 ) 排名连续且唯一，根据col1分组，在分组里根据col2排序 RANK() OVER ( PARTITION BY col1 ORDER BY col2) 排名可以相同有重复值，会产生不连续 DENSE_RANK() OVER(PARTITION BY col1 ORDER BY col2) 相同数据返回相同排名，且排名连续 --集合操作 UNION 并集，每个元素只有一个 UNION ALL 并集，有重复元素 INTERSECT 交集，获取两个结果集的交集 MINUS 差集 --高级分组函数 -- ROLLUP , CUBE , GROUPING SETS 运算符是 GROUP BY 子句的扩展， GROUP BY ROLLUP(A,B,C) --按照阶梯状进行分组 n 个参数 ，则会有 n+1 次分组 GROUP BY CUBE (A,B,C) --按照n个参数，则会有 2^n 次分组 GROUP BY GROUPING SETS((A),(B)) --可以指定分组 --视图 分为：简单视图 和 复杂视图 ；select基于多个表叫做 连接视图 目的：1简化复杂查询，查询复用；2 限制数据访问 -- 简单视图：基于单表建立的，不包含任何函数运算和分组、表达式或分组函数，即简单取出基表记录不做任何改变 -- 对简单视图做的 insert、update、 delete 等操作就是就基表的操作 WITH CHECK OPTION 给视图添加约束，只能做视图可见的 insert、update、 delete 等操作 WITH READ ONLY 保证视图对应的基表数据不会被非法修改 -- 复杂函数：基于单表，但包含单行函数、表达式、分组函数或GROUP BY子句等 视图也叫做虚拟表，是一组数据的逻辑表示； 视图对应一条 SELECT 语句，结果集被赋予一个名字 ，即叫做视图； 视图本身不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也会随之改变； CREATE VIEW v_TableName_ViewName AS SELECT SAL FROM TABLE_NAME; DROP VIEW VIEW_NAME; 视图的可以向表一样查询结构等； --和视图相关的数据字典 -- USER_OBJECTS 查询所有视图名称 SELECT object_name FROM USER_OBJECTS WHERE object_type = 'VIEW'; -- USER_VIEWS 查询指定视图 SELECT text FROM USER_VIEWS WHERE view_name = 'v_EMP_10'; -- USER_UPDATE_COLUMNS SELECT column_name,insertable,updatable,deletable FROM user_updatable_columns WHERE table_name = 'V_emp_10'; --序列 SEQUENCE 是一种用来生成唯一数字值的数据库对象 CREATE SEQUENCE sequence_name [START WITH i] [INCREMENT BY j] [MAXVALUE m] [NOMAXVALUE] [MINVALUE n] [NOMINVALUE] [CYCLE | NOCYCLE ] [ CACHE p | NOCACHE] sequence_name 是序列名，序列的第一个值是 i ，步进 j ；如果j是正数，表示递增，如果是负数，表示递减； 序列可生成的最大值是m，最小值是n； 如果没有任何可选参数，序列的第一个值是1，步进是1； CYCLE 表示在递增至最大值或递减至最小值之后是否继续生成序列号，默认值是NOCYCLE； CACHE 用来指定先预定取P个数据在缓存中，以提高序列值得生成效率，默认值是20； CREATE SEQUENCE sequence_name start with 100 increment by 10 NEXTVAL 获取序列的下个值，不能回退 CURRVAL 获取序列的当前值，必须限制性一个nextval才能用currval SELECT sequence_name.nextval FROM DUAL; SELECT sequence_name.currval FROM DUAL; --s删除序列 DROP SEQUENCE sequence_name --索引 索引是一种允许直接访问数据表中某一数据行的树形结构，为了提高查询效率而引入，是独立于表的对象， 可以存放在表不同的表空间（TABLESPACE）中； 索引记录中存有 索引关键字 和 指向表中数据的指针； 小数据量不需要索引； 限制表上索引的数目，索引并不是越多越好 删除很少使用、不合理的索引； 不要在经常做DML操作的表上建立索引； 索引是数据库自行维护和使用， --创建索引 CREATE INDEX index_name ON table(column1[,column2...]) --删除索引 DROP INDEX index_name; --修改索引 ALTER INDEX index_name REBUILD; --约束：全称是约束条件，也称作完整性约束条件； 约束是在数据表上强制执行的一些数据消炎规则，当执行DML操作时必须遵循这些规则，若果不符合无法执行； 约束条件可以保证表中数据的完整性，保证数据间的商业逻辑； 非空约束 NOT NULL 简称 NN ，确保字段不为空值 --创建时添加约束 CREATE TABLE emp ( id number(6) NOT NULL, hiredate DATE CONSTRAINT emp_hirdate_nn NOT NULL ); --修改时添加约束 ALTER TABLE emp MODIFY (id NUMBER(6) NOT NULL); 唯一性约束 Unique 简称 UK，确保字段不出现重复值，null可以重复，同样可以创建和修改表是添加； ALTER TABLE emp ADD CONSTRAINT emp Unique(name); 主键约束 Primary Key 简称 PK, NOT NULL 加上 Unique ；用法同上 外键约束 Foreign Key 简称 FK 检查约束 Check 简称 CK，用来强制在字段上的每个值都要满足Check中定义的条件 ALTER TABLE emp ADD CONSTRAINT emp_salary_check CHECK(salary >2000);