MySQL学习笔记-02

创建数据库

使用SQL语句创建数据库

创建数据库的语法规则

其中CREATE DATABASE是创建数据库的关键字；database_name 表示要创建数据库的名字。

注意：在使用MySQL 5.0 Command Line Client窗口书写SQL语句时，在SQL语句的后面都需要加一个分号（;），否则SQL语句无法得到正确地执行。

删除数据库

使用SQL语句删除数据库

删除数据库的语法规则

数据库中的表

在关系数据库中，数据表是存储数据的基本单元，由行和列两部分组成。

其中，行用来描述实体中的具体数据，在数据表中每一行中的数据被称为一条数据记录；字段是表中的一列，用来保存数据表中某一条记录的特定信息。字段在关系数据库中也可以称为列。

表1：学生信息表

主键

为保证在一张数据表中不会出现两个完全相同的数据记录，需要为每一张数据表都定义一个主键。主键作为数据表中的唯一标识，保证了一条记录的唯一性。通过主键可以区分数据表中的每一条记录。在创建数据表时，需要保证被定义为主键的列的列值唯一，并且不能为空值（即NULL值）

表1：学生成绩表

在上表中，学生编号字段stuID就是这张表的主键，每一张主键对应其中一名学生，在学生信息表中主键对应的值不能为空，更不能重复出现，也就是说不能出现两个stuID是s102203的学生信息。

当然主键列并不一定就只有一列，也可以将多个列组合在一起作为表的主键，在上表中，由3列，列stuID用来表示学生编号，列curID用来表示课程编号，列result用来表示学生成绩。如果想要唯一地标识一条学生的记录信息，需要将列stuID和列curID共同作为学生成绩表的主键，这样才能唯一地标识一条学生的成绩信息。

注意：主键可以定义在多个列上，并不一定只定义在一个列上。也就是说，在定义数据表的时候，可以将数据表中的多个列合在一起作为该表的主键。

外键

外键是用来定义表与表之间的关系。在数据表中，外键是这样定义的：如果属性列F是关系B中的一个属性（并不是关系B的主键），并且属性列F是关系A中的主键，则F就是关系B的外键。关系A中的表被称为主表，关系B中的表被称为主表的从表。

外键在关系模型中用来约束参照完整性。所谓参照完整性约束，是指表与表之间的约束。在参照完整性中，从表中的每一条数据记录中的外键值都必须存在于主表中。对于建立了关联关系的两个数据表来说，对其中一个数据表的增加、修改或删除数据都会对另一个数据表的记录产生影响。

例如，列stuID和列curID两个联合作为该表的主键。则该列stuID就是学生成绩表的外键。其中表1是主表，表2是主表的从表。如果想向学生成绩表中插入一条学生成绩记录，数据库管理系统首先会对学生信息进行检查，看这个学生是否在学生信息表中存在，如果存在，才会允许插入操作；如果该名学生在学生信息表中不存在，则执行插入操作时，数据库管理系统会报错，拒绝执行插入操作。

索引

在实际应用中，为加快访问速度，节省访问时间，一般都需要使用索引进行查询。

索引是一个指向数据表中数据的指针，指向索引字段在数据表中的物理位置。如果在执行查询操作时，WHERE子句中指定的字段是被设置为索引的字段，则数据库会首先在索引中对指定的值进行查询，并返回查询的数据在数据表中的位置。如果在执行查询操作时，WHERE子句中指定的字段没有设置为索引的字段，那么数据库会对查询数据表中的每一行数据记录进行扫描。因此适当地创建索引，可以加快数据的索检速度，提高对数据的访问效率。

一般可以在下面这些列中创建索引：

在主键列中创建索引多表连接时，在经常使用的连接列上创建索引在经常使用WHERE子句查询上创建索引在经常进行分组（GROUP BY）和排序（ORDER BY）的列上创建索引

约束

为保证数据的完整性，需要使用数据库约束。完整性约束包括对表的约束和对列的约束。表约束主要包括唯一约束、主键约束、外键约束和检查约束，列约束除了包括唯一约束、参照约束和检查约束之外，还有非空约束。

唯一约束（UNIQUE）：保证使用唯一约束的某一列或一组列中没有相同的值，即保证列的值的唯一性。但是唯一约束中可以约束允许在列中插入空值（即NULL值）主键约束（PRIMARY KEY）：保证使用主键约束的列中只能有唯一的值，并且不能包含空值。数据表中每一列只能定义一个PRIMARY KEY外键约束（FOREIGN KEY）：保证表的参照完整性。确保对一个表的数据操作不会对与之关联的表造成不利的影响非空约束（NOT NULL）：只用来约束列。在向该列插入数据时不允许插入空值。

创建数据表

创建数据表可以通过使用CREATE TABLE 语句。使用CREATE TABLE 语句创建数据表语法格式如下：

使用CREATE TABLE 语句创建数据表

其中，CREATE TABLE是创建数据表的关键字；table_name1用来指定数据表的列名；datatype1指定列名为column_name1中的数据类型；constraint_condition1指定列名为column_name1中的完整性约束条件。

