数据库-MySQL-1-数据库和数据表的基本操作

一、数据库的基本操作

1、命令行登入mysql

mysql [-h 主机名] -u <用户名> [—P 端口号] -p

2、查看数据库

SHOW DATABASES;

3、创建数据库

CREATE DATABASE `database_name`;
# 反引号是为了区分MySql的保留字和普通字符而引入的符号

4、查看数据库的定义

SHOW CREATE DATABASE `database_name`\G

5、删除数据库

DROP DATABASE `database_name`

二、数据表的基本操作

1、选择数据库

USE `database_name`;

2、查看数据表

SHOW TABLES;

3、创建数据表

（1）基本语法

CREATE TABLE `table_name`{
	字段名1 数据类型 [列级别约束条件] [默认值],
	字段名2 数据类型 [列级别约束条件] [默认值],
	...
	[表级别约束条件]
};

表的名字不能使用SQL的关键字，非要使用，必须用``（反引号），例子如下

CREATE TABLE `grade`{
	id INT(4),
	name VARCHAR(10)
};

例子中创建了一个数据表grade（年级），表里有两列数据，分别是id和name

（2）使用主键约束

主键：又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一， 且不为空，主键唯一地标识表中的一条记录

<1>单字段主键

主键由一个字段组成，SQL语句格式分两种情况

第一种是在***定义列的同时指定主键***

# 字段名 数据类型 PRIMARY KEY [默认值]
CREATE TABLE `grade`(
	id INT(4) PRIMARY KEY,
	name VARCHAR(10)
);

第二种是在***定义完所有列后指定主键***

# [CONSTRAINT <约束名>] PRINMARY KEY [字段名]
CREATE TABLE `grade`(
	id INT(4),
	name VARCHAR(10),
	PRIMARY KEY(id)
);

<2>多字段联合主键

主键由多个字段组成

# PRIMARY KEY [字段1, 字段2, ...]
CREATE TABLE `student`(
	name VARCHAR(10),
	class_id INT(4),
	sex VARCHAR(5),
	PRIMARY KEY(name, class_id)
);
# 该表用name和class_id作为主键唯一标识一条记录

（3）使用外键约束

外键：外键用来建立两张表的连接，一张表的外键可以为空，若不为空，则***该外键的值必须等于另一张表的主 键的某一个值***

应用：外键对应参照完整性，即两张表之间关联的数据一致性，举个例子，若有一个数据库school，里面有三张 表grade（年级）、class（班级）和sudent（学生），那么class中的班级所在的年级必须存在于表grade 中，student中的学生所在的班级必须存在于表class中，这样就可有效防止产生垃圾数据和错误数据

***主表（父表）***：即两张关联的表中，主键所在的表

***从表（子表）***：即两张关联的表中，外键所在的表

# [CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [, 字段名2, ...]
# REFERENCES <主表名> 主键列1 [, 主键列2, ...];
# 外键名为定义的外键约束的名称，一个表中不能有相同名称的外键
# 字段名表示子表需要添加外键约束的字段列
# 主表名即被子表外键所依赖的表的名称
# 主键列表示主表中定义的主键列
CREATE TABLE `class`(
	id INT(5) PRIMARY KEY,
	name VARCHAR(10)
);
CREATE TABLE `student`(
	id INT(8) PRIMARY KEY,
	name VARCHAR(10),
	class_id INT(5),
	CONSTRAINT fk_student_class FOREIGN KEY(class_id) REFERENCES class(id)
);

# 或者在定义两张表之后在定义外键，语法规则如下
# ALTER TABLE 子表名 ADD [CONSTRAINT 外键名] FOREIGN KEY 字段名1 [, 字段名2, ...]
#												REFERENCES <主表名> 主键列1 [, 主键列2, ...];
ALTER TABLE `student` ADD CONSTRAINT fk_student_class 
FOREIGN KEY(class_id) REFERENCES class(id);

# 指定类型的外键
# ALTER TABLE 子表名 ADD [CONSTRAINT 外键名] FOREIGN KEY 字段名1 [, 字段名2, ...]
#												REFERENCES <主表名> 主键列1 [, 主键列2, ...]
#												[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
#												[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}];
# CASCADE：级联模式，父表操作后，子表关联的数据也跟着一起操作
# RESTRICT：限制子表中的外键改变
# SET NULL：置空模式，前提外键字段允许为NULL，父表操作后，子表对应的字段被置空
# NO ACTION：默认作用

外键的默认作用有两点：

• 对子表的作用：子表在写操作的时候，如果外键字段在主表中找不到对应的匹配，操作就会失败

