MySQL学习

最新推荐文章于 2025-07-26 10:46:52 发布

原创最新推荐文章于 2025-07-26 10:46:52 发布 · 1k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #学习 #数据库

文章目录

MySQL学习

MySQL学习

1.mysql 高级数据结构和算法

1.1mysql的介绍

sql语句，增删改查 CURD
索引以及原理
事务处理
存储引擎

bst、avl、红黑树（跳跃表）
分治算法
回溯算法
动态规划

2. mysql第一节课

mysql =》 oracle
关系型数据库
RDBMS 关系型数据库管理系统=》创建很多数据库 =》二维表
SQL(Structed Query Language)语句
二维表行：记录列：属性/字段

oracle
mysql: 企业版（收费的）社区版（免费的） mysql
SQL Server

非关系型数据库NoSQL [key, value]
memcached（不能持久化）/redis（可以持久化数据）缓存数据库

MySQL：分布式的关系型数据库管理系统
不是一个单机的数据库，mysql client和mysql server
1.首先得启动mysql server
2.通过mysql client和server建立tcp链接
3.mysql client就可以通过创建得连接发送sql语句到mysql server
上
4.mysql server处理完sql语句，把结果返回到mysql client和mysql
5.关闭当前mc和ms得链接

mysql client：api接口 c&c++ java python php go

mysql server作为一个网络服务器，采用得网络模型是：
select(epoll) + 线程池得模型 >> 磁盘得I/O速率
linux:epoll windows:iocp

windows上在任务管理器 - 服务 - 启动 mysql57
打开mysql command line - unicode命令行窗口输入密码就可以登录MySQL server

linux先启动mysqld /etc/init.d/mysqld start/stop/restart
netstat -tanp 查看以下mysqld服务是否启动成功
mysql -u root -p密码

登录得是DBMS
show databases;
use mysql;
show tables;
desc user; // => 查看表得结构
show create table user; // => 查看创建表得sql语句，表得存储引擎以及编码格式

// 创建数据库
create database tulun;
// 删除数据库
drop databases tulun;

// 选择数据库
use tulun; // show tables;

// 创建一张学生表 id int name varchar age int sex enum score double level “”
// 字段名称+字段类型+字段得完整性约束条件(主键，外键，unique，not null, default, auto_increment)
create table student(id int primary key,
name varchar(20), age tinyint,
sex enum(‘男’, ‘女’), score double,
level varchar(20) default “不合格”);

// 删除表
drop table student;

// insert
insert into student values();

// delete
delete fron student where score < 60.0 or age < 20;

// update
update student set level=“合格” where score > 90.0;

// select 单表得查询和多表得查询
select * from student;
select id,name,score,level front student;
select * from student where score>60.0;
select name,age,sex where level = ‘合格’;

SQL得排序order by 字段名称 asc（默认）/desc 和
分组group by 字段名称经常和mysql得函数一起使用，得到分组后得详细情况

索引的sql语句很多学生10000记录 name : zhang san
name字段创建一个索引，mysql server会把表中所有记录的name字段的值进行排序进行一个二分搜索
create index name_index on student(name);
注意：
1.对区分度高的字段创建索引好；对区分度低的字段不用创建索引了。
2.MySQL Server使用索引的时候，会进行索引优化，如果过滤字段需要搜索表的数据达到60-70%，索引就
被放弃了，执行整表搜索了。
3.如果过滤条件+排序或者分组条件，此时最好创建联合索引
4.对于一张表的一次查询只能用到一个索引
《MySQL索引漫画》

带in的子查询： <=== 为什么多表联合查询时，不建议使用带in子查询，都要写成连接查询，为甚？
select * from 
Employeeaddress 
where addreddID 
in (select AddressID from Employee where EmployeeId=10000)

熟悉单表的CURD，排序和分组，字段创建索引，explain查看SQL执行计划，
连接查询：内连接和外连接查询（左连接和右连接）
select b.country,b.city,b.street 
from 
Employee a, Employeeaddress b 
on a.AddressID = b.addreddID
where a.EmployeeId = 10000;

===> 同样的内连接SQL语句是：

select b.country,b.city,b.street  
from 
Employee a
inner join 
Employeeaddress b
on a.AddressID = b.addreddID
where a.EmployeeId = 10000;
	



