MySQL学习笔记
MySQL
数据库的本质
本质其实就是一款基于网络通信的应用程序
也就意味着数据库软件其实有很多
关系型数据库
MySQL、oracle、db2.access、sql server
非关系型数据库
redis、mongodb、mencache
关系型
1.数据之间彼此有关系或约束性
2.存储数据的表现形式通常是以表格存储 每个字段还会有存储类型的限制
非关系型
存储数据通常都是以k、v键值对的形式
sql语句
针对库的增删改查
增：
create database db1;
create database db1 charset= 'gbk'; 字符编码
查：
show databases; 查看所有
show create database db1; 查看单个
改：
alter database db1 charset = 'utf8';
删：
drop database db1;
针对表的增删改查
在操作表（文件）的时候 需要指定所在的库（文件夹）
查看当前所在的库的名字
select database();
切换库：
use db1;
增: (表头)
create table t1(id int，name char(4));
查：
show tables; 查看当前库下所有的表明
show create table t1; 查单个表
describe t1; 支持简写 desc t1;
改：
alter table t1 modify name char(16);
删：
drop table t1;
针对数据的增删改查
增：
insert into t1 values(1,'jason');
insert into t1 values(1,'jason'),(2,'egon');
删：
delect from t1 where id > 1;
delect from t1 where name='jason';
将表所有的数据清空
delect from t1;
改：
update t1 set name='DSB' where id > 1;
查：
select * from t1; 该命令当数据特别大得到时候不建议使用
select id,name from t1;
创建表的完整语法
create table 表名(
字段名1 类型(宽度) 约束条件，
字段名2 类型(宽度) 约束条件，
字段名3 类型(宽度) 约束条件，
);
注意
1.在同一张表中字段名不能重复
2.宽度和约束条件是可选的(可写可不写)而字段名和字段类型是必须的
约束条件写得到话 也支持写多个
字段名1 类型(宽度) 约束条件1 约束条件2...,
3.最后一行不能有逗号
严格模式
如何查看严格模式：
show variables like “%mode”
模糊匹配/查询：
关键字 like
%：匹配任意多个字符
_：匹配任意单个字符
修改严格模式：
set session 只在当前窗口有效
set global 全局有效
set global sql_mode = 'STRICT_TRANS_TABLES';
NO_ENGINE_SUBSTITUTION
修改完之后 重行进入服务端即可
数据类型：
整形：
分类：
TINYINT 1bytes 范围:有符号(-128,127) 无符号(0,255) 小整数值
SAMLLINT 2bytes 范围:有符号(-322768,32767) 无符号(0,65535) 大整数值
MEDUIMINT 3bytes 范围:有符号(-8388608,8388607) 无符号(0,16777215) 大整数值
INT 4bytes 范围:有符号(-2147483648,2147483647) 无符号(0,4294967295) 大整数值
BINGINT 8bytes 大整数值
作用：
存储年龄、等级、id、号码等等
以TINYINT
是否有符号
默认情况下是带符号的
超出会如何
超出限制只存最大可接受值
约束条件之unsigned()
create table t1(id tinyint unsigned)
整形默认情况下都是带符号的
针对整形 括号内的宽度到底是干嘛的
create table t1(id int(8));
insert into t1 values(123456789);
特例：只有整形括号里面的数字不是表示限制位数而是显示长度
id int(8)
如果数字没有超出8位 那么默认用空格填充至8位
如果数字超出8位 那么有几位就存几位(但是还是要遵循最大范围)
create table t1(id int(8) unsigned zerodill) 用0填充至8位
总结：
针对整形字段 括号内无需指定宽度 因为它默认的宽度已经足够显示所有的数据了
浮点型：
分类：
FLOAT、DOUBLE、DECIMAL
作用：
身高、体重、薪资
储存限制：
float(255,30) 总共255位 小数部分占30位
double(255,30) 总共255位 小数部分占30位
decimal(65,30) 总共65位 小数部分占30位
只要是浮点型 括号后面有两个数字
第一个数字表示的是总位数
第二个数字表示的是小数位
扩展：其实在实际生产中 很多看似需要用整形或者浮点存储的数据内部可能都是用的字符类型存储
精确度验证：
float < double < decimal
字符类型：
分类：
char：
定长
char(4)超过四个字符直接报错 不够四个字符空格补全
char硬盘上存的绝对是真正的数据 带有空格的
但是在显示的时候MySQL会自动将多余的空格剔除
修改sql_mode 让MySQL不要做自动剔除操作
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
缺点：浪费空间
优点：存取都很简单
直接按照固定的字符存取数据即可
varchar：
变长
varchar(4)超过四个字符直接报错 不够有几个存几个
优点：节省空间
缺点：存取较为麻烦
存的时候需要制作报头
取得时候也需要先读报头 之后才能读取真实数据
日期类型：
data：年月日
datatime：年月日时分秒
time：时分秒
year：年
枚举与集合类型：
分类；
枚举(enum) 多选一
枚举字段 后期在存储数据的时候只能从枚举里面选择一个存储
集合(set) 多选多
集合可以只写一个 但是不能写没有列举的
default默认值：
create table t1(
id int,
name char(16),
gender enum('male','female','other') default 'male'
);
unique唯一：
单列唯一：
一个字段唯一
create table t1(
id int unique,
name char(16),
);
联合唯一：
单个可以重复 但是加在一起必须是唯一的 组合字段唯一
create table t1(
id int,
ip char(16),
port int,
unique(ip,port)
);
primary key主键：
1.单单从约束效果上来看primary key等价于not null + unique 非空且唯一
create table t1(id int primary key);
2.它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据
Innodb存储引擎在创建的时候必须要有primary key因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据
*一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
**如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段为主键，隐藏意味着你无法使用到它 就无法提升查询速度
***一张表中通常都应该有一个主键字段 并且通常将id(标识编号)字段作为主键
联合主键：
多个字段联合起来作为表的主键 本质还是一个主键
primary key(id,sid)
auto_increment自增：
当编号特别多的时候 人为的去维护太麻烦
create table t1(
id int primary key auto_increment,
name char(16)
);
注意auto_increment 通常都是加在主键上 不能给普通字段
delete from 在删除表中数据的时候 主键的自增不会停止
truncate t1 清空表数据并重置主键
结论：
以后在创建表id(数据的唯一标识)字段的时候
id int primary key auto_increment,
表与表之间建关系(约束)：
外键：
外键就是用来帮助我们建立表与表之间关系的
foreign key
表关系：
表与表之间最多只有四种关系
一对多关系
多对多关系
一对一关系
没有关系
判断表与表之间关系的时候 分别站在两张表的角度考虑
一对多关系：
1.一对多表关系 关键字建在多的一方
2.在创建表的时候 一定要先建被关联表
3.在录入数据的时候 也必须先录入被关联表
SQL语句实现：
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','others'),
dep_id int,
foregin key(dep_id) references dep(id)
);
更改关联数据：
1.先删除教学部对应的员工数据 之后再删除部门
操作太过繁琐
2.真正做到数据之间有关系
更新就同步更新
删除就同步删除
级联更新
级联删除
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','others'),
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade 同步更新
on delete cascade 同步删除
);
多对多关系：
1.针对多对多字段表关系 不能在原有的表中创建外键
需要单独再创建一张表 专门存储两者之间的关系
SQL语句实现：
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int,
foreign key(author_id) references author(id)
on update cascade 同步更新
on delete cascade 同步删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
book_id int,
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references book(id)
on update cascade 同步更新
on delete cascade, 同步删除
foreign key(book_id) references book(id)
on update cascade 同步更新
on delete cascade 同步删除
一对多关系：
一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
book_id int unique,
foreign key(book_id) references book(id)
总结：
表关系的建立需要用到foreign key
一对多
外键字段建在多的一方
多对多
自己开设第三方存储
一对一
建在任意一方都可以 但是推荐你在查询频率较高的表中
sql语句：
MySQL对大小写是不敏感的
1.修改表名
alter table 表名 rename 新表名
2.增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first; 添加在最前面
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名; 添加在最xxx字段后面
3.删除字段
alter table 表名 drop 字段名;
4.修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
复制表：
sql语句查询的结果其实也是一张虚拟表
create table 新表名 select * from 旧表名; 不能复制主键 外键 索引...
create table 新表名 select * from 旧表名 where xxx
where约束条件：
作用：是对整体数据的一个筛选操作
1.查询id大于等于3小于等于6的数据
select * from emp where id>=3 and id <= 6;
select * from emp where id between 3 and 6;
2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);
3.查询员工姓名中包含o的员工姓名和薪资
模糊查询
like
% 匹配任意多个字符
_ 匹配任意单个字符
select * from emp where name like '%o%';
4.查询员工姓名是由四个字符组成的 姓名和薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
5.查询id小于3或id大于6的数据
select * from emp where id not between 3 and 6;
6.查询薪资不在20000或者18000或者17000的数据
select * from emp where salary!=20000 or salary!=18000 or salary!=17000;
select * from emp where salary not in (20000,18000,17000);
7.查询岗位描述为空的员工姓名和岗位名
select name,post from emp where post_comment is NULL;
group by分组：
1.按照部门分组
select * from emp group by post;
分组之后 最小可操作单位应该是组 而不再是组内的单个数据
上述命令在你没有设置严格模式的时候是可以正常执行的 返回的是分组之后 每个组的第一条数据 但是不符合分组的规范：分组之后不应该考虑单个数据 而应该以组为操作单位（分组之后 没办法直接获取组内单个数据）
如果设置了严格模式 那么上述命令会直接报错
set global sel_mode = 'strict_trans_tables,only_full_group_by';
设置严格模式之后 分组 默认只能拿到分组的依据
select post from emp group by post;
按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助一些方法（聚合函数）
聚合函数：
max 最大值
min 最小值
sum 总值
count 计数
avg 平均值
什么时候需要分组：
关键字 每个 平均 最高 最低
1.获取每一个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as ‘最高薪资’ from emp group by post;
as 可以给字段起别名 也可以直接省略不写 但是不推荐 因为 省略的话语意不明确 容易错乱
补充：as语法不单单可以给字段起别名 还可以给表起别名
select t1.id,t1.name from emp as t1;
2.获取每一个部门的最低薪资
select post,min(salary) from emp group by post;
3.获取每一个部门的平均薪资
select post,avg(salary) from emp group by post;
4.获取每一个部门的工资总和
select post,sum(salary) from emp group by post;
5.获取每个部门的人数
select post,count(id) from emp group by post;
6.查询分组之后的部门名称和每个部门下所有的员工姓名
select post,group_concat(name) from emp group by post；
group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
concat不分组的时候用
select concat('NAME:',name),concat('SAL:',salary) from emp;
7.查询每个人的年薪
select name,salary*12 from emp;
分组注意事项：
关键字where和group by 同时出现单单时候group by 必须在where的后面
where先丢整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
聚合函数只能在分组之后使用
select id,name,age from emp where max(salary) > 3000;
select max(salary) from emp; 不分组 默认就是一组
统计各部门年龄在30岁以上的员工平均薪资
select post,avg(salary) from emp where age >30 group by post;
having分组之后的筛选条件：
having的语法跟where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
1.统计各部门年龄在30岁以上的员工工资并保留平均薪资大于10000的部门
select post,avg(salary) from emp where age >30 group by post having avg(salary)>1000;
distinct去重：
一定要注意 必须是完全一样的数据才可以去重
一定不要将主键忽视了 有主键存在的情况下 是不可能去重的
select distinct id,age from emp;
order by排序：
select * from emp order by salary;
order by 默认是升序 asc 该asc可以省略不写也可以修改为降序 desc
select * from emp order by salary asc;
select * from emp order by salary desc;
select * from emp order by age desc,salary asc;
先按照age降序排 如果碰到age相同 则在按照salary升序排
统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于1000的部门，然后对平均工资降序排序
select post,avg(salary) from emp
where age >30
group by post
having avg(salary)>1000
order by avg(salary) desc
;
concat_ws: 如果多个字段之间的连接符号是相同的情况下 你可以直接使用concat_ws来完成
select concat_ws(':',name,age,gender) from emp;
limit限制展示条数：
针对数据过多的情况 我们通常分页处理
select * from emp limit 3; 只展示三条数据
select * from emp limit 3; 只展示三条数据
select * from emp limit 0,5;从第一拿到第五条
第一个参数是起始位置
第二个参数是展示条数
正则：
select * from emp where name regexp '^j.*(n|y)$';
多表查询；
select * from dep,emp; 结果交笛卡尔积
select * from dep,emp where dep.id = emp.id; 结果交笛卡尔积
inner join 内连接
只拼接两张表中共有的数据部分
left join 左连接
左表所有的数据都展示出来 没有对应的项就用NULL
right join 右连接
右表所有的数据都展示出来 没有对应的项就用NULL
union 全连接
左右两表表的数据都展示出来 没有对应的项就用NULL
select * from emp inner join dep on emp.dep_id = dep.id
子查询
分步骤解决问题
讲一个查询语句的结果当做另外一个查询语句的条件去用
查询部门是计数或者人力资源的员工信息
1.先获取部门的id号
2.在去员工表里面筛选出对应的员工
总结
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一张虚拟表跟其他表关联
关键字exists():
只返回布尔值 True False
返回True的时候外层查询语句执行
返回False的时候外层查询语句不再执行
select * from emp where exists(select id from dep where id > 3)
事务；
开启一个事务可以包含多条sql语句 这些sql语句要么同时成功 要么一个都别想成功
称之为事务的原子性
事务的作用：
保证了对数据操作的安全性
在操作多条数据的时候可能会出现某几条操作不成功的情况
事务的四大特性：
ACID
A 原子性
一个事务是一个不可分割的单位，事务中包含的诸多操作要么同时成功要么同时失败
C 一致性
事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态
一致性跟原子性密切相关的
I 隔离性
一个事务的执行不能被其他事务干扰。
即一个事务内部的操作及使用到的数据对并发的其他是隔离的，并发执行的事务之间也是互不干扰的
D 持久性
也叫永久性
一个事务一旦提交成功执行成功 那么它对数据库中数据的修改应该是永久的
接下来的其他操作或者故障不应该对其有任何影响
如何使用事务
事务相关的关键字
1.开启事务
start transaction;
2.回滚(回到事务执行之前的状态)
rollback
3.确认(确认之后就无法回滚了)
commit
1.先开启事务
2.多条sql语句
3.确认
视图
视图就是通过查询得到的一张虚拟表，然后保存下来，下次可以直接使用
如果要频繁的操作一张虚拟表 你就可以制作成视图 后续直接操作
create view
注意：
1.创建视图在硬盘上只会有表结构 没有表数据（数据还是来自之前的表）
2.视图一般只用来查询 里面的数据不要继续修改 可能会影响真正的表
触发器：
在满足对表数据进行增、删、改的情况下，自动触发的功能
使用触发器可以帮组我们实现监控、日志...
触发器可以在六种情况下自动触发 增前 增后 删前 删后 改前 改后
基本语法结构：
create trigger 触发器名字 before/after insert/update/delete on 表名
for each row
begin
sql语句
end
具体使用 针对触发器的名字 我们通常需要做到见名知意
ps：修改MySQL默认的语句结束符 只作用于当前窗口
delimiter $$ 将默认的结束符号由;改为$$
NEW指代的就是一条条数据对象
create trigger 触发器名字 after insert on 表名：
for each row
begin
if NEW.xxx = 'xxx' then
insert into 表名(xxx)
values(xxx）;
end if;
end$$
浙公网安备 33010602011771号