数据库整理

发表于 | 更新于: | 分类于 |
字数统计: 4.6k字 | 阅读时长 ≈ 19分钟

关于主键和外键：

简而言之，SQL的主键和外键就是起约束作用。

关系型数据库中一条记录中有若干个属性，若其中某一个属性组（注意是组）能唯一标识一条记录，该属性就可以成为一个主键。

成绩表中单一一个属性无法唯一标识一条记录，学号和课程编号的组合才可以唯一标识一条记录，所以学号和课程编号的属性组是一个主键。

成绩表中的学号不是成绩表中的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键；同理，成绩表中的课程号是课程表的外键。

定义：

1
2
3
主键：唯一标识一条记录，不能有重复，不允许为空。
外键：表的外键是另一表的主键，外键是可以有重复的，可以是空值。
索引：该字段没有重复值，但可以有一个空值。

作用：

1
2
3
主键：用来保证数据完整性
外键：用来和其他表建立联系用
索引：用来提高查询排序的速度

个数：

1
2
3
主键：主键只能有一个。
外键：一个表可以有多个外键。
索引：一个表可以有多个唯一索引。

常用数据库查询操作

数据库查询

数据库操作的原理就是将需求翻译成数据库语言

简单的单表查询操作

查询“数据库原理”课程的学分；

1
2
SELECT cname，credit FROM course WHERE cname = ‘数据库原理’
从course表中取出cname和credit数据，条件是cname为数据库原理

查询选修了课程编号为“C01”的学生的学号和成绩，并将成绩按降序输出；

1
2
SELECT sno，grade FROM sc WHERE cno = ‘C01’ ORDER BY grade DESC；
从sc表中选出sno和grade数据，条件是cno是C01，按grade降序输出

查询学号为“31401”的学生选修的课程编号和成绩；

1
2
SELECT cno，grade FROM sc WHERE sno = ‘31401’
从sc表中选出cno和grade两个数据，条件是sno为31401

查询选修了课程编号为“C01”且成绩高于85分的学生的学号和成绩；

1
2
SELECT sno, grade FROM sc WHERE cno = 'C01' AND grade > 85;
从sc表中查询sno和grade，条件是cno为C01以及grade大于85分
简单的多表的连接查询

由于学号和成绩分别在student和sc表中，所以这是多表查询，多表查询注意隐含条件的表达

查询选修了课程编号为“C01”且成绩高于85分的学生的学号、姓名和成绩；

1
2
SELECT sc.sno, sname, grade FROM student, sc WHERE student.sno = sc.sno AND cno = 'C01' AND grade >85;
从student和sc表中选取学号、姓名和成绩，条件是课程编号C01、成绩>85、并且【学生编号相同】

查询所有学生的学号、姓名、选修的课程名称和成绩；

1
2
SELECT sc.sno, sname, cname, grade FROM student, sc, course  WHERE sc.sno = student.sno AND sc.cno = course.cno;
从student、course和sc表中查询学号、姓名、课程名和成绩，条件是三个表中的学号均相同
复杂的查询操作

GROUP BY用于合计函数，根据一个或多个列对结果集进行分组，比如这题想要得到的是 学生 的 课程总和 ，所以GROUP BY学生，合计课程号cno

查询至少选了三门课程的学生的学号和姓名；

1
2
3
4
5
SELECT sc.sno, sname FROM sc, student 
WHERE sc.sno = student.sno 
GROUP BY sc.sno 
HAVING COUNT(sc.cno)>=3
从sc和student表中查询，条件是学号相同，并且统计选课>=3

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组

查询所有学生的学号和他选修课程的最高成绩，要求他的选修课程中没有成绩为空的；

1
2
SELECT sno, max(grade) FROM sc GROUP BY sno HAVING MIN(grade) > 0;
这里调用一个max，并且选课成绩不为空仍然用group by和having min
带有IN、比较符的嵌套查询

查询修了数据库原理的学生的学号和姓名;

1
2
3
4
5
6
SELECT sc.sno, sname FROM student, sc

WHERE sc.sno = student.sno AND

	sc.cno IN (SELECT cno FROM course WHERE cname = '数据库原理')；
从student和sc中查询学号和姓名，条件是学号相同，这里IN相当于等号【在course表中数据库对应的cno】

inner join表示输出前后两个表的共同部分on【共同的志向条件】

