sql 基础

2019-11-21 本文已影响0人守住这块热土

select col ,col ,col （列名）找什么
from tabe 从哪找
where col 条件是什么
column
select title frome movies

1. sql--查询

select * from movies where 1=1;
1表示没有任何条件

SELECT sysid, rulename FROM fablistrule where 1=1;

2. sql----数字查询

select * from table where col=1;

col!=4
col between 1.5 and 10.5
col not between 1 and 10
col in (2,4,6)                   在集合里
col not in (1,3,5)             不在集合里

SELECT *
FROM fabmisc
where value not BETWEEN '10' and '20';

SELECT *
FROM fabmisc
where value >= '10' and value <= '15';

3. 字符串查询

文本

= : 不能结合%使用
!= or <> 大小写敏感
like 大小写不敏感
not like
% 通配符 : col like '%AT%'
_  模糊匹配单个字符 : 只使用在like 和 not like --- col like 'AN_'
in 
not in

SELECT *
FROM fabmisc
where NAME like 'EAPTimeOu_';

4. sql---排序

oderby   col asc/desc  按col排序
asc 升序
desc 降序
limit 4 offset 9
order by col1 asc, col2 desc; 多列排序

排重
distinct col

SELECT distinct value
FROM fabmisc
where 1=1
order by value desc;

5. 多表连接

当查找的数据在多张关联table里时：
select *
from table1 left join table2 on table1.id=table2.id
where col >1;

join ···on   ---  t1 join t2 on t1.id = t2.id  按ID连成一个表
inner join   保留id相等的row
left join
right join  保留t2的所有row
is/is not null   ---    col  is /is not null   col是不是null

可参考该文档：
https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html

6. 表达式

+-*/
substr  取子串
as   col*2 as col_new   取别名

select id,tiltle,year/2 from movies;

7. 统计（select）

count()  计数
min(col)最小
max
avg
sum
group by
having  分组后条件

SELECT nickname,count(*)
FROM fwuserprofile
where 1=1
group by nickname;

8. 数据库介绍

sqlbolt.com
excel
金老师手册
课程视频：https://www.bilibili.com/video/av54957581?p=10