-- 1. 准备数据集
-- 创建数据库并切换
create database db_itheima;    -- 创建数据库db_itheima
use db_itheima;                -- 切换到db_itheima数据库

-- 创建班级表classes，拥有两个字段，cls_id代表班级编号，cls_name代表班级名称
drop table if exists classes;  -- 如果classes表存在则删除
create table classes(          -- 创建classes表
    cls_id int,                -- 班级编号字段
    cls_name varchar(20)       -- 班级名称字段
);

-- 插入数据，ui、java、python、shell
insert into classes values
    (1, 'UI'),                 -- 班级1：UI
    (2, 'Java'),               -- 班级2：Java
    (3, 'Python'),             -- 班级3：Python
    (4, 'Shell');              -- 班级4：Shell

-- 创建学生表students，拥有id、name、age、gender值使用male和female、score、cls_id
drop table if exists students; -- 如果students表存在则删除
create table students(         -- 创建students表
    id int,                    -- 学生编号字段
    name varchar(20),          -- 学生姓名字段
    age int,                   -- 学生年龄字段
    gender enum('male', 'female'),  -- 性别字段，只能是male或female
    score decimal(11,2),       -- 分数字段
    cls_id int                 -- 班级编号字段
);

-- 插入数据，刘备属于Java，貂蝉属于UI，赵云属于Python，关羽属于Python，大乔属于UI
insert into students values
    (1, '刘备', 18, 'male', 100.00, 2),    -- 刘备：Java班，100分
    (2, '貂蝉', 18, 'female', 99.00, 1),   -- 貂蝉：UI班，99分
    (3, '赵云', 18, 'male', 98.00, 3),     -- 赵云：Python班，98分
    (4, '关羽', 18, 'male', 96.00, 3),     -- 关羽：Python班，96分
    (5, '大乔', 18, 'female', 97.00, 1),   -- 大乔：UI班，97分
    (6, '小乔', 18, 'female', 97.00, 5);   -- 小乔：cls_id=5不存在

-- 创建学生详情表students_detail
drop table if exists students_detail;  -- 如果students_detail表存在则删除
create table students_detail(          -- 创建students_detail表
    id      int primary key auto_increment,  -- 主键，自动增长
    stu_id  int,                       -- 学生编号
    address varchar(200)               -- 地址
);

-- 插入数据
insert into students_detail (stu_id, address)
values (1,'aaaaaa'),           -- 学生1的地址
       (2,'bbbbbb');           -- 学生2的地址

-- 交叉连接也称之为笛卡尔积连接，一旦出现，就会出现数据库性能问题
select * from students cross join classes;  -- 使用cross join关键字
-- 或
select * from students, classes;           -- 使用逗号分隔

select 字段,... from 表1 inner join 表2 on 表1.字段1 = 表2.字段2;

-- 案例：求每个学生所属的班级信息（学生所有字段 + 班级名称）
select * from students inner join classes on students.cls_id = classes.cls_id;

-- 给表起别名，在后续使用
select * from students as stu inner join classes as cls on stu.cls_id = cls.cls_id;

-- 要students表中所有字段，但只要classes中的cls_name字段
select stu.*,cls.cls_name from students as stu inner join classes as cls on stu.cls_id = cls.cls_id;

-- inner关键字可以不写，默认就是，所以直接写一个join就可以了
select stu.*,cls.cls_name from students as stu join classes as cls on stu.cls_id = cls.cls_id;

-- 要students中指定的字段和classes表中指定的字段
select stu.id,stu.name,cls.cls_name from students as stu inner join classes as cls on stu.cls_id = cls.cls_id;

-- 要求student中分数在97及以上，要得到它的分类,还要指定字段
select stu.id,stu.name,stu.score,cls.cls_name from students as stu inner join classes as cls
on stu.cls_id = cls.cls_id
where stu.score>=97;

select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2;

