MySQL数据库万字保姆级教程（有这一篇就够了，建议收藏）

前言

这是我呕心沥血整理出的MySQL教程，一方面为自己复习回顾用，一方面也想将自己的知识分享给大家。
注意：文中命令都是用的大写，但MySQL中的命令采用大小写均可

1 数据库基本概念

1.1 数据Data

定义：数据是用来记录信息的可识别的符号，是信息的具体表现形式

表现形式：不仅包括数字和文字，还包括图像、图形、声音、影像等。这些数据可转化为计算机可识别的标识，并以二进制形式存入计算机

1.2 数据库Database

定义：数据库是存储数据的基地，具体而言就是长期存放在计算机内的有组织的、可共享的数据集合

基本特征：

• 按一定的数据模型组织、描述和储存
• 可为各种用户共享
• 冗余度较小
• 数据独立性较高
• 易扩展

1.3 数据库管理系统DBMS

定义：位于用户与操作系统之间的一层数据管理软件，是一个大型复杂的软件系统；负责数据库的建立、操作、管理和维护的软件系统；
科学地组织和存储数据、高效地获取和维护数据

数据库管理系统是数据库系统的核心部分，用户在数据库系统中的一些操作都是由数据库管理系统来实现的。

主要功能：

• 数据定义功能
• 数据组织、存储和管理功能
• 数据操作功能
• 数据库的事务管理和运行管理功能

1.4 数据库系统DBS

定义：在计算机系统中引入数据库后的系统构成

构成：数据库系统DBS=数据库Database+数据库管理系统DBMS+数据库管理员DBA+应用程序+用户

1.5 MySQL优点

• 成本低：开放源代码，社区版本可以免费使用
• 性能优良：执行速度快，功能强大
• 操作简单：安装方便快捷，有多个图形客户端管理工具和一些集成开发环境
• 兼容性好：安装于多种操作系统，跨平台性好

2 数据定义

2.1 SQL定义的模式种类、特点以及功能

SQL数据定义的模式种类：模式、基本表、索引、视图

SQL数据定义的特点：
1.数据库定义可以不断增长（不必一开始就定义完整）
2.数据库定义可以随时修改（不必一开始就完全合理）

SQL数据定义的功能：

2.2 完整性规则

定义表需要遵守三个完整性规则

1.实体完整性
需要由一个主键（PRIMARY KEY），并且主键唯一，不能部分或全部为空
多个属性列构成则必须在表级上定义

PRIMARY KEY(A1,A2,...)
1

2.参照完整性
在某些表中，还需要外键（FOREIGN KEY）

FOREIGN KEY (A) REFERENCES S(B)
1

3.用户自定义完整性

• 空值NULL/NOT NULL，表示不知道、不清楚、没有数据，只能用于列约束
• 唯一值UNIQUE，表示某列或多个列的组合取值必须唯一，可用于列和表的约束
• CHECK（约束条件），例如：CHECK(A>=0)

2.3 数值类型

选择数据类型时应遵循的原则：
1.选择最小的可用类型，如果该字段的值不会超过127，则使用TINYINT比INT效果好
2.对于完全都是数字的，即无小数点时，可以选择整数类型，比如年龄
3.浮点数类型用于可能具有小数部分的数，比如学生成绩
4.在需要表示金额等货币类型时优先选择DECIMAL数据类型

2.4 日期时间类型

2.5 字符串类型

注：
1.CHAR是定长字符串，占用空间大，速度快
2.VARCHAR是变长字符串，占用空间小，速度慢
3.CHAR、VARCHAR、TEXT这3个数据类型和检索数据的方式都不一样，数据检索的效率CHAR>VARCHAR>TEXT，所以尽量减少使用TEXT
4.每个枚举值都有一个索引，列出的元素被分配从1开始的索引，且枚举值不能是0或空字符串。

在下述定义的sql语句中，查询WHERE sex='male’和WHERE sex=1是等效的

sex ENUM('male','female','both','unknown')
1

