Oracle数据类型及表管理,SQL轻巧基础

  对于SQL不再做过多的介绍,毕竟作为一个初学者对于SQL(结构化查询语言)也好,关系型数据库也好理解都并不是很深,只知道一些基本的概念。

Oracle 数据类型

创建表时,必须为各个列指定数据类型

以下是 Oracle 数据类型的类别:

图片 1


=====

# Sql的分类 #

DDL (Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等; CREATE、 ALTER、DROP
DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据); INSERT、 UPDATE、 DELETE
DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;
DQL(Data Query Language):数据查询语言,用来查询记录(数据)。
SELECT
*注意:sql语句以 ; 结尾
这些都是数据库的SQL的基础知识(也是常识),一定要记住。

SQL:结构化查询语言 RDBMS:关系型数据库管理系统
SQL可以分为两个部分:数据操作语言(DML)和数据定义语言(DDL)。
查询和更新指令构成了 SQL 的 DML 部分:
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

  本系列旨在介绍一些简单开发中用得上的SQL语句以及其使用方法,数据库广泛地使用在各种各样的应用开发中,数据库设计、原理和SQL语言也成了开发者们不得不掌握的一门技能之一,一下便开始简单介绍SQL语言的使用方法。

Oracle主要数据类型

DDL:操作数据库、表、列等


使用的关键字:CREATEALTERDROP

SQL 中最重要的 DDL 语句:
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更(改变)数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引(搜索键)
DROP INDEX - 删除索引

 

Character 数据类型

Char:固定长度字母数字值,可1-2000个字节

Varchar2:可变长度,1-4000个字节

Long:可变长度,最多2G

表基本的操作

  • 创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表:

select * into table_new from table_old (使用旧表创建新表)

create table tab_new as select col1,col2… from tab_old definition only

  • 删除表

drop table tablename

  • 修改表

rename 旧表名 to 新表名 修改表名

alert table tabname rename column oldColumn to newColumn 修改列名

alter table tabname add column col type 添加一列

Oracle数据类型及表管理,SQL轻巧基础。alter table tabname drop column colname 删除一列

alter table tabname modify( ) 修改表中列的值


 

Number 数据类型

可以存储整数、浮点数和实数

最高精度为 38 位

格式: NUMBER [( p[, s])]

主键

添加主键:

Alter table tabname add primary key(col)

删除主键:
Alter table tabname drop primary key(col)

SQL:
SELECT DISTINCT : 去除重复的数据。
ORDER BY :根据指定的列队结果集进行排序,默认为升序(ASC)排列,可以设置为降序(DESC)。
Top子句:用于规定要返回的记录的数目。
语法:
SQL Server:
SELECT TOP number | percent column_name(s) FROM table_name;
MySQL:
SELECT column_name(s) FROM table_name LIMIT number;
Oracle:
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;

数据库的大小写:我上网找了很多资料,许多人说数据库是不区分大小写的,也有的人说目前常用的Oracle数据库和MySQL数据库在运行时会自动将语句转变为大写形式执行,如果在大数据的应用领域上这一层转换会影响性能,所以书写SQL查询语句时应该养成习惯:关键字尽量使用大写,属性的名称可以使用小写。(博主使用SQL_server学习时发现SQL_server的关键字和属性名称是不区分大小写的)

Date 数据类型

存储日期和时间值

Date:存储日期和时间部分,精确到整个的秒

Timestamp:存储日期、时间和时区信息,秒值精确到小数点后6位

索引

创建索引:

create [unique] index idxname on tabname(col….)

删除索引:

drop index idxname

注:索引是不可更改的,想更改必须删除重新建。

