MySQL基础篇--自用笔记

MySQL基础篇

数据库相关概念

名称	全称	简称
数据库	存储数据的仓库，数据是有组织的进行存储	DataBase(DB)
数据库管理系统	操纵和管理数据库的大型软件	DataBase Management System(DBMS)
SQL	操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准	Strctured Query Language(SQL)

MySQL数据库

• 关系型数据库(RDBMS)

  概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

  特点：

  1、使用表存储数据，格式统一，便于维护

  2、使用SQL语言操作，标准统一，使用方便

• 数据模型

SQL

SQL通用语法

1、SQL语句可以单行或多行书写，以分号结尾；

2、SQL语句可以使用空格/缩进来增强语句的可读性；

3、MySQL数据库的SQL语句不区分大小写，关键字建议使用大写；

4、注释：

• 单行注释：–注释内容或 # 注释内容（MySQL特有）

• 多行注释：/*注释内容 */

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

• DDL——数据库操作

  1、查询

  查询所有数据库

  ​ show databases;

  查询当前数据库

  ​ select database();

  2、创建

  ​ create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

  3、删除

  ​ drop database[if exists] 数据库名;

  4、使用

  ​ use 数据库名;

• DDL—表操作—查询

  1、查询当前数据库所有表

  ​ show tables;

  2、查询表结构

  ​ desc 表名；

  3、查询指定表的建表语句

  ​ show create table 表名;

• DDL—表操作—创建

  create table 表名(

  ​		字段1  字段1类型[comment 字段1注释]，

  ​		字段2  字段2类型[comment 字段2注释]，

  ​		......

  ​		字段n  字段n类型[comment 字段n注释]

  )[comment 表注释];

• DDL—表操作—数据类型

  MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

• DDL—表操作—修改

  1、修改数据类型

  ​ alter table 表名 modify 字段名 新数据类型（长度）;

  2、修改字段名和字段类型

  ​ alter table 表名 change 旧字段名 新字段名 类型（长度）[comment 注释] [约束];

  3、删除字段

  ​ alter table 表名 drop 字段名;

  4、修改表名

  ​ alter table 表名 rename to 新表名;

• DDL—表操作—删除

  1、删除表

  ​ drop table [if exists] 表名;

  2、删除指定表，并重新创建该表

  ​ truncate table 表名;

  ​ 注意：在删除表时，表中的全部数据也会被删除。

DML

• DML-介绍

  DML英文全称是Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。

  添加数据（insert）

  修改数据（update）

  删除数据（delete）

• DML-添加数据

  1、给指定字段添加数据

 insert into 表名(字段名1，字段名2，...)value(值1，值2，...);

2、给全部字段添加数据

  insert into 表名 value(值1，值2，...);

3、批量添加数据

  insert into 表名(字段名1，字段名2，...)value(值1，值2，...),(值1，值2，...),(值1，值2，...);

  insert into 表名 value(值1，值2，...),(值1，值2，...),(值1，值2，...);

注意：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

字符串和日期数据应该包含在引号中。

插入的数据大小，应该在字段的规定范围内。

• DML-修改数据

  UPDATE 表名 SET 字段名1=值1，字段名2=值2，...[WHERE 条件];

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

• DML-删除数据

  DELETE FROM 表名 [WHERE 条件]

注意：

DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

DELETE语句不能删除某一个字段的值（可以使用UPDATE）。

DQL

• DQL-语法

• DQL-基本查询

  1、查询多个字段

SELECT 字段1，字段2，字段3...FROM 表名;

  SELECT * FROM 表名;

2、设置别名

  SELECT 字段1[AS 别名1]，字段2[AS 别名2] ...FROM 表名;

3、去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

• DQL-条件查询

  1、语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2、条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或!=	不等于
BETWEEN…AND…	在某个范围内（含最小、最大值）
IN(…)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL	是NULL

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或 ||	或者（多个条件任意一个成立）
NOT 或 ！	非，不是

• DQL-聚合函数

  1、介绍

  将一列数据作为一个整体，进行纵向计算。

  2、常见聚合函数

  函数	功能
  count	统计数量
  max	最大值
  min	最小值
  avg	平均值
  sum	求和

  3、语法

SELECT 聚合函数（字段列表）FROM 表名;

注意：null值不参与所有聚合函数运算。

• DQL-分组查询

  1、语法

  SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

2、where与having的区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意：

执行顺序：where > 聚合函数 > having。

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

• DQL-排序查询

  1、语法

  SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;

2、排序方式

ASC：升序（默认值)

DESC:降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

• DQL-分页查询

  1、语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;

注意：

起始索引从0开始，起始索引 = （查询页码 - 1）× 每页显示记录数。

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

DCL

• DCL-介绍

