数据库入门 自用

数据库简介

测试主要工作：1.数据校验（查询）  2.适当准备数据和清理数据

数据库：存放数据的仓库

数据库分类

关系型数据库

RDMS:(Relational Database ManagementSystem关系型数据库系统。

将数据间的关系以数据库表的形式加以表达，并将数据存储在表格中，以便于查询。

常用关系型数据库:

• Oracle:在大型项目中使用，例如:银行、电信等项目

• MySQL:Web项目中使用最广泛的关系型数据库

• Microsoft SQL Server:在微软的项目中使用

• SQLite:轻量级数据库，主要应用在移动平台

非关系型数据库  

• redis

• mongodb

• hbase

关系型数据库核心要素

数据行(一条记录)

数据列(字段)

数据表(数据行的集合)

数据库(数据表的集合，一个数据库中能够有n多个数据表)

SQL的分类:

SQL是一个结构化的查询语言，通过SQL能够对数据库进行相关的操作

• 🌟DQL:数据查询语言，用于对数据进行查询，例如:select

data query language

• 🌟DML:数据操作语言，对数据进行增加、修改、删除，例如:insert、update、dele

data manipulation language

• TPL:事务处理语言，对事务进行处理，例如:begin transaction、commit、rollba

transaction processing language

• DCL:数据控制语言，进行授权与权限回收，例如:grant、revoke

data control language

• 🌟DDL:数据定义语言，进行数据库、表的管理等，例如:create、drop

data definition language

• CCL:指针控制语言，通过控制指针完成表的操作，例如:declare cursor

cursor control language

mysql安装及组成

mysql服务器：存储数据并解析编译后的sql语句，讲执行结果返回客户端

mqsql客户端（终端，工具navicat）：下发用户要执行的sql语句，并显示服务器返回的执行结果

🌟命令连接MySQL数据库

前置条件:

确定mysql数据库的IP地址

可以通过ifconfig来确认

确认mysql数据库服务是否开启

netstat-anptu|grep3306

连接命令:

mysql  -h数据库IP    -P端口号    -u数据库登陆用户名  -p数据库登陆密码

mysql -uroot -p123456 本机登录数据库

-h不加时则表示为本机

-P不加时则表示默认3306端口

exit  退出

🌟工具navicat

临时关闭防火墙：systemctl stop firewalld

新建连接 本地ip地址 端口22 连接

新建数据库  数据库名test-db（尽量英文）  

  字符集 utf8mb4  排序规则utf8mb4_0900_ai_ci

操作表   点击表 右击新建表，填写字段信息（添加字段），保存填写表名，确定。

右击表名  打开表(双击表名)：填写表中真正的数据  设计表：修改表的字段  删除表

🌟数据类型和约束

数据类型

整数 int  年龄无负数，无符号

小数 decimal  可以存5位数，小数位数2 整数3位

字符串 varchar  一个字母，一个中文  varchar(3)最多存3个字符

通过命令来写要加单引号  ‘姜美’

时间日期 datetime  2021-01-01

数据约束

主键(primarykey)：物理上存储的顺序

当把某个字段设定为主键之后，该字段的值必须唯一，且不能为空

针对主键的字段，可以设定自动自增，自动在原来最大的ID值上加1

非空(notnull)：此字段不允许填写空值

  空(Null)和"空字符是不一样的

唯一(unique)：此字段的值不允许重复

默认值(default)：当不填写此值时会使用默认值，如果填写时以填写为准

外键(foreignkey)：维护两个表之间的关联关系

数据库备份和还原

备份： 右击数据库-转储sql文件-结构和数据-选择保存路径

还原：右击数据库-运行sql文件-选择备份文件-开始

命令行操作数据库

常用命令

查看所有数据库:show databases;

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

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

创建数据库:create database数据库名charset=utf8删除数据库:drop database数据库名;

sql语句

创建删除数据库表

create table 表名（

字段名  类型  约束，

……

）

• CREATE TABLE students(

• id INT UNSIGNED PRIMARY KEY auto_increment,

• name VARCHAR(20),

• age INT UNSIGNED,

• height DECIMAL(5,2)

• )

navicat  数据库右击 新建查询  

drop table 表名;

drop table if exists 表名;

-- 注释

选中要注释的内容 ctrl+斜杠

🌟添加数据insert

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

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

• 例: insert into students values(0,'亚瑟',22,177.56)；

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

• 例: insert into students(name) values('老夫子')；

其他的不填就默认空

插入多行数据

• 例: insert into students values(0,'亚瑟',22,177.56)，(0,'张三',21,173.2)；

添加删除数据 update 

语法格式:

update 表名 set 字段名1=值1,字段名2=值2…where 条件

例:修改id为5的学生数据，姓名改为狄仁杰，年龄改为20

update students set name=‘狄仁杰‘，age=20  where  id=5

delete from 表名 where 条件(物理删除对应的数据)

delete from students where id=6;

注意

此方法为物理删除，工作中大部分使用逻辑删除

逻辑删除是指通过设定一个字段（is_delete）来标识当前记录已经删除  实际还有数据，只是看不到了        1代表删除

delete

• delete可以通过where子句删除部分记录。 

• delete删除所有数据时，自增长字段不会从1开始。

truncate 

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

drop 

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

🌟查询

查询的基本操作

SELECT *FROM students; (查询全部)

查询部分字段数据:

• select 字段1,字段2，...from 表名

• 例:select name，sex，age from students

表起别名:

• select 别名.字段1,别名.字段2…. from 表名 as 别名

• 例:select  s.name , s.sex , s.age from students as s;

字段起别名

• select 字段1 as 别名1 , 字段2 as 别名2.... from 表名

• 例:select name as 姓名，sexas性别，age as 年龄from students;

字段内容去重

• SELECT DISTINCT sex FROM students ;

🌟条件查询

根据一定条件查询数据结果

比较运算

• !=    <>不等于

• hometown != '北京' 家乡不在北京的      age < 20 年龄小于20岁的

逻辑运算

• and 满足条件1和条件2

• or 满足条件1或者条件2

• not  非

• SELECT * from students where not hometown ='天津'

模糊查询

• 关键字查询 like

• % 匹配任意多个字符 

• _ 匹配任意一个字符

SELECT * FROM students WHERE name like '孙%'  查找孙某某

SELECT * FROM students WHERE name like '__' and age > 20    大于20岁名字两个字的

范围查询

in 表示在一个 非连续的范围内

例:查询家乡是北京或上海或广东的学生

select * from students where hometown in('北京'，'上海','广东')

●between .. and ..表示在一个连续的范围内

例:查询年龄为18至20的学生

select * from students where age between 18 and 20

空判断

注意:Mysq|中空表示null，与“(空)是不一样的。

空字符串是""，会创建一个对象，内容是“”，有内存空间。
而null，不会创建对象，没有内存空间，所对应的变量只是一个引用

●判断为空: is null

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

select * from students where card is null

●判断非空: is not null

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

select * from students where card is not null

排序

●语法格式:

• select * from表名order by字段名1 asc l desc,字段名2 asc | desc..

说明:

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

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

• asc从小到大排列，即升序  这个可以不写

• desc从大到小排序，即降序

查询所有学生信息，按班级从小到大排序，班级相同时，再按学号从小到大排序.

SELECT * FROM students ORDER BY class ,studentNo;

聚合函数

方便进行数据统计

聚合函数不能在where中使用  where age=max（age）这个会报错

count( ):查询总记录数

max(字段名):查询最大值

min(字段名):查询最小值

sum(字段名):求和

avg(字段名):求平均数

查询北京学生的平均年龄

SELECT AVG(age) from students where hometown='北京'

分组分页

按照字段分组， 此字段相同的数据会被放到一个组中

分组的目的是对每一组的数据进行统计(使用聚 合函数)

●语法格式:

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

例1:查询各种性别的人数

• select sex,count(*) from students group by sex

例2:查询每个班级中各种性别的人数

• select class,sex,count(*) from students group by class,sex（先以班级 再以性别分）

having是对group by后面的结果进行筛选，不能直接用where

limit  start，count  从start开始，0是表示第一个 

SELECT * FROM students LIMIT 0,5;

分页查询实现

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

n表示的是页数      n=1,2,3, 4

m表示的是每页显示的记录数        m=3

(n-1)*m, m是公式，并不是语法格式，不能直接写 在SQL语句中，需要替换为具体的数字

🌟连接查询

必须有字段是能关联的 

内连接    结果为两个表匹配到的数据，两边都有才连

语法格式:

• select * from表1

• inner join表2 on表1.列=表2.列

另一种写法:

• select * from表1,表2 where表1.列=表2.列

-- 查询学生信息及成绩

SELECT *FROM students as stu INNER JOIN scores as sc on stu.studentNo=sc.studentno;    (as可以省略不写)

-- 查询王昭君的成绩，显示姓名课程号成绩

SELECT stu.name,sc.courseNo,sc.score FROM students stu INNER JOIN scores sc on stu.studentNo=sc.studentno WHERE stu.name='王昭君';

左连接    结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充

语法格式:

• select * from表1

• left  join表2 on表1.列=表2.列

右连接    结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null填充

语法格式:

• select * from表1

• right  join表2 on表1.列=表2.列

-- 查询男生中最高成绩，显示姓名课程名，成绩

• SELECT students.`name`,courses.`name`,scores.score FROM students -- 显示姓名,课程名，成绩

• inner JOIN scores on students.studentNO=scores.studentno 

• INNER JOIN courses on courses.courseNo=scores.courseNo

• WHERE students.sex='男' -- 从男生中选

• ORDER BY scores.score desc -- 从大到小排序

• LIMIT 0,1; -- 排序中取第一个

自关联

不需要多张表 自己和自己连接  比如省市区

自关联: inner join关联同一个表，不同的字段。

●自关联要用别名

例1:查询河南省所有的市

Select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='河南省';

例2:查询郑州市的所有的区

Select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle= ='郑州市';

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

SELECT * FROM areas as a1 

INNER JOIN areas as a2 on a1.aid=a2.pid 

LEFT JOIN areas as a3 on a2.aid=a3.pid -- 市也会显示，没有区显示null

where a1.atitle='河南省';

子查询

嵌入在其他查询语句中的select语句称为子查询其他的查询语句称之为主查询。

子查询辅助主查询，要么充当条件，要么充当数据源。

子查询是一条完整的、可单独执行的select查询语句。

-- 查询18岁学生的成绩  因为不止一个学生号，用in

SELECT score from scores WHERE studentno in (SELECT studentno FROM students WHERE age =18)

--查询数据库和系统测试的课程成绩  作为数据源

SELECT * FROM scores as sc INNER JOIN (

SELECT * FROM courses where name in ('数据库','系统测试')

)as co on co.courseno=sc.courseno;

起别名 原来没有名字 充当数据源

自查询特定关键字：

in 范围

主查询 where 条件 in （列子查询）

all 所有

主查询 where  列=all（列子查询）

any|some 任意

主查询 where  列 =  any（列子查询）

⚠️in 等价于 =any          some是any的别称        !=all等价于 not  in 

主键( primary key) :物理上存储的顺序

外键( foreign key): 维护两个表之间的关联关系

tpshop环境部署，数据库验证

软件工具准备

Vmware虚拟机

●在本机上安装好Vmware虛拟机

●在虚拟机.上安装并运行Linux系统

●注意:实际工作中使用云服务器

远程连接工具

●在本机上安装好远程连接工具 (xshell/putty/finalshell)

●通过远程连接工具连接到虚拟机

vmware 虚拟机  可以看作电脑

Linux  电脑上的系统

centos7——Linux的一个发行版本

平时操作的不是虚拟机是虚拟机上的某个系统，系统出问题重新解压打开就是一个新的系统（centos7.zip） 不需要重新安装虚拟机

环境搭建准备

关闭Linux上的防火墙（不然有些端口访问不通）

停止firewall服务  systemctl stop firewalld.service

禁止firewall开机启动  systemctl stop firewalld.service

远程连接步骤

远程机器  查看IP  ifconfig（如果是连云服务器，会直接给公网地址）

本地（电脑终端）测试是否能ping通    ping 远程ip

本地远程连接  finalshell  打开后新建ssh连接 填写名称，ip，用户名，密码

环境部署

1. 项目技术架构介绍

客户端		服务器
浏览器	www.xxx.com➡️	web服务器	请求➡️ 相应⬅️	数据库服务器
	返回页面数据⬅️	php项目		mysql数据库
		php项目代码
操作系统

lnmp环境  linux  mysql  nginx(web服务器)  php或python或java

1. centos常见安装方式

yum 在线安装

必须连互联网；能够解决依赖问题

rpm 安装

自己下载RPM包；自己解决依赖

源码编译安装

自己下载源码并修改编译安装；自己解决依赖

依赖问题：安装过程中 弹窗 软件运行需要xxx软件，要依赖其他软件