前言
本文是数据库操作过程中纪录的笔记，包括安装、异常处理、基础操作等内容，基本涵盖了日常所需要的MySQL操作
基本操作
# 本地登录
mysql -uusername -ppasswd
# 查看当前连接所有的数据库
show databases;
# 进入数据库
use estore;
# 显示本数据库中所有的表
show tables;
安装--rpm安装方式
版本：mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar 下载链接
# 查看是否安装：
rpm -qa | grep mysql
# 卸载：
rpm -e xxx --nodeps
//使用rpm -ivh命令进行安装,按以下步骤安装，有依赖关系所以需要依次执行
rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64.rpm
# 依赖于common
rpm -ivh mysql-community-libs-5.7.9-20.el7.x86_64.rpm
# 依赖于libs
rpm -ivhmysql-community-client-5.7.20-1.el7.x86_64.rpm
# 依赖于client、common
rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm
初始化数据库,初始密码在/var/log/mysqld.log
service mysqld start
问题解决
新安装的MySQL启动不起来
cd /var/lib/mysql
chown mysql *；
chgrp mysql *；
chmod ug+rwx *
安装失败：
warning: mysql-community-server-5.7.17-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
libnuma.so.1()(64bit) is needed by mysql-community-server-5.7.17-1.el6.x86_64
libnuma.so.1(libnuma_1.1)(64bit) is needed by mysql-community-server-5.7.17-1.el6.x86_64
libnuma.so.1(libnuma_1.2)(64bit) is needed by mysql-community-server-5.7.17-1.el6.x86_64
解决方法：yum install numactl
centos 7 安装失败
报错信息：mariadb-libs is obsoleted by mysql-community-libs-5.7.9-1.el7.x86_64
解决方法：卸载mariadb-libs
首次安装设置root密码
# 使用默认密码登录
mysql –uroot -p
# 修改密码
set password =password('smile666.');
可以在mysql的log日志中查看默认密码(vim /var/log/mysqld.log 需要首先启动服务 service mysqld start)
允许远程登录
-
授权用户root使用密码passwd从任意主机连接到mysql服务器：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION; flush privileges; -
授权用户root使用密码passwd从指定ip为10.1.0.45的主机连接到mysql服务器：
代码如下:GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.1.0.45' IDENTIFIED BY 'passwd' WITH GRANT OPTION; flush privileges;
limit的使用（可用于分页操作）
# 取X个记录，从第Y行记录开始
limit X OFFSET Y
# 取Y个记录，从第X+1个记录开始
limit X,Y
中文乱码问题
# 查看字符编码
show variable like '%character%'
# 修改配置文件
[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8
# 代码连接 配置
jdbc:mysql://localhost:3306/crawler?useUnicode=true&characterEncoding=utf-8&useSSL=false
配置文件配置用户名密码
解决问题：脚本或者shell连接数据库时会出现警告，不建议直接在脚本或者命令行中使用密码，这个时候可以把用户名、密码写在配置文件里
host=localhost
user=root
password='Smile666'
数据库导出
# 导出整个数据库(包括数据）
mysqldump -uusername -p estore > estore.sql
# 导出数据库结构（只有建表语句，不含数据）
mysqldump -uusername -p -d estore > estore.sql
# 导出数据库中的某张数据表（包含数据）
mysqldump -uusername -p estore product > product.sql
# 导出数据库中的某张数据表的表结构（只有建表语句，不含数据）
mysqldump -uusername -p -d estore product > product.sql
数据库导入
# 方法一：Linux直接操作
mysql -uusername -p estore < estore.sql
# 方法二：进入mysql命令行，执行mysql命令
source estore.sql
emoji表情插入失败（数据库方式解决方案）
条件：
- mysql的版本必须为v5.5.3或更高
- 把数据库的编码改成utf8mb4 -- UTF-8 Unicode
- 然后需要存储emoji表情的字段选择
utf8mb4_general_ci - 数据库连接也需要改为utf8mb4
查看字符编码
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
修改my.cnf
[client]
default-character-set=utf8mb4
[mysqld]
character-set-server = utf8mb4
[mysql]
default-character-set = utf8mb4
修改数据库字符集
ALTER DATABASE estore CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
**修改表的字符集：**
ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
修改字段的字符集
ALTER TABLE user CHANGE nick_name nick_name VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
异常处理
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fulldata.api_weibo_data_cctv.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
vim /etc/my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
删除重复数据
//删除重复的数据，保留id最大的数据
delete a from test a, (select c.name,max(c.id) as bid from test c GROUP BY name having count(1) >1) b where a.name=b.name and a.id < b.bid;
//删除重复的数据，保留id最小的数据
delete a from test a, (select c.name,min(c.id) as bid from test c GROUP BY name having count(1) >1) b where a.name=b.name and a.id > b.bid;
//以下为创建临时表的方法
//删除重复数据，保留id最小的
delete from test where id not in (select * from ((select min(id) from test group by(name)) as tmptable));
//保留id最大的
delete from test where id not in (select * from ((select max(id) from test group by(name)) as tmptable));
密码复杂度
mysql> select @@validate_password_policy;
mysql> set global validate_password_policy=0;
不允许重复数据
ALTER TABLE job_scheduler ADD UNIQUE (task_id);
# 撤销
ALTER TABLE job_scheduler DROP INDEX task_id
代码捕获异常(jdbc，防止插入重复数据，需要首先设置UNIQU)
try{
SqlQuery.addTask(obj);
}catch (Exception e){
if (e instanceof SQLIntegrityConstraintViolationException){
System.out.println("任务已存在");
}
continue;
}
Spring捕获异常，注意Mybatis需要在mapper抛出异常throws DataAccessException
DuplicateKeyException
异常提示
Duplicate entry 'bfafd0cdd9a345ac8586133add60039c' for key 'task_id'
插入更新操作
意义是，如果不存在数据插入，存在数据的话执行update，需要首先设置UNIQUE
INSERT INTO unique_visitors_monthly(`timestamp`,unique_visitors) VALUES("2018-03-01",10) on DUPLICATE KEY UPDATE `timestamp` = "2018-03-01", unique_visitors = 44;
创建用户、授权
创建用户
# 创建用户并允许localhost, 127.0.0.1访问
create user 'test'@'localhost' identified by '123456';
# 创建用户并允许外网访问
create user 'test'@'%' identified by '123456';
# 生效
flush privileges;
对数据库授权
# 授权用户对testdb的所有权限，本地访问
grant all privileges on `testdb`.* to 'test'@'localhost' identified by '123456';
# 授权用户对testdb的所有权限，外网访问
grant all privileges on `testdb`.* to 'test'@'%' identified by '123456';
# 生效
flush privileges;
修改默认的数据文件目录
# 设置/data/下mysql文件夹的属主和权限
chown -R mysql:mysql /data/mysql
查看连接数
# 当前连接数
show status like 'Threads%';
# IP连接数
select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
# 查看连接状态
show full processlist;
# 查看最大连接数
show variables like '%max_connections%';
# 修改最大连接数
set GLOBAL max_connections = 200;
连接超时设置
# 查看当前超时设置
show variables like "%timeout%";
# 查看wait_timeout（全局变量）
show global variables like 'wait_timeout';
# 临时更改
set global wait_timeout=10;
配置文件配置--不建议使用默认的超时设置，太长了可能会出现很多的sleeping连接占用资源
[mysqld]
wait_timeout=10
表操作命令
# 查看表结构
desc table_name
# 修改字段的数据类型
ALTER TABLE table_name MODIFY COLUMN col_name VARCHAR(50);
# 字符替换
update tb_room set room_info = replace(room_info,'-','');
网友评论