MySQL笔记

安装MySQL数据库

1、下载mysql-8.0.22-winx64.zip文件。

2、解压到指定目录，本例为D:\mysql-8.0.22-winx64

3、在MySQL的根目录下新建 my.ini

[mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录 basedir=D:\\mysql-8.0.22-winx64 # 设置mysql数据库的数据的存放目录 datadir=D:\\mysql-8.0.22-winx64\\data # 允许最大连接数 max_connections=200 # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统 max_connect_errors=10 # 服务端使用的字符集默认为UTF8 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # 默认使用“mysql_native_password”插件认证 default_authentication_plugin=mysql_native_password [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [client] # 设置mysql客户端连接服务端时默认使用的端口 port=3306 default-character-set=utf8

4、安装MySQL服务

在路径C:\Windows\System32下找到cmd.exe，点击右键以管理员身份运行（win 7下面严格区分权限，必须以管理员身份）

初始化命令执行：

在MySQL安装目录的 bin 目录下执行命令：

mysqld --initialize --console

执行完成后，会打印 root 用户的初始默认密码(复制到文本工具里，后面登录完成后需要修改)

安装服务命令

mysqld --install 服务名

5、mysql服务启动与停止

在doc命令下进入到mysql的bin目录(D:\ mysql-8.0.22-winx64\bin>)，

输入"net start mysql"启动mysql,

输入"net stop mysql"停止mysql服务。

6、修改密码：

使用mysql -uroot -p初始密码 -P端口 登录成功后，修改密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

7、删除服务

sc delete 服务名

MySQL8.0安装问题：

1）、net start命令时提示：缺少 vcruntime140_1.dll 文件

直接拷贝vcruntime140_1.dll文件到D:\ mysql-8.0.22-winx64\bin目录中

2）、net start 命令时，提示缺少msvcp140.dll 文件

a) 执行vc_redist.x64.exe文件，安装默认的dll文件即可

b) 打开“控制面板”-“程序”-“卸载程序”

如图所示，找到此程序，点击“修复”即可，提示重启电脑。

安装参考：https://www.cnblogs.com/laumians-notes/p/9069498.html

常见的关系型数据库

MySQL Oracle SQLServer DB2 SQLite

SQL全称：

Structured Query Language

SQL分类：

DDL（Data Definition Language）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。

DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update和select等。

DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。常用的语句关键字主要包括grant、revoke等。

PS：DML还可以细分成DQL（Data Query Language）针对的select查询操作

mysql的登录命令

mysql -h 主机名 -u 用户名 –p密码 –P 端口

例： mysql -h 127.0.0.1 -u root -p123456 -P3306

简化： mysql -uroot -p123456

DDL语法

数据库操作

1. 创建数据库

create databse 数据库名

1. 查看数据库

show databases

1. 选择数据库

use 数据库名

1. 查看表

show tables

1. 删除数据库

drop database 数据库名

表操作

1. 创建表

create table 表名（

列名1 列1的数据类型，

列名2 列2的数据类型

）

1. 查看表结构

desc 表名

1. 查看表的创建语法

show create table 表名

1. 删除表

drop table 表名

常见完整性约束：

NOT NULL 非空值约束

DEFAULT 默认值约束

UNIQUE 唯一性约束

PRIMARY KEY 主键约束

AUTO_INCREMENT 用于整数列默认自增1

Foreign key 外键约束

Check 检查约束(MySQL8.0之前不支持，但不会报语法错误)

修改表的字段：

修改字段类型：ALTER TABLE 表名MODIFY [COLUMN] 列名 列新类型

增加表字段：ALTER TABLE 表名 ADD [COLUMN] 列名 列类型

删除表字段：ALTER TABLE 表名 DROP [COLUMN] 列名

字段改名： ALTER TABLE 表名CHANGE [COLUMN] 原列名 新列名 列类型

修改字段顺序：ALTER TABLE 表名 ADD|MODIFY 列名 列类型 [FIRST | AFTER 列名]

