1.MySQL
升级包
sudo apt-get update
安装mysql-client-5.6
sudo apt-get install mysql-client-core-5.6
sudo apt-get install mysql-client-5.6
安装 mysql-server-5.6
sudo apt-get install mysql-server-5.6
检查mysql是否启动
进程是否启动
ps -ef | grep mysql
mysql 监听窗口
netstat -tap | grep mysql
msyql服务停止
sudo service mysql stop
Mysql 服务启动
sudo service mysql start
Mysql 服务远程访问
vim /etc/mysql/my.cnf
注释 bind address = 127.0.0.1
重启msyql
sudo service mysql restart
/etc/init.d/mysql restart
登录MySQL
sudo mysql -u root -p;
输入密码
help
mysql --help
授权非本机用户登陆数据库
登录
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;
grant all privileges on tppamltest3.* to 用户名@localhost identified by '密码';
. : 所有的表
root : 授权给谁
% : 所有ip和域名
root : 密码
刷新设置
FLUSH PRIVILEGES;
Mysql 服务端的编码方式修改
登录
查看编码方式
status
修改配置文件
vim /etc/mysql/my.cnf
在[client]下追加 default-character-set = utf8
在[mysqlId]下追加 character-set-server=utf8
在[mysql]下追加 default-character-set = utf8
保存重启
sudo server mysql restart
基本操作
查看所有数据库
show databases;
切换库
use database name;
查看所有表
show tables;
查看表结构
desc func;
describe func;
show columns from func;
explain func;
使用mysql的工具mysqlshow.exe
mysqlshow databaseName tableName
建库
create database databaseName;
use databaseName;
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for cfg_auto_mend
-- ----------------------------
DROP TABLE IF EXISTS `cfg_auto_mend`;
建表指定字符编码
CREATE TABLE `cfg_auto_mend` (
`ID` varchar(32) NOT NULL,
`MEND_SQL` text,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
新添用户
CREATE USER 'demo'@'localhost' IDENTIFIED BY 'demo';
赋予用户权限
GRANT privileges ON databasename.tablename TO 'demo'@'localhost';
GRANT ALL ON *.* TO 'demo'@'localhost';
查看数据库编码:
mysql> show create database db_name;
修改数据库编码:
mysql> ALTER DATABASE db_name ####这里修改整个数据库的编码
CHARACTER SET utf8
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci
DEFAULT COLLATE utf8_general_ci;
在在建数据库的时候指定编码:
mysql> CREATE DATABASE db_name
CHARACTER SET utf8
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci
DEFAULT COLLATE utf8_general_ci ;
查看数据库表和字段编码
mysql> show create table table_name;
> ALTER TABLE table_name DEFAULT CHARACTER SET utf8;
修改字段编码
该命令就是将MYSQL数据库table_name表中 dd的字段编码改为utf8
mysql> ALTER TABLE `table_name` CHANGE `dd` `dd` VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
命令行下插入汉字时指定编码
mysql> set names utf8;
mysql> insert into test(name) values('王东伟');
如果是通过JDBC 连接数据库,可以这样写URL:
URL=jdbc:mysql://localhost:3306/abs?useUnicode=true&characterEncoding=字符集