【笔记】软件测试04——MySQL

五、Mysql数据库

学习数据库需要掌握的几个关键概念：

• 数据库
• 表
• 字段
• 记录
• 约束
• 主键和外键

一）数据库基础

1）数据库基本概念

• 数据库由表、关系以及操作对象组成
• 数据存放在表中，表由行（记录）和列（字段）组成，类似于我们平时用的excel数据表。由于mysql是关系型数据库，所以表又成为关系（relation）

01约束

约束即管理，对于数据库管理而言，就是要管理数据的完整性。

可靠性+准确性=数据完整性

实体完整性：任何一个实体，都必须要一个唯一可标识的字段来区分两个不同的实体（指主键）

参照完整性：指两张不同的表，主键和外键之间的一个参照，主键和外键之间必须满足外键的参照完整性（主要指外键）

自定义完整性：指系统自己定义的一些约束条件。

02实体完整性—— 主键

概念：

表中有一列或几列组合的值能用来唯一地标识表中的每一行，这样的一列或者多列叫做表的主键。

        如：学号可以作为学生表的主键，课程号可以作为课程表的主键，（学号、课程号）作为成绩表的主键（组合键，也叫联合组建，单使用一个不能有效区分是否重复时使用组合键）

说明：

• 主键必须是非空且唯一的
• 一个表只能有一个主键，主键约束确保了表中的行是唯一的。
• 表中可以没有主键，但是通常情况下应当为表设置以一个主键。

主键选择的原则：

• 最少性：尽量选择单个字段作为主键
• 稳定性：尽量选择数值更新少的列作为主键

03参照完整性——外键

        即“子表”中对应于“主表”的列，在子表中称为外键或者引用键。

        它的值要求于主表的主键或者唯一键相对应，外键用来强制引用完整性。例如在成绩表中，学号为。一个表可以有多个外键。

04自定义完整性

        自定义完整性：指根据具体的业务要求所设置的约束，跟之前介绍的主键和外键不同，自定义完整性需要我们单独进行设置之后，数据库才会进行检查。

        约束方法：存储过程、触发器

        比如：成绩，是0~100分值区间，但是103就不符合要求，就需要设置约束。

2）数据库类型

01常见的关系型数据库

语法是SQL

• SQL Server
• MySQL
• Oracle
• DB2
• SQLite：比较适合嵌入式软件，容量较小

02常见的非关系型数据库

• MongoDB
• Redis

非关系型数据库的数据不是以表的形式保存的，而是以键值对的形式保存的。

3）结构化查询语句SQL

什么是SQL：

01SQL语句的分类

语言分类	名称	详细描述
1、数据库查询语句（DQL）	select	数据选择查询
2、数据操纵语句（DML）	insert update delete	插入 更新 删除
3、数据定义语句（DDL）	create alter drop rename truncate	新建 修改结构 删除 重命名 删除
4数据控制语句（DCL）	grante revoke	授权 接触授权

学习重点：

4）总结

1. 使用数据库，可以降低存储数据的冗余度，提供更高的数据一致性、数据完整性和安全性
2. 数据是以表的形式存储于数据库中，表由行和列组成，类似于excel中的数据表
3. 表中的一行叫作一条记录，对应一个实体的信息。表中的一列对应一个实体的属性，通常称为“域”或“字段”
4. 数据库对数据的管理主要体现在约束上，常见的约束有实体完整性、参照完整性以及 自定义完整性约束
5. 主键用于唯一地标识某一个实体，主键不能为空，也不能重复
6. 外键是关联到主表的一个主键，主要用于约束关联字段的取值范围
7. 自定义约束并不是强制的，可以根据需要进行设置
8. SQL四关系型数据库的标准编程语言，可以通过编写SQL语句访问和操纵数据库中的数据
9. SQL标准定义了操作关系型数据库及其包含的数据的多种技术，它分为DQL、DML、DDL、DCL。
10. 不同的RDBMS对SQL标准的支持有所不同。

二）管理数据库

1）创建和使用数据库

在大多数EDNMS中，使用如下简单的 SQL语句，创建整个数据库：

CREATE DATABASE [IF NOT EXISTS] 数据库名：

[IF NOT EXISTS] 指，若是不存在则创建

• 其中，数据库名在服务器中必须是唯一的，并且符合标识符规则。规则：数据库名必须以字母开头，一般可以根据业务规则取一个有意义的数据库名字，中文也可以做表名和数据库名，但不建议
• 标识符不能是所用RDBMS的保留字
• 不允许嵌入空格或其他特殊字符
• 除了通过命令创建外，也可通过navicat在界面中创建一个新的数据库。navicat的注释：CTRL+/

01数据库语句的基本书写规则

• SQL语句要以分号（;）结尾，一条SQL语句可描述一个数据库操作。在RDBMS中，SQL语句也是逐条执行的。
• SQL语句不区分大小写，例如，不管SELECT还是select，解释都是一样的，表名和列名也是如此。通常我们遵循关键字大写，表名的首字母大写、其余（列名等）小写的习惯。
• 字符串和日期值需要用引号括起来，如'abc'、'20220-9-20'，但数值型的值，如1、2、3等这样的不需要用引号。
• SQL语句的单词之间需使用半角空格或换行符来进行分隔。如CRATETABLE Product这种未加分隔的语句会发生错误，无法正常执行。

02管理数据库

• 查看数据库

SHOW DATABASES;

• 删除数据库

DROP DATABASE 数据库名;

• 连接到数据库

        新的 数据库创建完毕后，为了在该数据库上执行SQL语句，需先生们使用的数据库。声明当前使用哪个数据库：

USE 数据库名;

1. 修改数据库编码集

ALTER DATABASE ab_name CHARACTER SET = charset_name

eg:

  ALTER DATABASE test CHARACTER SET =gb2312

注：如果在navicat中需要关掉数据库连接后重新打开查看数据库属性才能看到更改

• 创建表

CREATE  TABLE 表名(

  列名 数据类型 约束，

  列名 数据类型 约束

)[ENGINE = engine_name|[DEFAUT]CHARACTER SET = charset_name];

        其中，表名在一个数据库中必须是唯一的，并且符合标识符规则。列名在一个表内必须是唯一的，并且符合标识符规则。列的数据类型决定了什么数据可以存储在列中，而列的约束定义了创建列时的限制条件。MySQL引擎类型有InnoDB和MyISQM两种，目前一般5.4以上的版本都是InnoDB。

eg：

CREATE TABLE Friend(Name VARCHAR(50),PhoneNo VERCHAR(15));

CREATE TABLE demo(id INT,name VAARCHAR(10))ENGINE=INNODB DEFAULT CHARACTER SET=utf8；

2）数据类型

01常见的数据类型

类型	精度范围	内容格式	用途
INT(n)	(-2147483648,2147483647)	(0,4294967295)	整数值
FLOAT	(-3.40E-38,3.40E+38)	7位小数	单精度浮点数
CHAR(n)	0-255	字符型	存储定长字符
VARCHAR(n)	0-65535	字符型	存储不定长字符
TEXT	0-65535	文本型	大容量文本
BLOB	65k	二进制文件	二进制大对象

                BLOB可保存音乐、图片等

定长字符串和不定长字符串

• 定长字符型：字符串长度时一定的，如果实际的字符串长度不足，将以空格来补齐。比如char(10)，若实际存储的字符时hello，将存储hello+5个空格。
• 不定长字符型：字符串的最长长度是一定的，但实际的字符串长度是根据实际存储值来觉得的，如果长度不足不会空格补齐。varchar(10)，如果实际存储hello，则实际存储的字符长度为5。

日期和时间类型

类型	格式	范围
YEAR	YYYY	1901-2155
DATE	YYYY-MM-DD	1000-01-01 - 9999-12-31
TIME	HH:MM:SS	-835:59:59 - 835:59:59
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01-2038
DATEIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 - 9999-12-31 23:59:59

日期和时间数据类型插入示例：

CREATE TABLE demo(

  id INTauto_increment PRIMARY KEY,

  reg_datetime DATETIME,

  reg_time TIME,

  reg_daya DATE,

  reg_year YEAR

)

