MySQL基础总结

MySQL的安装与配置

MySQL下载

通常直接去MySQL官网下载。
如： https://dev.mysql.com/downloads/

MySQL主要分为社区版(Community Server)和企业版(Enterprise)：
社区版是可以自由下载而且是免费的，但是官方不提供任何技术支持；
企业版是收费的，不能在线下载，提供了更多的功能和更完备的技术支持。

MySQL安装

在Windows系统下，可以下载免安装版，如 MySQL Community Server，下载好zip文件后，直接解压就可以使用。还有别的图形化安装方式，具体的安装步骤，就不在此累赘了，可以查看下面的文章：

Windows系统上MySQL Community Server下载与安装配置
Linux系统上MySQL Community Server下载与安装配置

MySQL配置

解压后，在目录下可以发现，文件类似于"my-***.ini"，其中***代表不同的环境特点，例如，my-small.ini， my-large.ini 等等，这两个分别代表此文件适合小型数据库和大型数据库。

启动和关闭MySQL服务

通过cmd命令进入到MySQL的安装路径下的bin文件夹下：

mysqld是MySQL的主程序
mysqld — The MySQL Server

mysql是MySQL的命令行工具
mysql — The MySQL Command-Line Tool

启动服务

mysqld --console

关闭服务

mysqladmin -uroot -p shutdown
根据提示输入密码，然后回车；注意，这句话后面不要加分好“;”，否则报语法错误。

mysqladmin 也是bin文件夹下的一个 exe程序。

mysqladmin --version
查看mysql数据版本。

SQL基础

启动MySQL服务后，输入以下命令连接到MySQL服务器：
在安装目录的bin路径下，打开cmd框

mysql -u root -p

根据提示，输入正确数据库密码后，就可以进入MySQL。
-u 后面跟连接的数据库用户
-p 表示需要输入密码

SQL 语句分类

