五、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)总结
- 使用数据库,可以降低存储数据的冗余度,提供更高的数据一致性、数据完整性和安全性
- 数据是以表的形式存储于数据库中,表由行和列组成,类似于excel中的数据表
- 表中的一行叫作一条记录,对应一个实体的信息。表中的一列对应一个实体的属性,通常称为“域”或“字段”
- 数据库对数据的管理主要体现在约束上,常见的约束有实体完整性、参照完整性以及 自定义完整性约束
- 主键用于唯一地标识某一个实体,主键不能为空,也不能重复
- 外键是关联到主表的一个主键,主要用于约束关联字段的取值范围
- 自定义约束并不是强制的,可以根据需要进行设置
- SQL四关系型数据库的标准编程语言,可以通过编写SQL语句访问和操纵数据库中的数据
- SQL标准定义了操作关系型数据库及其包含的数据的多种技术,它分为DQL、DML、DDL、DCL。
- 不同的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 数据库名;
- 修改数据库编码集
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主表时的行为?
即指定外键删除或更新的行为:
- 若子表试图创建一个在父表种不存在的外键值,InnoDB会拒绝任何INSERT或UPDATE操作。
- 若父表试图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:模糊匹配
- 使用LIKE运算符执行通配拆线呢
- 查询条件可包含文字字符或数字
- (%)可表示零或多个字符
- (_)可表示一个字符
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)多表连接查询分类
- 内连接(INNER JOIN) --用 都比较多
- 外连接
- 左外连接(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语句,存储过程写好后,可以被其他编程语言直接调用,非常方便。
优点:
- 通常存储过程有助于提高应用程序的性能。有预编译的过程。
- 存储过程有助于减少应用程序和数据库服务器之间的流量,不必发送冗长的sql语句到数据库,只需要船体存储过程名称和参数。
- 存储的程序对任何应用程序都是可重用和透明的。
- 存储的程序是安全的,支持权限控制。
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两大对象
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()
有参数: