MySQL 知识点

Prerequisite

MySQL 下载方法看这里
MySQL 大佬记的学习笔记

数据库划分如下：

• 关系型数据库（SQL）
  • MySQL
  • MSSQL
  • Oracle
• 非关系数据库（NoSQL）
  • MongoDB
  • Redis
  • Elastisearch

知识点补充：

• 可用命令行执行 MySQL 语句，且语句不区分大小写
• 可用 Navicat premium 16 或 VSCode 的 MySQL 插件查看数据库
• 使用 Navicat premium 16 时，需要打开命令窗口输入以下内容：

CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

MySQL 知识点

基础知识

• 使用命令行登入：mysql -u root -proot
• 数据类型
  • int 整数
  • double 小数
  • varchar 字符串
  • date 时间（年月日）
  • datetime 时间（年月日时分秒）
  • text 大文本
• 约束条件
  • primary key 主键（全表唯一值，能够唯一的确定一条数据，比如：学号、身份证）
  • auto_increment 主键自增（必须是整数类型）
  • not null 不可以为空
  • null 可以为空
  • default 设置默认值
• 使用命令行查看数据库

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

-- 创建数据库
CREATE DATABASE test;

-- 切换数据库
use test;

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

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

-- 查询表
SELECT * from pet;

增加数据 / 删除数据 / 修改数据

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

-- 插入数据
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 pet;

查询数据

① 基础查询

-- 全表查询
SELECT * FROM STUDENT;

-- 查询学生姓名, 年龄
SELECT sname, sage FROM STUDENT;

-- 查询学号是 1 的学生信息
select * from student where sno = 1;

-- 查询年龄大于 20 的学生信息
select * from student where sage > 20;

-- 查询学生年龄大于 20 小于 40 的信息（包含）
select * from student where sage >= 20 and sage <= 40;
select * from student where sage between 20 and 40 ;

-- 查询姓张的学生信息
-- 	_ 一位字符串
-- 	% 多位字符串
select * from student where sname like '张%';

② 分组查询

-- 查询每一个班级的平均年龄
select avg(sage), class_name from STUDENT group by class_name;

-- 查询每个班级最小的年龄
select min(sage), class_name from STUDENT group by class_name;

-- 查询每个班的最大年龄
select max(sage), class_name from STUDENT group by class_name;

-- 查询每个班的学生数量
select count(*), class_name from STUDENT group by class_name;

-- 查询每个班级的年龄和
select sum(sage), class_name from STUDENT group by class_name;

avg min 都是聚合函数

③ having 语句

-- 查询平均年龄在 15 岁以上的班级信息
select avg(sage), class_name from student group by class_name having avg(sage) > 15;

having 和 where 的区别：

1. where，在原始数据上进行的数据筛选
2. having，在聚合函数计算后的结果进行筛选

④ 排序

-- 按照年龄从小到大查询学生信息
select * from student order by sage asc

-- 按照年龄从大到小查询学生信息
select * from student order by sage desc

asc 是升序，desc 是降序

⑤ 分页查询

-- 前 3 行
select * from men limit 3;
select * from men limit 1,3;

-- 从第五条开始，往后数 8 条
select * from men limit 5,8;

多表联合查询数据

-- 创建学生表, 课程表, 成绩表
-- 1. 学生表：学号, 姓名, 性别, 住址
-- 2. 课程表：课程编号, 课程名称, 授课教师
-- 3. 学生课程-成绩表：成绩表编号, 学号, 课程编号, 成绩
create table stu(
	sid int primary key auto_increment,
	sname varchar(50) not null, 
	gender int(1),
	address varchar(255)
);

create table course(
	cid int primary key auto_increment,
	cname varchar(50) not null, 
	teacher varchar(50)
);

create table sc(
	sc_id int primary key auto_increment,
	s_id int, 
	c_id int,
	score int,
	CONSTRAINT FK_SC_STU_S_ID FOREIGN key(s_id) REFERENCES stu(sid),
	CONSTRAINT FK_SC_COURSE_C_ID FOREIGN key(c_id) REFERENCES course(cid)
);

解释 CONSTRAINT FK_SC_STU_S_ID FOREIGN key(s_id) REFERENCES stu(sid) 的含义：
答：这是一条外键约束（foreign key constraint）语句，它在创建 sc 表时定义了一个指向 stu 表的外键。

