数据库基本查询

Instructor:

instructorID	fName	lName	SSN	deptCode	position	bonus
76	Andy	Chou	467374211	math	assistant	300.00
52	Chris	Bowen	602497126	math	associate	0.00
44	Jennifer	Furman	290337845	acct	assistant	800.00
89	Daniel	Pradmore	589035216	acct	full	300.00

TeachingAssistant:

teachingAssistantID	SSN	studentID	salary
37	478902824	379	2500.00
92	352761903	574	5000.00

Course:

courseNo	title	sectionNo	units	deptCode	instructorID	teachingAssistantID	prerequisite
ACCT101	Accounting Ⅰ	1	4	acct	44	92	None
ACCT101	Accounting Ⅰ	2	4	acct	44	92	None
ACCT102	Accounting Ⅱ	1	3	acct	89	37	ACCT101
MATH105	Algebra	1	3	math	76		None

---

• 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';