MySQl学习

最新推荐文章于 2021-09-23 10:30:59 发布
最新推荐文章于 2021-09-23 10:30:59 发布
阅读量371 收藏
点赞数
分类专栏： 笔记存档专用 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mr_chenyong/article/details/116723814
版权

1、初识MySQL

JavaEE：企业级Java开发、web。

前端：页面->展示数据。

后台：连接点->连接数据库（JDBC）；连接前端->控制，控制视图跳转和给前端传递数据。

数据库：存储数据。

初级：写代码、基本数据库操作

中级：操作系统、数据结构与算法

高级：离散数学、数字电路、体系结构、编译原理、实战经验

1.1、为社么学习数据库

1、岗位需求

2、大数据时代

3、存数据需求

4、数据库是所有软件体系中最核心的存在

1.2、什么是数据库

数据库：DB（DataBase）

概念：数据仓库，软件

作用：存储数据，管理数据

1.3、数据库的分类

**关系型数据库：**SQL

  • MySQL、Oracle、SQL Server、SQL lite
  • 通过表和表之间，行和列之间的关系进行数据的存储

**非关系型数据：**NoSQL

  • Redis、MongoDB
  • 非关系型数据库，对象存储，通过对象自身的属性来决定

DBMS（数据库管理系统）

  • 数据库的管理软件，科学有效的管理数据、维护和获取数据
  • MySQL->数据库管理系统

1.4 MySQL简介

MySQL是一个***关系型数据库管理系统***

前世：瑞典MySQL AB 公司

今生：属于 Oracle 旗下产品

MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一

体积小、速度快、总成本低

中小型网站、或者大型网站、集群的开发都选择 MySQL 作为网站数据库

官网：https://www.mysql.com/

官网下载地址：https://dev.mysql.com/downloads/mysql/

安装建议：

  1. 尽量不要使用 .exe安装
  2. 尽可能使用压缩包安装

1.5、安装MySQL

1.6、安装基于客户端工具

MySQL图形化管理工具极大的方便了数据库的操作与管理，常用的图形化工具有MySQL Workbench、Navicat、phpMyAdmin等。

**注：**选择适合自己的一款即可

1.7、连接数据库

一：命令行连接

mysql -uroot -p密码  --mysql注释->连接数据库
--所有的语句都使分号（;）结尾

show databases; --查看所有的数据库
mysql> use 数据库名; --切换数据库
mysql> show tables; --查看数据库中所有的表
mysql> deac 数据库名； --查看数据库中所有表的信息
mysql> create database 数据库名； --创建一个数据库
mysql> quit或exit --退出连接

单行注释：--
多行注释：
/*

Hello World！

*/

数据库XXX语言：

  • DDL：定义
  • DML：操作
  • DQL：查询
  • DCL：控制

2、操作数据库

操作数据库 -> 操作数据库中的表 -> 操作数据库中表的数据

2.1、操作数据库

1、创建数据库

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

2、删除数据库

DROP DATABASE [IF NOT EXISTS] 数据库名;

3、使用数据库

USE `数据库名`； --这里的是着重号，Tab上面的键，注意不是单引号

4、查看数据库

SHOW DATABASES; --查看所有的数据库

2.2、数据库的数据类型

数值型

  • tinyint：十分小的数据 1个字节
  • smallint：较小的数据 2个字节
  • mediumint：中等大小的数据 3个字节
  • int：标准的整型 4个字节（常用）
  • bigint：较大的数据 8个字节

浮点型

  • float：单精度 4个字节
  • double：双精度 8个字节
  • decimal：定点数 ，金融计算的时候一般用

字符型

  • char：字符串固定大小的 0~255
  • varchar：可变字符串 0~65535（常用）
  • text：文本串 2^16-1(保存大文本)

日期型

  • 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：

  • 无符号整数
  • 声明了该列没有负数

zerofull：

  • 0填充
  • 不足的位数用0来填充 如：int(3)->5—>005

自增(AUTO_INCREMENT)：

  • 自动在上一条记录上 + 1（默认）
  • 通常用来设置唯一的主键——index，必须是整数类型
  • 可以自定义设置自增的起始值和步长

非空（NOT NULL）：

  • 必须赋值，不然报错
  • NULL，如果不填写值，默认为NULL

默认（DEFAILT）：

  • 设置默认的值
  • 如：sex(性别)，设置默认为男，如果不知道该列的值就出现默认值

2.4、创建数据库表

例如：

