MySQL学习笔记（2）

2. MySQL学习第二天

2.1 MySQL的操作

MySQL用以存放表、约束、索引、视图、存储过程等对象

MySQL数据库的操作主要包括

• 创建数据库
• 选择数据库
• 修改数据库
• 查看数据库
• 删除数据库
• 数据库的备份与还原

2.2 命令行工具

直接输入命令进行数据库操作

CREATE DATABASE 数据库名；创建数据库

SHOW VARIABLES LIKE ‘datadir’；查找数据库在哪

USE 数据库名；选择数据库

ALTER DATABASE [数据库名] [DEFAULT] CHARACTER SET <字符集> | [DEFAULT] COLLATE <比较规则>；修改数据库

SHOW DATABASES；显示所有数据库

DROP DATABASE 数据库名；删除数据库

2.3 数据类型

数值类型：

• 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
• 小数类型：DOUBLE、FLOAT、DECIMAL
• 日期类型：DATE、TIME、YEAR、DATETIME、TIMESTAMP
• 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
• 复合数据类型：Enum、Set
• 二进制类型：Binary、VARBinary、Bit、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

选择合适的类型可以提升存储速度，节省存储空间

选择类型的一些原则：

1. 在符合应用要求的前提下，尽量使用短的数据类型
2. 数据类型越简单越好
3. 在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串类存储日期和时间
4. 尽量采用精确小数类型（DECIMAL）而不是浮点数类型
5. 尽量避免NULL字段，为字段添加NOT NULL约束

2.4 设计表结构

1. 字段名要通俗易懂且具有代表性，字段名不允许重复，为了编程序方便，尽量使用英文名字
2. 字段类型根据需求选择合适的，在满足需求的情况下字段类型尽可能小
3. 不参与数学计算的数字定义为字符类型
4. 字段长度是指能容纳该字段的最大数量
5. 字符集编码有中文字符时统一采用UTF8类型

2.5 创建表

以一个学校的课程管理系统为例，创建如下的一个数据库表

学院表department

字段名	数据类型	长度	是否空值	是否主键外键	默认值	备注
Department_id	char	3	no	主键		学院编号
Department_name	varchar	10	no			学院名称

CREART TABLE department(
Department_id char(3) NOT NULL PRIMARY KEY COMMENT '学院编号',
Department-name varchar(10) NOT NULL COMMENT '学院名称',
)ENFINE=InnoDB DEFAULT CHARSET=utf8;

教师表teacher

字段名	数据类型	长度	是否空值	是否主键外键	默认值	备注
Teacher_id	char	5	no	主键		教师编号
Teacher_name	varchar	4	no			教师姓名
Department_id	char	3	no	外键		学院编号
Teacher_gender	cahr	1	no		男	性别

CREATE TABLE teacher(
Teacher_id char(5) NOT NULL PRIMARY KEY COMMENT '教师编号',
Teacher_name varchar(4) NOT NULL COMMENT '教师姓名',
Department_id cahr(3) NOT NULL COMMENT '学院编号',
Teacher_gender char(1) NOT NULL DEAFAULT '男' COMMENT '学院编号',
CONSTRAINT teacher_department FOREIHN KEY (Department_id)
REFERENCES deparment(Department_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

课程表course

字段名	数据类型	长度	是否空值	是否主键外键	默认值	备注
Course_id	char	4	no	主键		课程号
Course_name	varchar	10	no			课程名称
Course_capacity	tinyint	4	no		60	人数上限
Teacher_id	char	5	no	外键		教师编号

CREATE TABLE course(
Course_id char(4) NOT NULL PRIMARY KEY COMMENT '课程号',
Course_name varchar(10) NOT NULL COMMENT '课程名称',
Course_capacity tinyint(4) NOT NULL DEFAULT 60 COMMENT '人数上限',
Teacher_id char(5) NOT NULL COMMENT '教师编号',
CONSTRAINT course_teacher FOREIGN KEY (Teacher_id)
REFERENCES teacher(Teacher_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

班级表class

字段名	数据类型	长度	是否空值	是否主键外键	默认值	备注
Class_id	char	8	no	主键		班级编号
Class_name	varchar	8	no			班级名称
Class_year	smallint	4	no			年度
Department_id	char	3	no	外键		学院编号

CREATE TABLE class(
Class_id char(8) NOT NULL PRIMARY KEY COMMENT '班级编号',
Class_name varchar(8) NOT NULL COMMENT '班级名称',
Class_year smallint(4) NOT NULL COMMENT '年度',
Department_id char(3) NOT NULL COMMENT '学院编号',
CONSTRAINT class_department FOREIGN KEY (Department_id)
REFERENCES department(Department_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生表student

字段名	数据类型	长度	是否空值	是否主键外键	默认值	备注
Student_id	char	12	no	主键		学号
Student_name	varchar	4	no			姓名
Class_id	char	4	no	外键		班级编号
Student_Phone	varchar	18				电话号码

CREATE TABLE student(
Student_id char(12) NOT NULL PRIMARY KEY COMMENT '学号',
Student_name varchar(4) NOT NULL COMMENT '姓名',
Class_id char(4) NOT NULL COMMENT '班级编号',
Student_phone varchar(18) NOT NULL COMMENT '电话',
CONSTRAINT student_class FOREIGN KEY (Class_id)
REFERENCES class(Class_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

选课表choose

字段名	数据类型	长度	是否空值	是否主键外键	默认值	备注
Choose_id	int	11	no	主键		auto_increment
Student_id	char	12	no	外键		学号
Course_id	char	4	no	外键		课程号
Choose_time	datetime		no			选课时间
Choose_report	int				0	成绩

CREATE TABLE choose(
Choose_id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
Student_id char(12) NOT NULL COMMENT '学号',
Course_id char(4) NOT NULL COMMENT '课程号',
Choose_time dattime NOT NULL COMMENT '选课时间',
Choose_report int DEFAULT 0 COMMENT '成绩'，
CONSTRAINT choose_course FOREIGM KEY (Course_id)
REDERENCES course(Course_id),
CONSTRAINT choose_student FOREIGM KEY (Student_id)
REDERENCES student(Student_id),
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中

• PRIMARY KEY用于定义主键
• COMMENT用于定义别名
• NOT NULL是非空约束
• DEFAULT为该字段加默认值
• AUTO_INCREMENT为自增型属性，一般用作主键，数值自动加1
• CONSTRAINT用于设置外键
• ENGINE=InnoDB设置表的存储引擎
• DEFAULT CHARSET=utf8是设置给表的默认字符集

显示表结构：DESCRIBE/DESC 表名

查看表的详细信息：SHOW CREATE TABLE 表名

复制表结构：CREATE TABLE 新表名 LIKE 源表名

复制表结构和表记录：CREATE TABLE 新表名 SELECT * FROM 源表

修改表名：RENAME TABLE 旧表名 TO 新表名 或者 ALTER TABLE 旧表名 RENAME 新表名