mysql学习笔记

最新推荐文章于 2023-05-19 17:34:51 发布

原创最新推荐文章于 2023-05-19 17:34:51 发布 · 430 阅读

1 ·

CC 4.0 BY-SA版权

学习笔记专栏收录该内容

11 篇文章

订阅专栏

这篇博客介绍了MySQL的基础知识，包括常用数据类型、登陆和退出数据库的基本语法，以及建表时的主键、唯一、非空、默认和外键等约束。进一步讲解了数据库的三大设计范式：1NF、2NF和3NF。重点讨论了查询联系的各种方法，如分组、多表、子查询和连接查询。最后探讨了事务的概念，包括ACID特性、事务控制（COMMIT、ROLLBACK）以及事务的隔离性级别。

1. 常用数据类型

mysql常用数据类型：数值，日期/时间和字符串(字符)类型。
在这里插入图片描述

2. 登陆和退出数据库

# 登录MySQL
$ mysql -u root -p

# 退出MySQL数据库服务器
exit;

3. 基本语法

-- 显示所有数据库
show databases;

-- 创建数据库
CREATE DATABASE test;

-- 切换数据库
use test;

-- 显示数据库中的所有表
show tables;

-- 创建数据表
CREATE TABLE pet (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

-- 查看数据表结构
-- describe pet;
desc pet;

-- 查询表
SELECT * from pet;

-- 插入数据
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);

-- 修改数据
UPDATE pet SET name = 'squirrel' where owner = 'Diane';

-- 删除数据
DELETE FROM pet where name = 'squirrel';

-- 删除表
DROP TABLE myorder;

4. 建表约束

建立约束方式：

建表的时候添加约束
建表之后可以使用alter…add…
alter … modify …
删除 alter… drop…

4.1 主键约束 primary key

-- 主键约束
-- 使某个字段不重复且不得为空，确保表内所有数据的唯一性。
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 联合主键
-- 联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);

-- 自增约束
-- 自增约束的主键由系统自动递增分配。搭配主键一起使用。
CREATE TABLE user (
    id INT PRIMARY KEY
);

-- 添加主键约束
-- 如果忘记设置主键，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- 删除主键
ALTER TABLE user drop PRIMARY KEY;

4.2 唯一约束 unique

-- 建表时创建唯一主键 (PRIMARY KEY唯一且不为空，这个可以空)
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

-- 添加唯一主键
-- 如果建表时没有设置唯一建，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- 删除唯一主键
ALTER TABLE user DROP INDEX name;

4.3 非空约束 not null

-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);

4.4 默认约束 default

-- 建表时添加默认约束
-- 约束某个字段的默认值
CREATE TABLE user2 (
    id INT,
    name VARCHAR(20),
    age INT DEFAULT 10
);

-- 移除非空约束
ALTER TABLE user MODIFY age INT;

4.5 外键约束 foreign key

-- 班级
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    -- 这里的 class_id 要和 classes 中的 id 字段相关联
    class_id INT,
    -- 表示 class_id 的值必须来自于 classes 中的 id 字段值
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
-- 2. 主表中的记录被副表引用时，主表不可以被删除。

5. 数据库的三大设计范式

第一范式 1NF

要求数据库表的每一列都是不可分割的原子数据项。

第二范式 2NF

在1NF的基础上，其他列都必须完全依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下（即不满足第二范式），需要将表拆分。

第三范式 3NF

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！

6. 查询联系

6.1 数据准备

-- 创建数据库
CREATE DATABASE select_test;
-- 切换数据库
USE select_test;

-- 创建学生表
CREATE TABLE student (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE, -- 生日
    class VARCHAR(20) -- 所在班级
);

-- 创建教师表
CREATE TABLE teacher (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE,
    profession VARCHAR(20) NOT NULL, -- 职称
    department VARCHAR(20) NOT NULL -- 部门
);