CREATE TABLE `department` (
  `d_id` int(4) NOT NULL,
  `d_name` varchar(20) NOT NULL,
  `function` varchar(50) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`d_id`),
  UNIQUE KEY `index_id` (`d_id`) USING BTREE,
  UNIQUE KEY ` index_name` (`d_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

语法格式：

CREATE TABLE `表名` (
	`字段名` 字段类型 [约束],
    `字段名` 字段类型 [约束],
    ......
    `字段名` 字段类型 [约束]
)[表类型] [字符类型]；

常用命令：

SHOW CREATE DATABASE 数据库名; --查看创建数据库的语句
SHOW CREATE TABLE 表名; --查看数据表的定义语句
DESC 表名; --显示表结构

2.5、数据表的类型

/*
关于数据库引擎
INNODB	默认使用
MYISAM	早年使用
*/
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
晚间约束不支持支持
全文索引支持不支持
表空间的大小较小较大

常规使用操作：

  • MYISAM 节约空间，速度较快
  • INNODB 安全性高，事务处理，多表多用户操作

在物理空间存在的位置

  • 所有的数据库文件都在data目录下，一个文件夹对应一个数据库
  • 本质是文件存储

设置数据库表的字符集编码

  • CHARSET=utf8
/*
不设置，会是MySQL默认的字符集编码（不支持中文）
*/

  • 在my.ini中配置默认的编码

    character-set-server=utf8;

2.6、修改删除表

修改

--修改表名：ALTER TABLE 旧表名 RENAME AS 新表名;
eg:ALTER TABLE teacher RENAME AS teacher1;
--增加表的字段：ALTER TABLE 表名 ADD 字段名 字段类型；
eg:ALTER TABLE teacher1 ADD age int(2);
--修改表的字段（重命名，修改约束）
ALTER TABLE 表名 MODIFY 字段名 字段类型 [约束];
eg:ALTER TABLE teacher1 MODIFY age VARCHAR(2); --修改约束
ALTER TABLE 表名 CHANGE 旧名字 新名字 字段类型 [约束];
eg:ALTER TABLE teacher CHANGE age age1 VARCHAR(2); --字段重命名
--删除表的字段：ALTER TABLE 表名 DROP 字段名;
eg:ALTER TABLE teacher1 DROP age1;

删除

--删除表（如果表存在则删除）
DROP TABLE IF EXISTS teacher1;

所有的创建和删除操作尽量加上判断，以免报错

注意点：

  • `` ：字段名尽量使用其包裹
  • 注释：-- 、 /* */
  • sql关键字大小写不敏感，建议大写
  • 所有的符号全部用英文

3、MySQL数据管理

3.1、外键（了解）

方式一:

CREATE TABLE `department` (
  `d_id` int(4) NOT NULL,
  `d_name` varchar(20) NOT NULL,
  `function` varchar(50) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
   PRIMARY KEY (`d_id`),	--主键
   UNIQUE KEY `index_id` (`d_id`) USING BTREE,			--唯一索引
   UNIQUE KEY ` index_name` (`d_name`) USING BTREE		--唯一索引
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*
worker表的d_id字段 要去引用department表的d_id字段
定义外键key
给这个外键加约束（CONSTRAINT 起约束名；REFERENCES 引用表名(字段)）
*/
CREATE TABLE `worker` (
 	`id` int(4) NOT NULL AUTO_INCREMENT,
  	`num` int(10) NOT NULL,
  	`d_id` int(4) DEFAULT NULL,
  	`name` varchar(20) NOT NULL,
 	`sex` varchar(4) NOT NULL,
    `birthday` datetime DEFAULT NULL,
    `address` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),		--主键
  	UNIQUE KEY `id` (`id`) USING BTREE,		--唯一索引
 	UNIQUE KEY `num` (`num`) USING BTREE,	--唯一索引
  	KEY `d_id` (`d_id`), 	--定义外键key
 	CONSTRAINT `d_id` FOREIGN KEY (`d_id`) REFERENCES `department` (`d_id`)
    --给这个外键加约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：

删除有外键关系表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）或者先删除外键约束，再删除要删除的表。

方式二：

CREATE TABLE `department` (
  `d_id` int(4) NOT NULL,
  `d_name` varchar(20) NOT NULL,
  `function` varchar(50) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`d_id`),
  UNIQUE KEY `index_id` (`d_id`) USING BTREE,
  UNIQUE KEY ` index_name` (`d_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `worker` (
 	`id` int(4) NOT NULL AUTO_INCREMENT,
  	`num` int(10) NOT NULL,
  	`d_id` int(4) DEFAULT NULL,
  	`name` varchar(20) NOT NULL,
 	`sex` varchar(4) NOT NULL,
    `birthday` datetime DEFAULT NULL,
    `address` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
  	UNIQUE KEY `id` (`id`) USING BTREE,
 	UNIQUE KEY `num` (`num`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--创建表的时候没有外键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREING KEY(作为外键的列) REFERENCES 引用表名（字段）；
eg：ALTER TABLE `worker` ADD CONSTRAINT `d_id` FOREIGN KEY (`d_id`) REFERENCES `department` (`d_id`);

以上操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰）

最佳实践：

  • 数据库是单纯的表，只用来存数据，只有行（数据）和列（字段）
  • 我们想使用多张表的数据，想使用外键（可以用程序去实现）

3.2、DML语言（*）

**数据库意义：**数据存储，数据管理

DML语言：数据操作语言

  • INSERT
  • UPDATE
  • DELETE

3.3、添加

INSERT

语法：

INSERT INTO 表名([字段1,字段2,字段3,...]) VALUES ('值1','',...),(),(),...,();

注意事项：

  1. 字段和字段之间使用 英文逗号 隔开
  2. 字段是可以省略的，但是后面的值必须要一一对应
  3. 可以同时插入多条数据，VALUES后面的值要使用 逗号隔开 VALUES (),(),…,();

​

3.4、修改

UPDATE

--修改学员名字
UPDATE `student` SET name = '张三' WHERE id = 1;
--不指定条件的作用下，会改变所有表
UPDATE `student` SET name = '张三';
--修改多个属性，英文逗号隔开
UPDATE `student` SET name = '张三',email = '123456789@qq.com' WHERE id = 1;

语法：

UPDATE 表名 SET 字段名1 = '值1'[,字段名2 = '值2'...] [WHERE 条件];
操作符含义例子结果
=等于5=6false
<>或！=不等于5<>6true
>大于1>2false
<小于1<2true
>=大于等于2>=3false
<=小于等于2<=3true
BETWEEN…AND…范围BETWEEN 4 AND 8[4,8]
AND和&&5>1 AND 1>2false
OR或||5>1 OR 1>2true
--通过多个条件定位数据
UPDATE `student` SET name = '张三' WHERE id = 1 AND sex = '男';

--语法：
UPDATE 表名 SET colnum_name = value1[,colnum_name = value2...] [WHERE 条件];

注意：

  1. colnum_name 是数据库的列，尽量带上 ``
  2. 筛选的条件如果没有指定，则会修改所有的列
  3. value是一个具体的值，也可以是一个变量
  4. 多个设置的属性之间，使用英文逗号隔开

3.5、删除

DELETE

语法：DELETE FROM 表名 [WHERE 条件];

--删除数据(全部删除)
DELETE FROM `student`;
--删除指定数据
DELETE FROM `student` WHERE id = 1;

TRUNCATE

作用：完全清空一个数据库表，表的结构和索引约束不会变！

--语法:TRUNCATE 表名;
TRUNCATE `student`;

DELETE和TRUNCATE的区别

  • 相同点：都能删除数据，都不会删除表结构
  • 不同点：
    1. TRUNCATE: 重新设置自增长列，计数器会归零
    2. DELETE: 不会影响事物
--测试DELETE和TRUNCATE的区别
DELETE TABLE `Test`;	--不会影响自增
TRUNCATE TABLE `test`;	--自增会归零

了解：DELETE删除的问题 重启数据库

  • InnoDB 自增列从1开始（存在内存中，断电即失）
  • MyISAM 继续从上一个自增长量开始（存在文件中，不会丢失）

4、DQL查询数据（*）

4.1、DQL

  • 所有的查询操作都用它（SELECT）
  • 数据库中最核心的语言，最重要的语句

SELECT的完整语法：

SELECT 语法：

SELECT [ALL | DISTINCT] {*|table.*|table.file1 [AS 别名1][,table.file2 [AS 别名2]],...}
FROM table_name1 [AS 别名1]
[INNER|LEFT|RIGHT] JOIN table_name2 [AS 别名2]	--联合查询
[WHERE 条件]		--指定结果满足的条件
[GROUP BY 分组字段]		--满足条件的进行分组
[HAVING 条件]		--筛选分组后的记录
[ORDER BY 排序字段]		--指定查询记录按一个或多个条件排序
[LIMIT [起点，长度]];	--指定查询记录

4.2、指定查询字段

语法：

SELECT 字段1,字段2,... FROM 表名；

--查询全部的学生：SELECT 字段 FROM 表；
SELECT * FROM `student`;

--查询指定的字段：SELECT 字段1,字段2,... FROM 表；
SELECT `Sno`,`Sname`,... FROM `student`;

--起别名（AS）：给结果起别名，给字段起别名，也可以给表起别名->AS可以省略用空格代替
SELECT `Sno` AS 学号,`Sname` 姓名,... FROM `student` s;

--函数：连接concat(a,b)
SELECT CONCAT('姓名：',Sname) AS 新名字 FROM `student`;

去重 DISTINCT

​ 作用：去除SELECT语句查询出来的结果中重复的数据，重复的数据只显示一条

--例：查询有哪些同学参加了考试
SELECT * FROM result;	--查询全部的考试成绩
SELECT Sno FROM result;	--查询有哪些同学参加了考试
SELECT DISTINCT Sno FROM result;	--查询有哪些同学参加了考试+去重

SELECT VERSION();	--查询系统版本（函数）
SELECT 100*3-1 AS 计算结果;	--用来计算（表达式）
SELECT @@AUTO_INCREMENT_INCREMENT;	--查询自增的步长(变量)
SELECT AUTO_INCREMENT_INCREMENT;	--也可以省略@@

--学院考试成绩 + 1分后结果
SELECT Sno,Sresult + 1 AS 提分后 FROM result;

数据库中的表达式：可以是文本值，列，函数，NULL，计算表达式，系统变量等

4.3、WHERE条件子句

作用:检索条件中 符合条件 的值

搜索的条件由一个或多个表达式组成,结果为布尔值

运算符语法描述
AND &&a AND b a&&b逻辑与,两个都为真,结果为真
OR ||a OR b a || b逻辑或,其中一个为真,则结果为真
NOT !NOT a !a逻辑非,真为假,假为真

尽量使用英文字母

--查询成绩在95~100之间(AND或&&或BETWEEN...AND...)
SELECT Sno,Sresult FROM result WHERE Sresult >= 95 AND Sresult <= 100;
SELECT Sno,Sresult FROM result WHERE Sresult >= 95 && Sresult <= 100;
SELECT Sno,Sresult FROM result WHERE Sresult BETWEEN 95AND 100;

--除了100号之外的同学的成绩(NOT或!=)
SELECT Sno,Sresult FROM result WHERE Sno != 100;
SELECT Sno,Sresult FROM result WHERE NOT Sno = 100;

模糊查询

​

运算符语法描述
IS NULLa IS NULLa是空，则结果为真
IS NOT NULLa IS NOT NULLa不是空，则结果为真
BETWEEN…AND…a BETWEEN b AND c若a在b，c之间，则结果为真
LIKEa LIKE b如果a匹配b，则结果为真
INa IN (a1,a2,a3,…)假设a在(a1,a2,a3,…)其中一个，则结果为真
--*******************************************模糊查询****************************************
--查询姓刘的同学
--LIKE（%->表示0个或任意一个字符；_->表示一个字符）
SELECT Sno,Sname FROM student WHERE Sname LIKE '刘%';

--查询姓刘的同学,名字后面只有一个字的
SELECT Sno,Sname FROM student WHERE Sname LIKE '刘_';

--查询姓刘的同学,名字后面有两个字的
SELECT Sno,Sname FROM student WHERE Sname LIKE '刘__';

--查询名字中有瑞字的同学：%瑞%
SELECT Sno,Sname FROM student WHERE Sname LIKE '%瑞%';

--IN 具体的一个或多个值
--查询(01,02,03,04)号同学
SELECT Sno,Sname FROM student WHERE id IN (01,02,03,04);

--IS NULL; IS NOT NULL
--查询地址为空的学生
SELECT Sno,Sname FROM student WHERE address = '' OR address IS NULL;

--查询有成绩的同学:不为空
SELECT Sno,Sname,grade FROM student WHERE grade IS NOT NULL;

--查询没有成绩的同学:为空
SELECT Sno,Sname,grade FROM student WHERE grade IS NULL;

4.4、连表查询

/*
思路：
1、分析需求，分析查询的字段来自哪些表（连接查询）
2、确定使用哪种连接查询（7种）
确定交叉点（两表中数据相同的）
*/

--INNER JOIN
SELECT s.Sno,Sname,Sresult 
FROM student AS s
INNER JOIN result AS r
ON s.Sno = r.Sno;

--LEFT JOIN
SELECT s.Sno,Sname,Sresult 
FROM student s
LEFT JOIN result r
ON s.Sno = r.Sno;

--RIGHT JOIN
SELECT s.Sno,Sname,Sresult 
FROM student AS s
RIGHT JOIN result AS r
ON s.Sno = r.Sno;

--WHERE	等值查询
SELECT s.Sno,Sname,Sresult 
FROM student AS s,result AS r
WHERE s.Sno = r.Sno;

--多张表连接查询
SELECT s.Sno,Sname,Sresult,Cno,Cname 
FROM student AS s
INNER JOIN result AS r
ON s.Sno = r.Sno
INNER JOIN course AS c
ON s.Sno = c.Sno;

4.5、自连接（了解）

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

4.6、分页和排序

排序

-- 排序：升序 ASC(默认)	降序 DESC
-- ORDER BY 通过字段进行排序
SELECT s.Sno,Sname,Sresult 
FROM student AS s,result AS r
WHERE s.Sno = r.Sno
ORDER BY Sresult DESC;

分页

--语法：LIMIT 起始值，页面大小;
SELECT s.Sno,Sname,Sresult 
FROM student AS s,result AS r
WHERE s.Sno = r.Sno
ORDER BY Sresult DESC
LIMIT 0,5;

--第一页：LIMIT 0,5;
--第二页：LIMIT 5,5;
--第三页：LIMIT 10,5;
--第n页：LIMIT (n-1)*pageSize,pageSize;
--pageSize:页面大小
--(n-1)*pageSize：起始值
--n：当前页
--数据总数/页面大小=总页数

**语法：**LIMIT 查询起始下标,页面大小;

4.6、子查询

**WHERE (后面的结果条件是计算出来的)：**本质是在WHERE语句中嵌套一个子查询语句

--子查询例子：
--查询课程为 高等数学-2 且 分数不小于80 的同学的姓名和学号
--方式一：
SELECT s.student,studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN subject sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName = '高等数学-2' AND StudentResult >=80;

--方式二：
--(1)、分数不小于80分的学生的姓名和学号
SELECT s.student,studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
WHERE SubjectName = '高等数学-2';
--(2)、查询课程为 高等数学-2 且 分数不小于80 的同学的姓名和学号
SELECT s.student,studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
WHERE SubjectName = '高等数学-2' AND SubjectNo = (
	SELECT SubjectNo FROM subject
    WHERE SubjectName = '高等数学-2' 
)

5、MySQL函数

5.1、常用函数

字符函数：

concat：连接 substr：截取字串 upper：变大写 lower：变小写 instr：获取字符串第一次出现的索引

replace：替换 length：获取字节长度 trim：去前后空格 lpad：左填充 rpad：右填充

数学函数：

ceil：向上取整 round：四舍五入 mod：取模

floor：向下取整 truncate：截断 rand：获取随机数（0~1之间的小数）

日期函数：

now：返回当前日期+时间 year：年 month：月 day：日 hour：时 minute：分

second：秒 curdate：返回当前日期 curtime：返回当前时间 datediff：返回两个日期相差的而天数

系统：

version：当前数据库服务器的版本 database：打开当前的数据库 user：当前用户

password(‘字符’)：返回该字符的密码形式 md5(‘字符’)：返回该字符的md5形式

5.2、聚合函数

函数名称描述
count()计数
max()最大值
min()最小值
sum()求和
avg()平均值
……
--===========count()===================
--都能够统计  表中的数据 （想查询一个表中有多少个记录，就用count()函数）
SELECT COUNT(*) FROM student;	--count(*):不会忽略NULL值，本质计算行数
SELECT COUNT(1) FROM student;	--count(*):不会忽略NULL值，本质计算行数
SELECT COUNT(Sno) FROM student;	--count(字段):会忽略所有NULL值

5.3、数据库级别的MD5加密（扩展）

什么是MD5：

  • 主要增强算法复杂度和不可逆性
  • MD5不可逆，一样的值的MD5是一样的
  • MD5破解网站原理，背后是一个字典，MD5加密后的值
--================MD5测试===========================

CREATE TABLE Tmd5 (
	id INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	pwd VARCHAR(50) NOT NULL,
	PRIMARY KEY (id)
);

--明文密码
INSERT INTO Tmd5 
VALUES  (1,'陈永瑞','123456'),
				(2,'赵杰','123456'),
				(3,'马骏','123456');
				
SELECT * FROM Tmd5;

--加密
UPDATE Tmd5 SET pwd=MD5(pwd) WHERE id=1;
UPDATE Tmd5 SET pwd=MD5(pwd);		--加密全部

--插入的时候加密
INSERT INTO Tmd5 VALUES (4,'李珂',MD5('123456'));

--如何检验：将用户传递进来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM Tmd5 WHERE `name`='李珂' AND pwd=MD5('123456');

6、事物

7、索引

8、权限管理和备份

9、规范数据库设计

10、JDBC（重点）

10.1、数据库驱动

驱动：声卡、显卡、数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XkEETAY2-1620831425072)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210101133952940.png)]

