MYSQL概述:

关系型数据库,建立在关系模型基础上,有多张相互连接的二维表组成的数据库

使用表存储数据,格式统一,便于维护

使用sql语言操作,标准统一,使用方便

SQL

sql:通用语法

可以当行或者多行书写,已分号结尾

可以使用空格和tab增强语句可读性

不区分大小写,但关键字建议大写.

单行注释

-- 或者 #

多行注释

/**/

sql分类

DDL 数据定义语言 定义数据库字段

DML 数据操作语言 增删改

DQL 数据查询语言 查询

DCL 数据控制语言 用于创建数据库用户,控制数据库访问权限

DDL 数据库操作

查询

查询所有数据库
SHOW DATABASES;
查询当前数据库
SELSECT DATABSE();
创建数据库
CREATE DATABSE [IF NO EXISTS] 数据库名 [DEFAULF CHARSET 字符集] [COLLATE 排序规则] ;
删除数据库
DROP DATABSE[IF EXISTS] 数据库名;
使用数据库
USE 数据库名;
DDL 表操作
查询当前数据库所有的表
SHOW TABLES;
查询表结构
DESC 表名;
查询指定表的建表语句
SHOW CREATE TABLE 表名;
DDL 表结构创建
CREATE TABLE 表名 {
​
字段一 字段类型[COMMENT 字段一注释],
字段二 字段类型[COMMENT 字段二注释],
字段三 字段类型[COMMENT 字段三注释],
.....
}[COMMENT 表注释];
DDL 表修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束]
修改数据类型
ALTER TABLE 表名 MODIFY   字段名 新数据类型;
修改字段名和数据类型
ALTER 表名 CHANGE 表名 旧字段名 新字段名 类型[COMMENT 注释][约束]
删除字段
ALTER TABLE 表名 DROP 字段名
修改表名
ALTER TABLE 表名 RENAME TO 新表名
删除表
DROP TABLE [IF EXISTS] 表名

删除指定表,并重新建立该表

TRUNCAT TABLE 表名;

DML 添加数据

1.给指定字段添加数据
INSERT INTO 表名 (字段名1,字段名2...) VALUES (值1,值2....);
2.给全部字段添加数据
INSERT INFTO 表名 VALUES (值1,值2.....);
3.批量添加数据
INSERT INTO 表名 (字段名1,字段名2...) VALUES (值1,值2....)(值1,值2....)(值1,值2....)(值1,值2....)(值1,值2....);
INSERT INTO 表名 VALUES (值1,值2....)(值1,值2....)(值1,值2....)(值1,值2....)(值1,值2....);
DML 修改数据
UPDATE 表名 set 字段1=值1 .... [WHERE 条件]
DML 删除数据
DELETE FROM 表名 [WHERE 条件]

DQL 查询数据

SELECT 字段列表
FROM 表名
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组之后的条件列表
ORDER BY 排序字段列表
LIMIT 分页参数
基本查询

查询多个字段

  SELECT 字段1 ,字段2 FROM 表名

查询所有字段

SELECT * FROM 表名

设置别名

SELECT 字段1[AS 别名1] ,字段2[AS 别名2] FROM 表名[AS 别名]
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
条件查询

运算符

功能

>

大于

>=

大于等于

<

小于

<=

小于等于

<>或!=

不等于

BETWEEN....AND...

在某个范围之内(最大或者最小)

IN(...)

在in 之后的列表中的值,多选一

LIKE 占位符

模糊匹配(_匹配单个字符,%匹配多个字符)

IS NULL

是NULL

ADN 或 &&

且

OR 或 ||

或

NOT 或 !

否

聚合函数
将一列数据作为一个整体进行纵向计算

函数

功能

COUNT

统计数量

max

最大

min

最小

avg

平均

sum

求和

分组查询

where 和having 的区别

执行时机不同,where 是分组前进行过滤,having 是分组后进行过滤

判断条件不同, where 不能对聚合函数进行判断 having可以

排序查询

desc 降序 asc 升序

分页查询
SETLECT * FROM 表名 LIMTIT 起始索引 ,查询记录数
DQL执行顺序
SELECT 字段列表
FROM 表名       
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组之后的条件列表
ORDER BY 排序字段列表
LIMIT 分页参数

DCL 管理用户

查询用户
USE mysql
SELECT * FROM USER;

创建用户

CREATE USER '用户名@主机名' IDENTIFIED BY '密码'

修改用户密码

ALTER USER '用户名@主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户

DROP USER '用户名@主机名';

DCL 权限控制

权限

说明

ALL ,ALL PRIVILEGES

所有权限

SELECT

查询数据

INSERT

插入数据

UPDATE

修改数据

DELETE

删除数据

ALTER

修改表

DROP

删除数据库/表/视图

CREATE

创建数据库/表

GRANT 权限列表 ON 数据库.表名 TO '用户名@主机名';
REMOVE 权限列表 ON 数据库.表名 FROM '用户名@主机名';

函数

字符串函数

函数

功能

CONCAT(S1,S2,S3....)

字符串拼接

LOWER(str)

将字符串转为小写

UPPER(str)

将字符串转为大写

LPAD(str,n,pad)

左填充,用字符串pad对str进行填充,达到n个字符串长度

RPAD(str,n,pad)

右填充,用字符串pad对str进行填充,达到n个字符串长度

TRIM(str)

去掉字符串前后空格

SUBSTRING(str,start,len)

