MySQL

最新推荐文章于 2023-07-22 18:14:59 发布

TopEr⊙

最新推荐文章于 2023-07-22 18:14:59 发布

阅读量683

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/i6666huhujiji/article/details/127452623

版权

MYSQL语言分类

一般来说，MYSQL语言主要分为三类：

DDL（Data Definition Language），即数据定义语言，例如建数据库、建表等，都属于数据定义语言。
DML（Data Manipulation Language），即数据操纵语言，最常用的增删改查就属于DML，操作对象是数据表中的记录。
DCL（Data Control Language），数据控制语言，如Grant、Rollback等等，常见于数据库安全管理，多数人一般很少用。

DDL中的“增删改查”

增：Create
删：Drop / Truncate
改：Alter + add/drop/modify/change
查：Show / Desc

在介绍操作命令之前，我们先来介绍下MYSQL的常见数据类型：

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。

Text类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。
TEXT	存放最大长度为 65,535 个字符的字符串。

Number 类型：

数据类型	描述
INT(size)	-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。

Date类型：

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD
DATETIME()	日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS
TIME()	时间。格式：HH:MM:SS

SQL DDL 定义概况

字段类型（整数、小数、字符串、时间日期）

整数类型：有效的整数数据：MySQL中为了数据空间的有效使用，设定了五种整数类型（无符号需要unsigned修饰整数）

迷你整型：tinyint，使用1个字节存储整数，最多存储256个整数（-128~127）

短整型：smallint，使用2个字节存储整数

中整型：mediumint，使用3个字节存储整数

标准整型：int，使用4个字节存储整数

大整型：bigint，使用8个字节存储

小数类型：

浮点型：float / double，存储不是特别精确的数值数据

定点型：decimal，能够保证精度的小数

字符串类型：

定长型：char(L)，指定固定长度的存储空间存储字符串

变长型：varchar(L)，根据实际存储的数据变化存储空间

文本字符串：text/blob，专门用来存储较长的文本

枚举型：enum, 一种映射存储方式，以较小的空间存储较多的数据

集合型：set，一种映射存储方式，以较小的空间存储较多的数据

时间日期类型：

年：year，MySQL中用来存储年份的类型

时间戳：timestamp，基于格林威治时间的时间记录（YYYY-MM-DD HH:II::SS）

日期：date，用来记录年月日信息

日期时间：datetime，用来综合存储日期和时间

时间：time，用来记录时间或者时间段

属性（Null、Default、主键、自增长属性、唯一键、comment属性）

Null属性：数据是否允许为空

Default属性：在设计表字段的时候给定默认数据，在后续字段操作（数据新增）的时候系统没有检测到字段有数据的时候自动使用的值

主键：primary key，用来保证整张表中对应的字段永远不会出现重复数据（唯一性）

自增长属性：auto_increment，被修饰的字段在新增时，自动增长数据

唯一键：unique key，用来维护数据的唯一性

comment属性：comment，是用文字描述字段的作用的

参考资料

SQL 字段类型（4种）、属性（6种）总结

数据库|数据类型

Mysql的浮点数类型（MySQL中float、double、decimal三个浮点类型的区别与总结） - 开心学习 (studyofnet.com)

MySQL学习笔记之MySQL中的数据类型整形、浮点数、定点数、字符型、时间日期型

定点数相关知识

1. CREATE：创建数据库表

在MySQL中创建数据库和创建表中，一定会用到Create语法。

创建数据库

创建数据库语法简单，最简洁的建库语句如下：

CREATE DATABASE <数据库名>;

一般来说，Create语法都可以在对象之后增加If not exists，用于处理创建表已存在的情况，此时仅会发出警告而不会报错。

同时，还可以为新创建的数据库指定字符集 charset 和校对规则 collate：

CREATE DATABASE IF NOT EXISTS <数据库名> 
DEFAULT CHARACTER SET <字符集名>
DEFAULT COLLATE <校对规则>;

如果不指定字符集和校对规则，所建数据库默认为：utf8和utf8_general_ci。当然，需注意的是MySQL中的utf8字符集实际上是一个伪utf-8，真正意义上的uft-8在MySQL中是uft8mb4(utf-8 more bytes 4)。具体可查阅相关文档介绍。

