在阿里云ECS（centos）中安装mysql（笔记📒）

准备：

• 关闭服务器的防火墙systemctl stop firewalld
• 阿里云ECS安全组中开放mysql默认端口3306
• 详细配置，点这里
• 下载mysql官网地址：传送门。
• （在阿里云ECS中不需要提前下载安装包，直接使用命令进行下载安装）

普通账号：

用户名：jack

密码：12345Qsdbl--

管理员账号：

用户名：admin_qsdbl

密码：123Cyj--

安装

下载mysql的yum源（下载到/home/temp）

1

wget -P /home/temp http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

安装mysql的YUM源（mysql57，即5.7版本的）

1

rpm -ivh /home/temp/mysql57-community-release-el7-11.noarch.rpm

安装MySQL

1

yum install -y mysql-community-server

MacOS 安装mysql5.6.x后初始化密码，参考这篇博客。

1 2 3 4 5 6 7 8 9 10 11 12 13

第一步： 点击系统偏好设置->最下边点MySQL，在弹出页面中，关闭服务 第二步： 进入终端输入：cd /usr/local/mysql/bin/ 回车后 登录管理员权限 sudo su 回车后输入以下命令来禁止mysql验证功能 ./mysqld_safe --skip-grant-tables & 回车后mysql会自动重启（偏好设置中mysql的状态会变成running） 第三步： 输入命令 ./mysql 回车后，输入命令 FLUSH PRIVILEGES; 回车后，输入命令 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('你的新密码');

m1 mac mysql下载地址：http://mirrors.sohu.com/mysql/MySQL-5.6/

或者：https://dev.mysql.com/downloads/mysql/

进入mysql

默认配置文件路径：

• 配置文件：/etc/my.cnf
• 日志文件：/var/log//var/log/mysqld.log
• 服务启动脚本：/usr/lib/systemd/system/mysqld.service
• socket文件：/var/run/mysqld/mysqld.pid

注意：MySQL命令终止符为分号 ; 。

注意： -> 是换行符标识，不要复制。

启动mysql服务。start、stop、enable、status

1

systemctl start mysqld

进入mysql

1

mysql -uroot -p

初次进入需要修改一下密码（见下边的密码配置）。

扩展-Mac

mac本地安装的mysql，在terminal访问mysql，配置如下：（更多详细介绍参考这篇博客）

• 添加MySQL运行路径（环境变量）：PATH="$PATH":/usr/local/mysql/bin
  • 若要让环境变量永久失效，参考这篇博客进行配置。
• 登录mysql：$ mysql -u用户名 -p密码

Mac系统启动操作：

1、启动mysql

1

sudo /usr/local/mysql/support-files/mysql.server start

2、停止mysql

1

sudo /usr/local/mysql/support-files/mysql.server stop

3、重启mysql

1

sudo /usr/local/mysql/support-files/mysql.server restart

配置

密码

5.7之后会有初始密码，查看初始密码

1

grep 'temporary password' /var/log/mysqld.log

进入mysql

1

mysql -uroot -p

初次进入需要修改一下密码（我这里将root密码修改成了123Cyj--。)

1

alter user 'root'@'localhost' identified by '123Cyj--';

或

1

set password for 'root'@'localhost' = password('123Cyj--')

用户名：root

密码：123Cyj--

忘记了密码，可以参考这篇博客修改密码。（配置放在最后一行）

远程访问

端口

若不关闭防火墙，就需要放行mysql的3306端口（还是要配置阿里云ECS安全组）

1
2
3

firewall-cmd --permanent --zone=public --add-port=3306/tcp
firewall-cmd --permanent --zone=public --add-port=3306/udp
firewall-cmd --reload

查看已开放的端口

1

firewall-cmd --permanent --zone=public --list-ports

用户

创建一个普通用户，有增删改查的权限。（用户授权）

用户名：jack

密码：12345Qsdbl--

1
2
3
4
5

mysql> create user 'jack'@'%'identified by'127600Qsdbl--';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,update,delete on *.* to 'jack'@'%';
Query OK, 0 rows affected (0.00 sec)

修改密码（复习一下上边的命令）

1

set password for 'jack'@'%' = password('12345Qsdbl--')

再创建一个管理员账户，拥有所有权限，可以管理、备份、还原数据库。

用户名：admin_qsdbl

