数据库基本语法

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