2.6 选择数据类型时应遵循的原则

1.从速度方面考虑，选择固定的列使用CHAR
2.从节省空间方面考虑，选择动态的列使用VARCHAR
3.要将列中的内容限制在一种选择，使用ENUM
4.允许在一个列中有多于一个的条目，使用SET
5.在符合取值范围、精度的前提下，尽量使用“短”数据类型
6.数据类型越简单越好
7.尽量采用精确小数类型（例如DECIMAL），而不采用浮点数类型
8.使用内置的日期和时间数据类型，而不是采用字符串来存储日期和时间
9.避免NULL字段，建议将字段指定为NOT NULL约束

3 数据表基本操作（增删查改）

3.1 查看数据表结构

1.显示数据库：

SHOW DATABASES;
1

运行结果：

2.创建一个名为hb的数据库：

CREATE DATABASE hb;
1

运行结果：

此时来看一下数据库，发现数据库hb已被创建

3.查看被创建的hb数据库的基本信息：

SHOW CREATE DATABASE hb;
1

运行结果：

4.使用hb数据库：

USE hb;
1

运行结果：

5.查看当前使用的数据库：

SELECT DATABASE();
1

6.删除hb数据库的命令：

DROP DATABASE hb;
1

运行结果：

再次查看数据库，会发现数据库hb已被删除

由于刚刚我们已将hb数据库删除，所以接下来我们将重新创建新数据库以进行后续的练习
创建一个school数据库，并使用它

CREATE DATABASE school;
USE school;
1
2

7.创建一个student表

（1）sno表示学号，为主键，定长6位；
（2）sname表示姓名，具有唯一性，变长；
（3）ssex表示性别，只能在‘男’，‘女’中选；
（4）sage表示年龄，不能为空，且不能小于0；
（5）sdept表示所在系

CREATE TABLE student(
sno CHAR(9) PRIMARY KEY,
sname VARCHAR(20) UNIQUE,
ssex CHAR CHECK(ssex IN('m','fm')),
sage SMALLINT NOT NULL CHECK(sage>0),
sdept CHAR(20)
);
1
2
3
4
5
6
7

运行结果：

再来两个练习
创建一个含有外键的课程表course
（1）cno表示课程号，为主码
（2）cname表示课程名，具有唯一性
（3）ccredit表示学分
（4）cpno表示先行课，来自cno

CREATE TABLE course(
cno CHAR(4) PRIMARY KEY,
cname CHAR(40) UNIQUE,
ccredit SMALLINT,
cpno CHAR(4),
FOREIGN KEY (cpno) REFERENCES course(cno)
);
1
2
3
4
5
6
7

运行结果：
创建一个学生选课表sc
（1）sno表示学号
（2）cno表示课程号
（3）score表示成绩
（4）sno,cno分别为外键，(sno,cno)为主键
（5）sno来自表student，cno来自表course

CREATE TABLE sc(
sno CHAR(9) COMMENT '学号',
cno CHAR(4) COMMENT '课程号',
score SMALLINT COMMENT '成绩',
PRIMARY KEY(sno,cno),
FOREIGN KEY(sno) REFERENCES student(sno),
FOREIGN KEY(cno) REFERENCES course(cno)
);
1
2
3
4
5
6
7
8

运行结果：

注：COMMENT的作用是为字段名加注释

8.查看student表信息

DESCRIBE student;
1

运行结果:

类似的，查看course表和sc表信息

9.查看数据库school中所包含的表

SHOW TABLES;
1

运行结果：

可以看到刚才创建的三个表已经显示在school数据库中

3.2 修改基本表

1.修改表名

将student表名修改为st

ALTER TABLE student RENAME st;
1

运行结果：

通过查看school数据库中包含的表，发现student已改名为st
练习：将表名st再改回student

2.修改字段名和类型

将student表中的sdept字段改名为sdept，并且数据类型改为varchar(20)

ALTER TABLE student CHANGE sdept dept VARCHAR(20);
1

运行结果：