• DCL-管理用户

  1、查询用户

  USE mysql;

  SELECT * FROM user;

  2、创建用户

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

  3、修改用户密码

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

  4、删除用户

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

  注意：

  主机名可以使用%通配。

  这类SQL开发人员操作的比较少，主要是DBA ( Database Administrator 数据库管理员）使用。

• DCL-权限控制

  MySQL中定义了很多种权限，但是常用的就以下几种:

  权限	说明
  ALL, ALL PRIVILEGES	所有权限
  SELECT	查询数据
  INSERT	插入数据
  UPDATE	修改数据
  DELETE	删除数据
  ALTER	修改表
  DROP	删除数据库/表/视图
  CREATE	创建数据库/表

  1、查询权限

  SHOW GRANTS FOR ‘用户名’@‘主机名’;

  2、授予权限

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

  3、撤销权限

  REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;

  注意：

  多个权限之间，使用逗号分隔。

  授权时，数据库名和表名可以使用进行通配，代表所有。*

函数

字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：

函数	功能
CONCAT(S1,S2,…Sn)	字符串拼接，将S1，S2，…Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串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)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回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)	返回起始时间date1和结束时间date2之间的天数

流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

函数	功能
IF(value , t , f)	如果value为true，则返回t，否则返回f
IFNULL(value1 , value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [res1] …ELSE [ default ] END	如果val1为true，返回res1，…否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [res1] …ELSE [ default ] END	如果expr的值等于vall，返回res1，…否则返回default默认值

约束

概述

1、概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

2、目的：保证数据库中数据的正确、有效性和完整性。

3、分类:

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

外键约束

• 概念

  外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
  

  注意:目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

• 语法

  添加外键

 CREATE TABLE表名(
  		字段名 数据类型,
  		[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES主表(主表列名)
  );

  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

删除外键

  ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

• 删除/更新行为

  行为	说明
  NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致)
  RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与NO ACTION一致)
  CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键,如果有，则也删除/更新外键在子表中的记录。
  SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(这就要求该外键允许取null)。
  SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DEIETE CASCADE;

多表查询

多表关系

• 概述

  项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种:

  一对多(多对一)、多对多、—对一

• —对多(多对一)

  案例：部门与员工的关系

  关系：一个部门对应多个员工，一个员工对应一个部门

  实现：在多的一方建立外键，指向一的一方的主键

  

• 多对多

  案例：学生与课程的关系

  关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

  实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

  

• 一对一

  案例：用户与用户详情的关系

  关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

  实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

  

多表查询概述

• 概述：指从多张表中查询数据。

• 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)。

  

多表查询分类

1、连接查询

内连接：相当于查询A、B交集部分数据

外连接：

​左外连接：查询左表所有数据，以及两张表交集部分数据

​右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

2、子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);

子查询外部的语句可以是INSERT /UPDATE / DELETE/ SELECT的任何一个。
根据子查询结果不同，分为:

标量子查询（子查询结果为单个值)

列子查询(子查询结果为一列)

行子查询(子查询结果为一行)

表子查询(子查询结果为多行多列)

根据子查询位置，分为: WHERE之后、FROM之后、SELECT之后。

• 连接查询-内连接
  内连接查询语法:

  隐式内连接

 SELECT 字段列表 FROM 表1，表2 WHERE 条件...;

显式内连接

  SELECT 字段列表FROM 表1[INNER] JOIN表2 ON 连接条件...;

内连接查询的是两张表交集的部分

• 连接查询-外连接
  外连接查询语法:

  左外连接

 SELECT 字段列表 FROM 表1 LEFT [ OUTER ]  JOIN 表2 ON 条件...;

相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据

右外连接

  SELECT 字段列表 FROM 表1 RIGHT [OUTER ] JOIN 表2 ON条件....;

相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据

• 连接查询-自连接
  自连接查询语法:

  SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

自连接查询,可以是内连接查询,也可以是外连接查询。

• 联合查询-union , union all
  对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
  SELECT 字段列表FROM 表A …
  UNION [ ALL]
  SELECT字段列表 FROM 表B …;

  对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

  union all会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

• 标量子查询

  子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

  常用的操作符：= <> > >= < <=

• 列子查询

  子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

  常用的操作符：IN 、NOT IN 、ANY 、SOME、ALL

  操作符	描述
  IN	在指定的集合范围之内，多选一
  NOT IN	不在指定的集合范围之内
  ANY	子查询返回列表中，有任意一个满足即可
  SOME	与ANY等同，使用SOME的地方都可以使用ANY
  ALL	子查询返回列表的所有值都必须满足

• 行子查询

  子查询返回的结果是一行（可以是多列)，这种子查询称为行子查询。

  常用的操作符：= 、>、IN 、NOT IN

• 表子查询

  子查询返回的结果是多行多列，这种子查询称为表子查询。

  常用的操作符：IN

事务

事务简介

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

默认MysQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

事务操作

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

 SELECT @@autocommit ;

  SET @@autocommit = 0 ;

• 开启事务

START TRANSACTION或 BEGIN;

• 提交事务

  COMMIT;

• 回滚事务

  ROLLBACK;

事务四大特性

原子性（Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性（Consistency)：事务完成时，必须使所有的数据都保持一致状态。

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

持久性（Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了"幻影”。

事务隔离级别

隔离级别	赃读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默认)	×	×	√
serializable	×	×	×

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [SESSIN | GLOBAL] TRANSACTON SOLATION LEVEL { READUNCOMMTTED | READCOMMITED | REPEATABLE READ | SERALIZABLE}