INSERT INTO table1 VALUES(1,'2018-03-29 23:23:34','2018-03029','2018')

思考：在创建表时下列一般定义为何种数据类型？

• 姓名、地址、电话号码等：VARCHAR
• 年龄：   INT
• 出生日期：DATA
• 照片：BLOB
• 薪水：FLOAT

02创建主键

• 直接在字段定义后面声明主键

CREATE TABLE demo(

  id INT PRIMARY KEY,

  ...

)

不能两次设置PRIMARY KEY，可以用第三种方法设置第二种方法

• 用constraint声明主键

CREATE TABLE demo(

  id INT,

  ...

  (主键命名规范：pk_表名,)

  CONSTRAINT pk_name PRIMARY KEY(id)

)

也可以简写成

CREATE TABLE demo(

  id INT,

  NAME VARCHAR(10)

  ...

  PRIMARY KEY(id,NAME)  --复合主键，单主键也可

)

• 用ALTER语句补充声明主键

ALTER TABLE stu ADD CONSTRAINT pk_stu PRIMARY KEY(id);  --也可以补充符合主键

• 删除主键

ALTER TABLE stu DROP PRIMARY KEY;

03创建外键

“子表”中对应于“主表”的列，在子表中称为外键或者引用键。

它的值要求于主表的主键或者唯一键相对应，外键用来强制引用完整性。一个表可以有多个外键。

语法： 

FOREIGN KEY(sid) REFERENCES 主表(sid)

eg：

CREATE TABLE IF NOT EXISTS demo_new(

  cid INT PRIMARY KEY,

  sid INT,

  FOREIGN KEY(sid) REFERENCES demo(sid)

)

如何制定外键在update或delete主表时的行为？

即指定外键删除或更新的行为：

1. 若子表试图创建一个在父表种不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。
2. 若父表试图UPDATE或者DELECT任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB支持4种不同的动作（MyASM），如果没有指定ON UPDATE或者ON DELETE，默认的动作为RESTRICT。

• CASCADE：从父表中删除或更新对应的行，同时自动的删除或更新子表中匹配的行。
• SET NULL：从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有设置NOT NULL约束才有效。ON DELETE SET NULL和ON UPDDATE SET NULL 都被InnoDB所之处。
• NO ACTION：InnoDB拒绝删除或更新父表。
• RESTRICT：拒绝删除或更新父表。指定RESTRICT（或者NO ACTION）和虎烈ON DELETE或者ON UPDATE选项的效果时一样的。

eg：从父表中删除或更新对应的行，同时自动的删除或更新子表中匹配的行。

CREATE TABLE IF NOT EXISTS demo_new(

  cid INT PRIMARY KEY.

  sid INT,

  FOREIGN KEY(sid) REFERENCES demo(sid) ON UPDATE CASCADE

)

更新和删除随着主键。

CREATE TABLE IF NOT EXISTS demo_new(

  cid INT PRIMARY KEY.

  sid INT,

  FOREIGN KEY(sid) REFERENCES demo(sid) ON DELETE CASCADE ON UPDATE CASCADE

)

更新随着主键，拒绝删除父表,此时想要删除主键,可以先删除子键所引用到的数据删除.

CREATE TABLE IF NOT EXISTS demo_new(

  cid INT PRIMARY KEY.

  sid INT,

  FOREIGN KEY(sid) REFERENCES demo(sid) ON UPDATE CASCADE ON DELETE RESTRICT

)

添加外键

fk即FOREIGN  KEY

ALTER TABLE demo_new ADD CONSTRAINT fk_demo_new FOREIGN KEY(sid)

REFERENCES demo(sid)

ON DELETE CASCADE ON UPDATE CASCADED

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KRY 外键名称

3)数据库管理语句

01创建自增长

eg:

DROP TABLE demo;  --删除表

CREATE TABLE demo(

  --设定为自增的列必须设置为主键,并且一张表只能由一个自增列.字段和主键同时定义并设定自增长.

  ID INT AUTO_INCREMENT PRIMARY KEY,

  name VARCHAR(10)

)

INSERT INTO demo VALUES(3,'tester1');

INSERT INTO demo(name) VALUES ('tester2'); --没有指定主键的值则会自增长

INSERT INTO demo VALUES (DEFAULT,'tester2'); --若是没有指定行,则可defatlt

02创建唯一约束

CREATE TABLE demo(

  ...

  vip VARCHAR(50) UNIQUE,

  ...

)

03创建非空约束

CREATE TABLE demo(

  ...

  vip VARCHAR(50) NOT NULL,

  ...

)

04创建检查约束

Mysql原生并不支持检查约束，对于离散值可使用set或enum方式解决。对于连续纸只能通过触发器解决。

CREATE TABLE demo(

       id INT auto_increment PRIMARY KEY,

       name VARCHAR(10),

    sex SET('男','女'),  --如果其他值则不插入值

    payment ENUM('货到付款','在线支付')

)

05创建默认值

默认值和唯一约束不能同时设置。

CREATE TABLE demo(

  ...


  tel VARCHAR(11) DEFAULT '15888888888'  --默认值

  ...

)

06管理数据库中的表

• 查看数据库中的所有表

 SHOW TABLES;

• 查看表的定义，可查看创建表的时候的sql语句。

SHOW CREATE TABLE 表名;

• 删除表

DROP TABLE <表名>;

通过外键约束连接在一起的表不能被删除。在删除之前，必须线删除约束。在删除表时，必须是表的所有者或者对该表有管理员权限。

• 复制表

在DB中，下面的语句创建一个名为new_aimtable的新表，该表是赋值了table表的数据和结构（列、数据类型）

CREATE TABLE 新表名 AS (SELECT * FROM 被赋值表名);

 

如果是只复制表的结构，不复制数据：

CREATE TABLE 新表名 LIKE 被赋值表名;

• 修改表名

alter table 表名 RENAME [TO|AS] 新的表名;

eg: alter table stu RENAME TO student;

• 添加列：

ALTER TABLE <表名> ADD COLUMN <列的定义>;

eg：ALTER TABLE product ADD COLUMN product_name VARCHAR(100);

• 更新列名及类型

更新列名及类型
ALTER TABLE 表名 CHANGE [COLUMN] 原列名 新列名 列定义;

EG: ALTER TABLE stu CHANGE COLUMN stu_name sname varchar(20);

只修改列数据：

ALTER TABLE 表名 MODIFY [COLUMN] col_name column_definition;

EG: ALTER TABLE stu MODIFY COLUMN sname varchar(10);

• 删除列

alter table 表名 DROP [COLUMN] col_name;

eg: alter table stu DROP COLUMN sname;

之前的版本的形式，反引号可不加。

 

总结

• 使用CEATE TABLEBASE和DROP DAYABASE语句可以创建和删除数据库。
• 创建数据库、表以及表中的列时，必须遵循SQL标识符规范。
• 数据库中的每个列有一个指定的数据类型，数据欸写决定了什么样的数据可以别存储在列中。创建表之气那，我们必须选定每一列的数据类型。
• 使用CREATE TABLE 和 DROP TABLE语句可以创建和删除数据库表。
• ALTER TABLE语句同其他选项一起，可以更高已有数据的表的结构。

4）练习题

1. 编写一条CREATE TABLE 语句，用来创建一个包含下表中所列各项的表Addressbook（ 地址簿），并为regist_no（ 注册编号）列设置主键约束。

CREATE TABLE Addressbook(

       regist_no INT NOT NULL PRIMARY KEY,

       name VARCHAR(128) NOT NULL,

       address VARCHAR(256) NOT NULL,

       tel_no CHAR(10),

       mail_address CHAR(20)

)DEFAULT CHARACTER SET=utf8

2. 假设在创建练习1.1 中的Addressbook 表时忘记添加如下一列postal_code（邮政编码）了，请把此列添加到Addressbook 表中。

列名 ：postal_code

数据类型 ：定长字符串类型（长度为8）

约束 ：不能为NULL

 ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;

3. 编写SQL语句来删除Addressbook表。

DROP TABLE Addressbook;

三）DML语句操作

1）对表进行操作

01插入数据行

