MySQL

最新推荐文章于 2023-09-19 23:00:19 发布

J练习生

最新推荐文章于 2023-09-19 23:00:19 发布

阅读量748

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/m0_51458935/article/details/119064552

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Mysql

1、数据库常用命令

查看有哪些数据库

show databases;//这个不是SQL语句，属于MySQL的命令

创建属于我们自己的数据库

create database school; //这个不是SQL语句，属于MySQL的命令

使用school数据

use school; //这个不是SQL语句，属于MySQL的命令

查看当前使用的数据库中有哪些表？

show tables; //这个不是SQL语句，属于MySQL的命令

初始化数据

source 目录 //sql脚本的数据量太大是不能直接打开的，使用source命令导入

删除数据库
```
drop database 库名;
```

2、DB DBMS SQL之间的关系

DB:数据库
DBMS:数据库管理系统
SQL:结构化查询语句

DBMS -(执行)-> SQL -(操作)-> DB

3、SQL语句的分类

DQL（数据查询语言）: 查询语句，凡是select语句都是DQL
DML（数据操作语言）：insert delete update，对表当中的数据进行增删改
DDL（数据定义语言）：create drop alter，对表结构的增删改
TCL（事务控制语言）：commit提交事务，rollback回滚事务(TCL中的T是

Transaction)
DCL（数据控制语言）: grant授权、revoke撤销权限等

4、操作数据库

4.1、操作数据库（了解）

创建数据库

-- 如果不存在就创建qwer
CREATE DATABASE IF NOT EXISTS qwer

删除数据库

-- 如果存在qwer就删除
DROP DATABASE IF EXISTS qwer

使用数据库

-- tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``
USE `school`

查看数据库

-- 查看所有数据库
show DATABASES

4.2、数据库的列类型

数值
字符串
时间日期
null

4.3、数据库的字段属性（重点）

Unsigned:

无符号的整数
声明了该列不能声明为负数

zerofill:

0填充的
不足的位数，使用0来填充，int(3)，5—005

自增：

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

非空 :

假设设置为not null，如果不给它赋值，就会报错
NULL，如果不填写值，默认就是null

默认：

设置默认值
sex，默认值为男，如果不指定该列的值，则会有默认的值

拓展：

/*每一个表，都必须存在以下五个字段！未来做项目用的，表示一个记录存在意义！

id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间

*/

4.4、创建数据库表（重点）

CREATE TABLE IF NOT EXISTS `student` (
    `id` int(4) NOT NULL COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` CHAR(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`) 

)ENGINE=INNODB DEFAULT CHARSET=utf8

常用命令

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

4.5、数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规使用操作

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

在物理空间存在的位置

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

本质还是文件的存储

MySQL引擎在物理文件上的区别

innoDB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
MYISAM 对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）

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

CHARSET = utf8

不设置的话，会是mysql默认的字符集编码，不支持中文

mysql的默认编码是Latin1，不支持中文

4.6、修改删除表

修改

-- 修改表名
ALTER TABLE student RENAME student1
-- 增加表的字段
ALTER TABLE student1 ADD hobby VARCHAR(50)
-- 修改表的字段
ALTER TABLE student1 MODIFY sex1 VARCHAR(2) -- 修改约束
ALTER TABLE student1 CHANGE sex1 sex char(2) -- 字段重命名

-- 删除表的字段
ALTER TABLE student1 DROP hobby

删除

-- 删除表
DROP TABLE IF EXISTS student1

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

注意点

`` 字段名，使用这个包裹
注释 – 或者 /**/
sql关键字大小写不敏感，建议用小写
所有的符号全部用英文

5、MySQL数据管理

5.1、外键（了解即可）

方式一：在创建表的时候直接添加外键约束

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

-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束 （执行引用） references 引用

