数据库基本知识

DDL（数据定义语言） create, alter, drop
DML（数据操纵语言）insert, update, delete, select
DCL（数据访问控制语言）grant, revoke

数据模型3 要素：数据结构，数据操作，数据完整性约束

特殊运算符号：
IN, NOT IN(检查属性值是否属于一组值之一)
BETWEEN…AND…., NOT BETWEEN…AND…（检查属性值是否属于某个范围）
IS NULL, IS NOT NULL（检查属性值是否为空）
LIKE, NOT LIKE（字符串匹配）

1.>ANY（只要大于其中一个即可）
2.>ALL（必须大于所有结果）
3.< ANY（只要小于其中之一即可）
4.< ALL（必须要小于其中所有结果）

数据类型：
INT或INTEGRT—-全字长整数
SMALLINT—-半字长整数
DECIMAL（p[,q]）或DEC(p,[q])—精确数值型，共p位，小数点后q位，0<=q<=p<=15, q=0时可省略。
FLOAT—–双字长的浮点数
CHAR(n)或CHARATER(n)—-长度为n的定长字符串
VARCHAR(n)—–最大长度为n的变长字符串
DATETIME—日期时间型，格式可以设置

基本的函数集
COUNT ([DISTINCT / ALL])—-计算结果中，元祖的个数,DISTINCT去掉了重复部分，ALL所有的元祖都输出，包括重复的部分，两个都不选默认为ALL
COUNT ([DISTINCT / ALL]<列名>)—-计算某列值得个数
MAX(<列名>)—-给出一列上的最大值
MIN(<列名>)—-给吃一列上的最小值
SUM ([DISTINCT / ALL]<列名>)—-给出一列上值的总和（只对数值型）
AVG([DISTINCT / ALL]<列名>)—–给出一列上的平均值（只对数值型）

SQL对大小写不规定！！！

接下来开始演示
Employee表（职工表）

Eno    Ename     Sex   Age     Is_Mary     Title     Dno;
1002   胡一民      男    38        1         工程师     01
1004   王爱民      男    60        1         高工       03
1005   张小华      女    50        1         工程师     02
1010   宋文彬      男    36        1         工程师     01
1011   胡民        男    34       1         工程师      01
1015   黄晓英      女    26        1         助工       03
1022   李红卫      女    27        1         助工       02
1031   丁卫国      男    24        0         助工       02

Item表（项目表）

Ino      Iname             Start_date  End_date   Outlay  Check_date
200801  硬盘伺服系统改进      3/1/2008    2/28/2009  10.0    2/10/2009
200802  巨磁阻磁头研究        6/1/2008    5/30/2009  6.5     5/20/2009
200901  磁流体轴承改进        4/1/2009    2/1/2010   4.8     3/18/2010
200902  高密度记录磁性材料研究 10/18/2009  9/30/2010  25.0    9/28/2010
201001  MO驱动器性能研究      3/15/2010   3/14/2011  12.00   NULL
201002  相变光盘性能研究      6/1/2010    12/31/2011 20.00   NULL

Item_Emp表（参加表）

Ino      Eno   IENo
200801   1004   1
200801   1016   2
200802   1002   1
200802   1010   2
200902   1005   3
200901   1010   1
200901   1031   2
200902   1005   1
200902   1031   2
201001   1002   1
201001   1004   2
201002   1015   1

Salary表（工资表）

Eno    Basepay   Service    Price   Rest    Insure   Fund
1002   685.00    1300.00    85.00   488.40  18.80    630.50
.......

Department表（部门表）

Dno     Name    Phone    Manager
01     技术科    88090121  1002
02     设计所    88090122  1005
03     车间     88090123   1004
04     销售科    88090124  1101

1.创建职工表–CREATE TABLE

CREATE TABLE Employee
(Eno CHAR(4) NOT NULL UNIQUE,
Ename CHAR(8) NOT NULL,
Sex CHAR(2) NOT NULL DEFAULT('男'),
Age INT NULL,
Is_Mary CHAR(1) NULL,
Title CHAR(6) NULL,
Dno CHAR(2) NULL
);

2.表结构的修改—ALTER TABLE

//1. 在Employee表中增加一列Emgr（负责人）
ALTER TABLE Employee
ADD Emgr CHAR(4) NULL;

//2. 修改列Emgr的数据类型为CHAR(10)
ALTER TABLE Employee
ALTER COLUMN Emgr CHAR(10);

//3. 删除新加的列Emgr
ALTER TABLE Employee
DROP COLUMN Emgr;

//4. 增加Ename取值必须唯一的约束条件
ALTER TABLE Employee
ADD CONSTRAINT UQ_Ename UNIQUE(Ename);//Ename有了取值唯一的约束，约束名字叫UQ_Ename
OR
ALTER TABLE Employee
ADD UNIQUE(Ename);//系统会自动为此约束名赋值

