计算机二级MySQL基本知识点

计算机二级MySQL基本知识点

※数据库：数据库是指长期存储在计算机内的、有组织的、可共享的数据集合。

※数据库管理系统：是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。

※DBMS的主要功能： ①数据定义功能； ②数据操纵功能；

③数据库的运行和管理； ④数据库的建立和维护功能；

⑤数据库的通信功能（提供方便、有效存储数据库信息的接口和工具）；

※数据库系统：是指在计算机系统引入数据库后的系统；一个完整的数据库系统一般包括数据库、数据库管理系统、应用开发工具、应用系统、数据库管理员和用户构成。 ※数据库系统的特点： ①数据结构化； ②数据独立性高； ③数据共享性好； ④数据冗余度低；

⑤数据由DBMS统一管理和控制；

※内模式：对数据物理结构和存储方式的描述，是数据在数据库内部的具体表示方式。 ※模式：对数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。 ※外模式：对数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。（外模式也称为子模式） ※三层模式使各层数据保持独立：

①层次独立性：数据库的整体逻辑结构和特征的描述是独立于数据库其他层次结构的描述； ②存储模式独立性：数据库的内部存储结构依赖于概念模式，但存储模式独立于外部模式，也独立于具体的存储设备；

③外模式独立性：用户逻辑结构（外模式）是在全局逻辑结构的描述的基础上定义的，它面向具体的应用程序，独立于内部模式和存储设备。 ④应用程序独立性：特定的应用程序是在外模式描述的逻辑结构上编写的，它依赖于特定的外模式，与数据库的模式和存储结构独立。

※映象：是一种对应规则，它指出映象双方是如何进行转换的。 ※两层映象：外模式/模式映象、模式/内模式映象。 ※数据库系统的应用结构： ①客户/服务器（C/S）结构

优点：运行速度快、表现能力强

缺点：需要专门的客户端，不能跨平台

②浏览器/服务器（B/S）结构——eg：百度、淘宝等网站。 优点：基于网络语言，与操作系统无关，可以跨平台 ※数据模型：对现实世界数据特征的抽象。

※概念模型：是面向数据库用户的现实世界的模型。 ※模型：对现实世界特征的模拟和抽象。 ※实体：客观存在并且可以相互区别的事物。 ※属性：实体所具有的某一特性。

※联系：连接实体之间的一种关系。 ※码：唯一标识实体的属性集。

※域 ：域是一组具有相同数据类型的值的集合。 ※实体型：同型实体的集合称为实体集。

※E/R图 ：表示实体型、属性和联系的方法。

※E-R图为实体-联系图，提供了表示实体型、属性和联系的方法，用来描述现实世界的概念模型。表示方法如下：

· 实体型：用矩形表示，矩形框内写明实体名；

· 属性：用椭圆形表示，并用无向边将其与相应的实体连接起来；

· 联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（1 : 1，1 : n或m : n）。 ※两个实体之间联系的种类：

①一对一联系（1:1）:实体集A中的一个实体至多与实体集B中的一个实体相对应，反之亦然，则称实 体集A与实体集B为一对一的联系。记作1:1。 如：班级与班长，观众与座位，病人与床位。

②一对多联系（1:n）:实体集A中的一个实体与实体集B中的多个实体相对应，反之，实体集B中的一个实体至多与实体集A中的一个实体相对应。记作1:n。 如：班级与学生、公司与职员、省与市。

③多对多（m:n）:实体集A中的一个实体与实体集B中的多个实体相对应，反之，实体集B中的一个实体 与实体集A中的多个实体相对应。记作（m:n）。 ※关系模式：对关系的描述。 ※主码（主键）：是表中的一个或多个字段，它的值用于唯一的标识表中的某一天记录。 ※外码（外键）：用于建立和加强两个数据间的链接的一列或者多列。 ※元组：表中的行称为元组；

※行：表中的一行记录，表中的数据都是按行存储的。

※列：表中的一个字段，所有表都是由一个或者多个列组成的。 ※关系模型规范化的设计方法：通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常，删除异常，更新异常和数据冗余的问题。

※INF：关系数据库中的关系要满足一定要求的，满足不同程度要求的为不同范式，满足最低要求的叫第一范式。

定义：任给关系R，如果R中每个列与行的交点处的取值都是不可再分的基本元素，则R达到第一范式，简称1NF。

2NF：若R属于1NF，且每一个非主属性完全函数依赖于任何一个候选码，则R属于2NF 3NF：每一个非主属性既不传递依赖于码，也不部分依赖于码。

BCNF：如果一个关系R中所有属性都不传递依赖于R的任何候选关键字，或者说关系R的每个决定因数都是候选关键字时，则称关系R属于BCNF范式。

也即通过消除主键列对主键的部分函数依赖和传递函数依赖，将3NF规范为BCNF。 ※数据库结构设计的不同阶段形成数据库的各级模式，即：

①在概念设计阶段形成独立于机器特点，独立于各个DBMS产品的概念模式，在本篇中就是E-R图；

②在逻辑设计阶段将E-R图转换成具体的数据库产品支持的数据模型，如关系模型，形成数据库逻辑模式；然后在基本表的基础上再建立必要的视图， 形成数据的外模式；

③在物理设计阶段，根据DBMS特点和处理的需要，进行物理存储安排，建立索引，形成数据库内模式。

概念模式是面向用户和设计人员的，属于概念模型的层次；逻辑模式、外模式、内模式是DBMS支持的模式，属于数据模型的层次。可以在DBMS中加以描述和存储。

课后总复习错题

10.设有E-R图，含有A、B两个实体，A、B之间的联系类型是M：N，则将该E-R图转换为关系模式时，关系模式的数量是3. 解释：

一般情况下，在设计数据库的时候，如果存在多对多的情况

那么就必须将其分解为两个一对多的情况。也就是说，两个实体多对 多的关系，分解后必然在它们之间再出现一个实体来连接它们的关系。 举个例子来说：学生和课程之间它们是多对多的关系，因此在设计的时候 就需添加选课表了。这样答案就是3.

当两个实体是一对多的关系时，那就不需要分解了，这是就是2. 15.数据库、数据库管理系统和数据库系统三者之间的关系是： 数据库系统包括数据库和数据库管理系统。

20.在讨论关系模型时，与“属性”同义的术语是列。 21.下列关于数据的叙述中，错误的是（A）

A.数据的种类包括文字、图形和图像三类； B.数字只是简单的一种数据；

C.数据是描述事物的符号记录； D.数据是数据库中存储的基本对象。

※phpMyAdmin：是一个以PHP为基础，以Web-Base方式架构在网站主机上的MySQL的数据库管理工具。

注意：由于phpMyAdmin是PHP程序，因此需要LAMP或者WAMP运行环境。

※SQL：结构化查询语言，是关系数据库的标准语言，也是一个通用的、功能极强的关系数据库语言。

※SQL的特点（优点）：

①综合统一，具有一体化特点

②高度非过程化，是一种是面向对象的操作方式 ③语言简洁、易学易用 ※DDL：数据定义语言 DML：数据操纵语言 DCL：数据控制语言 ※比较运算符：

运算符 = > < >= <= <>、!= !

含义 相等 大于 小于 大于等于 小于等于 不等于 不小于/不大于 ※逻辑运算符：

运算符 ALL AND/&& ANY/SOME BETWEEN EXISTS IN LIKE NOT/! OR/ ||

※位运算符：

运算符 & | ^ ~ >> << 运算规则 位AND 位OR 位XOR 位取反 位右移 位左移 含义 如果一组的比较都为TRUE，那么就为TRUE 如果两个布尔表达式都为TRUE，那么就为TRUE 如果一组的比较中其中有任何一个为TRUE，那么就为TRUE 如果操作数在某个范围以内，那么就为TRUE 如果子查询中包含一些行，那么就为TRUE 如果操作数等于表达式列表中的一个，那么就为TRUE 如果操作数与一种模式相匹配，那么就为TRUE 对任何其他布尔运算符的值取反 如果两个布尔表达式中的一个为TRUE,那么就为TRUE ※内置函数的主要分类：

①数学函数：用于执行一些比较复杂的算术操作的函数，例如ABS()函数、SORT()函数； ②聚合函数：特意为求和或者对表中的数据进行集中概括而设计的函数；例如，COUNT()函数。

③字符串函数：为2字符串操作而设计的函数；例如，ASCII()函数、CHAR()函数。 ④日期和时间函数：操作日期和时间的函数；例如，NOW()函数、YEAR()函数。 ⑤加密函数：对数据进行加密的函数；例如ENCODE()函数、ENCRYPT函数。 ⑥控制流函数：用来进行条件操作的函数；例如IF()函数。

⑦格式化函数：为格式化数据设计的函数；例如FORMAT()函数。

⑧类型转化函数：可以把一个值转换为指定的数据类型的函数；例如，CAST()函数。 ⑨系统信息函数：获得系统本身的信息的函数；例如USER()函数、VERSION()函数。

课后总复习错题

2.在MySQL中，NULL的含义是（C）

A.空串 B.数值0 C.无值 D.FALSE

NULL和空串的区别：NULL是指该字段没有值，而空串代表的是该字段有值。

5.在安装和配置MySQL实例的向导中，可选的MySQL服务器类型包括Developer Machine（开发者机器）、Server Machine （服务器）、Dedicated MySQL Server Machine（专用MySQL服务器）

7.使用MySQL时，可以在MySQL客户端中执行SQL语句，但下面无法用于执行SQL语句的客户端工具是mysqld（C）。

A.mysql命令行 B.phpMyAdmin C.mysqld D.Navicat工具 8.函数NOW()返回的结果是系统的当前日期和时间。

※数据对象：性质相同的数据元素的组合。 ※数据类型：系统中所允许的数据的类型。

※在MySQL中可以利用SHOW ENGINES语句来显示可用的数据库引擎和默认引擎。InnoDB是系统的默认引擎，其支持可靠的事务处理。 ※一般语法描述所使用的符号如下：

<>：表示在语句中必须指定数据对象，是不可缺少的。 []：表示可以根据需要进行选择。 |：表示多个选项只能选择其一。 {}：表示必选项。

问题：mysql命令行输入时，忘记输入分号；就按了回车，就会出现->，然后回不到上一行怎么办？

若是没有分号结束的话，不管多少行都会视作一句，如果已经输好了程序但是忘了输入分号按了回车，接着在出现->的后边输入分号即可，然后按回车就会执行前面的sql。

例1：在MySQL中创建一个名为mytest的数据库。 Mysql>Create database mytest;

可以在这里加上if exists用于判断是否存在这个数据库，用于防止数据库不存咋时发生错误。

例2：修改已有数据库mytest的默认字符集和校对规则。 Mysql>alter database mytest

->default character set gb2312

->default collate gb2312_chinese_ci;

