MYSQL数据库复习资料 - 图文

1.1★数据库（DB）是存放数据的仓库，这些数据存在一定的关联，并按照一定的格式存放在计算机上。 ★数据库管理系统（DBMS）是管理数据库的系统，它按照一定的数据模型组织数据。

★主流关系型数据库管理系包括Oracle SQL server My SQL DB2 Sybase INFORMIX INGRES Access Visual FoxPro 等。

★数据库模型包括：层次模型、网状模型、关系模型（表格中的一列成为一个记录，一列成为一个字段），在关系表中，如果一个字段或最小几个字段组合的值可唯一标志其对应记录，则称该字段或字段组合为码。 1.2关系数据库设计

★通常，把每一类数据对象的个体称为“实体”，而每一类对象个体的集合称为“实体集”。实体集中的实体彼此是可区别的，如果实体集中的属性或最小属性组合的值能唯一标志其对应实体，则将该属性组合称为码。对于每一个实体集，可指定一个码为主码。

一对一的联系

一对多的联系（将下图的选课改为属于，M改为I）

多对多的联系

★ODBC即开放式数据库互联 ADO/ADO.NET/JDBC数据库接口 ★客户/服务器(CS)模拟应用系统（说明此图意思）

当应用程序需要处理数据库中的数据时，首先向数据库管理系统发送一个数据处理请求，数据库管理系统接受这一请求后对其进行分析，然后执行数据操作，并把操作结果返回给应用程序。 ★关系数据库语言

关系数据库的标准语言是SQL 具有丰富的查询系统、数据定义和数据控制功能 。 SQL语言的功能：数据查询、数据操纵、数据定义、数据控制。 SQL数据库的特点：

1使用核心线程的完全多线程服务； 2可运行在不同平台；

3使用C和C++编写，并使用多种编译器进行测试，保证了源代码的可移植性。

4支持AIX FreeBSD HP-UX Linux Mac OS Novell Netware Open BSD windows等多种操作系统 5 支持多线程 充分利用CPU资源 6可以处理上万条大型数据库数据

第二章

空值（null）概念及其作用：空值通常表示未知，若一列允许为空值，则向表中输入记录值时可不为该列给出具体值。而一列不允许为空值时，则在输入时必须给出该列的具体值。

列的IDENTITY概念：对于任何表都可以创建包含系统所生成序号值的一个标志列该序号值唯一标志表中的一列，可作为键值。

创建附录A学生成绩数据库及其表结构： create database xscj; use xsc;

create table xs

(sno char(6) not null primary key; sname char(8) not null; major char(10);

sex tinyint not null comment 'female 0,male '1''; bir date not null; score tinyint pic blob; add text);

写出创建产品销售数据库CPXS和表的SQL语句。 create database cpxs

create table 产品表(产品编号 varchar(20) primary key ,产品名称 varchar(50),价格 money ,库存量 int )

create table 销售商表(客户编号 varchar(20) primary key,客户名称 varchar(20),地区 varchar(50),负责人 varchar(20),电话 varchar(20))

create table 产品销售表(

销售日期 datetime not null,

产品编号 varchar(20) not null FOREIGN KEY REFERENCES 产品表(产品编号),

客户编号 varchar(20) not null FOREIGN KEY REFERENCES 销售商表(客户编号),销售数量 int,销售额 money)

ALTER TABLE 产品销售表 ADD CONSTRAINT PK_产品销售表 PRIMARY KEY CLUSTERED (销售日期, 产品编号, 客户编号 )

alter table 产品表 add 产品简介 text

insert into 产品表(产品编号,产品名称,价格,库存量) values ('01','空调',30000,100)

update 产品表 set 价格=价格*0.8 delete from 产品表 where 价格<50

——————————————华丽分割线————————————————————————

Join连接：内连接：指定了INNER关键字的链接是内连接

【例4.24】 用FROM的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。

SELECT XS.学号, 姓名, 课程名, 成绩

FROM XS JOIN XS_KC ON XS.学号 = XS_KC.学号 JOIN KC ON XS_KC.课程号 = KC.课程号 WHERE 课程名 = '计算机基础' AND 成绩>=80 ;

外连接：指定了outer关键字的链接为外连接。

交叉链接：指定了cross join关键字的链接是交叉链接。

Where子句

【例4.32】 查询XSCJ数据库XS表中学号为081101的学生的情况。 SELECT 姓名,学号,总学分 FROM XS

WHERE 学号='081101';

Group by子句

【例4.59】 在XSCJ数据库上产生一个结果集，包括每门课程各专业的平均成绩、每门课程的总平均成绩和所有课程的总平均成绩。

SELECT 课程名, 专业名, AVG(成绩) AS '平均成绩' FROM XS_KC, KC,XS

WHERE XS_KC.课程号 = KC.课程号 AND XS_KC.学号 = XS.学号 GROUP BY 课程名, 专业名 WITH ROLLUP;

视图：视图是一个或多个表导出的表。视图与表不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。视图一经定义以后，就可以像表一样被查询、修改、删除和更新。使用视图有下列优点： 不可更新视图：（1）定义视图的用户必须对所参照的表或视图有查询（即可执行SELECT语句）权限； （2）不能包含FROM子句中的子查询； （3）不能引用系统或用户变量； （4）不能引用预处理语句参数；

4、写出SQL语句，对产品销售数据库进行如下操作：

