MySQL基础及远程连接配置

1 简介及数据类型

1.1 基本认识

MySQL是一个数据库，但是我们安装的MySQL数据库服务，服务就会有状态（启动，停止，重启），作用是集中存取管理数据。
数据库核心的对象是表
MySQL核心数据库mysql存放用户和授权信息

1.2 数据类型

1.2.1 字符型

 变长字符  VARCHAR(101), VARBINARY(#)                                                                         
 定长字符  CHAR(#), BINARY(#)   BINARY是区分大小写的 

注意：使用字符型数据时，需要加单引号，select user,host,password from mysql.user where user=’root’

1.2.2 内建类型

	 ENUM, SET(集合)       mysql独有的数据类型

1.2.3 大对象

	TEXT,  TINYTEXT,  MEDIUMTEXT,  LOGTEXT, （文本大对象类型） 

BLOB(binary large object)，TINYBLOB，MEDIUMBLOB，LONGBLOB （二进制大对象类型）

1.2.4 数值型

精确数值：

1：整形 INT (包含正数和负数)
TINYINT,SMALLINT,INT,MEDIUMINT,BIGINT
2：十进制 DECIMAL (包含正数和负数) salary decimal(8,2)

近似数值：

1：单精度：FLOAT
2：双精度：DOUBLE

注意：使用数值型数据时，不需要加单引号，select * from students where stuid<2;
日期型
日期：DATE 年-月-日 1000-9999
时间：TIME 时-分-秒
日期和时间：DATETIME
时间戳：TIMESTAMP 1970 2038-01-14
年份：YEAR(2),YEAR(4) 88 1988
注意：使用日期型数据时，需要加单引号，select user,host,password from mysql.user where now()>‘1986-01-15’;

1.2.5 修饰符

1.2.5.1 整型数据修饰符

NOT NULL 非空约束
NULL
DEFAULT NUMBER 指定默认值
UNSIGNED (无符号修饰, 仅正数) （mysql中数值不能加引号，字符型要加引号，支持三种引号，’’,” ”, .）
AUTO_INCREMENT (自动增长)

PRIMARY KEY | UNIQUE KEY （不一定非要定义在数值型上）

1.2.5.2内建类型（枚举和集合）修饰符

NULL
NOT NULL
DEFAULT
CHARACTER SET ‘’ 使用的字符集，或者使用CHARSET ‘’ （字符数据以什么编码方式存入表中）
COLLATION：使用的排序规则 （指明字符数据的排序规则，不同语言排序规则不同）

1.3 文件类型

数据文件（包括索引） 日志文件 SQL
（二进制日志、错误日志、查询日志、 慢查询日志、中继日志）
Binlog log_error general_log slow_query_log 10秒

2 MySQL数据库操作

2.1 连接数据库 MySQL -u -p -h

-u 用户，默认有root用户，root用户是MySQL数据库的管理员用户，拥有最高的权限。所以默认不允许远程登录root用户。
-p 密码，用户对应的密码
-h 主机，默认是localhost(127.0.0.1)代表本机，
注意：MySQL用户帐号由两部分组成：’USERNAME’@’HOST’;其中HOST指明此用户可以通过哪些远程主机来连接MySQL服务。

举例：
MySQL -uroot -hlocalhost -p
MySQL --user=root --password
MySQL --user=root --host=localhost --password

2.2 MySQL结构

2.2.1 体系结构

数据库是C/S架构，已mysql为例,mysql.exe是client端,mysqld是server端

2.2.2 对比office

MySQL是数据库类型，在MySQL当中，存放的Database 数据库Table 表Field（column） 字段Value 值

对比office的Excel来看
MySQL Excel
Database file
Table Sheet
Field 表头
Value 数据

2.3 MySQL客户端程序

mysql：交互式的CLI（字符交互界面）工具
选项 -e 不用登入mysql的sql界面，直接在字符终端交互输入相关语句进行操作，并返回操作结果
例如：mysql -uroot -e “show databases” -p******
mysql -uroot -p -e “show databases;use mysql; desc user;”
mysql -uroot -p -e “show databases;use mysql; desc user;” > c:\1.txt

2.4 MySQL导入sql示例脚本

在mysql的sql接口进行导入脚本，可用于恢复mysql的数据信息
前提条件必须指定某个库，use db_name
语法格式为：
mysql > source 路径下的脚本名（相当于. 路径下的脚本名）
例如 mysql> source c:/student.sql

2.5 SQL语法规则

(1)分号结尾
(2)所有SQL文档，系统变量大写
(3)MySQL本身大小写不敏感，但使用时一般统一大小写

2.6 SQL语句分类

DDL：数据定义语言
针对非行数据层面的组件， 库 表 视图 索引 存储过程 函数
CREATE, DROP, ALTER
DML：数据操作语言
针对行数据层面的增删改查，要先有DDL语句建立表的结构，才能有行数据的DML
INSERT, DELETE, UPDATE, SELECT 增删该查 (row,select) (CRUD)
DCL：数据控制语言
GRANT, REVOKE
TCL：事务控制语言
COMMIT; ROLLBACK;

2.7 SQL基础语句

2.7.1 普通命令

（1）MySQL命令行客户端查看帮助文档：
HELP KEYWORD (命令关键字)
? （相当于HELP）
（2）查看MySQL的状态和版本
status 相当于\s
（3）查看所有字符集
show charset;
SHOW CHARACTER SET
（4）查看校对规则
show collation;
（5）查看存储引擎
SHOW ENGINES;
（6）查看排序规则
SHOW COLLATION
（7）绝对路径访问表：DATABASE.TABLE
select * from MySQL.db;
（8）相对路径访问表：USE DB_NAME; TABLE
use MySQL;
show tables;
desc db;
select host,db,user from db;
（9）MySQL（windows版本）命令行的快捷操作键
ESC：光标删除至最左侧
F3：上一次命令再操作补全
F7：之前操作过的SQL命令列表
exit：退出SQL接口（或者quit 或者\q）

2.7.2 数据库命令

（1）查询数据库命令
SHOW DATABASES;
（2）创建数据库
CREATE DATABASE school CHARSET=UTF8;
（3）使用数据库
USE school

2.7.3 表层级命令

（1）查看表

SHOW TABLES;

（2）创建表

CREATE TABLE 表名(字段1 数据类型 权限修饰词，字段2 数据类型 权限修饰词…） [ENGINE=’’];

（3）创建表格式

CREATE TABLE tb_name(field FIELD_TYPE,) [CHARSET=UTF8];
字段类型：
int 整形
TINYINT 微整数 1个字节 -128-127
SMALLINT 小整数 2个字节
MEDIUMINT 中等整数 3个字节
INT 整数 4个字节 -2147483648-2147483647
BIGINT 大整数 8个字节
Float 4个字节
Char 字符
Varchar 字符
Char和varchar 都需要指定长度，如果字符的长度不够char的长度，以空格填充，
Varchar 按照字符的长度设定长度
Char类型的字符串不可以用空格结尾
Date 3字节 1998-12-21
TIME 3字节 23:24:24
INDEX(col1,…) 索引
UNIQUE KEY(col1,…)
表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

（4）查看表结构

DESC student;

2.7.4 数据层级命令

（1）插入数据

INSERT INTO student(id,name,age,major,class,phone) VALUE(1,“张三”,18,“python”,“0506”,“13331153360”);

（2）查询数据库数据

SELECT 字段 FROM table_name WHERE 限制条件;
所有字段可以用代替，在工作当中一般不允许用

（3）修改数据

UPDATE student SET major=”java” WHERE name=”老李”;

（4）删除数据

	DELETE FROM student WHERE name="张三";

（5）删除表和库

DROP TABLE(DATABASE) tableName(DBName);

3 数据库的约束、索引及运算

3.1 约束：constraint

3.1.1 作用

向数据表提供的数据要遵守的限制

3.1.2 分类

（1）完整性约束：表约束和键约束

（2）主键 (primary key)：一个或多个字段的组合，填入的数据必须在本表中唯一标识本行，必须提供数据，不能为空（not null），一个表只能有一个

（3）唯一键 (unique key)：一个或多个字段的组合，填入的数据必须在本表中唯一标识本行，可以为空，一个表可以有多个

（4）外键：一个表中的某字段可填入数据取决于另一个表的主键已有的数据

3.1.3 外键设置及定义

（1）目的：为了满足关系型数据库的第三范式，降低数据冗余，在表与表之间需要满足相应的约束关系，可以为表和表之间设置外键

（2）局限：表类型不能是myisam等非innodb存储引擎，MySQL的innodb存储引擎支持外键

（3）设置外键语法

foreign key(col_name) references f_tbl_name(col_name)

（4）查看表的外键

show create table tab_name\G
select * from information_schema.key_column_usage;

3.2 索引

3.2.1 定义

按照特定数据结构存储的数据（较小的数据集）
创建在经常用作查询条件的字段上

3.2.2 目的

 将经常用于查询的某些字段定义索引后，数据库利用索引定位技术，能够大大加快查询速率。
 特别是在当表特别大的时候，或者涉及到多表查询的时候，利用索引可以使查询加快成千倍。
 极大的降低I/O成本，降低数据库的排序成本。
 使用分组、排序时，能大大减少分组和排序时间

3.2.3 缺点

增加了数据库的存储空间
创建索引和维护索引要耗费时间
插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

3.2.4 分类

（1）根据数据库功能分类：
唯一索引：不允许其中任何两行具有相同索引值的索引
主键索引：数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
聚集索引：数据和索引存储在一起，MySQL主键索引就是聚集索引。MySQL建议每个表要有主键，会自动创建主键聚集索引。
（2）主键索引、非主键索引（辅助索引）
（3）稠密索引DENSE、稀疏索引 (DB2) SPARSE
（4）简单索引、组合索引 (where cno=xxx and score=600) create index idx_cno_score on stu(cno,score)

3.2.5 组织方式

MySQL索引左右组织方式：左前缀索引：例如like ‘abc%’

3.2.6 建立索引

创建表的时候同时定义索引
CREATE TABLE tabl_name (col1 type,col2 type, …, index idx_name(col_name));
追加索引：add
ALTER TABLE tbl_name ADD index index_name(col_name);
删除索引
ALTER TABLE tbl_name DROP index index_name;
查看表中的索引
SHOW INDEX FROM [db_name.] tbl_name;
等效写法：
show indexes from [db_name.] tbl_name;
show keys from [db_name.] tbl_name;

可以基于数据库表中的单列或多列创建
CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);

3.2.7 管理索引的途径

（1）创建表的时候指定
create table t1(id int,name char(10), index idx_name(name));
（2）修改表的时候添加或删除索引
ALTER TABLE tb_name add/drop index index_name(col_name)
（3）单独创建索引
CREATE INDEX index_name on tbl_name(col_name);
（4）删除索引
DROP INDEX index_name ON tbl_name;

3.2.8 查看索引

show index from tbl_name;
show keys from tbl_name;

3.2.9 查看是否使用索引

select语句前面加上explain

3.2.10 查看索引

3.2.11 索引作用示例：

use test;
drop table if exists t;
create table t (id int not null,name varchar(30));

#创建存储过程，输入记录数，插入t表行数据
delimiter $$createprocedureproc1(cntint)begindeclareiintdefault1;starttransaction;repeatinsertintotest.t(id,name)values(i,concat{(}^{\prime }{a}^{\prime },i));seti=i+1;untili>cntendrepeat;commit;end$$
delimiter ;

#调用存储过程proc1，1百万条记录
call proc1(1000000);
#查看记录数
select count(*) from t;
#查看执行时间
select * from t where id=1500;
#t表id列建立索引
create index idx_id on t(id);
show keys from t;
#查看执行时间
select * from t where id=1500;

3.3 关系运算

3.3.1 查询操作 *

3.3.1.1传统的关系运算

并 U UNION(并集，重复的元组不显示)
UNION ALL(并集，重复的元组也会显示)
交 n INTERSECT(交集)
差 – MINUS(差集)
广义笛卡尔积 X

3.3.1.2专门的关系运算

选择 (selection)：在查询结果中挑选出符合条件的行的运算
投影 (projection)：在查询结果中挑选出需要的列的运算（字段）
连接 (join)：（连接表操作的运算）
除

3.3.2 数据更新操作

插入 删除 修改

4 视图

4.1 简介

是一种虚表（存储下来的select语句），表结构为空。视图中存储SELECT语句，对应的数据是放在视图对应的母表中，也可以牵连母表（视图里面的数据发生变化，其实是母表中数据的变化所致），视图不要随便变更里面数据，否则会破坏原有表的数据体系

4.2 作用

（1）简单，视图构建了一个虚拟的逻辑表，这个表的数据来源于指定的查询，而查询可以是多表，在使用的时候直接使用虚表代替复杂的查询
（2）安全，数据库有权限设置，但是没有对行和列进行设置的。所以，对同一个表当中完整的数据是否展示就是问题了。
提高安全性（隐藏部分行和列）
（3）数据独立，就相当于在原表的基础上加一列

4.3 缺点

视图会降低查询的效率，尤其是嵌套视图

4.4 创建视图

CREATE VIEW vtbl_name as select_statement;

4.5 查看

SHOW TABLE STATUS WHERE COMMENT = “VIEW”\G;

show create table v_stu;

5 事务

5.1 概念

事务是一种机制，一个操作序列，包含了一组数据库操作命令(DML,insert update delete)，并且把所有的命令作为一个整体一起将系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。

5.2 作用

事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。通过事务的完整性以保证数据的一致性。

5.3 属性（ACID）

原子性 Atomicity
一致性 Consistency
隔离性 Isolation
持久性 Durability

5.4语法

begin;/start transactions;
SQL1
SQL2
SQL3…
commit;/rollback;

6 数据备份与恢复

6.1 相关术语

Backup 备份 针对故障：1.介质损坏 2.人为错误
Restore 还原
Recover 恢复 重做日志，实现恢复

6.2 备份的三种方式

1：冷备份 关闭数据库，copy , cp
2：温备份 不关闭数据库，设置成只读模式，只能select，不能写（dml,ddl,dct,tcl）
3：热备份 不关闭数据库，用户可以对数据库进行写操作 （innodb支持热备）

6.3 mysqldump (支持温备和热备，必须打开数据库)

可以使用MySQL自带客户端的命令mysqldump，进行数据的备份
mysqldump命令用法说明：
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

相关其他选项
-d 只备份表的结构，不包含数据
–all-databases 备份所有数据库中的所有表
–lock-tables 备份时对数据库进行锁表（防止备份过程中出现同时读取写入的状况）（默认）
–single-transaction 进行热备份（仅支持INNODB引擎）
–flush-logs 备份时刷新二进制日志
–master-data=2 mysqldump备份数据库时会把备份时走到的日志的日志号和位置号写到备份文件的头部
开启binlog时最常用的选项（完全备份包括存储过程和触发器）：
mysqldump -uroot -p -hxx.xx.xx.xx --all-databases --master-data=2 --single-transaction --flush-logs --routines --triggers> c:\all-db.sql

6.4 示例

备份全部数据库：
mysqldump -uroot -p --all-databases > filename

备份指定数据库：
mysqldump -uroot -p --databases db1 db2 > filename

备份指定一个数据库的表：
mysqldump -uroot -p dbname tblname1 tblname2 > filename

开启binlog时最常用的选项（完全备份包括存储过程和触发器）：
mysqldump -uroot -p -hxx.xx.xx.xx --all-databases --master-data=2 --single-transaction --flush-logs --routines --triggers> d:\all-db.sql

7 配置允许远程网络连接MySQL

7.1 配置用户允许远程

MySQL> rename user root@localhost to ‘root’@’%’;

7.2 配置win7防火墙添加MySQL默认端口3306

进入Windows的防火墙–>高级设置–>入站规则–>新建规则–>选择类型为端口–>协议为TCP–>适用于特定本地端口–>下一步–>允许连接–>下一步–>选中域、专用和公用后下一步–>设置规则名称–>找到规则点击

7.3虚机防火墙允许ping

主机Ping虚机

7.4主机远程连接虚机MySQL测试（主机需有MySQL客户端）

MySQL -uroot -hxxx.xxx.xxx.xxx(虚拟机IP) -p