跳到主要内容

二.数据库创建与管理

创建数据库​

数据库的基本知识

数据库的类型

  • performance_schema:系统数据库,例如有:mysql,sakila,world,sys等
  • 自建数据库

创建数据库的常用术语

英文中文英文中文
CREATE创建DATABASE数据库
SHOW查看SCHEMA数据库
DROP删除IF假如
EXISTS存在USE使用

创建数据库

创建数据库的SQL语句

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] database_name;

-IF NOT EXISTS:在创建数据库前加上一个判断,只有该数据库目前尚不存在时才执行CREATE DATABASE操作

  • []表示可选,|表示或者
  • database_name:是创建的数据库的名称

需注意的问题

  • MySQL对大小写不敏感,SQL语句中出现大写字母表示MySQL的保留字,小写字母需要自己定义
  • 语句结束一定要用英文状态的分号,不能使用中文状态的

SQL语句

  • 查看数据库:SHOW DATABASES;
  • 选择数据库:USE database_name;
  • 删除数据库:DROP DATABASE database_name;

创建数据库-实例

CREATE DATABASE db_library; --即创建图书管理系统数据库db_library
SHOW DATABASES; --查看数据库
USE db_library; --选择db_library作为当前数据库
CREATE DATABASE libraryinfo; --创建一个libraryinfo的数据库
DROP DETABASE libraryinfo; --删除libraryinfo数据库

小结

  • 创建数据库:CREATE DATABASE database_name;
  • 查看数据库:SHOW DATABASES;
  • 选择数据库:USE database_name;
  • 删除数据库:DROP DATABASE database_name;

数据类型​

数据类型概述

  • 数据类型是数据的一种属性,是决定存储数据的存储格式,有效范围和相应的限制
  • MySQL数据类型主要分为5类
    • 数值类型
      • 整数类型
      • 小数类型
    • 字符串类型
      • CHAR类型
      • VARCHAR类型
      • TEXT类型
    • 时间/日期类型
      • 日期型
      • 时间型
    • 二进制类型
      • BINARY类型
      • BLOB类型
    • 复合类型
      • SET类型
      • ENUM类型

整型

整数类型字节最小值最大值备注
TINYINT1有符号:-128
无符号:0		有符号:127
无符号:255		微整型
SMALLINT2有符号:-32768
无符号:0		有符号:-32767
无符号:65535		小整型
MEDIUMINT3有符号:-8388608
无符号:0		有符号:8388607
无符号:1677215		中整型
INT或INTEGER4有符号:-21448648
无符号:0		有符号:2147483647
无符号:4294967295		整型
BIGINT8有符号:-92372036854775808
无符号:0		有符号:92372036854775807
无符号:18446744073709551615		大整型

小数类型

  • 浮点数类型
    浮点数类型字节最小值最大值备注
    FLOAT4±1.7549435E-38±3.402823466E+38单精度浮点数
    DOUBLE8±2.2250738585072014E-308±1.7976931348623157E+308双精度浮点数
  • 定点数类型
    定点数类型定义说明备注
    DEC(M,D)
    DECIMAL(M,D)    		DECIMAL(length,precision)length决定小数的最大位数
    pricision用于设置小数位数    		定点数

日期时间型

日期和时间类型字节描述格式备注
DATE41000-101-01YYYY-MM-DD日期型
DATETIME81000-01-01
00 : 00 : 00		YYYY-MM-DD
HH : MM : SS		日期时间型
TIMESTAMP419700101080001YYYY-MM-DD
HH : MM : SS		时间戳型
TIME3-835 : 59 : 59HH : MM : SS时间型
YEAR11901YYYY年
日期和时间类型分别使用的情况:
  • 日期型:开学时间,放假日期等
  • 日期时间型:上课时间等
  • 时间戳型:当使用即使时间的时候,如订单下单时间等
  • 时间型:只用到时间的情况
  • 年:只用到年的情况

字符串类型

CHAR系列字符串类型字节描述备注
CHAR(M)MM为0-255之间的整数定长字符串
VARCHAR(M)MM为0-65 535之间的整数不定长字符串
字符串类型分别使用的情况:
  • CHAR:字符串长度经常发生变化,如地址,姓名等
  • VARCHAR:字段的长度固定,如身份证号,学号,员工编码

TEXT字符串类型

TEXT系列字符串类型字节描述备注
TINYTEXT0-255值的长度为+1个字节微文本
TEXT0-65 535值的长度为+2个字节文本
MEDIUMTEXT0-167 772 150值的长度为—+3个字节中文本
LONGTEXT0-4 294 967 295值的长度为+4个字节长文本

二进制类型:BINARY

BINARY系列类型大小备注
BINARY(M)0-255字节定长二进制
VARBINARY(M)0-65535字节不定长二进制

位类型

位类型大小备注
BIT0-63字节位类型

二进制类型:BLOB

BLOB类型大小备注
TINYBLOB0-255字节微二进制
BLOB0-65 535字节二进制
MEDIUMBLOB0-167 772 15字节中二进制
LONGBLOB0-4 294 967 295字节长二进制

复合类型

类型大小备注
Enum("value1","value2",...)0-65535字节该类型的列只能容纳所列值之一或为NULL
Set("value1","value2",...)0-63字节该类型的列可以容纳一组值或为NULL
日期和时间类型分别使用的情况:
  • enum类型:只可以容纳所列值之一或为NULL,如男,女,只能选择其一
  • set类型:可以容纳一组值或为NULL,比如兴趣可以是游泳,唱歌,网球等

选择数据类型的注意事项

  • 在符货应用要求(取值范围,精度)的前提下,尽量在符合应用要求的前提下,使用"短"数据类型,如购买数量选用整型
  • 一些数字类型的字段,如学号,电话号码,设置称字符串类型;如果需要设置成自增,则设置称整型,如ID
  • 在MySQL中,日期型默认宽度为8,日期型默认为8,日期时间型默认为14,不需要自己去定义宽chyab度
  • 尽量采用精准小数类型(例如decimal),而不采用浮点数类型.如学生成绩,选择定点小数类型,而不选用浮点数类型
  • 选择合适的数据类型,既能节省存储空间,也能提升计算的性能

小结 数据类型可分为

  • 数值类型
    • 整数类型
    • 小数类型
  • 字符串类型
    • CHAR类型
    • VARCHAR类型
    • TEXT类型
  • 日期/时间类型
    • 日期类型
    • 时间类型
  • 二进制类型
    • BINARY类型
    • BLOB类型
    • BIT类型
  • 复合类型
    • SET类型
    • ENUM类型

创建数据表​

创建数据表元素

  • 字段命名规范
    • 采用26个英文字母和0-9的自然数加上下划线_组成,命名简洁明确,多个单词用下划线_分隔
    • 一般采用小写命名
    • 禁止使用数据库关键字,如:table,time,datetime,primary等
    • 字段名称一般采用名词或动宾短语,如user_id,is_good
    • 使用的字段名称必须是易于理解,一般不超过三个英语单词
  • 牢记一些专业术语
  • 熟练地使用数据类型
  • 在数据表上要加上约束

创建数据表术语

英文中文
CREATE创建
SHOW查看
DROP删除
TABLE数据表
DESCRIBE描述

创建数据表SQL语句

CREATE TABLE table_name(
    col_name 1 data_type1,
    col_name 2 data_type 2,
    ┋
    col_name n data_type n
)

说明

  • table_name:为创建的数据表的表名
  • col_name:为创建的字段名
  • data_type:为要创建的字段的数据类型

语句

  • DESCRIBE table_name;:查看数据表t_student,用DESCRIBE t_student;
  • SHOUW TABLES;:查看所有数据表
  • CREATE TABLE table_name1 LIKE table_name2;:复制表结构(创建一个新表table_name1,与已存在的表table_name2的表结构一致)
    • 如果是复制其他数据库的表结构,在table_name2前加上数据库的名称
    • eg:将数据库db_library中的reader表结构复制到当前数据库,并命名为表结构t_reader:CREATE TABLE t_reader LIKE db_library.reader;
  • DROP TABLE table_name;:删除表

创建数据表-实例