INSERT INTO <表名> ([列名]) VALUES (<值列表>)

eg:

INSERT INTO Student(SName,SAddress,SGrade,SEmail,SSEX)

VALUES('张三','上海松江',6,'zs@SoHu.com',0);

--如果没有指定列名，则代表每个列都加入内容，需要都作插入，自增列，可以定义为DEFAULT

--若是插入的类型和定义的不一致，不会报错，会设置为默认值。

--如果在设计表时指定了某列不允许为空，单输入数据的时候没有提供这个值，MYSQL会给该列插入一个默认值，eg，若是上诉的sname没有赋值设置插入，则为" "一个空值字符串

注意：

• 当主键设置auto_increment时，设置为null，mysql会自动填入自增后的数字作为主键
• 当表名后没有指定要插入的字段的名称时，values后面的值的个数必须跟表中所有字段的个数相等，即表中有多少字段，就需要提供多少个值，否则会报错。

一次性插入多行记录：

INSERT INTO <表名> ([列名]) VALUES (<值列表>),(<值列表>),(<值列表>)...

02更新数据行

UPDATE <表名> SET <列名=更新值>

[WHERE <更新条件>]

eg:

UPDATE Student SET SSEX=0



UPDATE Scoures

SET Soures = Soures+5

WHERE Soures <= 95

03删除数据行

DELETE FROM <表名> [WHERE <删除条件>]

DELETE FROM <表名>; --若是不指定删除条件，则将删除所有表中数据

与之类似功能的：

TRUNCATE TABLE <表名>;

--二者区别：

关于TRUNCATE：DDL语句，会自动提交事务，无法回滚。

 1、表结构、列、约束等不被改动

 2、不能用于有外键约束的表

 3、标识重新开始编号

关于DELETE FROM：手动提交事务，可以在事务中回滚。

  1、数据会一行一行的删，效率比较低

eg：

START TRANSACTION;  --开始事务

SELECT* FROM stu;

DELETE FROM stu;

SELECT* FROM stu;

ROLLBACK;  --事务回滚，只能用作于事务未提交时进行回滚

SELECT* FROM stu;



START TRANSACTION;

SELECT* FROM stu;

DELETE FROM stu;

SELECT* FROM stu;

ROLLBACK;

SELECT* FROM stu;

04总结

• 向数据中添加数据使用INSERT INTO关键字。
• 在使用INSERT INTO 向表中插入数据时，如果不指定列名，那么values列表的值必须和表中列的顺序，列的个数一致。
• 如果表中的列值由RDBMS自动创建，指定了默认值或运行为空值等情况，那么再插入数据时可以不指定这些列值。
• 再插入诗句时还可以使用INSERT INTO...SELECT语句赋值其他表的数据，使用这种方法时不再需要使用values关键字
• 更新表中数据时使用UPDATE...SET...子句，通过使用where子句来选择型的更新某一行或多行记录。如果不指定where条件，则删除表中所有数据。
• 删除数据时使用DELETE FROM子句，如果指定了where条件则选择性的删除一行或多行数据，如果不指定where条件，则删除表中所有数据。
• 一次性删除表中所有数据，使用TRUNCATE TABLE子句。使用它要比使用DELETE FROM 效率高，由于它不会记录删除的详细日志，因而删除的数据不能回滚。同时不能用于有外键约束引用的表。

四）数据查询

1)什么是查询

01查询格式

SELECT <列名>  --如是为*则表示查找所有列

FROM <表名>

[WHERE <查询条件表达式>]

[ORDER BY <排序的列名>[ASC|DESC]] <根据排序的列>

 

• sql语句大小写不敏感
• sql语句可写成一行或多行
• 一个关键字不能跨多行或缩写
• 子句通常位于独立行，以便编辑，并易读
• 空格和缩进使程序易读
• 关键字大写，其他小写

02对数值型数据可用算数运算创建表达式

运算	描述
+	加法
-	减法
*	乘法
/	除法

eg：

SELECT ename,sal,sal+300   --表示薪资加了300后，显示成一列

FROM emp;

注意：

• 乘法和除法的优先级高于加法和减法
• 同级运算的顺序是从左往右
• 表达式中使用括号可强行改变优先级的运算顺序

03对于空值

• 空值是指不可用、不知道、不适用的值
• 空格不等于零和空格
• 空值的算法表达式等于空

04定义列的别名

• 改变列的标题头
• 使用计算结果
• 列的别名
• 如果使用特殊字符，或大小写敏感，或有空格时，需加引号

eg

1、

2、

SELECT ename as "姓    名",sal as 薪资

from emp;

05DISTINCT 去重

使用DISTINCT关键字可删除重行

SELECT DISTINCT deptno FROM emp;

使用DISTINCT关键字多列排除重复

SELECT DISTINCT 列A,列B FROM 表;

--注意，并不意味着任一列没有重复，只有再组合时没有重复，一般没有意义

eg:

select DISTINCT deptno,ename from emp;

06限制查询行数

在SELECT语句中使用LIMIT子句来约束查询结果集中的行数。LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数。

SELECT 列1,列2...列n FROM LIMIT offset,count

offset：参数指定要返回的第一行的偏移量。第一行的偏移量为0，二不是1

count：指定要返回的最大行数

eg：

SELECT* FROM emp LIMIT 5,6  --从偏移量为5的行开始取，取第6行

当使用带一个参数的LIMIT子句时，此参数将用于确定从结果集的开头返回的最大行数。

SELECT* FROM emp LIMIT 5

--这个语句等于 select* from emp limit 0，5

1、使用limit来获取结果的前几行

eg：求工资最高的前三个员工的信息

SELECT* FROM emp ORDER BY sal DESC LIMIT 3;  --DESC降序

2、使用limit来获取第n个数据

eg：求工资第4高的员工的信息

SELECT* FROM emp ORDER BY sal DESC LIMIT 3,1;

07限定返回记录

使用WHERE子句限定返回的记录

SELECT [DISTINCT] {*,COLUMN[alias],...}

FROM table

[WHERE condition(s)]

WHERE子句必须跟在FROM字句后

注意：

在where子句中字符串和日期要用单引号括起来

SELECT * FROM emp 

WHERE ename="king"  --没有区分大小写

运算符：

运算符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于
BETWEEN...END...	在两值之间（包含）
IN(list)	匹配列出的值
LIKE	匹配一个字符模式，模糊匹配
IS NULL	是空值
AND	如果组合的条件都为真则返回真值
OR	如果组合的条件之一是真值，返回真值
NOT	如果条件为假则返回真值

优先级：

所有的比较运算>NOT>AND>OR，可用括号改变

eg：

SELECT empo,ename,sal,mgr

FROM emp

WHERE mgr IN(7902,7566,7788);

也可以：

WHERE mgr=7902 or mgr=7566 or mgr=7788);

LIKE：模糊匹配

1. 使用LIKE运算符执行通配拆线呢
2. 查询条件可包含文字字符或数字

• （%）可表示零或多个字符
• （_）可表示一个字符

eg:

SELECT ename

FROM emp

WHERE ename LIKE 'S%'

另外：

WHERE ename LIKE '_A%'

           

08ORDER BY

使用ORDER BY 子句将记录排序

• ASC：升序，缺省，即可省
• DESC：降序

ORDER BY 子句在SELECT语句的最后

SELECT empno,ename,sal*12 annsal

FROM emp

ORDER BY annsal;  --使用别名排序

09总结

• 为从数据库中查询数据，必须使用SQL的SELECT语句。
• 所有的SELECT语句均使用SELECT关键字开头。
• 如果表名或列名是一个SQL关键字或是数据库厂商的保留字，则需要将表名或列名用中括号或双引号括起来。
• 通过SELECT语句，既可以返回单列数据，也可用返回多列数据。
• 在SQL语句中，可用对列执行一般的加减乘除数学计算。
• 在SQL语句中使用DISTINCT关键字可用排除重复数据。
• 在SQL语句中使用WHERE子句来过滤查询，过滤条件可用是多种i形式。
• 为对数据排序，可在SQL语句中使用ORDER BY 子句，当ORDER BY 子句后只指定一个列名时，即按照单列排序。如果指定多个列名时，即按照多列排序。多列排序 是根据后面列名的顺序确定优先级。
• 在ORDER BY子句中的列名上指定ASC或DESC关键字，使数据按照升序或降序排列。如果不指定，则默认以升序的方式排列

