linux数据库基本介绍--个人笔记

鸳鸯蝴蝶
122 阅读14分钟

一、认识数据库

1、什么是数据库

数据库(Database)指长期存储在计算机内、有组织的数据集合。简而言之，数据库就是一个存储和管理数据的地方。

2、作用

  • 对数据进行持久化的保存
  • 方便数据的存储和查询，速度快，安全，方便
  • 可以处理并发访问
  • 更加安全的权限管理访问机制

3、常见的数据库

数据库按照数据存储格式的不同可以分为关系型数据库和非关系型数据库

常用关系型数据库

  • Oracle：在大型项目中使用，例如：银行、电信等项目
  • MySQL：Web 项目中使用最广泛的关系型数据库
  • Microsoft SQL Server：在微软的项目中使用
  • SQLite：轻量级数据库，主要应用在移动平台

常用非关系型数据库

  • Redis：开源，可做数据库、缓存、消息中间件和队列
  • Mongodb：面向文档的开源数据库系统，将数据存储为文档
  • Hbase：大数据存储

关系型数据库(二维表)

  • RDMS：(Relational Database Management System)关系型数据库系统。
  • 将数据间的关系以数据库表的形式加以表达，并将数据存储在表格中，以便于查询。

关系型数据库核心要素

  • 数据行（一条记录）

  • 数据列（字段）

  • 数据表（数据行的集合）

  • 数据库（数据表的集合，一个数据库中能 够有 n 多个数据表）

4、SQL介绍

概念：SQL：Structured Query Language（结构化查询语言），通过SQL语言可以对数据库进行操作。

SQL语言分类:

  • DQL：数据查询语言，用于对数据进行查询，例如：select
  • DML：数据操作语言，对数据进行增加、修改、删除，例如：insert、update、delete
  • TPL：事务处理语言，对事务进行处理，例如：begin transaction、commit、rollback
  • DCL：数据控制语言，进行授权与权限回收，例如：grant、revoke
  • DDL：数据定义语言，进行数据库、表的管理等，例如：create、drop
  • CCL：指针控制语言，通过控制指针完成表的操作，例如：declare cursor

注意：主流的关系型数据库都支持SQL语言，在MySql中SQL语言不区分大小写

5、MySQL介绍

MySql是一个关系型数据库，由瑞典MySql AB公司开发，后被Sun公司收购，Sun公司后来又被Oracle收购。目 前MySql属于Oracle的产品。

官网：www.mysql.com/

MySql产品分为社区版和商业版本，社区版本免费使用。

MySQL特点

  • 1、可移植性好
  • 2、支持多操作系统
  • 3、支持多种编程语言
  • 4、开源、社区版免费
  • 5、支持多线程
  • 6、优化SQL查询算法
  • 7、多种数据库连接方式
  • 8、支持多语言编码格式

二、MySQL安装及组成

MySQL安装: 详细安装步骤，请查看MySQL安装手册。

MySQL组成：

  • MySQL服务器：存储数据并解析编译后的SQL语句，将执行结果返回给客户端。
  • MySQL客户端：下发用户要执行的SQL语句，并显示服务器返回的执行结果。

MySQL连接-命令行模式

注意：

  • MySQL服务器安装完成之后，默认就已经安装了命令行客户端。
  • 在连接MySQL服务器之前需要知道MySQL服务器的IP地址及对应的端口(默认3306)。
  • Windows服务器可以通过ipconfig查看IP地址，Linux中可以通过ifconfig查看IP地址。

连接命令：mysql -hhost -P端口号 -u用户名 -p密码

说明： host表示服务器的IP地址或者域名,不填则表示为本机。-P端口号不加时则表示默认3306端口

MySQL连接-工具连接

说明：MySQL客户端工具有很多种，DBeaver用的比较多，还有Navicat、mysql workbench等。

MYSQL基础理论(以DBeaver为例)