查看student表信息，可以发现字段名已改变

练习：将dept改回sdept

3.增加字段

向student表增加“入学时间”列(stime)，数据类型为日期型

 ALTER TABLE student ADD stime DATE;
1

运行结果：

练习：在course表中增加课程性质必须取唯一的约束条件

ALTER TABLE course ADD ctype CHAR(4) UNIQUE;
1

运行结果

练习：在student中增加是sphone字段，并不能为空，放学生姓名字段后

注：放在xx之后用AFTER，放在第一位用FIRST

ALTER TABLE student ADD sphone INT NOT NULL AFTER sname;
1

4.修改字段类型

将年龄的数据类型修改为整数

ALTER TABLE student MODIFY COLUMN sage INT;
1

运行结果：

5.增加或删除主键

随机创建一个表test，然后添加复合主键(id,card),最后删除test的复合主键

#创建表
CREATE TABLE IF NOT EXISTS test(
id INT,
card CHAR(18),
username VARCHAR(20) NOT NULL);
1
2
3
4
5

运行结果：

#增加主键
ALTER TABLE test ADD PRIMARY KEY(id,card);
1
2

运行结果：

#删除主键
ALTER TABLE test DROP PRIMARY KEY;
1
2

运行结果：

6.删除列

删除student中的入学时间字段

ALTER TABLE student DROP COLUMN stime;
1

运行结果：

7.删除基本表

删除之前创建的test表

注：
1.RESTRICT是受限制删除，与删除的基本表不能被其他表的约束所引用（如CHECK,FOREIGN KEY等约束，不能有视图，不能有触发器，不能有存储过程或函数等
2.CASCADE是无限制删除，删除基本表的同时相关的依赖对象一并删除

 DROP TABLE test CASCADE;
1

运行结果：

可以看到删除test表后，再进行查询发现test表不存在

8.复制表

复制student表至st表中

CREATE TABLE st LIKE student;
1

运行结果：

可以看到student的数据结构一并被复制到st当中

回顾一下之前的学习，做几个小练习
1.删除后student表中的sphone字段
2.删除st表
3.修改student中ssex的数据类型，将CHAR(1)改为CHAR(5)

#删除字段
 ALTER TABLE student DROP COLUMN sphone;
1
2

#删除表
DROP TABLE st CASCADE;
1
2

#修改类型
 ALTER TABLE student CHANGE ssex ssex CHAR(5);
1
2

3.3 数据更新

1.插入数据

a.不指定具体的字段名
将学生的信息插入到student表中
学号：1；姓名：zhangsan；性别：男；年龄19；所在系：computer；

 INSERT INTO student VALUES ('1','zhangsan','m',19,'computer'
);
1
2

使用下述语句进行查看插入表内容，具体的查询语句用法下一大节再讲述

SELECT * FROM student;
1

可以看到zhangsan的信息已经插入到student表中

练习：在course表中插入一条课程记录
课程号：1；课程名：数据结构；学分：3，先行课：1；种类：必修

INSERT INTO course VALUES ('1','shujujiegou',3,'1','bx');
1

练习：在sc表中插入一条选课记录(‘1’,‘1’)

 INSERT INTO sc VALUES ('1','1',NULL);
1

注：
1.VALUE子句提供的值必须与表中的每个字段对应，包括值的顺序、个数和类型
2.NOT NULL的属性列不能取空值，否则出错
3.字符串类型取值必须加上引号，否则会报错