创建表

相较于建库而言，建表的可选项较多，当然这里的可选项是指针对定义表中列字段而言。与建库类似，create建表也支持if not exists语法，用来处理表重复的情形。

常见的建表语句例如：

CREATE TABLE [IF NOT EXISTS] `表名` (
    `字段名` 列类型[属性]  [索引]  [注释],
    `字段名` 列类型[属性]  [索引]  [注释],
    `字段名` 列类型[属性]  [索引]  [注释],
    ...
)[表类型][字符集设置][注释]

CREATE Table IF NOT EXISTS <数据表名> 
                (字段名1 类型（长度） [DEFAULT] [NOT NULL] [comment '...']， 
                ....,
                ....,
                ....,
                [PRIMARY KEY], 
                [FOREIGEN KEY ])

MySQL 添加注释(comment)

MySQL 中的 information_schema

还可以在列字段后指定引擎信息，例如不想使用默认引擎innodb，而想使用MyISAM引擎，则可在建表语句后增加如下语法：

CREATE Table [IF NOT EXISTS] <数据表名>(……) ENGINE = MyISAM;

create table student(
    student_id int not null comment '学号',
    name varchar(20) not null comment '姓名'
)engine=innodb charset utf8mb4 comment '学生基本信息表';

create table student1(
    id bigint not null auto_increment comment 'ID',
    class_no varchar(20) not null comment '班级No',
    primary key (id)
)engine=innodb DEFAULT CHARSET=utf8mb4 comment = '学⽣基本信息表';

