MySQL day01

最新推荐文章于 2024-10-16 15:26:08 发布

程序员小许CL

最新推荐文章于 2024-10-16 15:26:08 发布

阅读量121

点赞数

文章标签： mysql 数据库 big data

本文链接：https://blog.csdn.net/qq_45618899/article/details/120172529

版权

MySQL day01

1 数据库系统引言

数据库系统是用来组织、存储和管理数据的系统。可以对其存储的数据执行新增、删除、修改和查询的操作，并提供了保障数据完整性的事务机制以及容灾备份的能力。

1.1 为什么要学习数据库

数据库核心的功能是存储数据，以此为基础提供了操作数据的各种功能。那么文件系统同样可以存储数据，为什么不直接使用文件保存数据？文件系统在保存数据方面是存在一些问题的，而这也正是我们使用数据库管理数据的原因：

针对文件进行增删改查操作繁琐且低效
没有数据类型，文件中的数据都是字符串
缺乏对大数据集的优化，大文件操作速度会非常慢
缺乏并发访问的支持，多个用户不能同时操作同1个文件
缺乏权限校验机制：身份认证、操作授权等等
没有备份容灾机制，机器一旦宕机存在极大的数据丢失风险

从上世纪50年代末的理论研究开始，数据库历经了60多年的发展。数据库的数据模型从一开始的层面模型，网状模型，关系模型，到对象模型，对象关系模型，半结构化等等。一些模型已经是昨日黄花被时代抛弃，而有一些仍然处于理论研究阶段尚未落地，目前为止行业主流数据模型仍是关系模型。

1.2 数据库的分类

企业应用开发中使用到的数据库可以分为2种：传统的关系型数据库和新兴的NoSQL（Not Only SQL）型数据库。

	关系型数据库	NoSQL数据库
存储的数据结构	结构化的数据，以表为单位存储数据	半结构化的数据（xml、json）非结构化的数据（文档、文本、图片、视频、音频）
事务（保证数据完整性的手段）	强事务	弱事务
性能	高并发下性能较差	高并发下性能较好

实战开发时，关系型数据库和NoSQL数据库相辅相成。

关系型数据库：对事务要求比较高的数据，例如金融类数据
NoSQL型数据库：对事务要求低，对性能要求高的数据，例如聊天记录、商品详情

常见的数据库产品：

关系型数据库

Oracle、DB2、SQLServer、MySQL、SQLite、H2
NoSQL型数据库

Redis、MongoDB、HBase

行业背景：

互联网技术在近 10 多年发生了翻天覆地的变化，最早是以 IOE 架构为代表，即：IBM 的服务器、Oracle 数据库、EMC 存储设备。IOE 几乎是全世界大公司的“黄金搭档”。国内的银行、电信、证券这些不差钱的行业很喜欢 IOE。这些传统大企业的业务规模变化稳定，IOE 足够支撑日常业务需求，公司本身又有足够的 IT 预算。

直到互联网公司崛起，互联网公司的业务增长已经不再是传统公司的线性模式，而是指数级爆发式的增长。为了应对这种业务增长速度，服务海量的用户，最早像谷歌、Amazon、Facebook、阿里等等国内外的互联网巨头纷纷选择用 X86 通用服务器、开源数据库 MySQL，以及分布式存储代替 IOE 架构，经过 10 多年的发展，这股风潮已经逐渐从互联网企业蔓延到传统企业。

我们的课程中首先学习关系型数据库MySQL，而后续课程中学习NoSQL型的Redis。

2 数据库的安装和使用

2.1 数据库的工作模型

数据库采用是客户端-服务器工作模型。

单机工作模型

软件同一时刻只能为一个用户提供服务，如果有多个用户需要使用，必须在使用软件的每一台电脑上安装软件。典型软件有office、输入法、视频播放器…

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vqXJFYEK-1631064768809)(MySQL day01.assets/image-20210502215519005.png)]
客户端-服务器工作模型

软件同一时刻可同时为多个用户提供服务，即使有多个用户需要使用也不需要多次安装。只需要在一台电脑上安装软件后即可对外提供服务（所以称为服务端），用户只需要在各自的电脑上安装用于连接服务器的客户端软件即可。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q7DbkSfp-1631064768811)(MySQL day01.assets/image-20210518092907240.png)]

