mysql连接
1.创建连接
(1)语法
mysql.createConnection(options);
(2)参数options
常用参数如下：
host：主机地址 （默认：localhost）
user：用户名
password：密码
port：端口号 （默认：3306）
database：数据库名
还有一些不常用的，如multipleStatements，socketPath，charset，timezone，stringifyObjects，insecureAuth，typeCast，queryFormat，supportBigNumbers，bigNumbersStrings，debug等等
(3)返回值
返回一个mysql数据库与服务器之间连接的连接(connection)对象
- connection对象常用的方法
(1)connect(callback)
建立连接， callback为建立连接操作结束后的回调函数，参数为连接失败时触发的错误对象
(2)end(callback)
关闭连接, callback为关闭操作结束时的回调函数, 参数为关闭失败时触发的错误对象。destory也可以关闭连接，但是没有参数
(3)query(sql,[parameters],[callback])
执行增删改查的操作
sql：需要执行的sql表达式。必填，为字符串
parameters：存放sql参数所用到的所有参数的参数值。为对象或者数组
callback：执行操作结束后的回调函数。回调函数有两个参数err和results。 err 为执行操作失败时触发的错误对象，results为一个对象，代表sql操作的执行结果
(4)escape()
防止 SQL 注入。在写 SQL 语句的时间尽量不要使用 SQL 拼装，因为很容易被 SQL注入，从而引发安全问题，如果数据和 SQL 语句需要分离，那么请使用 占位符 的方式
var mysql = require('mysql');
//使用 createConnection 创建连接
var connection = mysql.createConnection({
host : 'localhost',
port : 3306,
database : 'test',
user : 'root',
password : '123456'
});
//连接数据库
connection.connect(function(err) {
if(err) {
console.log("连接失败");
}else{
console.log("连接成功");
}
});
//执行增删改查的操作
var sql = 'select * from user where username = "node"';
connection.query(sql, function (err, result) {
if(err) {
console.log("操作失败");
}else{
console.log(results);
}
})
//关闭连接
connection.end(function(err) {
if(err) {
console.log("关闭连接失败");
}else{
console.log("关闭连接成功");
}
});
//防止 SQL 注入
connection.query("select * from users where id = ? and name = ?", [1, 'jmjc'], (err, result)=>{}) // 这种方式 mysql 模块内部会调用 escape 方法，过滤掉一些非法的操作
connection.query('select * from users where id = ' + connection.escape(userId), (err, result) => {}) //自己使用 escape 方法
mysql连接池
建立一个数据库连接所消耗的性能成本是很高的。在服务器应用程序中，如果为每一个接收到的客户端请求都建立一个或多个数据库连接，将严重降低应用程序性能
因此在服务器应用程序中通常需要为多个数据库连接创建并维护一个连接池，当连接不再需要时，这些连接可以缓存在连接池中，当接收到下一个客户端请求时，从连接池中取出连接并重新利用，而不需要再重新建立连接
连接池可以直接使用，也可以共享一个连接或管理多个连接
- 创建连接池
(1)语法
mysql.createPool(options);
(2)参数options
参数options除了拥有createConntion的参数，还有如下参数：
waitForConnections：当连接池没有连接或超出最大限制时，设置为true且会把连接放入队列，设置为false会返回error
connectionLimit：连接数限制，默认：10
queueLimit：最大连接请求队列限制，设置为0表示不限制，默认：0
(3)返回值
返回一个mysql数据库与服务器之间连接的连接池(pool)对象
- pool对象的常用方法
(1)getConnection(callback)
从连接池中取出连接，如果无连接可用则隐式的建立一个数据库连接
callback 有两个参数 err 和 connection, err为取出连接或建立连接失败时的错误对象，connection为获取到的连接对象
connection连接对象与上面的connection连接对象拥有的方法差不多
connection.release()
将connection连接对象归还到连接池中，等待其它使用者使用
(2)on()
监听connection事件
(3)query()
直接使用连接池执行增删改查的操作
(4)end()
当一个连接池不再需要使用时, 关闭连接池
var mysql = require('mysql');
//创建连接池
var pool = mysql.createPool({
host : 'localhost',
port : 3306,
database : 'test',
user : 'root',
password : '123456'
});
//监听connection事件
pool.on('connection', function(connection) {
connection.query('SET SESSION auto_increment_increment=1');
});
//方法一：直接使用连接池
pool.query('SELECT 1 + 1 AS solution', function(err, results, fields) {
if (err) throw err;
console.log('The solution is: ', results[0].solution);
});
//方法二：从连接池中取出连接，共享一个连接或管理多个连接
pool.getConnection(function(err,connection) {
if(err) {
console.log("连接失败");
}else{
console.log("连接成功");
console.log(pool._allConnections.length);//1
connection.query('select * from user',function(err,results) {
if(err) {
console.log("查询失败");
}else{
console.log(results);
}
connection.release();
});
}
//pool.end();
})
使用mysql常见报错
1.Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'name' at row 1
原因：编码错误
参考文档
常用的sql语句
- 插入数据
1.1 INSERT 语句中指定所有字段名
语法：INSERT INTO 表名(字段名1，字段名2，…) VALUES(值1，值2，…);
举例：INSERT INTO student(id,name,grade) VALUES(1,'zhangshan',98);
1.2 INSERT 语句中不指定字段名
语法：INSERT INTO 表名 VALUES(值11，值2，…);
举例：INSERT INTO student VALUES (2,'lisi',62);
1.3 为表的指定字段添加数据
语法：INSERT INTO 表名 (字段1，字段2，…) VALUES(值1，值2，…）
举例：INSERT INTO student(id,name) VALUES(3,'wangwu');
1.4 INSERT语句的其他写法
语法：INSERT INTO 表名 SET 字段名1=值1[,字段名2=值2，…]
举例：INSERT INTO student SET id=4，name='zhaoliu',grade=72;
1.5 同时添加多条数据
语法：INSERT INTO 表名[(字段名1，字段名2，…)] VALUES (值1，值2，…),(值1，值2，…), … (值1，值2，…)
举例：INSERT INTO student VALUES (5，‘lilei’,99), (6,'hanmeimei',87), (8,'poly',76);
- 删除数据
2.1删除部分数据
即删除指定的部分数据，需要使用WHERE子句来指定删除记录的条件
语法：DELETE FROM 表名 WHERE 条件表达式
举例：DELETE FROM student WHERE id=7;
2.2删除全部数据
语法：DELETE FROM 表名
举例：DELETE FROM student；
2.3 删除全部数据的另一种方法——TRUNCATE
语法：TRUNCTE [TABLE ] 表名
举例：TRUNCATE TABLE student；即可删除student表
注意：
(1)DELETE 后面可以跟WHERE子句指定删除部分记录，TRUNCATE只能删除整个表的所有记录
(2)使用TRUNCATE语句删除记录后，新添加的记录时，自动增长字段（如本文中student表中的 id 字段）会默认从1开始，而使用DELETE删除记录后，新添加记录时，自动增长字段会从删除时该字段的的最大值加1开始计算（即原来的id最大为5，则会从6开始计算）。所以如果是想彻底删除一个表的记录而且不会影响到重新添加记录，最好使用TRUNCATE来删除整个表的记录
- 更新数据
语法：UPDATE 表名 SET 字段名1=值1，[ ，字段名2=值2，…] [ WHERE 条件表达式 ]
3.1 UPDATE 更新部分数据 举例：UPDATE student SET name=‘caocao’,grade=50 WHERE id=1;
3.2 UPDATE 更新全部数据
举例：UPDATE student SET grade=80；
4. 查询数据
4.1 查询对应字段的数据
查询出来的结果是按字段顺序排列的
语法：SELECT 字段名1，字段名2，··· FROM 表名
4.2 在SELECT语句中使用 * 通配符代替所有字段
查询指定数据表中的所有数据
语法：SELECT * FROM 表名
4.3 带关系运算符的查询
SELECT 字段名1，字段名2，··· FROM 表名 WHERE 条件表达式
举例：SELECT id，name FROM student2 WHERE id=4;
4.4 带 IN 关键字的查询
IN关键字用于判断某个字段的值是否在指定集合中，若在，则该字段所在的记录将会被查询出来
语法：SELECT 字段名1，字段名2，··· FROM 表名 WHERE 字段名 [ NOT ] IN （元素1，元素2，…）
举例：SELECT * FROM student2 WHERE id IN (1,2,3);
4.5 带 BETWEEN AND 关键字的查询
BETWEEN AND 用于判断某个字段的值是否在指定范围之内，若在，则该字段所在的记录会被查询出来，反之不会
语法：SELECT 字段名1，字段名2，… FROM 表名 WHERE 字段名 [ NOT ] BETWEEN 值1 AND 值2;
举例：SELECT id,name FROM students WHERE id BETWEEN 2 AND 5;
4.6 空值查询
在数据表中有些值可能为空值（NULL），空值不同于0，也不同于空字符串，需要使用 IS NULL 来判断字段的值是否为空值
语法：SELECT 字段名1，字段名2，··· FROM 表名 WHERE 字段名 IS [ NOT ] NULL
举例：SELECT * FROM student2 WHERE gender IS NULL;
4.7 带 DISTINCT 关键字的查询
很多表中某些字段的数据存在重复的值，可以使用DISTINCT关键字来过滤重复的值，只保留一个值
语法：SELECT DISTINCT 字段名 FROM 表名；
举例：SELECT DISTINCT gender FROM student2;
4.8 带 LIKE 关键字的查询
语法：SELECT 字段名1，字段名2，··· FROM 表名 WHERE 字段名 [ NOT ] LIKE ‘匹配字符串’;
(1)百分号（%）通配符
匹配任意长度的字符串，包括空字符串。例如，查询student2表中name字段以字符“s”开头的人的id,name
举例：SELECT id,name FROM student2 WHERE name LIKE "S%";
(2)下划线（_）通配符
下划线通配符只匹配单个字符，若要匹配多个字符，需要使用多个下划线通配符。例如，查询在student2表中name字段值以“ wu ”开始，以“ ong ”结束，并且中间只有一个字符的记录
举例：SELECT * FROM student2 WHERE name LIKE 'wu_ong';
注意：若要查询的字段值本来就含有“ % ”或者“ _ ”,则要用“ \ ”进行转义，如要查询本身含有“ % ”的字符串，命令应改为 “ %%%”
4.9 带 AND 关键字的多条件查询
语法：SELECT 字段名1，字段名2，… FROM 表名 WHERE 条件表达式1 AND 条件表达式2 [ … AND 条件表达式 n ];
如查询student2表中 id 字段小于5，并且 gender 字段值为“ 女 ”的人的id和name
举例：SELECT id,name FROM student2 WHERE id<5 AND gender='女';
参考文档：
Nodejs学习笔记（四）--- 与MySQL交互
Node.js在MySQL中存储数据 & 创建连接池
MySQL之增删改查
