数据库和表的基本操作

2019-10-4数据库和表的基本操作

数据库—>数据表—>字段

一、数据库和表的基本操作

1、创建和查看数据库

①创建数据库

CREATE DATABASE 数据库名称;

实例：创建一个名称为itcast的数据库

CREATE DATABASE itcast;

②查看已经存在的数据库

SHOW DATABASES;

③查看某个已经创建的数据库信息

SHOW CREATE DATABASE 数据库名称;

实例：查看已经存在的数据库信息

SHOW CREATE DATABASE itcase;

2、修改数据库

①修改数据库编码

ALTER DATABASE 数据库名称 DEFAULT CHARACTER 编码方式 COLLATE 编码方式_bin;

实例：将数据库itcast的编码方式修改为gbk

ALTER DATABASE itcast DEFAULT CHARACTER SET gbk COLLATE gbk_bin;

3、删除数据库

①删除数据库

DROP DATABASE 数据库名称;

实例：删除名称为itcast的数据库

DROP DATABASE itcast;

4、创建数据表

①创建数据表

CREATE TABLE 表名;
(
   字段名 1,数据类型[完整性约束条件],
   字段名 2,数据类型[完整性约束条件],
   ...
   字段名 n,数据类型[完整性约束条件],
)

实例：创建一个用于存储学生成绩的表tb_grade

字段名称	数据类型	备注说明
id	INT	学生的编号
name	VARCHAR(20)	学生的姓名
grade	FLOAT	学生的成绩

首先需要创建一个数据库

CREATE DATABASE itcast;

选择创建表的数据库

USE itcast;

创建数据表

CREATE TABLE tb_grade
(
   id INT(11),
   name VARCHAR(20),
   grade FLOAT
);

验证数据表是否创建成功

SHOW TABLES;

5、查看数据表

②使用SHOW CREATE TABLE查看数据表

SHOW CREATE TABLE 表名;

实例：使用SHOW CREATE TABLE查看tb_grade表

SHOW CREATE TABLE tb_grade;
//或者
SHOW CREATE TABLE tb_grade\G;

②使用DESCRIBE语句查看数据表

DESCRIBE 表名;
//或者简写为
DESC 表名;

实例：使用DESCRIBE查看数据表

DESCRIBE tb_grade;

6、修改数据表

①修改表名

ALTER TABLE 旧表名 RENAME [TO] 新表名;

//TO是可选的，在SQL语句中是否出现不影响语句的执行

实例：将数据库itcast中的tb_grade表名改为grade
修改数据库的表名之前，首先使用SHOW TABLES语句查看数据库中所有表

SHOW TABLES;

开始修改表名

ALTER TABLE tb_garde RENAME TO grade;

检查表名是否修改正确

SHOW TABLES;

②修改字段名

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;

实例：将数据表grade中的name字段改为username，数据类型保持不变

ALTER TABLE grade CHANGE name username VARCHAR(20);

验证修改是否成功

DESC grade;

③修改字段的数据类型

ALTER TABLE 表名 MODIFY 字段名 数据类型;

实例：将id字段的数据类型由INT(11)修改为INT(20)
修改之前，先查看数据表的结构

DESC grade;

修改操作

ALTER TABLE garde MODIFY id INT(20);

验证修改成功

DESC grade;

④添加字段

ALTER TABLE 表名 ADD 新字段名 数据类型
[约束条件][FIRST|AFTER 已存在字段名]

实例：在数据表grade中添加一个没有约束条件的INT类型的字段age

ALTER TABLE grade ADD age INT(10);

⑤删除字段

ALTER TABLE 表名 DROP 字段名;

实例：删除grade表中的age字段

ALTER TABLE grade DROP age;

⑥修改字段的排列位置

ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;

实例：将grade的username字段修改为表的第一个字段

ALTER TABLE grade MODIFY username VARCHAR(20) FIRST;

实例：将数据表grade的id字段插入到grade字段后面

ALTER TABLE grade MODIFY id INT(20) AFTER grade;

7、删除数据表
①删除没有被其他表关联的数据表

DROP TABLE 表名;

实例：删除数据表grade

DROP TABLE grade;

二、数据类型
1、整数类型

数据类型	字节数
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT	4
BIGINT	8

2、浮点数和定点数类型

数据类型	字节数
FLOAT	4
DOUBLE	8
DECIMAL(M,D)	M+2,其中M表示数据的长度，D表示小数点后的长度

3、日期与时间类型

数据类型	字节数
YEAR	1
DATE	4
TIME	3
DATETIME	8
TIMESTAMP	4

4、字符串和二进制类型

