mysql
2017年10月11日
16:39
两种方法查询该端口哪个应用程序在用
centos 7.3二进制安装mariadb10.2.8
（1）在官方网站下载最新的mariadb包，然后rz到linux系统上去
（2）useradd -r -d /app/mysqldb -m -s /sbin/nologin mysql 创建mysql用户
（3）准备数据目录
以/app/mysqldb为例，建议使用逻辑卷，因为这个目录是存放数据库的，会逐渐增大，而逻辑卷能够扩展。
（4）tar xvf mariadb-10.2.8-linux-x86_64.tar.gz -C /usr/local(默认是放在/usr/local这个目录下)
cd /usr/local，进入到这个目录下，mariadb的数据库的目录已经生成，但是这个目录不符合要求，要求mariadb数据库必须放在mysql目录下，所以有两种方法解决，一是改名，将生成的mariadb数据库目录改为mysql,二是创建软链接，ln -s mariadb-10.2.8-linux-x86_64.tar.gz/ mysq,我们就选择创建软链接的方式。
ln -s mariadb-10.2.8-linux-x86_64.tar.gz/ mysq
（5）准备mysql的配置文件
cd /usr/local/mysql/support-files （这个目录下的一些文件可以当mysql的配置文件）
mkdir /etc/mysql/ （建立mysql配置文件的目录）
cp my-huge.cnf /etc/mysql/my.cnf （mysql的配置文件名也是有要求的，所以要改名为my.cnf）
然后再编辑配置文件
vim /etc/mysql/my.cnf
[mysqld]
datadir = /app/mysqldb （数据库的数据放在哪，关键的一行，必须加，下面两行可以不加）
innodb_file_per_table = on （数据库中有很多表，加上这一行就可以使每个表单独生成一个文件）
skip_name_resolve = on （为了加速访问速度，忽略名字的反向解析）
这样配置文件就编辑好了
（6）scripts/mysql_install_db --user=mysql --datadir=/app/mysqldb 创建mysql的数据库，指定数据库的路径以及用户名身份
创建mysql的系统数据库，系统提供了一个脚本，我们直接可以利用它来生成mysql的系统数据库，这个脚本就在scripts下，但是不要进去运行脚本，否则会报错
我们先进去运行一下，来看一下效果，如下图：它找./bin当前目录的bin来运行，但是当前目录下没有bin,而bin在上级目录中，在/usr/local/mysql下，所以只能/usr/local/mysql目录下运行脚本
（7）启动服务
在/usr/local/mysql/support-files目录下有一个现成的服务脚本，但是这个服务脚本必须放在规定的目录下才能使用，这个规定的目录就是/etc/init.d/
cp support-fil
es/mysql.server /etc/init.d/mysqld 复制到这个目录下，并改名为mysqld，将来这个服务名就叫mysqld
chkconfig --list mysqld 先列出mysqld的服务，看checkconfig指令能否管理它
chkconfig --add mysqld 添加mysqld服务，让checkconfig指令来管理它
service mysqld start 失败，启动mysql服务，还需要向/var/log/mariadb/这个目录下写日志，但是这个目录还没有创建出来，所以启动服务失败，所以下一步要创建这个目录
（8）创建mysql日志目录，因为是以mysql用户的身份来写日志的，所以创建了这个目录还必须修改一下其所属人为mysql
mkdir /var/log/mariadb/
chown mysql /var/log/mariadb/
service mysqld start 再一次启动mysql服务，成功
（9）添加PATH变量，以方便来运行mysql程序
vim /etc/profile.d/mysql.sh
PATH=/usr/local/mysql/bin:$PATH
. /etc/profile.d/mysql.sh
（10）运行mysql安全脚本
cd /usr/local/mysql
mysql_secure_installation
（11）测试，运行一下mysql程序，-u指定用户，-p后面跟口令（该口令是上一步运行安全脚本给root用户设置的口令）
mysql -uroot -p
数据三要素
v 数据结构：
Ü 包括两类，一类是与数据类型、内容、性质有关的对象，
比如关系模型中的域、属性和关系等；另一类是与数据之
间联系有关的对象，它从数据组织层表达数据记录与字段
的结构
v 数据的操作：
Ü 数据提取：在数据集合中提取感兴趣的内容。（SELECT） ）
Ü 数据更新：变更数据库中的数据。（INSERT 、DELETE
、 、UPDATE
v 件 数据的约束条件 ：是一组完整性规则的集合
Ü 性 实体（行）完整性 Entity integrity
Ü 性 域（列）完整性 Domain Integrity
Ü 性 参考完整性 Referential Integrity
简易数据规划流程
v 第一阶段：收集数据，得到字段
• 收集必要且完整的数据项
• 转换成数据表的字段
v 第二阶段：把字段分类，归入表，建立表的关联
• 关联：表和表间的关系
• 分割数据表并建立关联的优点
• 节省空间
• 减少输入错误
• 方便数据修改
v 第三阶段：
• 规范化数据库
范式
1NF：无重复的列，每一列都是不可分割的基本数据项，同一列不能有多个值，即实体中的摸个属性不能有多个值或者不能有重复的属性。除去同类型的字段，就是无重复的列
说明：第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库
2NF：属性完全依赖于主键，第二范式必须满足第一范式，要求表中的每个行必须可以被唯一的区分。通常为表加上一个列，以存储各个实例的唯一标识PK，非PK的字段需要与整个PK有直接相关性
3NF：属性不依赖于其他非主属性，满足第三范式必须满足第二范式。第三范式要求一个数据库表中不包含已在其他表中已包含的非主关键字信息，非PK的字段间不能有从属关系
SQL 概念
v SQL: Structure Query Language
结构化查询语言
SQL 解释器：
数据存储协议：应用层协议，C/S
v S ：server, 监听于套 接 字，接收并处理客户端的应用请求
v C ：Client
程序接口
CLI
GUI
应用编程接口
ODBC ：Open Database Connectivity
JDBC ：Java Data Base Connectivity
约束
约束：表中的数据要遵循的限制
主键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；必须提供数据，即NOT NULL，一个表只能有一个
唯一键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为NULL，一个表可以存在多个
外键：一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
检查：字段值在一定范围内
基本概念
索引：将表中的一个或多个字段中的数据复制一份另存，并且此需要按照特定次序排序存储
关系运算：
选择：挑选出符合条件的行
投影：挑选出需要的字段
连接：表间字段的关联
数据模型
v 数据抽象：
Ü 物理层：决定数据的存储格式，即RDBMS 在磁盘上如何组
织文件
Ü 逻辑层：描述存储什么数据，以及数据间存在什么样的关
系
Ü 视图层：描述DB 中的部分数据
v 关系模型的分类：
Ü 关系模型
Ü 基于对象的关系模型
Ü 半结构化的关系模型：XML 数据
安装MySQL
v 安装方式：
v 1 、源代码：编译安装
v 2 、二进制格式的程序包：展开至特定路径，并经过简单配置
后即可使用
v 3 、程序包管理器管理的程序包
CentOS 安装光盘
项目官方：
https://downloads.mariadb.org/mariadb/repositories/
v 4 、安装和使用MariaDB
CentOS 7 直接提供
CentOS 6
MariaDB的特性 ：
单进程 ，多进程
插件式存储引擎：存储管理器有多种实现版本，功能和特性可能均略有差别；用户可以根据需要灵活选择
存储引擎也称之为“表类型”
更多的存储的存储引擎
MyISAM ==> Aria
InnoDB ==> XtraDB
Mysql5.5.8 开始innoDB 引擎是MYSQL 默认引擎，
InnoDB 对比MyISAM 的最大特点就是InnoDB 支持事务
v (2) 诸多扩展和新特性
v (3) 提供了较多测试组件
v (4) 开源
MariaDB
Centos7 的MariaDB 数据库包括两个包组：
Mariadb mariadb-client
v mariadb 组包括下面包：
mariadb-server ：mandatory package
mariadb-bench ：optional package
mariadb-test ：optional package
v mariadb-client 组包括下面包:
Mariadb ：mandatory package
MySQL-python ：default package
mysql-connector-odbc ：default package
libdbi-dbd-mysql ：optional package
mysql-connector-java ：optional package
perl-DBD-MySQL ：optional package
v 配置文件：
/etc/my.cnf 和/etc/my.cnf.d/ *.cnf
MariaDB安装
安装
Yum groupinstall mariadb mariadb-client
systemctl start mariadb
systemctl enable mariadb
提高安全性
mysql_secure_installation
设置数据库管理员root口令
禁止root远程登录
删除anonymous 用户账号
删除test数据库
MairaDB 配置
侦听3306/tcp端口可以在绑定有一个或全部接口IP 上
vim /etc/my.cnf
[mysqld] 加一行：
skip-networking=1
只侦听本地客户端，所有和服务器的交互都通过一个socket实现，socket的配置存放在/var/lib/mysql/mysql.socket) 可在/etc/my.cnf修改
firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload
MariaDB 程序
MariaDB 的程序组成：
client:
mysql:CLI交互式客户端程序
mysqldump, mysqladmin...
Server:
mysqld_safe
mysqld
mysqld_multi ：多实例
服务器监听的两种socket地址：
IP socket：监听在tcp的3306端口，支持远程通信
unix sock: 监听在sock 文件上(/tmp/mysql.sock,
/var/lib/mysql/mysql.sock) ，仅支持本机通信
server: localhost, 127.0.0.1 自动使用unix soc
客户端工具
命令行交互式客户端程序：mysql
mysql选项：
-uUSERNAME：用户名；默认为root
-hHOST:服务器主机；默认为localhost
-pPASSWORD：用户的密码；建议使用-p，默认为空密码
mysql用户账号由两部分组成：
'USERNAME'@'HOST'
HOST 用于限制此用户可通过哪些远程主机连接mysql服务
支持使用通配符：
%匹配任意长度的任意字符
172.16.0.0、16或172.16.%。%
— 匹配任意单个字符
查询有哪些用户在mysql上登录：
执行命令
运行mysql命令：默认空密码登录
mysql>use mysql
mysql>select user(); 查看当前用户
mysql>SELECT User,Host,Password FROM user;
安全初始化
/usr/local/mysql/bin/mysql_secure_installatoin
登录系统：mysql –uroot –p
客户端命令：本地执行
mysql> help
每个命令都完整形式和简写格式
mysql> status 或 或 \s
关系型数据库的常见组件
数据库：database
表：table
行：row
列：column
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure ，无 返回值
存储函数：function ，有 返回值
触发器：trigger
事件调度器：event scheduler
SQL 语句构成
Keyword 组成clause
多条clause 组成语句
SELECT * SELECT 子句
FROM products FROM 子句
WHERE price>400 WHERE 子句
这是一组语句，由三个子句构成，SELECT,FROM 和WHERE
（不要用关键字给表名起名）
SQL 语句分类
SQL 语句：
DDL: Data Defination Language
CREATE, DROP, ALTER
DML: Data Manipulation Language
INSERT, DELETE, UPDATE
DCL ：Data Control Language
GRANT, REVOKE
DQL ：Data Query Language
SELECT
39
SQL 语言规范
在数据库系统中，SQL 语句不区分大小
数据库操作
创建数据库：
CREATE DATABASE|SCHEMA [IF NOT EXISTS]
'DB_NAME';
CHARACTER SET 'character set name'
COLLATE 'collate name'
删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
查看支持所有字符集：SHOW CHARACTER SET;
查看支持所有排序规则：SHOW COLLATION;
获取命令使用帮助：
mysql> HELP KEYWORD;
查看数据库列表：
mysql> SHOW DATABASES;
创建表
CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1
修饰符, col2 type2 修饰符, ...)
字段信息
• col type1
• PRIMARY KEY(col1,...)
• INDEX(col1, ...)
• UNIQUE KEY(col1, ...)
表选项：
• ENGINE [=] engine_name
SHOW ENGINES; 查看支持的engine 类型
• ROW_FORMAT [=]
{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT
|COMPACT}
获取帮助：mysql> HELP CREATE TABLE;
表操作
查看所有的引擎：SHOW ENGINES;
查看表：SHOW TABLES [FROM db_name];
查看表的内容，并让其竖着显示：
查看表结构：DESC [db_name.]tb_name;
删除表：DROP TABLE [IF EXISTS] tb_name； ；
查看表创建命令：SHOW CREATE TABLE tbl_name;
查看表状态：SHOW TABLE STATUS LIKE 'tbl_name'
索引
索引是特殊数据结构；定义在查找时作为查找条件的字段
优点：提高查询速度
缺点：占用额外空间，影响插入速度
创建索引必须要有索引名称
创建索引：
CREATE INDEX index_name ON tbl_name
(index_col_name,...);
help CREATE INDEX
删除索引：
DROP INDEX index_name ON tbl_name;
查看索引：
SHOW INDEXES FROM [db_name.]tbl_name;
DML 语句
查看表结构：
DML: INSERT, DELETE, UPDATE
• INSERT
INSERT [INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} (val1,...),(...),...
添加表记录：
查询此表中有几条数据：
利用select * from students的执行结果来创建student3这张新表
删除表的内容；drop删除整个表
将select * from students的执行结果插入到student3这张新表里
• UPDATE:
UPDATE tbl_name SET col1=val1, col2=val2, ...
[WHERE clause] [ORDER BY 'col_name' [DESC]]
[LIMIT [m,]n];
• DELETE:
DELETE FROM tbl_name [WHERE clause] [ORDER
BY 'col_name' [DESC]] [LIMIT [m,]n];
可先排序再指定删除的行数
• TRUNCATE TABLE tbl_name; 清空 表
DQL 语名
DQL ：SELECT
• SELECT col1,col2,... FROM tbl_name [WHERE clause]
[ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];
Limit m,n 跳过m 个，要n个 个
字段表示法：
*: 所有字段
as ：字段别名，col1 AS alias1
