目录
第一章:基础语法
DDL:数据定义语言
1.操作数据库
(1)查看所有数据库
show databases;
(2)查询所有数据库
select database();
(3)创建数据库
create database [if not exists] 数据库名 [可添加字符集规则,指定引擎等];
(4)删除数据库
drop database [ if exists ] 数据库名;
(5)切换数据库
use 数据库名;
2.操作数据表
(1)查询当前数据库所有表
show tables;
(2)查看表结构
desc 表名;
(3)查询建表语句
show create table 表名;
(4)创建表结构
create tables 表名(
字段1 字段1类型
字段2 字段2类型
字段2 字段2类型
);
(5)添加字段
alter table 表名 add 字段名 类型 [ 约束 ];
(6)修改数据类型
alter table 表名 modify 字段名 新数据类型;
(7)修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型 [ 约束 ];
(8)删除字段
alter table 表名 drop 字段名;
(9)修改表名
alter table 表名 rename to 新表名;
(10)删除表
drop table 表名;
DML:数据操作语言
1.增加数据
(1)给指定字段添加数据
insert into 表名 (字段1,字段2,...) values(值1,值2,...);
(2)给全部字段添加数据
insert into 表名 values(值1,值2,...);
2.修改数据
update 表名 set 字段1 = 值1 , 字段2 = 值2 ,... [ where 条件];
3.删除数据
delete from 表名 [ where 条件];
DQL:数据查询语言
1.基本语法
select
字段列表4
from
表名列表1
where
条件列表2
group by
分组字段列表3
having
分组后条件列表
order by
排序字段列表5
limit
分页参数6
2.基础查询
(1)查询多个字段
select 字段1 ,字段2 ,... from 表名;
select * from 表名;
(2)去除重复记录
select distinct 字段列表 from 表名;
3.条件查询
(1)基础语法
select 字段列表 from 表名 where 条件列表;
(2)条件
除了常见的>,>=,<,<=,=和!=外还有
比较运算符 | 功能 |
between 值1 and 值2 | 在某个范围内(包括最大值及最小值) |
in(...) | 在in之后的列表中的值,多选一 |
like '占位符' | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
is null | 值为null |
4.聚合函数
常见聚合函数(count、max、min、avg、sum)
注意:NULL值不参与聚合函数运算
5.分组查询
(1)语法
select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [having 分组后过滤条件];
6.排序查询
(1)语法
select 字段列表 from 表名 order by 字段1 排序方式 ,字段2 排序方式;
7.分页查询
(1)语法
select 字段列表 from 表名 limit 起始索引,查询记录数;
DCL:数据控制语言
1.管理用户
(1)查询用户
select * from mysql.user;
(2)创建用户
create user '用户名'@'主机名' identified by '密码';
(3)修改用户密码
alter user '用户名'@'主机号' identified with mysql_native_password by '新密码';
(4)删除用户
drop user '用户名'@'主机名';
2.权限控制
(1)查询权限
show grants for '用户名'@'主机名';
(2)授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
(3)撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
多表联查
1.内连接
(1)隐式内连接
select 字段列表 from 表1 ,表2 where 条件;
(2)显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
2.外连接
(1)左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
(2)右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;
3.自连接
(1)自连接查询
select 字段列表 from 表A 别名A join 表A 别名B on 条件;
(2)联合查询
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
/*注意:union会对查询后的数据去重,而union all不会*/
4.子查询
select * from t1 where column1 = (select coulumn1 from t2);
/*子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。*/
第二章:进阶语法
索引
1.索引语法
(1)创建索引
create index 索引名 on 表名(列1 ,列2 ,...);
(2)查看索引
show index from 表名;
(3)删除索引
drop index 索引名 on 表名;
(4)前缀索引
create index 索引名 on 表名(列名(字符长度));
/*有些字段里内容过长,影响效率,所以我们以它的一部分作为索引内容*/
视图
1.基本语法
(1)创建视图
create view 视图名 as select语句;
(2)查看视图
select * from 视图名称;
(3)修改视图
alter view 视图名 as select语句;
(4)删除索引
drop view 视图名;
存储过程
1.基本语法
(1)创建存储过程
create procedure 存储过程名 ([参数列表]);
begin
--SQL语句
end;
(2)调用存储过程
call 名称 ([参数]);
(3)查看存储过程
/*查询指定数据库的存储过程及状态信息*/
select * from information_schema.routines where routines_schema = 'xxx';
/*查询存储过程的定义*/
show create procedure 存储过程名;
(4)删除存储过程
drop procedure 存储过程名;
2.变量
(1)系统变量
系统变量分为全局变量(global)和会话变量(session)。
/*查看所有系统变量*/
show [session/global] variables;
/*模糊匹配*/
show [session/global] variables like '...';
/*设置系统变量*/
set 系统变量名 = 值;
(2)用户自定义变量
/*赋值*/
set @变量名 = 值1;
/*使用*/
select @变量名
(3)局部变量
/*声明*/
declare 变量名 变量类型 [default...];
/*赋值*/
set 变量名 = 值;
3.IF
(1)基本语法
if 条件1 then
......
elseif 条件2 then
......
else
......
end if;
4.参数
/*参数类型分为in、out、inout*/
create procedure 存储过程名 ([in/out/inout 参数名 参数类型])
begin
--SQL语句
end;
5.case
(1)基本语法
/*哪个条件成立就执行对应语句,否则执行else*/
case
when 条件1 then 执行语句1
when 条件2 then 执行语句2
else 执行语句
end case;
6.while
(1)基本语法
/*判定条件为真则执行*/
while 条件 do
--SQL逻辑
end while;
7.repeat
(1)基本语法
/*先执行一次语句,然后判定until是否满足*/
repeat
--SQL逻辑
until 条件
end repeat;
8.游标
(1)声明游标
declare 游标名称 cursor for 查询语句;
(2)打开游标
open 游标名称;
(3)获取游标记录
fetch 游标名称 into 变量 [,变量];
(4)关闭游标
close 游标名称;
触发器
1.类型
触发器分为insert/delete/update三种,在数据库进行增删改之前或之后,触发器会自动执行定义好的SQL语句集合。
2.基础语法
(1)创建触发器
crate trigger 触发器名
before/after insert/delete/update
on 表名 for each row
begin
--SQL语句
end;
(2)查看触发器
show triggers;
(3)删除触发器
drop trigger 触发器名;
第三章:运维阶段
日志
1.错误日志
该日志默认开启,存放在/var/log/mysqld.log
/*查看日志位置*/
show variables like '%log_error%';
2.二进制日志
(1)介绍
二进制日志记录了所有的DDL和DML语句。
作用:①数据恢复;②主从复制。
/*查看二进制日志相关参数*/
show variables like '%log_bin%';
(2)查看二进制日志
mysqlbinlog [参数] 二进制文件名
/*
参数选项
-d 指定数据库名
-o 忽略日志中的前n行
-v 将行事件重构为SQL语句
-vy 将将行事件重构为SQL语句,并输出注释信息
*/
(3)开启二进制文件
# 修改my.cnf文件,添加如下内容
vim /etc/my.cnf
log_bin=bin
binlog_format=ROW
(4)删除二进制文件
/*删除全部binlog日志*/
reset master
/*删除指定数字之前的所有日志*/
purge master logs to 'binlog.数字'
/*删除指定日期之前的所有日志*/
purge master logs before 'yyyy-mm-dd hh24:mi:ss'
/*在配置文件中设置二进制日志的过期时间*/
show variables like '%binlog_expire_logs_seconds%';
3.慢查询日志
/*修改/etc/my.cnf*/
/*设置为1开启慢查询日志*/
slow_query_log=1
/*设置超时多久记录*/
long_query_time=2
/*记录执行较慢的管理语句*/
log_slow_admin_statements=1
/*记录执行较慢的未使用索引的语句*/
log_queries_not_using_indexes=1
备份还原
1.mysqldump备份还原
(1)基本语法
备份单个数据库
mysqldump -u 用户名 -h主机名 -p 数据库名称>文件名.sql
备份部分数据库
mysqldump -u 用户名 -h 主机名 -p --databases 数据库1 数据库2>文件名.sq1
备份所有数据库
mysqldump -u 用户名 -h 主机名 -p -A>文件名.sq1
(2)还原备份数据
如备份文件中包含了创建数据库的语句,恢复时不需要指定数据库名称
mysql -u root -p<备份文件名.sql
否则需要指定数据库名称
mysql -uroot -p 数据库名<备份文件名.sql
主从复制
1.主库配置
(1)修改/ect/my.cnf
vim /etc/my.cnf
/*MySQL服务ID,在集群中唯一,取值范围1-231*/
server-id=1
/*1表只读,0表读写*/
read-only=0
(2)重启MySQL
systemctl restart mysqld
(3)创建从库账号并授予主从复制权限
/*创建从库用户*/
create user '用户名'@'%' identified with mysql_native_password by '密码';
/*授予主从复制权限*/
grant replication slave on *.* to '用户名'@'%';
(4)查看二进制日志位置
show master status;
2.从库配置
(1)修改配置文件
/*修改/etc/my.cnf*/
server-id=2
read-only=1
(2)重启MySQL
systemctl restart mysqld
(3)登陆MySQL,设置主库配置
CHANGE MASTER TO
MASTER_HOST='主库地址',
MASTER_USER='主从复制用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='binlog文件名',
MASTER_LOG_POS=binlog文件位置;
8.0.23以后的版本执行以下SQL
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='主库地址',
SOURCE_USER='主从复制用户名',
SOURCE_PASSWORD='密码',
SOURCE_LOG_FILE='binlog文件名',
SOURCE_LOG_POS=位置;
(4)开启同步操作
start slave;
8.0.23以后的版本执行以下SQL
start replica;
(5)查看主从同步状态
show slave status\G;
8.0.23以后的版本执行以下SQL
show replica status\G;