如何学习数据库

数据库

数据库的分类

SQL关系型数据库 有库有表 表结构

NOSQL非关系型数据库 键值对

数据库的构成

数据库系统 DBS

数据库管理系统DNMS

数据库管理员 DBA

SQL语句结构化查询语言

SQL语言：

定义语言DDL ：数据库，表，视图 ，存储过程，函数 create创建 alter修改

操作DML 数据库操作语言 insert 插入数据 drop ,delete删除数据 update更新数据

查询DQL 数据库查询语言 select 查询数据

控制DCL 数据库控制语言 可以给用户授权 grant ,revoke

数据库访问技术：让项目连接数据库

php 对应的数据库访问技术 ODBC

JAVA 对应的数据库访问技术 f JDBC

PYTHON 对应的数据库的访问技术 settings.py

数据库的安装

yum 安装

到mysql 官网下载，先下载安装mysql 的yum 仓库

看下载那个版本 ，

法一：yum -y install yum-untils

yum -config-manager --enable mysql157-community

yum -config-manager --enable mysql180-community

法二：vim /etc/yum.repos.d/mysql-community.repo

然后就是安装mysql yum -y install mysql mysql-server

查看初始密码 grep 'passwd' /var/log/mysql.log

修改密码 mysqladmin -u root -p'旧密码' passwd '新密码'

登录数据库 mysql -u root -p'密码'

yum 安装

存放数据文件 /var/lib/mysql

存放字符集等信息 /usr/share/mysql

数据库的配置文件 /etc/my.cnf

存放数据的目录

数据库服务的uuid

判断数据库服务是否运行

[root@mysql1 mysql]# vim /etc/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid validate_password=off 添加后可设置弱密码强度 lower_case_table_names=1 不区分大小写

扩展： key_buffer = 384M //key_buffer是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)。索引被所有的线程共享，key_buffer的大小视内存大小而定 table_open_cache = 512 //MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64, 假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上 max_allowed_packet = 4M //接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。 该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出 sort_buffer_size = 2M //MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小 read_buffer_size = 2M //读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能 thread_concurrency = 8 //最大并发线程数，取值为服务器逻辑CPU数量×2 max_connections = 1000 //MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值

编译安装

准备编译安装的环境

准备编译安装的安装包

清理之前下载的数据库残留，创建新用户

删除

userdel -r mysql

yum -y remove mariadb mariadb-libs mariadb-server mariadb-devel

rm -rf /etc/my*

rm -rf /var/lib/mysql

rm -rf /var/log/mysql*

创建

groupadd mysql

useradd -r -g mysql -s /bin /nolgin mysql

配置

cmake . \
-DWITH_BOOST=boost/boost_1_59_0/ \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DSYSCONFDIR=/etc \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DINSTALL_MANDIR=/usr/share/man \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1

编译和安装 make && make install

初始化数据库还原出厂设置

systemctl stop mysqld

rm -rf /var/lib/mysql/*

要是删除目录mlysql 得创建新的，得改所有者和所属组

在重启数据库

初始化 修改数据库的初始密码

设置环境变量；可以直接使用mysql相关命令：
[root@xingdian ~]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@xingdian ~]# source /etc/profile
​
设置开机启动，并用systemctl管理mysql服务：
[root@xingdian ~]# cp  /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@xingdian ~]# chkconfig mysqld on    //做开机启动
[root@xingdian ~]# systemctl start mysqld
[root@xingdian ~]# systemctl stop mysqld

编译安装

bin目录 ：用于存放一些可执行文件

include 目录 ：用于存放一些头文件

lib 目录：用于存放库文件

share 目录：用于存放字符集

编译安装mysql

不能直接使用systemctl 去管理编译安装的和nginux

需要去配置systemctl

要想在全局调用，不需要加路径。得添加环境变量‘

华为云 对于云数据库 RDS

腾讯云的云数据库就是MYSQL

安装数据库的方法 yum 安装 mysql(数据库) rpm包 8.0的上一个版本5.7 , 最新版本8.0 使用最多的版本 网址:www.mysql.com 官网 提供mysql仓库的安装包 在现有的 base epel 仓库没有mysql的rpm包 有(mariadb) 所有官方提供于一个仓库 cent os7 选择第三个的 点击No thanks,just start my 下载 1、部署 环境准备 关闭防火墙 设置网络 静态 检查仓库 2上传仓库的.rpm包 安装rpm包 rpm -ivh 查看仓库cd /etc/yum.repos.d/ 修改版本 默认8.0版本 这里需要 5.7版本 非交互式更改: 安装 yum-utils yum-config-manager --disable(关闭) mysql80-community yum-config-manager -- enable(开启)mysql57community 安装数据库 yum -y install mysql mysql-server 切换到yum仓库 修改 mysql-community.repo enabled=1(0代表关闭) 把5.7的开启 8.0版本的关闭 mysql 默认初始密码 密码存放 cat /var/log/mysqld.log 启动数据库 systemctl start mysqld 查看密码 修改密码: mysqladmin -u root -p'初始密码' password '新密码' 登录数据库 mysql -u root -p'密码' 编译安装 环境准备 检查防火墙 检查网络 检查仓库 源码包 环境 配置 编译 安装 获取源码包官网 选择带boost的源码包 可以加快下载速度 解压源码包 安装编译安装的环境: yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake 清理环境:查看rpm -qa | grep mariadb rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps (卸载不考虑依赖关系) 查看 ls /var/log/ miaradb 卸载 groupadd mysql useradd -r -g mysql -s /sbin/mologin mysql tar解压 cd 到mysql-5.7.43/ 解压目录下 vi a.sh bash 文件名 执行 make && make install [root@mysql-5.7.26 ~]# cmake . \ cmake 配置 \目的让这些都在一行 -DWITH_BOOST=boost_1_59_0/ \ 指定boost -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ 指定编译安装的路径 -DSYSCONFDIR=/etc \指定安装目录配置文件的位置,默认就是etc -DMYSQL_DATADIR=/usr/local/mysql/data \数据目录 错误日志文件 -DINSTALL_MANDIR=/usr/share/man \ 帮助文档的目录 -DMYSQL_TCP_PORT=3306 \ 默认端口号3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ 用来做网络通信,启动的时候才会产生 -DDEFAULT_CHARSET=utf8 \默认字符集 -DEXTRA_CHARSETS=all \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_READLINE=1 \可以上下翻历史命令 -DWITH_SSL=system \ -DWITH_EMBEDDED_SERVER=1 \ 嵌入式服务器 -DENABLED_LOCAL_INFILE=1 \ 支持从本机导入 -DWITH_INNOBASE_STORAGE_ENGINE=1 //默认存储引擎创建一个目录 mkdir mysql-files 创建在安装路径下 /usr/local/mysql chown mysql.mysql /usr/local/mysql/mysql-files/ -R 默认情况下mysql 命令存放处 /usr/local/mysql/bin/ /usr/local/mysql/bin/mysqld 初始化参数 指定数据库mysql 指定安装目录 指定存放数据目录 初始化提供一个初始密码 ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 启动数据库 : . /

存储引擎

存储引擎：数据库的核心,数据库存储引擎是数据库底层软件组织，数据库管理系统使用数据引擎进行数据的创建，查询，更新，删除数据。

常见的存储引擎

innoDB 是事务性数据库的默认引擎，支持行锁和外键

特点：支持事务处理，支持外键，支持崩溃修复和并发控制。

适合范围 对事务完整要求比较高，要求实现并发控制 ，需要频繁的更新和删除操作的数据库

MyISAM 存储引擎 基于ISAM存储引擎，拥有较高的插入和查询速度，不支持事务

特点：插入数据快，空间和内存使用比较低

适合范围:如果表主要用于插入和读取数据，对完整性和并发性要求不高可以使用

MEMORY 存储引擎 将数据存储到内存，为查询和引用数据提供快速访问

特点：所用的数据都在内存中，数据的处理速度快，安全性不高，不能太大的表

适合范围：需要很快的读写，不要求安全，使用相对较小的数据库表

事务：

一组相关的数据库操作，主要管理数据库读写操作，保证数据的完整和一致性 ，事务用来管理 insert update delete语句

满足事务的条件

原子性 就是所有的操作必须全部完成，否则就会回到开始的位置

一致性 就是数据在事务开始到结束，数据的完整性

隔离性 可以防止数据多个事务同时处理一个数据，导致数据不一致。

持久性 就是事务修改完之后，对数据修改就是永久的 ，即使故障也不会丢失

锁

协调多个进程同时访问一个数据 在不同的存储引擎支持不同的锁结构

例如：MyISAM 在执行查询语句时，会自动给所涉及的表加读锁，在执行更新删除插入等，会自动给涉及的表加写锁 ，实现在某一时间多个表的一致性读取

锁的分类

表锁:开销小，加锁快，不会出现死锁，并发最低，发生锁冲突最高

行锁：开销大，加锁慢 ，会出现死锁，发生锁冲突最低，并发最高

页面锁：时间在上面两个之间，会出现死锁，并发一般

外键

引用另一个表中的一列或者多列数据，被引用的列具有主键约束或者唯一性约束，外键是用来建立和加强两个表数据之间的连接

外键是某个表的一列，包含在另一个表的主键中

外键是索引的一种，通过一张表的一列指向另一张表的主键，进行数据的联系

一张表主键只能有一个，但是外键可以有很多个

外键的作用：保证数据的一致性和完整性，减少数据的冗余

数据类型

整数型 TINYINT SMALLINT MEDIUMINT INT BIGINT

用于存储用户的年龄、游戏的Level、经验值等

浮点型 FLOAT DOUBLE

用于存储用户的身高、体重、薪水等

float(5,3)      5宽度  3精度
注意：
    宽度不算小数点
    宽度-精度=点前

字符串类型

CHAR系列 CHAR VARCHAR

TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT

BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB

BINARY系列 BINARY VARBINARY

枚举类型 ENUM

enum ('m','f')

时间和日期类型

时间和日期类型：DATE 日期值TIME时间值 DATETIME时间日期值 TIMESTAMP时间日期值，时间戳 YEAR年份值

作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。

select now();查看当前时间

表操作

创建表 create table 表名（字段名 数据类型（宽度）约束条件，多个用逗号隔开）

注意在一张表中字段名不能相同，约束条件和宽度是可选的，字段名和类型是必须的

create table [库名].表名

输入数据 insert into 表名 (字段名，字段名) values (字段值)，（字段值）；

查看表的数据 select *（可以是表里面的字符段） from 表名；

查看库里面的表 show tables;使用这个得在库里面

查看表结构desc 表名；

查看表的详细结构show create table 表名；

删除表 drop table 表名；

修改数据 update 表名 set 列名=修改数据 where 定位到哪一行（不定位的话就会将一列都更新）；

删除一行数据 delete from 表名 where 定位到哪一行；

表的完整性约束

作用就是保证数据的完整和一致

约束条件

primary key 标识主键 ，不为空（unique key + not null）

foreign key 标识表的外键 实现表之间的关联

not null 标识字段不为空

unique key 标识字段唯一，可以为空

auto_increment 标识字段的自动增长（整数类型，且为主键）

default 该字段的默认值

是否为空 ，默认是null 可设置not null ,字段不为空，必须赋值

字段是否有默认值，默认值是null ,如果插入字段不给字段赋值，字段使用默认值

修改表alter

修改表名 alter table 旧表名 rename 新表名；

增加 .........add 字段名 数据类型 指定位置after first 原字段 （就可增加到指定位置）

删除字段 ...........drop 原字段

修改字段

修改字段的数据类型和约束条件....... modify 字段 新类型 [新的约束条件]可以先用desc 查看

alter table student10 modify id int not null primary key ; 修改字段类型、约束、主键

alter table student1 add primary key(id);///增加主键

alter table student10 modify id int not null primary key auto_increment; 增加约束

alter table student10 drop primary key;///删除主键（删除不了，看是否有自增）

alter table student10 modify id int not null;///删除自增

修改字段的名字 数据类型 约束条件 ............ change 字段 新名字 新类型 新约束条件

修改存储引擎 ........engine=新引擎

复制表

复制表结构 和数据 不会复制主键和外键和索引

create table 新表名 select * from 旧表名；

只复制表结构

create table 新表名 select * from 旧表名 where 1=2 ;条件为假，没有数据

可以复制主键，但是只复制表结构

create table 新表名 like 旧表名；

库操作

系统自带的库的作用

show databases ;

information_schema 虚拟库，主要存储系统中一些数据库对象的信息，用户表信息，列信息，权限信息，字符信息等。

performmance_schema 主要存储数据库服务器的性能参数

mysql 主要存储数据库的权限信息

sys 主要存储数据库服务器的性能

注意:information _schema.SCHEMATA 存放的是系统中的库

SCHEMATA 数据库名

select * from information_schema.SCHEMATA;

目录名称 实际库名字 默认字符设置的名称 默认分类的名称

需要注意的表

COLUMNS 字段名

select * from information_schema.COLUMNS\G

TABLES 存储表名

select * from information_schema.TABLES\G（G的作用就是分行显示）

创建数据库

区分大小写 唯一性 不能使用关键字create select 不能单独使用数字

交互式创建：

create database 库名；

非交互式创建

mysql -u root -pQianFeng@123 -e "create database diandian"

进入数据库或者切换数据库

use 库名；

查看数据库

show databases;

show create database 库名；

select database();查看当前所在库

删除数据库

drop database 数据库名；

数据库查询

单表查询

select 字段名称,字段名称2...... from 表名 [条件]

去重查询

查询数据时可以去重查询 distinct

SELECT distinct 字段名 FROM 表名;

四则运算查询

在表中数据的是数字时，可以使用加减乘除在字段后 还可以改名

AS (可省略) 新名字

SELECT salary, salary*14 (AS) Annual_salary FROM 表名;

连接字符串

可以将将两个字符串连接 concat(name,'degongzi',salary*14)

SELECT concat(name, 'degongzi ', salary*14) AS Annual_salary FROM employee5;

结果:jack degongzi 70000

条件查询

单条件查询 where 字段名 =值；

添加条件查询 where　and or

关键字

between

between 500 and 5000 在500 到5000之间的

SELECT name,salary FROM employee5 WHERE salary BETWEEN 500 AND 5000;

is （not） null 为空 或者不为空的

SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;

还有没有数值的 是where name='';

注意：null 和0 空字符串 空格不同 null 没有存储空间

对空值做加减乘除为空 ，排序在前面索引小

in集合 查询

(not)in (1,2,3,4) 在这个集合内的=or 不在这个集合内的=and

SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;

||

SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;

模糊查询 like

like 'a%'; 意思是以a开头的后面有任意字段

like 'a_';以a开头的后面有一个字段

排序查询 order by (默认升序)

asc 升序 desc 降序

select name,salary from employee5 order by salary desc;

以行数显示 limit 3 ; 显示前三行

select name,salary from employee5 order by salary desc limit 3; //控制显示前3行

limit 1,3; 从序号开始显示，默认是序号0

select name,salary from employee5 order by salary desc limit 1,3; //从序号1开始显示三行的内容

集合函数查询

统计记录 count

select count(*) from 表名;

查看最大值 max

select max(salary) from employee5;

查看最小值 min

查看平均值 avg

求和 sum

select concat(sum(salary)) from employee5 where post='sale';///求部门sale 的工资之和

获取薪水最高的人详细信息

select * from employee5 where salary = (select max(salary) from employee5);

分组查询 group by

group by 和 group_concat

SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;///可以用dep_id 为组查看 group _coucat 将数据连接起来

+--------+------------------------------+
| dep_id | GROUP_CONCAT(name)           |
+--------+------------------------------+
|    100 | jack,tom,robin,alice         |
|    101 | ,harry                       |
|    102 | emma,christine,zhuzhu,gougou |
+--------+------------------------------+

SELECT post,max(salary) FROM employee5 GROUP BY post;///获取部门的最高的工资

正则查询 REGEXP

以什么开头 MySQL [company]> SELECT * FROM employee5 WHERE name REGEXP '^ali';

以什么结尾 MySQL [company]> SELECT * FROM employee5 WHERE name REGEXP 'ce$';

连续出现n次 MySQL [company]> SELECT * FROM employee5 WHERE name REGEXP 'm{2}';

多表联合查询

交叉连接 ：生成笛卡尔积 ，不使用任何匹配条件，匹配所有的行

select employee6.emp_name,employee6.age from employee6,department6;

内连接 :只连接匹配的行

select employee6.emp_name,employee6.age from employee6,department6 where employee6.dept_id=department6.dept_id;

外连接 :左连接 （会显示左边表内的所有值） 右连接（会显示右边表内的所有值） 全外连接（会显示左右两个表的所有值）

SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;

全外连接

select * from employee6 full join department6;

子查询

子查询是将一个查询语句嵌套在另一个查询语句中

内层查询语句的查询结果，可以为外层查询语句提供查询条件

子查询中可以包含：IN、NOT IN等关键字；还可以包含比较运算符：= 、 !=、> 、<等

数据库的日志管理

日志分类

错误日志 error log

启动停止关闭失败 报错 rpm 安装日志位置/var/log/mysql.log

通用查询日志 ：所有的查询都记录下

log-bin=/var/log/mysql-bin/slave2 server-id=2

[root@slave2 ~]# mkdir /var/log/mysql-bin [root@slave2 ~]# chown mysql.mysql /var/log/mysql-bin/ [root@slave2 ~]# systemctl restart mysqld

[root@slave2 ~]# mysqlbinlog slave2-bin.000001 -v --base64-output=decode-rows 时间点 ： 141126 14:04:49 位置点 : at 106 注：

1. 重启mysqld 会截断

2. flush logs 会截断

3. reset master 删除所有binlog rm -rf /var/lib/mysql/*.000001

4. 删除部分 PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

截取binlog all：

datetime：

position：

慢查询日志 slow log

定义某一个查询语句，定义超过时间，通过日志提供调优建议给开发人员

二进制日志:所有的查询都记录下来

中继日志：读取主服务器的binlog 在本地回放，保持一致

DDL log 定义语句的日志

权限管理

权限级别

1.权限级别

Global level：系统级，所有库，所有表的权限

Database level：某个数据库中的所有表的权限

Table level：库中的某个表的权限

Column level：表中的某个字段的权限

procs level：某个存储过程的权限

proxies level：代理服务器的权限

用户管理

登录和退出

-h 指定主机名 【默认为localhost】 ​ -P MySQL服务器端口 【默认3306】 ​ -u 指定用户名 【默认root】 ​ -p 指定登录密码 【默认为空密码】 ​ 此处mysql为指定登录的数据库 ​ -e 接SQL语句 (在脚本中使用)

创建用户

法一

create user xingdian@'%' identified by 'QianFeng@123';

法二

grant all on . to 'diange'@'localhost' identified by 'QianFeng@123';

删除用户

Drop user xingdian@'%';

delete from mysql.user where user='diandian' AND Host='%

修改密码

mysqladmin -uroot -p'123' password 'new_password'

update mysql.user set authentication_string=password(123456) where user='diange' And Host='%';

自己设置自己的密码

set password='123';

root 用户修改其他用户密码

SET PASSWORD FOR user3@'localhost'='new_password';

UPDATE mysql.user SET authentication_string=password('new_password') WHERE user='user3' AND host='localhost';

授权

mysql> grant all on *.* to '用户名'@'主机名' identified by '密码';

all是所有的权限 （select drop create）也可以多权限用逗号隔开

第一个* 是所有的库，可以指定库

第二个*是所有的表 ，也可以制定表

刷新授权表 flush privileges