mysql学习总结

qq_39075859

已于 2023-10-21 17:42:21 修改

阅读量85

点赞数

分类专栏： mysql 文章标签： mysql 学习数据库

于 2023-10-13 16:54:17 首次发布

本文链接：https://blog.csdn.net/qq_39075859/article/details/133800521

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、创建表语句
二、修改数据库
三、MySQL数据管理
四、MySQL函数
五、mysql事务
- 1、事务的概念
二、事务的ACID特点
三、事务控制语句

一、创建表语句

CREATE TABLE IF NOT EXISTS `student`(
	--创建学号id，类型为int，数据不允许为空，自增
	`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 '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
	--表类型为INNODB，缺省字符集为utf8，可以存储中文
)ENGINE=INNODB DEFAULT CHARSET=utf8

注解：

CREATE TABLE IF NOT EXISTS `student`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]

show create database 数据库名-- 查看创建数据库的语句
show create table 表名-- 查看表的定义语句
DESC 表名-- 显示表的具体结构

二、修改数据库

修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名列属性[属性]
修改字段 :
ALTER TABLE 表名 MODIFY 字段名列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名新字段名列属性[属性]
删除字段 : ALTER TABLE 表名 DROP 字段名

ALTER TABLE teacher RENAME AS teacher1；--修改表名
ALTER TABLE teacher ADD AGE INT(4);--增加age列，类型为int
ALTER TABLE teacher MODIFY AGE VARCHAR(20);--将字段AGE类型修改为varchar（20）
ALTER TABLE teacher CHANGE AGE AGE1 VARCHAR(20);--将字段AGE重命名为AGE1,类型为varchar(20);
ALTER TABLE teacher drop age1;--删除age1字段

-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS teacher;

三、MySQL数据管理

1、外键

如果一个实体的某个字段指向另一个实体的主键，就称为外键。

被指向的实体，称之为主实体（主表），也叫父实体（父表）。
负责指向的实体，称之为从实体（从表），也叫子实体（子表）

方式一：在创建表的时候增加约束
下表中从表为student，外键为gradeid，指向grade表的gradeid字段（主键），通过**foreign key(自己的字段) references 主表(主表字段)**设置外键约束

/*
	1. 定义外键key
	2. 给外键添加约束（执行引用）references 引用
*/
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 '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建年级表
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方法二：创建表成功后，添加外键约束

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

2、数据库操作语句（DML语言）

Data Manipulation Luaguge：数据操作语言

1. 添加 insert
常见的用法
语法：

-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');

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

2. 修改 update
常见的用法

UPDATE `student` SET `name`='zsr204',`address`='湖北' WHERE `name`='zsr' AND `pwd`='200024';

语法：

UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

关于WHERE条件语句：
在这里插入图片描述
3. 删除 delete
常见用法

DELETE FROM `student` WHERE id=1;

语法：

DELETE FROM 表名 [WHERE 条件]

3、数据查询语言（DQL）

Data QueryLanguage 数据查询语言

1.基础查询
常见用法

-- 查询全部学生
SELECT * FROM student;

-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;

-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;

-- 查询有哪写同学参加了考试,重复数据要去重
SELECT DISTINCT `StudentNo` FROM result;

2.条件查询
常见语句

-- 查询考试成绩在95~100之间的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`BETWEEN 95 AND 100;

-- 查询除了1000号以外的学生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`= 1000;

-- 查询名字含d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d%';

-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';

-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);

3、连接查询

内连接查询（INNER JOIN ON）

特点：内连接是将两张表有关联关系的数据显示出来，没有关联关系的不显示。

#1.查询每个讲师所教的科目（显式内连接）
select * from teacher a INNER JOIN `subject` b on a.TId=b.teaid;

#2.查询每个讲师所教的科目（隐式内连接）
select * from teacher a , `subject` b where a.TId=b.teaid;

在这里插入图片描述

左外连接（LEFT JOIN ON）

特点：左外连接是以LEFT JOIN关键字左边为左表，关键字右边为右表。左表的数据全显示出来，右表的数据有关联关系的正常显示，没有关联关系的显示null。

#查询所有学生和成绩
select * from student a LEFT JOIN score b ON a.SId = b.stuId;

在这里插入图片描述

右外连接（RIGHT JOIN ON）

特点：右外连接是以RIGHT JOIN关键字左边为左表，关键字右边为右表。右表的数据全显示出来，左表的数据有关联关系的正常显示，没有关联关系的显示null。

#查询所有学生和成绩
select * from student a RIGHT JOIN score b ON a.SId = b.stuId;

4、排序

select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc

在这里插入图片描述

5、子查询

本质：在 where子句中嵌套一个子查询语句

-- 查询‘课程设计’的所有考试结果（学号，科目编号，成绩）降序排列

-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.StudentNo=s.SubjectNo
WHERE SubjectName='课程设计'
ORDER BY StudentResult DESC;
-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
	SELECT SubjectNo FROM `subject`
	WHERE SubjectName='课程设计'
)

四、MySQL函数

1.常用函数

-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1

-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('rsz'); -- 反转字符串

-- 时间日期函数
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();

2. 聚合函数
在这里插入图片描述
语法：

select 公式（字段名） from 表名；

语句：

SELECT COUNT(StudentName) FROM student; 
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;

SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;

五、mysql事务

1、事务的概念

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一* * 起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
事务通过事务的整体性以保证数据的一致性。
事务能够提高在向表中更新和插入信息期间的可靠性

总结：所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

二、事务的ACID特点

ACID，是指在可靠数据库管理系统(DBMS) 中，事务(transaction)应该具有的四个特性：原子性(Atomicity) 、一致性(Consistency )、隔离性(Isolation) 、持久性(Durability) 。这是可靠数据库所应具备的几个特性。

2.1 原子性（保证事务的整体性）

原子性：事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

事务是一个完整的操作，事务的各元素是不可分的。
事务中的所有元素必须作为一个整体提交或回滚。
如果事务中的任何元素失败，则整个事务将失败。

案例说明：

A给B转帐100元钱的时候只执行了扣款语句，就提交了，此时如果突然断电，A账号已经发生了扣款，B账号却没收到加款，在生活中就会引起纠纷。这种情况就需要事务的原子性来保证事务要么都执行，要么就都不执行。

2.2 一致性（保证数据的完整性）

一致性：指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

当事务完成时，数据必须处于一致状态。
在事务开始前，数据库中存储的数据处于一致状态。
在正在进行的事务中，数据可能处于不一致的状态。
当事务成功完成时，数据必须再次回到E知的一致状态。

案例说明：
对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致。例如，A的账户有1000元，B的账户有1000元，现在A转账200元给B，不管事务成功还是失败，转账前后A和B的存款总额都应该是2000元

2.3 隔离性

隔离性：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。

对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一一个使用相同数据的事务结束之后访问这些数据。
也就是说并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的

2.4 持久性

持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

指不管系统是否发生故障，事务处理的结果都是永久的。
一旦事务被提交，事务的效果会被永久地保留在数据库中。

总结：在事务管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是结果。

三、事务控制语句

BEGIN 或 START TRANSACTION：显式地开启一个事务。
COMMIT 或 COMMITWORK：提交事务，并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
SAVEPOINT S1：使用SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个 SAVEPOINT；“S1”代表回滚点名称。
ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点。

3.1 测试begin和commit（开始事务和提交事务）

BEGIN：显式地开启一个事务。
COMMIT：提交事务，并使已对数据库进行的所有修改变为永久性的。

先准备一个数据表：

 use school;
 create table account (
 id int(10) primary key not null,
 name varchar (40),
 money double
 );
 
 insert into account values(1, '张三', 1000);
 insert into account values(2, '李四', 1000);

例1：begin开启事务，修改数据未提交，退出后重新连接查看，数据未改变
在这里插入图片描述

例2： begin开启事务，修改数据后commit提交，退出后重新连接查看，数据改变

3.2 测试事务回滚rollback

ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
在这里插入图片描述

3.3 创建回滚点并进行回滚

SAVEPOINT S1：使用SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个SAVEPOINT；“S1”代表回滚点名称。
ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点。
在这里插入图片描述

qq_39075859

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql学习总结

下表中从表为student，外键为gradeid，指向grade表的gradeid字段（主键），通过**foreign key(自己的字段) references 主表(主表字段)**设置外键约束。添加字段 : ALTER TABLE 表名 ADD字段名列属性[属性]删除字段 : ALTER TABLE 表名 DROP 字段名。如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）
复制链接

扫一扫

专栏目录