11.3数据库笔记

SELECT语句

SELECT[ALL|DDISTINCT]<目标列表达式>[,…n]
[INTO<新表>]
[from <数据源>] 数据库名.模式名.表名，其中模式名一般为用户名(dbo)
其中，dbo可以省略，例如grademanagement…student
模式，scheme，结构
[WHERE<筛选表达式>]
[GROUP BY<分组依据表达式>
[HAVING<分组提取条件>]]
[ORDER BY<排序依据列>[排序方式]]

登录方式：

（存疑）有两种登录方式，分别是windows登录和sql账户登录，如果用windows登录，用户名会是dbo，即database owner，这也会死选择数据库的模式名

WHERE语句

1.可以用between and 代替大于等于和小于等于
2.用in(…)，进行枚举
3.基于字符串匹配条件WHERE exression LIKE ‘string’
4.不能用等号匹配null，应该用is not null或者is null

举例：

where grade >=60 and age <=90
等价
where grade between 60 and 90

where dno = ‘1’ or dno = ‘2’ or dno = ‘3’
等价
where dno in (‘1’,‘2’,‘3’)

SELECT 商品名，销量，店铺名
FROM 商品表
WHERE 商品名 LIKE ‘%洗衣液%’
%百分号：代表任意多个字符（0到n个都可以）
下划线：代表单个字符（有且仅有1个）
[]：代表指定范围内的单个字符（例如 [a-f]ouse）
[^ ]：代表不在指定范围内的单个字符（例如 [^mh]ouse）
转义字符：举例：搜索’DB’开头的(未完成）

查询结果排序

order by
语法:
SELECT select_list
FROM table_source
（未完成）

desc:降序

SELECT top 3 lname,Outlay 
FROM Item
ORDER BY Outlay DESC

select top 10 Sno,Gmark
from grade
where cno = '1'
order by gmartk desc

先以部门号降序排序，部门号相同的再以年龄的顺序排序
order by Dno desc,age;

聚合函数

COUNT([distinct|ALL]*)统计结果中元组的个数
COUNT([distinct|ALL]<列名>)统计一列上元组的个数
MAX(<列名>)
MIN(<列名>)
SUM([distinct|all]<列名>)
AVG([distinct|all]<列名>)

举例
SELECT max(age),min(age),avg(age1.0),COUNT()
其中，age*1.0可以在输出平均值时保留一位小数

举例，书P83
SELECT count(*)
FROM grade

count(*) 18,这个表一共18行
count(gmark) 16,去掉两个gmark值为null的记录
count(distinct gmark) 13，不重复且不为null的记录数量

select使用聚合函数的时候，注意行数是否匹配
例如：SELECT dno,count()
其中，dno为多行目标列，count()为单行目标列
会报语法错误，因为输出时不知道输出的表格应该有几行（一行还是多行）

数据分组

1.查询的每个部门的职工人数和平均年龄

SELECT dno,count(*),avg(age)
FROM employee
GROUP BY dno

2.按职称输出每种职称的人数，最高年龄和最低年龄

SELECT title,count(*),max(age),min(age)
FROM employee
GROUP BY title

3.查询人数在两个以上的每个部门的职工人数和平均年龄

SELECT dno,count(*),avg(age)
FROM employee
GROUP BY dno
	HAVING count(*)>2

(不能用where进行筛选，应该在分组条件中进行修改)
WHERE子句和HAVING短语的区别
where作用于表或者视图，从中选择满足条件的元组
不可包含聚合函数
HAVING短语作用于组，从中选择满足条件的组
可以在条件中包含聚合函数
在执行之前先执行where，执行group by之后再having

从多个表中查询数据——表连接

内连接/自然连接

1.参加了项目‘200801’的职工信息

SELECT e.*
FROM item_emp join employee e
	on ie.eno = e.eno
WHERE ie.ino = '200801'

等价于

SELECT e.*
FROM item_emp ie , employee e
WHERE ie.eno = e.eno AND 
	ie.eno = '200801

2.参加了项目‘遗传算法’的职工信息

SELECT e.*
FROM Item ,item_emp ie, employee e
WHERE Item.Iname = '遗传算法' 
	AND Item.Ino = ie.Ino 
	AND ie.Eno = e.eno

等价于

SELECT e.*
FROM employee e JOIN item_emp ie
	ON e.eno = ie.eno
	JOIN item
	ON ie.ino = item.ino
WHERE item.Iname = '遗传算法'

当出现三张表的时候，可以用连续JOIN…ON…，或者在FROM中引入三张表，在where中进行条件的筛选

外连接

LEFT JOIN 左外连接
对左侧表中的条目不加限制，所有行都出现在结果集里
在内连接的基础上，加上左侧没匹配到，行中右侧表的列为null

查询有员工存在的部门的员工人数

SELECT d.Dname ,count(Eno)
FROM Department d JOIN employee e
	ON d.eno = e.eno
GROUP BY d.dname

查询所有部门的员工人数

SELECT d.Dname ,count(Eno)
FROM Department d LEFT JOIN employee e
	ON d.eno = e.eno
GROUP BY d.dname

自身连接

输出职工自身的姓名和他顶头上司的姓名

SELECT e1.name, e2.name
FROM employee e1, employee e2
WHERE e1.mgrNo = e2.eno
	AND e1.eno = '1011'