MySQL数据库基础理论、部署与使用

目录

一、MySQL数据库

Ⅰ、数据库的类型

（1）关系数据库

（2）非关系型数据库（NoSQL）

Ⅱ、MySQL数据库的字符类型（）

二、MySQL-5.7的部署

Ⅰ、编译安装

Ⅱ、调整配置文件

Ⅲ、初始化数据库

 三、MySQL基础操作（增、删、改、查）语句

Ⅰ、查（DML语句）

（1）查看所有库

（2）查看指定库的表

（3）查看表结构

（4）查看指定表的指定信息

Ⅱ、增（DDL语句）

（1）创建新的库和表

（2）以已创建的表为基准创建表（克隆）

 （3）向表中插入数据

Ⅲ、删（DDL语句）

（1）删除表、库

（2）删除数据（delete、truncate、drop）

（3）删除表字段

Ⅳ、改（DML语句）

（1）修改数据记录

（2）修改表结构、表名、表字段

---

一、MySQL数据库

一款深受欢迎的开源关系型数据库，遵守GPL协议，可以免费使用与修改

常用版本：MySQL-5.7

mysql是目前最流行的关系型数据库管理系统，也是在WEB应用方面最好的应用软件之一，而学习mysql操作对于我们学习mysql数据库是必不可少的。

Ⅰ、数据库的类型

（1）关系数据库

关系数据库结构是二维数据库表，二维表当中每个字段（列)用来描述对象的一个属性，每个记录(行)用来描述一个对象的信息，关系数据库写到哪里也就是存储在硬盘当中
读写系统就会受到的Io限制或者瓶颈,关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织

优点:易于维护:都是使用表结构，格式一致；使用方便,SQL语言通用，可用于复杂查询；
复杂操作，支持sQL，可用于一个表以及多个表之间非常复杂的查询。
缺点:读写性能比较差，尤其是海量数据的高效率读写；固定的表结构，灵活度稍欠；高并发读写需求，传统关系型数据库来说，硬盘I/o是一个很大的瓶颈。

（2）非关系型数据库（NoSQL）

