我的MySQL学习

小流同志
155 阅读18分钟

基础篇

了解MySQL

数据库基础

什么是数据库

数据库(database) 保存有组织的数据的容器（通常是一个文件或一组文件）。需要注意的是：人们通常用数据库这个术语来代表他们使用的数据库软件。这是不正确的，确切地说，数据库软件被称为数据库管理系统DBMS。数据库是通过DBMS创建、操纵、访问的。

表

表某种特定类型数据的结构化清单

列和数据类型

列表中的一个字段。所有表都是由一个或多个列组成的； 分解数据：正确地将数据分解为多个列极为重要。 数据类型：所容许的数据的类型。每个表列都有相应的数据类型，它限制该列中存储的数据。

行

表中的一个记录

主键

主键：一列（或一组列），其值能够唯一标识表中的每个行。

什么是SQL

SQL是一种专门用来与数据库通信的语言。

SQL的通用语法与分类

SQL通用语法

  1. SQL语句可以单行或多行书写，以分号结尾。
  2. SQL语句可以使用空格/缩进来增强语句的可读性
  3. MYSQL数据库的SQL语句不区分大小写，关键字建议使用大写。
  4. 注释：单行注释：--或者# 多行注释:/* 注释内容*/

SQL分类

  1. DDL:数据库定义语言，用来定义数据库对象（数据库、表、字段）
  2. DML:数据库操作语言，用来对数据库表中的数据进行增删改
  3. DQL:数据库查询语言，用来查询数据库中表的记录
  4. DCL:数据库控制语言，用来创建数据库用户，控制数据库的访问权限。

DDL语句

DDL语句是数据定义语言：主要由create、 alter、 drop和truncate四个关键字完成

操作数据库

  • -- 查看所有数据库
  • SHOW DATABASES;
  • -- 切换（选择要操作的）数据库
  • USE 数据库名;
  • -- 创建数据库
  • CREATE DATABASE [IF NOT EXISTS] 数据库名[CHARSET=utf8];
  • -- 删除数据库
  • DROP DATABASE [IF EXISTS] 数据库名;
  • -- 修改数据库编码
  • ALTER DATABASE mydb1 CHARACTER SET utf8;

操作表

  • -- 创建表
  • CREATE TABLE [IF NOT EXISTS] 表名( 字段名 数据类型 约束条件， 字段名 数据类型 约束条件， ... 字段名 数据类型 约束条件， );
  • -- 查看当前数据库中所有表名称
  • - SHOW TABLES;
  • -- 查看指定表的创建语句
  • - SHOW CREATE TABLE 表名;
  • -- 查看表结构
  • - DESC 表名;
  • -- 删除表
  • - DROP TABLE 表名;
  • -- 修改之添加列
  • - ALTER TABLE 表名 ADD ( 列名 列类型, 列名 列类型, ... );
  • 修改之修改列类型(如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据)
  • - ALTER TABLE 表名 MODIFY 列名 列类型;
  • -- 修改之修改列名
  • - ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
  • -- 修改之删除列
  • - ALTER TABLE 表名 DROP 列名;
  • -- 修改表名称
  • - ALTER TABLE 原表名 RENAME TO 新表名;

SQL图形化界面工具DataGrip

DML语句

插入记录

  1. 插入一条记录
insert into tablename(field1,field2,...,fieldn) values(value1,value2,...,valuen);
  1. 插入多条记录
insert into tablename(field1,field2,...,fieldn) values(value1,value2,...,valuen),values(value1,value2,...,valuen);

更新记录

  1. 更新单表记录
update tablename set field1=value1,filed2=value,...fieldn=valuen [where condition];
  1. 更新多表记录
update t1,t2,...,tn set t1.field1=expr1,tn.field=exprn [where condition];

删除记录

  1. 删除单表记录
delete from tablename [where condition];
  1. 删除多表记录
delete from tablename1,tablename2 [where conditon];

DQL语句

DQL语法：

SELECT 字段列表 FROM 表名列表  WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数

基本查询

