重学MySQL之基本操作和约束

重新学习 MySQL 的基础知识，本章内容为数据库的基本操作和约束的介绍

使用终端操作 MySQL 数据库

如何登录 MySQL？

mysql -u用户名 -p密码
如何查看所有数据库？

SHOW DATABASES;
如何选择数据库？

USE 数据库名;
如何查看数据库中所有数据表？

SHOW TABLES;
如何查看数据表结构？

DESCRIBE 数据表名; / DESC 数据表名;
如何查看数据表定义？

SHOW CREATE TABLE 数据表名;
如何查看数据表中所有数据？

SELECT * FROM 数据表名;
如何创建数据库？

CREATE DATABASE 数据库名;
如何退出 MySQL？

EXIT;

数据的增改删

如何创建数据表？(定义 person 表)

CRAETE TABLE person(
id INT PRIMARY KEY,
name VARCHAR(20),
gender CHAR(1),
birthday DATE
);
如何往数据表中新添一条记录？（新增 Mike, id 为 1，性别男，生日2000-1-1）

INSERT INTO person VALUES(1, ‘Mike’, ‘M’, ‘2000-1-1’);
INSERT INTO person(id, name, birthday, gender) VALUES(1, ‘Mike’, ‘M’, ‘2000-1-1’);
如何修改数据表中的记录？（将 id 为 1 的人姓名更改为 Amy，性别更改为 F）

UPDATE 数据表 SET 字段1=值1,字段2=值2 … WHERE 条件;
UPDATE person SET name=’Amy’, gender=’F’ WHERE id=1;
如何修改列名？（将 birthday 修改为 birth）

ALTER TABLE 数据表 CHANGE [COLUMN] 旧列名新列名列定义 [FIRST| AFTER 列名]
ALTER TABLE person CHANGE COLUMN birthday birth DATE
如何修改列的定义？（修改 id 为自增）

ALTER TABLE 数据表 MODIFY [COLUMN] 列名列定义 [FIRST| AFTER 列名]
ALTER TABLE person MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT
如何删除数据表中的一条记录？（删除 id 为 1 的记录）

DELETE FROM 数据表 WHERE 条件;
DELETE FROM person WHERE id=1;
如何删除数据表？（删除 person 表）

DROP TABLE person;

建表约束

1. 主键约束

作用使得某个字段不重复且不得为空，确保表内所有数据的唯一性

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

建表后如何添加主键约束？

ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;
如何删除主键约束？

ALTER TABLE user DROP PRIMARY KEY;

2. 联合主键

作用： 使用多个字段来确定一条记录，受约束字段可以分别重复但不能为空

CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20)
    PRIMARY KEY(id, name)
);

3. 自增约束

作用受约束字段的值由系统自动递增分配

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

4. 唯一约束

作用受约束字段的值不能重复

注意一张表可以有多个唯一约束，但只能有一个主键约束

CREATE TABLE user (
    id INT,
    name VARCHAR(20) UNIQUE
);

如果要联合多个唯一约束，可以在末尾指定，联合唯一约束只需任意受约束字段不重复即可

CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(id, name)
);

建表后如何添加唯一约束？

ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;
如何删除唯一约束？

ALTER TABLE user DROP INDEX name;

5. 非空约束

作用受约束字段的值不能为空（NULL）

CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL,
);

建表后如何添加非空约束？

ALTER TABLE user MODIFY name VARCHAR(20) NOT NULL;
如何删除非空约束？

ALTER TABLE user MODIFY name VARCHAR(20);

6. 默认约束

作用未对受约束字段传值时，会使用默认值代替

CREATE TABLE user (
    id INT,
    name VARCHAR(20) DEFAULT 'anonymous',
);

建表后如何添加默认约束？

ALTER TABLE user ALTER COLUMN name SET DEFAULT ‘anonymous’;
如何删除默认约束？

ALTER TABLE user ALTER COLUMN name DROP DEFAULT;

7. 外键约束

作用用来在两个表的数据之间建立链接，保持主表（父表）和从表（子表）数据的一致性、完整性

注意

主表（父表）需先于从表（子表）存在于数据库中
必须为父表定义主键
外键中列的数目必须和父表的主键中列的数目相同
外键中列的数据类型必须和父表主键中对应列的数据类型相同
主表（父表）没有的记录，从表（子表）是无法引用的
主表（父表）中的记录若正在被从表（子表）引用，则无法被删除；若想删除，需先将从表（子表）中引用了该记录的数据删除

首先定义主表（父表）

CREATE TABLE class (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

再定义从表（子表）

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES class(id) 
);

在往从表（子表）中添加数据时，class_id 字段值必须是 class 表中已存在的值，否则会报错

建表后如何添加外键约束？

ALTER TABLE <数据表名> ADD <索引名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
ALTER TABLE student ADD CONSTRAINT student_class FOREIGN KEY(class_id) REFERENCE class(id);
如何删除外键约束？

ALTER TABLE student DROP FOREIGN KEY student_class;