b.指定具体的字段名
将新学生记录插入到student表中：
学号：2；姓名：lisi；性别：男；所在系：computer，年龄：18

 INSERT INTO student(sno,sname,ssex,sdept,sage) VALUES ('2','
lisi','m','computer',18);
1
2

可以看到我们在插入时，INTO句子属性的顺序可以与表中顺序不一样

练习：在sc中插入一条选课记录（‘2’，‘1’）

INSERT INTO sc(sno,cno) VALUES ('2','1');
1

注：
1.INTO子句指定部分属性列，属性列的顺序可与表定义中的顺序不一致；
2.VALUE 子句提供的值必须与INTO子句匹配，包括值的顺序、个数和类型；
没有赋值的字段，数据库系统会为其插入默认值，默认值是在创建表的时候定义的，或者取空值

c.一次插入多条数据
将新学生的记录插入到student表中
学号：3；姓名：wangwu；性别：男；所在系：math；年龄：18
学号：4；姓名：mary；性别：女；所在系：computer；年龄：17
学号：5；姓名：bob；性别：男；所在系：math；年龄：20
学号：6；姓名：alice；性别：女；所在系：math；年龄：18

INSERT INTO student(sno,sname,ssex,sdept,sage) VALUES 
('3','wangwu','m','math',18),('4','mary','fm','computer',17),('5','bob','m','math',20),('6','alice','fm','math',18);
1
2

练习：
1.将以下数据插入course表中
课程号：2；课程名：C；学分：4，先行课：1；
课程号：3；课程名：算法；学分：3，先行课：2；
课程号：4；课程名：python；学分：4，先行课：1；
课程号：5；课程名：mysql；学分：4，先行课：1；

 INSERT INTO course(cno,cname,ccredit,cpno) VALUES ('2','c',4,'1'),
('3','suanfa',3,'2'),
('4','python',4,'1'),('5','mysql',4,'1');
1
2
3

2.将以下数据插入到sc表中
学号：3；课程号：2；分数：98；
学号：2；课程号：2；分数：93；
学号：5；课程号：3；分数：67；
学号：1；课程号：3；分数：87；
学号：4；课程号：5；分数：89；
学号：6；课程号：1；分数：78；
学号：4；课程号：4；分数：95；

INSERT INTO sc(sno,cno,score) VALUES ('3','2',98),('2','2',93),('5','3',67),('1','3',87),('4','5',89),('6','1',78),('4','4',95);
1

d.通过SET形式插入数据
将记录插入到sc中
学号：1；课程号：5；分数：99

INSERT INTO sc SET sno='1',cno='5',score=99;
1

e.从目标表中插入值
建立系-系平均年龄表，记录系学生平均年龄
第一步：建表

CREATE TABLE dept_age(
sdept CHAR(15),
avg_age SMALLINT);
1
2
3

第二步：插入数据

INSERT INTO dept_age(sdept,avg_age)   (SELECT sdept,AVG(sage) FROM student GROUP BY sdept);
1

上述MySQL语句中有部分语句将在下一节学习

f.REPLACE语句
将记录插入到sc中
学号：5；课程号：1；分数：90

 REPLACE INTO sc(sno,cno,score) VALUES ('5','1',90);
1

学号：1；课程号：1；分数：59

REPLACE INTO sc(sno,cno,score) VALUES ('1','1',59);
1

注：
1.REPLACE可将一条或多条记录插入到表中，或将一个表中的结果集插入到目标表中
2.新行不存在，则插入该新行；若已存在，则先删除旧的行再插入新行
3.新记录的主键或者唯一性约束的字段值与已经有记录相同，则已有记录被删除后再添加新纪录

2.修改数据

将所有学生的年龄增加1岁

UPDATE student SET sage = sage+1;
1

将2号学生的年龄改为24岁

UPDATE student SET sage=24 WHERE sno='2';
1

3.删除数据

删除学号为1，课程号为3的sc选课表记录

DELETE FROM sc WHERE sno='1' AND cno='3';
1

4 数据查询

4.1 单表查询

1.简单查询

a.查询指定列
查询全体学生的姓名、学号、所在系

SELECT sno,sname,sdept FROM student;
1

b.查询全部列
查询student全部列

SELECT * FROM student;
1

c.查询经过计算的值

查询全体学生的姓名，及其出生年份

 SELECT sname,2023-sage FROM student;
1

查询全体学生的姓名，出生年份和大写字母显示的所在系名

SELECT sname,'Year of Birth:',2023-sage,UPPER(sdept) FROM student;
1

注：小写将UPPER改为LOWER

查询全体学生的姓名NAME、出生年份BIRTH和所在系DEPARTMENT，并用大写字母显示系名

SELECT sname NAME,2023-sage BIRTH, UPPER(sdept) AS DEPARTMENT FROM student;
1

列别名：可用空格或AS来连接列别名和表达式，使用列别名改变查询结果的列标题

d.消除取消重复的行
查询选修了课程的学生学号
如果不去重

 SELECT sno FROM sc;
1

也可写

SELECT ALL sno FROM sc;
1

去重

SELECT DISTINCT sno FROM sc;
1

注：DISTINCT短语的作用范围是所有的目标列

#错误写法
SELECT DISTINCT cno,DISTINCT score FROM  sc;
#正确写法
SELECT DISTINCT cno,score FROM sc;
1
2
3
4

2.条件查询

查询满足指定条件的元组可以通过WHERE子句实现

条件查询——比较
查询数学系全体学生的姓名

SELECT sname FROM student WHERE sdept='math';
1

查询年龄小于21岁的学生姓名以及年龄

SELECT sname,sage FROM student WHERE sage<21;
1

条件查询——确定范围
查询年龄在18-20岁的学生姓名和年龄（包含18岁和20岁）

 SELECT sname,sage FROM student WHERE sage BETWEEN 18 AND 20;
1

条件查询——确定集合
查询学号为1，3，5的学生的学号，姓名和性别

SELECT sno,sname,ssex FROM student WHERE sno IN('1','3','5');
1

条件查询——字符匹配
a.查询学号为1的学生的基本情况

#使用运算符
SELECT * FROM student WHERE sno='1';
#使用LIKE
SELECT * FROM student WHERE sno LIKE '1';
1
2
3
4

注：
1.LIKE后面的匹配串可以是一个完整的字符串，也可以含有通配符
2.通配符：是使用特殊字符或语法拼写，用来执行模糊搜索指令的语句功能
3.通配符可以替代一个或多个字符，必须与LIKE运算符一起使用
4.当用户要查询的字符串本身就含有通配符时，要对通配符进行转义

当要查询的字符串本身就含有’%‘，’_'时，要用通配符‘\’进行转义

b.查询姓wang，且名为2个字符的学生姓名

SELECT sname FROM student WHERE sname LIKE 'wang__';
1

c.查询姓wang的学生的姓名学号
先再添加两个wang姓学生

INSERT INTO student VALUES ('7','wangsan','m',23,'computer')
,('8','wangfeng','fm',22,'math');
1
2

查询wang姓学生的学号姓名

SELECT sno,sname FROM student WHERE sname LIKE 'wang%';
1

字符匹配

条件查询——涉及空值的查询
在sc表中插入一条学号为4，课程号为1的数据

 INSERT INTO sc(sno,cno) VALUES ('4','1');
1

在sc表中查询无成绩的数据

SELECT * FROM sc WHERE score IS NULL;
1

在sc表中查询有成绩的数据

SELECT * FROM sc WHERE score IS NOT NULL;
1

条件查询——多重条件查询
AND优先级高于OR，可以用括号改变优先级
查询数学系年龄在20岁以下的学生姓名年龄所在系

SELECT sname,sage,sdept FROM student WHERE sdept='math' AND sage<20;
1

查询学号为1，2，3的学生姓名和性别

#多重条件查询
SELECT sname,ssex FROM student WHERE sno='1' OR sno='2' OR sno='3';
#确定集合
SELECT sname,ssex FROM student WHERE sno IN('1','2','3');
1
2
3
4

3.高级查询

高级查询——聚集函数

DISTINCT:计算时去除重复值
ALL：不去除重复值

注：count计算时不统计null值

查询学生总人数

 SELECT COUNT(*) FROM student;
1

查询学习了课程3的学生总人数

 SELECT COUNT(*) FROM sc WHERE cno='3';
1

计算学习了1号课程的学生平均成绩

SELECT AVG(score) FROM sc WHERE cno='1';
1

高级查询——分组GROUP BY

GROUP BY：将表中元组按指定列上的值相等的原则分组，然后再每一分组上使用聚集函数，得到单一值
HAVING：选择满足条件的分组，只将聚集函数作用到满足条件的分组上。

注：
1.WHERE只能用在GROUP BY前，而HAVING用于GROUP BY后
2.HAVING短语作用于组，从中选择满足条件的组
3.WHERE子句作用于基本表或视图，从中选择满足条件的元组

查询各个课程以及相应的选课人数

SELECT cno,COUNT(cno) FROM sc GROUP BY cno;
1

查询学习了1门以上课程的学生学号

SELECT sno FROM sc GROUP BY sno HAVING COUNT(DISTINCT cno)>1;
1

高级查询——排序ORDER BY

ORDER BY：对查询结果按一个或多个属性列排序
升序——ASC；降序——DESC

注：默认值为升序

查询学习了1号课程的学生的学号及其成绩，以分数降序排列，分数相同按学号升序

SELECT sno,score FROM sc WHERE cno='1' ORDER BY score DESC,sno ASC;
1

查询学生学习课程情况，按学号升序，同一学生成绩降序，成绩相同按课程号升序排列

SELECT * FROM sc ORDER BY sno,score DESC,cno ASC;
1

高级查询——限制结果数量LIMIT

LIMIT：限制被SELECT语句返回的行数
offset（默认为0）：指定返回数据的第一行在SELECT语句结果集中的偏移量，是非负的整数常量
row_count：指定返回数据的行数（非负）

在student表中查找从第2名同学开始的3位学生信息，按学号升序排列

SELECT * FROM student ORDER BY sno LIMIT 1,3;
1

offset偏移量为1，row_count返回行数为3

4.2 连接查询

1.交叉连接CROSS JOIN

直接将各表的每条数据组合成新的数据行

SELECT * FROM student CROSS JOIN sc;
1

或者

SELECT * FROM student,sc;
1

2.隐式内连接

查询成绩高于90的女学生的姓名、成绩及课程名称

SELECT student.sname,sc.score,course.cname FROM student,sc,course WHERE student.sno=sc.sno AND sc.cno=course.cno AND ssex='fm' AND score>90;
1

3.显式内连接 INNER JOIN

查询每个学习了课程的学生所有情况（包括课程信息）

SELECT * FROM sc INNER JOIN student ON sc.sno=student.sno INNER JOIN course ON sc.cno=course.cno;
1

4.外连接（左、右）

查询学生及其所学课程的情况
左外连接

SELECT * FROM student LEFT JOIN sc ON student.sno=sc.sno;
1

右外连接

SELECT * FROM student RIGHT JOIN sc ON student.sno=sc.sno;
1

4.自身连接

查询每一门课的间接先修课

SELECT a.cno,b.cpno FROM course a,course b WHERE a.cpno=b.cn
o;
1
2

注意下图可以看到3的先修课是2，但2的先修课是1，所以上图所有的课程先修课均为1

4.3 嵌套查询

一个SELECT-FROM-WHERE语句成为一个查询块；
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询成为嵌套查询

查询学习2号课程的所有学生的学号及其名称

#嵌套查询
SELECT sno,sname FROM student WHERE sno IN(SELECT sno FROM s
c WHERE cno='2');
1
2
3

#隐式内连接
SELECT student.sno,sname FROM student,sc WHERE student.sno=sc.sno AND sc.cno='2';
1
2

4.4 集合查询

集合操作类型：
并操作：UNION ，UNION去掉重复值，UNION ALL保留重复值
查询选修了课程1或课程2的学生

#集合查询
SELECT sno FROM sc WHERE cno='1' UNION SELECT sno FROM SC WHERE cno='2';
1
2

#单表查询
SELECT sno FROM sc WHERE cno='1' OR cno='2';
1
2

一般来说不采用集合查询，集合查询代码繁复，不如单表查询语句简洁