【MySql笔记】总览

1.数据类型

1.1 整形

1.2 浮点型和定点型

1.3 日期时间类型

1.4 字符型

2.运算符

运算符类别	运算符	作用
算术运算符	+，-，*，/，%	加，减，乘，除，取余
比较运算符	>，<，>=，<= ，!=，<>	大于，小于，大于等于，小于等于，不等于，不等于
比较运算符	=	sql中=承担赋值和比较的作用，相当于c语言中=和==
比较运算符	is (not) null	判断字段是否为空需要借助is （not） null结构
比较运算符	between x and y	判断一个值是否在[x,y] 中时可以借助此结构
比较运算符	（not）in（a，b…）	判断值是否为集合中的一员
比较运算符	like	通配符匹配，支持（通配符）%和（占位符）_
逻辑运算符	and，or，not	与，或，非

3.基本命令

显示数据

show databases;

创建数据库

create database if not exists 数据库名;

使用数据库

use 数据库名;

查询当前数据库信息

select database();//查询当前使用数据库，返回数据库名
select version();//查询当前数据库版本
select now();//查询当前时间
select user();//查询当前用户

删除数据库

drop database if exists 数据库名;

显示数据库中的表

show tables;

导入数据库脚本（xxx.sql）

source 脚本路径 //不加;如果脚本文件中不包含创建和使用数据库语句时应该先创建并使用数据库然后再执行

查看表结构

desc 表名;

查看数据库/表的创建语句（优化器优化后的）

show create database 数据库名;
show create table 表名;

4.DDL（数据定义语言）

4.1 创建表

create table if not exists 表名(
字段名 字段类型 [列约束条件]，
字段名 字段类型 [列约束条件]，
...
[表约束条件]
);

4.2 修改表

添加列

alter table 表名
add column 列名 类型 [列约束条件] [first||after 列名];

修改列属性

alter table 表名
modify column 列名 类型 [列约束条件] [first||after 列名];

修改字段名

alter table 表名
change column 旧列名 新列名 类型;

删除字段

alter table 表名
drop column 列名;

4.3 表的重命名

alter table 表名
rename to 新表名;

4.4 删除表

drop table if exists 表名1[,表名2...];

5.DML（数据操作语言）

添加数据

insert into 表名(字段1，...，字段n)
values(
字段1数据，...，字段n数据
),
...(
字段1数据，...，字段n数据
);

删除数据

delete from 表名 where 限制条件;

修改数据

update 表名 set
字段1=数据1，
...
where 限制条件;

查询数据

select [*||(字段1，...，字段n)] from 表名  where 限制条件;

6.约束

6.1 非空约束

创建时加入

create table if not exists 表名(
字段名 字段类型 not null，
...
字段名 字段类型 not null
);

创建后加入

alter table 表名
modify column 列名 类型 not null;

删除约束

alter table 表名
modify column 列名 类型;

6.2 默认值约束

设置默认值约束后如果数据输入可以非空可以不插入对应数据，否则传值需要对应传入空

create table if not exists student(
no int,
name varchar(20) default '张三'
);
insert into student values(20);
select*from student;//无报错，查询结果为：20 张三

当一个表中多次使用timestump时必须要进行默认值约束使初始值不同

create table class(
no int,
c_start TIMESTAMP NOT NULL COMMENT '课程开始时间',
c_end  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '课程结束时间'
);

6.3 唯一约束

唯一约束的作用是保证某一列或某一些列值的唯一，但当某一列运行空值时空值允许重复出现，唯一约束只能约束非空的数据

列级唯一约束（无论几个unique约束始终唯一，约束会进行整合中间用and连接）

create table if not exists student(
no int unique,
name varchar(20) unique
sex varchar(10);
);
insert into student values(20,'张三','男');//运行成功
insert into student values(21,'张三','男');//运行成功
insert into student values(21,'张三','男');//运行失败

表级唯一约束(可以为约束起名建立多列唯一约束)

create table if not exists 表名(
字段名 字段类型,
...
字段名 字段类型,
[constraint 约束名]unique (字段1[,字段2，...，字段n])
);

添加约束

alter table 表名
modify column 列名 类型 unique;

alter table 表名
add [constraint 约束名]unique (字段名1[,字段名2，...，字段名n]);

删除约束

alter table 表名
drop index 约束名;

alter table 表名
drop key 约束名;

6.4 主键约束

1.主键不可为空
2.主键值唯一
3.主键在表内具有唯一性
4.主键的选择应与业务无关
5.主键不应该修改
6.每个表都应该有主键

主键的设置

create table if not exists 表名(
字段名 字段类型 primary key,
...
字段名 字段类型
);

create table if not exists 表名(
字段名 字段类型,
...
字段名 字段类型,
[constraint 约束名]primary key (字段1[,字段2，...，字段n])
);

删除主键

alter table 表名
drop primary key;

6.5 自动递增

create table if not exists 表名(
字段名 字段类型 auto_increment,
...
字段名 字段类型
);

