Mysql数据库基础操作

Mysql数据库基础

一、数据库简介

1、数据库的特点

使用数据库可以高效且条理分明地存储数据，是人们能更加迅速、方便地管理数据。数据库具有以下特点：

• 可以结构化地存储大量的数据信息，方便用户进行有效的检索和访问。
• 可以有效地保持数据信息的一致性、完整性，降低数据冗余。
• 可以满足应用的共享和安全方面的要求。

2、数据库的概念

1）数据

描述事物的符号记录称为数据（Data）。数据不仅仅包含数字，文字、图像、图形、声音、档案记录等都是数据。

在数据库中，数据是以“记录”的形式按统一的格式进行存储的，相同格式和类型的数据统一存放在一起。

2）数据库和数据库表

不同的记录组织在一起，就形成了数据库（Dtatbase，DB）的“表”（table），即表是用来存储具体数据的。

数据库就是表的集合，它是以一定的组织方式存储的相互有关的数据集合。例如，关系数据库的表由记录组成，记录由字段组成，字段由字符或数字组成。它可以供各种用户共享，具有最小冗余度和较高的数据独立性。它是统一管理的相关数据的集合。

3）数据库管理系统和数据库系统

数据库管理系统（Database Managment System，DBMS）是实现对数据库资源有效组织、管理和存储的系统软件。它在操作系统的支持下，支持用户对数据库的各项操作。DMBS主要包括以下功能：

①数据库的建立和维护功能

包括建立数据库的结构和数据的录入与转换、数据库的转储和回复、数据库的重组与性能监视等功能。

②数据定义功能

包括定义全局数据结构、局部逻辑数据结构、存储结构、保密模式及信息格式等功能。保证存储在数据库中的数据正确、有效和相容，以防止不合适语义的错误数据被输入或输出。

③数据操纵功能

包括数据查询统计和数据更新两个方面。

④数据库的运行管理功能

这是数据库管理系统的核心部分，包括并发控制、存取控制、数据库内部维护等功能。

⑤通信功能

DBMS其他软件系统之间的通信，如Access能与其他Office组件进行数据交换。

数据库系统（Database System，DBS）是一个人一机系统，一般由硬件、操作系统、数据库、DBMS、应用软件和数据库用户（包括数据库管理员）组成。用户可以通过DBMS操作数据库，也可以通过应用程序操作数据库。

应用程序是利用DBMS，为解决某个具体的管理或数据处理任务而编制的一系列命令的有序集合，

数据库管理员（Database Administrator，DBA）负责数据库的更新和备份、数据库系统的维护、用户管理等工作，保证数据库的正常运行。

二、关系型数据库与非关系型数据库

1、关系型数据库的基本结构

关系型数据库使用的存储结构是多个二维表格，即反映事物及其联系的数据描述是以平面表格的形式体现的。

在每个二维表中，每一行称为一条记录，用来描述一个对象的信息；每一列称为一个字段，用来描述对象的一个属性。数据表与数据库之间存在相应的关联，这些关联用来查询相关的数据。

关系数据库是由数据表之间的关联组成的。其中：

• 护数据表通常是一个由行和列组成的二维表，每一个数据表分别说明数据库中某一特定的方面或部分的对象及其属性。
• 数据表中的行通常叫做记录或者元组，它代表众多具有相同属性的对象中的一个。
• 数据表中的列通常叫做字段或者属性，它代表相应数据库中存储对象的共有的属性。

2、主键与外键

1）主键

数据表中的每行记录都必须是唯一的，而不允许出现完全相同的记录，通过定义主键（主关键字，Primary Key）可以保证记录（实体）的唯一性。

主键唯一标识表中的行数据，一个主键值对应一行数据。主键由一个或多个字段组成，其值具有唯一性，不允许取空值（NULL）。一个表只能有一个主键。

2）外键

一个关系数据库通常包含多个表，通过外键（Foreign Key）可以使这些表关联起来。

外键是用于建立和加强两个表数据之间的链接的一列或多列。通过将表中主键值的一列或多列添加到另一个表中，可以创建两个表之间的链接。这个列就称为第二个表的外键。

3、数据完整性规则

为了维护数据库中的数据和现实世界的一致性，关系数据库的数据与更新操作必须遵守下列四类完整性规则。

1）实体完整性规则

实体完整性规则要求关系中的元组（记录）在主键的属性上不能有空值，如果出现空值，那么主键值就起不到唯一标识元组的作用。

2）域完整性规则

域完整性也称列完整性，指定一个数据集对某一个列是否有效或确定是否允许空值。

3）引用完整性规则

