SQL语句-查询

  • 查询Select
    • 简单查询
    • 条件选择where
      • 比较条件和逻辑条件:
      • 字符串
      • 运算符的优先级:
      • 特殊条件:
        • **判空**
        • **区间**
        • in
        • 模糊查询like
    • 排序order by
    • 分页查询
    • 别名 as

查询Select

简单查询

查询所有列:

select * from tablename;

查询指定列:

select 列名1,列名2,……  from tablename;

去重,返回唯一不同的值,若有多列,只要不全同皆返回

select distinct 列…… from tablename;

条件选择where

select * from tablename where id=1;

比较条件和逻辑条件:

比较:=,>,<,>=,<=,!=
逻辑 :and,or,not

select * from tablename where id=1 and name='cj''l';
select * from tablename where id=1 or name='cjl';
select * from tablename where not id=1;

字符串

用单引号’ ‘括起来,若要在字符串中用单引号’,在字符串中两个单引号’’表示一个单引号
isn’t

'isn''t'

运算符的优先级:

()—> not —> and —> or

特殊条件:

判空

is null

select id from tablename where name is null;
select id from tablename where name not is null;

区间

between,区间时左闭右开,[1,10)

select * from tablename where id between 1 and 10
select * from tablename where id not between 1 and 10

字母区间:以’A’到’H’之间字母开头的所有name

select * from tablename where name between 'A' and 'H'

日期区间

select id from tablename where datename between '2020-07-06' and '2020-07-08';

in

select * from tablename where id in(1,2,3,4);

模糊查询like

% : 代表0个或多个字符
_ : 代表一个字符

like 'M%'  //以M开头的所有
like '%M'  //以M结尾的所有
like '%M%' //包含M的所有
like '_oogle' //任意字符+oogle的所有

排序order by

默认:升序
desc:降序

selec * from tablename order by id;
selec * from tablename order by id desc;

多列排序
先按第一个列排序,第一列一样的再对第二列排序

select * from tablename order by id,age;
select * from tablename order by id desc,age;
select * from tablename order by id,age desc;

分页查询

limit:返回规定数目的记录
返回前五条记录

select * from tablename limit 5;

返回id最大的前五条

select * from tablename order by id desc limit 5;

分页查询重点:limit startIndex,length
startIndex:起始位置
length:要查询记录的数目
意思:从startIndex的位置开始查询length条记录

查询前五条记录

select * from tablename limit 0,5;

查询第6到10条记录

select * from tablename limit 5,5;

别名 as

对列名分别取别名

select1 as 别名1,列2 as 别名2,…… form tablename;

将不同列结合取别名

select1 as 别名1,contact(2,',',3,',',……)as 别名2 from tablename;

表的别名

select1别名.列,表2别名.from1 as 别名1,表2 as 别名2 where ……
select w.name,a.date from1 as w,2 as a where w.id=a.id;

本文地址:https://blog.csdn.net/qq_36976201/article/details/107167027

THE END
喜欢就支持一下吧
点赞8分享