MySQL数据库基础

一 MySQL数据库简介

1. MySQL是一个关系型数据库管理系统（Oracle公司）；
2. MySQL是开源的，不需要支付额外费用；
3. MySQL支持大型的数据库，可以处理拥有上万条数据的数据库；
4. MySQL使用标准的SQL数据语言形式；
5. MySQL可以运行在多个系统上（macOS、Windows、Linux），并且支持多种语言（C/C++、Python、Java、PHP、Perl、Ruby等）；
6. MySQL对PHP又很好的支持，PHP是目前比较流行的Web开发语言；
7. MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大支持4GB，64位系统支持最大的表文件为8TB；
8. MySQL是可以定制的，采用了GPL协议，可以修改源代码来开发自己的MySQL系统。

二 MariaDB数据库

1. MariaDB是MySQL的一个分支，主要由开源社区维护，采用GPL协议授权许可；
2. MariaDB完全兼容MySQL，包括API和命令行；
3. MySQL被Oracle收购，存在潜在的闭源风险，所以开发了这个分支，使之能够替代MySQL。

三 MySQL数据库操作

1、显示：show databases; show create database db_name;
2、创建：create database db_name;
3、删除：drop database db_name
4、选择：use db_name

create database if not exists db_name default charset utf8 collate utf8_general_ci;

四 MySQL数据类型

MySQL支持多种数据类型，大致可分为三类：数值、日期/时间、字符串（字符） 类型。

1、数值

类型	大小
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT or INTEGER	4 bytes
BIGINT	8 bytes
FLOAT	4 bytes
DOUBLE	8 bytes
DECIMAL	DECIMAL(M,D) -- MAX(M,D)+2

2、日期/时间

类型	大小(bytes)	格式
DATE	3	YYYY-MM-DD
TIME	3	HH:MM:SS
YEAR	1	YYYY
DATETIME	8	YYYY-MM-DD HH:MM:SS
TIMESTRAMP	4	YYYYMMDD HHMMSS

3、字符串

类型	大小(bytes)	用途
CHAR	0-255	定长字符串
VARCHAR	0-65 535	变长字符串
TINYBLOB	0-255	不超过255个字符的二进制字符串
TINYTEXT	0-255	短文本字符串
BLOB	0-65 535	二进制形式的长文本数据
TEXT	0-65 535	长文本数据
MEDIUMBLOB	0-16 777 215	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215	中等长度文本数据
LONGBLOB	0-4 294 967 295	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295	极大文本数据

备注：UTF-8（1汉字=3字节）、GBK（1汉字=2字节）

五 MySQL数据表操作

1. 创建

数据表创建需要以下信息：

• 表名
• 表字段名
• 定义每个表字段

语法： CREATE TABLE table_name(column_name column_type);

    create table if not exists table_1 (
    -> id INT unsigned auto_increment,  
    -> title VARCHAR(100) NOT NULL,
    -> author VARCHAR(100) NOT NULL,
    -> date DATE,
    -> PRIMARY KEY(id)
    -> )engine=InnoDB DEFAULT CHARSET=utf8;

实例解析：

• 不想字段为 NULL 可以设置字段的属性 NOT NULL;
• AUTO_INCREMENT 定义列为自增属性，一般用于主键；
• PRIMARY KEY 关键字用于定义列为主键，可以使用多列，逗号分隔；
• ENGINE 设置存储引擎，CHARSET 设置编码。
• 备注：MySQL命令终止符: ;、换行符标识：->。

2. 查看

语法：

• show tables; // 显示所有当前数据库中所有数据表
• desc table_name; // 查看指定数据表

3. 删除

语法： drop table table_name;

4. 重命名

语法： rename table name_1 to name_2;

5. 修改

语法：

// 增
alter table table_name add new_node varchar(10); 
// 添加外键
alter table customer_1 add constraint fk_id_aaa foreign key(cus_id) references customer(id);
// 删
alter table table_name drop sex; 
// 删除外键
alter table customer_1 drop foreign key fk_id_aaa;
// 改 modify or change
alter table table_name modify is_delete BIGINT NOT NULL;
alter table table_name change is_delete isDelete int;

六 MySQL数据管理

1. 插入数据：

SQL语法：

INSERT INTO table_name (field1, field2, ..., fieldn)  
VALUES   
(value1, value2, ..., valuesn), 
(value1, value2, ..., valuesn), 
(value1, value2, ..., valuesn)

mysql> insert into (id, name, is_delete)
    -> values
    -> (6, 'n6', 1),
    -> (7, 'n7', 1)
    -> ;

2. 查询数据：