密码：123Cyj--

1
2
3
4
5

mysql> create user 'admin_qsdbl'@'%' identified by '123Cyj--';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'admin_qsdbl'@'%';
Query OK, 0 rows affected (0.00 sec)

下边的设置针对root用户，二选一。

注意%与localhost的区别。（前边设置都是本地可以访问，我这里设置了任意IP下可被访问）

设置用户 root 可以在任意 IP 下被访问

1

grant all privileges on *.* to root@"%" identified by "新密码";

设置用户 root 可以在本地被访问

1

grant all privileges on *.* to root@"localhost" identified by "新密码";

重要

使授权立刻生效

1

flush privileges;

设置字符集

查看字符集

进入mysql后，查看目前使用的字符集：show variables like 'character%';

database对应的字符集为latin1，为了避免中文乱码，我们需要修改为utf-8

1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

设置字符集

我们需要进入到mysql配置文件/etc/my.cnf中进行配置。（记得要重启一下mysql数据库）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

mysql> exit	//先退出mysql
Bye
[root@ali_server ~]# vim /etc/my.cnf	//使用vim编辑器打开配置文件

//添加下边四句语句（若配置文件中有[mysqld]则不写，其他的也是）
[mysqld]
character_set_server = utf8
[mysql]
default-character-set = utf8



[root@ali_server ~]# systemctl restart mysqld	//重启一下mysql服务器
[root@ali_server ~]# mysql -uroot -p	//进入mysql
Enter password: 123Cyj--
mysql> show variables like 'character%';	//查看一下字符集，我们可以看到已经都是utf8了
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |	//这个是二进制文件，不用理它
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

常用命令

摘自该博客。

用户相关

创建用户

（前边创建了两个账户，点这里查看）

1
2
3
4
5
6

格式：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
例如：CREATE USER 'chun'@'%' IDENTIFIED BY 'chun';

PS：username - 你将创建的用户名,
host - 指定该用户在哪个主机上可以登陆，此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录，如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录;也可以指定某台机器可以远程登录;
password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

给用户授权

1
2
3
4

命令:GRANT privileges ON databasename.tablename TO 'username'@'host'
例如：GRANT privileges ON *.* TO 'chun'@'%';

PS: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*.*

设置与更改密码

1
2

命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
例子: SET PASSWORD FOR 'chun'@'%' = PASSWORD("chun");

撤销用户权限

1
2
3
4
5
6
7

命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';
说明: privilege, databasename, tablename - 同授权部分.
例子: REVOKE SELECT ON mq.* FROM 'chun'@'localhost';

PS: 假如你在给用户'dog'@'localhost''授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'dog'@'localhost', 则在使用REVOKE SELECT ON *.* FROM 'dog'@'localhost';命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是GRANT SELECT ON *.* TO 'dog'@'localhost';则REVOKE SELECT ON test.user FROM 'dog'@'localhost';命令也不能撤销该用户对test数据库中user表的Select 权限.

具体信息可以用命令SHOW GRANTS FOR 'dog'@'localhost'; 查看.

删除用户

1

命令: DROP USER 'username'@'host';

查看用户的授权

1
2

mysql> show grants for dog@localhost;
PS:GRANT USAGE:mysql usage权限就是空权限，默认create user的权限，只能连库，啥也不能干

数据库–database

数据库创建

create database 数据库名；

数据库销毁

drop database 数据库名；

查看数据库

show databases;

代码演示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

创建：create database 数据库名；
例如：create database mytest;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| CARDGAME           |
| mysql              |
| mytest             |
| performance_schema |
| test               |
+--------------------+
销毁：drop database 数据库名；
例如：drop database mytest;
mysql> flush privileges;

表–table

在创建表之前，我们先了解一下数据库的数据类型。MySQL中定义数据字段的类型对数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

详细介绍可以查看菜鸟教程或这篇博客

存储多媒体数据，可以保存二进制文件或者文件路径，可以使用BLOB类型（典型的BLOB是一张图片或一个声音）。但是大文件不建议存放到数据库中，一般在数据库中保存多媒体文件的路径。

使用jdbc保存多媒体文件到mysql数据库，可以参考这篇两篇博客：博客1、博客2。

创建表

• 选择数据库，use 数据库名；