数据库的操作

  • 使用数据库：在DBeaver窗口中，双击左侧已连接 的连接名称即可打开并连接到数据库
  • 创建数据库：打开数据库后，右击 连接名称，选择“创建 - 数据库”，然后输入对应的数据，点击确定。
  • 修改数据库：打开数据库后，右击要修改的数据库名称，选择 “查看数据库”。
  • 删除数据库：打开数据库后，右击 要删除的数据库名称，选择 “删除”

数据表的操作-创建表

操作步骤

  • 1：选择已打开的数据库，右击 “创建 - 表”,填写表名。
  • 2：在新窗口中，右键“新建列”，输入所需要的字段名称、字段类型、长度等相关数。
  • 3：点击“保存”按钮，在弹出SQL预览框中，点击 “执行”即可。

数据表的操作-修改、删除表

修改表(设计)操作

  • 入口：右击表名，选择“查看表 –属性”
  • 修改字段
  • 增加字段
  • 删除字段

删除表操作

  • 入口：右击表名，选择“删除”表删除后，表及表中的内容全部被清除。

数据的操作

新增数据

  • 1、右击表名，选择“查看表 – 数据”
  • 2、点击下方的 +号，输入数据
  • 3、点击下方的 Save

修改数据

  • 1、右击表名，选择“查看表 – 数据”
  • 2、在表中修改对应的数据
  • 3、点击下方的 Save

删除数据

  • 1、右击表名，选择“查看表 – 数据”
  • 2、在表中选中对应数据行
  • 3、点击下方的减号

数据类型与约束

1、常用数据类型

字符串：varchar(0~65533)

一个字母或一个中文点一个字符,例如：varchar(3) 表示最多存3个字符

整型：int

有符号范围(-2147483648 ~2147483647)

无符号范围(0 ~ 4294967295)

小数：decimal

示例：decimal(5, 2)，表示该字段可以存5位数，其中小数位数为2，整数为3

时间日期：datetime 范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59）

示例： 2021-01-01 12:29:59

2、数据约束

数据约束主要是指对数据库中数据进行限制，以确保数据的正确性、有效性、一致性。

常见数据约束有：

  • 主键（primary key）：物理上存储的顺序
  • 非空（not null）：此字段不允许填写空值
  • 唯一（unique）：此字段的值不允许重复
  • 默认值（default）：当不填写此值时会使用默认值，如果填写时以填写为准
  • 外键（foreign key）：维护两个表之间的关联关系

命令操作数据库

操作数据库

说明：操作数据库之前要选通过工具连接到数据库。

常见数据库操作命令：

查看所有数据库：show databases;

使用数据库：use 数据库名;

查看当前使用数据库：select database();

创建数据库：create database 数据库名 charset=utf8;

删除数据库：drop database 数据库名;

操作数据表

说明：操作数据表之前要先通过use打开对应的数据库。

常见数据表操作命令：

查看当前数据库所有的表：show tables;

查看表结构：desc 表名;

查看表的创建语句：show create table 表名;

三、 SQL语言

数据库表操作

  1. 创建数据库表

语法格式： create table 表名 (

字段名1 类型 约束,

字段名2 类型 约束,

…… …. )

例：create table students (name varchar(10));

  1. 删除数据库表

语法格式： drop table if exists 表名

例： drop table if exists students;

数据的增删改操作

添加数据

说明：主键自增长，可以用0或null代替。

添加一行数据

方式一: insert into 表名 values(…)

例1: insert into students values(0, '张三', 22, 177.56);

方式二: insert into 表名 (字段1, 字段2, …) value(值1, 值2, …)

例: insert into students (name) values ('张三');

添加多行数据

方式一: 写多条insert语句，多条语句之间用英文分号分隔

例：

  1. insert into students(name) value ('张三');
  2. insert into students(name) value ('李四');
  3. insert into students values(0,'王五',23,167.56);

方式二: 通过一条insert语句插入多条数据，数据间用逗号分隔

格式一: insert into 表名 values (...), (...) ...

例: insert into students values(0,'张三',23,167.56),(0,'李四',23,167.56);

格式二: insert into 表名(字段名1,...) value(值1,...),(值1,...)...