USE db_library; --选择当前数据库db_library
CREATE TABLE t_reader( --创建数据表t_reader
    reader_id char(6), --读者编号,数据类型为定长字符串6位
    reader_name varchar(50), --读者姓名,数据类型为不定长字符串50位
    reader_sex char(2), --性别,数据类型为定长字符串两位
    reader_birthday date, --出生日期,数据类型为日期型
    reader_borrowtotal float --借阅总量,数据类型为浮点型
); --完成创建
DESCRIBE t_reader; --查看t_reader表的数据结构
DESC t_reader; --同样也能达到查看t_reader表的数据结构
SHOW TABLE; --查看当前数据库的所有表
CREATE TABLE t_book1 like t_book; --创建一个表结构t_book1,与t_book一致
DROP TABLE t_book1; --删除t_book1数据表

小结

  • 字段命名规范
    • 采用26个英文字母和0-9的自然数加上下划线_组成,命名简洁明确,多个单词用下划线_分隔
    • 一般采用小写命名
    • 禁止使用数据库关键字,如:table,time,datetime,primary等
    • 字段名称一般采用名词或动宾短语,如user_id,is_good
    • 使用的字段名称必须是易于理解,一般不超过三个英语单词
  • 数据类型
    • 数值型
    • 字符串型
    • 日期/时间型
  • 创建数据表SQL语句
    • CREATE TABLE...
    • SHOUW TABLES;
    • DROP TABLE...
    • DESCRIBE...
    • CREATE TABLE...LIKE...

修改数据表​

语法

  • 修改表名:ALTER TABLE old_table_name RENAME new_table_name;
    • eg:修改表t_reader为新表名reader:ALTER TABLE t_reader RENAME reader;
  • 添加字段:ALTER TABLE table_name ADD col_name1 data_type [FIRST|AFTER col_name2]
    • 添加的字段默认放在最后,如果想放在表中第一个,加first
    • 放在某个字段之后,则用after
    • eg:在表reader中添加一个字段reader_address:ALTER TABLE reader ADD reader_address VARCHAR(100);
  • 删除字段:ALTER TABLE table_name DROP col_name;
    • eg:删除表t_student添加的字段"地址":ALTER TABLE student DROP 地址;
  • 修改字段:ALTER TABLE table_name MODIFY col_name1 data_type;
    • eg:将地址reader_address的数据类型由varchar改为char:ALTER TABLE reader MODIFY reader_address CHAR(100);
  • 修改字段的名称:ALTER TABLE table_name CHANGE old_col_name new_col_name old_data_type;
    • eg:将字段名reader_address改为address:'ALTER TABLE reader CHANGE reader_address address CHAR(100);'
  • 修改字段的顺序:ALTER TABLE table_name MODIFY col_name1 data_type FIRST|AFTER col_name2;
    • eg:将字段名"address"调整到字段名"reader_phnoe"之后:ALTER TABLE reader MODIFY address VARCHAR(100) AFTER reader_phone

修改数据表-实例

  • 将表t_book1重命名为t_book 
    ALTER TABLE t_book1 RENAME t_book;
  • 在t_book表下添加两个字段:book_copy(复本量),book_invertory(库存量)数据类型均为INT 
    ALTER TABLE t_book ADD book_copy INT;
    ALTER TABLE t_book ADD book_inventory INT;
  • 删除t_return_record表下的两个字段ISBN(图书编号),borrow_date(借阅日期) 
    ALTER TABLE t_return_record DROP ISBN;
    ALTER TABLE t_return_record DROP borrow_date;
  • 将表t_reader中的字段reader_borrowtotal的数据类型改为INT 
    ALTER TABLE t_reader MODIFY reader_borrowtotal INT;
  • 将表t_press中的字段website改名为press_website,字段postcode改名为press_postcode 
    ALTER TABLE t_press CHANGE website press_website VARCHAR(50);
    ALTER TABLE t_press CHANGE postcode press_postcode CHAR(6);

修改数据表术语

  • 修改数据表名:ALTER TABLE old_table_name RENAME new_table_name;
  • 添加字段:ALTER TABLE table_name ADD col_name1 data_type [FIRST|AFTER col_name2]
  • 删除字段:ALTER TABLE table_name DROP col_name;
  • 修改字段
    • ALTER TABLE table_name MODIFY col_name1 data_type;
    • ALTER TABLE table_name CHANGE old_col_name new_col_name old_data_type;
    • ALTER TABLE table_name MODIFY col_name1 data_type FIRST|AFTER col_name2;