• 创建表，create table tb_dept( 列名1 数据类型 是否非空 是否设置为主键,。。。);（最后一个列参数后边不需要逗号）

• 表建好之后，编辑列：（参考博客：传送门）

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  新增： 添加新列（在最后）： alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(255) not null; 添加新列到某一列后面： alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(255) not null after COLUMN_NAME; 添加新列到第一列： alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(255) not null first; 编辑： 编辑列属性： ALTER TABLE table_name MODIFY COLUMN col_name 新属性。。。; 关于外键： 插入数据时，先插入主表中的数据，再插入从表中的数据。 删除数据时，先删除从表中的数据，再删除主表中的数据。

• 设置备注：comment '备注'（详细介绍见这篇博客）

• 外键设置：foreign key(当前表外键) references 目标表(目标表作为外键的列名)（参考这篇博客）

代码演示

下边以创建一个数据表为例，详细解释名词和用法。

数据库名为user，数据表的名为account。包含的列有id、user、passwd、type、time。保存用户的账号、密码、账号创建时间、数据上一次被修改的时间、账号类型（管理员、普通账号、已注销的账号）

• id，作为主键。插入数据时通过判断ID值，确定数据库中是否已存在该用户账号信息
• user，用户名
• passwd，密码
• type，账号类型
• time，账号创建时间
• timeup，记录数据上一次被修改的时间
• 数据表的编码类型为utf-8

详细介绍：

• 列名：id

  • id int unsigned auto_increment primary key
  • 数据类型为int
  • unsigned，无符号，非负数（只用于整型）
  • （int 与 unsigned，一起使用）
  • auto_increment，自增类型（插入数据时，不需要添加id列的值，会自动加1）
  • primary key，主键

• 列名：user

  • user varchar(10) not null unique
  • 数据类型为varchar，字符个数限制为最多10个
  • 非空，插入数据时该值必须得有
  • unique，唯一性约束

• 列名：passed

  • passwd varchar(20) default "123456"
  • 数据类型为varchar，字符个数限制为最多20个
  • 默认密码为123456

• 列名：type

  • type varchar(10) default "common"
  • 数据类型为varchar，字符个数限制为最多10个
  • 默认值为common，表示是普通用户
  • admin，管理员
  • common，普通用户
  • deprecated，废弃的（注销的账号）

• 列名：time

  • time datetime DEFAULT CURRENT_TIMESTAMP()
  • 类型，datetime，精确到秒
  • 默认值为插入数据时的时间（5.x版本不支持current_timestamp()，可以把类型datetime改成timestamp）

• 列名：timeup

  • timeup timestamp default current_timestamp on update current_timestamp
  • 类型，时间戳timestamp
  • 默认值为插入数据时的时间（current_timestamp）
  • 更新数据时，更新时间戳（该列的值）为当时的时间

ENGINE 设置存储引擎（默认，不写也行），CHARSET 设置编码。

1

create table 表名(。。。)engine=innodb default charset=utf8;

代码示例：

创建表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

mysql> use user;	//选择数据库	
Database changed

mysql> create table account(	//创建表
-> id int unsigned auto_increment primary key, 
-> user varchar(10) not null unique, 
-> passwd varchar(20) default "123456", 
-> type varchar(10) default "common", 
-> time datetime default current_timestamp, 
-> timeup timestamp default current_timestamp on update current_timestamp
-> )engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_qsdbl |
+-----------------+
| account         |
+-----------------+
1 row in set (0.00 sec)

查看一下建好的表：

1
2
3
4
5
6
7
8
9
10
11
12

mysql> desc account;
+--------+------------------+------+-----+-------------------+-----------------------------+
| Field  | Type             | Null | Key | Default           | Extra                       |
+--------+------------------+------+-----+-------------------+-----------------------------+
| id     | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| user   | varchar(10)      | NO   |     | NULL              |                             |
| passwd | varchar(20)      | YES  |     | 123456            |                             |
| type   | varchar(10)      | YES  |     | common            |                             |
| time   | datetime         | YES  |     | CURRENT_TIMESTAMP |                             |
| timeup | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

插入一条数据：（只添加了账号和密码，其他数据自动设置为默认值）

1
2
3
4
5
6
7
8
9
10

mysql> insert into account (user,passwd) values ("jack","127500");
Query OK, 1 row affected (0.01 sec)