通配符
SQL中的通配符必须与LIKE运算符一起使用:
描述

  • INT:整数类型(4字节),适用于SQL_server、My_SQL
  • FLOAT:浮点数类型(4字节),适用于SQL_server、My_SQL
  • NUMBER(精度p,位数s):数值型,精度p表示总有效数字位数,位数s表示小数点后有效数字位数,适用于Qracle数据库
  • DECIMAL(精度p,位数s):数值型,精度p表示总有效数字位数,位数s表示小数点后有效数字位数,适用于Oracle、SQL_server、My_SQL数据库(其中在Oracle数据库中底层也是由NUMBER类型实现的,为了数据库可转移性推出的数据类型)
  • CHAR(长度n):固定长度字符串,该类型长度固定位n字节(My_SQL在某些版本用字符作单位,不必考虑汉子占用字节比较多的问题)
  • VARCHAR(长度n):可变长度字符串,该类型长度可变,最大为n字节(My_SQL在某些版本用字符作单位,不必考虑汉子占用字节比较多的问题;Oracle中有VARCHAR2使用字符作单位,不必考虑汉子占用字节比较多问题)
  • DATE:显示时间(年月日)
  • DATESTEMP:显示时间(年月日 时分秒)
  • NULL:表示未知,既不表示0也不表示不存在而是表示存在值但是未知。(某些约束中会禁止使用NULL)

RAW/LONG RAW数据类型

RAW:存储二进制数据,最多2000字节

LONG RAW:用于存储可变长度的二进制数据,最多2GB

视图

创建视图:

create view viewname as select statement

删除视图:

drop view viewname

  • %:替代一个或多个字符
  • _ :仅替代一个字符
  • [charlist]:字符列中的任何单一字符
  • [^charlist] 或者[!charlist]:不在字符列中的任何单一字符

SQL(Structured Query Language)即我们所说的结构化查询语言,可以用来对数据库及其内部的基本表进行增、删、查、改的工作一下先简单介绍SQL语句对数据库和表能完成的基本操作:

LOB数据类型

称为“大对象”数据类型,可以存储多达 4GB 的非结构化信息,例如声音剪辑和视频文件等,允许对数据进行高效、随机、分段的访问

CLOB:字符数据;BLOB:二进制对象,如图形、视频、音频等;BFILE :即 Binary File(二进制文件),它用于将二进制数据存储在数据库外部的操作系统文件中


=====

常用数据类型


    • ##创建数据库

       CREATE DATABASE 数据库名 ON(
       NAME = 逻辑数据库名,
       FILENAME = '路径名.mdf'
       SIZE = 文件大小
       )
      ##删除数据库
       DROP DATABASE 数据库名
      
    • ##创建索引

       CREATE INDEX 索引名
       ON 表名(属性名)
      ##删除索引
       DROP INDEX 索引名                      ##Oracle中 
       ALTER TABLE 表名 DROP INDEX 索引名    ##My_SQL中
       DROP INDEX 表名.索引名                ##SQL_server中
      

SQL 语句复习

SQL 支持下列类别的命令:

数据定义语言(DDL):用于改变数据库结构

CREATE  ALTER  DROP

数据操纵语言(DML):用于检索修改和插入数据

INSERT  SELECT  DELETE UPDATE

事务控制语言(TCL):保证事务的执行

COMMIT  ROLLBACK  SAVEPOINT

数据控制语言(DCL):提供权限控制命令

GRANT  REVOKE


=====

字符串类型

CHAR类型 CHAR(size [BYTE | CHAR])

  CHAR类型,定长字符串,会用空格填充来达到其最大长度。非NULL的CHAR(12)总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息。如果创建表时,不指定CHAR长度,则默认为1。

VARCHAR类型

  不要使用VARCHAR数据类型。使用VARCHAR2数据类型。虽然VARCHAR数据类型目前是VARCHAR2的同义词,VARCHAR数据类型将计划被重新定义为一个单独的数据类型用于可变长度的字符串相比,具有不同的比较语义。

** VARCHAR2类型**

  变长字符串,与CHAR类型不同,它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。

NVARCHAR2类型

  这是一个包含UNICODE格式数据的变长字符串。 NVARCHAR2最多可以存储4,000字节的信息。


函数:


图片 2

  • 连接符||及CONCAT函数

可以连接两个列名或者常量。

注:如果姓名为中文还好,但是如果是英文的,这样连接会导致读起来比较困难,所以可以在中间加上常量“空格”;CONCAT函数符合ANSI SQL标准,所以适合更多不同的数据库,||是Oracle专有的,使用起来更简洁。

  • 格式统一:RPAD和LPAD

