文章目录
echarts线性回归+聚类+直方
echarts线性回归
<!DOCTYPE html>
<html>
<head>
<meta charset='utf-8'>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<!-- <script src="../static/echarts.js"></script>-->
<script src="../static/esl.js"></script>
<!-- https://github.com/ecomfe/esl-->
<!-- esl.js是百度自己开发的AMD标准的javascript模块加载器，性能优于requireJS。-->
<!-- esl.js使用方式跟require.js几乎一毛一样。-->
<script src="../static/jquery-1.11.1.min.js"></script>
</head>
<body>
<style>
html, body, #main {
width: 100%;
height: 100%;
margin: 0;
padding: 0;
}
</style>
<div id="main"></div>
<script>
// 路径配置
require.config({
baseUrl:'../static', // 设置根目录为 ../static
paths: {
echarts: 'echarts', //注意实际路径,相当于echarts对应于scripts路径!，
// 相当于 ../static/echarts
ecStat: 'ecStat.min' // 注意不加 js 后缀
}
});
require([
'echarts',
'ecStat'
], function (echarts, ecStat) {
var chart = echarts.init(document.getElementById('main'));
$.getJSON('../data/regression_data0.json')
.done(function (data) {
var myRegression = ecStat.regression('linear', data);
// ('linear', data)
// linear 回归算法类型
// data 二维数组，每一个数组对象中包括 自变量和因变量
console.log("绘制折线图的拟合数据点 points")
console.log(myRegression.points)
console.log("回归曲线的参数 parameter")
console.log(myRegression.parameter)
console.log("拟合出的曲线表达式 expression")
console.log(myRegression.expression)
//myRegression.points[myRegression.points.length - 1]
console.log(myRegression.points[myRegression.points.length - 1])
console.log(myRegression.points.length - 1)
chart.setOption({
tooltip: {
trigger: 'axis',
axisPointer: { // 坐标轴指示器配置项
type: 'cross' // 十字准星指示器
}
},
xAxis: {
type: 'value',
splitLine: { // 坐标轴在 grid 区域中的分隔线。
lineStyle: {
type: 'dashed' // 分隔线线的类型。虚线
}
},
},
yAxis: {
type: 'value',
min: 1.5,
splitLine: {
lineStyle: {
type: 'dashed'
}
},
},
series: [{
name: 'scatter',
type: 'scatter',
label: {
emphasis: { // 高亮
show: true
}
},
data: data
},{
name: 'line',
type: 'line',
showSymbol: false, // false 随主轴标签间隔隐藏策略
data: myRegression.points,
// 其中 myRegression 对象中有三个属性
// 绘制折线图的拟合数据点 points
// 回归曲线的参数 parameter
// 拟合出的曲线表达式 expression
markPoint: {
itemStyle: {
normal: {
color: 'transparent'
}
},
label: {
normal: {
show: true,
formatter: myRegression.expression,
textStyle: {
color: '#333',
fontSize: 14
}
}
},
data: [{
coord: myRegression.points[myRegression.points.length - 1]
}]
}
}]
});
});
});
</script>
</body>
</html>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="../static/echarts.js"></script>
<script src="../static/ecStat.min.js"></script>
</head>
<body>
<div id="main" style="width: 600px;height:400px"></div>
<script>
var myecharts = echarts.init(document.getElementById("main"))
var data = [
[0.067732, 3.176513],
[0.42781, 3.816464],
[0.995731, 4.550095],
[0.738336, 4.256571],
[0.981083, 4.560815],
[0.526171, 3.929515],
[0.378887, 3.52617],
[0.033859, 3.156393],
[0.132791, 3.110301],
[0.138306, 3.149813],
[0.247809, 3.476346],
[0.64827, 4.119688],
[0.731209, 4.282233],
[0.236833, 3.486582],
[0.969788, 4.655492],
[0.607492, 3.965162],
[0.358622, 3.5149],
[0.147846, 3.125947],
[0.63782, 4.094115],
[0.230372, 3.476039],
[0.070237, 3.21061],
[0.067154, 3.190612],
[0.925577, 4.631504],
[0.717733, 4.29589],
[0.015371, 3.085028],
[0.33507, 3.44808],
[0.040486, 3.16744],
[0.212575, 3.364266],
[0.617218, 3.993482],
[0.541196, 3.891471],
[0.045353, 3.143259],
[0.126762, 3.114204],
[0.556486, 3.851484],
[0.901144, 4.621899],
[0.958476, 4.580768],
[0.274561, 3.620992],
[0.394396, 3.580501],
[0.87248, 4.618706],
[0.409932, 3.676867],
[0.908969, 4.641845],
[0.166819, 3.175939],
[0.665016, 4.26498],
[0.263727, 3.558448],
[0.231214, 3.436632],
[0.552928, 3.831052],
[0.047744, 3.182853],
[0.365746, 3.498906],
[0.495002, 3.946833],
[0.493466, 3.900583],
[0.792101, 4.238522],
[0.76966, 4.23308],
[0.251821, 3.521557],
[0.181951, 3.203344],
[0.808177, 4.278105],
[0.334116, 3.555705],
[0.33863, 3.502661],
[0.452584, 3.859776],
[0.69477, 4.275956],
[0.590902, 3.916191],
[0.307928, 3.587961],
[0.148364, 3.183004],
[0.70218, 4.225236],
[0.721544, 4.231083],
[0.666886, 4.240544],
[0.124931, 3.222372],
[0.618286, 4.021445],
[0.381086, 3.567479],
[0.385643, 3.56258],
[0.777175, 4.262059],
[0.116089, 3.208813],
[0.115487, 3.169825],
[0.66351, 4.193949],
[0.254884, 3.491678],
[0.993888, 4.533306],
[0.295434, 3.550108],
[0.952523, 4.636427],
[0.307047, 3.557078],
[0.277261, 3.552874],
[0.279101, 3.494159],
[0.175724, 3.206828],
[0.156383, 3.195266],
[0.733165, 4.221292],
[0.848142, 4.413372],
[0.771184, 4.184347],
[0.429492, 3.742878],
[0.162176, 3.201878],
[0.917064, 4.648964],
[0.315044, 3.510117],
[0.201473, 3.274434],
[0.297038, 3.579622],
[0.336647, 3.489244],
[0.666109, 4.237386],
[0.583888, 3.913749],
[0.085031, 3.22899],
[0.687006, 4.286286],
[0.949655, 4.628614],
[0.189912, 3.239536],
[0.844027, 4.457997],
[0.333288, 3.513384],
[0.427035, 3.729674],
[0.466369, 3.834274],
[0.550659, 3.811155],
[0.278213, 3.598316],
[0.918769, 4.692514],
[0.886555, 4.604859],
[0.569488, 3.864912],
[0.066379, 3.184236],
[0.335751, 3.500796],
[0.426863, 3.743365],
[0.395746, 3.622905],
[0.694221, 4.310796],
[0.27276, 3.583357],
[0.503495, 3.901852],
[0.067119, 3.233521],
[0.038326, 3.105266],
[0.599122, 3.865544],
[0.947054, 4.628625],
[0.671279, 4.231213],
[0.434811, 3.791149],
[0.509381, 3.968271],
[0.749442, 4.25391],
[0.058014, 3.19471],
[0.482978, 3.996503],
[0.466776, 3.904358],
[0.357767, 3.503976],
[0.949123, 4.557545],
[0.41732, 3.699876],
[0.920461, 4.613614],
[0.156433, 3.140401],
[0.656662, 4.206717],
[0.616418, 3.969524],
[0.853428, 4.476096],
[0.133295, 3.136528],
[0.693007, 4.279071],
[0.178449, 3.200603],
[0.199526, 3.299012],
[0.073224, 3.209873],
[0.286515, 3.632942],
[0.182026, 3.248361],
[0.621523, 3.995783],
[0.344584, 3.563262],
[0.398556, 3.649712],
[0.480369, 3.951845],
[0.15335, 3.145031],
[0.171846, 3.181577],
[0.867082, 4.637087],
[0.223855, 3.404964],
[0.528301, 3.873188],
[0.890192, 4.633648],
[0.106352, 3.154768],
[0.917886, 4.623637],
[0.014855, 3.078132],
[0.567682, 3.913596],
[0.068854, 3.221817],
[0.603535, 3.938071],
[0.53205, 3.880822],
[0.651362, 4.176436],
[0.901225, 4.648161],
[0.204337, 3.332312],
[0.696081, 4.240614],
[0.963924, 4.532224],
[0.98139, 4.557105],
[0.987911, 4.610072],
[0.990947, 4.636569],
[0.736021, 4.229813],
[0.253574, 3.50086],
[0.674722, 4.245514],
[0.939368, 4.605182],
[0.235419, 3.45434],
[0.110521, 3.180775],
[0.218023, 3.38082],
[0.869778, 4.56502],
[0.19683, 3.279973],
[0.958178, 4.554241],
[0.972673, 4.63352],
[0.745797, 4.281037],
[0.445674, 3.844426],
[0.470557, 3.891601],
[0.549236, 3.849728],
[0.335691, 3.492215],
[0.884739, 4.592374],
[0.918916, 4.632025],
[0.441815, 3.75675],
[0.116598, 3.133555],
[0.359274, 3.567919],
[0.814811, 4.363382],
[0.387125, 3.560165],
[0.982243, 4.564305],
[0.78088, 4.215055],
[0.652565, 4.174999],
[0.87003, 4.58664],
[0.604755, 3.960008],
[0.255212, 3.529963],
[0.730546, 4.213412],
[0.493829, 3.908685],
[0.257017, 3.585821],
[0.833735, 4.374394],
[0.070095, 3.213817],
[0.52707, 3.952681],
[0.116163, 3.129283]
];
// See https://github.com/ecomfe/echarts-stat
var myRegression = ecStat.regression('linear', data);
myRegression.points.sort(function(a, b) {
return a[0] - b[0];
});
option = {
title: {
text: 'Linear Regression',
subtext: 'By ecStat.regression',
sublink: 'https://github.com/ecomfe/echarts-stat',
left: 'center'
},
tooltip: {
trigger: 'axis',
axisPointer: {
type: 'cross'
}
},
xAxis: {
type: 'value',
splitLine: {
lineStyle: {
type: 'dashed'
}
},
},
yAxis: {
type: 'value',
min: 1.5,
splitLine: {
lineStyle: {
type: 'dashed'
}
},
},
series: [{
name: 'scatter',
type: 'scatter',
emphasis: {
label: {
show: true,
position: 'left',
color: 'blue',
fontSize: 16
}
},
data: data
}, {
name: 'line',
type: 'line',
showSymbol: false,
data: myRegression.points,
markPoint: {
itemStyle: {
color: 'transparent'
},
label: {
show: true,
position: 'left',
formatter: myRegression.expression,
color: '#333',
fontSize: 14
},
data: [{
coord: myRegression.points[myRegression.points.length - 1]
}]
}
}]
};
myecharts.setOption(option)
</script>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<meta charset='utf-8'>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<script src="../static/esl.js"></script>
<!-- https://github.com/ecomfe/esl-->
<!-- esl.js是百度自己开发的AMD标准的javascript模块加载器，性能优于requireJS。-->
<!-- esl.js使用方式跟require.js几乎一毛一样。-->
<script src="../static/jquery-1.11.1.min.js"></script>
</head>
<body>
<style>
html, body, #main {
width: 100%;
height: 100%;
margin: 0;
padding: 0;
}
</style>
<div id="main"></div>
<script>
// 路径配置
require.config({
baseUrl:'../static', // 设置根目录为 ../static
paths: {
echarts: 'echarts', //注意实际路径,相当于echarts对应于scripts路径!，
// 相当于 ../static/echarts
ecStat: 'ecStat.min' // 注意不加 js 后缀
}
});
require([
'echarts',
'ecStat'
], function (echarts, ecStat) {
var chart = echarts.init(document.getElementById('main'));
$.getJSON('../data/clustering_dataset2.json')
.done(function (data) {
var clusterNumber = 6;
var step = ecStat.clustering.hierarchicalKMeans(data, clusterNumber, true);
// data 加载的数据
// clusterNumber 要生成的数据簇的个数
// 是否动态地展示数据簇的聚类过程
var result;
var option = {
timeline: {
top: 'center',
right: 50,
height: 300,
width: 10,
inverse: true,
playInterval: 2500, // 表示播放的速度（跳动的间隔），单位毫秒（ms）
symbol: 'none',
orient: 'vertical',
axisType: 'category',
autoPlay: true,
label: {normal: {show: false}},
data: []
},
baseOption: {
xAxis: {
type: 'value'
},
yAxis: {
type: 'value'
},
series: [{
type: 'scatter'
}]
},
options: []
};
for (var i = 0; !(result = step.next()).isEnd; i++) {
option.options.push(getOption(result, clusterNumber));
option.timeline.data.push(i + '');
}
chart.setOption(option);
function getOption(result, k) {
var clusterAssment = result.clusterAssment;
var centroids = result.centroids;
var ptsInCluster = result.pointsInCluster;
var color = ['#c23531','#2f4554', '#61a0a8', '#d48265', '#91c7ae','#749f83', '#ca8622', '#bda29a','#6e7074', '#546570', '#c4ccd3'];
var series = [];
for (i = 0; i < k; i++) {
series.push({
name: 'scatter' + i,
type: 'scatter',
label: {
emphasis: {
show: true
}
},
animation: false,
data: ptsInCluster[i],
markPoint: {
symbolSize: 29,
label: {
normal: {
show: false
},
emphasis: {
show: true,
position: 'top',
formatter: function (params) {
return Math.round(params.data.coord[0] * 100) / 100 + ' '
+ Math.round(params.data.coord[1] * 100) / 100 + ' ';
},
textStyle: {
color: '#000'
}
}
},
itemStyle: {
normal: {
opacity: 0.7
}
},
data: [{
coord: centroids[i]
}]
}
});
}
return {
tooltip: {
trigger: 'axis',
axisPointer: {
type: 'cross'
}
},
series: series,
color: color
};
}
});
});
</script>
</body>
</html>
sqlalchemy总结
sqlalchemy多表连接查询
内连接和我们平时所用的where语句效果一致，即两个表的共同的部分
外连接包括（左连接、右连接)
左连接，即已左边的表为主表，右边的表为副表，将主表中需要的字段全部列出，然后将副表中的数据按照查询条件与其对应起来，
右连接则相反。
全连接，则是将两个表的需要的字段的数据全排列。
在sqlalchemy中 join() 函数默认的是内连接
其中 左外连接可以是 outerjoin()
也可以设置 join() 中的参数，将其改为左连接，即 join(isouter = True) ,
salalchemy中没有右连接，可以在左连接的时候将查询的表顺序换一下，模仿右连接
#1. 查询所有的用户 + 所属部门名称
ret1 = session.query(Users,Depart).join(Depart).all() # .join() 时 默认的 on 是根据 ForeignKey("depart.id")，进行 on users.depart_id = depart.id （默认是通过 ForeignKey 进行连表）
for row in ret1: # 此时 row 为 一个元组，里面的元素为 Users 和 Depart 的对象
print(row[0].name,row[1].title)
"""
ret1 = session.query(Users,Depart).join(Depart).all() 中的 .join() 也可以指定 on ，如下：
ret1 = session.query(Users,Depart).join(Depart,Users.depart_id == Depart.id).all()
"""
.join()
.join(isouter=True) 左连接
sqlalchemy中没有右连接，右连接通过改变查询表的顺序实现
"""
.join() 默认是 inner join，想要变成 left join 可以在 join()中设置 isouter=True ，如下：
session.query(Users.id,Users.name,Depart.title).join(Depart,Users.depart_id==Depart.id,isouter=True).all()
另外，SQLAlchemy 的 join() 没有 right join，想要 right join 可以在 query() 中将 Users 和 Depart 调换下位置
注： .join() 后面可以继续 .join() ， 即可以 连很多张表
"""
SQLAlchemy（2）：多表操作 & 连接方式及原生SQL
data = db.session.query(func.round((marttype.sales-marttype.cost)/marttype.cost,3).label('rate'),
marttype.type, region.city).join(store, store.marttype_id==marttype.id).join(
region, store.region_id==region.id
).filter(marttype.type=='a').all()
u = session.query(User).join(Address).\
filter(Address.email_address=='jack@google.com').\
one()
print(u)
# 执行结果
jack
sqlalchemy多表联合查询(inner outer join 左右连接)详解
from data.user_modules import User,session,UserDetails
#带条件查询
raw=session.query(User).filter_by(username='nanian').all()
raw=session.query(User).filter_by(username='nanian') #去掉.all()原生sql
raw=session.query(User).filter(User.username =='nanian').all()
raw=session.query(User.username).filter(User.username !='nanian').all()
raw=session.query(User.username).filter(User.username !='nanian').first()
raw=session.query(User.username).filter(User.username !='nanian').one() #如果前面查出的是多条数据则报错
print(session.query(User).get(2)) #根据主键查，会自己找主键
print(raw)
#限制查询结果数
print(session.query(User).filter(User.username!='nanian').limit(3).all())#前三行
print(session.query(User).filter(User.username!='nanian').offset(3).all())#第三行以后
print(session.query(User).filter(User.username!='nanian').slice(1,3).all())#2,3行
#排序
from sqlalchemy import desc
raw=session.query(User).filter(User.username !='nanian').order_by(User.username).all()
raw=session.query(User).filter(User.username !='nanian').order_by(desc(User.username).all()#逆序
#模糊查询 尽量少用模糊查询，效率低
from sqlalchemy import or_
raw=session.query(User).filter(User.username!='nanian').all()
raw=session.query(User).filter(User.username.like('n%').all()
raw=session.query(User).filter(User.username.notlike('n%').all()
raw=session.query(User).filter(User.username.in_(['nanian','a']).all()) #加下划线表示和python关键字作区分
raw=session.query(User).filter(User.username.isnot(None),User.passwd=='123').all()) #多条件
raw=session.query(User).filter(or_(User.username.isnot(None),User.passwd=='123')).all()) #或
raw=session.query(User).filter(User.username==None).all())
#聚合函数
from sqlalchemy import func,extract
print(session.query(User.passwd,func.count(User.id)).group_by(User.passwd).all())
print(session.query(User.passwd,func.count(User.id)).group_by(User.passwd).\
having(func.count(User.id)>1) all())
print( session.query(extract('minute',User.createtime).label('minute'),\
func.count(User.id)).group_by('minute').all() ) #提取分钟，按分钟分组
#多表查询
raw=session.query(User,UserDetails).all()
raw=session.query(User,UserDetails).filter(UserDetails.id==User.id) all()# cross join
raw=session.query(User.username,UserDetails.last_login).\
join(UserDetails, UserDetails.id==User.id) all()# inner join
raw=session.query(User.username,UserDetails.last_login).\
outerjoin(UserDetails, UserDetails.id==User.id) all()
# outer join代表left join 左连接，右连接将表反过来(sqlalchemy没有rightjoin),小表左连接右表效率高
q1=session.query(User.id)
q2=session.query(UserDetails.id)
raw=q1.union(q2).all()
from sqlalchemy import all_,any_
sql_0=session.query(UserDetails.last_login).subquery() #声明子表
raw=session.query(User).filter(User.createtime >all_(sql_0)).all()
raw=session.query(User).filter(User.createtime >any_(sql_0)).all()
#原生sql
sql_1='''
select * from `user`
'''
raw=session.execute(sql_1)
#print(raw,dir(raw))
#print(raw.fetchone())
#print(raw.fetchmany())
#print(raw.fetchall())
for i in raw:
print(i)
query.py
join() 和 join_from()
join()
stmt = select(user_table).join(addresses_table, user_table.c.id == addresses_table.c.user_id)
sql
SELECT user.id, user.name FROM user JOIN address ON user.id=address.user_id
另一种新方法 Select.join_from() 也添加了，这样可以同时更方便地指定连接的左侧和右侧：
stmt = select(Address.email_address, User.name).join_from(User, Address)
sql
SELECT address.email_address, user.name FROM user JOIN address ON user.id == address.user_id
aliased()
上述语法对于我们希望连接到特定目标实体的别名的情况非常有用。如果我们想加入 Address 两次，可以通过使用 aliased() 功能：
a1 = aliased(Address)
a2 = aliased(Address)
q = session.query(User).\
join(a1, User.addresses).\
join(a2, User.addresses).\
filter(a1.email_address=='ed@foo.com').\
filter(a2.email_address=='ed@bar.com')
sqlalchemy的子查询subquery()
从SQL的角度来看，子查询是一个带圆括号的命名构造，它可以放在另一个SELECT语句的from子句中。
给出了如下select语句：
stmt = select(table.c.id, table.c.name)
上面的语句可能如下所示：
SELECT table.id, table.name FROM table
子查询表单本身呈现的方式相同，但是当嵌入到另一个select语句的from子句中时，它将成为一个命名的子元素：：
subq = stmt.subquery()
new_stmt = select(subq)
上面呈现为：
SELECT anon_1.id, anon_1.name
FROM (SELECT table.id, table.name FROM table) AS anon_1
子查询即select语句中还有select。
那么在sqlalchemy中，要实现一个子查询，需以下几个步骤：
将子查询按照传统的方式写好查询代码，然后在query对象后面执行subquery方法，将这个查询变成一个子查询。
在子查询中，将以后需要用到的字段通过label方法，取个别名。
在父查询中，如果想要使用子查询的字段，那么可以通过子查询的返回值上的c属性拿到（c=Column）。
stmt = session.query(User.city.label('city'),\
User.age.label('age'))\
.filter(User.uname == '一哥').subquery()
result = session.query(User)\
.filter(User.city == stmt.c.city, User.age == stmt.c.age).all()
sqlalchemy查询日期格式的数据
sqlalchemy中 extract() 函数
这个方法要使用extract函数，这个函数可以从datetime字段中分解出年月。不过在flask_sqlalchemy中没有extract，所以只能从sqlalchemy包导入。
# 基于Flask的SQLAlchemy
# models
class History(db.Model):
__tablename__ = 'historys'
id = db.Column(db.Integer, primary_key=True)
date= db.Column(db.Date)
from sqlalchemy import extract
之后只需要这么写，就可以获取某月份的所有数据了
#获取12月份的所有数据
historys = History.query.filter(extract('month', History.date) == 12).all()
但是用上面的查询会把往年的12月也查询出来，那么就加上年份的查询
from sqlalchemy import extract, and_
historys = History.query.filter(and_(
extract('year', History.date) == 2016,
extract('month', History.date) == 12
).all()
sqlalchemy按DateTime字段的年或月进行group_by查询
【Flask】SQLAlchemy中从datetime字段获取某月份的数据
sql中的extract()函数
EXTRACT()("提取"的意思) 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
就是返回出来具体的年，月，日
select extract(year from shen_qing_shi_jian) as shen_year,
extract(month from shen_qing_shi_jian) as shen_month,
extract(day from shen_qing_shi_jian) as shen_day
from test_time_csv;
结果
mysql中的时间处理函数
MySQL之日期时间处理函数
MySQL 时间函数加减计算
SQL 日期和时间处理函数
use db1;
desc test_time_csv;
select * from guo_sai_4;
select extract(year from shen_qing_shi_jian) as shen_year,
extract(month from shen_qing_shi_jian) as shen_month,
extract(day from shen_qing_shi_jian) as shen_day
from test_time_csv;
查询时间在 2020-10-18 这一天的餐厅名称
SELECT can_ting_name, shen_qing_shi_jian
From test_time_csv
Where Date(shen_qing_shi_jian) = '2020-10-18'; date
查询时间在 2020-10 这一个月的餐厅名称
SELECT can_ting_name, shen_qing_shi_jian, Year(shen_qing_shi_jian), Month(shen_qing_shi_jian)
From test_time_csv
Where Year(shen_qing_shi_jian) = 2020 AND Month(shen_qing_shi_jian) = 10;
查询时间在 2020-10 这一个年的餐厅名称
SELECT can_ting_name, shen_qing_shi_jian, Year(shen_qing_shi_jian), Month(shen_qing_shi_jian)
From test_time_csv
Where Year(shen_qing_shi_jian) = 2020;
日期相减
DATEDIFF()
datediff(date1,date2)：两个日期相减 date1 – date2，返回天数。
timediff()
timediff(time1,time2)：两个日期相减 time1 – time2，返回 time 差值。
select can_ting_name, shen_qing_shi_jian, chuang_jian_shi_jian,DATEDIFF(shen_qing_shi_jian,chuang_jian_shi_jian) from test_time_csv;
select can_ting_name, shen_qing_shi_jian, chuang_jian_shi_jian,timediff(shen_qing_shi_jian,chuang_jian_shi_jian) from test_time_csv;
查询是星期几 curdate()返回当前日期
select date_format(curdate(shen_qing_shi_jian),'%w') from test_time_csv;
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。 WEEKDAY();
select WEEKDAY(shen_qing_shi_jian) from test_time_csv;
mysql多表查询
交集 ： SELECT w.name, a.count, a.date FROM websites w, access_log a WHERE w.id = a.site_id;
内连接：
inner Join 和 join相同
SELECT w.name, a.count, a.date FROM websites w INNER JOIN access_log a ON w.id = a.site_id;
left join 左连接：
LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL
SELECT w.name, a.count, a.date FROM websites w LEFT JOIN access_log a ON w.id = a.site_id;
right join 右链接
RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。
SELECT Websites.name, access_log.count, access_log.date
FROM access_log
RIGHT JOIN Websites
ON access_log.site_id=Websites.id;
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（这个不讲，很少用！）
内连接之等值连接
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
-- inner 可以省略
也可以写为
select
e.ename,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
内连接之非等值连接
查询在s.losal和s.hisal区间中的员工信息
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
2.6、内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系。
案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。
mysql> select ename,sal from emp; e
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
mysql> select * from salgrade; s
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
// inner可以省略
select
e.ename,e.sal,s.grade
from
emp e
inner join
salgrade s
on
e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
内连接之自连接
自连接：最大的特点是：一张表看做两张表。自己连接自己。
select
a.ename as '员工名',b.ename as '领导名'
from
emp a
inner join
emp b
on
a.mgr = b.empno;
2.7、自连接：最大的特点是：一张表看做两张表。自己连接自己。
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。
mysql> select empno,ename,mgr from emp;
emp a 员工表
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
emp b 领导表
+-------+--------+
| empno | ename |
+-------+--------+
| 7566 | JONES |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7902 | FORD |
+-------+--------+
员工的领导编号 = 领导的员工编号
select
a.ename as '员工名',b.ename as '领导名'
from
emp a
inner join
emp b
on
a.mgr = b.empno;
+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------
2.8、外连接？
什么是外连接，和内连接有什么区别？
内连接：inner
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
AB两张表没有主副之分，两张表是平等的。
外连接：outer
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中
的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
外连接的分类？
左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。
左连接有右连接的写法，右连接也会有对应的左连接的写法。
select a.ename '员工', b.ename '领导' from emp a left join emp b on a.mgr = b.empno;
如上语句：left join 左边的是主表，left join右边的是副表，主要是查询左边的主表
select a.ename '员工', b.ename '领导' from emp a right join emp b on a.mgr = b.empno;
如上语句： right join 右边的是主表，right join左边的是副表，主要是查询右边的主表
案例：找出每个员工的上级领导？（所有员工必须全部查询出来。）
emp a 员工表
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
emp b 领导表
+-------+--------+
| empno | ename |
+-------+--------+
| 7566 | JONES |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7902 | FORD |
+-------+--------+
内连接：
select
a.ename '员工', b.ename '领导'
from
emp a
join
emp b
on
a.mgr = b.empno;
外连接：（左外连接/左连接）
select
a.ename '员工', b.ename '领导'
from
emp a
left join
emp b
on
a.mgr = b.empno;
// outer是可以省略的。
select
a.ename '员工', b.ename '领导'
from
emp a
left outer join
emp b
on
a.mgr = b.empno;
外连接：（右外连接/右连接）
select
a.ename '员工', b.ename '领导'
from
emp b
right join
emp a
on
a.mgr = b.empno;
// outer可以省略。
select
a.ename '员工', b.ename '领导'
from
emp b
right outer join
emp a
on
a.mgr = b.empno;
+--------+-------+
| 员工 | 领导 |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
外连接最重要的特点是：主表的数据无条件的全部查询出来。
2.9、三张表怎么连接查询？
案例：找出每一个员工的部门名称以及工资等级。
EMP e
+-------+--------+---------+--------+
| empno | ename | sal | deptno |
+-------+--------+---------+--------+
| 7369 | SMITH | 800.00 | 20 |
| 7499 | ALLEN | 1600.00 | 30 |
| 7521 | WARD | 1250.00 | 30 |
| 7566 | JONES | 2975.00 | 20 |
| 7654 | MARTIN | 1250.00 | 30 |
| 7698 | BLAKE | 2850.00 | 30 |
| 7782 | CLARK | 2450.00 | 10 |
| 7788 | SCOTT | 3000.00 | 20 |
| 7839 | KING | 5000.00 | 10 |
| 7844 | TURNER | 1500.00 | 30 |
| 7876 | ADAMS | 1100.00 | 20 |
| 7900 | JAMES | 950.00 | 30 |
| 7902 | FORD | 3000.00 | 20 |
| 7934 | MILLER | 1300.00 | 10 |
+-------+--------+---------+--------+
DEPT d
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
SALGRADE s
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
注意，解释一下：
....
A
join
B
join
C
on
...
表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。
select
e.ename,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal;
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+
案例：找出每一个员工的部门名称、工资等级、以及上级领导。
select
e.ename '员工',d.dname,s.grade,e1.ename '领导'
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e1
on
e.mgr = e1.empno;
+--------+------------+-------+-------+
| 员工 | dname | grade | 领导 |
+--------+------------+-------+-------+
| SMITH | RESEARCH | 1 | FORD |
| ALLEN | SALES | 3 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| JONES | RESEARCH | 4 | KING |
| MARTIN | SALES | 2 | BLAKE |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
| TURNER | SALES | 3 | BLAKE |
| ADAMS | RESEARCH | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| FORD | RESEARCH | 4 | JONES |
| MILLER | ACCOUNTING | 2 | CLARK |
+--------+------------+-------+-------+
3.、子查询
3.1、什么是子查询？子查询都可以出现在哪里？
select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？
select
..(select).
from
..(select).
where
..(select).
3.2、where子句中使用子查询
案例：找出高于平均薪资的员工信息。
select * from emp where sal > avg(sal); //错误的写法，where后面不能直接使用分组函数。
第一步：找出平均薪资
select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
第二步：where过滤
select * from emp where sal > 2073.214286;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
第一步和第二步合并：
select * from emp where sal > (select avg(sal) from emp);
3.3、from后面嵌套子查询
案例：找出每个部门平均薪水的等级。
第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
+--------+-------------+-------+
案例：找出每个部门平均的薪水等级。
第一步：找出每个员工的薪水等级。
select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+--------+-------+
| ename | sal | deptno | grade |
+--------+---------+--------+-------+
| SMITH | 800.00 | 20 | 1 |
| ALLEN | 1600.00 | 30 | 3 |
| WARD | 1250.00 | 30 | 2 |
| JONES | 2975.00 | 20 | 4 |
| MARTIN | 1250.00 | 30 | 2 |
| BLAKE | 2850.00 | 30 | 4 |
| CLARK | 2450.00 | 10 | 4 |
| SCOTT | 3000.00 | 20 | 4 |
| KING | 5000.00 | 10 | 5 |
| TURNER | 1500.00 | 30 | 3 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| FORD | 3000.00 | 20 | 4 |
| MILLER | 1300.00 | 10 | 2 |
+--------+---------+--------+-------+
第二步：基于以上结果，继续按照deptno分组，求grade平均值。
select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
3.4、在select后面嵌套子查询。
案例：找出每个员工所在的部门名称，要求显示员工名和部门名。
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
select
e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
4、union （可以将查询结果集相加）
案例：找出工作岗位是SALESMAN和MANAGER的员工？
第一种：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种：select ename,job from emp where job in('MANAGER','SALESMAN');
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
第三种：union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename | job |
+--------+----------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
两张不相干的表中的数据拼接在一起显示？
select ename from emp
union
select dname from dept;
+------------+
| ename |
+------------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
mysql> select ename,sal from emp
-> union
-> select dname from dept; //拼接的数据必须两两相对应。不能一张表是一个数据，另一张表是两个数据，这样无法拼接！
ERROR 1222 (21000): The used SELECT statements have a different number of columns
5、limit (重点中的重点，以后分页查询全靠它了。)
5.1、limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）
5.2、limit取结果集中的部分数据，这时它的作用。
5.3、语法机制：
limit startIndex, length
startIndex表示起始位置，从0开始，0表示第一条数据。
length表示取几个
案例：取出工资前5名的员工（思路：降序取前5个）
select ename,sal from emp order by sal desc;
取前5个：
select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5;
5.4、limit是sql语句最后执行的一个环节：
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
limit 7
...;
5.5、案例：找出工资排名在第4到第9名的员工？
select ename,sal from emp order by sal desc limit 3,6;
+--------+---------+
| ename | sal |
+--------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
5.6、通用的标准分页sql？
每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3
每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize, pageSize
pageSize是什么？是每页显示多少条记录
pageNo是什么？显示第几页
java代码{
int pageNo = 2; // 页码是2
int pageSize = 10; // 每页显示10条
limit (pageNo - 1) * pageSize, pageSize
}
扫一扫
477
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
