mysql 学习笔记-阿里云开发者社区

以前主要使用oracle做数据库，现在换成mysql了，发现不一样的地方还是挺多的，记录一下：

一、centos上的yum install方式安装

完全卸载(可选，如果之前安装了旧版本)

a) rpm -qa|grep mysql

先查看是否已经安装了mysql

b) yum remove mysql*

执行完成后，再执行下a)中的命令确认下

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的游标是以临时表实现的，性能不怎么样，如果游标中处理上十万条数据，就比较慢。

mysql 学习笔记

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像