计算机二级MySQL基本知识点(8)

其语法格式为：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>]?? 第二种：

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库计算机二级MySQL基本知识点(8)在线全文阅读。