如果两个表之间相互关联，那么引用完整性规则要求不允许引用不存在的元组。

4）用户定义的完整性规则

用户定义的完整性规则是针对某一具体数据的约束条件，由应用环境决定。它反映了某一具体应用所设计的数据必须满足的语义要求。

三、Mysql数据库

1、Mysql的编译安装

前面介绍的LAMP/LNMP中架构搭建中已有讲到Mysql数据库的编译安装，不再赘述。

下面附上一键执行的手工编译安装Mysql数据库脚本。

#！/bin/bash
MYSQL_INSTALL () {
#上传安装包mysql-5.7.17.tar.gz和boost_1_59_0.tar.gz到目录/opt，解包到指定目录并重命名
tar zxf mysql-5.7.17.tar.gz -C /opt
tar zxf boost_1_59_0.tar.gz -C /usr/local/
mv /usr/local/boost_1_59_0 /usr/local/boost

#安装编译环境
yum -y install gcc gcc-c++ cmake ncurses ncurses-devel bison

#cmake配置并加载相关功能模块
cd /opt/mysql-5.7.17
cmake
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock
-DSYSCONFDIR=/etc
-DSYSTEMD_PID_DIR=/usr/local/mysql
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_EXTRA_CHARSETS=all
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1
-DMYSQL_DATADIR=/usr/local/mysql/data
-DWITH_BOOST=/usr/local/boost
-DWITH_SYSTEMD=1

#make编译并安装
make -j4 && make install

#创建普通用户管理mysql，并更改管理主、组
useradd -s /sbin/nologin mysql
chown -R mysql:mysql /usr/local/mysql
chown mysql:mysql /etc/my.cnf

#修改配置文件/etc/my.cnf
echo "[client]
port = 3306
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock
auto-rehash

[mysqld]
user = mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character-set-server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections=2048
default-storage-engine=INNODB
max_allowed_packet=16M
server-id = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES" > /etc/my.cnf

#设置环境变量，申明mysql命令便于系统识别
echo “PATH=$PATH:/usr/local/mysql/bin” >> /etc/profile
source /etc/profile

#初始化数据库
cd /usr/local/mysql/bin/
./mysqld
–initialize-insecure
–user=mysql
–basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data
#使用systemctl工具来进行服务控制
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system
systemctl daemon-reload
systemctl start mysqld.service
systemctl enable mysqld

##给root账号设置密码
mysqladmin -u root -p password “123456”
}
MYSQL_INSTALL

2、访问数据库

Mysql数据库系统是典型的C/S架构的应用，要访问Mysql数据库需要使用专门的客户端软件。在Linux系统中，最简单、易用的Mysql客户端软件是其自带的Mysql命令工具。

1）登录到Mysql服务器

经过安装后的初始化过程，Mysql数据库的默认管理员用户名为“root”，密码为给定或者安装时设定的随机密码。以root用户登录本机的Mysql数据库，可执行以下操作：

2）执行mysql语句操作

验证成功以后将会进入提示符为“mysql>”的数据库操作环境，用户可以输入各种操作语句对数据库进行管理。每一条Mysql操作语句以分号“；”表示结束，输入时可以不区分大小写，但习惯上讲MYSQL语句中的关键字部分大写。

例如，以用户名root登录到“mysql>”环境后，执行“SHOW DATABASES;"语句可以查看当前数据库中有哪些库。

3）退出”mysql>"操作环境

在“mysql>”操作环境中，执行“EXIT”或“QUIT”命令可以退出mysql命令工具。返回原来的shell环境。

3、使用mysql数据库

1）查看数据库结构

①查看当前服务器中包含的库

SHOWBASES语句：用于查看当前Mysql服务器中包含的库。经初始化后的Mysql服务器，默认建立了四个库：sys、mysql、information_schema和performence_schema（其中mysql库中包含了用户认证相关的表）。执行下列操作可以查看：

②查看当前使用的库中包含的表

SHOW TABLES语句：用于查看当前所在的库中包含的表。在操作之前，需要先使用USE语句切换到所使用的库。例如执行下列操作可以显示Mysql库中包含的所有表：

Mysql数据库的数据文件存放在/usr/local/mysql/data目录下，每个数据库对应一个子目录，用于存储数据表文件。每一个数据表对应为三个文件，后缀名分别为“.frm"、".myd"和“.myi”。也有少数以opt、csm、csv、ibd结尾的。

③查看表的结构

DESCRIBE语句：用于显示表的结构，即组成表的各字段（列）的信息。需要指定“库名.表名”作为参数；若至指定表名参数，则需要先通过USE语句切换到目标库。例如执行下面操作可以查看mysql库中的user表的结构，与直接执行“DESCRIBE mysql.user；”语句的效果相同。

