MySQL

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、操作数据库(了解)

  1. 创建数据库

    -- 如果不存在就创建qwer
    CREATE DATABASE IF NOT EXISTS qwer
    
  2. 删除数据库

    -- 如果存在qwer就删除
    DROP DATABASE IF EXISTS qwer
    
  3. 使用数据库

    -- tab键的上面,如果你的表名或者字段名是一个特殊字符,就需要带``
    USE `school`
    
  4. 查看数据库

    -- 查看所有数据库
    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 早些年使用
*/
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间的大小较小较大,约为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’),…

注意点:

  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 [条件]

注意点:

  1. colnum_name 是数据库的列,尽量带上``

  2. 条件,筛选的条件,如果没有指定,则会修改所有的列

  3. 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 NULLa is null如果操作符为NULL,结果为真
IS NOT NULLa is not null如果操作符为not null,结果为真
BETWEENa between b and c若a在b和c之间,结果为真
Likea like bSQL匹配,如果a匹配b,结果为真
Ina 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会从右表中返回所有的值,即使左表中没有匹配

自连接

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

主表

在这里插入图片描述

父类

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

子类

pidcategoryidcategoryName
34数据库
36web开发
57PS技术
28办公信息

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

父类子类
信息技术办公信息
软件开发数据库
软件开发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
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值