一、DDL语言使用
常见约束
1.主键约束 PRIMARY KEY
2.唯一约束 UNIQUE
3.非空约束 NOT NULL
4.枚举约束 CHECK
创建表
create table 模式名.表名 (
字段名 数据类型 列约束类型 [default 赋值],
);
字段注释
COMMENT ON COLUMN “模式名".“表名” IS ‘字段说明’;
表注释
COMMENT ON TABLE “模式名".“表名” IS ‘字段说明’;
CREATE TABLE TEST.T_TEST1(
S_ID VARCHAR(32) PRIMARY KEY,
S_NAME VARCHAR(64) UNIQUE,
S_AGE INT NOT NULL,
S_GENDER CHAR(3) CHECK(S_GENDER IN ('男', '女'))
);
创建普通视图
CREATE OR REPLACE VIEW 模式名.视图名 AS SELECT * FROM TEST.T_TEST1;
CREATE OR REPLACE TEST.V_TEST1 AS SELECT * FROM TEST.T_TEST1;
删除表
DROP TABLE 模式名.表名;
DROP TABLE TEST.T_TEST1;
删除视图
DROP VIEW 模式名.视图名;
DROP VIEW TEST.V_TEST1;
添加表字段
ALTER TABLE 模式名.表名 ADD(字段名 数据类型 约束);
ALTER TABLE TEST.T_TEST1 ADD(HOBBY VARCHAR(128) NOT NULL);
删除表字段
ALTER TABLE 模式名.表名 DROP COLUMN 列名;
ALTER TABLE TEST.T_TEST1 DROP COLUMN HOBBY;
修改字段名
ALTER TABLE 模式名.表名 RENAME COLUMN 列名 TO 新列名;
ALTER TABLE TEST.T_TEST1 RENAME COLUMN S_AGE TO S_AGE2;
修改字段数据类型,约束类型
ALTER TABLE 表名 MODIFY(列名 数据类型 约束类型);
ALTER TABLE TEST.T_TEST1 MODIFY(S_AGE2 VARCHAR(3) NOT NULL);
清空表数据
TRUNCATE TABLE 模式名.视图名;
TRUNCATE TABLE TEST.T_TEST1;
第二种方式
DELETE FROM 模式名.表名;
DELETE FROM TEST.T_TEST1;
DML语言使用
修改表数据
UPDATE 模式名.表名 SET 列名=新值 WHERE 列名=表数据;
UPDATE TEST.T_TEST1 SET S_AGE2 = 23 WHERE S_ID = '1002';
插入单条数据
INSERT INTO 模式名.表名(列名1,列名2,列名3…) VALUES(值1,值2,值3…);
INSERT INTO TEST.T_TEST1(S_ID,S_NAME,S_AGE2,S_GENDER) VALUES('1004','小萱',20,'女');
插入多条数据
INSERT INTO 模式名.表名(列名1,列名2,列名3…) VALUES(值1,值2,值3…),( 值1,值2,值3…);
INSERT INTO TEST.T_TEST2(S_ID,S_NAME,S_AGE2,S_GENDER) VALUES('1005','小宏',20,'男'),('1006','小贺',21,'男');
删除数据
DELETE FROM 模式名.表名 WHERE 过滤条件;
DELETE FROM TEST.T_TEST1 WHERE S_AGE2 > 20;
查询数据
1.select 列名1,列名2,列名3… from 模式名.表名;
2.select * from 模式名.表名;
SELECT U_ID,U_NAME,U_AGE,U_GENDER,U_PHONE_NUM,U_CITY FROM TEST.T_USER_INFO;
SELECT * FROM TEST.T_USER_INFO;
函数记录
函数名 | 功能简要说明 |
---|---|
ABS(n) | 求数值 n 的绝对值 |
ACOS(n) | 求数值 n 的反余弦值 |
ASIN(n) | 求数值 n 的反正弦值 |
ATAN(n) | 求数值 n 的反正切值 |
ATAN2(n1,n2) | 求数值 n1/n2 的反正切值 |
CEIL(n) | 求大于或等于数值 n 的最小整数 |
CEILING(n) | 求大于或等于数值 n 的最小整数,等价于 CEIL(n) |
COS(n) | 求数值 n 的余弦值 |
COSH(n) | 求数值 n 的双曲余弦值 |
COT(n) | 求数值 n 的余切值 |
DEGREES(n) | 求弧度 n 对应的角度值 |
EXP(n) | 求数值 n 的自然指数 |
FLOOR(n) | 求小于或等于数值 n 的最大整数 |
GREATEST(n1,n2,n3) | 求 n1、n2 和 n3 三个数中最大的一个 |
GREAT (n1,n2) | 求 n1、n2 两个数中最大的一个 |
LEAST(n1,n2,n3) | 求 n1、n2 和 n3 三个数中最小的一个 |
LN(n) | 求数值 n 的自然对数 |
LOG(n1[,n2]) | 求数值 n2 以 n1 为底数的对数 |
LOG10(n) | 求数值 n 以 10 为底的对数 |
MOD(m,n) | 求数值 m 被数值 n 除的余数 |
PI() | 得到常数 π |
POWER(n1,n2) | 求数值 n2 以 n1 为基数的指数 |
RADIANS(n) | 求角度 n 对应的弧度值 |
RAND([n]) | 求一个 0 到 1 之间的随机浮点数 |
ROUND(n[,m]) | 求四舍五入值函数 |
SIGN(n) | 判断数值的数学符号 |
SIN(n) | 求数值 n 的正弦值 |
SINH(n) | 求数值 n 的双曲正弦值 |
SQRT(n) | 求数值 n 的平方根 |
TAN(n) | 求数值 n 的正切值 |
TANH(n) | 求数值 n 的双曲正切值 |
TO_NUMBER (char [,fmt]) | 将 CHAR、VARCHAR、VARCHAR2 等类型的字符串转换为 DECIMAL 类型的数值 |
TRUNC(n[,m]) | 截取数值函数 |
TRUNCATE(n[,m]) | 截取数值函数,等价于 TRUNC(n[,m]) |
TO_CHAR(n [, fmt [, ‘nls’ ] ]) | 将数值类型的数据转换为 VARCHAR 类型输出 |
BITAND(n1, n2) | 求两个数值型数值按位进行 AND 运算的结果 |
ASCII(char) | 返回字符对应的整数 |
ASCIISTR(char) | 将字符串 char 中,非 ASCII 的字符转成\XXXX(UTF-16)格式,ASCII 字符保持不变 |
BIT_LENGTH(char) | 求字符串的位长度 |
CHAR(n) | 返回整数 n 对应的字符 |
CHAR_LENGTH(char)/CHARACTER_LENGTH(char) | 求字符串的串长度 |
CHR(n) | 返回整数 n 对应的字符,等价于 CHAR(n) |
CONCAT(char1,char2,char3,…) | 顺序联结多个字符串成为一个字符串 |
DIFFERENCE(char1,char2) | 比较两个字符串的 SOUNDEX 值之差异,返回两个SOUNDEX 值串同一位置出现相同字符的个数。 |
INITCAP(char) | 将字符串中单词的首字符转换成大写的字符 |
INS(char1,begin,n,char2) | 删除在字符串 char1中以 begin参数所指位置开始的n 个字符, 再把 char2 插入到 char1 串的 begin 所指位置 |
INSERT(char1,n1,n2,char2)/ INSSTR(char1,n1,n2,char2) | 将字符串 char1 从 n1 的位置开始删除 n2 个字符,并将 char2 插入到 char1 中 n1 的位置 |
INSTR(char1,char2[,n,[m]]) | 从输入字符串 char1 的第 n 个字符开始查找字符串char2 的第 m 次出现的位置,以字符计算 |
INSTRB(char1,char2[,n,[m]]) | 从 char1 的第 n 个字节开始查找字符串 char2 的第 m次出现的位置,以字节计算 |
LCASE(char) | 将大写的字符串转换为小写的字符串 |
LEFT(char,n) / LEFTSTR(char,n) | 返回字符串最左边的 n 个字符组成的字符串 |
LEN(char) | 返回给定字符串表达式的字符(而不是字节)个数(汉字为一个字符),其中不包含尾随空格 |
LENGTH(char) | 返回给定字符串表达式的字符(而不是字节)个数(汉字为一个字符),其中包含尾随空格 |
OCTET_LENGTH(char) | 返回输入字符串的字节数 |
COPYB(DEST_LOB,SRC_LOB,LEN[,DOFFSET[,SOFFSET]]) | 拷贝指定长度的源 BLOB 数据插入到目标 BLOB |
LOCATE(char1,char2[,n]) | 返回 char1 在 char2 中首次出现的位置 |
LOWER(char) | 将大写的字符串转换为小写的字符串 |
LPAD(char1,n,char2) | 在输入字符串的左边填充上 char2 指定的字符,将其拉伸至 n 个字节长度 |
LTRIM(char1,char2) | 从输入字符串中删除所有的前导字符,这些前导字符由char2 来定义 |
POSITION(char1,/ IN char2) | 求串 1 在串 2 中第一次出现的位置 |
REPEAT(char,n) / REPEATSTR(char,n) | 返回将字符串重复 n 次形成的字符串 |
REPLACE(STR, search [,replace] ) | 将输入字符串 STR 中所有出现的字符串 search 都替换成字符串 replace ,其中 STR 为 char、clob 或text 类型 |
REPLICATE(char,times) | 把字符串 char 自己复制 times 份 |
REVERSE(char) | 将字符串反序 |
RIGHT / RIGHTSTR(char,n) | 返回字符串最右边 n 个字符组成的字符串 |
RPAD(char1,n,char2) | 类似 LPAD 函数,只是向右拉伸该字符串使之达到 n 个字节长度 |
RTRIM(char1,char2) | 从输入字符串的右端开始删除 char2 参数中的字符 |
SOUNDEX(char) | 返回一个表示字符串发音的字符串 |
SPACE(n) | 返回一个包含 n 个空格的字符串 |
STRPOSDEC(char) | 把字符串 char 中最后一个字符的值减一 |
STRPOSDEC(char,pos) | 把字符串 char 中指定位置 pos 上的字符值减一 |
STRPOSINC(char) | 把字符串 char 中最后一个字符的值加一 |
STRPOSINC(char,pos) | 把字符串 char 中指定位置 pos 上的字符值加一 |
STUFF(char1,begin,n,char2) | 删除在字符串 char1中以 begin参数所指位置开始的n 个字符, 再把 char2 插入到 char1 串的 begin 所指位置 |
SUBSTR(char,m,n) / SUBSTRING(char FROM m [FOR n]) | 返回 char 中从字符位置 m 开始的 n 个字符 |
SUBSTRB(char,n,m) | SUBSTR 函数等价的单字节形式 |
TO_CHAR(character) | 将 VARCHAR、CLOB、TEXT类型的数据转化为 VARCHAR类型输出 |
TRANSLATE(char,from,to) | 将所有出现在搜索字符集中的字符转换成字符集中的相应字符 |
TRIM([LEADING | TRAILING |
UCASE(char) | 将小写的字符串转换为大写的字符串 |
UPPER(char) | 将小写的字符串转换为大写的字符串 |
REGEXP | 根据符合 POSIX 标准的正则表达式进行字符串匹配 |
OVERLAY(char1 PLACING char2 FROM int [FOR int]) | 字符串覆盖函数,用 char2覆盖 char1中指定的子串,返回修改后的 char1 |
TEXT_EQUAL | 返回两个 LONGVARCHAR 类型的值的比较结果,相同返回 1,否则返回 0 |
BLOB_EQUAL | 返回两个 LONGVARBINARY 类型的值的比较结果,相同返回 1,否则返回 0 |
NLSSORT(str1 [,nls_sort=str2]) | 返回对汉字排序的编码 |
GREATEST(char1, char2, char3) | 求 char 1、char 2 和 char 3 中最大的字符串 |
GREAT (char1, char2) | 求 char 1、char 2 中最大的字符串 |
TO_SINGLE_BYTE (char) | 将多字节形式的字符(串)转换为对应的单字节形式 |
TO_MULTI_BYTE (char) | 将单字节形式的字符(串)转换为对应的多字节形式 |
EMPTY_CLOB () | 初始化 clob 字段 |
EMPTY_BLOB () | 初始化 blob 字段 |
UNISTR (char) | 将字符串 char 中,ascii 码(‘\XXXX’4 个 16 进制字符格式)转成本地字符。对于其他字符保持不变。 |
ADD_DAYS(date,n) | 返回日期加上 n 天后的新日期 |
ADD_MONTHS(date,n) | 在输入日期上加上指定的几个月返回一个新日期 |
ADD_WEEKS(date,n) | 返回日期加上 n 个星期后的新日期 |
CURDATE() | 返回系统当前日期 |
CURTIME(n) | 返回系统当前时间 |
CURRENT_DATE() | 返回系统当前日期 |
CURRENT_TIME(n) | 返回系统当前时间 |
CURRENT_TIMESTAMP(n) | 返回系统当前带会话时区信息的时间戳 |
DATEADD(datepart,n,date) | 向指定的日期加上一段时间 |
DATEDIFF(datepart,date1,date2) | 返回跨两个指定日期的日期和时间边界数 |
DATEPART(datepart,date) | 返回代表日期的指定部分的整数 |
DAY(date) | 返回日期中的天数 |
DAYNAME(date) | 返回日期的星期名称 |
DAYOFMONTH(date) | 返回日期为所在月份中的第几天 |
DAYOFWEEK(date) | 返回日期为所在星期中的第几天 |
DAYOFYEAR(date) | 返回日期为所在年中的第几天 |
DAYS_BETWEEN(date1,date2) | 返回两个日期之间的天数 |
EXTRACT(时间字段 FROM date) | 抽取日期时间或时间间隔类型中某一个字段的值 |
GETDATE(n) | 返回系统当前时间戳 |
GREATEST(date1,date2,date3) | 求 date1、date2 和 date3 中的最大日期 |
GREAT (date1,date2) | 求 date1、date2 中的最大日期 |
HOUR(time) | 返回时间中的小时分量 |
LAST_DAY(date) | 返回输入日期所在月份最后一天的日期 |
LEAST(date1, date2, date3) | 求 date1、date2 和 date3 中的最小日期 |
MINUTE(time) | 返回时间中的分钟分量 |
MONTH(date) | 返回日期中的月份分量 |
MONTHNAME(date) | 返回日期中月分量的名称 |
MONTHS_BETWEEN(date1,date2) | 返回两个日期之间的月份数 |
NEXT_DAY(date1,char2) | 返回输入日期指定若干天后的日期 |
NOW(n) | 返回系统当前时间戳 |
QUARTER(date) | 返回日期在所处年中的季节数 |
SECOND(time) | 返回时间中的秒分量 |
ROUND (date1[, fmt]) | 把日期四舍五入到最接近格式元素指定的形式 |
TIMESTAMPADD(datepart,n,timestamp) | 返回时间戳 timestamp 加上 n 个 datepart 指定的时间段的结果 |
TIMESTAMPDIFF(datepart,timeStamp1,timestamp2) | 返回一个表明 timestamp2与 timestamp1之间的指定 datepart 类型时间间隔的整数 |
SYSDATE() | 返回系统的当前日期 |
TRUNC(date[,fmt]) | 把日期截断到最接近格式元素指定的形式 |
WEEK(date) | 返回日期为所在年中的第几周 |
WEEKDAY(date) | 返回当前日期的星期值 |
WEEKS_BETWEEN(date1,date2) | 返回两个日期之间相差周数 |
YEAR(date) | 返回日期的年分量 |
YEARS_BETWEEN(date1,date2) | 返回两个日期之间相差年数 |
LOCALTIME(n) | 返回系统当前时间 |
LOCALTIMESTAMP(n) | 返回系统当前时间戳 |
OVERLAPS | 返回两个时间段是否存在重叠 |
TO_CHAR(date[,fmt[,nls]]) | 将日期数据类型 DATE 转换为一个在日期语法 fmt 中指定语法的 VARCHAR 类型字符串。 |
SYSTIMESTAMP(n) | 返回系统当前带数据库时区信息的时间戳 |
NUMTODSINTERVAL(dec,interval_unit) | 转换一个指定的 DEC 类型到 INTERVAL DAY TO SECOND |
NUMTOYMNTERVAL(dec,interval_unit) | 转换一个指定的 DEC 类型值到 INTERVAL YEAR TOMONTH |
WEEK(date, mode) | 根据指定的 mode 计算日期为年中的第几周 |
UNIX_TIMESTAMP(datetime) | 返回自标准时区的’1970-01-01 00:00:00 +0:00’的到本地会话时区的指定时间的秒数差 |
FROM_UNIXTIME(unixtime) | 返回将自’1970-01-01 00:00:00’的秒数差转成本地会话时区的时间戳类型 |
SESSIONTIMEZONE | 返回当前会话的时区 |
COALESCE(n1,n2,…nx) | 返回第一个非空的值 |
IFNULL(n1,n2) | 当 n1 为非空时,返回 n1;若 n1 为空,则返回 n2 |
ISNULL(n1,n2) | 当 n1 为非空时,返回 n1;若 n1 为空,则返回 n2 |
NULLIF(n1,n2) | 如果 n1=n2 返回 NULL,否则返回 n1 |
NVL(n1,n2) | 返回第一个非空的值 |
NULL_EQU | 返回两个类型相同的值的比较 |
常用函数
函数名 | 功能简要说明 |
---|---|
mod(n1,n2) | n1除以n2的余数 |
power(n1,n2) | 返回数字n1的n2次幂 |
sqrt(n) | 平方根 |
lower(n) | 返回字符串,并将所有的字符小写 |
upper(n) | 返回字符串,并将所有的字符大写 |
substr(string,start,count) | 取子字符串,从start开始,取count个 |
replace(‘string’,‘s1’,‘s2’) | s2替换string中的s1 |
length(n1) | 返回字符串的长度 |
concat(n1,n2) | 连接字符串n1和字符串n2 |
count(0) | 获取数据行数 |
max(n1) | 获取该列最大值 |
min(n1) | 获取该列中最小值 |
avg(n1) | 获取该列平均值 |
sum(n1) | 获取当前列总和 |
授权
添加权限控制
GRANT INSERT ON 表名 to 用户名;
GRANT INSERT ON JL.TABLE_2 TO TEST;
删除权限控制
GRANT DELETE ON 表名 to 用户名;
GRANT DELETE ON JL.TABLE_3 TO TEST;
修改权限控制
GRANT UPDATE ON 表名 to 用户名;
GRANT UPDATE ON JL.TABLE_3 TO TEST;
查询权限控制
GRANT SELECT ON 表名 to 用户名;
GRANT SELECT ON JL.TABLE_4 TO TEST;
角色授权
通过grant可以对用户进行角色授权
GRANT 角色 TO 用户名;
GRANT DBA TO TEST;
revoke是收回权限的语句,与grant相对应。
回收添加控制
revoke INSERT ON 表名 FROM用户名;
revoke INSERT ON JL.TABLE_2 FROM TEST;
回收删除控制
revoke DELETE ON 表名 FROM用户名;
revoke DELETE ON JL.TABLE_2 FROM TEST;
回收修改控制
revoke UPDATE ON 表名 FROM用户名;
revoke UPDATE ON JL.TABLE_2 FROM TEST;
回收查询控制
revoke SEELCT ON 表名 FROM用户名;
revoke SELECT ON JL.TABLE_2 FROM TEST;
角色权限收回
通过revoke可以收回用户所对应的角色,使该角色的权限不再归属于该用户。
REVOKE 角色 FROM 用户;
REVOKE DBA FROM TEST;