• DDL (Data Definition Language)语句：数据定义语句，定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字有：create, drop, alter等
• DML (Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字有：insert, delete, update, select等
• DCL(Data Control Language)语句：数据控制语句，用于控制不同的数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字有：grant, revoke等。

DDL

操作数据库

在 MySQL 中，可以使用 CREATE DATABASE 语句创建数据库，语法格式如下：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

说明，[ ]中的内容是可选的。

语法说明如下：

<数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。

IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。

[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。

[DEFAULT] COLLATE：指定字符集的默认校对规则。
通过 show collation; 语句可以查看所有支持的校对规则。

注意：MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。
字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式

例子如下：
使用 MySQL 命令行工具创建一个测试数据库，命名为 test_db，指定其默认字符集为 utf8。

CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8;

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

相关其他SQL:

drop database <databaseName>;		删除数据库
use <databaseName>;		选择要操作的数据库
show tables;		当执行完上一句后，可以用该命令查看该数据库中的所有数据表

show create database <databaseName>;		查看数据库字符集

MySQL有4个系统自动创建的数据库：

• information_schema: 主要存储了系统中的一些数据库对象信息，比如用户表信息，列信息，权限信息，字符集信息，分区信息等；
• cluster: 存储了系统的集群信息
• mysql：存储了系统的用户权限信息
• test：系统自动创建的测试数据库，任何用户都可以使用

操作表

创建表
create table <tableName>(
column_name_1 column_type_1 constraints,
column_name_2 column_type_2 constraints,
...
column_name_n column_type_n constraints,
)

desc <tableName>	查看表的定义
show create table <tableName> \G	查看更全面的表定义信息
    上面这条命令，还可以看到表的engine（存储引擎）和charset（字符集）等信息，
    \G 选项，使得记录能够按照字段竖向排列，以便显示内容较长的记录。

删除表
drop table <tableName>		

例如：

CREATE TABLE Peoples
(
   id INT NOT NULL AUTO_INCREMENT,
   age INT NOT NULL,
   first VARCHAR(255),
   last VARCHAR(255),
   PRIMARY KEY ( id )
)ENGINE=INNODB DEFAULT CHARSET=utf8;

修改表

下面方括号中的内容，是选填项；

修改表类型
alter table <tableName> modify [column] <columnName> column_definition [first | after col_name]

增加表字段
alter table <tableName> add [column] <columnName> column_definition [first | after col_name]

删除表字段
alter table <tableName> drop [column] <columnName>

字段改名
alter table <tableName> change [column] <oldColumnName> <newColumnName> column_definition [first | after col_name]

修改字段排列顺序
前面的add/modify/change 后都有一个可选项 first|after column_name, 这个选项可以用来修改字段在表中的位置； add增加的字段默认是在表中的最后位置，modify和change不会改变位置。

更改表名
alter table <tableName> rename [to] <newTableName>

change/first/after column这个关键字是MySQL在标准SQL上的扩展，其他数据部不一定适用。

FIRST表示增加此列为第一个列
AFTER表示增加在某个列之后
注意： MySQL增加列指定位置时没有BEFORE的用法，第一列可以使用FIRST，非第一列使用AFTER。

DML

插入记录

insert into <tableName>(field1, field2, ..., fieldn) values(value1, value2, ..., valuen);
可空字段，非空但是含有默认值的字段，自增字段，可以不用在insert后的字段列表出现。

MySQL中，可以一次性插入多条记录：
insert into <tableName>(field1, field2, ..., fieldn) 
values
(record1_value1, record1_value2, ..., record1_valuen),
(record2_value1, record2_value2, ..., record2_valuen),
...
(recordn_value1, recordn_value2, ..., recordn_valuen);

更新记录

update <tableName> set field1=value1,field2=value2, ..., fieldn=valuen [where condition]

MySQL中可以同时更新多个表中的数据
update t1, t2, ..., tn set t1.field1=value1, t2.field2=value2, ..., tn.fieldn=valuen [where condition]
多表更新多用在根据一个表的字段来动态更新另一个表的字段。

删除记录

delete from <tableName> [where condition]

MySQL中可以一次删除多个表中的数据
delete t1, t2, ..., tn from t1, t2, ..., tn [where condition]
注意：如果from后的表名用别名，delete后面的表名也要用别名，否则有语法错误。

查询记录

select * from <tableName> [where condition]
distinct 关键字的使用，去掉重复

排序
select * from <tableName> [where condition] [order by field1 [desc|asc], field2 [desc|asc], ..., fieldn [desc|asc]] 
默认升序。

限制
select ... [limit offset_start, row_count]
offset_start: 表示记录的起始偏移量，默认情况下为1
row_count: 表示显示的行数
limit通常和order by配合使用，来进行记录的分页显示。
注意：这个语法，也是MySQL的扩展，其他数据库不能通用。

聚合

select [field1, field2, ..., fieldn] fun_name
from <tableName>
[where where_condition]
[group by field1, field2, ..., fieldn]
[with rollup]
[having where_conditon]

fun_name表示聚合操作：sum(求和), count(*)记录数 , max(最大值), min(最小值)
with rollup: 是可选项，表示是否对分类聚合后的结果进行再汇总。
having关键字表示对分类后的结果再进行条件的过滤。

having和where的区别：
having表示对分类后的结果再进行条件的过滤，而where是在聚合前就对记录进行过滤。如果逻辑允许，尽可能先使用where过滤，减少结果集，再用having进行过滤。

表连接

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
  MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

where和on的区别：
先执行on，后执行where；
on是建立关联关系在生成临时表时候执行，where是在临时表生成后对数据进行筛选的；

子查询

当进行查询时，需要的条件是另一个select语句的结果。
子查询的关键字有： in, not in, =, !=, exists, not exists等。
例如：
select * from test where deptNo in (select deptNo from dept)

记录联合

union/union all
主要区别是union all把结果集直接合并在一起，union是将union all的结果进行一次distinct，去除重复记录后的结果。

DCL

DCL语句主要是DBA用来管理系统中的对象权限时使用。主要使用grant, revoke语句。

例如：添加数据库用户user1，并对test数据库中的所有表具有select/insert 权限
grant select, insert on test.* to 'user1'@'localhost' identified by '111'

从用户user1上收回insert权限
revoke insert on test.* from 'user1'@'localhost'

帮助命令

有时候忘记具体命令怎么写，或者MySQL的相关定义，可以用帮助命令查看。

1. 直接输入，根据提示来选择
? contents

2.快速查阅帮助
例如： ? create table

查询元数据信息

MySQL 5.0之后，提供了一个新的数据库information_schema，用来记录MySQL中的元数据记录（表名，列名，列类型，索引名等）。
在该数据库下，通过show tables查到的，都是视图。

例子：

删除数据库test1下的所有前缀为tmp的表
select concat('drop table test1.', table_name, ';') from tables where table_schema = 'test1' and table_name like 'tmp%';

将数据库test1下的所有存储引擎为myisam的表改为innodb
select concat('alter table test1.', table_name, ' engine=innodb;') from tables where table_schema = 'test1' and engine = 'MyISAM';

官方资源

https://dev.mysql.com/doc/