mysql基础
2020-11-10 13:49:40 0 举报AI智能生成
mysql基础
作者其他创作
大纲/内容
mysql安装.tar.gz
1、下载mysql安装包
2、安装rzsz并上传到home文件夹中
使用yum安装yum -y install lrzsz
rz 上传命令，从本地上传到CentOS
sz 下载命令，从CentOS下载到本地
3、创建文件夹
mkdir /usr/local/mysql
4、解压并复制
tar -xvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz/* /usr/local/mysql
5、创建一个mysql用户和组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
6.将位置更改为MySQL安装目录的顶级目录，通常是 /usr/local/mysql：如果目录不是这样的，请修改
mv mysql-5.7.30-linux-glibc2.12-x86_64/* ./
rm -rf mysql-5.7.30-linux-glibc2.12-x86_64
7.初始化数据目录
8.启动服务
bin/mysqld_safe --user=mysql &
登陆mysql
1、mysql -uroot -p123456
2、指定IP地址和端口号登录MySQL数据库
命令格式为： mysql -h 114.119.37.98 -ujwollse -pmf_jwollse_2019 -P 3306
mysql编码格式
修改编码
临时生效set names gbk;
改配置文件[mysql]
default-character-set=utf8
备份还原
导出数据库结构
mysqldump -u root -p --default-character-set=utf8 --databases test mysql > db_backup.sql
mysqldump -u root -p --default-character-set=utf8 --all-databases > db_backup.sql 所有库
mysqldump -u root -p dbname table1 table2 > db_backup.sql 多个指定表
导出数据
mysqldump -t 数据库名 -uroot -p > xxx.sql
还原
mysql -uroot -p db < D:/c.sql
变量
局部变量
一般用declare声明，可以用default来说明默认值
用set赋值
会话变量
查看show session variables like '△%';
指定变量名set @@session.变量名=某个值;
查看指定select @@session.变量名;
全局变量
show global variables；【语法同session】
用户变量
set @变量名=1;
存储过程
改变分隔符:delimiter $$
语法 create procedure △() begin... end
三种参数
in输入参数：必须在proc前指定，不能被返回
out输出参数：内部改变，可以返回
inout参数：可在调用时指定，并可以修改返回
create procedure (in v_int int) begin ... end
流程控制
条件控制
if：if ... elseif .. else ... end if;
case: case △ when ... then ... else ... end case;
ifnull(exp1,exp2)函数
循环控制
while：while △ do ... end while;
repeat： repeat △ utile 条件【退出条件】 end repeat
loop: loop名字 ： loop内容 if 条件 then leave loop loop名字; end if;end loop
定义条件和处理
declare continue handler for sqlstate '错误代码值' set 变量=△
存储过程
管理
查看proc:show procedure status where db='';
查看当前库下的proc列表:select specificaname from mysql.proc；
查看内容：show create procedure proc名字；
删除proc: drop procedure if exists 存储过程名；
函数
show variables like '%fun%'；
set global △=1; 开启功能
创建语法
create function 函数名([参数列表]) returns 数据类型
begin
sql语句;
return 值;
end;
删除
drop function if exists ..；
视图
修改/创建语法
CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];
with check option；更新视图的数据必须先满足视图条件才能更新到基础表
有聚合函数、distionct、group by、having、join等不可更新
触发器
语法
create trigger trigger_name trigger_time trigger_event on table_name for each row
begin
-- 需要执行的sql操作语句
end
//trigger_time:before/after ; trigger_event: update/delete/insert
注意
update 可用**old**访问旧数据，**new**访问新数据
delete 可用**old**访问旧数据
insert 可用**new**访问新数据
管理
show triggers; # 查看所有触发器
drop trigger 触发器名;
show create trigger 触发器名; # 查看创建指定的触发器语句
锁
概念
使各种共享资源在被并发访问变得有序所设计的一种规则
MyISAM采用表级锁，innoDB支持行级锁和表级锁
MyISAM表锁
读锁（共享锁）
针对同一份数据，多个读操作可以同时进行而不会互相影响。
加锁lock table tablename read; 解锁 unlock tables;
写锁（排它锁）
当前写操作没有完成前，它会阻断其他写锁和读锁。
加锁lock table tablename write; 解锁 unlock tables;
事务
概念
一个事务是一个连续的一组数据库操作
4个条件（ACID）
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
事务处理
BEGIN/START TRANSACTION 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
直接用 SET 来改变 MySQL 的自动提交模式:SET AUTOCOMMIT=0 禁止自动提交
commit and chain提交并启动新事务
慢查询
概念
mysql记录下查询超过指定时间的语句
show variables like '%long%'
show status语句
show status like 'uptime%'/'com_select'/'connections'/'slow-quries'[慢查询时间界限]
启动慢查询
临时开启
set global slow_query_log = on;
启动开启
以安全模式启动mysql
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables &
永久设置慢查询日志开启
修改my.cnf，log_show_queries、log_query_time、log_queries_not_using_indexes等参数
索引
概念
索引是一种与表有关的数据结构
分类
主键索引
alter table tbl_name add PRIMARY KEY(column);
唯一索引
create unique index idx_name on tbl_name(column); //创建
索引所在列可为null，但不能为空字符串
普通索引
create index idx_name on tbl_name(column);
全文索引
全文索引fulltext只有myisam支持，只可以从char、varchar、text列中创建,fulltext(column1,column2)；
data下文件后缀
.frm 表的结构；.myd 数据；.myi 索引文件
创建索引条件
在where条件中经常使用到的；该字段的变化不会太频繁
通过show profile分析sql
select @@have_profiling; 查看是否支持
set profiling=1; 开启
show profiles; 查看当前的SQL的queryID
show profiles for query queryID 分析
mysql优化
show variables like '%partition%';查看是否支持
select查询：当查询/更新等一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
表的分析、检查、优化
在执行期间将对表进行lock write锁定，不要在繁忙time执行
分析analyze table tbl_name;
检查check table tbl_name;
优化optimize table tbl_name;
show table status;获取表信息，rows【innodb结构的是估算的】
表分区
类型
Range分区
基于属于一个给定连续区间的列值，把多行分配给分区。
partition by range (store_id) (
partition p0 values less than (6),
partition p1 values less than (11),
partition p2 values less than (16),
partition p3 values less than (21)，
partition p3 values less than maxvalue
);
List分区
类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
partition by list(store_id)
partition pNorth values in (3,5,6,9,17),
partition pEast values in (1,2,10,11,19,20),
partition pWest values in (4,12,13,14,18),
partition pCentral values in (7,8,15,16)
)；
hash分区
基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。
partition by hash(store_id)
partitions 4；
keys分区
类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
partition by linear key (col1)
partitions 3;
操作
增加表分区
PARTITION p1 VALUES LESS THAN (MAXVALUE) 这句要去掉，才可以增加分区。
ALTER TABLE sale_data ADD PARTITION (PARTITION s20100402 VALUES LESS THAN (20100403));
删除表分区
ALTER TABLE sale_data DROP PARTITION s20100406 ;
mysql内存管理与优化
myisam
key_buffer_size;索引缓存空间大小
read_buffer_size
read_rnd_buffer_size
innodb
innodb_buffer_poor_size
innodb_log_buffer_size 日志缓存
max_connections最大连接数
mysql权限管理
查看db的用户 select host,user,password from user;
赋权 grant select/all on *.* to zy@localhost identified by '123' with grant option;
删除权限 rovoke select on *.* from zy@localhost;
删除用户及权限 drop user 'zy'@'localhost';
修改账号pwd set password for zy@localhost=password('new')；
mysql定时维护
开启event：set global event_scheduler=1;
查看event状态：show variables like '%sche%';
创建定时器
drop event if exists timer_smile;
CREATE EVENT IF NOT EXISTS timer_smile
ON SCHEDULE EVERY 1 SECOND
-- 某个时间执行
-- ON SCHEDULE AT TIMESTAMP '2018-09-17 18:16:00'
-- 五天后执行
-- ON SCHEDULE AT CURRENT_TIMESTAMP+INTERVAL 5 DAY
-- 可以设置是否立即生效
-- 设置定时任务创建后不立即开启
-- DISABLE 表示的是关闭 , 默认是开启的
ON COMPLETION NOT PRESERVE DISABLE
DO INSERT `test`(`name`,`type`)VALUES(CURRENT_TIMESTAMP,'1');
开启定时器任务
ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;
0 条评论
下一页
