本文共 21221 字,大约阅读时间需要 70 分钟。
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。 MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型 | 大小 | 范围(有符号-默认) | 范围(无符号) | 用途 |
TINYINT | 1 字节 | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 字节 | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 字节 | (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 字节 | (-3.402 823 466 E+38,1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 字节 | (1.797 693 134 862 315 7 E+308,2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | |
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型 | 大小 (字节) | 范围 | 格式 | 用途 |
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 8 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 | 大小 | 用途 |
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
TEXT | 0-65 535字节 | 长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求。 1、显示所有数据库:Show databases; 2、创建数据库:Create database 数据库名; 3、删除数据库:Drop database 数据库名; CREATE TABLE 表名( 属性名 数据类型 [完整性约束条件], 注: UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。简单来说:unique=primary key+not null 区别:1、primary key只能有一个,unique可以有多个同时存在; 2、primary key不能为空,而unique可以为空; primary key一般在设计逻辑中用作记录标识,而unique只是用来保证唯一性 UNIQUE (bookId)//新建表时将bookId设为唯一 CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)//设置Id_P和LastName都为唯一并命名为uc_PersonID
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)//设置本表Id_p字段和Persons表的Id_p主键关联
1,查看基本表结构: DESCRIBE(或DESC) 表名;//返回表字段及其详细信息表格 2,查看表详细结构: SHOW CREATE TABLE 表名;//返回结果是创建表的sql 1、 drop table 表名; //彻底删除表格,删除字段数据,释放内存空间 2、 truncate 表名; //删除表格数据,不删除字段,释放内存空间 3、 delete from 表名;或delete * from 表名;// 删除内容不删除定义,不释放空间,系统一行一行地删,效率较truncate低 1、truncate 将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时,经过 truncate 操作后的表比Delete操作后的表要快得多。 当表被清空后表和表的索引将重新设置成初始大小,而delete则不能。 2、truncate 是隐式提交,不能触发任何Delete触发器,不能通过rollback命令撤回,而delete可以,因为delete 语句每次删除一行,都在事务日志中为所删除的每行记录一项 ; 3、truncate table 在功能上与不带 WHERE 子句的 delete语句相同:二者均删除表中的全部行。但 truncate 比 delete速度快,且使用的系统和事务日志资源少,因为delete是一行一行的删除。 1,修改表名 ALTER TABLE 旧表名 RENMAE 新表名 ; 2,修改字段 ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型 3,增加字段 ALTER TABLE 表名 ADD 属性名 数据类型 [完整性约束条件] [FIRST | AFTER 属性名 2] 4,删除字段 ALTER TABLE 表名 DROP 属性名 格式:INSERT INTO 表名 VALUES(值 1,值 2,值 3,...,值 n); 格式:INSERT INTO 表名(属性 1,属性 2,...,属性 n) VALUES(值 1,值 2,值 3,...,值 n); INSERT INTO 表名 [(属性列表)] VALUES(取值列表 1),(取值列表 2) ..., (取值列表 n); UPDATE 表名 SET 属性名 1=取值 1,属性名 2=取值 2, ..., 属性名 n=取值 n WHERE 条件表达式; DELETE FROM 表名 [WHERE 条件表达式] SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] IN (元素 1,元素 2,元素 3);//查找属性(不为)为元素1、元素2和元素3的结果 SELECT 字段 1,字段 2...FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 [...AND 条件表达式 n] //and多条件查询 SELECT 字段 1,字段 2...FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 [...OR 条件表达式 n] //or多条件查询 SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 > 指定值;//大于小于等符号 SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 AND 取值 2;//两端都包含 SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’; SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 IS [NOT] NULL; SELECT DISTINCT 字段名 FROM 表名; SELECT 字段 1,字段 2...FROM 表名 ORDER BY 属性名 [ASC|DESC] //ASC升序(默认) DESC降序 GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP] 2,与 GROUP_CONCAT()函数一起使用; 4,与 HAVING 一起使用(限制输出的结果); 5,与 WITH ROLLUP 一起使用(最后加入一个总和行); 语句 SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus; 的返回结果为 +----------+------------------+ | locus | GROUP_CONCAT(id) | +----------+------------------+ +----------+------------------+ 即将查询到的括号内字段的结果合并到一起并用逗号隔开 语句 SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;的返回结果为 +----------+----------------------------------------------------------+ | locus | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') | +----------+----------------------------------------------------------+ | AB086827 | 2_1 | | AF040764 | 24_23 | +----------+----------------------------------------------------------+ 同时有CONCAT函数将查询结果连成一个字符串,但是查询的字段中有null时则整个结果为null,如: SELECT CONCAT(id, ‘,’, name,age) AS con FROM info LIMIT 1;返回结果为 +----------+ | con | +----------+ | 1,BioCyc23 | WITH ROLLUP会在返回结果的最后加上一行将查询出的如果是数字结果则将所有结果数字相加,如果是字符结果则将所有字符合并 SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP; SELECT 字段 1,字段 2...FROM 表名 LIMIT 初始位置,记录数; COUNT()函数用来统计记录的条数; 与 GOUPE BY 关键字一起使用; SUM()函数是求和函数; 与 GOUPE BY 关键字一起使用;如:SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;//根据学生姓名分组算出每个学生的分数总和 注意:当用group by 分组查询时只能查出分组的字段和聚合函数的结果,其他字段无法查出 AVG()函数是求平均值的函数; 与 GOUPE BY 关键字一起使用;//SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";//算出张三的平均分 MAX()函数是求最大值的函数; 与 GOUPE BY 关键字一起使用; MIN()函数是求最小值的函数; 与 GOUPE BY 关键字一起使用; 连接查询是将两个或两个以上的表按照某个条件连接起来,从中选取需要的数据; 如:SELECT * from t_book,t_booktype; 返回结果将两个表的所有组合结果返回 内连接查询是一种最常用的连接查询。内连接查询可以查询两个或者两个以上的表; SELECT * from t_book b,t_booktype t where t.id=b.id; 或SELECT * from t_book,t_booktype where t_book.id=t_booktype.id; 也可这么写来查询需要的字段:SELECT bookName,author,bookTypeName from t_book,t_booktype where t_book.id=t_booktype.id; 注意:如果查询的字段(如id)中两个表都有时这种写法会出错,因为无法得知id是哪个表的字段,所以平时多表查询时要写别名 修改为:SELECT tb.bookName,tb.author,tby.id from t_book tb ,t_booktype tby where tb.id=tby.id; SELECT 属性名列表 FROM 表名 1 LEFT|RIGHT JOIN 表名 2 ON 表名 1.属性名 1=表名 2.属性名 2; 可以查询出“表名 1”的所有记录,而“表名 2”中,只能查询出匹配的记录;如: select * from t_book tb left JOIN t_booktype tby on tb.bookTypeId=tby.id; 查询结果: 可以查询出“表名 2”的所有记录,而“表名 1”中,只能查询出匹配的记录;如: select * from t_book tb right JOIN t_booktype tby on tb.bookTypeId=tby.id; 查询结果: 一个查询语句的条件可能落在另一个 SELECT 语句的查询结果中。 假如子查询查询到记录,则进行外层查询,否则,不执行外层查询;not exists表示内层没有内容时才查询外层 如:SELECT * from t_book where EXISTS(select * from t_booktype);//表示t_booktype中返回的有内容时才执行外层t_book的查询 ANY 关键字表示满足其中任一条件;如:SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);//表示筛选价格大于任意t_pricelevel表中选出的价格的结果,即比最小的大的价格就满足条件 ALL 关键字表示满足所有条件;如:SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);//表示筛选价格大于所有t_pricelevel表中选出的价格的结果,即比最大的价格大的才满足条件 使用 UNION 关键字是,数据库系统会将所有的查询结果合并到一起,然后去除掉相同的记录; 索引是由数据库表中一列或者多列组合而成,其作用是提高对表中数据的查询速度,建立索引查询速度有数量级提升 ; 使用 UNIQUE 参数可以设置,在创建唯一性索引时,限制该索引的值必须是唯一的; 使用 FULLTEXT 参数可以设置,全文索引只能创建在 CHAR,VARCHAR,TEXT 类型的字段上。主要作用 就是提高查询较大字符串类型的速度;只有 MyISAM 引擎支持该索引,Mysql 默认引擎不支持; 在表中可以给单个字段创建索引,单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引; 使用 SPATIAL 参数可以设置空间索引。空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数 据的效率;只有 MyISAM 引擎支持该索引,Mysql 默认引擎不支持; CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件], [UNIQUE | FULLTEXT | SPATIAL ] INDEX| KEY [别名] (属性名 1 [(长度)] [ASC | DESC]) id INT PRIMARY KEY AUTO_INCREMENT, bookName VARCHAR(20) NOT NULL, //UNIQUE INDEX (bookId) //创建唯一性索引 //UNIQUE INDEX bokId(bookId) //创建唯一性索引并起别名为bokId //UNIQUE INDEX bokId(bookId,id) //创建多列索引并起别名为bokId CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (属性名 [(长度)] [ ASC | DESC]); 如:CREATE INDEX bookId ON mytable(bookId); //创建普通索引 CREATE UNIQUE INDEX bookId ON mytable(bookId); //创建唯一索引 CREATE UNIQUE INDEX bookId_price ON mytable(bookId,price); //创建多列索引 ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (属性名 [(长度)] [ ASC | DESC]); 如:ALTER TABLE mytable ADD INDEX index_bookId(bookId); //创建普通索引 ALTER TABLE mytable ADD UNIQUE INDEX index_bookId(bookId); //创建唯一索引 ALTER TABLE mytable ADD UNIQUE INDEX index_bookId_price(bookId,price); //创建多列索引 DROP INDEX 索引名 ON 表名 ; 如:drop index index_bookId_price on mytable; 1,视图是一种虚拟的表,是从数据库中一个或者多个表中导出来的表。 2,数据库中只存放了视图的定义,而并没有存放视图中的数据,这些数据存放在原来的表中。 3,使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。 CREATE [ ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]; ALGORITHM 是可选参数,表示视图选择的算法; “属性清单”是可选参数,其指定了视图中各种属性的名词,默认情况下与 SELECT 语句中查询的属性相同; SELECT 语句参数是一个完整的查询语句,标识从某个表查出某些满足条件的记录,将这些记录导入视图中; WITH CHECK OPTION 是可选参数,表似乎更新视图时要保证在该视图的权限范围之内; ALGORITHM 包括 3 个选项 UNDEFINED、MERGE 和 TEMPTABLE。其中, UNDEFINED 选项表示 MySQL 将 自动选择所要使用的算法; MERGE 选项表示将使用视图的语句与视图定义合并起来,使得视图定义的某一部分 取代语句的对应部分; TEMPTABLE 选项表示将视图的结果存入临时表,然后使用临时表执行语句; CASCADED 是可选参数,表示更新视图时要满足所有相关视图和表的条件,该参数为默认值; LOCAL 表示更新视图时,要 满足该视图本身的定义条件即可; 如:创建视图:create view v1 as select * from mytable where bookId >30; 创建视图并给返回的结果栏目定义栏目名:CREATE VIEW v1(b,p) AS select bookId,bookName from mytable; 返回结果如:
注意查询视图字段时有别名的话按照别名查询,没有别名可以按照原字段名称查询; 4.1 DESCRIBE 视图名称; //查看视图字段属性信息 4.2 SHOW TABLE STATUS LIKE ‘视图名称’;// 查看视图基本信息,因为视图是虚表,故查询结果字段都为空;此sql也可以查询普通表的基本信息;后面没有like指定视图时表示查询库中所有表和视图的基本信息 4.3 SHOW CREATE VIEW 视图名称;//查看视图详细信息 5.1 CREATE OR REPLACE VIEW 语句修改视图 CREATE OR REPLACE [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]; 如:create or replace view v1 as select * from mytable where bookId < 30; ALTER [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }] [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]; 如:alter view v1 as select * from mytable; 更新视图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)表中的数据。因为视图是一个虚 拟的表,其中没有数据。通过视图更新时,都是转换基本表来更新。更新视图时,只能更新权限范围内的数据。 CREATE VIEW v1(b,p) AS select bookId,bookName from mytable; INSERT into v1 values(44,'think in java');//插入视图,再次查看视图v1时会看到插入的数据,视图操作实际是对表的操作,可以看到mytable表中增加了一条数据,但是其他没有插入的字段则是显示默认值 update v1 set b=8,p='设计模式' where b=44 delete from v1 where b=44; 删除视图是指删除数据库中已存在的视图。删除视图时,只能删除视图的定义,不会删除数据; DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE ] 如: DROP VIEW IF EXISTS v1; 触发器(TRIGGER)是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。 当数据库系统执行这些事件时,就会激活触发器执行相应的操作。 CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 ON 表名 FOR EACH ROW 事件发生时要执行的语句 如:CREATE TRIGGER trig_book AFTER INSERT UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id; 表示t_book表中国执行插入操作时触发t_bookType的更新操作,将bookNum加1 注:new是中间量,表示插入(更新)时的那条数据,如果是删除时用old,表示删除的那条数据 CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 CREATE TRIGGER trig_book2 AFTER DELETE UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id; INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据'); DELETE FROM t_test WHERE old.bookTypeId=t_test.id; 注:DELIMITER | 包裹起来避免触发多条sql时遇到分号中断执行的情况 3.1 SHOW TRIGGERS; 语句查看触发器信息 1,CURDATE() 返回当前系统日期; 如:select CURDATE() ,CURTIME(),MONTH(created_at) from theme13; 3,MONTH(d) 返回日期 d 中的月份值,范围是 1~12 1,CHAR_LENGTH(s) 计算字符串 s 的字符数; 1,PASSWORD(str) 一般对用户的密码加密 不可逆 3,ENCODE(str,pswd_str) 加密函数,结果是一个二进制数,必须使用 BLOB 类型的字段来保存它; 4,DECODE(crypt_str,pswd_str) 解密函数; 存储过程和函数是在数据库中定义一些 SQL 语句的集合,然后直接调用这些存储过程和函数来执行已经定义好 的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且,存储过程和函数是在 MySQL 服务器中存储和执行的,可以减少客户端和服务器端的数据传输; CREATE PROCEDURE sp_name([proc_parameter[,...]]) [characteristic...] routine_body proc_parameter 表示存储过程的参数列表; characteristic 参数指定存储过程的特性; routine_body 参数是 SQL 代码的内容,可以用 BEGIN...END 来标志 SQL 代码的开始和结束。 proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输入输出类型、参数名称和参数类型。 [ IN | OUT | INOUT ] param_name type 其中,IN 表示输入参数;OUT 表示输出参数;INOUT 表示既可以是输入,也可以是输出;param_name 参数是 存储过程的参数名称;type 参数指定存储过程的参数类型,该类型可以是 MySQL 数据库的任意数据类型; Characteristic 参数有多个取值。其取值说明如下: LANGUAGE SQL:说明 routine_body 部分是由 SQL 语言的语句组成,这也是数据库系统默认的语言。 [ NOT ] DETERMINISTIC :指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每 次执行存储过程时,相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是非确定的,相同的输入 { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } :指明子程序使用 SQL 语句的限制; CONTAINS SQL 表示子程序包含 SQL 语句,但不包含读或写数据的语句;NO SQL 表示子程序中不包含 SQL 语句;READS SQL DATA 表示子程序中包含读数据的语句;MODIFIES SQL DATA 表示子程序中包含写数据的 语句。默认情况下,系统会指定为 CONTAINS SQL; SQL SECURITY { DEFINER | INVOKER };指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行; INVOKER 表示调用者可以执行。默认情况下,系统指定的权限是 DEFINER。 CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT) SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT; 调用:CALL pro_book(1,@total); CREATE FUNCTION sp_name ( [func_parameter[,...]] ) [ characteristic... ] routine_body sp_name 参数是存储函数的名称;func_parameter 表示存储函数的参数列表;RETURNS type 指定返回值的 类型;characteristic 参数指定存储过程的特性,该参数的取值与存储过程中的取值是一样的;routine_body 参数 是 SQL 代码的内容,可以用 BEGIN...END 来标志 SQL 代码的开始和结束; func_parameter 可以由多个参数组成,其中每个参数由参数名称和参数类型组成,其形式如下: 其中,param_name 参数是存储函数的参数名称;type 参数指定存储函数的参数类型, CREATE FUNCTION func_book (bookId INT) RETURN ( SELECT bookName FROM t_book WHERE id=bookId ); DECLARE var_name [,...] type [ DEFAULT value ] CREATE PROCEDURE pro_user() DECLARE a,b VARCHAR(20) ; INSERT INTO t_user VALUES(NULL,a,b); //将t_user2表数据读取出来直接插入到t_user中 CREATE PROCEDURE pro_user3() DECLARE a,b VARCHAR(20) ; SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1; INSERT INTO t_user VALUES(NULL,a,b); SET var_name = expr [,var_name=expr] ... SELECT col_name[,...] INTO var_name[,...] FROM table_name WHERE condition CREATE PROCEDURE pro_user2() DECLARE a,b VARCHAR(20) ; SET a='java1234',b='123456'; INSERT INTO t_user VALUES(NULL,a,b); 查询语句可能查询出多条记录,在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标的使 用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前,并且声明在变量和条 DECLARE cursor_name CURSOR FOR select_statement ; FETCH cursor_name INTO var_name [,var_name ... ]; CREATE PROCEDURE pro_user4() DECLARE a,b VARCHAR(20) ; DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2; //声明游标 FETCH cur_t_user2 INTO a,b; //将游标数据赋给a和b INSERT INTO t_user VALUES(NULL,a,b); CLOSE cur_t_user2; //关闭游标 存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用 IF 语句、CASE 语句、LOOP语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制。 IF search_condition THEN statement_list [ ELSEIF search_condition THEN statement_list ]... CREATE PROCEDURE pro_user5(IN bookId INT) SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId; IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId; INSERT INTO t_user VALUES(NULL,'2312312','2321312'); WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... CREATE PROCEDURE pro_user6(IN bookId INT) SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId; WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId; WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312'); ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312'); LOOP 语句可以使某些特定的语句重复执行,实现一个简单的循环。但是 LOOP 语句本身没有停止循环的语句,必须是遇到 LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下: LEAVE 语句主要用于跳出循环控制。语法形式如下: CREATE PROCEDURE pro_user7(IN totalNum INT) IF totalNum=0 THEN LEAVE aaa ; ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312'); ITERATE 语句也是用来跳出循环的语句。但是,ITERATE 语句是跳出本次循环,然后直接进入下一次循环。基本语法: CREATE PROCEDURE pro_user8(IN totalNum INT) IF totalNum=0 THEN LEAVE aaa ; ELSEIF totalNum=3 THEN ITERATE aaa ; INSERT INTO t_user VALUES(totalNum,'2312312','2321312'); REPEAT 语句是有条件控制的循环语句。当满足特定条件时,就会跳出循环语句。REPEAT 语句的基本语法形式如下: CREATE PROCEDURE pro_user9(IN totalNum INT) INSERT INTO t_user VALUES(totalNum,'2312312','2321312'); [ begin_label : ] WHILE search_condition DO CREATE PROCEDURE pro_user10(IN totalNum INT) INSERT INTO t_user VALUES(totalNum,'2312312','2321312'); CALL sp_name( [parameter[,...]] ) fun_name( [parameter[,...]] ) 4.1 SHOW STATUS 语句查看存储过程和函数的状态 SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’ ] ;//pattern代表存储过程名 如:SHOW PROCEDURE STATUS LIKE 'pro_book'; 4.2 SHOW CREATE 语句查看存储过程的函数的定义 SHOW CREATE { PROCEDURE | FUNCTION } sp_name ; 如:SHOW CREATE PROCEDURE pro_book;
4.3 从 information_schema.Routines 表中查看存储过程和函数的信息 ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ] { CONTAINS SQL } NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } 其中,sp_name 参数表示存储过程或函数的名称;characteristic 参数指定函数的特性。CONTAINS SQL 表示子程 序包含 SQL 语句,但不包含读或写数据的语句;NO SQL 表示子程序中不包含 SQL 语句;READS SQL DATA 表 示 子 程 序 中 包 含 数 据 的 语 句 ; MODIFIES SQL DATA 表 示 子 程 序 中 包 含 写 数 据 的 语 句 。 SQL SECURITY{ DEFINER | INVODER } 指明谁有权限来执行。 DEFINER 表示只有定义者自己才能够执行; INVODER 表示调用者可以执行。COMMENT ‘string’ 是注释信息。 ALTER PROCEDURE pro_book COMMENT '我来测试一个COMMENT'; //修改pro_book 的注释为'我来测试一个COMMENT' DROP {PROCEDURE | FUNCTION } sp_name ; DROP PROCEDURE pro_user3; 备份数据可以保证数据库中数据的安全,数据库管理员需要定期的进行数据库备份; mysqldump -u username -p dbname table1 table2 ... > BackupName.sql dbname 参数表示数据库的名称;table1 和 table2 参数表示表的名称,没有该参数时将备份整个数据库; BackupName.sql 参数表示备份文件的名称,文件名前面可以加上一个绝对路径。通常以 sql 作为后缀。 mysqldump -u root -p db_book > c:\db_book.sql //将整个db_book数据库备份到c盘db_book.sql文件中,当然图形界面都是可以的 Mysql -u root -p [dbname] < backup.sql dbname 参数表示数据库名称。该参数是可选参数,可以指定数据库名,也可以不指定。指定数据库名时,表 示还原该数据库下的表。不指定数据库名时,表示还原特定的一个数据库。而备份文件中有创建数据库的语句。 转载地址:http://juuhj.baihongyu.com/