MySQL的建库、建表、建约束与存储引擎

lijiawnen 2020-06-04

一、MySQL建库、建表

1、创建数据库

创建数据库是在系统磁盘上划分?块区域用于数据的存储和管理,如果管理员在设置权限的时候为用户创建了数据库,则可以直接使用,否则,需要自己创建数据库。

语法格式:
CREATE DATABASE [IF NOT EXISTS] 数据库名
示例:

IF NOT EXISTS:在创建数据库之前进行判断,只有该数据库目前尚不存在时才能执行操作。
此选项可以用来避免数据库已经存在而重复创建的错误。

# 创建myschool数据库
create database myschool;

IF NOT EXISTS:在创建数据库之前进行判断,只有该数据库目前尚不存在时才能执行操作。
此选项可以用来避免数据库已经存在而重复创建的错误。

2、创建表

语法格式:

CREATE TABLE [IF NOT EXISTS] 表名 (
字段1 数据类型 [字段属性|约束][索引][注释],
……
字段n 数据类型 [字段属性|约束][索引][注释]
)[表类型][表字符集][注释];

示例:

#创建学生表
CREATE TABLE `student`(
`studentNo` INT(4) PRIMARY KEY,
` name` CHAR(10),
……);

注意:

多字段使用逗号分隔
保留字用撇号括起来
单行注释:#……
多行注释:/*……*/

(1)字段的约束及属性

MySQL的建库、建表、建约束与存储引擎

主键

CREATE TABLE student(
`studentNo` INT(4) PRIMARY KEY,
……);

注释

CREATE TABLE test (
`id` int(11) UNSIGNED COMMENT ‘编号’
)COMMENT=‘测试表’ ;

设置字符集编码

CREATE TABLE [IF NOT EXISTS] 表名(
#省略代码
)CHARSET = 字符集名;

(2)在myschool数据库中创建学生表

MySQL的建库、建表、建约束与存储引擎

所需执行的命令

create databases myschool;
use myschool;
create table student(
    `studentNo` int(4) not null comment ‘学号‘ primary key,
    `loginPwd` varchar(20) not null comment ‘密码‘,
    `studentName` varchar(50) not null comment ‘姓名‘,
    `sex` char(2) not null default ‘男‘ comment ‘性别‘,
    `gradeID` int(4) unsigned comment ‘年级编号‘,
    `phone` varchar(50) comment ‘电话‘,
    `address` varchar(255) default ‘地址不详‘ comment ‘地址‘,
    `bornDate` datetime comment ‘出生日期‘,
    `email` varchar(50) comment ‘邮件账号‘,
    `identityCard` varchar(18) comment ‘身份证号‘ unique key
)charset=‘utf8‘ comment=‘学生表‘;

查看一下表结构

mysql> desc student;
+--------------+-----------------+------+-----+----------+-------+
| Field        | Type            | Null | Key | Default  | Extra |
+--------------+-----------------+------+-----+----------+-------+
| studentNo    | int(4)          | NO   | PRI | NULL     |       |
| loginPwd     | varchar(20)     | NO   |     | NULL     |       |
| studentName  | varchar(50)     | NO   |     | NULL     |       |
| sex          | char(2)         | NO   |     | 男       |       |
| gradeID      | int(4) unsigned | YES  |     | NULL     |       |
| phone        | varchar(50)     | YES  |     | NULL     |       |
| address      | varchar(255)    | YES  |     | 地址不详 |       |
| bornDate     | datetime        | YES  |     | NULL     |       |
| email        | varchar(50)     | YES  |     | NULL     |       |
| identityCard | varchar(18)     | YES  | UNI | NULL     |       |
+--------------+-----------------+------+-----+----------+-------+
10 rows in set (0.00 sec)

3、查看表

(1)查看表是否存在

use myschool;
show tables;

(2)查看表定义

语法格式:

use myschool;
desc `student`;

示例:

use myschool;
desc `student`;

4、删除表

语法格式:

drop table [if exists] 表名;

示例:

use myschool;
drop table if exists `student`;
在删除表之前,先使用 if exists 语句验证表是否存在

5、删除数据库

删除数据库是将已经存在的数据库从磁盘空间上清除,清除之后,数据库中的所有数据也将除。
删除数据库语句和创建数据库的命令相似,MySQL中删除数据库的基本语法格式为:

drop database if exists 数据库名;

示例:

drop database if exists myschool;

6、上机练习

(1)myschool数据库中创建科目表(subject)

MySQL的建库、建表、建约束与存储引擎

create table subject(
    `subjectNo` int(4) comment ‘课程编号‘ primary key auto_increment,
    `subjectName` varchar(50) comment ‘课程名称‘,
    `classHour` int(4) comment ‘学时‘,
    `gradeID` int(4) comment ‘年级编号‘
);

查看一下表结构

