mysql select left [outer] join_sequelize-typescript 子查询、分页、order、like、fn

0. 实体结构

0.1 MySQL：

/*Table structure for table `book` */

DROP TABLE IF EXISTS `book`;

CREATE TABLE `book` (

`rid` INT(11) NOT NULL,

`authorId` INT(11) DEFAULT NULL,

PRIMARY KEY (`rid`),

KEY `FK_person_id` (`authorId`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

/*Table structure for table `person` */

DROP TABLE IF EXISTS `person`;

CREATE TABLE `person` (

`rid` INT(11) NOT NULL AUTO_INCREMENT,

`name` CHAR(50) DEFAULT NULL,

PRIMARY KEY (`rid`)

) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

0.2 Sequelize-typescript Model：

import {Table, Column, Model, Sequelize, ForeignKey, BelongsTo, HasMany } from 'sequelize-typescript';

@Table({

tableName: 'base_table'

})

export default class base_table extends Model{

@Column({

primaryKey: true,

autoIncrement: true

})

rid: number;

}

@Table({

tableName: 'person'

})

export default class person extends base_table {

@Column

name:string;

@HasMany(() => book)

book: book[];

}

@Table({

tableName: 'book'

})

export default class book extends base_table {

@ForeignKey(() => person)

@Column

authorId: number;

@BelongsTo(() => person)

person:person;

}

1. 嵌套子查询

MySQL:

SELECT

`person`.*,

(select count(*)

from book

where person.rid = book.authorId) AS `bookCount`

FROM `person`;

sequelize-typescript:

let options = {

attributes:['person.*',

[

Sequelize.literal("(select count(*) from book where person.rid = book.authorId)"),

'bookCount'

]]

};

person.findAndCountAll(options)

.then(result => {

console.log('result.rows:',result.rows)

})

2. LEFT OUTER JOIN

MySQL:

SELECT

`person`.`rid`,

`person`.`name`,

`book`.`rid` AS `book.rid`,

`book`.`authorId` AS `book.authorId`

FROM `person`

LEFT OUTER JOIN `book`

ON `person`.`rid` = `book`.`authorId`;

typescript:

let options = {

include:[{model:book}]

}

person.findAndCountAll(options).then(results => {

results.rows.forEach((item, index) => {

console.log('item:',item)

})

})

3. 分页查询

MySQL:

SELECT `rid`, `authorId`

FROM `book`

LIMIT 0, 10;

sequelize-typescript:

let currentPage = 1;

let pageSize = 10;

let options ={

raw:true,

offset:(currentPage - 1) * pageSize,

limit:pageSize

}

book.findAndCount(options)

.then(result => {

result.rows.forEach((item, index) => {

console.log('item:',item)

})

})

4. order

MySQL:

SELECT `rid`, `name`

FROM `person`

ORDER BY `rid` DESC;

sequelize-typescript:

let options = {

raw:true,

order:[['rid', 'DESC']]

}

person.getList(options)

.then(result =>{

console.log('result:',result)

})

5. like操作符

MySQL:

SELECT `rid`, `name`

FROM `person`

WHERE `name` LIKE '%asa%';

sequelize-typescript:

let options = {

where: {

name: {

[Sequelize.Op.like]:'%'+'asa'+'%'

}

}

}

person.findAndCountAll(options)

.then(result => {

console.log('result:',result);

})

6. Function

MySQL:

UPDATE `person`

SET

`rid`=1,

`name`=md5("222")

WHERE `rid` = 1

sequelize-typescript:

let options = {

where:{rid:1}

}

let person_item= {

rid:1,

name:Sequelize.literal('md5("222")')

}

person.update(person_item,options)

.then(result => {

console.log('result:',result)

})