查询
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