检索单个列
SELECT prod_name(列名) From Products(表名）
检索多个列
SELECT prod_name(列名),prod_price From Products(表名);
检索所有列
SELECT * FROM Products

条件查询

SELECT prod_name,prod_price FROM Products WHERE prod_price = 3.5;
只检索所需数据需要指定搜索条件
WHERE子句操作符
1. =  等于
2. <> 不等于
3. != 不等于
4. <  小于
5. <= 小于等于
6. !< 不小于
7. >  大于
8. >= 大于等于
9. !> 不大于
10. BETWEEN 再指定的两个值之间
11. IS NULL 为NULL值
12. AND或&& 并且（多个条件同时成立）
13. OR 或|| 或者（多个条件任意一个成立）
14. NOT 或！ 非，不是
检查单个值
SELECT prod_name,prod_price FROM Products WHERE prod_price<10;
SELECT prod_name,prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
空值检查
SELECT prod_name FROM Products WHERE prod_price IS NULL
AND操作符
SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <=4;

SELECT prod_name,prod_price FROM Products WHERE(vend_id='DLL01' OR vend_id ='BRS01') AND prod_price >=10;
注意：SQL在处理OR操作符前，优先处理AND操作符；所以需要将OR连接语句用括号包裹起来
NOT操作符
SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;
通配符
SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE '%bean bag';
SELECT prod_name FROM Products WHERE prod_name LIKE 'F%y';
SELECT cust_contact FROM customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;
SELECT cust_contact FROM customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;
注意：使用_下划线只能匹配单个字符而不是多个字符。
使用通配符要记住的技巧：
- 不要过分使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
- 在确实需要使用通配符，除非绝对有必要，否则不要把它们用搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来最慢的。
- 仔细注意通配符的位置。如果放错了地方，可能不会返回想要的数据

聚合函数

使用场景：我们经常需要汇总数据而不用把它们实际检索出阿里，为此SQL提供了专门的函数。使用这些函数，SQL查询可用于检索数据，以便分析和报表生成。

COUNT函数
SELECT COUNT( * ) AS num cust FROM Customers;
注意:使用COUNT( * )对表中的行的数据进行计数，不管表列中包含的是空NULL还是非空值。

SELECT COUNT(cust_email) as num_cust from custormers;
注意：只对电子邮件地址的客户计数，使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
SUM函数
SELEC SUM(quantity) as items_ordered FROM OrderItems WHERE order_num = 20005;
组合聚集函数
SELECT COUNT(*) AS num_items MIN(prod_price) as price_min,MAX(prod_price) as price_max,avg(prod_price) as price_avg FROM Products;

分组查询

SELECT vend_id,COUT(*) AS num_prods FROM Products GROUP BY vend_id;
返回每个供应商提供的产品数目

SELECT vend_id,COUNT(*) as num_prods FROM Products GROUP BY vend_id HAVING COUNT(*)>=2;
返回产品数目大于2的每个供应商

SELECT vend_id,COUNT(*) as num_prods FROM Products WHERE prod_price>=4 GROUP BY vend_id HAVING COUNT(*)>=2;

SELECT order_num,COUNT(*) as items FROM OrderItems GROUP BY order_num HAVING COUNT(*)>3 ORDER BY items,order_num;

排序查询

按多个列排序
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name;
仅在多行具有相同的prod_price值才对产品按prod_name进行排序。
按列位置排序
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3;
除了能用列名指出顺序外，ORDER BY 还支持按相对列位置进行排序。
指定排序方向(升序或降序)
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY; prod_price DESC, prod_name
prod_price列以降序排序，prod_name列按升序排序

分页查询

语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数
注意：
- 起始索引从0开始，起始索引=（查询页码-1）* 每页显示记录数
- 分页查询是数据库方言，不同的数据库有不同的实现，MYSQL中是LIMIT
- 如果查询的是第一页数据，起始索引可以省略，直接简写limit 10；
> SELECT TEST FROM DEMO limit 1,20;

DQL语句执行顺序

DQL的编写顺序：
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数

DQL的执行顺序:
FROM 
WHEER
GROUP BY
HAVING
SELECT
ORDER BY 
LIMIT

DCL语句

权限控制语句

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

语法：
1. 查询权限
SHOW GRANTS FOR'用户名@主机名'
2. 授予权限
GRANTS 权限列表 ON 数据库 表名 TO '用户名@主机名'
3. 撤销权限
REVOKE 权限列表 ON 数据库 表名 FROM '用户名@主机名'
4. 创建用户
CREATE USER '用户名@主机名' IDENTIFIED BY '密码'
ALTER USER '用户名@主机名' IDENTIFIED WHITH mysql_native_password BY '密码';
5.删除用户
DROP USER '用户名@主机名'

函数

是指一段可以直接被另一段程序调用的程序或代码，

  • 比如说数据库表中，存储的是入职日志，如2000-11-12，如何快速计算入职天数？？？
  • 数据库表中，存储的是学生的分数值。如98、75，如何快速判定分数的等级呢？

字符串函数

常用的字符串函数:

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位小数
通过数据库的函数，生成一个六位数的随机验证码
SELECT round(rand()*1000000,0);

日期函数

常见的日期函数如下：

CURDATE()     返回当前日期
CURTIME()     返回当前时间
NOW()         返回当前日期和时间
YEAR(date)    获取指定date的年份
MONTH(date)   获取指定date的月份
DAY(date)     获取指定date的日期
DATE_ADD(date，INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)   返回起始时间date1和结束时间date2之间的天数。
查询所有员工的入职天数，并根据入职天数倒序排序
select name,datediff(curdate(),entrydate) as 'days' from emp order by days desc;

流程函数

在sql语句中实现条件筛选

IF(value,t,f)          如果value为true，则返回t，否则返回f
IFNULL(value1,value2)  如果value1不为空，则返回value1，否则返回vaule2
CASE WHEN [val1] THEN [res1] ... ELSE [default] END (略)

约束

  1. 约束是作用于表中字段上的规则，用于限制存储在表中的数据
  2. 目的：保证数据库数据的正确性,有效性和完整性
  3. 分类:
非空约束   限制该字段的数据不能为NULL               NOT NULL
唯一约束   保证该字段的所有数据都是唯一，不重复的    UNIQUE
主键约束   主键是一行数据的唯一标识，要求非空且唯一   PRIMARY KEY
默认约束   保存数据时，如果未指定该字段的值，则采用默认值  DEFAULT
检查约束   保证字段值满足某一个条件                  CHECK
外键约束   用来让两张表的数据之间建立连接，保证数据的一致性和完整性FOREIGN KEY
外键删除/更新行为
NO ACTION:
- 当在父表中删除/更新对应记录时，首先检查该记录是否有对应的外键，如果有则不允许删除/更新。[与RESTRICT 一致]
RESTRICT:
- 当在父表中删除/更新对应记录时，首先检查该记录是否有对应的外键，如果有则不允许删除/更新。[与NO ACTION 一致]
CASCADE:
- 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
SET NULL: 
- 当父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null
SET DEFAULT:
- 父表变更时，子表将外键列设置成一个默认的值（Innodb不支持）

多表关系

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

一对多

案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键
场景：有一张员工表，有一张部门表，都存在主键id，一的一方肯定是部门表，所以需要在员工表建立外键，外键绑定部门表中的主键id

多对多

案例：学生与课程的关系
关系：一个学生可以选择多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键
场景：有一张课程表，有一张学生表，都存在主键id，会创建第三张表，表中记录的是主键自增id，学生外键id以及课程外键id分别关联各表的主键id；

一对一

案例：用户与用户详情的关系
关系：一对一的关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的UNIQUE.
场景：都是用户表，用户详情又包含了用户的其他信息，那么一张用户详情表拆分成两个表，然后进行关联就成为了一对一的关系，只是需要保证id是唯一的

多表查询

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

多表查询分类

连接查询

内链接
相当于查询A、B交集部分的数据
隐式内链接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...
案例：查询每个员工的姓名，及关联部门的名称
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id
显式内链接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件
案例：查询每个员工的姓名，及关联部门的名称
selct e.name,d.name from emp e inner join dept d on e.dept_id=d.id
外连接
左外链接
查询左表所有数据，以及两张交集部分数据

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件
说明：相当于查询表1(左表)的所有数据包含表1与表2交集部分的数据
案例：查找所有员工的信息和对应的部门名称信息
select e.*,d.name from emp e letf join dept d on e.dept_id=d.id
右外链接
查询右表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件
说明：相当于查询表2(右表)的所有数据包含表1与表2交集部分的数据
案例：查找部门表所有数据和对应员工的信息
select d.*,e.* from emp e right join dept d on e.dept_id=d.id
自连接
当前表与自身的连接查询，自连接必须使用表别名

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件
自连接查询，可以是内连接查询，也可以是外链接查询
案例：查询员工及其所属领导的名字
select * from emp a,emp b where a.manager_id=b.id
案例：查询所有员工emp及其领导的名字，如果员工没有领导，也需要查询出来
select * from emp a left join emp b on a.manager_id = b.id

联合查询-union,union all

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

SELECT 字段列表 FROM 表A ... UNION [ALL] SELECT 字段列表 FROM 表B ...;
案例：将薪资低于5000的员工和年龄大于50岁的员工全部查询出来
select * from emp where salary<5000
union all select * from emp where age >50;
查询的结果存在相同的元素，那么可以选择去掉all，去掉all之后也就是去重了；
select * from emp where salary<5000 union select * from emp where age >50;
注意：对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

子查询

  • 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
SELECT * FROM T1 WHERE COLUMN1 =(SELECT COLUMN1 FROM T2)
子查询的外部语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个
  • 根据子查询结果不同，分为：
  1. 标量子查询 （子查询结果为单个值）
  2. 列子查询 （子查询结果为一列）
  3. 行子查询 （子查询结果为一行）
  4. 表子查询 （子查询结果为多行多列）
  • 根据子查询位置，分为：WHERE之后，FROM之后，SELECT之后。
标量子查询

子查询返回的结果是单个值（数字，字符串，日期），最简单的形式，这种子查询称为标量子查询。 常用的操作符：= <> > >= < <=

案例:查询销售部的所有员工信息
select * from emp where dept_id=(select id from dept where name ='销售部');
列子查询
  • 子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。 常用的操作符：IN,NOTIN,ANY,SOME,ALL
  • IN 在指定的集合范围之内，多选一
  • NOT IN 不在指定的集合范围之内
  • ANY 子查询返回列表中，有任意一个满足即可
  • SOME 与ANY等同，使用SOME的地方都可以使用ANY
  • ALL 子查询返回列表的所有值都必须满足
案例:查询销售部和市场部所有员工信息
select * from emp where dept_id in (select id from dept where name ="销售部 or name ="市场部");
案例：查询所有人工资都比财务部员工工资都高的信息
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name='财务部'));
行子查询
  • 子查询返回的结果是行（也可以是多列），这种查询称为行查询 常用的操作符：= 、<> IN NOT IN
