数据库基本查询

Instructor:

TeachingAssistant:

Course:

• SELECT子句中不仅可以填属性列，也可以是算术表达式；用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式的列尤为有用
  eg:Produce a list of monthly salaries for all teaching assistants, showing teaching assistant ID and monthly salaries

SELECT teachingAssistantID,salary/12 AS monthly_salaries  
FROM teachingassistant;  

• IN可以用来查找属性值属于指定集合的元组；当子查询返回的值可能有多个时，要用到谓词 IN，如果用=，则报错，因为=表示子查询的返回值是唯一的；与IN相对的谓词是NOT IN
  eg:list the instructor ID and position of instructors who are associate professors or full professors

SELECT InstructorID,position  
FROM instructor  
WHERE position IN ('associate','full'); 

• LIKE可以用来进行字符串的匹配，其中匹配串可以是一个完整的字符串，也可以含有通配符%和_；%代表任意长度的字符串，eg:a%b表示以a开头，以b结尾的任意长度的字符串，如ab、acb、addgb都满足该匹配串；_代表任意单个字符，eg:a_b表示以a开头，以b结尾的长度为3的任意字符串，如acb、afb都满足该匹配串
  eg:list the instructor ID and last name of all instructors whose last names contain character ‘o’

SELECT InstructorID,lName  
FROM instructor  
WHERE lName LIKE '%o%'; 

• SELECT语句中可以使用COUNT、SUM、AVG、MAX和MIN等聚合函数;一个子句中可以使用多个聚合函数，但不能嵌套在一起用，avg(mum(xm)) ；注意：WHERE子句中是不能用聚合函数的，聚合函数只能用于SELECT子句和GROUP BY中的HAVING子句
  eg:how many instructors have $300.00 or more bonus?

SELECT count(bonus) AS count  
FROM instructor  
WHERE bonus>=300.00;

eg:how many different course titles are there?

SELECT count(distinct title) AS count//如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值；不指定时默认为ALL表示不取消重复值
FROM course;

eg:find the total number of assistant professors and the sum of their bonus

SELECT count(position),SUM(bonus)  
FROM instructor  
WHERE position='assistant';  

• GROUP BY子句将查询结果按某一列（或多列）的值分组，值相等的为一组；目的是为了细化聚合函数的作用对象，因为分组后聚合函数将作用于每一组，即每一组都有一个函数值
  eg:find the total number of instructors in each department and the sum of their bonus, respectively

SELECT deptCode,COUNT(InstructorID),SUM(bonus)  
FROM instructor  
GROUP BY deptCode;

eg:for each position type with more than one instructor, find the total number of instructors and the sum of their bonus

SELECT position,COUNT(InstructorID),SUM(bonus)  
FROM instructor  
GROUP BY position  
HAVING COUNT(InstructorID) > 1;

• 嵌套查询：将一个SELECT-FROM-WHERE查询块嵌套在另一个查询块的WHERE或HAVING子句中；在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词
  eg:list all details of the teaching assistant of course ACCT102 Section 1

SELECT *  
FROM teachingassistant  
WHERE teachingAssistantID=(SELECT teachingAssistantID  
	                       FROM course  
                           WHERE courseNo='ACCT102' and sectionNo='1');

eg:list the instructor ID and bonus of instructors whose bonus is greater than the average bonus

SELECT instructorID,bonus  
FROM instructor  
WHERE bonus>(SELECT AVG(bonus) FROM instructor);

• 连接查询：涉及两个以上的表时需要使用连接查询(也可以用上述嵌套查询完成)，在WHERE子句中连接两个表
  eg:list the instructor IDs, names, and course numbers, titles, and section numbers that they teach, and order results by instructor IDs (ASC), course numbers (ASC), & section numbers (DESC)

SELECT i.instructorID,fName,lName,courseNo,title,sectionNo  
FROM instructor i,course c  
WHERE i.instructorID=c.instructorID  
ORDER BY i.instructorID,courseNo,sectionNo DESC;

eg:for each course that has an instructor and a teaching assistant, list its courseNo, title, sectionNo, instructor ID & name, teachingAssistantID & studentID

SELECT courseNo,title,sectionNo,i.instructorID,fName,lName,t.teachingAssistantID,studentID  
FROM instructor i,course c,teachingassistant t  
WHERE i.instructorID=c.instructorID AND c.teachingAssistantID=t.teachingAssistantID;  

• 带有EXISTS谓词的嵌套查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”
  eg:find all information of all teaching assistants who assist the course ACCT101

SELECT *  
FROM teachingassistant t  
WHERE EXISTS (SELECT *  
	          FROM course c  
	          WHERE t.teachingAssistantID=c.teachingAssistantID AND courseNo='ACCT101'); 

• 修改数据，一般格式为UPDATE<表名>，SET子句给出用于取代相应属性列的值，修改满足WHERE子句条件的元组
  eg:give all assistant professors 10% bonus increase and change their positions to associate professors

UPDATE instructor  
SET bonus=bonus*1.1,position='associate'  
WHERE position='assistant';