寒假里有一项要做的事就是练SQL使用,今天考完试,也没人带我去外面玩玩,那我就先做这件事呗。做起来发现这件事比我想象的复杂。我刚开始学C语言那种手足无措的感觉,在写SQL时,又出现了。不过也是好事吧,我能早点面对这种不熟练,从而早点去克服它,而不是像其他人,开学之后同时在数据库课上面对理论和SQL使用的问题。

我下午和晚上写的题目有《数据库系统概念》第三章实践习题的前两题,和http://codex.cs.yale.edu/avi/db-book/db6/lab-dir/labexercises-dir/Lab1.html上的题。网页里的题目,最后一道我没做出来,网上还找不到解析,之后问下别人。

网页里实验题我的解附在下方,这些实验题网上找不到对照材料,只能自己在数据集上跑一下去大致验证,不保证绝对正确性。

  1. Find the names of all the instructors from Biology department.



2. Find the names of courses in Computer science department which have 3 credits.

3. For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by the student.

4. As above, but show the total number of credits for such courses (taken by that student). Don’t display the tot_creds value from the student table, you should use SQL aggregation on courses taken by the student.

5. As above, but display the total credits for each of the students, along with the ID of the student; don’t bother about the name of the student. (Don’t bother about students who have not registered for any course, they can be omitted)

6. Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names)

7. Display the IDs of all instructors who have never taught a couse (Notesad1) Oracle uses the keyword minus in place of except; (2) interpret “taught” as “taught or is scheduled to teach”)

8. As above, but display the names of the instructors also, not just the IDs.

9. You need to create a movie database. Create three tables, one for actors(AID, name), one for movies(MID, title) and one for actor_role(MID, AID, rolename). Use appropriate data types for each of the attributes, and add appropriate primary/foreign key constraints.



(There’s a mistake in the primary key of actor_role. It’s possible that one actor play multiple roles in a movie. So the primary key is (MID. AID, rolename).)

10. Insert data to the above tables (approx 3 to 6 rows in each table), including data for actor “Charlie Chaplin”, and for yourself (using your roll number as ID).

11. Write a query to list all movies in which actor “Charlie Chaplin” has acted, along with the number of roles he had in that movie.

12. Write a query to list all actors who have not acted in any movie.

13. List names of actors, along with titles of movies they have acted in. If they have not acted in any movie, show the movie title as null. (Do not use SQL outer-join syntax here, write it from scratch.)

13题还不会解,等待询问一下别人。

这次练习里,有比较长的一段时间是被少打了几个逗号卡着了,挺气人。只能下次注意,少犯了。MySQL的语法错误提示是真的弱,可能是自己被IntelliSense惯坏了吧。

MySQL不支持”WITH…AS…”语法,要表达相同的意思,只能写成子查询的形式。

练习里几次用到在FROM子句里写一个子查询,然后用AS重命名,再在SELECT、WHERE中使用。虽然FROM写的顺序是在SELECT后面,但执行时,还是先去解释FROM子句(牵涉到SQL语句的执行顺序),所以这样写能达成目的。

今天写语句时用到IN关键词。记得之前翻网上的说法,说MySQL里尽量别用IN去表达。不过这次为了把功能实现出来,就直接用了。这点以后再慢慢搞明白是为啥吧。

写有各种嵌套的SQL语句时,发现有这些小窍门:

  • 把查询需要的临时表的列,在草稿纸上画出来,去想怎么构造。
  • 可以先把一些子查询单独在server上跑一下,看结果是否是符合自己的期望。
  • 合适的indent、别名对语句可读性存在相对提高(虽然子查询多了,可读性是无限趋近于0的)
  • 敢去尝试各种组合写法,瞎猫碰上死耗子的可能性还是有的。

MySQL里的单行注释,–后面需要加一个空白字符,之后才能写具体的注释内容,否则语句会报错。

那两道实践习题,我直接上传doc文档吧。第一题最后一小题我自己都嫌自己写的难看死了……