最新更新时间:2020年11月27日10:54:07
《猛戳-查看我的博客地图-总有你意想不到的惊喜》
本文内容:最近在做node开发,写了大量的sql语句,本文以作记录,CRUD(create, read, update, delete)
常用sql语句
- 增
//创建视图 视图是一张虚拟表
//视图的创建方式和表一样,需要实例化、配置映射文件、实现get/set方法等
CREATE VIEW my_view_data AS SELECT t.name FROM table1 AS t;
- 删
//删除单条数据
DELETE FROM table1 WHERE value=1;
//删除指定的多条数据
DELETE FROM table1 WHERE value IN (1,4);
//删除连续的多条数据
DELETE FROM table1 WHERE value>=1 AND value<=9;
-
改
-
查
//查询 table1 表中的所有数据
SELECT * FROM table1;
SELECT age,name FROM table1;
//查询某列的值并去重
SELECT DISTINCT phone_brand FROM table1;// xiaomi huawei iphone vivo oppo
SELECT COUNT(DISTINCT phone_brand) AS nums FROM table1;//5
//查询 table1 表中的数据量
SELECT COUNT(*) FROM table1;
SELECT COUNT(*) AS numbs FROM table1;
//按照条件查询 table1 表中的所有数据
SELECT * FROM table1 WHERE name="wanshaobo";
SELECT * FROM table1 WHERE is_delete="wanshaobo" AND name="bus";
//分页查询 table1 表中的数据
SELECT * FROM table1 LIMIT 0,10;//从表的偏移量0开始查询10条数据
SELECT * FROM table1 LIMIT 12,8;//从表的偏移量12开始查询8条数据
//分页查询 table1 表中的数据后排序返回
SELECT * FROM table1 ORDER BY create_time DESC;//创建时间降序
//表连接查询 table1是主表 table2是从表
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.page_id;//按照关联字段将table2的数据合并到table1中
SELECT table1.*,table2.id FROM table1 LEFT JOIN table2 ON table1.id=table2.page_id;//按照关联字段将table2的部分数据合并到table1中
SELECT table1.age,table2.name FROM table1 LEFT JOIN table2 ON table1.id=table2.page_id;//按照关联字段将table2的数据合并到table1中 只返回部分数据
//3表查询 table1是主表 table2和table3是从表
SELECT t3.i_d,t3.na_me,t4.verion FROM (SELECT t1.id AS i_d,t2.name AS na_me FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.p_id = t2.id) AS t3 LEFT JOIN table3 AS t4 ON t3.pId = t4.p_id
高级SQL
SELECT name AS n, country AS c FROM Websites;
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites;
SELECT w.name, w.url, a.count, a.date FROM Websites AS w, access_log AS a WHERE a.site_id=w.id and w.name="菜鸟教程";
参考资料
感谢阅读,欢迎评论^-^