CREATE TABLE
IF NOT EXISTS `student` (
	`id` INT (4) NOT NULL COMMENT '学号',
	`name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR (20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` CHAR (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

CREATE TABLE
IF NOT EXISTS `student` (
	`id` INT (4) NOT NULL COMMENT '学号',
	`name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR (20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` CHAR (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`)
) ENGINE = INNODB DEFAULT CHARSET = utf8

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

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY 作为外键的列 REFERENCES 哪个表(那个字段)

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

最佳实践

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

5.2、DML语言（全部记住）

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

DML语言：数据操作语言

insert
update
delete

5.3、添加

insert

-- 插入语句
-- INSERT INTO 表名(字段1,字段2,字段3) VALUES('值1'),('值2'),('值3'),....
INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO `grade` VALUES('大五')-- 会报错，没有一一对应

-- 一般写插入语句，我们一定要数据和字段一一对应

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

INSERT INTO `student`(`name`) VALUES(`王迪`)

INSERT INTO `student`(`name`,`pwd`,`sex`,`birthday`) 
VALUES('王二维','aswqd1','男','2020-05-06'),('王撒旦','asder','男','2020-01-02')

语法：INSERT INTO 表名(字段1,字段2,字段3) VALUES(‘值1’),(‘值2’),(‘值3’),…

注意点：

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

5.4、修改

update

-- 修改学生名字
UPDATE `student` set `name`='威威' WHERE id = 1;

-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='微微';

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='长春',`sex`='女' where `id` = 1;

-- 语法：update 表名 set colnum_name = value,colnum_name = value where [条件]

条件：where子句运算符 id等于某个值，大于某个值，在某个区间内修改

操作符会返回布尔值

在这里插入图片描述

-- 通过多个条件定位数据
UPDATE `student` SET `name`='张山' WHERE `name`='微微' AND `sex` = '女'

语法：update 表名 set colnum_name = value,colnum_name = value where [条件]

注意点：

colnum_name 是数据库的列，尽量带上``
条件，筛选的条件，如果没有指定，则会修改所有的列
value，是一个具体的值，也可以是一个变量

5.5、删除

delete命令

语法：delete from 表名 where 条件

-- 删除数据 （避免这样写，会全部删除）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` where id = 1;

TRUNCATE 命令

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

-- 清空student表
TRUNCATE TABLE `student`

delete 和 TRUNCATE 的区别

相同点：都能删除数据，而且都不会删除表结构
不同：
- delete 不会影响自增计数器不归零
- TRUNCATE 重新设置自增列计数器归零
- TRUNCATE 不会影响事务

6、DQL查询数据（重点）

6.1、DQL

（Data Query LANGUAGE：数据查询语言）

所有的查询操作都用它 Select
简单的查询，复杂的查询它都能做
数据库中最核心的语言，最重要的语句
使用频率最高的语句

表的创建

-- 表架构
-- student(sid,sname,sage,ssex) 学生表 
-- course(cid,cname,tid) 课程表 
-- sC(sid,cid,score) 成绩表 
-- teacher(tid,tname) 教师表


-- DQL
CREATE DATABASE `school`

USE `school`

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL,
  `cname` varchar(30) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('3001', '语文', '4');
INSERT INTO `course` VALUES ('3002', '数学', '2');
INSERT INTO `course` VALUES ('3003', '英语', '1');
INSERT INTO `course` VALUES ('3004', '物理', '3');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('101', '3001', '90');
INSERT INTO `sc` VALUES ('102', '3001', '85');
INSERT INTO `sc` VALUES ('103', '3001', '76');
INSERT INTO `sc` VALUES ('105', '3001', '87');
INSERT INTO `sc` VALUES ('106', '3001', '66');
INSERT INTO `sc` VALUES ('108', '3001', '96');
INSERT INTO `sc` VALUES ('101', '3002', '92');
INSERT INTO `sc` VALUES ('102', '3002', '81');
INSERT INTO `sc` VALUES ('103', '3002', '93');
INSERT INTO `sc` VALUES ('104', '3002', '73');
INSERT INTO `sc` VALUES ('105', '3002', '65');
INSERT INTO `sc` VALUES ('108', '3002', '96');
INSERT INTO `sc` VALUES ('101', '3003', '96');
INSERT INTO `sc` VALUES ('102', '3003', '85');
INSERT INTO `sc` VALUES ('103', '3003', '76');
INSERT INTO `sc` VALUES ('104', '3003', '63');
INSERT INTO `sc` VALUES ('105', '3003', '59');
INSERT INTO `sc` VALUES ('106', '3003', '56');
INSERT INTO `sc` VALUES ('107', '3003', '91');
INSERT INTO `sc` VALUES ('108', '3003', '86');
INSERT INTO `sc` VALUES ('101', '3004', '100');
INSERT INTO `sc` VALUES ('102', '3004', '83');
INSERT INTO `sc` VALUES ('103', '3004', '75');
INSERT INTO `sc` VALUES ('104', '3004', '69');
INSERT INTO `sc` VALUES ('105', '3004', '50');
INSERT INTO `sc` VALUES ('106', '3004', '52');
INSERT INTO `sc` VALUES ('107', '3004', '87');
INSERT INTO `sc` VALUES ('108', '3004', '78');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL,
  `sname` varchar(30) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `ssex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('101', '龙大', '18', '男');
INSERT INTO `student` VALUES ('102', '熊二', '19', '男');
INSERT INTO `student` VALUES ('103', '张三', '18', '男');
INSERT INTO `student` VALUES ('104', '李四', '19', '女');
INSERT INTO `student` VALUES ('105', '王五', '20', '男');
INSERT INTO `student` VALUES ('106', '李华', '19', '男');
INSERT INTO `student` VALUES ('107', '李红', '19', '女');
INSERT INTO `student` VALUES ('108', '李明', '20', '男');
INSERT INTO `student` VALUES ('109', '贝贝', '19', '女');
INSERT INTO `student` VALUES ('110', '娜娜', '20', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL,
  `tname` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '叶平');
INSERT INTO `teacher` VALUES ('2', '李龙');
INSERT INTO `teacher` VALUES ('3', '李逍遥');
INSERT INTO `teacher` VALUES ('4', '朱钊');

select语法

在这里插入图片描述

6.2、指定查询字段

-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM student

-- 查询指定字段
SELECT `sid`,`sname` FROM student

-- 别名，给结果起一个名字  AS 可以给字段起别名，也可以给表起别名
SELECT `sid` AS 编号,`sname` AS 姓名 FROM student AS s

-- 函数  Concat(a,b)
SELECT CONCAT('姓名：',sname) AS 新名字 FROM student

语法：select 字段... from 表

注意：有的时候，列的名字不那么见名知意。我们取别名 AS。字段名 AS 别名表名 AS 别名

去重 distinct

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

-- 查询有哪些同学参加了考试，成绩SELECT * FROM sc  -- 查询全部的考试成绩SELECT sid from sc  -- 查询有哪些同学参加了考试SELECT DISTINCT sid from sc -- 发现重复数据，去重

数据库的列

SELECT VERSION() -- 查询系统版本（函数）SELECT 200*3+200 AS 计算结果  -- 用来计算（表达式）SELECT @@auto_increment_increment -- 查询自增的步长（变量）-- 学员考试成绩+1分查看SELECT `sid`,`score`+1 AS 提1分后 FROM sc

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量…

语法：select 表达式 from 表

6.3、where条件子句

作用：检索数据中符合条件的值

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

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or \|\|	a or b a\|\|b	逻辑或，其中一个为真，结果为真
Not !	not a !a	逻辑非，真为假，假为真

尽量使用英文字母

select sid,score from sc-- 查询考试成绩在85~100分之间(and,&&)SELECT sid,score from sc where score>=85 and score<=100SELECT sid,score from sc where score>=85 && score<=100-- 模糊查询（区间）SELECT sid,score from sc WHERE score BETWEEN 85 AND 100-- 查询除了101号学生之外的同学的成绩(!=,not)SELECT sid,score from sc where sid != 101SELECT sid,score from sc where not sid = 101

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符为not null，结果为真
BETWEEN	a between b and c	若a在b和c之间，结果为真
Like	a like b	SQL匹配，如果a匹配b，结果为真
In	a in (a1,a2,a3…)	假设a在a1，或者a2… 其中的某一个值中，结果为真

-- 模糊查询-- 查询姓李的同学-- like结合  %（代表0到任意个字符）   _（一个字符）SELECT sid,sname from student where sname like '李%'-- 查询姓李的同学，名字后面只有一个字的SELECT sid,sname from student where sname like '李_'-- 查询姓李的同学，名字后面只有两个字的SELECT sid,sname from student where sname like '李__'-- 查询名字中有红字的同学SELECT sid,sname from student where sname like '%红%'-- === in(具体的一个或多个值) ===-- 查询101，102，103号学生SELECT sid,sname from student where sid in (101,102,103)-- 查询在河南商丘和河南安阳的学生SELECT sid,sname,address from student where address in ('河南商丘','河南安阳')-- === null    not null ===-- 查询地址为空的学生 null ''SELECT sid,sname,address from student where address = '' or address is null-- 查询地址不为空的同学SELECT sid,sname,address from student where address is NOT null-- 查询地址为null的同学SELECT sid,sname,address from student where address is null

6.4、联表查询

JOIN 对比

在这里插入图片描述

-- === 联表查询 ===-- 查询参加了考试的同学（学号，姓名，科目编号，分数）select * from studentselect * from sc/*思路：1. 分析需求，分析查询的字段来自哪些表，student,sc（连接查询）二表连接2. 确定使用哪种连接查询？ 7种确定交叉点（这两个表中哪个数据是相同的）判断的条件：学生表中的 sid = 成绩表中的 sid*/-- join （连接的表） on （判断的条件）连接查询-- where 等值查询-- INNER JOINselect s.sid,sname,cid,scorefrom student sINNER JOIN sc con s.sid = c.sid-- RIGHT JOINselect s.sid,sname,cid,scorefrom student sRIGHT JOIN sc con s.sid = c.sid-- LEFT JOINselect s.sid,sname,cid,scorefrom student sLEFT JOIN sc con s.sid = c.sid-- 查询缺考的同学select s.sid,sname,cid,scorefrom student sLEFT JOIN sc con s.sid = c.sidwhere score is null-- 查询参加考试的同学信息（学号，姓名，科目名，分数）三表连接/*思路：1. 分析需求，分析查询的字段来自哪些表，student,sc,course（连接查询）2. 确定使用哪种连接查询？ 7种确定交叉点（这两个表中哪个数据是相同的）判断的条件：学生表中的 sid = 成绩表中的 sid*/select s.sid,sname,cname,scorefrom student sRIGHT JOIN sc con s.sid = c.sidINNER JOIN course coon c.cid = co.cid-- 我要查询哪些数据 select ...-- 从哪几个表中查 from表  xxx join 连接的表 on 交叉条件-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

操作	描述
Inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

自连接

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

主表

在这里插入图片描述

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
3	6	web开发
5	7	PS技术
2	8	办公信息

思考：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	PS技术

-- 查询父子信息：把一张表看为两张一摸一样的表select a.categoryName as 父栏目,b.categoryName as 子栏目from category a,category bwhere a.categoryid = b.pid

6.5、分页和排序

排序

-- 排序：升序ASC，降序DESC-- ORDER BY 通过哪个字段排序，怎么排-- 查询的结果根据成绩降序排序select s.sid,sname,cname,scorefrom student sRIGHT JOIN sc con s.sid = c.sidINNER JOIN course couon c.cid = cou.cidORDER BY score DESC

分页

-- 为什么要分页-- 缓解数据库压力，给人的体验更好-- 分页，每页只显示五条数据-- 语法，limit 当前页，页面的大小select s.sid,sname,cname,scorefrom student sRIGHT JOIN sc con s.sid = c.sidINNER JOIN course couon c.cid = cou.cidORDER BY score DESClimit 0,5-- 第一页  limit 0,5    (1-1)*5-- 第二页  limit 5,5    (2-1)*5-- 第三页  limit 10,5    (3-1)*5-- 第n页  limit (n-1)*5,5    (n-1)*pageSize-- pageSize:页面大小-- (n-1)*pageSize：起始值-- n：当前页-- 数据总数/页面大小 = 总页数-- 练习：查询 语文 成绩排名前3的学生，并且分数要大于70分的学生信息（学号，姓名，课程名称，分数）select s.sid,sname,cname,scorefrom student sRIGHT JOIN sc con s.sid = c.sidINNER JOIN course couon c.cid = cou.cidwhere cname = '语文' and score>70ORDER BY score DESClimit 0,3

语法：limit(查询起始下标，pageSize)

6.6、子查询

6.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分-- 核心：（根据不同的课程分组）SELECT cname,AVG(score) as 平均分,MAX(score) as 最高分,MIN(score) as 最低分FROM course couINNER JOIN sc con cou.cid = c.cidGROUP BY cou.cidHAVING 平均分>80

7、MySQL函数

7.1、常用函数

-- === 常用函数 ===-- 数学运算select ABS(-5) -- 绝对值select CEILING(2.3) -- 向上取整select FLOOR(3.5) -- 向下取整select RAND() -- 返回一个0~1的随机数select SIGN(-20) -- 判断一个数的符号 0-0  负数返回-1  整数返回1-- 字符串函数SELECT CHAR_LENGTH('qweasdzxc') -- 字符串长度SELECT CONCAT('i','lo','you') -- 拼接字符串SELECT INSERT('我爱你',1,2,'超级') -- 查询，替换，从某个位置开始替换某个长度SELECT LOWER('ASD') -- 小写字母SELECT UPPER('asd') -- 大写字母

7.2、聚合函数(常用)

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
…	…

7.3、数据库级别的MD5加密

什么是MD5？

主要增强算法复杂度和不可逆性。

MD5不可逆，具体的值的MD5是一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密的前值

-- === 测试MD5 加密 ===CREATE table testmd5(id int(4) NOT NULL,`name` VARCHAR(20) NOT NULL,pwd VARCHAR(50) NOT NULL)ENGINE=INNODB DEFAULT CHARSET=utf8-- 明文密码INSERT INTO testmd5(`id`,`name`,`pwd`) VALUES(1,'zhangsan','12345'),(2,'lisi','12345'),(3,'wangwu','12345')-- 加密UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码-- 再插入的时候加密INSERT INTO testmd5(`id`,`name`,`pwd`) VALUES(4,'xiao',MD5('12345'))-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值SELECT * from testmd5 where `name`="xiao" and pwd=MD5('12345')

8、事务

8.1、什么是事务

要么都成功，要么都失败

将一组SQL放在一个批次中去执行

事务原则：ACID原则原子性，一致性，隔离性，持久性（脏读，虚读…)

原子性

要么都成功，要么都失败

一致性

事务前后的数据完整性要保证一致

持久性

事务一旦提交则不可逆，被持久化到数据库中

隔离性

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

隔离所导致的一些问题

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。

虚读（幻读）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

-- === 事务 ===-- mysql 是默认开启事务自动提交的set autocommit = 0  -- 关闭set autocommit = 1  -- 开启-- 手动处理事务set autocommit = 0 -- 关闭自动提交-- 事务开启START TRANSACTION   -- 标记一个事务的开始，从这个之后的sql都在同一个事务内INSERT **INSERT **-- 提交：持久化（成功！）-- 回滚：回到原来的样子（失败！）-- 事务结束set autocommit = 1 -- 开启自动提交SAVEPOINT 保存点名  -- 设置一个事务的保存点ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点RELEASE SAVEPOINT 保存点名 -- 撤销保存点

-- 转账CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ciUSE shopCREATE table account(`id` INT(3) not null,`name` VARCHAR(30) not NULL,`money` DECIMAL(9,2) not null,PRIMARY KEY (`id`))ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO account(`name`,`money`)values ('A',2000.00),('B',10000.00)-- 模拟转账：事务set autocommit = 0; -- 关闭自动提交START TRANSACTION -- 开启一个事务UPDATE account set money=money-500 where `name`='A'UPDATE account set money=money+500 where `name`='B'COMMIT;  -- 提交事务，就被持久化了ROLLBACK;  -- 回滚set autocommit = 1;

9、索引

9.1、索引的分类

-- 索引的使用-- 1、在创建表的时候给字段增加索引-- 2、创建完毕后，增加索引-- 显示所有的索引信息show INDEX from student-- EXPLAIN 分析sql执行的状况EXPLAIN SELECT * from student  -- 非全文索引

在这里插入图片描述

例子

创建表，在表中添加一百万条值（省略）

-- 添加索引-- id_表明_字段名-- CREATE INDEX 索引名 on 表（字段）CREATE INDEX id_student_sname on student(sname);

然后进行对比（没有索引和有索引），上边有索引，下没有

在这里插入图片描述

索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显。

9.2、索引原则

索引不是越多越好
不要对经常变动数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引

Btree：InnoDB的默认数据结构

乐观锁

乐观锁实现方式：

取出记录时，获取当前version
更新时，带上version
执行更新时，set version = newVersion where version = oldVersion
如果version不对，就更新失败

乐观锁：1、先查询，获得版本号 version = 1
-- A线程
update user set name = "zhangsan", version = version + 1
where id = 2 and version = 1

-- B线程，抢先完成，这个时候version = 2，会导致A修改失败！
update user set name = "zhangsan", version = version + 1
where id = 2 and version = 1

J练习生

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL

Mysql1、数据库常用命令查看有哪些数据库show databases;//这个不是SQL语句，属于MySQL的命令创建属于我们自己的数据库create database school; //这个不是SQL语句，属于MySQL的命令使用school数据use school; //这个不是SQL语句，属于MySQL的命令查看当前使用的数据库中有哪些表？show tables; //这个不是SQL语句，属于MySQL的命令初始化数据source 目录 //sq
复制链接

扫一扫

专栏目录