我们的程序会通过数据库驱动和数据库打交道！

10.2、JDBC

sun公司为了简化开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库）的规范，俗称JDBC。

这些规范的实现由具体的厂商去做。

对于开发人员来说，我们只需要掌握JDBC接口的操作即可。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6CV6UNbT-1620831425074)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210101135100331.png)]

需要的包：

java.sql

javax.sql

还需要导入一个数据库驱动包：mysql-connector-java-8.0.22

10.3、第一个JDBC程序

1、创建一个普通项目

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

2、导入数据库驱动

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pV09oq3E-1620831425076)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210101190222779.png)]

3、编写测试代码

//我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");     //固定写法

        //2.用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true(1.支持中文编码 2.设置字符集为utf8 3.使用安全连接)
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "199819";

        //3.连接成功，数据库对象      Connection代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);

        //4.执行SQL的对象       Statement执行sql的对象
        Statement statement = connection.createStatement();

        //5.执行SQL的对象 去 执行SQL，可能存在的结果，查看返回结果
        String sql = "SELECT * FROM users;";

        ResultSet resultSet = statement.executeQuery(sql);      //返回的结果集，结果集中封装了我们查询出来的全部结果

        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("NAME"));
            System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));
            System.out.println("==========================================");
        }

        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

步骤总结：

  1. 加载驱动
  2. 连接数据库DriverManager
  3. 获取执行sql的对象Statement
  4. 获得返回的结果集
  5. 释放连接