MongoDB 、 Redis（内存数据库/缓存数据库）K-V键值对、与之类似的Memcache,K-V键值对
redis-memecache对比：
相同点：存储高热数据(在内存中高速运行）
不同点：redis可以做持久化保存，可以存储对象

非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等;使用灵活，应用场景广泛，而关系型数据库则只支持基础类型；速度快,nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；高扩展性；成本低,nosql数据库部署简单，基本都是开源软件。

缺点：不提供sql支持，学习和使用成本较高；无事务处理；数据结构相对复杂，复杂查询方面稍欠。

Ⅱ、MySQL数据库的字符类型（）

二、MySQL-5.7的部署

Ⅰ、编译安装

如果linux系统中已经安装了mariadb数据量，则必须先将其删除

#查看
rpm -q mariadb
#删除
yum remove mariadb

MySQL官方站点：https://dev.mysql.com/

编译安装所需的编译器等，创建一个mysql用户管理

yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake
adduser -s /sbin/nologin mysql

 对安装包进行解压缩，然后进行编译安装

tar -zxvf mysql-5.7.*.tar.gz -C /opt
cd ../mysql-5.7.*
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \		#指定安装路径（目录）
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \	#指定通信文件,连接数据库，通讯协议的载体
-DSYSCONFDIR=/etc \								#配置文件目录指向/etc
-DSYSTEMD_PID_DIR=/usr/local/mysql \			#指定pid目录文件
-DDEFAULT_CHARSET=utf8 \						#字符集设定
-DDEFAULT_COLLATION=utf8_general_ci \			#字符集设定
-DWITH_INNOBASE_STORAGE_ENGINE=1 \				#以下三行均为存储引擎ENGINE
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \			#指定数据存放位置
-DWITH_BOOST=boost \							#底层C++运营库 独立的表空间结构
-DWITH_SYSTEMD=1								#守护进程id 守护进程 
 
make && make install
#权限管理
chown -R mysql:mysql /usr/local/mysql/

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 \
-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=boost \
-DWITH_SYSTEMD=1

Ⅱ、调整配置文件

[root@localhost mysql-5.7.20]# vi /etc/my.cnf
[root@localhost etc]# vim my.cnf
#删除原有内容，添加以下内容
[client]					#client 用户程序
port = 3306				
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
 
[mysql]						#mysql 软件内容 
port = 3306					#端口
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
 
[mysqld]					#以后对mysql配置时 更多的会针对于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	#PID文件目录
socket = /usr/local/mysql/mysql.sock	#通讯文件
server-id = 1				#服务id，在之后的mysql集群中用于标识mysql服务器
 
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

 

[client]
port = 3306				
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
 
[mysql]	
port = 3306default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
 
[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
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

Ⅲ、初始化数据库

设置环境变量

echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile
source /etc/profile

初始化并添加系统服务

cd /usr/local/mysql/
 
bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
 
#配置系统服务
[root@ljp mysql-5.7.17]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +X /etc/rc.d/init.d/mysqld
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
 
systemctl daemon-reload
systemctl enable mysqld --now

数据库开启自启、 关闭、状态查看
systemctl enable mysqld
systemctl start mysqld
systemctl stop mysqld
systemctl status mysqld
netstat -anpt | grep 3306

systemctl start mysqld                         # 开启mysql
echo "validate-password=OFF">> /etc/my.cnf     # 关闭密码强度审计插件

  设置Mysql密码并登录

mysqladmin -u root -p password 
#直接回车进入设置密码
mysql -u root -p'password'

登录成功 

 三、MySQL基础操作（增、删、改、查）语句

SQL语言分类：

 示例：

#创建表family（如果不存在）,id设置为主键，不满4位前面用0填充，并且每条记录自增1
姓名不能为null
mysql>create table if not exists family(id int(4)zerofill primary key 
auto_increment,name char(16) not null);
Query OK, 0 rows affected (0.02 sec)
 
mysql> select * from family;
Empty set (0.00 sec)
 
mysql> desc family;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type                     | Null | Key | Default | Extra          |
+-------+--------------------------+------+-----+---------+----------------+
| id    | int(8) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| name  | char(16)                 | NO   |     | NULL    |                |
+-------+--------------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Ⅰ、查（DML语句）

（1）查看所有库

mysql>show databases;

（2）查看指定库的表

#进入sys库
mysql>use sys;
#查看库中所有表
mysql>show tables;

（3）查看表结构

#查看表结构
mysql> desc test.test1;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| id          | int(5)   | NO   | PRI | NULL    |       |
| user_name   | char(8)  | NO   |     | NULL    |       |
| user_passwd | char(24) | YES  |     |         |       |
+-------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 

（4）查看指定表的指定信息

#查看test1表id为1的user_name和user_passwd信息
mysql>select user_name,user_passwd from test.test1 where id=1;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| paul      | 24156       |
+-----------+-------------+
1 row in set (0.00 sec)
 
#查看test1表全部信息
mysql>select * from test.test1;
+----+-----------+-------------+
| id | user_name | user_passwd |
+----+-----------+-------------+
|  1 | paul      | 24156       |
|  2 | jack      | 1987680     |
+----+-----------+-------------+
2 rows in set (0.00 sec)

Ⅱ、增（DDL语句）

（1）创建新的库和表

#创建新库
mysql> create database test;
#显示如下表示成功
Query OK, 1 row affected (0.00 sec)
 
#创建新表(在当前所在库中)
#default''表示设置默认为空的密码字串，not null表示不能为空，primary key表示主键
mysql>create table test1(id int(5),user_name char(8)not null,user_passwd 
char(24)default'',primary key(id));
#显示如下表示成功
Query OK, 1 row affected (0.00 sec)

（2）以已创建的表为基准创建表（克隆）

mysql> create table test_02 like test.test_01;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test_02 select * from test_01;
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| family         |
| test_01        |
| test_02        |
+----------------+
3 rows in set (0.00 sec)
 
或者：
mysql> create table test_02 (select * from test_01);

 （3）向表中插入数据

mysql>insert into test.test1(id,user_name,user_passwd); values(1,'paul',PASSWORD('241568790'));
#若不使用PASSWOR(),查询时以明文显示。
 
或者
mysql>insert into test.test1 values(2,'jack','1987680');

 #如果要使用PASSWORD(),那你的密码不能"简单"，所有这里没有用，导致user_passwd都是明文显示。

Ⅲ、删（DDL语句）

（1）删除表、库

#删除test库中的test1表
mysql>drop table test.test1;
#删除test库
mysql>drop database test;

（2）删除数据（delete、truncate、drop）

DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。(不会删除原表结构)

#将user_name为paul的数据记录删除
 
mysql> select * from test.test1;
+----+-----------+-------------+
| id | user_name | user_passwd |
+----+-----------+-------------+
|  1 | paul      | 24156       |
|  2 | jack      | 1987680     |
+----+-----------+-------------+
2 rows in set (0.00 sec)
 
mysql> delete from test.test1 where user_name='paul';
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from test.test1;
+----+-----------+-------------+
| id | user_name | user_passwd |
+----+-----------+-------------+
|  2 | jack      | 1987680     |
+----+-----------+-------------+
1 row in set (0.00 sec)

TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录（删除后重新建立）

DROP清空表，直接删除表结构和数据记录。（直接抹除）

执行速度：drop>truncate>delete

（3）删除表字段

#删除test_01表的user_passwd字段
mysql> desc test_01;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| id          | int(5)   | NO   | PRI | NULL    |       |
| uname       | char(12) | YES  | UNI | NULL    |       |
| user_passwd | char(24) | YES  |     |         |       |
| sex         | char(8)  | YES  |     | unknow  |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> alter table test_01 drop user_passwd;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc test_01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(5)   | NO   | PRI | NULL    |       |
| uname | char(12) | YES  | UNI | NULL    |       |
| sex   | char(8)  | YES  |     | unknow  |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Ⅳ、改（DML语句）

（1）修改数据记录

#将user_name为paul的user_passwd修改为空
 
mysql> update test.test1 set user_passwd='' where user_name='paul';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from test.test1;
+----+-----------+-------------+
| id | user_name | user_passwd |
+----+-----------+-------------+
|  1 | paul      |             |
|  2 | jack      | 1987680     |
+----+-----------+-------------+
2 rows in set (0.00 sec)

（2）修改表结构、表名、表字段

#修改表名
#test1 ---》 test_01
mysql> alter table test.test1 rename test_01;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_01        |
+----------------+
1 row in set (0.00 sec)

#修改表结构
#增加sex字段，默认值unknow
mysql> alter table test_01 add sex char(8) default 'unknow';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc test_01;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| id          | int(5)   | NO   | PRI | NULL    |       |
| user_name   | char(8)  | NO   |     | NULL    |       |
| user_passwd | char(24) | YES  |     |         |       |
| sex         | char(8)  | YES  |     | unknow  |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#修改表字段名，添加唯一键
#将user_name修改为uname，并添加唯一键（不能重复）
mysql> alter table test_01 change user_name uname char(12) unique key;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> desc test_01;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| id          | int(5)   | NO   | PRI | NULL    |       |
| uname       | char(12) | YES  | UNI | NULL    |       |
| user_passwd | char(24) | YES  |     |         |       |
| sex         | char(8)  | YES  |     | unknow  |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)