1.数据库对象

• 什么是数据库对象？
  • 数据库对象是数据库的组成部分，数据库对象主要包含：表、索引、视图、存储过程、缺省值、规则、触发器、用户、函数等。
• 表
  • 表是数据库中的一种特殊数据结构，用于存储数据对象以及对象之间的关系，由行和列组成的。
• 索引
  • 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
• 视图
  • 视图是从一个或几个基本表中导出的虚表，可用于控制用户对数据访问。
• 存储过程
  • 存储过程是一组为了完成特定功能的SQL语句的集合。一般用于报表统计、数据迁移等。
• 缺省值
  • 缺省值是当在表中创建列或插入数据时，对没有指定其具体值的列或列数据项赋予事先设定好的值。
• 规则
  • 规矩是对数据库表中数据信息的限制。它限定的是表的列。
• 触发器
  • 触发器是一种特殊类型的存储过程，通过指定的事件触发执行。一般用于数据审计、数据备份等。
• 函数
  • 函数是对一些业务逻辑的封装，已完成特定的功能。函数执行完成后会返回执行结果。

2.DDL分类

DDL（Data Definition Language数据定义语言），用于定义或修改数据库中的对象，主要分为三种类型语句：create、alter和drop。

• create用于创建数据库对象。
• alter用于修改数据库对象的属性。
• drop用于删除数据库对象。

3.定义表

表是数据库中的一种特殊数据结构，用于存储数据对象以及对象之间的关系。所涉及的SQL语句如下表所示。

4.创建表

• 语法格式

  create [temporary] table [if not exists][database_name.]table_name
  {
  relational_properties|[(column_name [default expr [on update expr]][auto_increment][comment 'string'][collate collation_name][inline_constraint]|out_of_line_constraint [,...])] as query
  }
  [physical_properties]
  [table_properties]
  

• 注意
  • 创建当前用户的表，用户需要被授予create table系统权限。
  • 其中，表名、列名（数据类型）在创建表时必须指定。
• 参数说明
  • temporary
• 创建临时表
  • if not exists
• 创建表时，如果表已经存在，则不做改动直接返回；如果表不存在，则创建新表
  • table_name
  • 表名，不能和用户下表重名。
  • relational_properties
  • 表属性，包括列名、类型、行内约束和行外约束。
• 示例
  • 创建表education

    drop table if exists education;
    create table education(
        staff_id int,
        higest_degree char(8) not null ,
        gradute_school varchar(64),
        gradute_date datetime,
        education_note varchar(70)
    );
    

  • 创建分区表training

    drop table if exists training;
    create table training
    (
        staff_id      int not null,
        course_name   char(20),
        course_period datetime,
        exam_date     datetime,
        score         int
    )
        partition by range (staff_id)(
            partition training1 values less than (100),
            partition training2 values less than (200),
            partition training3 values less than (300),
            partition training4 values less than (maxvalue )
            );
    

5.修改表属性

• alter table功能指通过更改、添加、删除列和约束来更改表的定义，功能包括
  • 列的添加、删除、修改、重命名。
  • 约束的添加、删除。
  • 约束的启动和禁用。
  • 修改分区的名称。
• 注意
  • 增加表中列属性时，保证表中无记录。
  • 修改表中列属性时，保证表中数据类型不冲突，如有冲突需要将该列的值置为null。
• 语法格式

  alter table table_name
  {
      | add [column] col_name column_definition
      | add {index|key} [index_name][index_type] (key_part,...) [index_option]
      | add {fulltext | spatial} [index|key][index_name](key,...)[index_option]
      | add [constrant[symbol]] primary key |unique [index|key]
      | drop {check|constrant} symbol
      | alter {check|constrant} symbol [not] enforced
      | drop [column] col_name
      | rename column old_col_name to new_col_name
  }
  

• 示例
  • training表中添加列full_masks

    alter table training add full_masks int;
    

  • 删除列course_period

    alter table training drop course_peried;
    

  • 修改列的数据类型

    alter table training modify course_name varchar(60);
    

  • 添加约束

    alter table training add constrant ch_training check(staff_id>0);
    alter table training add constrant uk_training unique(course_name,staff_id);
    

6.删除表

• 注意
  • 用户需要drop table权限，普通用户不可以删除系统用户对象。
  • 语法格式

    drop [temporary] table [if exists] [database_name.][table_name] [restrict|cascade]
    

• 参数说明
  • cascade
  • restrict
  • 以上两参数暂时无其他意义，为了移植语法。
• 示例
  • 删除表training

    drop table if exists training;
    

  • 删除临时表tmp_training

    drop table if exists tmp_training;
    

