mysql入门

一、MYSQL在容器中的安装与运维，与空间的扩展

1.创建mysql容器
docker run --name test-mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3308:3306 -d mysql:5.7

2.mysql容器目录挂载my-vol数据卷
docker run -d -it --name test-mysql02 -v my-vol:/home/mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3308:3306 -d mysql:5.7

3.mysql容器文件挂载my-vol数据卷
docker run -d -it --name test-mysql02 -v /home/mysql:/home/mysql -e MYSQL_ROOT_PASSWORD=admin123 -p 3308:3306 -d mysql:5.7

4.查看所有数据卷
docker volume ls

5.容器开机自启动
docker run -d --restart=always --name 设置容器名 使用的镜像
（上面命令 --name后面两个参数根据实际情况自行修改）

Docker 容器的重启策略如下：

–restart具体参数值详细信息：
no　　　　　　　 // 默认策略,容器退出时不重启容器；
on-failure　　 // 在容器非正常退出时（退出状态非0）才重新启动容器；
on-failure:3 // 在容器非正常退出时重启容器，最多重启3次；
always　　　　 // 无论退出状态是如何，都重启容器；
unless-stopped // 在容器退出时总是重启容器，但是不考虑在 Docker 守护进程启动时就已经停止了的容器。

docker update --restart=always 容器ID(或者容器名)
（容器ID或者容器名根据实际情况修改）

二、数据库常用的查询

1、where 条件
创建数据库
CREATE DATABASE 数据库名;
create table students(
id int primary key not null,
name varchar(20) default ‘’,
age tinyint default 0,
height decimal(5,1),
gender enum(‘男’,‘女’,‘中性’,‘保密’) default ‘保密’,
cls_id int default 0,
is_delete bit
);

插入数据
insert into students values
(0,‘小明’,18,180,2,1,0),
(1,‘小月月’,18,180,2,2,0),
(2,‘彭于晏’,29,185,1,1,0),
(3,‘刘德华’,59,175,1,2,0),
(4,‘黄蓉’,38,160,2,1,0),
(5,‘凤姐’,28,150,4,2,1),
(6,‘王祖贤’,18,172,2,1,1),
(7,‘周杰伦’,36,NULL,1,1,0),
(8,‘程坤’,27,181,1,2,0),
(9,‘刘亦菲’,25,166,2,2,0),
(10,‘金星’,33,162,3,3,1),
(11,‘静香’,12,180,2,4,0),
(12,‘郭靖’,12,170,1,4,0),
(13,‘周杰’,34,176,2,5,0);

创建数据表
create table classes(
id int primary key not null,
name varchar(30) not null
);

插入数据
INSERT into classes VALUES (2,‘python_01期’),(1,“python_02期”);

查询所有字段
语句：select * from 表名
select * from students

查询指定字段
select name , age from students;

使用as 给字段取别名
select name as 姓名 , age as 年龄 from students;
SELECT s.name as 姓名 ,s.age as 年龄 from students as s

使用as给表格取别名
SELECT s.name as 姓名 ,s.age as 年龄 from students as s

消除重复行
语句： SELECT DISTINCT 字段名 FROM 表名
SELECT DISTINCT gender FROM students

条件查询
比较运算符：
select 。。。from 表名 where。。。。

查询大于18岁的信息
SELECT name as 姓名 , id as 数字 from students WHERE age>18;

<
查询小于18岁的信息
SELECT name as 姓名 from students WHERE age<18;

=
<=
查询小于或等于18岁的信息
SELECT name as 姓名 from students WHERE age<=18;

=
查询等于18岁的所有学生的名字
SELECT name as 姓名 from students WHERE age=18;

!= 或者<>
逻辑运算符
and
18到28之间的所有的学生信息
SELECT name as 姓名 , age as 年龄 from students where age>18 and age<28;

18岁以上的女性
SELECT name as 姓名 , age as 年龄 , gender as 性别 from students where age>18 and gender =“女”;

