MySQL数据库的设计和命令行模式下建立详细过程

1.数据表的设计

MySQL数据库管理系统(DBMS)中,包含的MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。

下面以大学熟悉的学生选课管理系统中用到的数据库为例,来设计相应的数据表。主要有三张表:学生表,课程表和选课表。

学生表设计:

字段(Field)

类型(Type)

可空(Null)

键(Key)

默认值(Default)

其他(Extra)

学号(studentNo)

VARCHAR(12)

N

PRI

NULL

姓名(name)

VARCHAR(12)

N

N

NULL

学院(school)

VARCHAR(12)

N

N

NULL

年级(grade)

VARCHAR(12)

N

N

NULL

专业(major)

VARCHAR(12)

N

N

NULL

性别(gender)

Boolean

N

N

NULL

课程表设计:

字段(Field)

类型(Type)

可空(Null)

键(Key)

默认值(Default)

其他(Extra)

课程号(courseNo)

VARCHAR(10)

N

PRI

NULL

课程名(courseName)

VARCHAR(10)

N

N

NULL

课时(hour)

TINYINT Unsigned

N

N

NULL

课程学分(credit)

TINYINT Unsigned

N

N

2

选课表设计:

字段(Field)

类型(Type)

可空(Null)

键(Key)

默认值(Default)

其他(Extra)

编号(id)

INT Unsigned

N

PRI

NULL

auto_increment

学号(studentNo)

VARCHAR(12)

N

MUL

NULL

课程号(courseNo)

VARCHAR(10)

N

MUL

NULL

选课时间(time)

TIMESTAMP

N

N

CURRENT_TIMESTAMP

对于上面三张数据表的设计,你会发现: (1)MySQL在为数据定义字符串(字符)类型时,需要在类型名称的后面的小括号内指明最长字符数,即TypeName(M),这里的M指的是字符数,而不是数据占用的字节数。例如varchar(12),则表述存储的字符数不能超过12 ,超过12则截断,小于12个字符则以实际占用的存储空间来存储,这样就节省了存储空间。

(2)在对选课表的设计时,我们添加外键约束,可以使得两张表关联,保证数据的一致性和实现一些级联操作。

(3)MySQL中有三种Key和一个Index: Primary Key(主键), Unique Key(唯一键),Foreign Key(外键)和 Index(索引)。

四者的区别如下: 定义: 主键(Primary Key):唯一标识一条记录,不能有重复,不允许为空。 外键(Foreign Key):表的外键是另一表的主键, 外键可以有重复, 可以是空值。 唯一键( Unique Key):唯一标识一条记录,不能有重复,可以为空。 索引(Index):该字段可以有重复值记录,可以有空值,如果是唯一索引,那么就不可以有重复的记录,可以有空值。

作用: 主键:用来保证数据完整性。 外键:用来和其他表建立联系,以保证数据的一致性和级联操作。 唯一键:用来用防止数据插入的时候重复。 索引:是提高查询排序的速度。

个数: 主键:一个表只能有一个列是主键。 外键:一个表可以有多个列是外键。 唯一键:一个表可以有多个列是唯一键。 索引:一个表可以有多个列是索引。

从中可以看出,索引和唯一键很相似,二者的区别在于作用不同,索引用于提高查询速度,唯一键用于唯一约束。当然如果建立的索引是唯一索引的话,也可以起到唯一约束的作用。在MySQL具体实现上,索引需要建立数据结构,需要额外的磁盘空间来存储索引,而键(主键,外键和唯一键)都是逻辑层面的实现和约束,二者还是有着本质的区别。

还有就是MySQL的索引包括:普通索引(Index)、唯一索引(UNIQUE INDEX)、全文索引(FULLTEXT INDEX)、组合索引。这里就不要把唯一键和唯一索引弄混淆了!也不要把索引和其它的三种键弄混淆了。

还有一点需要注意的是,虽然键(key)和索引(index)有着本质的区别,但是当我们在建立主键或者唯一键的时候,也就建立了索引,MySQL和Oracle都是这么做的,要是不明白这一点,很容易把索引和主键和唯一键弄混淆。

唯一键可以起到唯一约束的作用,当然主键也可以起到唯一约束的作用。当然我们可以不建立唯一键和主键,直接为指定的数据表的列添加唯一约束。唯一约束保证指定列的值不能重复。

所以,关于上面的概念,大家不要咬文嚼字,而是根据功能去理解每一种KEY的作用,在什么场合需要去使用它。

比如需要主键约束的时候,我们就可以对某一列建立主键; 需要对某一列或者多个列进行唯一约束的时候,我们就建立唯一键或者唯一约束; 需要加快查询速度的时候,我们就建立相应类型的索引。

关于四者的理解,上未参考到权威的资料,请读者保持怀疑的态度接受。

2.数据库的建立

在安装完mysql之后,我们要建立自己的数据库。下面将详细地一步一步演示如何创建上面设计好的数据库。 在MySQL模式下,使用status命令可以查看MySQL版本,本人使用的MySQL版本:5.6.30 MySQL Community Server (GPL)。

关于mysql大小写问题,mysql命令是不区分大小写的。数据表的表名在windows下不分,linux下分。数据表的字段名在windows和linux下都不分。

(1)登录mysql DBMS Linux命令行中输入如下命令:

mysql -hlocalhost -uroot -p123456

说明:依次指明登录mysql的主机地址,用户和用户密码。

(2)使用show查看当前mysql服务器上存在什么数据库

show databases;

说明:在进入mysql模式下,使用mysql命令时,每条语句要以分号结束。但是use [DatabaseName]却不用,可能是mysql的一个小bug。类似于quit和exit退出mysql的命令,不需要一个分号。(如果你喜欢,你可以用一个分号终止这样的语句)。

