100条常用SQL语句

数据库 专栏收录该内容
1 篇文章 0 订阅
最新更新时间:2021年03月09日13:56:21

《猛戳-查看我的博客地图-总有你意想不到的惊喜》

本文内容:最近在做node开发,写了大量的sql语句,本文以作记录,CRUD(create, read, update, delete)

基础学习

关键字

  • ORDER BY

ORDER BY 关键字默认按照升序对记录进行排序,也可以使用关键字ASCascending
如果需要按照降序对记录进行排序,可以使用 DESC descending关键字

//单列降序 只查询一条数据
select * from table1 where origin_id = 333 order by modified_time DESC limit 1;
//多列升序
SELECT * FROM table1 ORDER BY origin_id,modified_time;
//筛选3列按照2列升序排列升序
SELECT id,origin_id,modified_time FROM table1 ORDER BY origin_id,modified_time;
  • SELECT TOP, LIMIT, ROWNUM

返回记录的数目

//MySQL 语法
SELECT * FROM table_name LIMIT 2;
//SQL Server / MS Access 语法
SELECT TOP 2 * FROM table_name;
//Oracle 语法
SELECT * FROM Persons WHERE ROWNUM <=5;

常用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;

  • 修改已有行的某几列
//更改table表中多条数据的多列,id是[36,42]的七条数据的school_id和school_name
UPDATE `table1` SET `school_id` = '101', `school_name` = '初中' WHERE `id` BETWEEN 36 AND 42;

//更改table1表中每一条数据的name为'[]',一般用于数据初始化
UPDATE `table1` SET `name` = '[]';
  • 向表增加列
//向 table1 表增加列 school_id和school_name,默认值为''
ALTER TABLE `table1` ADD COLUMN `school_id` varchar(36) NULL DEFAULT '' COMMENT '学校编号', ADD COLUMN `school_name` varchar(36) NULL DEFAULT '' COMMENT '学校名称';

ALTER TABLE `table_name` ADD COLUMN `column_name` varchar(1000) NOT NULL DEFAULT '[]' COMMENT '这个列的文字说明';

  • 精确查询
SELECT * FROM `table1` WHERE `is_deleted` = 0;
SELECT * FROM `table1` WHERE `id` = 1;
SELECT * FROM `table1` WHERE `school_id` = '80022431';
  • 范围查询
//查询 1,5,13
SELECT * FROM `table1` WHERE id=1 OR id=5 OR id=13;
SELECT * FROM `table1` WHERE id IN(1,5,13);

//查询 1,5,13 以外的数据
SELECT * FROM `table1` WHERE NOT (id=1 OR id=5 OR id=13);
SELECT * FROM `table1` WHERE id NOT IN(1,5,13);

//查询 [11,22]
SELECT * FROM `table1` WHERE `id` BETWEEN 11 AND 22;

//查询 [11,22] 以外的数据
SELECT * FROM `table1` WHERE `id` NOT BETWEEN 11 AND 22;
  • 判断空
//为空
SELECT * FROM `table1` WHERE `id` IS null;
//不为空
SELECT * FROM `table1` WHERE `id` IS NOT null;
  • 分页查询
SELECT * FROM table1 LIMIT 0,10;//从表的偏移量0开始查询10条数据
SELECT * FROM table1 LIMIT 12,8;//从表的偏移量12开始查询8条数据
  • 排序查询
SELECT * FROM table1 ORDER BY create_time DESC;//按照创建时间降序
  • 单列多值查询
//查询 school_type 为 101 和 102 的数据
SELECT * FROM table1 WHERE (school_type=101 OR school_type=102) AND is_deleted=0;
  • 模糊查询
//school列保存的是 学校id 的字符串数组 "[1,2,3,4,5]"
//查询 school 列中包含2的数据
let school_id = '2';
SELECT * FROM table1 WHERE school like '%2%';

注意:
需要有左右引号
“%” 符号用于在模式的前后定义通配符(默认字母)
NOT LIKE 表示取非

//查询 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是主表 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="菜鸟教程";

参考资料

感谢阅读,欢迎评论^-^

打赏我吧^-^

  • 1
    点赞
  • 0
    评论
  • 7
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页

打赏作者

万少博

快打赏我吧

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值