案例:查询张无忌的薪资及直属领导相同的员工信息
select * from emp where (salary,manager_id)=(select salary,manager_id from emp where name='张无忌');
表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。常用的操作符：IN

查询与鹿杖客，宋元桥的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name ='鹿杖客' or name ='宋元桥');

事务

事务简介

  • 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败
案例：比如张三向李四转账1000块钱
先查询张三账户是否有足额的资金
然后张三账户余额-1000
李四账户余额+1000
但是可能在转账的过程中出现异常，那么这个时候就需要通过事务来解决，所以上述一系列的操作可以放在同一个事务中进行，如果发生异常则回滚事务，刚开始的时候就开启事务，如果3个步骤都执行成功，则提交事务；

事务操作

  • 查看/设置事务提交方式
查看事务提交方式：SELECT @@autocommit;1则为自动提交，0则为手动提交
  • 提交事务
COMMIT;
  • 回滚事务
ROLLBACK;

事务四大特性

  • 原子性（Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么你全部失败；（比如刚刚转账案例中，要么执行转帐成功，要么转转账失败）
  • 一致性（Conssistency）:事务完成时，必须使用所有的数据都保持一致状态。（比如刚刚转账案例，那么他们加起来的总值是不会发生变化的）
  • 隔离性（Isolation）:数据库系统提供的隔离机制，保证事务不受外部并发操作影响的独立环境下运行。（比如说AB两个事务在操作的时候互相不干扰，不会相互影响，两个事务在独立的环境下运行）
  • 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。（比如说转账，对于数据的改变是永久的，相当于将结果保存到了磁盘中）

并发事务问题

  • 比如说A事务或者B事务在操作某个数据库或者某张表的时候所引发的问题。
  • 脏读：一个事务读取到了另外一个事物没有提交的数据
  • 不可重复读：一个事务先手读取同一条记录，但两次读取的数据不同，称之为不可重复读
  • 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是插入数据时，又发现这样数据已经存在，好像出现了幻影；

事务隔离级别

事务隔离级别主要解决上述出现的问题

  • 读未提交 脏读 不可重复读 幻读 都会出现
  • 读已提交 不会出现脏读问题 会出现不可重复读问题 会出现幻读问题
  • 可重复读 不会出现脏读问题 不会出现不可重复读问题 会出现幻读问题
  • 可串行化 脏读 不可重复读 幻读 都不会出现，但是性能低
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION
设置事务隔离级别
SET [SESSION|GROBAL] TRANSACTION ISOLATION LEVEL {读未提交|读已提交|可重复读|可串行化}
avatar
文章
阅读
粉丝