数据类型	类型说明
CHAR	表示固定长度的字符串
VARCHAR	表示可变长度的字符串
BINARY	表示固定长度的二进制数据
VARBINARY	表示可变长度的二进制数据
BOLB	表示二进制大数据
TEXT	表示大文本数据
ENUM	表示枚举类型，只能存储一个枚举字符串值
SET	表示字符串对象，可以有零或多个值
BIT	表示字段类型

①CHAR和VARCHAR类型
定义CHAR和VARCHAR

CHAR(M)或VARCHAR(M)
//M指字符串的最大长度

插入值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
‘’	‘’	4字节	‘’	1字节
‘ab’	‘ab’	4字节	‘ab’	3字节
‘abc’	‘abc’	4字节	‘abc’	4字节
‘abcd’	‘adcd’	4字节	‘abcd’	5字节
‘abcdef’	‘abcd’	4字节	‘abcd’	5字节

CHAR(4)时，不管插入值的长度是多少，所占用的存储空间都是4字节。而VARCHAR(4)所对应数据所占用的字节数为实际长度加1。

②BINARY和VARBINARY
定义

BINARY(M)或VARBINARY(M)
//M指的是二进制数据的最大字节长度

需要注意的是，BINARY类型的长度是固定的，如果数据长度不足最大长度，将在数据后面用’\0’补齐，达到最终长度。例如BINARY(3)，当插入a时，实际存储的数据为’a\0\0’

③TEXT类型
用于表示大文本数据，例如文章内容、评论等
④BLOB类型
一种特殊二进制类型，用于表示数据量很大的二进制数据，例如图片、PDF文档等
⑤ENUM类型
定义

ENUM('值1','值2','值3','值4'...'值n')
//枚举列表

ENUM类型的数据只能从枚举列表中取，并且只能取一个。注意，枚举列表中的每一个值都有一个顺序编号，MySQL中存入的就是这个顺序编号，而不是列表中的值

⑥SET类型
用于表示字符串对象，它的值可以有零个或多个

SET('值1','值2','值3','值4'..'值n')

与ENUM相似，同上

⑦BIT类型
表示二进制数据

BIT(M)

三、表的约束
表的约束：防止数据表中插入错误数据，定义维护数据库完整性的规则

约束条件	说明
PRIMARY KEY	主键约束
FOREIGN	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段默认值

1、主键约束（PRIMARY KEY）
主键设置：快速查找表中某条信息
①单字段主键：由一个字段构成的主键
定义

字段名 数据类型 PRIMARY KEY

实例：创建一个数据表example01，并设置id作为主键

CREATE TABLE example01(id INT PRIMARY KEY,
                       name VARCHAR(20),
                       grade FLOAT );

②多字段主键
定义

PRIMARY KEY(字段名1,字段名2,字段名3,..,字段名n)
//"字段名1,字段名2,字段名3,..,字段名n"指的是构成主键的多个字段的名称

实例：创建一个数据表example02，在表中将stu_id和course_id两个字段共同作为主键

CREATE TABLE example02(stu_id INT,
                       course_id INT,
                       grade FLOAT,
                       PRIMARY KEY(stu_id,course_id)
                       );

注：每个数据表中最多只能有一个主键约束，定义为PRIMARY KEY的字段不能有重复值且不能为NULL值

2、非空约束（NOT NULL）
非空约束：指字段的值不能为NULL
定义

字段名 数据类型 NOT NULL;

实例：创建一个数据表example04，将表中的name字段设置为非空约束

CREATE TABLE example04(id INT PRIMARY KEY,
                        name VARCHAR(20) NOT NULL,
                        grade FLOAT);

注意：在同一个数据表中，可以定义多个非空字段

3、唯一约束（UNIQUE）
唯一约束：保证数据表中字段的唯一性，即表中字段的值不能重复出现
定义

字段名 数据类型 UNIQUE;

实例：创建一个数据表example05，将表中stu_id设置为唯一约束

CREATE TABLE example05(id INT PRIMARY KEY,
                       stu_id INT UNIQUE,
                       name VARCHAR(20) NOT NULL
                        );

4、默认约束（DEFAULT）
默认约束：用于数据表中的字段指定默认值，即当在表中插入一条新记录时，如果没有给这个字段赋值那么数据库系统会自动认为这个字段插入默认值

字段名 数据类型 DEFAULT 默认值;

实例：创建一个数据表example06，将表中grade字段的默认值设置为0

CREATE TABLE example06(id INT PRIMARY KEY AUTO_INTCREMENT,
                       stu_id INT UNIQUE,
                       grade FLOAT DEFAULT 0
                       );