admin管理员组文章数量:1558102
SQL语法
SQL添加
INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
SQL查询
学SQL网站
数字条件查询
SELECT col,col,col
找什么?
FROM table
从哪找?
WHERE col
条件 条件是啥?
select * from table where col = 1;
SELECT * FROM movies WHERE year=2010;
SELECT id,title,director FROM movies WHERE year=2010;
//查找其中某几列
SELECT * FROM movies WHERE year between 2000 and 2010;
SELECT * FROM movies WHERE year in (2001,2002,2010);
条件查找
SELECT * FROM movies WHERE year>=2005 or year<=2004;
文本条件查询
select * from table where col like '%jin'
必须针对字符串对象进行查找
SELECT * FROM movies WHERE year like 2005;
year不是字符串所以什么都查不到
SELECT * FROM movies WHERE Title like 'Cars';
SELECT * FROM movies WHERE Title like '%ar%';
SELECT * FROM movies WHERE Title like 'Cars_%';
SELECT * FROM movies where YEAR=1998 and title like '%B%'
数字条件与字符条件同时使用 and
查找结果排序
需要对结果rows排序和筛选部分rows
select * from table where col > 1 order by col asc limit 2 offset 2
SELECT * FROM movies WHERE year between 1999 and 2009 order by Length_minutes
SELECT * FROM movies WHERE year between 1999 and 2009 order by Length_minutes desc
例如select * from article LIMIT 3 OFFSET 1 表示跳过1条数据,从第2条数据开始取,取3条数据,也就是取2,3,4三条数据
SELECT * FROM movies WHERE year between 1999 and 2009 order by Length_minutes desc
limit 200 offset 1
SELECT * FROM movies WHERE year between 1999 and 2009 order by Length_minutes desc
limit 200 offset 2;
SELECT * FROM movies WHERE year between 1999 and 2009 order by Length_minutes desc
limit 200 offset 3;
排重
SELECT director FROM movies ORDER BY director
有重复导演
SELECT director FROM movies GROUP BY director
复杂查询,先根据导演名字进行选择,然后再对结果进行排序
SELECT title FROM movies where director like '%jo%' order by length_minutes limit 1 offset 2
多表链接
当查找的数据在多张关联table里
select * from table1 left join table2 on table1.id = table2.id where col > 1
可以直观的看出inner为只保留中间的C,left保留左半部分,right保留右半部分,全连接就是全都保存
【联表】找到所有电影的国内Domestic_sales和国际销售额 ✓
SELECT * FROM movies LEFT JOIN Boxoffice ON MOVIES.ID=Boxoffice.MOVIE_ID
相当于生成了一个新表
SELECT Domestic_sales FROM movies LEFT JOIN Boxoffice ON MOVIES.ID=Boxoffice.MOVIE_ID
相当于再新表中进行了筛选
--请输入sql
SELECT * FROM movies LEFT JOIN Boxoffice ON MOVIES.ID=Boxoffice.MOVIE_ID where International_sales>Domestic_sales
外连接
【复习】找到所有有雇员的办公室(buildings)名字 ✓
SELECT BUILDING FROM employees join Buildings on employees.Building=Buildings.Building_name group by Building
【复习】找到所有办公室里的所有角色(包含没有雇员的),并做唯一输出(DISTINCT) ✓
SELECT Building_name,role FROM BUILDINGS left JOIN EMPLOYEES ON buildings.Building_name=Employees.Building group by role,building_name
这个group by多列其实也挺好理解的 相当于把两列都作为状态了
如果不写链接条件,则相当于进行简单的排列组合
SELECT * FROM Buildings left join employees
对于NULL的处理
在查询条件中处理 NULL
SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;
在查询中使用表达式
相当于将表达式所得的行做一个别名,相当于插入了一列
SELECT particle_speed / 2.0 AS half_particle_speed (对结果做了一个除2)
FROM physics_data
WHERE ABS(particle_position) * 10.0 >500
(条件要求这个属性绝对值乘以10大于500);
--请输入sql
SELECT id, title,(Domestic_sales+International_sales)/1000000 FROM movies left join Boxoffice on movies.id=Boxoffice.movie_id
筛选所有偶数电影年份
--请输入sql
SELECT id, title,year FROM movies left join Boxoffice on movies.id=Boxoffice.movie_id where year%2==0
【难题】John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以 ✓
SELECT title, (Domestic_sales+International_sales)/Length_minutes as val
FROM movies left join Boxoffice on movies.id=Boxoffice.movie_id
where director like '%joh%' order by val desc limit 3;
对表进行统计
对全部结果数据做统计
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression;
用分组的方式统计
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column;
使用Group by,列表中有几年,就返回几条数据
SELECT * FROM employees group by Years_employed
Group by的用处不在于直接分组 在于分组统计
--请输入sql
SELECT Years_employed,count(Years_employed) FROM employees group by Years_employed
根据年数进行分组计数
统计每个角色的平均年限:
先根据角色进行分组,再再组内进行均值统计
--请输入sql
SELECT role,avg(Years_employed) FROM employees group by role
【难题】每栋办公室按人数排名,不要统计无办公室的雇员 ✓
SELECT building,count(name) FROM employees where building is not null group by Building
SQL语句执行顺序:
1st) FROM字句:执行顺序为从后往前、从右到左。数据量较大的表尽量放在后面。
2nd) WHERE字句:执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE字句的最右。
3rd) GROUP BY:执行顺序从右往左分组,最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉
4th) HAVING字句:消耗资源。尽量避免使用,HAVING会在检索出所有记录之后才对结果进行过滤,需要排序等操作。
5th) SELECT字句:少用号,尽量使用字段名称,oracle在解析的过程中,通过查询数据字典将号依次转换成所有列名,消耗时间。
6th) ORDER BY字句:执行顺序从左到右,消耗资源
【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色,数量,有无办公室,注意一个角色如果部分有办公室,部分没有需分开统计) ✓
SELECT role,count(name),building is not null FROM employees group by building is null,role
group可以有多个目标,判断表达式可以写在前面
【难题】按导演分组计算销售总额,求出平均销售额冠军(统计结果过滤掉只有单部电影的导演,列出导演名,总销量,电影数量,平均销量) ✓
SELECT sum(Domestic_sales+International_sales)/count(id) as ii,director,sum(Domestic_sales+International_sales),count(id)
FROM movies left join Boxoffice on movies.id=Boxoffice.movie_id
group by director
having count(id)>=2
order by ii desc limit 1
having可以再group之后再筛选一次
【变态难】找出每部电影和单部电影销售冠军之间的销售差,列出电影名,销售额差额 ✓
SELECT title,((select MAX(Domestic_sales+International_sales)
from Boxoffice)-(Domestic_sales+International_sales))as li
FROM movies left join Boxoffice on movies.id=Boxoffice.movie_id
order by li desc
select MAX(Domestic_sales+International_sales) from Boxoffice
这一句相当于取出了最大值这个数
之前使用的这一句相当于得到的还是
SELECT title,MAX(Domestic_sales+International_sales)
FROM movies left join Boxoffice on movies.id=Boxoffice.movie_id
版权声明:本文标题:数据库基本语法 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/xitong/1727372144a1111306.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论