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)
})