sql一些基本常识

sql一些基本常识

sql写法顺序和执行顺序

写法顺序：select–from–where–group by–having–order by
执行顺序：from–where–group by–having–select–order by

SQL四种类型

（1）数据定义。（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
DDL不需要commit. eg. CREATE ALTER DROP TRUNCATE COMMENT RENAME
（2）数据操纵。（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
DML需要commit. eg. SELECT INSERT UPDATE DELETE MERGE CALL EXPLAIN PLAN LOCK TABLE
（3）数据控制。（DCL）包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
GRANT 授权, REVOKE 取消授权
（4）嵌入式SQL的使用规定。（TCL）涉及到SQL语句嵌入在宿主语言程序中使用的规则。
SAVEPOINT 设置保存点, ROLLBACK 回滚, SET TRANSACTION

语句编写技巧

查询奇数的一般方法：如上(最好是位运算&)
查询偶数的一般方法：emp_no=(emp_no>>1<<1)

链接：https://www.nowcoder.com/questionTerminal/4a052e3e1df5435880d4353eb18a91c6?answerType=1&f=discussion
来源：牛客网
题目：获取所有部门中当前员工薪水最高的相关信息，给出dept_no，emp_no以及其对应的salary，当前表示to_date=‘9999-01-01'
隐含要求：按照部门升序排列
错误示范(虽然能通过测试，但实际上是错的)
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_emp as d
INNER JOIN salaries as s
ON d.emp_no=s.emp_no
WHERE d.to_date='9999-01-01’
AND s.to_date='9999-01-01'
GROUP BY d.dept_no
HAVING salary=MAX(s.salary);
错误点1：d.emp_no是非聚合字段，不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，所以选择的时候，会随机选择非聚合字段中的任何一个，于是出错。
错误点2：根据错误点1，如果强行写上述代码，当多人同时拥有最高薪水时也无法查出。
错误点3：HAVING语句用来与聚合函数联合使用，过滤GROUP BY语句返回的记录集，筛选的是分组，例如HAVING AVG(score)>=80，筛选的是那些平均成绩大于80的分组，而上面的HAVING salary=MAX(s.salary)实际上是想筛选分组中的记录。这是错误的。
补充：
GROUP BY语句与HAVING语句的使用：参考资料：https://www.cnblogs.com/geogre123/p/11177204.html
语法：
SELECT column_1, column_2, … column_n, aggregate_function(expression), constant
FROM tables
WHERE predicates
GROUP BY column_1, column_2, … column_n
HAVING condition_1 … condition_n;
注意：因为聚合函数通过作用一组值而只返回一个单一值，因此，在SELECT语句中出现的字段要么为一个聚合函数的输入值，如COUNT(course)，要么为GROUP BY语句中指定的字段，要么是常数，否则会出错。
简而言之：使用GROUP BY子句时，SELECT子句中只能有聚合键、聚合函数、常数。
正确答案：关联子查询，外表固定一个部门，内表进行子查询
SELECT d1.dept_no, d1.emp_no, s1.salary
FROM dept_emp as d1
INNER JOIN salaries as s1
ON d1.emp_no=s1.emp_no
AND d1.to_date='9999-01-01'
AND s1.to_date='9999-01-01'
WHERE s1.salary in (SELECT MAX(s2.salary)
FROM dept_emp as d2
INNER JOIN salaries as s2
ON d2.emp_no=s2.emp_no
AND d2.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND d2.dept_no = d1.dept_no
)
ORDER BY d1.dept_no;
补充：如果这题不需要给出emp_no(即只求所有部门中当前员工薪水最高值)，则用INNER JOIN和GROUP BY和MAX即可解决：
SELECT d.dept_no, MAX(s.salary)
FROM dept_emp as d
INNER JOIN salaries as s
ON d.emp_no=s.emp_no
AND d.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY d.dept_no;