2.2 数据库和客户端的安装

见视频操作

2.3 客户端的使用

2.3.1 Navicat的使用

建立连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bvZSFrp4-1631064768816)(MySQL day01.assets/image-20200325150210070.png)]
内置数据库简介

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qpuZcWSm-1631064768817)(MySQL day01.assets/image-20200325150815343.png)]

注意：MySQL用户下，是数据库，数据库中保存表。
双击打开某一个数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mbirWXlp-1631064768817)(MySQL day01.assets/image-20200325151403808.png)]
执行sql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y6LXQYuk-1631064768818)(MySQL day01.assets/image-20200325151817432.png)]

2.3.2 MySQL Client （命令行）

添加环境变量

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aB5VgAqB-1631064768818)(MySQL day01.assets/image-20200325153605807.png)]
打开黑窗口，输入命令
```
>mysql -u root -p
>enter password: root
```
注意：如果登录失败，会有以下错误提示

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5TUGnpEC-1631064768819)(MySQL day01.assets/image-20200325145112875.png)]
使用命令执行sql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5g1gkl0J-1631064768819)(MySQL day01.assets/image-20200325145354338.png)]

2.4 导入测试数据

为方便学习我们导入测试数据，此时我们新建数据库，将数据添加到数据库中即可！！！

新建数据库[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PK5NwCMv-1631064768820)(MySQL day01.assets/image-20210503230212643.png)]
导入sql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RSGEPSv4-1631064768821)(MySQL day01.assets/image-20210503230847596.png)]

3 数据库的一些概念

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sYguAK5e-1631064768821)(MySQL day01.assets/image-20210504202555451.png)]

表

表是数据库组织、存储数据的基本单位，表中保存着多行多列的数据。不同类型的数据保存到不同的表中，比如:用户信息保存在user表中，学员信息保存在student表中，员工信息保存在employees表中。
行

行是表中存储数据的基本单位，一行数据通常包含多个字段（列）。向表中添加数据，再少要添加一行。
字段（列）

一个字段用来描述一行数据的某个特征，是一行数据的组成部分。比如：用户要包含用户名、密码等内容，user表中就有用户名、密码这些字段（列）。
主键

本质上就是特殊的字段：用来在一张表中唯一的标识一行数据的字段。主键要求：唯一、不能为null
外键

也是一个特殊的字段：用来记录表和表之间关系的列，比如：员工一定有其所属的部门，那么employees员工表和departments部门表就存在关系，为了记录员工的部门，就可以在员工表中定义一列department_id保存自己所属部门的id（对应着departments中的department_id主键），这样员工表department_id就记录了员工和部门的关系。employees表的 employee_id就是一个外键。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5dM7KQpx-1631064768822)(MySQL day01.assets/image-20210504204725260.png)]
数据库

为了方便对表管理，MySQL中通过库管理表，也就是说表又存在于数据库中。举个例子：一个表好比是一个excel文件，数据库就好比是保存excel文件的文件夹。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UH4OcTjm-1631064768822)(MySQL day01.assets/image-20210504205932987.png)]

4 查询SQL

SQL（Structured Query Language，结构化查询语言），是用于访问和处理数据库的标准的计算机语言，核心功能是提供了对表中数据增、删、改、查操作。

需要注意的是：SQL是一种国际标准，并不为哪个数据库独有，最普及的是SQL92标准。所有的关系型数据库都支持SQL，并在标准语法外提供了各自的私有扩展（不用过于担心私有扩展带来的碎片化和学习成本的增加，私有扩展只是很少的一部分）。绝大多数的SQL语句可以不用修改的在任何数据库正确运行。

4.1 简单的查询

查询部分列

语法：select 列名1,列名2,...
     from 表名;
示例：查询员工的编号、姓名、邮箱和薪资
select employee_id,first_name,email,salary
from employees;

查询所有列

语法：select *
      from 表名;
示例：查询员工的所有信息
select *
from employees;