10.4、使用IDEA连接数据库

1、连接数据库（必须先导入包，才能连接成功）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C4dYxP6q-1620831425077)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210101212014580.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oSqjAMBY-1620831425079)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210101221926039.png)]

如果连接不上报错：Server returns invalid timezone. Go to ‘Advanced’ tab and set ‘serverTimezone’ property manually. （服务器返回无效时区；进入“Advanced”选项卡，手动设置“serverTimezone”属性为“UTC”。）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R3ron0wI-1620831425080)(MySQl学习.assets/image-20210117234019718.png)]

2、连接成功后，选则数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3cf25kbs-1620831425082)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210103141450156.png)]

3、查看数据库里面得内容：双击数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OOQglATZ-1620831425083)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210103141729700.png)]

4、更新数据：双击修改后提交

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IfQIJewU-1620831425084)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210103142153382.png)]

5、编写SQL语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MciNawWD-1620831425084)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210103142409155.png)]

6、连接失败，查看原因

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KW9nbmpL-1620831425086)(C:\Users\24937\AppData\Roaming\Typora\typora-user-images\image-20210103143150824.png)]

10.5、事物

要么都成功，要么都失败

ACID原则：

原子性：要么全部完成，要么都不完成

一致性：总数不变

隔离性：多个进程互不干扰

持久性：一旦提交不可逆，持久化到数据库

隔离性得问题：

脏读：一个事物读取了另一个没有提交得事物

不可重复读：在同一个事物内，重复读取表中得数据，表数据发生了改变

虚读（幻读）：在一个事物内，读取到了别人插入得数据，导致前后读出来结果不一致

10.6、数据库连接池

数据库连接–执行完毕–释放：这个过程十分浪费资源

池化技术：准备一些预先得资源，过来就连接预先准备好的资源

最小连接数

最大连接数

等待超时

编写连接池，实现一个接口：DataSource

开源数据源实现：

DBCP

C3P0

Druid：阿里巴巴

使用了上述的这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了！

补充：

1.查看创建数据库的命令
show create database 数据库名;
eg：show  create database java1;

2.查看创建表的命令
show create table 表名;
eg：show create table employee;

3.ZEROFILL:填补0，结合数字长度使用
	当添加的数字长度小于指定长度填补0，自动添加UNSIGNED
eg：
create table t1(
   num1 tinyint unsigned,
   num2 tinyint zerofill
);

4.子查询
特点: 一个结果(重点), 多行一列的结果(in...),一行多列结果， 多行多列(新表)

-- where或having后：

(1)一个结果(重点)
(2)多行一列的结果(in, all所有,some/any任意)

