SQL基础知识（笔记）

SQL:结构化查询语言

SQL：分为

    -数据定义语言（DDL）：

    1、CREATE:创建表或者其他对象的结构

    2、ALTER:修改表或者其他对象的结构

    3、DROP：删除表或者其他对象的结构

    4、TRUNCATE：删除表数据、保留表结构（清空数据）

    -数据操作语言（DML）

    1、INSERT ：将数据插入到数据表中，只能插入到最后一行

    2、UPDATE：更新数据表中已存在的数据

    3、DELETE：删除数据表中的数据

    -事务控制语言（DQL）

    1、Data Query Language

    2、用来查询所需要的数据

    3、SELECT语句

    -数据控制语言（DCL）

    1、DATA CONTROL LANGUAGE

    2、用于执行权限的授予和收回操作

    包括：

            GRANT：授予，用于给用户或者角色授予权限

            REVOKE：用于收回用户或者角色已有的权限

            CAEATE USER：创建用户

NUMBER(n,m)，数字类型，n表示数字的总位数，m表示小数点后面的位数

CHAR(N），表示固定长度的字符类型，N表示占用的字节数，最长2000个字节

VARCHAR2（N），表示可变长度的字符类型，N表示最多可占用的字节数

DATE，定义日期时间的数据，长度是7个字节，默认格式为DD-MON-RR

创建表

    CREATE TABLE tablename(

id  NUMBER(n,m),

........

)

查看表的结构:DESC语句，DESC  tablename

DEFAULT 语句，设置记录的默认值

CREAT TABLE tablename(

gender CHAR(1) DEFAULT 'M'

)

NOT NULL语句，约束语句，确保字段值非空

CREAT TABLE tablename(

id NUMBER(n,m) NOT NULL

)

修改表名  RENAME：

    RENAME tablename TO  newtablename

增加字段 （增加列）ALTER  ADD，只能插入到最后一列：

    ALTER TABLE tablename ADD(

name  VARCHAR2(20)

）

删除字段（删除列）ALTER DROP:

    ALTER TABLE tablename DROP (

name

)

修改字段（修改列）ALTER MODIFY，仅能修改表列中的数据类型、长度、和默认值:

    ALTER TABLE tablename MODIFY(

    name NUMBER(n,m) DEFAULT '2'      //随意做演示

)

SELECT语句
用于查询表中数据
SELECT 子句后面跟的是要查询的字段，可以包括表中的具体字段，函数或者表达式。
FROM 子 句用来指定数据来源的表
WHERE 子句用来添加过滤条件，这样做的结果是只将满足条件的记录查询出来


SELECT * FROM emp
SELECT empno,ename,job,sal From emp
SELECT ename,sal*12 FROM emp


字符串函数
CONCAT()函数，用来连接字符串
SELECT CONCAT(CONCAT(ename,','),sal) FROM emp
SELECT CONCAT(CONCAT(ename,','),empno) FROM emp


||
SELECT ename||','||sal FROM emp


LENGTH 函数，显示字符串的长度
SELECT ename,LENGTH(ename) FROM emp


UPPER,LOWER,INITCAP
将字符串转换为全大写，全小写，以及首字母大写
对于INITCAP而言，可以使用空格隔开多个单词，那么每个单词首字母都会大写


SELECT LOWER(ename) FROM emp
SELECT INITCAP(ename) FROM emp


伪表：dual
当查询的内容不和任何表中的数据有关系时，可以使用伪表，伪表只会查询出一条记录
SELECT UPPER('helloword') ,
        LOWER('HELLOWORD'),
        INITCAP('HELLO WORD')
FROM dual


TRIM,LTRIM,RTRIM
去除当前字符串中两边的指定重复字符，LTRIM仅去除左侧的，RTRIM则仅去除右侧的
SELECT TRIM('e' FROM 'eeeliteeee')
FROM dual
SELECT LTRIM('ereressrLITeee','er') FROM dual
SELECT RTRIM('EEELITEEEE','E') FROM dual


LPAD,RPAD补位函数
SELECT RPAD(sal,5,'$')
FROM emp


SUBSTR 截取字符串
数据库中的下标都是从1开始的
SELECT SUBSTR('think in java',10,4)
FROM dual
第三个参数不指定则是截取到末尾，指定的长度若超过实际可以截取的内容也是截取到末尾
SELECT SUBSTR('think in java',7)
FROM DUAL


SELECT SUBSTR('think in java',-4,4) FROM dual


INSTR(char1,char2[,n,m])函数
查找char2在char1中的位置
n为从第几个字符开始检索
m为第几次出现
n,m不写则默认都是1


SELECT INSTR('thinking in java','in',4,3) FROM dual


数字函数
ROUND(n,m)四舍五入


SELECT ROUND(45.478,2) FROM dual
SELECT ROUND(45.478,0) FROM dual
SELECT ROUND(45.478,-1) FROM dual


TRUNC(n,m)截取数字
SELECT TRUNC(45.678,2) FROM DUAL   --截取小数点后面剩下两位
SELECT TRUNC(45.678,0) FROM DUAL
SELECT TRUNC(45.678,-1) FROM DUAL   --截取整数位第一位，从个位数开始


MOD(m,n)求余数
SELECT MOD(1000,3) FROM DUAL
SELECT MOD(1000,0) FROM DUAL
SELECT ename,sal,MOD(sal,1000)
FROM EMP


CEIL,FLOOR
向上取整和向下取整


SELECT CEIL(45.678) FROM DUAL
SELECT FLOOR(45.678) FROM DUAL

SELECT ename,sal,deptno FROM emp WHERE ename=UPPER('scott')

1、SYSDATE 对应数据库一个内部函数，该函数返回一个表示当前系统时间的DATE类型值。

SYSTIMESTAMP返回的是一个表示当前系统时间的时间戳类型的值。

SELECT SYSDATE FROM DUAL

SELECT SYSTIMESTAMP FROM DUAL

2、TO_DATE函数，可以将字符串按照给定的日期格式解析为一格DATE类型的值

D              一周中的星期几     
DAY        天的名字，使用空格填充到9个字符     
DD          月中的第几天     
DDD       年中的第几天     
DY          天的简写名     
IW           ISO标准的年中的第几周     
IYYY      ISO标准的四位年份     
YYYY    四位年份     
YYY,YY,Y       年份的最后三位，两位，一位     
HH         小时，按12小时计     
HH24    小时，按24小时计     
MI         分     
SS         秒     
MM      月     
Mon     月份的简写     
Month  月份的全名     
W        该月的第几个星期
WW    年中的第几个星期

3、日期的计算

日期可以与一个数字进行加减法，这相当于加减指定的天

两个日期可以进行减法，差为相差的天

输入自己的生日，查看到今天为止活了多少天

SELECT TRUNC(SYSDATE-TO_DATE('1994-12-12','YYYY-MM-DD')) FROM DUAL

TO_CHAR():可以将DATE按照给定的格式转换为字符串
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL

用RR代表年和用YY代表年份的区别，如下图

SELECT  TO_CHAR(TO_DATE('98-12-09','YY-MM-DD'),'YYYY-MM-DD') FROM DUAL

SELECT  TO_CHAR(TO_DATE('98-12-09','RR-MM-DD'),'YYYY-MM-DD') FROM DUAL

LAST_DAY(DATE)   返回给定日期所在月的月底日期

SELECT LAST_DAY(SYSDATE) FROM DUAL

ADD_MONTHS(date,i)，对给定日期加上指定的月，若i为负数则是减去
查看每个员工入职20周年的纪念日

SELECT ename,ADD_MONTHS(hiredate,12*20) FROM emp

MONTHS_BETWEEN(date1,date2)，计算两个日期之间相差的月，是根据date1-date2得到的

SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2013-11-1','YYYY-MM-DD')) FROM DUAL

NEXT_DAY(date,i)，返回给定日期的第二天开始一周之内的指定周几的的日期
i:1表示周日，2：表示周一，以此类推

SELECT NEXT_DAY(SYSDATE,2) FROM DUAL

LEAST,GREATEST，求最小值与最大值，除了日期外，常用的数字也可以比较大小
SELECT LEAST(SYSDATE,TO_DATE('2019-12-12','YYYY-MM-DD'),TO_DATE('2017-12-12','YYYY-MM-DD')) FROM DUAL
SELECT GREATEST(SYSDATE,TO_DATE('2019-12-12','YYYY-MM-DD'),TO_DATE('2017-12-12','YYYY-MM-DD')) FROM DUAL

SELECT LEAST(1,2,3) FROM DUAL

EXTRACT()提取给定日期中指定时间分量的值
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL

SELECT ename,EXTRACT(YEAR FROM hiredate) FROM emp

NULL值的运算操作
NULL与任何数字运算结果还为NULL

NULL与任何字符串拼接等于什么都没干

NULL值的运算操作
NULL与任何数字运算结果还为NULL

NULL与任何字符串拼接等于什么都没干

空值函数
NVL(arg1,arg2)，当arg1为NULL，函数返回arg2的值，若不为NULL，则返回arg1本身，
所以该函数的作用是将NULL值替换为一格非NULL值。

SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp

NVL2(arg1,arg2,arg3)
当arg1不为NULL，则函数返回arg2，当arg1为NULL，则函数返回arg3,

该函数是根据一格值是否为NULL来返回两个不同结果。

SELECT ename,comm,NVL2(comm,'有绩效','没有绩效') FROM emp