SQL语法：

SELECT column_name, column_name
FROM table_name
[WHERE clause]
[LIMIT N][OFFSET M]

SELECT field_1, field_2,..., field_N
FROM table_1, table_2,..., table_N
[WHERE condition_1 [and/or] condition_2...]

// in
select * from student where department in ('中文系', '计算机系'); 
// between and  // as
select id, name, 2020-birth as age from student where 2020-birth between 25 and 30; 
// 逻辑运算符
select id, name, 2020-birth as age from student where 2020-birth > 25 and 2020-birth < 30;
// 分组 group by
select department, count(id) as person_count from student group by department;
select c_name, max(grade) from score group by c_name;
// group by ... having
select c_name, max(grade) from score group by c_name having max(grade) > 95;

// 两个表之间 子查询
select c_name, grade from score where student_id = (select id from student where name='李四');
select student.name, score.c_name, score.grade from student, score where student.id=score.student_id and student.name='李四';

语法解析：

• 查询语句中可以使用一个或多个表，表之间使用逗号 . 分割；
• SELECT命令可以读取一条或多条记录；
• 使用 * 来代替其他字段，SELECT语句会返回表的所有字段数据；
• WHERE语句用来包含限定条件；
• LIMIT属性用来设定返回最大记录数；
• OFFSET属性用来指定开始查询的数据偏移量;
• BINARY关键字，是用来区分大小写的。

3. 删除数据：

SQL语法：

delete from table_name; // 按行删除，可以回滚；

truncate table_name;    // 删除整表数据，不可以回滚；

4. 修改数据：

SQL语法：

update table_name set name='new name', is_delete=1 where id=2;

5. 约束条件：

SQL语法：

mysql> create table custumer (
    -> id int not null auto_increment primary key,
    -> uid int unique,
    -> uname varchar(50) not null default 'aaa'
    -> );

mysql> create table customer_1 (
    -> cus_id int not null,
    -> name varchar(50),
    -> constraint fk_id_aaa foreign key(cus_id) references customer(id)) [on update (cascade etc.)]/[on delete (restrict etc.)];

cascade: 父表更新时，子表也更新；父表删除时，子表连带一起删除。
restrict: 父表更新时，子表不允许更新；父表删除时，子表不允许删除。

6. 关联查询：

SQL语法：

// union 默认去重
select id from student union select student_id from score;
// union all 查询全部
select id from student union all select student_id from score;

// left join 以左表为标准，右表可能包含NULL数据
select * from student a left join score b on a.id=b.student_id;
// 
select a.id, b.c_name from student a left join score b on a.id=b.student_id;
// 等同于
select a.id, b.c_name from (student a left join score b on a.id=b.student_id);
// inner join / join 左右表都不包含NULL数据
select * from student a join score b on a.id=b.student_id;
// right join 以右表为标准，左表可能包含NULL数据
select * from student a right join score b on a.id=b.student_id;

7. 模糊查询：

SQL语法：

// 模糊查询
select * from student where department like '中文%';
// 关联 + 模糊
select student.id, student.name, student.department, c_name, grade from student, score where (student.name like '张%' or name like '王%') and student.id=score.student_id;

8. 索引：

解决数据量较大的查询问题，加快访问速度；
SQL语法：

MyISam引擎对应文件（类似书籍目录）：

• myisam_file.frm
• myisam_file.MYD
• myisam_file.MYI

InnoDB引擎对应文件（类似超市货架）：

• innodb_file.frm
• innodb_file.ibd

// create index
create index name_index on customer(uname(10));
// alter ... add index
alter table customer add index my_index(uname(10));
// 组合索引
alter table customer add index id_uid_uname (id, uid, uname);

// 创建时添加索引
mysql> create table tmp_index_table(
    -> id int not null auto_increment,
    -> name varchar(20) not null,
    -> primary key(id),
    -> index name_index (name(10))
    -> );

9. binlog：

日志文件，记录sql语句的执行；
数据库：主从同步（主写从读）；
主从同步配置
vim常用命令总结

// 修改配置 (配置文件中)
bind-address = 0.0.0.0  // 外部访问
// 慢查询
long_query_time=1
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-bin.log // 日志存储地址
log-queries-not-using-indexes=1
// binlog日志
log_bin=/var/mysql/mysql-bin.log

mysql重启：service mysql restart

10. 慢查询：

// 查看配置
show variables like '%slow_query%';
// explain
explain select * from student;

// 修改Cache (配置文件中)
query_cache_limit=1M
query_cache_size=16M

参考文档：
慢查询结果详情-expain
慢查询日志的使用和优化