RPAD允许在列的右边填充一组字符,填充的字符可以为任何字符。LPAD从左边添加。

使用方式:

RPAD(string,length[,'set'])

LPAD(string,length[,'set'])

这里的string是数据库中的字符串列或常量,length是填充后的长度,set是用来填充的字符串。如果方括号中的内容省略了,会默认使用空格填充。

  • 修剪:LTRIM,RTRIM,TRIM

LTRIM和RTRIM从串的左边或右边删除不需要的字符。

使用方式:

RTRIM(string[,'set'])

LTRIM(string[,'set'])

如果没有设置要删除的值,默认删除空格。

  • 大小写转换:LOWER、UPPER和INITCAP

LOWER把串或列种的任意字母转换为小写。

UPPER与LOWER相反。

INITCAP将串或列中每个单词的首字母转换成大写。

它们经常一起使用。

使用格式:

LOWER(string)

UPPER(string)

INITCAP(string)

  • 子串:SUBSTR

使用SUBSTR函数可以提取出串的一部分。

使用格式:

SUBSTR(string,start[,count])

这个函数告诉Oracle提取string的一个子串,从start位置开始,长度为count个字符。如果不指定count,将从start开始一直到这个串结束。

  • 索引位置:INSTR

INSTR可以告诉你要搜索的字符(串)在串种的位置。

使用格式:

INSTR(string,set[,start[,occurrence]])

string为要寻找的列或常量;set为要指定的要寻找的值;start可选,默认为从串的第一个位置开始搜索;occurrence可选,为指定字符串出现的第occurrence次的位置。

  • like

查询含下划线的名字

SELECT * FROM emp
where ename LIKE '%/_%'  escape '/';

BETWEEN 操作符:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

    • ##修改表属性,添加列
       ALTER TABLE 表名
       MODIFY COLUMN 属性名 数据类型 约束   ##Oracle、My_SQl
       ADD COLUMN 属性名 数据类型 约束      ##SQL_server
      ##修改表属性,删除列
       ALTER TABLE 表名
       DROP COLUMN 属性名
      ##修改表属性,修改已有列
       ALTER TABLE 表名
       ALTER COLUMN 属性名 数据类型 约束
      
       

表管理--创建表

利用现有的表创建表

语法:

CREATE TABLE <new_table_name> AS SELECT column_names FROM <old_table_name>;

示例:

SQL> CREATE TABLE newemp

           AS SELECT * FROM emp[WHERE 1 = 2;];

SQL> CREATE TABLE newemp

           AS SELECT empno, salary

           FROM emp;


=====

数字类型

NUMBER类型

  NUMBER(P,S)是最常见的数字类型,可以存放数据范围为10^130~10^126(不包含此值),需要1~22字节(BYTE)不等的存储空间。

  P 是Precison的英文缩写,即精度缩写,表示有效数字的位数,最多不能超过38个有效数字

  S是Scale的英文缩写,可以使用的范围为-84~127。Scale为正数时,表示从小数点到最低有效数字的位数,它为负数时,表示从最大有效数字到小数点的位数

INTEGER类型

  INTEGER是NUMBER的子类型,它等同于NUMBER(38,0),用来存储整数。若插入、更新的数值有小数,则会被四舍五入。

浮点数

BINARY_FLOAT

  BINARY_FLOAT 是 32 位、 单精度浮点数字数据类型。可以支持至少6位精度,每个 BINARY_FLOAT 的值需要 5 个字节,包括长度字节。

BINARY_DOUBLE

  BINARY_DOUBLE 是为 64 位,双精度浮点数字数据类型。每个 BINARY_DOUBLE 的值需要 9 个字节,包括长度字节。

  在数字的列中,浮点数有小数精度。在 BINARY_FLOAT 或 BINARY_DOUBLE 的列中,浮点数有二进制的精度。二进制浮点数支持的特殊值无穷大和 NaN (不是数字)。

FLOAT类型

  FLOAT类型也是NUMBER的子类型。

  Float(n),数 n 指示位的精度,可以存储的值的数目。N 值的范围可以从 1 到 126。若要从二进制转换为十进制的精度,请将 n 乘以 0.30103。要从十进制转换为二进制的精度,请用 3.32193 乘小数精度。126 位二进制精度的最大值是大约相当于 38 位小数精度。


