sequelize初始化
先初始化一个数据库实例
// 实例化数据库对象-数据库连接池 var sequelize = new Sequelize( CONFIG.MYSQL.database, //数据库名 CONFIG.MYSQL.username, //账号 CONFIG.MYSQL.password, //密码 { host: CONFIG.MYSQL.host,//端口，一般为localhost // 数据库类型 dialect: 'mysql', // 是否打印日志 logging: CONFIG.DEBUG ? console.log : false, // 配置数据库连接池 pool: { max: 5, min: 0, idle: 10000 }, // 时区设置 timezone: '+08:00' });
再初始化一个model实例
// 定义model const Wish = db.define('Wish', { // 定义主键，autoIncrement 选项用于创建一个自增的整型列 id: {type: Sequelize.INTEGER, primaryKey: true, allowNull: false, autoIncrement: true}, // 日期默认值 => 当前时间 myDate: { type: Sequelize.DATE, defaultValue: Sequelize.NOW }, // 添加唯一（unique）约束后插入重复值会报错 someUnique: {type: Sequelize.STRING, unique: true}, // 可以通过 "field" 属性来指定数据库中的字段名 fieldWithUnderscores: { type: Sequelize.STRING, field: "field_with_underscores" }, }, { // 是否支持驼峰 underscored: true, // MySQL数据库表名 tableName: 'wish', });
sequelize的数据类型
Sequelize.STRING // VARCHAR(255) Sequelize.STRING(1234) // VARCHAR(1234) Sequelize.STRING.BINARY // VARCHAR BINARY Sequelize.TEXT // TEXT Sequelize.TEXT('tiny') // TINYTEXT Sequelize.INTEGER // INTEGER Sequelize.BIGINT // BIGINT Sequelize.BIGINT(11) // BIGINT(11) Sequelize.FLOAT // FLOAT Sequelize.FLOAT(11) // FLOAT(11) Sequelize.FLOAT(11, 12) // FLOAT(11,12) Sequelize.REAL // REAL PostgreSQL only. Sequelize.REAL(11) // REAL(11) PostgreSQL only. Sequelize.REAL(11, 12) // REAL(11,12) PostgreSQL only. Sequelize.DOUBLE // DOUBLE Sequelize.DOUBLE(11) // DOUBLE(11) Sequelize.DOUBLE(11, 12) // DOUBLE(11,12) Sequelize.DECIMAL // DECIMAL Sequelize.DECIMAL(10, 2) // DECIMAL(10,2) Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres Sequelize.DATE(6) // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision Sequelize.DATEONLY // DATE without time. Sequelize.BOOLEAN // TINYINT(1) Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2' Sequelize.ARRAY(Sequelize.TEXT) // Defines an array. PostgreSQL only. Sequelize.JSON // JSON column. PostgreSQL only. Sequelize.JSONB // JSONB column. PostgreSQL only. Sequelize.BLOB // BLOB (bytea for PostgreSQL) Sequelize.BLOB('tiny') // TINYBLOB (bytea for PostgreSQL. Other options are medium and long) Sequelize.UUID // PostgreSQL 和 SQLite 中为 UUID, MySQL 中为CHAR(36) BINARY (使用 defaultValue: Sequelize.UUIDV1 或 Sequelize.UUIDV4 生成默认值) Sequelize.RANGE(Sequelize.INTEGER) // Defines int4range range. PostgreSQL only. Sequelize.RANGE(Sequelize.BIGINT) // Defined int8range range. PostgreSQL only. Sequelize.RANGE(Sequelize.DATE) // Defines tstzrange range. PostgreSQL only. Sequelize.RANGE(Sequelize.DATEONLY) // Defines daterange range. PostgreSQL only. Sequelize.RANGE(Sequelize.DECIMAL) // Defines numrange range. PostgreSQL only. Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // Defines array of tstzrange ranges. PostgreSQL only. Sequelize.GEOMETRY // Spatial column. PostgreSQL (with PostGIS) or MySQL only. Sequelize.GEOMETRY('POINT') // Spatial column with geomerty type. PostgreSQL (with PostGIS) or MySQL only. Sequelize.GEOMETRY('POINT', 4326) // Spatial column with geomerty type and SRID. PostgreSQL (with PostGIS) or MySQL only.
基本增删改查的写法
SELECT * FROM 表名 WHERE 属性名 = 值
写法：
model对象名 .findOne({ where: {属性名: 值}}).then(function(result) { console.log(result.dataValues) })
SELECT * FROM 表名
写法：
model对象名 .findAll({ raw: true }).then(function(result) { console.log(result) })
SELECT * FROM 表名 OREDER BY 属性名 DESC LIMIT 3
写法：
model对象名 .findAll({ limit: 3, order: [['属性名', 'DESC']] }).then(function(result) { result.forEach((v) => { console.log(v.dataValues); }); })
SELECT count(*) AS count FROM 表名
写法：
model对象名 .count().then(function(c) { console.log("表中有" + c + " 个元素") })
SELECT max(属性名) AS max FROM 表名
写法：
model对象名 .max('属性名').then(function(c) { console.log("表中最大值为" + c) })
SELECT min(属性名) AS min FROM 表名
写法：
model对象名 .min('属性名').then(function(c) { console.log("表中最小值为" + c) })
SELECT sum(属性名) AS sum FROM 表名
写法：
model对象名 .sum('属性名').then(function(c) { console.log("表中求和为" + c) })
INSERT INTO 表名 VALUES(‘value1’, …)
model对象名 .create({ 属性名1 : 值, 属性名2 : 值, 属性名3 : 值, ... });
UPDATE 表名 SET 属性名1=新值 WHERE 属性名2=值
model对象名 .update({ 属性名1: 新值, }, { where:{属性名2:值} });
DELETE FROM 表名 WHERE 属性名=值
model对象名 .destroy({ where: {属性名:值} });
