澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

澳门新浦京娱乐场网站:MySQL游标循环取出空值的

早上同事要我写个MySQL去除重复数据的SQL,想起来上次写过一篇MySQL去除重复数据的博客,使用导入导出加唯一索引实现的,但是那种方式对业务影响较大,所以重新写一个存储过程来删重复数据,这一写就写了一个上午,这种BUG确实是很令人沮丧和浪费时间的。

SQL语言基础(一)

本章,我们将会重点探讨SQL语言基础,学习用SQL进行数据库的基本数据查询操作。另外请注意本章的SQL语法基于MySQL数据库的PL/SQL语法。

PL/SQL: Procedural Language SQL 过程化语言 SQL

MySQL心得7-1-存储过程

用于操作数据库的SQL一般分为两种,一种是查询语句,也就是我们所说的SELECT语句,另外一种就是更新语句,也叫做数据操作语句。言外之意,就是对数据进行修改。在标准的SQL中有3个语句,它们是INSERT、UPDATE以及DELETE。在MySQL中又多了一个REPLACE语句,因此,本文以MySQL为背景来讨论如何使有SQL中的更新语句。

MySQL 变量和条件,MySQL变量条件

这里把流程简单的描述一下,删重复数据的逻辑很简单:

SQL定义

SQL,Structured Query Language,结构化查询语言。SQL 是用于访问和处理数据库的标准的计算机语言。

读音:/ˈɛs kjuː ˈɛl/ 或者 /ˈsiːkwəl/

SQL语言,和C、C 、C#、Java、Python、PHP一样,是一种编程语言,每个月都可以看到Tiobe编程排行榜上SQL上榜。同时SQL又是一种标准,每个数据库的厂商都提供了对标准SQL的支持。此外每个厂商也基本上扩展了标准SQL的使用。

SQL编程是指用SQL语言来完成对数据库的逻辑性操作。这个操作可以比较简单,只有一段SQL来完成最基本的数据库操作;也可以比较复杂,需要多段SQL在一起建立起存储过程来完成复杂的数据库的操作。

 

  一、INSERT和REPLACE

概述  

 变量在存储过程中会经常被使用,变量的使用方法是一个重要的知识点,特别是在定义条件这块比较重要。

 mysql版本:5.6

1.根据重复判断条件找出重复记录的最小主键(一般是ID列)。

注释

SQL的注释有两种形式:单行注释和多行注释。

  1. 使用存储过程的优点有:

  INSERT和REPLACE语句的功能都是向表中插入新的数据。这两条语句的语法类似。它们的主要区别是如何处理重复的数据。

变量定义和赋值  

#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主键',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部门id'
);

INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);

declare定义变量

在存储过程和函数中通过declare定义变量在BEGIN...END中,且在语句之前。并且可以通过重复定义多个变量

注意:declare定义的变量名不能带‘@’符号,mysql在这点做的确实不够直观,往往变量名会被错成参数或者字段名。

DECLARE var_name[,...] type [DEFAULT value]

例如:

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

END$$
DELIMITER ;

SET变量赋值 

SET除了可以给已经定义好的变量赋值外,还可以指定赋值并定义新变量,且SET定义的变量名可以带‘@’符号,SET语句的位置也是在BEGIN ....END之间的语句之前。

1.变量赋值

SET var_name = expr [, var_name = expr] ...

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

  SELECT @pcount;

澳门新浦京娱乐场网站 1

 2.通过赋值定义变量

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SET @ID=1;
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;
SELECT @ID;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

澳门新浦京娱乐场网站 2

2.在符合重复条件的记录中,把主键大于最小主键的记录全部删掉即可。

单行注释:

单行以--开头,该行之后的内容以及SQL代码将会被注释掉。

MySQL 中,--后面需要加一个空格才能代表是注释,对于其他数据库,不需要空格也可以。

例如

-- 该行是注释,无论是文本还是SQL代码都会被注释掉
-- SELECT * FROM Country;

 

  1. INSERT的一般用法

SELECT ... INTO语句赋值

 通过select into语句可以将值赋予变量,也可以之间将该值赋值存储过程的out参数,上面的存储过程select into就是之间将值赋予out参数。

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
DECLARE Pid INT;
SELECT COUNT(id) INTO Pid FROM Employee WHERE depid=pdepid AND name=pname;
SELECT Pid;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

这个存储过程就是select into将值赋予变量;

 澳门新浦京娱乐场网站 3

表中并没有depid=101 and name='陈'的记录。 

假设我有如下表,需要删除start_time和end_time都一样的重复记录。

多行注释:

多行注释是以/*开头,以*/结尾的,中间的部分将会被全部注释掉。

例如

/*
该行是注释
该行还是注释
SELECT * FROM Country;
*/

(1)存储过程在服务器端运行,执行速度快。

  MySQL中的INSERT语句和标准的INSERT不太一样,在标准的SQL语句中,一次插入一条记录的INSERT语句只有一种形式。

条件  

条件的作用一般用在对指定条件的处理,比如我们遇到主键重复报错后该怎样处理。 

定义条件

 定义条件就是事先定义某种错误状态或者sql状态的名称,然后就可以引用该条件名称开做条件处理,定义条件一般用的比较少,一般会直接放在条件处理里面。

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

1.没有定义条件:

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee_insert();

#查询变量值
SELECT @ID,@X;

澳门新浦京娱乐场网站 4

 报主键重复的错误,其中1062是主键重复的错误代码,23000是sql错误状态

澳门新浦京娱乐场网站 5

2.定义处理条件

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR 1062;
#引用前面定义的条件名称并做赋值处理
DECLARE EXIT HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

CALL Pro_Employee_insert();

SELECT @ID,@X;

在执行存储过程的步骤中并没有报错,但是由于我定义的是exit,所以在遇到报错sql就终止往下执行了。

澳门新浦京娱乐场网站 6

接下来看看continue的不同

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR SQLSTATE '23000';
#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

CALL Pro_Employee_insert();

SELECT @ID,@X;

其中红色标示的是和上面不同的地方,这里定义条件使用的是SQL状态,也是主键重复的状态;并且这里使用的是CONTINUE就是遇到错误继续往下执行。

澳门新浦京娱乐场网站 7

澳门新浦京娱乐场网站 8

条件处理

条件处理就是之间定义语句的错误的处理,省去了前面定义条件名称的步骤。

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
    CONTINUE| EXIT| UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

handler_type:遇到错误是继续往下执行还是终止,目前UNDO还没用到。

CONTINUE:继续往下执行

EXIT:终止执行

condition_values:错误状态

SQLSTATE [VALUE] sqlstate_value:就是前面讲到的SQL错误状态,例如主键重复状态SQLSTATE '23000'

condition_name:上面讲到的定义条件名称;

SQLWARNING:是对所有以01开头的SQLSTATE代码的速记,例如:DECLARE CONTINUE HANDLER FOR SQLWARNING。

NOT FOUND:是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION:是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

mysql_error_code:是错误代码,例如主键重复的错误代码是1062,DECLARE CONTINUE HANDLER FOR 1062

 

语句:

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN

#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x=2;
#开始事务必须在DECLARE之后
START TRANSACTION ;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(7,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

IF @x=2 THEN
  ROLLBACK;
ELSE
  COMMIT;
END IF;  

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee_insert();
#查询
SELECT @ID,@X;

澳门新浦京娱乐场网站 9

通过SELECT @ID,@X可以知道存储过程已经执行到了最后,但是因为存储过程后面有做回滚操作整个语句进行了回滚,所以ID=7的符合条件的记录也被回滚了。

澳门新浦京娱乐场网站 10

SQL语言分类

SQL语言大体上可以分为六大类别:

一:数据查询语言(DQL :Data Query Language):Select
二:数据操作语言(DML :Data Manipulation Language):Insert、Update、Delete
三:事务处理语言(TPL):BEGIN TRANSACTION、Commit、Rollback
四:数据控制语言(DCL):Grant、Revoke
五:数据定义语言(DDL):Create、Drop、Alter(table,index etc.)
六:指针控制语言(CCL):DECLARE CURSOR,FETCH INTO

 

  INSERT INTO tablename(列名…) VALUES(列值);

总结  

变量的使用不仅仅只有这些,在光标中条件也是一个很好的功能,刚才测试的是continue如果使用EXIT的话语句执行完“SET @ID=2;”就不往下执行了,后面的IF也不被执行整个语句不会被回滚,但是使用CONTINE当出现错误后还是会往下执行如果后面的语句还有很多的话整个回滚的过程将会很长,在这里可以利用循环,当出现错误立刻退出循环执行后面的if回滚操作,在下一篇讲循环语句会写到,欢迎关注。

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接。

《欢迎交流讨论》

变量和条件,MySQL变量条件 概述 变量在存储过程中会经常被使用,变量的使用方法是一个重要的知识点,特别是在定义条件这块比较重...

那么存储过程如下:

DQL语言

DQL,Data Query Language,数据查询语言。

SELECT 查询

基本语法结构

SELECT <字段名1, 字段名2...>
FROM <表名1, 表名2...>
WHERE <条件>;

(2)存储过程执行一次后,其执行规划就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。

  而在MySQL中还有另外一种形式。

DELIMITER //
DROP PROCEDURE IF EXISTS Del_Dup_FOR_TEST;
CREATE PROCEDURE Del_Dup_FOR_TEST()
BEGIN
DECLARE min_id INT;
DECLARE v_start_time,v_end_time DATETIME;
DECLARE v_count INT;
DECLARE done INT DEFAULT 0;
DECLARE my_cur CURSOR FOR SELECT start_time,end_time,min(id),count(1) AS count FROM leo.test GROUP BY start_time,end_time HAVING count>1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN my_cur;
  myloop: LOOP
  FETCH my_cur INTO v_start_time,v_end_time,min_id,v_count;
  IF done=1 THEN
  LEAVE myloop;
  END IF;
  DELETE FROM leo.test WHERE start_time=v_start_time AND end_time=v_end_time AND id>min_id;
  COMMIT;
  END LOOP myloop;
CLOSE my_cur;
END;
//
DELIMITER ;

DDL语言

DDL,Data Definition Language,数据定义语言。

注意:DDL中,必须加入被定义的对象

  • DDL 数据定义子语言 DBMS:数据定义
  1. CREATE [object] 创建
  2. ALTER [object] 修改
  3. DROP [object] 删除

[object]:

  1. DATABASE data:数据 base 基地 数据库
  2. TABLE* table:桌子, 表格
  3. VIEW view: 视图,用眼睛看数据
  4. INDEX index: 索引,目录(优化查询)

CREATE创建

创建数据库的对象。

CREATE DATABASE
CREATE TABLESPACE (Oracle 特有的结构,类似于货架)
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE (存储过程)
CREATE USER

ALTER修改

修改数据库的对象

ALTER TABLE
ALTER USER

DROP删除

删除数据库的对象,对象中的内容也一定一并删除。

  • 删除数据库:数据库中所有的东西(表、数据……)全部都没有了
DROP TABLE
DROP VIEW
DROP PROCEDURE
DROP USER

RENAME重命名

重命名数据库的对象

RENAME TABLE
RENAME COLUMN

 

  INSERT INTO tablename SET column_name1 = value1, column_name2 = value2,…;

逻辑很清晰,就是根据重复判断条件依次删掉重复组中主键大于最小主键的记录们。

DML语言

DML,Data Manipulation Language,数据操纵子语言。

DML的对象一般指的是表。DML不会对数据库对象(比如:表)的结构进行进行任何更改,只会对数据库对象(比如:表)的数据记录进行增加、更新、删除等操作。此外,DML的操作,需要事务提交才能真正完成。

  • DML 数据操纵子语言 DBMS:数据操纵
  1. SELECT 查询 【查】(有些时候,会出来一个 DQL,单独只查询)
  2. INSERT 增加,插入 【增】
  3. UPDATE 更新【改】
  4. DELETE 移除【删】
  • 对象:记录,数据

INSERT插入

在表中插入记录。增加记录。

UPDATE更新

更新表中的记录,可以更新精确到字段。

DELETE删除

删除表中的记录,对表结构没有任何影响。

(3)确保数据库的安全。使用存储过程可以完成所有数据库操作,并可通过编程方式控制上述操作对数据库信息访问的权限。  www.2cto.com  

  第一种方法将列名和列值分开了,在使用时,列名必须和列值的数一致。如下面的语句向users表中插入了一条记录:

但是在编写过程中却遇到一个很恶心的BUG,我最初的内容是这么写的:

DCL/TPL语言

DCL,Data Control Language,数据控制子语言。
TPL, Transaction Process Language,事务处理语言

DCL应用的场景,一般是授权、回收等权限操作
TPL一般是事务处理,包括事务的提交、回滚。

GRANT

授权,给用户授权。授权后的用户才可以操作数据库。

REVOKE

取消授权

COMMIT

提交,事务提交

ROLLBACK

回滚,事务回滚

 

  INSERT INTO users(id, name, age) VALUES(123, ‘姚明’, 25);

DELIMITER //
DROP PROCEDURE IF EXISTS Del_Dup_FOR_TEST;
CREATE PROCEDURE Del_Dup_FOR_TEST()
BEGIN
DECLARE min_id INT;
DECLARE start_time,end_time DATETIME;
DECLARE count INT;
DECLARE done INT DEFAULT 0;
DECLARE my_cur CURSOR FOR SELECT start_time,end_time,min(id),count(1) AS count FROM leo.test GROUP BY start_time,end_time HAVING count>1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN my_cur;
  myloop: LOOP
  FETCH my_cur INTO start_time,end_time,min_id,count;
  IF done=1 THEN
  LEAVE myloop;
  END IF;
  DELETE FROM leo.test WHERE start_time=start_time AND end_time=end_time AND id>min_id;
  COMMIT;
  END LOOP myloop;
CLOSE my_cur;
END;
//
DELIMITER ;

MySQL数据类型

MySQL的数据类型见如下表格

  • 数值型
整数类型 字节 范围(有符号) 范围(无符号) 用途
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) 单精度浮点数值,如:float(7,3)表示总长度7位,小数点3位
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) 双精度浮点数值 ,double(7,3)表示总长度7位,小数点3位
DECIMAL 依赖于M和D的值, 对DECIMAL(M,D) ,如果M>D,为M 2否则为D 2 依赖于M和D的值 DECIMAL(4, 1) 取值范围:-999.9 到 9999.9
  • 字符串型
