MySQL 连接

一、使用mysql二进制方式连接

在登录成功后会出现 mysql> 命令提示窗口，你可以在上面执行任何 SQL 语句

mysql -u root -p
Enter password:******

以上命令执行后，登录成功输出结果如下:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2854760 to server version: 5.0.9

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

退出 mysql> 命令提示窗口可以使用 exit 命令，如下所示：

mysql> exit
Bye

二、Node.js 连接 MySQL

问题描述？
Client does not support authentication protocol requested by server; consider upgrading MySQL client

解决办法如下：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你想设置的MySQL登录密码';

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : '主机地址',
  user     : '数据库登录用户',
  password : '数据库密码',
  database : '数据库名'
});
 
connection.connect();
 
// 数据计算
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

// 数据计算
connection.query('select 1 + 1 as solution', function (err, results, fields) {
   if (err) throw  err;
   console.log('The solution is: ', results[0].solution);
});

// 查
var  sql = 'SELECT * FROM mytest_acount';
connection.query(sql,function (err, result) {
    if(err){
        console.log('[SELECT ERROR] - ',err.message);
        return;
    }

    console.log('--------------------------SELECT----------------------------');
    console.log(result);
    console.log('------------------------------------------------------------\n\n');
});

// 插
var  addSql = 'INSERT INTO mytest_acount(col1,col2,col3) VALUES(0,?,?)';
var  addSqlParams = ['cf', new Date()];
connection.query(addSql,addSqlParams,function (err, result) {
    if(err){
        console.log('[INSERT ERROR] - ',err.message);
        return;
    }

    console.log('--------------------------INSERT----------------------------');
    //console.log('INSERT ID:',result.insertId);
    console.log('INSERT ID:',result);
    console.log('-----------------------------------------------------------------\n\n');
});

// 改
var modSql = 'UPDATE mytest_acount SET col2 = ? WHERE col1 = ?';
var modSqlParams = ['zz', 0];
connection.query(modSql,modSqlParams,function (err, result) {
    if(err){
        console.log('[UPDATE ERROR] - ',err.message);
        return;
    }
    console.log('--------------------------UPDATE----------------------------');
    console.log('UPDATE affectedRows',result.affectedRows);
    console.log('-----------------------------------------------------------------\n\n');
});

// 删
var delSql = 'DELETE FROM mytest_acount where col1=0';
connection.query(delSql,function (err, result) {
    if(err){
        console.log('[DELETE ERROR] - ',err.message);
        return;
    }

    console.log('--------------------------DELETE----------------------------');
    console.log('DELETE affectedRows',result.affectedRows);
    console.log('-----------------------------------------------------------------\n\n');
});

connection.end();