mysql> select * from account;
+----+------+--------+--------+---------------------+---------------------+
| id | user | passwd | type   | time                | timeup              |
+----+------+--------+--------+---------------------+---------------------+
|  1 | jack | 127500 | common | 2020-10-27 17:42:40 | 2020-10-27 17:42:40 |
+----+------+--------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

修改一下数据（修改密码）：

1
2
3

mysql> update account set passwd="1234567890" where user="jack";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看一下数据：

1
2
3
4
5
6
7

mysql> select * from account;
+----+------+------------+--------+---------------------+---------------------+
| id | user | passwd     | type   | time                | timeup              |
+----+------+------------+--------+---------------------+---------------------+
|  1 | jack | 1234567890 | common | 2020-10-27 17:42:40 | 2020-10-27 17:43:51 |
+----+------+------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

我们可以看到，timeup自动更新了数据（时间）。

复制表

1
2
3
4
5
6
7

#复制表mytable4的表结构。新表名为mytable2
create table mytable2 as select * from mytable4 where 1=2

#复制表mytable4的表结构和表数据。新表名为mytable3
create table mytable3 as select * from mytable4

#不同点在于where条件。where 1=2，所以不会查询出表mytable4的数据

删除表

• 选择数据库，use 数据库名；

• 删除表，drop table 表名;

代码演示：

1
2
3
4
5
6
7
8
9
10
11
12

创建表：
  use 数据库名；
  create table tb_dept( Id int primary key auto_increment, Name varchar(18),description varchar(100));	
  mysql> show tables;
  +-------------------+
  | Tables_in_mystest |
  +-------------------+
  | tb_dept           |
  +-------------------+
  1 row in set (0.00 sec)
删除表：drop table 表名;
    例如：drop table tb_dept;

视图–views

介绍

• 视图可以理解为一张虚拟表，不存储数据，实际的数据来源可以来源自多张表

• 创建视图：

  • create view 视图名 as 查询语句。。。;

• 使用视图（和使用表相同）：

  • select * from 视图名;

• 删除视图：

  • drop view 视图名;

• 查看视图：

  1 2	use information_schema; select * from views;

• 视图的注意事项：

  1. 视图中可以使用多个表
  2. 一个视图可以嵌套另一个视图
  3. 对视图数据进行添加、更新和删除操作直接影响所引用表中的数据。但视图数据来自多个表时，不允许添加和4删除数据
  4. 使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

#查询 上周销量最好的10款菜品
use cashier_system;
select a.name 菜品名,a.type 类型,a.price 单价,a.unit 单位,sum(b.num) 销售量
from goods a,order_goods b,order_table c
where a.id = b.goods_id
  and b.order_id = c.id
  and date_format(c.order_time,'%Y-%m-%d') >= DATE_SUB(date_format(now(),'%Y-%m-%d'),INTERVAL 7 DAY)
group by b.goods_id
order by sum(b.num) desc
limit 10;


#创建视图 best_selling_last_10
#create view 视图名 as 查询语句。。。;
use cashier_system;
create view best_selling_last_10 as
select a.name 菜品名,a.type 类型,a.price 单价,a.unit 单位,sum(b.num) 销售量
from goods a,order_goods b,order_table c
where a.id = b.goods_id
  and b.order_id = c.id
  and date_format(c.order_time,'%Y-%m-%d') >= DATE_SUB(date_format(now(),'%Y-%m-%d'),INTERVAL 7 DAY)
group by b.goods_id
order by sum(b.num) desc
limit 10;

#使用 视图
use cashier_system;
select * from best_selling_last_10;

#删除 视图
use cashier_system;
drop view best_selling_last_10;

#查看所有视图
use information_schema;
select * from views;

数据的增删改查

增insert

1
2
3

insert into 表名 ( 列名1, 列名2,... ) vlaues ( value1, value2,...valueN );

不是要求非空，或有默认值的列，可以不把列名写出来。

如果数据是字符型，必须使用单引号或者双引号，如：”value”。

插入多条数据

1

insert into 表名  (field1, field2,...fieldN)  values  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;

删delete

where后边为匹配条件。（多个条件用and连接）

delete from 表名 where 列='值';

改update

where后边为匹配条件。

update 表名 set 列 = 更新的数据 where 列='值';

• 多个条件：(列名1,列名2,列名3) = (值1，值2，值3)