字符串类型 字节大小 描述及存储需求
CHAR 0-255字节 定长字符串,如CHAR(10),定长10占位,不足补空格
VARCHAR 0-255字节 变长字符串 ,如VARCHAR(10),最长10个字节,存储长度按照实际输入长度为准
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65535字节 二进制形式的长文本数据
TEXT 0-65535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据
VARBINARY(M) M 允许长度0-M个字节的定长字节符串,值的长度 1个字节
BINARY(M) M 允许长度0-M个字节的定长字节符串
  • 日期时间型
类型 大小(字节) 范围 格式 用途
DATE 4 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 4 1970-01-01 00:00:00/2037 YYYYMMDD HHMMSS 混合日期和时间值,用于记录INSERT或UPDATE操作时记录日期和时间。 如果你不分配一个值,表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。 也可以通过分配一个NULL值,将TIMESTAMP列设置为当前的日期和时间。
  • 混合型
ENUM SET
ENUM 类型   ENUM 类型因为只允许在集合中取得一个值,有点类似于单选项。在处理相互排拆的数据时容易让人理解,比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值, 除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配,MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。 ENUM 类型在系统内部可以存储为数字,并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素,其中一个元素被 MySQL 保留,用来存储错误信息, 这个错误值用索引 0 或者一个空字符串表示。 MySQL 认为 ENUM 类型集合中出现的值是合法输入,除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。 SET 类型  SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录,MySQL 将会保留合法的元素,除去非法的元素。  一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列,这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。 并且它还去除了重复的元素,所以 SET 类型中不可能包含两个相同的元素。 希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

2.创建存储过程可以使用create procedure语句。

  第二种方法允许列名和列值成对出现和使用,如下面的语句将产生中样的效果。

不同的部分在于变量定义的名称,即:

表的基本操作

表的基本操作主要包括对表的增删查改,以及创建、修改和移除表结构。

 

  INSERT INTO users SET id = 123, name = ‘姚明’, age = 25;

FETCH INTO的变量名绝对不能是你定义CURSOR时SQL语句查出来的列名或者列别名,也就说你定义的变量名既不能是表中已经存在的列名,也不能是你定义游标时用过的别名(如本例中的count),只要一个条件不符合,FETCH INTO就把全部的变量赋NULL值,这点你可以尝试在FETCH INTO后加一句Select打印变量名验证。

命令行操作MySQL

  • 登录数据库:mysql-u root-p
  • 列出所有的数据库: show databases;
  • 选择某个数据库(test):use test;
  • 列出选定数据库的表:show tables;
  • 描述(desc, describe) 某个表结构: desc salesorder;
Setting environment for using XAMPP for Windows.
we@TEACHER-3 d:xampp
# cd mysqlbin

we@TEACHER-3 D:xamppmysqlbin
# mysql-u root-p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.5.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| cdcol              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
 -------------------- 
7 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
 ---------------- 
| Tables_in_test |
 ---------------- 
| salesorder     |
| user           |
 ---------------- 
2 rows in set (0.00 sec)

mysql> desc salesorder;
 --------- --------------- ------ ----- --------- ------- 
| Field   | Type          | Null | Key | Default | Extra |
 --------- --------------- ------ ----- --------- ------- 
| id      | char(10)      | YES  |     | NULL    |       |
| tx_date | datetime      | YES  |     | NULL    |       |
| amount  | decimal(9,2)  | YES  |     | NULL    |       |
| remark  | varchar(1000) | YES  |     | NULL    |       |
 --------- --------------- ------ ----- --------- ------- 
4 rows in set (0.00 sec)

mysql>

要在MySQL 5.1中创建存储过程,必须具有CREATE routine权限。要想查看数据库中有哪些存储过程,可以使用SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息,可使用SHOWCREATE PROCEDURE sp_name命令,其中sp_name是存储过程的名称。

  如果使用了SET方式,必须至少为一列赋值。如果某一个字段使用了省缺值(如默认或自增值),这两种方法都可以省略这些字段。如id字段上使用了自增值,上面两条语句可以写成如下形式:

在查询到这个BUG之前去官网页面特地看了一下是否是我的语法有错误: ,确信语法没问题,但倒数第二条评论显示可能是列名的隐藏BUG,最后一条评论反驳了BUG说法,但没有办法我还是根据BUG REPORT做了以上修改,然后功能就正常了。

SQLyog 创建表

  1. 打开SQLyog,并连接数据库

  2. 选择 test数据库:用鼠标左键点击 左侧列表的 test

  3. 右键 test | ,选择 创建表

  4. 填写具体的输入

    Snap17.jpg

  • 表名称(Table Name):英文(或者英文 数字),不需要空格
    • 数据库(Database):查看是不是 test
    • 引擎(Engine):选择 InnoDB
    • 字符集(character set):utf8
    • 核对(collection):utf8_general_ci

 

  INSERT INTO users (name, age) VALUES(‘姚明’,25);

关于此BUG的BUG报告页面详见MySQL BUG:#28227 和 BUG:#5967

表约束

约束,constraint,用于实施数据库中的某些数据完整性。当给某一列增加一个约束,MySQL自动确保不满足此约束的数据是绝对不能被接受的。如果用户试图写一个不满足约束的数据记录,那么MySQL就会对这个非法的SQL语句产生一个错误。

约束是表级的强制规定

约束放置在表中删除有关联关系的数据

约束可以再创建或者增加包含某列的表时,与该列进行关联,也可以在表创建以后通过SQL命令ALTER TABLE来实现与该列的关联。

主要的几种约束类型:

  • NOT NULL(非空)

    设定 NOT NULL 非空的列,必须有值,尽管值可以重复。

    场景:【名字】,姓名、性别、出生日期、课程名、客户名称、商品名称

任何列都可以设置为NOT NULL。如果在SQL操作中将一个NULL值赋给某个有NOT NULL约束的列,那么MySQL会为这个语句返回一个错误。

  • **唯一键 **(UNIQUE KEY)

    设定 UNIQUE, 唯一。该列不可以重复,可以不填(NULL),如果填了就不能重复

    场景:【可有可无的唯一标识】,手机号、QQ号,Email,银行账户账号,支付宝。。。

如果将某个列设置为唯一,那么就不能在表中插入和这个列中已有值重复的行,也不能修改已有的列值使之与其他列值重复。

  1. 字段数据允许为空
  2. 如果有数据那么必须保证每行都不相同,否则无法存储
  3. 系统也会为唯一键默认创建一个索引(高级,了解就行)
  4. 可以是一个或多个字段组成
  5. 一个表的唯一(唯一键)可以有多个
    1. 比如:第5列(手机号)本身唯一,第2列(股票代码) 第3列(交易时间)也唯一
    2. 如上:就有两个唯一键
  • 主键(Primary Key, 简称PK)

    PK = NOT NULL UNIQUE

    一个表的主键,只能有一个。

    这个主键可能是1列,也可能是多列一起组成。

    • 比如 身份证号 做主键
    • 比如 银行卡号 社保卡号 做主键

每个表最多可以有一个主键约束。主键约束可以由表中的多个列组成。

主键:相当于身份证号码,是唯一的,通过身份证号码只能找到一个人,即通过主键列只能找到一行数据,在创建表时,通常要有主键列

主键属于表对象,所以主键有一个名字,若没给主键指定名字,MySQL会自动分配一个唯一的名字,在一个表中只能有一个主键对象

1.主键一定是唯一的行标识,即每行的主键都不会重复
2.主键是不允许为空
3.系统会为主键默认的创建一个索引
4.主键可以是一个或多个字段
5.通常情况下,关系型数据库中每张表都会有主键

外键(Foreign Key,简称FK)

外键是约束,约束了该列的内容

外键对应了另外表的主键

外键的值,可以为空,如果不为空,就必须是对应主键所在表的主键列表的值

  • 股票基本信息表
股票代码1(PK, PRIMARY KEY) 股票名称 价格
000281 XXX1
000295 XXX3
100254 XXX2
  • 股票购买表
购买日期 股票代码2(FK, FOREIGN KEY) 数量
2017-3-10 000295 1000
2017-3-9 100254 2000
2017-3-8 100254 5000
  • 股票代码1在股票基本信息表是主键
  • 股票代码2在股票购买表是外键
  • 在 股票购买表 中,股票代码2的值,必须是 股票基本信息表 中 股票代码1的值。

外键约束是为数据库中某个与其他表(称作父表)有关系的表(称作子表)而定义的。外键的值必须事先出现在某个特定表的唯一键或者主键中。外键可包含一列或者多列,但是其所参考的键也必须包含相同的列。外键也可以和同一个表的主键相关联。如果没有其他约束限制,外键可以包含NULL值。

  1. 一张表的外键关联字段通常情况下关联的是另外一张表的主键
  2. 一张表的外键关联字段必须填写外键表中存在的数据
  3. 外间关联表的数据被引用了之后,通常不允许删除,如果一定要删除,可以级联删除引用数据
  4. 外键字段允许为空
  5. 外键字段可以是一个或多个

CREATE PROCEDURE的语法格式:

  INSERT INTO uses SET name = ‘姚明’, age = 25;

那么再回头看一下官网文档下的最后一条评论,开始我认为最后一条反驳BUG的评论完全是扯淡,是哪个傻X说这不是个BUG的?后来仔细想了想,他俩都对,这确实也算个BUG,傻X的也是我。

创建表

表(TABLE)是关系型数据库的核心,所有的数据都存储在表中。

 

  MySQL在VALUES上也做了些变化。如果VALUES中什么都不写,那MySQL将使用表中每一列的默认值来插入新记录。

贴一下页面下最后两条评论(截止2018.08.01):

创建表的前提

登录数据库的用户必须拥有

  • CREATE TABLE权限
  • 可用的存储空间

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

  INSERT INTO users () VALUES();

Posted by Brent Roady on May 9, 2012
It should be noted that the local variable names used in FETCH [cursor] INTO must be different than the variable names used in the SELECT statement 
defining the CURSOR. Otherwise the values will be NULL. 
In this example, 
DECLARE a VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT a FROM table1;
FETCH cur1 INTO a;
the value of a after the FETCH will be NULL.
This is also described here: http://bugs.mysql.com/bug.php?id=28227

Posted by Jérémi Lassausaie on February 3, 2015
Answer for Brent Roady :
I don't see any bug in the bahaviour described.
DECLARE a VARCHAR(255);
/* you declare a variable "a" without a specified default value, a=NULL */
DECLARE cur1 CURSOR FOR 
SELECT a FROM table1;
/* You declare a cursor that selects "a" FROM a table */
OPEN cur1;
/* You execute your cursor query, a warning is raised because a is ambiguously defined but you don't see it */
FETCH cur1 INTO a;
/* you put your unique field in your unique row into a (basically you do "SET a=a;") so a is still NULL */
There is no bug report, just a misunderstanding.

创建表的基本语法

CREATE TABLE [schema.]table
        (column datatype [DEFAULT expr][, ...]);

在创建表的时候,必须制定:

  • 表名
  • 字段名(又叫做列名)
  • column:栏位
  • field:字段,列
  • point:点,数据点
  • 字段类型
  • 字段长度等

 

  如果表名后什么都不写,就表示向表中所有的字段赋值。使用这种方式,不仅在VALUES中的值要和列数一致,而且顺序不能颠倒。 INSERT INTO users VALUES(123, ‘姚明’, 25);

Brent遇到的现象与我相同,并列出了BUG Report的链接。

表名和字段名(列名)

表名和列名:

  • 必须以字母开头(可以为中文,但是不推荐用)
  • 必须在 1–30 个字符之间
  • 必须只能包含 AZ, az, 09, _, $, 和 #
  • 必须不能和用户定义的其他对象重名
  • 必须不能是 MySQL 的保留字

   [characteristic ...] routine_body

  如果将INSERT语句写成如下形式MySQL将会报错。

Jeremi(猜测可能是个程序员)回答,这是一个显而易见的误解,当你声明了变量a(初始值为NULL),然后FETCH INTO a就相当于set a=a,在任何程序语言中这都是无解的。

DEFAULT选项

插入时为一个列指定默认值

... hire_date DATE DEFAULT '2017-03-08', ... 

具体要求:

  • 字符串, 表达式, 或SQL 函数都是合法的
  • 其它列的列名和伪列是非法的
  • 默认值必须满足列的数据类型定义

 

  INSERT INTO users VALUES(‘姚明’,25);

因此在编写存储过程中为定义的变量加个前缀标识是很好的习惯,想起以前Oracle写存储过程确实都加v_前缀,SQL Server 都用@前缀,现在轮到mysql却忽略了,确实需要牢记下。

表的确认与查看

SQL> DESC <表名>

或者

SQL> DESCRIBE <表名>

示例:

MariaDB [mysql]> desc db;
 ----------------------- --------------- ------  ----- --------- ------- 
| Field                 | Type          | Null | Key | Default | Extra |
 ----------------------- --------------- ------  ----- --------- ------- 
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(80)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
 ----------------------- --------------- ------  ----- --------- ------- 
22 rows in set (0.01 sec)