函数:


函数 说明
ROUND(date, fmt) 四舍五入
TRUNC(date, fmt) 截断
MOD(n1, n2) 求余
CEIL(n) 向上取整
FLOOR(n) 向下取整
GREATEST(expr1, ... exprn) 返回参数中最大的数
LEAST(expr1, ... exprn) 返回参数中最小的数

随机数

SELECT TRUNC(dbms_random.value(a,b)) FROM dual

产生 [a,b)之间的数

  • 重要事项:不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 "Adams" 和 "Carter" 之间的人,但不包括 "Adams" 和 "Carter" ;某些数据库会列出介于 "Adams" 和 "Carter" 之间并包括 "Adams" 和 "Carter" 的人;而另一些数据库会列出介于 "Adams" 和 "Carter" 之间的人,包括 "Adams" ,但不包括 "Carter" 。所以,请检查你的数据库是如何处理 BETWEEN....AND 操作符的!

对表的基本操作:

表管理--约束

日期类型

  日期类型用于存储日期数据,但是并不是使用一般的格式(2012-08-08)直接存储到数据库的。

DATE类型

  DATE是最常用的数据类型,日期数据类型存储日期和时间信息。虽然可以用字符或数字类型表示日期和时间信息,但是日期数据类型具有特殊关联的属性。为每个日期值,Oracle 存储以下信息: 世纪、 年、 月、 日期、 小时、 分钟和秒。一般占用7个字节的存储空间。

TIMESTAMP类型

  这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同,因为TIMESTAMP可以包含小数秒,带小数秒的TIMESTAMP在小数点右边最多可以保留9位

TIMESTAMP WITH TIME ZONE类型

  这是TIMESTAMP类型的变种,它包含了时区偏移量的值


函数:


函数 说明
MONTHS_BETWEEN(date1, date2) 两个日期相差的月数
ADD_MONTHS(date, int) 向指定日期中加上若干月数
NEXT_DAY(date, ch) 指定日期的下一个日期(从星期日和1开始计算)
LAST_DAY(date) 本月的最后一天
ROUND(date, fmt) 日期四舍五入 fmt的值('YEAR','MONTH')
TRUNC(date, fmt) 日期截断 fmt的值('YEAR','MONTH')
EXTRACT(time_unit FROM date) 从date中提取time_unit指定格式的日期数据

日期格式

格式 说明 举例
YYYY 年份的数字格式全称 2017
YEAR 年的英文全称 twenty seventeen
MM 月份(数字格式) 10
MONTH 月得全称 10月
DY 星期几 星期四
DAY 星期几 星期四
DD 一个月的第几天 05
HH12 小时(12) 1
HH24 小时(24) 13
MI 分钟 12
SS 12
  • 当前时间
SELECT SYSDATE 
FROM
    dual;
SELECT
    SYSTIMESTAMP 
FROM
    dual;-- 精确到毫秒

SELECT
    TO_CHAR( SYSDATE, 'yyyy-mm-dd hh24:mi:ss' ) 
FROM
    dual;
-- 昨天 今天   明天
SELECT
    ( SYSDATE - 1 ) 昨天,
    SYSDATE 今天,
    ( SYSDATE   1 ) 明天 
FROM
    dual;

修改日期格式
(默认格式为'DD-MON-RR')

SELECT * FROM v$nls_parameters;

ALTER SESSION SET nls_date_format='yyyy-mm-dd';
-- 改回默认
ALTER SESSION SET nls_date_format='DD-MON-RR';


    • ##创建表

       CREATE TABLE 表名(
       属性1 数据类型 约束,
       属性2 数据类型 约束,
       属性3 数据类型 约束,
       ......
       )
      ##删除表
       TURNCATE TABLE 表名     ##只删除表的数据,保留表
       DROP TABLE 表名         ##删除表的数据以及表本身
      
    • ##往表中插入行 (手动添加数据)

       INSERT INTO 表名(属性1,属性2,属性3......)
       VALUES(值 1,值2,值3......)
      
      ##往表中插入行(SELECT投影的数据插入)
       INSERT INTO 表名(属性1,属性2,属性3......)
       SELECT语句
      
    • ##修改行的内容

       UPDATE 表名
       SET 属性1=值1,属性2=值2,属性3=值3...
       WHERE语句
      

