常用的sql
1.查询一班得分在80分以上或者等于60,61,62的学生
select s.s_id, s.name, s.score, sc.c_id, c.c_name from student s
left join student_class sc on s.s_id = sc.s_id
left join class c on sc.c_id = c.cid
where (s.score>80) or s.score in (60,61,62) and c.c_name = ‘一班’
2.査询所有班级的名称,和所有版中女生人数和女生的平均分。
select s.s_id, s.name, AVG(s.score), count(s.sex)from student s
left join student_class sc on s.s_id = sc.s_id
left join class c on sc.c_id = c.cid
where (s.sex=‘女’) group by c.c_name order by c.c_id desc/asc
返回记录条数的实现:
select * from person limit 5
Select * from person rownum<=5
Select TOP 5 * from person
关联表
JOIN: 如果表中有至少一个匹配,则返回行
LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配,就返回行
INNER JOIN(内连接)
执行顺序:where>聚合函数(sum,min,max,avg,count)>having,故where不使用聚合函数。
SELECT job_type
FROM job_task having task_id
>1252217
Unknown column ‘task_id’ in ‘having clause’
SELECT job_type
,task_id FROM job_task having task_id
>1252217 加上task_id后解决
构造几何
ST_GeomFromText 根据字符串表示构造几何
ST_GeomFromText(‘POINT(-122.34900 47.65100)’, 4326)ST_GeomFromText(‘MultiLineString((113.39607238769531 34.19425964355469,113.85887145996094 34.12559509277344))’, 4326)ST_GeomFromText( ‘LineString( 1 2, 5 7 )’, 4326 )
通配符