查询

SELECT city FROM dataanalyst
WHERE city = '上海' and (positionname = '数据分析师' OR positionname = '数据产品经理')

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

SELECT * FROM dataanalyst
WHERE companyId BETWEEN 10000 and 20000
#使用between和and时，包括边界值

SELECT * FROM dataanalyst
WHERE positionName LIKE '%数据分析%'
#%是通配符

SELECT * FROM dataanalyst
WHERE city NOT IN ('上海')
#上面的几种查询语句都可以加上not

分组

GROUP BY city

SELECT city,count(1) FROM dataanalyst
group by city
#count(1)表示对第一列计数

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等函数都可以在此使用

筛选

SELECT city,
            COUNT(DISTINCT positionid),
            COUNT(DISTINCT IF(industryfield LIKE '%电子商务%',positionid,NULL))
FROM dataanalyst
GROUP BY city
#NULL会被当成空值，不会被计数

#对数据进行筛选1
SELECT city,count(DISTINCT positionid) FROM dataanalyst
GROUP BY city 
HAVING COUNT(DISTINCT positionId)>=500

#对数据进行筛选2
SELECT city,count(DISTINCT positionid) AS counts FROM dataanalyst
GROUP BY city 
HAVING counts>=500
#having是对查询后的结果进行筛选，所取变量是查询后的表中的；
#而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的话是倒序

时间

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都可以，减的话改为负数
SELECT DATEDIFF(DATE(NOW()),DATE_ADD(DATE(NOW()),interval 2 DAY))

数据清洗

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 '%以上%'

表相关操作

#创建表
CREATE TABLE newtable(
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 companyid INT(11) NOT NULL DEFAULT 0,
 companyname VARCHAR(600) NOT NULL DEFAULT '')
#查看表相关字段信息
-SHOW FULL COLUMNS from newtable
#在表中插入新值
INSERT INTO newtable VALUES(1,8581,'纽海信息技术(上海)有限公司')
#或者使用旧表直接创建新的表
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