五大约束

1、非空约束:NOT NULL

2、主键约束:PRIMARY KEY,唯一、且非空

3、外键约束:FOREIGN KEY ... REFERENCES

4、唯一约束:UNIQUE,唯一,允许为空

5、条件约束:CHECK

LOB类型

  内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE(外部存储)的大型化和非结构化数据,如文本、图像、视屏、空间数据存储。BLOB、CLOB、NCLOB类型

CLOB 数据类型

它存储单字节和多字节字符数据。支持固定宽度和可变宽度的字符集。CLOB对象可以存储最多 (4 gigabytes-1) * (database block size) 大小的字符

NCLOB 数据类型

它存储UNICODE类型的数据,支持固定宽度和可变宽度的字符集,NCLOB对象可以存储最多(4 gigabytes-1) * (database block size)大小的文本数据。

BLOB 数据类型

它存储非结构化的二进制数据大对象,它可以被认为是没有字符集语义的比特流,一般是图像、声音、视频等文件。BLOB对象最多存储(4 gigabytes-1) * (database block size)的二进制数据。

BFILE 数据类型

二进制文件,存储在数据库外的系统文件,只读的,数据库会将该文件当二进制文件处理

Join:
下面列出了您可以使用的 JOIN 类型,以及它们之间的差异。

创建临时表只需要在表名前面加#号即可(临时表会在每次关闭数据库时清楚)

列级约束

约束作为列定义的一部分,允许定义全部的五类约束

SQL> CREATE TABLE clazz_table (

          cid NUMBER(10)PRIMARY KEY,

          cname VARCHAR2(30) NOT NULL,

          cdate DATE);

SQL> CREATE TABLE student_table(

          sid NUMBER(10) primary key ,

          clazzid NUMBER(10)REFERENCES clazz_table(cid),

          sno VARCHAR2(30)UNIQUE,

          sname VARCHAR(30) NOT NULL,

          sage NUMBER(3) CHECK(sage>0 AND sage<120));

注意:

列名、数据类型、缺省值的次序不能乱;

在列定义的最后,指定列类型的约束;

主键约束的列可以不指定非空约束(NOT NULL)。

LONG类型

  它存储变长字符串,最多达2G的字符数据(2GB是指2千兆字节, 而不是2千兆字符),与VARCHAR2 或CHAR 类型一样,存储在LONG 类型中的文本要进行字符集转换。ORACLE建议开发中使用CLOB替代LONG类型。支持LONG 列只是为了保证向后兼容性。CLOB类型比LONG类型的限制要少得多。 LONG类型的限制如下:

  • 一个表中只有一列可以为LONG型。

  • LONG列不能定义为主键或唯一约束,

  • 不能建立索引.

  • LONG数据不能指定正则表达式。

  • 函数或存储过程不能接受LONG数据类型的参数。

  • LONG列不能出现在WHERE子句或完整性约束(除了可能会出现NULL和NOT NULL约束)

  • JOIN: 如果表中有至少一个匹配,则返回行(INNER JOIN)
  • LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
  • RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
  • FULL JOIN: 只要其中一个表中存在匹配,就返回行

创建视图:将SELECT的查询结果创建为视图(视图是一种虚表,由查找语句得到的结果组成的虚表。视图并不存储数据,只是一个结果集)

表级约束

约束作为表定义的一部分,除非空约束外,允许定义其他四类约束

唯一约束

CONSTRAINT nameUNIQUE(column[,column...])‏

主键约束

CONSTRAINT name PRIMARY KEY(column[,column...])‏

外键约束

CONSTRAINT name FOREIGN KEY (column[,column...]) REFERENCES table(column[,column...])

条件约束

CONSTRAINT name CHECK(condition)‏

定义表级约束