查询没有选修数据库原理的学生的学号和姓名；

1
2
3
4
5
6
7
8
SELECT sno, sname FROM student

WHERE sno NOT IN

	(SELECT sc.sno FROM sc INNER JOIN course ON sc.cno = course.cno

	WHERE (course.cname = '数据库原理'))
从student表中查询学号姓名，条件是sno不等于（从【sc和course表中cno相同】中选出sno，条件是cname为数据库原理）

查询至少选修了【学号为“31401”的学生所选修的所有课程】的学生的学号和姓名；

EXCEPT 仅返回那些不存在于第二个 SELECT 语句结果的记录（差集）

DISTINCT 关键字同 SELECT 语句一起使用，可以去除所有重复记录，只返回唯一项

EXISTS 和 NOT EXISTS 表示是否存在，只返回True/False

1
2
3
4
5
6
select distinct sno, sname
from student 
where not exists ((select cno from sc where sno=’31401’)
									except
									(select cno from sc where sc.sno=student.sno));
（31401选的课程号减去别的同学所选的课程号）【判断是否存在】，不存在返回真，选出不重复的学生

但是MYSQL没有except关键字，只能用建立视图解决问题：

视图的优点：简单、安全、数据独立

左连接where只影向右表，右连接where只影响左表

「select * from tbl1 Left Join tbl2 where tbl1.ID = tbl2.ID」

左连接后的检索结果是显示tbl1的所有数据和tbl2中满足where 条件的数据

[左连接实例]:http://www.w3school.com.cn/sql/sql_join_left.asp

1
2
3
4
5
6
7
8
9
10
11
12
13
create view temp as select distinct cno from sc where sno=’31401’;
第一步，选出学号为31401所选的所有课程，创建视图
create view ttemp as select temp.cno, sno from temp left join sc on temp.cno=sc.cno ; 
第二步，选出cno和sno，从temp表以及sc表中cno相同的数据，「对照temp.cno把学号输入一遍」，创建视图
另一种写法：
【create view ttemp as select temp.cno, sno from temp, sc where temp.cno=sc.cno(+)】

select sno,sname 
from student 
where sno in (select sno 
              from ttemp 
              group by sno having count(distinct cno)=3);
第三步，查找从学生表中查询学号姓名，条件是sno等于【从ttemp中选出sno，不重复的cno个数大于等于3的sno】

由浅入深多表查询

多表连接分为三类

  1. 内连接：（joind，inner join）
  2. 外连接：（left join，left outer join，right join，right outer join，union）
  3. 交叉连接：（cross join）

一、内连接语法：

对内连接

1
2
3
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

例：查询每个员工的工号，姓名，工资，部门名和工作地点

1
2
3
SELECT empno, ename, sal, dname, loc 
FROM emp, dept
WHERE emp.deptno=dept.deptno;

内连接的另一种写法：

1
2
3
SELECT empno,ename,job,sal,dept.deptno,dname,loc
FROM emp JOIN dept 
ON (emp.deptno=dept.deptno);

二、外链接语法

对外连接

1
2
3
SELECT table1.column, table2.column   --右外连接
FROM table1, table2
WHERE table1.column(+) = table2.column;
1
2
3
SELECT table1.column, table2.column   --左外连接
FROM table1, table2
WHERE table1.column = table2.column(+);

左外连接就是把左边的表内容全列出来，右边的只显示相同的部分，不存在的写NULL。
右外连接就是把右边表内容全列出来，左边只显示相同的部分，不存在的写NULL。
全连接就是完整返回左右表所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

例：查询每个员工的工号，姓名，工资，部门名和工作地点

1
2
3
SELECT empno,ename,job,sal,dept.deptno,dname,loc   --右外连接
FROM emp,dept
WHERE emp.deptno(+)=dept.deptno;

另一种写法：

1
2
SELECT empno,ename,job,sal,dept.deptno,dname,loc   --右外连接
FROM emp right join dept on (emp.deptno=dept.deptno);

左外链接：

1
2
SELECT empno,ename,job,sal,dept.deptno,dname,loc   --左外连接
FROM emp left join dept on (emp.deptno=dept.deptno);

全连接：

1
2
SELECT empno,ename,job,sal,d.deptno,dname,loc
FROM emp e full join dept d on (e.deptno=d.deptno);

三、交叉连接（笛卡尔积）