更改表名： ALTER TABLE 表名 RENAME [TO] 新表名

DML

插入：

insert into 表名 values(值1，值2，……)

insert into 表名(列1，列2……) values(值1，值2……)

批量插入：

insert into 表名(列1，列2……) values(值1，值2……), (值1，值2……)……

更新

update 表名 set 列1=值1, 列2=值2 [where 条件]

删除

delete from 表名 [where 条件]

查询（DQL）

SELECT [DISTINCT] 字段列表

FROM 表名

[WHERE 条件表达式1]

[GROUP BY 字段名1 [HAVING 条件表达式2] ]

[ORDER BY 字段名2 [ASC | DESC] ]

[LIMIT [offset,] rows] ]

SQL执行的顺序：

from:需要从哪个数据表检索数据

where:过滤表中数据的条件

group by:如何将上面过滤出的数据分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果

order by :按照什么样的顺序来查看返回的数据

limit: 分页

参考：https://www.cnblogs.com/warehouse/p/9410599.html

复制表

CREATE TABLE 表名 SELECT * FROM 表名1

CREATE TABLE 表名 LIKE 表名1

INSERT INTO 表名 SELECT * FROM 表名1

聚合函数

记数函数： count(列名) 计算元素的个数

求和函数： sum(列名) 对某一列的值求和，但属性必须数值类型

计算平均值：avg(列名)对某一列的值计算平均值

求最大值： max(列名) 找出某一列的最大值

求最小值： min(列名) 找出某一列的最小值

条件查询

where关键字

=、!= <> 、>、<、>=、<= 、<=>

and、or

[not] in

[not] between and

like % _

REGEXP

is [not] null (<=>可以做null判断)

多表数据查询

1、内链接

inner join

eg:

select * from 表A inner join 表B on 表A.列=表B.列

select * from 表A ,表B where 表A.列=表B.列

2、外连接

left [outer] join

right [outer] join

eg:

select * from 表A left join 表B on 表A.列=表B.列

select * from 表A right join 表B on 表A.列=表B.列

3、交叉连接

cross join (笛卡尔积)

4、合并查询数据记录 （全外关联）

union | union all

5、子查询

独立子查询

相关子查询

关键字包含：in、not in、=、!=、exists、not exists

DCL

root用户创建普通用户

1、create user命令

CREATE USER 'jsj001'@'localhost' IDENTIFIED BY '123456';

2、grant GRANT是一个赋权限的命令，同时可以创建用户，并指定用户的密码

GRANT ALL ON *.* TO 'jsj001'@'localhost' [ IDENTIFIED BY '123456'];

GRANT SELECT,INSERT ON jsj01.t_dept TO 'jsj001'@'localhost';

查看用户的所有权限

show grants for 'jsj001'@'localhost';

回收权限

REVOKE INSERT,UPDATE ON *.* FROM 'jsj001'@'localhost'

删除用户

1、drop命令

DROP USER 'jsj001'@'localhost'

2、delete mysql的user表数据

delete FROM mysql.`user` WHERE `Host`='localhost' and `User`='jsj002';

FLUSH PRIVILEGES;

修改密码：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

数据类型

常用数据类型

分类	备注和说明	数据类型	说明
文本数据类型	字符数据包括任意字母、符号或数字字符的组合	char	固定长度的字符数据
		varchar	可变长度的字符数据
		text	存储长文本信息
日期和时间	日期和时间在单引号内输入	date	日期
		time	时间
		datetime /timestamp	日期和时间
数值型数据	该数据仅包含数字，包括正数、负数以及浮点数	int | integer smallint	整数
		float double	浮点数
货币数据类型	用于财务数据	dec | decimal	定点数
Bit数据类型	表示是/否的数据	bit	存储布尔数据类型
二进制数据类型	存储非子符和文本的数据	BLOB	可用来存储图像

其他：（了解）

