Mysql基础知识（完整篇）

安装与使用

官网网站：https://dev.mysql.com/downloads/mysql

安装：

步骤2：找到要安装的目录，进行安装。安装具体步骤在我的博客里的笔记找到

我的博客：http://goodysr.cn

Windows 启动mysql服务：

停止：net stop  mysql的服务名
启动：net start mysql的服务名

查看mysql的版本：

mysql --version //
mysql version() //在mysql服务端执行

进入Mysql:

mysql 【-h主机名 -P端口号 】-u root -p
退出： exit或ctrl+C

规范：

不区分大小写，（关键字大写）

注释：# /* */

分为：

• DQL语言：查询相关的
• DML语言：增删改
• DDL语言：库和表的定义
• TCL语言：事务

导入数据库

source d://xxx/xxx.sql (路径文件)

DQL语句

基础查询：

select 列表 from 表;
# 去重
select distinct 字段1 from 表;
# 如果某个字段为null,赋值默认值0
select ifnull(字段名,0) as 别名 from 表;

条件查询:

条件运算符：> < = <> (不等) >= <= <=>

逻辑运算符：and or not

模糊查询：like between and in is null

# 模糊 %：代表任何 _：代表一个不确定的 
# 查询带_的数据，$ 相当于转义字符，跟后面的 ESCAPE 保存一致，需要转的是关键字_,模糊查询带_的字符
select * from 表 where 字段 like '_$_%' ESCAPE '$';
SELECT * from 表 where CONCAT(CONCAT(',',字段),',') like '%,模糊字段,%';
# 范围查询 包含临界值 
select * from where 字段 between 100 and 200;
select * from where 字段 not between 100 and 200;
# 正则
select 字段 REGEXP '^表达式' from dual;

排序查询：

select * from 表 order by 字段（asc 升序,desc 降序）
# order by  后面可以支持别名/函数 

分页查询：

limit 分页（offset,size）要显示条目的起始索引 ，要显示条目个数

公式：（page - 1）* size , size

# 查询最低平均工资的部门编号
select id 部门id from emp 
group by 部门id
order by Avg(salary)
limit 1;
# 8版本的新特性：limit 32 offset 2; (与之前的写法 limit 2,32)

连接查询：

语法：

select * from 表1 join 表2 on 连接条件 where 条件

内连接

关键字 ：inner

外连接

关键字 ：left outer / right outer / full

外连接的查询结果为主表中的所有记录

如果从表中有和它匹配的，显示匹配的值

如果从表中没有和它匹配的，显示null

外连接查询结果=内连接查询结果 + 主表中有而从表没有的记录

left 左外连接 left join 左边是主表

right 右外连接 right join 右边是主表

总结:

注意：连接条件 on 后面可以是范围

select * from A a inner join B b on a.id  Between 值1 and  值2;

交叉连接

关键字 ：cross (表1 和 表2 的乘积)

子查询：

出现在其他语句内部的select语句，称为子查询或内查询

多行子查询

in / not in 等于列表中的任意一个

any / some 和子查询返回的某一个值比较 相当于 max(值)

all 和子查询返回的所有值比较 相当于 min(值)

# 返回其他部门中比job_id 为 it 部门所有工资都低的员工
select name,id,job_id,salary
from emp where salary < all (
	select distinct salary from emp where job_id = 'it'
) and job_id <> 'it'

# 或者
select name,id,job_id,salary from emp
where salary < (
	select Min(salary) from emp where job_id = 'it'
) and job_id <> 'it'

# 行子查询写法：
select * from emp
where (id,salary) = (
	select Min(id),Max(salary) from emp
)
# 查询每个部门的员工个数
select d.* ,(
	select Count(*) from emp e
    where e.id = d.id
) 个数
from departments d;

联合查询：

union 联合 ：将多条查询语句的结果合并成一个结果

语法：查询语句1 union 查询语句2 union 查询语句2…

特点：

1. 要求多条查询语句的查询列数一致

2. 要求多条查询语句的查询每一列的类型顺序一致

3. union关键字默认去重，如果不去重使用union all