在创建数据表的CREATE TABLE语句中，可以为数据表创建多个列，数据表中的每个列都需要指定列名和该列对应的数据类型，列名对应的完整性约束条件是可选的。

例子：创建表名为T_student的学生信息表

创建表名为T_student的学生信息表

stuID指定数据表的列名，列stuID用来表示学生编号；VARCHAR (15)用来指定stuID列的数据类型，这里将其指定为VARCHAR类型，并且设定该列中字符长度为15；PRIMARY KEY用来指定列stuID的完整性约束条件，这里将stuID设定为主键，表示stuID的值唯一并且不能为空值（NULL值）stuName指定数据表的列名，列stuName用来表示学生姓名；VARCHAR (10)用来指定stuName列的数据类型，这里将其指定为VARCHAR类型，并且设定该列中字符长度为10；NOT NULL用来指定列stuName的完整性约束条件，这里将stuName设定为非空，表示该列不能为空值（NULL值）

使用约束

唯一约束（UNIQUE）：保证使用唯一约束的某一列或一组列中没有相同的值，即保证列的值的唯一性。但是唯一约束中可以约束允许在列中插入空值（即NULL值）。唯一约束既可以定义的表级上，也可以定义在列级。一般在为列创建唯一约束后，数据库会自动为该列建立一个唯一索引，其索引名与约束名是相同的。

例子：创建院系信息表，并为院校编号所在列唯一约束

创建院系信息表，并为院校编号所在列唯一约束

deptID指定数据表的列名，列deptID用来表示院系编号；VARCHAR (15)用来指定deptID列的数据类型，这里将其指定为VARCHAR类型，并且设定该列中字符长度为15；UNIQUE用来指定列deptID的完整性约束条件，这里将其定义为唯一约束

列deptName用来表示院系名称；VARCHAR (10)用来指定deptName列的数据类型，这里将其指定为VARCHAR类型，并且设定该列中字符长度为10

主键约束（PRIMARY KEY）：保证使用主键约束的列中只能有唯一的值，并且不能包含空值。数据表中每一列只能定义一个PRIMARY KEY。数据表中每一列只能定义一个PRIMARY KEY。

在创建数据表时，如果希望将多个列组合起来作为一个数据表中的主键，可以在PRIMARY KEY关键字后使用括号，将需要定义为主键的列放到PRIMARY KEY关键字后面的括号中。括号中的多个列之间需要使用括号分隔。

例子：创建成绩信息表

创建成绩信息表

列stuID用来表示学生编号；VARCHAR (15)用来指定stuID列的数据类型，这里将其指定为VARCHAR类型，并且设定该列中字符长度为15

列curID用来表示院系编号；VARCHAR (15)用来指定curID列的数据类型，这里将其指定为VARCHAR类型，并且设定该列中字符长度为15

列result用来表示学生成绩，DOUBLE用来指定result列的数据类型，这里将其指定为DOUBLE类型

PRIMARY KEY表示主键约束，这里将stuID和列curID联合作为成绩信息表的主键。

外键约束（FOREIGN KEY）：保证表的参照完整性。确保对一个表的数据操作不会对与之关联的表造成不利的影响

首先考虑这样的一个问题，现在由于某种原因需要将一名学生在校的全部信息删除。这里涉及学生信息的一共有两个表，一个是学生信息表，一个是学生成绩表。如果没有定义外键约束，将该名学生从学生信息表中删除时，而没有将该名学生的选课的课程成绩信息删除，这样就会造成两个关联表之间的数据不一致。

同样的问题也可能出现对两个关联表中数据的修改操作中。如果是多个表之间存在关联关系时，不定义外键约束，类似上面的情况很可能会发生。为了避免由于操作不当而引起的关联表中数据的不一致，有必要为关联表之间定义外键约束。

例子：为成绩信息定义外键

为成绩信息定义外键

其中，FOREIGN KEY 表示创建外键约束的关键字；stuID表示为成绩信息表（T_student）中的表示学生编号的列stuID定义外键约束；REFERENCES T_student (stuID)表示将列stuID定义为一个指向学生信息表T_student的主键stuID中的外键，并使用ON DELETE CASCADE定义了其删除方式为级联删除。

检查约束（CHECK）：

检查约束（CHECK）是用来限制列的取值范围或取值条件，使用CHECK约束可以保证数据规则的一致性。可以为一个列定义多个CHECK约束，当为列定义了CHECK约束后，该列中所对应的数据必须满足指定的约束条件。

定义课程信息表

列curID用来表示院系编号；VARCHAR (15)用来指定curID列的数据类型，这里将其指定为VARCHAR类型，并且设定该列中字符长度为15

列curName用来表示课程名称；VARCHAR (10)用来指定curName列的数据类型，这里将其指定为VARCHAR类型，并且设定该列中字符长度为10

列credit用来表示课程学分；INT用来指定credit列的数据类型，这里将课程学分所在的列定义为整数类型的数据