2)练习题

1. 根据要求创建一个product表，并向其插入以下数据。

Table：product

字段1：product_id（商品id），4位字符，非空，主键

字段2：product_name（商品名称）, 可变字符100位，非空

字段3：product_type（商品类型），可变长32位，非空

字段4：sale_price（销售价格），整型

字段5：purchase_price（进货价格），整型

字段6：regist_date（注册日期），日期型



插入数据如下：

'0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20'

'0002', '打孔器', '办公用品', 500, 320, '2009-09-11'

'0003', '运动T恤', '衣服', 4000, 2800, NULL

'0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'

'0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'

'0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'

'0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28'

'0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11'

 如下：

CREATE TABLE product(

       product_id CHAR(4) NOT NULL PRIMARY KEY,

       product_name VARCHAR(100) NOT NULL,

       product_type VARCHAR(32) NOT NULL,

       sale_price INT,

       purchase_price INT,

       regist_date DATE

)DEFAULT CHARACTER SET=utf8;



INSERT INTO product VALUES('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20')

INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')

INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL)

INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')

INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15')

INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20')

INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28')

INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100,NULL, '2009-11-11')

2. 编写一条SQL 语句，从Product（商品）表中选取出“登记日期（regist_date）在2009 年4 月28 日之后”的商品。查询结果要包含product_name 和regist_date 两列。

SELECT product_name ,regist_date FROM Product

WHERE regist_date>'2009-04-28';

3. 请写出一条SELECT 语句，从Product 表中选取出满足“销售单价打九折之后利润高于100 元的办公用品和厨房用具”条件的记录。查询结果要包括product_name 列、product_type 列以及销售单价打九折之后的利润（别名设定为profit）

SELECT product_name,product_type, sale_price*0.9 - purchase_price AS profit FROM Product

WHERE (sale_price*0.9 - purchase_price)>100 AND (product_type='办公用品' OR product_type='厨房用具');

 

4. 筛选出sale_price比purchase_price高出500元及以上的商品的product_name, sale_price，purchase_price。

SELECT product_name,sale_price,purchase_price FROM Product

WHERE sale_price-purchase_price >= 500;

5. 将所有销售价格在1000到4000之间的所有商品的信息打印出来

SELECT* FROM product

WHERE sale_price BETWEEN 1000 AND 4000;

6. 找出所有销售价格大于等于4000的商品并按进货价格降序排列

SELECT* FROM product

WHERE sale_price>=4000 ORDER BY sale_price DESC;

五)聚合函数和分组

1)聚合分组的概念

聚合函数会针对进行运算的所有记录返回唯一一个结果

分组函数GROUP BY可对记录进行分组

01常用聚合函数

COUNT	返回结果集中行的数目
SUM	返回结果集中所有值的总和
AVG	返回结果集中所有值的平均值
MAX	返回结果集中所有值的最大值
MIN	返回结果集中所有值的最小值

MAX和MIN也可以作用于时间，最早时间和最晚时间

eg:

SELECT COUNT(<计数规范>) FROM <表名>;

为什么叫(分组)聚合函数?

        普通的select语句会针对表中的每行记录进行处理并返回结果，而聚合函数使根据表中分组的情况进行处理，每组只返回一个结果。

• IFNULL 函数强制分组函数包括空值

SELECT AVG(IFNULL(comm,0)) FROM emp;

• 使用group by子句将表分成小组

分组函数忽略空值，可以使用IFNULL等函数处理空值

SELECT deptno, AVG(sal) FROM emp

GROUP BY deptno;

如果有普通列

SELECT job，deptno, AVG(sal) FROM emp

GROUP BY deptno;

eg2：

GROUP BY deptno,job;  --多列分组，先按照部门分组，再按照职位分组

--job：普通列(没有意义)  AVG(sal)：聚合函数  deptno：分组依据列

结论：

MYSQL中，SELECT后出现了聚合函数，则该位置只能出现其他聚合函数和分组依据列，普通列只显示第一行值，其他数据库有些不支持该行为。

02限定分组结果——having

Having子句的作用使对行分组进行过滤

• 记录被分组
• 使用组函数
• 匹配HAVING 子句的组被显示
• HAVING子句应放在GROUP BY 后面，ORDER BY 之前

SELECT column，group_function

FROM <表>

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[GROUP BY colunm];

查询工资大于￥2900的部门的最高工资：

SELECT deptno,max(sal)

FROM emp

GROUP BY deptno

HAVING max(sal)>2900;

03分组查询对比

• WHERE子句：从数据源中去掉不符合其搜索条件的数据。
• GROUP BY子句：搜集数据行到各个组中，统计函数为各个组机选统计值，每个组输出一行结果。
• HAVING子句：从分组计算结果中进行过滤，去掉不符合其组搜索条件的各组数据行。

WHERE -> GROUP BY -> HAVING

04嵌套组函数

显示最高的平均工资（嵌套组函数）

SELECT max(avg(sal))

FROM emp

GROUP BY deptno;

05总结

• SQL使用COUNT函数用于对满足某条件的行计数。
• SUM函数，用于计算一个列的合计值。它是用来执行数学合计的操作，合计的列的数据类型必须是数值型的。
• SQL中使用AVG函数获取列平均值。
• SQL中使用聚合函数MAX和MIN 函数返回列的最大值和最小值。
• SQL中使用GROUP BY子句来对数据分组。
• 不想通过数据表中的实际值，而是通过聚合函数的结果来过滤查询结果集。这时使用HAVING子句来实现。HAVING子句放在GROUP BY子句之后，其形式为HAVING过滤体条件。

06SQL语句的执行顺序

关系型数据库管理系统在执行一条SQL时，按照如下顺序执行各子句：

• 首先执行FROM子句，从表中加载数据，在内存中形成一张虚拟表
• 如果有WHERE子句，则根据其中的过滤条件，从第一步的虚拟表中去掉不满足过滤条件的行。
• 根据GROUP BY子句中指定的分组列，对中间表中的数据进行分组。
• 为每个组计算SELECT子句聚合函数的值，并为每组生成查询结果中的一行。
• 如果有HAVING子句，根据HAVING子句的过滤条件，分组激素那聚合 激素那的结果再次过滤。
• 如果有GROUP BY子句中，则根据ORDER BY 子句中的列，对结果集进行排序。

SELECT AVG(列名) FROM tablename

WHERE **

GROUP BY<列名>

HAVING

ORDER BY<列名>ASC/DESC

LIMIT

2)练习题

1. 请编写一条SELECT 语句，求出销售单价（sale_price 列）总和大于进货单价（purchase_price 列）总和1.5 倍的商品种类以及销售总价和进货总价。

SELECT product_type 商品类型, sum(sale_price) 销售总价,SUM(purchase_price) 进货总价 FROM product

GROUP BY product_type

HAVING sum(sale_price)>SUM(purchase_price)*1.5

2. 查找商品表中商品销售价格的平均价、最高价、最低价。

SELECT AVG(sale_price) 销售平均价, MAX(sale_price) 销售最高价, MIN(sale_price) 销售最低价

FROM product

3. 查找商品表中每种商品的平均售价，并降序显示。

SELECT AVG(sale_price) 销售平均价

FROM product

GROUP BY product_type DESC

4. 查找商品表中按类型统计商品数大于2的商品类型及商品数

SELECT product_type,COUNT(product_type) 商品数

FROM product

GROUP BY product_type

HAVING COUNT(product_type)>2

 5. 查询销售价格比最低进货价还低的商品的id、商品名及销售价格。

SELECT product_id,product_name,sale_price

FROM product

WHERE sale_price<purchase_price

6. 按照下列语句创建几个表：

DROP TABLE IF EXISTS stu;

create table stu (

sid int primary key, -- 学生id

sname varchar(50)    -- 学生姓名

);

DROP TABLE IF EXISTS course;