其中character set 子句用于更改默认的数据库字符集。

例3：首先删除一个尚未创建的数据库“an_test”，然后在删除语句中加上if exists语句以后再操作一次。

出现报错的现象；

加上if exists 语句以后，可以正常执行； Mysql>drop database if exists an_test;

例4：列出当前用户可查看的数据库列表。

Mysql>show databases;——显示权限范围内的所有数据库名；

Mysql>show databases like 'mytest';——显示与‘mytest’相匹配的数据库名。

例5：在一个已有的数据库mytest中新建一个包含姓名、性别、年龄、专业、联系方式等字段的学生的基本信息表，要求将字段student_id号指定为该表的主键，并使用InnoDB引擎存储表数据。 mysql> use mytest Database changed

mysql> create table students -> (

-> student_id int not null auto_increment, -> student_name char(50) not null,

-> student_sex char(1) not null default 0, -> student_age int not null,

-> student_major char(50) not null, -> student_contact char(50) null, -> primary key(student_id)) -> engine=innodb;

※在mysql中写sql语句回车后如何更改上一句话? 右键单击“mysql”的“dos窗口”上面的 蓝色横条

选择编辑->标记->然后按住鼠标左键选中你要修改的语句，再去右击蓝色横条选择编辑->复制就把这条你要修改的语句复制到剪贴板了然后去记事本里粘贴修改！

在记事本里写好命令复制，然后右键单点“mysql”的“dos窗口”上面的蓝色横条编辑->粘贴然后执行，当然这只是windows下的办法，要一次执行很多行代码的时候比较省事。

例6：向数据库mytest的表students中添加一列，并且命名为student_from，用于描述学生的生源地，要求不能是NULL，且该列位于原表列student_sex列之后。 Mysql>use table mytest.students

->add column student_from char(10) not null after student_sex;

※change子句：同时修改表中指定列的名称和数据类型。

例7：将数据库mytest中标students的student_from 列重命名为student_city，且数据类型为char(20)，允许为NULL。

Mysql>alter table mytest.students

->change column student_from student_city char(20) null;

※Alter子句可以修改或者删除表中指定列的默认值。

例8：将数据库mytest中表students的student_sex列的默认值改为1（代表‘女’）. Mysql>alter table mytest.students

->alter column student_sex set default 1;

※modify子句可以修改指定列的数据类型，但不会干涉它的列名。 与change的区别：change是可以将列名和数据类型都一起改了的。

例9：将数据库mytesr中的表students的students_name列的数据类型由char(50)更改为char(20)，并且将此列设置为该表的第一列。 Mysql>alter table mytest.students

->modify column student_name char(20) first;

注意：add和modify都可以通过first或者after关键字来修改指定列在表中的位置。

例10：删除数据库mytest中students的student_contact列。 Mysql>alter table mytest.students

->drop column student_contct;

※rename子句可以为表重新赋予一个表名。

例11：使用rename子句，重新命名数据库mytest中表students的表名为university_students. Mysql>alter table mytest.students

->rename to mytest.university_students; 或者使用另一种格式：

例12：使用rename table 子句将数据库中mytest的university_students重新命名为students Mysql>rename table mytest.university_students to mytest.students;

※复制表的语法格式：

Create table <表名>[LIKE|AS<旧表名>]

LIKE：可以创建一个与旧表结构相同的表，其中列名、数据类型、空指定和索引都将复制到新表，但是表的内容不会复制，因此创建的是一个和原表结构相同的空表；

AS：如果在复制表结构的同时，复制表的内容，可以用AS子句来完成。使用AS子句可以复制表的内容，但是索引和完整性约束不会被复制。

例13：在数据库mytest中创建一份表students的拷贝students_copy。 Mysql>create table mytest.students_copy like mytest.students;

※删除表：可以使用drop table子句删除表（可以同时删除多个表） 例14：删除数据库mytest的表students_copy。 Mysql>drop table mytest.students_copy;

※显示表的名称：show tables 方法一：

Mysql>use mytest; Mysql>show tables;

这里的use引用了数据库以后也可以不用“；”直接就接下一个show tables； 方法二：

Mysql>show tables from mytests;

※显示表的结构：show columns（可以理解为每一列相当于属性，也即为表中的结构） 例16：显示数据库mytest中表students的结构。 Mysql>show columns from mytest.students;

注意：mysql支持用describe或者desc作为show columns from的一种快捷方式。 Mysql>desc mytest.students;

※关于空值（NULL）：表的关键字不允许为空值，且任意两个空值不相等。

※关于标志（IDENTITY）属性：每个表中只有一个列设置为标志属性，并且该列只能是DECIMAL、INT、NUMERIC、SMALLINT、BIGINT、TINYINT数据类型。 ※关于列类型的隐含改变：

①长度小于4的VARCHAR类型会被改变为CHAR类型；

②当一张表中包含任何变长的列时，如VARCHAR、TEXT、BLOB类型的列，该表中所有大于3个字符的其他CHAR类型列会被改变为VARCHAR类型列； ③TIMESTAMP类型的列值显示尺寸必须是在偶数2~14范围内；

④不能在TIMESTAMP中存储空值NULL，所以当该列的值设置为NULL的时候，MySQL

会默认设置为它当前的日期时间。

课后总复习错题

5.查看当前正在使用的工作数据库名称的语句是（C）

A.SHOW DATABASES; B.SHOW TABLES; C.SELECT DATABASE(); D.SHOW SCHEMAS; 6.在MySQL数据库中，以下不会受字符集设置影响的数据类型有（B） A.CHAR B.INT C.VARCHAR D.TEXT （参见以上资料——列类型的隐含改变）

※INSERT语句的三种形式：

①INSERT?VALUES：向表中插入一行数据，也可以插入多行数据； ②INSERT?SET：指定插入行中每列的值，也可以指定部分列的值； ③INSERT?SELECT：向表中插入其他表的数据； 例1：使用INSERT?VALUES语句向数据库mytest的表students中插入这样一行完整数据：（1320，‘王丽’，‘1’，22，‘计算机专业’，‘138xxxxxx’）。 Mysql>set names gbk; /*改变字符集*/ mysql> insert into mytest.students

-> values ('王丽',1320,'1','138xxxxxx',22,'计算机专业'); 由于表的结构如下：

+---------------+----------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra +---------------+----------+------+-----+---------+----------------+

| student_name | char(20) | YES | | NULL | | student_id | int(11) | NO | PRI | NULL | auto_increment | student_sex | char(1) | NO | | 1 | | student_contact | char(20) | YES | | NULL | | student_age | int(11) | NO | | NULL | | student_major | char(50) | NO | | NULL | +---------------+----------+------+-----+---------+----------------+ 应该按照显示的表结构输入数据。

注意：在MySQL不能输入中文或者现实不出中文的时候，可以使用SET NAMES GBK;语句来临时改变字符集。

例2：使用INSERT?VALUES语句向数据库mytest 表students中插入一行数据，这行数据只给出student_name、student_major和student_age列的信息，对应的值是“李明”、“数学专业”，“22”，其中student_id由系统自动生成，其他采用默认或者不指定值。 mysql> insert into mytest.students

-> values('李明',0,default,null,22,'数学专业');

※其中的0代表的是学号；由于AUTO_INCREMENT属性列的值是在表中其它列被赋值以后生成的，所以在对表中其它列做任何赋值操作时，对该AUTO_INCREMENT属性列的引用只会返回数字0.

还有一种方法：由于自动增长大的学号值可以不必列出，由系统自动生成，同时允许NULL值的列也可以不给出，因为系统允许其为空值，只需要输入提供的值即可。

Mysql>insert into mytest.students

->(student_name,student_sex,student_age,student_major) >values('李明',DEFAULT,22,'数学专业');

这种方法的好处是不必知道表结构，不会出现输入列与结构不对应的情况。

例3：使用INSERT?SET 语句来实现例2的数据插入需求。 Mysql>insert into mytest.students

->set student_name='李明',student_sex=default,student_age=22,student_major='数学专业'; 采用这种方式的好处：输入方式更为灵活。

例4：使用INSERT?VALUES一次向mytest表students中插入两行数据，数据分别为（‘张三’，‘1’，24，‘化学专业’）和（‘王五’，‘1’，23，‘数学专业’）。 mysql> insert into mytest.students

-> (student_name,student_sex,student_age,student_major) -> values('张三','1',24,'化学专业'),

-> ('王五','1',23,'数学专业');

例5：假设数据库mytest的表student中有一个拷贝备份表student_copy，现使用insert?select语句将student_copy中的数据合并到表students。 Mysql>insert into mytest.students

->(student_name,student_sex,student_age,student_major) ->select student_name,student_sex,student_age,student_major ->from mytest.sudents_copy;

※REPLACE语句的功能：插入的数据主键与已有数据的主键重复，则INSERT语句将无法插入此行。此时若是需要插入此行数据，则可以使用REPLACE语句来实现。则REPLACE的功能是替换主键重复的行数据。REPLACE的三种语法格式为：REPLACE?VALUES、REPLACE?SET、REPLACE?SELECT语句。 例6：使用INSERT 语句成功执行例2以后，数据库mytest表students会存在这样一条数据：('王丽',1320,'1','138xxxxxx',22,'计算机专业');现在向该表插入一条新数据：（‘李芳’，1320，‘1’，‘137xxxxxx’，26，‘会计专业’）。

如果直接插入，则程序会出现错误，原因是两条数据的主键都一样，都是1320。 所以，此程序应该为：

mysql> replace into mytest.students

-> values('李芳',1320,'1','137xxxxxx',26,'会计专业');

※使用DELETE语句从单个表中删除数据

DELETE FROM 表名 where子句/order by子句/limit 子句

Limit子句：用于告知服务器在控制命令被返回到客户端前删除行的最大值。

例7：使用DELETE 语句删除数据库mytest的表students中名字为‘王丽’的学生信息。 Mysql>delete from mytest.students where student_name='王丽';

例8：假设数据库中有三个表tbl1,tbl2,tbl3，它们均含有id列，现在要求删除表tbl1中id值等于tbl2的id值的所有行，以及表tbl2中id值等于tbl3的id值的所有行。

注意：弄清楚要删除的是哪个表。 Mysql>delete tbl1,tbl2 from tbl1,tb2l,tbl3

->where tbl1.id=tbl2.id and tbl2.id=tbl3.id;

※使用TRUNCATE删除表数据（清除表数据）：使用TRUNCATE以后，表中的AUTO_INCREMENT计数器重新被置为该列的初始值。对于参与了索引和视图的表，不能使用TRUNCATE语句删除数据，而应该使用DELETE语句。

※事务日志：事务日志是一个数据库文件分开的文件，它存储对数据库进行的所有更改，并全部记录插入、更新、删除、提交、回退和数据库模式变化。 ※使用UPDATE语句修改单个表：