6.6 外键约束

外键为表内某一字段它为别的表的主键用以连接两个表，一个表内可以存在多个外键
1.外键可以为空，这样的外键又称孤儿外键
2.有了外键引用后表就可以分为父表和子表
创建表时先创建父表再创建子表
插入数据时先插入父表数据再插入子表数据
删除数据时先删子表再删父表
3.子表外键类型应与父表外键类型一致

[constraint 外键名]foreign key （列名） references  主表名（主键）;
//实例
create table if not exists student(
no int primary key,
name varchar(20)
);

create table if not exists score(
no int primary key auto_increment,
student_no int,
name varchar(20),
sex varchar(10),
constraint f1 foreign key (student_no) references student(no)
);

对含外键的表进行操作时应首先对父表操作再对子表操作

7.高级查询

7.1 排序

//按单列排序
select * from 表名 order by 列名1;//按照 列名1 顺序 默认asc
select * from 表名 order by 列名1 asc;//按照 列名1 顺序
select * from 表名 order by 列名1 desc;//按照 列名1 逆序
//按多列排序
select * from 表名 order by 列名1, ... ,列名n;
/*排序方式为首先按照列名n排序（默认顺序逆序后边加desc），
然后列名n下值相同时按照列名n-1排序，同样默认顺序需要逆序的话在列名后加desc
...
持续进行直到排序完成
*/

7.2 分组

select * from 表名 group by 字段1[,字段2....,字段n];
//与order by类似但不同之处在于作用不同

注意点：
1.如果分组中含有null将被分到null组，即order by可以给null分组
2.group by 必须出现在where 后如果和order by连用需要放在order by前

筛选分组

对分组的筛选不能使用where而使用having 二者不同点：
1.having 过滤分组而where 过滤行
2.having 必须和order by一起用
3.having 是在分组完成后对结果进行的筛选

select * from 表名 group by 字段1[,字段2....,字段n] having 组筛选条件;

7.3 限制输出数据数量

select * from 表名 limit n;//限制输出前n行数据
select id from 表名 limit n,m;//限制从第n条数据开始输出后面的m条数据

7.4 去重

select distinct 字段1[,...字段n] from 表名;//输出字段内数据 不相同/不完全相同 的数据

7.5 组合

select 字段,[,...字段n] from 表名1
union //默认取字段 不相同/不完全相同 的数据，需要展示全部时后边加上all
select 字段,[,...字段n] from 表名2

注意点
1.组合内数据条数应一致
2.组合优先级比函数高，组合相当于将数据分别查询后放进一个表再输出，而函数相当于对输出结果进行处理，因而组合不可与函数共用
3.组合选择列应当具有相似的数据类型

8.函数

8.1 数值函数

8.2 字符函数

8.3 日期时间函数

8.4 聚合函数（分组函数）

select avg(列名) from 表名;
select sum(列名) from 表名;
select count(列名) from 表名;
select max(列名) from 表名;
select min(列名) from 表名;

8.5 其它函数

当null参与函数运算时运算结果会出现null，因为不同于c语言sql中null不为0，ifnull则是专门识别这些null数据并进行处理的函数

ifnull(字段名，value)//将为空的字段数据理解为value，不是修改是替换使用

select 列名1+ifnull(列名2,value) from 表名;

连接查询

查询的过程是 进待查询表—>执行限制条件—>查询结果—>检索输出内容—>输出
证明案例

create table student(//某个班级要进行两两分组
id int,
partner_id int,
name varchar(20)
);
//现在如何输出分组情况呢？
select s1.name,s2.name
from student s1
join student s2
where s1.partner_id=s2.id;

从上述案例可以看出如果先指定查询内容的情况下程序会出错，但运行结果正常，因而可以证明执行顺序是 进待查询表—>执行限制条件—>查询结果—>检索输出内容—>输出

内连接

select * from 表名1,表名2 where 表名1.字段1=表名2.字段2;
select * from 表名1 join 表名2 on 表名1.字段1 = 表名2.字段2;
select * from 表名1  where 字段1 in (select 字段2 from 表名2 [where xxx]);
//将查询结果作为临时表进行处理，表名和字段名均需要临时定义
select *,字段替换名 from 表名1,(select 字段2 字段替换名 from 表名2 [where xxx])as 表替换名
where 表名1.字段1 = 表替换名.字段替换名;//以=为例
//join和多表查询相比更加灵活可以进行自表查询
select 别名1.字段1，别名2.字段2 from 表名1 别名1 
join 表名1 别名2 on
别名1.字段1=别名2.字段2;

外连接

当完全以表内字段数据进行的表间连接称之为内连接
那么外连接就是不完全依赖于字段间数据进行的连接
比如我们要获取所有图书的借阅情况时我们需要获取的数据包括借出去的和没借出去的那么我们需要怎么进行查询呢？

select from 表名1
left|right join 表名2
where xxx;

left join 表示以left join加入的表为准将数据插入，有关联的插入数据没有关联的对应的数据为空
right join 表示以right join前加入的表为准将数据插入，有关联的插入数据没有关联的对应的数据为空