binary varbinary enum set

char、varchar、text数据类型的区别？

Char为定长，varchar，text为变长

Char在保存的时候，后面（右边）会用空格填充到指定的长度；

Varchar在保存的时候，不进行填充；

text不可以写默认值，不需要指定长度。

运算符

算数运算符

+ - * /(div) %(mod)

ps：跟null运算返回null，除数为0时返回null

比较运算符

=或<=> !=或<> > < >= <= (<=>可以操作null)

BETWEEN AND | NOT BETWEEN AND

IS NULL | IS NOT NULL

IN | NOT IN

LIKE % _

REGEXP

REGEXP匹配模式： ^ 匹配输入字符串的开始位置 $ 匹配输入字符串的结束位置 . 匹配任意字符 [...] 匹配所包含的任意一个字符 [^...] 匹配未包含的任意字符 p1|p2|p3 匹配 p1 或 p2 或 p3 * 匹配前面的表达式零次或多次 + 匹配前面的表达式一次或多次 {n} 匹配前面的表达式确定的 n 次 {n,m} 最少匹配前面的表达式 n 次且最多匹配 m 次

逻辑运算符

AND(&&) OR(||) NOT(!) XOR

位运算符（了解）

& | ~ ^ << >>

ps：先将十进制数转换为二进制，然后再进行位运算，最后结果转换为十进制，再显示。

常用函数

字符串函数

char_length() length()

concat() group_concat()

insert()

lower() upper()

left() right()

lpad() rpad() repeat()

ltrim() rtrim() trim()

replace()

strcmp()

substring()

数值函数

abs() ceil() floor() mod() rand() round() truncate()

日期函数

now() curdate() curtime()

year() month() monthname() week()

day() dayname() dayofmonth() dayofweek() dayofyear()

hour() minute() second()

date_format()

date_add()

流程函数

if(value,t,f)

ifnull(value1,value2)

case when[value1] then [result]...else [default] end

case [expr] when [value1] then [result1] ... else [default] end

系统函数

database() version() user() md5()

存储引擎

查看当前的默认引擎

show variables like 'default_storage_engine'

查询当前数据库支持的存储引擎

show engines

修改存储引擎

alter table 表名 engine=存储引擎名

MySQL存储引擎的区别：

MyISAM：不支持事务 不支持外键 访问速度比较快 适用不需要事务处理的情况

InnoDB： 5.5以上自动开启 支持事务 支持外键 占空间 适用事务、并发控制、安全性高

MEMORY：使用内存存储 速度最快 安全没有保障 可做临时表

字符集

常用字符集

ASCII （单字节 127个字符）

ISO8859-1 （latin1）（单字节，ASCII的扩展）

GB2312，GBK，Big5（双字节）

Unicode

UTF-8（1-4字节，可变字节编码，兼容ASCII，中文占三个字节）

UTF-16（2字节）

UTF-32（4字节）

修改表字符集

alter table 表名 default charset=字符集名

查看所有可用的字符集命令

show character set

要显示当前数据库的字符集和排序规则，可以使用以下的指令：

show variables like 'character_set_database'

show variables like 'collation_database'

查看所有字符集设置

show variables like 'char%';

客户端字符集参数

character_set_client（客户端）

character_set_connection（连接）

character_set_results（返回结果的字符集）

统一修改以上字符集： set names 字符集名

索引 Index

1、索引的分类

1） 普通索引 INDEX

a) 创建表的同时建立索引

create table 表名

(

字段 字段类型… ,

INDEX|KEY 索引名（字段名(长度) ）

)

b) 表已经创建过

alter table 表名 add index 索引名（字段名(长度) ）

create index 索引名 on 表名（字段名(长度) ）

2) 唯一索引 unique index

跟普通索引创建方式一样，在index关键字前面加unique

创建唯一约束时会自动创建唯一索引

3) 主键索引 (自动索引) primary key

alter table 表名 add primary key(字段)