//5. 增加列Title默认取值为“助工”
ALTER TABLE Employee
ADD CONSTRAINT DF_Ttitle DEFAULT '助工' for Title;

//6. 删除Ename上所设定的完整性约束
ALTER TABLE Employee
DROP CONSTRAINT UQ_Name;

//7. ALTER TABLE不支持列修改名字
//将Employee表改名为Employee1
EXEC sp_rename 'Employee'，'Employee1';
//将Ename列改名为Ename1
EXEC sp_name 'Employee.Ename', 'Ename1', 'column';

3.删除基本表DROP TABLE

//1.删除职工表
DROP TABLE Employee;

4.创建索引 CREATE INDEX 降序DESC, 默认为升序ASC

//1.为职工表创建索引，先以部门值排序，部门相同时，再以职工号降序排序
CREATE INDEX IX_Emp
ON Employee(Dno, Eno(DESC));

5.删除索引 DROP INDEX

//1.删除IX_Emp索引
DROP INDEX Employee.IX_Emp;

6.数据操作，表中增加元祖—INSERT
字符串常量，日期常量，需要用英文的单引号括起来。

//1.在Employee表中插入职工记录
INSERT INTO Employee
VALUES ('2002','胡一兵','男',38,'1','工程师','01');
INSERT INTO Employee
VALUES('2003','宋文彬','男');//可以为NULL的，可以为空。

7.数据操作，修改表中数据—UPDATE

//1.在工资表中，将所有职工的基本工资都增加50
UPDATE Salery
SET Basepay = Basepay+50;

//2.将职工‘胡一兵’修改为‘胡一民’
UPDATE Employee
SET Ename='胡一民'
WHERE Ename='胡一兵';

8.数据操作，删除元祖—DELETE

//1.从职工表中删除Eno(职工号)为1003的记录。
DELETE FROM Employee
WHERE Enp='1003';

//2.删除Employee表中所有的记录,此时表结构还是在的，数据没了而已
DELETE FROM Employee;

9.数据查询—SELECT

//1.查询职工表所有信息
SELECT *
FROM Employee;

//2.查询职工的职称
SELECT Title 
FROM Employee;//这时结果会有很多重复的。
//需要改为如下：
SELECT DINSTINCT Title
FROM Employee;

//3.查询职工姓名、出生年份、职称
SELECT Ename, 2010-Age, Title
FROM Employee;//结果第二列，没有列名
//需要改为如下：
SELECT Ename, 2010-Age AS [Year of Birth], Title
FROM Employee;//这时第二列名字是Year of Birth

//4.求所有项目的总经费、平均经费、最高经费、最低经费。
SELECT SUM(Outlay) AS 总经费， AVG(Outlay) AS 平均经费，MAX(Outlay) AS 最高经费，MIN(Outlay) AS 最低经费
FROM Item;

//5.查询所有工程师的姓名、年龄
SELECT Ename,Age
FROM Employee
WHERE Title='工程师';

//6.查询所有在2009年底前结束的项目情况
SELECT *
FROM Item
WHERE End_date < '1/1/2010';

//7.查询职称为工程师或者高工的职工的姓名
SELECT Ename
FROM Employee
WHERE Title in ('工程师','高工');

//8.从salary表中查找Basepay不在500-700之间的元祖，显示符合条件的元祖的所有属性
SELECT * 
FROM Salary
WHERE Basepay NOT BETWEEN 500 AND 700;

//9.从项目表中选取验收日期不为空的记录
SELECT *
FROM Item
WHERE Check_date IS NOT NULL;

//10.从项目表中查找Iname中包含‘性能研究’的项目的名称。
SELECT Iname
FROM Item
WHERE Iname LIKE '%性能研究%';

//11.从Salary表中选取符合下列条件的元祖
基本工资在600-700之间，津贴为600,700，1300，2500；职工号前三位为100.
SELECT *
FROM Salary
WHERE (Basepay BETWEEN 600 AND 700) AND (Service IN (600.0,700.0,1300.0,2500.0)) AND Eno LIKE '100_';

//12.把职工表中的元祖按部门分组，计算平均年龄，并输出部门号和平均年龄两列
SELECT Dno, AVG(Age) AS Average_Age
FROM Employee
GROUP BY Dno;

//13.把职工表中的元祖按部门分组，计算平均年龄，并输出部门号和平均年龄两列;并且只显示平均年龄小于40的结果
SELECT Dno , AVG(Age) AS Average_Age
FROM Employee
GROUP BY Dno
HYAVING AVG（Age）< 40;

//14.将Employee表中的数据先以部门号降序排序，部门号相同再以年龄升序排序
SELECT * 
FROM Employee
ORDER BY Dno DESC, Age;//ORDER BY只能作用于最终查询结果

多关系连接查询
交叉连接（Cross Join）
内部连接（Inner Join）
外部连接（Outer Join）
自身连接（Self Join）

