投稿
  1. 极客之音首页
  2. 后端开发

MySQL-关系型数据库

小半 • • 后端开发 • 阅读 628

导读：本篇文章讲解 MySQL-关系型数据库，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

MySQL初识

一、数据库概述

数据库(database)是“按照数据结构来组织、存储和管理数据的仓库”；用来长期存储在计算机内的；有组织的、可共享的、统一管理的大量数据集合

1、SQL与NOSQL

1.1、分类

  • 关系型数据库：MySQL、Oracle、PostgreSQL等；
  • 非关系型数据库：Redis、MongoDB、Memcached、etcd等。

1.2、区别

关系型数据库

优：
数据完全存储在磁盘中，安全可靠；
易于维护，基于ACID属性(原子性、一致性、隔离性、持久性) ，降低了数据冗余和数据不一致的概率；
缺：
高并发读写能力差，海量数据的读写效率降低；
高拓展性与可用性差，横向拓展能力稍弱。

非关系型数据库

优：
速度快，效率高，可以使用内存作为载体进行存储；
NOSQL具有扩展简单、高并发、高稳定性、等优势；
缺：
没有事务处理，无法保证数据的完整性和安全性，不一定安全；
功能没有关系型数据库完善，没有sql支持，且复杂表关联查询不容易实现。

2、MySQL概述

  • MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于 Oracle 旗下公司；
  • MySQL是一个真正的多用户、多线程SQL数据库。SQL(结构化查询语言)是世界上最流行的和标准化的数据库语言；
  • MySQL是一个 C/S 结构的实现，它由一个服务器守护程序 mysqld 和很多不同的客户程序和库组成；
  • SQL是一种标准化的语言，它使得存储、更新和存取信息更容易。

3、MySQL存储原理

MySQL架构图：
在这里插入图片描述
1、连接池：最上层负责和客户端进行连接，比如 jdbc类似的数据库连接的 API，这一层类似于线程池概念，连接池可以同时处理很多个数据库请求。同时这一层有 SSL的安全概念，可以确保连接是安全的；
2、SQL接口：当 SQL语句进入 MySQL后，会先到 SQL接口中，这一层是封装层，将传过来的 SQL语句拆散，将底层的结果封装成 SQL的数据格式；
3、解析器：这一层负责将 SQL语句进行拆分，验证，如果 SQL语句有问题那么就返回错误，如果没问题就继续向下执行；
4、优化器：对 SQL查询结果优化处理，产生多种执行计划，最终数据库会选择最优化的方案去执行，尽快返会结果；
5、缓存：对要查询的 SQL语句进行 hash缓存，如果下一次是相同的查询语句，则在 SQL接口之后直接返回结果；
6、存储引擎： MySQL有很多种存储引擎，每一种存储引擎有不同的特性，他们负责组织文件的存放形式，位置，访问文件的方法等等。比较常用的有 innoDB，MyISAM，MEMORY等，默认的是 innoDB存储引擎；
7、文件系统：真正存放物理文件(磁盘)的单位。

三、MySQL部署

地址 系统 主机名 软件名
192.168.10.1 Centos7.4 mysql mysql-8.0.13-el7-x86_64.tar.gz 
[root@localhost ~]# hostnamectl set-hostname mysql
[root@localhost ~]# bash
[root@mysql ~]# groupadd mysql
[root@mysql ~]# useradd -g mysql mysql
[root@mysql ~]# cat << EOF >> /etc/sudoers
> mysql   ALL=(ALL)       NOPASSWO:ALL
> EOF
[root@mysql ~]# su mysql
[mysql@mysql root]$ cd
[mysql@mysql ~]$ 
[mysql@mysql mysql]$ mkdir /tmp/mysql
[mysql@mysql mysql]$ cd /tmp/mysql
[mysql@mysql mysql]$ wget http://dev.mysql.com/Downloads/MySQL-8.0/mysql-8.0.13-el7-x86_64.tar.gz
[mysql@mysql mysql]$ tar -zxvf mysql-8.0.13-el7-x86_64.tar.gz
[mysql@mysql mysql]$ ll		#属组属主是MySQL
总用量 642460
drwxrwxr-x 9 mysql mysql       172 4月   3 10:26 mysql-8.0.13-el7-x86_64
-rw-r--r-- 1 root  root  657877489 4月   3 10:25 mysql-8.0.13-el7-x86_64.tar.gz
[mysql@mysql mysql]$ cd mysql-8.0.13-el7-x86_64
[mysql@mysql mysql-8.0.13-el7-x86_64]$ vim my.cnf
[mysqld]													#声明区
default_authentication_plugin=mysql_native_password			#默认加密方式
character-set-server=utf8									#数据库编码为utf8
collation-server=utf8_general_ci							#意思是大小写不敏感
skip-host-cache												#禁用主机名缓存
skip-name-resolve											#禁用DNS查找
lower_case_table_names=1									#不严格区分表名大小写
basedir =/tmp/mysql/mysql-8.0.13-el7-x86_64					#MySQL路径
datadir =/tmp/mysql/mysql-8.0.13-el7-x86_64/data			#MySQL物理文件存放位置
socket=/tmp/mysql/mysql-8.0.13-el7-x86_64/mysql.sock		#MySQLsocket文件位置
port =3306													#MySQL端口
max_connections=1000										#系统支持最大连接数
max_allowed_packet=1073741824								#最大允许传输包的大小
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES			#定义MySQL的语法,以及各种确认检查
innodb_print_all_deadlocks=1								#默认0关闭,1打开,每次发生死锁后，系统会自动将死锁信息输出到错误日志中
log-error= /tmp/mysql/mysql-8.0.13-el7-x86_64/error.err		#错误日志ERROR路径
[mysql@mysql mysql-8.0.13-el7-x86_64]$ bin/mysqld --defaults-file=/tmp/mysql/mysql-8.0.13-el7-x86_64/my.cnf --initialize-insecure		#初始化MySQL
[mysql@mysql mysql-8.0.13-el7-x86_64]$ bin/mysqld --defaults-file=/tmp/mysql/mysql-8.0.13-el7-x86_64/my.cnf &							#启动MySQL
[mysql@mysql mysql-8.0.13-el7-x86_64]$ bin/mysql --socket=mysql.sock -uroot -p															#敲Enter直接进入
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'root'@'%' identified by '123123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.09 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.01 sec)

mysql> delete from user where host='localhost' and user='root';
Query OK, 1 row affected (0.07 sec)

mysql> exit
Bye
[mysql@mysql mysql-8.0.13-el7-x86_64]$ bin/mysqladmin shutdown --socket=mysql.sock -uroot -p					#敲Enter直接停止MySQL
Enter password:
[mysql@mysql mysql-8.0.13-el7-x86_64]$ bin/mysqld --defaults-file=/tmp/mysql/mysql-8.0.13-el7-x86_64/my.cnf &	#重启
[mysql@mysql mysql-8.0.13-el7-x86_64]$ bin/mysql --socket=mysql.sock -uroot -p123123							#重启生效
[mysql@mysql mysql-8.0.13-el7-x86_64]$ ps -ef | grep mysql
root       1304   1248  0 09:43 pts/0    00:00:00 su mysql
mysql      1305   1304  0 09:43 pts/0    00:00:00 bash
mysql     15246   1305  0 10:56 pts/0    00:01:17 bin/mysqld --defaults-file=/tmp/mysql/mysql-8.0.13-el7-x86_64/my.cnf
mysql     15389   1305  0 14:10 pts/0    00:00:00 ps -ef
mysql     15390   1305  0 14:10 pts/0    00:00:00 grep --color=auto mysql

MySQL默认四个库

  • information_schema：是一个信息数据库，这个数据库保存了所有数据库信息。如：数据库名、数据库表、访问权限等；
  • mysql：是一个系统库，主要负责存储数据库的用户，权限设置等；
  • performance_schema：是 MySQL 5.5之后新增的一个性能优化引擎；
  • sys：库中数据与 performance_schema 一致，但是把 performance_schema 中内容复杂度降低了。

二、MySQL组成及常用工具

1、客户端程序

基本客户端程序

  • mysql：交互式或非交互式的CLI工具；
  • mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存在文本文件中；
  • mysqladmin：基于mysql协议管理mysqld；
  • mysqlimport：数据导入工具；

MyISAM存储引擎的管理工具

  • myisamchk：检查MyISAM库；
  • myisampack：打包MyISAM表，只读。

2、服务端程序

  • mysqld_safe：作为mysqld 启动脚本，开启了守护mysqld进程的任务；
  • mysqld：mysqld是二进制程序，所有启动会调用mysqld_safe；
  • mysql_multi：管理多个mysql实例。

3、MySQL命令

  • 客户端命令：本地执行，每个命令都完整形式和简写格式。
mysql> \h #等同help
mysql> \u #等同use
mysql> \s #等同status
mysql> \t #等同system
  • 服务端命令：通过mysql协议发往服务器执行并取回结果，命令末尾必须都跟上结束符号 ;。
mysql> show databases;
  • mysql命令：格式 mysql [OPTIONS] [database]
常用选项：
-A ，--no-aute-rehash	#禁止补全
-u ，--user=			#用户名
-h ，--host=			#服务器主机名
-P ，--password=		#用户密码 
-p ，--port=			#服务器端口
-S ，--socket=			#socket路径
-D ，--database=		#指定数据库
-C ，--compress			#启用压缩
-e “SQL”				#执行SQL命令
-V ，--version			#显示版本
-v ，--verbose			#显示详细信息
--print-defaults		#获取程序默认使用的配置

三、SQL语句

1、sql语句概述

SQL即结构化查询语言(Structured Query Language)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

2、sql语句分类

  • DDL：数据定义语言，用来建立数据库，数据对象和定义其列，如create、alter、drop；
  • DML：数据操纵语言，用来查询、插入、删除、修改数据库中的数据，如select、insert、update、delete；
  • DCL：数据控制语言，用来控制数据库组件的存取许可，存取权限等，如commit、rollback、grant、revoke；

3、sql数据类型

日期类型
在这里插入图片描述
小数类型
在这里插入图片描述
字符串
在这里插入图片描述

4、表相关SQL

  • 表是数据库中的数据组成单位，类似于Java中的对象，表中的字段相对应对象中的属性。

创建表原理

  • 在客户端中写完创建表的sql语句后客户端会把sql语句交给DBMS（MySQL）；
  • DBMS解析后，会在数据库中创建语句中的表和表中的字段 。

表引擎

  • InnoDB：支持数据库的高级处理包括(事物、外键)等。（默认是innodb）；
  • Myisam：只支持数据的基本存储。

创建表时指定表的字符集和引擎

#格式 create table person (id int,name varchar(44)) engine=myisam charset=utf8;

5、简单操作

库操作

mysql> create database bld;						#创建bld库
mysql> show databases;							#查看所有库
mysql> show create database bld;				#查看库信息
mysql> use bld									#进入bld数据库
mysql> select database();						#查看当前所在库

注解

varchar：可变长字符串类型。
int：整数类型。
date：时间类型。
char：固定字符串类型。
not null：不允许为空。
double：浮点类型。
primary key：设置主键，具有唯一性。

表操作

mysql> create table Employe (id int not null,name varchar(44) not null,posts varchar(44) not null,salary double,primary key (id));			#创建表
mysql> show tables; 															#查看所有表
mysql> show create table Employe;												#查看表详细信息
mysql> desc Employe;															#查看表结构
mysql> rename table Employe to employee; 										#更改表名
insert into employee values(1,"张总","部门主管",100331.2);
insert into employee values(2,"王哥","项目经理",72221.6);
insert into employee values(3,"小刘","Java开发工程师",33661.2);
insert into employee values(4,"小赵","运维工程师",12211.2);						#插入数据
mysql> select * from employee;
+----+--------+---------------------+----------+
| id | name   | posts               | salary   |
+----+--------+---------------------+----------+
|  1 | 张总   | 部门主管            | 100331.2 |
|  2 | 王哥   | 项目经理            |  72221.6 |
|  3 | 小刘   | Java开发工程师      |  33661.2 |
|  4 | 小赵   | 运维工程师          |  12211.2 |
+----+--------+---------------------+----------+
4 rows in set (0.00 sec)

#mysql> insert into bbb values(5,"小陈","Golang开发工程师","99999.9"),(6,"小孙","Devops云计算工程师","69999.9");
#批量插入数据，中间使用,隔开
#mysql> insert into employee (id,name) values(5,"小陈"),(6,"小孙");
#批量某个字段插入数据

#mysql> update employee set salary=10000;						#修改employee表中的age项全部为10000
#mysql> update employee set salary=10000 where name="小刘";		#修改employee表中的salary项为10000并且只限于name为小刘的行
#mysql> delete from employee where id="1";						#删除employee表中id项为1的行
#mysql> delete from employee where posts is NULL;				#删除employee表中abc项为null空的行
#mysql> delete from employee;									#删除表中的所有数据

表字段操作

mysql> alter table employee add age int first;					#在employee(员工)表最前面添加age字段
mysql> select * from employee;
+------+----+--------+---------------------+----------+
| age  | id | name   | posts               | salary   |
+------+----+--------+---------------------+----------+
| NULL |  1 | 张总   | 部门主管            | 100331.2 |
| NULL |  2 | 王哥   | 项目经理            |  72221.6 |
| NULL |  3 | 小刘   | Java开发工程师      |  33661.2 |
| NULL |  4 | 小赵   | 运维工程师          |  12211.2 |
+------+----+--------+---------------------+----------+
4 rows in set (0.00 sec)

mysql> alter table employee add sex char(10);					#在employee(员工)表最后面添加sex字段
mysql> select * from employee;
+------+----+--------+---------------------+----------+------+
| age  | id | name   | posts               | salary   | sex  |
+------+----+--------+---------------------+----------+------+
| NULL |  1 | 张总   | 部门主管            | 100331.2 | NULL |
| NULL |  2 | 王哥   | 项目经理            |  72221.6 | NULL |
| NULL |  3 | 小刘   | Java开发工程师      |  33661.2 | NULL |
| NULL |  4 | 小赵   | 运维工程师          |  12211.2 | NULL |
+------+----+--------+---------------------+----------+------+
4 rows in set (0.00 sec)

mysql> alter table employee add origin varchar(10) after name;	#在表中某个字段后面添加字段
mysql> select * from employee;
+------+----+--------+--------+---------------------+----------+------+
| age  | id | name   | origin | posts               | salary   | sex  |
+------+----+--------+--------+---------------------+----------+------+
| NULL |  1 | 张总   | NULL   | 部门主管            | 100331.2 | NULL |
| NULL |  2 | 王哥   | NULL   | 项目经理            |  72221.6 | NULL |
| NULL |  3 | 小刘   | NULL   | Java开发工程师      |  33661.2 | NULL |
| NULL |  4 | 小赵   | NULL   | 运维工程师          |  12211.2 | NULL |
+------+----+--------+--------+---------------------+----------+------+
4 rows in set (0.00 sec)

mysql> alter table employee drop age;							#删除表中指定字段
mysql> select * from employee;
+----+--------+--------+---------------------+----------+------+
| id | name   | origin | posts               | salary   | sex  |
+----+--------+--------+---------------------+----------+------+
|  1 | 张总   | NULL   | 部门主管            | 100331.2 | NULL |
|  2 | 王哥   | NULL   | 项目经理            |  72221.6 | NULL |
|  3 | 小刘   | NULL   | Java开发工程师      |  33661.2 | NULL |
|  4 | 小赵   | NULL   | 运维工程师          |  12211.2 | NULL |
+----+--------+--------+---------------------+----------+------+
4 rows in set (0.00 sec)

mysql> alter table employee change name 姓名 char(22);			#修改成绩表字段名和字段类型
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| 姓名   | char(22)    | YES  |     | NULL    |       |
| origin | varchar(10) | YES  |     | NULL    |       |
| posts  | varchar(44) | NO   |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
| sex    | char(10)    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> alter table employee modify 姓名 char(11) after sex;		#修改成绩表类型和顺序（将姓名类型修改为char(11)并放在sex后）
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| origin | varchar(10) | YES  |     | NULL    |       |
| posts  | varchar(44) | NO   |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
| sex    | char(10)    | YES  |     | NULL    |       |
| 姓名   | char(11)    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改表的引擎与字符串

mysql> show create table employee;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                       |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `origin` varchar(10) DEFAULT NULL,
  `posts` varchar(44) NOT NULL,
  `salary` double DEFAULT NULL,
  `sex` char(10) DEFAULT NULL,
  `姓名` char(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8   |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table employee engine=myisam charset=gbk;
mysql> show create table employee;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                  |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `origin` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `posts` varchar(44) CHARACTER SET utf8 NOT NULL,
  `salary` double DEFAULT NULL,
  `sex` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `姓名` char(11) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk   |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/11959.html

赞 (0)
小半的头像小半
0 0

相关推荐

极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！