SQL语句-查询

查询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

对列名分别取别名

select 列1 as 别名1，列2 as 别名2，…… form tablename;

将不同列结合取别名

select 列1 as 别名1，contact(列2,',',列3,',',……)as 别名2 from tablename;

表的别名

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

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