# MYSQL

**Repository Path**: SuLiu_laile/mysql

## Basic Information

- **Project Name**: MYSQL
- **Description**: 用于学习MYSQL
- **Primary Language**: SQL
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2023-10-30
- **Last Updated**: 2023-12-06

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 基础篇学习过程的练习语句

```sql
-- 1.添加数据
insert into employee(id, workno, name, gender, age, idcard, entrydate)
values (1, '1', 'itcast', '男', 10, '1234567891', '2000-11-11');

# 错误写法
# insert into employee(id, workno, name, gender, age, idcard, entrydate) values (2,'2','itcast2','男',-1,'1234567891','2000-11-11');

# 不写字段名，直接插入全部字段
insert into employee
values (2, '2', 'itcast2', '男', 18, '1234567891', '2012-12-12');

# 插入多条数据
insert into employee
values (3, '3', '苏六01', '男', 18, '1234567891', '2012-12-12'),
       (4, '4', '苏六02', '男', 18, '1234567891', '2012-12-12');

-- ######################################

-- 2.修改数据
update employee
set name = 'itheima'
where id = 1;

update employee
set name   = '小赵',
    gender = '女'
where id = 1;

update employee
set entrydate = '2008-01-01';

-- 删除数据
delete
from employee
where gender = '女';

delete
from employee;
# 查询全部
select *
from employee;

-- ######################################

-- 3.查询数据
# 实际开发中，不要直接写*
select *
from employee;

# 起别名
select name as '姓名'
from employee;
# as可以省略
select name '姓名'
from employee;

# 去除重复记录
select distinct workaddress '工作地址'
from employee;

-- ######################################

-- 4.条件查询
-- 略过

-- 5.聚合函数
# 1.统计该企业员工数量
select count(*)
from employee;

# 2.统计该企业员工的平均年龄
select avg(age)
from employee;

# 3.统计该企业员工的最大年龄
select max(age)
from employee;

# 4.统计该企业员工的最小年龄
select min(age)
from employee;

# 5.统计北京地区的员工的年龄之和
select sum(age)
from employee
where workaddress = '北京';

-- ######################################

-- 6.分组查询
# 1.根据性别分组，统计男性和女性员工的数量
select gender '性别', count(*) '数量'
from employee
group by gender;

# 2.根据性别分组，统计男性员工和女性员工的平均年龄
select gender '性别', avg(age) '平均年龄'
from employee
group by gender;

# 3.查询年龄小于18的员工，并根据工作地址分组，获取员工数量大于等于2的工作地址
select workaddress '工作地址', count(*) '数量'
from employee
where age < 19
group by workaddress
having count(*) >= 2;

-- 7.排序查询
# 1.根据年龄对公司的员工进行升序排序
select *
from employee
order by age;

# 2.根据入职时间，对员工进行降序排序
select *
from employee
order by entrydate desc;

# 3.根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序
select *
from employee
order by age, entrydate desc;

-- ######################################

-- 8.分页查询
# 1.查询第一页员工数据，每页展示2条记录
select *
from employee
limit 0,2;

# 2.查询第二页员工数据，每页展示2条记录
select *
from employee
limit 2,2;

-- ######################################

-- 9.综合案例
# 1.查询年龄为16.18.19的员工信息
select *
from employee
where gender = '男'
  and age in (16, 18, 19);

# 2.查询性别为女，并且年龄在16-18岁（含）以内的姓名为三个字的员工
select *
from employee
where gender = '女'
  and age between 16 and 18
  and name like '___';

# 3.统计员工表中，年龄小于18岁，男性和女性的人数
select gender '性别', count(*) '数量'
from employee
where age < 18
group by gender;

# 4.查询所有年龄小于18岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同过按入职时间降序排序
select name '姓名', age '年龄'
from employee
where age < 18
order by age, entrydate desc;

# 5.查询性别为男，且年龄在16-18岁（含）以内的3个员工信息，对查询的结果按年龄升序排列，年龄相同按入职时间升序排序
select *
from employee
where gender = '男'
  and age between 16 and 18
order by age, entrydate desc
limit 0,3;

-- ######################################

-- 10.用户管理
# 1.创建用户itcast，只能在当前主机localhost访问，密码123456
create user 'itcast'@'localhost' identified by '123456';

# 2.创建用户heima，可以在任意主机上访问该数据库，密码是123456
create user 'heima'@'%' identified by '123456';

# 3.修改用户heima的访问密码为1234
alter user 'heima'@'%' identified with mysql_native_password by '1234';

# 4.删除itcast@localhost用户
drop user 'itcast'@'localhost';

-- ######################################

-- 11.权限控制
# 1.查询权限
show grants for 'heima'@'%';

# 2.授予权限
grant all on mysqltest.* to 'heima'@'%';

# 3.撤销权限
revoke all on mysqltest.* from 'heima'@'%';

-- ######################################

-- 12.函数
-- 12.1 字符串函数
# 1.concat 字符串拼接
select concat('hello', ' world');

# 2.lower 大写转小写
select lower('HELLO');

# 3.upper 小写转大写
select upper('hello');

# 4.lpad 左填充
select lpad('hello', 6, '-');

# 5.rpad 右填充
select rpad('hello', 6, '-');

# 6.trim 去掉两边的空格
select trim(' hello world ');

# 7.substring 字符串截取
select substring('hello', 1, 3);

# 8.练习1
update employee
set workno = lpad(workno, 5, '0');

-- ######################################

-- 12.2 数值函数
# 1.ceil向上取整
select ceil(1.5);
# 2

# 2.floor向下取整
select floor(1.9);
# 1

# 3.mod 取余
select mod(3, 4);
# 3

# 4.rand 随机数（0-1）
select rand();

# 5.round 四舍五入（第一位待处理的数字，第二位是保留几位小数 ）
select round(2.345, 2);

# 6.案例：
select round(((rand() * 899999) + 100000), 0);
select lpad(round(rand() * 1000000, 0), 6, '0');

-- ######################################

-- 12.3 日期函数
# 1.curdate():当前日期
select curdate();

# 2.curtime()：当前时间
select curtime();

# 3.now():返回当前日期和时间
select now();

# 4.year(now()):获取指定日期的年份
select year(now());

# 5.month(now()):获取指定日期的月份
select month(now());

# 6.day(now()):获取指定时间的日期
select day(now());

# 7.date_add(now(),INTERVAL 2 day)  返回一个具体时间间隔之后的时间，单位自己定
select date_add(now(), INTERVAL -2 day);

# 8.DATEDIFF(now(),date_add(now(),INTERVAL 10 day)):求两个时间的相差天数，第一个参数减去第二个参数
select DATEDIFF(now(), date_add(now(), INTERVAL 10 day));

# 9.练习
select name, datediff(curdate(), entrydate) entrytime
from employee
order by entrytime desc;

-- ######################################

-- 12.4 流程控制函数
# 1.if
select if(true, 'Ok', 'Error');
# Ok

# 2.ifnull
select ifnull(null, 'Error');
# Error

# 3.练习1：case when then else end
# 需求：查询emp表的员工姓名和工作地址（北京/上海 ---> 一线城市，其他 ---> 二线城市）
select name,
       (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from employee;

# 4.练习2
select id,
       name,
       (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end)       '数学',
       (case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) '英语',
       (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) '语文'
from scope;

-- ######################################

-- 13.约束
# 1.创建表
create table user
(
    id     int primary key auto_increment comment '主键',
    name   varchar(10) not null unique comment '姓名',
    age    int check (age > 0 && age <= 120) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

# 2.插入数据
insert into user (name, age, status, gender)
values ('Tom1', 19, '1', '男'),
       ('Tom2', 25, '1', '男');

# insert into user (name,age,status,gender) values
#                                               (null,19,'1','男');
# insert into user (name,age,status,gender) values
#                                               ('Tom2',19,'1','男');
insert into user (name, age, gender)
values ('Tom3', 19, '男');
# 3.外键约束
# 1.创建部门表和员工表
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id, name)
values (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办');

# 2.创建员工表
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

# 3.添加外键
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);

# 4.删除外键
alter table emp
    drop foreign key fk_emp_dept_id;

# 5.外键的删除和更新行为
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references dept (id) on update cascade on delete cascade;
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references dept (id) on update set null on delete set null;


-- 14.多表查询
# 14.1 多对多
#   创建表结构
# 1.创建学生表
create table student
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no   varchar(10) comment '学号'
) comment '学生表';
insert into student
values (null, '戴季斯', '2206831541'),
       (null, '张三', '2206831542'),
       (null, '李四', '2206831543'),
       (null, '王五', '2206831544');

# 2.创建课程表
create table course
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into course
values (null, 'Java'),
       (null, 'PHP'),
       (null, 'MYSQL'),
       (null, 'Hadoop');

# 3.创建中间表
create table student_course
(
    id        int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';
insert into student_course
values (null, 1, 1),
       (null, 1, 2),
       (null, 1, 3),
       (null, 2, 2),
       (null, 2, 3),
       (null, 3, 4);


# 14.2 一对多
# 创建表结构
create table tb_user
(
    id     int auto_increment primary key comment '主键ID',
    name   varchar(10) comment '姓名',
    age    int comment '年龄',
    gender char(1) comment '1: 男 , 2: 女',
    phone  char(11) comment '手机号'
) comment '用基本信息表';

create table tb_user_edu
(
    id            int auto_increment primary key comment '主键ID',
    degree        varchar(20) comment '学历',
    major         varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool  varchar(50) comment '中学',
    university    varchar(50) comment '大学',
    userid        int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user (id)
) comment '用户教育信息表';

insert into tb_user(id, name, age, gender, phone)
values (null, '黄渤', 45, '1', '18800001111'),
       (null, '冰冰', 35, '2', '18800001112'),
       (null, '码云', 55, '1', '18800001118'),
       (null, '李彦宏', 50, '1', '18800001119');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid)
values (null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
       (null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
       (null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
       (null, '本科', '应用数学', '阳泉第一小学', '阳泉第一中学', '清华大学', 4);


# 多表查询
# 1.笛卡尔积
select *
from emp,
     dept;
# 2.消除无效的笛卡尔积
select *
from emp,
     dept
where emp.dept_id = dept.id;

-- 14.3 内连接
# 1.查询每个员工的姓名，及关联的部门的名称（隐式内连接）
select emp.name, dept.name
from emp,
     dept
where emp.dept_id = dept.id;

select e.name, d.name
from emp e,
     dept d
where e.dept_id = d.id;

# 2.查询每个员工的姓名，及关联的部门的名称（显式内连接）
select e.name, d.name
from emp e
         join dept d on e.dept_id = d.id;

-- 14.4 外连接
# 1.左外连接
select e.*, d.name
from emp e
         left outer join dept d on e.dept_id = d.id;
# 省略outer
select e.*, d.name
from emp e
         left join dept d on e.dept_id = d.id;

# 2.右外连接
select d.*, e.*
from emp e
         right join dept d on e.dept_id = d.id;

-- 14.5 自连接(同一张表中的连接)
# 1.查询员工 及其 所属领导的名字
select a.name, b.name
from emp a,
     emp b
where a.managerid = b.id;

# 2.查询所有员工emp及其领导的名字 emp,如果员工没有领导，也需要查询出来
select a.name '员工', b.name '领导'
from emp a
         left join emp b on a.managerid = b.id;

-- 14.6 联合查询
# 1.将薪资低于9000的员工，和年龄小于50岁的员工全部查询出来
# union all联合全部记录
select *
from emp
where salary < 9000
union all
select *
from emp
where age < 50;

# union 具有去重效果
select *
from emp
where salary < 9000
union
select *
from emp
where age < 50;


-- 14.7 子查询
# 1.标量子查询
# 1.1查询研发部的所有员工信息
select id
from dept
where name = '研发部';
select *
from emp
where dept_id = '6';

# 子查询
select *
from emp
where dept_id = (select id from dept where name = '研发部');

# 1.2查询在张无忌之后入职的员工信息
select entrydate
from emp
where name = '张无忌';
select *
from emp
where entrydate > '2005-12-05';

# 子查询
select *
from emp
where entrydate > (select entrydate from emp where name = '张无忌');

# 2.列子查询
# 2.1 查询研发部和总经办的所有员工信息
# 1..查询研发部和总经办的id
select id
from dept
where name = '研发部'
   or name = '总经办';
select *
from emp
where dept_id in (select id from dept where name = '研发部' or name = '总经办');

# 2.2 查询比研发部所有人工资都高的员工信息
select id
from dept
where name = '研发部';
# 第一种写法
select *
from emp
where salary > all (select salary from emp where dept_id = (select id from dept where name = '研发部'));
# 第二种写法
select *
from emp
where salary > (select MAX(salary) from emp where dept_id = (select id from dept where name = '研发部'));

# 2.3 查询比研发部其中任意一人工资高的员工信息
select *
from emp
where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

# 3.行子查询
# 3.1 查询与'张无忌'的薪资及其直属领导相同的员工信息
select salary, managerid
from emp
where name = '张无忌';
select *
from emp
where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

# 4.表子查询
# 4.1 查询与'张无忌'的职位和薪资相同的员工信息
select job, salary
from emp
where name = '张无忌';
select *
from emp
where (job, salary) in (select job, salary from emp where name = '张无忌');

# 4.2 查询入职时间是 '2006-01-01'之后的员工信息,及其部门信息
select *
from emp
where entrydate > '2006-01-01';

# 第一种写法
select e.*, d.*
from emp e
         left join dept d on d.id = e.dept_id
where entrydate > '2006-01-01';
# 第二种写法
select e.*, d.*
from (select * from emp where entrydate > '2006-01-01') e
         left join dept d on d.id = e.dept_id;

-- 15 事务
# 1.创建数据库
create table account
(
    id    int auto_increment primary key comment '主键ID',
    name  varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表';
insert into account(id, name, money)
values (null, '张三', 2000),
       (null, '李四', 2000);

# 2.出错-恢复数据
update account
set money = 2000
where name = '张三'
   or name = '李四';

# 3.转账操作（张三给李四转账1000）
# 方式1：
# 查看当前事务提交方式
select @@autocommit;

# 设置事务提交方式(设置为手动提交)
set @@autocommit = 0; -- 设置为手动提交

# 1.查询张三账户余额
select *
from account
where name = '张三';

# 2.将张三账户余额-1000
update account
set money = money - 1000
where name = '张三';

# 出异常了啊...

# 3.将李四账户余额+1000
update account
set money = money + 1000
where name = '李四';

# 提交事务
commit;

# 回滚事务
rollback;

# --------------------------------------

# 方式2：
# 查看当前事务提交方式
select @@autocommit;

# 设置事务提交方式(设置为手动提交)
set @@autocommit = 1; -- 设置为手动提交

# 开启事务
start transaction;

# 1.查询张三账户余额
select *
from account
where name = '张三';

# 2.将张三账户余额-1000
update account
set money = money - 1000
where name = '张三';

出异常了啊...

# 3.将李四账户余额+1000
update account
set money = money + 1000
where name = '李四';

# 提交事务
commit;

# 回滚事务
rollback;

update account
set money = 2000
where name = '张三'
   or name = '李四';

-- 15.2 事务隔离级别
# 1.查看事务隔离级别
select @@transaction_isolation;
# REPEATABLE-READ
# repretable-read

# 2.设置事务隔离级别
set session transaction isolation level read uncommitted ;
set session transaction isolation level repeatable read;
```



