您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:Oracle数据类型及表管理,SQL轻便基

新葡亰496net:Oracle数据类型及表管理,SQL轻便基

发布时间:2019-07-20 12:43编辑:网络数据库浏览(119)

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

    Oracle 数据类型

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

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

    新葡亰496net 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 - 删除索引

    SQL是什么

    SQL是一种结构化查询语言(或者说一种访问和操作数据库的语言)

     

    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 添加一列

    alter table tabname drop column colname 删除一列

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


    SQL的作用

    使用SQL对关系型数据库中的数据进行定义和操作的语言

    SQL 面向数据库执行查询 在数据库中插入新的记录 更新数据库中的数据 从数据库删除记录

    SQL 可创建新数据库 在数据库中创建新表  在数据库中创建视图

    SQL 可以设置表、存储过程和视图的权限

     

    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;

    SQL的特点

    SQL语言简洁,语法简单,好学好用 SQL是一种结构化查询语言

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

    Date 数据类型

    存储日期和时间值

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

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

    索引

    创建索引:

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

    删除索引:

    drop index idxname

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

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

    SQLite 存储数据类型

    每个存储在 SQLite 数据库中的值都具有以下存储类之一:

    数据类型 描述

    NULL ( null )值是一个 NULL 值。

    INTEGER ( integer ) 值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。

    REAL ( real )值是一个浮点值,存储为 8 字节的 IEEE 浮点数字。

    TEXT (text)值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。

    BLOB( blob ) 值是一个 blob 数据,完全根据它的输入存储。

    SQLite 的存储类稍微比数据类型更普遍。INTEGER 存储类,例如,包含 6 种不同的不同长度的整数数据类型。

    • 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语句

    SQL语句主要分两部分,DDL DML 

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

    LOB数据类型

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

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


    =====

    常用数据类型


    1.DDL(数据定义语句)

      DDL是我们创建和删除数据库

      DDL 创建,删除和修改数据库中的表 

      DDL用来创建 和删除索引(搜索键 )

      • ##创建数据库

         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)新葡亰496net:Oracle数据类型及表管理,SQL轻便基础。:用于检索修改和插入数据

    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字节的信息。


    函数:


    新葡亰496net 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

    2.DML(数据操作语句)

       DML是我们对数据库进行SELECT-获取数据,INSERT INTO-插入数据,

       UPDATE-更新数据, DELETE-删除数据的执行语句

      • ##修改表属性,添加列
         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 操作符的!

    SELECT示例

    e1:

    SELECT * FROM  t_student(表名称)

    注释:SQL 语句对大小写不敏感。SELECT 等效于 select。

    e2:

    select * from t_student where age > 10 ;  //  条件查询

    对表的基本操作:

    表管理--约束

    日期类型

      日期类型用于存储日期数据,但是并不是使用一般的格式(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 字段类型1, 字段名2 字段类型2, …) ;

    示例: create table t_student (id integer, name text) ;

    推荐写法: create table if not exists 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;

    SQL不区分大小写,编写SQL规范,最好关键字都使用大写.表名最好加上t_前缀.

    PRIMARY KEY: 约束为主键.

    AUTOINCREMENT: 自动递增.

    创建表SQL语句:CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY AUTOINCREMENT, name text);

      • ##创建表

         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 类型,以及它们之间的差异。

    修改表

    格式: ALTER TABLE 旧表名 RENAME TO 新表名;

    示例: ALTER TABLE t_student RENAME TO t_person;

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

    列级约束

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

    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: 只要其中一个表中存在匹配,就返回行

    添加字段

    格式: ALTER TABLE 表名 ADD COLUMN 字段名 数据类型 限定符

    示例: ALTER TABLE t_student ADD COLUMN address text;

    创建视图:将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

    删除表

    1.删除表的格式

    格式: DROP TABLE IF EXISTS 表名;

    示例: DROP TABLE IF EXISTS t_student;

    推荐资源:w3school

    总结:SQL 是一种与数据库程序协同工作的标准语言,这些数据库程序包括 MS Access、DB2、Informix、MS SQL Server、Oracle、MySQL、Sybase 等等。

      • 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));

    新葡亰496net,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)各自的查询语言标准大体上是相同的,但是某些数据库中有语法上的区别,这里不一一列出,在自己做实验、练习、开发中自然会遇到,到时候最好的解决方法就是谷歌。而且数据库是一门计算机科学,背后可以深挖的技术细节有很多,对于普通的学生和开发者基本不会考虑得到,只要知道最常用的语法和功能即可,遇到疑难杂症的最好办法依然是找谷歌。另外本篇中可能会暗藏不少错误,希望路过的同学能帮忙指正,共同学习。

     

     

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:Oracle数据类型及表管理,SQL轻便基

    关键词: