MYSQL

关键字补充

distinct

指定某个属性唯一返回

select distinct id from table;

asc/desc

升序/降序

limit/offset

limit 指定返回多少行结果，offset指定从哪里开始剪

as

给表达式起别名

in

select device_id ,gender, age, university, gpa
from user_profile
where university IN ("北京大学","复旦大学","山东大学");

查询基本操作

​
select atable.*,btable.*,func(c.table) as cc
from mytable
inner join dtable
where condition
order by column limit 3 offset 3 desc/asc
group by column
having group_condition

​

 

库基础操作

创建数据库

create database if not exists mydatabase;

删除数据库

drop database if exists mydatabase;

选择数据库

use mydatabase;

数据类型

表基础操作

创建表

create table user(
    id int primary key ,
    name varchar(50)
)

 删除表

drop table mytable;

插入表

insert into user(id, name) values (1,'lhd'),(2,'LHD');

查询表

select * from users;//选择所有列的的所有行
select username from users;//选择特定列的所有行
select * from users where cast = true;//选择特定条件
select * from users order by day;//默认按日期升序排序
select * from users order by day desc ;//降序
select * from users limit 10;//分页查询前10条表中数据

where字句

 

更新表

update users
set user=(
    select sum(amount)
    from orders
    where cast(orders as id)//orders.id=customers.id
    )
where customer_type='P'

 删除表

delete from user
where user.id in(
    select user.id
    from orders
    where orders.data<'2023-01-01'
    )

排序与分组表

select user1,user2 from table_name where condition order by day;//desc
select user1,user2 from table_name where condition group by col1,col2;

函数

字符串函数

select concat('HELLO','MYSQL');//将字符拼接在一起
select lower('Hello');//将字符串全部小写
select upper('Hello');//将字符串全部大写
select lpad('01',5,0);//将某字符串的左边共5位补上0
select rpad('01',5,0);//将某字符串的右边共5位补上0
select trim('  HELLO  WORLD  ');//去除字符串左边和右边的空格
select substring('Hello MYSQL',1,5);//从第1个开始截取5个字符

数值函数 

select ceil(1.5);//向上取整
select floor(1.1);//向下取整
select mod(3,4);//3除4取余
select rand();//返回随机数，0到1
select round(2.344,2);//对2.34保留2位小数
select lpad(round(rand()*1000000,0),6,0);//生成一个6位数的随机验证码

 日期函数

select curdate();//当前年月日
select curtime();//当前时分秒
select now();//当前年月日时分秒
select year(now()),select month(now()),select day(now());//当前年或月或日
select date_add(now(),interval 70 day);//从当前往后70天
select datediff('2021-12-01','2021-11-01');//求两个日期之间的差值
select name,datediff(curdate(),entryday) as 'entrydays' from emp order by entrydays desc;
//查询所有员工的入职天数，并根据入职天数倒序排序

流程函数