数据库目前标准的指令集是SQL（Structured Query Langugae，结构化查询语言）。

SQL语言主要由以下几个部分组成：

• DDL（Data Definition Language，数据定义语言）：用来建立数据库、数据库对象和定义其列，如CREATE、ALERT、DROP。
• DML（Data Manipulation Language，数据操纵语言）：用来查询、插入、删除和修改数据库中的数据，如SELECT、INSERT、UPDATE、DELETE。
• DCL（Data Control Language，数据控制语言）：用来控制数据库组件的存取许可、存取权限等，如COMMIT、ROLLBACK、GRANT、REVOKE。
• DQL（数据查询语言）：主要宝库数据库中表数据的查询

2）创建及删除库和表

①创建新的库

CREATE DATABASE语句：用于创建一个新的库，需指定数据库名称作为参数。例如执行下面操作，创建一个名为FP的新库：

刚创建的数据库是空的，其中不包含任何表，在/usr/local/mysql/data目录下会自动生成一个与新建的库名相同的文件夹。

②创建新的表

CREATE TABLE语句：用于在当前库中创建新的表，需指定数据表名称作为参数，并定义该表格使用的各字段。基本格式为：

CREATE TABLES 表名 （字段1 名称 类型，字段2 名称 类型，… ,PRIMARY KEY（主键名））

创建表之前，应先明确数据表的结构、各字段的名称和类型的信息。例如，若要创建一个包含用户名、密码字串的用户验证表如下，应先分析表结构：

表格中，”用户名“为不超过16个字符的字符串，且不能为空；”密码字串“为不超过48个字符的字符串（插入记录时使用Mysql的函数加密），默认值为空字串。考虑到字符集的兼容性，最好不使用中文字段名，因此改用user_name、user_passwd分别表示用户名、密码字串。表格中不能拥有同名的用户，因此可将user_name作为主键。

③删除一个数据表

DROP TABLE语句：用于删除库中的表，需要指定”库名.表名“作为参数；若只指定表明参数，则需先通过执行USE语句切换到目标数据库。例如执行下面操作可以删除FP库中的users表。

④删除一个数据库

DROP DATABASE语句：用于删除指定的库，需指定库名作为参数。例如执行以下操作可以删除名为FP的库：

3）管理表中的数据记录

①插入数据记录

INSERT INTO语句：用于向表中插入新的数据记录。语句格式如下：

INSERT INTO 表名 （字段1，字段2，…) VALUES(字段1的值，字段2的值,…)

执行以下操作会向FP库中的users表插入一条记录

在插入新的数据记录时，如果这条记录完整的包括表中所有字段的值，则插入语句中指定字段的部分可以省略，例如，再向FP库的users表中插入一条记录：用户名yyl，密码子串654321;可执行下面操作：

②查询数据记录

SELECT语句：用于从指定的表中查询符合条件的数据记录。Mysql数据库支持标准的SQL查询语句，语句格式如下：

SELECT 字段名1，字段名2，… FROM 表明 WHERE 条件表达式

表示所有字段时，可以使用通配符” * “，若要显示所有的数据记录则可省略WHERE条件字句。

例如：执行下面操作可以查看FP库中users表内的所有数据记录，其中密码字段因已加密，因此不会直接显示出实际的密码内容：

当需要根据特定条件进行查找时，WHERE条件子语句则是必不可少的。例如要查找users表中名为”yyl“的记录，显示其用户名、密码字段的信息，可以执行操作如下：

③修改数据记录

UPDATA语句：用于修改、更新表中的数据记录。语句格式如下：

UPDATA 表名 SET 字段名1=字段值 [，字段名2=字段值2] WHERE 条件表达式

例如，执行下面操作，修改users表中用户名为zhangsan的记录，设置其密码子串为空值

④删除数据记录

DELETE语句：用于删除表中指定的数据记录。语句格式为：

DELETE FROM 表名 WHERE 条件表达式

例如：，执行以下操作将users表中用户名为”fangpeng“的数据记录删除，并验证删除情况

四、小结

• 数据库由数据库表和其他数据对象组成。
• 主键有一个或多个字段组成，其值具有唯一性，而且不允许取空值（NULL）。一个表只能有一个主键。
• 一个关系数据库通常包含多个表，可以通过外键将这些表关联起来。
• MYSQL是一个开源的SQL数据库软件，默认使用TCP 3306端口提供服务，配置文件是/etc/my.cnf。
  记录