不能用create index 语法

4) 多列索引

跟普通索引创建方式类似， 索引名（字段1，字段2…）

5) 全文索引

fullText MyISAM引擎使用

2、索引的查看

show index from 表名

3、 explain关键字

explain select查询语句

4、删除索引

alter table 表名 drop index 索引名

drop index 索引名 on 表名

5、索引使用场合

1） where子句

2） group by 子句

3） 联合查询中的主键和外键

4） 唯一约束的字段

6、索引失效条件

条件是or, 如果还想让or条件生效，给or每个字段加个索引

like查询，以%开始

对索引列进行计算

联合索引是第一个字段不在筛选条件里时用不到

7、索引的优缺点，什么时候使用索引，什么时候不能使用索引

索引最大的好处是提高查询速度

缺点是更新数据时效率低，因为要同时更新索引

对数据进行频繁查询时建立索引，如果要频繁更改数据不建议使用索引

8、索引的存储类型

B+TREE ( InnoDB、MyISAM默认 )

HASH ( Monery默认)

9、MySQL B+Tree索引和Hash索引的区别？

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;

B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问;

MyInnoDB引擎只能是B+TREE索引，在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树效率更高。

视图 View

1、创建视图语法

create view 视图名(列1，列2)

as select语句

2、查询视图

select * from 视图名　

3、查看视图

1）查看表或视图名

　　show tables

2）查看表或视图结构

desc 视图名

3）查看表或视图的定义

　 show create view|table 视图名

4）查看表或视图的详细信息

show table status [from 数据库名] like 表|视图名

4、修改视图

1）alter view 视图名 as select语句

2）create or replace view 视图名 as select语句

5、删除视图

drop view 视图名1,视图名2

ps: 可以修改视图情况

触发器 Trigger

1、创建触发器的语法

CREATE TRIGGER 触发器名

BEFORE|AFTER INSERT|UPDATE|DELETE

ON 表名 FOR EACH ROW

[BEGIN]

触发器内容

[END]

2、查看触发器

SHOW TRIGGERS LIKE 't_%'; -- like 后面跟的表名

SHOW TRIGGERS WHERE `trigger` LIKE 'tri_%'; --根据字段名查询

3、删除触发器

DROP TRIGGER 触发器名

4、delimiter的使用

定义mysql分隔符

5、new 和 old关键字的使用

注意事项：

1、MYSQL中触发器中不能对本表进行 insert ,update ,delete操作，以免递归循环触发

 2. 对于update 只能用set进行操作，insert与delete只能借助第二张表才能实现需要的目的

存储过程 Procedure和函数 Function

1、创建存储过程：

create procedure 存储过程名(in|out|inout 参数名 参数类型)

begin

存储过程内容

end

2、创建函数:

create function 存储函数名(参数名 参数类型) returns 返回类型

begin

存储函数内容

retrun 返回变量

end

3、调用存储过程或函数

call 存储过程名(参数列表)

select 函数名(参数列表)

4、变量的使用：

系统变量@@（全局变量global、会话变量session）

selecet @@global.xxx

selecet @@session.xxx | selecet @@ xxx

自定义变量 （用户变量@、 局部变量）

SET @xxx = ‘’;

SELECT @xxx

局部变量就是存储过程内部声明的

声明: declare 变量名 变量类型 [default 默认值]

赋值: a) set 变量名 = 值

b) select 字段名 into 变量名 SQL语句

5、游标的使用：

声明游标： declare 游标名 cursor for select 字段1，字段2 from XXX

打开游标：open游标名

使用游标：fetch游标名 into 变量1，变量2

关闭游标： close 游标名

6、流程控制：

条件控制语句:

if 条件表达式 then XXXX

[elseif 条件表达式 then XXXX ]

[else XXXX]

end if

类似： if(){ }else if(){ } else{ }

case when条件表达式 then XXXX

[when条件表达式 then XXXX]

[else XXXX]