# 查询中国用户男性信息以及外国用户男性信息
select id,name,csex from t_ca where csex = '男'
union
select t_id,t_name,t_csex from t_ua where t_csex = '男'

常见函数：

字符函数

concat 拼接     concat("abc","sf","sdf")
substr 截取子串
upper  转换成大写
lower  转换成小写
trim   去前后指定的空格和字符
ltrim  去左边空格
rtrim  去右边空格
replace替换
lpad   左填充
rpad   右填充
instr  返回子串第一次出现的索引
length 获取字节个数

数学函数

round 四舍五入
rand  随机数
floor 向下取整
ceil  向上取整
mod   取余 计算：mod(a,b)  a-a/b*b
truncate 截断
abs(x)  绝对值
sign(x) 正数返回1，负数返回-1
least    返回列最小值  least(2,3,5)
greatest 返回列最大值  greatest(2,3,5)

日期函数

now     当前系统日期+时间
curdate 当前系统日期  (年月日)
curtime 当前系统时间  (时分秒)
str_to_date 将字符转换成日期
date_format 将日期转换成字符
TIME_TO_SEC 将时间转换为秒数
SEC_TO_TIME 将秒数转换为时间
DATEDIFF 获取两个日期的间隔（参数1-参数2）返回天数
TIMEDIFF 获取两个时间的间隔（参数1-参数2）返回时间间隔

# 返回具体日期（年）
select year(now());
select curdate();
select curtime();
# 字符转换成日期
select str_to_date('2020-3-2','%Y-%c-%d')
# 日期转字符串
select date_format(now(),'%Y-%c-%d')
日期： 
%Y 年       %y 2位数的年
%m 月 01    %c 月 1     %d 日
%H 时 24制  %h 时 12制
%i 分       %s 秒
# 时间转换为秒 （只考虑时分秒部分）
SELECT TIME_TO_SEC(CURRENT_TIME) from DUAL;
# 时间计算，当前时间减去 2年
SELECT now(),DATE_ADD(now(),INTERVAL -2 YEAR) from DUAL;
# 返回date所在月的最后一天  (年月日)
LAST_DAY(now())
# 返回time增加n后的时间
PERIOD_ADD(time,n)

流程函数

# 如果某个字段为null,赋值默认值0
ifnull(字段名,0)
# 如果value为true ，该值是value1，否则是value2
if(字符>500,'高','低')
# 相当于java的if else
case when 条件1 then 结果1
	 when 条件2 then 结果2
	 when 条件3 then 结果3
	 else ‘其他结果’ end  
# 相当于java的switch...case
case expr when 常量1 then 结果1
	 	  when 常量2 then 结果2
	      when 常量3 then 结果3	 
	      else ‘其他结果’ end  

信息函数

# mysql的版本号
VERSION()
# 当前mysql的连接数
CONNECTION_ID()
# 当前所在的数据库
DATABASE()
# 返回当前用户名
USER()
# 返回当前字符集
CHARSET("abc")
# 返回当前字符集比较规则
COLLATION("abc")
# 将一个字符串进行字符集转换
CONVERT("abc" USING 'gbk')
# 转换指定字符集
CONVERT(expr USING transcoding_name)
SELECT CONVERT('2022-05-25', DATE);
SELECT CONVERT('9.5', DECIMAL(10,2));
# DECIMAL(数值精度，小数点保留长度)
# DECIMAL(10,2)可以存储最多具有8位整数和2位小数的数字

分组函数

sum 求和 avg 平均值 max 最大值 min 最小值 count 计算个数

注意：会忽略null值

# 查询员工表 最大入职时间和最小时间的相差天数
select Datediff(Max(字段1)，Min(字段1)) as diffrence from 表

Datediff() 时间方法，得到两个时间的相差天
# 查询最大值
select Max(distinct 字段) from 表

分组 group by

注意：group by 可以多个字段进行分组，筛选条件使用having

select count(*),字段2 from 表
where 字段 = 值1
group by 字段2  
having count(*) > 10;

使用WITH ROLLUP

查询出分组记录后增加一行记录，记录查询出所有记录的总和

select AVG(字段2),id from 表
where 字段 = 值1
group by 字段2 WITH ROLLUP

