数据库学习(三)之初级SQL(表、查询、基本运算、聚集函数、附加运算)

一、SQL的组成：SQL是一种查询语言，最初是由IBM开发。
1.数据定义语言(Data-Definition Language,DDL): 提供定义关系模式、删除关系和修改关系模式的命令；
2.数据操作语言(Data-Manipulation Language,DML): 提供从数据库中查询信息，以及在数据库中插入、删除、修改元组的能力；
3.完整性(integrity): SQL DDL包括定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束；
4.视图定义(view definition): SQL DDL包括定义视图的命令；
5.事务控制( transaction control): SQL包括定义事务的开始和结束的命令；
6.嵌入式SQL和动态SQL: 定义SQL语句如何嵌入到通用编程语言；
7.授权(authorization): SQL DDL包括定义关系和视图的访问权限的命令。

二、SQL数据定义

2.1 基本类型定义

            | 类型     		 |   说明                                |
            |  char(n)		 |  固定长度字符串                      	 |
			|  varchar(n) 	 |  可变长度字符串                        |
			|  int			 |  整数类型，和机器相关的的整数的有限子集  |
			|  smallint		 |  小整数类型，和机器相关的的整数的有限子集 |
			|  numeric(p,d)	 | 定点数，共有p位，其中小数点部分有d位     |
			|  real	precision | 单精度浮点数，精度与机器有关     		|
			|  double precision | 双精度浮点数，精度与机器有关     	|
			|  float(n)		 | 精度至少为n位的浮点数                  |

2.2 基本模式定义

	2.2.1 关系定义
				**create table r                   create table teaches
					(A1 D1,							(ID	varchar(5),
				 	A2 D2,							 course_id	varchar(8) not null,
				 	....							 sec_id		varchar(8),
				 	<完整性约束1>,				 	 primary key (ID,course_id),
					 ....							 foreign key (course_id) references section,
					 <完整性约束2>);					 foreign key (course_id,sec_id) references instructor);**
			说明：
			a.create table 命令后用分号**“;”**结束;
			b.primary key(A1,...AN):主码唯一且为空;
			c.foreign key(A1,...AN): 外码一定是某个表的主码，用references 连接这个表);
			d. not null: 约束这个属性不允许出现空值;
	2.2.2 插入语句
			**insert into r [(属性1, 属性2,...)] values( 值1, 值2, ....);         insert  instructor values(10211, 'Smith', 'Biology', 660000);**
	2.2.3 删除原则操作
			**delete from table_name where ...;**         **delete from student [where id = '1011'];**
	2.2.4 删除关系操作
			**drop table table_name;**										**drop table student;**
	2.2.5 增加/删除属性
			**alter table r add A D;**									**alter table r drop A;**