非空与默认值的设置​

什么是数据完整性

  • 对于已创建好的数据表,已经设置了数据类型,但表中所储存的数据是否合法没有进行检查.因此可以对已经创建好的表定义完整性约束,或在创建新表使就进行完整性约束的定义
  • 数据完整性是指数据的精确性和可靠性.防止数据库中存在不符合语义规定的数据和防止因数据库操作员错误数据的输入/输出而造成数据库中存在的错误数据

数据完整性的分类

  • 实体完整性:指关系中的主属性值不能为Null且不能有相同值
  • 参照完整性:两个表的主键和外键的数据对应一致
  • 用户定义完整性:针对某个特点关系数据库的约束条件

参照完整性的规则

  • 禁止在从表中插入包含主表中不存在的关键字的数据行
  • 禁止删除在从表中有对应记录的主表记录
  • 禁止会导致从表中相应值孤立的主表中的外键值改变

数据表约束类型

完整性约束关键字含义
NOT NULL约束字段的值不能为空
DEFAULT设置字段的默认值
UNIQUE KEY(UK)约束字段的值是唯一
AUTO_INCREMENT约束字段的值为自动增加
PRIMARY KEY(PK)约束字段为表的主键,可以作为该表记录的唯一标识
FOREIGN KEY约束字段为表的外键

设置非空与默认值-实例

  • 创建新表t_reader1,为reader_id设置非空,设置性别的默认值为'男' 
    CREATE TABLE t_reader1
    (
      reader_id INT NOT NULL,
      reader_name VARCHAR(50),
      reader_sex CHAR(2) DEFAULT "男",
      reader_birthdat DATE,
      reader_borrowtotal INT
    );
  • 为已存在的表t_book,设置ISBN为非空 
    ALTER TABLE t_book MODIFY ISBN CHAR(17) NOT NULL;
  • 设置表t_reader中的reader_borrowtotal(借书总量)的默认值为0 
    ALTER TABLE t_reader MODIFY reader_borrowtotal INT DEFAULT 0;

  • 小结(数据表约束-非空与默认值)
    • 数据完整性
      • 实体完整性
      • 参照完整性
      • 用户定义完整性
    • 创建新表时设置 
      CREATE TABLE table_name
      (
       col_name data_type NOT NULL | DEFAULT default_value,
       ……
      );
    • 为已存在的表设置 
      ALTER TABLE table_name MODIFY col_name
      data_type NOT NULL | DEFAULT default_value

唯一键与自增的设置​

唯一键与自增

完整性约束关键字含义
UNIQUE KEY(UK)约束字段的值是唯一
AUTO_INCREMENT约束字段的值为自动增加

UNIQUE唯一键

在数据库中,约束某些字段必须使用唯一值,如公民的身份证号,学生的学号,职工的编号等,都必须设置唯一键

AUTO_INCREMENT自增

例如序号等,在数据库中设置自增,可以减少手动输入数据的工作量

语法

  • 创建新表时设置 
    CREATE TABLE table_name
    (
     col_name data_type
     UNIQUE | AUTO_INCREMENT
     ……
    );
  • 设置已存在的表 
    ALTER TABLE table_name MODIFY
    col_name data_type
    UNIQUE | AUTO_INCREMENT;
  • 删除唯一键 
    ALTER TABLE table_name DROP INDEX index_name;
    • 索引一般是设置唯一约束的字段名

设置自增的注意事项

  1. 必须是整数类型才可以设置AUTO_INCREMENT
  2. 必须先定义为一个键(主键或者唯一键)才可以设置AUTO_INCREMENT
  3. 可以用ALTER TABLE table_name AUTO_INCEREMENT=default_value为字段设置自增的初始值

设置唯一键与自增-实例

  • 创建新的数据表t_reader2,表结构与t_reader一致,设置return_id为整型,且为AUTO_INCEREMENT和UNIQUE 
    CREATE TABLE t_reader2
    (
      reader_id int UNIQUE AUTO_INCREMENT,
      reader_name VARCHAR(50),
      reader_sex CHAR(2),
      reader_birthday DATE,
      reader_borrowtotal INT
    );
  • 为已存在的表t_press的press_id设置唯一键与自增 
    ALTER TABLE t_press MODIFY press_id INT UNIQUE AUTO_INCREMENT;