查select&show

• 查看MySQL中的数据库

• show databases;

• 显示数据库中的表

• show tables;

• 查看表信息

• desc 表名;

• 查看表中的数据

  • select * from 表名;

  • 联表查询：参考这篇博客。

    1 2 3 4 5 6

    查询student表的学生id、姓名，teacher表的老师姓名。where条件是根据学生表的外键tid匹配老师表的主键id SQL语句如下： select student.id,student.name,teacher.name from student,teacher where student.tid = teacher.id; 给表student、teacher取别名： select s.id,s.name,t.name from student as s,teacher as t where s.tid = t.id;

  • 子查询：参考这篇博客。

    1 2 3

    查询学生小红的班主任名字。先查询学生表的外键tid，再根据tid到老师表中去查询老师的姓名。 SQL语句如下： select name from teacher where id in (select tid from student where name = '小红')

  • 高级查询，更多介绍查看这篇博客。

高级查询：

• 判断是否存在某个用户名

  • select count(*) from 表名 where username字段 ='用户名'

• 判断账号密码是否正确

  • select count(*) from 表名 where user='输入的用户名' && passwd='输入的密码';

• 前边的查询，通过判断返回值是否大于0，即可得出结果

更多命令，点这里。

索引

介绍

• 提升查询效率，其他的增删改操作的效率会降低

• 查询数据时，有索引项作为条件时才会使用到索引

• MySQL的索引类型：

  • 普通索引：经常使用
  • 唯一索引：当创建unique唯一性约束的时候，数据库会自动创建，没有重复项，可以有一个null
  • 主键索引：创建primary key主键的时候，会自动创建，没有重复项，没有null
  • 组合索引：同时创建在多列上
  • 全文索引：全数据库
  • 空间索引：全磁盘空间

• 创建索引：

  1 2 3 4 5 6

  #[] 可选（可有可无） #unique - 唯一索引 #fulltext - 全文索引 #spatial - 空间索引 #以上三者都不使用，则为普通索引。一个索引名（自定义）对应多个列名，为组合索引。 create [unique | fulltext | spatial] index 索引名 on 表名(列名[,列名,列名...]);

• 查看索引：

  1 2	#查看某张表中的索引 show index from 表名;

• 删除索引：

  1 2	drop Index 索引名 on 表。 #删除表时，该表的所有索引同时会被删除

• 建议创建索引的列：

  • 频繁搜索的列
  • 经常用作查询选择的列
  • 经常排序、分组的列（order by；group by）
  • 经常用作连接的列（主键/外键）

• 不建议创建索引的列：

  • 仅包含几个不同值得列（数据大部分相同，例如：性别列）
  • 表中仅包含几行（数据量少）

• 经验（索引优化）：

  • 查询时减少使用*返回全部列，不要返回不需要的列
  • 索引应该尽量小，在字节数小的列上建立索引
  • where子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
  • 避免在order by（排序）子句中使用表达式（将不会使用索引）

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

#创建索引
use cashier_system;
create index i_index on account(sex);#普通索引
create index i_index02 on account(birthday,tel);#组合索引
create fulltext index i_index03 on account(mail);#全文索引

#查看索引
show index from account;

#使用：
#1.下列情况，查询数据时没有使用索引
select * from account;
select * from account where state = "正常" ;
#2.使用索引（有索引项作为条件时）
select * from account where sex = "男";

#删除索引
drop index i_index on account;
drop index i_index02 on account;

案例

案例一

案例二：统计

订单日期分布

对时间进行分组（group by），并总数作为订单数量。得到 订单日期分布 数据。

1
2
3
4

select date_format(createtime,'%Y-%m-%d') as time,count(*) as orders 
from myorder 
group by date_format(createtime,'%Y-%m-%d') 
order by date_format(createtime,'%Y-%m-%d');

营业额日期分布

对时间进行分组（group by），在此基础上（with rollup）统计money的总和。得到 营业额日期分布 数据。

• date_format()函数，将时间转换成指定的格式。

  • 通过改变格式，实现年、月、日、小时的数据查询

• order by，升序排序（从小到大）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

//年-月-日
select date_format(createtime,'%Y-%m-%d') as time,sum(money) as money 
from myorder 
group by date_format(createtime,'%Y-%m-%d') 
with rollup 
order by date_format(createtime,'%Y-%m-%d');