2.3 SQL查询的基本结构: select…from…where…;

	2.3.1 单关系查询： **select ...from ..;**	**select distinct name from student [where name = 'Comp.Si' and salary > 7000;**
		说明：
			a. distinct 删除重复属性行，all 不删除，默认为不删除；
			b. select 可带“**+,-,*,/**”运算符的算术表达式，比如 **select salary*1.1 from teachers;**
			c. where 可带 **and , or , not** 逻辑连词.
	2.3.2 多关系查询: **select a,b,c... from r1,r2... where...**			
		**select name,instructor.dept_name,building from instructor,department where instructor.dept_name = department.dept_name;**
		说明：
			a. 当属性名在多个表共有时，需要前缀**"."**;
			b. 多关系查询原理是笛卡尔积;

2.4 附加的基本运算

	2.4.1 更名运算 **as**
		**select name as instructor_name,course_if					select distinct T.name
		from instructor, teaches												from instructor as T, instructor as S
		where instructor.ID = teaches.ID;								where T.salary > S.salary and S.dept_name = 'Biology';**
		说明: **后一个例子是代表找出在所有教师，他们的工资至少要比Biology系的某一个教师的工资要高；**
	2.4.2 字符串运算：SQL标准是大小写敏感，但是MySQL 和 SQL Sever 默认大小写不敏感，字符串运算分为字符串函数和字符串匹配.
		字符串匹配：**用like操作符实现字符串匹配, 用not like 实现相反匹配**
		
		|字符			  | 说明				     		 |
		| 	百分号(%)  | 匹配任意子串 			 |
		| 	下划线(_)   | 匹配任意一个字符	 |
		| 	escape	  | 定义转义字符			 |

		**like 'ab%\%cd%' escape '\'	: 匹配所有以"ab....%cd....."的字符串；
		select dept_name
		from department
		where building like '%Watson%';**
	2.4.3 ***号可表示select子句中的所有属性**
	2.4.4 **排序: **order by** , 升序 asc, 降序 desc, 默认升序排列.**
	2.4.5 where子句谓词
		a. between:  **where salary between 9000 and 10000**;
		b. 元组: where (instructor.ID, dept_name) = (teaches.ID, 'Biology).

2.5 集合运算: 默认集合运算去除重复，如果想要重复用all 关键字

		 并、交、差运算: union, 
		(select course_id from section where semester = 'Fall' and year = 2009)
		union(intersect,except) [all]
		(select course_id from section where semester = 'Fall' and year = 2010);

2.6 空值：第三逻辑值(unknown)

	a.任何与空值的运算都为空，unknown类型;
	b.and: true and unknown is unknown, false and unknown is false, unknown and unknown is unknown;
	c. or: true or unknown is true, false or unknown is unknown, unknown or unknown is unknown;
	d. not : not unknown is unknown.

2.7 聚集函数: 以值的一个集合(集或多重集)为输入、返回单个值的函数。

	2.7.1 基本聚集: avg, min, max, sum, count;
		**select avg(salary) from instructor where dept_name = 'Com.Sci';**
	2.7.2 分组聚集: group by
		**select dept_name, avg(salary) from instructor group by dept_name;**
		说明: 任何出现在select子句中且未在聚集函数内的属性，都在在group by 子句中。
	2.7.3 having 子句:对分组作出约束
		**select dept_name, avg(salary) from instructor group by dept_name having avg(salary)>42000;**
		说明:
			a.与select相同，任何未出现在having聚集函数内的属性都需要出现在group by子句中;
			b.整个逻辑流程为: from - where - group by - having - select;
	2.7.4 对空值的聚集：除了count(*)外，空值都将被聚集函数忽略;

2.8 嵌套子查询

	2.8.1 集合成员资格: **in, not in**
	2.8.2 集合的比较
		a. some: >some, <some, =some(等价in), <>some(并不等同于not in 考虑空值);
		b.all: >all, <all, =all(不等价于in), <>all(等价not in);
			**select name from instructor
			where salary >some (select salay from instructor where dept_name =  'Biology');**
	2.8.3 空关系测试: exists(select....),非空返回true
	2.8.4 重复元组存在性测试: unique
	2.8.5 with 子句:定义临时关系,只在含with的查询语句中有效
		**with max_budget(value) as(select max(budget) from department)
		select budget from department, max_budget where department.budget = max_budget.budget;**

2.9 数据库的修改

	2.9.1 删除
		**delete from ....where ....;**
		**delete from instructor where salary < (select avg(salary) from instructor);**
	2.9.2 插入
		**insert into table_name[(A1,A2...)] values(V1,V2...);**
		**insert into table_name select....**
	2.9.3 更新
		**update table_name set ... where...;**
		**update instructor set salary = salary * 1.5 where salary < 10000;**
		**case 结构: case when .... then ....
								  when ...  then...
						 else
						 	....
						 end**
		**update instructor set salary =  case when salay < 10000 then salary = salary *1.5 else salary = salary*0.8 end**
		说明： **case结构无分号结束**