or
18以上或者身高查过180（包含）以上
SELECT * from students WHERE age>18 or height>=180;

not
不在 18岁以上的女性 这个范围内的信息
SELECT * from students where not (age>18 and gender =“女”);

年龄不是小于或者等于18 并且是女性
SELECT * from students where age>18 and gender =“女”;
SELECT * from students where (not age<=18) and gender =“女”;

模糊查询
like

%替换一个或者多个
替换一个
查询姓名中以“小”开始的名字
SELECT name FROM students where name LIKE “小%”;
SELECT name FROM students where name LIKE "小";

查询姓名中以“小”开始的所有名字
SELECT name from students where name like “%小%”;

查询两个字的名字
SELECT NAME FROM students WHERE NAME LIKE “__”

查询三个字的名字
SELECT NAME FROM students WHERE NAME LIKE “___”

查询至少有两个字的名字
SELECT NAME FROM students WHERE NAME LIKE “__%”

rlike 正则
查询以周开始的姓名
SELECT name FROM students WHERE NAME RLIKE “^周.*”;

查询以周开始，伦结尾的姓名
SELECT name FROM students WHERE NAME RLIKE “^周.*伦$”;

范围查询
in(1,3,8)表示一个非连续的范围内
查询 年龄为：18、34的姓名
SELECT * FROM students where age in (18,34)

not in 不非连续的范围之内
年龄不是18、34岁之间的信息
SELECT * FROM students where age not in (12,18,34)

between … and … 表示在一个连续的范围内
SELECT * FROM students where age BETWEEN 18 and 34

not between … and … 表示不在一个连续的范围内
–查询 年龄不在18到34之间的信息
SELECT * FROM students where age not BETWEEN 18 and 34
SELECT * FROM students where not (age BETWEEN 18 and 34)

空判断
判空 is null
查询身高为空的信息
SELECT * FROM students WHERE height is null

判非空 is not null
SELECT * FROM students WHERE height is not null

排序
order by 字段
asc 从小到大排练 即升序
desc 从大到小排练 即降序

查询年龄在18到34岁之间的男性 ，按照年龄从小到大排序
SELECT * from students WHERE (age BETWEEN 18 and 34) and (gender = ‘男’) ORDER BY age

查询年龄在18到34岁之间的女性，升高从高到矮排序
SELECT * from students WHERE (age BETWEEN 18 and 34) and (gender = ‘女’) ORDER BY height desc

order by 多个字段
查询年龄在18到34岁之间的女性，升高从高到矮排序，如果身高相同的情况下按照年龄从小到大排序
SELECT * from students WHERE (age BETWEEN 18 and 34) and (gender = ‘女’) ORDER BY height desc, age asc

从小到大asc
从大到小desc
查询年龄在18到34岁之间的女性，升高从高到矮排序，如果身高相同的情况下按照年龄从小到大排序
如果年龄也相同按照ID从大到小排序
SELECT * from students WHERE (age BETWEEN 18 and 34) and (gender = ‘女’) ORDER BY height desc, age asc ,cls_id desc

按照年龄从小到大，升高从高到矮排序
SELECT * from students WHERE ORDER BY age;

三、常用函数。

聚合函数，可以计算出某个值
总数count
查询男生有多少人，女生有多少人
SELECT COUNT() from students WHERE gender = ‘男’
SELECT COUNT() as 女生人数 from students WHERE gender = ‘女’

最大值max
查询最大的年龄
SELECT MAX(age) from students

查询女生的最高，升高
SELECT MAX(height) from students where gender =‘女’

最小值min
求女生最小的升高
SELECT min(height) from students where gender =‘女’

求和sum
计算所有人年龄的总和
SELECT sum(age) from students

平均值
avg
计算平均年龄
SELECT avg(age) from students

计算平均年龄sum（age）/conut（）
SELECT sum(age)/count() from students

四舍五入round （123.23,1）保留1位小数
SELECT ROUND(sum(age)/count(*),1) from students