例9：使用UPDATE语句将数据库mytest的表students中姓名为“张三”的学生的联系方式更新为“139xxxxxx”。

mysql> update mytest.students

-> set student_city='139xxxxxx'

-> where student_name='张三';

例10：使用UPDATE语句将数据库mytest的表students中名为“李芳”的年龄修改为23，将专业修改为“物理专业”。 mysql> update mytest.students

-> set student_age=23 and student_major='物理专业'

-> where student_name='李芳';

例11：假设数据库中有两个表tbl1和tbl2，他们都有两个名为id和name列，其中id列为各自的主键，现在要求当表tbl1和tbl2中id 值相同时，将表tbl1中name的列的值修改为“李明”，将表tbl2中name列的值为改为“王伟”。 Mysql>update tbl1,tbl2

->set tbl1.name='李明' and tbl2.name='王伟' ->where tbl1.id=tbl2.id;

课后总复习错题

3/7：学生表student包含sname、sex、age三个属性列，其中age默认值为20，执行SQL语句INSERT INTO student(sex,sname,age) VALUES('M','Lili', );的结果是（A） A.执行成功，sname,sex,age的值分别为Lili,M,20 B.执行成功，sname,sex,age 的值分别为M,Lili,NULL C.执行成功，sname,sex,age的值分别为M,Lili,20 D.SQL语句不正确，执行失败。 解析：首先排除B，既然有默认值，那么执行出来的结果就不可能是NULL。有默认值的age可以在VALUES值代入的时候显示“default”，也可以直接不用理会，直接不显示地填充值（注意：不是NULL，NULL是真的表结构规定的时候允许使用NULL而且真的没有相应值代入的时候才去使用）

注意：以上加粗的办法是最可行的，在有些版本的MySQL中并不支持直接不显示默认值的方法，会有出错提醒。

8.在使用insert?into插入记录时，对于AUTO_INCREMENT列，若需要使其值自动增长，下面填充方式中错误的是（D）

A.填充NULL值； B.不显示地填充值； C.填充数字0； D.填充数字1； 解析：填充数字0和1是制定默认值的时候；而自动增长通常会返回1即可，也可填充NULL值或者不显示地填充值。

※使用SELECT语句时，WHERE子句必须位于GROUP子句之前，GROUP子句必须位于HAVING子句之前。

例1：在MySQL数据库中执行“SELECT 1+4-2”运算。 Mysql>select 1+4--2;

※使用*可以返回所有列的数值。

例2：查询数据库mytest中表students中学生的姓名、年龄和专业信息。 mysql> select student_name,student_age,student_major

-> from mytest.students;

例3：查询数据库mytest中表students学生的所有信息。 Mysql>select * from mytest.students;

※使用别名的方法：

例4：查询数据库mytest中表students的student_name 和student_contact字段，并且要求student_contact列用“联系方式”来显示。

mysql> select student_name,student_contact as '联系方式'

-> from mytest.students; 或者

mysql> select student_name,student_contact 联系方式

-> from mytest.students;

※计算列值：

例5：查询数据库mytest的表students，输出每个学生的student_name和student_id列，同时要求输出student_id 加上数字100后构成的新列的值。 Mysql>select student_name,student_id,student_id+100 ->from mytest.students;

※替换查询结果中的数据：在对表进行查询的时候，如果希望得到某些列的分析结果，而不仅仅是查询的原始值，则可以在SELECT语句中替换这些列。 CASE表达式：

WHEN 条件1 THEN 表达式1 WHEN 条件2 THEN 表达式2 ??

ELSE 表达式

END [AS 列名]——列名可更改也可以不更改。 例6：查询数据库mytest中表students，输出student_name 和student_sex列，判断student_sex，如果为0，则显示为“男”，否则显示为“女”，并且在结果集中用“性别”来标注该列。 mysql> select student_name, /*要记得这里的逗号*/ -> case

-> when student_sex='0' then '男'

-> else '女' -> end as '性别'

-> from mytest.students;

※聚合函数：MySQL中的内置函数，常常用于对一组值进行计算或者统计，然后返回计算或者统计结果。

例7：查询mytest中students表的年龄最大值。（这里不需要用聚合函数） Mysql>select max(student_age) from mytest.students;

※连接查询的类型以及使用方法： ①交叉连接：用于实现一张表的每一行与另一张表的每一行的笛卡尔积，返回两张表的每一行相乘的所有可能的搭配结果。

例8：假设数据库中有两张表，分别是a和b，要求查询这两张表的交叉连接后的结果集。 首先查看a表中的数据行： Mysql>select * from mytest.a; id 1 2 接着查看b表中的数据行： Mysql>select * from mytest.b; id 1 2 3 计算a表和b表交叉连接的结果：

Mysql>select * from mytest.a cross join mytest.b; id 1 1 1 2 2 2 name a1 a1 a1 a2 a2 a2 id 1 2 3 1 2 3 name b1 b2 b3 b1 b2 b3 name b1 b2 b3 name a1 a2 ※内连接：利用条件表达式来消除交叉连接中的某些数据行，在FROM子句中使用关键字INNER JOIN连接两张表，并使用ON子句来设置连接条件。如果没有任何条件的话，INNER JOIN和 CROSS JOIN在语法上是等同的，两者可以互换。

需要注意的是：内连接是系统默认的表连接，所以在from子句后可以省略INNER关键字. 例9：在例7的表中，利用内连接查询出a表Id大于b表Id的结果集。 Mysql>select * from mytest.a inner join mytest.b on mytest.a.Id >mytest.b.Id;

※相等连接：使用运算符“=”

例10：在例8的表中，利用内连接查询出a表Id等于b表Id的结果集。 Mysql>select * from mytest.a inner join mytest.b on

->mytest.a.Id=mytest.b.Id;

※不等连接：内连接的一种，只是在ON子句中的连接条件使用除“=”的其他运算符。 ※自连接：内连接的一种，如果需要在一个表中查找具有相同列值的行，则可以考虑用自连接。

※自然连接：自然连接是只有在连接的列在两张表中的名称都相同时才会有用，否则返回的会是笛卡尔积。自然连接在FROM子句中使用关键字NATURAL JOIN。

※外连接：外连接分为左外连接和右外连接，首先将连接的表分为基表和参考表，然后再以基表为依据返回满足条件和不满足条件的记录。 ※关于特殊的比较运算符“<=>”的说明： 对于运算符“<=>”，当两个表达式彼此相等或者都等于空值的时候，比较的结果值为TRUE；若其中一个是空值或者都是非空值但却不相等时，则为FALSE，不会出现UNKNOWN的情况。

例11：查找数据库mytest的表students，输出所有女生的信息。 Mysql>select * from mytest.students

->where student_sex='1'; ※字符串匹配

通配符有以下两种：

①%：%可以表示任何字符串，并且该字符串可以出现任意次数。

例12：查询数据库mytest中表students，输出所有姓“李”的学生姓名和ID。 Mysql>select student_name,student_id from mytest.students

->where student_name like '李%';

②下划线'_'：下划线只匹配单个字符，而不是多个字符，也不是0个字符。 例13：查询数据库mytest中students，输出姓‘李’并且姓名只有两个中文字符的学生名字。 Mysql>select student_name from mytest.students

->where student_name like '李_';

※文本匹配； ①基本字符匹配：

例14：查询数据库mytest中students，分别使用字符串匹配和文本匹配的方式，输出专业含有“物”字的学生信息。（学生信息包括名字、年龄和专业） ?采用百分号匹配：

Mysql>select student_name,student_age,student_major

->from mytest.students where student_major like '%物%'; ?采用正则表达式regexp匹配：

Mysql>select student_name,sttudent_age,student_major

->from mytest.students where student_major regexp '物'; ——这里可以在“[物]”左右括号。 ②选择匹配：正则表达式而已达到待搜索对象的选择性匹配，使用‘|’分隔符提供选择匹配的字符串，该分割符类似于在SELECT语句中使用OR子句。

例15：查询数据库mytest的表students，输出“自动化专业”或者“数学专业”的学生姓名。 mysql> select student_name,student_major

-> from mytest.students where student_major regexp '自动化专业|数学专业';

LIKE和REGEXP的区别：LIKE严格受单引号之间的内容查询，有没有百分号对于选择结果而言至关重要。

③范围匹配：正则表达式还可以在某一个范围内对数据进行过滤，使用“【】”包含字符或者数字集合。如果表示范围4-6则可以表示为'[4-6]'.

例16：查询数据库mytest的表students，输出年龄中含有数字4或5的学生信息。（这里的学生信息包括名字和年龄）

mysql> select student_name,student_age

-> from mytest.students where student_age regexp '[4|5]';

④特殊字符匹配；如果要在列值中查找具有这些特殊含义的字符的时候，需要用到转义字符“\\\\”。具体方法是将其作为前导，例如正则表达式中如果出现“\\\\-”则表示查找字符“-”，正则表达式中为了查找反斜杠\\本身，则需要使用“\\\\\\”； “\\\\f”——换页；“\\\\r”——回车；“\\\\t”——制表；“\\\\v”——纵向制表。

⑤重复匹配：正则表达式支持重复元素匹配。即用某些字符来表示要匹配的元素重复的次数。 元字符 * + ？ {n} {n,} {n,m} 说明 0个或者多个匹配 1个或者多个匹配 0个或者1个匹配 指定n个匹配 不少于n个匹配 匹配的数目在n和m之间（m不可超过255） 例17：查询数据库mytest中表students，输出学生名字含有两个“圆”的学生信息。 Mysql>select student_name,student_age,student_major

->from mytest.students where student_name regexp '[圆]{2}';

⑥字符类匹配：例如字符类'[:upper:]'表示任意大写字母，如同在正则表达式中的'[A-Z]'. ⑦使用定位符匹配：

定位符 ^ $ [[:<:]] [[:>:]] 说明 文本开始 文本结尾 词的开始 词的结尾 例18：查询数据库mytest 中表students，输出“生”开头的专业名称的学生姓名、专业。 Mysql>select student_name,student_major

->from mytest.students where student_major regexp '^[生]';

※判定范围：

①BETWEEN?AND

例19：查询数据库mytest的表student，输出年龄为20到23岁之间学生的姓名、专业。 Mysql>select student_name,student_major

->from mytest.students where student_age between 20 and 23; ②IN

例20：查询数据库mytest中students表，输出年龄为23岁和24岁之间的学生姓名。 首先解决以下问题：

mysql> select student_name,student_age

-> from mytest.students where student_age regexp '[23|24]'; +--------------+-------------+ | student_name | student_age | +--------------+-------------+

| 李芳 | 25 | | 李明 | 22 | | 张三 | 24 | | 王五 | 23 | | 李芳 | 23 | | 孙艺华 | 22 | | 范圆圆 | 21 | +--------------+-------------+