另外一种查询所有列：
select 列名1,列名2,...，最后一个列名 
from 表名;
实战开发时推荐，显式写出列名的方式：可读性好,易于维护

数学运算

语法：
select 数字列名+数字,数字列名-数字,数字列名*数字,数字列名/数字,数字列名%数字
from 表名;
示例：查询薪资，并基于薪资进行计算
select salary,salary+100,salary-100,salary*12,salary/30
from employees;

别名

select 列名 as 别名,列名2 as 别名2
from 表名;
示例：查询月薪、年薪、日薪并取别名
select salary as 月薪,salary*12 as 年薪,salary/30 as 日薪
from employees as e;

注意：列名起别名时，as可以省略
示例：
select salary as 月薪,salary*12 as 年薪,salary/30 as 日薪
from employees as e;

去重

语法：
select distinct 列名1,列名2,...
from 表名;
示例：查询员工表中的工种和部门信息，并对数据进行去重
select distinct job_id,department_id 
from employees;
注意：查询出的所有列相同才算相同

case when

语法：
case
       when 条件1 then 结果1
         when 条件2 then 结果2
         when 条件3 then 结果3
           ...
         else 其他结果  
     end 
类似于java中if（条件）{结果}else

示例：查询薪资，并对薪资进行等级判定
select salary,
         case 
            when salary >= 15000 then '高薪'
            when salary >= 10000 then '中等'
            when salary >= 7000 then '一般'
            else '垃圾'
         end 薪资等级
from employees;

查询表详情

查询一个表的信息：
describe 表名; 
示例：查看employees表的信息
	describe employees;
注意：describe 可以简写为 desc

4.2 条件查询

4.2.1 单条件查询

语法：
select 列名,...
from 表名
where 条件;

比较运算符： >  <  >=  <=   =   != 
示例：查询薪资大于等于8000的员工
select * 
from employees
where salary >= 8000;

--查询薪资小于等于15000的员工
select *
from employees
where employee_id <= 150;


注意：mysql针对字符串比较时默认不区分大小写，如要区分大小写添加关键字 binary
select *
from employees
where first_name = 'steven';

select *
from employees
where binary first_name = 'steven';

4.2.2 多条件查询

语法：使用and或者or连接多个条件
and  连接条件，连接的所有条件必须同时成立
or  连接条件，连接的条件任意一个成立即可以

示例：查询薪资大于等于8000且薪资小于等于15000的员工
select * 
from employees
where salary >= 8000 and salary <= 15000;

--查询薪资大于等于8000或者员工编号大于等于170的员工
select * 
from employees
where salary >= 8000 or employee_id >= 170

4.2.3 区间查询

语法 between 起始值 and 截止值
示例：查询薪资在8000~15000范围的数据
select * 
from employees
where salary >= 8000 and salary <= 15000;
-- 改造为范围查询：
select * 
from employees
where salary between 8000 and 15000;

-- 查询薪资不在8000~15000之间的数据
select *
from employees
where salary < 8000 or salary > 15000;

select *
from employees
where salary not between 8000 and 15000;

4.2.4 枚举查询

语法：列 in(值1，值2，值3，...)
-- 查询 60 90 100 部门的员工
传统写法：
select *
from employees
where department_id = 60
or department_id = 90
or department_id = 100;

in写法：
select * 
from employees
where department_id in (60,90,100);

-- 不是 60 90 100 部门的员工
select *
from employees
where department_id not in (60,90,100);

4.2.5 空值查询

--查询没有提成的员工
select *
from employees
where commission_pct is null;

--查询有提成的员工
select *
from employees
where commission_pct is not null;

注意：null的比较要使用is

4.3 模糊查询

语法： select *
from 表名
where 列名 like '匹配模式';
匹配模式的构成：
 % 表示n个任意的字符
 _ 表示1个任意的字符
 
--查询first_name 以A开头的员工
select *
from employees
where first_name like 'A%';

--查询first_name 包含a的员工
select *
from employees
where first_name like '%a%';

--查询first_name第3个字符为a的员工
select *
from employees
where first_name like '__a%';
--注意：示例中使用的是2个下划线

4.4 排序

在查询数据时，通常需要根据某些列的值进行排序显示。