-- 运算符
> >=  ALL  max(大于最大值)
> >=  SOME/ANY min(大于最小值)
< <=  ALL  min(小于最小值)
< <=  SOME/ANY  max(小于最大值)
=     SOME/ANY  IN 

select * from employee where salary in(4000,5000);
--   =some/any  等同 in
select * from employee where salary =any(select salary from employee where salary=4000 or salary=5000);
--  >=  ALL  max(大于最大值)
select * from employee where salary >all(select salary from employee where salary=4000 or salary=5000);

--  >=  any  min(大于最小值)
select * from employee where salary >any(select salary from employee where salary=4000 or salary=5000);

--  <=  ALL  min(小于最小值)
select * from employee where salary <all(select salary from employee where salary=4000 or salary=5000);

--  <=  any  max(小于最大值)
select * from employee where salary <any(select salary from employee where salary=4000 or salary=5000);

5.DCL 操作: grant(权限), revoke(撤销)
授权语法：
grant 权限 on 数据库名.表名 to 用户名@登陆方式
--     权限： all,insert,update,delete,select
--     数据库名.表名:   company2.* , *.*
--     用户名@登陆方式:  alice@localhost

eg:案例1：
（1）创建用户
 CREATE USER 'jerry'@'localhost' IDENTIFIED BY '123456';
（2）赋予权限
 GRANT ALL ON company.* TO 'jerry'@'localhost'
（3）查看用户权限
 SHOW GRANTS FOR jerry@localhost;
（4）在 root 用户下查看所有用户
 SELECT User, Host FROM mysql.user;
（5） 删除用户
 drop user tina@localhost;

eg:案例2 ： 更新用户密码
use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED [WITH mysql_native_password] BY '123456';
FLUSH PRIVILEGES; -- 刷新权限

收权语法：
revoke 权限 on 数据库名.表名 from 用户名@登陆方式;

eg:案例3：
REVOKE ALL ON company.* from jerry@localhost;

数据库的导入导出：注意一定退出 mysql 命令提示符

-- 导出
语法：mysqldump -uroot -p 数据库名 > 导出路径
eg：mysqldump -uroot -p company > d:/companyback.sql
-- 导入
语法：mysql -uroot -p 数据库名< 导入路径
eg：mysql -uroot -p mytest< d:/companyback.sql

6.索引
分类
普通索引、唯一索引、主键索引、外键索引、全文索引
-- 查看索引
语法：desc 表名;
eg:desc employee;
   show create table employee; 
-- 普通索引
创建普通索引3种方法:
(1) 创建索引：CREATE INDEX 索引名 ON 表名(name[(length)]);
eg:create index index_sex on employee(sex);
注：如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。
(2) 修改表结构(添加索引)：ALTER table 表名 ADD INDEX 索引名(name);
eg：alter table employee add index index_age(age);
(3) 创建表的时候直接指定：
eg：
CREATE TABLE myta1(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX index_username(username)  
);  

删除索引的语法：
（1）DROP INDEX [indexName] ON 表名; 
 eg：drop index index_sex on employee;
（2）alter table 表名 drop index index_age;
 eg：alter table employee drop index index_age;

-- 唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
(1)创建索引：CREATE UNIQUE INDEX indexName ON 表名(name(length)); 
eg：create unique index unique_username on myta1(username);
(2)修改表结构：ALTER table 表名 ADD UNIQUE [indexName] (name(length));
eg：alter table myta1 add unique unique_id(id);
(3)创建表的时候直接指定：
eg：
CREATE TABLE myta2(  
 ID INT NOT NULL primary key,   
 username VARCHAR(16) NOT NULL,  
 UNIQUE(username)  
); 

删除唯一索引：
（1）drop index 索引名 on 表名;
 eg：drop index username on myta2;
（2）alter table 表名 drop index 索引名;
 eg：alter table myta1 drop index unique_id;

-- 主键索引
添加主键索引：
语法：alter table 表名 add primary key(字段);
eg：alter table myta1 add primary key(id);

删除主键：
语法：alter table 表名 drop primary key;
eg：alter table myta1 drop primary key;
注：如果主键是自增的列,应该先取消自增,才能删除：ALTER TABLE employee MODIFY id int;

-- 外键索引
添加外键语法：alter table 表名 add [constraint employee_fk1] foreign key(字段) references job(字段);
eg：alter table employee add  foreign key(job_id) references job(id);
注: 1.参考表 id 必须主键；2.参考表和子表的外键类型必须一致:alter table employee modify job_id int unsigned not null;

删除外键:注意: 先删除外键约束关系,然后删除Mysql添加的对应索引;
(1) 解除外键约束关系
alter table employee drop foreign key employee_fk1;
(2) 删除索引
alter table employee drop index employee_fk1;

注：（重要）：上面所有查询 like 范围值时不起作用

-- 全文索引(FULLTEXT) (将来有第三方技术实现中文全文索引)
语法：
ALTER TABLE 表名 ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
注：如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

-- 组合索引
eg：
CREATE TABLE myta4(  
ID INT NOT NULL,   
col_a VARCHAR(16) NOT NULL, 
col_b VARCHAR(16) NOT NULL, 
col_c VARCHAR(16) NOT NULL,  
INDEX abc_index (col_a,col_b,col_c)  
);  
--组合索引遵循最左优先原则
--索引方法是Btree，树状的，搜索时需要从根节点出发，上层节点对应靠左的值，所以有最左优先原则。

下列是未使用索引的情况：
where col_b = "some value"
where  col_c = "some value"
where  col_b = "some value" and col_c = "some value"
where  col_c = "some value" and col_b = "some value"
注：必须要存在col_a,和col_a随意搭配都行

7.explain
explain sql语句(select) \G ： 执行计划，可以分析查询语句的信息  
eg:explain select name,salary from employee where id between 2 and 5\G;


8.视图
（1）什么是视图：数据库视图被称为“虚拟表”，允许您查询其中的数据。

（2）语法: CREATE VIEW <视图名> AS <SELECT语句>;
-- 案例：
-- （1） 创建视图
create view myview1 as select name,salary from employee;
--  (2) 查询视图
select * from myview1;
select * from myview1 where salary between 3000 and 5000;

-- 【案例 1】查询姓名中包含a字符的员工名、部门名和工种名
#①创建
CREATE VIEW myview2
AS
SELECT e.name,d.department_name,j.job_name
FROM employee e
JOIN department d ON e.department_id  = d.department_id
JOIN job j ON j.job_id  = e.job_id;

#②使用
SELECT * FROM myview2 WHERE name LIKE '%a%';

视图的修改：
#方式一：
/*
create or replace view  视图名
as
查询语句;
*/
eg:create or replace view  myview3
   as select name from employee;

#方式二：
/*
语法：
alter view 视图名
as 
查询语句;
*/
eg:alter view myview3
   as select salary from employee;