正则表达式

sql中正则表达式的作用是匹配文本，按照一个用户规定的模式进行比较，正则表达式通过 操作符 regexp 进行处理。
like 和 regexp区别
like匹配整个列，如果被匹配的文本仅在列值中出现（没有配合其它通配符），like将不会找到它。
regexp在列值内进行匹配，如果被匹配的文本在列值中出现，regexp将会找到它，相应的行将被返回。

正则表达式使用过程常见问题
1.字符串含转义字符匹配问题

存储过程

定义

create procedure 存储过程名()
begin
	----语句；
end;

执行

call 存储过程名();

删除

drop procedure [if exists] 存储过程名;

参数类型

1.in（默认）：输入参数，存储过程的输入值，从外部传递给存储过程，存储过程内部是只读不可修改值
2.out ：输出参数存过程的返回值，存储过程可以修改它的值并将其返回
3.inout ：输入和输出参数即可以作为输入值传递给存储过程，也可以由春初过程修改并返回

create procedure 存储过程名(

[in|out|inout] 参数名1 参数类型1,
....
[in|out|inout] 参数名n 参数类型n
)
begin
	----语句；
end;

变量的使用

//定义变量
declare @变量名 变量类型 [default 初始值];

//修改值
set a=a+1;

逻辑语句

if 条件1 then 逻辑代码1, .... ,逻辑代码n;
....
else if 条件n then 逻辑代码1, .... ,逻辑代码n;
else 逻辑代码1, .... ,逻辑代码n;
end if;

case 
when 条件1 then 逻辑代码1, .... ,逻辑代码n;
.....
when 条件n then 逻辑代码1, .... ,逻辑代码n;
else 逻辑代码1, .... ,逻辑代码n;
end case;

循环语句

while 条件 do //while循环
逻辑代码1;
 .... 
逻辑代码n;
end while;

repeat//do...while 循环
逻辑代码1;
 .... 
逻辑代码n;
until 条件 end repeat;

for 变量 in 范围1 to 范围2 do//for循环
逻辑代码1;
 .... 
逻辑代码n;
end for;

特点

优点：
1.代码复用：存储过程可以被多个应用程序或脚本调用，首先代码的复用
2.提高性能：MySQL将编译后的存储过程放在缓存中，入股偶应用程序在单个连接中多次使用存储过程直接使用编译版本
3.减少网络流量：存储过程可以一次执行多条sql语句，减少了与数据库交互的次数
4.安全控制：存储过程可以对数据库中的数据进行严格的访问控制个权限管理
5.数据一致性：存储过程可以实现复杂的数据操作和事务处理，确保数据的一致性和完全性
缺点
1.创建和维护成本高：sql是一种结构化查询语言，难以处理复杂的业务逻辑
2.开发调试复杂：需要特定的工具和技术进行，不方便调试
3.可以移植性差：存储过程通常依赖于特定的数据库平台和版本，不同的数据库系统间存储过程的语法和特性可能有差异，导致存储过程的可以移植性差

自定义函数

创建

create function 函数名(参数1 参数类型1,
....
参数n 参数类型n
)
returns 类型
begin
函数语句1;
....
函数语句n;
return 返回值;
end;

删除

drop function if exists 函数名;

调用

call 函数名(参数1[, ... ,参数n]);

与存储过程相比差异
1.参数结构不同
2.返回值方式不同
3.存储过程可以执行修改表等操作但自定义函数不能执行一组修改全局数据库状态的操作
4.存储过程可以使用非确定性函数，自定义函数不允许在用户定义函数主体中内置非确定函数

游标

//定义游标
declare 游标名 cursor
for 查询语句;
//打开游标
open 游标名;
//读取游标
fetch 游标名 into 变量1[, ... ,变量n];
//关闭游标
close 游标名;

触发器

drop trigger if exists 触发器名;
create trigger 触发器名 after|before delete|insert|update on 表名
for each row
begin
//执行语句
end;

索引

//创建索引
create index 索引名 on 表名(列名)
create index 索引名 on 表名(列名(前缀长度))
//查看索引
show index from 表名
//删除索引
drop index 索引名 on 表名
//查看是否使用索引
explain 查询语句
desc 查询语句

视图

//创建视图
create view 视图名 as 查询语句
//修改视图
alter view 视图名 as 查询语句
//删除视图
drop view if exists 视图名
//查看创建视图语句
show create view 视图名

三范式

1NF：保证每列原子性，即字段不可再分割
2NF：保证唯一性和依赖性，即表中均有主键且其它字段完全依赖于主键，其中完全依赖是指字段依赖于联合主键中每一个字段

不满足实例

学生编号	课程编号	学生姓名
primary key	primary key	not null

3NF：非主键必须依赖于主键不能存在传递依赖

不满足实例

学生编号	学生名	班级编号	班级名
primary key	not null	foreign key	not null

末尾---->>有问题敬请指出