使用sql简单操作Mysql数据库

SQL基本语句

id	username	password	status
1	test	123456	0
2	admin	ABC123	0
2	admin1	000000	1

-- 双中划线代表注释

-- 查询my_db下user_info下的所有数据，不区分大小写
-- select * from 要查询的数据库表;
-- * 代表所有
-- SELECT * FROM my_db.user_info;
select* from my_db.user_info;

-- 向指定的表中，插入新数据，通过values指定
-- insert into 数据库表 (列1，列2, ....) values (值1, 值2, ...);
-- 列和值要一一对应，多个列、多个值之间要用英文逗号分割
INSERT INTO my_db.user_info (username, password) VALUES ('admin1', 123456);

-- 更新指定数据,多个要用英文逗号隔开
-- update 数据库表 set 要更新的列名称1 = 新值, 要更新的列名称2 = 新值 where 列值 = 某值
-- 其中 （where 列值 = 某值）是筛选条件
-- UPDATE my_db.user_info SET password = 'ABC123' WHERE id = 2;
UPDATE my_db.user_info SET username = 'domain', password = 'ABC123' WHERE id = 3;

-- 删除某行数据
-- delete form 数据库表 where 条件
DELETE FROM my_db.user_info WHERE id = 3;

-- where 语句： where 列 运算符 值
-- =:等; <>:不等; >:大于；<:小于; >=:大于等于; <=:小于等于; between:在某个范围; link:在某种搜索模式
-- 查询id不为2的
select* from my_db.user_inf where id<>2; 
-- and和or可以在where中将多个条件结合：where id= 2 or id= 5;

-- 排序 order by 列名 ASC|DESC; 不写默认为ASC, ASC升序，DESC降序
select * from my_db.user_info ORDER BY id ASC;
-- 先按照username升序，再按照status降序,多重排序asc不可省略
select * from my_db.user_info order by username asc, status desc;
-- 自定义 排序
select * from my_db.user_info ORDER BY
case username
	when 'test' then 2
    when 'domain' then 1
    when 'admin' then 3
    end
 asc;

-- count(*)产看返回数据的总条数
select count(*) from my_db.user_info where id > 1;
-- as 给查出的列设置别名
select count(*) as total from my_db.user_info where id > 1;

-- select* from my_db.user_info;

as 设置别名

项目中操作mysql

安装sql

npm install sql

连接数据库

// 配置mysql
// 导入mysql
const mysql = require('mysql');

// 建立与MySQL数据库的链接
const db = mysql.createPool({
    host: '', // 数据库ip地址
    user: '', // 登录的数据库账号
    password: '', // 登录数据库的密码
    database: '', // 指定要操作哪个数据库，
    // 是否可执行多条语句
    // multipleStatements: true/false,
});

// select 1:无实际意义，只用来检测
db.query('select 1', (error, result) => {
    if (error) {
        return console.log("数据库链接错误：", error.message);
    }
    // 只要打印出[ RowDataPacket { '1': 1 } ]，就证明连接成功
    console.log('结果：', result)
});

如果出现错误： ER_HOST_NOT_PRIVILEGED: Host 'LAPTOP-IGQA52K8' is not allowed to connect to this MySQL server

-- 登录数据库
mysql -u root -p
-- 使用mysql数据库
use mysql
-- 将root的localhost改为%，谁都可以连接
update user set host = '%' where user = 'root'
-- 刷新生效
flush privileges;
-- exit; 退出mysql
-- 重新打开新窗口测试

查询数据

// 查询user_info数据表所有数据
const sqlStr = 'select * from user_info';
// db.query('SQL语句', (错误, 结果) => {})
db.query(sqlStr, (error, result) => {
    if (error) {
        return console.log('报错：', error.message);
    }
    console.log('结果：', result);
});

插入数据

逐个插入

// 插入数据
const user = { username: 'usre1', password: '123456' };
// 一个英文?对应一个字段的占位符
const intoStr = 'insert into user_info (username, password) values (?, ?)';
// 使用数组形式，依次为?占位符的值，只有一个?时，可不写为数组直接为数值即可
db.query(插入值SQL, [值1, 值2, ...], (error, result) => {});
 db.query(插入值SQL, 值, (error, result) => {});
db.query(intoStr, [user.username, user.password], (error, result) => {
    if (error) {
        return console.log('报错：', error.message);
    }
    if (result.affectedRows === 1) {
        console.log('结果：', result);
    }
    console.log('成功');
});

快捷一次插入

// 插入数据的快捷方式
const user = { username: 'user2', password: '000000' };
const userStr = 'insert into user_info set ?';
// 插入值的key一定不要查出数据表的列
//  db.query(插入值SQL, 值, (error, result) => {});
db.query(userStr, user, (error, result) => {
    if (error) {
        return console.log('报错：', error.message);
    }

    console.log('成功，', result);
});

批量插入

const intoList = [
    ['name1', 'abccc'],
    ['name2', '123123'],
];
// 批量插入数据
const batchIntoStr = 'insert into user_info(username, password) values ?';
db.query(batchIntoStr, [intoList], (error, result) => {
    if (error) {
        return console.log('报错：', error.message);
    }
    console.log('成功');
});

更新数据

逐个字段更新

// 更新数据
// 插入数据的快捷方式
const user = { username: 'test1', password: 'abcdefg' };
const updateStr = 'update user_info set username=?, password=? where id=?';
// 插入值的key一定不要查出数据表的列
//  db.query(插入值SQL, [值1,值2,...], (error, result) => {});
db.query(updateStr, [user.username, user.password, 1], (error, result) => {
    ...
});

优化逐个字段更新更新

const user = { username: 'test1', password: 'abcdefg' };
const batchUpdateStr = 'update user_info set ? where id=?';
db.query(batchUpdateStr, [user, 1], (error, result) => {
   ...
});

批量更新(1)

// 拼接多个sql
// 注意如果要同时执行多条sql语句，需要在创建mysql连接的时候，在连接数据库时设置multipleStatements为true
var values = [ ['test11', '111'], ['test12', '2222'], ['test13', '3333'] ]
const batchUpdateStr = 'update user_info set ? where id=?';
let sql = '';
values.forEach(
    (item) => {
        sqls += mysql.format(batchUpdateStr, item) + ';'
    }
);
db.query(sqls, (eror, result) => {
      ...
})

(2) 利用insert into ... on duplicate key update

批量更新（2） 利用insert into ... on duplicate key update，该语法在insert的时候，如果insert的数据会引起唯一索引（包括主键索引）的冲突，即这个唯一值重复了，则不会执行insert操作，而执行后面的update的更新操作操作

var values = [ ['test11', 1], ['test12', 2], ['test13', 6] ]
var batchUpdateStr = 'insert into user_info(username, id) values ? on duplicate key update username = values(username)';
db.query(sql, [values], (eror, result) => {
   ...
})

删除数据

// 删除数据
const deleteStr = 'delete from user_info where id=?';
// 一个占位符可不用数组
db.query(deleteStr, 5, (error, result) => {
    ...
});

避免误删，一般不会直接删除数据表数据，可用更新数据表的指定字段如：status，标记该数据已删除

const deleteStr = 'update user_info set status=? where id=?';