-- 左外连接  主表和从表，取主表的全部和从表的交集部份，如果主表中的数据在从表中不存在，则使用null
select stu.id,stu.name,stu.score,cls.cls_name from classes as cls left join students as stu
on stu.cls_id = cls.cls_id;

-- 左连接后再进行where过滤
select stu.id,stu.name,stu.score,cls.cls_name from classes as cls left join students as stu
on stu.cls_id = cls.cls_id where id is not null;

select 字段 from 表1 right join 表2（主表） on 表1.字段1 = 表2.字段2;

-- 查看班级中对应的学生信息，如果某个班级没有对应的学生也要显示。（既可以用左外连接也可以用右外连接）
select stu.id,stu.name,stu.score,cls.cls_name from classes as cls right join students as stu
on stu.cls_id = cls.cls_id;

select * from (select * from xxx) as t;

-- 取出大于平均分的人信息  子查询   位置 where条件处
select * from tb_score where math > (select avg(math) from tb_score);

-- 在from取字段处
select name,math,(select max(math) as aa from tb_score) from tb_score;

-- 自关联，一张表自己和自己关联起来
select a.*,b.title from areas a join areas b on a.pid=b.id where a.id=110106;

-- 查看当前的连接者
show processlist;

-- 模糊查看wait_timeout
show variables like 'wait_timeout';

-- 查看当前mysql中系统变量
-- mysql中变量调用用 @@变量名
select @@wait_timeout;

[mysqld]
max_connections=100          -- 最大连接数
thread_cache_size=4          -- 线程缓存池大小
wait_timeout=300             -- 连接闲置等待时间
interactive_timeout=300      -- 交互超时时间

-- 查看当前mysql实例中支持的存储引擎种类
show engines;

-- 查看当前默认的存储引擎
select @@default_storage_engine;

我的银行卡：1.00
itwu银行卡：2000.00

发生一系列操作：
① itwu发起转账，扣款1000，余额-1000
② 银行接收任务，处理（ATM）
③ 我的银行卡接收到1000，余额+1000

update bank set money=money-1000 where name = "itwu"
-- 交易系统停电了
update bank set money=money+1000 where name = "我"

-- 查看表的存储引擎
show create table 表名;

-- ① 开启事务处理功能
start transaction;

-- ② 执行一系列的SQL语句（多条）
update bank set money=money-1000 where name = "itwu";
update bank set money=money+1000 where name = "我";

-- ③ 判断SQL语句是否全部执行成功，如果成功则提交事务 => commit; 失败，则回滚事务 => rollback;
commit;

-- 创建InnoDB引擎的表
create table bank (
    id int primary key auto_increment,  -- 主键，自动增长
    name varchar(20),                   -- 姓名
    money int not null default 1        -- 余额，默认为1
);

-- 查看表结构
show create table bank;

-- 插入测试数据
insert into bank (name,money) values ('itwu',2000),('我',1);

-- 代码示例：
-- 开始一个事务
start transaction;

-- 扣款
update bank set money=money-1000 where name = "itwu";

-- 收款
update bank set money=money+1000 where name = "我";

-- 如果事务中没有问题，则提交commit；如果发现有问题则可以rollback
commit;

-- 永久修改默认存储引擎方案
-- vi /etc/my.cnf
-- [mysqld]
-- default_storage_engine=innodb

-- 在创建表时来指定存储引擎  engine
create database test5;
use test5;

-- 创建一个表并指定它的存储引擎为myisam
create table user1(id int) engine=myisam;

-- 创建一个表并指定它的存储引擎为innodb
-- 如果默认mysql它的引擎就为innodb，所以你可以不指定
create table user2(id int) engine=innodb;

-- 查看一下表空间大小
select @@innodb_data_file_path;

-- 配置文件 /etc/my.cnf
-- innodb_file_per_table=1
-- 建议最多不要超过3个
-- innodb_data_file_path=ibdata1:12M;ibdata2:100M:autoextend