删除视图:
/*
语法：drop view 视图名,视图名,...;
*/
eg:drop view myview3;

查看视图:
eg:
(1).DESC myv1;
(2).SHOW CREATE VIEW myv1;


视图的更新（了解）:
具备以下特点的视图不允许更新数据:
包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all;

9.事物
查看自动提交的开关：
show variables like 'autocommit';

-- 步骤1：开启事务
set autocommit=0;-- 把自动提交功能关闭
start transaction;可选的
-- 步骤2：编写事务中的sql语句(select insert update delete)
-- 步骤3：结束事务
-- 逻辑:  得sql的result 结果集  
if(result){
	commit;提交成功
}else{
	rollback;回滚失败
}

-- commit;提交事务
-- rollback;回滚事务

eg：1.演示事务的使用步骤
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE employee SET salary = 8000 WHERE name='tom';
UPDATE employee SET salary = 5001 WHERE name='alice';
#结束事务: 二选一
ROLLBACK;	-- 回滚
commit;		-- 提交

SELECT * FROM account;

eg：2.演示savepoint的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM employee WHERE id=5;
SAVEPOINT a;#设置保存点
DELETE FROM employee WHERE id=8;
ROLLBACK TO a;#回滚到保存点

SELECT * FROM account;


-- 事务的隔离级别：
-- 		                              脏读    不可重复读	  幻读
-- read uncommitted（读取未提交内容）:    √		   √		  √
-- read committed（读取提交内容）：       ×		   √		  √     ORCALE默认
-- repeatable read（可重读）：           ×		 ×		    √     MySQL默认
-- serializable （可串行化）             ×         ×          ×

这三者都是数据库事务的错误情况:
(1)、脏读：事务A读到了事务B未提交的数据。也就是说,当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。
(2)、不可重复读：事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到row1，但内容发生了变化。
(3)、幻读：事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到两行记录row1和row2。

-- mysql中默认 第三个隔离级别 repeatable read
-- oracle中默认第二个隔离级别 read committed
-- 查看隔离级别
select @@transaction_isolation ;
-- 设置隔离级别
set session|global transaction isolation level 隔离级别;

10.变量
(1)系统变量: 全局变量和会话变量
#1》全局变量
#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
show variables like 'character_set_%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
select @@character_set_server;
#④为某个系统变量赋值
SET @@global.autocommit=0;
set autocommit=0;
SET GLOBAL autocommit=0;

#2》会话变量
/*
作用域：针对于当前会话（连接）有效
*/
#①查看所有会话变量
SHOW SESSION VARIABLES;


(2)自定义变量
#1》用户变量
/*
作用域：针对于当前会话（连接）有效，作用域同于会话变量
*/

#2》局部变量
/*
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
*/
#案例：声明两个变量，求和并打印

eg:用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

eg:局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;

11.存储过程
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
(1)、提高代码的重用性
(2)、简化操作
(3)、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
*/
创建语法： create procedure

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	--存储过程体（一组合法的SQL语句）
END

调用过程
CALL 存储过程名(实参列表);

注意：过程语句用分号隔开， 那与mysql的命令提示符下的分号冲突,将 mysql的命令提示符下的分号 改为‘$’: delimiter $

eg:
delimiter $

create procedure mypro1()
	begin
	    select name from employee;
	    select salary from employee; 
	end$

delimiter ;
call  mypro1();

#注意：
/*
参数列表包含三部分:
参数模式  参数名  参数类型
参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
*/

(1).创建带in模式参数的存储过程

案例1：创建存储过程实现 根据员工信息名称查找对应员工名称，薪水，部门名称
delimiter $

CREATE PROCEDURE myp1(IN username VARCHAR(20))
BEGIN
	SELECT e.name,e.salary,d.department_name
	FROM employee as e
	INNER JOIN department as d ON e.department_id = d.department_id
	WHERE e.name=username;
END $

delimiter;
call myp1('tina');

(2).创建out 模式参数的存储过程
案例2:根据输入的员工信息，返回对应的部门名称
delimiter $

CREATE PROCEDURE myp1(IN userName VARCHAR(20),OUT departmentName VARCHAR(20))
BEGIN
	SELECT d.department_name INTO departmentName
	FROM employee as e
	INNER JOIN department as d ON e.department_id = d.department_id
	WHERE e.name=userName;
END $

delimiter ;
CALL myp11('tina',@dname);
SELECT @dname;

(3).创建带inout模式参数的存储过程
案例3：传入a和b两个值，最终a和b都翻倍并返回
delimiter $

CREATE PROCEDURE myp4(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $
delimiter ;

SET @m=10;
SET @n=20;
CALL myp4(@m,@n);
SELECT @m,@n;

（4). 局部变量的使用
create table admin(
   id int unsigned primary key auto_increment,
   username varchar(50) not null,
   password char(32) not null 
);
insert admin (username,password) values
	('tom','12345'),
	('alice','12345'),
	('tina','12345');

#创建存储过程实现，用户是否登录成功  procedure
delimiter $