create table course(

cid int primary key, -- 课程id

cname varchar(50)    -- 课程名称

);



DROP TABLE IF EXISTS sc;

create table sc(

sid int,    -- 学生id

cid int,    -- 课程id

score int   -- 成绩



);

ALTER TABLE sc  ADD CONSTRAINT fk_sc_course_cid FOREIGN KEY (cid)  REFERENCES course(cid);



ALTER TABLE sc ADD CONSTRAINT fk_sc_stu_sid FOREIGN KEY (sid)  REFERENCES stu(sid);





insert into stu values(1,"张三");

insert into stu values(2,"李四");

insert into stu values(3,"王五");



insert into course values(1,"语文");

insert into course values(2,"数学");

insert into course values(3,"英语");



insert into sc values(1,1,78);

insert into sc values(2,2,45);

insert into sc values(3,1,67);

insert into sc values(1,3,83);

insert into sc values(3,2,42);

insert into sc values(2,3,54);

insert into sc values(1,2,69);

insert into sc values(2,1,72);

insert into sc values(3,3,34);

然后完成以下题目：

（1）获取平均成绩大于60的每个学生名字

（2）获取到语文及格的每个学员的名字

（3）获取及格科目大于1个的每个学员的名字（要点：先按照分数过滤出高于60分的，然后分组，再在每组里面统计分数值大于1个的）

六）MSQL子查询

01子查询

• 子查询在主查询前执行一次
• 主查询使用子查询的结果

SELECT select_list

FROM table

WHERE expr operator(

  SELECT select_list

  FROM table

);

eg:

SELECT ename

FROM emp

WHERE sal>(

  SELECT sal

  FROM emp

  WHERE empno='7560'

);

02子查询规则

• 子查询要用括号括起来
• 将子查询放在比较运算符的右边
• 子查询中不要加ORDER BY子句
• 对单行子查询使用单行运算符
• 对多行子查询使用多行运算符

03单行子查询

• 返回一行记录
• 使用单行记录比较运算符

操作符：=、>、>=、<、<=、<>

eg：

SELECT ename,job

FROM emp

WHERE job = (

    SELECT job

    FROM emp

    WHERE empno = '7369'

  )

AND sal>(

    SELECT sal

    FROM emp

    WHERE empno = '7876'

  );

子查询中使用聚合函数：

SELECT ename,job,sal

FROM emp

WHERE sal=

  (

    SELECT MIN(sal)

    FROM emp

  );

HAVING子句中使用子查询结果

• 先执行子查询
• 然后返回结果到著查询的HAVING子句

SELECT deptno,MIN(sal)

FROM emp

GROUP BY deptno

HAVING MIN(sal)>

  (

    SELECT MIN(sal)

    FROM emp

    WHERE deptno=20

  );

错误：在多行子查询中使用单行运算符，比如子查询中使用了group by

04多行子查询

• 返回多行
• 使用多行比较运算符

运算符	含义
IN	等于列表中的任何值
ANY	比较子查询返回的每一个值，只要其中一个值满足条件就返回true
ALL	比较子查询返回的每一个值，要其中每一个值都满足条件才返回true

IN:

    SELECT deptno FR

SELECT empno,ename,job,sal FROM emp

WHERE deptno in

  (

OM emp

    WHERE ename = 'SEITH' or ename='MILLER'

  );

ANY:

SELECT empno,ename,job FROM emp

WHERE sal < ANY

  (

    SELECT sal FROM emp

    WHERE  job= 'CLERK'

  )

AND job <> 'CLERK';

• <ANY指小于最大值，>ANY指大于最小值
• ANY运算相当于对子查询返回的集合内的每个值用or进行比较。

即：

where sal>1300 or sal>1100 or sal>800 or sal>950

--只需要满足小于1300

ALL:

SELECT empno,ename,job,sal FROM emp

WHERE sal > ALL

  (

    SELECT avg(sal)FROM emp

    GROUP BY deptno  

  );

• >ALL指大于最大值，<ALL指小于最小值

即：

where sal >1566 and sal >2175 and sal >2916

--最后只要sal >1566，所有取值都小于子查询中的返回的所有值了。

05子查询总结

• 嵌套在其他查询中的查询称为子查询。子查询又称内部查询，包含子查询的查询语句称为外部查询。
• 在查询中，我们可以使用表的别名和列的别名。当我们使用多个表时，使用表别名的优点是显然的。
• 在有多个子查询的查询，每个组成子查询的查询仅执行一次，每个一次将船体一个值或一系列值到一个外部查询。
• ALL运算符于子查询和比较运算符一起使用。如果子查询返回的所有值都满足比较运算符，那么比较表达式就为真。如果不是所有值都满足比较运算或子查询没有给外部语句返回行，则返回假。
• ANY与ALL的工作方式不同的是：在子查询返回的值中，只要有一行满足比较运算，那么比较表达式就为真。如果所有制都不满足，则返回假。

七）多表连接查询

1）多表连接查询分类

1. 内连接（INNER JOIN）  --用 都比较多
2. 外连接

• 左外连接（LEFT JOIN）
• 右外连接（RIGHT JOIN）

2）内连接

外键取值于主键。

如果没有主外键关系，那么就没有多表查询的需求。

语法：

使用连接从多个表中查询数据（使用where来指定连接条件）

1、

SELECT table1.column,table2.column

FROM table1,table2

WHERE table1.colum1 = table2.colum2;

2、

SELECT table1.column,table2.column

FROM table1 INNER JOIN table2

ON table1.colum1 = table2.colum2;

• 在WHERE子句或JOIN ON中写连接条件
• 在多个表中具有相同的列名（利用主键和外键相等的特性）

连接查询必须指定连接条件，如果不指定连接条件会造成笛卡尔积的结果。

笛卡尔积形成于：

• 连接条件被省略
• 连接条件无效
• 第一个表的所有记录连接到第二个表的所有记录

为了避免笛卡尔结果我们总是在WHERE子句中指定连接条件。

设A，B为集合，用A中元素为第一元素，B中元素为第二元素构成的有序对，所有这样的有序对组合成的集合，叫做A与B的笛卡尔积，记AxB.

eg：

SELECT * FROM emp,dept;

则记录数=emp记录数 x dept记录数

可使用别名简化查询

SELECT a.column,b.column

FROM table1 a,table2 b

WHERE a.colum1 = b.colum2;

可以使用and过滤条件。

表的连接数=表的数量-1

3）外连接

在LEFT JOIN 中，会返回左表中的所有行，即使左表中又不符合条件的记录，也会在查询结果中显示。同理，RIGHT JOIN即返回右表中所有记录。

eg：

如题：查询所有员工姓名和部门编号，包括没有员工的部门。

SELECT emp.ename,dept.deptno

FROM dept LEFT JOIN emp

ON emp.deptno = dept.deptno;

即返回dept中所有在emp的deptno以及在emp没有出现，但dept存在的数据。

4）总结

• 所有的多表连接都必须指定连接条件（使用where关键字指定或xxx join on指定）。不指定连接条件时，将形成笛卡尔积，这是没有任何意义的。
• 当要显示符合连接条件的所有数据时，使用内连接。如果既要显示符合连接条件的数据，又要显示不符合连接条件的数据，则需要使用外连接。

5）练习题

某图书馆系统有以下几个表，建表及数据如下:

-- 创建BOOK:（图书表）

CREATE TABLE BOOK (

 BOOK_ID int,

 SORT VARCHAR(10),

 BOOK_NAME VARCHAR(50),

 WRITER VARCHAR(10),

 OUTPUT VARCHAR(50),

 PRICE int);

-- 创建READER:（读者表）

CREATE TABLE READER (

READER_ID int,

COMPANY VARCHAR(10),

NAME VARCHAR(10),

SEX VARCHAR(2),

GRADE VARCHAR(10),

ADDR VARCHAR(50));

-- 创建BORROW:（借阅表）

CREATE  TABLE  BORROW (

 READER_ID  int,

 BOOK_ID  int,

 BORROW_DATE  datetime)

-- 插入数据:

-- BOOK表:

insert into BOOK values(445501,'TP3/12','数据库导论','王强','科学出版社',17.90);

