按步骤完成一个 spring boot 项目 (一) 数据库创建

按步骤完成一个 spring boot 项目 (一) 数据库创建

百度云盘:

链接: https://pan.baidu.com/s/11lURu0nMOepXJluwpjTkpA 提取码: en4w

安装脚本:

进入 mysql解压目录 /bin 目录中
按照下面的代码中执行 ./mysqld -install
后面会提示临时密码: !OfS1jIL3+e.
重要的事情说三遍:
!OfS1jIL3+e(随机的,下面会用到)
!OfS1jIL3+e(随机的,下面会用到)
!OfS1jIL3+e(随机的,下面会用到)
!OfS1jIL3+e(随机的,下面会用到)

./mysqld --initialize --user=mysql --console
./mysqld -install
net start mysql
mysql -uroot -p!OfS1jIL3+e.      #这里就是临时的密码  
set password = 'qhdswchzh';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'qhdswchzh';
FLUSH PRIVILEGES;                #刷新权限

常见提示错误:

问题1: mysql Install/Remove of the Service Denied!
解决办法: cmd 管理员运行.

问题2:Navicat for MySQL 连接 Mysql 8.0.11 出现1251- Client does not support authentication protocol 错误

解决办法: 修改mysql 默认 密码规则

系统根目录增加:

my.ini

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
basedir = "G:\mysql-8.0.16-winx64"
datadir = "G:\mysql-8.0.16-winx64\data\"
port = 3306
max_connections=1000
max_user_connections=500
wait_timeout=200
character-set-server=utf8
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
#character-set-server=UTF-8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
#忘记密码时使用
#skip-grant-tables
#设置协议认证方式(重点啊)
default_authentication_plugin=mysql_native_password
[mysql]
default-character-set=utf8

mysql 优化:

连接池修改:

jdbc.url=jdbc:mysql://localhost:3306/workflow?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL
jdbc.driver=com.mysql.cj.jdbc.Driver

pom.xml 修改:

	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>8.0.11</version>
	</dependency>

https://blog.csdn.net/yulizi0215/article/details/103819578

1 创建数据库：
数据库类型： mysql
数据库名称： spring_boot_database
数据库编码： utf-8
数据库排序规则： utf8_general_ci

2 创建表系统配置表：
CREATE TABLE sys_config (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
value varchar(100) NOT NULL COMMENT ‘数据值’,
label varchar(100) NOT NULL COMMENT ‘标签名’,
type varchar(100) NOT NULL COMMENT ‘类型’,
description varchar(100) NOT NULL COMMENT ‘描述’,h
sort decimal(10,0) NOT NULL COMMENT ‘排序（升序）’,
create_by varchar(50) DEFAULT NULL COMMENT ‘创建人’,
create_time datetime DEFAULT NULL COMMENT ‘创建时间’,
last_update_by varchar(50) DEFAULT NULL COMMENT ‘更新人’,
last_update_time datetime DEFAULT NULL COMMENT ‘更新时间’,
remarks varchar(255) DEFAULT NULL COMMENT ‘备注信息’,
del_flag tinyint(4) DEFAULT ‘0’ COMMENT ‘是否删除 -1：已删除 0：正常’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT=‘系统配置表’;

INSERT INTO sys_config VALUES (‘1’, ‘#14889A’, ‘theme’, ‘color’, ‘主题色’, ‘0’, ‘admin’, ‘2018-09-23 19:52:54’, null, null, ‘主题色’, ‘0’);

二 : 必须会使用
insert
INSERT INTO spring_boot_database.sys_config(id, value, label, type, description, sort, create_by, create_time, last_update_by, last_update_time, remarks, del_flag) VALUES (1, ‘#14889A’, ‘theme’, ‘color’, ‘主题色’, 0, ‘admin’, ‘2018-09-23 19:52:54’, NULL, NULL, ‘主题色’, 0);
INSERT INTO spring_boot_database.sys_config(id, value, label, type, description, sort, create_by, create_time, last_update_by, last_update_time, remarks, del_flag) VALUES (2, ‘#14889A’, ‘theme’, ‘color’, ‘主题色’, 0, ‘admin’, ‘2018-09-23 19:52:54’, NULL, NULL, ‘主题色’, 0);
update
UPDATE sys_config SET VALUE = ‘#FFFFFF’ WHERE ID = ‘1’ ;
delete
DELETE FROM sys_config WHERE ID = ‘1’ ;
left join
SELECT * FROM SYS_CONFIG A LEFT JOIN SYS_CONFIG b ON A.ID = B.ID;
right join
SELECT * FROM SYS_CONFIG A RIGHT JOIN SYS_CONFIG b ON A.ID = B.ID;
inner join
SELECT * FROM SYS_CONFIG A INNER JOIN SYS_CONFIG b ON A.ID = B.ID;
distinct
SELECT distinct id FROM SYS_CONFIG ;
max()
SELECT max(id) FROM SYS_CONFIG ;
order by desc /asc
SELECT * FROM SYS_CONFIG ORDER BY ID DESC ;
group by
SELECT * FROM SYS_CONFIG GROUP BY type DESC ;
limit 0,1
SELECT * FROM SYS_CONFIG GROUP BY type DESC LIMIT 0,10 ;
创建索引

设置主键

主键自增