CREATE PROCEDURE myp12(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	
	SELECT COUNT(*) INTO result#赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = password;
	
	SELECT IF(result>0,'成功','失败');#使用
END $
delimiter ;

CALL myp12('tina','12345');


#删除存储过程
语法：drop procedure 存储过程名;
eg:DROP PROCEDURE p1;
#查看存储过程的信息
语法：SHOW CREATE PROCEDURE 存储过程名;
eg：SHOW CREATE PROCEDURE  myp12;

12.函数
/*
区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果
*/

创建语法  function
/*
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END
*/

/*
注意：
(1).参数列表 包含两部分：
参数名 参数类型
(2).函数体：肯定会有return语句，如果没有会报错,如果return语句没有放在函数体的最后也不报错，但不建议
(3).函数体中仅有一句话，则可以省略begin end
(4).使用delimiter语句设置结束标记
*/

(1).无参有返回
#案例1：返回公司的员工个数
-- 解决方案: set global log_bin_trust_function_creators=TRUE;
delimiter $

CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employee;
	RETURN c;
END $

delimiter ;
SELECT myf1();

#案例2：根据部门名，返回该部门的平均工资
delimiter $

CREATE FUNCTION myf6(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employee e
	JOIN department d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END $

delimiter ;
SELECT myf6('部门1');

#查看函数语法:SHOW CREATE FUNCTION 函数名;
eg：SHOW CREATE FUNCTION myf6;
#删除函数语法：DROP FUNCTION 函数名;
eg：DROP FUNCTION myf6;


13.流程控制语句
#分支结构
#(1).if函数
/*
语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面
*/
eg:select if(1=1,'ok','error');

/*
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能：类似于多重if
只能应用在begin end 中
*/

#(2).case结构
/*
语法：
情况1：类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end 

情况2：
case 
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end 
*/

#if-else多分枝结构
案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D。
delimiter $

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $

delimiter ;
SELECT test_if(87);

#case 结构的分支
delimiter $

CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	case
    when score>90 then set ch='A';
    when score>80 then set ch='B';
    when score>60 then set ch='C';
    else set ch='d';
    end case;
	RETURN ch;
END $

delimiter ;
SELECT test_case(87);

#循环结构
/*
分类：
while、loop、repeat

循环控制：
iterate类似于 continue ，继续，结束本次循环，继续下一次
leave 类似于 break ，跳出，结束当前所在的循环
*/

#(1).while
/*
语法：
【标签:】while 循环条件 do
	循环体;
end while【 标签】;
*/

#(2).loop
/*
语法：
【标签:】loop
	循环体;
end loop 【标签】;
可以用来模拟简单的死循环
*/

#(3).repeat
/*
语法：
【标签：】repeat
	循环体;
until 结束循环的条件
end repeat 【标签】;
*/

案例1：批量插入，根据次数插入到admin表中多条记录
delimiter $

CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,password) 			             VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
END $

delimiter ;
CALL pro_while1(100);

#添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
delimiter $

trancate admin;
CREATE PROCEDURE test_while2(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,password)                         VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $

delimiter ;
CALL test_while2(100);

eg:存储过程实现：返回公司的员工个数
delimiter $

create procedure mypr8(out count int)
	begin
	    select count(*) into count
	    from employee;
	end$
	
delimiter ;
call mypr8(@count);
select @count;

eg:分支语句实现:创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
delimiter $

CREATE PROCEDURE t_if_pro(IN sal DOUBLE)
BEGIN
	IF sal<2000 THEN DELETE FROM employee WHERE 	employee.salary=sal;
	ELSEIF sal>=2000 AND sal<5000 THEN UPDATE 		employee SET salary=salary+1000 WHERE 			employee.`salary`=sal;
	ELSE UPDATE employee SET salary=salary+500 		WHERE employee.`salary`=sal;
	END IF;
END $

delimiter ;
CALL t_if_pro(3200);

eg:循环使用iterate：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
delimiter $

CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username,`password`) 			VALUES(CONCAT('xiaohua',i),'0000');
	END WHILE a;
END $

delimiter ;
CALL test_while1(100);

Node.js连接MySql数据库:

Node.js 连接 MySQL:
1.查看当前的 Node 版本：
$ node -v
v4.4.3

2.配置淘宝静态源
npm install -g cnpm --registry=https://registry.npm.taobao.org

3.安装驱动：使用了淘宝定制的 cnpm 命令进行安装：
$ cnpm install mysql

4.数据库操作( CURD )

（1）查询数据
//导入mysql模块
var mysql = require('mysql');

//连接mysql createConnection
var db = mysql.createConnection({
	host:'localhost',
	user:'root',
	password:'199819',
	prot:'3306',
	database:'java1'
});

//连接
db.connect();

//查询数据库中的department表的sql语句
var sql = 'SELECT * FROM department';

//执行sql语句
db.query(sql,function(err,res){
	if(err){
		console.log('查询错误'+ err.message);
		return;
	}
	console.log(res);
})

//结束
db.end();

（2）插入数据
//导入mysql模块
var mysql = require('mysql');

//连接mysql createConnection
var db = mysql.createConnection({
	host:'localhost',
	user:'root',
	password:'199819',
	prot:'3306',
	database:'java'
});

//连接
db.connect();

//插入数据数据库中的admin表的sql语句
var sql = 'INSERT INTO admin(username,password) VALUES(?,?)';
var sqlParms = ['陈永瑞','199819'];

//执行sql语句
db.query(sql,sqlParms,function(err,res){
	if(err){
		console.log('INSERT ERROR:'+err.message);
		return;
	}
	//拿到添加返回值对象
	console.log(res);
	//返回插入的行数值
	console.log(res.insertId);	
});

//结束
db.end();

（3）更新数据
//导入mysql模块
var mysql = require('mysql');

//连接mysql createConnection
var db = mysql.createConnection({
	host:'localhost',
	user:'root',
	password:'199819',
	prot:'3306',
	database:'java'
});

//连接
db.connect();

//更新数据数据库中的admin表的sql语句
var sql = 'UPDATE admin SET username=?,password=? WHERE id=?';
var sqlParms = ['赵杰','88888',52];

//执行sql语句
db.query(sql,sqlParms,function(err,res){
	if(err){
		console.log('UPDATE ERROR:'+err.message);
		return;
	}
	//拿到更新返回值对象
	console.log(res);	
});

//结束
db.end();

（4）删除数据
//导入mysql模块
var mysql = require('mysql');

//连接mysql createConnection
var db = mysql.createConnection({
	host:'localhost',
	user:'root',
	password:'199819',
	prot:'3306',
	database:'java'
});

//连接
db.connect();

//删除数据数据库中的admin表的sql语句
var sql = 'DELETE FROM admin WHERE id=53';

//执行sql语句
db.query(sql,function(err,res){
	if(err){
		console.log('DELETE ERROR:'+err.message);
		return;
	}
	//拿到删除返回值对象
	console.log(res);	
});