(3)创建数据库

mysql> CREATE DATABASE StudentCourse;

(4)使用use语句访问数据库

mysql>use StudentCourse;

(5)创建数据表student

mysql> create table student(
    studentNo varchar(12) not null,
    name varchar(12) not null,
    school varchar(12) not null,
    grade varchar(12) not null,
    major varchar(12) not null,
    gender boolean not null,
    primary key(studentNo)
)engine=MyISAM default charset=utf8;

观察上面的建表语句,需注意以下几点: (a)其中,布尔类型boolean在MySQL是以类型tinyint(1)来实现,这里的1指代的是数据显示时最短长度。 实际上tinyint(1)是可以插入-128到127之间的其它数值。因为mysql数据库中以 :数据类型(m)来定义数据类型,其中 数字m在不同的数据类型中表示含义是不同的。 整型数系统已经限制了取值范围,tinyint占一个字节、int占4个字节。所以整型数后面的m不是表示的数据长度,而是表示数据在显示时显示的最小长度(长度为字符数)。

tinyint(1) 这里的1表示的是最短显示一个字符。tinyint(2) 这里的2表示的是最短显示两个字符,但这里光设置m是没有意义的,你还需要指定当数据少于长度m时用什么来填充,比如zerofill(表示有0填充)。设置tinyint(2) zerofill你插入1时他会显示01。设置tinyint(4) zerofill你插1时他会显示0001。

(b)还要注意一点是,使用engine可指明引擎,如果省略了engine语句,则使用默认的引擎(MYISAM)。MYSQL支持三个引擎:ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY(BDB),也常常可以使用。

(c)设置default charset指明mysql数据表的编码方式,不显示指定编码方式的话,数据表的默认编码方式一般是latin1。也可以通过如下命令查看数据表的编码方式:

show create table student;

如果需要转换数据表的编码格式,使用如下命令:

alter table student convert to character set utf8;

如果想查看当前数据库的编码格式:

mysql>status;
#或者
show variables like 'character%' ;

为了验证所建立的数据表是否是按照期望的方式创建的,使用如下命令:

#查看表中的列
SHOW COLUMNS FROM student;
#或者直接使用describe
describe student;

(6)创建数据表course

mysql> create table course(
    studentNo varchar(12) not null primary key,
    courseNo varchar(10) not null,
    hour tinyint unsigned not null,
    credit tinyint unsigned not null default 2    
)engine=MYISAM default charset=utf8;

同样可以使用describe查看表信息:

(7)创建数据表courseSelection

mysql> create table courseSelection(
    id int unsigned not null auto_increment primary key,
    studentNo varchar(12) not null,
    courseNo varchar(10) not null,
    time timestamp not null default CURRENT_TIMESTAMP,
    FOREIGN KEY(studentNo) REFERENCES student(ISBN) ON UPDATE CASCADE ON DELETE  CASCADE,
    FOREIGN KEY(courseNo) REFERENCES course(courseNo) ON UPDATE CASCADE ON DELETE  CASCADE
)engine=MYISAM default charset=utf8 AUTO_INCREMENT=0;

查看表信息:

(8)其它关于数据库和数据表的操作命令 删除数据库:

mysql> DROP DATABASE 库名;

删除数据表:

mysql> DROP TABLE 表名;

将表中记录清空:

mysql> DELETE FROM 表名;

参考文献:

[1]http://www.runoob.com/mysql/mysql-data-types.html [2]Mysql 查看、创建、更改 数据库和表 [3]php里tinyint(1)为什么还能插入99这个值?搜索 [4]MySQL引擎 [5]MySQL中的四种Key [6]SQL的主键和外键约束 [7]唯一索引和唯一约束有什么区别

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏xcywt

学习SQLite之路(四)

20160621 更新 参考: http://www.runoob.com/sqlite/sqlite-tutorial.html 1. SQLite   a...

2178
来自专栏漆洪凯的专栏

MySQL 数据库设计总结

MySQL支持很多种不同的数据类型,并且选择正确的数据类型对于获得高性能至关重要。本文将对MySQL 数据库设计总结,希望与大家共同探讨。

25.8K12
来自专栏java小白

explain 分析sql语句字段的解释

3605
来自专栏wblearn

mysql错误Every derived table must have its own alias解决

Every derived table must have its own alias

921
来自专栏JAVA同学会

mysql如何执行关联查询与优化

在数据库中执行查询(select)在我们工作中是非常常见的,工作中离不开CRUD,在执行查询(select)时,多表关联也非常常见,我们用的也比较多,那么my...

1443
来自专栏岑玉海

SqlServer 索引

什么是索引 拿汉语字典的目录页(索引)打比方:正如汉语字典中的汉字按页存放一样,SQL Server中的数据记录也是按页存放的,每页容量一般为4K 。为了加快查...

3809
来自专栏JMCui

MySQL数据库规约.

一、建表规约 1、表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint(1 表示是, 0 表示否) 。 2、表...

3615
来自专栏一个爱吃西瓜的程序员

学习SQL【2】-数据库与SQL

一:数据库是什么? 1:定义 ● 将大量数据通过计算机加工而成的可以进行高效访问的数据集合称为数据库(DB)。 ● 用来管理数据库的计算机系统称为数据库管理系...

3589
来自专栏Leetcode名企之路

索引使用策略及优化

面试官常常会问你,怎么查看一个sql语句有没有使用索引这种类似的问题,或者问你sql怎么优化,那么如何了解sql怎么执行,执行情况如何呢?这就要用到Mysql的...

1243
来自专栏谢庆玲的专栏

MySQL 索引及查询优化总结

本文主要讨论 MySQL 索引原理及常用的 sql 查询优化。

14.5K5

扫码关注云+社区

领取腾讯云代金券