语法：

select 列名

  from 表名

  where 条件

  order by 列名  [asc(默认值，升序)|desc(降序)]；

4.41 单列排序

--根据薪资排序 
select *
from employees
order by salary asc ;

select *
from employees
order by salary desc;

--条件查询和排序联合使用
--对salary>=8000的员工按照薪资降序排列
select *
from employees
where salary >= 8000
order by salary desc;

4.4.2 多列排序

语法：
select 列名,...
from 表名
where 条件
order by 列名1 排序规则，列名2 排序规则,... 

排序效果：整体先根据列1进行排序，如果列1值相同时，再根据列2值排序，以此类推。
示例：
--先根据薪资降序排列，然后薪资相同根据id升序排列
select * 
from employees
order by salary desc,employee_id asc;

5 函数

MySQL内置了很多函数，提供了特定的功能。

5.1 单行函数

单行函数：作用到表中的每一行数据，表中有多少行，就得到多少行新结果。

5.1.1 concat(列名,…)

concat()用于拼接多列的值

select concat(first_name,last_name) 姓名
from employees;

select concat(first_name,'-',last_name) 姓名
from employees;

5.1.2 mod(m,n)

mod()等同于数学运算中的%，标准的SQL中并不支持直接使用%，使用mod()更有通用性。

select mod(5,2);
相当于Java中的5%2

5.1.3 length(数据)

length()获取数据的长度。

select first_name,length(first_name)
from employees;

select *
from employees
where length(first_name) = 6;

5.1.4 now() sysdate()

now()和sysdate()用于获取系统时间。

select now(),sysdate()
from employees;

注意：now()和sysdate()后可以省略from表
select now(),sysdate();

5.1.5 str_to_date(str,format)

str_to_date将日期格式的字符串转换为日期数据。

日期格式： 
	%Y：代表4位的年份 
	%y：代表2为的年份

	%m：代表月, 格式为(01……12) 
	%c：代表月, 格式为(1……12)

	%d：代表月份中的天数,格式为(00……31) 
	%e：代表月份中的天数, 格式为(0……31)

    %H：代表小时,格式为(00……23) 
    %k：代表 小时,格式为(0……23) 

	%i： 代表分钟, 格式为(00……59) 

    %s：代表 秒,格式为(00……59)
--将'2020-01-06 10:20:30' 字符串转换为相应的日期数据
SELECT str_to_date('2020-01-06 10:20:30','%Y-%m-%d %H:%i:%s')

5.1. 6 date_format(date,format)

date_format(date,format)将日期和时间数据转换为指定的字符串。

--将系统时间按照 年-月-日 时:分:秒 的格式输出字符串
SELECT date_format(now(),'%Y-%m-%d %H:%i:%s');

5.2 组（多行）函数

组函数：作用到一组（多行）数据，每组数据得到一个结果。一张表没有显式分组前，默认当做一个组处理。

sum(列)

获取某一列的总和
avg(列)

获取某一列的平均值
max(列)

获取某一列的最大值
min(列)

获取某一列的最小值
count(列)

获取某一列有值（会忽略null）的个数

select sum(salary),
	avg(salary),
	max(salary),
	min(salary),
	count(salary),
	count(commission_pct),
	count(*)
from employees;

注意：
通常使用count(*)统计表中数据的行数。

重点：

条件查询
- 等值比较是=
- 多条件查询and或者or连接
模糊查询
- like关键字
- %和_的含义
排序
- order by关键字
- asc|desc排序规则
单行函数
- concat
- length
- now
组函数
- count(*)

5.2 组（多行）函数

组函数：作用到一组（多行）数据，每组数据得到一个结果。一张表没有显式分组前，默认当做一个组处理。

sum(列)

获取某一列的总和
avg(列)

获取某一列的平均值
max(列)

获取某一列的最大值
min(列)

获取某一列的最小值
count(列)

获取某一列有值（会忽略null）的个数

select sum(salary),
	avg(salary),
	max(salary),
	min(salary),
	count(salary),
	count(commission_pct),
	count(*)
from employees;

注意：
通常使用count(*)统计表中数据的行数。

重点：