-- 创建课程表
CREATE TABLE course (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    t_no VARCHAR(20) NOT NULL, -- 教师编号
    -- 表示该 tno 来自于 teacher 表中的 no 字段值
    FOREIGN KEY(t_no) REFERENCES teacher(no) 
);

-- 成绩表
CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL, -- 学生编号
    c_no VARCHAR(20) NOT NULL, -- 课程号
    degree DECIMAL,	-- 成绩
    -- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
    FOREIGN KEY(s_no) REFERENCES student(no),	
    FOREIGN KEY(c_no) REFERENCES course(no),
    -- 设置 s_no, c_no 为联合主键
    PRIMARY KEY(s_no, c_no)
);

-- 查看所有表
SHOW TABLES;
-- 添加数据略

6.2 1-10练习

-- 查询 student 表的所有行
SELECT * FROM student;

-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;

-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;

-- 查询 score 表中成绩在60-80之间的所有行（区间查询和运算符查询）
-- BETWEEN xx AND xx: 查询区间, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

-- 查询 score 表中成绩为 85, 86 或 88 的行
-- IN: 查询规定中的多个值
SELECT * FROM score WHERE degree IN (85, 86, 88);

-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
-- or: 表示或者关系
SELECT * FROM student WHERE class = '95031' or sex = '女';
-- 以 class 降序的方式查询 student 表的所有行
-- DESC: 降序，从高到低
-- ASC（默认）: 升序，从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;

-- 以 cno 升序、degree 降序查询 score 表的所有行
SELECT * FROM score ORDER BY c_no ASC, degree DESC;

-- 查询 "95031" 班的学生人数
-- COUNT: 统计
SELECT COUNT(*) FROM student WHERE class = '95031';

-- 查询 score 表中的最高分的学生学号和课程编号（子查询或排序查询）。
-- (SELECT MAX(degree) FROM score): 子查询，算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

--  排序查询
-- LIMIT r, n: 表示从第r行开始，查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

6.3 分组查询

分组计算平均成绩
-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';

-- GROUP BY: 分组查询
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

-- 查询 score 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。
-- 后面加上一个 COUNT(*)，表示将每个分组的个数也查询出来。group by 后面跟having 
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';

6.4 多表查询

-- 查询所有学生的name,cno,degree列。
SELECT name, cno, degree FROM student, score 
WHERE student.sno = score.sno;

-- 查询所有学生sno, cname,和degree列。
mysql> select sno,cname,degree from score,course 
where score.cno=course.cno;
+-----+-----------------+--------+
| sno | cname           | degree |
+-----+-----------------+--------+
| 103 | 计算机导论      |     92 |
| 105 | 计算机导论      |     88 |
| 109 | 计算机导论      |     76 |
| 103 | 操作系统        |     86 |
| 105 | 操作系统        |     75 |
| 109 | 操作系统        |     68 |
| 103 | 数字电路        |     85 |
| 105 | 数字电路        |     79 |
| 109 | 数字电路        |     81 |
+-----+-----------------+--------+
9 rows in set (0.01 sec)

-- 三表关联查询
-- 查询所有学生的sname,cname,degree列。
select sname,cname,degree from student,course,score 
where student.sno = score.sno 
and course.cno = score.cno;

6.5 子查询

-- 1.查询 95031 班学生每门课程的平均成绩。
--- 先查找96031班的所有学生，将筛选出的学生号当做 s_no 的条件查询
SELECT sno, cno, degree FROM score
WHERE sno IN (SELECT no FROM student WHERE class = '95031');
--- 这时只要将 c_no 分组一下就能得出 95031 班学生每门课的平均成绩：
SELECT cno, AVG(degree) FROM score
WHERE sno IN (SELECT no FROM student WHERE class = '95031')
GROUP BY cno;

-- 2. 查询在 3-105 课程中，所有成绩高于 109 号同学的记录。
select * from score where  degree > (select degree from score where sno='109' and cno = '3-105') and cno = '3-105';

-- 3. 查询所有成绩高于 109 号同学的 3-105 课程成绩记录。
select * from score where degree > (select degree from score where sno='109' and cno='3-105');

