[大数据]【mysql笔记 2022.9.27】

数据库概念

• 按照一定的数据结构来组织、存储和管理数据的仓库。

数据库管理系统

• 操纵和管理数据库的大型软件，用于创建、使用和维护数据库，简称DBMS

关系型数据库（RDBMS）

• 概念：关系型数据库是建留在关系模型的基础上，由多张相互连接的二维表组的数据库
• 特点：使用表存储数据， 格式统一，便于维护；使用SQL语句，标准统一，使用方便；数据存储在磁盘中，断电也不会影响数据安全。

非关系型数据库（NoSQL）

• 概念：Not-Only SQL,泛指非关系型数据库，是对关系型数据库的补充
• 特点：数据结构灵活；伸缩性强

SQL概念

• 结构化查询语言（Structured Query Language）
• 是一种操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准

Mysql 版本

• Mysql社区版：免费，MYSQL不提供技术支持
• Mysql商业版：收费，可免费适用30天
• 本文已安装8.0.26为例，如果要安装最新版，需要先卸载旧版

Mysql安装

• 选择默认的开发者模式
  
• 检查环境，点击继续
  
• 执行安装包含的组件
  
• 安装完成，耗时5分钟左右
  
• 继续点击下一步
  
• 默认端口3306，无需改动，点击继续
  
• 选择默认的推荐安装，继续点击下一步
  
• 配置密码，点击下一步
  
• 继续下一步
  
• 继续点击执行
  
• 安装完成
  

Mysql启动

方式一

• 调出系统服务（mysql安装完成后自动注册为启动服务）
  
  

方式二

• CMD命令行 启动 net start mysql80
• CMD命令行 停止 net stop mysql80
  

Mysql客户端连接

方式一

• 使用MYSQL提供的客户端连接工具
  
• 已登录
  

方式二

• windows命令行执行命令，使用这种方式时，需要配置PATH环境 变量
• 编辑PATH环境变量
  
• 复制以下路径
  
• 添加环境变量
  
• 语法格式为：mysql [127.0.0.1] [-P 3306] -u user -p
• 命令行登录：
  

方式三

• 第三方图形化工具，如Navicat
  

MYSQL数据模型

• 通过DBMS操作数据库
  

MYSQL 图形化操作

• sqlyog、navicat 、datagrip （三者中功能最强大）
• 通过图形化操作软件，也可自行编写sql进行数据库操作
• 创建数据库，右键数据库点击新建
  
  
• 创建表
  
  
• 创建完成
  
• 右键 设计表即可
  
• 进入命令行界面，右键进入命令列界面
  

SQL

SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾
• SQL语句可以使用空格或缩进（空格和缩进的个数没有限制）来增强语句的可读性
• Mysql数据的SQL语句部分大小写，关键字建议使用大写
• 注释
• 单行注释：“–”只是内容或者“#”（MYSQL特有）
• 多行注释：“*/“或”/*”

SQL分类

DDL

• 数据库定义语言（DATA Definition Language）,用来定义数据库对象（数据库，表，字，索引）

DDL-数据库操作

查询

• 查询所有数据库：show databases;
  
• 查询的当前数据库：select database();

创建

• 创建数据库：create datatbase [if not exists] 数据库名 [default charset 字符集] [coliate 排序规则];（方括号内容可选）
  
• 如果数据库存在，创建时会有提示
  
• 如果不想报错，存在不创建，不存在则创建
  
• 字符集设置，如果要设置字符集不建议设置UTF8，存储长度只有3字节，一些特殊字符需要4个字节
  

删除

• drop database [if exists] 数据库；
  

使用

• use 数据库；
  
• 数据库的切换直接用use切换

DDL- 表操作

查询

• 查询当前数据库所有表 show tables;
  
• 查询表结构 desc 表名;
  
• 查询指定表的建表语句 show create table 表名;
  

创建

• create table 表名（字段1 字段1 类型 [COMMENT 字段1注释] ， 。。。。。（最后一个字段后面没有逗号）
• ）[COMMENT 表注释]；
  

数据类型

• 数据类型很多，主要分三类：数值类型，字符串类型，日期时间类型

数值类型

• 有符号即出现负数
• DECIMAL精度和标度：如小数123.45精度为5，位数为5；标数为2，小数点后有两位
• 描述年龄可选用无符号的TINYINT :age TINYINT UNSIGNED
• 描述分数可选用DOUBLE，数字长度最长4位，小数为最多1位：score DOUBLE(4,1)

字符串类型

• 带BLOB是描述二进制数据的，如视频音频，软件安装包等，因为放在数据库中不方便管理且性能不高，经常会采用专门的文件服务器管理
• char字符：如果设置char(10),10是最多存储10个字符，但即是只有1个字符，也会占满，其余9位用空格补位
• vachar字符：类似VMware的精简置备，varchar(10)，存储多少个字符占多少空间。
• char性能相对varchar来说性能会更好，因为varchar多了一步根据内容计算空间。

日期时间类型

案例

• 根据需求创建表(设计合理的数据类型、长度）
• 设计一张员工信息表：
• 1 编号(纯数字）
• 2 员工工号(字符串乐行，长度不超10位）
• 3 员工姓名(字符串类型，长度不超10位）
• 4 性别 (男或女，仅存储一个汉字）
• 5 年龄
• 6 身份证号(18位2代身份证）
• 7 入职时间(年月日）
• 建表语句如下
  
  

修改

• 添加字段：ALTER TABLE 表名 ADD 字段名 类型(长度）[COMMENT 注释] [约束];
  
• 修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度）[COMMENT 注释] [约束];
  
• 修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;
  
• 修改表名:ALTER TABLE 表名 RENAME TO 新表名;
  

删除

• 删除字段：ALTER TABLE 表名 DROP 字段名;
  
• 删除表：DROP TABLE [IF EXITS] 表名;
  
• 删除指定表，并重新创建该表(只有表结构，没有数据):TRUNCATE TABLE 表名;
  

DML

• 数据库操作语言语言（DATA Manipulation Language），用来对数据库中表的数据记录进行增改删操作

添加数据 insert

• 给指定字段添加数据：INSERT INTO 表名（字段名1，字段名2，…）VALUES(值1，值2…);
• sql语句添加查询
  
  
• 报错演示
  
• 图形化工具查询
  
• 给全部字段添加数据：INSERT INTO 表名 VALUES（值1（对应表中的第一个字段），值2…)；
  
• 批量添加数据：INSERT INTO 表名（字段名1，字段名2，…）VALUES(值1，值2),(值1，值2…)(值1，值2)…;
  INSERT INTO 表名 VALUES（值1（对应表中的第一个字段），值2…),值1（对应表中的第一个字段），值2…)…；
  
• 注意：插入数据时，指定的字段顺序需要和值的顺序是一一对应的；字符串或日期类型应该包含在引号中；插入的数据大小，应该在字段的规定范围内

修改 update

• update 表名 SET 字段名=值1,字段名=值2,…[where 条件];
• 注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
• 修改单个值
  
• 修改多个值
  
  
• 修改整张表
  
  

删除 delete

• delete from 表名 [where 条件];
  
  
• 删除所有

• delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的数据。
• delete语句不能删除某一个字段的值（可以使用update）

DQL

• 数据库查询语言（DATA Query Language)，用来查询数据库中表的记录

语法结构

• select 表名
• from 表名列表
• where 条件列表
• group by 分组字段列表
• having 分组后条件列表
• order by 排序字段列表
• limit 分页参数

基本查询

查询多个字段

• select 字段1,字段2…from 表名;
  
• select * from 表名(也可把所有字段都罗列出);
  

设置别名

• select 字段1 [as 别名1], 字段2 [as 别名2] … from 表名;
  

去除重复记录

select distinct 字段列表 from 表名;

条件查询（where）

语法

• select 字段列表 from 表名 where 条件列表;

条件

示例

• 查询年龄为22岁的
  
• 查询年龄小于等于24的
  
• 查询没有id号码的，新添加数据没有idcard的阿花数据
  
• 查询有idcard
  
• 查询年龄不等于24岁的
  
• 查询年龄在20-23之间的，包含20和23
  
• between and用法
  
• 性别为女，且年龄小于28岁的
  
• or用法
  
• in 用法
  
• 新增三个字的用户信息小小勇，查询名字为3个字的员工
  
• 查询idcard最后一位是9的
  

聚合函数（count、max、min、avg、sum）

介绍

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

常见聚合函数

语法

• select 聚合函数(字段列表) from 表名;
• 注意 所有null值不参与所有聚合函数运算。

示例

• 求员工数量
  
• 统计平均年龄
  
• 求取最大或最小年龄
  
• 统计北京地区的年龄之和
  

分组查询（group by）

语法

• select 字段 from 表名 [where 条件] group by 分组字段 [having 分组后过滤条件];

where和having区别

• 执行时机不通:where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
• 判断条件不同:where不能对聚合函数进行判断，而having可以。.
• 执行顺序：where>聚合函数>having
• 分组之后，查询的字段一般为聚合函数和分组字段，查询的其他字段无任何意义。

示例

• 根据性别分组，统计男性和女性的数量
  
• 根据性别分组，统计男女的平均年龄
  
• 查询年龄小于25的员工，并根据工作地址分组，获取员工数量少于2个的地址。
  

排序查询（order by）

语法

• select from 表名 order by 字段1 排序方式，字段2 排序方式 2;

排序方式

• ASC:升序(默认值)
• DESC:降序
• 如果多个字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

示例

• 根据年龄进行升序排序，asc可省略。
  
• 根据年龄进行升序排序，对年龄相同的，按照入职时间进行降序排序
  

分页查询（limit）

语法

• select 字段 from 表名 limit 起始索引,查询记录数;
• 注意：起始索引是从0开始，起始索引=(查询页码-1)乘以每页显示记录数；分页查询时数据的方言，不同数据库呦呦呦不同的实现，mysql中是limit；如果查询的是第一页数据，起始索引可以省略，直接简写为limit 查询记录数

示例

• 查询第一页的员工数据，每页展示3条
  
• 查询第二页员工数据，每页展示3条
  

DQL总结示例

• 查询性别为男，年龄在24以上的并且年龄是三个字的员工
  
• 查询年龄小于25，男性和女性的数量
  
• 查询所有年龄小于26的员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同，按入职时间降序排序
  
• 查询性别为男，且年龄在24以上的2个员工信息对查询的结果按年龄升序，年龄相同的按入职时间升序排序
  
• 注意limit放在最后。

DQL语言编写和执行顺序

编写顺序

• select 字段 from 表 where 条件列表 group by 分组字段 having 分组后条件列表 order by 排序字段列表 limit 分页参数

执行顺序

DQL语句总结

• select 字段 [as] 别名（as可省略）

执行顺序 验证

DCL

• 数据库控制语言（DATA Control Language),用来创建数据库用户，控制数据库的访问权限

管理用户

• 主机名可以使用%表示通配
• 这类sql开发人员操作较少，主要是DBA使用

查询用户

• mysql数据中的用户信息和用户权限信息都存放在mysql数据库中的user表
  

创建用户

• create user ‘用户名’@‘主机名’ identified by ‘密码’；只能在本机登录
  
  
• 使用创建的用户登录，但并未分配权限
  
  
• 创建用户可以在任何主机上登录
  
  

修改用户密码

• alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’;
  
• 再次登录测试，密码修改成功
  

删除用户

• drop user ‘用户名’@‘主机名’;
  
  

权限控制

• 多个权限之间，使用逗号分隔；授权时，数据库名和表名可以使用*进行通配，代表所有。
• mysql中定义了多种权限，常用如下

查询权限

• show grants for ‘用户名’@‘主机名’;
  
• usage表示没有任何权限，仅仅能登录mysql

授予权限

• grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
  

撤销权限

• revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;
  
• 再次查看