# 进阶篇联系语句

```sql
-- 1.存储引擎
# 1.1、存储引擎简介
# 1查询建表语句
show create table account;
# 2.查询当前数据库支持的存储引擎
show engines;
# 3.创建表my_myisam,并指定MyISAM存储引擎
create table my_myisam(
    id int,
    name varchar(10)
) engine = MyISAM;
# 4.创建表my_memory,并指定Memory存储引擎
create table my_memory(
    id int,
    name varchar(10)
) engine = Memory;

use mysqltest;
# 1.2、存储引擎特点
show variables like 'innodb_file_per_table';
```

```sql
select database();
# 进阶篇
-- 1.索引
# 1.1索引的语法
# 1.创建数据库
use mysqltest;
create table tb_user(
	id int primary key auto_increment comment '主键',
	name varchar(50) not null comment '用户名',
	phone varchar(11) not null comment '手机号',
	email varchar(100) comment '邮箱',
	profession varchar(11) comment '专业',
	age tinyint unsigned comment '年龄',
	gender char(1) comment '性别 , 1: 男, 2: 女',
	status char(1) comment '状态',
	createtime datetime comment '创建时间'
) comment '系统用户表';


INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO mysqltest.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

select * from tb_user;

# 2.查看索引
show index from tb_user;
# 或者在命令行中使用：show index from tb_user\G; 查看会自动格式化

# 3.为name字段创建索引
create index idx_user_name on tb_user(name);

# 4.为电话添加唯一索引
create unique index idx_user_phone on tb_user(phone);

# 5.为profession,age,status三个字段创建联合索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);

# 6.为email创建索引
create index idx_user_email on tb_user(email);

# 7.删除email的索引
drop index idx_user_email on tb_user;

# 1.2性能优化
# 1.查看执行频次
show global status like 'Com_______';
select * from tb_user;

# 2.慢查询日志
show variables like 'slow_query_log';

# 3.profile
# 查看是否支持profile
select @@have_profiling;
#查看是否开启profile
select @@profiling; # 0:未开启
# 开启profile
set profiling  = 1;
# 查看列表
show profiles;

select * from tb_user;

# 4.explain
explain select * from tb_user where id = 1;
# 创建表
create table student(
    id   int auto_increment comment '主键ID' primary key,
    name varchar(10) null comment '姓名',
    no   varchar(10) null comment '学号'
)comment '学生表';

INSERT INTO student (name, no) VALUES ('黛绮丝', '2000100101');
INSERT INTO student (name, no) VALUES ('谢逊', '2000100102');
INSERT INTO student (name, no) VALUES ('殷天正', '2000100103');
INSERT INTO student (name, no) VALUES ('韦一笑', '2000100104');



create table course(
    id int auto_increment comment '主键ID' primary key,
    name varchar(10) null comment '课程名称'
)comment '课程表';


INSERT INTO course (name) VALUES ('Java');
INSERT INTO course (name) VALUES ('PHP');
INSERT INTO course (name) VALUES ('MySQL');
INSERT INTO course (name) VALUES ('Hadoop');




create table student_course(
    id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';

INSERT INTO student_course (studentid, courseid) VALUES (1, 1);
INSERT INTO student_course (studentid, courseid) VALUES (1, 2);
INSERT INTO student_course (studentid, courseid) VALUES (1, 3);
INSERT INTO student_course (studentid, courseid) VALUES (2, 2);
INSERT INTO student_course (studentid, courseid) VALUES (2, 3);
INSERT INTO student_course (studentid, courseid) VALUES (3, 4);


-- 1.3索引使用规则
# 1.验证索引效率
# 创建表
CREATE TABLE `tb_sku` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `sn` varchar(100) NOT NULL COMMENT '商品条码',
  `name` varchar(200) NOT NULL COMMENT 'SKU名称',
  `price` int(20) NOT NULL COMMENT '价格（分）',
  `num` int(10) NOT NULL COMMENT '库存数量',
  `alert_num` int(11) DEFAULT NULL COMMENT '库存预警数量',
  `image` varchar(200) DEFAULT NULL COMMENT '商品图片',
  `images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表',
  `weight` int(11) DEFAULT NULL COMMENT '重量（克）',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `category_name` varchar(200) DEFAULT NULL COMMENT '类目名称',
  `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',
  `spec` varchar(200) DEFAULT NULL COMMENT '规格',
  `sale_num` int(11) DEFAULT '0' COMMENT '销量',
  `comment_num` int(11) DEFAULT '0' COMMENT '评论数',
  `status` char(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

# 插入十条数据
insert into tb_sku values
                       (1,100000003145001,'华为Meta1',87901,9961,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','真皮包','viney','白色1',39,0,1),
                       (2,100000003145002,'华为Meta2',3,9946,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t23998/350/2363990466/222391/a6e9581d/5b7cba5bN0c18fb4f.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t23998/350/2363990466/222391/a6e9581d/5b7cba5bN0c18fb4f.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','拉拉裤','巴布豆','白色2',54,0,1),
                       (3,100000003145003,'华为Meta3',78903,9993,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t1/25363/12/2929/274060/5c21df3aE1789bda7/030af31afd116ae0.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t1/25363/12/2929/274060/5c21df3aE1789bda7/030af31afd116ae0.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','拉杆箱','莎米特','白色3',7,0,1),
                       (4,100000003145004,'华为Meta4',26604,10000,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t1/25363/12/2929/274060/5c21df3aE1789bda7/030af31afd116ae0.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t1/25363/12/2929/274060/5c21df3aE1789bda7/030af31afd116ae0.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','拉杆箱','莎米特','白色4',0,0,1),
                       (5,100000003145005,'华为Meta5',40805,9996,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t22642/312/2563982615/103706/1398b13d/5b865bb3N0409f0d0.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t22642/312/2563982615/103706/1398b13d/5b865bb3N0409f0d0.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','手机','华为','白色5',4,0,1),
                       (6,100000003145006,'华为Meta6',89206,9930,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t22642/312/2563982615/103706/1398b13d/5b865bb3N0409f0d0.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t22642/312/2563982615/103706/1398b13d/5b865bb3N0409f0d0.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','手机','华为','白色6',70,0,1),
                       (7,100000003145007,'华为Meta7',11707,4,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t20707/78/2349564629/130172/50a245d8/5b8e00e2Nf0bcd624.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t20707/78/2349564629/130172/50a245d8/5b8e00e2Nf0bcd624.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','手机','vivo','白色7',1,0,1),
                       (8,100000003145008,'华为Meta8',99808,9994,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t26281/196/340140952/94362/c353173/5b8f6e63N56334c08.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t26281/196/340140952/94362/c353173/5b8f6e63N56334c08.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','老花镜','夕阳红','白色8',6,0,1),
                       (9,100000003145009,'华为Meta9',41709,10000,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t26281/196/340140952/94362/c353173/5b8f6e63N56334c08.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t26281/196/340140952/94362/c353173/5b8f6e63N56334c08.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','老花镜','夕阳红','白色9',0,0,1),
                       (10,1000000031450010,'华为Meta10',23710,10000,100,'https://m.360buyimg.com/mobilecms/s720x720_jfs/t26281/196/340140952/94362/c353173/5b8f6e63N56334c08.jpg!q70.jpg.webp','https://m.360buyimg.com/mobilecms/s720x720_jfs/t26281/196/340140952/94362/c353173/5b8f6e63N56334c08.jpg!q70.jpg.webp',10,'2019-05-01','2019-05-01','老花镜','夕阳红','白色10',0,0,1);

show tables;

# 查询
select * from tb_sku where sn = '100000003145001' ;
# 为sn设置索引
create index idx_sku_sn on tb_sku(sn);

# 2.最左前缀法则
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0'; # 54
explain select * from tb_user where profession = '软件工程' and age = 31; # 49(status:5)
explain select * from tb_user where profession = '软件工程'; # 47(status:5,age:2)
# 没有第一列，失效了
explain select * from tb_user where age = 31 and status = '0'; # 实现

# 没有中那一列，后面的索引直接失效
explain select * from tb_user where profession = '软件工程' and status = '0'; # 47

# 第一列放到最后
explain select * from tb_user where  age = 31 and status = '0' and profession = '软件工程'; # 54

# 范围查询，后面失效
explain select * from tb_user where profession = '软件工程' and age > 31 and status = '0'; # 49
explain select * from tb_user where profession = '软件工程' and age >= 31 and status = '0'; # 54

# 3.索引失效
# 3.1 使用函数
explain select * from tb_user where substring(phone,10,2) = '15';
# 3.2 字符串类型不加引号
explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;
# 3.3 首部模糊匹配
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';

# 4.SQL提示
# 给profession创建一个单列索引
create index idx_user_pro on tb_user(profession);
show index from tb_user;
explain select * from tb_user where profession = '软件工程';

# 使用sql提示来指定使用的索引
# 建议
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
# 忽略
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
# 强制
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';


# 5.覆盖索引-回表查询


# 6.前缀索引
show index from tb_user;
# 给邮箱创建前缀索引
select *  from tb_user;

select count(distinct substring(email,1,10))/count(*) from tb_user;
select count(distinct substring(email,1,9))/count(*) from tb_user;
select count(distinct substring(email,1,8))/count(*) from tb_user;
select count(distinct substring(email,1,7))/count(*) from tb_user;
select count(distinct substring(email,1,6))/count(*) from tb_user;
select count(distinct substring(email,1,5))/count(*) from tb_user;
select count(distinct substring(email,1,4))/count(*) from tb_user;
# 给邮箱创建前缀索引
create index idx_user_email_5 on tb_user(email(5));
show index from tb_user;

explain select * from tb_user where email = 'lvbu666@163.com';

```