-- 4. 查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列。
select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));

-- 5. 查询 '张旭' 教师任课的学生成绩表。
SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE t_no = ( 
        SELECT no FROM teacher WHERE NAME = '张旭' 
    )
);

6.6 多表查询 2

-- 查询某选修课程多于5个同学的教师姓名。
select tname from teacher where tno = 
	(select tno from course where cno = 
	(select cno from score group by cno having count(cno)>5));

6.7 UNION 和 NOTIN 的使用

-- UNION 合并两个集
SELECT * FROM teacher WHERE depart = '计算机系' AND prof NOT IN (
    SELECT prof FROM teacher WHERE depart = '电子工程系'
)
UNION
SELECT * FROM teacher WHERE depart = '电子工程系' AND prof NOT IN (
    SELECT prof FROM teacher WHERE depart = '计算机系'
);

6.8 ANY 和 ALL 的使用

-- 1.查询课程 3-105 且成绩 至少 高于 3-245 的 score 表,结果按降序排序。
select * from score where cno='3-105' and 
	degree > any( select degree from score where cno='3-245') 
	order by degree desc;

-- 2.查询课程 3-105 且成绩高于 3-245 的 score 表。
select cno,sno,degree from score where cno='3-105' and 
	degree > all( select degree from score where cno='3-245') ;

6.9 AS取别名

-- 查询所有教师和同学的name,sex,birthday.
select tname as name, tsex as sex, tbirthday as birthday from teacher 
union
select sname,ssex,sbirthday from student;

6.10 复制表的数据作为条件查询

--查询某课程成绩比该课程平均成绩低的 score 表。
-- 将表 b 作用于表 a 中查询数据
-- 如此就能用 a.c_no = b.c_no 作为条件执行查询了。
SELECT * FROM score a WHERE degree < (
    (SELECT AVG(degree) FROM score b WHERE a.cno = b.cno)
);

6.11 条件加分组查询

-- 查询 student 表中至少有 2 名男生的 class 。
select class from student where ssex = '男' 
	group by class having count(*)>1;

6.12 NOTLIKE 模糊查询取反 // YEAR和NOW函数

-- 1. 查询 student 表中不姓 "王" 的同学记录。
-- NOT: 取反
-- LIKE: 模糊查询
SELECT * FROM student WHERE name NOT LIKE '王%';

-- 2、 查询 student 表中每个学生的姓名和年龄。
-- 使用函数 YEAR(NOW()) 计算出当前年份，减去出生年份后得出年龄。
select sname, year(now()) - year(sbirthday) 
	as '年龄' from student;

6.13 MAX和MIN函数

SELECT MAX(birthday), MIN(birthday) FROM student;
+---------------+---------------+
| MAX(birthday) | MIN(birthday) |
+---------------+---------------+
| 1977-09-01    | 1974-06-03    |
+---------------+---------------+

6.14 多字段排序

-- 以 班号 和 年龄 从大到小的顺序查询 student 表。

SELECT * FROM student ORDER BY class DESC, birthday;

6.15 子查询2

-- 1.查询 "男" 教师及其所上的课程。
select cname from course where tno in 
	(select tno from teacher where tsex = '男');

-- 2. 查询最高分同学的 score 表。
select * from score where degree = (select max(degree) from score);

-- 3. 查询和 "李军" 同性别且同班的同学 name 。
select sname from student where ssex = 
	(select ssex from student where sname ='李军') 
	and class = 
	(select class from student where sname ='李军') ;

-- 4.查询所有选修 "计算机导论" 课程的 "男" 同学成绩表。
select * from score 
where cno = (select cno from course where cname ='计 算机导论') 
and sno in (select sno from student where ssex ='男');

6.16 连接查询

-- 准备用于测试连接查询的数据：

CREATE DATABASE testJoin;

CREATE TABLE person (
    id INT,
    name VARCHAR(20),
    cardId INT
);

