精华内容
下载资源
问答
  • 一张表中,需要根据条件查询出几乎所有字段的内容 将需要的字段都建立成复合索引,是否合理?
  • SOL: SELECT TMP_PAGE.*, ROWNUM ROW_ID ...建立索引 ①给group by三个字段分别建立单个索引②给group by三个字段建立组合索引③给SQL中用到的字段都建立单个索引。最终效果都不明显,大家有啥好办法达到需求?
  • MySql中的索引基础

    2020-03-04 15:56:39
    索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。 MySql中索引的存储类型有两种:Btree、Hash 优点: 1、所有列(字段可以被索引 2、大大提高查询速度 缺点: 1、索引会降低更新表的速度(更新...

    索引相当于书本上的目录

    索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

    优点:

    1、所有列(字段)都可以被索引
    2、大大提高查询速度

    缺点:

    1、索引会降低更新表的速度(更新表时MySql不仅需保存数据还要保存索引文件)
    2、建立索引会占用磁盘空间
    3、数据越大索引维护越耗时

    使用原则:

    1、对经常更新的表避免建立过多索引,对经常用于查询的字段应创建索引
    2、数据量小尽量不用索引
    3、在唯一性强的列上适合创建索引

    索引的分类:

    从逻辑角度

    分为四类:单列索引(普通索引,唯一索引,主键索引)、组合索引、全文索引、空间索引

    1)单列索引:一个索引只包含单个列,但一个表中可以有多个单列索引。

    普通索引:MySQL中基本索引类型没有什么限制,允许在定义索引的列中插入重复值和空值

    唯一索引:索引列中的值必须是唯一的,但是允许为空值

    主键索引:是一种特殊的唯一索引,不允许有空值

    2)组合索引
    在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合

    3)全文索引
    只有在MyISAM引擎上才能使用,只能在CHAR,TEXT,VARCHAR类型字段上使用。索引类型为FULLTEXT
    允许有重复值和空值,全文索引需要配合match against操作使用。

    4)空间索引
    对空间数据类型的字段建立的索引,MySQL中的
    空间数据类型有四种,GEOMETRY、POLYGON、LINESTRING、POINT。在创建空间索引时,使用SPATIAL关键字。
    要求:引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL

    从物理存储角度

    分为聚集索引和非聚集索引。
    1)聚集索引
    数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。像新华字典的拼音检索。

    2)非聚集索引
    索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。像新华字典的偏旁检索。

    从数据结构角度

    1)Hash索引
    2)B+tree索引

    注意:索引是在存储引擎中实现的,也就是说不同的存储引擎,会使用不同的索引

    创建索引的sql语句:

    直接创建索引:

    普通索引
    create index index_name 
    on table_name (column(length));
    
    唯一索引
    create unique index index_name 
    on table_name (column(length));
    

    修改表结构添加索引:

    普通索引
    alter table table_name
    add index index_name 
    on (column(length));
    
    唯一索引
    alter table table_name 
    add index unique index index_name
    on (column(length));
    
    组合索引
    alter table table_name
    add index index_name(column1,column2,...);
    
    全文索引
    alter table table_name
    add fulltext index_name(column);
    

    创建表时创建普通索引:

    普通索引
    create table table_name(
    ...
    ...
    index index_name(column(length)));
    
    唯一索引
    create table table_name(
    ...
    ...
    unique index index_name(column(length)));
    
    主键索引
    create table table_name(
    ...
    ...
    primary key(column));
    

    删除索引:

    drop index index_name on table_name;
    

    显示索引信息:

    show index from table_name;\G
    

    引用:
    添加链接描述
    添加链接描述
    添加链接描述
    添加链接描述

    展开全文
  • mysql的索引介绍

    2019-05-13 21:48:05
    简单的来说,索引就是相当一个目录,举个栗子,我现在想在一张学生中查找叫李明的学生,没有索引的话,mysql执行全盘扫描(理解为for循环),并且扫描的是数据库中的所有字段。如果我在name这个字段上建立一个索引...

      什么是索引?

     简单的来说,索引就是相当一个目录,举个栗子,我现在想在一张学生表中查找叫李明的学生,没有索引的话,mysql执行全盘扫描(理解为for循环),并且扫描的是数据库中的所有字段。如果我在name这个字段上建立一个索引,那么只需要去查找name这一列,然后在将这一列的值取出来就好。

    索引的优缺点

    任何事物都是一种双刃剑,有优点必然有缺点。

    索引的优点很明显:查找速度快。

    索引的缺点:索引也是表中的信息,索引如果使用不恰当,反而会使查找速度降低。

    下面一个例子看一下用索引查找和不用索引查找的时间。

    city表结构如下:

     CREATE TABLE `city` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `Name` char(35) NOT NULL DEFAULT '',
      `CountryCode` char(3) NOT NULL DEFAULT '',
      `District` char(20) NOT NULL DEFAULT '',
      `Population` int(11) NOT NULL DEFAULT '0',
      `test` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`ID`),
      KEY `CountryCode` (`CountryCode`),
      CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
    

        我们查询人口大于200000的城市信息:

    右下角时间为0.469秒

    下面我们加上索引来查一下:

    重新修改表结构:

     CREATE TABLE `city` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `Name` char(35) NOT NULL DEFAULT '',
      `CountryCode` char(3) NOT NULL DEFAULT '',
      `District` char(20) NOT NULL DEFAULT '',
      `Population` int(11) NOT NULL DEFAULT '0',
      `test` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`ID`),
      KEY `CountryCode` (`CountryCode`),
      KEY `testindex` (`Population`),
      CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
    ) ENGINE=InnoDB AUTO_INCREMENT=304080 DEFAULT CHARSET=latin1
    

    我们在population加上了普通索引,下边来查询:

    右下角时间0.016秒,效率提交了约30倍,可见适当的加上所以还是很必要的。

    索引分类

     普通索引:上述使用的就是普通索引

         语法:alter table 表名 add index 索引名('字段名')

                   create index on 表名(列名);

     唯一索引:字段设置唯一

      主键也是唯一索引,因为主键字段必须是唯一的。

     全文索引:可以在varchar,char,text上边创建 

      关键字:fulltext(语法自行百度把)

    这个全文索引我在用mysql8.0的时候遇到一些问题,详见我的博客置顶,去看看

    多列索引:可以创建基于多个字段的索引

     

      使用其中的一个字段去搜索也可以作为普通索引。

      合理使用比单列索引效率更高。遵循最左匹配原则(下边详讲)

    索引在mysql是怎么存储的?

    索引在mysql中是通过B树,B+树,R-树以及散列类型。

    最左匹配原则:

    通过上面了解了索引的存储方式以后,可以更好的了解最左匹配原则:

    将age,sex,grade作为多列索引。

    select * from test where age<10 and sex ='man' and grade<5

    查询的时候就会先去查找age<10的数据,然后在这点数据中找sex=‘man’的数据最后再进行筛选出grade<5的数据。

    但是如果我把查询条件顺序修改一下,那么他的执行是怎么执行的呢?

    select * from test where grade<5  and sex ='man' and age<10

    但是这条数据并不是并不是向上述那样执行的,那么不是最左匹配原则吗?

    这是因为mysql中存在查询优化器,他会去判断这条语句怎么执行效率最高,然后生成执行计划。

     

     

     

      

     

    展开全文
  • 对于数据库而言实际上每一张表都表示是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表,视图,索引,序列,约束等等,属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为...

    1.常用操作及字段

    对于数据库而言实际上每一张表都表示是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表,视图,索引,序列,约束等等,都属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为以下三类语法:
    创建对象:CREATE 对象名称…;
    删除对象:DROP 对象名称…;
    修改对象:ALTER 对象名称…;
    常用的数据字段
    每一张数据表实际上都是由若干个字段组成,而每一个字段都会有其对应的数据类型,在oracle中,常用的数据类型有以下几种:
    字符串 VARCHAR2(n) n表示的是字符串所能保存的最大长度,基本上保存200个左右的内容
    整数 NUMBER(n) 表示最多为n位的整数,有时候可以使用INT代替
    小数 NUMBER(n,m) 其中m为小数位,n-m为整数位,有时候可以用FLOAT代替 7,2 ,* * * * * .* *
    日期 DATE 存放日期时间
    大文本 CLOB 可以存储海量文字(4Gb),例如存储小说等
    大对象 BLOB 存放二进制,例如:电影,MP3,图片,文字 LOB

    语法

    1.表的创建

    CREATE TABLE 表名称(
    字段1 数据类型 [DEFAULT 默认值],
    字段2 数据类型 [DEFAULT 默认值],

    字段3 数据类型 [DEFAULT 默认值]
    );
    #创建一张成员表(member),保存以下信息:姓名、年龄、生日、个人简介。
    CREATE TABLE MEMBER(
    name VARCHAR2(20) DEFAULT ‘WUMING’,
    age NUMBER(3),
    birthday DATE DEFAULT SYSDATE,
    content CLOB
    );
    该表创建好之后,插入几条数据,把以下命令执行三次来插入三条相同的数据
    INSERT INTO MEMBER(name,age,birthday,content) VALUES (‘ZHANGSAN’,20,TO_DATE(‘1990-08-12’,‘yyyy-mm-dd’),‘superman’);
    在这里插入图片描述

    2.表的复制

    CREATE TABLE 复制表名称 AS 子查询

    #复制一张只包含20部门的雇员信息的表
    CREATE TABLE EMP20 AS SELECT * FROM EMP WHERE DEPTNO=20;
    #查看该表
    SELECT * FROM TAB;
    在这里插入图片描述
    #复制emp表的表结构,不要数据
    CREATE TABLE EMPNULL AS SELECT * FROM EMP WHERE 1=2;
    #查询该表的内容
    SELECT * FROM EMPNULL;
    该语句只有ORACLE支持,其他数据库可能存在不同的方法。
    只复制表结构,则表里面就没有东西,但是可以通过desc命令查看表的结构

    3.表重命名

    在oracle数据库中,所有的数据实际上都是通过数据字典保存的,如:
    SELECT * FROM TAB;
    以上就是一个数据字典。在oracle中,提供了四种类型的数据字典,最常用的是:cdb_、dba_、user_、all_
    下面演示一个user_tables数据字典
    SELECT * FROM USER_TABLES;

    也就是说oracle中的所有数据都是按照文件保存的,那么所有的内容都会在数据字典中注册,所谓的修改表名称实际上就是修改一条数据。

    修改表名称的方法如下:
    RENAME 旧的表名称 TO 新的表名称;
    如:
    #修改member表为person
    RENAME MEMBER TO PERSON;
    #确认修改完成
    SELECT * FROM TAB;
    在这里插入图片描述
    #查询表中的数据
    SELECT * FROM PERSON;
    这种方法是oracle数据库所独有,了解即可。

    4.截断表

    在之前讲过的删除表数据的操作,使用的是DELETE操作,但是这种删除操作本身有一个特点就是可以进行事务的回滚,也就是说删除之后并不会立即释放数据的资源,所有可以使用rollback进行数据回滚;如果现在希望彻底释放一张表所占用的全部资源(表空间,索引等等)就可以使用截断表的语法,语法如下:
    TRUNCATE TABLE 表名称

    #截断PERSON表
    TRUNCATE TABLE PERSON;
    #回滚事务
    ROLLBACK;
    #再次查询表中数据
    SELECT * FROM PERSON;
    在这里插入图片描述

    5.表的删除

    表的删除操作指的是数据库对象的删除,使用DROP语句,语法如下:
    DROP TABLE 表名称

    #删除person表
    DROP TABLE PERSON;
    #查询该表会出现错误提示
    SELECT * FROM PERSON;
    在这里插入图片描述

    6.闪回技术 flashback

    在oracle10g之后,为了预防用户的误删除表操作,专门提供了回收站的功能,用户所删除的表默认情况下会在一个回收站之中保存,而用户也可以通过回收站进行表的恢复,所以此技术称为闪回(FLASHBACK).
    #查看回收站
    SHOW RECYCLEBIN;
    在这里插入图片描述
    这个时候可以发现所有已经删除的表都在回收站之中保存,那么下面就可以使用如下的语法进行表的恢复;
    FLASHBACK TABLE 表名称 TO BEFORE DROP;

    #恢复MYEMP表
    FLASHBACK TABLE MYEMP TO BEFORE DROP;
    在这里插入图片描述当然,也可以直接删除回收站中的一些数据表,语法如下:
    PURGE TABLE 表名称;

    #删除回收站中的member表
    PURGE TABLE EMPNULL;
    #查看回收站中的表
    SHOW RECYCLEBIN;
    这时发现少了一个EMPNULL表:
    在这里插入图片描述
    #清空回收站
    PURGE RECYCLEBIN;
    #查询所有表
    SELECT * FROM TAB;
    在这里插入图片描述
    如果想在删除表的时候进行直接删除,而不进入回收站,可以在删除的时候增加PURGE关键字。
    #删除MYEMP表不进入回收站
    DROP TABLE MYEMP PURGE;
    #查询回收站;
    SHOW RECYCLEBIN;
    在这里插入图片描述

    7.修改表结构

    如果一张建立好的数据表,发现其初期的结构已经不满足后期的使用要求,则可以进行表的修改操作,而表的修改操作实际上就是数据库对象的修改操作,使用ALTER指令完成,例如,有以下一张表:
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50)
    );

    #检查表结构
    DESC MEMBER;
    在这里插入图片描述增加数据:
    INSERT INTO MEMBER(MID,NAME) VALUES (1,‘ZHANGSAN’);
    INSERT INTO MEMBER(MID,NAME) VALUES (2,‘LISI’);
    在这里插入图片描述现在希望可以向表中增加字段,所以此时可以采用如下的语法完成:
    ALTER TABLE 表名称 ADD(列名称 数据类型 [DEFAULT 默认值],
    列名称 数据类型 [DEFAULT 默认值],…);

    #为member表增加字段
    ALTER TABLE MEMBER ADD(AGE NUMBER(3),BIRTHDAY DATE DEFAULT SYSDATE);
    在这里插入图片描述
    如果增加的数据列没有默认值,则所有已有的数据的列的内容都是null,而如果增加的列指定了DEFAULT默认值的话,则所有已有的数据列都是设置的默认值。
    也可以修改已有的表结构,此时的语法如下:
    ALTER TABLE 表名称 MODIFY(列名称 数据类型 [DEFAULT 默认值],
    列名称 数据类型 [DEFAULT 默认值],…);

    #将name字段的默认值定义为“wuming”
    ALTER TABLE MEMBER MODIFY(NAME VARCHAR2(100) DEFAULT ‘WUMING’);

    #修改完成后,再进行增加数据:
    INSERT INTO MEMBER(MID) VALUES (46);
    #注意查看结果
    SELECT * FROM MEMBER;
    在这里插入图片描述
    #修改字段名(无数据)
    ALTER TABLE 表名称 RENAME COLUMN 原列名称 TO 新列名称;

    #删除表中字段的默认值
    ALTER TABLE MEMBER MODIFY NAME DEFAULT NULL;

    2.约束

    表建立完成后,并不能检查表中的数据是否合法,如果想要针对表中的数据做一些过滤的话,则可以通过约束完成,约束的主要功能是保证表中的数据合法性,按照约束的分类,一共有五种约束:
    非空约束
    唯一约束
    主键约束
    检查约束
    外键约束

    1.非空约束/NOT NULL/NK

    非空约束保证数据不能为空;
    当数据表中的某个字段上的内容不希望设置为null的话,则可以使用NOT NULL进行指定。
    #定义一张数据表
    DROP TABLE MEMBER PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(20) NOT NULL
    );
    #因为存在了“NOT NULL”约束,所以下面插入两组数据;
    #正确的数据:
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘ZHANGSAN’);
    INSERT INTO MEMBER(MID,NAME) VALUES(NULL,‘LISI’);
    #完成测试数据的插入后,查询表的内容
    SELECT * FROM MEMBER;
    在这里插入图片描述#错误的数据:
    INSERT INTO MEMBER(MID,NAME) VALUES(9,NULL);
    INSERT INTO MEMBER(MID) VALUES(10);
    执行上面的语句后,提示如下报错:
    ORA-01400: cannot insert NULL into (“SCOTT”.“MEMBER”.“NAME”)

    2.唯一约束/UNIQUE/UK

    唯一约束指的是某一个列上的数据是不允许重复的,如邮件地址。
    #定义一张数据表
    DROP TABLE MEMBER PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(50) UNIQUE
    );
    #插入正确的数据:
    INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(1,‘ZHANGSAN’,‘ZHANGSAN.163.COM’);
    INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(2,‘LISI’,NULL);
    #插入错误的数据:
    INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(3,‘WANGWU’,‘ZHANGSAN.163.COM’);
    结果报错:
    在这里插入图片描述该错误信息并不太详细,没有明确说明违反了哪个唯一性约束,所以我们可以通过添加约束,如下:

    #添加约束条件,
    DROP TABLE MEMBER PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    EMAIL VARCHAR2(50),
    CONSTRAINT UK_EMAIL UNIQUE(EMAIL)
    );

    #插入正确的数据:
    INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(1,‘ZHANGSAN’,‘ZHANGSAN@163.COM’);
    INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(2,‘LISI’,NULL);

    #插入错误的数据:
    INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(3,‘WANGWU’,‘ZHANGSAN@163.COM’);
    插入上面的数据后,提示如下错误:
    ORA-00001: unique constraint (SCOTT.UK_EMAIL) violated
    自此,约束有了自定义的名字,可以很明确的提示用户。
    在这里插入图片描述

    3.主键约束/PRIMARY KEY/PK

    主键约束=非空约束+唯一约束。在之前设置唯一约束的时候发现可以设置为null,而如果使用了主键约束之后则不能为null,而主键一般作为数据的唯一的一个标记出现。例如人员的编号。
    #建立主键约束
    DROP TABLE MEMBER PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL
    );

    #增加正确的数据
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘LISI’);

    #插入错误的数据:主键为null
    INSERT INTO MEMBER(MID,NAME) VALUES(null,‘LISI’);
    出现如下错误提示:
    ORA-01400: cannot insert NULL into (“SCOTT”.“MEMBER”.“MID”)
    在这里插入图片描述但是信息不明确,因为没有约束名称

    #添加约束,并设置约束名称
    DROP TABLE MEMBER PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    CONSTRAINT PK_MID PRIMARY KEY (MID)
    );
    #插入正确数据
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘LISI’);

    #插入重复数据
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘LISI’);
    出现以下错误提示,可以看到错误提示很明显。
    ORA-00001: unique constraint (SCOTT.PK_MID) violated

    3.1.复合主键

    从开发角度来说,一张表一般都只有一个主键,但是从SQL语法的规定来说,一张表可以设置多个主键,此种做法叫做复合主键,如:

    #创建复合主键
    DROP TABLE MEMBER PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    CONSTRAINT PK_MID_NAME PRIMARY KEY (MID,NAME)
    );
    在复合主键的使用之中,只有两个字段的内容都一样的情况下,才被称为重复数据。

    #插入正确的数据
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘ZHANGSAN’);
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘LISI’);
    INSERT INTO MEMBER(MID,NAME) VALUES(2,‘LISI’);
    在这里插入图片描述
    #插入错误数据
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘ZHANGSAN’);
    出现以下错误提示
    ORA-00001: unique constraint (SCOTT.PK_MID_NAME) violated
    在这里插入图片描述
    注意:正常开发情况下,一张表只设置一个主键。

    4.检查约束/CHECK/CK

    检查约束指为表中的数据增加一些过滤条件,如:
    设置年龄的时候范围为0-250
    设置性别的时候为男、女和其他

    #设置检查约束
    DROP TABLE MEMBER PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    SEX VARCHAR2(10) NOT NULL,
    AGE NUMBER(3),
    CONSTRAINT PK_MID PRIMARY KEY (MID),
    CONSTRAINT CK_SEX CHECK (SEX IN (‘NAN’,‘NV’,‘QITA’)),
    CONSTRAINT CK_AGE CHECK (AGE BETWEEN 0 AND 250)
    );
    在这里插入图片描述
    #增加正确的数据
    INSERT INTO MEMBER(MID,NAME,SEX,AGE) VALUES (1,‘ZHANGSAN’,‘NAN’,‘200’);
    在这里插入图片描述
    #增加错误的数据,性别
    INSERT INTO MEMBER(MID,NAME,SEX,AGE) VALUES (2,‘LISI’,‘RENYAO’,‘200’);
    出现以下错误提示:
    ORA-02290: check constraint (SCOTT.CK_SEX) violated

    #增加错误的数据,年龄
    INSERT INTO MEMBER(MID,NAME,SEX,AGE) VALUES (3,‘WANGWU’,‘NV’,‘500’);
    出现以下错误提示;
    ORA-02290: check constraint (SCOTT.CK_AGE) violated

    检查的操作就是对输入的数据进行一个过滤。

    5.外键约束/FOREIGN KEY,REFERENCES MEMBER/FK

    之前的约束都是在单张表中进行的,而外键约束是在两张表中进行的,这两张表是存在父子关系的,即子表中的某个字段的取值范围是由父表所决定。
    例如,多个人有多本书,创建数据表
    #创建两张表,如下
    DROP TABLE MEMBER PURGE;
    DROP TABLE BOOK PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    CONSTRAINT PK_MID PRIMARY KEY (MID)
    );
    CREATE TABLE BOOK(
    BID NUMBER,
    TITLE VARCHAR2(50) NOT NULL,
    MID NUMBER,
    CONSTRAINT PK_BID PRIMARY KEY (BID)
    );
    #插入几条数据
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘ZHANGSAN’);
    INSERT INTO MEMBER(MID,NAME) VALUES(2,‘LISI’);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(101,‘JAVA’,1);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(102,‘ANDROID’,2);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(103,‘PYTHON’,2);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(104,‘EJB’,1);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(105,‘AJAX’,1);
    #为了验证这个数据是否有意义,最简单的做法就是写查询语句来查看数据
    #统计每个人拥有书的数量
    SELECT M.MID,M.NAME,COUNT(B.BID)
    FROM MEMBER M,BOOK B
    WHERE M.MID=B.MID
    GROUP BY M.MID,M.NAME;
    在这里插入图片描述
    #查询出每个人的编号,姓名和用户有书的名称
    SELECT M.MID,M.NAME,B.TITLE
    FROM MEMBER M,BOOK B
    WHERE M.MID=B.MID;
    在这里插入图片描述现在的book.mid字段应该是与member.mid字段相关联的,但是由于本程序没有设置约束,所以以下的数据也是可以增加的。
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(108,‘PS’,3);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(109,‘C’,9);
    在这里插入图片描述在这里插入图片描述
    现在增加了两条数据,也可以保存在数据库中,但member表中却没有book.mid中的3和9.需要使用外键约束来解决。
    让book.mid的字段的取值由member.mid所决定,如果member.mid的数据真实存在,则表示为正常数据,可以更新。
    DROP TABLE MEMBER PURGE;
    DROP TABLE BOOK PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    CONSTRAINT PK_MID PRIMARY KEY (MID)
    );
    CREATE TABLE BOOK(
    BID NUMBER,
    TITLE VARCHAR2(50) NOT NULL,
    MID NUMBER,
    CONSTRAINT PK_BID PRIMARY KEY (BID),
    CONSTRAINT FK_MID FOREIGN KEY (MID) REFERENCES MEMBER(MID)
    );

    #该表较之前的多添加了约束,下面来插入数据。
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘ZHANGSAN’);
    INSERT INTO MEMBER(MID,NAME) VALUES(2,‘LISI’);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(101,‘JAVA’,1);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(102,‘ANDROID’,2);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(103,‘PYTHON’,2);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(104,‘EJB’,1);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(105,‘AJAX’,1);
    #输入以下的数据则会报错
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(108,‘PS’,3);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(109,‘C’,9);
    提示以下报错:
    ORA-02291: integrity constraint (SCOTT.FK_MID) violated - parent key not found
    意思就是member表中没有输入的参考外键;
    在这里插入图片描述
    因为member.mid没有指定的数据,所以book.mid如果数据有错误,则无法执行更新操作。
    使用外键最大好处是控制了子表中某些数据的取值范围,但是同样带来了不少的问题,如:

    1、
    #删除member表中mid为1的数据
    DELETE FROM MEMBER WHERE MID=1;
    提示以下错误信息:
    ORA-02292: integrity constraint (SCOTT.FK_MID) violated - child record found
    此时只能删除子表记录之后再删除父表记录,如:
    DELETE FROM BOOK WHERE MID=1;
    DELETE FROM MEMBER WHERE MID=1;
    在这里插入图片描述
    在这里插入图片描述
    但是这种操作明显不方便,如果说想在主表数据删除之后,子表中对应的数据也可以删除的话,则可以在建立外键的时候指定一个级联删除的功能,修改数据库创建脚本;

    注意:先删除父表会提示以下错误信息:
    在这里插入图片描述
    ORA-02449: unique/primary keys in table referenced by foreign keys
    所以,需要先删除子表,再删除父表。

    DROP TABLE BOOK PURGE;
    DROP TABLE MEMBER PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    CONSTRAINT PK_MID PRIMARY KEY (MID)
    );
    CREATE TABLE BOOK(
    BID NUMBER,
    TITLE VARCHAR2(50) NOT NULL,
    MID NUMBER,
    CONSTRAINT PK_BID PRIMARY KEY (BID),
    CONSTRAINT FK_MID FOREIGN KEY (MID) REFERENCES MEMBER(MID) ON DELETE CASCADE
    );

    #执行完成之后,再次插入数据;
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘ZHANGSAN’);
    INSERT INTO MEMBER(MID,NAME) VALUES(2,‘LISI’);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(101,‘JAVA’,1);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(102,‘ANDROID’,2);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(103,‘PYTHON’,2);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(104,‘EJB’,1);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(105,‘AJAX’,1);
    在这里插入图片描述
    #删除主表
    DELETE FROM MEMBER WHERE MID=1;

    #该表中的数据删除成功,然后查看booK表
    SELECT * FROM BOOK;
    已无编号为1的人员的相关信息。
    在这里插入图片描述
    此时由于存在级联删除的操作,所以主表中的数据删除之后,对应的子表中的数据也都会被同时删除。

    2、删除数据的时候,让子表中对应的数据设置为null
    当主表中的数据删除后,对应的子表中的数据相关项希望设置为null,而不是删除。可以继续修改数据库的创建脚本:

    DROP TABLE BOOK PURGE;
    DROP TABLE MEMBER PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    CONSTRAINT PK_MID PRIMARY KEY (MID)
    );
    CREATE TABLE BOOK(
    BID NUMBER,
    TITLE VARCHAR2(50) NOT NULL,
    MID NUMBER,
    CONSTRAINT PK_BID PRIMARY KEY (BID),
    CONSTRAINT FK_MID FOREIGN KEY (MID) REFERENCES MEMBER(MID) ON DELETE SET NULL
    );
    INSERT INTO MEMBER(MID,NAME) VALUES(1,‘ZHANGSAN’);
    INSERT INTO MEMBER(MID,NAME) VALUES(2,‘LISI’);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(101,‘JAVA’,1);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(102,‘ANDROID’,2);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(103,‘PYTHON’,2);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(104,‘EJB’,1);
    INSERT INTO BOOK(BID,TITLE,MID) VALUES(105,‘AJAX’,1);
    在这里插入图片描述
    #再次执行删除操作
    DELETE FROM MEMBER WHERE MID=1;

    #查看MEMBER表
    SELECT * FROM MEMBER;

    #查看BOOK表
    SELECT * FROM BOOK;
    在这里插入图片描述

    3、删除父表之前必须先删除对应的子表,否则无法删除

    #直接删除member表
    DROP TABLE MEMBER PURGE;
    提示以下的错误提示:
    ORA-02449: unique/primary keys in table referenced by foreign keys

    #只有先删除子表,再删除父表才可以;
    DROP TABLE BOOK PURGE;
    DROP TABLE MEMBER PURGE;
    但是这样很麻烦,对于未知的数据库,如果要按照这样的顺序来执行,必须知道表之间的父子关系。在oracle中,提供了一个强制性删除表的操作,不再关心约束,如下:

    #强制删除表,不再关心约束
    DROP TABLE MEMBER CASCADE CONSTRAINT PURGE;
    这样删除表之后,子表还在。

    #删除完member表之后,再删除book表
    DROP TABLE BOOK CASCADE CONSTRAINT PURGE;

    更好的做法:
    在以后进行数据表删除的时候,最好是先删除子表,再删除父表。

    6.修改约束

    约束本身也属于数据库对象,那么也肯定可以进行修改操作,而且只要是修改都使用ALTER指令,约束的修改主要指的是以下两种操作:
    为表增加约束:
    ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段);
    删除表中的约束
    ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;
    如果要维护约束,肯定需要一个正确的名字才可以。(注意非空约束)

    #创建一张新表,不含约束。
    DROP TABLE MEMBER CASCADE CONSTRAINT PURGE;
    CREATE TABLE MEMBER(
    MID NUMBER,
    NAME VARCHAR2(50) NOT NULL,
    AGE NUMBER(3)
    );

    #为表增加主键约束。
    ALTER TABLE MEMBER ADD CONSTRAINT PK_MID PRIMARY KEY(MID);

    #增加测试数据
    INSERT INTO MEMBER(MID,NAME,AGE) VALUES(1,‘ZHANGSAN’,30);
    INSERT INTO MEMBER(MID,NAME,AGE) VALUES(2,‘LISI’,300);

    #增加年龄约束
    ALTER TABLE MEMBER ADD CONSTRAINT CK_AGE CHECK(AGE BETWEEN 0 AND 250);
    执行后产生了如下错误提示:
    ORA-02293: cannot validate (SCOTT.CK_AGE) - check constraint violated
    因为表中已经存在了违反约束的数据,所以无法添加约束。

    #删除mid为2的数据
    SQL> delete from member where mid=2;

    #增加年龄约束
    ALTER TABLE MEMBER ADD CONSTRAINT CK_AGE CHECK(AGE BETWEEN 0 AND 250);
    可以增加

    #增加测试数据
    INSERT INTO MEMBER(MID,NAME,AGE) VALUES(2,‘LISI’,300);
    在这里插入图片描述

    #删除member表中的mid的主键约束
    ALTER TABLE MEMBER DROP CONSTRAINT PK_MID;

    #增加重复的数据
    INSERT INTO MEMBER(MID,NAME,AGE) VALUES(1,‘ZHANGSAN’,30);
    可以发现,该语句执行成功
    在这里插入图片描述
    跟表结构一样,约束最好不要修改,在表建立的同时一定要将约束定义好,后期使用中建议不要修改。
    问题?创建约束时没有添加名称,怎么查询?

    6.1.查询约束

    SELECT CONSTRAINT_NAME,SEARCH_CONDITION,TABLE_NAME FROM USER_CONSTRAINTS;

    #删除之前自己创建的所有的表:
    DROP TABLE MEMBER PURGE;
    DROP TABLE NATION PURGE;
    DROP TABLE BOOK PURGE;

    #查询部分字段
    SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS;
    查看查询结果只有约束的名字,而没有约束的是哪个字段

    #查询另一张数据字典表
    SELECT * FROM USER_CONS_COLUMNS;
    查看COLUMN_NAME字段,该字段就是约束的字段名称

    #再次进行查询该表的其他字段
    SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS;

    #该查询结果显示不方便查看,使用格式化来调整
    COL OWNER FOR A15;
    COL CONSTRAINT_NAME FOR A15;
    COL TABLE_NAME FOR A15;
    COL COLUMN_NAME FOR A15;
    SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS;
    至此查询结果效果好

    3.视图

    在我们之前学过的所有的SQL语法之中,查询操作是最麻烦的,为了更好更快地使用数据库,我们需要创建视图,视图中包含了一些复杂的SQL语句。

    视图创建语法:
    CREATE [OR REPLACE] VIEW 视图名称 AS 子查询

    #创建一张视图
    CREATE VIEW MYVIEW AS
    SELECT D.DEPTNO,D.DNAME,D.LOC,COUNT(E.EMPNO) COUNT,NVL(AVG(E.SAL),0) AVG
    FROM EMP E,DEPT D
    WHERE E.DEPTNO(+)=D.DEPTNO
    GROUP BY D.DEPTNO,D.DNAME,D.LOC;

    #scott用户没有创建视图的权限,如果直接创建视图,会返回以下错误提示:
    ORA-01031: insufficient privileges
    #先使用sys用户登录
    SQLPULS / AS SYSDBA;
    #使用以下命令放权
    GRANT CREATE VIEW TO c##SCOTT;

    #执行下面语句查询myview
    SELECT * FROM MYVIEW;
    我们发现,通过一个简单的视图查询操作,就可以完成之前的复杂SQL语句的功能,所以视图就是包装了的SQL查询操作。

    #创建一张包含简单查询语句的视图
    CREATE VIEW MYVIEW AS
    SELECT * FROM EMP WHERE DEPTNO=20;
    执行上面语句会返回以下错误提示:
    ORA-00955: name is already used by an existing object

    #drop掉之前创建的视图myview
    DROP VIEW MYVIEW;
    #然后再次执行
    CREATE VIEW MYVIEW AS
    SELECT * FROM EMP WHERE DEPTNO=20;
    在这里插入图片描述
    语句执行成功,上面的操作实际上是属于一个视图的替换操作,所以此时可以使用另外一种语法:

    CREATE OR REPLACE VIEW MYVIEW AS
    SELECT * FROM EMP WHERE DEPTNO=20;
    表示如果视图存在则替换,不存在则创建。
    在这里插入图片描述

    创建视图有两个选项:

    选项一:WITH CHECK OPTION
    以上创建的视图,是存在一个创建条件的,如“WHERE DEPTNO=20”,那么如果更新该条件呢?

    #更新条件
    UPDATE MYVIEW SET DEPTNO=30 WHERE EMPNO=7369;

    #查询该视图,发现SMITH用户的信息已不存在,该用户的部门信息已经更改。
    SELECT * FROM MYVIEW;
    此时更新的是一张视图,但是视图本身并不是一个具体的数据表,而且现在更新的操作又是视图的创建条件,这样的做法不可取,为了解决这个问题,可以加入WITH CHECK OPTION.

    #回滚数据表:
    ROLLBACK;

    #创建一个视图
    CREATE OR REPLACE VIEW MYVIEW AS
    SELECT * FROM EMP WHERE DEPTNO=20
    WITH CHECK OPTION;

    #再次更新视图的更新操作:
    UPDATE MYVIEW SET DEPTNO=30 WHERE EMPNO=7369;
    UPDATE MYVIEW SET sal=5000 WHERE EMPNO=7369;

    #出现以下错误提示:
    ORA-01402: view WITH CHECK OPTION where-clause violation
    现在已经无法更新视图的创建条件。
    在这里插入图片描述
    选项二:WITH READ ONLY
    虽然使用WITH CHECK OPTION可以保证视图的创建条件不被更新,但是其他的字段却允许更新。

    #更新一个数据
    UPDATE MYVIEW SET SAL=9000 WHERE EMPNO=7369;

    #查询视图,发现SMITH的SAL值已经更改,和上一个问题一样,视图本身不是具体的真实数据,而是一些查询语句,所以这样更新并不合理,据此,我们可以在创建视图的时候建议设置为只读视图。
    CREATE OR REPLACE VIEW MYVIEW AS
    SELECT * FROM EMP WHERE DEPTNO=20
    WITH READ ONLY;

    #再次进行更新操作
    UPDATE MYVIEW SET SAL=9000 WHERE EMPNO=7369;
    返回以下错误提示:
    ORA-42399: cannot perform a DML operation on a read-only view

    以上是一个简单的的操作语句视图,如果视图中的查询语句是统计操作,则根本就不可能更新,如下:

    #创建一个视图:
    CREATE OR REPLACE VIEW MYVIEW AS
    SELECT D.DEPTNO,D.DNAME,D.LOC,COUNT(E.EMPNO) COUNT,AVG(E.SAL) AVG
    FROM EMP E,DEPT D
    WHERE E.DEPTNO(+)=D.DEPTNO
    GROUP BY D.DEPTNO,D.DNAME,D.LOC;

    #查看视图查询结果
    SELECT * FROM MYVIEW;

    #执行更新操作;
    UPDATE MYVIEW SET COUNT=30 WHERE DEPTNO=10;
    执行之后,返回如下错误提示:
    ORA-01732: data manipulation operation not legal on this view
    该信息是统计而来的,根本就不可能更新。
    项目中,视图的数量可能多于表的数量。

    #查看视图的字段
    desc myview

    #查看视图具体的定义
    SELECT * FROM USER_VIEWS;

    展开全文
  • Oracle的创建及管理

    2018-06-08 16:18:00
    对于数据库而言实际上每一张表都表示的是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表、视图、索引、序列、约束等等,属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为...

    对于数据库而言实际上每一张表都表示的是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表、视图、索引、序列、约束等等,都属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为以下三类语法:

    • 创建对象:CREATE 对象名称 …;
    • 删除对象:DROP 对象名称 …;
    • 修改对象:ALTER 对象名称 …;

    一、常用的数据字段

    每一张数据表实际上都是由若干个字段所组成,而每一个字段都会有其对应的数据类型,而在Oracle之中,常用的数据类型有如下几种:

    数据类型

    关键字

    描述

    1

    字符串

    VARCHAR2(n)

    其中n表示的是字符串所能保存的最大长度,基本上保存200个左右的内容

    2

    整数

    NUMBER(n)

    表示最多为n位的整数,有时候也可以使用INT代替

    3

    小数

    NUMBER(n,m)

    其中m为小数位,n-m为整数位,有时候也可以使用FLOAT代替

    4

    日期

    DATE

    存放日期-时间

    5

    大文本

    CLOB

    可以存储海量文字(4G),例如存储《三国演艺》、《红楼梦》

    6

    大对象

    BLOB

    存放二进制数据,例如:电影、MP3、图片、文字

    一般在开发之中使用最多的:VARCHAR2()、NUMBER、DATE、CLOB,而对于BLOB字段一般使用较少,首先BLOB可以存放4G的二进制数据,但是存放进去之后,一是数据库过于庞大,二是读取不方便;

    二、表的创建

    如果现在要想进行表的创建,可以使用如下的操作语法:

    CREATE TABLE 表名称 (
        字段1 数据类型 [DEFAULT默认值],
        字段2 数据类型 [DEFAULT默认值],
        … …,
        字段n 数据类型 [DEFAULT默认值]
    );

    下面创建一张成员表(member),有如下保存的信息:姓名、年龄、生日、个人简介。

    CREATE TABLE member (
         name VARCHAR2(50) DEFAULT '无名氏',
         age NUMBER(3),
         birthday DATE DEFAULT SYSDATE,
         content CLOB
    );

    表创建成功之后,下面开始向表中增加数据:

    INSERT INTO member(name,age,birthday,content) VALUES('张三',20,TO_DATE('1990-08-12','yyyy-mm-dd'),'一个好人');
    INSERT INTO member(age,content) VALUES(20,'一个好人');

    一定要再次记住,表的创建是属于数据库对象的创建,所以使用的是CREATE语法。

    三、表的复制

    在之前学习过表的复制操作,下面给出其完整的操作语法:

    CREATE TABLE 复制表名称 AS 子查询;

    范例:复制一张只包含20部门雇员信息的表

    CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20;

    范例:现在要求将emp表的表结构复制出来,不要数据 —— 写一个永远满足不了的条件即可。

    CREATE TABLE empnull AS SELECT * FROM emp WHERE 1=2;

    但是以上的语法只是Oracle数据库所支持的操作,其他的数据库语法上会有一些区别。

    四、为表重命名

    在Oracle数据库之中,所有的数据实际上都是通过数据字典保存的,例如,在之前曾经使用过如下的一种查询:

    SELECT * FROM tab;

    以上就是一个数据字典,而在Oracle之中,提供了三种类型的数据字典,最常用的是:dba_、user_,所以下面查询一个user_tables数据字典:

    SELECT * FROM user_tables;

    也就是说Oracle中的所有数据都是按照文件保存的,那么所有的内容都会在数据字典中注册,既然这样,所谓的修改表名称实际上对于Oracle而言就相当于修改一条数据而已,而修改表名称的方法如下:

    RENAME 旧的表名称 TO 新的表名称;

    范例:将member表更名为person表

    RENAME member TO person;

    但是这种操作由于是Oracle数据库所独有的一种特性,所以了解即可,不用做深入的掌握。

    五、截断表

    在之前曾经讲解过一个删除表数据的操作,使用的是DELETE操作,但是这种删除操作本身有一个特点,即:可以进行事务的回滚,也就是说删除之后并不会立刻释放数据的资源,如果现在希望彻底释放掉一张表所占用的全部资源(表空间、索引等等)就可以使用截断表的语法,此语法如下:

    TRUNCATE TABLE 表名称;

    范例:截断person表

    TRUNCATE TABLE person;

    但是这种语法本身只是Oracle所有的,所以只做了解即可。

    六、表的删除

    表的删除操作指的是数据库对象的删除,既然是删除则使用DROP语句,删除表的语法如下:

    DROP TABLE 表名称;

    范例:删除person表

    DROP TABLE person;

    七、Oracle 10g的新特性:闪回技术

    在Oracle 10g之后,为了预防用户的误删除表操作,专门提供了回收站的功能,用户所删除的表默认情况下会在一个回收站之中保存,而用户也可以通过回收站,进行表的恢复,所以此技术称为闪回(FLASHBACK);

    范例:查看回收站

    SHOW RECYCLEBIN;

    这个时候可以发现所有已经删除的表都在回收站之中保存,那么下面就可以使用如下的语法恢复表:

    FLASHBACK TABLE 表名称 TO BEFORE DROP;

    范例:恢复myemp表

    FLASHBACK TABLE myemp TO BEFORE DROP;

    当然,现在也可以直接删除掉回收站中的一些数据表,语法如下:

    PURGE TABLE 表名称;

    范例:删除回收站中的person表

    PURGE TABLE person;

    范例:清空回收站

    PURGE RECYCLEBIN;

    如果现在希望删除一张表,而又不希望其进入到回收站之中,则可以在删除的时候增加PURGE。

    DROP TABLE myemp PURGE;

    这种技术是在Oracle 10g之后才有的,而Oracle 11g也是存在的。

    问题:现在在回收站之中存在了一张tab表,而后又建立了一张tab表,那么如果从回收站中恢复的话,可以吗?

    答:无法恢复。

    八、修改表结构

    如果一张建立好的数据表,发现其初期的结构已经不满足于后期的使用要求,则可以进行表的修改操作,而表的修改操作实际上就是数据库对象的修改操作,使用ALTER指令完成,例如,现在有如下一张表:

    CREATE TABLE member (
         mid NUMBER,
         name VARCHAR2(50)
    );

    现在希望可以向表中增加字段,所以此时可以采用如下的语法完成:

    ALTER TABLE 表名称 ADD(列名称 数据类型 [DEFAULT 默认值],
      列名称 数据类型 [DEFAULT 默认值],…);

    范例:为member表增加字段

    ALTER TABLE member ADD(age NUMBER(3), birthday DATE DEFAULT SYSDATE);

    如果增加的数据列没有默认值,则所有已有的数据的列的内容都是null,而如果增加的列指定了DEFAULT默认值的话,则所有已有的数据列都是设置的默认值。

    现在也可以修改已有的表结构,此时的语法如下:

    ALTER TABLE 表名称 MODIFY(列名称 数据类型 [DEFAULT 默认值],
      列名称 数据类型 [DEFAULT 默认值],…);

    范例:将name字段的默认值定义为无名氏

    ALTER TABLE member MODIFY(name VARCHAR2(100) DEFAULT '无名氏');

    虽然在SQL语法之中以及Oracle数据库之中,都给出了修改表结构的操作,但是这种操作能不使用就不使用,从大型数据库来讲,世界上性能最高的数据库是IBM DB2,但是IBM DB2本身有一个平台的限制问题,所以如果说是跨平台的数据库则是Oracle数据库的性能最高。

    在IBM DB2数据库之中是不允许修改表结构的,即:表建立完成之后就不能修改了,所以以后在开发之中也尽可能的不要去修改表结构。

    九、思考题

    现在要求建立一张nation表,表中有一个name字段,里面保存四条记录:中国、美国、巴西、荷兰,要求通过查询实现如下的操作效果:

    中国         美国

    中国         巴西

    中国         荷兰

    美国         巴西

    美国         中国

    美国         荷兰

    剩下的依次类推,现在要求建立新的表并完成此查询的操作。

    本题目的主要目的并不是在于查询的编写上,而是在于规范化数据库创建脚本的格式,以后只要是碰到了类似的要求,首先必须编写一个数据库创建脚本,这个脚本的要求如下:

    1、  本文件的文件名称后缀必须是“*.sql”;

    2、  先删除相应的数据表;

    3、  编写创建表的语句;

    4、  增加测试数据;

    5、  提交事务;

    --1、删除表
    DROP TABLE nation PURGE;
    --2、创建表
    CREATE TABLE nation(
        name VARCHAR2(50)
    );
    --3、测试数据
    INSERT INTO nation(name) VALUES('中国');
    INSERT INTO nation(name) VALUES('美国');
    INSERT INTO nation(name) VALUES('巴西');
    INSERT INTO nation(name) VALUES('荷兰');
    --4、事务提交
    COMMIT;

    本程序如果要想完成依靠笛卡尔积完成,属于表的自身关联。

    SELECT n1.name,n2.name
    FROM nation n1,nation n2
    WHERE n1.name<>n2.name;

    而且以后如果面试之中,出现了一些复杂的查询,建议把脚本写全了。

    转载于:https://www.cnblogs.com/lc1776/p/9156252.html

    展开全文
  • 对于数据库而言实际上每一张表都表示是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表,视图,索引,序列,约束等等,属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为...
  • 对于数据库而言实际上每一张表都表示的是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表、视图、索引、序列、约束等等,属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为...
  • 对于数据库而言实际上每一张表都表示是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表,视图,索引,序列,约束等等,属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为...
  • 对于数据库而言实际上每一张表都表示的是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表、视图、索引、序列、约束等等,属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为...
  • 对于数据库而言实际上每一张表都表示的是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表、视图、索引、序列、约束等等,属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为...
  • 遇到的java面试题

    2019-04-28 23:04:03
    所有字段建立索引可以吗 说一说范式 数据库三范式,根据某个场景设计数据所有字段是不可分解的原子值。 在一个数据库中,一个中只能保存一种数据,不可以把多种数据保存在同一张数据库中。 数据...
  • MySQL设计三范式

    2020-08-20 21:32:30
    任何一张表都应该由主键、并且其他的字段具有原子性(不可再分) 例如一个完整的地址、是可以将它分为多个字段 address xx省xx市xx县 可以分为 province city county xx省 xx市 xx县 范式二 ...
  • 数据库

    2021-03-28 22:32:19
    在MySQL的数据库中,、视图、存储过程和索引等具体存储数据或对数据进行操作的实体被称为数据库对象。 Ø 是包含数据库中所有数据的数据库对象,由行和列组成,用于组织和存储数据。 Ø 字段中每列...
  • 2.一张表一定要明确哪个字段或哪几个字段作为索引,不能重复的。除非是history操作记录,否则所有应该有一个唯一索引。 这不仅有利于提高检索效率,而且有利于理解业务逻辑。 3.尽量把需要的字段先加进去,...
  • SQL语句基础用法大全(DDL)

    千次阅读 2018-08-30 12:07:18
    对于数据库而言实际上每一张表都表示是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表,视图,索引,序列,约束等等,属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为...
  • Oracle-DDL

    2019-06-08 21:30:38
    对于数据库而言实际上每一张表都表示是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表,视图,索引,序列,约束等等,属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为...
  • orcale常用命令

    2009-08-04 00:34:59
    b、将一张表删除语句的格式如下: DORP TABLE 表名; 例:表删除将同时删除表的数据和表的定义 sql>drop table test c、表空间的创建、删除 六、ORACLE逻辑备份的SH文件 完全备份的SH文件:exp_comp.sh rq=` ...
  • oracle bfile blob

    2013-07-18 22:18:00
    优点:将整个图片库全部放到一张表中,方便管理,成为一个整体。 缺点:表很大后所有数据库操作很慢 解决:因为图片查询是查询图片的相关文字信息,所以如果在文字信息  上合理的建立索引,那么将会极大的...
  • oracle bfile blob

    2009-06-20 23:53:43
    首先,图片是binary格式,另外比较大。所以目前有2种方法: 1. 图片放入DB中。 这种方法需要设置blob字段来存放图片。数据量大的时候造成很大。... 上合理的建立索引,那么将会极大的提高检索...
  • sql总结.doc

    2019-08-27 22:08:51
    注:聚簇索引:一张表只能建立一个聚簇索引,以主键建立索引。聚簇索引包括主键索引和二级索引(二级索引是在对非主键字段建立索引后,通过索引找到对应这个非主键字段的主键,再进行主键索引,找到B+树中叶子结点)...
  • 其一、就业面广:全球前100强企业99家在使用ORACLE相关技术,中国政府机构,大中型企事业单位能有ORACLE技术的工程师岗位。 其二、技术层次深:如果期望进入IT服务或者产品公司(类似毕博、DELL、IBM等),...

空空如也

空空如也

1 2 3
收藏数 48
精华内容 19
关键字:

一张表所有字段都建立索引