explain: 查看sql语句的执行计划

》》》了不了解数据库得范式设计？？？
范式一、范式二、范式三、BC范式、范式四

范式：减少表得冗余，防止数据不一致，提高数据的查询效率；范式越多，表划分的就越多，多表
联合查询的次数就越多，查询的效率就会变慢！因此范式绝对不是越高越好，一般数据库表的
设计达到范式三就足够了！

student 1000行
id name sex age 选择的课程课程的总成绩学生的考试成绩
1 zhang male 20 语文 100 98

3.MySQL第二节课

单例索引：
联合索引：(name, score) 必须使用第一列
select * from student where score > 80.0

explain select * from address where id in
(select addressid from student where id=6)\G
1.内查询
2.生成中间表存储内查询的结果
3.再执行外查询，在中间表中过滤记录
4.中间表释放掉

实际上上面的语句：
1.内查询
2.直接用内查询的结果addressid，直接在外表中查找

范式一：所有的字段要保证是原子的
范式二：主要是针对联合主键所说的，非主属性一定要依赖联合主键的
每一个字段，如果只依赖其中一部分字段，对表进行拆分

联合主键（学号） student
id name age courseid score
学号, 姓名, 年龄, 课程ID, 成绩
100 zhang 20 202 98
104 gao 23 202 76
123 wang 18 202 83
167 liu 19 202 93

courseid name grade
课程id 课程名称学分 course
202 mysql 6
207 C++ 6

请输出一个SQL语句，把student表中所有mysql课程考试成绩在80分以上的学生
的学号，姓名，成绩，学分打印出来
select a.id,a.name,a.score,b.grade from student a inner join course b
on a.courseid = b.courseid where b.courseid=202;

一般关系型数据库，表的设计达到范式三就可以了！！！
范式三：非主属性只能依赖主键，不能依赖其它非主属性
学号, 姓名, 年龄, 学院id
100 zhang 20 200
100 zhang 20 300
100 zhang 20 200

学院ID，所在学院, 学院地点, 学院电话
200 电信学院 8号楼 110
300 计算机学院 9号楼 119

范式4：消除表中某些字段的多值依赖 MySQL mysql

BC范式：每个表中只有一个候选键(每一个记录该值都不一样)

4.实际项目中表的设计

4.1 第一步：先找实体：

商品product：商品ID(pid)，商品名称(pname)，商品价格(pprice)，商品库存(pamount)
用户user：用户ID(uid)，用户名称(uname)，用户密码(upwd)
订单order：订单ID，订单送货地址，订单的商品信息

4.2 第二步：再找实体和实体之间的关系一对一一对多多对多

用户商品没关系

用户 =》订单一对多的关系
uid uname upwd user
100 曹操 111
102 孙权 112
103 刘备 113

orderid orderaddr orderprice uid order
87987234 陕西西安 125.0 100
23784618 四川成都 40.0 100

内连接
select * from user a inner join order b on a.uid = b.uid where a.uid=100
左连接
select * from user a left outer join order b on a.uid = b.uid where a.uid=100
右连接
select * from user a right outer join order b on a.uid = b.uid where a.uid=100

商品订单多对多的关系 =》一定要设计一张中间的表
pid pname pprice pamount product
100 鼠标 20.0 200
101 笔记本 2000.0 100
102 键盘 25.0 500

orderid orderaddr orderprice uid order
87987234 陕西西安 125.0 100
23784618 四川成都 40.0 100

多对多的实体关系，设计的时候，要产生如下的中间表
orderlist
id pid orderid pcount pchoiceprice
1 100 87987234 5 100.0
2 100 23784618 2 40.0
3 102 87987234 1 25.0

曹操的指定订单的商品信息
select * from orderlist a inner join product b on a.pid = b.pid where a.orderid = 87987234;

5.MySQL第三节课主题：索引

主键索引
辅助索引
聚集索引
非聚集的索引
索引的底层实现 B-树和B+树
为什么用B+树，而不用B-树？
1.
2.
3.