为什么不可以？因为REGEXP是只要出现单引号内条件的都会被筛选出来，而23|24都存在2，所以只要包含2就会被选出来。——其筛选灵活程度极强。 所以此程序应该是：

mysql> select student_name,student_age from mytest.students -> where student_age regexp '[3|4]'; 或者使用IN：

Mysql>select student_name,student_age from mytest.students

->where student_age in (23,24);

※判定空值：关键字IS NULL可以用来判断一个表达式是否为空值。 例21：查询数据库mytest中表students，输出联系方式为空的学生姓名。 Mysql>select student_name ,student_contact from mytest.students

->where student_contact is null; ※子查询：

①IN子查询，比如<表达式> [not ] in <子查询> ②比较运算符子查询：

<表达式>{=|<|<=|>|>=|<=>|<>|!=}{ALL|SOME|ANY}<子查询>

ALL、SOME、ANY：可选项。ALL表示指定表达式需要与子查询结果集中的每个值进行比较；

SOME和ANY是同义词，表示表达式只要与子查询结果集中的某个值满足关系即可返回TRUE。

③EXIST子查询：判断子查询结果集是否为空，如果存在子查询的结果则返回TRUE。 ※GROUP BY ：

<位置>：通常后面接一个正整数，比如GROUP BY 2的意思是根据SELECT选择列清单上的第二列的值进行分组。

ASC|DESC:ASC表示升序分组，DESC表示降序分组。

例22：在数据库mysql中表students中获取一个结果数据集，要求该结果集以性别分组，并且统计男生和女生的人数。

Mysql>select student_sex,count(*) as '人数'

->from mytest.students ->group by student_sex; 扩展：

mysql> select count(*) as '人数', -> case

-> when student_sex='0' then '男' -> else '女' -> end as '性别'

-> from mytest.students group by student_sex;

+------+------+ | 人数 | 性别 | +------+------+ | 1 | 男 | | 6 | 女 | +------+------+

例23：查询数据库mytest的表students中要求输出每个专业学生的平均年龄和专业名称。 MySQL>select student_major, avg(student_age) as '平均年龄' from mytest.students ->group by student_major;

※HAVING子句——用于指定过滤条件

与WHERE子句的区别：WHERE子句主要用于过滤数据行，而HAVING子句主要用于过滤分组。

例24：在数据库mytest中表students，查找专业学生数量大于2的学生总数和专业名称。 MySQL>select student_major, count(*) as '总人数' ->from mytest.students ->group by student_major ->having count(*) >2;

例25：在数据库mytest中表students，按照学生年龄的降序方式，输出学生姓名和年龄。 MySQL>select student_name, student_age from mytest.students ->order by student_age desc;

※LIMIT子句：表示从第<标志位置>+1行开始取<行数>行数据。 比如：limit4,3指的是从第五行开始取三行数据。

例26：在数据库mytest中表students，查找从第五个学生开始的三个学生的id和姓名。 Mysql>select student_id,student_name

->from mytest.students ->limit 4,3;

※UNION语句与联合查询：

联合查询：把来自多个SELECT语句查询的结果组合到一个结果集中，并且作为单个查询结果集返回，这种查询方式称为并或者联合查询。

UNION的语法格式：SELECT?UNION[ALL|DISTINCT]SELECT? All：表示当两个结果集有重复记录的时候，允许重复记录存在；

Distinct：表示去除两个结果集中重复出现的记录，默认是DISTINCT。

例27：在数据库mytest中表students，使用UNION关键字合并专业是“物理专业”和性别是“1”的学生姓名、性别和专业信息。

Mysql>select student_name,student_sex,student_major

->from mytest.students where student_major='物理专业' ->union

->select student_name,student_sex,student_major ->from mytest.students where student_sex='1';

简单而言，UNION的使用方法非常简单，只需给出每条SELECT语句然后在各条SELECT语句之间加上关键字UNION即可。

注意：UNION语句只使用一条ORDER BY 语句或者LIMIT子句，且它们必须置于最后一条，SELECT语句之后。

课后总复习错题

6.在下列有关group by语句的描述中，不正确的是（B）

A.分组条件可以有多个，并且每一个可以分别指定排序方式； B.可以使用WHERE子句对所得的分组进行筛选；

C.GROUP BY可以配合聚合函数一起使用，但GROUP BY子句不能直接使用聚合函数； D.除了聚合函数，SELECT语句中的每个列都必须在GROUP BY子句中给出。 解析：如果对排序需要有条件筛选，应该在属性名后接 having +条件 select 选择的列 from 表

where 查询的条件

group by 分组属性 having 分组过滤的条件 order by 排序属性

limit 起始记录位置，取记录的条数

到了group by那一步已经是对查询的条件进行分组了，所以就算要加条件也不是where，而是having。

7.对于SQL查询：SELECT *from tbl_name where id=(select id from tbl_name)，假设该表中包含id字段，那么该语句正确执行的条件是（C） A.该表中必须有多条记录； B.该表中必须只有一条记录；

C.该表中记录数必须小于等于一条；

D.此SQL语句错误，无论如何都无法正确执行。

解析：当表中记录多于1条记录时，(SELECT id FROM tbl_name)返回的是一个结果集，把结果集赋给id，显然执行语句失败，当记录小于等于1时，返回的是空或者是id值，可以作为条件查询。

9.学生表student如下所示： 学号 021 026 056 101 姓名 林山 张宏 王林 赵松 所在系编号 02 01 02 04 总学分 32 26 22 NULL 下面SQL语句中返回值为3的是（D） A.SELECT COUNT(*) FROM STUDENT;

B.SELECT COUNT(所在系编号) FROM STUDENT;

C.SELECT COUNT(*) FROM STUDENT GROUP BY 学号; D.SELECT COUNT(总学分) FROM STUDENT;

解析：A返回4；B返回4；C返回4四个1；D返回3。

其中，B答案中要写成“SELECT COUNT(DISTINCT 所在系编号) FROM STUDENT;”结果才会返回3； 二、基本操作题：

数据库db_emp中有职工表tb_employee和部门表tb_dept,tb_employee包含的字段有eno（职工号）、ename（姓名）、age（年龄）、title（职务）、salary（工资）和deptno（部门号），tb_dept包含的字段有deptno（部门号）、dname（部门名称）、manager（部门负责人）、telephone（电话）。

（1）用SQL语句完成以下操作：给企业新增加一个“公关部”，部门号为“D4”，电话为“010-82953306”，并任命“Liming”担任部门负责人。 首先建立数据库和表格：

mysql> create database db_emp; mysql> use db_emp Database changed

mysql> create table tb_employee -> (eno char(7) primary key, -> ename char(20), -> age int,

-> title char(20), -> salary int,

-> deptno char(10)); mysql> create table tb_dept

-> (deptno char(10) primary key, -> dname char(20), -> manager char(20), -> telephone char(20)); 第一题过程如下：

mysql> insert into db_emp.tb_dept(deptno,dname,manager,telephone)

-> values('D4','公关部','Liming','010-82953306'); Modify和change的区别：

如果只是要改列的属性类型而不该列的名字，则两者区别如下： Mysql>alter table db_emp.tb_dept

->change column manager manager char(20); Mysql>alter table db_emp.tb_dept

->modify column manager char(20);

原因：因为change 的功能还可以改变列的名字所以第一个manager表示的是原来列名，第二个manager表示的是更改过后的列名，如果没有区别的话，那么两个列名应该是一样的， 都是manager。

（2）用SQL语句将tb_employee表中的salary字段的默认值修改为3500. mysql> alter table db_emp.tb_employee

-> alter column salary set default 3500;

（3）用SQL语句查询“销售部”的员工人数，要求查询结果显示为“总人数”。 首先对两个表格进行信息的输入：

mysql> insert into db_emp.tb_employee(eno,ename,age,title,salary,deptno) -> values('2550','李静',23,'普通职员',3500,'D4'), -> ('2603','李鹏鹏',22,'普通职员',3600,'D4'), -> ('2506','李欢',26,'主任',4500,'D4'), -> ('2410','郭红红',36,'主任',5500,'D3'), -> ('2568','邓英',26,'普通职员',4000,'D3'), -> ('2563','赵佳力',27,'普通职员',default,'D2');

mysql> insert into db_emp.tb_dept(deptno,dname,manager,telephone) -> values('D3','销售部','赵小溪','1562983446'), -> ('D2','后勤部','陈琪琪','1459856325'),

-> ('D4','公关部','Liming','010-82953306'); 第三题过程如下：

mysql> select count(eno) as '总人数' from db_emp.tb_employee

-> where deptno=(select deptno from db_emp.tb_dept where dname='销售部');

※索引：根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，索引实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。 ※两种方式访问数据库表行数据的方式：

①顺序访问：在表中实行全表扫描，从头到尾逐行遍历； ②索引访问：通过遍历索引来直接访问表中记录行的方式。 ※索引的分类：

①B-树索引——基于树形的数据结构查找数据；

②哈希索引——根据索引列对应的哈希值的方法获取表的记录行。 特点：访问速度快，但是建立哈希索引会耗费更多时间； 只支持等值比较，不能使用HASH索引排序。 ※索引在逻辑上分为以下五类： ①普通索引； ②唯一性索引； ③主键； ④空间索引； ⑤全文索引。

※在实际应用中通常分为单列索引和组合索引； ※创建索引的方法：

①用CREATE INDEX语句创建索引：

CERATE <索引名>ON <表名>(<列名>[<长度>][ASC|DESC]) 索引列长度的最大上限为255个字节。

例1：在数据库mytest中表students，根据学生姓名前三个字符，采用默认的索引类型，创建一个升序索引index_students。 Mysql>create index index_students

->on mytest.students(student_name(3) asc); 语句执行成功以后可以查看已经创建的索引：

Mysql>show index from mytest.students;

例2：在数据库mytest表students中，根据student_name和student_id采用BTREE的索引类型，创建一个复合索引index_stud。 Mysql>create index index_stud

->on mytest.students(student_name,student_id) ->using btree;

语句执行成功以后可以查看已经创建的索引： Mysql>show index from mytest.students;

②使用CREATE TABLE语句来创建索引——即在创建表的过程中同时创建。 CONTRAINT PRIMARY KEY(列名) UNION

FOREIGN KEY

例3：在已有的数据库mytest中新建一个课程信息表包含下列字段：课程号、课程名称、上课教室和任课老师姓名。要求在创建该表的同时，将课程号作为主键，并且给课程名称创建索引。

Mysql>use mytest;

MySQL>create table course ->(course_id int not null,

->course_name char(50) not null, ->course_place char(50) null, ->course_teacher char(50) null, ->primary key(course_id),

->index index_course (course_name)); ③使用ALTER TABLE 语句来创建索引：

