IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
图片自动播放器
↓图片自动播放器↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> 复习MySQL:简述及简单使用(navicat) -> 正文阅读

[大数据]复习MySQL:简述及简单使用(navicat)

一、简述MySQL

MySQL 是最流行的关系型数据库管理系统之一,由Oracle公司开发、发布和支持。关系型数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
为了提高存储速度,结构化数据是有组织的存放在物理文件中。数据库、表格、视图、行和列等对象的逻辑模型提供了灵活的编程环境。你可以在指向不同表格的不同数据字段中设置对应关系的规则,如一对一、一对多、唯一、必须和可选。数据库强制执行这些规则,因此,在一个设计良好的数据库中,应用程序永远不会看到不一致的、重复的或者孤立的、过期或者丢失的数据。

备注:
1、SQL:Structure Query Language(结构化查询语言)简称SQL。
2、、MySQL所使用的 SQL 语言是用于访问数据库的最常用【标准化语言】
3数据库管理系统可以通过SQL管理数据库;定义和操作数据,维护数据的完整性和安全性。

数据库的特点:

  1. 持久化存储数据;其实数据库就是一个文件系统
  2. 方便存储和管理数据
  3. 使用了统一的方式操作数据库。(sql语句)

二、连接数据库

这里使用的数据库软件是 navicat
数据库版本是 8.0.26
使用navicat连接数据库
在这里插入图片描述

三、创建数据库(了解)

说明1:
存储引擎:存储引擎是服务于存储服务的,通过存储引擎将数据保存。就跟计算机如何将数据保存到磁盘中一样,在数据库中,存储引擎的意思就是通过何种引擎【将数据存储在磁盘中】。
常用的有两种:InnoDB和MylSAM
——InnoDB:MySQL 8.0中的默认存储引擎。 InnoDB是用于MySQL的事务安全(兼容ACID)的存储引擎,具有提交,回滚和崩溃恢复功能以保护用户数据。 InnoDB行级锁定(无需升级为更粗粒度的锁定)和Oracle风格的一致非锁定读取可提高多用户并发性和性能。InnoDB将用户数据存储在聚集索引中,以减少基于主键的常见查询的I / O。为了保持数据完整性, InnoDB还支持FOREIGN KEY引用完整性约束。【总结:安全性好,事务处理及用户操作数据表】
——MyISAM:这些表占用的空间很小。 表级锁定 限制了读/写工作负载中的性能,因此它通常用于Web和数据仓库配置中的只读或只读工作负载中。【节约空间及响应速度】
mysql常用引擎

说明2:在数据的存储上,MySQL提供了不同的字符集支持。而在数据的对比操作上,则提供了不同的字符序支持。
字符集(character set):定义了字符以及字符的编码。
——utf8:utf8 是 Mysql 中的一种字符集,只支持最长三个字节的 UTF-8字符,也就是 Unicode 中的基本多文本平面。
——utf8mb4.:mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。可以简单的理解 utf8mb4 是目前最大的一个字符编码,支持任意文字。
如果实际用途上来看,可以给要用到emoji的库或者说表,设置utf8mb4.

字符序(collation):定义了字符的比较规则。
utf8mb4_unicode_ci比较准确
utf8mb4_general_ci 速度比较快。通常情况下 utf8mb4_general_ci 的准确性就够我们用的了
utf8和utf8mb4的区别

这里我们选择InnoDB引擎、utf8mb4和utf8mb4_general_ci

操作数据库(了解)(后面可以使用可是化操作来创建数据库)
1、【创建】数据库——【create】 database if not exits +数据库名
2、【删除】数据库:【drop】 database +[if exists] +数据库名;
3、【使用/切换】数据库 :【use】 +数据库名 ;
4、【查看】 所有的数据库 :【show】 +databases;

在这里插入图片描述
在这里插入图片描述

四、创建数据表(设置字段)

1、删除表
drop table [if exists] 表名
2、创建表(并设置存储引擎)
语法:

create table [if not exists]  `表名`(
`字段名` 列类型 [属性] [索引] [注释]
`字段名` 列类型 [属性] [索引] [注释]
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

例1:

--创建一个用户表
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `password` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`) --主键
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

例2:
在这里插入图片描述

【 表的字段属性说明】

  1. Unsigned:无符号的整数,
    ·声明该列后【不能为负数】
  2. zerofill:【0填充的】
    ·不足的位数,使用0填充,int(3), 5—005
  3. Auto_InCrement :【自增】:
    ·自动在上一条记录上+1(默认)
    ·用来设计唯一的主键~index,必须是整数类型
    ·可以自定义设计主键自增的起始值和步长
  4. NULL和NOT NULL 空与非空
    默认为NULL,即没有插入该列的数值
    如果设置为NOT NULL,则该列必须有值,如果不填写值 就会报错
  5. DEFAULT :默认的
    ·用于设置默认值
    ·例如,性别字段,默认为"男”,否则为“女”;若无指定该列的值﹐则默认值为"男"的值
  6. comment’…’ ----注释信息…
  7. 主键primary key (`id`)一般一个表只有1个,放到最后面
  8. COLLATE :对于mysql中那些字符类型的列,如VARCHAR,CHAR,TEXT类型的列,都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。COLLATE通常是和数据编码(CHARSET)相关的,一般来说每种CHARSET都有多种它所支持的COLLATE,并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci,GBK编码的默认COLLATE为gbk_chinese_ci,utf8mb4编码的默认值为utf8mb4_general_ci。

附加说明

  1. 字段名称尽量使用` `括起来
  2. 字符串用单引号‘’括起来
  3. 所有的语句后面加(英文的,) 最后一个不用加

附:【可视化操作】查看创建的数据库/表语句

1、查看创建的数据库语句
show create database +数据库名
在这里插入图片描述
【建议:如果想跟我创建一样的数据库可以使用下面的语句(创建跟我一样的后面的举例也运行来看看效果)】

CREATE DATABASE `db001` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */

2、查看创建的数据表语句(同上)
show create table +表名
在这里插入图片描述
【建议:创建跟我一样的数据表,方便后面查看运行效果】

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `age` int DEFAULT NULL,
  `password` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `Math` int DEFAULT '0',
  `English` int DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

五、alter table修改表的字段(列)

1、修改表名
alter table 旧表名 【rename as】 新表名
2、添加字段
alter table 表名 【add】 字段名 +列属性

alter table 表名  add age  int11

3、修改表的字段
——3.1修改表的约束类型(列属性)
alter table 表名 【modify】 字段名 新的列属性[]

alter table 表名  modify age  varchar11

——3.2修改表的字段名
alter table 表名 【change】 旧名字 新名字 (新的)列属性[]

alter table 表名 change  age age1 int(10)

说明:字段重命名包括修改约束、但是一般只用来重命名
4、删除表的字段
alter table 表名 【drop】 字段名(列)

六、 MySQL数据管理

DML:数据操作语言

DML(database manage language):数据库操作语言
作用:操作表中的值/数据

0、建议:插入我准备的数据

插入我准备的数据,后面可以同样运行看看效果

1、插入数据【insert into】

insert into 表名(字段名1,字段2,字段3) values(‘值1’),(‘值2’),(‘值3’),(…)
注意事项:

  1. 字段与字段之间使用英文逗号,末尾不能以逗号结尾
  2. 可以插入多条数据value(), (), ()
  3. 如果不写表的字段,从主键开始,默认一一匹配
-- 插入单独字段名,及对应内容
INSERT INTO `user`(`name`) VALUE('小明'),('小红'),('小白')
-- 插入字段及多个内容多条内容
INSERT INTO `user`(`name`,`password`) VALUE('小黑','123'),('小紫','456')

2、更新/修改数据

语法:【update】 表名 【set 】列名=新的值 where [条件]
注意:必须添加条件,否则会将对应表的字段中所有的数据修改
【条件】:where +条件
条件的返回值为Boolean类型 如果为ture才执行
where可以 通过多个条件定位数据,无上限 &&(and) 与 ||(or)

比较 = < > <= >=
不等于 :<> 或 !=
在某个范围内 [2,5] :between …and …
与: and 、&&
或 :or
非:NOT 或 !

在这里插入图片描述

--修改单个属性
update `user`  set `name`='长江七号'  where id = 1 
--修改多个字段属性 用逗号隔开
update `user`  set `name`='长江六号' ,`password`= '8888' where id = 2 

3、删除数据

语法:【delete】 from 表名 where 条件
注:如果不添加条件,会导致对应表所有的数据被删除

--删除指定数据
DELETE FROM `user` WHERE id = 3
--删除该数据表的所有数据
DELETE FROM `user`

DQL 数据库查询语言【最重要】

Select完整语法

select [distinct]
		字段名1 [as +别名], ...
from  表名
	

在这里插入图片描述
DQL是数据库中最核心的语言,最重要的语句,使用频率最高

基础查询:

语法:
select +字段
from +表名
备注:*代表全部字段,如果想查询多个字段可以用 , 隔开

1、查询多个字段

-- 查询学生信息,*代表全部 (不过公司可能不让使用——写注释)
SELECT * FROM `user`

-- 查询(多个)指定字段
SELECT `id`,`name`FROM `user`

2、字段注释

以后假如查询的字段非常多,一般会使用注释来说明(这样就会显得想要查询的字段非常清晰,下面只是列举两个)
在这里插入图片描述

3、相同数据去重 distinct

SELECT DISTINCT username FROM user
备注:去重只会【去除完全重复的数据】
1、如果你查询的是username字段,不会显示重复的名字
在这里插入图片描述
2、如果你查询的是全部字段,仍然会显示两个重复的名字(因为他们的id不同)
在这里插入图片描述

4、起别名 as

可以给表、给字段起别名
作用 :
1、可以显示到查询的数据上面
2、如果涉及重复使用且多个表的查询,可以通过表的别名来使用表

SELECT username as 用户名, `password` as 密码 from user  as u

在这里插入图片描述

5、显示的结果拼接字符串——函数 :concat(a,b)

可以用于拼接字符串并且显示到查询出来的数据中
注”字符串用’‘ 包围
在这里插入图片描述

6、计算多列的值

备注1:两个字段相加 不可以在左右两边加上`` 否则会报错,因为这是一个表达式,而不是字段

SELECT `name`,`Math`,`English`,Math + English as 总分 FROM `user`	

在这里插入图片描述
备注2:null与任何值计算都为null,但是我们想要的总分是100
——使用函数ifnull(a,b)
a代表需要判断是否为null的数据
b代表替换值

SELECT `name`,`Math`,`English`,Math + IFNULL(English,0)as 总分 FROM `user`	

在这里插入图片描述

条件查询:

语法 : where +条件

比较 = < > <= >=
不等于 :<> 或 !=
在某个范围内 [2,5] :between …and …

与: and 、&&
或 :or
in(集合)
非:NOT 或 !
like

–备注:null不能使用等号去判断
is null 判断为null
is not null 判断不为null

1、常规运算符查询

--比较运算
SELECT * FROM `user` WHERE age > 5
SELECT * FROM `user` WHERE age >= 5
SELECT * FROM `user` WHERE age = 5
SELECT * FROM `user` WHERE age != 5


SELECT * FROM `user` WHERE age >= 5 && age <= 7 -- 和between..and等价
SELECT * FROM `user` WHERE age >= 5 and age <= 7 -- 和between..and等价
SELECT * FROM `user` WHERE age BETWEEN 5 and 7 -- [5,7]

SELECT * FROM `user` WHERE age = 5 or age = 7 or age = 9 -- 等价于下面的in
SELECT * FROM `user` WHERE age in (5,7,9)

--备注:null不能使用 = 和 != 去判断
SELECT * FROM `user` WHERE English = null -- 错误
SELECT * FROM `user` WHERE English is null --正确

2、like(模糊查询)

【占位符】
% 代表0到任意个字符
_ 代表一个字符

查询以小开头的用户名
在这里插入图片描述
查询 以小开头(小后面只有1个字)
查询 以小开头(小后面只有2个字)
在这里插入图片描述
查询名字是2个字的人

SELECT * FROM user WHERE name like '__'

在这里插入图片描述

排序查询

语法 :order by +子句
order by排序字段1排序方式1,排序字段2排序方式2…

排序方式:
ASC:升序,默认的。
DESC:降序。

注意:
*如果有多个排序条件,则当前边的条件值―样时,才会判断第二条件。

SELECT * FROM user ORDER BY Math  
SELECT * FROM user ORDER BY Math  ASC  -- 等价于上方
SELECT * FROM user ORDER BY Math  DESC
-- 优先按照第一个排序方式排序,假如第一个数据相同,再按照第二个排序方式排序
SELECT * FROM user ORDER BY Math  DESC, English asc

在这里插入图片描述

聚合函数

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

  1. count :计算个数
  • 一般选择非空的列:主键
  • count(*)
  1. max :计算最大值
  2. min :计算最小值
  3. sum:计算和
  4. avg :计算平均值
    *注意:聚合函数的计算,会排除null值。
    解决方案︰
    1、选择不包含非空的列进行计算(id或者*)
    2、IFNULL函数
select count(id) FROM user
select count(*) FROM user -- 不推荐

select max(math) FROM user
select min(math) FROM user
select avg(math) FROM user
select sum(math) FROM user

select count(ifnull(English,0)) FROM user

在这里插入图片描述

分组group by和过滤 having

分组查询:
1.语法︰group by 分组字段;

-- 按照性别分组,查询男女同学的数学平均分
SELECT sex ,avg(math) FROM `user` GROUP BY sex
SELECT sex ,avg(math) ,count(id) as 人数 FROM `user` GROUP BY sex
-- 按照性别分组,查询男女同学的数学平均分,要求:分数大于60才参与筛选, 
SELECT sex ,avg(math) ,count(id) as 人数 FROM `user` where math >60 GROUP BY sex
-- 按照性别分组,查询男女同学的数学平均分,要求:分数大于60才参与筛选, 分组之后,人数要大于2人
SELECT sex ,avg(math) ,count(id) as 人数 FROM `user` where math >60 GROUP BY sex having 人数 > 2

注意:分组之后查询的字段:分组字段、聚合函数
在这里插入图片描述

where和 having 的作用差不多,那where和 having 的区别?
1、 where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
2、where后不可以跟聚合函数,having可以进行聚合函数的判断。

在这里插入图片描述

分页查询

limit 开始的索引, 每页查询的条数

备注:开始的索引=(当前的页码- 1)*每页显示的条数

SELECT * FROM `user` limit 0,3  -- 第1页
SELECT * FROM `user` limit 3,3  -- 第2页
SELECT * FROM `user` limit 6,3  -- 第3页

在这里插入图片描述

联表查询

准备工作:导入数据

-- 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20)
);
-- 插入部门表数据
INSERT INTo dept(NAME)VALUES ('开发部'),('巿场部'),('财务部');

-- 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date Date, -- 入职日期
dept_id INT,
FOREIGN KEY(dept_id) REFERENCES dept(id) -- 外键,关联部门表(部门表的主键)
);
-- 插入员工表数据
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (1, '孙悟空', '男', 7200, '2013-02-24', 1);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (2, '猪八戒', '男', 3600, '2010-12-02', 2);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (3, '唐僧', '男', 9000, '2000-08-08', 2);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (4, '白骨精', '女', 5000, '2015-10-07', 3);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (5, '蜘蛛精', '女', 4500, '2011-03-14', 1);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (6, '金蝉子', '男', 4000, NULL, NULL);

如果我们直接查询两个表,会出现两个表所有的id组成情况
(笛卡尔积:有两个集合A,B .取这两个集合的所有组成情况。)
因此,要完成【多表查询】,需要【消除无用的数据】
在这里插入图片描述

1、内连接

1.1 隐式内连接:使用where条件消除无用数据

在这里插入图片描述

1.2 显式内连接:[inner] join

语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件
在这里插入图片描述

2、外连接

这里用主表的概念比较好理解 left 就是主表在左边 right主表在右边
左外连接:查询的是左表所有数据以及其交集部分。
语法:
SELECT 字段列表
FROM 表1
left [outer] join 表2
on 条件

演示:

SELECT * FROM emp 
SELECT * FROM emp left join dept  on dept_id = dept.id

在这里插入图片描述

在这里插入图片描述
右外链接:查询的是右表所有数据以及其交集部分。
语法:
SELECT 字段列表
FROM 表1
right outer join 表2
on 条件

补充:约束

概念︰对表中的数据进行限定,保证数据的正确性、有效性和完整性。
分类:

  1. 非空约束:not null
    在这里插入图片描述

  2. 唯一约束:unique
    在这里插入图片描述

  3. 主键约束: primary key(非空且唯一),一般是id
    在这里插入图片描述
    自动增长
    在这里插入图片描述

  4. 外键约束:foreign key(不推荐数据库使用,了解即可)
    表的拆分(部门表和员工表)
    为了避免删除部门表导致员工表的dep_id失效,会添加外键约束
    作用:(dept_id和部门表产生关联)
    目的:(当员工表的人和部门表相关id产生关联时,不得删除)
    在这里插入图片描述
    在这里插入图片描述
    备注:加上constraint 外键名是为了给这个约束命名,后面的修改和删除比较方便 ,也可以不给约束命名

 foreign key(员工的外键) referance 关联/引用 的表名(字段名)

阿里巴巴的JAVA规范:
在这里插入图片描述
最佳实践:
数据库就是单纯的表 只用来行(数据)和列(字段)
我们想使用多张表的数据,想使用外键(利用程序去实现)
现在都是有外键列,但不设置外键约束,至于保证数据的正确性,就通过程序逻辑保证了

  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技:还在苦于ETL?Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch:如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2022-05-05 11:25:14  更:2022-05-05 11:28:22 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年11日历 -2024/11/24 1:25:07-

图片自动播放器
↓图片自动播放器↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码