js►nodejs

05-SQL增删改查,node操作SQL

数据库基本操作

一、字段操作(列操作)

1. 增加列

1
2
3
4
5
6
7
8
9
10
alter table 'tableName' add 'columnName' int(255)
-- 类型长度可变
alter table 'tableName' add (
	'columnName1' int(255),
    'columnName2' timestamp(8),
    'columnName3' float(5,2)
)
-- 添加多个字段，如果某个新字段和原有的重复了，那么所有的新字段都不会添加
-- timestamp类型长度可选，14(显示完整年份且精确到秒)，12(不显示完整年份且精确到秒)，8(完整年份+精确到日期)，6(不完整年份+精确到日期)
-- float(5,2)表示这个数一共5位，小数部分2位，例如321.42

2. 修改列

1
2
alter table 'tableName' alter 'columnName' int(255)
-- 修改类型

3. 删除列

1
alter table 'tableName' drop column 'columnName'

4. 获取所有某数据库某表所有列名

1
2
3
select COLUMN_NAME from information_schema.COLUMNS where table_name = 'tableName' and table_schema = 'databaseName'
-- information_schema.COLUMNS中存有所有数据库所有表的各种信息，包括字段名。从中筛选出需要的某个数据库中的某个表的字段名即可
-- 会获取到一个对象数组。每个对象是RowDataPacket类，只有一个COLUMN_NAME成员，存有字段名

二、行操作

1. 增加数据

1
2
3
4
5
insert into 表名 (键1, 键2, ...) values (值1, 值2, ...);
-- 如果没有值可传，就写null
insert into 表名 values (值1, 值2, ...);
-- 必须给所有键传值，没有值就写null
insert into 表名 set 键1=值1, 键2=值2, 键3=值3, ...;

2. 更新(修改)数据

1
2
3
4
5
6
7
-- where 筛选出一条或多条数据
update 表名 set 键1=值1, 键2=值2, 键3=值3, ...;
-- 这样会将所有数据对应键的值全部修改（很危险）
update 表名 set 键1=值1, 键2=值2, 键3=值3, ... where 键=值;
-- 根据指定键的指定值来修改某一条或多条数据
-- 如果既没有字段名，又找不到这条数据(where找不到)，那么先报错：没有字段名
-- 如果找不到这条数据，那么不会报错，而返回的OkPacket对象中affectedRows为0

3. 删除数据

1
2
3
4
delete from 表名;
-- 会清空整个表的数据（很危险）
delete from 表名 where 键=值;
-- 根据指定键的指定值来删除某一条或多条数据

4. 查询数据（普通）

1
2
3
4
5
6
7
select * from 表名;
-- 查询表中所有的数据
select 键1, 键2, ... from 表名;
-- 查询每条数据的指定键的值
select * from 表名 where 键1=值1;
-- 根据指定键的指定值来查询某一条或多条数据
-- 查询字符串时注意要给值加上双引号，否则查询的是字段名(列名)

5. 查询数据（组合）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 且 and
select * from 表名 where 键1=值1 and 键2=值2 and ...;
-- 查询满足：键1是值1且键2是值2且… 的数据

-- 或 or
select * from 表名 where 键1=值1 or 键2=值2 or ...;
-- 查询满足：键1是值1或键2是值2或… 的数据

-- 模糊查询 like （一般用来搜索）
select * from 表名 where 键1 like "%值1%";
--（% → 可以指代若干个任意的字符）
-- 根据指定键[包含]指定值来查询某一条或多条数据

-- 指定多个值 in
select * from 表名 where 键名 in (值1, 值2, ...);
-- 查询所有键是值1或值2或…的所有数据（一般用于批量删除）和or效果类似，不过在字段没有索引时or的效率是O(N)，而in的效率是O(logN)
    
-- 排序 order by xxx
select * from 表名 order by 键名;
-- 将查询结果根据键名的值排序（默认升序，从小到大）
select * from 表名 order by 键名 desc;（降序）
    
-- 分页 limit
select * from 表名 order by 键名 limit （跳过条数,） 选取条数;
-- 查询并排序，展示时跳过第一个参数，展示第二个参数的条数（跳过条数可以省略）
    
-- 总条数 count
select count(*) from 表名;
select count(*) as 计数名 from 表名;
-- 返回查出数据的条数

三、使用nodejs操作SQL

  1. 安装mysql包并导入
    npm i mysql
    const mysql = require('mysql')

  2. 使用步骤

    1. 创建连接

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      const connection = mysql.createConnection({
            // 主机地址（必须，根据需要更换）
            host: '127.0.0.1',
            // 用户（必须，就是数据库用户）
            user: 'root',
            // 密码（必须，就是数据库密码）
            password: 'root',
            // 连接的数据库（必须）
            database: 'mydatabase',
            // 端口号
            port: 3306
      })

    2. 建立连接 connection.connect()

    3. 执行查询操作

      1
      2
      3
      4
      5
      6
      7
      8
      9
      connection.query(sql语句, [参数列表,] callback(error, results) => {
            if (error) return console.log(error)
            // results为数组，就算没有数据也会给一个空数组
            console.log(results)
      })
      sql语句：
            可以用一个或多个?来占位
            然后输入实参/实参数组/实参键值对对象作为参数列表（由mysql模块提供语法，其他地方不要用）
            也可以传入多个实参时就用字符串拼接/模板字符串

    4. 关闭连接（重要）connection.end()

    注意：每次调用end之后必须重新建立链接，createConnection的参数也必须重新输入。

  3. 操作效果
    select results => 返回查询内容[{}, {}, …]，如果没查到会是空数组[]。
    delete results => 返回OkPacket对象，包含操作成功后的信息，affectedRows属性表示影响行数。
    update、insert和delete相同。
    参考：https://www.npmjs.com/package/mysql