mysql使用

注意mysql在输入命令时-u和-p与密码之间没有空格

SQL

对比两张表的数据

SELECT  'robot' AS `set`, r.*
FROM    robot r
WHERE   ROW(r.col1, r.col2, ) NOT IN
        (
        SELECT  *
        FROM    tbd_robot
        )
UNION ALL
SELECT  'tbd_robot' AS `set`, t.*
FROM    tbd_robot t
WHERE   ROW(t.col1, t.col2, ) NOT IN
        (
        SELECT  *
        FROM    robot
        )

参考:https://stackoverflow.com/questions/950414/compare-differences-between-two-tables-in-mysql

导出到csv

SELECT 
    *
FROM
    table_name 
INTO OUTFILE '/var/lib/mysql-files/filename.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '';

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement问题处理方法

  1. 通过SELECT @@secure_file_priv;查询到安全路径

  2. 将outfile输出到上述路径即可

DBA

安装启动相关

windows上注册服务 windows mysql service stsart

关闭mysql然后在命令行执行以下命令:

mysqld --install

如果提示:Install/Remove of the Service Denied!

表示权限不够,使用管理员身份打开命令行重新执行命令

windows-start-service

默认项

Using Option Files

window配置文件读取顺序:

File Name Purpose
%PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini, %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf Global options
%WINDIR%\my.ini, %WINDIR%\my.cnf Global options
C:\my.ini, C:\my.cnf Global options
BASEDIR\my.ini, BASEDIR\my.cnf Global options
defaults-extra-file The file specified with –defaults-extra-file, if any
%APPDATA%\MySQL.mylogin.cnf Login path options (clients only)

linux配置文件读取顺序:

File Name Purpose
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Global options
defaults-extra-file The file specified with –defaults-extra-file, if any
~/.my.cnf User-specific options
~/.mylogin.cnf User-specific login path options (clients only)

linux默认data目录:

/var/lib/mysql
/usr/local/mysql

pgp file(使用官方源安装时的校验文件)

5.7-checking-gpg

启动方法

data-ini

5.7启动的时候可以指定是否生成随机密码

--initialize 生成随机密码 --initialize-insecure 空密码

1.配置文件加载顺序

参考mysql配置文件的加载顺序

系统状态

1. show processlist

显示正在运行的线程

2. select version()

查看数据库版本

3. show table status

显示表空间,索引空间,表行数等信息

4. SHOW VARIABLES LIKE '%character%'

显示编码格式

系统管理

1. 修改root用户密码

当root密码为空时:mysqladmin -uroot -ppassword mypassword

当root密码不为空时:mysqladmin -uroot -pmypassword password mynewpassword

2. 关闭数据库服务

mysqladmin shutdown -uroot -proot 其中root/root是用户名密码

mysql Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp

解决方案:

jdbc的连接url增加参数zeroDateTimeBehavior

http://hostname:port/db?zeroDateTimeBehavior=convertToNull

数据库操作

创建数据库

–online

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

参考:创建数据库

创建用户

create user 'username'@'localhost' identified by 'user_password';
grant all on dbname.* TO 'username'@'localhost';