ALTER TABLE可以在一个已有的表上创建索引。

例4：在数据库mytest表course中，为course_place字段添加一个非唯一的索引，取名为index_place。

MySQL>alter table mytest.course

->add index index_place(course_place);

※查看索引：show index from ??

例5：显示数据库mytest的表course的索引情况。 两种表达方式：

①mysql>show index from mytest.course;

②mysql>show index from course from mytest;

※删除索引：

①使用DROP INDEX 语句：

语法格式——DROP INDEX <索引名>ON<表名>; 例6：删除例4创建的索引index_place。 Mysql>drop index index_place on mytest.course; ②使用ALTER TABLE语句：

例7：删除数据库mytest的表students的索引index_students。

Mysql>alter table mytest.students

->drop index index_students;

注意：如果删除的列时索引的组成部分，那么删除该列时，也会将该列从索引中删除；如果组成索引的所有列都会被删除，则整个索引将被删除。

※索引进阶：索引可以在一些情况下加快查询，但是在某些情况下，会降低效率。

课后总复习错题

6.以下关于PRIMARY KEY和UNION的描述中，错误的是（A） A.UNIQUE约束只能定义在表的单个列上；

B.一个表可以定义多个UNIQUE，只能定义一个PRIMARY KEY； C.在空值列上允许定义UNIQUE，不能定义PRIMARY KEY； D.PRIMARY KEY 和UNIQUE都可以约束属性值的唯一性。 解析：

(1) 唯一性约束所在的列允许空值，但是主键约束所在的列不允许空值。

(2) 可以把唯一性约束放在一个或者多个列上，这些列或列的组合必须有唯一的。但是，唯一性约束所在的列并不是表的主键列。

(3) 唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下，创建唯一性的非聚簇索引，但是，也可以指定所创建的索引是聚簇索引。 (4)建立主键的目的是让外键来引用.

(5)一个表最多只有一个主键，但可以有很多唯一键

7.执行语句“CREATE INDEX in_dept ON tb_dept(telephone(6),DESC)”后，在tb_dept表的telephone属性上（A）

A.根据telephone属性前六个字符采用BTREE索引类型创建索引 B.根据telephone属性前六个字符采用HASH索引类型创建索引 C.根据前六条记录采用BTREE索引类型创建索引 D.根据前六条记录采用HASH索引类型创建索引 解析：

HASH索引适合等式比较的操作，不能用来加速order by操作，也不能确定在两个值之间大约有多少行，会影响一些查询的执行效率。而且只能使用整个关键字来搜索一行。 对索引字段进行范围查询的时候，只有BTREE索引可以通过索引访问。用B-Tree索引进行全关键字、关键字范围和关键字前缀查询。

※视图：视图是从一个或者几个基本表（或视图）导出的表。视图是一个虚拟表，并不是数据库真实存储的数据集。 ※视图的作用：

①能够简化用户的操作

②视图使用户以多种角度看待同一数据

③视图对重构数据库提供了一定程度的逻辑独立性。 ④视图能够对机密数据提供安全保护

⑤适当利用视图可以更加清晰地表达查询。 ※视图和基本表的区别：

基本表是本身独立存在的表，在 SQL 中一个关系就对应一个表。

视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表

中。视图在概念上与基本表等同，用户可以如同基本表那样使用视图，可以在视图上再定义视图。

※试述视图的优点。

①为用户集中数据，简化用户的数据查询和处理。 ②保证数据的逻辑独立性。

③重新定制数据，使得数据便于共享；合并分割数据，有利于数据输出到应用程序中。 ④数据保密。

※创建视图：CREATE VIEW <视图名> AS ; 部分特殊的权限：

①SELECT语句不能包含FROM子句中的子查询；

②定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

例1：在数据库mytest中创建视图students_view，该视图包含学生基本信息表students中所有男生的姓名、专业和年龄，并且要求今后对视图数据的修改都必须符合“性别=男”这一个条件。

MySQL>create view mytest.students_view——标明mytest表示视图属于这个数据库。 ->as

->select student_name,student_major,student_age,student_sex ->from mytest.students ->where student_sex='0' ->with check option;

with check option的意思是修改视图时，检查插入的数据是否符合WHERE设置的条件。 此外，由于对所有数据的修改都需要判断性别是否为男，所以性别作为一个条件也应该加入到视图当中去。

※删除视图：DROP VIEW<视图名>

例2：删除例1中创建的视图students_view。 Mysql>drop view mytest.students_view;

※修改视图：

①ALTER VIEW <视图名> AS ;

②使用DROP VIEW语句先删除视图，然后使用CREATE VIEW语句来创建一个新的视图。

※查看视图：

SHOW CREATE VIEW<视图名>

例3：查看数据库mytest中名称为students_view的视图结构。 方法一：

MySQL>use mytest;

Mysql>show create view students_view; 方法二：

Mysql>show create view mytest.students_view; ——把视图当做表一样。

※更新视图数据：某些视图是可以更新的，但某些特定的结构会使得视图变得不可更新，比如：

①聚合函数SUM(),MIN(),MAX(),COUNT()等； ②DISTINCT关键字；

③GROUP BY 子句； ④HAVING子句；

⑤UNION或UNION ALL运算符；

⑥位于选择列表中的子查询，FROM子句中不可更新视图或包含多个表； ⑦WHERE子句中的子查询；

⑧使用临时列表使视图成为不可更新的；

※使用INSERT语句通过视图向基础表插入数据；

例4：在数据库mytest中，向视图students_view插入下面一条记录：（‘刘强’，‘地理空间专业’，‘19’，‘0’）。

mysql> insert into mytest.students_view

-> (student_name,student_major,student_age,student_sex)

-> values('刘强','地理空间专业',19,'0');

※使用UPDATE语句通过视图修改基础表数据：

例5：将视图student_view的student_major字段值“化学专业”改为“材料化学专业”。 Mysql>update mytest.students_view

->set student_major='材料化学专业' ->where student_major='化学专业';

※使用DELETE语句通过视图删除基础表的数据：

例6：删除视图students_view中姓名为“王五”的学生的信息。 Mysql>delete from mytest.students_view where student_name='王五'; 注意：对于依赖于多个基础表的视图，不能使用DELETE语句；

※查询视图数据：

例7：在视图students_view中查找姓名为“张明”的学生，以及他的姓名和专业信息。 Mysql>select student_name,student_major from mytest.students_view

->where student_name='张明';

※视图不能索引，也不能有关联的触发器、默认值或者规则。

例8：基于视图students_view创建一个只包含专业为“物理专业”的子视图。 Mysql>create view mytest.students_phy ->as

->select * from mytest.students_view ->where student_major='物理专业';

课后总复习错题

3.下列关于视图的叙述，正确的是（A） A.使用视图，能够屏蔽数据库的复杂性；

B.更新视图数据的方式与更新表中数据的方式相同； C.视图可以建立索引；

D.使用视图，可以提高数据更新的速度。

解析：参见视图的优点——为用户集中数据，简化用户的数据查询和处理。

※数据库完整性：数据库完整性是指数据库中数据的正确性和相容性。数据库完整性由各种各样的完整性约束来保证，因此可以说数据库完整性设计就是数据库完整性约束的设计。 完整性有三类：实体完整性，参照完整性，用户定义完整性。 ①实体完整性：实体完整性要求每一个表中的主键字段都不能为空或者重复的值。规定基本关系R的主属性A不能取空值， 如：Create Table 学生

( 学号CHAR(10) PRIMARY KEY, 姓名 CHAR(20), ....... );

②参照完整性：规定若F是基本关系的外码，它与基本关系S的主码相对应，则对于R中每一个元组在F上的值必须取空值（F的每一个属性值均为空值），或等于S中某一个元组的主码值。

如：Create Table学生(学号CHAR(10) PRIMARY KEY, 姓名 CHAR(20), 课程号 CHAR(10),

FOREIGN KEY(课程号)REFERENCES 课程(课程号) ); Create 课程( 课程号 CHAR(10) PRIMATY KEY, ........);

※参照完整性简单来说就是表间主键外键的关系。当更新、删除、插入一个表中的数据时，通过引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。 ③用户定义完整性：就是针对某一具体的关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求，由应用环境决定，即属性值限定，包括：列值非空(NOT NULL), 列值唯一(UNIQUE),检查列值是否满足一个布尔表达式。 如：Create Table 学生_课程(学号 CHAR(10) NOT NULL, 课程号 CHAR(10) NOTNULL, 成绩 SMALLINT NOT NULL,

PRIMARY KEY(学号，课程号)， ...... );

Mysql支持三种用户自定义完整性，分别是非空约束，CHECK约束和触发器。 ?非空约束：在某个列定义后加上NOT NULL即可； ?CHECK约束：CHECK<表达式>

※实体完整性约束的分类： ①主键约束：

例1：创建一个人和例3-5中基本学生信息表，相同结构的表students_new，以列的完整性约束定义主键。——也即是标明PRIMARY KEY。 mysql> create table mytest.students_new

-> (student_id int not null auto_increment primary key, -> student_name char(50) not null,

-> student_sex char(1) not null default 0, -> student_age int not null,

-> student_major char(50) not null,

-> student_contact char(50) null); ②候选键约束：

候选键可以在CREATE TABLE或ALTER TABLE语句中指定关键字UNIQUE来定义。一个表中只能有一个主键，但是可以有多个候选键。

※指定参照完整性约束的实现策略：

RESTRICT：限制策略，当要删除或者更新父表中被参照列上并在外键中出现的值时，系统拒绝对父表的删除或者更新操作。

CASCADE：级联策略，当父表中删除或更新记录行时，系统会自动删除或更新子表中匹配的记录行。

SET NULL：置空策略，当父表中删除或更新记录行时，设置子表与之对应的外键的列的值为NULL，该策略需要子表的外键没有声明限定词NOT NULL。

NOT ACTION：不采取实施策略，当一个相关的外键值在父表中时，不允许删除或更新父表中的键值，该策略动作语义与RESTRICT相同。

例2：在数据库mytest中创建一个成绩表grades，用于记录学生的成绩信息，该表包含的信息有：成绩序号grade_id，科目grade_obj，课程分数grade_score，学期grade_time。要求完成成绩表grades的所有学生姓名都在表students中对应记录。 mysql>use mytest;

Mysql>create table mytest.grades

->(grade_id int not null auto_increment, ->grade_obj char(50) not null, ->grade_score int not null, ->grade_time int not null,

->student_id int not null, /*有某一行在这里不是主键在另一个表students是主键*/ ->primary key(grade_id),

->foreign key(student_id) references students(student_id) on delete restrict ->on update restrict);

例3：向例2的表中grades插入一条记录：（1,'大学物理',89,2,1320） Mysql>insert into mytest.grades

->(grade_id,grade_obj,grade_score,grade_time,student_id) ->values(1,'大学物理',89,2,1320);