SQL>create table account(

name VARCHER2(32) ,

acc_type NUMBER(1) NOT NULL,

acc_code VARCHAR2(32),

ba NUMBER(5,2) DEFAULT 100,

CONSTRAINT pk_qrsx_account PRIMARY KEY (name),

CONSTRAINT uk_qrsx_account UNIQUE(acc_code),

CONSTRAINT ck_qrsx_account CHECK(ba>=100 AND ba<=1000));


=====

数据转换

数据转换分为

  • 隐式转换 varchar《==》number oracle数据库自动转换

  • 显示转换 number《==》character《==》date

INNER JOIN 关键字:
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
语法
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

    • CREATE VIEW 视图名 AS
      SELECT语句
      

表管理--修改表

ALTER TABLE :修改表

列的添加、修改

给表添加/删除约束

语法:

ALTER TABLE[add][modify][drop column]

示例:

alter table users add(pubdate date);

alter table users add(age number)

alter table users modify(userName varchar2(20));

alter table users drop(password);

alter table users add primary key(userId);


=====

character 《==》 date

  • TO_CHAR(date[,fmt])

  • TO_DATE(ch[, fmt])

例:

SELECT
    TO_CHAR( SYSDATE, 'dy' ) 
FROM
    dual;
  • 注释:INNER JOIN 与 JOIN 是相同的。

设置主键:

表管理--其它

RENAME table_name TO new_name :重命名

TRUNCATE TABLE:删除表 ,记录不可恢复

DROP TABLE:删除表

COMMENT ON:为表添加注释

示例:

RENAME users to test

DROP TABLE users;--删除表结构

TRUNCATE TABLE users --删除记录,释放空间

DELETE FROM emp --删除记录,但可以恢复

COMMENT ON TABLE emp IS 'Employee Information';


=====

number《==》character

  • TO_CHAR(NUMBER[,fmt])

  • TO_NUMBER(expr[, fmt])

数字转换格式

格式 说明
9 数字
0
$ 美元符号
. 小数点
, 千位符

例子:

-- 查询员工的薪水:两位小数,千位符,本地货币
SELECT
    TO_CHAR( sal, 'L9,999.99' ) 
FROM
    emp;
-- ¥7,000.00

    • PRIMARY KEY 属性名 ##设置唯一的主键
      CONSTRAINT 主键名 PRIMARY KEY (属性1,属性2...)    ##设置联合主键
      

Oracle数据库中的表

用户表

用户创建和维护的一系列表的集合包含用户的信息

数据字典

Oracle 服务器创建和维护的一系列表和视图的集合,包含数据库的信息

user_xxx用户拥有的

all_xx 用户有权查看

dba_xxx(sys)  所有的信息


=====

通用函数

函数 说明
NVL(expr1, expr2) 把列expr1的null值修改为expr2
NVL2(expr1, expr2, expr3) 把列expr1的null值修改为expr2,不为null修改为expr3
NULLIF(expr1, expr2) 判断expr1和expr2是否相等,相等返回null,反之返回expr2
COALESCE(expr1, ... exprn) 从左到右找到第一个不为null的值

LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
语法
SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

设置外键:

数据控制语言DCL

数据控制语言为用户提供权限控制命令

用于权限控制的命令有:

GRANT 授予权限

REVOKE 撤销已授予的权限

示例:

SQL> GRANT ALL ON EMP TO TEA;

SQL> REVOKE SELECT, UPDATE ON EMP FROM TEA;


=====

条件表达式

通用的

CAST expr WHEN comparison_expr1 THEN return_expr1
          [WHEN comparison_expr1 THEN return_expr1
                    ELSE else_expr]
END

例子

SELECT
    ename,
    job,
    sal 涨前,
CASE
        job 
        WHEN '学生' THEN
        sal   100 
        WHEN '教师' THEN
        sal   800 ELSE sal   50 
    END 张后 
FROM
    emp;

oracle自己的

DECODE(COLUMN,search, result [[,search, result]*, default]) oracle自己的

例子:

SELECT
    ename,
    job,
    sal 涨前,
    DECODE( JOB, '学生', sal   100, '教师', sal   800, sal   50 ) 张后 
FROM
emp;

注释:在某些数据库中, LEFT JOIN 称为 LEFT OUTER JOIN。

    • FOREIGN KEY (表内属性名) REFERENCES 表名(表外属性名)