CREATE TABLE IF NOT EXISTS `student2` (
   `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
   `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

当想从一个表中筛选若干数据来建立一个新表时，可以在表名后接as语句。如下语句将使用表tb中数据创建一个一样表结构和表记录的新数据表：

CREATE Table [IF NOT EXISTS] <数据表名> AS SELECT * FROM tb;

2. DROP：删除数据表字段

与Create对应的关键字是Drop(DML)，且其操作对象也几乎一致：不仅可以Drop数据库和数据表，也可以drop函数、过程、索引等等。当然，这里还是主要介绍drop数据库和数据表。

删数据库

与create类似、又比create更为简单，drop语句用法有限，没有太多可定义的操作选项，仅需增加drop对象的关键字即可。所以在drop数据库时，即

DROP Database <数据库名> ;

再一次与create类似，为了防止数据库不存在时删除引发错误，可增加存在性判断。当然，创建时是要判断是否不存在，而drop时则判断是否存在：

DROP Database [If Exists] <数据库名> ;

删数据表

常规的删数据表与删数据库几乎完全一致，仅需更改关键字和对应表名即可：

DROP Table [If Exists] <数据表名> ;

除了Drop关键字删数据表外，还有另外一个关键字可以删数据表，即Truncate，英文截断的意思。如其名字描述的那样，Truncate与Drop(完全丢弃)不同，用Truncate删除数据表时仅是"截断"记录数据，而保留数据表的结构信息。

Truncate Table <数据表名> ;

某种意义上，Truncate的效果与Delete 不加限定条件时的效果一致，但其速度更快；且由于truncate是数据定义语言(DDL)，其操作对象不是记录，所以不支持事务和触发器等。

Truncate table(截断表)

MYSQL：如何清空表中的数据

3. ALTER：修改数据表字段

对于已定义的数据库和数据表来说，如果想修改其中的某些选项和信息，此时就要用到alter关键字。Alter英文即是更改，键盘的alt即为其缩写。

因为广义上的更改含义有多种，例如增加或删除个字段叫更改，修改一些选项信息也叫更改，所以Alter其实又常常配套以下几个附属关键字：

alter与set配套使用常用于修改字段默认值等信息，例如：

 ALTER TABLE <已有数据表> ALTER <字段> SET DEFAULT <默认值>;

对数据表增加字段：

ALTER TABLE <数据表名> ADD <新字段名> <数据类型> [……]；

drop

有增加即有删减，对已有数据表删减字段的语法为：

ALTER TABLE <数据表名> DROP <已有字段名>；

modify

对已有表进行修改，例如修改其数据类型等：

ALTER TABLE <数据表名> MODIFY <已有字段名> <数据类型>；

MySQL报错：Error Code: 1044. Access denied for user 'example1'@'%' to database

change

修改表信息的另一个关键字是应用change，例如仍然修改某个字段的数据类型，则应用change语法为：

ALTER TABLE <数据表名> CHANGE <已有字段名> <已有字段名> <数据类型>；

注意到其与modify的一个重要不同是需要写字段名2次，所以实际上change还可用于更改字段名，即将新字段名放于已有字段名之后即可，若字段名相同意味着不修改，也不会报错。

rename

modify和change都是用于更改列字段信息，rename则是用于更改表名，其语法为：

ALTER TABLE <已有数据表名> RENAME TO/AS <新数据表名>;

4. SHOW：查询数据库中信息

与DML中查询最为复杂不同，DDL中的查询用法其实最为简单。主要是应用Show关键字进行显示查询：

例如，显示数据库创建信息：

Show Create Database <数据库名>;

类似的，可用如下语句查询数据表创建信息：

Show Create Table <数据表名>;

如果应用Desc关键字，则可用于显示数据表结构信息：

Desc <数据表名>;

注意，desc查询的数据表信息，包括各字段名、数据类型、默认值等等；而show查询返回的则是创建该数据表时的SQL语句。

Show and Desc(ribe)

SHOW CREATE DATABASE `school` ; -- 查看创建数据库的语句
SHOW CREATE TABLE `student`; -- 查看student数据表的定义语句

DESC `student`; -- 查看表的结构

另外，还有一个可算是查询的用法，即为了查询当前应用的是哪个数据库时，可用如下语句：

Select Database();

返回的当前应用数据库名。值得注意的是这里的database()算作内置函数，与select version()查询MySQL版本信息用法类似。

DML中的“增删改查”

增：INSERT
删：DELETE
改：UPDATE
查：SELETE

1. INSERT：插入数据

单条数据

insert into 表名(字段名,...) values(值,...);

特点：

1、要求值的类型和字段的类型要一致或兼容

2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致

但必须保证值和字段一一对应

3、假如表中有可以为null的字段，注意可以通过以下两种方式插入null值

①字段和值都省略

②字段写上，值使用null

4、字段和值的个数必须一致

5、字段名可以省略，默认所有列（按顺序添加数据）——insert into 表名 values(值1,值2，...)

注：自增的id用0站位即可，cmd操作页面无法中文操作，添加内容无中文。

多条数据

INSERT INTO `表名`(`字段1`,`字段2`...) VALUES('值1','值2'...),('值1','值2'...),...;

INSERT INTO grade(name) VALUES(大一),(大二);

INSERT INTO student(name,birthday,id_grade) VALUES(李四,1994-11-20,2),(王五,1995-11-20,1);

字段名省略，默认所有列（按顺序添加数据）

insert into 表名 values(值1,值2，...),(值1,值2，...),(值1,值2，...),...;

insert into users values
(0,now(),'lisi','123456',52,'13212345678','eat egg'),
(0,now(),'lhh','123456',18,'15912345678','swim in the ocean');

2. DELETE：删除表中数据

指定删除

DELETE from 表名 [where 条件]

DELETE FROM student WHERE id=1;

全部删除

DELETE FROM 表名;

TRUNCATE 表名;

delete 和 truncate 区别

相同点：都能删除数据，都不会删除表结构
不同：
- truncate 重新设置自增列，计数器会归零；而delect不会影响自增
- truncate 完全清空一个数据库表，标的结构和索引约束不会变——不会影响事务

1.truncate删除后，如果再插入，标识列从1开始

delete删除后，如果再插入，标识列从断点开始

2.delete可以添加筛选条件

truncate不可以添加筛选条件

3.truncate效率较高

4.truncate没有返回值

delete可以返回受影响的行数

5.truncate不可以回滚

delete可以回滚（如同还可以从回收站回复删除文件）

3. UPDATE：更新表中的数据

update 表名 set 列名=新值,列名=新值,... where 列名=筛选值;

多个修改

update users set createDate='2021-07-07 22:22:22',passWord='222222' where id=3;

多个条件（where）

UPDATE student SET NAME='hehe' WHERE NAME='zyy' AND id_grade='2';

无条件（改所有）

UPDATE student SET NAME='all';

条件：where 字句运算符 id等于某个值，大于某个值，在某个区间内修改

操作符	含义	范围	结果
=	等于	5=6	false
<> 或者 !=	不等于	5!=6	true
BETWEEN ... AND ...	某个范围内	BETWEEN 1 AND 3	[1,3]
AND	和 &&	5>1 and 1>2	false
OR	或 \|\|	5>1 or 1>2	true

注意事项：

列尽量带上``
条件，筛选的条件，如果没有指定，则会修改所有的列

value，可以是一个具体的值，也可以是一个变量

UPDATE `student` SET birthday=CURRENT_TIME WHERE NAME='hehe' AND id_grade='2';

多个设置的属性之间，使用英文逗号隔开

4. SELECT：查询表中数据

查询全部的学生

SELECT * FROM student; (“select * from”中“*”意思是：指查询表或视图中的所有字段。)

查询指定字段

SELECT 要查询的列名称
FROM 表名
[WHERE 满足的行条件]
[GROUP BY 分组的依据]
[ORDER BY 排序的依据]
[LIMIT 限定输出结果];

SELECT student_name, student_no FROM student;
-- order by 对查询结果排序

select * from students order by age asc;#默认按升序排列,asc 可省略

select * from students order by age desc;#加 desc 按降序排列

-- group by 查询结果只显示每组的一条记录

select sid,sname,age,phone from students group by age; #数据中两个年龄16岁的只显示了一个

select sid,sname,age,phone,group_concat(age) from students group by age; #加上group_concat()函数，可以显示所有数据记录

select sid,sname,age,address,group_concat(age,address) from students group by age,address;

SQL GROUP BY 实例

我们拥有下面这个 "Orders" 表：

O_Id OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter

现在，希望查找每个客户的总金额（总订单）——想要使用 GROUP BY 语句对客户进行组合。

我们使用下列 SQL 语句：
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer		
结果集类似这样：

Customer SUM(OrderPrice)
Bush 2000
Carter 1700
Adams 2000

-- limit 限定输出行数

select * from students order by age;

select * from students order by age limit 3;#取前三条数据

select * from students order by age limit 0,3;#从第1（编号从0开始）条数据开始查询3条

select * from students order by age limit 2,2; #从第3条数据开始查询2条

more

SQL GROUP BY 语句

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Customer	SUM(OrderPrice)
Bush	2000
Carter	1700
Adams	2000

别名（好理解）

给结果起一个名字 AS 可以给字段起别名，也可以给表起别名

SELECT student_name AS '学号', student_no AS '姓名' FROM student;

拼接字段（一同显示关联性强的信息）

concat()函数可用于select语句，同样可用于更新列的内容

括号中列名不用加引号，否则会变成引号中内容的组合；

select concat(first_name, last_name) from 表名
update 表名 set 列名 = concat(列A，列B，… )

Example

SQL约束

SQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

SQL CREATE TABLE + CONSTRAINT 语法

CREATE TABLE *table_name*
(
*column_name1 data_type*(*size*) *constraint_name*,
*column_name2 data_type*(*size*) *constraint_name*,
*column_name3 data_type*(*size*) *constraint_name*,
....
);

在 SQL 中，我们有如下约束：

1. NULL -- 数据是否允许为空——默认情况下数据是允许为Null的

一般有效的数据都必须设定为Not Null来保证数据的有效性，数据为空一般不具备运算和分析价值

MySQL默认情况下指定字段为NULL修饰符，如果一个字段指定为NOT NULL，MySQL则不允许向该字段插入空值(这里面说的空值都为NULL)，因为这是“规定”。

但是在自增列和TIMESTAMP字段中，这个规则并不适用。向这些字段中插入NULL值将会导致插入下一个自动增加的值或者当前的时间戳。
- NOT NULL：指示某列不能存储 NULL 值，必须填上数据。
- NULL：指定该字段可以为NULL 值。
MySQL 中NULL和空值的区别

SQL语句(Null,DDL)

MySQL自动更新列时间戳CURRENT_TIMESTAMP

示例：用户信息表：用户名、密码、姓名、年龄、注册时间

create table user(
	username varchar(50) not null,
    password char(32) not null,
    name varchar(20),
    age tinyint unsigned,
    register_time int unsigned not null
)charset utf8mb4;

2. UNIQUE -- 保证某列的每行必须有唯一的值。

unique key，用来维护数据的唯一性
一个表中可以有多个唯一键
唯一键与主键的区别在于 唯一键允许数据为Null（而且Null的数量不限）
唯一键与主键一样，可以提升字段数据当做条件查询的效率（索引）
复合唯一键：多个字段共同组成
- unique key(字段1,字段2,…字段N)
- 联合唯一键一般不会出现，最多2个字段组成

示例：学生成绩表：一个学生只有一个"学科（成绩）"属性，但是可以添加多个学科

# 学号和学科编号共同组成唯一
create table student(
	id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2),
    unique key(stu_name,course)
)charset utf8mb4;