常见案例：

# 查询生日在1988-1-1后的学生姓名
select studentname from student s join major m on s.mid = m.mid
where Datediff(bdate,'1988-1-1') > 0

# 查询每个专业的男生人数和女生人数
select majorid,
(select count(*) from student where sex='男' and majorid=s.mid ) 男,
(select count(*) from student where sex='女' and majorid=s.mid ) 女
from student s
group by majorid

DML语句

插入语句：

方式一 ：insert into 表 (列名,…) values (值1,…)

方式二 ：insert into 表 set 列名=值,列名=值,列名=值…

# 方式一 支持插入多行,方式二不可以
insert into 表 (列名,..) values (值1,...),(值1,...),(值1,...);

# 方式二 支持子查询,方式二不可以
insert into 表 (列名,..) 
select id,name,phone from boy where id < 3;

计算列

mysql8的新特性

create table 表名(
	a字段 int,
    b字段 int,
    c字段 int GENERATED ALWAYS AS (a字段 + b字段) VIRTUAL, --该字段（自动计算a + b后的值，赋给c）
);
insert into 表 (a字段,b字段) values (10,3);
-- c 的结果自动是13

修改语句：

修改单表：update 表 set 列名=值, 列名=值, 列名=值… where 条件

修改多表：update 表 别名 inner/left… join 表2 别名 on 连接条件 set 列名=值… where 条件

# 修改表中某个字段，该字段添加前缀
UPDATE 表
set 字段1 = REPLACE(字段1 ,"/前缀a/","/xxx/前缀a/")
# 依据其他表，修改某个字段
update 
	表1 a join 表2 on a.关键字段= b.字
set a.列名 = b.列名;

删除语句：

删除单表：delete from 表 where 条件

删除多表：delete from 表 别名 inner/left… join 表2 别名 on 连接条件 where 条件

truncate ：删除，效率高，不能回滚。

语法：truncate table 表名

查看表的结构：

describe 表名 或者 desc 表名

DDL语句

库的管理:

# 库的创建：
create database if not exists 库名;
create database if not exists 库名 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

# 库的修改：更改库的字符集：
alter database 库名 character set utf-8;

# 库的删除：
drop database if exists  库名;

# 查询数据库
show databases

# 设置字符集
set names gdk; 

# 切换数据库
use 数据库;

表的管理:

