SQL学习心得

SQL(Structured Query Language)--- 结构化查询语言SQL是在关系数据库中执行数据操作，检索，维护所使用的标准语言，可以用来查询数据，操作数据，定义数据，控制数据执行SQL语句时用户只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。

------数据库对象通常包含表，视图，索引，序列【数据定义语言DDL】-->表结构Data Definition Language，用于建立，修改，删除数据库对象，不需要事务的参与，自动提交。

——CREATE：创建表或其他对象的结构CREATE TABLE table_name(column_name datatype [DEFAULT expr],………………………………………………）；Eg：--创建表empCREATE TABLE emp(id NUMBER(10),name VARCHAR2(20),gender CHAR(1),birth DATE,salary NUMBER(6,2),job VARCHAR2(30),deptid NUMBER(2));——ALTER：修改表或其他对象的结构修改表名：RENAME old_name TO new_name （新表名不能使数据库中已有的表）增加列：ALTER TABLE table_name ADD(column_name datatype[DEFAULT],……………………………）；（新增列只能在表的最后一列追加）删除列：ALTER TABLE table_name DROP(column_name)；（删除不需要的列）删除字段需要从每行中删掉该字段占据的长度和数据，并释放在数据块中占据的空间，如果表记录比较大，删除字段可能需要比较长的时间。

修改列：ALTER TABLE table_name MODIFY(column_name datatype[DEFAULT],……………………………）；（修改仅对以后插入的数据有效，修改字段前的所有数据不受影响）修改时一般类型不改，改长度，尽量往长里改，因为如果表中已经有数据的情况下，把长度由大改小，有可能不成功——DROP:删除表或其他对象的结构DROP TABLE table_name——TRUNCATE：删除表数据，保留表结构TRUNCATE TABLE table_name可以通过DESC table_name 查看表结构【数据操作语言DML】-->表结构中的数据 Data Manipulation Language，用于改变数据表中的数据，和事务是相关，执行完DML操作后必须经过事务控制语句提交后才真正的将改变应用到数据库中——INSRET：将数据插入到数据表中INSERT INTO table_name[(column[, column…])]VALUES(value[, value…]); （每执行一次增加一条记录）指定向哪些列插入对应的值，没有指定的列：若设有默认值（DEFAULT），那么插入的就是该默认值，否则插入null,若某列设为not null，执行INSERT语句时又没指定该列，那么插入会抛出违反不为空的约束条件，若不写指定的列，默认所有列插入，每一列的值VALUE都不能少——UPDATE:更新数据表中已存在的数据UPDATE table_nameSET column = value[,column = value]……[WHERE condition]; --若不写where子句，全表所有行的column都被更新——DElETE：删除表中的数据DELETE [FROM] table_name[WHERE condition]; --若不写where子句，全表所有行数据都被删除另注意与DDL中的TRUNCATE的区别：* 二者都是删除表记录，DELETE可以有条件的删（WHERE），TRUNCATE是将表数据全部删除 * DELETE是DML，可以回退（ROLLBACK），TRUNCATE是DDL，立即生效，无法回退* 如果删除的是全部表记录，且数据量较大，TRUNCATE速度更快【事务控制语言TCL】Transaction Control Language，用来维护数据的一致性——COMMIT：提交，确认已经进行的数据改变——ROLLBACK：回滚，取消已经进行的数据改变——SAVEPOINT：保存点，使当前事务可以回退到指定的保存点，便于取消部分改变Eg：DDL 范畴，控制表结构不需要TCL参与，自动提交DML 范畴，控制表结构中的数据经TCL确认后，才会真正生效，否则是“假象”【数据控制语言DCL】Data Control Language，用于执行权限的授予和收回操作——GRANT：授予，用于给用户或角色授予权限——REVOKR：用于回收用户或角色已有的权限——CREATE USER：创建用户【数据查询语言DQL】Data Query Language,用来查询需要的语句★补充知识点★数据类型：定义表中每一列可以使用的数据格式和范围，用来保证数据类型的格式和有效性➢字符串操作：Oracle中的字符串是用单引号（‘’）括起来的，注意与Java的区别1.字符串类型：CAHR，VARCHAR2 表示字符串数据类型，用来在表中存放字符串信息几点说明：——在数据库中CHAR，V ARCHAR表示的是字符串，注意与java的区别——CHAR(N),V ARCHAR2(N) 指定的是字节数，不是字符数——V ARCHAR2是Oracle独有的数据类型，和其他数据库中的VARCHAR作用一样，加2 是Oracle为以后新增一个字符类型，而又不改变原有V ARCHAR定义的长远考虑LONG：VARCHAR2 的加长版，也是存储变长字符串，最多可达2GB的字符串数据，LONG有诸多限制：每张表只能有一个LONG类型列；不能作为主键；不能建立索引；不能出现在查询条件中……CLOB：LONG的改进版，存储定长或变长字符串，最多可达4GB的字符串数据，Oracle建议用CLOB替代LONGEg：UTF-8环境下：varchar2英文占1个字节，中文占3个字节，nvarchar2英文占2个字节，中文占2个字节GBK环境下： varchar2英文占1个字节，中文占2个字节，nvarchar2英文占1个字节中文占3个字节2.字符串函数：【补充】虚表dual的概念：在数据库中，我们想要测试某个表达式的结果只能使用SELECT语句来实现DUAL，虚表，没有这么一个表，只是为了满足SELECT的语法要求我们常用虚表来测试表达式的结果当SELECT后没有一张表的字段参与时，FROM后就用虚表a:CONCAT(char 1,char 2):返回char1和 char2连接后的结果相当于Java中的“+”等价操作：连接操作符“||”CONCAT的参数只能有两个，若多个字符串CHAR连接，需嵌套使用，如果char1 和 char2 任何一个为NULL，相当于连接了一个空格SELECT CONCAT('I',CONCAT ('LIKE','Oracle'))FROM dualSELECT 'I'||'LIKE'||'Oracle'FROM dualb:LENGTH(char)：获取字符串的长度（字符个数）若字符类型是VARCHAR2，返回字符的实际长度若字符类型是CHAR，长度还有包括补充的空格SELECT ename,LENGTH(ename)FROM emp_yysc:UPPER（char)：字符串转大写SELECT upper('asfadg') FROM duald:LOWER(char)：字符串转小写SELECT lower('asAAAS') FROM duale:INITCAP(char)：将字符串中每个单词的首字母转大写，其他字符小写，单词之间用空格或非字母分隔SELECT initcap('as%NGSY isaT') FROM dualf:TRIM(c2 FROM c1)：去掉字符串c1两侧的t2g:LTRIM(c1,[c2])：从c1的左侧截去c2h:RTRIM(c1,[c2])：从c1的右侧截去c2注：TRIM，LTRIM，RTRIM 中，如果没有c2，就截去空格TRIM的c2只能是单一字符，LTRIM，RTRIM中的c2可以是多字符，且多字符不管顺序，只要出现就截若c1中不包含c2，则无法截取，原样输出c1在LTRIM，RTRIM 中，若c2不是c1的最左/右端，则无法截取，原样输出c1SELECT TRIM('1' FROM '1sd1') FROM dualSELECT LTRIM('eaaeeeehaha','ae')FROM dualSELECT RTRIM('aaahaha','o') FROM duali:LPAD(原字符串 , 总长度 , 填充字符串)：左补齐j:RPAD(原字符串 , 总长度 , 填充字符串)：右补齐要求显示N个字符，若char1的值不足长度，则在L/R补充若干个char2，以达到N个字符，不够则补，刚好则原样，超了则择取到N （都是从左—>右读取（截取）字符，像计算器看到从右—>左的效果，只是在左—>右读取时加空格显示的效果）SELECT LPAD('haha',10,'OK') FROM dualSELECT RPAD(' yys'，20,' very good') FROM dualk:SUBSTR(char,[ m[, n]])：将字符串char从第m开始，获取n个长度大小的子字符串，在oracle中字符串的下标从1开始说明：m：若果m = 0，则从首字符开始，m为负数，则从尾部开始（从后往前……，-3，-2，-1），倒数第m个，向后获取n 个字符n：如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止SELECT SUBSTR('abcde', 2, 8) FROM DUALSELECT SUBSTR('abcde', -3, 2) FROM DUALl:INSTR(char1 ,char2，[,n[,m]] :返回字符串char2在源字符串 char1中的位置n，从第几个字符开始找不写默认从第一个开始找 n为负数，从后开始计数，且往前找，注意与SUBSTR 的区别（SUBSTR是从后开始计数，往后截取）m，指定第m次出现不写默认第一次出现如果在char1中没有找到子串char2，返回0 n不能为负数，否则会报错其实找子字符串只是看字符串的第一个字符SELECT INSTR('abc abc abc','abc') FROM DUALSELECT INSTR('abc abc abc','abc',6) FROM DUALSELECT INSTR('abc abc abc','abc',1,3) FROM DUALSELECT INSTR('abc abc abc','abc',-3,2) FROM DUAL充分说明了，找子字符串只是看字符串的第一个字符，往前找前提是必须是abc ，abcd就不包含在源字符串中会返回0m:REPLACE(char, 'm' , '*')：将字符串char中的'm'，替换成'*'，注意：replace不会替换原始字符串，仅影响显示结果SELECT REPLACE('abc abc abc','b','*') FROM DUAL➢数值操作：1.数值类型：NUMBER（p，s）用来在表中存放数值类型的数据—> p表示数字的总位数，取值为1-38，不写（NUMBEE（*，s）），p默认为38—> s表示小数点后面的位数，不写[,s]只有p 表示纯整数整数部分开头有再多的0，只要除去开头的0不超过p-s就OK小数部分结尾有再多的0，只要除去结尾的0不超过s就OK2.数值函数：a:ROUND(要处理的数字m[,小数位数n])：对数字进行四舍五入 * n必须是整数，为正数，四舍五入到小数点后n位为0，四舍五入到整数位，n缺省，默认为0为负数，四舍五入到小数点前n位n 为0，保留到个位，看小数点后一位n 为-1，保留到十位，看个位n 为-2，保留到百位，看十位………………SELECT ROUND (45.678,2) FROM dualSELECT ROUND (45.678) FROM dualSELECT ROUND(43455.678, -3) FROM dualb:TRUNC(m[,n])：按位截取数字mm，n含义与ROUND的m，n一样，只不过，只舍不进位SELECT trunc(45.678, 2) FROM dualSELECT TRUNC(45.678,-1) FROM dualc:CEIL(n)：上取整——天花板，大于等于n的最小整数SELECT CEIL(3248.999) FROM DUALd:FLOOR(n)：下取整——地板，小于等于n的最大整数SELECT floor(34564.4343)FROM DUALe:MOD(m , n)：取m/n的余数，n若为0，直接返回mSELECT mod(9,0)FROM dual➢日期操作：Oracle中的字符串是用单引号（‘’）括起来的，注意与Java的区别1.日期类型：Date：年月日时分秒，占7字节——保存日期和时间，能表示的日期范围公元前4712年1月1日~公元9999年12月31日TIMESTAMP(timestamp)：年月日时分秒.小数秒最高精度可达纳秒（ns）占7或者11字节CREATE TABLE test_1（C1 DATE，C2 TIMESTAMP ）；2.日期关键字：SYSDATE ，其本质就是一个Oracle内部的函数，返回当前系统时间，精确到秒默认显示格式：DD—MON—RRCREATE TABLE test_2（registerDate DATE DEFAULT SYSDATE）；column type defuult exprSELECT SYSDATE FROM DUALSYSTIMESTAMP，返回当前系统时间，精确到毫秒SELECT systimestamp FROM DUAL3.日期转换函数：* TO_DATE(要转换的字符串[，转换格式[，指定的日期语言]]) 将字符串按照指定格式转换为日期类型‘ xxx-xx-xx’‘xxxx/xx/xx’‘ xxxx’’年’’xx”月”’装换格式是按习惯自行编写的，是字符串所以用单引号括起来，若中间非关键字或符号的其他字符时，在把这些字符双引号括起来Eg ‘yyyy“年”mm“月”dd“日”’在日期格式字符串中大小写不区分，其他字符串中大小写区分转化格式的代词SELECT TO_DATE('2014年09月14','YYYY"年"MM"月"DD') FROM dual说明：1.实际上已经按照YYYY"年"MM"月"DD的日期格式，只不过Oracle的控制台输出格式为DD—MON—RR2.需要转换的字符串里出现的非符号字符时，不需要再加“”，只有转换格式字符串中出现的非关键字或符号的其他字符时，才把这些字符用双引号括起来* TO_CHAR(data[，转换格式[，指定的日期语言]])将日期类型数据data按照装换格式输出字符串SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') FROM dual4.日期常用函数：a：LAST_DAY(data):返回给定日期date所在月的最后一天SELECT LAST_DAY(to_date('2014年9月14日','YYYY"年"MM"月"DD"日"')) FROM DUALb：NEXT_DAY(date,char)给定日期离给定日期最近的下一个星期几（char决定）中文环境下，char可写成’星期三‘英文环境下，char可写成‘WEDNESDAY’为避免麻烦，可直接使用数字1-7 表示周日----周六SELECT NEXT_DAY(SYSDATE,'星期三') FROM DUAlSELECT NEXT_DAY('4-9月-14',5)FROM DUAL说明：获取的是距当前时间最近的周四，若给定的时间刚好是周四，及周四以后则获取的是下周周四，周四之前，则获取本周周四注意是按外国的周算一周时间为：日一二三四五六c：ADD_MONYHS(date,i):返回给定日期加上i个月后的日期值i可以是任何数字，大部分时候取正值整数i为小数，会被截取整数后再参与运算i为负数，即减去i个月后的日期值SElECT ADD_MONTHS('14-9月-14'，2.6) FROM DUAldate默认格式为DD—MON—RR 所以写‘14-9月-14’d：MONTHS_BETWEEN(date1,data2)：获取date1和date2之间隔了多少个月是date1 - date2，若果date2比date1的时间晚，会得到负数除非两个日期之间隔整数月，否则结果会带小数SELECT months_between('1-1月-14','3-3月-14') FROM DUAl e：LEAST（expr1[，expr2[，expr3]]……）GREATEST（expr1[，expr2[，expr3]]……）比较函数，返回结果是参数列表中最大或最小的值参数类型必须一致，或可转（在比较之前，参数列表的第二个参数会被自动转换为第一个参数的数据类型，可以转，则继续比较，不可以转报错）SELECT LEAST(22,99.9)FROM DUALSELECT greatest(sysdate,'1-1月-14')FROM DUAlf：EXTRACT(date FROM datetime):从参数datetime中提取参数date 指定的数据，比如extract(year/month/day from 日期变量) SELECT extract(MINUTE FROM SYSTIMESTAMP) FROM DUAl➢空值操作：* 数据类型未知或暂时不存在* 数据库中字段无论是什么类型，默认值都是NULL* 若使用了DAFAULT关键字指定了默认值，则使用指定的* 在创建表的时候，可以为列添加非空约束，被约束的列在插入数据时必须给值，更新数据时，不能将该列的值设为空* DAFULT 和NOT NULL不约束同一字段* 判断是否为空，不能写= null 要写IS NULL插入值INSERT，更新（UDATE）时，可以写column = null * NULL和任何数字运算结果还是NULL空值函数：NVL（expr1，expr2）:若expr1 为空，则取expr2的值（不管expr2是否为空eg NVL（null，null）结果为null ）若expr1不为空，则还是expr1，expr2没用expr1和expr2可以是任何数据类型，但这两个参数的类型必须一致SELECT NVL(1,8) FROM DUAlSELECT NVL（null，'Good'） FROM DUALNVL2(expr1,expr2,expr3):若expr1为NULL，返回expr3若expr1不为NULL，返回expr2SELECT NVL2（null，'Good'，'Better'） FROM DUALSELECT NVL2（'YYS'，'Good'，'Better'） FROM DUAL【查询语句】SELECT [ALL|DISTINCT][<目标列表达式>[,…n]] ——4FROM <表名或视图名>[,<表名或视图名>,…] ——1WHERE <条件表达式> ——2GROUP BY <列名1>［HAVING <条件表达式>］］——3ORDER BY <列名2>[ASC|DESC],…］；——5说明：1.其中SELECT和FROM语句为必选子句，其他子句为任选子句；2.SELECT [ALL|DISTINCT][<目标列表达式>[,…n]]子句指明查询结果集的目标列。