mysql> desc subject;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| subjectNo   | int(4)      | NO   | PRI | NULL    | auto_increment |
| subjectName | varchar(50) | YES  |     | NULL    |                |
| classHour   | int(4)      | YES  |     | NULL    |                |
| gradeID     | int(4)      | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

(2)myschool数据库中创建成绩表(result)

MySQL的建库、建表、建约束与存储引擎

create table result(
    `studentNo` int(4) comment ‘学号‘ not null,
    `subjectNo` int(4) comment ‘课程编号‘ not null,
    `examDate` datetime(0) comment ‘考试日期‘ not null,
    `studentResult` int(4) comment ‘考试成绩‘ not null
);

查看一下表结构

mysql> desc result;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| studentNo     | int(4)   | NO   |     | NULL    |       |
| subjectNo     | int(4)   | NO   |     | NULL    |       |
| examDate      | datetime | NO   |     | NULL    |       |
| studentResult | int(4)   | NO   |     | NULL    |       |
+---------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

二、MySQL的存储引擎

1、存储引擎简介

数据库存储引擎是数据库底层软件组件,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更
新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。使用不同的存
储引擎,还可以获得特定的功能。
现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

2、存储引擎的类型

mysql有多种存储引擎,它们分别为:

MyISAM
InnoDB
MERGE
MEMORY
EXAMPLE
FEDERATED
ARCHIVE
CSV
BLACKHOLE

3、存储引擎的主要区别

MySQL的建库、建表、建约束与存储引擎

(1)MyISAM 存储引擎特点

  • MySQL 5.5 之前使用 MyISAM 引擎,MySQL 5.5 之后使用 InnoDB 引擎
  • MyISAM 引擎读取速度较快,占用资源相对较少,不支持事务,不支持外键约束,但支持全文索引
  • 读写互相阻塞,也就是说读数据的时候你就不能写数据,写数据的时候你就不能读数据
  • MyISAM 引擎只能缓存索引,而不能缓存数据

(2)InnoDB 存储引擎特点

  • 事务型数据库的首选引擎,支持事务安全表,支持行锁定和外键,MySQL5.5.5 版本之后,InnoDB作为默认存储引擎
  • 具有提交、回滚和崩溃恢复能力的事务安全存储引擎,能处理巨大数据量,性能及效率高,完全支持外键完整性约束
  • 具有非常高效的缓存特性,能缓存索引也能缓存数据,对硬件要求比较高
  • 使用InnoDB时,将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据?文件,以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB ?大?小的日志?文件

(3)Memory 存储引擎特点

  • Memory存储引擎将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问
  • Memory存储引擎执行 HASH 和 BTREE 索引,不支持 BLOB 和 TEXT 列,支持AUTO_INCREMENT列和对可包含 NULL 值得列的索引
  • 当不再需要 Memory 表的内容时,要释放被 Memory 表使用的内存,应该执行DELETE FROM 或
    TRUNCATE TABLE ,或者删除整个表

4、存储引擎适用场合

(1)MyISAM 适?用场景

  • 不需要事务支持的业务,例如:转账就不行
  • 适用于读数据比较多的业务,不适用于读写频繁的业务
  • 并发相对较低、数据修改相对较少的业务
  • 硬件资源比较差的机器可以考虑使用 MyISAM 引擎

(2)InnoDB 适?用场景

  • 需要事务?持的业务、?并发的业务
  • 数据更新较为频繁的场景,?如:BBS、SNS、微博等
  • 数据?致性要求较?的业务,?如:充值转账、银?卡转账

(3)总结

使用MyISAM: 不需事务,空间小,以查询访问为主 使用InnoDB: 多删除、更新操作,安全性高,事务处理及并发控制

5、查看当前默认存储引擎

mysql> show variables like ‘%storage_engine‘;
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
3 rows in set, 1 warning (0.02 sec)

6、修改默认存储引擎

(1)MySQL 5.5

修改my.ini配置文件

default_storage_engine=InnoDB

(2)MySQL 5.7

最简单的方法,就是通过命令直接修改表的存储引擎,如下所示:

alter table 表名 ENGINE = 引擎名;

示例:

ALTER TABLE student ENGINE = InnoDB;

7、设置表的存储引擎

语法格式:

CREATE TABLE 表名(
#省略代码
)ENGINE=存储引擎;

示例:

CREATE TABLE `myisam` (
id INT(4)
)ENGINE=MyISAM;

三、MySQL补充知识

在mysql中,每个数据库最多可创建20亿个表,一个表允许定义1024列,每行的最大长度为8092字节(不包括?本和图像类型的长度)。
当表中定义有varchar、nvarchar或varbinary类型列时,如果向表中插入的数据行超过8092字节时,将导致语句失败,并产生错误信息。
SQL Server对每个表中行的数量没有直接限制,但它受数据库存储空间的限制。每个数据库的最大空间1048516TB,所以一个表可用的最大空间为1048516TB减去数据库类系统表和其它数据库对象所占用的空间。理论上无限大,就看你硬盘够不够大,大多数情况先是你的硬盘不够。

相关推荐