代码编织梦想

数据库操作-爱代码爱编程

Posted on2023-06-27 分类: 知识类 数据库 mysql 程序人生

1.连接数据库

1.1命令行连接：

mysql -uroot -p****** --数据库连接

1.2修改密码：

update mysql.user set authentication_string=password('123456') where user='root' 
and Host = 'localhost'; -- 修改密码

1.3刷新权限:

flush privileges; -- 刷新权限

1.4 创建两个数据库：

外键简介：

  • 外键是用来连接数据库的，保证数据库的参照完整性。
  • 表的外键是另一表的主键，外键是可以有重复的，可以是空值。
  • 以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
  • 保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。
  • 删除表时，只能先删除从表，再删除主表。
  • 在开发的时候不会使用外键
CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id', -- auto_increment 主键自增长
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称', -- comment 注释属性
	PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8  -- 默认引擎，编码

接下来的表为其增加外键：

方式一：创建表的时候增加约束

-- 学生表的 gradeid 字段引用年级表的字段
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',   -- 创建关联项
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`), -- FK_是通用外键约束名称
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) -- 给外键添加约束
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二：

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

2.DML语言

2.1 添加

INSERT INTO `表名`(`字段1`,`字段2`,...) VALUES (值1,值2,...),(值1,值2,...),...

添加多个字段

-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大三'),('大四');

INSERT INTO `student`(`name`,`pwd`,`birthday`,`gradeid`,`address`,`email`) 
VALUES ('***','abc','********** 01:22:54','2','中******','******');

插入的数据要和字段一一对应!

2.2 修改

UPDATE `grade` SET `gradeName` = '研一' WHERE `gradeID` = 1;
UPDATE 表名 SET 字段名 = 新值,... WHERE 条件

UPDATE `student` SET `pwd` = 'abc123' WHERE id = 1;

2.3 删除

删除一行或多行数据

-- 删除数据
DELETE FROM `grade` WHERE `gradeID` BETWEEN 1 AND 4 
DELETE FROM 表名 WHERE 条件

DELETE FROM `student` WHERE id = 2;

删除整个表

DELETE FROM `grade`;

INSERT INTO `grade`(`gradeName`) VALUES ('大一'),('大二'),('大三'),('大四');

TRUNCATE `student`;

3.DQL语言

3.1 基础查询

SELECT语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- 联合查询
    [WHERE ...]  -- 指定结果需满足的条件
    [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查询的记录从哪条至哪条

SQL的执行顺序：

–第一步：执行FROM

–第二步：WHERE条件过滤

–第三步：GROUP BY分组

–第四步：执行SELECT投影列

–第五步：HAVING条件过滤

–第六步：执行ORDER BY 排序

SELECT * FROM `student`; -- 查询表中所有字段

SELECT 3+4 FROM DUAL; -- 如果么有子语句，一般用DUAL来维持语句平衡

DISTINCT去重

-- 用as重命名 查询结果只返回一条
SELECT DISTINCT `student` AS '***' 
FROM result

列的别名

NOTE

-重命名一个列，更方便计算。

-别名使用双引号，并且可以使用空格等特殊字符

SELECT `last_name` AS `name`,`commission_pct` comm -- 尚硅谷的表格
FROM `employee`;

SELECT "轩" AS corporation, `employee_id`, `last_name`
FROM `employee`;  -- 可以在返回结果时插入一行常数列

where 过滤数据

SELECT 字段1，字段2 from <表名> where <过滤条件>

SELECT * FROM employee
WHERE department_id = 90;  -- 查询id号为90的员工名单信息

NOTE :

  • 使用 like 操作符时 %：表示任意字符 _：表示一个字符
  • in(一个或者多个具体的结果)

3.2 联表查询

3.2.1 多表查询

如果想要查询两个表的内容（例如：查询员工姓名及部门名称）

SELECT `last_name`,`department_name`
FROM `employee`,`departments`; -- 会出现笛卡尔积错误

-- 解决办法：使用where进行有效连接
SELECT `last_name`,`department_name`,`employee`.`department_id`
FROM `employee`,`departments`
WHERE `employee`.`department_id` = `departments`.`department_id`;

如果查询的字段存在于多表中，则需要指明字段所在的表；

3.2.2 Join连接

(非)等值连接

SELECT `last_name`,`salary`,grade_level
FROM `employee` e , `job_grades` j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

(非)自连接

SELECT emp.`employee_id`,emp.`last_name`,mgr.`employee_id`,mgr.`last_name`
FROM `employee` emp,`employee` mgr
WHERE emp.`manager_id` = mgr.`employee_id`; -- 自连接就是自我引用

思路：

  1. 分析需求，确定查询内容来自哪一张表。

  2. 判断使用哪一种查询方式。

  3. 确认表与表之间的连接点。

内，左，右连接实现

-- 语法
SELECT 字段列表 FROM A表 ...（left,right,inner） JOIN B表 ON 关联条件
-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
-- 内查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
INNER JOIN `result` r ON s.studentno = r.studentno

-- 左查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s -- 左表
LEFT JOIN `result` r ON s.studentno = r.studentno

-- 右查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s -- 左表
RIGHT JOIN `result` r ON s.studentno = r.studentno
操作描述
Inner Join如果表中至少有一个匹配，就返回行。
Left Join左表还是会返回所有的值，即使右表没有匹配
Right Join右表还是会返回所有的值，即使左表没有匹配

查询训练

-- 查询缺考学生(左连接实现)
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN `result` r ON s.studentno = r.studentno
WHERE `studentresult` IS NULL

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r ON s.studentno = r.studentno
INNER JOIN `subject` sub ON r.subjectno = sub.subjectno

3.2.3Union语句

格式

-- UNION 操作符返回两个表查询的结果并集(去重之后的)
SELECT ... form A
UNION [ALL] -- 如果后面加ALL，则返回不去重的结果
SELECT ... from B

UNION操作符效率会更高，可以使用它来完成Join的7种连接

SELECT *
FROM employee
WHERE `email` LIKE '%a%'
UNION 
SELECT * 
FROM employee 
WHERE `department_id` > 90;

3.3分页排序

3.3.1 排序数据

使用order by 子句进行排序**（在SELECT语句结尾）**

  • ASC(accend) ：升序
  • DESC(descend)：降序
-- order by 默认升序排序
SELECT `last_name`,`job_id`,`hire_date`
FROM `employee`
ORDER BY `hire_date` DESC;

-- 也可以使用不在SELECT查询的数据进行排序
SELECT `last_name`,`job_id`,`hire_date`
FROM `employee`
ORDER BY `salary` DESC;

3.3.2 分页数据

-- 格式
Limit [位置偏移量] 行数

-- 分页前10条内容
SELECT *
FROM `employee`
LIMIT 0,10;

*分页显示式公式：(当前页数-1) 每页页数，每页页数

-- 格式
SELECT *
FROM `employee`
LIMIT (pagenumber-1)*pagesize,pagesize;

SELECT salary,last_name
FROM `employee`
WHERE salary > 6000
ORDER BY salary DESC
LIMIT 0,15; -- 从第0条记录开始获取后面的15条记录

3.3.3 分组数据

  1. 使用group by 时，select 涉及的列要么是参与分组的列，要么列包含在聚合函数中
  2. where将对分组前的所有数据进行筛选。having将对分组后的一组数据搞事情。
-- 查询不同课程的平均分,最高分,最低分
-- 前提:根据不同的课程进行分组
 SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
 FROM result r
 INNER JOIN `subject` sub ON r.`subjectno` = sub.`subjectno`
 GROUP BY `subjectname`

3.4 子查询

子查询的定义：

  • 子查询是将一个查询语句嵌套在另一个查询语句中；
  • 在特定情况下，一个查询语句的条件需要另一个查询语句来获取，内层查询（inner query）语句的查询结果，可以为外层查询（outer query）语句提供查询条件。
  • where(查询的值为计算出来的而不是固定的)
-- 查询课程为 高等数学-1 且分数不小于80分的学生的学号和姓名
-- 连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r ON s.studentno = r.studentno 
INNER JOIN `SUBJECT` sub ON sub.subjectno = r.subjectno
WHERE subjectname = '高等数学-1' AND studentresult >= 80

-- 子查询：由内及外. where 语句中的条件就是两张表的交叉点
SELECT studentno,studentname
FROM student 
WHERE studentno IN(
	SELECT studentno FROM result WHERE subjectno = (
		SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1'
	)
)

4.函数

4.1 常用函数

4.1.1 数据函数

 SELECT ABS(-8);  /*绝对值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
 SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

4.1.2 字符串函数

 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
 SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/
 SELECT LOWER('KuangShen'); /*小写*/
 SELECT UPPER('KuangShen'); /*大写*/
 SELECT LEFT('hello,world',5);   /*从左边截取*/
 SELECT RIGHT('hello,world',5);  /*从右边截取*/
 SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
 SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
 SELECT REVERSE('狂神说坚持就能成功'); /*反转*/

4.1.3 时间函数

 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());

4.1.4 系统信息函数

 SELECT VERSION();  /*版本*/
 SELECT USER();     /*用户*/

4.2 聚合函数

函数名称描述
count返回满足Select条件的记录总和数。
min可以为数值字段，字符字段或表达式列作统计，返回最小的值。
max可以为数值字段，字符字段或表达式列作统计，返回最大的值。
avg返回一列的平均值。
sum返回一列的总和。

NOTE: where不能使用聚合函数

聚集函数也叫列函数，它们都是基于整列数据进行计算的，而where子句则是对数据行进行过滤的，在筛选过程中依赖“基于已经筛选完毕的数据得出的计算结果”是一种悖论，这是行不通的。更简单地说，因为聚集函数要对全列数据时行计算，因而使用它的前提是：结果集已经确定！

/*
count(*)包括了所有的列，统计行数，在统计结果的时候，不会忽略列值为NULL  
count(1)忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL  
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空
（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
*/

SELECT COUNT(*) FROM `student`
SELECT COUNT(1) FROM `student`
SELECT COUNT(`identitycard`) FROM student

SELECT AVG(`studentresult`) AS 平均分 FROM `result` WHERE `studentno` = 1001
SELECT MIN(`studentresult`) AS 最低分 FROM `result` WHERE `studentno` = 1001
SELECT MAX(`studentresult`) AS 最高分 FROM `result` WHERE `studentno` = 1001
SELECT SUM(`studentresult`) AS 总分 FROM `result` WHERE `studentno` = 1001

4.3 MD5加密函数

特点

1.不可逆性 — 根据 MD5 值计算不出原始数据

2.唯一性 — 不同原始数据会有不同的 MD5 值

数据加密与匹配

CREATE TABLE md5test (
	id INT(11) NOT NULL ,
	`name` VARCHAR(11) NOT NULL,
	`pwd` VARCHAR(11) NOT NULL,
	PRIMARY KEY (id)
 )ENGINE=INNODB,DEFAULT CHARSET = utf8
 
INSERT INTO md5test VALUES (1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
UPDATE md5test SET pwd = MD5(pwd) WHERE id = 1
 INSERT INTO md5test VALUES(4,'kuangshen3',md5('123456'));

-- 数据匹配
SELECT * FROM md5test WHERE `name` = 'zhangsan' AND pwd = MD5('123456')

5.事务

5.1 ACID原则

5.1.1 原子性

原子性是指事务包含的所有操作要么全部成功，要么全部失败。因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

5.1.2 一致性

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

5.1.3 隔离性

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

5.1.4 持久性

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

5.2 基本语法

MYSQL中事务是默认开启的

SET autocommit = 0 -- 关闭事务自动提交
START TRANSACTION -- 开启事务

COMMIT -- 提交事务

ROLLBACK -- 将事务回滚

SET autocommit = 1 -- 开启事务自动提交
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 把事务回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 释放保存点

事务实践

/*
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
USE shop;

CREATE TABLE account(
	id INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	money DECIMAL(9,2) NOT NULL,
	PRIMARY KEY (id)
)ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO account(`name`,money) VALUES ('a',2000),('b',10000)
DELETE FROM `account`;

SET autocommit = 0
START TRANSACTION -- 开启事务
UPDATE account SET money = money - 500 WHERE `name` = 'a'
SAVEPOINT temp -- 创建保存点
UPDATE account SET money = money + 500 WHERE `name` = 'b'
ROLLBACK TO SAVEPOINT temp -- 回滚到保存点
COMMIT -- 事务一旦提交不可逆转，持久化到数据库文件了
ROLLBACK -- 回滚数据
SET autocommit = 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_73593563/article/details/131417996

常用的10个mysql命令-爱代码爱编程

2023-06-23 分类: 数学建模 Java 数据库

0. 显示数据库 命令：show databases。 作用：列出当前数据库服务器中所有的数据库。 例子： mysql> show databases; +--------------------+| Database           | +--------------------+| information_schema | | and

Continue Reading

java springboot整合mybatis联合查询-爱代码爱编程

2023-06-24 分类: spring boot Java mybatis

前面文章 java springboot整合MyBatis做数据库查询操作写了springboot整合MyBatis的方法 并演示了基础查询的语法 根据id查 那么 我们这次来演示联合查询 我们staff 表 内容如下

Continue Reading