简介
本系列(MySQL学习笔记)是我基于B站上SQL播放量第一的MySQL 基础+高级篇- 数据库 -sql -尚硅谷视频所做的笔记,方便大家学习和掌握MySQL。
说明
1.这个系列基本包含了视频中老师讲课的所有内容,包括知识点、案例、部分测试题。
2.所需的配套资料(来自B站评论区)
@黎曼的猜想:
配套资料下载–>公众号公众号 DragonWell 回复:mysql 注意:是公众号!!是公众号!!是公众号,点那个 搜一搜 搜索!!!
不是私人号,是公众号!!! (可以直接在 搜一搜 搜索 dragonwell) 名称是:DragonWell公众号
3.SQLyog的安装(来自B站评论区)
@江左萌粽猪 链接:https://pan.baidu.com/s/18PDjbqEeDSAjQM0VQye6og 提取码:qjuz
目录
MySQL学习笔记1:数据查询语言(DQL语言)select基本用法
MySQL学习笔记2:where 条件查询
MySQL学习笔记3:order by 排序查询
MySQL学习笔记4:常见函数(一) 单行函数
MySQL学习笔记5:常见函数(二) 分组函数
MySQL学习笔记6:group by 分组查询
MySQL学习笔记7:join 连接查询(SQL 92标准)
MySQL学习笔记8:join 连接查询(SQL 99标准)
MySQL学习笔记9:子查询(内查询)
MySQL学习笔记10:union 联合查询
MySQL学习笔记11:limit 分页查询
MySQL学习笔记12:数据操纵语言(DML语言)insert update delete
MySQL学习笔记13:数据定义语言(DDL语言)create alter drop
MySQL学习笔记14:常见数据类型
MySQL学习笔记15:常见约束
MySQL学习笔记16:事务控制语言(TCL语言)
MySQL学习笔记17:视图
MySQL学习笔记18:存储过程
MySQL学习笔记19:函数
MySQL学习笔记20:流程控制结构
常见约束
约束:一种限制,用于限制表中的数据,为了保证表中数据的准确可靠性
分类:六大约束
not null:非空约束,用于保证该字段的值不能为空
比如姓名、学号等
default:默认约束,用于保证该字段有默认值
不插入的时候,自动显示默认值
Primary key:主键约束,用于保证该字段的值有唯一性,并且非空,
比如学号、员工编号等
Unique:保证唯一性,可以为空,
比如座位号
check:检查约束,mysql不支持,
比如年龄
foreign key:外键约束,用于限制两个表的关系,用于保证该字段值必须来自主表的关联列的值
外键举例
表stuinfo,id 1,2,3,4 ,majorid 要保证在major表里1,2
表major, id 只有1,2
在从表(stuinfo的majorid)添加外键约束,引用主表(major)列(id)的值
比如专业编号
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:
六大约束语法上都支持,但外键约束没有效果
表级约束:
除了非空和默认,其他的都支持
create table 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
);
主键和唯一键的对比:
保证唯一性 | 是否保证为空 | 一个表几个 | 是否允许组合 | |
---|---|---|---|---|
主键 | 可以 | 不允许 | 一个表至多有1个 | 允许但不推荐 |
唯一 | 可以 | 允许 | 一个表可以有多个 | 允许但不推荐 |
外键:
1.在从表设置外键关系
2.从表的外键列类型和主表的关联列类型保持一致,名称无所谓
3.要求主表的关联列必须是一个key(一般是主键、唯一键)
4.插入数据时,先插入主表(major),再插入从表(stuinfo)
删除数据时,先删除从表,再删除主表
DESC stuinfo;
insert into major values(1,'java');
insert into major values(2,'h5');
insert into stuinfo values(1,'john','男',null,17,1);
insert into stuinfo values(2,'lily','男',null,18,2);
select * from stuinfo; # 不报错,seat都是NULL
一.创建表时添加约束
1.添加列级约束
语法:
直接在字段类型后面加 约束类型
只支持 primary key,not null,unique,default
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo (
id INT PRIMARY KEY, # 主键
stuname VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查
seat INT UNIQUE, #唯一
age INT DEFAULT 18, #默认
majorid INT REFERENCES major(id) #外键约束列级约束不支持,没起到效果
);
CREATE TABLE major(
id INT PRIMARY KEY, # 主表的关联列是主键或者唯一键才可以
majorname VARCHAR(20)
);
DESC stuinfo;
# 查看表中所有索引(主键primary key、外键foreign key、唯一unique键自动产生索引)
SHOW INDEX FROM stuinfo; # 只有主键id、唯一键seat
2.添加表级约束
语法:在各个字段最下面
【constraint 约束名】 约束类型(字段名)
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id), #主键
CONSTRAINT uq UNIQUE(seat), # 唯一键
CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
);
DESC stuinfo;
SHOW INDEX FROM stuinfo; # id,seat,majorid都显示
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
PRIMARY KEY(id), #主键
UNIQUE(seat), # 唯一键
CHECK(gender='男' OR gender='女'),#检查
FOREIGN KEY(majorid) REFERENCES major(id) #外键
);
DESC stuinfo;
SHOW INDEX FROM stuinfo; # id,seat,majorid都显示
通用写法
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
sex CHAR(1),
seat INT UNIQUE,
age INT DEFAULT 18,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
DESC stuinfo;
组合:以主键为例
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
sex CHAR(1),
seat INT UNIQUE,
age INT DEFAULT 18,
majorid INT,
PRIMARY KEY(id,stuname), # 组合主键
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
SHOW INDEX FROM stuinfo; # id,stuname都显示为主键
INSERT INTO stuinfo VALUES(1,'john','男',NULL,17,1);
INSERT INTO stuinfo VALUES(1,'lily','男',NULL,18,2);
INSERT INTO stuinfo VALUES(2,'lily','男',NULL,19,2);
SELECT * FROM stuinfo; # 以上都不报错,组合的两个字段里有一个不一样就可以了
二.修改表时添加约束
添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束
添加表级约束(可以起名字)
alter table 表名 add 【constraint 约束名】 新约束(字段名) 【外键引用】
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
sex CHAR(1),
seat INT UNIQUE,
age INT DEFAULT 18,
majorid INT
);
1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
DESC stuinfo;
SHOW INDEX FROM stuinfo;
2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
3.添加主键
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
4.添加唯一键
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo ADD UNIQUE(seat);
5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_1 FOREIGN KEY(majorid) REFERENCES major(id);
三.修改表时删除约束
1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL ;
2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;# 不写id
4.删除唯一键
ALTER TABLE stuinfo DROP INDEX seat ;
5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_1;
测试
CREATE TABLE emp2(
id INT
);
DESC emp2;
CREATE TABLE dept2(
id INT
);
DESC dept2;
id添加主键约束
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);
#
ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);
向表emp2中添加列dept_id,并在其中定义外键约束,与之相关联 的列是dept2表中的id列
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_1 FOREIGN KEY(dept_id) REFERENCES dept2(id);
SHOW INDEX FROM emp2;