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