实际应用中还有这样一种情形，想得到A，B记录的排列组合，即笛卡儿积，这个就不好用集合和元素来表示了。需要用到cross join

1
2
SELECT empno,ename,dname
FROM emp cross join dept;

数据库ODBC接口

利用C语言编程实现简单的数据库应用程序，掌握基于ODBC的数据库访问的基本原理和方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
#include<stdio.h>
#include<stdlib.h>
#include"windows.h"
#include"sql.h"
#include"sqlext.h"
#include"sqltypes.h" 
#include"odbcinst.h"

struct course_cell
{
    char cno[5];
    char cname[20];
    int lhour;
    int credit;
    char semester[5];
};

unsigned char sql_select[]="select * from db.course";     //SQL查询语句                                      

unsigned char sql_dolist[3][100]={"insert into db.course values('C27','internet',48,2,'')", "update db.course set semester='τ' where cno='C27'", "delete from db.course where cno='C27'"};     
//插入、修改、删除语句
           
unsigned char do_name[3][10]={"insert","update","delete"};

void show_course(HDBC hdbc) 							   //显示course的内容
{
    HSTMT hstmt;
    RETCODE retcode;
    
    long lenOut1,lenOut2,lenOut3,lenOut4,lenOut5;
    struct course_cell* ccell;
    ccell = (struct course_cell*)malloc(sizeof(struct course_cell));

    retcode=SQLAllocStmt(hdbc,&hstmt);
    if(retcode==SQL_SUCCESS || retcode==SQL_SUCCESS_WITH_INFO)      
    {//SQLBindCol将应用程序的数据缓冲绑定到结果集的各列
        retcode=SQLBindCol(hstmt,1,SQL_C_CHAR,ccell->cno,5,&lenOut1);
        retcode=SQLBindCol(hstmt,2,SQL_C_CHAR,ccell->cname,20,&lenOut2);      
      retcode=SQLBindCol(hstmt,3,SQL_C_SLONG,&ccell->lhour,sizeof(int),&lenOut3);
        retcode=SQLBindCol(hstmt,4,SQL_C_SLONG,&ccell->credit,sizeof(int),&lenOut4);
        retcode=SQLBindCol(hstmt,5,SQL_C_CHAR,ccell->semester,5,&lenOut5);   
    
        retcode=SQLExecDirect(hstmt,sql_select,SQL_NTS);    
        //把SQL语句送到数据库服务器，请求执行由SQL语句定义的数据库访问
        if(retcode==SQL_SUCCESS || retcode==SQL_SUCCESS_WITH_INFO)
        {
            printf("db.course is as following:\n");
           printf("----------------------------------------------------------------\n");
            printf("cno       cname               lhour          credit          semester\n");
            retcode=SQLFetch(hstmt);
		   /*SQLFetch fetches the next rowset of data from the result set and returns data for all bound columns.
相当与SQLFetchAdvances和SQLGetData两个函数*/
            while(retcode==SQL_SUCCESS || retcode==SQL_SUCCESS_WITH_INFO)
            {                             
                printf("%-10s%-20s%-15d%-16d%-5s\n", ccell->cno,ccell->cname,ccell->lhour,ccell->credit,ccell->semester);
                retcode=SQLFetch(hstmt);
            }
            printf("----------------------------------------------------------------\n\n");
        }
    }
    
    SQLFreeStmt(hstmt,SQL_DROP);//释放语句句柄
}
           