insert into student values(null,'Timmy','Math',50);
insert into student values(null,'Sun','Science',80);

唯一键管理：在表创建后对唯一键的管理

删除唯一键相对麻烦，一张表中不止一个唯一键

新增唯一键要保证字段里的数据具有唯一性

删除唯一键：alter table 表名 drop index 唯一键名字；

新增唯一键： alter table 表名 add unique key(字段列表)；

1、删除表中已有的唯一键
alter table student drop index stu_name;
2、追加唯一键
alter table student add unique key stu_course (stu_name,course);

3. PRIMARY KEY（主键）-- 用来保证整张表中对应的字段永远不会出现重复数据（唯一性）

NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。（如学生学号这类变量就需要设置PRIMARY KEY)
- 主键在一张表中只能有一个，主键的另外一个特性是能够提升主键字段作为查询条件的效率（索引）
  - 逻辑主键：数据没有具体业务意义，纯粹是一种数值数据
    - 逻辑主键通常是整数：int
    - 逻辑主键目的是方便检索和数据安全（不暴露数据真实信息）
  - 复合主键：多个字段共同组成不能重复的数据
    - primary key(字段1,字段2,…字段N)
    - 联合主键使用不多，一般也不会超过2个字段

示例：银行账户信息：账户、姓名、余额