• 对父表的作用：对父表的主键字段进行删除和修改时，如果对应的主键在子表中被引用，操作就会失败

注意：使用外键的前提是***表存储引擎必须是innodb***，否则创建的外键无约束作用

（4）使用非空约束

非空约束指***字段的值不能为空***

# 字段名 数据类型 NOT NULL
CREATE TABLE `student`(
	id INT(11),
	name VARCHAR(10) NOT NULL
);

（5）使用唯一性约束

唯一性约束要求***该列唯一，允许为空，但只能有一个空值***

# 字段名 数据类型 UNIQUE
CREATE TABLE `student`(
	id INT(11) PRIMARY KEY,
	name VARCHAR(10) UNIQUE
);

# 或者在定义完所有列后声明
# [CONSTRAINT 约束名] UNIQUE(字段名)
CREATE TABLE `student`(
	id INT(11),
	name VARCHAR(10),
	CONSTRAINT STH UNIQUE(name)
);

唯一性约束和主键约束对比：

第一，主键唯一标识表中每条记录（一行），主键对表实施完整性约束

第二，唯一性约束唯一地标识表中的一行

第三，每张表中只允许有一个主键，但允许有多个唯一键；主键不能为空，唯一键可以有一个空值

第四，主键和唯一键都可以被另一张表的外键所引用

（6）使用默认约束

默认约束指定某列的默认值，当插入新的记录时，没有指定该列的值，就自动将该列置为默认值

# 字段名 数据类型 DEFAULT 默认值
CREATE TABLE `student`(
	in INT(11),
	sex VARCHAR(5) DEFAULT '男'
);

（7）设置表的属性值自动增加

一张表中只有主键可以设置为自动增加，自动增加的默认初始值为1，每插入一条记录，字段值加1

# 字段名 数据类型 AUTO_INCREMENT
CREATE TABLE `student`(
	id INT(11) PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10) NOT NULL
);

关于自动增加，mysql中有两个参数，分别是***AUTO_INCREMENT_INCREMENT（增加的幅度）和AUTO_INCREMENT_OFFSET（增量开始的值）***，可以用下面语句查询、更改

# 查询语句
SHOW VARIABLES LIKE 'AUTO_INC%';

# 更改语句（改为默认初始值为2，增量为3）
SET @@AUTO_INCREMENT_INCREMENT=3;
SET @@AUTO_INCREMENT_OFFSET=2;

***注意：***在MySQL 8.0版本以前，AUTO_INCREMENT_INCREMENT和AUTO_INCREMENT_OFFSET在每次mysql重 启时都会置为1

4、查看数据表结构

（1）查看表基本结构语句

DESCRIBE `table_name`;
DESC `table_name`;

（2）查看表详细结构语句

SHOW CREATE TABLE `table_name`[\G];

选择\G，可以使显示的结果更加直观，易于观看

5、修改数据表

（1）修改表名

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

（2）修改字段的数据类型

ALTER TABLE 表名 MODIFY 待修改字段 新数据类型; 

修改字段数据类型时，如表里已有该字段的数据了，需要先对表数据进行备份，然后清空数据表，再修改

（3）修改字段名

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

修改字段名时，可以同时修改该字段数据类型，也可以不修改，但是新数据类型不能为空

（4）添加字段

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

约束条件包括：非空约束、唯一性约束等等

FIRST和AFTER表示新添加的字段的位置，若不加这两个，则默认添加在最后面

举个例子，向表student中的id字段后面添加一个sex字段（要求非空）

ALTER TABLE `student` sex VARCHAR(4) NOT NULL AFTER `id`;

（5）删除字段

ALTER TABLE 表名 DROP 字段名;

（6）修改字段的排列位置

ALTER TABLE 表名 MODIFY 待修改位置字段 数据类型 (FIRST) | (AFTER 作为参照的字段); 

举个例子，将表student中的id字段的位置修改到name字段后面

ALTER TABLE `student` MODIFY `id` INT(10) AFTER `name`;

（7）更改表的存储引擎

ALTER TABLE 表名 ENGINE=更改后的存储引擎;

（8）删除表的外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

一旦删除外键，主表和从表之间的关联就解除了

6、删除数据表

（1）删除没有被关联的表

DROP TABLE [IF EXISTS] 表1[, 表2, ...];

可以同时删除多个没有被关联的表，"IF EXISTS"用于删除前判断该表是否存在，这样若表不存在，也不会报error，只会弹出warning

（2）删除被其它表关联的表

数据表之间存在外键的情况下，不能直接删除父表，可以用一下两种办法删除该表

• 先删除子表，在删除父表
• 先解除外键约束，在删除父表