mysql笔记

 

MySql学习：

一．

要把一个表从一个类型转到另一个类型，可使用ALTER TABLE语句，这个语句指明新的类型：

ALTER TABLE t ENGINE = MYISAM;

ALTER TABLE t TYPE = BDB;

 

二．

存储过程：

下面一些情况下存储程序尤其有用：

1．当用不同语言编写多客户应用程序，或多客户应用程序在不同平台上运行且需要执行相同的数据库操作之时。

2．安全极为重要之时。比如，银行对所有普通操作使用存储程序。这提供一个坚固而安全的环境，程序可以确保每一个操作都被妥善记入日志。在这样一个设置中，应用程序和用户不可能直接访问数据库表，但是仅可以执行指定的存储程序。

存储程序和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建的子程序。一个子程序要么是一个程序要么是一个函数。使用CALL语句来调用程序，程序只能用输出变量传回值。就像别其它函数调用一样，函数可以被从语句外调用（即通过引用函数名），函数能返回标量值。存储子程序也可以调用其它存储子程序。

在MySQL 5.1中，一个存储子程序或函数与特定的数据库相联系。这里有几个意思：

1． 当一个子程序被调用时，一个隐含的USE db_name 被执行（当子程序终止时停止执行）。存储子程序内的USE语句时不允许的。

2． 你可以使用数据库名限定子程序名。这可以被用来引用一个不在当前数据库中的子程序。比如，要引用一个与test数据库关联的存储程序p或函数f，你可以说CALL test.p()或test.f()。

3． 数据库移除的时候，与它关联的所有存储子程序也都被移除。

下面一节描述用来创建，改变，移除和查询存储程序和函数的语法。

CREATE PROCEDURE和CREATE FUNCTION

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

 [characteristic ...] routine_body

 

CREATE FUNCTION sp_name ([func_parameter[,...]])

 RETURNS type

 [characteristic ...] routine_body

 

proc_parameter:

 [ IN | OUT | INOUT ] param_name type

 

func_parameter:

 param_name type

 

type:

 Any valid MySQL data type

 

characteristic:

 LANGUAGE SQL

 | [NOT] DETERMINISTIC

 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

 | SQL SECURITY { DEFINER | INVOKER }

 | COMMENT 'string'

 

routine_body:

 Valid SQL procedure statement or statements

如果子程序名和内建的SQL函数名一样，定义子程序时，你需要在这个名字和随后括号中间插入一个空格，否则发生语法错误。当你随后调用子程序的时候也要插入。为此，即使有可能出现这种情况，我们还是建议最好避免给你自己的存储子程序取与存在的SQL函数一样的名字。

由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT

注意: 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数）

RETURNS字句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

创建数据库：

mysql> create database itcast;

进入数据库：

mysql> use itcast;

创建表：

mysql> create table Student (stu_No int not null,stu_Name varchar(20) not null,stu_Address varchar(50));

显示当前数据库中的表：

mysql> show tables;

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

| Tables_in_itcast |

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

| student          |

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

添加主键约束：

mysql> alter table Student add constraint pk_stu_No primary key (stu_No);

显示指定表的列信息：

mysql> desc student;

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

| Field       | Type        | Null | Key | Default | Extra |

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

| stu_No      | int(11)     | NO   | PRI |         |       |

| stu_Name    | varchar(20) | NO   |     |         |       |

| stu_Address | varchar(50) | YES |     |         |       |

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

插入记录：

mysql> insert into Student values (1,'张三','武汉');

也可以这样插入：

mysql> insert into Student values (2,'李四','长沙'),(3,'王五','香港'),(4,'赵六'

'广州');

查询：

mysql> select * from student;

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

| stu_No | stu_Name | stu_Address |

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

| 1      | 张三     | 武汉        |

| 2      | 李四     | 长沙        |

| 3      | 王五     | 香港        |

| 4      | 赵六     | 广州        |

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

创建带输入参数的存储过程：

在程序被定义的时候，用mysql客户端delimiter命令来把语句定界符从 ;变为//。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。

mysql> delimiter //

mysql> create procedure proc_insert(in no int,in name varchar(20),in address var

char(50))

    -> begin

    -> insert into Student values (no,name,address);

    -> end

    -> //

Query OK, 0 rows affected (0.00 sec)

 

mysql> delimiter ;

执行存储过程：

mysql> call proc_insert(5,'田七','深圳');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from Student;

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

| stu_No | stu_Name | stu_Address |

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

| 1      | 张三     | 武汉        |

| 2      | 李四     | 长沙        |

| 3      | 王五     | 香港        |

| 4      | 赵六     | 广州        |

| 5      | 田七     | 深圳        |

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

5 rows in set (0.00 sec)

再次执行这条语句时报错了。因为前面加了主键约束，这意味着一旦定义成了主键就不能有重复的字段。唯一约束也是如此。

mysql> call proc_insert(5,'田七','深圳');

ERROR 1062 (23000): Duplicate entry '5' for key 1

 

创建一个不带参数的存储过程：

mysql> create procedure proc_select()

    -> begin

    -> select * from Student;

    -> end

    -> //

Query OK, 0 rows affected (0.00 sec)

 

mysql> delimiter ;

mysql> call proc_select();

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

| stu_No | stu_Name | stu_Address |

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

| 1      | 张三     | 武汉        |

| 2      | 李四     | 长沙        |

| 3      | 王五     | 香港        |

| 4      | 赵六     | 广州        |

| 5      | 田七     | 深圳        |

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

5 rows in set (0.01 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

创建一个带一个输出参数，一个输入参数的存储过程。

mysql> delimiter //

mysql> create procedure proc_search(out name varchar(20),in no int)

    -> begin

    -> select stu_Name into name from Student where stu_No=no;

    -> end

    -> //

Query OK, 0 rows affected (0.00 sec)

 

mysql> delimiter ;

 

应当象这样执行：

mysql> call proc_search(@name,'1');

Query OK, 0 rows affected (0.00 sec)

 

mysql> select @name;

+-------+

| @name |

+-------+

| 张三 |

+-------+

1 row in set (0.00 sec)

 

 

创建自定义函数：

如果在存储函数中的RETURN语句返回一个类型不同于在函数的RETURNS子句中指定类型的值，返回值被强制为恰当的类型。比如，如果一个函数返回一个ENUM或SET值，但是RETURN语句返回一个整数，对于SET成员集的相应的ENUM成员，从函数返回的值是字符串。

mysql> delimiter //

mysql> create function getName(no int) returns varchar(20)

    -> begin

    -> declare name varchar(20);

    -> select stu_Name into name from Student where stu_No=no;

    -> return name;

    -> end

    -> //

Query OK, 0 rows affected (0.00 sec)

 

mysql> select getName(3);

    -> //

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

| getName(3) |

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

| 王五       |

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