查詢

SELECT city FROM dataanalyst
WHERE city = '上海' and (positionname = '數(shù)據(jù)分析師' OR positionname = '數(shù)據(jù)產(chǎn)品經(jīng)理')

SELECT * FROM dataanalyst
WHERE city IN ('上海','北京','廣州','深圳')

SELECT * FROM dataanalyst
WHERE companyId BETWEEN 10000 and 20000
#使用between和and時(shí)，包括邊界值

SELECT * FROM dataanalyst
WHERE positionName LIKE '%數(shù)據(jù)分析%'
#%是通配符

SELECT * FROM dataanalyst
WHERE city NOT IN ('上海')
#上面的幾種查詢語句都可以加上not

分組

GROUP BY city

SELECT city,count(1) FROM dataanalyst
group by city
#count(1)表示對(duì)第一列計(jì)數(shù)

select city,count(DISTINCT positionId) FROM dataanalyst
group by city
#DISTINCT去重

SELECT city,AVG(DISTINCT companyId) from dataanalyst
GROUP BY city
#avg、min、max、sum等函數(shù)都可以在此使用

篩選

SELECT city,
            COUNT(DISTINCT positionid),
            COUNT(DISTINCT IF(industryfield LIKE '%電子商務(wù)%',positionid,NULL))
FROM dataanalyst
GROUP BY city
#NULL會(huì)被當(dāng)成空值，不會(huì)被計(jì)數(shù)

#對(duì)數(shù)據(jù)進(jìn)行篩選1
SELECT city,count(DISTINCT positionid) FROM dataanalyst
GROUP BY city 
HAVING COUNT(DISTINCT positionId)>=500

#對(duì)數(shù)據(jù)進(jìn)行篩選2
SELECT city,count(DISTINCT positionid) AS counts FROM dataanalyst
GROUP BY city 
HAVING counts>=500
#having是對(duì)查詢后的結(jié)果進(jìn)行篩選，所取變量是查詢后的表中的；
#而where是查詢的條件，所取變量是原表中的

嵌套

SELECT * FROM (
    SELECT city,count(DISTINCT positionid) as counts from dataanalyst
    GROUP BY city) as t1
WHERE counts >= 500
#t1是指嵌套的查詢得出的表

排序

GROUP BY city
ORDER BY counts #DESC
#加上desc的話是倒序

時(shí)間

SELECT DATE(NOW())  #只返回日期
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT QUARTER(NOW())
select DAYOFMONTH(NOW())
SELECT DAYOFYEAR(NOW())
SELECT WEEKOFYEAR(NOW())
#日期加減
SELECT DATE_ADD(DATE(NOW()),interval 1 DAY)
#用day，week，year都可以，減的話改為負(fù)數(shù)
SELECT DATEDIFF(DATE(NOW()),DATE_ADD(DATE(NOW()),interval 2 DAY))

數(shù)據(jù)清洗

SELECT LOCATE("k",salary),salary from dataanalyst
SELECT LEFT(salary, LOCATE("k",salary)-1),salary FROM dataanalyst
SELECT LEFT(salary,LOCATE("k",salary)-1) as bottomsalary,
    SUBSTR(salary,LOCATE("-",salary)+1,LENGTH(salary)-LOCATE("-",salary)-1) as topsalary,
    salary FROM dataanalyst
WHERE salary NOT LIKE '%以上%'

表相關(guān)操作

#創(chuàng)建表
CREATE TABLE newtable(
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 companyid INT(11) NOT NULL DEFAULT 0,
 companyname VARCHAR(600) NOT NULL DEFAULT '')
#查看表相關(guān)字段信息
-SHOW FULL COLUMNS from newtable
#在表中插入新值
INSERT INTO newtable VALUES(1,8581,'紐海信息技術(shù)(上海)有限公司')
#或者使用舊表直接創(chuàng)建新的表
CREATE table newtable(id INT) SELECT companyId, companyFullName FROM dataanalyst
limit 0,100

JOIN

JOIN dataanalyst as d
on d.companyId=n.companyid AND d.companyFullName=n.companyfullname
#其他join的方式：
#A left join B,B right join A
#A left join B where B.key is null
#A inner join B 
#A full outer join B
#A full outer join B where A.key is null or B.key is null
select * from newtable
LEFT JOIN dataanalyst 
WHERE dataanalyst.key is NULL