数据库sql操作基础和ORM

女爷i 2022-10-30 09:30 279阅读 0赞

**一，数据库基本操作，连表查询**

show databases;
    -- `显示所有的库`
    use koa2weibo;
    -- `启用当前的库`
     
    select * from blog order by id desc;  //通过id 倒序查询
    select count(*) from blog; //查询总数
    select count(*) as `count` from blog; //查询总数用一个别名
    select count(id) as `count` from blog;//查询总数不需要查所有的 查id就行了
    
    select * from blog order by id desc limit 2 offset 2;  //查表进行分页每页limit2条 
    
    select * from users; //查询users表 所有数据 一般不用 而用下面条件查询
    select username,`password` from users where username='aaa' and `password`='123'; //条件查询
    -- select username,password from users  //条件查询
    
    insert into users (username,`password`,nickname) values('aaa','123','二个eee猴子'); //新增
    insert into blog (title,content,userid) values('标题4','内容4',2); //新增
    update blog set content='内容sssss' where id ='1'; //更新
    delete from blog where id = '3' //删除

-- '连表查询'
    select * from blog inner join users on users.id = blog.userid;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjMxMjA3NA_size_16_color_FFFFFF_t_70][]

select blog.*,users.username,users.nickname from blog inner join users on users.id = blog.userid;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjMxMjA3NA_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjMxMjA3NA_size_16_color_FFFFFF_t_70 2][]

**二,ORM知识点**

直接在node里面写sql语句查询比较底层，也不太方便。用ORM（对象关系映射）工具简化操作。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjMxMjA3NA_size_16_color_FFFFFF_t_70 3][]

我们用sequelize

npm i mysql2 sequelize -D

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjMxMjA3NA_size_16_color_FFFFFF_t_70]: /images/20221024/18c71adf3d424385a95d44f613250b24.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjMxMjA3NA_size_16_color_FFFFFF_t_70 1]: /images/20221024/4fd8d65965e847d4871cef052969012f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjMxMjA3NA_size_16_color_FFFFFF_t_70 2]: /images/20221024/8003ea8f29c74af0a608e1a2b9074098.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjMxMjA3NA_size_16_color_FFFFFF_t_70 3]: /images/20221024/4beb25e6473a4af29d4606234a132b8a.png