mysql 学习笔记

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql 学习笔记

杨俊明 2015-11-04 18:43:00 浏览496
展开阅读全文

以前主要使用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

Download

(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 

参考配置:

 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
View Code

这里有几个关键参数,需要修改

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) 自定义函数示例

 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//
View Code

c) 存储过程示例

 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 //
View Code

d)三种常用的循环写法 

while..do 写法 

 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 //
View Code

repeat 写法 

 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 // 
View Code

loop .. end loop写法 

 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 //
View Code

e) 游标示例 

 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 //
View Code

当然,也可以将游标的遍历换成while do ...end while的写法 

 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 //
View Code

注:mysql的游标是以临时表实现的,性能不怎么样,如果游标中处理上十万条数据,就比较慢。

网友评论

登录后评论
0/500
评论
杨俊明
+ 关注