具体来说，CONSTRAINT FK_SC_STU_S_ID 是为这个外键约束命名，以便在需要删除或修改这个约束时可以方便地引用它。FOREIGN key(s_id) 声明了这个外键列是 sc 表中的 s_id 列。REFERENCES stu(sid) 声明了这个外键引用了 stu 表中的 sid 列。这意味着 sc 表中的每个 s_id 值必须在 stu 表的 sid 列中存在。这样可以确保 sc 表中的每个学生都已经存在于 stu 表中，以便正确地记录学生和他们所选修的课程的关系。

① 子查询

-- 查询选择了"编程"这门课的学生，分为三个步骤：
-- 先查询编程课程的编号，查出 cid 为 2
select cid from course where cname = '编程';
-- 再根据 cid 可以去 sc 表查询出学生的 id，查出 s_id 为 (1,2,3,4,5,6)
select s_id from sc where c_id = 2;
-- 最后根据学生 id 查询学生信息
select * from stu where sid in (1,2,3,4,5,6);

-- 把上面的 sql 语句串起来 
select * from stu where sid in (
    select s_id from sc where c_id in (
        select cid from course where cname = '编程'
    )
);

-- 查询课程名称为“编程”，且分数低于 60 的学生姓名和分数
select stu.sname, sc.score from stu, sc where stu.sid = sc.s_id and sc.score < 60 and sc.c_id in (
	select cid from course where cname = '编程'
)

② 关联查询

-- 语法规则：A 表和 B 表连接. 通过 A 表的字段 1 和 B 表的字段 2 进行连接，通常 on 后面的都是主外键关系、
-- inner join 表示交集
-- left join 表示左集
-- right join 表示右集
-- full join 表示并集
select ... from A xxx join B on A.字段1 = B.字段2

人性化的描述可以看这篇博客

Python 连接 MySQL

PS：可以选择 pymysql 或 mysqlclient，方便就行（二者可以一起下载，不冲突）

基础知识【pymysql】

• 下载相关库：pip install pymysql
• 基本用法如下：

import pymysql
from pymysql.cursors import DictCursor # 返回结果为 json 格式

# 链接数据库
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='root',
    database='test'
)
# 创建游标
cursor = conn.cursor(DictCursor)

# 使用游标去执行各种操作
...

# 操作结束
cursor.close()  # 断开 cursor
conn.close()  # 断开连接

增删改查【pymysql】

① 添加数据

try:
    result = cursor.execute("insert into stu(sname, address, gender) values ('李嘉诚', '八宝山', 1)")
    print(cursor.lastrowid)  # 获取自增的 ID 值
    print(result)  # result 代表该 sql 语句会影响多少条数据
    conn.commit()  # 提交
except:
    conn.rollback()  # 回滚

② 修改数据

try:
    cursor = conn.cursor()
    result = cursor.execute("update stu set gender = 2 where sid = 12")
    print(result)  # result 代表该 sql 语句会影响多少条数据
    conn.commit()  # 提交
except:
    conn.rollback()  # 回滚

③ 删除数据

try:
    cursor = conn.cursor()
    result = cursor.execute("delete from stu where sid = 12")
    print(result)  # result 代表该 sql 语句会影响多少条数据
    conn.commit()  # 提交
except:
    conn.rollback()  # 回滚

④ 查询操作

sql = """
    select * from stu
"""
ret_num = cursor.execute(sql)

# result = cursor.fetchall()  # 获取全部结果
# result = cursor.fetchmany(5)  # 获取部分结果
result = cursor.fetchone()  # 获取单个结果
print(result)
result = cursor.fetchone()  # 获取下一个结果, 可以连续获取
print(result)

一步到位【mysqlclient】

• 下载相关库：pip install mysqlclient
• 基本用法如下：

import MySQLdb

# 连接数据库
conn = MySQLdb.connect(host='localhost',
                    port=3306,
                    user='root',
                    password='root',
                    database='music')
cursor = conn.cursor()

# 执行语句
cursor.execute("""
    select * from music
""")

# 获取全部结果
result = cursor.fetchall()
print(result)