Mysql操作指南
概述
非关系型数据库
非关系型数据库并没有统一的存储结构标准,现常见结构有键值、文档、JSON类型等,对高性能需求设计
- 灵活的数据类型:支持多种数据模型,例如键值对、文档型、列族型和图形数据库
- 无固定的表结构:不需要预定义固定的表结构,适应非结构化或者半结构化数据
- 高性能读写:少关系型,注重高性能读写能力,适用于大规模数据和高并发访问模型
关系型数据库
数据按照类别进行存储,每个类别存储到一个容器中,,表和表之间可以建立关系,可以进行关联操作,性能相对一般
- 结构化数据模型:数据以表格形式存储,具有固定的结构
- ACID事务:通过强大的事务支持,保证数据的原子性、一致性、隔离性和持久性
- 丰富查询语句:支持SQL语句,能够进行复杂的关联数据查询
- 数据一致性:数据的关系和约束确保数据的一致性和完整性
特点总结
关系型数据库适用于需要强一致性和复杂查询的场景
非关系型数据库适用于需要高性能、灵活性和分布式处理场景
数据库的选择
- 绝大部分应用采用混合模式,二者结合使用
- 程序主体数据一般存储在关系型数据库(主)
- 程序缓存数据和高并发数据存储到非关系型数据库(辅)
关系型数据库存储设计规则(模型)
遵循ER模型
E(Entity)代表实体类别,关系型数据库中一类数据,对应数据库中的一张“表”存储
R(Relationship)“表”和“表”可以维护某种关系,可以通过“关系”进行多表操作
模型解释
数据中最大的存储单位是库
每类数据存储一张“表”中,“表”存储到“库”中
“表”具有一些特性“列”,这些特性“列”定义数据在表中如何存储
“表”中的数据时按照“行”存储的,一“行”即为一条记录
数据库管理系统概述
概述
数据库管理系统(DataBase Management System,DBMS)
指一种操作和管理数据库的大型软件,用户通过数据库管理系统操作数据库中的数据
常见关系型数据库管理系统
- Oracle:行业标杆、大型项目、企业许可收费昂贵
- MySQL:中小项目、企业开源免费
- DB2:IBM产品生态、金融项目
- SQLite:小型嵌入式、移动设备、企业开源免费
安装官网
MySQL_Downloads (https://www.mysql.com/downloads/)
MySQL启动
net start mysql80
net stop mysql80
客户端连接
- 直接使用MySQL提供的工具
- 以管理员身份运行命令行窗口,这种方法需要配置path环境变量(粘贴MySQL bin路径下的地址)
结构化查询SQL
数据库操作方式
- 图形化界面(GUI)应用程序:拥有图形化界面直接进行操作
- 命令行界面(CLI)/后台服务应用程序:用户通过在命令行或终端输入文本命令进行交互
结构化查询语句(SQL)
SQL:Structure Query Language (结构化查询语言)
SQL被国际化标准组织(ISO)采纳为关系型数据库语言的国际标准,可以使用SQL命令进行关系型数据库操作
SQL概述
SQL包括了所有对数据库的操作,主要是由数据定义、数据操纵、数据查询、数据控制、事务控制等SQL语言的使用规定组成
基础和重点
创建和修改盛放数据的容器(数据定义DDL)
表中添加、修改、删除数据(数据控制DML)
表中数据多条件查询(数据查询DQL)
扩展和提高
事务启动、提交和回滚(事务控制TCL)
账号创建、权限控制(数据控制DCL)
DDL——>DML——>DQL——>TCL——>DCL
基础指令
SQL语句以分号结尾,可以逐行书写
先打开cmd命令窗口(以管理员身份运行)
连接mysql服务命令
登录用户名>mysql-u <username> -p<password> -h <hostname> <databasename>
参数含义:
-u <username>
:用于指定你要连接的MYSQL数据库的用户名-p<password>
:表示密码,后边紧跟着密码,中间没有空格 如果你不希望在命令行中显示密码,可以不指定密码,直接-p,然后在提示下手动输入密码
-h <hostname>
:用于指定MySQL服务器的主机名或者IP地址。如果MySQL在本地运行,可以使用localhost-p <port>
:用于指定连接MySQL服务器的端口号,默认情况下是3306<databasename>
:你要连接的数据库名称,连接后会默认使用这个数据库
其它基础指令
SQL注释
单行注释:
#注释内容
单行注释:
-- 注释内容
其中–后面必须要有空格多行注释:
/ * 注释内容 * /
版本和退出连接
–查看版本,命令符号之间用空格隔开,每条命令后使用;结束否则不执行
mysql>select version();
–退出连接
mysql>exit;
数据定义DDL
数据库操作
查询
查询所有数据库
SHOW DATABASES( );
查询当前数据库
SELECT DATABASE( );
创建
CREATE DATABASE [ IF NOT EXITS ] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
必须大写删除
DROP DATABASE[ IF EXISTS ] 数据库名;
使用
USE 数据库名;
不区分大小写
表操作-创建&查询
查询
查询当前数据库所有表
SHOW TABLES;
查询表结构
DESC 表明;
查询指定表的建表语句
SHOW CREATE TABLE 表名;
创建
CREATE TABLE 表名(
字段1 字段1类型 COMMENT 字段1注释,
字段2 字段2类型 COMMENT 字段2注释,
字段3 字段3类型 COMMENT 字段3注释,
……
字段n 字段n类型 COMMENT 字段n注释
) COMMENT 表注释;
注意:[…]为可选参数,最后一个字段后面没有逗号
表操作-修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
删除字段
ALTER TABLE 表名 DROP 字段名;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
表操作-删除
删除表
DROP TABLE[IF EXISTS] 表名;
删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
注意:在删除表时,表中的全部数据也都会被删除
数据操作DML
概述
Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增删改操作
- 添加数据(INSERT)
- 修改数据(UPDATE)
- 删除数据(DELETE)
DML-添加数据
给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,…)VALUES(值1,值2,…);
给全部字段添加数据
INSERT INTO 表名 VALUES(值1,值2);
批量添加数据
INSERT INTO 表名(字段名1,字段名2,…)VALUES(值1,值2,…)(值1,值2,…)(值1,值2,…);
INSERT INTO 表名 VALUES(值1,值2,…)(值1,值2,…)(值1,值2,…);
注意:
- 插入数据时,指定的字段顺序需要与值的顺序一一对应
- 字符串和日期型数据应该包含在引号中
- 插入数据的大小,应该在字符规定的范围内
DML-修改数据
更新数据
UPDATE 表名 SET 字段名1=值1,字段名2=值2,…WHERE 条件;
注意:修改语句的条件可以有也可以没有,如果没有条件,则会修改整张表的数据
删除数据
DELETE FROM 表名 WHERE 条件
注意:
- DELETE语句的条件可以有也可以没有,没有条件会删除整张表的数据
- DELETE语句不能删除某一个字段的值(使用UPDATE代替)
数据查询DQL
概述
Data Query Language ,数据查询语言,用来查询数据库中表的记录
DQL-基本查询
查询多个字段
SELECT 字段1,字段2,字段3…FROM 表名;
SELECT * FROM 表名;
设置别名
SELECT 字段1 [AS 别名1],字段2[AS 别名2]…FROM 表名;
去除重复记录
SELECT DISTINCT 字段列表 表名;
DQL-条件查询
基础语法
SELECT 字段列表 FROM WHERE 条件列表;
条件
比较运算符 功能 BETWEEN…AND… 在某个范围之内(含最大值,最小值) IN(…) 在in之后的列表中的值,多选一 LIKE 占位符 模糊匹配(_匹配单个字符,%匹配任意个字符) IS NULL 为空
DQL-聚合函数
将数据作为一个整体,进行纵向运算
常见的聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 语法
SELECT 聚合函数(字段列表) FROM 表名;
注意:null值不参与所有聚合函数的运算
DQL-分组查询
语法
SELECT 字段列表 FROM 表名 WHERE 条件 GROUP BY 分组字段名 HAVING 分组过滤后的条件
WHERE和HAVING的区别
执行时机不同:where是在分组之前进行过滤的,不满足where条件,不参与分组;having是在分组之后对结果进行过滤
判断条件不同:where不能对聚合函数进行判断,而having可以
DQL-排序查询
语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
排序方式
ASC:升序(默认值)
DESC:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行升序
DQL-分页查询
语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意
起始索引从0开始,起始索引=(查询页码-1)*每页显示的记录数
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
如果查询的是第一页数据,起始索引可以省略,直接简写为limit+查询记录数
数据控制DCL
Data Control Language 数据控制语言,用来管理数据库用户、控制数据库访问权限
DCL-管理用户
查询用户
USE mysql;
SELECT * FROM user;
创建用户
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
修改用户密码
ALTER USER ‘用户名’@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
删除用户
DROP USER ‘用户名’@‘主机名’;
DCL-权限控制
MySQL中定义了很多种权限,常用的有以下几种
权限 | 说明 |
---|---|
ALL,ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 修改数据库/表 |
- 查询权限
SHOW GRANTS FOR ‘用户名@‘主机名’;
- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;
- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;
函数
SELECT 聚合函数(字段列表) FROM 表名;
字符串函数
函数 | 功能 |
---|---|
CONCAT(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个长度字符串 |
数值函数
函数 | 功能 |
---|---|
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,INRTERVAL 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] …[default] END | 如果expe的值等于val1,返回res1,否则返回default默认值 |
约束
概述
- 概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据
- 目的:保证数据库中数据的正确、有效性和完整性
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一的、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY (自增AUTO_INCREMENT) |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束 | 保证字段满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立联系,保证数据的一致性和完整性 | FOGEIGN KEY |
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束
外键约束
外键用来让两张表的数据之间建立链接,从而保证数据的一致性和完整性
语法
- 添加外键
CREATE TABLE 表名(
字段名 数据类型,
…
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERNCES 主表(列表名);
- 删除外键
ALTER TABLE 表名 DROP FOGREIGN KEY 外键名称;
删除/更新行为
行为 说明 NO ACTION 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新 RESTRICT 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新 CASCADE 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新外键在子表中的记录 SET NULL 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null SET DEFAULT 父表有变更时,子表将外键列设置成一个默认的值 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段)REFERENCES 主表名(主表名字段)ON UPDATE 行为 DELETE 行为
多表查询
多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模板之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多
- 多对多
- 一对一
多表查询概述
概述:指从多张表中查询数据
笛卡尔积:笛卡尔积是指两个集合A和B的所有组合情况
- 在多表查询中,需要消除无效的笛卡尔积(WHERE 关键字)
分类
- 连接查询
内连接:相当于查询A、B交集的数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用别名
- 子查询
内连接
隐式内连接
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查询,就是把多次查询的结果合并起来,形成一个新的查询结果集
SELECT 字段列表 FROM 表A
UNION[ALL]
SELECT 字段列表 FROM 表B…;
子查询
概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称为子查询
SELECT * FROM t1 WHERE column1 =(SELECT column1 FROM t2);
子查询外部语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个
根据子查询结果的不同,分为:
标量子查询(子查询结果为单个值)
列子查询(子查询结果为一列)
行子查询(子查询结果为一行)
表子查询(子查询结果为多行多列)
根据子查询位置,分为WHERE之后、FROM之后、SELECT之后
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式
常用操作符:= <> > >= < <=
列子查询
子查询返回的结果是一列(可以是多行)
常用操作符
操作符 描述 IN 在指定的集合范围之内,多选一 NOT IN 不在指定的集合范围之内 ANY 子查询返回的列表中,有任意一个满足即可 SOME 与ANY等同,使用SOME的地方都可以使用ANY ALL 子查询返回列表的所有值都必须满足
行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询
常用的操作符:=、<>、IN、NOT IN
表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询
常用的操作符:IN
事务
概述
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
默认MySQL的事务时自动提交的,也就是说,当执行一条DML语句时,MySQL会立即隐式的提交事务
相关操作
查看/设置事务提交方式
SELECT @@autocommit;
查看事务的自动提交方式,一为自动提交,零为手动提交SET @@autocommit=0;
提交事务
COMMIT;
回滚事务
ROLLBACK;
四大特性
原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败
一致性(Consistency):事务完成时,必须使所有的数据保持一致状态
隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
持久性(Durability):事务一旦完成提交或回滚,它对数据库中的数据的改变就是永久的
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条数据,但两次读取的数据不同,称之为 不可重复读 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,有发现这行数据已经存在了 |
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | ✔ | ✔ | ✔ |
Read committed | × | ✔ | ✔ |
Repeatable Read(默认) | × | × | ✔ |
Serializable | × | × | × |