MariaDB [mysql]>

其中,proc_parameter的参数如下:

  2. 使用INSERT插入多条记录

创建表脚本

-- 推荐的写法
CREATE TABLE 名字(
  字段名 字段类型 是否为空 PRIMARY KEY,
  字段名 字段类型 是否为空 DEFAULT 默认值,
  字段名 字段类型 是否为空 UNIQUE,
  ....
  字段名1 字段类型  /* 外键 */,
  FOREIGN KEY(字段名1) REFERENCES 另一个表表名 (另一个表的主键字段名)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

如果想看最标准(MySQL 官方建议写法),可以使用 SQLyog 导出表的脚本。

 

   看到这个标题也许大家会问,这有什么好说的,调用多次INSERT语句不就可以插入多条记录了吗!但使用这种方法要增加服务器的负荷,因为,执行每一次 SQL服务器都要同样对SQL进行分析、优化等操作。幸好MySQL提供了另一种解决方案,就是使用一条INSERT语句来插入多条记录。这并不是标准的 SQL语法,因此只能在MySQL中使用。

创建表示例

CREATE TABLE person 
(
  id CHAR(18),
  name VARCHAR(100),
  sex CHAR(1),
  birthday DATETIME,
  height INT,
  weight INT,
  age INT,
  hometown CHAR(6)
);
-- Table created.

上面的SQL代码执行后便新建了一个数据库的表,表的名字是person,一共有七个字段。

接下来我们可以使用之前的DESC来查看所创建的表的结构。请注意务必在PLSQL Developer的命令窗口执行,或者在操作系统的命令行窗口执行。

SQL> desc person
Name     Type          Nullable Default Comments 
--------------------------------------------
ID       CHAR(18)      Y                         
NAME     VARCHAR(100) Y                         
SEX      CHAR(1)       Y                         
BIRTHDAY DATETIME          Y                         
HEIGHT   INT        Y               ()          
WEIGHT   INT        Y                         
AGE      INT        Y                         
hometown    CHAR(6)       Y  

[ IN | OUT | INOUT ] param_name type

  INSERT INTO users(name, age)
  VALUES(‘姚明’, 25), (‘比尔.盖茨’, 50), (‘火星人’, 600);

添加表的约束

还记的上一章我们曾经学习过的表的约束么?接下来我们创建表的时候,来添加表的约束,实现数据的完整性。

约束:就是限制

约束 描述
非空约束 值不能为空,一个列默认是可以为空
唯一约束 值不能重复,属于表对象(跟列一样),必须要有名字,若没有指定名字,则MySQL随即分配一个唯一的名字
主键 相当于身份证号码,包含非空约束和唯一约束,也是属于表对象,在设计一张表示,需要有主键列
校验约束(MySQL 失效) 检查值是否满足某个条件,属于表对象,必须要有名字
外键 也属于表对象,必须要有名字

首先创建一个表:hometown

CREATE TABLE hometown
(
  id CHAR(6) PRIMARY KEY, 
  city VARCHAR2(100) DEFAULT 'shenzhen' NOT NULL, 
  province VARCHAR2(100) DEFAULT 'guangdong' 
);
-- Table created.

可以看到上述表hometown使用了主键约束和非空约束。我们在接下来的修改表结构小节里面来继续讨论约束

 

  上面的INSERT 语句向users表中连续插入了3条记录。值得注意的是,上面的INSERT语句中的VALUES后必须每一条记录的值放到一对(…)中,中间使用”,”分割。假设有一个表table1

修改表结构

刚刚我们接连创建了两个表,是person和hometown后者在创建的时候我们考虑了约束并且添加了,但是前者在创建的是时候,我们没有增加约束,接下来我们通过修改表结构,使前者也添加约束。

修改表person的字段,使的表满足以下约束条件。

字段名 约束 详细描述
id 主键 定长18位字符 主键约束
name 非空 可变长度100位字符 姓名不允许为空
sex 检查约束和默认值 定长1位字符 检查输入是否为'M'或'F',默认值是'F' 修改列名为gender
birthday 非空和默认值 日期 生日不允许为空,默认值为系统时间
height 精度为999.9
weight 精度为999.9
hometown 外键 定长6位字符 参考hometown这个表的id字段
age 删除字段,已经与birthday重复了
phone 唯一 定长11位字符 增加字段,保证唯一约束

characteristic特征如下:

  CREATE TABLE table1(n INT);

修改字段

修改表person的字段,使的表满足以下约束条件。

修改字段的语法

-- 修改字段
ALTER TABLE table
MODIFY     (column datatype [DEFAULT expr]
           [, column datatype]...);

-- 添加约束
ALTER TABLE  table
  ADD [CONSTRAINT constraint] type (column);

具体代码如下:

-- id添加主键约束
ALTER TABLE person
ADD constraint person_id_pk primary key (ID);

-- 姓名不允许为空
ALTER TABLE person
ADD constraint person_name_nn check (name IS NOT NULL);
-- 或者这样
alter table PERSON modify name not null;

-- 性别检查约束
ALTER TABLE person
ADD constraint person_sex_chk check (sex = 'M' OR sex = 'F');

-- 性别默认值
ALTER TABLE person
MODIFY sex CHAR(1) DEFAULT 'F';

-- 性别字段名由sex修改为gender
ALTER TABLE person RENAME COLUMN sex TO gender;

-- 生日不允许为空
ALTER TABLE person
 ADD constraint person_birthday_nn check (birthday IS NOT NULL);

-- 生日默认值
ALTER TABLE person
MODIFY birthday DATE DEFAULT NOW();

-- 修改身高和体重的格式,使其满足999.9
ALTER TABLE person
MODIFY(
  height DECIMAL(3,1),
  weight DECIMAL(3,1)
);

接下来是外键约束

-- 添加外键,使得person的hometown字段参考hometown这个表的id字段
ALTER TABLE person
  ADD CONSTRAINT person_hometown_fk FOREIGN KEY (hometown)
  REFERENCES hometown (id);

 

  如果要向table1中插入5条记录,下面写法是错误的:

删除字段

ALTER TABLE table
DROP (column);

具体代码

-- 删除年龄字段
ALTER TABLE person
DROP (age);

-- 或者
ALTER TABLE person
DROP COLUMN age;

  language SQL

  INSERT INTO table1 (i) VALUES(1,2,3,4,5);

添加字段

ALTER TABLE table
ADD        (column datatype [DEFAULT expr]
           [, column datatype]...);

具体代码

-- 添加字段,手机。并设置为唯一约束
ALTER TABLE person
ADD (phone CHAR(11) UNIQUE);

至此,整个person的表就已经按照要求完全实现了。

整个表的一次性创建脚本如下

-- 完整的一次性创建表的脚本
CREATE TABLE person2
(
  ID CHAR(18) PRIMARY KEY,
  NAME VARCHAR(100) NOT NULL,
  SEX CHAR(1)  DEFAULT 'F' CHECK(sex='M' OR sex='F'),
  birthday DATETIME DEFAULT NOW() NOT NULL,
  height DECIMAL(4,1) ,
  weight DECIMAL(4,1),
  hometown CHAR(6) REFERENCES hometown(ID),
  phone CHAR(11) UNIQUE
);

 

  MySQL将会抛出下面的错误

插入记录

-- 插入记录到 Hometown
INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('518000', 'Shenzhen', 'Guangdong');

INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('100000', 'Beijing', 'Beijing');

INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('517000', 'Guangzhou', 'Guangdong');

INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('516000', 'Shanghai', 'Shanghai');

-- 插入表 Person 记录
INSERT INTO PERSON
  (ID, NAME, SEX, BIRTHDAY, HEIGHT, WEIGHT, HOMETOWN, PHONE)
VALUES
  ('123456789012345678',
   'Juliet',
   'F',
   TO_DATE('19900712', 'yyyymmdd'),
   170,
   62,
   '518000',
   '13866666666');

-- 插入表 Person 记录
INSERT INTO PERSON
  (ID, NAME, SEX, BIRTHDAY, HEIGHT, WEIGHT, HOMETOWN, PHONE)
VALUES
  ('123456789012345679',
   'Remeo',
   'M',
   TO_DATE('19920316', 'yyyymmdd'),
   162,
   45,
   '100000',
   '13866666669');

由上述脚本可以看出,由于Person.hometown字段是外键,参考了Hometown.id字段,那么在插入Person记录的时候,必须插入在Hometown.id中已经存在的记录。

另外,务必先插入Hometown记录,再插入Person记录。

 | [NOT] DETERMINISTIC

  ERROR 1136: Column count doesn’t match value count at row 1

移除表结构

DROP TABLE table;

具体代码

-- 删除表person
DROP TABLE person;
-- Table dropped.

 

  而正确的写法应该是这样:

表的查询

表的查询是整个数据库的基础,也是我们学习的重点

讲义中使用 HRDB的六个表

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

  INSERT INTO t able1(i) VALUES(1),(2),(3),(4),(5);

初始化数据库

  • 创建数据库

    CREATE DATABASE HRDB;
    USE HRDB;
    
  • 创建数据库的表

  • 区域表 Regions

    字段名 字段类型 注释说明
    region_id smallint(5) 无符号、主键、自增长
    region_name varchar(25) 可以为空
    DROP TABLE IF EXISTS regions;
    
    CREATE TABLE regions (
      region_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
      region_name varchar(25) DEFAULT NULL,
      PRIMARY KEY (region_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
  • 国家表 Countries

    字段名 字段类型 注释说明
    country_id char(2) 主键、非空
    country_name varchar(40)
    region_id smallint(5) 无符号、外键:参考 Regions(region_id)
    DROP TABLE IF EXISTS countries;
    
    CREATE TABLE countries (
      country_id char(2) NOT NULL DEFAULT '',
      country_name varchar(40) DEFAULT NULL,
      region_id smallint(5) unsigned DEFAULT NULL,
      PRIMARY KEY (country_id),
      FOREIGN KEY (region_id) REFERENCES regions (region_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
  • 位置表 Locations

    字段名 字段类型 注释说明
    location_id smallint(5) 主键
    street_address varchar(40) 街道
    postal_code varchar(12) 邮编
    city varchar(30) 城市
    state_province varchar(25) 省份
    country_id char(2) 外键,关联 Countries(country_id)
    DROP TABLE IF EXISTS locations;
    
    CREATE TABLE locations (
      location_id smallint(5) unsigned NOT NULL,
      street_address varchar(40) DEFAULT NULL,
      postal_code varchar(12) DEFAULT NULL,
      city varchar(30) NOT NULL,
      state_province varchar(25) DEFAULT NULL,
      country_id char(2) DEFAULT NULL,
      PRIMARY KEY (location_id),
      FOREIGN KEY (country_id) REFERENCES countries (country_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
  • 职位表 Jobs

    字段名 字段类型 注释说明
    job_id varchar(10) 主键
    job_title varchar(35) 职位名称
    min_salary int(11) 职位最低工资
    max_salary int(11) 职位最高工资
    DROP TABLE IF EXISTS jobs;
    
    CREATE TABLE jobs (
      job_id varchar(10) NOT NULL,
      job_title varchar(35) NOT NULL,
      min_salary int(11) DEFAULT NULL,
      max_salary int(11) DEFAULT NULL,
      PRIMARY KEY (job_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

  • 员工表 Employees

    字段名 字段类型 注释说明
    employee_id mediumint(8) 主键
    first_name varchar(20) 名字
    last_name varchar(25)
    email varchar(25)
    phone_number varchar(20)
    hire_date date 入职日期
    salary float(8,2) 工资
    commission_pct float(2,2) 销售提成比
    job_id varchar(10) 外键 jobs (job_id)
    manager_id mediumint(8) 外键 employees (employee_id)
    department_id smallint(5) 外键 departments (department_id)
    DROP TABLE IF EXISTS employees;
    
    CREATE TABLE employees (
      employee_id mediumint(8) unsigned NOT NULL,
      first_name varchar(20) DEFAULT NULL,
      last_name varchar(25) DEFAULT NULL,
      email varchar(25) DEFAULT NULL,
      phone_number varchar(20) DEFAULT NULL,
      hire_date date DEFAULT NULL,
      job_id varchar(10) DEFAULT NULL,
      salary float(8,2) DEFAULT '10000.00',
      commission_pct float(2,2) DEFAULT NULL,
      manager_id mediumint(8) unsigned DEFAULT NULL,
      department_id smallint(5) unsigned DEFAULT NULL,
      PRIMARY KEY (employee_id),
      UNIQUE KEY email (email),
      FOREIGN KEY (manager_id) REFERENCES employees (employee_id),
      FOREIGN KEY (department_id) REFERENCES departments (department_id),
      FOREIGN KEY (job_id) REFERENCES jobs (job_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

  • 部门表 Departments

    字段名 字段类型 注释说明
    department_id smallint(5) 主键
    department_name varchar(30) 部门名称,非空
    manager_id mediumint(8) 外键,参考 employees (employee_id)
    location_id smallint(5) 外键,参考 locations (location_id)
    DROP TABLE IF EXISTS departments;
    
    CREATE TABLE departments (
      department_id smallint(5) unsigned NOT NULL,
      department_name varchar(30) NOT NULL,
      manager_id mediumint(8) unsigned DEFAULT NULL,
      location_id smallint(5) unsigned DEFAULT NULL,
      PRIMARY KEY (department_id),
      FOREIGN KEY (manager_id) REFERENCES employees (employee_id),
      FOREIGN KEY (location_id) REFERENCES locations (location_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

 

  当然,这种写法也可以省略列名,这样每一对括号里的值的数目必须一致,而且这个数目必须和列数一致。如:

基本查询

查询的语法

SELECT  *|{[DISTINCT] column|expression [alias],...}
FROM    table;

SQL的查询有以下特点:

  • SQL 语言大小写不敏感。
  • SQL 可以写在一行或者多行
  • 关键字不能被缩写也不能分行
  • 各子句一般要分行写。
  • 使用缩进提高语句的可读性。
-- 简单基础查询
SELECT * FROM HRDB.EMPLOYEES;
SELECT * FROM HRDB.DEPARTMENTS D; /*表的别名 D*/
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL FROM SCOTT.EMP E;-- 表的别名E和查询指定字段
SELECT
    SG.GRADE AS jibie,-- 字段的别名
    SG.LOSAL AS "Low Salary",-- 字段的别名
    SG.HISAL "High Salary"-- 字段的别名
FROM
    SCOTT.SALGRADE SG; 
SELECT ROWNUM,t.*,ROWID FROM SCOTT.EMP t;
  • *代表所有列
  • HRDB是数据库名
  • BONUSDEPARTMENTSEMPLOYEESSALGRADE是表名,代表查询哪个表
  • DESG是表的别名
  • 对每个字段都可以进行重命名,可以使用AS,也可以不用AS。
  • PS:请注意,字段(列)的重命名,是在查询结果产生以后进行的。
  • SELECT后面跟的是字段(列)
  • FROM表名关注的是表,后面只能是表名或者是查询出来的子表
  • ROWNUM ROWID:伪列,系统自动生成的列,只能查询,不能进行增删改操作,ROWNUM表示行号、ROWID表示当前记录数据物理存放位置

 | SQL SECURITY { DEFINER | INVOKER }

  INSERT INTO t able1 VALUES(1),(2),(3),(4),(5);

条件查询

SELECT  *|{[DISTINCT] column|expression [alias],...}
FROM    table
[WHERE  condition(s)];
  • 条件查询是用WHERE语句进行过滤的,在执行完过滤条件后,查询的结果才会展现出来。
  • WHERE条件关注的是字段(列)
  • 使用WHERE子句,将不符合条件的记录过滤掉。

具体的例子

 

  3. REPLACE语句

基本条件查询

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HR.EMPLOYEES E
 WHERE E.EMPLOYEE_ID = 200
-- 查询员工编号为200的员工的 员工编号、姓名、入职日期和工作职位

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID = 200
    OR E.EMPLOYEE_ID = 201;
-- 查询员工编号为200或者201的员工
-- 还可以用:E.EMPLOYEE_ID >= 201   "<>  <= >= > <"

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID IN (200, 201, 202, 205, 208);
-- 查询员工编号为(200, 201, 202, 205, 208)范围内的员工

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID BETWEEN 200 AND 208;
-- 查询员工编号为200到208范围内的员工

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID > 200
   AND E.EMPLOYEE_ID <= 209;
-- 查询员工编号为大于200并且小于等于209范围内的员工

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID, E.Salary
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME = 'Jennifer'
-- 查询员工名字为“Jennifer”的员工

 | COMMENT 'string'

   我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一 个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用DELETE语句删除原先的记录,然后再使用 INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案,这就是REPLACE语句。使用REPLACE插入一条记录时,如果不重 复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。

NULL空值查询

NULL,读音/nʌl/,表示未知的,空值。

NULL是数据库中特有的数据类型,当一条记录的某个列为NULL,则表示这个列的值是未知的、是不确定的。既然是未知的,就有无数种的可能性。因此,NULL并不是一个确定的值。

这是NULL的由来、也是NULL的基础,所有和NULL相关的操作的结果都可以从NULL的概念推导出来。

判断一个字段是否为NULL,应该用IS NULLIS NOT NULL,而不能用=,对NULL的任何操作的结果还是NULL。

查询示例

-- 空值查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, E.MANAGER_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.MANAGER_ID IS NULL;
-- 查询所有的没有确定经理的员工;

-- 空值查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, E.MANAGER_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.MANAGER_ID IS NOT NULL;
-- 查询所有的有确定经理的员工;

 

  使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一,形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。

模糊条件查询

模糊条件查询一般是指字符类型模糊查询,使用LIKE

  • LIKE 后面一定用单引号 ''
  • 查询用到了通配符,选择相似值
  • %代表任意字符(零个或者一个或者多个字符)
  • _代表一个字符
  • 查询字符或者数字
  • %_可以同时使用
-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME Like 'J%'
-- 查询员工名字以“J”开头的员工

-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME Like '%on%'
-- 查询员工名字包含“on”的员工

-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME Like '_a%'
-- 查询员工名字以“任意一个字符 a”开头的员工

-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID LIKE '_2%'
-- 查询员工编号以“任意一个字符 2”开头的员工

-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID LIKE '__2'
-- 查询员工编号为“XX2”的员工

说明:

  在使用REPLACE时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则REPLACE就和INSERT完全一样的。

函数条件查询

 

   在执行REPLACE后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了 DELETE删除这条记录,然后再记录用INSERT来插入这条记录。如果返回的值大于2,那说明有多个唯一索引,有多条记录被删除和插入。

字符大小写转换函数
函数 描述 示例
UPPER 将字符串变成全部大写 UPPER('SmitH')='SMITH'
LOWER 将字符串变成全部小写 LOWER('SmitH')='smith'
CONCAT 连接函数 CONCAT('Smith','.', 'LEE')='Smith.LEE'
-- 函数查询
SELECT UPPER(E.FIRST_NAME) "Upper Name",
       LOWER(E.FIRST_NAME) "Lower Name",
       CONCAT(E.FIRST_NAME, '.', E.LAST_NAME) "Full Name"
  FROM HRDB.EMPLOYEES E;
-- 查询所有员工的名字和姓名,并直接相连。

查询示例

-- 函数条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE UPPER(E.First_Name) = 'PETER'; 
-- 查询员工名字大写为“PETER”的员工(peter, Peter, pEter...)

-- 函数条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE LOWER(E.FIRST_NAME) = 'peter';
-- 查询员工名字大写为“PETER”的员工(peter, Peter, pEter...)

●   sp_name:存储过程的名称,默认在当前数据库中创建。需要在特定数据库中创建存储过程时,则要在名称前面加上数据库的名称,格式为:db_name.sp_name。值得注意的是,这个名称应当尽量避免取与MySQL的内置函数相同的名称,否则会发生错误。

  REPLACE的语法和INSERT非常的相似,如下面的REPLACE语句是插入或更新一条记录。

字符控制函数
函数 描述 示例
CONCAT 连接两个字符成为一个新的字符 CONCAT('Hello','World')='HelloWorld'
SUBSTRING 截取字符串中指定的子字符串 SUBSTR('HelloWorld',1,5)='Hello'
LENGTH 获取字符串的长度 LENGTH('HelloWorld')=10
INSTR 查询字符串中指定字符的位置 INSTR('HelloWorld', 'Wor')=6
LPAD 输出指定位数的字符串,将侧全部补指定字符 LPAD(salary,10,'*')='*****24000'
RPAD 输出指定位数的字符串,将侧全部补指定字符 RPAD(salary, 10, '*')='24000*****'
TRIM 除去字符串中前后的空格, 也可以去除前后的某个字符 trim(' john ')='john'

查询示例

-- 函数查询
SELECT CONCAT(E.FIRST_NAME, E.LAST_NAME) "Full Name",
       E.JOB_ID,
       TRIM(E.first_name), 
       LENGTH(E.LAST_NAME),
       LPAD(E.SALARY, 11, '*'),
       RPAD(E.SALARY, 12, '$')
  FROM HRDB.EMPLOYEES E
 WHERE SUBSTRING(JOB_ID, 4) = 'REP';

 

  REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山’, 50);
 
  插入多条记录:

数字处理函数
函数 描述 示例
ROUND 四舍五入并保留指定小数位数 ROUND(45.926, 2)=45.93
TRUNCATE 直接截断舍去并保留指定小数位数 TRUNC(45.926, 2)=45.92
MOD 求余数 MOD(1600, 300)=100

函数示例

SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923,-1) , ROUND(45.923,-2)

SELECT  TRUNCATE(45.923,2), TRUNCATE(45.923,0), TRUNCATE(45.923,-1), TRUNCATE(45.923,-2)

SELECT LAST_NAME, SALARY, MOD(SALARY, 5000)
  FROM HRDB.EMPLOYEES WHERE JOB_ID = 'SA_REP';
-- 查询职位ID是“SA_REP”的员工的工资对5000取余

 ●   proc_parameter:存储过程的参数,param_name为参数名,type为参数的类型,当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存储过程支持三种类型的参数:输入参数、输出参数和输入/输出参数,关键字分别是IN、OUT和INOUT。输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候,存储过程使用输出参数。输入/输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数,但是名称后面的括号是不可省略的。

  REPLACE INTO users(id, name, age)
  VALUES(123, ‘赵本山’, 50), (134,’Mary’,15);

日期处理函数
函数 描述 示例
NOW() 当前日期 SELECT NOW();
CURTIME() 当前时间 select curtime();
WEEKDAY(date) 返回日期date是星期几(0=星期一,1=星期二,……6= 星期天) select WEEKDAY(NOW());
MONTHNAME(date) 返回日期的月份名 select MONTHNAME(NOW());
DATE_FORMAT(date,format) 输出指定格式的日期 select DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
DATE_ADD 日期增加函数 SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND);')
DATE_ADD 增加一天 SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 DAY);
DATE_ADD 后退一天10小时 SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR);

 

  REPLACE也可以使用SET语句

查询优先级

优先级 查询方式
1 算术运算符 /,*, ,-
2 连接符 ||
3 比较符 >,>=,=,<,<=
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT
7 AND
8 OR

可以用括号()来改变查询的优先级

SELECT LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE JOB_ID = 'SA_REP'
    OR JOB_ID = 'AD_PRES'
   AND SALARY > 15000;
-- 查询所有工作ID是“SA_REP”或者工作ID是“AD_PRES”并且工资高于15000

SELECT LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE (JOB_ID = 'SA_REP' OR JOB_ID = 'AD_PRES')
   AND SALARY > 15000;
-- 查询所有工作ID是“SA_REP”或“AD_PRES”并且工资高于15000

注意:参数的名字不要等于列的名字,否则虽然不会返回出错消息,但是存储过程中的SQL语句会将参数名看做列名,从而引发不可预知的结果。

  REPLACE INTO users SET id = 123, name = ‘赵本山’, age = 50;

排序

使用ORDER BY字句进行排序操作。

  • ASC是升序, ascend,如果省略ASC,一般数据库也是默认升序。
  • DESC是降序, descend
  • ORDER BY字句用在SELECT语句的结尾

排序语法

SELECT     *|{[DISTINCT] column|expression [alias],...}
FROM       table
[WHERE     condition(s)]
[ORDER BY  {column, expr, alias} [ASC|DESC]];

 

   上面曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一 个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。

默认排序

一般的关系型数据库中,默认排序是升序排序。就是在排序的ORDER BY子句中不添加ASC或者DESC

查询示例

-- 排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.DEPARTMENT_ID, E.HIRE_DATE
  FROM HRDB.EMPLOYEES E
 ORDER BY E.HIRE_DATE;
-- 默认排序,查询所有的员工,并按照入职时间从小到大升序排序
-- ORDER BY E.HIRE_DATE 等同于 ORDER BY E.HIRE_DATE ASC

-- 排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.DEPARTMENT_ID, E.HIRE_DATE
  FROM HRDB.EMPLOYEES E
 ORDER BY E.HIRE_DATE DESC;
-- 默认排序,查询所有的员工,并按照入职时间从大到小降序排序

characteristic:存储过程的某些特征设定,下面一一介绍:

  CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

字段别名排序

字段的别名排序,是将字段重新命名以后,按照新的名称进行排序。

重点理解:排序的动作,是在查询表已经呈现以后,并且若有字段重命名,那么排序也是在重命名之后。

-- 按照别名排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY * 12 ANNUAL_SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.SALARY > 5000
 ORDER BY ANNUAL_SALARY ASC;
-- 查询月薪高于5000的员工,并按照年薪从小到大升序排序
-- ANNUAL_SALARY 不在 HRDB.EMPLOYEES
-- 先筛选where
-- 检查 ANNUAL_SALARY 在不在 HRDB.EMPLOYEES
    -- 如果在, 就直接排序,然后筛选结果
    -- 如果不在,就先展示结果,看结果中有无 ANNUAL_SALARY, 如果有,就再排序

 

  假设table1中已经有了3条记录

多字段排序

多个字段的排序是支持的。排序的效果是按照字段的优先级进行,若当前字段无法分出高低,则依靠后续的字段进行排序确认。

-- 多个字段排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY
  FROM HRDB.EMPLOYEES E
 ORDER BY E.LAST_NAME ASC, E.JOB_ID DESC, E.SALARY DESC;
-- 查询员工的信息,按照员工名字升序,工作职位降序,工资降序依次进行排序

最后确认一点,排序是可以使用不在SELECT 列表中的列进行排序的。

-- 多个字段排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY
  FROM HRDB.EMPLOYEES E
 ORDER BY E.LAST_NAME ASC, E.JOB_ID DESC, E.SALARY DESC;
-- 查询员工的信息,按照员工名字升序,工作职位降序,工资降序依次进行排序
-- JOB_ID 不在 SELECT 后面

language sql:表明编写这个存储过程的语言为SQL语言,目前来讲,MySQL存储过程还不能用外部编程语言来编写,也就是说,这个选项可以不指定。将来将会对其扩展,最有可能第一个被支持的语言是PHP。  www.2cto.com  

  a b c
  1 1 1
  2 2 2
  3 3 3

排序总结

  • order by 字段/表达式 asc/desc

  • where vs order by

    1. 先执行 where,筛选出合适的数据
    2. 再执行 order by,按照指定的列进行排序
  • from vs order by

    1. 只要是排序的列在 from 后面的表中,就可以按该列排序
    2. 排序和 select 后面的列无关
  • select vs order by

    1. select 只能在 order by 出来结果后,选择显示指定的select 后面的列
    2. order by 后面的列不在 from 的表里面,那么必须在 select 的别名中。
  • order by 可以跟 多个列

    1. 先按前面的列进行排序,无法决定结果的时候,再考虑后面的列
    2. 每个列之间,用逗号","隔开
    3. 每个列后面,必须加上 asc/desc,不要省略
    4. 排序的依据还可以是列的表达式(算术)

deterministic:设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果,设置为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOTDETERMINISTIC。

  下面我们使用REPLACE语句向table1中插入一条记录。

分组函数

分组函数作用于一组数据,并对一组数据返回一个值。

查询语法

SELECT  [column,] group_function(column), ...
FROM        table
[WHERE  condition]
[GROUP BY   column]
[ORDER BY   column];
-- group_function()是分组函数方法
函数 描述 示例
AVG 求平均 AVG(SALARY)
COUNT 求数量 COUNT(SALARY), COUNT(*)
MAX 求最大值 MAX(SALARY)
MIN 求最小值 MIN(SALARY)
SUM 求和 AVG(SUM)

其中:COUNT(SALARY)是求SALARY不为NULL的记录数,COUNT(*)是求记录总数

分组函数忽略空值NULL

查询示例

-- 分组函数查询
SELECT AVG(SALARY), MAX(SALARY), MIN(SALARY), SUM(SALARY)
  FROM HRDB.EMPLOYEES
 WHERE JOB_ID LIKE '%REP%';
-- 可以对数值型数据记录使用 AVG 和 SUM 函数进行求平均值和汇总等操作
-- 可以对任意数据类型的数据使用 MIN 和 MAX 函数。

GROUP BY子句的使用

-- 分组查询
SELECT E.DEPARTMENT_ID, MIN(E.HIRE_DATE), MAX(E.HIRE_DATE)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.DEPARTMENT_ID;
-- 对日期类型进行 MAX 和 MIN 函数操作

-- 分组函数查询
SELECT E.JOB_ID, AVG(E.SALARY), MAX(E.SALARY), MIN(E.SALARY), SUM(E.SALARY)
  FROM HRDB.EMPLOYEES E
 WHERE E.JOB_ID LIKE '%REP%'
 GROUP BY E.JOB_ID;
-- 按照职位包含“REP”的员工的职位进行统计,统计每个组的平均工资、最高工资、最低工资和工资总和。

-- 分组函数查询
SELECT E.DEPARTMENT_ID, COUNT(*), COUNT(E.EMPLOYEE_ID), COUNT(E.MANAGER_ID)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.DEPARTMENT_ID
-- COUNT是计数函数。COUNT(*) 返回表中记录总数,COUNT(字段)返回的是当前组里面不是NULL的记录数

 

  REPLACE INTO table1(a, b, c) VALUES(1,2,3);

综合示例

--  请查询(工资高于8000)员工信息,EMPLOYEES
--  按照部门、职位输出:每个部门 职位,有几个员工
--  职位,部门,员工数,最低工资,最高工资,平均工资,工资总和
SELECT 
  e.department_id,
  e.job_id,
  COUNT(*) total_count,
  MIN(e.salary) min_salary,
  MAX(e.salary) max_salary,
  AVG(e.salary) avg_salary,
  SUM(e.salary) sum_salary 
FROM
  hrdb.employees e 
WHERE e.salary > 8000 
GROUP BY e.department_id,
  e.job_id 
ORDER BY avg_salary DESC ;

-- 1. 先筛选工资高于8000的 员工
-- 2. 对筛选出来的员工进行分组
-- 3. 同时按照 department_id 和 job_id 进行分组
-- 4. 分组后,对分组的结果进行统计
-- 5. 重命名分组的结果(列)
-- 6. 按照分组后的 avg_salary 列进行排序

查询结果:

department_id  job_id      total_count  min_salary  max_salary    avg_salary  sum_salary  
-------------  ----------  -----------  ----------  ----------  ------------  ------------
           90  AD_PRES               1    24000.00    24000.00  24000.000000      24000.00
           90  AD_VP                 2    17000.00    17000.00  17000.000000      34000.00
           20  MK_MAN                1    13000.00    13000.00  13000.000000      13000.00
           80  SA_MAN                5    10500.00    14000.00  12200.000000      61000.00
          110  AC_MGR                1    12000.00    12000.00  12000.000000      12000.00
          100  FI_MGR                1    12000.00    12000.00  12000.000000      12000.00
           30  PU_MAN                1    11000.00    11000.00  11000.000000      11000.00
           70  PR_REP                1    10000.00    10000.00  10000.000000      10000.00
           80  SA_REP               15     8400.00    11500.00   9660.000000     144900.00
           60  IT_PROG               1     9000.00     9000.00   9000.000000       9000.00
          100  FI_ACCOUNT            2     8200.00     9000.00   8600.000000      17200.00
          110  AC_ACCOUNT            1     8300.00     8300.00   8300.000000       8300.00
           50  ST_MAN                1     8200.00     8200.00   8200.000000       8200.00

contains SQL:表示存储过程不包含读或写数据的语句。NO SQL表示存储过程不包含SQL语句。reads SQL DATA表示存储过程包含读数据的语句,但不包含写数据的语句。modifies SQL DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定,默认的是CONTAINS SQL。

  返回的结果如下

分组总结

  • 分组的目的,作用:统计
  • 分组后,结果还是一个表,表里面有:
    1. 共同的列(分组依据)
    2. 统计的列
  • 每组记录的个数
    1. COUNT(*):* 代表一条记录
    2. COUNT(列): 列:代表不是null的列
  • 每组记录某些列(数值)的统计
    1. 用统计函数(聚合函数)
    2. 求和:SUM()
    3. 求平均:AVG()
    4. 求最大值: MAX()
    5. 求最小值: MIN()
      GROUP BY 列(多个列,用","分开,表达式)

 

  Query OK, 4 rows affected (0.00 sec)

DISTINCT关键字

DISTINCT是去除重复记录。

-- DISTINCT关键字
SELECT DISTINCT e.job_id
FROM hr.employees e;

DISTINCT后面如果加了多列,那么会剔除多列共同重复的记录

SELECT DISTINCT 
  d.location_id,
  d.manager_id 
FROM
  hrdb.departments d ;
-- 去除 location_id 和 manager_id 一起重复的记录

查询结果

location_id  manager_id  
-----------  ------------
       1700           200
       1800           201
       1700           114
       2400           203
       1500           121
       1400           103
       2700           204
       2500           145
       1700           100
       1700           108
       1700           205
       1700        (NULL)

DISTINCT关键字也可以使用在分组函数中。

COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数

查询示例

-- DISTINCT关键字
SELECT COUNT(DISTINCT E.SALARY), COUNT(E.SALARY)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.JOB_ID;
-- DISTINCT去除了每个组中重复的工资数目计数

SQL SECURITY:SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户(DEFINER)的许可来执行,还是使用调用者(INVOKER)的许可来执行。默认值是DEFINER。

  在table1中的记录如下

多个字段分组

与之前的ORDER BY子句一样,GROUP BY子句也是可以多个字段进行的。

-- 多字段分组
SELECT E.JOB_ID, E.DEPARTMENT_ID, COUNT(DISTINCT E.SALARY), COUNT(E.SALARY)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.JOB_ID, E.DEPARTMENT_ID;
-- 所有的数据将会以(JOB_ID   DEPARTMENT_ID)作为依据分组,统计每一个组的计数
-- 第四行中:E.JOB_ID 和 E.DEPARTMENT_ID 可以互换位置,不影响结果

 

  a b c
  1 2 3

过滤分组 HAVING

HAVING 是在 GROUP BY 之后进行过滤,对分组的结果进行过滤

使用 HAVING 过滤分组:

  1. 行已经被分组。
  2. 使用了组函数。
  3. 满足 HAVING 子句中条件的分组将被显示。
  4. 不满足 HAVING 子句中条件的分组,将不会显示

查询语法

SELECT  column, group_function
FROM        table
[WHERE  condition]
[GROUP BY   group_by_expression]
[HAVING group_condition]
[ORDER BY   column];

查询示例

-- 分组后进行过滤
 SELECT DEPARTMENT_ID, MAX(SALARY)
   FROM HRDB.EMPLOYEES
  GROUP BY DEPARTMENT_ID
 HAVING MAX(SALARY) > 10000;
-- 分组后,过滤最大工资。筛选最大工资高于10000的数据进行查询

与WHERE子句一起使用

-- 分组后进行过滤
SELECT JOB_ID, SUM(SALARY) PAYROLL
  FROM HRDB.EMPLOYEES
 WHERE JOB_ID NOT LIKE '%REP%'
 GROUP BY JOB_ID
HAVING SUM(SALARY) > 13000
 ORDER BY SUM(SALARY);
-- 先筛选出合适的记录进行分组统计,统计以后再次对分组统计好的数据进行筛选,然后排序

使用之前的例子进行查询

--  请查询(工资高于8000)员工信息,EMPLOYEES
--  按照部门、职位输出:(平均工资不低于10000)每个部门 职位,有几个员工
--  职位,部门,员工数,最低工资,最高工资,平均工资,工资总和
SELECT 
  e.department_id,
  e.job_id,
  COUNT(*) total_count,
  MIN(e.salary) min_salary,
  MAX(e.salary) max_salary,
  AVG(e.salary) avg_salary,
  SUM(e.salary) sum_salary 
FROM
  hrdb.employees e 
WHERE e.salary > 8000 
GROUP BY e.department_id,
  e.job_id 
HAVING avg_salary >= 10000 
ORDER BY avg_salary DESC ;

-- 1. 先筛选工资高于8000的 员工
-- 2. 对筛选出来的员工进行分组
-- 3. 同时按照 department_id 和 job_id 进行分组
-- 4. 分组后,对分组的结果进行统计
-- 5. 用 HAVING 对统计的结果进行过滤
-- 6. 重命名分组的结果(列)
-- 7. 按照分组后的 avg_salary 列进行排序

查询结果

department_id  job_id   total_count  min_salary  max_salary    avg_salary  sum_salary  
-------------  -------  -----------  ----------  ----------  ------------  ------------
           90  AD_PRES            1    24000.00    24000.00  24000.000000      24000.00
           90  AD_VP              2    17000.00    17000.00  17000.000000      34000.00
           20  MK_MAN             1    13000.00    13000.00  13000.000000      13000.00
           80  SA_MAN             5    10500.00    14000.00  12200.000000      61000.00
          110  AC_MGR             1    12000.00    12000.00  12000.000000      12000.00
          100  FI_MGR             1    12000.00    12000.00  12000.000000      12000.00
           30  PU_MAN             1    11000.00    11000.00  11000.000000      11000.00
           70  PR_REP             1    10000.00    10000.00  10000.000000      10000.00

COMMENT 'string':对存储过程的描述,string为描述内容。这个信息可以用SHOWCREATE PROCEDURE语句来显示。

  我们可以看到,REPLACE将原先的3条记录都删除了,然后将(1, 2, 3)插入。

子查询

  • 子查询作为新查询的数据表(常见)

查询语法

SELECT  select_list
FROM    (SELECT select_list
               FROM     table) s
WHERE   expr operator;

查询示例

SELECT 
  s.job_id,
  s.ct,
  s.ay 
FROM
  (
  SELECT 
    e.job_id,
    COUNT(*) ct,
    AVG(e.salary) ay 
  FROM
    hrdb.employees e 
  WHERE e.salary > 10000 
  GROUP BY e.job_id 
  HAVING COUNT(*) > 2
  ) s 
WHERE s.ay > 11000 ;
-- s表,是一个查询语句
-- s表有三个字段,job_id, ct, ay.
--  select xx from s where xxx

在当前的查询结果中,做进一步的查询

  • 子查询作为新查询的条件

查询语法

SELECT  select_list
FROM    table
WHERE   expr operator
            (SELECT select_list
               FROM     table);
  • 子查询要包含在括号内。

  • 将子查询放在比较条件的右侧。

  • 一般情况下不要在子查询中使用ORDER BY 子句。

  • 单行操作符对应单行子查询,多行操作符对应多行子查询。

    查询示例

查询示例

-- 子查询
SELECT LAST_NAME, SALARY
  FROM HRDB.EMPLOYEES
 WHERE SALARY > (SELECT SALARY FROM HRDB.EMPLOYEES WHERE LAST_NAME = 'Abel');
-- 查询工资高于“Abel”的所有员工的名字和工资
-- 这条语句有风险,如果雇员中有多个人的 LAST_NAME 是 Abel,这条语句执行可能失败
  • ALL:全部,一般指最大值

    SELECT 
      LAST_NAME, SALARY 
    FROM
      hrdb.employees e 
    WHERE e.salary > ALL 
      (SELECT 
        e.salary 
      FROM
        hrdb.employees e 
      WHERE e.last_name = 'Cambrault') ;
    -- 查询工资高于(全部Last_name是“Cambrault”雇员工资)的所有员工的名字和工资
    

  • ANY:任何一个,一般指最小值

    SELECT 
      LAST_NAME, SALARY 
    FROM
      hrdb.employees e 
    WHERE e.salary > ANY 
      (SELECT 
        e.salary 
      FROM
        hrdb.employees e 
      WHERE e.last_name = 'Cambrault') ;
    -- 查询工资高于(任意Last_name是“Cambrault”雇员工资)的所有员工的名字和工资
    
  • 精确子查询,需要用主键(PRIMARY KEY)

-- 子查询
 SELECT LAST_NAME, JOB_ID, SALARY
   FROM HRDB.EMPLOYEES
  WHERE JOB_ID = (SELECT JOB_ID FROM HRDB.EMPLOYEES WHERE EMPLOYEE_ID = 141)
    AND SALARY > (SELECT SALARY FROM HRDB.EMPLOYEES WHERE EMPLOYEE_ID = 143);
/*查询职位和员工编号141相同,并且工资高于员工编号143的所有员工的姓,工作编号和工资*/

 

二、UPDATE

HAVING子句子查询

-- HAVING子句子查询
 SELECT DEPARTMENT_ID, MIN(SALARY)
   FROM HRDB.EMPLOYEES
  GROUP BY DEPARTMENT_ID
 HAVING MIN(SALARY) > (SELECT MIN(SALARY)
                         FROM HRDB.EMPLOYEES
                        WHERE DEPARTMENT_ID = 50);
-- 统计以后再次对分组的数据进行筛选,筛选条件基于一个子查询的结果

●   routine_body:这是存储过程的主体部分,也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句,这个部分总是以begin开始,以end结束。当然,当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。

  UPDATE的功能是更新表中的数据。这的语法和INSERT的第二种用法相似。必须提供表名以及SET表达式,在后面可以加WHERE以限制更新的记录范围。

ANY & ALL 子查询

-- 子查询
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE SALARY < ANY
 (SELECT SALARY FROM HRDB.EMPLOYEES WHERE JOB_ID = 'IT_PROG')
   AND JOB_ID <> 'IT_PROG';
-- any,任何一个,查出非IT_PROG职位的员工中薪水少于IT_PROG任一员工薪水的信息,小于最高工资

-- 子查询
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE SALARY < ALL
 (SELECT SALARY FROM HRDB.EMPLOYEES WHERE JOB_ID = 'IT_PROG')
   AND JOB_ID <> 'IT_PROG';
-- all,全部,查出少于IT_PROG职位最低薪水的员工信息

 

  UPDATE table_anem SET column_name1 = value1, column_name2 = value2, …

EXISTS操作符

EXISTS操作符检查在子查询中是否存在满足条件的行

  • 如果在子查询中存在满足条件的行:
  • 不在子查询中继续查找
  • 条件返回TRUE
  • 如果在子查询中不存在满足条件的行:
  • 条件返回FALSE
  • 继续在子查询中查找
-- 子查询
select 'True' from dual WHERE EXISTS (SELECT '44'
                FROM HRDB.EMPLOYEES
               WHERE MANAGER_ID = 101);

-- 下面的思考一下原理
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, DEPARTMENT_ID
  FROM HRDB.EMPLOYEES  E
  WHERE EXISTS (SELECT NULL
                FROM HRDB.EMPLOYEES
               WHERE MANAGER_ID = E.EMPLOYEE_ID);
-- 找出员工表中职位是经理的员工,实际上只要子查询中有数据返回则Where条件成立
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, (select job_title from HRDB.jobs j where E.job_id=j.job_id) "job_title",DEPARTMENT_ID
  FROM HRDB.EMPLOYEES E 
  WHERE EXISTS (SELECT NULL
                FROM HRDB.EMPLOYEES
               WHERE MANAGER_ID = E.EMPLOYEE_ID);
-- 检验一下看是不是都是经理职位,这样改一改会更容易理解

-- 子查询
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE NOT EXISTS
 (SELECT 'X' FROM HRDB.EMPLOYEES WHERE DEPARTMENT_ID = D.DEPARTMENT_ID);
-- 找出部门表中没有员工的部门,实际上只要子查询中无数据返回则where条件成立
  1.   在开始创建存储过程之前,先介绍一个很实用的命令,即delimiter命令。在MySQL中,服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候,存储过程体中可能包含多个SQL语句,每个SQL语句都是以分号为结尾的,这时服务器处理程序的时候遇到第一个分号就会认为程序结束,这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。

  WHERE … ;

多表查询

 

  如下面的语句将users表中id等于123的记录的age改为24

笛卡尔积

笛卡尔积是集合的一种,假设A和B都是集合,A和B的笛卡尔积用A X B来表示,是所有有序偶(a,b)的集合,其中a属于A,b属于B。

SELECT COUNT(*) FROM HRDB.EMPLOYEES E;      -- 107行数据
SELECT COUNT(*) FROM HRDB.DEPARTMENTS D;    -- 27行数据
-- 笛卡儿积
SELECT * FROM HRDB.EMPLOYEES E, HR.DEPARTMENTS D;   -- 107 * 27 = 2889行数据
-- 记录数是两个表的记录数相乘
-- 所有表中的所有行互相连接

DELIMITER语法格式为:DELIMITER $$

  UPDATE users SET age = 24 WHERE id = 123;

内连接 INNER JOIN

连接语法

-- 两种语法:第一种比较古老
SELECT  table1.column, table2.column,...
FROM    table1, table2
WHERE   table1.column1 = table2.column2
AND     table.column1 = 'value';

SELECT       table1.column, table2.column,...
FROM         table1 
INNER JOIN   table2
ON           table1.column1 = table2.column2
WHERE        table1.column1 = 'value';
  • 使用连接在多个表中查询数据。

  • 在 WHERE 子句中写入连接条件。

  • 在表中有相同列时,在列名之前加上表名前缀。

    -- 多表连接查询
    SELECT 
        E.EMPLOYEE_ID,  
        CONCAT(E.FIRST_NAME, ' ', E.LAST_NAME) AS "employee_name",
        E.SALARY,
        E.DEPARTMENT_ID DEPARTMENT_ID1,
        D.DEPARTMENT_ID DEPARTMENT_ID2,
        D.DEPARTMENT_NAME
    FROM HRDB.EMPLOYEES E, HRDB.DEPARTMENTS D
    WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
    -- 查询每个员工的员工编号、姓名、工资、和部门编号、部门名称
    

  • 使用表名前缀在多个表中区分相同的列。

  • 使用表名可以提高效率。

  • 在不同表中具有相同列名的列可以用别名加以区分。

    -- 非等值内连接
    SELECT concat(E.FIRST_NAME, E.LAST_NAME), E.SALARY, J.JOB_TITLE
      FROM HRDB.EMPLOYEES E, HRDB.JOBS J
     WHERE E.JOB_ID = J.JOB_ID
       AND E.SALARY BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
    -- 查询工资符合职位工资区间的员工的姓名、工资和职位
    

查询语法2

SELECT       table1.column, table2.column,...
FROM         table1 
INNER JOIN   table2
ON           table1.column1 = table2.column2
WHERE        table1.column1 = 'value';

查询示例

-- 多表连接查询
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.' , E.LAST_NAME) AS "employee_name",
       E.SALARY,
       E.DEPARTMENT_ID DEPARTMENT_ID1,
       D.DEPARTMENT_ID DEPARTMENT_ID2,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 INNER JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查询每个员工的员工编号、姓名、工资、和部门编号、部门名称

-- 非等值内连接
SELECT concat(E.FIRST_NAME, '.', E.LAST_NAME), E.SALARY, J.JOB_TITLE
  FROM HRDB.EMPLOYEES E
INNER JOIN HRDB.JOBS J
  ON E.JOB_ID = J.JOB_ID
   AND E.SALARY BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
-- 查询工资符合职位工资区间的员工的姓名、工资和职位
  • 查询示例1

    -- 查询每个国家的编号、名字和所在的大洲(区域表)
    SELECT 
      c.country_id,
      c.country_name,
      r.region_name 
    FROM
      hrdb.countries c 
      INNER JOIN hrdb.regions r 
        ON c.region_id = r.region_id ;
    
  • 查询示例2

    -- 查询每个员工的员工编号、姓名、工资、和部门编号、部门名称
    SELECT 
      e.employee_id,
      e.first_name,
      e.salary,
      e.department_id,
      d.department_name 
    FROM
      hrdb.employees e 
      INNER JOIN hrdb.departments d 
        ON e.department_id = d.department_id ;
    --  从员工表(部门编号)查员工的部门名称(在部门表中)
    
  • 查询示例3

    -- 查询每个员工的员工编号、姓名、工资、和职位编号、职位名称  
    SELECT 
      e.employee_id,
      e.first_name,
      e.salary,
      e.job_id,
      j.job_title 
    FROM
      hrdb.employees e 
      INNER JOIN hrdb.jobs j 
        ON e.job_id = j.job_id ;
    

  • 查询示例4

    -- 查询每个部门的名字和部门经理的名字
    SELECT 
      d.department_name,
      m.first_name 
    FROM
      hrdb.departments d 
      INNER JOIN hrdb.employees m 
        ON d.manager_id = m.employee_id ;
    

  • 查询示例5

    -- 查询每个员工的名字和员工经理的名字
    SELECT 
      e.first_name employee_name,
      m.first_name manager_name
    FROM
      hrdb.employees e 
      INNER JOIN hrdb.employees m 
        ON e.manager_id = m.employee_id ;
    

    ##### 查询示例

    数据库查询示例.png

 

  同样,可以使用UPDATE更新多个字段的值 UPDATE users SET age = 24, name = ‘Mike’ WHERE id = 123;

外连接

外连接包括:左连接,右连接和全连接。

  • 内连接只返回满足连接条件的数据。
  • 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外联接。
  • 两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ,这种连接称为全 外联接。

说明:$$是用户定义的结束符,通常这个符号可以是一些特殊的符号,如两个“#”,一个“¥”、数字、字母等都可以。当使用DELIMITER命令时,应该避免使用反斜杠(“”)字符,因为那是MySQL的转义字符。

  上面的UPDATE语句通过WHERE指定一个条件,否则,UPDATE将更新表中的所有记录的值。

左连接

左连接是以左边的表为基础,左边的表符合过滤条件的都筛选出来,右边的表若能匹配的记录,直接匹配,否则补NULL。

查询语法

SELECT  table1.column, table2.column,...
FROM    table1
LEFT JOIN table2
ON  table1.column = table2.column;

查询示例

-- 外连接查询,左连接
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
  LEFT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查询所有员工的员工编号、姓名、部门编号、部门名称。确保列出所有的员工。
  • ORACLE支持老的语法

    -- ORACLE左连接
    SELECT    table1.column, table2.column,...
    FROM  table1, table2
    WHERE table1.column1 = table2.column2( )
    AND     table.column1 = 'value';
    

 

   在使用UPDATE更新记录时,如果被更新的字段的类型和所赋的值不匹配时,MySQL将这个值转换为相应类型的值。如果这个字段是数值类型,而且所赋 值超过了这个数据类型的最大范围,那么MySQL就将这个值转换为这个范围最大或最小值。如果字符串太长,MySQL就将多余的字符串截去。如果设置非空 字段为空,那么将这个字段设置为它们的默认值,数字的默认值是0,字符串的默认值是空串(不是null,是”")。    

右连接

查询语法

SELECT  table1.column, table2.column,...
FROM    table1
RIGHT JOIN table2
ON  table1.column = table2.column;

查询示例

-- 外连接查询,右连接
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 RIGHT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查询所有员工的员工编号、姓名、部门编号、部门名称。确保列出所有的部门。
  • ORACLE支持老的语法

    -- ORACLE左连接
    SELECT    table1.column, table2.column,...
    FROM  table1, table2
    WHERE table1.column1( ) = table2.column2
    AND     table.column1 = 'value';
    

例:创建存储过程,实现的功能是删除一个特定学生的信息。

#如果定义为整形,给定一个值是
 
  有两种情况UPDATE不会对影响表中的数据。

全连接

查询语法

SELECT  table1.column, table2.column,...
FROM    table1
FULL JOIN table2
ON  table1.column = table2.column;

MySQL 没有全连接的用法,需要用UNION把左右连接联合起来。

UNION 连接了两段完整的 SELECT 语句,会去掉重复记录

  • UNION 查询比 UNION ALL 慢

UNION ALL 连接了两段完整的 SELECT 语句,不会去掉重复记录

查询示例

-- 外连接查询,全连接
SELECT E.EMPLOYEE_ID,
       CONCAT(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 LEFT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
UNION
SELECT E.EMPLOYEE_ID,
       CONCAT(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 RIGHT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- oracle的写法:
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
  FULL JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查询所有员工的员工编号、姓名、部门编号、部门名称。确保列出所有的员工和部门。

 

  1. 当WHERE中的条件在表中没有记录和它匹配时。

多表查询总结

  • 内连接

    是否显示 左边表 右边表
    Y
    N
    N
  • 左连接

    是否显示 左边表 右边表
    Y
    Y 无(补NULL)
    N
  • 右连接

    是否显示 左边表 右边表
    Y
    N
    Y 无(补NULL)
  • 全连接

    是否显示 左边表 右边表
    Y
    Y 无(补NULL)
    Y 无(补NULL)

DELIMITER $$

  2. 当我们将同样的值赋给某个字段时,如将字段abc赋为’123′,而abc的原值就是’123′。

Top-N分析

Top-N 分析查询一个列中最大或最小的 n 个值:

  • 销售量最高的十种产品是什么?
  • 销售量最差的十种产品是什么?

最大和最小的值的集合是 Top-N 分析所关心的

查询最大的几个值的 Top-N 分析:

查询语法

SELECT [column_list] 
FROM   table
WHERE  column...
order by column
limit M, N; -- 从第M 1行开始,获取N行数据

查询示例

SELECT last_name,salary 
FROM HRDB.employees
ORDER BY salary DESC
limit 0,10;
-- 查询工资最高的十名员工

SELECT last_name,salary 
FROM HRDB.employees
ORDER BY salary DESC
limit 3,1;
-- 查询工资第3 1高的员工

 

  和INSERT、REPLACE一样,UPDATE也返回所更新的记录数。但这些记录数并不包括满足WHERE条件的,但却未被更新的记录。如下同的UPDATE语句就未更新任何记录。

Top-N分析 Oracle

Top-N 分析查询一个列中最大或最小的 n 个值:

  • 销售量最高的十种产品是什么?
  • 销售量最差的十种产品是什么?

最大和最小的值的集合是 Top-N 分析所关心的

查询最大的几个值的 Top-N 分析:

查询语法

SELECT [column_list], ROWNUM  
FROM   (SELECT [column_list] 
        FROM table
        ORDER  BY Top-N_column)
WHERE  ROWNUM <=  N;

查询示例

SELECT ROWNUM as RANK, last_name, salary 
FROM  (SELECT last_name,salary FROM HR.employees
       ORDER BY salary DESC)
WHERE ROWNUM <= 3;
--查询工资最高的三名员工

ROWNUM的使用必须遵循以下特性:

  • ROWNUM务必从第一个开始,若没有第一个,就没有后续的
  • ROWNUM务必连续使用。若中间断开,则段开以后的部分将不会被选择
  • ROWNUM将于查询记录形成的同时一起产生。ROWNUM的产生在ORDER BY子句以前。
SELECT RANK, LAST_NAME, SALARY
  FROM (SELECT ROWNUM AS RANK, LAST_NAME, SALARY
          FROM (SELECT LAST_NAME, SALARY
                  FROM HR.EMPLOYEES
                 ORDER BY SALARY DESC))
 WHERE RANK = 3;
--查询工资第三高的员工

CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6))

  UPDATE users SET age = 30 WHERE id = 12;
  Query OK, 0 rows affected (0.00 sec)

分组统计及Top分析综合示例:

​ 示例一:某学校系统中的“学分表Records”包含:学号SID、课程CID、分数SCORE三个字段,请查询出总分排名前三的学生的学号及总分

 

  需要注意的时,如果一个字段的类型是TIMESTAMP,那么这个字段在其它字段更新时自动更新。

创建表及插入数据

>mysql-u root-p
mysql>source XXX.sql

分析:

1、由于需要按照学号统计总分,因此首先要按照学号分组对分数求和,并按照分数倒序排列,如下:

SELECT r.SID, SUM(r.Score) as SS  FROM HRDB.Records r  GROUP BY r.SID

2、从上面的查询结果中做二次查询,以找到排名前三的学员,最终SQL如下:

SELECT r.SID, SUM(r.Score) as SS  
FROM HRDB.Records r  
GROUP BY r.SID 
order by SS desc
limit 0,3

示例二:同上表,请查询出每门课程得到最高分的学生的学号和课程号、分数

分析:1、由于要按照课程来统计出最高分,因此可以先用分组查询语句查询出每门课程的最高分

SELECT r.CID, MAX(r.Score) MS  FROM Records r  GROUP BY r.CID

2、上述的查询结果可以作为一张临时表

3、上述临时表再关联Records表,从Records表中查询出和最高分数相等的学生的相关信息

-- 方法1,使用inner join
SELECT r.SID, r.CID, r.Score 
FROM Records r
INNER JOIN 
(SELECT r.CID, MAX(r.Score) MS
  FROM HRDB.Records r
  GROUP BY r.CID
) s
ON r.CID = s.CID
AND r.Score = s.MS;

-- 方法2 直接用where联表
select  r.SID, r.CID, r.Score 
FROM HRDB.Records r,
(SELECT r.CID, MAX(r.Score) MS
  FROM HRDB.Records r
  GROUP BY r.CID
) s
where r.CID = s.CID and r.Score = s.MS

BEGIN

  在有些时候我们需要得到UPDATE所选择的行数,而不是被更新的行数。我们可以通过一些API来达到这个目的。如MySQL提供的C API提供了一个选项可以得到你想要的记录数。而MySQL的JDBC驱动得到的默认记录数也是匹配的记录数。

实用查询示例

接下来的部分是一些查询练习

-- 同姓的员工计数
SELECT LAST_NAME, COUNT(LAST_NAME)
  FROM HRDB.EMPLOYEES
 GROUP BY LAST_NAME
HAVING COUNT(LAST_NAME) > 1;

-- 同名的员工计数
SELECT FIRST_NAME, COUNT(FIRST_NAME)
  FROM HRDB.EMPLOYEES
 GROUP BY FIRST_NAME
HAVING COUNT(FIRST_NAME) > 1;

-- 列出至少有一个雇员的所有部门名称
-- 方法一
 SELECT D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID IN
       (SELECT distinct E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E)
 ORDER BY DEPARTMENT_NAME ASC;
-- 方法二
SELECT D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID IN
       (SELECT E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E
         GROUP BY E.DEPARTMENT_ID
        HAVING COUNT(DEPARTMENT_ID) > 0)
 ORDER BY DEPARTMENT_NAME ASC;

-- 列出没有任何雇员的部门名称
-- 方法一
 SELECT D.DEPARTMENT_ID,D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID NOT IN
       (SELECT distinct E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E WHERE E.DEPARTMENT_ID IS NOT NULL)
 ORDER BY DEPARTMENT_ID ASC;
-- 方法二
SELECT D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID NOT IN
       (SELECT E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E
          WHERE E.DEPARTMENT_ID IS NOT NULL
         GROUP BY E.DEPARTMENT_ID
        HAVING COUNT(DEPARTMENT_ID) > 0)
 ORDER BY DEPARTMENT_NAME ASC;

-- 列出工资大于5000的所有雇员的员工编号,姓名和 其经理的姓名
-- 方法一
SELECT  E.EMPLOYEE_ID,E.FIRST_NAME,E.LAST_NAME,E.SALARY,      
        M.FIRST_NAME,M.LAST_NAME
FROM HRDB.EMPLOYEES E
LEFT JOIN HRDB.EMPLOYEES M
    ON E.MANAGER_ID = M.EMPLOYEE_ID
 WHERE E.SALARY > 5000
 ORDER BY E.SALARY DESC;

-- 方法二
SELECT E1.EMPLOYEE_ID, E1.FIRST_NAME,
       E1.LAST_NAME, E1.SALARY,
       M.FIRST_NAME, M.LAST_NAME
  FROM (SELECT E.EMPLOYEE_ID,
               E.FIRST_NAME,
               E.LAST_NAME,
               E.SALARY,
               E.MANAGER_ID
          FROM HRDB.EMPLOYEES E
         WHERE E.SALARY > 5000) E1
  LEFT JOIN HRDB.EMPLOYEES M
    ON E1.MANAGER_ID = M.EMPLOYEE_ID
 ORDER BY E1.SALARY DESC;

-- 列出所有雇员的职位名称,要求职位名称不重复(列出所有雇员职位名称的种类)
-- 方法一
 SELECT DISTINCT J.JOB_TITLE
 FROM HRDB.EMPLOYEES E, HRDB.JOBS J
 WHERE E.JOB_ID = J.JOB_ID
-- 方法二
SELECT DISTINCT J.JOB_TITLE
  FROM HRDB.EMPLOYEES E
 INNER JOIN HRDB.JOBS J
    ON J.JOB_ID = E.JOB_ID;

-- 列出平均工资最高的部门的经理的所有信息
SELECT ROWNUM, S.*, D.*, E2.*
  FROM (SELECT E.DEPARTMENT_ID, ROUND(AVG(E.SALARY), 2)
          FROM HRDB.EMPLOYEES E
         GROUP BY E.DEPARTMENT_ID
         ORDER BY AVG(E.SALARY) DESC) S
 INNER JOIN HRDB.DEPARTMENTS D
    ON D.DEPARTMENT_ID = S.DEPARTMENT_ID
 INNER JOIN HRDB.EMPLOYEES E2
    ON E2.EMPLOYEE_ID = D.MANAGER_ID
 WHERE ROWNUM = 1

 

  UPDATE和REPLACE基本类似,但是它们之间有两点不同。

DELETE FROM XS WHERE 学号=XH;

  1. UPDATE在没有匹配记录时什么都不做,而REPLACE在有重复记录时更新,在没有重复记录时插入。

 

  2. UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除,再插入新的记录。也就是说,将所有的字段都更新了。

END $$

 

DELIMITER ;

 

说明:当调用这个存储过程时,MySQL根据提供的参数XH的值,删除对应在XS表中的数据。

 

在关键字BEGIN和END之间指定了存储过程体,当然,BEGIN-END复合语句还可以嵌套使用。

 

  1.  局部变量

 

在存储过程中可以声明局部变量,它们可以用来存储临时结果。要声明局部变量必须使用declare语句。在声明局部变量的同时也可以对其赋一个初始值。

 

DECLARE语法格式:DECLARE var_name[,...] type [DEFAULT value]

 

说明:var_name为变量名;type为变量类型;default子句给变量指定一个默认值,如果不指定默认为NULL的话。可以同时声明多个类型相同的局部变量,中间用逗号隔开。

 

例: 声明一个整型变量和两个字符变量。

 

DECLARE num INT(4);

 

DECLARE str1, str2 VARCHAR(6);

 

declare n char(10) default ‘abcdefg’;

 

说明:局部变量只能在BEGIN…END语句块中声明。

 

局部变量必须在存储过程的开头就声明,声明完后,可以在声明它的BEGIN…END语句块中使用该变量,其他语句块中不可以使用它。  www.2cto.com  

 

在存储过程中也可以声明用户变量,不过千万不要将这两个混淆。局部变量和用户变量的区别在于:局部变量前面没有使用@符号,局部变量在其所在的BEGIN…END语句块处理完后就消失了,而用户变量存在于整个会话当中。

 

  1.  使用SET语句赋值

 

要给局部变量赋值可以使用SET语句,SET语句也是SQL本身的一部分。语法格式为:SET  var_name = expr [,var_name = expr] ...

 

例: 在存储过程中给局部变量赋值。

 

SET num=1, str1= 'hello';

 

说明:与声明用户变量时不同,这里的变量名前面没有@符号。注意,例中的这条语句无法单独执行,只能在存储过程和存储函数中使用。

 

  1. SELECT...INTO语句(重点)

 

使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此,返回的结果只能有一行。语法格式为:

 

SELECT col_name[,...] INTO var_name[,...]  table_expr

 

说明:col_name是列名,var_name是要赋值的变量名。table_expr是SELECT语句中的FROM子句及后面的部分,这里不再叙述。

 

例: 在存储过程体中将XS表中的学号为081101的学生姓名和专业名的值分别赋给变量name和project。

 

SELECT 姓名,专业名 INTO name, project

 

   FROMXS;  WHERE 学号= '081101';

 

  1.  流程控制语句

 

在MySQL中,常见的过程式SQL语句可以用在一个存储过程体中。例如:IF语句、CASE语句、LOOP语句、WHILE语句、iterate语句和LEAVE语句。

 

(1)IF语句

 

IF-THEN-ELSE语句可根据不同的条件执行不同的操作。

 

语法格式为:

 

IF 判断的条件THEN 一个或多个SQL语句

 

[ELSEIF判断的条件THEN一个或多个SQL语句] ...

 

[ELSE一个或多个SQL语句]

 

END IF

 

说明:当判断条件为真时,就执行相应的SQL语句。

 

IF语句不同于系统的内置函数IF()函数,IF()函数只能判断两种情况,所以请不要混淆。

 

例: 创建XSCJ数据库的存储过程,判断两个输入的参数哪一个更大。

 

DELIMITER $$  www.2cto.com  

 

CREATE PROCEDURE XSCJ.COMPAR

 

(IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )

 

BEGIN

 

IFK1>K2 THEN

 

    SET K3= '大于';

 

ELSEIFK1=K2 THEN

 

    SET K3= '等于';

 

ELSE

 

    SET K3= '小于';

 

ENDIF;

 

END$$

 

DELIMITER ;

 

说明:存储过程中K1和K2是输入参数,K3是输出参数。

 

(2)CASE语句

 

前面已经介绍过了,这里介绍CASE语句在存储过程中的用法,与之前略有不同。语法格式为:

 

CASE case_value

 

   WHEN when_value THEN statement_list

 

   [WHEN when_value THEN statement_list] ...

 

   [ELSE statement_list]

 

END CASE

 

或者:

 

CASE

 

   WHEN search_condition THEN statement_list

 

   [WHEN search_condition THEN statement_list] ...

 

   [ELSE statement_list]  www.2cto.com  

 

END CASE

 

说明:一个CASE语句经常可以充当一个IF-THEN-ELSE语句。

 

第一种格式中case_value是要被判断的值或表达式,接下来是一系列的WHEN-THEN块,每一块的when_value参数指定要与case_value比较的值,如果为真,就执行statement_list中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END CASE结束。

 

第二种格式中CASE关键字后面没有参数,在WHEN-THEN块中,search_condition指定了一个比较表达式,表达式为真时执行THEN后面的语句。与第一种格式相比,这种格式能够实现更为复杂的条件判断,使用起来更方便。

 

例: 创建一个存储过程,针对参数的不同,返回不同的结果。

 

DELIMITER $$

 

CREATE PROCEDURE XSCJ.RESULT

 

(IN str VARCHAR(4), OUT sex VARCHAR(4) )

 

BEGIN

 

 CASE str

 

   WHEN'M' THEN SET sex='男';

 

   WHEN'F' THEN SET sex='女';

 

   ELSE  SET sex='无';

 

   ENDCASE;

 

END$$

 

DELIMITER ;

 

例: 用第二种格式的CASE语句创建以上存储过程。程序片段如下:

 

CASE

 

   WHENstr='M' THEN SET sex='男';

 

   WHENstr='F' THEN SET sex='女';

 

   ELSE  SET sex='无';

 

END CASE;

 

(3)循环语句

 

MySQL支持3条用来创建循环的语句:while、repeat和loop语句。在存储过程中可以定义0个、1个或多个循环语句。

 

●   WHILE语句语法格式为:

 

[begin_label:] WHILE search_condition  DO

 

statement_list  www.2cto.com  

 

END WHILE [end_label]

 

说明:语句首先判断search_condition是否为真,不为真则执行statement_list中的语句,然后再次进行判断,为真则继续循环,不为真则结束循环。begin_label和end_label是WHILE语句的标注。除非begin_label存在,否则end_label不能被给出,并且如果两者都出现,它们的名字必须是相同的。

 

例: 创建一个带WHILE循环的存储过程。

 

DELIMITER $$

 

CREATE PROCEDURE dowhile()

 

BEGIN

 

   DECLARE v1 INT DEFAULT5;

 

   WHILE  v1 > 0 DO

 

         SET v1 = v1-1;

 

   END WHILE;

 

END $$

 

DELIMITER ;

 

●   repeat语句格式如下:

 

[begin_label:] REPEAT

 

     statement_list

 

UNTIL search_condition

 

END REPEAT [end_label]

 

说明:REPEAT语句首先执行statement_list中的语句,然后判断search_condition是否为真,为真则停止循环,不为真则继续循环。REPEAT也可以被标注。

 

例: 用REPEAT语句创建一个如例7.9的存储过程。程序片段如下:

 

REPEAT

 

    v1=v1-1;

 

    UNTIL v1<1;

 

END REPEAT;

 

说明:REPEAT语句和WHILE语句的区别在于:REPEAT语句先执行语句,后进行判断;而WHILE语句是先判断,条件为真时才执行语句。

 

●   LOOP语句语法格式如下:

 

[begin_label:] LOOP

  www.2cto.com  

         statement_list

 

END LOOP [end_label]

 

说明:LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造,statement_list是需要重复执行的语句。在循环内的语句一直重复至循环被退出,退出时通常伴随着一个LEAVE 语句。

 

LEAVE语句经常和BEGIN...END或循环一起使用。结构如下:

 

LEAVE label ; label是语句中标注的名字,这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

 

例: 创建一个带LOOP语句的存储过程。

 

DELIMITER $$

 

CREATE PROCEDURE doloop()

 

BEGIN

 

    SET @a=10;

 

    Label: LOOP

 

          SET @[email protected];

 

          IF @a<0 THEN

 

              LEAVELabel;

 

          END IF;

 

    END LOOPLabel;

 

END$$

 

DELIMITER ;

 

循环语句中还有一个iterate语句,它只可以出现在LOOP、REPEAT和WHILE语句内,意为“再次循环”。它的格式为:ITERATE label

 

说明:该语句格式与LEAVE差不多,区别在于:LEAVE语句是离开一个循环,而ITERATE语句是重新开始一个循环。

 

8.我们调用此存储过程来查看最后结果。调用该存储过程使用如下命令:CALL doloop();

 

接着,查看用户变量的值:  [email protected];

 

   语法格式:CALL sp_name([parameter[,...]])

 

说明:sp_name为存储过程的名称,如果要调用某个特定数据库的存储过程,则需要在前面加上该数据库的名称。parameter为调用该存储过程使用的参数,这条语句中的参数个数必须总是等于存储过程的参数个数。  www.2cto.com  

 

例:创建一个存储过程,有两个输入参数:XH和KCM,要求当某学生某门课程的成绩小于60分时将其学分修改为零,大于等于60分时将学分修改为此课程的学分。

 

DELIMITER $$

 

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

 

BEGIN

 

   DECLARE  KCH CHAR(3);

 

   DECLARE  XF TINYINT;

 

   DECLARE  CJ TINYINT;

 

 SELECT课程号, 学分 INTO KCH, XFFROM 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 ;

 

  1.        存储过程创建后需要删除时使用DROP PROCEDURE语句。

 

在此之前,必须确认该存储过程没有任何依赖关系,否则会导致其他与之关联的存储过程无法运行。

 

语法格式为:  DROPPROCEDURE  [IF EXISTS] sp_name

 

说明:sp_name是要删除的存储过程的名称。IF EXISTS子句是MySQL的扩展,如果程序或函数不存在,它防止发生错误。

 

澳门新浦京娱乐场网站:MySQL游标循环取出空值的BUG,SQL语言查询。例: 删除存储过程dowhile:DROP PROCEDURE IF EXISTS dowhile;

 

  1.  使用ALTER PROCEDURE语句可以修改存储过程的某些特征。

 

语法格式为:ALTER PROCEDURE sp_name [characteristic ...]

 

其中,characteristic为:

  www.2cto.com  

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

 

| SQL SECURITY { DEFINER | INVOKER }

 

| COMMENT 'string'

 

说明:characteristic是存储过程创建时的特征,在CREATE PROCEDURE语句中已经介绍过。只要设定了其中的值,存储过程的特征就随之变化。

 

如果要修改存储过程的内容,可以使用先删除再重新定义存储过程的方法。

 

例: 使用先删除后修改的方法修改例7.12中的存储过程。

 

DELIMITER $$

 

DROP PROCEDURE IF EXISTS DO_QUERY;

 

CREATE PROCEDURE DO_QUERY()

 

BEGIN

 

SELECT * FROM XS;

 

END$$

 

DELIMITER ;

 

  ***11  往后为选看内容。。非重点!!

 

  1.  SQL语句中的错误提示

 

在存储过程中处理SQL语句可能导致一条错误消息。例如,向一个表中插入新的行而主键值已经存在,这条INSERT语句会导致一个出错消息,并且MySQL立即停止对存储过程的处理。每一个错误消息都有一个唯一代码和一个SQLSTATE代码。例如,SQLSTATE 23000属于如下的出错代码:

 

Error 1022, "Can't write;duplicate(重复) key intable"

 

Error 1048, "Column cannot benull"

 

Error 1052, "Column is ambiguous(歧义)"

 

Error 1062, "Duplicate entry forkey"

 

MySQL手册的“错误消息和代码”一章中列出了所有的出错消息及它们各自的代码。

 

为了防止MySQL在一条错误消息产生时就停止处理,需要使用到DECLAREhandler语句。该语句语句为错误代码声明了一个所谓的处理程序,它指明:对一条SQL语句的处理如果导致一条错误消息,将会发生什么。

 

DECLARE HANDLER语法格式为:

 

DECLARE handler_type HANDLER FOR condition_value[,...]sp_statement

 

其中,handler_type为:

 

 Continue

 

| EXIT

 

| UNDO

 

condition_value为:

 

 SQLstate [VALUE] sqlstate_value

  www.2cto.com  

| condition_name

 

| SQLwarning

 

| NOT FOUND

 

| SQLexception

 

| mysql_error_code

澳门新浦京娱乐场网站:MySQL游标循环取出空值的BUG,SQL语言查询。 

说明:

 

●   handler_type:处理程序的类型,主要有三种:CONTINUE、EXIT和UNDO。对CONTINUE处理程序,MySQL不中断存储过程的处理。对于EXIT处理程序,当前   BEGIN...END复合语句的执行被终止。UNDO处理程序类型语句暂时还不被支持。

 

●  condition_value:给出SQLSTATE的代码表示。

 

   condition_name是处理条件的名称,接下来会讲到。

 

   SQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOT FOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。当用户不想为每个可能的出错消息都定义一个处理程序时可以使用以上三种形式。

 

mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值,MySQL错误代码也被支持,表示的形式为:ERROR= 'xxxx'。

 

●   sp_statement:处理程序激活时将要执行的动作。

 

例: 创建一个存储过程,向XS表插入一行数据('081101', '王民', '计算机', 1, '1990-02-10',50 , NULL, NULL),已知学号081101在XS表中已存在。如果出现错误,程序继续进行。

 

USE XSCJ;

 

DELIMITER $$

 

CREATE PROCEDURE MY_INSERT ()

 

BEGIN

 

   DECLARECONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;

 

   [email protected]=2;

 

   INSERTINTO XS VALUES('081101', '王民', '计算机', 1, '1990-02-10', 50 , NULL, NULL);

 

   [email protected]=3;  www.2cto.com  

 

END$$

 

DELIMITER ;

 

说明:在调用存储过程后,未遇到错误消息时处理程序未被激活,当执行INSERT语句出现出错消息时,MySQL检查是否为这个错误代码定义了处理程序。如果有,则激活该处理程序,本例中,INSERT语句导致的错误消息刚好是SQLSTATE代码中的一条。接下来执行处理程序的附加语句(SET @x2=1)。此后,MySQL检查处理程序的类型,这里的类型为CONTINUE,因此存储过程继续处理,将用户变量x赋值为3。如果这里的INSERT语句能够执行,处理程序将不被激活,用户变量x2将不被赋值。

 

注意:不能为同一个出错消息在同一个BEGIN-END语句块中定义两个或更多的处理程序。

 

为了提高可读性,可以使用DECLARE CONDITION语句为一个SQLSTATE或出错代码定义一个名字,并且可以在处理程序中使用这个名字。

 

DECLARE CONDITION语法格式为:

 

DECLARE condition_name CONDITION FORcondition_value

 

其中,condition_value:

 

 SQLSTATE [VALUE] sqlstate_value

 

| mysql_error_code

 

说明:condition_name是处理条件的名称,condition_value为要定义别名的SQLSTATE或出错代码。

 

例: 修改上例中的存储过程,将SQLSTATE '23000' 定义成NON_UNIQUE,并在处理程序中使用这个名称。程序片段为:

 

BEGIN

 

   DECLARE NON_UNIQUE CONDITION FOR SQLSTATE '23000';

 

   DECLARE CONTINUE HANDLER FOR NON_UNIQUE SET @x2=1;

 

   SET @x=2;

 

   INSERT INTO XS VALUES('081101', '王民', '计算机', 1, '1990-02-10', 50 , NULL, NULL);

 

   SET @x=3;  www.2cto.com  

 

END;

 

  1.  游标

 

一条SELECT...INTO语句返回的是带有值的一行,这样可以把数据读取到存储过程中。但是常规的SELECT语句返回的是多行数据,如果要处理它需要引入游标这一概念。MySQL支持简单的游标。在MySQL中,游标一定要在存储过程或函数中使用,不能单独在查询中使用。

 

使用一个游标需要用到4条特殊的语句:DECLARE CURSOR(声明游标)、OPEN CURSOR(打开游标)、FETCH CURSOR(读取游标)和CLOSE CURSOR(关闭游标)。

 

如果使用了DECLARE CURSOR语句声明了一个游标,这样就把它连接到了一个由SELECT语句返回的结果集中。使用OPEN CORSOR语句打开这个游标。接着,可以用FETCH CURSOR语句把产生的结果一行一行地读取到存储过程或存储函数中去。游标相当于一个指针,它指向当前的一行数据,使用FETCH CORSOR语句可以把游标移动到下一行。当处理完所有的行时,使用CLOSECURSOR语句关闭这个游标。

 

(1)声明游标

 

语法格式:DECLAREcursor_name cursor for select_statement

 

说明:cursor_name是游标的名称,游标名称使用与表名同样的规则。select_statement是一个SELECT语句,返回的是一行或多行的数据。这个语句声明一个游标,也可以在存储过程中定义多个游标,但是一个块中的每一个游标必须有唯一的名字。

 

注意:这里的SELECT子句不能有INTO子句。

 

下面的定义符合一个游标声明:

 

DECLARE XS_CUR1 CURSOR FOR

 

   SELECT 学号,姓名,性别,出生日期,总学分

 

       FROM XS

 

       WHERE 专业名 = '计算机';

 

注意:游标只能在存储过程或存储函数中使用,例中语句无法单独运行。

 

(2)打开游标

 

声明游标后,要使用游标从中提取数据,就必须先打开游标。在MySQL中,使用OPEN语句打开游标,其格式为:OPEN cursor_name

 

在程序中,一个游标可以打开多次,由于其他的用户或程序本身已经更新了表,所以每次打开结果可能不同。  www.2cto.com  

 

(3)读取数据

 

游标打开后,就可以使用fetch…into语句从中读取数据。

 

语法格式:FETCH cursor_nameINTO var_name [, var_name] ...

 

说明:FETCH ...INTO语句与SELECT...INTO语句具有相同的意义,FETCH语句是将游标指向的一行数据赋给一些变量,子句中变量的数目必须等于声明游标时SELECT子句中列的数目。var_name是存放数据的变量名。

 

(4)关闭游标

 

游标使用完以后,要及时关闭。关闭游标使用CLOSE语句,格式为:

 

CLOSE cursor_name语句参数的含义与OPEN语句中相同。

 

例如: CLOSE XS_CUR2  将关闭游标XS_CUR2。

 

例: 创建一个存储过程,计算XS表中行的数目。

 

DELIMITER $$

 

CREATE PROCEDURE compute (OUT NUMBERINTEGER)

 

BEGIN

 

   DECLAREXH CHAR(6);

 

   DECLAREFOUND BOOLEAN DEFAULT TRUE;

 

   DECLARENUMBER_XS CURSOR FOR

 

     SELECT学号 FROM XS;

 

   DECLARECONTINUE HANDLER FOR NOT FOUND

 

     SETFOUND=FALSE;

 

   SETNUMBER=0;

 

   OPENNUMBER_XS;

 

   FETCHNUMBER_XS INTO XH;

  www.2cto.com  

   WHILEFOUND DO

 

     SETNUMBER=NUMBER 1;

 

     FETCHNUMBER_XS INTO XH;

 

   ENDWHILE;

 

   CLOSENUMBER_XS;

 

END$$

 

DELIMITER ;

 

 

作者 tianyazaiheruan

1. 使用存储过程的优点有: (1)存储过程在服务器端运行,执行速度快。 (2)存储过程执行一次后,其执行规划就...

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:澳门新浦京娱乐场网站:MySQL游标循环取出空值的