使用CHECK关键字对表示课程学分的取值范围进行约束。这里将课程学分的取值范围设置在3到8之间。如果插入的数据小于3或大于8，则SQL语句执行时会显示错误的信息。

非空约束（NOT NULL）：只用来约束列。在向该列插入数据时不允许插入空值。

创建教师信息表

在创建的教师信息表（T_teacher）中，表示教师姓名的列taeName、表示教师年龄的列age、表示教师性别的列sex和表示教师所在的院系列dept都定义为NOT NULL类型，也就是这几个列在数据插入时不允许插入空值。

使用索引

索引的分类

唯一索引：在数据表中使用UNIQUE关键字可以为一个数据定义一个唯一索引。如果在向数据表中插入一条记录时，数据库会对要插入的数据进行检查，如果发现这条记录中的值在定义了唯一索引的列出现过，那么数据库管理系统就不会将这条记录插入到数据表中。

主索引：在数据表中使用PRIMARY KEY关键字可以为一个数据定义一个主索引。所谓主索引，就是在定义的主键列中创建的索引。主索引也保证了数据记录的唯一性。

单列索引：定义在数据表中的一个数据列上的索引就是单列索引。一般在数据查询时，如果WHERE子句中经常用到的数据表中的某一列作为查询条件，为了提高查询效率，可以为该列创建单列索引。

复合索引：索引可以定义在一个数据表的多个数据列上，像这样的索引被称为复合索引。一般在数据查询时，如果WHERE子句中经常用到的数据表中的某几个列作为查询条件，为了提高查询效率，可以为这多个列创建复合索引。

聚簇索引：聚簇索引中索引项的顺序与数据表中的数据记录的物理顺序保持一致。聚簇索引在每一个数据表中只能创建一个。

为教师信息表中表示教师职称单列创建索引

这段SQL语句是为教师信息表中教师职称的列profession创建索引。其中，i_profession 表示索引的名字；T_teacher(profession)表示要为教师信息表T_teacher的列profession创建索引。

删除索引，使用DROP INDEX语句就可以将一个索引删除。索引删除的同时，数据库管理系统会将数据字典有关该索引的描述一并删除。

修改数据库中的表

向表中增加一列：

其中，ALTER TABLE 是修改表结构的关键字；ALTER TABLE之后是table_name是表的名字；ADD是关键字，用来指定数据表中要增加的列；关键字ADD后面括号中需指定新增加的列名、该列对应的数据类型和完整性约束条件。column_name表示新增加的列名，datatype用来指定该列的数据类型，constraint_condition用来表示该列的完整性约束条件，它是可选的

向教师信息表中增加一个表示教师工资的列

增加一个约束条件

其中，ALTER TABLE 是修改表结构的关键字；ALTER TABLE之后是table_name是表的名字；ADD是关键字，用来指定数据表中要增加的列；关键字ADD后面括号中需指定约束条件和新增加的列名。constraint_type用来表示指定的约束条件，column_name表示新增加的列名。

为院系信息表增加主键约束

其中T_dept表示院系信息表的名字，PRIMARY KEY表示T_dept表中的列增加主键约束，关键字PRIMARY KEY后的括号中指定了列的名字。列deptID表示院系编号。

为成绩信息表增加外键约束

其中，FOREIGN KEY表示创建外键约束的关键字；curID表示为成绩信息表（T_result）中的表示课程编码的列curID定义外键约束；REFERENCES T_curriculum (curID)表示将列curID定义为一个指向课程信息表T_curriculum的主键curID的外键。

增加一个索引

其中，T_curriculum表示课程信息表的名字；i_credit表示要增加的索引的名字；credit表示增加索引指定的列。这里列credit表示课程的学分。

修改表中的某一列

修改学生信息表中表示学生年龄列的数据类型

这段SQL语句是将学生信息中表示学生性别的列的数据类型修改为CHAR类型。其中，T_student表示学生信息表的名字；MODIFY关键字表示要对指定列进行修改；sex指学生信息表中学生的性别；CHAR(2)表示列sex的数据类型。

删除表中某一列

其中，ALTER TABLE是修改表结构的关键字；table_name是表的名字，DROP是关键字，用来表示要对数据表中某一列进行删除操作；关键字DROP后面需要指定要删除列的名字。

删除教师信息表中表示院系编号的列

删除一个约束条件

其中，ALTER TABLE是修改表结构的关键字；table_name是表的名字，DROP是关键字，用来表示要对数据表中列的约束条件删除；constraint_type表示要删除的约束条件。

删除数据库中的表

DROP TABLE 是删除数据表的关键字；table_name表示要删除的表的名字。使用DROP TABLE执行数据表的删除操作时，该表中的记录以及由此表建立的相应视图都会被同时删除。如果数据表之间存在外键约束，删除主表时使用CASCADE CONSTRAINTS级联删除从表中的外键约束。它是可选的，一般用户如果删除数据表，需要具有删除数据表的权限。