MySQL学习笔记,供参考
1、初识MySQL
JavaEE : 企业级Java开发 Web结构:
前端 :(页面 :展示,数据!)
后台 :(连接点 :链接数据库JDBC,连接前端(控制,控制试图跳转,和给前端传 递数据))
数据库(存数据,Text,word,Excel)
只会写代码,基本混饭吃!
操作系统,数据结构与算法,当一个不错得程序员!
离散数学,数字电路,体系结构,编译原理。+实战经验, 高级的优秀程序员
1.1、 为什么学习数据库
1、岗位需求
2、现在的世界,大数据时代~,得数据者得天下。
3、被迫需求:存数据
4、数据库是所有软件体系种最核心得存在 DBA
1.2、什么是数据库
数据库(DB,Database)
概念:数据仓库,软件,安装在操作系统(Windows,Linux,mac、…)之上!SQL,可以存储大量数据。500万!
作用:存储数据,管理数据
1.3、数据库分类
关系型数据库 : (SQL)
- MySQL,Oracle,SQL Server,DB2,SQLlite
- 通过表和表之间,行和列之间的关系进行数据的存储, 学院信息表,考勤表,…
非关系型数据库 :(NoSQL) Not Only
- Redis,MongDB
- 非关系型数据库,对象存储,通过对象的自身的属性来决定。
DBMS(DataBase Management System ) 数据库管理系统
- 数据库的管理软件,科学有效的管理我们的数据。维护和获取数据;
1.4、MySQL简介
MySQL是一个关系型数据库管理系统
前世:瑞典MySQL AB公司
今生:属于Oracle旗下产品
MySQL是最好的RDBMS(Relational DataBase Management System,关系型数据库管理系统)应用软件之一。
开源的数据库软件~
体积小、速度快、总体拥有成本低,招人成本比较低,所有人必须会~
中小型网站、或者大型网站,集群!
官网:https://www.mysql.com
安装建议:
1、尽量不要用exe,会添加到注册表种,卸载比较麻烦
2、尽可能使用压缩包安装,只需要自己配下环境变量就行~
1.5、安装MySQL
教程:
1、解压
2、把压缩包放到电脑目录下,要记住放哪里了~
3、配置环境变量
4、新建mysql.ini配置文件
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:/mysql/mysql-8.0.28-winx64
# 设置mysql数据库的数据的存放目录 (data文件夹如果没有的话会自动创建)
datadir=D:/mysql/mysql-8.0.28-winx64/data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
# 跳过密码认证
skip-grant-tables
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
5、启动cmd(管理员模式)
6、进入解压文件夹种的bin目录
cd /d D:\mysql\mysql-8.0.28-winx64\bin # 这是我自己的目录
7、初始化MySQL
mysqld --initialize-insecure
8、安装MySQL
mysqld --install
9、启动MySQL服务
net start mysql # 启动
net stop mysql # 停止
# 如果安装失败了,以下命令删除mysql服务,重新按照以上步骤进行操作
sc delete mysql
10、进入MySQL,并修改密码
mysql -uroot -p -- 进入命令,不输密码,跳过验证
-- 进入后,将密码修改为123456
mysql>update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
mysql>flush privileges;
11、注释掉ini配置文件种的跳过密码认证:skip-grant-tables
12、将MySQL目录种的bin文件夹加入环境变量的Path变量中~
13、重启MySQL服务,连接输入密码进入MySQL,大功告成!
1.6、连接数据库
命令行连接:
mysql -uroot -p123456
-- 出现mysql>的字样则连接成功!
-- ---------------------------------
-- 所有语句使用;结尾
mysql>show databases; -- 查看所有数据库
mysql>use mysql; -- 切换数据库
mysql>show tables; -- 查看数据库所有的表
mysql>describe student; -- 查看当前数据库中的名为student的表结构
mysql>create database school; -- 创建一个名为school的数据库
mysql>exit --退出连接
-- 单行注释
/*
多行
注释
*/
数据库语言:
DDL 数据库定义语言
DML 数据库**管理(操作)**语言
DQL 数据库查询语言
DCL 数据库控制语言
2、操作数据库
数据库 > 库中表 > 表中数据(字段,值)
MySQL关键字不区分大小写
2.1、了解基本命令概念
1、创建数据库
mysql>create database [if not exists] school;
2、使用/切换数据库
-- 为了避免和和特殊字符重合,我么使用自己定义的字符时尽量用``符号括起来(TAB键的上面)
mysql>use school;
3、删除数据库
mysql>drop database [if exists] school;
学习思路:
在SQLyoug中使用后,对比SQLyog的历史记录,可以让我们快速的查看命令
常用的语法和关键字须记住!
2.2、数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节 常用
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算的时候,一般用decimal
字符串
- char 字符串固定的大小 0~255
- varchar 可变长的字符串 0~65535 相当于java中的String 常用
- tinytext 微型文本 2^8 - 1
- text 文本串 2^16 - 1 保存大文本
时间日期
java.util.Date
- date YYYY-MM-DD,日期格式
- time HH:MM:ss,时间格式
- datetime YYYY-MM-DD HH:MM:ss 最常用的时间格式
- timestamp 时间戳, 1970年1月1日到现在的毫秒数! 也较为常用!
- year 年份
NULL
- 没有值,未知
- 不要使用NULL进行运算,因为结果还是NULL!
2.3、数据库的字段属性(重点)
Unsigned:
- 无符号的整数
- 不能为负!
zerofill:
- 0填充
- 不足的位数,用0填充,如int(5), 5---->00005
自增(AUTO_INCREMENT):
- 通常理解为自增,自动在上一条记录上 + 1(默认)
- 通常用来设置唯一的主键,index,必须是整型
- 可以自定义设置主键自增的起始值和步长
非空: NOT NULL,NULL
- 设置为NOT NULL,如果不赋值,则会报错!
- 设置为NULL,如果不填,默认为NULL!
DEFAULT(默认):
- 设置默认的值
- sex,设置默认值为男,如果不指定该列的值,则默认男!
拓展:
/* 每一个表,都必须存在以下五个字段!(未来做项目用的,表示每一个记录存在意义!)
这是阿里巴巴规范里面的
id 主键
`verson` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4、创建数据库表(重点)
CREATE 的语法:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
[(
COLUMN_DEFINITION,(这个有可选属性)
...
)]
[TABLE_OPTIONS]
[SELECT_STATEMENT];
-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段) 使用SQL创建
-- 学号int 登录密码varchar(20) 姓名varchar(30),性别varchar(2),出生日期(datetime),家庭住址(address), 邮箱email
-- 注意,使用英文(),表的名称和字段尽量用``括起来
-- AUTO_INCREMENT自增
-- 字符串用单引号或双引号括起来!
-- 所有的语句后面加,(英文的),最后一个不加
-- PRIMARY KEY 主键,一般一个表只有一个唯一的主键!
CREATE TABLE `school`(
`id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL COMMENT '姓名',
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME NOT NULL COMMENT '出生日期',
`address` VARCHAR(100) NULL COMMENT '家庭住址',
`email` VARCHAR(20) NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE = INNODB, CHARSET = utf8;
格式
create table `表名`(
`字段名` 数据类型 [属性] [索引] [注释],
... ,
`字段名` 数据类型 [属性] [索引] [注释]
)ENGINE = 引擎名, CHARSET = 字符集, [注释];
2.5、数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用~
MYISAM 早些年使用的
*/
引擎类型 | MYISAM | INNODB |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
本质还是文件的存储!
MySQL引擎在物理文件上的区别:
- INNODB在数据库表中只要一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM对应文件:
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
设置数据库表的字符集编码
CAHRSET = utf8
不设置的话,会是MySQL默认的字符集编码~(不支持中文!)
MySQL的默认编码是Latin1,不支持中文
可以在my.ini配置文件中设置默认编码:
character-set-server = utf8
2.6、修改删除表
修改
-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE `school` RENAME AS `school1`;
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性;
ALTER TABLE school1 ADD `phone` INT(11);
-- 修改表的字段 (重命名,修改约束!)
ALTER TABLE `school1` CHANGE `phone` `phone1` INT(1);-- 重命名
ALTER TABLE `school1` MODIFY `phone` VARCHAR(11);-- 修改约束
-- 删除表的字段
ALTER TABLE `school1` DROP `phone1`;
删除
-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS `school1`;
所有的创建和删除操作尽量加上判断,以免报错~
注意点:
- `` 所有的字段名用这个符号括起来
- 注释 – /**/
- SQL语句不区分大小写,建议用小写
- 所有符号用英文
3、MySQL数据管理
3.1、外键(了解即可)
在创建表的时候,增加约束(麻烦,比较复杂)
CREATE TABLE `grade`(
`gradeid` INT NOT NULL AUTO_INCREMENT COMMENT '班级id',
`gradename` VARCHAR(30) NOT NULL COMMENT '班级名称',
PRIMARY KEY(`gradeid`)
)ENGINE = INNODB, CHARSET = utf8;
-- student表的gradeid字段,要去引用年级表的gradeid字段
-- 定义外键key
-- 给外键增加约束(执行引用),references 引用
CREATE TABLE `student`(
`id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL COMMENT '姓名',
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME NOT NULL COMMENT '出生日期',
`gradeid` INT NOT NULL COMMENT '班级id',
`address` VARCHAR(100) NULL COMMENT '家庭住址',
`email` VARCHAR(20) NULL COMMENT '邮箱',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE = INNODB, CHARSET = utf8;
删除又外键关系的表的时候,必须要先删除引用别人的表(从表),再删除被引用的表(父表)
方式二 :创建表成功之后,增加外键约束
CREATE TABLE `grade`(
`gradeid` INT NOT NULL AUTO_INCREMENT COMMENT '班级id',
`gradename` VARCHAR(30) NOT NULL COMMENT '班级名称',
PRIMARY KEY(`gradeid`)
)ENGINE = INNODB, CHARSET = utf8;
CREATE TABLE `student`(
`id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL COMMENT '姓名',
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME NOT NULL COMMENT '出生日期',
`gradeid` INT NOT NULL COMMENT '班级id',
`address` VARCHAR(100) NULL COMMENT '家庭住址',
`email` VARCHAR(20) NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE = INNODB, CHARSET = utf8;
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
-- ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表字段) REFERENCES 主表名 (主表字段)
以上的操作都是物理外键,数据库级别的外键,我们不建议使用!(避免数据库过多造成困扰)
最佳实践:
- 数据库就是单纯的表,只用来存储数据,只要有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
3.2、DML语言(全部记住)
**数据库意义:**数据存储,数据管理
DML语言:数据操作(管理)语言
- Insert
- Update
- Delete
3.3、添加 ( INSERT )
insert 语法:
INSERT [LOW_PRIORITY |DELAYED| HIGH_PRIORITY]
[IGNORE][INTO]tbl_name[(col_name,...)]
VALUES ({expr| DEFAULT},...),(...),...
[ON DUPLICATE KEY UPDATEcol_name=expr,... ]
INSERT [LOW_PRIORITY |DELAYED| HIGH_PRIORITY]
[IGNORE][INTO]tbl_name
SET col_name={expr| DEFAULT},... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY]
[IGNORE][INTO]tbl_name[(col_name,...)]
SELECT ...[ON DUPLICATE KEY UPDATEcol_name=expr,... ]
-- 插入语句(添加)
-- insert into 表名([字段1],[字段2],[字段3],...) values('值1','值2','值3',...);
INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二'),('大三'),('大四');
-- 一个字段插入多个值,每个值用括号括住,并用逗号分开
-- 一般写插入语句,我们一定要将字段和值一一对应!
-- 插入多个字段
INSERT INTO `student`(`name`,`password`,`sex`)
VALUES('赵六','aaaaaa','男'),('吴七','bbbbbb','女'),('沈八','cccccc','男');
语法:insert into 表名([字段1],[字段2],[字段3],...) values('值1','值2','值3',...);
注意事项:
1. 字段和字段之间使用英文隔开
1. 字段是可以省略的,但是后面的值必须一一对应,一个字段也不能少
1. 可以同时插入多条数据,VALUES后面的值,先括住,再用逗号隔开即可 `VALUES(),(),(),...`
3.4、修改(UPDATE)
update 语法:
-- ================ 单表更改 ===================
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
-- ================ 多表更改 ===================
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
-- 修改学生名字,带条件
UPDATE `student` SET `name` = '东方红' WHERE `name` = '张三';
-- 修改学生名字,不带条件,会全部修改!
UPDATE `student` SET `name` = '东方红';
-- 修改多个属性,用逗号隔开
UPDATE `student` SET `name` = '黄河',`email` = '1059615162@qq.com' WHERE `id` = 1;
-- 语法:
-- UPDATE 表名 SET column_name = value,[column_name = value,......] [where 条件]
条件:where子句 运算符 id等于某个值,大于某个值,在某个区间内修改…
操作符会返回 布尔值
操作符 | 含义 | 例子 | 结果 |
---|---|---|---|
= | 等于 | 5 = 6 | false |
<>或!= | 不等于 | 5 <> 6 或5!= 6 | true |
> | 大于 | 5 > 6 | false |
< | 小于 | 5 < 6 | true |
>= | 大于等于 | 5 >= 6 | false |
<= | 小于等于 | 5 <= 6 | true |
BETWEEN…AND… | 在某个范围内(闭区间) | BETWEEN 5 AND 6 | [5,6] |
AND | 和(&&) | 5 < 6 AND 3 > 5 | false |
OR | 或(||) | 5 < 6 AND 3 > 5 | true |
-- 通过多条件定位数据
UPDATE `student` SET `name` = '长江' WHERE `name` = '东方红' AND `sex` = '女';
语法:UPDATE 表名 SET column_name = value,[column_name = value,......] [where 条件];
注意:
- column_name 是数据库的列,尽量带上``
- 条件,筛选的条件,如果没有指定,则会修改所有的列
- value,是一个具体的值,也可以是一个变量
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `id` BETWEEN 2 AND 4;
3.5、删除
delete 命令
语法:delete from 表名 [where 条件]
-- 删除数据(避免这样操作,删库跑路)
delete from `student`;
-- 删除指定数据
delete from `student` where `id` = 1;
TRUNCATE 命令
作用:完全清空一个数据表,表的结构和索引不会变!
-- 清空student 表
TRUNCATE `student`;
delete 和 truncate 的区别
- 相同点:都能删除表中数据,且不会删除表结构
- 不同点:
- TRUNCATE 会重新设置自增列,计数器归零
- TRUNCATE 不会影响事务
-- 测试DELETE 和 TRUNCATE 的区别
CREATE TABLE `test`(
`id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
`coll` VARCHAR(30) NOT NULL COMMENT 'coll',
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `test` (`coll`) VALUES('1'),('2'),('3');
DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test` -- 自增会归零
DELETE删除的问题
删除后重启数据库的现象:
- INNODB 自增列会重新从1开始计数(存在内存中的,断电即失)
- MYISAM 自增列会从上一次的自增量开始计数(存在文件中的,不会丢失,除非硬盘损坏)
4、DQL查询数据(最重点)
4.1、DQL的**重要性**
( Data Query Language : 数据查询语言 )
- 所有的查询操作都用它 Select
- 简单的查询,复杂的查询它都能做~
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
straight distinct high_priorty rollup
Select 的完整语法:
SELECT
[ALL | DISTINCT | DISTINCTROW]
[HIGH_PRIORTY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [,select_expr ...]
[FROM table_references
PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]
4.2、查询指定字段
-- 查询全部学生 SELECT 字段 FROM 表名
SELECT * FROM `student_information`;
-- 查询指定字段
SELECT `name` FROM `student_information`;
-- 别名,给结果起一个名字,可以给起别名,也可以给表起别名
SELECT `name` AS 姓名,`favorite` AS 兴趣爱好 FROM `student_information`;
-- 函数 CONCAT() 字符串拼接函数
SELECT CONCAT('姓名:',`name`,' 兴趣爱好:',`favorite`) AS 新名字 FROM `student_information`;
语法:SELECT 字段,... from 表名
有的时候,列名字不是那么的见名知意,所以我们可以起别名
关键字:AS
用法:
- 字段名 AS 别名
- 表名 AS 别名
去重 distinct
作用:去除SELECT查询出来的结果中重复的数据,只显示一条
-- 查询一下哪些同学参加了考试
SELECT * FROM `student_score` -- 查看成绩表
SELECT `s_id` FROM `student_score` -- 查询有哪些同学参加了考试
SELECT DISTINCT `s_id` FROM `student_score` -- 发现重复数据,去重
数据库的列(表达式)
-- 查询一下哪些同学参加了考试
SELECT * FROM `student_score` -- 查看成绩表
SELECT `s_id` FROM `student_score` -- 查询有哪些同学参加了考试
SELECT DISTINCT `s_id` FROM `student_score` -- 发现重复数据,去重
SELECT VERSION() -- 查看系统版本号(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)
-- 查看学院考试成绩并+1分
SELECT `s_id`,`score`+1 AS 提分后 FROM `student_score`
数据库中的表达式: 文本值,列,NULL,函数,计算表达式,系统变量,… …
select 表达式
from 表名
4.3、where条件子句
作用:检索数据中符合条件
的值
搜索的条件由一个或多个表达式组成!结果 布尔值
逻辑运算符
运算符 | 含义 | 描述 |
---|---|---|
AND && | a AND b a && b | 逻辑与,全部为真,结果为真 |
OR || | a OR b a || b | 逻辑或,一个为真,结果为真 |
NOT ! | NOT a !a | 逻辑非,真为假,假为真 |
尽量使用英文字母
-- ======================= where =============================
SELECT `s_id`,`score` FROM `student_score`
-- 查询考试成绩在60-75之间
SELECT `s_id`,`score` FROM `student_score`
WHERE `score`>=60 AND `score`<=75
-- and &&
SELECT `s_id`,`score` FROM `student_score`
WHERE `score`>=60 && `score`<= 75
-- 模糊查询(基于区间)
SELECT `s_id`,`score` FROM `student_score`
WHERE `score` BETWEEN 60 AND 75
-- 除了12341学生外的学生成绩
SELECT `s_id`,`score` FROM `student_score`
WHERE `s_id` != 12341
-- != NOT
SELECT `s_id`,`score` FROM `student_score`
WHERE NOT `s_id` = 12341
模糊查询
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a IS NULL | a为NULL,结果为真 |
IS NOT NULL | a IS NOT NULL | a不为NULL,结果为真 |
BETWEEN…AND… | a BETWEEN b AND c | a在[ b , c ]之间,结果为真 |
LIKE | a LIKE b | SQL匹配,如果a匹配b,结果为真 |
IN | a IN (a1, a2, a3, … ) | 假设a在a1,a2,a3其中的某一个值中,结果为真 |
-- ======================= 模糊查询 =============================
-- like结合 通配符 %(0 - 任意个字符),_(一个字符)
-- 查询姓康的学生
SELECT `id`,`name` FROM `student_information`
WHERE `name` LIKE '康%';
-- 查询康姓且两字的学生
SELECT `id`,`name` FROM `student_information`
WHERE `name` LIKE '康_';
-- 查询姓赵的学生
SELECT `id`,`name` FROM `student_information`
WHERE `name` LIKE '赵%';
-- 查询detail中含有‘健康’的学生
SELECT `id`,`name` FROM `student_information`
WHERE `detail` LIKE '%健康';
-- =================== IN (具体的一个或多个的值)===================
-- 查询id为12341,12342学员
SELECT `id`,`name` FROM `student_information`
WHERE `id` IN (12341,12342);
-- 查询age为19和18的学生
SELECT `id`,`name` FROM `student_information`
WHERE `age` IN (18,19);
-- IS NULL,IS NOT NULL
-- 查询detail不为空的学生
SELECT `id`,`name` FROM `student_information`
WHERE `detail` IS NOT NULL;
-- 查询detail为空的学生
SELECT `id`,`name` FROM `student_information`
WHERE `detail` IS NULL;
4.4、连表查询
Join对比
-- ============ Join 对比 ==============
-- 查询学号,姓名,分数
-- Inner Join
SELECT si.`id`,`name`,`score`
FROM `student_information` si
INNER JOIN `student_score` ss
ON si.`id` = ss.`s_id`
-- Left Join
SELECT si.`id`,`name`,`score`
FROM `student_information` si
LEFT JOIN `student_score` ss
ON si.`id` = ss.`s_id`
-- Right Join
SELECT si.`id`,`name`,`score`
FROM `student_information` si
RIGHT JOIN `student_score` ss
ON si.`id` = ss.`s_id`
操作 | 描述 |
---|---|
Inner Join | 返回两个表中共同有的值,只有一个表有则不返回 |
Left Join | 会从左表中返回所有的值,即使右表中没有匹配(返回NULL嘛) |
Right Join | 会从右表中返回所有的值,即使左表中没有匹配(还是返回NULL) |
-- ============================== 连表查询(Join) =======================================
/*思路:
1. 分析需求,分析查询的字段来自哪些表
2. 确定使用哪种连接(7种之一)
3. 确定交叉点(两个表之间的共同列数据)
4. 判断的条件 :学生信息表中的id = 学生分数表中的s_id
条件语法有两个:
1. Join (连接的表)on (判断条件) 固定语法 --连接查询
2. Where --等值查询
*/
-- 双表查询(学号,姓名,分数编号,分数)
-- 这里用的Inner Join查询(也可以使用另外两种,不同点就是基于哪张表而已)
SELECT si.`id`,`name`,ss.`id`,`score`
FROM `student_information` si
INNER JOIN `student_score` ss
ON si.`id` = ss.`s_id`
-- 三表查询(学号,姓名,科目名称,分数)
/*思路:
先将查询的字段放在两张表查,查出来再加上下一张表的字段,
一层一层往下走。
*/
SELECT si.`id`,`name`,`major_source`,`score`
FROM `student_information` AS si
INNER JOIN `student_score` AS sc
ON si.`id` = sc.`s_id`
INNER JOIN `student_source` AS so
ON sc.`so_id` = so.`id`
自连接
父类:
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类:
pid | categoryid | categoryName |
---|---|---|
3 | 4 | 数据库 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
2 | 8 | 办公信息 |
查询父类对应的子类关系:
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库,web开发 |
美术设计 | ps技术 |
-- ================== 自连接查询 =======================
-- 方式一 (Where)
SELECT a.`categoryName` AS 父项,b.`categoryName` AS 子项
FROM `category` a,`category` b
WHERE a.`categoryid` = b.`pid`
-- 方式二 (Inner Join),不可以用Left/Right Join
SELECT a.`categoryName` AS 父项,b.`categoryName` AS 子项
FROM `category` a
INNER JOIN `category` b
ON a.`categoryid` = b.`pid`
4.5、分页和排序
排序 语法 :ORDER BY 列数据 ASC / DESC
-- 排序 : 升序 ASC,降序 DESC
-- 语法 :ORDER BY 列数据 ASC/DESC
-- 根据成绩结果排序(降序)
SELECT `sin`.`id`,`name`,`major_source`,`score`
FROM `student_information` `sin`
INNER JOIN `student_score` `ssc`
ON `sin`.id = `ssc`.`s_id`
INNER JOIN `student_source` `sso`
ON `ssc`.`so_id` = `sso`.`id`
ORDER BY `score` DESC
分页 语法 :LIMIT 数据起始下标 pageSize
/* 假设有100万条数据,为何要进行分页?
1. 缓解数据库压力
2. 给人更好的体验
也有不分页的,叫瀑布流
如:百度搜图片,刷抖音,刷快手(划到最下面,会自动加载)
*/
-- 每页只显示三条数据
-- 语法 :LIMIT 数据起始下标, 页面大小
SELECT `sin`.`id`,`name`,`major_source`,`score`
FROM `student_information` `sin`
INNER JOIN `student_score` `ssc`
ON `sin`.id = `ssc`.`s_id`
INNER JOIN `student_source` `sso`
ON `ssc`.`so_id` = `sso`.`id`
ORDER BY `sin`.`id` DESC
-- ORDER BY `score` DESC
LIMIT 0,3
-- 第一页 limit 0,3 (1-1)*3
-- 第二页 limit 3,3 (2-1)*3
-- 第三页 limit 6,3 (3-1)*3
-- 第四页 limit 9,3 (4-1)*3
-- 第n页 (n-1)*pageSize
-- 【pageSize :页面大小】
-- 【(n-1)*pageSize : 起始下标】
-- 【n :当前页数】
-- 【总页数(pageCount) = 数据总量(dataTotal) / 页面大小(pageSize)】
练习:
-- 查询查询英语成绩排名前二的,并且分数大于80的
SELECT `sin`.`id`,`name`,`score`
FROM `student_information` `sin`
INNER JOIN `student_score` `ssc`
ON `sin`.`id` = `ssc`.`s_id`
WHERE `so_id` = 43213 AND `score`>=80
ORDER BY `score` DESC
LIMIT 0,2
-- 第二种方式
SELECT `sin`.`id`,`name`,`score`
FROM `student_information` `sin`, `student_score` `ssc`
WHERE `sin`.`id` = `ssc`.`s_id` AND `so_id` = 43213 AND `score`>=80
ORDER BY `score` DESC
LIMIT 0,2
4.6、子查询
where ( 这里再嵌套一个SQL语句 )
本质 :在where中嵌套一个查询语句
-- 查询英语成绩,并且分数大于80的
-- 方式一
SELECT `sin`.`id`,`name`
FROM `student_information` `sin`
RIGHT JOIN `student_score` `ssc`
ON `sin`.`id` = `ssc`.`s_id`
WHERE `score`>=80 AND `so_id` = (
SELECT `id`
FROM `student_source`
WHERE `major_source` = '英语'
);
-- 方式二
SELECT `id`,`name` FROM `student_information`
WHERE `id` IN (
SELECT `s_id` FROM `student_score` WHERE `score`>=80 AND `so_id` = (
SELECT `id` FROM `student_source` WHERE `major_source` = '英语'
)
);
4.7、分组过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80
SELECT `major_source`,AVG(`score`) AS 平均分,MAX(`score`) AS 最高分,MIN(`score`) AS 最低分
FROM `student_score` `ssc`
INNER JOIN `student_source` `sso`
ON `sso`.`id` = `ssc`.`so_id`
GROUP BY `major_source` -- 通过什么来分组
HAVING 平均分>=80 -- 分组后的过滤条件
5、MySQL函数
官网:https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html
5.1、常用函数
-- ============== 常用函数 ==============
-- 数学
SELECT ABS(-18) -- 绝对值
SELECT CEIL(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 随机值
SELECT SIGN(-2) -- 返回符号类型 0~0,-2~-1,2~1
-- 字符串函数
SELECT CHAR_LENGTH('jijiji') -- 字符串长度
SELECT CONCAT('jiji','ji') -- 拼接字符串
SELECT INSERT('jiji',2,2,'kw') -- 在指定位置插入并替换原字符串
SELECT LOWER('kAng') -- 转小写
SELECT UPPER('kang') -- 转大写
SELECT INSTR('kang','k') -- 返回第一次出现子串的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换指定字符串
SELECT SUBSTR('坚持就能成功',1,2) -- 获取子串
SELECT REVERSE('坚持就能成功') -- 反转字符串
-- 时间和日期
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 获取本地时间
SELECT SYSDATE() -- 获取系统时间
SELECT YEAR(NOW()) -- 获取年份
SELECT MONTH(NOW()) -- 获取月份
SELECT DAY(NOW()) -- 获取日期
SELECT HOUR(NOW()) -- 获取小时
SELECT MINUTE(NOW()) -- 获取分钟
SELECT SECOND(NOW()) -- 获取秒数
-- 系统
SELECT SYSTEM_USER() -- 获取当前用户
SELECT USER() -- 获取当前用户
SELECT VERSION() -- 获取当前版本号
5.2、聚合函数(常用)
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均分 |
MAX() | 最大值 |
MIN() | 最小值 |
… | … |
-- ============ 聚合函数 ==========
-- COUNT() 都能够统计表中的数据(想查询一个表中有多少条记录,就用Count())
SELECT COUNT(id) FROM `student_information` -- Count(字段),会忽略所有的NULL值
SELECT COUNT(*) FROM `student_information` -- Count(*),不会忽略NULL值,本质:计算行数
SELECT COUNT(1) FROM `student_information` -- Count(1),不会忽略NULL值,本质:计算行数
SELECT SUM(score) AS 总和 FROM `student_score`
SELECT AVG(score) AS 平均分 FROM `student_score`
SELECT MAX(score) AS 最大值 FROM `student_score`
SELECT MIN(score) AS 最小值 FROM `student_score`
5.3、自定义函数
创建自定义函数的语法:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aBHCFfiJ-1686195380855)(C:\Users\LT\AppData\Roaming\Typora\typora-user-images\image-20221013175730544.png)]
删除函数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LSTJrAQP-1686195380856)(C:\Users\LT\AppData\Roaming\Typora\typora-user-images\image-20221013180023241.png)]
5.4、数据库级别的MD5加密
什么是MD5?
主要增强算法复杂度和不可逆性
MD5不可逆,具体的MD5的值是一样的
MD5破解的原理,背后有一个字典,{加密后的值,加密前的值}
-- ============ MD5测试加密 ============
CREATE TABLE `testmd5`(
`id` INT NOT NULL,
`name` VARCHAR(20) NOT NULL,
`psd` VARCHAR(10) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 明文密码
INSERT INTO `testmd5` VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE `testmd5` SET `psd` = MD5(`psd`)
-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'小明',MD5('123456'))
-- 用户传进来的密码如何校验
SELECT * FROM `testmd5` WHERE `id` = 4 AND `psd` = MD5('123456')
6、事务
1、事务定义
事务是一个最小的不可在分的工作单元;通常一个事务对应一个完整的业务(例如银行账户转账业务,该业务是一个最小的工作单元)。
一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。
事务只和DML语句有关,或者说DML语句才有事务。这个和业务逻辑有关,业务逻辑不同,DML语句的个数不同。
2.事务是什么?
多个操作同时进行,那么同时成功,那么同时失败。这就是事务。
事务有四个特性:一致性、持久性、原子性、隔离性
比如有一个订单业务
1.订单表当中添加一条记录 2.商品数量数据更新(减少) 3…
当多个任务同时进行操作的时候,这些任务只能同时成功,或者同时失败。
原子性(Atomicity)
- 原子性意味着数据库中的事务执行是作为原子。即不可再分,整个语句要么执行,要么不执行。
在SQL SERVER中,每一个单独的语句都可以看作是默认包含在一个事务之中,每一个语句本身具有原子性,要么全部执行,要么全部不执行,不会有中间状态。 - 例如:
银行转账功能,从A账户减去100,在B账户增加100,如果这两个语句不能保证原子性的话,比如从A账户减去100后,服务器断电,而在B账户中却没有增加100.虽然这种情况会让银行很开心,但作为开发人员的你可不希望这种结果.而默认事务中,即使出错了也不会整个事务进行回滚。而是失败的语句抛出异常,而正确的语句成功执行。这样会破坏原子性。所以SQL SERVER给予了一些选项来保证事务的原子性。
一致性(Consistency)
一致性即在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。
一致性体现在两个层面:
-
数据库机制层面
数据库层面的一致性是,在一个事务执行之前和之后,数据会符合你设置的约束(唯一约束,外键约束,Check约束 等)和触发器设置.这一点是由SQL SERVER进行保证的. -
业务层面
对于业务层面来说,一致性是保持业务的一致性.这个业务一致性需要由开发人员进行保证.很多业务方面的一致性可以 通过转移到数据库机制层面进行保证.比如,产品只有两个型号,则可以转移到使用CHECK约束使某一列必须只能存 这两个型号.
隔离性(Isolation)
- 事务的执行是互不干扰的,一个事务不可能看到其他事务运行时,中间某一时刻的数据。
在Windows中,如果多个进程对同一个文件进行修改是不允许的,Windows通过这种方式来保证不同进程的隔离性,而SQL Server中,通过SQL SERVER对数据库文件进行管理,从而可以让多个进程可以同时访问数据库:SQL Server利用加锁和阻塞来保证事务之间不同等级的隔离性. - 一般情况下,完全的隔离性是不现实的,完全的隔离性要求数据库同一时间只执行一条事务,这样的性能可想而知.想要理解SQL Server中对于隔离性的保障,首先要了解事务之间是如何干扰的.事务之间的互相影响的情况分为几种,分别为:脏读(Dirty Read),不可重复读,幻读。
持久性(Durability)
- 持久性,意味着在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
即使出现了任何事故比如断电等,事务一旦提交,则持久化保存在数据库中.
隔离性导致的一些问题
脏读:(读未提交)
指一个事务读取到了另一个事务未提交的数据。
不可重复读:(读已提交)
对于事务A多次读取同一个数据时,由于其他是事务也在访问这个数据,进行修改且提交,对于事务A,读取同一个数据时,有可能导致数据不一致,叫不可重复读。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wd7NOUF1-1686195380856)(C:\Users\kang\AppData\Roaming\Typora\typora-user-images\image-20221013145104684.png)]
幻读:
- 原因:
- 因为mysql数据库读取数据时,是将数据放入缓存中,当事务B对数据库进行操作:例如删除所有数据且提交时,事务A同样能访问到数据,这就产生了幻读。
- 问题:
- 解决了可重复读,但是会产生一种问题,错误的读取数据,对于其他事务添加的数据也将访问不到
执行
-- ============================== 事务 =================================
-- MySQL是默认开启事务自动提交的
SET COMMIT = 0 -- 关闭
SET COMMIT = 1 -- 开启
-- 手动处理事务
SET COMMIT = 0 -- 关闭自动提交
START TRANSACTION -- 标记一个事务的开启,往后的sql语句都在同一个事务内
INSERT xxx
INSERT xxx
COMMIT -- 提交:持久化(成功!)
ROLLBACK -- 回滚:回到原来的地方(失败)
-- COMMIT 或 ROLLBACK 执行后,事务会被自动关闭
-- 保留点 SAVEPOINT (在SQL代码中保留点名越多越好,这样就可以灵活回滚)
SAVEPOINT 保留点名 -- 在此处定义保留点名
ROLLBACK TO 定义的保留点名 -- 回滚到定义的保留点名
模拟事务
-- 在shop库下建立account表,并插入数据
CREATE DATABASE `shop`
USE `shop`
CREATE TABLE `account`(
`id` INT NOT NULL AUTO_INCREMENT COMMENT '账户标识',
`name` VARCHAR(30) NOT NULL COMMENT '账户名字',
`money` DECIMAL(10,2) NOT NULL COMMENT '账户余额',
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `account` (`name`,`money`)
VALUES ('A','5000.00'),('B','10000.00');
-- =================模拟转账(事务)=====================
SET autocommit = 0; -- 关闭自动提交
SHOW autocommit
START TRANSACTION; -- 开始一个事务
UPDATE `account` SET `money` = `money` - 2200 WHERE `name` = 'A';-- A减2200
UPDATE `account` SET `money` = `money` + 2200 WHERE `name` = 'B';-- B加2200
COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务
SET autocommit = 1; -- 恢复自动提交
7、索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
提取句子主干,就可以得到索引的本质:索引是数据结构。
7.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个。
- 主键索引(PRIMARY KEY)
- 主键的数据不可重复,只能有一个列作为主键
- 唯一索引(UNIQUE KEY)
- 避免出现重复的数据,唯一索引可以有多个列
- 常规索引(KEY / INDEX)
- 默认的索引
- 全文索引(FULLTEXT)
- 快速定位数据
基础语法:
-- 显示所有的索引信息
SHOW INDEX FROM student_information;
-- 给指定的列增加一个全文索引
ALTER TABLE `student_information` ADD FULLTEXT INDEX `name` (`name`);
-- EXPLAIN 分析SQL语句执行的状况
EXPLAIN SELECT * FROM `student_information` WHERE MATCH(`name`) AGAINST('康');
模拟测试
-- 使用自定义函数插入100万条数据
DELIMITER $$ -- 将命令执行符;修改为$$
CREATE FUNCTION create_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `student_information`(`name`,`age`,`favorite`,`detail`,`pwd`) VALUES (CONCAT('用户',i),CEIL(RAND()*50),'学编程','健康',UUID());
SET i = i + 1;
END WHILE;
RETURN i;
END
SELECT create_data()
7.2、测试索引
-- 添加索引的语法:CREATE INDEX 索引名 ON 表名(字段)
-- 没有索引
SELECT * FROM `student_information` WHERE `name` = '用户9999' -- 执行4.523 sec
-- 给name增加索引
CREATE INDEX id_student_information_name ON `student_information`(`name`)
-- 有索引
SELECT * FROM `student_information` WHERE `name` = '用户9999' -- 执行0.347 sec
索引在数据量小的时候,区别不大,在大数据时候,区别十分明显~
7.4、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上!
索引的数据结构
通常默认的数据类型为Hash
但INNODB默认的数据类型为BTREE
8、视图
8.1、视图的作用
为什么使用视图?
- 重用SQL语句。
- 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的范围权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
8.2、视图的使用
语法:
CREATE VIEW 创建视图
SHOW CREATE VIEW view_name; 查询创建的视图
DROP VIEW view_name; 删除视图
CREATE OR REPLACE VIEW; 更新视图
-- 在男性用户里,查询年龄为34,26,45的用户
-- 先创建一个只有男性用户的视图
CREATE VIEW male_account AS
SELECT `name` FROM `student_information`
WHERE `sex` = '男';
-- 在创建的视图里查询年龄为34,26,45的用户
SELECT si.`name` FROM male_account `ma`
INNER JOIN `student_information` `si`
ON `si`.`name` = `ma`.`name`
WHERE si.`age` IN (34,26,45)
-- 刚刚创建视图没有添加age字段,现修改重来
CREATE VIEW `male_account2` AS
SELECT `name`,`age` FROM `student_information`
WHERE `sex` = '男';
SELECT * FROM `male_account2` -- 查看视图
DROP VIEW `male_account2` -- 删除视图
-- 在创建的视图里查询年龄为34,26,45的用户
SELECT `name`,`age` FROM `male_account2`
WHERE `age` IN (34,26,45)
8.3、视图的规则与限制
常见的规则:
- 与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
- 对于可以创建的视图数目没有限制。
- 为了创建视图,必须具有足够的反问权限。这些限制通常有数据库管理人员授予。
- 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
- ORDER BY 可以用在视图中,但如果从该视图检索数据SELECT中也含有ORDER BY,那么该视图中的ORDER BY 也将被覆盖。
- 视图不能索引,也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。
9、存储过程
9.1、存储过程的作用
为什么要使用存储过程?
优点:
- 通过吧处理封装在容易使用的单元中,简化复杂的操作。
- 由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果所有开发人员和应用程序都是用同一(实验和测试)存储过程,则所使用的的代码都是相同的。
这一点得延伸就是防止错误。需要执行的步骤越多,出错的可能性就越大。防止错误保证了数据的一致性。
- 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
这一点的延伸就是安全性。通过存储过程限制对基础局的访问减少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会。
- 提高性能。因为使用存储过程比使用单独的SQL语句要快。
- 存在一些只能用在单个请求中的MySQL元素和特性,存储过程可以使用他们来编写功能更强更灵活的代码。
换句话说,使用存储过程有3个主要的好处,即简单、安全、高性能。显然,他们都很重要。
缺点:
- 一般来说,存储过程的编写比基本SQL句复杂,编写存储过程需要更高的技能,更丰富的经验。
- 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限,允许用户使用存储过程,但不允许他们创建存储过程。
尽管有缺陷,存储过程还是非常有用的,并且应该尽可能地使用它。
9.2、存储过程的使用
语法:
– 创建存储过程
CREATE PROCEDURE name(
IN argument1, IN(传递给存储过程)
OUT argument2, OUT(从存储过程传出,返回给调用者)
… …
)
[BEGIN
SELECT … … FROM … … INTO …; INTO (将计算得到的值保存到相应的变量)
… …
END];
– 执行存储过程
CALL name(@argument1, 所有MySQL变量名必须以@
@argument2);
DROP PROCEDURE name; – 删除存储过程
显示创建的存储过程
SHOW PROCEDURE name;
显示所有存储过程的详细信息
SHOW PROCEDURE STATUS;
DELIMITER //
CREATE PROCEDURE count_age(
OUT min_age INT,
OUT max_age INT,
OUT avg_age INT
)
BEGIN
SELECT MIN(age) FROM `student_information` INTO min_age;
SELECT MAX(age) FROM `student_information` INTO max_age;
SELECT AVG(age) FROM `student_information` INTO avg_age;
END//
DELIMITER ;
CALL count_age(@min,@max,@avg);
DROP PROCEDURE count_age;
SELECT @min,@max,@avg;
10、游标
10.1、步骤
在MySQL中,游标只能用于存储过程(和函数)。
使用游标的步骤:
- 在能够使用游标之前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句。
- 一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT语句吧数据实际检索出来。
- 对于填有数据的游标,根据需要取出(检索)各行。
- 在结束游标使用时,必须关闭游标。
- 在声明游标后,可根据需要频繁的打开和关闭游标。在游标打开后,可根据需要频繁地执行取操作。
10.2、使用游标
创建游标
CREATE PROCEDURE name()
BIGIN
DELCARE 游标名称 CURSOR
FOR
SELECT … …; 要检索的语句
END;
打开,关闭游标
OPEN 游标名称;
CLOSE 游标名称;(如果不关闭,MySQL将会在END语句执行时关闭它)
从使用游标检索的数据中,取数据
FETCH 游标名称
11、触发器
11.1、步骤
- 唯一的触发器名;
- 触发器关联的表;
- 触发器应该形影的活动;(DELETE、INSERT、UPDATE)
- 触发器何时执行;(BEFORE、AFTER)
尽量保持每个数据库的触发器名唯一!
11.2、使用触发器
创建触发器
CREATE TRIGGER 触发器名称 AFTER ( BEFORE ) INSERT ( DELETE、UPDATE ) ON 表名
FOR EACH ROW 【SELECT … … 触发器激活后要执行的语句】
删除触发器
DROP TRIGGER 触发器名称;
触发器仅支持表( 视图和临时表都不行 )!!!
…
)
[BEGIN
SELECT … … FROM … … INTO …; INTO (将计算得到的值保存到相应的变量)
… …
END];
– 执行存储过程
CALL name(@argument1, 所有MySQL变量名必须以@
@argument2);
DROP PROCEDURE name; – 删除存储过程
显示创建的存储过程
SHOW PROCEDURE name;
显示所有存储过程的详细信息
SHOW PROCEDURE STATUS;
DELIMITER //
CREATE PROCEDURE count_age(
OUT min_age INT,
OUT max_age INT,
OUT avg_age INT
)
BEGIN
SELECT MIN(age) FROM `student_information` INTO min_age;
SELECT MAX(age) FROM `student_information` INTO max_age;
SELECT AVG(age) FROM `student_information` INTO avg_age;
END//
DELIMITER ;
CALL count_age(@min,@max,@avg);
DROP PROCEDURE count_age;
SELECT @min,@max,@avg;
10、游标
10.1、步骤
在MySQL中,游标只能用于存储过程(和函数)。
使用游标的步骤:
- 在能够使用游标之前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句。
- 一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT语句吧数据实际检索出来。
- 对于填有数据的游标,根据需要取出(检索)各行。
- 在结束游标使用时,必须关闭游标。
- 在声明游标后,可根据需要频繁的打开和关闭游标。在游标打开后,可根据需要频繁地执行取操作。
10.2、使用游标
创建游标
CREATE PROCEDURE name()
BIGIN
DELCARE 游标名称 CURSOR
FOR
SELECT … …; 要检索的语句
END;
打开,关闭游标
OPEN 游标名称;
CLOSE 游标名称;(如果不关闭,MySQL将会在END语句执行时关闭它)
从使用游标检索的数据中,取数据
FETCH 游标名称
11、触发器
11.1、步骤
- 唯一的触发器名;
- 触发器关联的表;
- 触发器应该形影的活动;(DELETE、INSERT、UPDATE)
- 触发器何时执行;(BEFORE、AFTER)
尽量保持每个数据库的触发器名唯一!
11.2、使用触发器
创建触发器
CREATE TRIGGER 触发器名称 AFTER ( BEFORE ) INSERT ( DELETE、UPDATE ) ON 表名
FOR EACH ROW 【SELECT … … 触发器激活后要执行的语句】
删除触发器
DROP TRIGGER 触发器名称;
触发器仅支持表( 视图和临时表都不行 )!!!