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----數(shù)字查詢
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. 字符串查詢
文本
= : 不能結(jié)合%使用
!= or <> 大小寫敏感
like 大小寫不敏感
not like
% 通配符 : col like '%AT%'
_ 模糊匹配單個(gè)字符 : 只使用在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. 多表連接
當(dāng)查找的數(shù)據(jù)在多張關(guān)聯(lián)table里時(shí)：
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連成一個(gè)表
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. 表達(dá)式
+-*/
substr 取子串
as col*2 as col_new 取別名
select id,tiltle,year/2 from movies;
7. 統(tǒng)計(jì)（select）
count() 計(jì)數(shù)
min(col)最小
max
avg
sum
group by
having 分組后條件
SELECT nickname,count(*)
FROM fwuserprofile
where 1=1
group by nickname;
8. 數(shù)據(jù)庫(kù)介紹
sqlbolt.com
excel
金老師手冊(cè)
課程視頻：https://www.bilibili.com/video/av54957581?p=10