例: insert into students(name) value ('张三'),('李四');

删除数据

delete：可以通过where子句删除部分记录。删除所有数据时，自增长字段不会从1开始。

语法格式: delete from 表名 where 条件

例:删除id为6的学生数据

delete from students where id=6;

truncate：删除数据时，表结构会保留，自增长字段从1开始。执行效率低于drop命令。

语法格式：truncate table 表名

例:删除学生表的所有数据(保留表结构)

truncate table students;

drop：如果想删除表，建议使用drop，且删除数据效率最高。

语法格式：drop table 表名

例:删除学生表(包括所有数据和表结构)

drop table students;

修改数据

语法格式： update 表名 set 字段名1=值1,字段名2=值2... where 条件

例:修改id为5的学生数据，姓名改为张三，年龄改为 20

update students set name='张三',age=20 where id=5;

数据查询操作

1. 简单查询

查询全部字段：select * from 表名;

例： select * from students;

查询部分字段：select 字段名称1,字段名称2,... from 表名; select name from students;

起别名
  • 关键字：as
  • 作用：给表名、字段名起别名
    • 表名--起别名语法格式：表名 as 别名
    • 字段名--起别名语法格式：字段名 as 别名
  • 使用场景：
    • 对表名起别名，主要用作表关联
    • 对字段名起别名，主要用作执行结果字段的中文展示
  • 例如：
  1. select stu.name as 姓名,stu.sex,stu.hometown from students as stu;
去重
  • 关键字： distinct
  • 语法格式：distinct 字段名称
  • 作用：针对某一个字段去除掉重复的数据
  • 使用场景：通过结合聚合函数中的"查询总记录数"一起使用，用来统计某一个字段有几种数据

语句格式：distinct 字段名称

例如：select distinct(hometown) from students;

2. 条件查询：根据一定的条件去查询数据的结果。

语法格式:

select 字段1,字段2... from 表名 where 条件;

  • 例:
  1. select * from students where studentNo = 001;

说明: where支持多种运算符进行条件处理

比较运算：>、<、=、>=、<=、!=或<>

例如：select * from students where studentNo != 001;

逻辑运算：not、or、and

例如：

  1. select * from students where not studentNo = 001;

模糊查询：like

例如：select * from students where name like '_三%';

范围查询：in(非连续范围)、between 开始范围 and 介绍范围

例如：

  1. select * from students where age in (18,20,22);

例如：

  1. select * from students where age between 18 and 20;

空判断

注意:Mysql中空表示null，与 ‘’ (空字符)是不一样的。

判断为空: is null

例：查询没有填写身份证的学生

select * from students where card is null;

判断非空: is not null

例：查询填写了身份证的学生

select * from students where card is not null;

3.排序

语法格式： select * from 表名 order by 字段名1 asc|desc, 字段名2 asc|desc,...

例1：查询所有学生信息，按年龄从小到大排序

select * from students order by age;

例2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序

select * from students order by age desc,studentNo;

说明:

将行数据按照字段1进行排序，如果某些字段1的值相同时，则按照字段2排序，以此类推

默认按照列值从小到大排列

asc从小到大排列，即升序

desc从大到小排序，即降序

4.聚合函数
  • 使用聚合函数方便进行数据统计
  • 聚合函数不能作为 where 条件
  • 聚合函数能作为 having 条件

聚合函数：count(*):查询总记录数

  • max(字段名):查询最大值
  • min(字段名):查询最小值
  • sum(字段名):求和
  • avg(字段名):求平均数

例1：查询学生总数

select count(*) from students;

例2：查询女生的最大年龄

select max(age) from students where sex='女';

例：查询1班的最小年龄

select min(age) from students WHERE class = '1班';

示例：查询北京学生的年龄总和

select sum(age) from students where hometown='北京';

例：查询女生的平均年龄

select avg(age) from students where sex='女'

5. 分组、分页

分组查询

  • 按照字段分组，此字段相同的数据会被放到一个组中
  • 分组的目的是对每一组的数据进行统计(使用聚合函数)