end case

类似： if(){ }else if(){ } else{ }

case 变量名 when值 then XXXX

[when 值 then XXXX]

[else XXXX]

end case

类似： swich 变量名case 值1: xxx; break; default: xxx;

循环控制语句：

while 条件表达式

do

XXXX

end while

类似：while( ){ }

repeat

XXXX

until 条件表达式

end repeat

类似：do{ }while() , 条件表达式相反

[标识名：]loop

XXXX

[leave 标识名]

[iterate标识名]

end loop[标识名];

类似： while(true){ } for( ; ; ){ }

leave—>break iterate🡪 continue

7、删除存储过程和函数：

drop procedure 存储过程名

drop function存储函数名

8、操作条件（异常处理）

1)、定义条件

declare 条件名 conditon for mysql_error_code|sqlstate_code

eg: declare not_found conditon for 1329

2)、定义处理程序

declare 处理类型 handler for 处理条件 SQL语句

处理类型： CONTINUE | EXIT | UNDO(暂时不支持)

处理条件： NOT FOUND | SQLWARNING | SQLEXCEPTION | errorCode | SQLSTATE |条件名

eg: declare EXIT handler for NOT FOUND set num = 1;

事务 TCL（事务处理语言）

事务的4个特性 ACID

1、原子性（atomicity） 事务的整个操作是一个整体，不可以分割，要么全部成功，要么全部失败

2、一致性（consistency）事务操作的前后，数据表中的数据总额一致

3、隔离性（isolation） 事务操作是相互隔离不受影响的

4、持久性（durability） 数据一旦提交，不可改变，永久的改变数据表数据

事务控制语句

开始事务：BEGIN | START TRANSACTION

回滚： ROLLBACK

结束事务：COMMIT

设置事物的提交方式：SET autocommit = 1|0

事务的4个隔离级别

1、未提交读

READ UNCOMMITTED

2、提交读（不可重复读） -- 大多数数据库默认的隔离级别

READ COMMITTED

3、可重复读 -- mysql的默认隔离级别

REPEATABLE READ

4、可串行化

SERIALIZABLE

读未提交：另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读

提交读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致，所有导致不可重复读。

可重复读：在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是针对新增的数据会有幻读现象。（MySQL新版本解决了幻读问题）

串行化：最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样

设置隔离级别

#未提交读

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

#提交读

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

#可重复读

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

#可串行化

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

查看MySQL隔离级别

mysql> SHOW VARIABLES LIKE 'tx_isolation'; （MySQL5）

show variables like 'transaction_isolation' （MySQL8）

隐式提交

DDL操作 alter table、truncate table、drop table

MySQL数据库锁的机制

MyISAM使用表锁，InnoDB使用行锁和表锁，在有索引的情况下默认使用行锁，在没有索引的情况下使用的是表锁。行锁分为共享锁(读锁)和排他锁(写锁)。

update,delete,insert都会自动给涉及到的数据加上排他锁 ==>演示：（有主键） 事务A update某条数据；事务B可以update其他数据，但对同一条数据不能修改，发生阻塞，等事务A提交后，才能继续修改。 select语句默认不会加任何锁类型 ==> 1、加共享锁： 事务A对某条数据加共享锁，select * from t_test where id=1 lock in share mode; 事务B对这条数据不能更新操作；可以同时对这条数据加共享锁，那现在这两个事务对这条数据就都只能读，不能update； 对其他数据update时，还是默认会加上排他锁，另一个事务update操作不了。 2、加排他锁 事务A对某条数据使用select * from.. where .. for update; 事务B对这条数据无法加共享锁和排他锁； 同时事务B不能对这条语句执行update，只有事务A可以。 =====>以上是行锁的两种实现模式：共享锁和排他锁 =====>下面使用没有索引的表测试--表锁 将以上的主键索引去掉； 事务A对某条数据加共享锁或排他锁，发现事务B对其他的数据也都没有update权限了；