insert into BOOK values(445502,'TP3/12','数据库导论','王强','科学出版社',17.90);

insert into BOOK values(445503,'TP3/12','数据库导论','王强','科学出版社',17.90);

insert into BOOK values(332211,'TP5/10','计算机基础','李伟','高等教育出版社',18.00);

insert into BOOK values(112266,'TP3/12','FoxBASE','张三','电子工业出版社',23.60);

insert into BOOK values(665544,'TS7/21','高等数学','刘明','高等教育出版社',20.00);

insert into BOOK values(114455,'TR9/12','线性代数','孙业','北京大学出版社',20.80);

insert into BOOK values(113388,'TR7/90','大学英语','胡玲','清华大学出版社',12.50);

insert into BOOK values(446601,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);

insert into BOOK values(446602,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);

insert into BOOK values(446603,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);

insert into BOOK values(449901,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);

insert into BOOK values(449902,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);

insert into BOOK values(118801,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);

insert into BOOK values(118802,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);

-- READER表:

insert into reader values(111,'信息系','王维利','女','教授','1号楼424');

insert into reader values(112,'财会系','李立','男','副教授','2号楼316');

insert into reader values(113,'经济系','张三','男','讲师','3号楼105');

insert into reader values(114,'信息系','周华发','男','讲师','1号楼316');

insert into reader values(115,'信息系','赵正义','男','工程师','1号楼224');

insert into reader values(116,'信息系','李明','男','副教授','1号楼318');

insert into reader values(117,'计算机系','李小峰','男','助教','1号楼214');

insert into reader values(118,'计算机系','许鹏飞','男','助工','1号楼216');

insert into reader values(119,'计算机系','刘大龙','男','教授','1号楼318');

insert into reader values(120,'国际贸易','李雪','男','副教授','4号楼506');

insert into reader values(121,'国际贸易','李爽','女','讲师','4号楼510');

insert into reader values(122,'国际贸易','王纯','女','讲师','4号楼512');

insert into reader values(123,'财会系','沈小霞','女','助教','2号楼202');

insert into reader values(124,'财会系','朱海','男','讲师','2号楼210');

insert into reader values(125,'财会系','马英明','男','副教授','2号楼212');



-- BORROW表:

insert into borrow values(112,445501,'2006-3-19');

insert into borrow values(125,332211,'2006-2-12');

insert into borrow values(111,445503,'2006-8-21');

insert into borrow values(112,112266,'2006-3-14');

insert into borrow values(114,665544,'2006-10-21');

insert into borrow values(120,114455,'2006-11-2');

insert into borrow values(120,118801,'2006-10-18');

insert into borrow values(119,446603,'2006-11-12');

insert into borrow values(112,449901,'2006-10-23');

insert into borrow values(115,449902,'2006-8-21');

insert into borrow values(118,118801,'2006-9-10');

-- 1)  找出姓李的读者姓名(NAME)和所在单位(COMPANY)。

SELECT NAME,COMPANY

FROM reader

WHERE NAME LIKE '李%';

-- 2) 列出图书库中所有藏书的书名(BOOK_NAME)及出版单位(OUTPUT)。

SELECT DISTINCT BOOK_NAME,OUTPUT FROM book;

-- 3)  查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE)，结果按单价降序排序。

SELECT BOOK_NAME,PRICE FROM book

WHERE OUTPUT = '高等教育出版社' ORDER BY PRICE DESC;

-- 4) 查找价格介于10元和20元之间的图书种类(SORT)，结果按出版单位(OUTPUT)和单价(PRICE)升序排序。

SELECT SORT FROM book

WHERE PRICE BETWEEN 10 AND 20 ORDER BY OUTPUT,PRI

CE;

-- 5) 查找书名以”计算机”开头的所有图书和作者(WRITER)。

SELECT DISTINCT BOOK_NAME,WRITER FROM book

WHERE BOOK_NAME LIKE '计算机%';

-- 6)* 查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。

SELECT DISTINCT R.NAME,R.COMPANY  

FROM reader R, borrow W

WHERE R.READER_ID = W.READER_ID;

或者:

FROM reader R INNER JOIN borrow W

ON R.READER_ID = W.READER_ID;

-- 7) 找出李某所借所有图书的书名及借书日期(BORROW_DATE)。

SELECT B.BOOK_NAME,W.BORROW_DATE

FROM READER R,BOOK B,BORROW W

WHERE R.NAME LIKE '李%' AND R.READER_ID = W.READER_ID AND B.BOOK_ID  = W.BOOK_ID;

或：

SELECT B.BOOK_NAME,W.BORROW_DATE

FROM book B INNER JOIN borrow W

ON B.BOOK_ID  = W.BOOK_ID INNER JOIN reader R

ON R.READER_ID = W.READER_ID 

WHERE R.NAME LIKE '李%';

-- 8) 查询2006年7月以后没有借书的读者借书证号、姓名及单位。

SELECT DISTINCT READER_ID,NAME,COMPANY

FROM reader

WHERE READER_ID NOT IN

  (

    SELECT DISTINCT W.READER_ID

    FROM borrow W

    WHERE W.BOR
    ROW_DATE > '2006-07'

  );

-- 9) 求出各个出版社图书的最高价格、最低价格和总册数。

SELECT MAX(PRICE),MIN(PRICE),COUNT(OUTPUT)

FROM book

GROUP BY OUTPUT;

注意COUNT(*)

SELECT OUTPUT,MAX(PRICE),MIN(PRICE),COUNT(*)

FROM book

GROUP BY OUTPUT;

-- 10) 找出与”赵正义”在同一天借书的读者姓名、所在单位及借书日期 。

SELECT R.NAME,R.COMPANY,W.BORROW_DATE

FROM reader R,borrow W

WHERE R.READER_ID = W.READER_ID AND r.NAME<> '赵正义' AND W.BORROW_DATE = 

       (

              SELECT borrow.BORROW_DATE FROM reader, borrow

              WHERE reader.NAME = '赵正义' and reader.READER_ID = borrow.READER_ID

       );

八）MySQL高级特性

1）什么是mysql高级特性

• mysql高级特性是 mysql对标准sql的一个扩充
• 不止mysql有对sql不成，比如msSQL有T-SQL，Oracle/有PL-SQL，都是对标准SQL的扩充，内容都是大同小异，存在一些语法差异。

2）变量

01设置用户自定义变量

第一种方式：以“@”开始，形式为“@变量名”

SET @nametest = 666

变量可以为数值、字符串等类型，要使用该变量时，直接利用@变量名

的形式即可，如：

SET @nametest；  --打印出值666，类似print

第二种方式：通过SELECT 语句定义变量

写法1： SELECT @variable_name := value;  注意这里:=是，不是=

写法2： SELECT XXX into @variable_name; 注意，这里的xxx必须是返回一行数据的值，比如一个聚合函数一般通过select语句定义时，可以通过sql语句来给变量赋值，方便后面使用，如：

SELECT @maxsal := MAX(sal) from emp; --通过select给变量@maxsal赋值

SELECT MAX(sal) into @maxsal from emp;

SELECT @maxsal; --查询@maxsal的值

select ename,sal from emp where sal=@maxsal; --使用变量@maxsal的值秋最高工资员工的姓名，注意，变量名均不区分大小写。

3)存储过程

概念：

存储过程是存储在数据库目录种的一段声明性SQL语句，存储过程写好后，可以被其他编程语言直接调用，非常方便。

优点：

1. 通常存储过程有助于提高应用程序的性能。有预编译的过程。
2. 存储过程有助于减少应用程序和数据库服务器之间的流量，不必发送冗长的sql语句到数据库，只需要船体存储过程名称和参数。
3. 存储的程序对任何应用程序都是可重用和透明的。
4. 存储的程序是安全的，支持权限控制。

01定义存储过程

DELIMITER //  --DELIMITER两个跟存储过程没有关系，有没有都没有，作用是改变sql分隔符的定义，而sql语句的分隔符默认使用分号，所以会修改默认分隔符，此处修改成了//

CREATE PROCEDURE 存储过程名字()

  BEGIN

    --存储过程代码

  END //