# /etc/my.cnf
[mysqld]
# 每个重做日志文件的大小
innodb_log_file_size = 50M
innodb_redo_log_capacity=100M
# 重做日志组中日志文件的个数 2（默认值）
innodb_log_files_in_group = 2
# redo log 文件存放路径
innodb_log_group_home_dir = /usr/local/mysql/data

# /etc/my.cnf
[mysqld]
# 开启慢查询日志
slow_query_log=1
# 设置超过 1 秒的查询被记录
long_query_time=1
# 指定慢查询日志文件存放路径
slow_query_log_file=/usr/local/mysql/logs/mysql-slow.log

# 记录未使用索引的查询（可选）
log_queries_not_using_indexes=1

# 通用日志  开启会他们记录所有的sql操作，建议不要在生产环境中去开启，只要测试环境中去开启，方便调试
general_log=1
general_log_file=/usr/local/mysql/data/general.log

# Time: 2026-05-26T10:00:38.070872Z
# User@Host: root[root] @ localhost []  Id:     8
# 执行的多久
# Query_time: 0.419292  Lock_time: 0.000002  Rows_sent: 655360  Rows_examined: 655360
use test5;
SET timestamp=1779789637;
# 语句
select * from user2;

use db_itheima;

-- 创建测试表
drop table if exists tb_people;
create table tb_people (
    id   int auto_increment primary key,  -- 主键，自动增长
    name varchar(50),                     -- 姓名
    age  int                              -- 年龄
);

-- 创建存储过程
drop procedure if exists insertdata;
delimiter //
create procedure insertdata()
begin
    declare i int default 1;              -- 声明变量i，默认值为1
    start transaction;                    -- 开启事务
    while i <= 2000000                    -- 循环200万次
        do
            insert into tb_people (name, age)
                values (concat('user_', i), floor(18 + (rand() * 42)));
            set i = i + 1;               -- i自增
        end while;
    commit;                               -- 提交事务
end
// delimiter ;

-- delimiter用于临时更改语句结束符（默认;），常用于定义存储过程、触发器等包含分号的复合语句。

-- 调用存储过程
call insertdata();

-- 移除自动增长，因为有自动增长的情况，主键无法移除的，因为自动增长所在字段要求必须是一个key索引
alter table tb_people
    change id id int,      -- 先移除自动增长
    drop primary key;      -- 移除主键

-- 检查数据生成结果
select count(*) from tb_people;

-- 做一个查询，然后查看效果
select * from tb_people where id = 1990000;

-- 查看性能分析
EXPLAIN select * from tb_people where id = 1990000;

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 查看索引
SHOW INDEX FROM 表名;  -- 详细显示所有索引
-- 等价于
show keys from 表名;

-- 第二种：直接展示
desc student;             -- 简略显示，PRI/UNI/MUL 标记索引类型

-- unique 唯一索引
alter table 表名 add unique (字段);

-- key/index 普通索引，这里以 name 举例
alter table student add index 索引名称 (字段);
-- 或者
create index 索引名称 on 表名 (字段);

-- 联合索引：可以针对多个字段建立索引 on 数据表(字段1,字段2)
alter table tb_student add index index_name_age (name, age);
-- 或者
create index index_name_age on tb_student (name, age);

-- 没加索引情况
explain select * from tb_people where id = 1990000;

+----+------+------+------+---------+----------+-------------+
| id | type | key  | rows | filtered | Extra                  |
+----+------+------+------+---------+----------+-------------+
|  1 | ALL  | NULL | 2M   | 100.00  | Using where |
+----+------+------+------+---------+----------+-------------+

-- 添加主键索引
alter table tb_people add primary key(id);

-- 查看表结构
desc tb_people;
show create table tb_people;
show index from tb_people;

-- 添加索引的情况
explain select * from tb_people where id = 1990000;

+----+-------+---------+------+----------+-------+
| id | type  | key     | rows | filtered | Extra |
+----+-------+---------+------+----------+-------+
|  1 | const | PRIMARY |    1 | 100.00  |       |
+----+-------+---------+------+----------+-------+