mysql 学习笔记
以前主要使用oracle做数据库,现在换成mysql了,发现不一样的地方还是挺多的,记录一下:
一、centos上的yum install方式安装
完全卸载(可选,如果之前安装了旧版本)
a) rpm -qa|grep mysql
先查看是否已经安装了mysql
b) yum remove mysql*
执行完成后,再执行下a)中的命令确认下
c)
rm -f /etc/my.cnf
rm -f /etc/my.cnf.rmp*
rm -rf /var/lib/mysql
安装最新版mysql
a) http://dev.mysql.com/downloads/repo/ 找到最新的
Red Hat Enterprise Linux 6 / Oracle Linux 6 (Architecture Independent), RPM Package |
|
8.6K |
||
(mysql57-community-release-el6-7.noarch.rpm) |
MD5: 4c4d512821c9cdbb8987d1942db84d11 |
找到rpm的下载(目前最新是5.7版,8.6k)
b) 把这个文件上传到服务器,比如:/data/download 下
c) 导入yum库
yum localinstall /data/download/mysql57-community-release-el6-7.noarch.rpm
d) 安装
yum install mysql-community-server
以下是安装后的几个关键目录默认位置:
数据库目录
/var/lib/mysql/
配置文件
/usr/share/mysql(mysql.server命令及配置文件)
相关命令
/usr/bin(mysqladmin mysqldump等命令)
启动脚本
/etc/rc.d/init.d/(启动脚本文件mysql的目录)
查看配置文件位置
mysqld --verbose --help |grep -A 1 'Default options’
会输出类似下面的信息:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
这表示mysql启动时会查找/etc/my.cnf,如果找不到,则到路径/etc/mysql/my.cnf,依此类推...
修改my.cnf
参考配置:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 [client] 2 3 default-character-set=utf8 4 5 [mysqld] 6 7 default-character-set=utf8 8 9 character_set_server=utf8 10 11 # innodb_buffer_pool_size = 128M 12 13 # log_bin 14 15 # join_buffer_size = 128M 16 17 # sort_buffer_size = 2M 18 19 # read_rnd_buffer_size = 2M 20 21 datadir=/data/mysql/db 22 23 socket=/var/lib/mysql/mysql.sock 24 25 symbolic-links=0 26 27 log-error=/data/mysql/log/mysqld.log 28 29 pid-file=/data/mysql/mysqld.pid
这里有几个关键参数,需要修改
default-character-set
character_set_server
这个是设置utf8编码,可以解决大多数中文乱码问题
datadir
log-error
pid-file
分别对应数据库文件目录位置,日志文件位置,pid文件位置,建议调整到剩余空间较大的分区
innodb_buffer_pool_size 这个是会影响mysql的性能,后面还会讲到
启动mysqld
a) 调整目录权限
chown -R mysql:mysql /data/mysql
启动前,建议先确认下相关目录,mysql有没有读写权限,否则启动会失败
service mysqld start
修改root密码:
mysql 5.7对用户安全性做了加强,默认root账号是无法登录的,修改方法如下:
service mysqld stop //先停止
mysqld_safe --skip-grant-tables //以安全模式启动
另开一个ssh终端窗口
mysql //进入mysql控制台
update user set authentication_string=password(‘新密码') where user='root’;
注:新密码必须复杂安全性要求,建议弄成A1b2c3@def.com这种复杂的
重启mysql
service mysqld stop
service mysqld start
现在mysql -uroot -p 应该能登录进去了
如果进去执行其它操作时,比如创建数据库时,提供要重设密码之类的,mysql命令行模式下再执行一遍下面的操作
set password=password(‘新密码’);
二、创建数据库及用户授权
特别要注意编码
create database xxx default character set utf8;
(注:从5.0.2开始,创建数据库也可以用create schema命令,这二者在mysql中等效的,这跟其它主流关系型数据库,比如:oracle,ms sql中的schema概念完全不同)
如果建错了,想删除数据库
drop database xxx;
切换数据库
use xxx
查看所有数据库
show database;
用户授权:
GRANT ALL PRIVILEGES ON db1.* TO ‘user1’@‘localhost’ IDENTIFIED BY ‘pwd1’;
上面的语句将db1的所有权限授权给用户user1,如果只想授权部分权限,参考下面的示例:
grant select on table2 to 'user1'@'localhost';
三、一些常用的sql操作
a) 执行外部sql脚本文件
mysql命令行下
source /opt/app/sql/xxx.sql (注:xxx.sql必须存在,且mysql账号必须有权限读取)
b)查看当前正在运行的sql
show processlist
通常mysql运行很卡时,用这个命令查找当前正在跑的sql,然后找到其id,方便将其kill掉
c) kill掉指定id的sql操作
mysqladmin -h 服务器 -u用户名 -p kill id号
d) 查看当前数据库引擎状态
show engine innodb status\G;
e) 查看几个关键参数:
show global status like 'innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 4053 |
+-------------------------------+-------+
1 row in set (0.01 sec)
show global status like 'innodb_buffer_pool_pages_total’;
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 4095 |
+--------------------------------+-------+
1 row in set (0.01 sec)
这二个的比值,即innodb_buffer_pool_pages_data/innodb_buffer_pool_pages_total ,按网上的经验之谈,如果>95%,说明mysql内存快满负载了,建议大innodb_buffer_pool_size的值 ,建议设置成系统内存的75%
注:select @@innodb_buffer_pool_size 显示出来的值
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 8388608 |
+---------------------------+
1 row in set (0.00 sec)
是以字节为单位的,要转换成M,需要除1024*1024,上面的值8388608,即相当于8388608/(1024*1024)=8M
f) 数据导出
导出整个数据库(包括数据)
mysqldump -h服务器ip -u 用户名 -p 数据库名 > 导出的文件名
导出单个表(包括数据)
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
仅导出表结构
./mysqldump -u用户名 -p -d --add-drop-table 数据库名 > 导出的文件名
仅导出数据
./mysqldump -u用户名 -p -t 数据库名 > 导出的文件名
导出后的sql脚本,可以在目标数据库上,通过前面提到的source命令导入
g) 查看所有表/视图/存储过程
show tables;
SELECT * from information_schema.VIEWS\G;
show procedure status\G;
h)查看表结构、视图结构、存储过程sql
show create table 表名\G;
show create view 视图名\G;
show create function 函数名\G;
show create procedure 存储过程名\G;
show create database 数据库名\G;
i) update ...join... on 操作
1 UPDATE table1 2 INNER JOIN table2 ON ( 3 table1.id = table2.id 4 ) 5 SET table1.x = table2.y;
j) 快速复制一张表
create table table1_bak select * from table1;
k) 将一张表的某些记录快速插入相同结构的备份表中
insert into table1_bak select * from table1 limit 0,5;
l) 跨库查询
1 SELECT 2 count(*) 3 FROM 4 db1.table1 t1 5 INNER JOIN db2.table2 t2 ON t1.id = t2.id 6 WHERE 7 t1.id > 0
前提:当前用户有db2.table2的select权限,如果没权限,先按前面的用户授权方法给相对的表授权。
四、自定义函数,游标,存储过程
a) 先解决命令行模式下;号的问题
因为;是默认的命令结束符号,写自定义函数或存储过程的时候,本身就会包含;符号,导致命令行下,mysql误认为存储过程代码结果,解决办法
delimiter //
上面的命令告诉mysql,命令结束符号为//,而不是默认的;
存储过程写完了以后,再执行
delimiter ;
还原回来
b) 自定义函数示例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 DELIMITER // 2 DROP FUNCTION 3 IF EXISTS `ifempty`// 4 5 CREATE FUNCTION `ifempty`( 6 s1 VARCHAR(4096), 7 s2 VARCHAR(4096) 8 ) 9 RETURNS VARCHAR(4096) 10 CHARSET utf8 NO SQL DETERMINISTIC SQL SECURITY INVOKER 11 BEGIN 12 13 IF (ISNULL(s1)) 14 THEN 15 RETURN s2; 16 ELSEIF CHAR_LENGTH(s1) = 0 17 THEN 18 RETURN s2; 19 ELSE 20 RETURN s1; 21 END 22 IF; 23 END//
c) 存储过程示例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN b INT) 4 5 BEGIN 6 7 DECLARE a INT; 8 9 SET a = b + 1; 10 11 SELECT a; 12 13 END 14 15 //
d)三种常用的循环写法
while..do 写法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN b INT) 4 5 BEGIN 6 7 DECLARE i INT; 8 9 SET i = 0; 10 11 WHILE i < b DO 12 13 SELECT i; 14 15 SET i = i + 1; 16 17 END WHILE; 18 19 END 20 21 //
repeat 写法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN b INT) 4 5 BEGIN 6 7 DECLARE i INT DEFAULT 0; 8 9 REPEAT 10 11 SELECT i; 12 13 SET i = i + 1; 14 15 UNTIL i >= b 16 17 END REPEAT; 18 19 END 20 21 //
loop .. end loop写法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN b INT) 4 5 BEGIN 6 7 DECLARE i INT DEFAULT 0; 8 9 mylabel: LOOP 10 11 SELECT i; 12 13 SET i = i + 1; 14 15 IF i >= b 16 THEN 17 18 LEAVE mylabel; 19 20 END IF; 21 22 END LOOP; 23 24 END 25 26 //
e) 游标示例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 DELIMITER // 2 3 CREATE PROCEDURE test(IN min_id INT) 4 5 BEGIN 6 7 DECLARE _done INT DEFAULT 0; 8 -- 判断游标是否结束的标志 9 10 DECLARE p_id INT DEFAULT 0; 11 12 DECLARE p_name VARCHAR(100) DEFAULT ''; 13 14 DECLARE _cur CURSOR FOR 15 16 SELECT 17 t.`d_id`, 18 t.`d_name` 19 FROM t_test AS t 20 WHERE t.`d_id` >= min_id; 21 22 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1; 23 -- 标记循环结束 24 25 OPEN _cur; 26 27 REPEAT 28 29 FETCH _cur 30 INTO p_id, p_name; 31 32 IF NOT _done 33 THEN 34 35 SELECT 36 p_id, 37 p_name; 38 39 END IF; 40 41 UNTIL _done 42 43 END REPEAT; 44 45 CLOSE _cur; 46 47 END 48 49 //
当然,也可以将游标的遍历换成while do ...end while的写法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 DELIMITER // 2 3 DROP PROCEDURE IF EXISTS p_test_cursor // 4 5 CREATE PROCEDURE p_test_cursor() 6 7 BEGIN 8 DECLARE _done INT DEFAULT 0; -- 判断游标是否结束的标志 9 10 DECLARE p_activity_id INT DEFAULT 0; 11 DECLARE p_community_id INT DEFAULT 0; 12 13 DECLARE _cur CURSOR FOR 14 SELECT 15 t.activity_id, 16 t.community_id 17 FROM h_activity_community AS t; 18 19 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1; -- 标记循环结束 20 21 OPEN _cur; 22 23 WHILE _done != 1 24 DO 25 FETCH _cur 26 INTO p_activity_id, p_community_id; 27 28 IF (_done != 1) -- 如果游标没结束,就打印出这些变量值 29 THEN 30 SELECT 31 p_activity_id, 32 p_community_id, 33 _done; 34 END IF; 35 36 END WHILE; 37 38 CLOSE _cur; 39 COMMIT; 40 END 41 //
注:mysql的游标是以临时表实现的,性能不怎么样,如果游标中处理上十万条数据,就比较慢。
出处:http://yjmyzz.cnblogs.com
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。