（1）查找价格在2000~2900之间的商品名。 use cpxs go select 产品名称 from 产品表 where 价格>=2000 and 价格<2900 （2）计算所有商品总价格。 use cpxs go select sum(价格*库存量) as 所有商品总价格 from 产品表 （3）在产品销售数据库上创建冰箱产品表的视图BXCP。 use cpxs go create view bxcp as select * from 产品表 were 产品名称=冰箱 with check option （4）在BXCP试图上查询库存量在100台以下的产品编号。 use cpxs go Select 产品编号 from bxcp were 库存量<100

索引的分类：

1. 普通索引（INDEX）

这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。 2. 唯一性索引（UNIQUE）

这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。

3. 主键（PRIMARY KEY）

主键是一种唯一性索引，它必须指定为“PRIMARY KEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。 4. 全文索引（FULLTEXT）

MySQL支持全文检索和全文索引。

【例5.5】 创建XS_KC表的语句如下，XS_KC表带有学号和课程号的联合主键，并在成绩列上创建索引。 CREATE TABLE XS_KC ( 学号 CHAR(6) NOT NULL, 课程号 CHAR(3) NOT NULL, 成绩 TINYINT(1), 学分 TINYINT(1), PRIMARY KEY(学号,课程号), INDEX CJ(成绩) );

主键约束：

【例5.5】 创建XS_KC表的语句如下，XS_KC表带有学号和课程号的联合主键，并在成绩列上创建索引。 CREATE TABLE XS_KC ( 学号 CHAR(6) NOT NULL, 课程号 CHAR(3) NOT NULL, 成绩 TINYINT(1), 学分 TINYINT(1), PRIMARY KEY(学号,课程号), INDEX CJ(成绩) );

【例5.8】 创建表XS1，将姓名定义为主键。 CREATE TABLE XS1 (

学号 varchar(6) NULL,

姓名 varchar(8) NOT NULL PRIMARY KEY , 出生日期 datetime );

主键规则：

1每个表只能定义一个主键

2关系模型理论要求，必须为每个表定义一个主键

3表中的两个不同的行在主键上不能具有相同的值。这就是唯一性的原则 4一个列名在一个主键的列的列表中只能出现一次。

变量：

【例6.10】 查询例6.7中创建的变量name的值。 SELECT @name; 比较运算符:

下面这个例子说明了在不同的情况下MySQL以不同的方式处理数字和字符串。 【例6.21】 执行下列语句： SELECT 5 = '5ab','5'='5ab';

数学函数：数学函数用于执行一些比较复杂的算术操作 日期和时间函数： 6）DAYNAME()

和MONTHNAME()相似，DAYNAME()以字符串形式返回星期名： SELECT DAYNAME('2008-06-01');

存储过程：

优点：执行速度快；提高了系统性能；切那批数据库的安全； 流程控制语句:

例7.6】 创建XSCJ数据库的存储过程，判断两个输入的参数哪一个更大。 DELIMITER $$

CREATE PROCEDURE XSCJ.COMPAR (IN K1 INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) ) BEGIN IF K1>K2 THEN SET K3= '大于'; ELSEIF K1=K2 THEN SET K3= '等于'; ELSE SET K3= '小于'; END IF; END$$

DELIMITER ;

说明：存储过程中K1和K2是输入参数，K3是输出参数。 【例7.9】 创建一个带WHILE循环的存储过程。 DELIMITER $$

CREATE PROCEDURE dowhile() BEGIN

DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO

SET v1 = v1?1; END WHILE; END$$

DELIMITER ;

创建一个带WHILE循环的累加和 DELIMITER $$

CREATE PROCEDURE dowhile() BEGIN set @x=0; set@v=0;

declare v1 in default 1;

while v1<100 do set v1=v1+1; set@x=@x+@v end while; end$$

调用存储过程

【例7.17】 创建一个存储过程，有两个输入参数：XH和KCM，要求当某学生某门课程的成绩小于60分时将其学分

修改为零，大于等于60分时将学分修改为此课程的学分。 DELIMITER $$

CREATE PROCEDURE XSCJ.DO_UPDATE(IN XH CHAR(6), IN KCM CHAR(16)) BEGIN

DECLARE KCH CHAR(3); DECLARE XF TINYINT; DECLARE CJ TINYINT;

SELECT 课程号, 学分 INTO KCH, XF FROM KC WHERE 课程名=KCM; SELECT 成绩 INTO CJ FROM XS_KC WHERE 学号=XH AND 课程号=KCH; IF CJ<60 THEN

UPDATE XS_KC SET 学分=0 WHERE 学号=XH AND 课程号=KCH; ELSE

UPDATE XS_KC SET 学分=XF WHERE 学号=XH AND 课程号=KCH; END IF; END$$

DELIMITER ;

触发器：

触发器是一个被指定关联到一个表的数据对象，触发器是不需要调用的，当对一个表的特别事件出现时，它会被激活。 触发器用于保护表中的数据，通过触发器可以实现多个表间数据的一致性和完整性。 事件：事件的主要作用如下： ● 关闭账户；

● 打开或关闭数据库指示器；

● 使数据库中的数据在某个间隔后刷新； ● 执行对进入数据的复杂的检查工作。

事件和触发器相似，都是在某些事情发生的时候启动。当在数据库上启动一条触发语句的时候，触发器就启动了；而事件是根据调度事件来启动的

本文来源：https://www.bwwdw.com/article/sq3r.html