会回答一个问题：你做过哪些索引的优化？你说说索引该怎么优化？
MySQL的四种日志：
1）二进制日志：记录所有操作数据库的SQL命令 mysqlbinlog xxx
2）错误日志：记录了mysql服务器在启动，运行过程中，关闭，重启所遇到的错误
3）通用查询日志：所有的查询操作
4）慢查询日志:记录所有SQL运行过程中，超过指定时间的SQL语句 long_query_time = 2s

在进行索引优化的时候
1.通过查看慢查询日志，找到所有执行时间过长的SQL语句
2.再通过explain查看一下SQL的执行计划
3.根据执行计划罗列的信息，再考虑索引的优化

索引创建和删除相关的SQL语句
create table student(id int primary key,
name varchar(20),
age int,
index(name, age));

create index name_index on table_name(field_name);
drop index name_index on table_name;

索引的底层实现是一颗B-树结构 B-balance m（300-500）阶平衡树二阶平衡树

主键索引：表里面所有记录主键字段的值取出来，创建了一颗B-树
explain select * from student where id = 100; 100万个数据 3层最多做3次磁盘I/O
primary key row:1

id name age sex
100 zhang 20 男
132 liu 28 女
145 gao 23 男
167 wei 25 女

select * from course a inner join student b on a.uid = b.id where b.id<=2
课堂上遇到的索引优化的情况
1.给where过滤字段添加索引
2.给排序或者分组条件字段添加索引 where name=xxx order by xxxx using file sort
3.使用联合索引的时候，一定要使用到联合索引的第一个字段
4.查看select语句，是否把select后面的字段和where后面的字段创建联合索引，这样查询就
不用搜索主键索引树
select name from baobei where selltime = ‘2019/5/13’ order by name (selltime,name)
5.多项数据插入的时候，insert into student values(xx,xx,xx),(xx,xx,xx)…
6.用explain分析SQL语句，对于多表联合查询的SQL语句创建合适的索引
7.分页查询limit的优化操作

事务的概念
事务的ACID特性
事务并发执行造成的数据不一致性
事务的隔离级别

#include <iostream>
#include <string>
/*
MySQL提供的C&C++相关头文件和库文件在安装目录里面
1.包含MySQL头文件
2.开启MySQL Server服务
3.mysql api介绍
mysql_init(MYSQL *mysql); 初始化数据库结构体MYSQL
mysql_real_connect(); 和mysql server创建连接
mysql_query; 实现增删改查
mysql_store_result; 返回mysql_query的查询的结果集
mysql_close; 关闭mysql连接


关于查询语句的结果处理
mysql_store_result; 返回mysql_query的查询的结果集
mysql_num_fields; 数据列数
mysql_field_count; 数据行数
mysql_fetch_row; 获取行数据
mysql_free_result; 释放结果集
mysql_affected_rows; 显示受影响的行数

错误处理
mysql_error返回错误信息的非空字符串
mysql_errno返回一个非0值错误码
*/

#include <mysql.h>
using namespace std;

int main()
{
	// 定义存储MYSQL连接信息的结构体
	MYSQL *mysql = nullptr;

	// 初始化MYSQL结构体信息
	mysql = mysql_init(nullptr);

	// 和mysql server创建连接
	if (!mysql_real_connect(mysql, "localhost", "root",
		"123456", "tulun", 3306, nullptr, 0))
	{
		cout << "connect mysql server error!" << endl;
		return 0;
	}

	// C和C++默认用GBK编码中文，所以设置服务器中文编码也采用gbk
	mysql_query(mysql, "set names gbk");

	// SQL查询操作
	string sql = "select * from paymentrecord";
	mysql_query(mysql, sql.c_str());

	// 存储查询到的结果集
	MYSQL_RES *res = nullptr;
	res = mysql_store_result(mysql);
	// 查询到的结果集的行数是0，说明没有查到任何信息
	int count = mysql_field_count(mysql);
	cout << "count:" << count<<endl;
	if (0 == count)
	{
		cout << sql << "未查询到任何信息!" << endl;
		mysql_free_result(res);
		return 0;
	}

	// 获取查询的结果集
	MYSQL_ROW row;
	int fields = mysql_num_fields(res);
	while (row = mysql_fetch_row(res))
	{
		for (int i = 0; i < fields; ++i)
		{
			cout << row[i] << " ";
		}
		cout << endl;
	}

	mysql_free_result(res);
	mysql_close(mysql);

	return 0;
}