int main()
{
    HENV env;             //定义环境句柄
    HDBC hdbc;            //定义链接句柄
    HSTMT hstmt;         //定义语句句柄
    RETCODE retcode;

    retcode=SQLAllocEnv(&env);  //初始化ODBC环境，返回环境句柄
    
    if(retcode==SQL_SUCCESS || retcode==SQL_SUCCESS_WITH_INFO)
    {
        retcode=SQLAllocConnect(env,&hdbc);  //为连接句柄分配内存并返回链接句柄
        
        if(retcode==SQL_SUCCESS || retcode==SQL_SUCCESS_WITH_INFO)
        {
            retcode=SQLConnect(hdbc,(SQLCHAR*)"test",SQL_NTS,(SQLCHAR*)"root",SQL_NTS,(SQLCHAR*)"",SQL_NTS);   //连接一个SQL数据资料

            if(retcode==SQL_SUCCESS || retcode==SQL_SUCCESS_WITH_INFO)
            {      
                show_course(hdbc);				//显示初始的course内容
                
                for(int i=0; i<3; i++)		//依次执行插入、修改、删除操作
                {
                    retcode=SQLAllocStmt(hdbc,&hstmt);
//为语句句柄分配内存，并返回语句句柄
                    retcode=SQLExecDirect(hstmt,sql_dolist[i],SQL_NTS);
				 //把SQL语句送到数据库服务器，请求执行由SQL语句定义的数据库访问
                    printf("%s ", do_name[i]);

                    if(retcode==SQL_SUCCESS || retcode==SQL_SUCCESS_WITH_INFO) printf("success!\n\n");
                    else printf("fail!\n\n");
                    SQLFreeStmt(hstmt,SQL_DROP);  //释放与语句句柄相关的资源
                
                    show_course(hdbc);//显示操作后的新course内容
                }

                SQLDisconnect(hdbc);  //切断连接
            }
            SQLFreeConnect(hdbc);    //释放与连接句柄相关的资源
        }
        SQLFreeEnv(env);    		   //释放与环境句柄相关的资源
}

    return 0;
}

数据库完整性与安全性

定义各基表的主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE sc(
	sno varchar(6) NOT NULL,
	cno varchar(3) NOT NULL,
	grade int(11) DEFAULT NULL,
	PRIMARY KEY (sno,cno)
)DEFAULT CHARSET = gbk;

Create Table student (
  sno varchar(6) NOT NULL,
  sname varchar(6) DEFAULT NULL,
  sex varchar(2) DEFAULT NULL,
  bdate datetime DEFAULT NULL,
  dept varchar(8) DEFAULT NULL,
  classno varchar(3) DEFAULT NULL,
  PRIMARY KEY (sno)
) DEFAULT CHARSET=gbk；

Create Table course (
  cno varchar(3) NOT NULL,
  cname varchar(12) DEFAULT NULL,
  lhour int(11) DEFAULT NULL,
  credit int(11) DEFAULT NULL,
  semester varchar(2) DEFAULT NULL,
  PRIMARY KEY (cno)
) DEFAULT CHARSET=gbk；
删除和添加主键外键
1
2
3
4
5
6
7
8
alter table course drop primary key;
alter table course add primary key(cno);

alter table sc add constraint foreign key<sno> references student<sno>;
alter table sc add constraint foreign key<cno> references course<cno>;

show create table course;
查看各表信息

验证完整型约束：

分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整型约束。可以看出对student和course插入具有相同学号和课程编号的学生数据，都失败了，验证其实体完整型约束。

向学生课表插入一条课表编号课程表中没有的，验证参照完整型约束。插入失败。

定义存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

首先定义存储过程的结束符

1
2
DELIMITER //
用delimiter修改结束符为//

声明存储过程

1
create procedure demo_in_parameter(IN p_in int)

存储过程开始和结束符号:

1
BEGIN .... END

变量赋值:

1
SET @p_in=1

变量定义:

1
DECLARE l_int int unsigned default 4000000;

创建mysql存储过程、存储函数:

1
create procedure 存储过程名(参数)

存储过程体:

1
create function 存储函数名(参数)
示例：

创建数据库备份数据用于示例：

1
2
3
4
create database db1;
use db1;
create table PLAYERS as select * from TENNIS.PLAYERS;
create table MATCHES as select * from TENNIS.MATCHES;

存储过程示例：「删除给定球员参加的所有比赛」

1
2
3
4
5
6
7
8
delimiter //   #将语句的结束符号从分毫；临时改为两个//
# 使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。
create procedure delete_matches(IN p_playerno INTEGER)
BEGIN
	delete from MATCHES
	where playerno = p_playerno;
END//
delimiter; #将语句的结束符号恢复为分号

调用存储过程：

1
call sp_name[(传参)]；

触发器

MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

创建触发器语法：

1
2
3
4
5
6
CREATE TRIGGER trigger_name #name
trigger_time	#触发时机，before/after
trigger_event ON tbl_name	#触发事件：insert/update/delete 建立触发器表名，在哪张表建立触发器。
FOR EACH ROW
trigger_stmt	#触发器程序体，可以是SQL语句也可以是BEGIN和END包含的多条语句（delimiter）
# 由此可见，可以创建（2x3）六种触发器