本章小结

掌握Oracle常用数据类型

掌握Oracle数据库表的管理

掌握事务控制语句

RIGHT JOIN 关键字
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
语法
SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

约束:约束包括空值约束、主键约束、外键约束、缺省值约束、CHECK值约束(是表的列对行的约束)。一般在创建或修改表时填在数据类型后的约束位置,一列可添加多个约束。

注释:在某些数据库中, RIGHT JOIN 称为 RIGHT OUTER JOIN。

  • 空值约束:NULL和NOT NULL 指是否允许填入空值
  • 主键约束:设定某一行属性为主键
  • 外键约束:设定某一行属性为外键
  • 缺省值约束:为某一行设定缺省值
    •  DEFAULT 默认值 
    •  [time] DATE DEFAULT GETDATE() ##表示为time这个列添加一个缺省值为当前系统时间(GETDATE()是SQL内置的函数之一,讲查找时会讲) 
  • CHECK值约束:指定某一行的属性的数据应该满足某些条件
    •  CHECK 某属性满足的条件
    • ALTER TABLE t ADD CHECK (t_id>100 AND t_id<999 AND t_price>0) ##表示添加多个CHECK约束,使t_id在100到999之间取值;t_price取值至少比0大

FULL JOIN 关键字
只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。
语法
SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name

 

  • 注释:在某些数据库中, FULL JOIN 称为 FULL OUTER JOIN。

## 总结:


  关于表中列属性的操作和数据库的操作基本是相同的,而且SQL查询语言实在是一种十分接近人类思维逻辑的语言,只要知道几个关键词的用法(CREATE、DROP、ALTER、ADD、INSERT INTO、SET等),按照需要将属性、约束等填进去基本就是对的了,大部分的修改、添加、删除、查询命令都可以直接按照平时说话的逻辑组合。像我这等初学者也可以很快地掌握。

UNION 和 UNION ALL 操作符

UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。

UNION 语法
SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

UNION ALL 语法
SELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2
另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。


SELECT INTO 语句
SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

SQL 约束
约束用于限制加入表的数据的类型。
可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)。
我们将主要探讨以下几种约束:

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT

当表已被创建时,如需在 "Id_P" 列创建 UNIQUE 约束,请使用下列 SQL:
ALTER TABLE Persons ADD UNIQUE (Id_P)

如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束,请使用下面的 SQL 语法:
ALTER TABLE PersonsADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

如需撤销 UNIQUE 约束,请使用下面的 SQL:

MySQL:
ALTER TABLE Persons DROP INDEX uc_PersonID


SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

CHECK 约束:
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制。

DEFAULT 约束:
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值,那么会将默认值添加到所有的新记录。


索引
您可以在表中创建索引,以便更加快速高效地查询数据。
用户无法看到索引,它们只能被用来加速搜索/查询。
注释:更新一个包含索引的表需要比更新一个没有索引的表更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。

CREATE INDEX 语法
在表上创建一个简单的索引。允许使用重复的值:
CREATE INDEX index_name ON table_name (column_name)

CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name ON table_name (column_name)

MySQL 上删除索引 DROP INDEX:
ALTER TABLE table_name DROP INDEX index_name


TRUNCATE TABLE 语句
仅仅需要除去表内的数据,但并不删除表本身, 使用 TRUNCATE TABLE 命令(仅仅删除表格中的数据):
TRUNCATE TABLE 表名称

视图是可视化的表
什么是视图?
在 SQL 中,视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句,我们也可以提交数据,就像这些来自于某个单一的表。
注释:数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
CREATE VIEW view_name ASSELECT column_name(s) FROM table_name WHERE condition


MySQL Date 函数
下面的表格列出了 MySQL 中最重要的内建日期函数:
NOW()
返回当前的日期和时间
CURDATE()
返回当前的日期
CURTIME()
返回当前的时间
DATE()
提取日期或日期/时间表达式的日期部分
EXTRACT()
返回日期/时间按的单独部分
DATE_ADD()
给日期添加指定的时间间隔
DATE_SUB()
从日期减去指定的时间间隔
DATEDIFF()
返回两个日期之间的天数
DATE_FORMAT()
用不同的格式显示日期/时间

