SQL-进阶知识

// SQL语句
1. a <> 0 // a !== 0
2. show databases // 查看所有的库
3. create schema '库名' // 创建库
4. create table '库名' '表名' // 创建表
5. use '库名' // 使用这个库
6. show tables // 查看当前库中的所有表
7. insert into '表名'(username, pwd) values ('cj', '123') // 插入数据
8. select * from users // 查这个表下的所有数据列
9. select id, username from users // 查users表下的id和username列的数据
10. select * from users where username = 'cj' and/or pwd = '123' // 查users表下username = cj并且/或者pwd是123的数据
11. select * from users where username like %cj% // 模糊查username = cj
12. select * from users where username like %cj% order by id desc limit 2 offset 2 // 模糊查username = cj,排序id,加desc为倒序
13. update users set pwd = '456' where username = 'cj' // 把username = cj 的这条记录的pwd改成 pwd = 456
14. delete from users where pwd = '123' // 删除pwd = 123这一行
15. select * from users limit 2 offset 2 // limit每次查2条,offset跳过2条相当于分页
16. select * from users inner join blogs on blogs.id = users.userId // 连表查询
// 连表查询
17. select users.*, blogs.title, blogs.content from users inner join blogs on blogs.id = users.userId where blogs.title = '标题1'
18. 正常不会通过直接删,会采用给每一行新增一个属性status,用update来改变这个值,从而达到删除的目的,不是真的删除
19. `password`是数据库的保留名称,如果有字段是password需要加上``才可以
20. 在SQL客户端中写SQL语句前面加上--表示注释
21. 在写SQL的条件where时可以在后面跟上 1=1, 避免后面要拼接的参数无值
    select * from users where 1=1
----------------------------------------------------------------------------------------------
// redis 语句，属于内存数据库
1. 访问数据频繁,对性能要求极高
2. 断电丢失数据,内存的硬伤
3. 数据量不会太大
4. 在命令行执行 redis-cli 才能使用以下命令
5. set name cj // 添加name = cj
6. get name // 获取name
7. del name // 删除name
8. keys * // 获取全部key
----------------------------------------------------------------------------------------------
// Mongodb 语句
1. show dbs // 查看所有的库
2. use myblog // 使用 myblog 这个库，如果 myblog 不存在则会自动创建并使用
3. show collections // 查看当前库中的集合
// 往当前集合中插入一条数据（文档），db 表示当前使用的库，如果 blogs 不存在则会自动创建
4. db.blogs.insert({ "title": "标题1", "content": "内容1" })
5. db.blogs.find() // 查看当前集合中的所有数据
6. db.blogs.find().sort({ _id: -1 }) // 查看当前集合中的所有数据并按 id 倒序排列
7. db.blogs.find({ "title": "标题1" }) // 查看当前集合中 title 是标题1 的数据
8. db.blogs.update({ "title": "标题1" }, { $set: { "title": "标题2" }}) // 把 title 的标题1 修改成 标题2
9. db.blogs.remove({ "title": "标题1" }) // 删除 title 是标题1 的这条数据
----------------------------------------------------------------------------------------------
// BSON 是什么
1. JSON 是一种常用的数据格式
2. JSON 是字符串类型的
3. BSON = Binary JSON 即二进制类型的 JSON
----------------------------------------------------------------------------------------------
// Mysql、Redis、Mongodb 的区别
1. Mysql：关系型，表格存储，SQL操作，硬盘
2. Redis：非关系型，key-value 存储，NoSql，内存
3. Mongodb：非关系型，文档存储，NoSql，硬盘
----------------------------------------------------------------------------------------------
// Mysql 和 Mongodb 选型
1. Excel 可类比 Mysql，Word 可类比 Mongodb
2. Mysql 适合存储格式规整的信息
3. Mongodb 适合存储格式松散的信息
4. 两者可以同时使用
  1. 如一个博客表，可以用 mysql 存储，而表中的内容字段（很长很多）则可以用 mongodb 存储
5. Mongodb 数据库相当于 Mysql 数据库
6. Mongodb 集合相当于 Mysql 表
7. Mongodb 文档相当于 Mysql 记录（一行数据）
----------------------------------------------------------------------------------------------
// sequelize
1. ORM // Object Relational Mapping 即对象关系映射
  1. 数据表，用 js 中的模型（'class' 或对象）代替
  2. 一条或多条记录，用 js 中一个对象或数组代替
  3. sql 语句，用对象方法代替
