目录
引言
MySQL 是一个真正的多线程，多用户的 SQL 数据库服务，凭借其高性能、高可靠性和易于使用的特性，成为服务器领域中最受欢迎的开源数据库系统。
一、MySQL 的编译安装
- 准备安装包
#安装包放进/opt/目录下
[root@lamp /opt]#ls
boost_1_59_0.tar.gz mysql-5.7.17.tar.gz rh
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
- 安装环境依赖包
yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake
- 创建运行用户
useradd -s /sbin/nologin mysql
- 解压安装包
#解包
tar zxvf mysql-5.7.17.tar.gz -C /opt
tar zxvf boost_1_59_0.tar.gz -C /usr/local/
#重命名
mv /usr/local/boost_1_59_0 /usr/local/boost
- 配置软件模块，编译安装
cd mysql-5.7.20/
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=boost \
-DWITH_SYSTEMD=1
make && make install
- 设置权限
chown -R mysql:mysql /usr/local/mysql/
chown mysql:mysql /etc/my.cnf
- 修改 mysql 配置文件
vim /etc/my.cnf #删除原配置项，再重新添加下面内容
[client]
port = 3306
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
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
- 设置环境变量
echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile #立即生效
或
./etc/profile
- 初始化数据库
cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
- 添加系统服务
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
systemctl daemon-reload
systemctl start mysqld.service
systemctl enable mysqld
netstat -anpt | grep 3306
- 访问 MySQL 数据库，修改登陆密码
mysqladmin -u root -p password "123456"
#-u选项用于指定认证用户，有密码的情况下还需使用-p选项来进行密码校验
mysql -u root -p
#授予root用户可以在所有终端远程登录，使用的密码是123456，并对所有数据库和所有表有操作权限
grant all privileges on *.* to 'root'@'%' identified by '123456';
#查看当前已有的数据库
show databases;
#退出 “mysql>”操作环境
quit
或
exit
[root@lamp /opt]#mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> quit
Bye
二、使用 MySQL 数据库
1. 常见的数据类型
int #整型
float #单精度浮点 4字节32位
double #双精度浮点 8字节64位
char #固定长度的字符类型
varchar #可变长度的字符类型
text #文本
image #图片
decimal(5,2) #5个有效长度数字，小数点后面有2位
#Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度
#如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错
2. 查看数据库结构
- 查看当前服务器中包含的库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql>
- 查看当前使用的库中包含的表
USE 数据库名;
SHOW TABLES;
- 查看表的结构（字段）
mysql> use mysql #切换到目标库
mysql> describe user; #查看mysql库中的user表的结构
#也可以直接执行 describe mysql.user; 语句效果相同
3. SQL 语言
数据库目前标准的指令集是SQL，SQL 是 Structured Query Language 的缩写，即结构化查询语言。
SQL 语言主要由以下几部分组成：
- DDL（数据定义语言）：用于建立数据库、数据库对象和定义其列
- DML（数据操纵语言）：用于查询、插入、删除和修改数据库中的数据
- DQL（数据查询语言）：用于从数据表中查找符合条件的数据记录
- DCL（数据控制语言）：用于控制数据库组件的存取许可、存取权限等
4. 创建及删除库和表
4.1 创建新的数据库
CREATE DATABASE 语句：用于创建一个新的库，需指定数据库名称为参数。
#创建名为gouwa的库
mysql> create database gouwa;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gouwa |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
刚创建的数据库是空的，其中不包含任何表，在/usr/local/mysql/data 目录下会自动生成一个与新建的库名相同的空文件夹。
4.2 创建新的表
CREATE TABLE 语句用于在当前库中创建新的表，需指定数据表名称作为参数，并定义该表格所使用的各字段。
#CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
mysql> create table haha (id int NOT NULL,name char(8) NOT NULL,score decimal(5,2),passwd char(36) DEFAULT'',PRIMARY KEY (id));
mysql> show tables; #查看当前库中的表
+-----------------+
| Tables_in_gouwa |
+-----------------+
| haha |
+-----------------+
1 row in set (0.00 sec)
mysql> describe haha; #查看表的结构
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(8) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| passwd | char(36) | YES | | | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
4.3 删除数据表
DROP TABLE 语句用于删除库中的表，需要指定“库名.表名”作为参数；若只指定表名参数，则需先通过执行“USE”语句切换到目标库。
mysql> drop table gouwa.haha; #删除gouwa库中的haha表
Query OK, 0 rows affected (0.00 sec)
4.4 删除数据库
DROP DATABASE 语句用于删除指定的库，需要指定库名作为参数
mysql> drop database gouwa; #删除名为gouwa的库
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
5. 管理表中的数据记录
5.1 插入数据记录
INSERT INTO 语句用于向表中插入新的数据记录
格式：
INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值,...);
案例：
mysql> create database school;
mysql> use school;
mysql> create table class1 (id int not null,name char(20) not null,sex char(1) not null,primary key (id));
mysql> describe class1;
mysql> insert into class1(id,name,sex)values(1,'wangwu','男');
mysql> create database auth;
mysql> create table users (user_name char(20) not null,user_passwd char(68) default '',primary key (user_name));
mysql> insert into users(user_name,user_passwd)values('wangba',password('123456'));
5.2 查询数据记录
SELECT 语句用于从指定的表中查找符合条件的数据记录
#语法格式
SELECT 字段名1，字段名2 ······FROM 表名 WHERE 条件表达式;
#表示所有字段时，可以使用通配符“*”，若要显示所有的数据记录则可以省略WHERE条件子句
-------------------------------------------------------------------------------------------------
#案例
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| wangba | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.01 sec)
-------------------------------------------------------------------------------------------------
#当需要根据特定的条件查找记录时，WHERE条件子句时必不可少的，比如查询users表中用户名为wangba的记录，显示其中用户名，密码字段的信息，操作如下
mysql> select user_name,user_passwd from auth.users where user_name='wangba';
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| wangba | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#查看前两行内容
mysql> select * from users limit 2;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| goudan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| lisi | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#查看第二行后的前三行
mysql> select * from users limit 2,3;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
| wangba | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangsan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
mysql> select * from xinxi;
+----+--------+-----+
| id | name | sex |
+----+--------+-----+
| 1 | meimei | 女 |
| 2 | lisi | 男 |
| 3 | wangr | 女 |
| 4 | gl | 男 |
| 5 | jinqh | 女 |
+----+--------+-----+
5 rows in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#查看xinxi表中id=3的名字
mysql> select id,name from xinxi where id=3;
+----+-------+
| id | name |
+----+-------+
| 3 | wangr |
+----+-------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#以列表方式竖向显示
mysql> select name from xinxi\G
*************************** 1. row ***************************
name: meimei
*************************** 2. row ***************************
name: lisi
*************************** 3. row ***************************
name: wangr
*************************** 4. row ***************************
name: gl
*************************** 5. row ***************************
name: jinqh
5 rows in set (0.00 sec)
------------------------------------------------------------------------------------------------
5.3 修改、更新数据记录
UPDATE 语句用于修改、更新表中得数据记录
#语法格式
UPDATE 表名 SET 字段名 1=字段值 1[，字段名 2= 字段值 2] WHERE 条件表达式;
#案例
mysql> update auth.users set user_passwd=password('') where user_name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| goudan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| gouwa | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
| lisi | |
| wangba | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangsan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
5.4 删除数据记录
DELETE 语句用于删除表中指定的数据记录
#语法格式
DELETE FROM 表名 WHERE 条件表达式;
#案例
mysql> delete from auth.users where user_name='lisi';
Query OK, 1 row affected (0.00 sec)
mysql> select * from auth.users; #可以看到lisi用户已被删除
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| goudan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| gouwa | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
| wangba | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| zhangsan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
5.5 案例扩展
例：
use SCHOOL;
create table if not exists CLASS4 (id int(4) zerofill primary key auto_increment,student_name varchar(20) not null,cardid varchar(18) not null unique key,hobby varchar(50));
-----------------------------------------------------------------------------------------------
#if not exists: 表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill: 表示若数值不满4位数，则前面用“0"填充，例0001
#auto_increment: 表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key: 表示此字段唯一键约束，此字段数据不可以重复;一张表中只能有一个主键，但是一张表中可以有多个唯一键
#not null:表示此字段不允许为NULL
三、数据表高级操作
1. 修改表名和表结构
- 修改表名
#语法格式
ALTER TABLE 旧表名 RENAME 新表名;
mysql> alter table xinxi rename zhiri;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_class1 |
+------------------+
| zhiri |
+------------------+
1 row in set (0.00 sec)
- 扩展表结构
#增加字段
mysql> alter table users add number varchar(50) not null default '号码未知';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from auth.users; #查看
+-----------+-------------------------------------------+--------------+
| user_name | user_passwd | number |
+-----------+-------------------------------------------+--------------+
| goudan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | 号码未知 |
| gouwa | *00A51F3F48415C7D4E8908980D443C29C69B60C9 | 号码未知 |
| wangba | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 号码未知 |
| zhangsan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | 号码未知 |
+-----------+-------------------------------------------+--------------+
4 rows in set (0.00 sec)
#修改字段(列)名，添加唯一键
mysql> alter table users change user_name student_name varchar(20) unique key;
mysql> select * from auth.users;
+--------------+-------------------------------------------+--------------+--------------+
| student_name | user_passwd | number | address |
+--------------+-------------------------------------------+--------------+--------------+
| goudan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | 号码未知 | 地址不详 |
| gouwa | *00A51F3F48415C7D4E8908980D443C29C69B60C9 | 号码未知 | 地址不详 |
| wangba | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 号码未知 | 地址不详 |
| zhangsan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | 号码未知 | 地址不详 |
+--------------+-------------------------------------------+--------------+--------------+
4 rows in set (0.00 sec)
#删除字段
语法格式：ALTER TABLE 表名 DROP 字段名;
mysql> alter table users drop address;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from auth.users;
+--------------+-------------------------------------------+--------------+
| student_name | user_passwd | number |
+--------------+-------------------------------------------+--------------+
| goudan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | 号码未知 |
| gouwa | *00A51F3F48415C7D4E8908980D443C29C69B60C9 | 号码未知 |
| wangba | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 号码未知 |
| zhangsan | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | 号码未知 |
+--------------+-------------------------------------------+--------------+
4 rows in set (0.00 sec)
2. 克隆表
mysql> select * from test;
+----+--------+--------+
| id | name | cardid |
+----+--------+--------+
| 1 | sanba | 123456 |
| 2 | wangba | 12345 |
| 3 | laod | 1234 |
+----+--------+--------+
3 rows in set (0.00 sec)
- 将数据表的数据记录生成到新的表中
#方法1：
mysql> create table test_1 like test;
mysql> describe test_1; #查看表内容
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| cardid | varchar(20) | NO | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into test_1 select * from test;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_1;
+----+--------+--------+
| id | name | cardid |
+----+--------+--------+
| 1 | sanba | 123456 |
| 2 | wangba | 12345 |
| 3 | laod | 1234 |
+----+--------+--------+
3 rows in set (0.00 sec)
#方法2：
mysql> create table test_2 (select * from test);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_2;
+----+--------+--------+
| id | name | cardid |
+----+--------+--------+
| 1 | sanba | 123456 |
| 2 | wangba | 12345 |
| 3 | laod | 1234 |
+----+--------+--------+
3 rows in set (0.00 sec)
3. 清空表
DELETE清空表后，返回的结果内有删除的记录条目，DELETE工作时是一行一行的删除记录数据的 如果表中有自增长字段，使用DELETE
FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录
#方法1
mysql> delete from test_2;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from test_2;
Empty set (0.00 sec)
#方法2
mysql> select * from test_2;
+----+-------+--------+
| id | name | cardid |
+----+-------+--------+
| 1 | wangw | 123123 |
+----+-------+--------+
1 row in set (0.00 sec)
mysql> truncate table test_2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_2;
Empty set (0.00 sec)
mysql> insert into test_2 (name,cardid)values('wangw','123123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_2;
+----+-------+--------+
| id | name | cardid |
+----+-------+--------+
| 2 | wangw | 123123 |
+----+-------+--------+
1 row in set (0.00 sec)
#TRUNCATE 清空表后，没有返回被删除的条目
#TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快
#使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录
4. 创建临时表
临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁
在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表
#语法格式
CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#案例
mysql> create temporary table test_3 (id int not null,name varchar(20) not null,cardid varchar(18) not null unique key,primary key (id));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| test |
| test_1 |
| test_2 |
+-----------------+
3 rows in set (0.00 sec)
mysql> insert into test_3 values (1,'wangl','123321');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_3;
+----+-------+--------+
| id | name | cardid |
+----+-------+--------+
| 1 | wangl | 123321 |
+----+-------+--------+
1 row in set (0.00 sec)
5. 创建外键约束
创建外键约束能保证数据的完整性和一致性
-
外键的定义:
如果同一个属性字段 x 在表一中是主键，而在表二中不是主键，则字段 x 称为表二的外键 -
主键表和外键表的理解：
以公共关键字作为主键的表为主键表（父表、主表）
以公共关键字作为外键的表为外键表（从表、外表）
注意：与外键关联的主表的字段必须设置为主键，要求从表不能是临时表，且主从表的字段具有相同的数据类型、字符长度和约束
- 常见的约束对应：
主键约束 primary key
外键约束 foreign key
非空约束 not null
唯一约束 unique key
默认值约束 default
自增约束 auto_increment
create table test_4 (hobid int(4),hobname varchar(50));
create table test_5 (id int(4) primary key auto_increment,name varchar(50),age int(4),hobid int(4));
alter table test_4 add constraint PK_hobid primary key(hobid);
alter table test_5 add constraint FK_hobid foreign key(hobid) references test04(hobid);
总结
- 数据库由数据库表和其他数据对象组成
- 一个关系数据库通常包含多个表，可以通过外键将这些表关联起来
- MySQL是一个开源的数据库软件，默认端口号3306，配置文件是 /etc/my.cnf
- MySQL的基本管理操作包括查看数据库结构，创建及删除库和表、管理表中的数据记录
978
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