DELIMITER;   --恢复成默认的设置为分号

eg：

DROP PROCEDURE IF EXISTS get_maxsal;

DELIMITER //

CREATE PROCEDURE get_maxsal()

  BEGIN

    SELECT max(sal) FROM emp;

  END //

CALL get_maxsal();  --调用存储过程

02在存储过程中声明并使用变量

• 声明变量：

DECLARE variable_name datatype(size) DEFAULT default_value;

• 变量赋值：

SET 变量名 = 变量值   --注意这里变量前面没有@符号

• 也可以使用sql语句赋值：

SELECT XXX into 变量名 FROM XXXX

EG:

DROP  PROCEDURE IF EXISTS get_Sal;

DELIMITER //

CREATE PROCEDURE get_sal()

  BEGIN

    DECLARE avgsal INT;  --声明变量

    SELECT avg(sal) into avgsal from emp;   --赋值变量

    SELECT ename,sal from emp where sal > avgsal;

  END //



CALL get_sal();

03带参数的存储过程

在mysql中，参数有三种模式：IN，OUT或INOUT。

• IN - 默认模式。在存储过程中定义IN参数时，调用存储过程时必须将参数传递给存储过程。另外，IN参数的值被保护，即IN参数的值在过程中不能发生变化。
• OUT - 可以在存储过程中更高OUT参数的值，并将其更改后新值传递回调用程序。由于OUT参数并没有被赋值，所以不能读取，只能赋值。
• INOUT - INOUT参数时IN和OUT参数的组合。这意味着调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值船体回调用程序。

语法：

IN/OUT/INOUT param_name param_type(param_size)

eg:

DROP PROCEDURE IF EXISTS get_sal;

DELIMITER //

CREATE PROCEDURE get_sal(IN empname VARCHAR(5),OUT empsal INT)

  BEGIN

    SELECT sal INTO empsal from emp WHERE ename=empname;

  END //



--存储过程中定义的参数，会在mysal中自动生成以下划线命名的参数

CALL get_sal('BLAKE',@_empsal);  --调用的时候给参数赋值

SELECT @_empsal;

4）流程控制

可以在存储过程中加入流程控制语句

分支：IF、CASE

循环：REPEAT、WHILE

01 IF判断

1、

IF expression THEN

  statements;

ENDIF;

2、

IF expression THEN

  statements;

ELSE

  else-statements;

ENDIF;

3、

IF expression THEN

  statements;

ELSEIF elseif-expression THEN

  else-statements;

...

ELSE

  else-statements;

ENDIF;  --不能少ENDIF

eg:

DROP PROCEDURE IF EXISTS get_sal;

DELIMITER //

CREATE PROCEDURE get_sal(IN empname VARCHAR(5),OUT sallevel VARCHAR(10))

  BEGIN

    DECLARE empsal INT;

    SELECT sal INTO empsal from emp WHERE ename=empname;

    IF empsal>3000 THEN

      SET sallevel = 'high';

    ELSEIF empsal<=3000 AND empsal>2000 THEN

      SET sallevel = 'middle';

    ELSE

      SET sallevel = 'LOW';

    END IF;

  END //



--存储过程中定义的参数，会在mysal中自动生成以下划线命名的参数

CALL get_sal('BLAKE',@sallevel);  --调用的时候给参数赋值

SELECT @sallevel;

02 CASE

CASE case_expression

  WHEN when_statements_1 THEN commands;

  WHEN when_statements_2 THEN commands;

  ...

  ELSE commands --else语句是可选的

ENDIF;  --不能少ENDIF

eg:

CREATE PROCEDURE p()

  BEGIN

    DECLARE v INT DEFAULT 1;

    CASE v

      WHEN 2 THEN SELECT v;

      WHEN 3 THEN SELECT 0;

    ELSE

      BEGIN

        SELECT 'hello';

      END;  --这里的 BEGIN..END代表是一个语句块，可以没有

    END CASE;

  END



CALL p();

03 REPEAT

REPEAT

  statement_list

UNTIL search_condition

END 

eg：打印指定开始和结束数字的和

DROP PROCEDURE IF EXISTS dorepeat;

CREATE PROCEDURE dorepeat(in b INT,in e INT)

  BEGIN

    DECLARE total INT DEFAULT 0;

    DECLARE TEMP INT DEFAULT B;

    REPEAT

      set total = total + temp;

      set temp = temp + 1;

    UNTIL temp>e

    END REPEAT;

    SELECT total;

  END



CALL dorepeat(1,10);

04 WHILE

WHILE search_condition DO

  statement_list

END WHILE

eg：

CREATE PROCEDURE dowhile()

BEGIN

  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO

    SELECT 'hello';

    SET v1 = v1-1;

  END WHILE;

END



CALL dowhile();

05跳出循环

• ITERATE：只能出现在Loop、repeat、while语句中，用于开始下次循环，类似continue

ITERATE lable

• LEAVE：通过指定的喇叭了来退出流程控制块，如果lable是在最外面的程序块则退出该程序。可以在begin...end和循环结构中使用。相当于break。

LEAVE lable

eg:

CREATE PROCEDURE testITERATE(p int)

BEGIN

  outw:while(true) DO

    set p = p+1;

    if p=5 THEN

      ITERATE outw; --不执行下面的语句，又从 outw:while(true) DO继续执行

    ELSEIF p=10 THEN

      LEAVE outw; --退出循环outw

    END IF;

  END WHILE outw;

  SELECT p;

END



--待用testITERATE

SET @a=2;

CALL testITERATE(@a);

eg2：打印5，3

CREATE PROCEDURE pro_repeat()

BEGIN

  DECLARE P INT DEFAULT 6;

  outw:REPEAT

    set p = p-1;

    if p=4 THEN

      ITERATE outw; --不执行下面的语句，又从 outw:while(true) DO继续执行

    ELSEIF p=2 THEN

      LEAVE outw; --退出循环outw

    END IF;

    SELECT p;

  UNTIL p<1

U 

  END REPEAT outw;

END



CALL pro_repeat();

5）练习题

3. 编写一个存储过程，该存储过程接受一个部门编号作为参数，可以求出该部门的员工总数。

CREATE PROCEDURE get_total(IN _deptno INT, OUT _total INT)

BEGIN

       SELECT COUNT(*) INTO _total FROM emp WHERE deptno=_deptno;

END



CALL get_total(20,@emptotal);

SELECT @emptotal;

6）触发器

概述：

• 触发器是一种特殊类型的存储过程，不由用户直接调用，而且可以包含复杂的SQL语句。他们主要用于强制复杂的业务规则或要求。
• 触发器还有助于强制引用完整性，一边在添加、更新或删除表中的行时保留表之间已定义的关系。

特点：

• 它与表紧密相连， 可以看作定义表的一部分。
• 它不能通过名称被直接调用，更不允许带参数，而是当用户对表中的数据进行修改时，自动执行；
• 它可以用于Mysql约束、默认值和规则的完整性检查，实施更为复杂的数据完整性约束。

语法：

CREATE TRIGGER trigger_name

trigger_timer trigger_event ON tbl_nmae FOR ENCH ROW --表示每操作一行就会触发一次

BEGIN

  routine_body

END

#trigger_timer:{BEFORE|AFTER}

#trigger_event:{INSERT|UPDATE|DELETE}

注：

• 不能有返回值或返回结果集。
• MYSQL的触发器中不能对本表进行insert、update、delete操作，以免递归循环触发。

触发器有两个特殊的变量：old和new

01 old和new的使用

• 当使用insert语句的时候，如果原表中没有数据的话，那么对于插入数据后表来说新插入的那条数据就是new。
• 当使用update语句时，当修改原表数据的时候，相当于修改数据后表的数据来说原表中修改的那条数据就是old，而修改数据后表被修改的那条数据就是new。
• 当使用delete语句时，删除的那一条数据相当于删除数据后表的数据来说就是old。

语法：

• 删除

DROP TRIGGER [IF EXISTS] trigger_name;

• 查看触发器

SHOW TRIGGERS;

执行触发器监听操作，不需要显示调用。

eg：

• 向emp表插入新数据时，如果comm大于sal，则将comm设置为跟sal意义的值。