2. 建模（外键）同步到数据库 // 相当于建表建字段等
3. 增删改查、连表查询 // 使用 sequelize API 而不用 sql
4. 连接测试
const Sequelize = require('sequelize') // npm
const config = {
  host: 'localhost',
  dialect: 'mysql' // 类型，mysql
}
// 线上环境使用连接池
config.pool = {
  max: 5, // 连接池中最大的连接数量
  min: 0, // 最小
  idle: 10000 // 如果一个连接池 10s 之内没有被使用，则释放
}
const seq = new Sequelize('库名', 'root', '123456', config)
seq.authenticate().then(() => {
  console.log('成功')
}).catch(() => {
  console.log('失败')
})
module.exports = seq
5. 创建模型
const Sequelize = require('sequelize') // npm
const seq = require('./连接测试') // 第 4 点的代码
// 创建 user 模型，数据表的名字是 users
const User = req.define('user', {
  // id 会自动创建，并设为主键自增
  // createdAt 和 updatedAt 也会自动创建
  userName: {
    type: Sequelize.STRING, // 相当于 varchar(255)
    allowNull: false // 不能为空
  }
  password: {
    type: Sequelize.STRING, // 相当于 varchar(255)
    allowNull: false // 不能为空
  },
  nickName: {
    type: Sequelize.STRING, // 相当于 varchar(255)
    comment: '昵称' // 这是注释
  }
})
// 创建 blog 模型，数据表的名字是 blogs
const Blog = req.define('blog', {
  // id 会自动创建，并设为主键自增
  // createdAt 和 updatedAt 也会自动创建
  title: {
    type: Sequelize.STRING, // 相当于 varchar(255)
    allowNull: false // 不能为空
  }
  content: {
    type: Sequelize.TEXT, // 比 varchar(255) 长
    allowNull: false // 不能为空
  },
  userId: {
    type: Sequelize.STRING, // 相当于 varchar(255)
    allowNull: false // 不能为空
  }
})
// 外键关联1（看第 7 点的连表查询）
Blog.belongsTo(User, {
  // 创建外键 Blog.userId -> User.id
  foreignKey: 'userId'
})
// 外键关联2（看第 7 点的连表查询）
User.hasMany(Blog, {
  // 创建外键 Blog.userId -> User.id
  foreignKey: 'userId'
})
module.exports = {
  User,
  Blog
}
6. 插入数据
const { User, Blog } = require('./创建模型') // 第 5 点的代码
!(async function() {
  // 创建用户 相当 insert into users (...) values (...)
  const zhangsan = await User.create({
    userName: 'zhangsan',
    password: '123',
    nickName: '张三'
  })
  console.log('zhangsan:', zhangsan.dataValues)
  const zhangsanId = zhangsan.dataValues.id

  const lisi = await User.create({
    userName: 'lisi',
    password: '123',
    nickName: '李四'
  })
  console.log('lisi:', lisi.dataValues)
  const lisiId = lisi.dataValues.id

  // 创建博客
  const blog1 = await Blog.create({
    title: '标题1',
    content: '内容1',
    userId: zhangsanId
  })
  console.log('blog1', blog1.dataValues)

  const blog2 = await Blog.create({
    title: '标题2',
    content: '内容2',
    userId: lisiId
  })
  console.log('blog2', blog2.dataValues)
})()
7. 查询
const { User, Blog } = require('./创建模型') // 第 5 点的代码
!(async function() {
  // 查询一条记录
  const zhangsan = await User.finOne({
    where: {
      userName: 'zhangsan'
    }
  })
  console.log('zhangsan', zhangsan.dataValues)

  // 查询特定的列
  const zhangsanName = await User.findOne({
    attributes: ['userName', 'nickName'],
    where: {
      userName: 'zhangsan'
    }
  })
  console.log('zhangsanName', zhangsanName.dataValues)

  // 查询一个列表
  const zhangsanBlogList = await Blog.findAll({
    where: {
      userId: 1
    },
    order: [
      ['id', 'desc']
    ]
  })
  console.log('zhangsanBlogList', zhangsanBlogList.map(item => item.dataValues))

  // 查询总数
  const blogListAndCount = await Blog.findAndCountAll({
    limit: 2, // 限制本次查询 2 条
    offset: 2, // 跳过多少条
    order: [
      ['id', 'desc']
    ]
  })
  console.log('blogListAndCount', blogListAndCount.count, blogListAndCount.rows.map(item => item.dataValues))

  // 连表查询1（对应第 5 点的关联外键 1）
  const blogListWithUser = await Blog.findAndCountAll({
    order: [
      ['id', 'desc']
    ],
    include: [
      {
        model: User,
        attributes: ['userName', 'nickName'],
        where: {
          userName: 'zhangsan'
        }
      }
    ]
  })
  console.log('blogListWithUser', blogListWithUser.count, blogListWithUser.rows.map(item => {
    const blogValue = item.dataValues
    blogValue.user = blogValue.user.dataValues
    return blogValue
  }))

  // 连表查询2（对应第 5 点的关联外键 2）
  const userListWithBlog = await Blog.findAndCountAll({
    attributes: ['userName', 'nickName'],
    include: [
      {
        model: Blog,
      }
    ]
  })
  console.log('userListWithBlog', userListWithBlog.count, userListWithBlog.rows.map(item => {
    const userValue = item.dataValues
    userValue.blogs = userValue.blogs.map(subItem => subItem.dataValues)
    return userValue
  }))

})()
8. 更新
const { User, Blog } = require('./创建模型') // 第 5 点的代码
!(async function() {
  const updateRes = await User.update({
    nickName: '张三'
  }, {
    where: {
      userName: 'zhangsan'
    }
  })
  console.log('updateRes', updateRes[0] > 0)
})()
9. 删除
const { User, Blog } = require('./创建模型') // 第 5 点的代码
!(async function() {
  // 删除一条博客
  const delBlogRes = await Blog.destroy({
    where: {
      id: 4
    }
  })
  console.log('delBlogRes', delBlogRes > 0)

  // 删除一个用户
  const delUserRes = await User.destroy({
    where: {
      id: 1
    }
  })
  console.log('delUserRes', delUserRes > 0)
})()