数据库基础知识

**数据库 database **
一、基础概念
1、数据库：一个用来存储、管理海量数据信息的超级仓库。
长期存储在计算机内，有组织可共享的数据大集合。数据量达到上百万，上亿等
海量存储、高效检索、安全可靠
数据库有什么？
2、层级概念：数据库》》》数据表》》》数据
数据库： 有关系型数据库和非关系型数据库
关系型数据库：数据表通过主键和外键相关联的数据库
比如：mysql、 sqlserver、oracle
非关系型数据库：以键值对方式存储数据，例如：Redis
数据表：有表结构：一个数据库有多张表，每一张表都有不同的字段（列）
数据：数据表的内容信息
3、DBMS 数据库管理系统
数据库的管理软件，负责管理数据访问、存储规则
通过sql结构化查询语句来存取管理关系型数据库
oracle：功能强大，收费。银行/电信等大型企业
SQL Server：比较适配Windows，政府单位常用
Mysql：开源免费、轻巧。校园系统/网站主流

二、数据库连接操作
1、命令行窗口连接：mysq安装目录bin目录下执行

mysql -uroot -p密码
mysql>

2、第三方工具可视化连接
navicat: 功能强大的数据库管理工具，支持多种主流数据库系统
建立连接：
主机：localhost
端口：3306
用户名：root
密码：数据库root用户密码
测试连接确认即可

三、创建数据库和表
创建数据库：
1、navicat：
右键 》》新建数据库
数据库名称：school
字符集：utf8（支持中文字符）
排序规则：utf8_general_ci
选中school，右键选择创建表

2、navicat命令行
--：sql注释符
--创建school数据库，CHARACTER定义字符集utf8，COLLATE定义排序规则
CREATE DATABASE school CHARACTER SET utf8 COLLATE utf8_general_ci;

--选择使用school库
use school;
--创建students表，定义表结构：字段id、name、id_card、phone、address、password

create TABLE students(
  id INT(11) PRIMARY KEY,
  name VARCHAR(50)  NOT NULL,
  id_card varchar(18) NOt NUll,
  phone varchar(11) NOT NULL,
  address TEXT,
  password VARCHAR(12) NOT NULL 
);

四、SQL操作：CRUD（增删改查）
SQL-结构化查询语言
1.数据定义语言（DDL）：用于创建和修改数据库对象（如表和索引）的结
构，如CREATE、ALTER、DROP等语句。
2.数据操纵语言（DML）：用于对数据库中的数据进行新增、修改、删除等
操作，如INSERT、UPDATE、DELETE等语句。
3.数据查询语言（DQL）：用于对数据进行查询操作，如SELECT语句。
4.数据控制语言（DCL）：用于控制用户对数据的访问权限，如GRANT和
REVOKE语句

整个流程：通过命令行和navicat的编写sql结构化语句，将sql语句发送给DBMS，
由DBMS执行语句后对数据库、以及表和数据做统一的管理

1、新增/插入数据（insert into）: 字段名与值一一对应，有几个字段名，就需要给几个值
insert into 库名,表名(字段1,字段2...) values(值1,值2...)，字段与值对应，按序插入数据
insert into school.students (id,name,id_card,phone,address,password)VALUES(2,'lisi','1234','13300000000','中国','abc');

2、U修改数据（update...set... where）
update 表名 set 字段名=新值 where 条件（最好使用主键）
UPDATE school.students SET password='abc' WHERE id=3;
注意：必须要有条件，否则就是修改整列为新值

3、D删除数据（delete from ...where）

delete from 表名 where 条件（最好使用主键）
delete from school.students where id=2;

注意：必须要有条件，否则就是清空了表数据

4、Read 查询读取数据库里的数据
查询表所有（列）信息，

select * from 表名
select * from school.students

按条件查询

select * from 库名.表名 where 条件
select name,password from school.students where id=1

多条件查询语句 AND 当条件同时满足时，OR 当条件之一满足时

select 字段名/* from 库名.表名 where 条件1 AND 条件2
select * FROM school.students WHERE name='zhangsan' and id=1
select 字段名/* from 库名.表名 where 条件1 OR 条件2
SELECT * FROM school.students WHERE id=3 OR password= 'abc'
SELECT * FROM school.students WHERE (name='wu' or password='abc') AND id=2

注意：当条件语句中or和and关键字同时存在时，AND优先级》OR，但是有括号先括号

模糊查询：LIKE 与通配符。下划（_）表示有且只能匹配一个字符，%表示匹配0个或多个字符

select */字段名 from 库名.表名 where  字段名 like "str_"
select */字段名 from 库名.表名 where  字段名 like "str%"
SELECT * FROM school.students WHERE name LIKE 'li%'
SELECT * FROM school.students WHERE name LIKE 'li__'

升序和降序查询：关键字：order by ASC升序，DESC降序

select */字段名 from 库名.表名 order by 字段名 ASC/DESC
SELECT * FROM school.students ORDER BY id DESC
SELECT * FROM school.students ORDER BY name ASC
SELECT name,password FROM school.students ORDER BY name ASC

限制查询：关键字：limit 起始位置/偏移量,条数(当没有偏移量的时候默认从0开始的)

select */字段名 from 库名.表名 limit 偏移量(起始位但不包括),条数
SELECT * FROM school.students LIMIT 1,2 --从返回结果从1开始往后2条数据
SELECT * FROM school.students LIMIT 3  --从开始返回3条数据

混合查询：关键语句混合查询
SELECT * FROM school.students WHERE name LIKE 'l%' ORDER BY name LIMIT 1

union查询：用于合并两个或多个 SELECT 语句的结果集，同时去除重复的记录。每个 SELECT 语句必须拥有
相同数量的列，列的数据类型也必须兼容。
sql1 union sql2 sql1和sql2的字段数要一致

select id,name from school.students UNION select 1,2

php与数据库交互步骤：
1、使用mysqlli_connect连接数据库
2、通过超全局变量$_Get或者$_POST 接收用户在前端页面输入的账号密码
3、拼接SQL语句，使用mysqlli_query 发送指令给数据库
4、根据查询结果返回成功或者失败的反馈给用户

五、数据库安全加固
1、限制权限，最小权限原则。
为应用程序分配仅能完成工作所需虽小权限，避免权限过度
禁止root用户等超级管理员运行程序，连接数据库。
限制IP访问数据库服务器，只允许业务服务器IP访问
创建独立的用户admin仅能本地访问数据库

CREATE USER 'dbadmin'@'localhost' IDENTIFIED BY '******';

授予必要的权限给用户在具体的库和表上
GRANT SELECT,INSERT,UPDATE ON school.* TO 'dbadmin'@'localhost';

2、开启审计日志
开启通用日志：
set global general_log on
查看日志位置：
show VARIABLES LIKE "general_log_file";
安全审计与取证
记录所有SQL操作，用于行为追踪。即使遭遇攻击，也能通过日志分
析黑客操作路径，是事后溯源的关键证据。

3、数据备份
原始数据+至少2份副本，确保数据不丢失
不同介质存储：U盘，云盘、本地硬盘
离线存储，移动硬盘等，防止被远程删除

数据备份与恢复
navicat 链接数据库后
备份
选择数据库，右键选择转储sql文件，选择结构和数据
恢复
新建数据库，连接之后，右键选择运行sql文件，选择sql文件
命令行工具

备份
mysqldump -uroot -p school > E:\school.sql

恢复
mysqldump -uroot -proot school > E:\school.sql

4、防御sql注入是重点
对用户输入进行严格过滤，关键符号转义操作，
对sql关键字禁止输入比如union，从源头拦截恶意字符。