主键与外键的设置​

数据表约束术语

英文中文英文中文
PRIMARKY KEY主键NOT NULL非空
FOREIGN KEY外键DEFAULT默认值
REFERENCES参照UNIQUE唯一
CONSTRAINT约束AUTO_INCREMENT自增

主表与外键的参照关系

  • 子表中的id的值只能从主表中的id值中进行选择
  • 主表中id当中没有的值,在子表中是不允许出现的

主键-单字段主键

  • 创建新表时设置主键语法格式: 
    CREATE TABLE table_name(col_name data_type PRIMARY KEY,……)
  • 为已存在的表设置主键: 
    ALTER TABLE table_name ADD [CONSTRAINT constraint_name] PRIMARY KEY(col_name)
  • 删除主键的语法格式: 
    ALTER TABLE table_name DROP PRIMARY KEY

主键-多字段联合主键

  • 创建新表时写上primary key,将设置主键的几个字段放在括号里 
     CREATE TABLE table_name
     (
       col_name data_type,……
       [CONSTRAINT constaint_name]
       PRIMARY KEY(col_name,col_name2……)
     )
  • 为已存在的表设置主键 
    ALTER TABLE table_name ADD
    [CONSTRAINT constraint_name]
    PRIMARY KEY(col_name1,col_name2……)

外键

  • 外键不能单独设置,只有当设置了主键之后才能设置外键,外键必须参照另一个表的主键
  • 具体语法格式: 
    CREATE TABLE table_name
    (
      col_name data_type,……
      [CONSTRAINT constraint_name] FOREIGN KEY (col_name1)
      REFERENCES table_name(col_name2)
    )
  • 为已存在的表设置: 
    ALTER TABLE table_name1 ADD [CONSTRAINT constraint_name]
    FOREIGN KEY(col_name1)
    REFERENCES table_name2(col_name2)

删除外键

  • 删除外键时一定要加上约束的名称: 
    ALTER TABLE table_name1 DROP FOREIGN KEY constraint_name
  • 删除外键有两个重要的事项
    1. 如果没有设置外键名称,系统则会自动加上一个外键的名称
      • 用命令SHOW CREATE TABLE table_name进行查看,然后删除
    2. 如果要彻底删除外键,应删除一个在建立外键时自动创建的索引
    • 如果没有定义约束的名称,一般是外键设置时的字段名
    • 如果定义了约束的名称,索引一般是约束的名称
    • 也可用命令SHOW INDEXES FROM table_name进行查看

外键与主键的约束

约束规则

  • Restrict:当要删除或更新父表中被参照列上在外键中出现的值时,拒绝对父表的删除或更新操作
  • Cascade:从父表删除或更新行时自动删除或更新子表中匹配的行
  • Set null:当从父表删除或更新行时,设置子表中与之对应的外键列为NULL;如果外键列没有指定NOT NULL限定词,这就是合法的
  • No action:不采取动作,如果有一个相关的外键值在被参考的表里,删除或更新父表中主要键值的企图不被允许,和RESTRICT一样

关系代数运算​

差

  • 关系R和S具有相同的关系模式
  • R和S的差是由属于R但不属于S的元组组成的集合,记作R-S
  • 形式定义如下:R-S={t|t∈R^t∈S}

笛卡尔积

  • 设关系R和S的元素分别为r和s
  • 定义R和S的笛卡尔积是一个(r+s)元的元组集合,每个元组的前r个元素属于R,后s个元素属于S，记为R×S
  • 形式定义如下:R×S={t|t<tr,ts>^tr∈R^ts∈S}

投影

  • 是对关系进行垂直分割,消去某些列,并重新安排列的顺序,再删去重复元组
  • 将关系R投影A,C字段,投影A字段,投影C字段,得到的如表所示的结果

选择

  • 是根据某些条件对关系作水平分割,即选择符合条件的元组

交

  • 关系R和S具有相同的关系模式
  • 关系R和S的交是由属于R又属于S的元组构成的集合,记作R∩S
  • 形式定义如下:R∩S={t|t∈R^t∈S}

总结

  • 关系代数运算
    • 并
    • 差
    • 笛卡尔积
    • 投影
    • 选择
    • 交