计算所有人的平均年龄，保留2位小数
SELECT ROUND(sum(age)/count(*),2) from students

计算男性的平均升高 保留2位小数
SELECT ROUND(AVG(height),2) from students WHERE gender = ‘男’

四、group by 分组

1.创建表：employee_tbl
CREATE TABLE employee_tbl (
id int(11) NOT NULL,
name char(10) NOT NULL DEFAULT ‘’,
date datetime NOT NULL,
signin tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘登录次数’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入数据
INSERT INTO employee_tbl VALUES
(‘1’, ‘小明’, ‘2016-04-22 15:25:33’, ‘1’),
(‘2’, ‘小王’, ‘2016-04-20 15:25:47’, ‘3’),
(‘3’, ‘小丽’, ‘2016-04-19 15:26:02’, ‘2’),
(‘4’, ‘小王’, ‘2016-04-07 15:26:14’, ‘4’),
(‘5’, ‘小明’, ‘2016-04-11 15:26:40’, ‘4’),
(‘6’, ‘小明’, ‘2016-04-04 15:26:54’, ‘2’);

3.查询所有数据
select * from employee_tbl

4.示例：使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：
SELECT name,COUNT(*) from employee_tbl GROUP BY name

表名：students
5.按性别分组将每个性别的人数，人员名称和平均年龄展示出来
select gender,count(*),avg(age),group_concat(name) from students group by gender

表名：students
6.分页limit
查询数据表的前5个数据
SELECT * from students LIMIT 5

查询学生信息按照年龄升序排序，在结果中从第11条数据开始取，取两个出来
select * from students order by age asc limit 10,2

五、多表关联 join

创建数据表 tbl_language、tbl_rank
DROP TABLE IF EXISTS tbl_language;
DROP TABLE IF EXISTS tbl_rank;

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tbl_rank(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
month VARCHAR(7) NOT NULL,
rank TINYINT NOT NULL,
rate VARCHAR(32) NOT NULL,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据：
INSERT INTO tbl_language VALUES
(1,‘Python’,‘https://www.twle.cn’,‘1991-2-20’),
(2,‘PHP’,‘http://www.php.net’,‘1994-1-1’),
(3,‘Ruby’,‘https://www.ruby-lang.org/’,‘1996-12-25’),
(4,‘Kotlin’,‘http://kotlinlang.org/’,‘2016-02-17’);

INSERT INTO tbl_rank VALUES
(1, ‘Python’,‘2018-04’,4,‘5.083%’),
(2, ‘PHP’,‘2018-04’,6,‘4.218%’),
(3, ‘Ruby’,‘2018-04’,11,‘2.018%’),
(4, ‘Java’,‘2018-04’,1,‘15.777%’),
(5, ‘Python’,‘2018-03’,4,‘5.869%’),
(6, ‘PHP’,‘2018-03’,7,‘4.010%’),
(7, ‘Ruby’,‘2018-03’,12,‘2.744%’),
(8, ‘Java’,‘2018-03’,1,‘14.941’),
(9, ‘Python’,‘2018-02’,4,‘5.168%’),
(10, ‘PHP’,‘2018-02’,7,‘3.420%’),
(11, ‘Ruby’,‘2018-02’,10,‘2.534%’),
(12, ‘Java’,‘2018-02’,1,‘14.988%’);

查询表数据tbl_language、tbl_rank
select * from tbl_language
select * from tbl_rank

inner join的使用
查询tbl_language和tbl_rank中name字段相同的所有记录
SELECT a.name , a.url , b.rate from tbl_language as a INNER join tbl_rank as b on a.name = b.name

left join的使用
下面的语句会读取 tbl_language 中所有的记录，然后查找 tbl_rank 中的 name 记录一一对应，如果没找到则空，如果找到多条，则重复多次
select a.name , a.url , b.rank from tbl_language as a left join tbl_rank as b on a.name = b.name

right join
下面的语句会读取 tbl_rank 中所有的记录，然后查找 tbl_language 中的 name 记录一一对应，如果没找到则空，如果找到多条，则重复多次
select a.name , a.url , b.rank from tbl_language as a RIGHT join tbl_rank as b on a.name = b.name

六、子查询的关联

创建数据表：users、orders
CREATE TABLE users (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
age INT
);

CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2)
);

插入数据：
– 插入users表测试数据
INSERT INTO users (user_id, user_name, age) VALUES (1, ‘张三’, 20);
INSERT INTO users (user_id, user_name, age) VALUES (2, ‘李四’, 25);

– 插入orders表测试数据
INSERT INTO orders (order_id, user_id, amount) VALUES (1, 1, 100.00);
INSERT INTO orders (order_id, user_id, amount) VALUES (2, 1, 200.00);
INSERT INTO orders (order_id, user_id, amount) VALUES (3, 2, 150.00);

1.根据用户ID将users表和orders表进行连接，并计算每个用户的订单总金额，并按照用户ID和用户姓名分组。
SELECT a.user_id , a.user_name ,SUM(b.amount) from users as a join orders as b on a.user_id = b.user_id GROUP BY a.user_id,a.user_name

七、字符截取函数

1.从左开始截取字符串
语法：select left(str,length)
示例：
SELECT LEFT(‘wwwsadajksldjlajsdl’,8)

2.从右开始截取字符串
语法：select right(str,length)
示例：
SELECT RIGHT(‘hjkasdhjkahskjdh’,6)

3.截取特定长度的字符串
语法：
select substring(str, pos)，即：substring(被截取字符串， 从第几位开始截取)
select substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)
示例：
SELECT SUBSTRING(‘wwwjashfdjklhkasj56546’, 9)
SELECT SUBSTRING(‘wwwjashfdjklhkasj56546’, 9,3)

4.从字符串的倒数第6个字符开始读取直至结束
SELECT SUBSTRING(‘wwwhkjashflk12313’, -6)

5.从字符串的倒数第6个字符开始读取，只取2个字符
SELECT SUBSTRING(‘wwwhkjashflk12313’, -6,2)

6.按照关键字进行提取：
语法：substring_index(str, delim, count)，
即：substring_index(被截取字符串，关键字，关键字出现的次数)
截取第二个“1”之前的所有字符
SELECT SUBSTRING_INDEX(‘www1asdasda1dasd’, ‘1’, 2);

7.截取倒数第二个“1”之后的所有字符
SELECT SUBSTRING_INDEX(‘www1asdasda1dasd’, ‘1’, -2);

8.如果关键字不存在，则返回整个字符串
SELECT SUBSTRING_INDEX(‘www1asdasda1dasd’, ‘p’, 1);

七、日期转换函数

1.时间转字符串
select date_format(now(), ‘%Y-%m-%d’);

2.时间转时间戳
select unix_timestamp(now());

3.字符串转时间
select str_to_date(‘2016-01-02’, ‘%Y-%m-%d %H’);

4.字符串转时间戳
select unix_timestamp(‘2016-01-02’);

5.时间戳转时间
select from_unixtime(1451997924);

6.时间戳转字符串
select from_unixtime(1451997924,‘%Y-%d’);

7.创建数据库：test，创建表：test.work_time1
create database if not exists test character set utf8;
drop table if exists test.work_time1;
create table test.work_time1 (
id int not null auto_increment comment ‘主键，表示记录数’
, user_id int not null comment ‘用户编号’
, user_type int not null comment ‘用户类型：1表示上班，2表示下班’
, create_time datetime not null comment ‘创建时间’
, primary key(id)
) engine=innodb default charset=utf8
;
insert into test.work_time1(
user_id
, user_type
, create_time
)
values(101, 1, ‘2020-01-05 09:00:00’)
, (101, 1, ‘2020-01-05 09:05:30’)
, (102, 1, ‘2020-01-05 09:15:02’)
, (101, 2, ‘2020-01-05 18:06:31’)
, (102, 2, ‘2020-01-05 18:30:31’)
, (102, 2, ‘2020-01-05 18:31:21’)
, (101, 1, ‘2020-01-06 08:45:22’)
, (101, 2, ‘2020-01-06 18:07:28’)
, (101, 1, ‘2020-01-07 09:30:02’)
, (101, 2, ‘2020-01-07 18:10:30’)
, (101, 2, ‘2020-01-07 18:15:21’)
;

id: 主键，仅表示记录数；
user_id: 员工id；
user_type: 员工操作类型，1表示上班，2表示下班；
create_time: 创建时间。

1.计算当日上班总时长
公式：下班时间-上班时间
上班时间的计算逻辑（if user_type = 1 then create_time），下班时间的计算逻辑（if user_type = 2 then create_time）。
– 员工101的上班总时长：27.1514小时
select ((unix_timestamp(‘2020-01-05 18:06:31’) - unix_timestamp(‘2020-01-05 09:00:00’))
+ (unix_timestamp(‘2020-01-06 18:07:28’) - unix_timestamp(‘2020-01-06 08:45:22’))
+ (unix_timestamp(‘2020-01-07 18:10:30’) - unix_timestamp(‘2020-01-07 09:30:02’))) / 3600
– 员工102的上班总时长：9.2581小时
select (unix_timestamp(‘2020-01-05 18:30:31’) - unix_timestamp(‘2020-01-05 09:15:02’)) / 3600

可以将组内最早的时间理解为：组内比该时间还早的时间数 < 1。
select id
, a.user_id
, a.user_type
, a.create_time
from test.work_time1 a
where 1 > (select count(*)
from test.work_time1 b
where b.user_id = a.user_id
and date(b.create_time) = date(a.create_time)
and b.user_type = a.user_type
and b.create_time < a.create_time)
;

– 计算每个员工的上班总时长
select t1.user_id
, sum(unix_timestamp(t2.create_time) - unix_timestamp(t1.create_time)) / 3600 work_time
from
(
select a.user_id
, a.user_type
, a.create_time
from test.work_time1 a
where 1 > (select count()
from test.work_time1 b
where b.user_id = a.user_id
and date(b.create_time) = date(a.create_time)
and b.user_type = a.user_type
and b.create_time < a.create_time)
) t1
left join
(
select a.user_id
, a.user_type
, a.create_time
from test.work_time1 a
where 1 > (select count()
from test.work_time1 b
where b.user_id = a.user_id
and date(b.create_time) = date(a.create_time)
and b.user_type = a.user_type
and b.create_time < a.create_time)
) t2 on t1.user_id = t2.user_id and date(t1.create_time) = date(t2.create_time)
and 1 = t2.user_type - t1.user_type
where t1.user_type = 1
group by t1.user_id
;

八、数据库的备份与恢复

导入：将sql直接导入对应数据库
mysql -u root -p 数据库名 < /home/mysql/study.sql;

导出数据库的结构和数据
mysqldump -u root -p 数据库名 > /home/mysql/study.sql

navicat
备份
选择数据库→选择转储sql→结构和数据→选择保存路径即可

恢复
选中数据库，导入sql文件即可

表名：students

九、其中数据视图与存储过程如何备份与恢复。

创建视图
CREATE VIEW <视图名> AS <SELECT语句>
示例：
create view students_view as select * from students

navicat中操作
1.备份视图
选择视图，选中需要备份的视图，选择逆向视图到模型，选择工具，然后选择导出SQl
2.恢复视图
选中数据库，导入sql文件即可

创建存储过程

DELIMITER $$
CREATE PROCEDURE user_procedure(IN sex VARCHAR(2) character set utf8,OUT num INT)
BEGIN
    SELECT  id FROM students WHERE gender=sex;
    SELECT FOUND_ROWS() INTO num;   
END $$
DELIMITER ;

navicat中操作
3.备份存储过程
工具→数据传输→选择文件→选择路径和命名。

4.恢复存储过程
选中数据库，导入sql文件即可

感谢您的观看，如有侵权，请私信删除！！！