7.定义索引

• 索引是对数据库中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。涉及3个SQL语句。

  功能	相关SQL
  创建索引	create index
  修改索引属性	alter index
  删除索引	drop index

• 索引按照索引列数分为单列索引和多列索引，按照索引使用方法可以分为普通索引、唯一索引、函数索引、分区索引、全文索引。

8.创建索引

• 功能描述
  • 在指定的表上创建一个索引。索引可以用来提高数据库查询性能，但是不恰当的使用将导致性能下降。
• 注意
  • 执行该语句的用户需要有index权限，普通用户不可以创建系统用户对象。
• 语法格式

  create [unique|fulltext|spatial] index index_name [index_type] on table_name (key_part,...) [index_option] [algorithm_option|lock_option]
  

  • 子句：

  key_part:{col_name[(length)]|(expr)} [asc|desc]
  index_option:{key_block_size[=]value|index_type|with parser parser_name|comment 'string'|{visible|invisible}}
  index_type:using {btree|hash}
  algorithm_option:algorithm[=]{default|inplace|copy}
  lock_option:lock[=]{default|none|shared|exclusive}
  

• 参数说明
  • unique
    • 创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会导致重复的记录时将生成一个错误。
  • index_name
    • 要创建的索引名。
  • table_name
    • 需要创建索引的表名。
• 在分区表education上创建索引

  drop table if exists education;
  create table education
  (
      staff_id        int not null,
      degree_id       int,
      higest_degree   char(8),
      graduate_school varchar(64),
      graduate_date   datetime,
      education_note  varchar(70)
  )
      partition by list (degree_id)
          (
              partition doctor values in (2),
              partition master values in (1),
              partition undergraduate values in (0)
          );
  -- 创建分区索引
  create index idx_training on education(staff_id asc ,higest_degree);
  

9.修改索引属性

• 语法格式

  alter table table_name{alter index index_name{visible|invisible} | rename index old_name to new_name};
  

• ALTER INDEX index_name{visible|invisible}
  • visible | invisible
  • 默认创建之后为可用状态。使用命令查看：show index from posts;
  • 索引状态分为可用和非可用状态。
• rename index old_name to new_name
  • 对索引进行重命名。
• 示例
  • 创建索引idx_posts

    create index idx_posts on posts(post_id asc,post_name);
    

  • 将索引状态置为可用

    alter table posts alter index idx_posts visible;
    

  • 重命名索引

    alter table posts rename index idx_posts to idx_posts_new;
    

10.删除索引

• 语法格式

  drop index index_name on table_name [algorithm_option | lock_option]
  
  algorithm_option:algorithm [=] {default | inplace | copy}
  lock_option:lock [=] {default | none | shared | exclusive}
  

• 参数说明
  • index_name
• 待删除索引名
  • on table_name
• 删除索引时指定表名
• 示例

  drop index idx_posts on posts;
  

11.定义视图

• 视图是从一个或者几个基本表中导出的虚表，可用于控制用户对数据访问，所涉及的SQL语句，如下所示：

  功能	相关SQL
  创建视图	create view
  删除视图	drop view

• 视图与基本表不同，数据库中仅存放视图的定义，不存放视图对应的数据，数据仍然存放在原来的基本表中。若基本表中的数据发生变化，从视图中查询出的数据也随之改变。从这个意义上，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及其变化。

12.创建视图

• 语法格式

  create [ or replace ] view_name as subquery
  

• 参数说明
  • [or replace]
    • 创建视图时，试图若存在则更新。
  • view_name
    • 视图名称。
  • as subquery
    • 子查询。
• 示例
  • 创建视图training_view，若该视图存在则更新该视图。

    create or replace view training_view as select staff_id,score from training;
    

  • 创建视图training_view并指定视图列别名，若该视图存在则更新该视图。

    create or replace view training_view(staff_id,course_name,exam_date,score,full_masks) as select * from training;
    

  • 查看视图中的数据，语法和查询表一样。

    select * from training_view;
    

  • 查看视图结构。

    describe training_view;
    

13.删除视图

• 语法格式

  drop view [if exists] view_name
  

• 参数说明
  • if exists
    • 视图存在，执行删除。
  • view_name
    • 待删除的视图名。
• 示例

  drop view if exists training_view;
  

14.思考题

• 为某表建立索引，如果对索引进行撤销操作则与之对应的基本表的内容也会被删除（错误）
• SQL语言集数据查询、数据操作、数据定义和数据控制于一体，其中，create、drop、alter语句是实现哪种功能？（数据定义）
• 视图的特点？（视图的列可以来自不同的表；视图是由基本表（实表）产生的；）