MySQL
B站视频：视频
笔记是前29节
数据库分类
关系型数据库（SQL）
- 例如： MySQL,Oracle,sql Server,DB2,SQLlite
- 通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表，…
非关系型数据库: (NoSQL)
- Redis，MongDB
- 非关系型数据库，对象存储，通过对象的自身的属性来决定。
数据库管理系统（DBMS）
MySQL
安装数据库
不要使用exe安装，”流氓软件“最后卸载不干净
使用压缩包安装
\1. .msi 为安装版本，安装过程中自动配置
\2. .zip 为压缩版本，安装过程中手动配置
我选择的5.7 跟着狂神安装的 8.0好像不容易安装
下载了安装包之后解压到自己的电脑目录下 然后将MySQL目录下的bin文件路径复制到 我的电脑环境变量-》系统变量-》的path路径下
在文件目录中创建my.ini文件写入以下代码
[mysqld] basedir=G:\mysql\mysql-5.7.29-winx64\mysql-5.7.29\ datadir=G:\mysql\mysql-5.7.29-winx64\mysql-5.7.29\data\ port=3306 skip-grant-tables
注意：不用新建data文件 因为会自动创建
弹幕有人说8.0的版本datadir哪一行的存在会导致下面启动服务时不成功，需要注释掉。。。
管理员模式下运行CMD（命令提示符）
在CMD中切换到Bin目录下（输入cd /d G:\mysql\mysql-5.7.29-winx64\mysql-5.7.29\bin
要跳转到不同分区的时候，需要添加强制跳转参数 /d 。但是，如果进入的是当前目录的子目录，则可以不使用 /d 参数
输入mysqld --install 开始安装（此步骤8.0版本有人说会有一个dll错误
弹幕有人说 mysql8安装是mysql install中间没有短横杠
输入mysqld --initialize-insecure --user=mysql
在这里和狂神的视频不一样了 我的初始化出来的data文件和他不一样
输入net start mysql 启动sql
输入mysql -u root -p 注意p后面不要加空格
不用管密码直接回车
输入update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';修改密码为123456
输入flush privileges;刷新权限
输入exit退出
回到之前的ini文件将最后一句话删掉（加一个#号就行
输入net stop mysql停止服务
之后想要进入时直接输入 mysql -u root -p123456即可
sqlyog 注册码：在网上找的
使用sqlyog
创建一个库school
字符集选择utf8mb4 核对选utf8mb4_bin
创建一个表student
在历史记录里面可以看间对应操作的sql语句
基本命令行操作
------------------
-- 注释为两个横杠加空格 多行注释为/**/
-- 所有语句都是用分号结尾（狂神所说）
show databases; -- 显示所有数据库
use school; -- 这是命令不是语句所以不用加分号，切换到相应的数据库（school）
show table;-- 查看数据库中的所有的表
describe student; -- 显示表中的所有信息
create database westos; -- 创建一个数据库westos
exit;-- 退出连接
ctrl+c -- 强制终止错误的命令
网上查询到底什么时候加分号
MySQL如同大多数DBMS一样，不需要单条SQL语句后加分号，但特定DBMS可能必须在单条SQL语句后加分号；如果是多条SQL语句必须加分号（；），如果使用的是MySQL命令行，则必须用分号结束SQL语句
sqlyog的基本操作
进行可视化的操作，然后通过历史记录获得对应操作的代码命令进行学习。
-- 查看所有的数据库
SHOW DATABASES
-- 创建库【如果不存在】可以作为一个条件
CREATE DATABASE IF NOT EXISTS westos
-- 删除库【如果存在】
DROP DATABASE IF EXISTS westos
-- 使用数据库
USE school
-- 如果数据库中的表的变量和系统变量重名 加``来区分 反引号！！！
-- 使用student表中的user自己设定的变量
SELECT `user` FROM student
列的数据类型详解
数值
- tinyint 十分小的数据 一个字节
- smallint 较小的数据 两个字节
- mediumint 中等的数据 三个字节
- int 标准的数据 四个字节
- bigint 较大的数据 八个字节
- float 浮点数 四个字节
- double 浮点数 八个字节
- decimal 字符串形式的浮点数 一般金融计算
字符串
- char 字符串固定大小的 0~255
- varchar 可变字符串 0~65535 常用的评论
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 保存到文本
时间日期
- data YYYY-MM-DD 日期格式**（大写的M表示月份 和分钟区别开）**
- time HH:mm:ss 时间格式 （小写的H为12小时制 大写的为24小时制 ）
- datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数！
- year 年份表示
null
- 没有值 未知
- 注意，不要使用null进行运算
数据库字段属性
Unsigned
- 无符号的整数，声明了该列不能声明为负数
zerofil
- 用0来填充不足的位比如， 00001 为五位值的1
自增
-
通常理解为自增，自动在上一条记录的基础上+1（默认）
-
通常用来设计唯一的主键 ~index，必须是整数类型
-
可以自定义设计主键自增的初始值和步长， 在‘高级’的‘自动增量里’。
非空 Null not null
- 默认情况下为null 如果为not null，并且不给他赋值，就会报错
- 有特殊情况比如name的字符串有空字符串 不算空 此情况难以表达空的情况
创建一个数据库表
-- 目标：创建学生表 使用SQL创建
-- 学号int 登陆密码varchar(20) 姓名，性别 varvhar(2) ,出生日期(datament) ,家庭住址，email
-- 注意点，使用英文括号 表的名称和字段尽量使用`` 反引号括起来
-- AUTO INCREMENT 自增
-- 字符串使用 单引号括起来！
-- 所有语句后面加 英文的逗号 ，最后一个不用加
-- 括号后面加引擎等
CREATE TABLE IF NOT EXISTS `student`(
-- `字段名` 列类型 [属性][索引][注释] ,
-- 设置id 字段 整数类型可显示长度为4 非空 自增 注释为空 语句后面加括号表示未完
-- 每个参数中间的空格必须有 但是没有限定必须多少个空格 下面是为了分离清楚空格数目不整齐
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '',
-- 设置name 字段 字符串类型可显示长度为30 非空 默认为匿名 注释提示为姓名 语句后面加括号表示未完
`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'邮箱',
-- 设置主键为 id 不是单引号
PRIMARY KEY (`id`)
)-- [表类型][字符集设置][注释]
-- 使用INNODB为数据库引擎 一般默认就是INNODB
-- 使用CHARSET 设置字符集编码 默认情况下不支持中文 也可以修改ini文件来修改默认字符集但是不建议
ENGINE=INNODB DEFAULT CHARSET=utf8
SHOW CREATE DATABASE `school`-- 查看创建了数据库的语句
SHOW CREATE TABLE `student`-- 查看创建了student表的语句
DESC `student` -- 显示表的结构
MyISAM和InnoDB的区别
MYISAM是MYSQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MYISAM还使用一 种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK工具和用来恢复浪费空间的 MYISAMPACK工具。
MYISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。
INNODB和BERKLEYDB（BDB）数据库引擎都是造就MYSQL灵活性的技术的直接产品，这项技术就是MYSQL++ API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MYISAM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和 MYISAM引擎慢很多，但是INNODB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性 中的一者或者两者，那你就要被迫使用后两个引擎中的一个了.
MyISAM和InnoDB是两种最常用的数据库引擎，默认的引擎是MyISAM，性能最好，但不支持事务处理， 只有InnoDB支持事务。
根据介绍可知，CSV引擎是以逗号分割的存储方式，MEMORY是内存存储方式（适用于于临时表）。
如果需要事务处理（含XA）就必需使用： InnoDB
狂神视频截图：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
在物理空间的存在位置
所有的数据库文件都存在data目录下，本质是还是文件的存储。
MySQL引擎在物理文件上的区别
- InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件（8.0以上是idb，也是表的数据文件）
- MYISAM对应文件有：
- *.frm 表结构的定义文件
- *.MYD 数据文件 data
- *.MYI 索引文件 index
删除和修改数据表字段
-- 修改表名 ALTER TABLE 旧表名 RENMAE AS 新表名
ALTER TABLE teacher RENMAE AS teacher1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 字段名重命名CHANGE
ALTER TABLE teacher1 CHANGE age age1 INT(1)
-- 修改约束MODIFY 后来发现change也可以修改为约束。。
ALTER TABLE teacher1 MODIFY age VARACHAR(11)
-- 删除表的字段 DROP
ALTER TABLE teacher1 DROP age1
-- 删除表
DROP TABLE IF EXSTS teacher1
-
所有的创建和删除尽量加上判断，以免报错
-
尽量用反引号将字段名包起来
-
sql关键词其实对大小写不敏感
数据库外键（了解即可）
例如 学生要有年级但是年级这个属性很难用一个字段表示出来
这个时候就要再开一个年级表。将学生表的年级属性绑到年级表中，想获取年级时需要到对应的年级表中寻找。
学生的grade 列引用 年级表的id（约束）
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR (50) NOT NULL COMMENT '年纪名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
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'邮箱',
PRIMARY KEY (`id`),
`gradeid` INT(10) NOT NULL COMMENT'学生的年级',
-- 定义外键 key 约束名为FK_gradeid
KEY `FK_gradeid` (`gradeid`),
-- 给这个外键添加约束（执行引用)
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
-- 另一种方法 创建表之后再添加约束
-- 最后的定义外键以及建立约束都可以写在外面且合成一句话
-- ALTER TABLE `表` ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表 (那个字段)
ALTER TABLE `student` ADD CONSTRAINT `FK-gradeid` FOREIGN KEY (`gradeid`) REFERENCES 'grade'(`gradeid`);
阿里规范手册明确规定不允许使用外键，否则更新删除都会很麻烦，要放在应用层去解决
DML语言
插入语句（添加）
语法:insert into 表名 ([字段名1，字段2 ，字段3]) values('值1'),('值2'),('值3')
INSERT INTO `grade` (`gradename`) VALUES('大四'),('大三')
-- 有时由于主键自增我们可以省略字段名 他就会一一匹配 否则就要将字段和数据一一对应好
-- 如果有非空的字段，则必须也要同时插入，保证插入的这一行要满足要求
-- 注意要求gradeid设置 非空 并且要有默认值才能这样
修改语句
语法：UPDATE 表名 SET colnum_name=value, WHERE [条件];
colnum_name 是数据库的列（字段名） value是数据库的数据，value（有时）会等于时间函数来获取时间
条件可以是等于大于小于某个值的数据进行修改
还可以是 BETWEN 3 AND 5 3和5之间的修改
或者满足多个条件用AND连接name='狂神 AND sex='男'
-- 修改学员姓名，带了条件
UPDATE `student` SET `name`='狂神' WHERE id =1;
-- 不指定条件的情况下，会改动所有的表！！！
UPDATE `student` SET `name`='长江七号'
删除语句
语法：delete from 表名[where 条件]
-- 删除数据（避免这样写，会删除全部数据）
DELETE FROM `student`
-- 另一种写法
TRUNCATE `student`
-- 删除指定数据 删除表中id为1的数据
DELETE FROM `student` WHERE id=1;
delete和truncate的区别
- 相同点 ：都能删除数据，都不会删除表的结构
- 不同：
- truncate 重新设置自增列，计数器会归为零
- truncate 不会影响事务
- delete是数据操作语言（DML）命令；而truncate是数据定义语言（DDL）命令。
- truncate只能删除全部数据不能像delete一样有条件只删除个别数据
- truncate执行速度更快
- delete可以通过rollback回滚
白话：truncate相当于也完全清空该表的历史记录，当在往这个表中插入数据，自增的字段名（例如id）会从零开始从新自增，不会像delete一样会接着删除的id继续自增
DQL查询数据（重点）
使用select简单复杂的查询都能够做.超级有用
视频中的代码：(2条消息) 狂神说Java MySQL P16 school.sql_xiaoQQya的博客-CSDN博客
-- 查询 SELECT 字段 FROM 表
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名，给结果起一个名字 AS(AS可省略) 可以给字段起别名 也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName`AS 学生姓名 FROM student AS S
-- 函数 Concat(a,b) 显示一个新表内容为concat合在了一起，字段名也为as出的别名
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student
-- 查询全部的考生的考试成绩 使用*号
SELECT * FROM result
-- 查询有哪些同学参加了考试(发现学号随着不同科目成绩登录多次导致学号出现重复)
SELECT `studentNo` FROM result
-- 使用distinct去重
SELECT DISTINCT `studentNo` FROM result
-- 除此之外，select还有许多可以应用的场景
SELECT VERSION() --可以查询系统版本（+函数）
SELECT 100*3-1 AS 计算结果 -- 计算（+表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（+变量）
-- 直接实现exel表的功能 所有成绩+1分
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
逻辑运算符：
| 运算符 | 语法 | 结果 |
|---|---|---|
| AND && | a AND b a&&b | 逻辑与 |
| OR || | a OR b a||b | 逻辑或 |
| NOT != | NOT a !a | 逻辑非 |
-- 查询考试成绩在95分到100分之间
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 AND StudentResult<=100
-- 模糊查询（区间）
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学成绩
SELECT `StduentNo`,`StudentResult` FROM result
WHERE NOT StudentNo = 1000
比较运算符：
| LIKE | a LIKE b | 如果a匹配到b则为真 |
|---|---|---|
| IN | a IN (a1,a2,a3…) | 如果a等于括号中任意一个为真 |
-- 查询姓刘的同学
-- like结合 %（代表0到任意字符） _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%';
-- 查询姓刘的同学，名字后只有一个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_';
-- 查询姓刘的同学，名字后只有两个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘__';
-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';
-- 查询学号为1001或1002或1003的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询地址为空的学生 空字符串为'' 或者为null
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=''OR address IS NULL
-- 查询有出生日期的同学（白话文） -》就是不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;
joins
| 操作 | 描述 | 对应上面的图片 |
|---|---|---|
| INNER JOIN | 如果表中至少有一个符合，就返回行 | 中间那个 |
| LEFT JOIN | 从左表返回所有的行，即使右表中没有匹配也会跟着显示null（右表不足的地方null填充） | 左上角 |
| RIGHT JOIN | 从右表返回所有的行，即使左表中没有匹配也会跟着显示null（左表不足的地方null填充） | 右上角 |
/*
要求：查询参加考试的同学 （学号，姓名，考试编号，分数）
1. 分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中 studentNo = 成绩表中 studentNo
*/
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s -- AS可省略
INNER JOIN result AS r
ON s.studentNo=r.studentNo
/*
JION（表） ON （判断的条件）连接查询 （尽量用这个
where 等值查询
on 条件是在生成临时表时使用的条件，它不管on中的条件是否为真都会返回左边表中的记录，还会返回on条件为真的记录
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有leftjoin的含义（必须返回左边表的记录）了，条件不为真的就全部过略掉
因此这两个还是不一样的 尽量先使用join on然后如果还有需要再使用where筛选
*/
-- 查询缺考的同学
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL-- 学生存在信息但是成绩为空
-- 查询了参加 数据库 考试同学的信息：学号：学生姓名：科目名：分数
-- 联系了三个表 学生信息表 学生成绩表 学生科目表
SELECT s.`studentNo`,`studentName`,`SubjectName`,`studentResult`
FROM student s
RIGHT JOIN result r-- 查询成绩所以以成绩表为中心。。。。感觉不对劲没有姓名的成绩也出来了
ON r.studentNo=s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo -- 查询科目名字 凭借两个表中的科目id相同
WHERE subjectName=’数据结构-1‘
弹幕有人说：inner join是并集，可选择条件的膨胀/扩张返回数据，
自连接
-- 视频代码
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id,顶点父id为1',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES ('2', '1', '信息技术'),
('3', '1', '软件开发'),
('4', '3', '数据库'),
('5', '1', '美术设计'),
('6', '3', 'web开发'),
('7', '5', 'ps技术'),
('8', '2', '办公信息');
-- 适用于将一张表分开
-- 比如 两层树形结构表格化
-- 有一个表格存在三列存放自己的id及其父类id以及自己的名称 要求创建一个新表存在两列存放自己的名称及其父类的名称
SELECT a.`categoryName` AS '父栏目', b.`categoryName`AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
效果:
分页和排序
ORDER BY 字段名 ASC -- 将该表按照字段名升序排序
ORDER BY 字段名 DESC -- 将该表按照字段名降序排序
limit 起始值 页面大小 -- limlit格式
limit 0,5 -- 从1~5条数据显示在这一页 （索引从0开始
limit 5,5 -- 从6~10条数据显示在这一页
limit (n-1)*page_size , page_size -- page_size 页面大小
-- 上面两个可以用于显示排名前十的同学
子查询
/*
什么是子查询?
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/
-- 查询课程为 高等数学-2 且分数不小于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 = '高等数学-2' AND StudentResult>=80
-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-2'
)
-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)
Mysql常用函数
数据函数：
SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机小数*/
SELECT SIGN(x); /*判断符号函数: 根据x的正负，负数返回-1,正数返回1,0返回0*/
字符串函数：
SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
SELECT CONCAT('我','爱','程序'); /*合并字符串,参数可以有多个*/
SELECT INSERT('我爱编程helloworld',2,4,'超级热爱'); /*替换字符串,从某个位置替换某个长度。把 超级热爱 替换 我爱 */
SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); /*替换字符串,把 努力 替换 坚持 */
SELECT LOWER('KuangShen'); /*小写*/
SELECT UPPER('KuangShen'); /*大写*/
SELECT LEFT('hello,world',5); /*从左边截取 5 位*/
SELECT RIGHT('hello,world',5); /*从右边截取 5 位*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度。。坚持就能成功*/
SELECT REVERSE('狂神说坚持就能成功'); /*反转
-- 查询姓周的同学,改成邹
SELECT REPLACE(studentname,'周','邹') AS 新名字
FROM student WHERE studentname LIKE '周%';
日期和时间函数：
SELECT CURRENT_DATE(); /*current_date 获取当前日期*/
SELECT CURDATE(); /*curdate 获取当前日期*/
SELECT NOW(); /*now 获取当前日期和时间*/
SELECT LOCALTIME(); /*localtime 获取当前日期和时间*/
SELECT SYSDATE(); /*sysdate 获取当前日期和时间*/
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
系统信息函数：
SELECT VERSION(); /*版本*/
SELECT USER(); /*用户*/
聚合函数（常用）
-- 聚合函数
/*COUNT:统计数据条数*/
SELECT COUNT(studentname) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student; /*推荐*/
-- 摘自博客
-- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
-- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
-- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
-- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
/*
很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。
下面它们之间的一些对比：
1）在表没有主键时，count(1)比count(*)快
2）有主键时，主键作为计算条件，count(主键)效率最高；
3）若表格只有一个字段，则count(*)效率较高。
*/
-- 弹幕上有人说高版本要加上any_value(`subjectName`) 否则报错
-- any_value 会选择 被分到同一组的 数据 里第一条 数据的指定列值作为返回数据
SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;
紧跟着一个题目
-- 查询 不同课 程的平均分,最高分,最低分,要求筛选出来的平均分大于80，并根据不同的课程进行分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;
/*
为什么用having？
因为having是分组后筛选，而where是在分组前筛选 ,并且where后面不能跟着聚合函数，例如avg函数
所以只能使用having
*/
MD5加密
具有不可逆性 破解网站背后都是有一个词典然后一个一个对比
CREATE TABLE `testmd5` (
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')
-- 如果我们要对pwd这一列数据都进行加密，语法是：
UPDATE testmd5 SET pwd = MD5(pwd);
-- 如果单独对某个用户(如kuangshen)的密码加密，使用where筛选确定
INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
UPDATE testmd5 SET pwd = MD5(pwd) WHERE NAME = 'kuangshen2';
-- 插入新的数据时自动加密，直接使用函数即可
INSERT INTO testmd5 VALUES(4,'kuangshen3',MD5('123456'));
-- 查询登录用户信息（md5的常用用法，查看用户输入加密后的密码进行比对）
SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');
事务
要么都成功要么都失败
将一组SQL放在一个批次中执行
事务原则 ： ACID原则 原子性，一致性，隔离性，持久性
原子性（Atomicity）
要么都成功，要么都失败
一致性（Consistency）
事务前后的数据完整性要保持一致
持久性（Durability）–事务提交
事务一旦提交就不可逆转，被持久化到数据库中
隔离性
事务产生多并发时，互不干扰
隔离产生的问题
脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）
执行事务
-- mysql 默认自动开启事务提交~！！
SET autocommit=0 -- 关闭自动提交
SET autocommit=1 -- 开启（默认的）
-- 以下为手动处理一段事务的步骤
-- 【首先关闭自动提交
SET autocommit =0 -- 关闭自动提交
-- 然后开启新事务
START TRANSACTION -- 标记一个事务的开始，从这个之后的SQP都在同一个事务内
-- 进行想要的操作
INSERT XX
INSERT XX
-- 然后进行提交 ： 持久化(成功)
COMMIT
-- 如果出现问题（失败），回滚： 回到原来的样子， 可以直接回到最开始的样子
ROLLBACK
-- 最后这一段事务结束 开启自动提交 】
SET autocommit = 1 -
-- 以下了解即可
SAVEPOINT 保存点名称 -- 设置一个事务的保存点 （存档）
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点（从存档重新开始）
RELEASE SAVEPOINT 保存点 -- 删除保存点 （移除存档）
模拟银行转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci -- 创建一个新库
USE shop -- 使用新库
CREATE TABLE `account`( --创建新表
`id` INT(3) 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)
-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一组新事务
-- 执行操作
UPDATE account SET money = money-500 WHERE `name` = 'A' -- A 转账给B，少500
UPDATE account SET money = money+500 WHERE `name` = 'B' -- B 收到钱，加500
COMMIT ; -- 提交事务 成功后不可更改，回滚也不行
-- 如果提交出现错误，说明该事务错误 执行回滚
ROLLBACK ; -- 回滚
SET autocommit=1 -- 记得一定要恢复默认值
402
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
