链表查询

Example

内连接

import emysql, { DefineTable } from '@aicblock/emysql'

// 数据库实例化
const mysql = new emysql({
  password: '[db登录密码]',
  user: '[db登录用户名]',
  database: '访问数据库名称'
})

await mysql.init()

// 创建表结构
const t_products = DefineTable({
  tableName: 't_products',
  columns: [
    {
      name: 'code',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '产品编码'
    },
    {
      name: 'name',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '产品名称'
    },
    {
      name: 'type',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '产品类型'
    }
  ]
})
const t_product_type = DefineTable({
  tableName: 't_product_type',
  columns: [
    {
      name: 'type',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '产品类型'
    },
    {
      name: 'cpu',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: 'CPU型号'
    }
  ]
})
// 创建表
await mysql.table.create([t_products, t_product_type])

const products = [
  {
    code: 'hw_Phone',
    name: 'Huawei Phone',
    type: 'phone'
  }
]
// t_product_type
const t_product_type = [
  {
    type: 'phone',
    cpu: '麒麟A1'
  }
]
// 插入数据
await mysql.change.insert({
  t: t_products,
  params: products
})
await mysql.change.insert({
  t: t_product_type,
  params: t_product_type
})

// 执行内连接查询
const result = await mysql.query({
  t: t_products,
  fields: ['name', 'code', 'cpu'],
  join: {
    t: t_product_type,
    on: {
      joinField: 'type',
      mainField: 'type',
      mainTable: t_products
    }
  }
})

// result:: [{ code: 'hw_Phone', name: 'Huawei Phone', cpu: '麒麟A1' }]

左连接

import emysql, { DefineTable } from '@aicblock/emysql'

// 数据库实例化
const mysql = new emysql({
  password: '[db登录密码]',
  user: '[db登录用户名]',
  database: '访问数据库名称'
})

await mysql.init()

// 创建表结构
const t_user = DefineTable({
  tableName: 't_user',
  columns: [
    {
      name: 'id',
      dataType: 'INT',
      primaryKey: true,
      autoIncrement: true,
      comments: '主键id'
    },
    {
      name: 'name',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '名称'
    },
    {
      name: 'department_id',
      dataType: 'INT',
      notNull: true,
      comments: '部门id'
    }
  ]
})
const t_department = DefineTable({
  tableName: 't_department',
  columns: [
    {
      name: 'id',
      dataType: 'INT',
      primaryKey: true,
      autoIncrement: true,
      comments: '主键id'
    },
    {
      name: 'name',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '部门名称'
    }
  ]
})
// 创建表
await mysql.table.create([t_user, t_department])

// t_user 数据
const user = [{ name: 'name1', department_id: 1 }]

// t_department 数据
const department = [{ name: '部门1', id: 1 }]

// 插入数据
await mysql.change.insert({
  t: t_user,
  params: user
})
await mysql.change.insert({
  t: t_department,
  params: department
})

// 执行左连接查询
const result = await mysql.query({
  t: t_user,
  fields: [
    { name: 'name', t: t_user.tableName, alias: 'userName' },
    { name: 'name', t: t_department.tableName, alias: 'departmentName' }
  ],
  join: {
    type: 'LEFT',
    t: t_department,
    on: {
      joinField: 'id',
      mainField: 'department_id',
      mainTable: t_user
    }
  }
})

// result:: [{ userName: 'name1', departmentName: '部门1' }]

右连接

import emysql, { DefineTable } from '@aicblock/emysql'

// 数据库实例化
const mysql = new emysql({
  password: '[db登录密码]',
  user: '[db登录用户名]',
  database: '访问数据库名称'
})

await mysql.init()
// 创建表结构
const t_user = DefineTable({
  tableName: 't_user',
  columns: [
    {
      name: 'id',
      dataType: 'INT',
      primaryKey: true,
      autoIncrement: true,
      comments: '主键id'
    },
    {
      name: 'name',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '名称'
    },
    {
      name: 'department_id',
      dataType: 'INT',
      notNull: true,
      comments: '部门id'
    }
  ]
})
const t_department = DefineTable({
  tableName: 't_department',
  columns: [
    {
      name: 'id',
      dataType: 'INT',
      primaryKey: true,
      autoIncrement: true,
      comments: '主键id'
    },
    {
      name: 'name',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '部门名称'
    }
  ]
})
// 创建表
await mysql.table.create([t_user, t_department])

// t_user 数据
const user = [{ name: 'name1', department_id: 1 }]

// t_department 数据
const department = [{ name: '部门1', id: 1 }]
await mysql.change.insert({
  t: t_user,
  params: user
})
await mysql.change.insert({
  t: t_department,
  params: department
})

// 执行左连接查询
const result = await mysql.query({
  t: t_user,
  fields: [
    { name: 'name', t: t_user.tableName, alias: 'userName' },
    { name: 'name', t: t_department.tableName, alias: 'departmentName' }
  ],
  join: {
    type: 'RIGHT',
    t: t_department,
    on: {
      joinField: 'id',
      mainField: 'department_id',
      mainTable: t_user
    }
  }
})

// result:: [{ userName: 'name1', departmentName: '部门1' }]