# 表的创建：
create table 表名(
	列名 列的类型 【(长度) 约束】,
	列名 列的类型 【(长度) 约束】,
	列名 列的类型 【(长度) 约束】...
)
create table if not exists 表名(
	id int,
	name vachar(200)
)
# 案例：
CREATE TABLE `emp` (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(255)  DEFAULT NULL COMMENT '员工名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `did` int(11) DEFAULT NULL COMMENT '部门id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部门表';

-- 1=2 代表只查询表结构，不进行导入数据
create table 表名 as (select * from 表 where 1=2)

# 表的修改：
修改列名：
alter table 表名 change column 列名 新列名 数据格式;
修改列的类型或约束：
alter table 表名 modify column 列名 数据格式 约束;
添加新列：
alter table 表名 add column 列名 数据格式;
添加外键
alter table 表名 add constraint fc_表1_表2 foreign key(字段) references 外键表(外键表主键);
删除列：
alter table 表名 drop column 列名;
删除非空约束：
alter table 表名 modify column 列名 数据格式 null;
删除主键：
alter table 表名 drop primary key;
删除唯一键：
show index from 表; 找到名字
alter table 表名 drop index 名字;
删除外键约束：
alter table 表名 drop foreign key 名字;
修改表名：
alter table 表名 rename to 表;
查看表信息：
desc 表名

# 删除表
drop table if exists 表;

# 清空表
truncate table 表

# 表的复制
复制表的结构
crrate table 新表名 like 要复制的表; 
复制表结构+数据
crrate table 新表名 select * from 表 where (筛选条件); 

# 通用写法
drop database if exists 旧库名;
create database 库名;

drop table if exists 旧表名;
create table 表名(...)

# 查看表结构 - 导出sql
SELECT
 TABLE_NAME 表名,
 COLUMN_NAME 字段名称,
 COLUMN_TYPE 字段类型,
 CHARACTER_MAXIMUM_LENGTH 字段长度,
 IS_NULLABLE 是否必填,
 COLUMN_DEFAULT 默认值,
 COLUMN_COMMENT 备注 
FROM
 INFORMATION_SCHEMA.COLUMNS
where
table_schema ='表名';

数据类型介绍：

数值型：
	整形
		Tinyint  (0-255)
		Smallint (0-65535)
		Mediumint(0-百万)
		Int/integer (0-4亿)
		bigint  (0-很大)
		注意：如果设置无符号，需要加unsigned关键字
		例子 create table 表名 ( 字段 int unsigned ...)
	小数 (长度，小数位)
		定点数 （精度高，如插入数值精度高如货币）
			dec(M,D)  
			decimal(M,D) 
		浮点数
			float(M,D) 
			double(M,D)  
字符型：
	较短的文本：
		char    固定长度，最大255
		vatchar 可变长度，最大65535
		binary  较短二进制
		varbinary 较长二进制
		enum  枚举
		字段 enum('a','b','c')
		set  类似枚举，可以存多个值，，分开
	较长的文本：
		text
		longtext
		blob (较长二进制数据)
日期型：
	date		只有日期
	datetime	日期格式
	timestamp	时间磋		字节少  受时区的影响
	time		只有时间
	year		只有年
空间数据类型：
	点、线、面
    POINT、LINESTRING、GEOMETRY...
例子：
# 在项目中 主键id 可以不设置自增id，可以设置：时间+去重字段+用户id
create table 表(
    id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
    name varchar(20) not null  COMMENT '名称',
    gender char(1) check(gender='男' or gender='女'),
    seet int unioue,    -- 唯一
    age int default 18, -- 默认
    majorid int,
    PRIMARY KEY (`id`)
    constraint fk_表1_表2 foreign key(majorid) references 外键表(外键表主键) #外键
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试表名';
# 案例2
CREATE TABLE `表` (  
    `ID` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',  
    `data_sources` int DEFAULT NULL COMMENT '来',  
    `STATE` int DEFAULT '0' COMMENT '状态（0未发送 1已成功 2已失败）',  
    `info` longtext COMMENT '信息',  
    `CREATE_USER` varchar(128) DEFAULT NULL COMMENT '创建人', 
    `CREATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`ID`),  KEY `t_sm_contacts_log_key` (`data_sources`,`CREATE_DATE`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='表';

约束

6大约束：
	not null  非空
	default   默认值
	primary key  主键
	unioue    唯一
	check  	  检查约束(mysql8支持)
			  gender char(1) check(gender='男' or gender='女'，该字段只接受男，女
	foreign key 外键
标识：
	auto_increment 自增长

外键约束：

# 策略1  
先把表2的外键字段 改为null,再删除表2;

# 策略2  级联操作
先删除之前的外键
alter table 表2 drop foreign key 外键名;
语法：
alter table 表2 add constraint 外键名 foreign key(字段) references 外键表(外键表主键) 
重新添加外键：并且删除，表1相关数据也删除，级联
alter table 表2 add constraint 外键名 foreign key(字段) references 外键表(外键表主键) 
on update cascade on delete cascade;
再执行 修改删除操作，出现如果删除表2 ，表1相关联的数据也会删除掉

# 策略3 置空操作
先删除之前的外键
alter table 表2 drop foreign key 外键名;
重新添加外键
alter table 表2 add constraint 外键名 foreign key(字段) references 外键表(外键表主键) 
on update set null on delete set null;
再执行 修改删除操作，出现如果删除表2 ，表1相关联的数据,关联字段会被设置为null

为什么不建议使用外键，因为并发低，不适合分布式

范式

第一份范式：

每张表每个字段必须是原子性，例子：地址，可拆分省、市、区

第二份范式：

每张表必须有主键，需要有唯一的标识

第三范式：

表中的非主键字段不能依赖其他非主键字段，

例子：表1：部门id，部门名称；表2 员工id，员工名称，部门id，部门名称（该字段是多余字段，冗余）

返范式化：

可以冗余，方便查询更快，违反第三范式；

产生的问题：

1.存储空间变大；2.一个表修改，其他表也需要修改，避免数据不一致；

巴斯范式：

只有一个候选键，每个候选键都是单属性；

例子：表：仓库、人、产品、数量 ； A仓库只有A人管理，B仓库只有B人管理

ER模型

实体关系模型，用来记录数据库的信息系统的设计阶段；工具有：PowerDesigner

TCL语句

事务

属性：(acid)

• 原子性 （要么全部发生，要么都不发生）
• 一致性（结果是一致的）
• 隔离性（一个事务的执行不能被其他事务干扰，并发执行各个事务之间不能互相干扰）
• 持久性（一个事务一但提交，就是永久性）

# 开启手动提交
set autocommit = 0;
# 开启事务
start transaction;
# 编写 增删改查语句 中间可设置回滚点 
savepoint 名字
# 结束语句
commit;	  # 提交
rollback; # 回滚 
rollback 名字 # 回滚到这个点

数据库的隔离级别：

• Read uncommitted 会出现 脏读 不可重复读 幻读
• Read committed 会出现 不可重复读 幻读
• Repeatable read 会出现 幻读
• Serializable

引发的问题

• 脏读 （T2修改，T1读了修改的数据，T2回滚了）
• 不可重复读 （T2修改，T1读了修改的数据，T2回滚了，T1读了回滚的数据，查询2次结果不同）
• 幻读（T1读了数据，T2新增数据，T1再读，数据多了）

避免-隔离级别

# 查看隔离级别
select @@tx_isolation 
# 设置隔离级别
set session|global transaction isolation level 隔离级别名;

视图

# 创建视图
create view 视图名 as 查询语句;

# 视图修改
方式一：
create or replace view 视图名 as 查询语句;
方式二：
alter view 视图名 as 查询语句;

# 删除视图：
drop view 视图名,视图名...;

# 查看视图：
desc 视图名;
show create view 视图名;

经典案例：

# 列出自己的掌门比自己年龄小的人员
SELECT e.*,d.*,a.`name` '掌门',a.age '掌门年龄' 
FROM emp e INNER join dept d ON e.did = d.id 
join emp a on d.ceo = a.id
WHERE e.age < a.age

# 列出所有年龄低于自己门派平均年龄的人员
SELECT a.*,b.avg 
from emp a INNER JOIN ( 
    SELECT e.did,AVG(e.age) avg 
    from emp e 
	WHERE did is not null GROUP BY e.did ) b  on a.did = b.did
WHERE a.age < b.avg
# 索引 did 、did-age

# 列出至少有2个年龄大于40岁的成员部门
explain 
SELECT a.*,COUNT(did) count 
from dept a straight_join emp e on a.id =e.did
WHERE e.age > 40 
GROUP BY e.did
having count >= 2
# 索引 e.did 
# straight_join 关键字可以指定谁是主表

# 至少有2位非掌门人员的门派
explain
SELECT *,COUNT(*) count 
from emp e LEFT JOIN dept d on e.id = d.ceo 
JOIN dept a on e.did = a.id
WHERE d.ceo is null 
GROUP BY e.did
having count >= 2

# 列出全部人员，是否是掌门
SELECT d.*,e.*,IF(d.ceo is NULL ,0,1) as '是否' 
from dept d RIGHT join  emp e on d.ceo =e.id 

# 显示每个部门年龄最大的人
explain 
SELECT a.* from emp a JOIN 
(SELECT e.did,MAX(e.age) max from emp e 
GROUP BY e.did ) w on a.did = w.did and a.age = w.max

# 显示每个部门年龄第二大的人
set @rank = 0;  #
set @last_did = 0; #下一个部门
set @last_age = 0; #年龄是否相等

SELECT a.* from (
SELECT t.* ,
		IF(@last_did=did ,
				if(@last_age = age ,@rank, @rank:=@rank+1),
				@rank:=1
		 ) as rk,
		@last_did:=did as last_did,
		@last_age:=age as last_age
from emp t
ORDER BY t.did,t.age desc
) a
WHERE a.rk = 2;