//年-月
select date_format(createtime,'%Y-%m') as time,sum(money) as money 
from myorder 
group by date_format(createtime,'%Y-%m') 
with rollup 
order by date_format(createtime,'%Y-%m');

//年
select date_format(createtime,'%Y') as time,sum(money) as money 
from myorder 
group by date_format(createtime,'%Y') 
with rollup 
order by date_format(createtime,'%Y');

//年-月-日 小时
select date_format(createtime,'%Y-%m-%d %H') as time,sum(money) as money 
from myorder 
group by date_format(createtime,'%Y-%m-%d %H') 
with rollup 
order by date_format(createtime,'%Y-%m-%d %H');

过去一周的 营业额日期分布

• NOW()函数，获取当前的日期和时间。

• DATE_SUB()函数，从日期减去指定的时间间隔。

• 例如：从 “OrderDate” 减去一周。

  • DATE_SUB(OrderDate,INTERVAL 7 DAY)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    
    查询**过去一周**的 营业额日期分布 数据：
    
    ```sql
    select date_format(createtime,'%Y-%m-%d') as time,sum(money) as money 
    from myorder  
    where date_format(createtime,'%Y-%m-%d') >= DATE_SUB(date_format(now(),'%Y-%m-%d'),INTERVAL 7 DAY)  
    group by date_format(createtime,'%Y-%m-%d') 
    with rollup 
    order by date_format(createtime,'%Y-%m-%d');
    
    
    
    

过去一周的 订单日期分布

1
2
3
4
5
6

select date_format(createtime,'%Y-%m-%d') as time,count(*) as orders 
from myorder  
where date_format(createtime,'%Y-%m-%d') >= DATE_SUB(date_format(now(),'%Y-%m-%d'),INTERVAL 7 DAY)  
group by date_format(createtime,'%Y-%m-%d') 
with rollup 
order by date_format(createtime,'%Y-%m-%d');

当天 营业额时刻分布

• select date_format(createtime,'%H') as time,//createtime字段的数据转换成两位数的小时
• sum(money) as money//对money字段的数据 求和
• from myorder//查询myorder表
• where
• date_format(createtime,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')/当天的时间
• and
• date_format(createtime,'%H') <= '21'//小于晚上21点
• and
• date_format(createtime,'%H') >= '09'//大于早上09点
• group by
• date_format(createtime,'%H')//对createtime字段进行分组，将其转换成小时，即以小时为一组
• with rollup//在分组的基础上进行统计、求和等操作
• order by
• date_format(createtime,'%H');//升序排序（从小到大）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

//09-21点之间 的数据
select date_format(createtime,'%H') as time,sum(money) as money 
from myorder 
where 
date_format(createtime,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d') 
and
date_format(createtime,'%H') <= '21' 
and
date_format(createtime,'%H') >= '09'
group by 
date_format(createtime,'%H') with rollup 
order by 
date_format(createtime,'%H');

//09 到 查询时刻 的数据
select date_format(createtime,'%H') as time,sum(money) as money 
from myorder 
where 
date_format(createtime,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d') 
and
date_format(createtime,'%H') <= date_format(now(),'%H') 
and
date_format(createtime,'%H') >= '09'
group by 
date_format(createtime,'%H') with rollup 
order 
by date_format(createtime,'%H');

当天 订单时刻分布

• 与上边，区别在于将求和sum(money) as money改成了count(*) as orders计数。
• count(*)，统计数据条数即订单数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

//09-21点之间 的数据
select date_format(createtime,'%H') as time,count(*) as orders 
from myorder 
where 
date_format(createtime,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d') 
and
date_format(createtime,'%H') <= '21' 
and
date_format(createtime,'%H') >= '09'
group by 
date_format(createtime,'%H') with rollup 
order 
by date_format(createtime,'%H');


//09 到 查询时刻 的数据
select date_format(createtime,'%H') as time,count(*) as orders 
from myorder 
where 
date_format(createtime,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d') 
and
date_format(createtime,'%H') <= date_format(now(),'%H') 
and
date_format(createtime,'%H') >= '09'
group by 
date_format(createtime,'%H') with rollup 
order 
by date_format(createtime,'%H');

上周销量最好的菜品

1
2
3
4
5
6
7
8
9
10

#查询 上周销量最好的10款菜品
use cashier_system;
select a.name 菜品名,a.type 类型,a.price 单价,a.unit 单位,sum(b.num) 销售量
from goods a,order_goods b,order_table c
where a.id = b.goods_id
  and b.order_id = c.id
  and date_format(c.order_time,'%Y-%m-%d') >= DATE_SUB(date_format(now(),'%Y-%m-%d'),INTERVAL 7 DAY)
group by b.goods_id
order by sum(b.num) desc
limit 10;

根据类型统计当日销量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

#根据菜品类型 统计当日销售量
select a.type 类型,date_format(c.order_time,'%Y-%m-%d') 时间,sum(b.num) 销售量
from goods a,order_goods b,order_table c
where a.id = b.goods_id
  and b.order_id = c.id
  and date(c.order_time) = (
      select date(max(order_table.order_time)) from order_table
    )
group by a.type
order by sum(b.num) desc;

#第二种写法
select a.type 类型,date_format(c.order_time,'%Y-%m-%d') 时间,sum(b.num) 销售量
from goods a,order_goods b,order_table c
where a.id = b.goods_id
  and b.order_id = c.id
  and date_format(c.order_time,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')
group by a.type
order by sum(b.num) desc;

获取最新的订单

根据传递的num，获取最新的n条订单：

1
2
3
4
5
6
7
8

select o.*,g.*,g.price gprice, o.price oprice, o.id oid,g.id gid,og.num count
from order_table o,goods g,order_goods og
where o.id = og.order_id
  and g.id = og.goods_id
  and o.id in (select t.id from (select id from order_table order by order_time desc LIMIT #{num})as t)
                                 
#注意：
select id from order_table order by order_time desc LIMIT #{num} 不能直接作为o.id in ()的参数使用，会报错。可以再套一层select解决。

问题

连接数

MySQL默认的最大连接数过低，导致部署的项目再对数据库进行增删改查操作时出现Too many connections异常。参考资料：传送门。

解决方法：1、修改MySQL配置文件/etc/my.cnf,设置成max_connections=1000,wait_timeout=5（在[mysqld] 中）。如果没有此项设置可以自行添加，修改后重启MySQL服务即可。2、临时解决方法，在mysql命令行中输入如下命令进行配置：

1
2

show variables like 'max_connections'; //查看最大连接数
set GLOBAL max_connections=1000; //设置

安全检查

mysql出现which is not functionally dependent on columns in GROUP BY clause报错（一般出现在mysql8.x版本）

笔记摘自：https://blog.csdn.net/qq_33621326/article/details/95950264

1
2

-- 执行以下sql命令：
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

时区

时区问题：设置时区位UTC，?serverTimezone=UTC（Java项目中）

select

where条件中使用not in时，要注意空值（null）的情况（select查询not in的条件时，避免查出空值。应添加is not null）

执行sql脚本

执行sql脚本文件：

1
2
3
4
5
6
7
8
9
10
11
12
13

Linux：
进入 MySQL 控制台:

mysql –u用户名 –p密码

执行sql脚本文件：

source 【sql脚本文件的路径全名】 或 . 【sql脚本文件的路径全名】

Windows：
cmd下执行下边命令：
mysql –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】
（–D数据库，可不要）

扩展

IDEA连接数据库

1.在侧边展开“数据库”面板。依次点击“+按钮 - 数据源 - MySQL”。

2.在弹出的“数据源和驱动程序”窗口中，配置用户名、密码、主机地址、连接名称、架构等。可参考下图配置。

注意：不填写数据库名，可实现一个数据库连接访问多个数据库，只需在“架构”页面勾选要显示的数据库即可。

3.数据库连接好之后，效果如下图。

IDEA导出数据

1.使用IDEA连接数据库。

2.选中数据库，右键选择“用’mysqldump’导出”。

3.打开“用’mysqldump’导出”面板后，简单配置（可参考下图）后点击“运行”按钮导出数据到”.sql”文件中。

4.导出的”.sql”文件如下图：

IDEA导入数据

1.使用IDEA连接数据库。

2.选中要恢复数据的目标数据库，右键选择“运行SQL脚本”。

3.“运行SQL脚本”，选择“.sql”后缀的SQL脚本，恢复数据。（下图选择了上边导出的.sql文件）