注意：此时若是输入一个students中不存在的学号学生的成绩，则程序最出错。

①主键不能包含空值，但允许在外键中出现空值，也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的；

②外键中列的数目必须和父表的主键中列的数目相同；

③外键中列的数据类型必须和父表中对应列的数据类型相同。

※用户定义完整性实例：

例4：在数据库mytest中，创建成绩表grades_new，结构和成绩表grades相同，要求表grades_new的student_id列所有值来源于表students的student_id列。 MySQL>create table mytest.grades_new

->(grade_id int not null auto_increment, ->grade_obj char(50) not null, ->grade_score int not null, ->grade_time int not null,

->student_id int not null check (student_id in (select student_id from mytest.students)), ->primary key(grade_id));

本例中设置的check约束效果实质上与参照完整性作用相同，与外键作用相同。

CHECK约束还可以用于多个列设置限定条件。

例5：在数据库mytest中创建一个成绩表grades_new_two，和成绩表grades的结构相同，要求表grade_score字段值大于0且小于等于100. Mysql>create table mytest.grades_new_two

->(grade_id int not null auto_increment, ->grade_obj char(50) not null, ->grade_score int not null, ->grade_time int not null,

->student_id int not null check(student_id in(select student_id from mytest.students)), ->primary key(grade_id),

->check(grade_score>0 and grade_score<=100));

※命名完整性约束：CONSTRAINT<约束名>；

此处如果没有给出明确的约束的名字，系统会自动创建一个约束名。

例6：在数据库mytest中创建一个成绩表grades_new_three，和成绩表grades的结构相同，将该表的主键约束名命名为PRIMARY_KEY_GRADES,并将它的外键命名为FOREIGN_KEY_ORDERS.

Mysql>create table mytest.grades_new_three

->(grade_id int not null auto_increment, ->grade_obj char(50) not null, ->grade_score int not null, ->grade_time int not null, ->student_id int not null,

->constraint PRIARY_KEY_GRADES primary key (grade_id), ->constraint FOREIGN_KEY_ORDERS foreign key(student_id) ->references students(student_id) on delete restrict on update restrict); 区别：就是比参照完整性多了定义名这一步。 ※更新完整性约束： ADD FOREIGE KEY DROP PRIMARY KEY

DROP FOREIGN KEY<外键名> 完整性约束不能直接被修改，如果要修改某个约束，实际上是用ALTER TABLE语句先删除该约束，然后再增加一个一个与该约束同名的新约束。 ※表维护语句——数据库优化的基础；

索引散列程度Cardinality——表示某个索引对应的列包含多少个不同的值。可以通过查看索引来得到索引的散列程度。

Mysql>show index from mytest.sudents;

※更新索引散列程度Cardinality可以用ANALYZE TABLE语句。 例8：更新数据库mytest中表students的索引散列程度。

Mysql>analyze table mytest.students; ——理解上是更新表的意思。

※CHECKSUM TABLE语句——数据可能在传输中造成损失或者破坏，为了保证数据的一致，可以使用CHECKSUM TABLE语句对数据库中的每一个表进行计算校检和。

CHECKSUM TABLE <表名>[QUICK|EXENDED] QUICK：表示返回已存储的校检和；

EXTENDED：整个表会一行一行被读取，并且计算校检和，对于较大的过程可能会比较慢。 例9：对数据库mytest的表students使用CHECKSUM TABLE语句获取一个校检和。 Mysql>checksum table mytest.students;

※CHECK TABLE语句：可以使用CHECK TABLE语句来检查一个或多个表是否有错误，该语句只对InnoDB、MyISAM、ARCHIVE、CSV起作用。

例10：使用CHECK TABLE语句检查数据库mytest中表students的相关检查信息。 MySQL>check table mytest.students;

例11：查阅数据库mytest中表students的相关检查信息。 MySQL>select table_name,check_time

->from information_schema.tables /*库·表（表可能多个，所以用到“s”）可以理解为schema.tables；*/ ->where table_name='students' and table_schema='mytest';

※REPAIR TABLE语句：可以使用REPAIR TABLE语句来尝试修复可能被损坏的表。该语句只对MyISAM和ARCHIVE表起作用。

※OPTIMIZE TABLE语句：重新利用未使用的空间，并整理数据文件的碎片，从而提高数据的读取效率。

例12：利用OPTIMIZE TABLE语句来优化数据库Mytest中的表students。

题目说明：如果在MySQL中开启了二进制日志文件，那么ANALYZE TABLE语句执行操作的结果也会写入二进制日志文件中。也可以在ANALYZE TABLE语句中添加NO_WRITE_TO_BINLOG或LOCAL选项来取消写入，其中两个关键字同义。 此处若是开启了二进制日志文件，则代码为：

Mysql>optimize no_write_to_binlog table mytest.students;

课后总复习错题

5.下列关于表级约束和列级约束的描述中，不正确的是（C） A.列级约束针对某个特定的列，包含在列定义中； B.表级约束与列定义相互独立，不包含在列定义中； C.列级约束可能涉及到多个列，也可能仅涉及一个列； D.表级约束可能涉及到多个列，也可能仅涉及一个列。

解析：列约束是对某一个特定列的约束，包含在列定义中，表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束。

※触发器是被指定关联到一个表的数据库对象，当一个表的特定时间发生时，它将会被激活。 语法格式：

?CREATE <触发器名>ON <表名> FOR EACH ROW <触发器主体> 其中：

INSERT：将新行插入表时激活触发器；

DELETE：从表中删除某一行时激活触发器； UPDATE：更改表中某一行数据时激活触发器。

FOR EACH ROW：使用INSERT语句向某个表插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

每个表支持INSERT、UPDATE和DELETE的BEFORE与AFTER，因此每个表最多支持6个触发器。每个表的每个事件每次只允许一个触发器，单一触发器不能与多个事件或则多个表关联。 例1：在数据库mytest中表students中，创建一个触发器students_insert，用于每次向表students中插入一行数据时，将用户变量str设置成“add a new student”。 Mysql>create trigger mytest.students_insert after

->insert on mytest.students

->for each row set @str='add a new student'; 接着向students表插入一行数据： Mysql>insert into mytest.students

->(student_name,student_sex,student_age,student_major,student_contact) ->values ('王媛','1','22','生物专业',null);

最后执行命令mysql>select @str，查看用户变量str，验证触发器是否执行。

※若是需要查看数据库中已有的触发器，可以使用SHOW TRIGGER语句。 Mysql>show create trigger mytest.students_insert; 关于show create 的另外一些应用：

show create database database_name; -- 显示create database语句是否能够创建指定的数据库。 show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。 show engines; -- 显示安装以后可用的存储引擎和默认引擎。

※删除触发器：DROP TRIGGER [IF EXISTS][数据库名]<触发器名> 例2：删除例1所创建的触发器students_insert。 Mysql>drop trigger if exists mytest.students_insert;

当删除一个表的同时，也会自动删除该表上的触发器，另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。

※使用触发器：

INSERT触发器：在INSERT语句执行之前或之后响应的触发器； UPDATE触发器：在UPDATE语句执行之前或之后响应的触发器； DELETE触发器：在DELETE语句执行之前或之后响应的触发器； 例3：在数据库mytest中表students中重新创建触发器students_insert，用于每次向表中插入一行记录时，将用户变量str设置为新插入的学生的student_id。 Mysql>create trigger mytest.students_insert

->after insert on mytest.students

->for each row set @str=new.student_id; 然后插入一行新的记录：

Mysql>insert into mytest.students

->(student_id,student_name,student_sex,student_age,student_major) ->values('1356','李鸣','0','21','计算机专业');

最后输入代码mysql>select @str ; 来查看用户变量str，验证触发器是否执行。 注意：可以引用一个名为new的虚拟表，来访问更新的值；

可以引用一个名为old的虚拟表来访问UPDATE语句执行前的值。

例4：将数据库mytest中表students，创建一个触发器students_update，用于每次更新表数据时，将student_age的值加1.

可以理解为再更新数据前将年龄加上1. Mysql>create trigger mytest.students_update

->before update on mytest.students

->for each row set new.student_age=old.student_age+1;

找到student_id为1322的学生信息,接着将该记录的姓名改为“张亮”。

mysql>select student_name,student_age from mytest.students where student_id='1322'; Mysql>update mytest.students

->set student_name='张亮' where student_id='1322'; 最后再重新执行这条程序，看年龄是否增大一岁。

mysql>select student_name,student_age from mytest.students where student_id='1322';

※DELETE触发器：在DELETE触发器代码内，可以引用一个名为OLD（不区分大小写）的虚拟表来访问被删除的行。

※触发器进阶：创建触发器可能需要特殊的安全访问权限，但是触发器的执行时自动的，也就是说，如果INSERT、DELETE和UPDATE语句能够执行，那么相关的触发器也能执行。应该多用触发器来保证数据的一致性、完整性和正确性。

课后总复习错题

※触发器不可以建立在视图上。

7.在MySQL中，INSERT 触发器操作数据时，可以使用的临时表是（A） A.NEW B.OLD C.NEW和OLD D.LAST INSERT触发器操作数据时可使用的临时表是NEW

UPDATE触发器操作数据时可使用的临时表是NEW和OLD

4.当触发器涉及对触发表自身的更新操作时，使用的触发器必须是（A） A.BEFORE UPDATE B.AFTER UPDATE C.UPDATE BEFORE D.UPDATE AFTER

※时间调度器可以进行监视，并判断是否需要调用事件，可以在指定的时刻执行某些特定的任务，并以此取代原先只能由操作系统的计划任务来执行的工作。这种需要在指定时刻才被执行的任务就是事件，这些特定的任务通常是一些特定的SQL语句。 时间也被称作临时触发器。 ※时间和触发器的区别：

时间是基于特定时间周期来执行某些任务；而触发器是基于某个表所产生的时间触发。 ※由于事件调度器默认是关闭的，需要手工打开，所以我们可以先查看当前是否已经开启事件调度器。

Mysql>show variables like 'event_scheduler'; 或者是查看系统变量event_scheduler：

Mysql>select @@event_scheduler; ——此处@@event_scheduler是一个整体，不分开的。 若系统返回的结果是OFF，则表示时间调度器没有开启，此时我们可以打开事件调度器。 Mysql>set global event_scheduler=true; 如果想要关闭时间调度器的话则是：

Mysql>set global event_scheduler =false;

※创建事件的语法格式：

CREATE EVENT<事件名> ON SCHEDULEDO<事件主体>; 其中schedule的介绍如下：

①AT子句：用于指定事件在某个时刻发生；

②EVERY子句：用于表示事件在指定时间区间内每间隔多长时间发生一次。 此外事件名前面可以用if not exists来修饰。 例1：在数据库mytest中创建一个事件，用于每个月向表students中插入一条学生基本信息。 Mysql>create event if not exists event_add /*if not exists表明如果不存在的话就创建*/