//结束
db.end();
Mr-陈先生
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库学习笔记：02 SQL详解
qq_44733409的博客
08-28 1107
文章目录SQL详解SQL简介SQL语法`DDL` - 数据定义语言`DML` - 数据操作语言DQL - 数据查询语言 SQL详解 SQL简介 SQL (Structured Query Language：结构化查询语言) 是用于管理关系数据库管理系统（RDBMS）。 SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。 SQL语法 启动MySQL80服务：以管理员身份打开命令行提示符，执行net start mysql80；也可以找到MySQL80服务，右键开启。
MySQL存储过程（带输入参数）实例
hejin17909mm的专栏
09-28 2万+
第一次写存储过程，参考了《SQL完全手册》，写了一个涉及到三张表的存储过程，简单实现了往一张表插入数据，修改另外两张表的功能。MySQL的语法和其他数据库语法有区别，写的过程需要特别注意。还有就是";"的问题，需要用DELIMITER //转义，因为在MySQL中，DELIMITER本身就是“;”的意思，具体这里不做详细说明。DROP TABLE IF EXISTS `CUSTOMERS`; cr
数据库连接字符串的使用方法
weixin_30945039的博客
10-14 153
综述 什么是数据库连接字符串？ 连接字符串的格式是一个以分号为界，划分键／值参数对的列表。连接字符串包含一个由一些属性名/值对组成的集合。每一个属性/值对都由分号隔开。 PropertyName1="Value1";PropertyName2="Value2";PropertyName3="Value3";..... 同样，连接字符串必须包含SQL Server实例名...
SQL数据库面试题以及答案（50例题）
热门推荐
魂-淡
02-05 24万+
SQL数据库面试题以及答案（50例题）
mysql存储过程出参入参_mysql存储过程出参入参，sqlserver很熟悉的一件事到mysql，捣鼓了大半天。记录一下提醒自己。勿看...
weixin_42128676的博客
01-20 295
create PROCEDURE myTestProcname(in score int ,out result varchar(100))BEGINIF score>60 THENset result='yes';ELSEset result='no';END if;END调用CALL myTestProcname(50,@result);select @result //为了看下出参结果...
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
MySQL学习资料PDF
02-11
MySQL必知必会.pdf 高性能MySQL（第3版）.pdf MySQL 技术内幕：InnoDB存储引擎.pdf
MySQL 学习路线.xmind
07-09
MySQL最全学习线路图 超级详细版
Mysql学习文档
04-17
文档讲述的从入门到精通的课程介绍，每个节点分级讲解，可根据自身需求进行选修。
MySQL学习书籍
11-22
MySQL学习书籍InnoDB引擎
用mysql计算85和32的和_SQL50题：答案与详细分析
weixin_26801149的博客
02-10 273
网上流传较广的50道SQL经典练习题，提供一些个人的解法，欢迎其他小伙伴进行指正和代码优化~数据表1.学生表Student(SId,Sname,Sage,Ssex)--SId 学生编号, Sname 学生姓名, Sage 出生年月, Ssex 学生性别create table Student (SId varchar(10), Sname varchar(10), Sage datetime, S...
SQL语言实现复杂查询
xujingjing 学习iOS开发历程
04-05 2788
SQL语言实现复杂查询 (1)查询每个学生及其选课情况   select student.*,sc.* from student,sc where student.sno=sc.sno (2)查询每门课的间接先修课。     select first.cno,second.cpno from course first left join course second on first.
mysql模拟题三_mysql经典45题（3刷）
weixin_35715235的博客
02-04 2336
Mysql 经典练习题我使用的Mysql版本是5.7.27-log，答案可能会因版本会有少许出入。一、数据源准备及说明：1.1、数据字段说明:1.学生表 Student(SId,Sname,Sage,Ssex)SId ：学生编号Sname：学生姓名Sage ：出生年月Ssex：学生性别2.课程表 Course(CId,Cname,TId)CId ：课程编号Cname ：课程名称TId ：教师编号3...
mysql的常用语句练习使用
xj15291519671的博客
09-23 111
使用的数据库 DROP TABLE IF EXISTS EMP; DROP TABLE IF EXISTS DEPT; DROP TABLE IF EXISTS SALGRADE; CREATE TABLE DEPT (DEPTNO int(2) not null , DNAME VARCHAR(14) , LOC VARCHAR(13), primary key (DEPTNO) ); CREATE TABLE EMP (EMPNO int(4) not nul.
mysql的快速入门
weixin_30361753的博客
08-25 71
最近想使用c语言写一个聊天的系统，所以看了一看mysql。看到过程中不断的总结，就写了一些东西。 说明一下，我使用的系统是ubuntu18.04, linux下mysql的安装和使用1.安装sudo apt-get install mysql-server sudo apt install mysql-clientsudo apt install libmysqlclient-dev...
MySQL基础之----数据类型篇(常用数据类型)
weixin_45591980的博客
10-03 215
MySQL数据类型 注: 转载 https://www.runoob.com/mysql/mysql-data-types.html MySQL中定义数据字段的类型对数据库的优化是非常有效的 MySQL支持多种类型, 大致可以分为三类: 数值, 日期/时间和字符串(字符)类型 注: 本文全部使用小写形式, SQL语句不区分大小写命令 数值类型 MySQL 支持所有标准SQL数值数据类型 这些类型包括严格数值数据类型(integer, smallint, decimal和numeric) , 以及浮点型
idea与mysql
Madridcrls7的博客
05-30 6884
在用idea与mysql数据库简历连接的时候，经常会出现好多情况，这里我总结一下：需要的工具：IDEA下载地址：https://www.jetbrains.com/idea/features/editions_comparison_matrix.htmmysql下载地址：https://dev.mysql.com/downloads/installer/navicat可以从百度下载一个破解版的，选...
mysql微信昵称特殊符号_MySQL保存微信昵称中的特殊符号造成：（Incorrect string value: "xxxx'for column ‘name’ at row 1...
weixin_35934226的博客
01-25 259
今天有业务员反应，编辑某个用户的信息的时候出现了异常，异常信息如下：Incorrect string value: "xFOx9Fx92x9D vxE6..'f or column 'name' at row 1我让她发个截图看看，结果发现该用户的昵称如下：原图该昵称是微信昵称，也未免会带一些特殊符号或者，既然异常由数据库抛出，我们不妨查看一下该字段的字符集:原来如此最初的 UTF-8 格式使用一...
MySQL基础学习笔记之——MySQL支持的数据类型
前尘忆梦的博客
08-12 481
MySQL支持的数据类型 1、数值类型 整数类型 字节 最小值 最大值 TINYINT 1 有符号：-128无符号：0 有符号：127无符号：255 SMALLINT 2 有符号：-32768无符号：0 有符号：32767有符号：65535 MEDIUMINT 3 有符号：-8388608无符号：0 有符号：8388607无符号：1677215 INT、INTEFRE 4 有符号：-2147483648无符号：0 有符号：2147483647无符号：4294967295 BI
mysql学习路线图
最新发布
03-15
MySQL是一种常用的关系型数据库管理系统，学习MySQL可以帮助你掌握数据库的设计、管理和查询等技能。以下是一个MySQL学习的基本路线图： 1. 学习SQL语言基础： - 了解SQL语言的基本概念和语法结构，包括数据类型、表的创建、插入、更新和删除数据等操作。 - 学习SQL查询语句的编写，包括SELECT语句的使用、条件查询、排序和分组等。 2. 数据库设计与规范化： - 学习数据库设计的基本原则和规范化理论，了解关系型数据库的范式理论。 - 学习如何进行数据库的逻辑设计和物理设计，包括表的结构设计、关系建立和约束等。 3. 数据库管理与优化： - 学习MySQL数据库的安装和配置，包括服务器设置、用户管理和权限控制等。 - 学习数据库备份与恢复、性能优化和索引设计等技术，以提高数据库的性能和可靠性。 4. 高级特性与扩展： - 学习MySQL的高级特性，如事务处理、视图、存储过程和触发器等。 - 学习MySQL与其他编程语言的集成，如使用PHP或Python连接MySQL数据库进行数据操作。 5. 实践项目： - 参与实际的数据库项目，如开发一个简单的数据库应用或优化一个现有的数据库系统。 - 在实践中不断提升自己的数据库设计和管理能力，加深对MySQL的理解和应用。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值