示例：

假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：

1
2
3
4
5
6
7
8
9
10
DELIMITER $
create trigger tri_stuInsert
AFTER INSERT
ON student FOR EACH ROW
BEGIN
declare c int; #变量定义
set c = (select stuCount from class where classID=new.classID); #变量赋值
update class set stuCount = c + 1 where classID = new.classID; #变量操作
END$
DELIMITER ;

数据库查询分析

深入理解分组查询

例题：把统计时间（statistic）相同的，服务域（service_domain_moid）a中的hd100的数量和hd100s的数量的和计算出来：

首先，我们把service_domain_moid为a的筛选出来，如下：

1
2
SELECT hd100, hd100s FROM statistic
WHERE service_domain_moid="a";

其次： 我们把统计时间（statistic_time）相同的分组，求和

1
2
3
SELECT sum(hd100),sum(hd100s)
FROM statistic WHERE service_domain_moid="a"
GROUP BY statistic_time;

最后，统计结果按降序排序：

1
2
3
4
SELECT sum(hd100),sum(hd100s)
FROM statistic WHERE service_domain_moid="a"
GROUP BY statistic_time
ORDER BY statistic_time desc;

GROUP BY关键字可以将查询的结果按某个字段或多个字段进行分组，字段中值相等的为一组。其语法规则如下：

1
GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]

ORDER BY关键字可以对记录进行排序，其语法规则如下：

1
ORDER BY 属性名 [ASC|DESC]

数据库练习题：

01 按部门名称查询出员工的人数大于等于100的语句.(员工:emp_employee, 部门名称: department,员工姓名chnalias.)

1
2
SELECT department FROM emp_employee
GROUP BY department HAVING COUNT(*) >= 100;

02 把员工的姓名、性别和年龄显示出来。（员工表emp_employee，员工姓名：chnalias，年龄age，性别：gender。性别表：pub_gendar_info，性别名称：smpalias,与员工表的性别无关）

1
2
SELECT e.chnalias, e.smpalias, e.age 
FROM emp_employee e INNER JOIN pub_gender g ON(e.gender = g.smpalias)

04 删除员工表中的姓名重复的数据，只保留重复数据中的一条数据。（员工表emp_employee,员工姓名：chnalias）

1
2
3
4
DELETE FROM emp_employee
WHERE id NOT IN(
    SELECT MAX(id) AS id FROM emp_employee GROUP BY chnalias
)

AS关键字为查询的字段起一个别名，例如上面的语句里，给MAX(id)起个别名id【AS关键字可省略】

05 查询出差表中的每个员工最近的出差时间和员工姓名(出差表emp_travel,员工姓名chnalias,出差时间godate)

1
SELECT chnalias, godate FROM emp_travel ORDER BY godate DESC

数据库查询练习

01 没有选修课程编号为C1的学生姓名【多表查询】

首先找出C1对应的chosen_class表中的c_id

1
2
SELECT chose_class.c_id FROM class, chosen_class 
WHERE class.c_id = chose_class.c_id AND c_name = "C1";

然后找出没有选修C1的学生

1
2
3
4
5
SELECT s_name FROM student, chose_class
WHERE student.s_id = chose_class.s_id AND c_id NOT IN (
    SELECT chose_class.c_id FROM class,chose_class
    WHERE class.c_id = chose_class.c_id AND c_name = "C1"
);

02 列出每门课程名称和平均成绩，并按照程序排序

先分组查找计算每门课对应的平均成绩：

1
2
SELECT c_id, avg(grade) avggrade FROM chosen_class
GROUP BY c_id

然后多表查询（名称+成绩）并按照程序排序：

1
2
3
4
5
SELECT c.c_name, avggrade FROM(
    SELECT c_id, avg(garde) avggrade FROM chosen_class
    GROUP BY c_id) b JOIN class c
    ON c.c_id = b.c_id
    ORDER BY b.avggrade DESC;

03 选了2门课以上的学生姓名

统计学生id对应的选课数

1
2
SELECT s_id, count(*) countclass FROM chosen_class
GROUP BY s_id
1
2
3
4
SELECT s.s_name, b.countclass FROM(
    SELECT s_id, count(*) countclass FROM chosen_class
    GROUP BY s_id) b JOIN student s
    ON (s.s_id = b.s_id)
0%