SQL基础知识

l  SELECT

SELECT distinct name,id from family

DISTINCT  查出name,id不同的行

 

l  ORDER BY

SELECT distinct name,id from family orderby id DESC

 

l  LIMIT

SELECT distinct name,id from family orderby id DESC LIMIT 2

 

l  WHERE

SELECT distinct name,id from family where hometown<>'xc' order by idDESC limit 2

 

l  BETWEEN

SELECT distinct name,id from family whereid BETWEEN 1 and 4

 

l  通配符

% 任意字符，出现任意次数

_ 任意字符，出现一次

 

l  计算字段

SELECTid*number from family

 

l  拼接字段

SELECT CONCAT(name,',',hometown)from family

 

l  别名

SELECT id*number as i_n from family

 

l  处理字符串的函数

SELECT LEFT(name,3)from family

UPPER,LOWER

 

l  GROUP BY

SELECT name,sex,max(number) from familyGROUP BY sex

核心：先分组，再聚合

函数：avg(),count(),max(),min(),sum()

l  HAVING

对GROUP BY后的分组进行过滤

l  count(1) 以第一列为计数标准

SELECT city,count(1) from DataAnalyst GROUPBY city

l  count(distinct positionId) 只计不重复项

SELECT city,count(distinct positionId) fromDataAnalyst GROUP BY city

 

l  SELECT 语句顺序

SELECT pas,COUNT(count) FROM didi WHERE id>0 GROUP BY pas Having count(*)>1 ORDER BY count

 

l  JOIN

SELECT name_dep.name,name_dep.dep,dep_tel.tel from name_dep INNER JOIN dep_tel ON name_dep.dep=dep_tel.dep

 

l  UNION

SELECT a.`name`, a.dep FROM name_dep AS a UNION ALL SELECT b.`NAME`, b.hometownFROM family AS b

UNIONALL包含重复项

UNION不包含重复项

l  IF

select *, IF(sex='m','男','女')from family