语法格式:

select 字段1,字段2,聚合函数... from 表名 group by 字段1,字段2...

select class,sex,count(*) from students group by class,sex;

分组后的数据筛选

  • where 是对 from 后面指定的表进行数据筛选， 属于对原始数据的筛选。
  • having 是对 group by 的结果进行筛选。
  • having 后面的条件中可以用聚合函数，where后面不可以。
  • where必须在group by前面，having必须在group by后面。

语法格式：select 字段1,字段2,聚合... from 表名 group by 字段1,字段2,字段3...having 条件

说明: 关键字having后面的条件运算符与where的相同

例1：查询男生总人数

方案一:select count(*) from students where sex='男';

方案二:

  1. select sex,count(*) from students group by sex having sex=‘男’;

例2：查询每个班级男生的总记录数

  1. select class,sex,count(*) from students group by class,sex having sex='男';

例3：查询所有班级中男生的人数大于1的记录数

  1. select class,sex,count(* ) from students where sex='男' group by class,sex having count(* )>1;

分页-获取部分数据

  • 使用场景: 用来获取一部分的数据或者用来分页

语法格式： select * from 表名 limit start,count

说明: 从start开始，获取count条数据start索引从0开始

例1：查询前3行学生信息

  • select * from students limit 0,3;
  • select * from students limit 3;

分页实现

Limit典型应用场景是分页查询：

select * from students limit (n-1)*m, m

说明: n表示显示第几页的数据 m表示每页显示多少条数据

注意：(n-1)*m, m 是公式，并不是语法格式,不能直接写在SQL语句中

6. 连接查询
内连接
  • 结果为两个表匹配到的数据 语法格式:
  1. select * from 表1
  2. inner join 表2 on 表1.列=表2.列
  1. 例如：select * from students stu
  2. inner join scores sc on stu.studentNo = sc.studentno;
外连接
  • 左连接：结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null 填充
select * from 表1
left join 表2  on 表1.关联字段a = 表2.关联字段b;
例如：
select * from students stu
	left join scores sc on stu.studentNo = sc.studentno
	left join courses cou on cou.courseNo = sc.courseNo;
  • 右连接：结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null 填充
select * from 表1
right join 表2  on 表1.关联字段a = 表2.关联字段b;
例如：
select * from students stu
	right join scores sc on stu.studentNo = sc.studentno
	right join courses cou on cou.courseNo = sc.courseNo;
自关联
    • 使用场景：当表存在等级关系时，查询其中数据需要使用自关联
    • 比如：省，市，县
    • 比如：总经理，部门主管，组长，组员
  • 自关联思路：
    • 把一张表当成多张表来使用 说明：将表定义为不同的别名
    • A表的字段与B表的字段进行关联 强调：一定是表中不同的字段

-- 例3：查询河南省的所有的市区

select * from areas a1
	inner join areas a2 on a1.aid = a2.pid
	left join areas a3 on a2.aid = a3.pid
	where a1.atitle = '河南省';
子查询
  • 嵌入在其他查询语句中的select语句称为子查询，其他的查询语句称之为主查询。
  • 子查询辅助主查询，要么充当条件，要么充当数 据源。
  • 子查询是一条完整的、可单独执行的select查询语句。

子查询-充当条件

例1：查询王昭君的成绩，要求显示成绩（标量子查询）

  1. select score from scores where studentNo = (select studentNo from students where name = '王昭君');

例2：查询18岁的学生的成绩，要求显示成绩（列子查询）

  1. select score from scores where studentNo in (select studentNo from students where age=18);

例3：查询和王昭君同班、同龄的学生信息（行子查询）

  1. select * from students where (class,age)=(select class,age from students where name='王昭君');

子查询-充当数据源

例1：查询数据库和系统测试的课程成绩

  1. Select * from scores s inner join (select * from courses where name in ('数据库','系统测试')) c on s.courseNo = c.courseNo;
avatar
文章
阅读
粉丝