SQL Server Date 函数
下面的表格列出了 SQL Server 中最重要的内建日期函数:
GETDATE()
返回当前日期和时间
DATEPART()
返回日期/时间的单独部分
DATEADD()
在日期中添加或减去指定的时间间隔
DATEDIFF()
返回两个日期之间的时间
CONVERT()
用不同的格式显示日期/时间

SQL Date 数据类型
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值:

  • DATE - 格式 YYYY-MM-DD
  • DATETIME - 格式: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
  • YEAR - 格式 YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值:

  • DATE - 格式 YYYY-MM-DD
  • DATETIME - 格式: YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式: 唯一的数字

NULL 值是遗漏的未知数据。
默认地,表的列可以存放 NULL 值。
提示:请始终使用 IS NULL 来查找 NULL 值。

MySQL 数据类型
在 MySQL 中,有三种主要的类型:文本、数字和日期/时间类型。
Text 类型:

  • CHAR(size)
    保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。
  • VARCHAR(size)
    保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。
    注释:如果值的长度大于 255,则被转换为 TEXT 类型。
  • TINYTEXT
    存放最大长度为 255 个字符的字符串。
  • TEXT
    存放最大长度为 65,535 个字符的字符串。
  • BLOB
    用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
  • MEDIUMTEXT
    存放最大长度为 16,777,215 个字符的字符串。
  • MEDIUMBLOB
    用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
  • LONGTEXT
    存放最大长度为 4,294,967,295 个字符的字符串。
  • LONGBLOB
    用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
  • ENUM(x,y,z,etc.)
    允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值,则插入空值。
    注释:这些值是按照你输入的顺序存储的。
    可以按照此格式输入可能的值:ENUM('X','Y','Z')
  • SET
    与 ENUM 类似,SET 最多只能包含 64 个列表项,不过 SET 可存储一个以上的值。

Number 类型:

  • TINYINT(size)
    -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
  • SMALLINT(size)
    -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
  • MEDIUMINT(size)
    -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
  • INT(size)
    -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
  • BIGINT(size)
    -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
  • FLOAT(size,d)
    带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
  • DOUBLE(size,d)
    带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
  • DECIMAL(size,d)
    作为字符串存储的 DOUBLE 类型,允许固定的小数点。
    注:这些整数类型拥有额外的选项 UNSIGNED。通常,整数可以是负数或正数。如果添加 UNSIGNED 属性,那么范围将从 0 开始,而不是某个负数。

Date 类型:

  • DATE()
    日期。格式:YYYY-MM-DD
    注释:支持的范围是从 '1000-01-01' 到 '9999-12-31'
  • DATETIME()
    *日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS
    注释:支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
  • TIMESTAMP()
    *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式:YYYY-MM-DD HH:MM:SS
    注释:支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
  • TIME()
    时间。格式:HH:MM:SS 注释:支持的范围是从 '-838:59:59' 到 '838:59:59'
  • YEAR()
    2 位或 4 位格式的年。
    注释:4 位格式所允许的值:1901 到 2155。2 位格式所允许的值:70 到 69,表示从 1970 到 2069。
    注: 即便 DATETIME 和 TIMESTAMP 返回相同的格式,它们的工作方式很不同。在 INSERT 或 UPDATE 查询中,TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式,比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

  这一篇主要介绍了SQL语句和数据库、表的增、删、改内容,目前比较流行的关系型数据库(如MySQL、Oracle、SQL_server、DB2)各自的查询语言标准大体上是相同的,但是某些数据库中有语法上的区别,这里不一一列出,在自己做实验、练习、开发中自然会遇到,到时候最好的解决方法就是谷歌。而且数据库是一门计算机科学,背后可以深挖的技术细节有很多,对于普通的学生和开发者基本不会考虑得到,只要知道最常用的语法和功能即可,遇到疑难杂症的最好办法依然是找谷歌。另外本篇中可能会暗藏不少错误,希望路过的同学能帮忙指正,共同学习。

 

 

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:Oracle数据类型及表管理,SQL轻巧基础

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。