CREATE TABLE card (
    id INT,
    name VARCHAR(20)
);

INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
SELECT * FROM card;

INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;

--内连接
--要查询这两张表中有关系的数据，可以使用 INNER JOIN ( 内连接 ) 将它们连接在一起。
-- INNER JOIN: 表示为内连接，将两张表拼接在一起。
-- on: 表示要执行某个条件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+

-- 将 INNER 关键字省略掉，结果也是一样的。


--左外连接
--完整显示左边的表 ( person ) ，右边的表如果符合条件就显示，不符合则补 NULL 。
-- LEFT JOIN 也叫做 LEFT OUTER JOIN，用这两种方式的查询结果是一样的。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+

--右外链接
--完整显示右边的表 ( card ) ，左边的表如果符合条件就显示，不符合则补 NULL 。
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

-- 全外链接
-- 完整显示两张表的全部数据。
-- MySQL 不支持FULL JOIN这种语法的全外连接
-- MySQL全连接语法，使用 UNION 将两张表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

7. 事务

在 MySQL 中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

比如我们的银行转账：

-- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';
-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';

在实际项目中，假设只有一条 SQL 语句执行成功，而另外一条执行失败了，就会出现数据前后不一致。
因此，在执行多条有关联 SQL 语句时，事务可能会要求这些 SQL 语句要么同时执行成功，要么就都执行失败。

如何控制事务 - COMMIT / ROLLBACK

在 MySQL 中，事务的自动提交状态默认是开启的。

-- 查询事务的自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

自动提交的作用：在执行插入语句后数据立刻生效，原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是，撤销执行过的所有 SQL 语句，使其回滚到最后一次提交数据时的状态。在自动提交状态中，所有执行过的 SQL 语句都已经被提交过了，所以数据并没有发生回滚。

-- 关闭自动提交
SET AUTOCOMMIT = 0;

-- 查询自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+

在关闭自动提交后，数据的变化是在一张虚拟的临时数据表中展示，发生变化的数据并没有真正插入到数据表中。

此时可以执行回滚rollback。

使用commit命令进行手动提交，之后无法rollback。

事务的手动开启

事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后，此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件，使其可以发生回滚：

-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
-- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- 由于手动开启的事务没有开启自动提交，
-- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 测试回滚
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

仍然使用 COMMIT 提交数据，提交后无法再发生本次事务的回滚。

总结

事务开启：
1. 修改默认提交，SET AUTOCOMMIT = 0 。
2. begin;
3. start transaction;
事务手动提交：
commit;
事务手动回滚：
rollback;

事务的 ACID 特征与使用

事务的四大特征：

A 原子性：事务是最小的单位，不可以再分割；
C 一致性：要求同一事务中的 SQL 语句，必须保证同时成功或者失败；
I 隔离性：事务1 和事务2 之间是具有隔离性的；
D 持久性：事务一旦结束 ( COMMIT ) ，就不可以再返回了 ( ROLLBACK )

事务的隔离性

事务的隔离性可分为四种 ( 性能从低到高 ) ：

READ UNCOMMITTED ( 读取未提交 )

如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。该事务使用回滚，会导致其他事务数据不一致。
会导致脏读：指一个事务读取了另外一个事务未提交的数据。
READ COMMITTED ( 读取已提交 )

只能读取到其他事务已经提交的数据。
会导致：不可重复读。指一个事务内对一份数据进行反复读取，数据内容不一致。例如，事务a在读取的过程中，事务b update了数据，导致事务a前后读取数据不同。
REPEATABLE READ ( 可被重复读 )

事务读取的是打开事务时表的快照，
会导致：幻读(虚读)，指前后多次读取，数据总量不一致。针对insert和delete。例如，事务a 读取了某个范围的数据，事务b在这个范围内插入了一些数据，事务a再次读取这个范围的数据，发现多了几条，与之前的结果不同。
SERIALIZABLE ( 串行化 )

所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。
在这个级别，可能导致大量的超时现象和锁竞争。