下面一一道来：
1.交叉连接（Cross Join）:也叫笛卡尔积，是返回，左表的每一行， 与， 右表的每一行的组合。用得比较少，应为返回的结果多了很多行，没什么意义。

//笛卡尔积,没什么意义,用得少
SELECT e.Ename, e.Title, e.Dno, d.Dno, d.Name
FROM Employee e, Department d

2.内部连接（Inner Join）：用的最多的一种连接。在连接的2表中，只有满足条件的元祖才作为结果输出。一般，表与表之间的连接是通过他们共同的属性实现的，连接的属性名字不必相同，但是数据类型要兼容。

SELECT e.Name, e.Title, e.Dno, d.Dno, d.Name
FROM Employee e INNER JOIN Department d
ON e.Dno = d.Dno;//这里可用WHERE 也可用 ON

//如下使用WHERE指定连接表达式
SELECT e.Name, e.Title, e.Dno, d.Dno, d.Name
FROM Employee e ，Department d
WHERE e.Dno = d.Dno;

3.外部连接（Outer Join）：分3种，左外连接（Left Join），右外连接（Right Join），全外连接（Full Join）

左外连接（Left Join）:除了返回两个表中满足连接条件的元祖以外，还返回左侧表中不匹配元祖，右侧表中以空值NULL替代。

右外连接（Right Join）：出来返回满足2表的连接条件的元祖外，还返回右侧表中的不匹配元祖，左侧表中的空值用NULL 来替代。

全外连接（Full Join）：出来返回满足2表的连接条件的元组外，还返回左侧表中的不匹配元祖，右侧表中的不匹配元祖，所有空值的地方用NULL来替代。

SELECT d.Dno, d.Name, e.Dno, e.Title
FROM Department d LEFT JOIN Employee e
ON d.Dno=e.Dno;

//下面是使用WHERE的方式
SELECT d.Dno, d.Name, e.Dno, e.Title
FROM Department d ，Employee e
WHERE d.Dno=e.Dno*;//左外连接，多了个*

4.自身连接：有时候需要对同一个表进行连接查询。

//假设职工表中只有职工号Eno,职工姓名Ename,负责人Emgr属性，求出所有职工号的间接负责人
//由于Employee表中只有职工的直接负责人，要找到负责人的负责人的话，就需要用到自身连接
SELECT emp1.Eno, emp.Emgr
FROM Employee emp1, Employee emp2
WHERE emp1.Emgr=emp2.Eno;

嵌套查询–IN,运算符，BETWEEN,相关子查询
—–IN

//1.查询所有参加了200901项目的职工号、姓名、职称
SELECT Eno, Ename, Title
FROM Employee
WHERE Eno IN 
（SELECT Eno
  FRMO Item_Emp
  WHERE Ino=200901）

//2.查询职工1010所没有参加的项目的所有情况
SELECT *
FROM Item
WHERE Ino NOT IN 
(SELECT Ino 
 FROM Item_emp
 WHERE Eno='1010';
 );
 //3.查询参加了项目“相变光盘性能研究”的职工情况
 SELECT * 
 FROM Employee
 WHERE Eno IN
 （SELECT Eno
  FROM Item_Emp
  WHERE INO=(SELECT INO FROM Item WHERE Iname='相变光盘性能研究')
 ）;

—–运算符

//1.查询所有大于平均年龄的职工的姓名和年龄
SELECT Ename, Age
FROM Employee
WHERE Age > (SELECT AVG(Age) FROM Employee);

//2.查询非02部门，年龄大于02部门最小职工年龄的职工，并按年龄升序排序
SELECT *
FROM Employee
WHERE Age > ANY （SELECT Age FROM Employee WHERE Dno='02'）AND Dno <>'02';

//>ANY只要大于其一即可
//>ALL必须大于所有结果

—–BETWEEN

//1.查询基本工资介于职工号为‘1010’职工的工资和800元之间的职工号
SELECT Eno
FROM Salary
WHERE Basepay BETWEEN (SELECT Basepay FROM Salary WHERE Eno='1010') AND 800;

—–相关子查询

//1.输出每个年龄高于该部门平均年龄的职工姓名。
SELECT Ename
FROM Employee emp1
WHERE Age > (SELECT AVG(Age) FROM Employee emp2 WHERE emp1.Dno=emp2.Dno)

//2.查出参加了项目的职工号、姓名
SELECT Eno, Ename
FROM Employee e
WHERE EXISTS (SELECT * FROM Item_Emp WHERE Eno=e.Eno);
//上面的EXISTS是测试子查询是否存在返回值的

//3.查询一个项目都没参加过的职工情况
SELECT *
FROM Employee 
WHERE Eno NOT EXISTS (SELECY * FROM Item_Emp WHERE Eno=Employee.Eno);