一、安装
windows安装
1.下载zip压缩包：https://dev.mysql.com/downloads/mysql/
2.添加环境变量：mysql目录下面的bin文件夹
3.启动管理员模式下的CMD，进入mysql下的bin目录，输入“安装mysql”命令
mysqld –install
4.初始化数据文件
mysqld --initialize-insecure --user=mysql
5.启动mysql服务(必须)
net start mysql
6.进入mysql管理界面 初次进入密码为空
mysql –u root –p
7.刷新权限
flush privileges;
8.进入界面后更改root密码
alter user 'root'@'localhost' identified by '123456';
9.刷新权限
flush privileges;
10.重启mysql
net stop mysql
net start mysql
二、命令
使用命令
查看版本
mysqladmin --version
连接
mysql -h 服务器地址 -P 端口 -u root -p
启动
net start mysql
停止
net stop mysql
重启
net restart mysql
常用命令
查看所有数据库
show databases;
选择(连接)数据库
use 数据库名;
查看当前数据库所有表
show tables;
查看创建数据库的sql语句
show create database 数据库名;
查看创建表的sql语句
show create table 表名;
查看表结构属性
desc 表名;
查看表的索引信息
show index from 表名;
查看sql语句的性能
explain SQL语句;
三、SQL
SQL分类
DDL(数据定义语言)：create、alter、drop、rename、truncate
DML(数据操作语言)：select、insert、update、delete
DCL(数据控制语言)：commit、rollback、savepoint、grant、revoke
DDL
创建删除
创建数据库
create database 数据库名;
create database if not exists <数据库名>; # 数据库已存在不报错
创建表
create table 表名(
id int auto_increment, # id自增 “auto_increment”也可以修改为identity(1,1)：从1开始每次递增1
字段1 varchar(255) not null, # 数据类型是varchar，最大长度为 255 个字符，不能为空
字段2 int(8) default '18', # 默认值为18
unique (字段1) # 不允许重复
primary key (id) # 主键：唯一且不为空约束 (每个表都应该有一个主键，并且每个表只能有一个主键。)
check (id>0) # 约束：id大于0
foreign key(此表的字段) references 要关联的表名(要关联的表的字段) # 设置关联外键
)engine=innodb auto_increment=100 charset=utf8; # 表的引擎 自增起始值 表的编码
删除数据库
drop database 数据库名;
drop database if exists 数据库名; # 数据库不存在不报错
删除表
drop table 表名;
清空表
truncate table 表名;
修改
修改表名
alter table 原表名 rename to 新表名;
修改字段名
alter table 表名 change column 旧字段 新字段 新列的类型;
修改字段数量
1.表的末尾新加字段
alter table 表名 add column 字段 varchar(255);
2.加在表的字段首列
alter table 表名 add 字段 int first;
3.加在已存在字段之后
alter table 表名 add 字段 int after 已存在的字段;
4.删除字段
alter table 表名 drop column 字段;
修改字段类型
1.修改字段数据类型
alter table 表名 modify column 字段 修改的类型;
2.修改字段类型并设置约束和默认值
alter table 表名 modify column 字段 修改的类型 not null default 默认值;
# 常见字段类型：
char(size) 保存固定长度的字符串(可包特殊字符),在括号中指定字符串的长度,最多 255 个字符。
varchar(size) 存放最大长度为 65,535个字符的字符串。
text 存放最大长度为 65,535个字符的字符串。
mediumtext 存放最大长度为 16,777,215个字符的字符串。
longtext 存放最大长度为 4,294,967,295个字符的字符串。
int(size) 整数,带符号范围 -2147483648到 2147483647,无符号的范围是0到 4294967295,size默认为 11
float(size,d) 带有浮动小数点的小数字,size参数规定显示最大位数,d参数规定小数点右侧的最大位数
double(size,d) 带有浮动小数点的大数字,size参数规显示定最大位数,d参数规定小数点右侧的最大位数
date 日期,格式：YYYY-MM-DD
time 时间,格式：HH:MM:SS
datetime 日期和时间的组合,格式：YYYY-MM-DD HH:MM:SS
timestamp 时间戳类型
修改默认值
1.设置默认值
alter table 表名 alter 字段名 set default '默认值'
修改表的引擎
alter table 表名 engine = 引擎; # 常见引擎：InnoDB MyISAM MEMORY Archive
MyISAM : 节约空间及相应速度
InnoDB : 安全性 , 事务处理及多用户操作数据表
ps : InnoDB的表空间大小约为MyISAM的两倍
约束
约束分类
1.键约束：主键约束、外键约束、唯一键约束
2.Not NULL约束：非空约束
3.Check约束：检查约束(5.7版本不支持)
4.Default约束：默认值约束
5.自增约束
常用命令
1.设置主键约束
alter table 表名 add primary key 字段名
2.删除主键约束
alter table 表名 drop primary key 字段名
1.设置唯一约束
alter table 表名 add unique 字段名
2.删除唯一约束
alter table 表名 drop index 字段名
1.设置不为空约束
alter table 表名 modify 字段名 数据类型 not null;
2.删除不为空约束
alter table 表名 modify 字段名 数据类型 null;
1.设置检查约束 (id大于10的约束)
alter table 表名 add check (id>10)
2.删除检查约束
alter table 表名 drop constraint 字段名
1.设置默认值约束
alter table 表名 alter 字段名 set default '默认值'
2.删除默认值约束
alter table 表名 alter 字段名 drop default
索引
作用
1.提高查询速度
2.确保数据的唯一性
3.全文检索字段进行搜索优化
分类
1.主键索引 (Primary Key):某一个属性组能唯一标识一条记录
# 最常见的索引类型
# 确保数据记录的唯一性
2.唯一索引 (Unique):避免同一张表中某列中的数据重复
# 唯一索引列中的数据不能重复
# 同一张表中的唯一索引可以有多个
3.常规索引 (Index):快速定位特定数据
# index 和 key 关键字都可以设置常规索引
# 应加在查询找条件的字段
# 不宜添加太多常规索引,影响数据的插入,删除和修改操作
4.全文索引 (FullText):快速定位特定数据
# 只能用于MyISAM类型的数据表
# 只能用于CHAR , VARCHAR , TEXT数据列类型
# 适合大型数据集
使用方法
# 方法一：创建表时添加索引
CREATE TABLE 表名 (
字段名1 数据类型 约束条件…,
字段名2 数据类型 约束条件…,
索引类型 索引名(字段名)
);
# 方法二：CREATE在已存在的表上创建索引
CREATE 索引类型 INDEX 索引名 ON 表名(字段名);
# 方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD 索引类型 INDEX 索引名(字段名);
常用命令
1.显示索引信息
SHOW INDEX FROM 表名;
全文索引
1.全文索引
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(字段);
2.全文索引
SELECT * FROM 表名 WHERE MATCH(字段) AGAINST('关键字');
索引准则
1.索引不是越多越好
2.不要对经常变动的数据加索引
3.小数据量的表建议不要加索引
4.索引一般应加在查找条件的字段
DML
增
# 不指定字段插入 字段顺序必须和表对应
insert into 表名 values (v1,v2,v3,...);
# 指定字段插入，字段顺序无需和表对应
insert into 表名 (k1,k2,k3,...) values (v1,v2,v3,...);
# 批量插入
insert into 表名 (k1,k2,k3,...) values (v1,v2,v3,...),(v1,v2,v3,...),(v1,v2,v3,...);
删
# 删除表中id列等于10的那一行
delete from 表名 where id='10';
# 删除表中id等于10且c等于facebook的那一行
delete from 表名 where id='facebook' and c='usa';
# 清空表，计数器不归零
delete * from 表名;
# 清空表，计数器归零(自增列重新开始基数)
truncate 表名;
改
# 将表中id等于10的行 的a列改为"26" c列改为"usa"
update 表名 set a='26', c='usa' where id='10';
ps：一定要加where语句，否则表中的所有列都会被更新。
查
# 读取user表中所有列的值 *表示该行的所有列
select * from 表名;
# 读取表中a列值和b列值
select a,b from 表名;
# 别名
select a as A, b as B from 表名 as 表别名;
# 查询结果a列结果将以"a的值:a"展示(concat():字符串拼接函数)
select concat(a的值:,a) from 表名;
# 去重
select distinct a from 表名;
排序
# 按照表中的id列进行降序排序
select * from 表名 order by id desc;
# 按照表中的id列进行升序排序
select * from 表名 order by id asc;
分页查找
select * from 表名 limit (x-1)*n,n # 要查找的页数x，当前页显示n条记录
例如：select name from 表名 limit 20,4 # 第6页数据 (6-1)*4 , 4
where过滤
常用
# 表中a列值为"CN"的行
select * from 表名 where a='CN';
# 表中id列值为"1"的行
select * FROM 表名 where id=1;
逻辑运算符
(不等于运算符："<>"或"!=")
# 表中id列值大于10且小于20的行
select * from 表名 where id>10 and id<20;
# 表中c列值为"ZN"或id大于10的行
select * from 表名 where c='ZN' or id>10;
# 表中c列值为"CN"且id大于50的行
select * from 表名 c='CN' and id>50;
# 表中id大于15且c列值为"CN"或'USA'的行
select * from 表名 id > 15 and (c='CN' or c='USA');
特殊条件
# 表中a列值不大于30的行
select * from 表名 where not a > 30;
# 表中a列值等于null的行
select * from 表名 where a is null;
# 表中a列值不为null的行
select * from 表名 where a is not null;
# 表中a列值等于5或10或12的行
select * from 表名 where a in (5,10,12);
# 表中a列值在20到30之间(包括20和30)的行
select * from 表名 where a between 20 and 30;
# 表中times列不在2012-05-10和2012-06-10之间的行
select * from 表名 where times not between '2012-05-10' and '2012-06-10';
联表查找
union
# 两表查询结果去重复查找
select 字段 from 表名1 union select 字段 from 表名2;
# 两表查询结果不去重复查找
select 字段1,字段2 from 表名1 where 条件 union all select 字段 from 表名2 where 条件;
join
# 内连接：只显示匹配条件的行
select a.字段1, a.字段2, b.字段3 from 表a as a inner join 表b as b on a.表a共同属性列 = b.表b共同属性列;
# 左外连接：显示匹配条件的行和左表中不匹配的行，右表没有的数据的列用null展示(左查询)
select a.字段1, a.字段2, b.字段3 from 表a as a left join 表b as b on a.表a同属性列 = b.表b同属性列;
# 右外连接：显示匹配条件的行和右表中不匹配的行，左表没有的数据的列用null展示(右查询)
select a.字段1, a.字段2, b.字段3 from 表a as a right join 表b as b on a.表a同属性列 = b.表b同属性列;
# 只显示左表不匹配条件的列
select a.字段1, a.字段2, b.字段3 from 表a as a left join 表b as b on a.表a同属性列 = b.表b同属性列 where b.表b同属性列 is null;
# 只显示右表不匹配条件的列
select a.字段1, a.字段2, b.字段3 from 表a as a right join 表b as b on a.表a同属性列 = b.表b同属性列 where a.表a同属性列 is null;
指定查询数量
select * from 表名 limit 2; # 前2行
select top 50 percent * from 表名; # 前面百分之五十
select top 5 * from 表名 # 表中前5行
select top 5 * from 表名 order by id desc # 表中后5行
null值过滤
ps：mysql数据库中的null值用"="和"!="比较是不起作用的，必须使用"is null"和"is not null"
# 查找表中某列null的数量
select * from 表名 where 字段 is null;
# 查找表中某列不为null的数量
select * from 表名 where 字段 is not null;
通配符
ps:通配符查找(%表示多个字符 _下划线表示一个字符)
select * from 表名 where 字段 like 'http%'; # 以'http'开头的行
select * from 表名 where 字段 like '%.jpg'; # 以'.jpg'结尾的行
select * from 表名 where 字段 like '%m%'; # 包含'm'的行
select * from 表名 where 字段 not like '%m%'; # 不包含'm'的行
select * from 表名 where 字段 like '%a_'; # 'a'在倒数第二位的行
select * from 表名 where 字段 like '__M%'; # 'M'在第三位的行
select * from 表名 where 字段 like '_oogle'; # 以一个任意字符开始，然后是"oogle"的行
select * from 表名 where 字段 like 'G_o_le'; # 以"G"开始，然后一个任意字符，然后是"o"，再一个任意字符，然后是"le"的行
正则表达式
select * from 表名 where 字段 regexp '^[gfs]'; # 以"g"或"f"或"s"开始的行
select * from 表名 where 字段 regexp '^[a-h]'; # 以"a"到"h"字母开头的行
select * from 表名 where 字段 regexp '^[^a-h]'; # 不以"a"到"h"字母开头的行
select * from 表名 where 字段 regexp '^st'; # 以"st"字符串开头的行
select * from 表名 where 字段 regexp 'ok$'; # 以"ok"为结尾的行
select * from 表名 where 字段 regexp 'mar'; # 包含'mar'字符串的行
select * from 表名 where 字段 regexp '^[aeiou]|ok$'; # 以[aeiou]的其中一个字符开头或以"ok"字符串结尾的行
数据函数
SELECT ABS(-8); # 绝对值
SELECT CEILING(9.4); # 向上取整
SELECT FLOOR(9.4); # 向下取整
SELECT round(9.4); # 四舍五入去整
SELECT RAND(); # 随机数,返回一个0-1之间的随机数
SELECT SIGN(0); # 符号函数: 负数返回-1,正数返回1,0返回0s
SELECT mod(m, n) # m%n m mod n 求余 10%3=1
SELECT pi(); # 获得圆周率
SELECT pow(m, n); # m^n
SELECT sqrt(x); # 算术平方根
SELECT rand(); # 随机数
SELECT truncate(x, d); # 截取d位小数
字符串函数
SELECT CHAR_LENGTH('字符串'); # 返回字符串包含的字符数
SELECT CONCAT('hello','golang','!!'); # 合并字符串,参数可以有多个
SELECT INSERT('hello world',6,4,'golang'); # 替换字符串,从某个位置开始替换某个长度(第6个位置替换4个长度)
SELECT LOWER('AbCd'); # 小写
SELECT UPPER('AbCd'); # 大写
SELECT LEFT('hello,world',5); # 从左边截取
SELECT RIGHT('hello,world',5); # 从右边截取
SELECT REPLACE('hello golang','golang','world'); # 替换字符串
SELECT SUBSTR('hello golang',4,6); # 截取字符串,开始和长度
SELECT REVERSE('八级大狂风'); # 反转
日期和时间函数
SELECT CURRENT_DATE(); # 获取当前日期
SELECT CURRENT_TIME(); # 获取当前时间
SELECT NOW(); # 获取当前日期和时间
SELECT LOCALTIME(); # 获取当前日期和时间
聚合函数
COUNT() # 返回满足select条件的记录总和数，如 select count(*) [不建议使用 *，效率低]
SUM() # 返回数字字段或表达式列作统计，返回一列的总和
AVG() # 通常为数值字段或表达列作统计，返回一列的平均值
MAX() # 可以为数值字段，字符字段或表达式列作统计，返回最大的值
MIN() # 可以为数值字段，字符字段或表达式列作统计，返回最小的值
SELECT COUNT(1) FROM 表名; # 求总和 推荐
SELECT COUNT(*) FROM 表名; # 求总和
区别：
# count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录
# count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录
# count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录
使用场景：
# 在表没有主键时，count(1)比count(*)快
# 有主键时，主键作为计算条件，count(主键)效率最高
# 若表格只有一个字段，则count(*)效率较高
DCL
事务
概念
# 事务就是将一组SQL语句放在同一批次内去执行
# 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
事务准则(ACID)
原子性(Atomicity)
# 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。
一致性(Consistency)
# 事务前后，数据库始终处于一致状态。
隔离性(Isolation)
# 多个事务并发执行时，互不干扰。
持久性(Durability)
# 事务提交后，数据永久写入，即使宕机也不会丢失。
事务隔离级别
读未提交(READ UNCOMMITTED)：一个事务读到另一个事务修改未提交的数据，出现脏读、不可重复读、幻读。
读已提交(READ COMMITTED)：一个事务中两次读取同一数据得到不同结果，出现不可重复读、幻读。
可重复读(REPEATABLE READ)：一个事务中两次查询返回的记录数不同，出现幻读。
串行化(SERIALIZABLE)：最严格隔离级别，强制事务串行执行，对所有读写都加锁。完全避免脏读、不可重复读、幻读。
常用命令
SET AUTOCOMMIT=0 # 关闭自动提交
SET AUTOCOMMIT=1 # 开启自动提交
BEGIN # 开启一个事务
START TRANSACTION # 开启一个事务
COMMIT # 提交一个事务给数据库
ROLLBACK # 将事务回滚,数据回到本次事务的初始状态
SAVEPOINT 保存点名称 # 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 # 回滚到保存点
RELEASE SAVEPOINT 保存点名称 # 删除保存点
用户管理
用户
# 用户信息表：mysql数据库user表
# 增加用户
CREATE USER 用户名 IDENTIFIED BY PASSWORD('密码')
# 重命名用户
RENAME USER 旧用户名 TO 新用户名
# 为当前用户设置密码
SET PASSWORD = PASSWORD('密码')
# 为指定用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')
# 删除用户
DROP USER 用户名
用户权限
# 查看权限
SHOW GRANTS FOR 用户名
# 查看当前用户权限
SHOW GRANTS FOR CURRENT_USER;
# 添加权限/增加用户并分配权限
# 表名参数：库名.表名 表示某库下面的某表 *.*表示所有库的所有表
GRANT 权限列表 ON 表名参数 TO 用户名 IDENTIFIED BY PASSWORD('密码')
# 撤消权限
# 表名参数：库名.表名 表示某库下面的某表 *.*表示所有库的所有表
REVOKE 权限列表 ON 表名参数 FROM 用户名
# 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名
# 刷新权限
FLUSH PRIVILEGES
权限列表
ALL PRIVILEGES # 设置除GRANT OPTION之外的所有简单权限
ALTER # 允许使用ALTER TABLE
ALTER ROUTINE # 更改或取消已存储的子程序
CREATE # 允许使用CREATE TABLE
CREATE ROUTINE # 创建已存储的子程序
CREATE TEMPORARY TABLES # 允许使用CREATE TEMPORARY TABLE
CREATE USER # 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW # 允许使用CREATE VIEW
DELETE # 允许使用DELETE
DROP # 允许使用DROP TABLE
EXECUTE # 允许用户运行已存储的子程序
FILE # 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX # 允许使用CREATE INDEX和DROP INDEX
INSERT # 允许使用INSERT
LOCK TABLES # 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS # 允许使用SHOW FULL PROCESSLIST
REFERENCES # 未被实施
RELOAD # 允许使用FLUSH
REPLICATION CLIENT # 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE # 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT # 允许使用SELECT
SHOW DATABASES # 显示所有数据库
SHOW VIEW # 允许使用SHOW CREATE VIEW
SHUTDOWN # 允许使用mysqladmin shutdown
SUPER # 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE # 允许使用UPDATE
USAGE # “无权限”的同义词
GRANT OPTION # 允许授予权限
四、拓展
数据备份
概念
备份作用：
# 保证重要数据不丢失
# 进行数据转移
备份方法：
# mysqldump备份工具
# 第三方可视化工具备份
# 直接拷贝数据库文件和相关配置文件
导出
# 导出一张表
mysqldump -u用户名 -p密码 库名 表名 > 路径/文件名;
# 导出多张表
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 路径/文件名;
# 导出所有表
mysqldump -u用户名 -p密码 库名 > 路径/文件名;
# 导出一个库
mysqldump -u用户名 -p密码 -B 库名 > 路径/文件名;
# 导出表中数据
select * from <表名> into outfile 导出的路径;
导入
# 导入数据
source 源数据路径;
# 向已存在的表中导入数据
load data local infile 源数据路径 into table 表名;
设计规范
三大范式
第一范式 (1st NF)
# 第一范式: 确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
第二范式(2nd NF)
# 第二范式: 要求每个表只描述一件事情
# 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
第三范式(3rd NF)
# 第三范式: 确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
# 如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式。
规范化和性能关系
# 为满足某种商业目标,数据库性能比规范化数据库更重要
# 在数据规范化的同时,要综合考虑数据库的性能
性能优化
# 表设计优化
# SQL语句优化
# 加索引
# 分库分表
# 主从复制、读写分离
# 增加额外方便查询的列