select if(true,'yes','no');//如果第一个为真就输出yes
select ifnull('nonull','yesnull');//如果第一个非空就输出第一个，否则输出第二个
select
    name,
    (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;
//统计班级各个学员的成绩，>=85，展示优秀，>=60，展示及格，否则展示不及格
select
    id,
    name,
    (case whem math >= 85 then '优秀' when math >= 60 then '及格' else '不及格') as '数学'
from student;

统计函数 

 约束

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 '用户表'

insert into user(name, age, status, gender) VALUE ('TOM1',19,'1','男')，('TOM2',25,'0','男');

外键约束 （两张表之间）

alter table emp add constraint emp_dept_id foreign key (dept_id) references dept(id);
//添加外键
alter table emp drop foreign key emp_dept_id;//删除外键

alter table emp add constraint emp_dept_id foreign key (dept_id) references dept(id) on UPDATE cascade on DELETE cascade;
//外键的删除和更新行为（修改主表，子表会变化，没了cascade会报错

alter table emp add constraint emp_dept_id foreign key (dept_id) references dept(id) on UPDATE set null on delete set null;
//删主表，子表相应的数据置为null

多表查询

多表关系

一对多

多对多

 一对一

SELECT column, another_table_column, …
FROM mytable （主表）
INNER JOIN another_table （要连接的表）
    ON mytable.id = another_table.id 
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

select movies.*,boxoffice.*
from movies
inner join boxoffice on movies.id = boxoffice.movie_id
where international_sales > domestic_sales
order by rating desc

内连接

隐式

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

显式 

select emp.name ,dept.name from emp inner join dept on emp.dept_id = dept.id;

外连接

左外

​//语法与inner join一样
select mytable.*,another_table.*
from mytable(主表）
inner/left/right/full join annther_table(要连接的表）
 on mytable.id = another_table.id(主键连接）
where 
order by asc/desc
limit offset

​

右外 

//查询emp表的所有数据，和对应的部门信息
//表结构：emp，dept
//连接条件：emp.dept_id=dept.id
    select d.*, e.*,d.name from emp e right outer join dept d on e.dept_id=d.id;

全连接

自连接

select a.name,b.name from emp a,emp b where a.managerid =b.id;
//查询员工及其所属领导的名字

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

联合查询 

select * from emp where  salary < 5000
union all
select * from emp where age > 50;
//对于联合查询的多张表的列数必须保持一致（*和*），字段类型也需要保持一致
//union all会将全部的数据直接合并在一起，union会将合并之后的数据去重

子查询（嵌套查询）

标量子查询

select * from emp where dept_id = (select id from dept where name = '销售部');

列子查询

select * from emp where dept_id in (select id from  dept where name ='销售部' or name = '市场部');
//根据部门ID，查询员工信息

行子查询

select * from  emp where (salary,managerrid) = (select salary,managerid from emp where  name = '张无忌')；
                   //查询与“张无忌”的薪资及直属领导相同的员工信息

表子查询 

select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' or name = '宋远桥')；
                  //查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息

 事务

法1：

select @@autocommit;
set @@autocommit = 0;//设置为手动提交
commit ;//提交事务
rollback ;//回滚事务

法2：

start transaction ;//开启事务
commit ;//提交事务
rollback ;//回滚事务

存储引擎

create table my_isam(
    id int,
    name varchar(10)
)engine = MyISAM;

进阶

索引

 

create index idx_user_name on tb_user(name);//创建索引
create unique index idx_user_name on tb_user(name);//创建唯一索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);//创建联合索引
drop index idx_user_name on tb_user;//删除索引

最左前缀法则

索引失效 

 

 

sql提示

 覆盖索引

 

 前缀索引

 单列索引和联合索引

 

索引设计原则

性能优化

慢日志 

show global status like 'Com___';//查看执行频次

show variables like 'slow_query_log';//查看是否开启慢日志查询开关



slow_query_log=1//要去文件中（修改）开启MySQL慢日志查询开关
long_query_time=2//设置慢日志的时间为两秒，sql语句执行时间超过2秒，就会视为慢查询，记录慢查询日志

profiling

select @@profiling;//命令行查看是否打开
set profiling = 1;//打开开关
show profiles ;//查看每一条sql的耗时基本情况
show profile for query query_id;//查看指定query_id的sql语句各个阶段的耗时情况
show profile cpu for query query_id;//查看指定query_id的sql语句cpu的使用情况

explain执行计划

SQL优化

插入数据

 主键优化

 

 order by优化

 

group by优化

limit分页优化

 count优化

update优化

 视图

create or replace view stu as select id,name from student where id <=10;
//创建视图
show create view stu;
select * from stu where id <= 3;
//查询视图
create or replace view stu as select id,name,no from student where id <= 10;
alter view stu as select id,name from student where id <= 10;
//修改视图
drop view if exists stu;
//删除视图

检查选项 

 

创建视图的视图 

cascaded：加上with cascaded check option 会检查报错，不加则创建成功；加上后视图的视图要检查是否满足条件

 

 local：

更新及作用

 存储过程

定义（类似函数）

基本语法

create procedure p1()
begin
    select count(*) from student;
end;
//创建
call p1();
//调用
show create procedure p1;//查看单个存储过程
select * from information_schema.routines where routine_schema = 'itcast';//查看所有存储过程

drop procedure if exists p1;
//删除

 delimiter设定结束符（命令行操作）

系统变量

show variables ;//查看系统变量
show session variables ;//查看会话变量
show session variables like 'auto%';//结合模糊匹配
select @@session.autocommit;//准确查看会话变量
select @@global.autocommit;//准确查看系统变量
set autocommit = 0;//设置系统变量

 用户定义变量

set @myname = 'lhd';//可以用“=”赋值
set @myage := 10;//建议用“：=”赋值
set @mygender := '男',@myhobby := 'java';//多赋值
select @mycolor := 'red';//另一种赋值
select count(*) into @mycount from tb_user;//将表的统计数据赋值给变量
select @myname;//查看

局部变量（存储过程begin，end内）

create procedure p2()
begin
    declare stu_count int default 0;
    set stu_count := 10;
    select count(*) into stu_count from student;
    select stu_count;
end;

if判断

create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
    select result;
end;

case

create procedure p6(in month int)
begin
    declare result varchar(10);
    case
        when month >= 1 and month <= 3 then
                set result := '第一季度';
        when month >= 4 and month <= 6 then
                set result := '第二季度';
        when month >= 7 and month <= 9 then
                set result := '第三季度';
        when month >= 10 and month <= 12 then
                set result := '第四季度';
        else
                set result := '非法参数';
    end case;
    select concat('您输入的月份为：',month,'所属的季度为：',result);
end;
call p6(6);

 循环while

create procedure p7(in n int)
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
        set n := n - 1;
        end while;
    select total;
end;
call p7(10);

循环repeat 

create procedure p8(in n int)
begin
    declare total int default 0;
    repeat
        set total := total + n;
        set n := n - 1;
    until n <= 0
        end repeat;
end;

循环loop 

create procedure p9(in n int)
begin
    declare total int default 0;
    sum:loop
        if n<=0 then
            leave sum;//离开循环
        end if;
        set total := total + n;
        set n := n - 1;
    end loop sum;
    select total;
end;
call p9(10);
//iterate直接进入下一次循环

存储过程（参数）

游标

create procedure p11(in uage int)
begin
    declare uname varchar(10);
    declare upro varchar(10);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    //创建游标
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(//创建表
        id int primary key auto_increment,
        name varchar(10),
        profession varchar(10)
    );
    open u_cursor;
    while true do
        fetch u_cursor into uname,upro;//捕获信息
        insert into tb_user_pro values (null,uname,upro);//通过游标捕获的信息插入表
        end while;
    close u_cursor;
end;

条件处理程序

类似异常处理

  declare exit handler for sqlstate '02000' close u_cursor;//条件处理

 存储函数

create function fun1(n int)
returns int deterministic 
begin 
    declare total int default 0;
    while n > 0 do
        set total := total + n;
        set n := n - 1;
        end while;
    return total;
end;
select fun1(50);

触发器

定义

基本语法

insert 

//通过触发器记录tb_user表的数据变更日志，将变更日志插入到日志表user_logs中，包含增加，修改，删除；
//准备日志表user_logs
create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型，insert/update/delete',
    operation_time datetime not null comment '操作时间',
    operation_id int(11) not null comment '操作的ID',
    operation_params varchar(500) comment '操作参数',
    primary key ('id')
)engine = innodb default charset = utf8;
//插入数据触发器
create trigger tb_user_insert_trigger
    after insert on tb_user for each row
begin
    insert into user_logs(id, operation, operation_time, operation_id, operation_params) values
    (null,'insert',now(),new.id,concat('插入的数据内容为： id=',new.id,',name=',new.name));
end;
show triggers ;//查看
drop trigger tb_user_insert_trigger;//删除
//插入数据到tb_user
insert into tb_user(id,name,phone)
values (25,'二皇子','133');

update 

//插入数据触发器
create trigger tb_user_update_trigger
    after update on tb_user for each row
begin
    insert into user_logs(id, operation, operation_time, operation_id, operation_params) values
    (null,'update',now(),new.id,concat('更新之前的数据： id=',old.id,',name=',old.name,'更新之后的数据： id=',new.id,',name=',new.name));//new和old
end;
//更新数据到tb_user
update tb_user set age = 32 where id = 23;

delete

//插入数据触发器
create trigger tb_user_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs(id, operation, operation_time, operation_id, operation_params) values
    (null,'delete',now(),old.id,concat('删除之前的数据： id=',old.id,',name=',old.name));
end;
//删除数据到tb_user
delete from tb_user where id = 25;

锁

定义 

 全局锁

flush tables with read lock;
//加锁
mysqldump -h -uroot -p1234 db01 > D:/db01.sql
//widow命令行 -h主机码 -u用户名 -p密码 需要数据备份的数据库 备份的文件地址
unlock tables ;
//释放锁

 表级锁

 表锁

lock tables score read ;
//加只读锁：表无论哪个终端只能读
lock tables score write ;
//加写锁，当前终端能读写，其他终端不可读写
unlock tables;
//释放锁

元数据锁

 

意向锁

 

行级锁

行锁

 间隙锁和临键锁

间隙锁

 Innodb引擎

逻辑存储结构

架构

内存架构

 

 

磁盘结构

后台线程

事务原理

 

MVCC        

 

 MySQL管理

常用工具