docker安装mysql并使用

mysql安装

1. 拉取镜像

docker pull mysql:5.7

查看镜像：docker images；

2. 启动容器

dodocker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

3. 查看运行的容器：docker ps;
   

4. 进入容器

docker exec -it 6002d9cd5075 /bin/bash;

5. 进入mysql：mysql -u root -p;
   
6. 选择database
   

mysql使用

1. 创建database;

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

2. 使用test实例：use test;
   
3. 创建表

#建学生信息表student
create table student
(
	id int(11) not null primary key,
	uname varchar(20),   
	address  varchar(20),   
	age  int(11)
)   
ENGINE=INNODB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci   
AUTO_INCREMENT=1   
ROW_FORMAT=COMPACT; 

查看表结构： desc table_name;

4. 生成数据

# 生成数字
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `random_num`( ) RETURNS int(5)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END$$
DELIMITER ;

# 生成字符串
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `random_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END$$
DELIMITER ;

# 生成数据
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `insert_student`(IN START INT(10),IN max_num INT(10))
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO student (id,uname,address,age) VALUES (START+i,random_string(10),random_string(6),random_num());  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
  END$$
DELIMITER;

• 函数查询：

select `name` from mysql.proc where db = 'test' and `type` = 'FUNCTION';

• 存储过程查询：

select `name` from mysql.proc where db = 'test' and `type` = 'PROCEDURE';

• 调用过程：

CALL insert_student(1,100);