# 银行账户具有唯一性，不能重复，也不允许为空
create table t_25(
    account varchar(17) primary key,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8mb4;

# 复合主键
create table t_26(
	account varchar(17),
    name varchar(20),
    money decimal(16,2) not null default 0.00,
    primary key(account,name)
)charset utf8mb4;

# 一般使用逻辑主键
create table t_27(
	id int unsigned primary key,
    account varchar(17) not null,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8mb4;

主键管理：在创建表并且已经有数据后的维护

删除主键

追加主键

修改主键（先删除后新增）

4. DEFAULT -- 规定没有给列赋值时的默认值。
- 可以使用DEFAULT修饰符为字段设定一个默认值。当插入记录时，忘记传该字段的值时，MySQL会自动设置上该字段的默认值
- 如果一个字段中没有指定DEFAULT修饰符，MySQL会依据这个字段是NULL还是NOT NULL自动设置默认值。
  - 如果指定字段可以为NULL，则MySQL为其设置默认值为NULL。
  - 如果是NOT NULL字段，MySQL对于数值类型插入0，字符串类型插入空字符串，时间戳类型插入当前日期和时间，ENUM类型插入枚举组的第一条。

NOT NULL DEFAULT '' 不能为null 默认为 ''

NULL DEFAULT NULL 可以为null 默认为null

示例：用户开户：银行卡账号、身份证号码、姓名、账户余额

create table account(
	account varchar(19) not null,
    id_card char(18) not null,
    name varchar(20) not null,
    money decimal(16,2) default 0.00 not null
)charset utf8mb4;

5. AUTO_INCREMENT -- 修饰符只适用于INT字段，表明MySQL应该自动为该字段生成一个数(每次在上一次生成的数值上面加1。

auto_increment，被修饰的字段在新增时，自动增长数据
自增长只能是整数类型，而且对应的字段必须是一个索引（通常逻辑主键）
一张表只能有一个自动增长
自增长一般是配合逻辑主键实现自动增长
- 整型字段
- 存在索引（主键）
自增长数据可以理解为一种默认值，如果主动给值，那么自动增长不会触发
自增长由两个变量控制
- 初始值：auto_increment_offset，默认是1
- 步长：auto_increment_increment，默认值也是1
- 查看自增长控制：show variables like 'auto_increment%';

示例：记录学生信息：学号和姓名

# 学生信息：学号自动增长
create table student(
    id int primary key auto_increment,
	stu_no int(8) zerofill not null,
    stu_name varchar(20) not null
)charset utf8mb4;

自增长管理：在某些特殊使用下，需要自增长按照需求实现
修改表中自增长的值：让下次自增长按照指定值开始
修改自增长控制：调整自增长的变化
1、修改表中自增长的值：跳过一些值，直接从下次开始按照新的目标值出现

注意：奇数会保留原值，偶数会自动加1（可能出现的情况）
alter table student auto_increment = 50;
2、修改自增长控制：步长和起始值（修改针对的是整个数据库，而非单张表）
set auto_increment_increment = 2;	# 当前用户当前连接有效（局部）
set @auto_increment_increment = 2;	# 所有用户一直有效（全局）--设置步长为2 
set @auto_increment_offset=3;    --设置初始值为3

MySQL中 auto_increment如何修改初始值和步长

6. *FOREIGN KEY -- 保证一个表中的数据匹配另一个表中的值的参照完整性。
- 一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY。
- FOREIGN KEY 约束用于预防破坏表之间连接的行为。
- FOREIGN KEY 约束能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

方式一：创建表的时候，增加约束（比较复杂）

-- 年级表
CREATE TABLE `grade` (
  `id` INT(30) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `name` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

DROP TABLE `student`;

-- 学生表 id_grade 字段 需要引用年级表的 id字段
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
  `id_grade` INT(30) NOT NULL COMMENT '年级id',
  PRIMARY KEY(`id`),
  KEY `fk_id_grade`(`id_grade`),
  CONSTRAINT `fk_id_grade` FOREIGN KEY (`id_grade`) REFERENCES `grade` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

从表：谁创建外键谁就是子表

主表：这个外键所依赖的表

方式二：创建表成功后，再添加外键约束

-- 年级表
CREATE TABLE `grade` (
  `id` INT(30) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `name` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 学生表
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
  `id_grade` INT(30) NOT NULL COMMENT '年级id',
  PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- DROP TABLE `grade`;
-- DROP TABLE `student`;


-- 创建表的时候没有外键关系
ALTER TABLE `student` 
ADD CONSTRAINT `fk_id_grade` FOREIGN KEY (`id_grade`) REFERENCES `grade` (`id`);
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 哪个表 (哪个字段)

--撤销 FOREIGN KEY 约束
ALTER TABLE `student`
DROP CONSTRAINT `fk_id_grade`

7. *CHECK -- 保证列中的值符合指定的条件。
- CHECK 约束用于限制列中的值的范围。
- 如果对单个列定义CHECK约束，那么该列只允许存在特定的值。
- 如果对一个表定义CHECK约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

示例：

创建表时的 CHECK 约束

# 命名 CHECK 约束，并定义列的 CHECK 约束
create table student1(
	student_id int not null comment '学号',
    name varchar(20) not null comment '姓名',
    CONSTRAINT  showtime CHECK (student_id > 0 AND name = 'QinHuangDao')
)engine=innodb charset utf8mb4 comment '学生基本信息表';

修改表时再加 CHECK 约束

# 命名 CHECK 约束，并定义列的 CHECK 约束
ALTER TABLE student1
ADD CONSTRAINT showtime CHECK (student_id > 0 AND name = 'Timmy');

撤销 CHECK 约束

ALTER TABLE student1
DROP CONSTRAINT showtime;

SQL学习-Constraints（约束）

MySQL查询某个表的外键约束

TopEr⊙

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL

MySQL
复制链接

扫一扫

MySQL

MYSQL语言分类

DDL中的“增删改查”

DML中的“增删改查”

“相关推荐”对你有帮助么？