MySql一文快速复习or入门（学习笔记）

前言

该文档记录了mysql大部分要点，为我学习时所做笔记，可供日后查询使用(crtl c crtl v)
大部分代码都是实例，有相应的注释解释
部分括号内注明了关键字

关于MySql的安装配置等前置工作没有说明

DDL

DDL（Data Definition Language）语句： 数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等。

库

创建库

create database db01;

create datebase if not exists db02;

切换使用数据库

use db01;

查看当前正在使用
select database();

删除数据库

drop database db03;
drop database if exists db03;

表

创建表

-- 创建
create table tb_user(
    id int comment 'ID,唯一标识',
    username VARCHAR(20) comment '用户名',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '性别'
) comment '用户表'

约束

-- 非空约束
not null
-- 唯一约束
unique
-- 主键约束
primary key (auto_increment 自动增长)
-- 默认约束
default
-- 外键约束
foreign key

查看（图形化界面不常用）

-- 查看表
show tables;
-- 查看表结构
desc tb_emp;
-- 查看建表语句
show create table 表名;

修改表(可用图形化)

-- 添加字段
alter table tb_user add qq varchar(11) comment 'qq';
-- 修改字段类型
alter table tb_user modify qq varchar(13) comment 'qq';
-- 修改字段名
alter table tb_user change qq qq_num varchar(13) comment 'qq';
-- 删除字段
alter table tb_user drop column qq;
-- 修改表名
rename table tb_emp to emp; 

删除表

drop table if exists tb_user;

DML

DML（Data Manipulation Language）语句: 数据操纵语言，主要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。

插入

-- 为指定字段插入值
insert into tb_user(username,name,gender,create_time,update_time) values('wuji','张无忌',1,now(),now());

-- 为所有字段插入值
insert into tb_user values('wuji','张无忌',1,now(),now());

-- 批量插入数据
insert into tb_user(username,name,gender,create_time,update_time) values('wuji','张无忌',1,now(),now()),('wuji','张无忌',1,now(),now());

修改

update tb_user set name = 'tom' , upadate_time = now() where id = 1;

删除

delete from tb_user where id = 1;

-- 删除整张表的元素
delete from tb_user;

DQL

DQL（Data Query Language）语句：数据查询语言，主要是对数据进行查询操作。常用关键字有 SELECT、FROM、WHERE 等。

基本查询

select name,date from tb_user;
-- 查询所有字段
select [所有字段名] from tb_user;/* 推荐 */

select * from tb_user;
-- 设置别名
select name as 姓名,date as 日期 from tb_user;
select name 姓名,date 日期 from tb_user;
select name '姓 名',date 日期 from tb_user;  /* 让别名里存在空格 */

-- 不重复查询
select distinct job from db_user;

条件查询

-- 查询姓名为Tom的信息 (=)

select * from tb_user where name = 'Tom';

-- 查询id小于等于5的信息(<=)
select * from tb_user where id <= 5;

-- 查询job为空的信息(is)
select * from tb_user where job is null;

-- 不等于(!= <>)
select * from tb_user where password != '123456';

select * from tb_user where password <> '123456';

-- 日期包含(between and)
select * from tb_user where date >= '1991-01-01' and date <= '2000-01-01';

select * from tb_user where date between '1991-01-01' and '2000-01-01';

-- 且(and)
select * from tb_user where date between '1991-01-01' and '2000-01-01' and gender = 1;

-- 或(or in)
select * from tb_user where job = 1 or job =2 or job = 3;

select * from tb_user where job in (1,2,3);

-- 模糊匹配(like)
'-'单个字符
'%'任意个字符
select * from tb_user where name like '__';/* 查找name为两个字的信息 */

select * from tb_user where name like '张%';

分组查询

聚合函数

------------
null 不参与运算
------------
-- 数量(count)
select count(id) from tb_user;
select count(*) from tb_user; /* 推荐 */
--最大小值 
min max
--平均值
avg
--求和
sum

分组查询

-- 根据gender分组，统计数量
select gender,count(*) from tb_user group by gender;
-- 查询age小于等于10的信息，根据job分组，获取人数大于2的职位
-- having后跟分组后的过滤条件

select job,count(*) from tb_user where age <= 10 group by job having count(*) > 2;

流程控制函数

-- if(条件，true的结果，false的结果)
select if(gender = 1,'男','女') 性别,count(*) from tb_user group by gender;

-- case 表达式 when 值1 then 结果1 when 值2 then 结果2 ....... else 结果 end
select (case job when 1 then 老师 when 2 then 医生 when 3 then 快递员 when 4 then 班主任 else 无职业 end) 职位,
count(*) from tb_user group by job;

排序查询

--asc desc
-- 降序排序
select * from tb_user order by date desc;
-- 先升序再降序
select * from tb_user order by date,entry_date desc;

分页查询

-- 从起始索引为0的数据开始查询，每页展示5个数据
select * from tb_emp limit 0,5;

-- 起始索引 = （页码-1）* 每页展示的记录数

多表设计

一对多

外键

可通过图形界面添加外键，无需命令

alter table tb_emp
    add constraint tb_emp_fk_dept_id
        foreign key (dept_id) references tb_dept (id);

白雪，物理外键已不常用

一对一

外键添加约束 not null unique

多对多

实现：建立第三张中间表

多表查询

引入

-- 多表查询-笛卡尔积错误
select * from tb_emp,tb_dept;
-- 条件
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

内连接

-- 隐式内连接
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
-- 别名
select e.name,d.name from tb_emp e,tb_dept d where e.dept_id = d.id;

-- 显式内连接
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;

select tb_emp.name,tb_dept.name from tb_emp join tb_dept on tb_emp.dept_id = tb_dept.id;

外连接

-- 左外连接
select e.name,d.name from tb_emp e left join tb_dept d on tb_emp.dept_id = tb_dept.id; 
-- 右外连接
select e.name,d.name from tb_dept d right join tb_emp e on tb_emp.dept_id = tb_dept.id; 

子查询

标量子查询

子查询的返回值是单个值

-- 查询“教研部”的所有员工信息

select * from tb_emp where dept_id = (select id from tb_emp where name = '教研部');

-- 查询在方东白入职之后的基本信息
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

列子查询(in, not in)

返回结果是一列多行值

-- 查询“教研部”和“咨询部”的所有员工信息

select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

行子查询

返回结果是多列一行值

-- 查询与韦一笑入职日期和职位相同的员工信息
select * from tb_emp where (entrydate,job) = (
select entrydate,job from tb_emp where name = '韦一笑');

表子查询

返回结果是多行多列

select e.*,d.name from (select * from tb_emp where entrydate > '2006-01-01') e,tb_dept d where e.dept_id = d.id;

事务

要么同时成功，要么同时失败！！！

start transaction;
delete from tb_emp where dept_id = 2;
delete from tb_dept where id = 2;
commit;
rollback;

索引

提升查询效率，但降低了insert update delete的效率

引入

-- 创建索引(create,index,on)
create index idx_sku_sn on tb_sku(sn);
-- tb_sku --- 表名
-- sn     --- 字段名

创建索引后瞬间变快

结构

多路平衡搜索树(B+Tree)
b站教程视频链接

操作语法

-- 为tb_emp的name创建一个索引
create index idx_emp_name on tb_emp(name);
-- 查询索引
show index from tb_emp;
-- 删除索引
drop index idx_emp_name tb_emp;

注意事项

主键索引：主键字段在建表时，会自动创建主键索引
唯一索引：字段添加唯一约束时，会自动创建唯一索引

MySql完结撒花

接下来可继续学习MyBatis,通过java操作数据库或其他如python的Django