->on schedule every 1 month ->do

->insert into mytest.students(student_name,student_sex,student_age,student_major) ->values('王维','0',24,'自动化专业');

※修改事件：事件被创建以后可以通过ALTER EVENT语句来修改其定义和相关属性。 语法格式：

ALTER EVENT<事件名>[RENAME TO <新事件名>][DO<事件主体>][ENABLE|DISABLE] 一个事件最后一次被调用后，它是无法被修改的，因为此时它已不存在了。 例2：临时关闭例1创建的时间event_add。 Mysql>alter event event_add disable;

例3：再次开启例2关闭的事件event_add。 Mysql>alter event event_add enable;

例4：将事件event_add的名字修改为event_new。 Mysql>alter event event_add

->rename to event_new;

※删除事件的语法格式：

DROP EVENT[IF EXISTS]<事件名> 例5：删除名为event_new的事件。

Mysql>drop event if exists event_new; /*如果存在的话就删除掉*/

课后总复习错题

1.以下有关mysql事件的叙述中，错误的是（A） A.事件是由操作系统调用的过程 ※不是系统调用的，而是按照计划自动运行的。或者可以理解为在创建事件的时候就已经调用了，只是没有运行。

B.事件能够按照设定的事件自动执行 C.在事件中可以调用存储过程

D.在事件中可以对数据表进行数据更新操作 ※事件是按计划运行工作，事件是按建立事件时设定的时间来执行，事件可调用存储过程及对表进行更新

2.以下不能开启事件调度器的语句是（C） A.SET GLOBAL EVENT_SCHEDULER=TRUE;

B.SET GLOBAL EVENT_SCHEDULER=1;

C.SELECT @@EVENT_SCHEDULER;——这个是查看事件调动器的状态。 还有一种查看方式是：mysql>show variables like 'event_scheduler'; D.SET GLOBAL EVENT_SCHEDULER=ON;

※存储过程：一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过制定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。 存储过程具有以下优点：

①封装性；②可增强SQL语句的功能和灵活性； ③可减少网络流量； ④高性能；

⑤提高数据库的安全性和数据的完整性。

※创建存储过程：CREATE PROCEDURE语句。

CREATE PROCEDURE <过程名>(过程参数[,??])<过程体> [过程参数[??]].格式：[IN|OUT|INOUT]<参数名><类型> 过程名：存储过程的名称；

输入参数IN：传递给一个存储过程；

输出参数OUT：存储过程需要返回一个操作结果的情形；

输入输出参数INOUT：既可以充当输入参数也可以充当一个输出参数。

过程体；以关键字BEGIN开始，以END结束。若存储过程只有一条SQL语句，则可以省略此过程。

※存储过程中通常使用DELIMITER命令将结束命令修改成其他符号（而不是“；”） 语法格式：DELIMITER $$

其中$$是用户定义的结束符，通常这个符号可以是一些特殊的符号，比如两个“？”或者两个“￥”等。

注意：当使用DELIMITER命令时，应避免使用反斜杠（“\\”），因为它是MySQL的转义字符。

例1：将MySQL结束符修改为两个问号“??”.利用该结束符来作为show databases语句的结束符。

Mysql>delimiter ?? ——这里的??和delimiter 是由一个空格的。

mysql> show databases??——这里就不需要空格，因为已经作为定义好的结束符。 +--------------------+

| Database | +--------------------+ | information_schema | | db_emp | | mysql | | mytest | | performance_schema | | test | +--------------------+

例2：换回默认的分号“;”作为结束符。 Mysql>delimiter ;

注意：此处的delimiter 和“;”应该要有个空格.

例3：在数据库mytest中创建一个存储过程，存储过程的功能是修改表students的姓名。要求给出学生的id，修改对应的姓名。 MySQL>use mytest; Mysql>delimiter ??

mysql->create procedure update_name(in cid int,in cname char(50))

->begin

->update mytest.students set student_name=cname when student_id=cid; ->end??

注意：可以使用show procedure status 命令，查看数据库中有哪些存储过程；若要查看某个存储过程的具体信息，可以使用show create procedure<存储过程名>。 当要查看数据库mytest中有哪些存储过程时： Mysql>delimiter ;

Mytest>show procedure status;

当要查看数据库mytest中的存储过程update_name时： Mysql>show create procedure mytest.update_name;

局部过程体中可以声明局部变量，用来存放产生的临时结果。 ※局部变量：与全局变量相对应，在局部范围内有效的变量； 声明局部变量的语法格式：

DECLARE <变量名>[,......]<类型>[DEFAULT<默认值>]

例4：声明一个字符型局部变量xname，默认值为“李晓明”。 在存储过程中输入以下语句：

Declare xname varchar(5) default '李晓明'; ※局部变量和用户变量的使用范围：

①局部变量只能在存储过程BEGIN?END语句中声明，且作用范围也只在BEGIN?END之间；

②局部变量必须在存储过程开头处说明；

③局部变量声明时没有使用@符号，并且只能在BEGIN?END范围内使用，用户变量在声明时，需要使用@符，已经声明的用户变量存在于整个会话中。

※SET语句：变量声明以后，可以用SET语句为局部变量赋值。 语法格式为：

SET <变量名1>=<表达式1>??<变量名2>=<表达式2> 例5：为例4的变量赋值为“王杰”。 在存储过程中输入以下语句： Set xname='王杰'

※SELECT......INTO语句：把选定列的值直接存储到局部变量中，存储过程体中的SELECT......INTO只能返回一行数据。 语法格式：

SELECT <列名>[......]INTO<变量名>[,......]<其他>

<其他>：SELECT语句中的FROM子句以及后面的语法部分。

例6：给局部变量x,y分别赋数据库mytest中的表t1的id,data列的值。

在存储过程中输入以下语句：

Select id,data into x,y from mytest.t1 limit 1;

Limit1 的意思：当没有指定位置偏移量时，只取1条结果。

※流程控制语句：

①条件判断句IF-THEN-ELSE 语法格式：

IF<判断语句>THEN <语句>

[ELSEIF<判断条件>THEN<语句>] [ELSE<语句>] END IF

②条件判断句CASE CASE<参数>

WHEN<参数>THEN<语句> [WHEN<参数>THEN<语句>]. [ELSE语句] END CASE 或

CASE<参数>

WHEN<参数>THEN<语句>

[WHEN<参数>THEN<语句>ELSE语句] END CASE

③循环语句LOOP <标签>LOOP <语句>

END LOOP[标签]

循环体中的语句会一直被重复执行，直至循环使用LEAVE语句退出。其中，LEAVE语句的语法格式为：LEAVE<标签>，这里的标签是LOOP语句中所标注的自定义名字。 ④循环语句WHILE

<标签>WHILE<判断条件>DO <语句>

END WHILE<标签> ⑤循环语句REPEAT <标签>REPEAT <语句>

UNTIL<判断条件> END REPEAT<标签>

REPEAT语句和WHILE语句的区别：REPEAT语句先执行语句，后进行判断；而WHILE语句时先判断，条件为真时才执行语句。

※游标：游标是一个被SELECT语句检索出来的结果集。在存储了游标以后，应用程序或用户可以根据需要滚动或浏览其中的数据。 注意：

①游标只能用于存储过程或者存储函数中，不能单独在查询操作中使用。

②在存储过程或者存储函数中可以定义多个游标，但是在一个BEGIN......END语句块中每一个游标的名字必须是唯一的。

※使用游标的步骤：

①声明游标：DECLARE<游标名>CURSOR FOR 注意：这里的SELECT语句不能有INTO语句。 ②打开游标：OPEN<游标名>

③读取数据：FETCH<游标名>INTO<变量名1>[,变量名2]... ④关闭游标：CLOSE<游标名>

注意：每个游标不再使用时都应该被关闭，关闭后才能释放出游标所使用的资源。同时，在游标被关闭后，需要重新打开才能使用。系统会在END语句结束后，自动关闭没有手工关闭的游标。 例7：在数据库mytest中创建一个存储过程sp_count，用于统计表students中的记录总行数。首先，在MySQL中输入以下语句创建存储过程： mysql>use mytest; Mysql>delimiter ??

Mysql>create procedure sp_count(out rows int)

/*此处是输出参数，将统计结果输出，参数名为rows*/

->begin

->select count(*) into rows from mytest.students; ->end??

接着我们调用编写好的存储过程： mysql>delimiter ;

Mysql>call sp_count(@rows);

调用存储过程有一个目的就是恢复结束符标志位“;” 调用过程中变量要加\

※调用存储过程——CALL语句。 CALL<过程名>[参数] CALL<过程名>[()]

例8：调用例3编写的存储过程update_name，将student_id=1320的学生的姓名改为“张泽志”。

Mysql>call update_name(1320,'张泽志');

※修改存储过程：ALTER PROCEDURE<过程名>[<特征>......] 这个语法用于修改存储过程中的某些特征，如若要修改存储过程中的内容，可以先删除该存储过程，然后再重新创建。

※删除存储过程：DROP {PROCEDURE|FUNCTION}[IF EXISTS]<过程名> 删除之前，必须确认存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

例9：删除数据库mytest中的存储过程update_name。

Mysql>drop procedure mytest.update_name; (×) 存储过程不能当做表来操作 Mysql>drop procedure update_name; (√)

※存储函数：由SQL语句和过程式语句组成的代码片段，并且可以被应用程序和其他SQL语句调用。

※存储函数和存储过程的区别： ①存储函数不能有输出参数，因为存储函数自身就是输出参数；而存储过程可以拥有输出参数；

②存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中。可以理解为：存储函数自身是输出参数所以需要RETURN回来。

③可以直接对存储函数进行调用而不需要CALL语句，而对存储过程的调用则需要使用CALL语句。

※创建存储函数：

CREATE FUNCTION <函数名>([<参数1>,<类型1>[,<参数2>,<类型2>]]......) RETURNS<类型> <函数主体>

其中，<参数><类型>：用于指定存储函数的参数。这里的参数只有名称和类型，不能指定关键字IN,OUT,INOUT。

RETURNS：用于声明存储函数返回值的数据类型。

<函数体>：存储函数体必须包含一个RETURN<值>语句，其中<值>用于指定存储函数的返回值。

例10：在数据库mytest中创建一个存储函数fn_search，函数功能是：根据给定的student_id查找学生并返回该学生的姓名，如果没有查找到相应的学生，则返回‘没有该学生信息’。 Mysql>use mytest; Mysql>delimiter ??

Mysql>create function fn_search(cid int)

/*存储函数不包含输出参数，所以这里主要是输入学号，输出姓名，所以只需要写输入参数学号即可*/

->returns char(50)

->deterministic /*确定性的*/ ->begin