返回字符串str从start位置起的len个长度的字符串

数值函数

函数

功能

CEIL(x)

向上取整

FLOOP(X)

向下取整

MOD(X)

返回x/y的模

RAND(X)

返回0~1的随机数

ROUND(X,y)

求参数X的四舍五入的值,保留y位小数

日期函数

函数

功能

CURDATE()

返回当前日期

CURTIME()

返回当前时间

NOW()

返回当前日期和时间

YEAR(date)

获取指定date的年份

MONTH(date)

获取指定date的月份

DAY(date)

获取指定date的日期

DATE_ADD(date.INTERVAL expr type)

返回一个日期/时间值加上一个时间间隔expr后的时间值

DATEDIFF(date1,date2)

返回起始时间和结束时间之间的天数

流程函数

函数

功能

IF(value,t,f)

如果value为true,则返回t,否则返回f

IFNULL(value1,value2)

如果value1不为空返回value1,否则返回value2

CASE WHEN [val1] THEN [res1] ... ELES [default] END

如果value1为true,返回res1...否则返回默认值

CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END

如果expr 等于value 返回res1 ... 否则返回默认值

约束

概述:作用于表中字段上的规则,用于限制存储再表中的数据

目的:保证数据库中数据的正确,有效和完整

分类

约束

描述

关键字

非空约束

限制字段的数据不为null

NOT NULL

唯一约束

保证该字段的所有数据不重复,唯一

UNQIUE

主键约束

主键是一行数据的的唯一标识,要求非空且唯一

PRIMARY KEY

默认约束

保存数据是,如果为空则填入默认数据

DEFAULT

检查约束

保证字段值满足某一条件

CHECK

外键约束

用来两张表的数据之间建立连接的,保证数据的完整性和唯一性

FOREIGE KEY

多表查询

多表关系

连接查询

连接查询
内连接

隐式内连接

select 字段列表 FORM 表1,表2 where 条件

显示内连接

select 字段列表 FROM 表1,表2 [INNER] JOIN 表2 ON 连接条件
外连接

左连接

slect 字段列表 FROM 表1 left [outer] JOIN 表2 on 连接条件

右连接

select 字段列表 FROM 表1 right [outer] JOIN 表2 on 连接条件
自连接
slect 字段列表 FROM 表1 别名1 JOIN 表1 别名2 ON 条件
联合查询-union ,union all

对于union 查询,就是把多次查询的结果合并起来,形成一个新的查询结果集

select 字段列表 form 表1... union [ALL] select 字段列表 form 表2...  ---- all 标识为是否去重 
注意,对于联合查询的多张表的列数必须保持为一致,字段类型也是必须要保持一致.

子查询

概述: sql语句中嵌套select语句,称为嵌套查询,又称子查询

select * from t1 where column=1 (select column1 from t2)

子查询外部sql语句可以是select,insert,update,delete

子查询分类

根据子查询结果不同,分为

标量子查询 : 子查询结果为单个值

列子查询 : 子查询结果为一列

行子查询 : 子查询结果为一行

表子查询 : 子查询结果为多行多列

根据子查询位置,分为再where之后,from 之后,select之后

事务

概述: 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求,即这些操作要么同时成功要么同时失败.

事务的操作

查看/设置事务的提交方式

select @@autocommit; set@@autocommit = 0;

提交事务

commit

回滚事务

ROLLBACK

事务的四大特性 A C I D

原子性 事务是不可分割的最小操作单元.要么同时成功要么同时失败

一致性 事务完成时,必须使所有的数据保持为一致的状态

隔离性 数据库系统提供的隔离机制,保证事务不受外部并发操作影响的独立环境下运行

持久性 事务一旦提交或者回滚,他对数据库中的数据的改变就是永久的

并发事务问题

脏读,一个事务读到另一个事务还没提交的数据

不可重复读 一个事务先后读取到同一条数据,但两次读取的数据不一致

幻读 一个书屋按照条件查询事务时,没有对应的数据行,但是再插入数据的时,又发现这条数据已经存在

事务数据隔离

  1. 读未提交（Read Uncommitted）：

    • 这是最低的隔离级别，在这个级别上，一个事务能够读取到其他事务尚未提交的数据更改。这种现象称为脏读（Dirty Reads），即一个事务可以读取到另一个事务还没有提交的数据。

  2. 读已提交（Read Committed）：

    • 在这个隔离级别上，一个事务只能看到其他事务已经提交的数据更改。这避免了脏读的问题，但是由于在事务期间允许进行多次查询，可能会出现不可重复读（Non-repeatable Reads）的情况，即在同一事务中的两次查询可能得到不同的结果。

  3. 可重复读（Repeatable Read）：

    • 这是MySQL的默认隔离级别。在这个级别上，事务执行过程中，对数据的所有读操作都会看到事务开始时的数据状态，而不管其它事务是否对该数据进行了修改并提交。这防止了不可重复读的问题，但是在某些情况下可能会出现幻读（Phantom Reads），即在同一事务中，相同条件的查询可能会因为其他事务插入新的符合条件的行而返回不同的结果集。

  4. 串行化（Serializable）：

    • 这是最高的隔离级别，它强制事务串行而不是并发地执行。在这种模式下，读取操作会加上共享锁，防止其他事务对读取的数据进行修改，从而完全避免了脏读、不可重复读和幻读的问题。但这也可能导致较高的锁定开销和较低的并发性能。

隔离级别越高,数据越安全,但是性能会越低