数据库系统原理学习笔记九(数据定义)

一、数据库模式定义

1.创建数据库：

  使用CREATE DATABASE或CREATE SCHEMA语句

##新建数据库
CREATE DATABASE zpc_test1;
CREATE SCHEMA zpc_test2;
CREATE DATABASE IF NOT EXISTS zpc_test3; 
CREATE SCHEMA IF NOT EXISTS zpc_test4; 
CREATE DATABASE IF NOT EXISTS zpc_test5 DEFAULT CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;	
	
	
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[DEFAULT]CHARACTER SET[=]charset_name|[DEFAULT]COLLATE[=]collation_name
## []标示其内容为可选项；| 用于分隔花括号中的选择项
## IF NOT EXISTS 创建数据库前进行判断
## db_name 标识具体的数据库命名
## 第一个DEFAULT：指定默认值
## CHARACTER SET：指定数据库字符集
## COLLATE 指定字符集的校对规则

2. 查看数据库：

##查看数据库
SHOW {DATABASES | SCHEMAS}
[LIKE’pattern’ | WHERE expr]
##Like关键字用于匹配指定的数据库名称；
##Where从句用于指定数据库名称查询范围的条件。
## []标示其内容为可选项；| 用于分隔花括号中的选择项
SHOW DATABASES;
SHOW SCHEMAS;
SHOW DATABASES LIKE '%zpc%';

3.切换数据库：

##选择数据库
USE db_name;
#从一个数据库“跳转”到另一个数据库。
USE zpc_test;

4.修改数据库

##修改数据库
ALTER{DATABASE|SCHEMA} db_name
[DEFAULT]CHARACTER SET[=]charset_name|[DEFAULT]COLLATE[=]collation_name
## []标示其内容为可选项；| 用于分隔花括号中的选择项

ALTER DATABASE zpc_test DEFAULT CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;

5.删除数据库

##删除数据库
DROP{DATABASE|SCHEMA}[IF EXISTS]db_name;
## []标示其内容为可选项；| 用于分隔花括号中的选择项
DROP DATABASE IF EXISTS zpc_test5;

 

二、表定义

1.创建表：

  数据表是关系数据库中最重要、最基本的数据对象，也是数据存储的基本单位。

  概念：被定义为字段的集合，按行和列的格式来储存的，每一行代表一条记录，每一列代码记录中一个字段的取值。

## 创建表
##切换到zpc_test数据库
use zpc_test;
## 创建表的规则
 CREATE[TEMPORARY] TABLE tbl_name(
   字段名1 数据类型 [长度] [是否可为null,默认值],
   字段名2 数据类型 [长度] [是否可为null,默认值],
   表级完整性约束条件
)[ENGINT=引擎类型] [AUTO_INCREMENT=1：从1开始自增长] [CHARACTER SET=字符] [COLLATE=指定字符集的校对规则];
## 若添加TEMPORARY 则该表为临时表
## 1.整型int 2.浮点型double 3.布尔型bool 4.日期型date 5.时间戳timestamp 6.时间型time 7.定长字符类型char 8.可变长字符varchar
## AUTO_INCREMENT：为整型的列设置自增属性
## DEFAULT：设置默认值
## COMMENT：设置注释
## PRIMARY KEY：主键
## UNIQUE INDEX ：唯一索引
CREATE TABLE `class`  (
  `class_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '班级id',
  `class_name` varchar(50) NOT NULL COMMENT '班级姓名',
  `class_no` varchar(20) NOT NULL COMMENT '班级编号',
  `is_valid` int(1) NOT NULL DEFAULT 0 COMMENT '有效状态：0有效 1无效',
  PRIMARY KEY (`class_id`) USING BTREE, 
  UNIQUE INDEX `unique_class_no`(`class_no`) USING BTREE COMMENT '班级编号唯一'  
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2.更新表：

  使用ALTER TABLE语句，增加或删减列、创建或取消索引、更改原有列的数据类型、重新命名列或表，更改表的评注和表的引擎类型，为表重新创建触发器、存储过程、索引和外键等。

1）ADD COLUMN

##添加一个名为class_slogan的字段，长度为50，不能为空，设置默认值为“好好学须”，并且该字段在class_no后
ALTER TABLE class ADD COLUMN `class_slogan` CHAR (50) NOT NULL DEFAULT '好好学习' AFTER `class_no`;

2）CHANGE COLUMN：修改表中的列名称或数据类型

##将class_slogan的改成slogan，并且修改其字段类型为varchar
ALTER TABLE class CHANGE COLUMN `class_slogan` `slogan` VARCHAR (100);

3）ALTER COLUMN：修改或删除表中指定列的默认值

##将slogan的默认值改成天天向上
ALTER TABLE class ALTER COLUMN `slogan` SET DEFAULT '天天向上';

4）MODIFY COLUMN：只修改指定列的数据类型，不会干涉他的列名

##将slogan的数据类型改成char,并将该字段放到第一列去
ALTER TABLE class MODIFY COLUMN `slogan` CHAR(20) FIRST;

5）DROP COLUMN：删除列

##将slogan的列删除
ALTER TABLE class DROP COLUMN `slogan`;

6）RENAME TO： 修改表名

##将class的表名改成class_info;
ALTER TABLE class RENAME TO class_info;

3.重命名表：

RENAME TABLE class TO class_info;

4.删除表：

DROP [TEMPORARY] TABLE [IF EXISTS] t_name;
## []标示其内容为可选项
DROP TABLE user;

5.查看表

##查看数据库中某表的列
SHOW COLUMNS FROM class_info;
DESCRIBE class_info;
DESC class_info;

 

三、索引定义

1.基本概念：

    就是DBMS根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。

2.索引的弊端：

  1）索引是以文件的形式存储的，如果有大量的索引，索引文件可能比数据文件更快达到最大的文件尺寸。

  2）索引在提高查询效率的同时，会降低更新表的效率。

3.索引分类：

   索引通常被创建成单列索引和组合索引

  1）普通索引 INDEX或者KEY：

  2）唯一性索引QNIQUE：候选码

  3）主键 PRIMARY KEY：

4.索引的创建

CREATE [UNIQUE] INDEX index_name ON tbl_name(index_col_name,…);
## UNIQUE 创建唯一性索引
## index_name 指定索引名
## tbl_name 指定要建立索引的表名
## index_col_name 建立索引的列
##写法1
CREATE INDEX index_class_name ON class_info (class_name);
##写法2
ALTER TABLE class_info ADD INDEX index_class_name1 (class_name);

5.索引的删除

##  DROP INDEX index_name ON tbl_name
## 删除索引
##方法1
DROP INDEX index_name ON class_info;
##方法2
ALTER TABLE class_info DROP INDEX index_class_name;

6.索引的查看

## SHOW {INDEX | INDEXES | KEYS}{FROM | IN} tbl_name[{FROM | IN} db_name] [WHERE expr]
## 查看索引
SHOW INDEX FROM class_info;
SHOW INDEX IN class_info;
SHOW INDEXES FROM class_info;
SHOW INDEXES IN class_info;
SHOW KEYS FROM class_info;
SHOW KEYS IN class_info;