CREATE TRIGGER tri_comm BEFORE INSERT ON emp FOR EACH ROW

--每次向emp表插入数据事件之前，就会触发触发器

BEGIN

  if new.comm>new.sal THEN  --新插入的数据comm>sal时

    SET new.comm = new.sal;

  END IF;

END

即使小于sal也会触发触发器。

• 删除dept表中华油员工的部门，提示错误信息。

CREATE TRIGGER tri_deptno BEFORE DELETE ON depat FOR EACH ROW

BEGIN

  IF old.deptno<>40 THEN

  --SIGNAL SQLSTATE 'HY000'：抛出异常信息 MESSAGE_TEXT：固定写法

    SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '不能删除还有员工的部门信息';  --必须写在一句上

  END IF;

END



DELETE FROM dept WHERE deotpo=30;

02练习题

1.利用sql复制一份emp表，生成一个名叫emp1的表。

CREATE TABLE 新表名 AS (SELECT * FROM 被赋值表名);

2.在emp1上建立一个触发器，要求在更新emp1的数据时，如果该记录的comm为null，则更新后自动变成0，如果该记录的comm小于sal，则更新后comm等于sal。

CREATE TRIGGER tri_emp BEFORE UPDATE ON emp1 FOR EACH ROW

BEGIN

       IF old.comm is NULL THEN

              SET new.comm = 0;

       ELSEIF old.comm < old.sal THEN

              SET new.comm = new.sal;

       END IF;

END



select * from emp1;

UPDATE emp1 SET deptno = 30 WHERE empno=7369 --old.comm is NULL

UPDATE emp1 SET deptno = 40 WHERE empno=7499 --old.comm < old.sal

7）事务

概述：

• 事务是作为单个逻辑的单元执行的一系列操作。
• 多个操作作为一个整体向系统提交，要么执行、要么都不执行，事务是一个不可分割的工作逻辑单元。
• 着特别适用于多用户同时操作的数据通信系统。例如：订票、银行、保险公司以及证券交易系统等。

注意：

• MySAM：不支持事务，用于只读程序提高性能。
• InnoDB：引擎不能结构化编程，只能通过标记开启、提交或回滚事务。

01 事务的特性

（Atom,Constant,Isoation,Duration）

• 原子性：组成事务处理的语句形成 了一个逻辑单元，不能只执行其中的一部分。即，是不是不可分割的最小单元。比如：银行转账过程中，必须同时从一个账户减去转账金额，并加到另一个账户中，只改变一个账户是不合理的。
• 一致性：在事务处理执行前后，数据库是一致的。即，事务应该正确的转换系统状态。比如：银行转账过程中给，要么转金额从一个账户转入另一个账户，要么两个账户都不变，没有其他的情况。
• 隔离性：一个事务处理对另一个事务处理没有影响。即，任何事务都不可能看到一个处在不完整状态下的事务。比如：银行转账过程中，在转账事务没有提交之前，另一个转账事务只能处于等待状态。
• 持久性：事务处理的效果能够被永久保存下来。反过来说，事务应当能够承受所有的失败，包括服务器、进程、通信以及媒体失败等等。比如：银行转账过程中，转账后账户的状态要能被保存下来。

02 常见事务控制语句

• BEGIN或START TRANSACTION ；显式地开启一个事务；
• COMMIT：用于提交事务，并使已对数据库进行的所有修改称为永久性的。
• ROLLBACK：用于回滚事务，回滚结束用户的事务，并撤销正在进行的所有未提交的修改。

03事务的处理方式

BEGIN;

  INSERT INTO dept VALUES(50,'TEST','CHINA');

  INSERT INTO dept VALUES(60,'JAVA','CHENGDU');

COMMIT;  --提交事务

或：

ROLLBACK; --回滚事务

九）Python操作数据库

1）连接

安装：pip install PyMySQL

格式：

import pymysql



#打开数据库

db = pymysql.connect(host="ip地址，本机是localhost",user="root",password="xxxx",database="xxx")

#适用cursor()方法创建一个游标对象cursor

cursor = db.cursor()

#使用execute()方法执行SQL查询

cursor.execute('SELECT VERSION()')  #要执行的sql语句

#使用fetchone()方法获取单条数据

data = cursor.fetchone()

print("Database version:%s"%data)

#关闭数据库连接

db.close()

使用with语法：

#打开数据库

with pymysql.connect(host="localhost",user="root",password="123456",database="study") as db:

    # 适用cursor()方法创建一个游标对象cursor

    cursor = db.cursor()

    # 使用execute()方法执行SQL查询

    cursor.execute('SELECT VERSION()')  # 要执行的sql语句

    # 使用fetchone()方法获取单条数据

    data = cursor.fetchone()

    print("Database version:%s" % data)

    # 关闭数据库连接

    #db.close()  不需要close

参考：

Python 数据库的Connection、Cursor两大对象

python中with...as的用法

eg:

with pymysql.connect(host="localhost",user="root",password="123456",database="study") as db:

    # 适用cursor()方法创建一个游标对象cursor

    cursor = db.cursor()

    # 使用execute()方法执行SQL查询

    cursor.execute('DROP TABLE IF EXISTS mytable')  # 要执行的sql语句

    sql = """

        CREATE TABLE mytable(

            FIRST_NAME CHAR(20) NOT NULL,

            LAST_NAME CHAR(20),

            AGE INT

        )

    """

    cursor.execute(sql)

eg2:

with pymysql.connect(host="localhost",user="root",password="123456",database="study") as db:

    # 适用cursor()方法创建一个游标对象cursor

    cursor = db.cursor()

    # 使用execute()方法执行SQL查询

    sql = """

        INSERT INTO mytable(FIRST_NAME,LAST_NAME,AGE)

        VALUES('%s','%s','%s')

    """%('Mac','Mohan',20)

    try:

     cursor.execute(sql)

     db.commit()     #事务不会自动提交，故需要commit一下，但mysql5.0以上的版本事务是自动提交的，没有定义十事务的时候也是自动提交的，可以省略

     print('插入成功！')

    except:

        db.rollback()

        print('插入失败，回滚')

eg3:

with pymysql.connect(host="localhost",user="root",password="123456",database="study") as db:

    # 适用cursor()方法创建一个游标对象cursor

    cursor = db.cursor()

    # 使用execute()方法执行SQL查询

    sql = """

        SELECT* FROM emp WHERE sal>%s

    """%(1000)

    try:

     cursor.execute(sql)

     results = cursor.fetchall()  #还有一个cursor.fetchone()方法

     print('结果数据共{}条'.format(cursor.rowcount))

     for row in results:

         print(row)

    except:

        print('发生错误，无法查询数据')

结果以元组形式显示出来了：

2）通过python调用存储过程

01 存储过程不带参数

import pymysql



#打开数据库

db = pymysql.connect(host="localhost",user="root",password="123456",database="study")

#适用cursor()方法创建一个游标对象cursor

cursor = db.cursor()

#使用execute()方法执行SQL查询

cursor.callproc('get_maxsal')  #要执行的sql语句

#使用fetchone()方法获取单条数据

result = cursor.fetchall()

print(cursor.rowcount) #显示结果的行数

print(result)

#关闭数据库连接

db.close()

没有参数：

有参数的get_sal:

报错：

pymysql.err.OperationalError: (1318, 'Incorrect number of arguments for PROCEDURE study.get_sal; expected 2, got 0')

02 存储过程带参数

import pymysql

#打开数据库

db = pymysql.connect(host="localhost",user="root",password="123456",database="study")

#适用cursor()方法创建一个游标对象cursor

cursor = db.cursor()

#对于out和inout参数python不支持，随便定义一个值即可

cursor.callproc('get_sal',('BLAKE',0))  #要执行的sql语句

#对于out和inout型的参数，是保存在服务器的变量中的，可以通过select语句查询

#对应的参数访问格式为@_存储过程名_0,@_存储过程名_1，以此类推

cursor.execute('SELECT @_get_sal_0,@_get_sal_1')

print(cursor.fetchall()) #显示结果的行数

#关闭数据库连接

db.close()

有参数：