->declare name char(50);

->select student_name into name from mytest.students where student_id=cid; ->if name is null then

->return(select ('没有该学生的信息')); /*SELECT里面也要有括号（）*/ ->else

->return (name); ->end if; ->end??

注意：在begin 和end中间函数体的“；”一定要有，否则程序出错。 加“；”的方法：每一种结构结束后要加“；” 比如declare声明变量结束后要加； Select语句结束后要加；

Return返回结果的时候也要加；if......end if结构结束后要加。

※使用SELECT关键字调用存储函数。

其语法格式为：SELECT<存储函数名>([<参数>][,......])

例11：调用例10创建的存储函数fn_search，查询student_id分别为1320和1300的学生，并显示该学生的姓名。

Mysql>select fn_search('1320'); Mysql>select fn_search('1300');

总的而言，和存储过程差不多，只是CALL变成SELECT。

※查看数据库中有哪些存储函数？以mytest为例： Mysql>show function status;

查看数据库中具体的一个存储函数。例如mytest中的fn_search函数： Mysql>show create function fn_seahrch;

※删除存储函数：DROP FUNCTION[IF EXISTS]<存储函数名> 删除之前，必须确认存储过程没有任何依赖关系，否则会导致其他与之关联的存储函数无法运行。

例12：删除例10创建的存储函数fn_search。 MySQL>drop function if exists fn_search;

课后总复习错题

6.设有学生成绩表score(sno,cno,grade)，各字段的含义分别是学生学号、课程号和成绩。现有如下创建存储函数的语句： CREATE FUNCTION fun() RETURNS DECIMAL BEGIN

DECLARE x DECIMAL

SELECT AVG(grade) INTO x FROM score RETURN x END;

以下关于上述存储函数的叙述，错误的是（A） A.表达式AVG(grade) INTO x 有语法错误

B.X是全体学生选修所有课程的平均成绩（√） C.Fun没有参数 （√）

D.RETURNS DECIMAL指明返回值的数据类型（√） 7.在MySQL中，不可以声明和使用游标的情况有（C）

A.存储过程 B.存储函数 C.交互式SQL查询 D.触发器 这个情况需要记住即可。 11.现有如下语句：

CREATE PROCEDURE p(IN nol int ,OUT pname VARCHAR (10)) BEGIN

SELECT sname INTO pname FROM student WHERE sno=nol; END;

能够直接调用过程p，并且能够正确返回结果的语句是（A）

A.CALL p(100,@x); B.CALL p(100,'张红'); C.CALL p(@a,@b); D.CALL p(@a,'张红');

解析：括号内应该只包括输入参数，即是nol；不包括名字，所以只有A符合条件。 13.下列关于局部变量和用户变量的描述中，错误的是（D） A.局部变量只能在BEGIN......END语句块之间有效； （√） B.用户变量以“@”开头，局部变量没有这个符号； （√）

C.用户变量使用SET语句定义，局部变量使用DECLARE语句定义；（√） D.在存储函数中只能用局部变量。

二、操作题

给定teaching 数据库中包含学生、课程和选课三个数据表，请按要求完成以下操作。 1.设计一个名称为fn_学分的存储函数，根据给定的课程名返回该课程对应的学分。

2.设计一个存储过程PR_学分，根据学号返回学生的总学分（注意：成绩>=60才能获得相应的学分）

1.mysql>delimiter ?? Mysql>use teaching;

Mysql>create function fn_学分(kc char(50)) ->returns int

->deterministic ->begin

->declare xf int;

->select 课程学分 into xf from 课程 where 课程名称=kc; ->return (xf); ->end??

Mysql>delimiter ; 2.mysql>delimiter ?? Mysql>use teaching ;

Mysql>create procedure PR_学分 (in xh char(10) ,out zxf int)

->begin

->select count(课程.课程学分) into zxf from 选课,课程

->where 选课.课程名称=课程.课程名称and 选课.学号=xh and 选课.成绩>=60; ->end??

Mysql>delimiter ;

加粗部分的解释：首先去强调两个表之间的主键相连，接着是输入参数的限制条件，输出参数没有条件限制，对这个过程没有关系，接着就是额外的条件成绩>=60分。 记得每段程序结束后更改回来结束符。

※MySQL的用户账号以及相关信息都会存储在一个名为mysql的数据库中，这个数据库中有一个名为user的数据表，包含了所有的用户账号，并且由一个名为user的列存储用户的登录名。

例1：利用SELECT语句来查看MySQL数据库的使用者账号。 Mysql>select uesr from mysql.user;

执行完该语句的结果显示为“root”，root账号具有对数据库所有的管理权限，因此在日常的操作中，要尽量减少root用户对数据库进行操作。

※创建用户账号：CREATE USER<用户名>[IDENTIFIED]BY[PASSWORD]<口令>

<用户名>：指定创建用户账号，格式为'user name'@'host name'，其中'user name'是用户名，'host name'是用户连接MySQL时主机的名字，若在创建过程中，只给出了账户中的用户名而没有指定主机名，则主机名默认为“%”，表示一组主机。

[IDENTIFIED]BY子句：用于指定用户账号对应的口令，若该用户账号没有口令，则可省略此子句。

例2：在MySQL服务器中添加一个新用户，其用户名为zhangsan，主机名为localhost，用户口令设置为明文123。

Mysql>create user 'zhangsan'@'localhost' identified by '123'; Mysql>create user zhangsan@localhost identified by '123';

有没有zhangsan@localhost的各自引号不影响程序进行，只是为了格式规整最好写上。 此时可以再次查看用户名user列是否多了一个账户。 Mysql>select user from mysql.user;

+----------+ | user | +----------+ | root | | zhangsan | +----------+

※删除用户：DROP USER <用户名1>[<用户名2>]

注意：用户的删除不会影响他们之前所创建的表、索引或者其他数据库对象，因为MySQL不会记录是谁创建了这些对象。

例3：删除例2创建的用户zhangsan。 Mysql>drop user zhangsan; （×）

因为只给出了用户名而没有给出主机名。 Mysql>drop user zhangsan@localhost;

※修改用户账号：RENAME USER <旧用户>TO<新用户>

例4：添加一个新的账号liming，密码为123456，主机为localhost，然后修改成wanghong。 Mysql>create user 'liming'@'localhost' identified by '123456';

Mysql>rename user 'liming'@'localhost' to 'wanghong'@'localhost';

※修改用户口令：

PASSWORD()函数为单向加密函数，一旦加密后不能解出原明文。

例5：将前面例子中用户wanghong的口令修改成明文“hello”所对应的PASSWORD()函数返回的散列值：

/*这道题的意思可以理解为更改wanghong原先密码123456为hello的原文*/ 首先在MySQL命令行客户端输入下面的SQL语句，得到明文hello所对应的PASSWORD()函数返回的散列值：

Mysql>select password('hello');

此时会输出hello的散列值。

+-------------------------------------------+

| password('hello') | +-------------------------------------------+

| *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 | +-------------------------------------------+

接着使用SET PASSWORD语句修改wanghong的口令为明文“hello”对应的散列值。 Mysql>set password for 'wanghong'@'localhost'

->=' *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119'; 此时口令修改成功。

※新创建的用户只有登录MySQL服务器的权限，没有任何其他权限，不能进行其他操作。 例6：利用show grants for语句来查询用户“wanghong”的权限。 Mysql>show grants for 'wanghong'@'localhost'; 得到以下表格；

+-----------------------------------------------------------------------------------------------------------------+ |Grants for wanghong@localhost |

+-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'wanghong'@'localhost' IDENTIFIED BY PASSWORD '*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119' |

+-----------------------------------------------------------------------------------------------------------------+ 由此可以知道只有一个权限。

※权限的授予： GRANT

<权限类型>[(<列名>)][<权限类型>[(<列名>)]] ON <对象> TO <用户>

ON子句：用于指定权限授予的对象和级别，如可在ON关键字后面给出要授予权限的数据库名或表名等。 例7：授予用户“wanghong”对数据库mytest的表students，拥有列student_id和列student_name的SELECT权限。

Mysql>grant select (student_id,student_name)

->on mytest.students to 'wanghong'@'localhost'; ※权限设置成功后，尝试用wanghong 的账号和密码

*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119来登录。 执行程序：

Mysql>select student_id,student_name from mytest.students; 若是执行mysql>select * from mytest.students;

则程序会出错，因为该账户没有查看全表的权限。

例8：当前系统中不存在用户liming和huang，要求创建这两个用户，并设置对应的系统登陆口令，同时授予他们对数据库mytest的表students，拥有select和update的权限。

MySQL>grant select,update ->on mytest.students

->to 'liming'@'localhost' identified by '123' , ->'huang'@'localhost' identified by '789';

注意：这里不用去重新一次次建立用户然后授予权限，直接可以在这里顺便建立。

例9：授予系统中已存在的用户wanghong对数据库mytest拥有所有数据库操作的权限。 Mysql>grant all

->on mytest.* /*表示mytest数据库中所有的表*/ ->to 'wanghong'@'localhost';

※在授予数据库权限、授予表权限、授予列权限、授予用户权限时，最有效率的权限时用户权限。

其中用户权限包括：CREATE USER;SHOW DATABASES;

※权限的转移：WITH GRANT OPTION 例11：授予当前系统中一个不存在的用户stard，对数据库mytest中的表students拥有SELECT和UPDATE的权限，并允许其可以将自身的权限授予其他用户。 Mysql>grant select,update

->on mytest.students

->to 'stard'@'localhost' identified by '123'

/*如果本来就没有这个用户的就要设置密码，这样不用专门再去创建新用户，但是如果本身就存在这个用户的，那么加粗的这段不写。*/

->with grant option;

※权限的限制：如果WITH 子句中后面紧跟的是如下

MAX_QUERIES_PER_HOUR<次数>：限制每小时可以查询数据库的次数。 MAX_UPDATES_PER_HOUR<次数>：限制每小时可以修改数据库的次数。

MAX_CONNECTIONS_PER_HOUR<次数>：限制每小时可以连接数据库的次数。 MAX _USER_CONNECTIONS<次数>：限制同时连接MySQL的最大用户数。 其中QUERIES的中文意思是问题，询问的意思，这里指代查询。

例12：授予用户wanghong对数据库mytest的表students，拥有每小时处理一条SELECT语句的权限。——每小时处理一条SELECT，SELECT其实是查询数据库语句。 Mysql>grant select

->on mytest.students

->to 'wanghong'@'localhost' ->with max_queries_per_hour 1;

※权限的撤销：可以使用REVOKE语句撤销一个用户的权限，此用户不会被删除。 语法格式： 第一种：

REVOKE <权限类型>[(<列名>)]??<权限类型>[(<列名>)] ON <对象类型><权限名>FROM <用户1>[,<用户2>]?? 第二种：

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