目录
1初识MySQL
1.1数据库分类
关系型数据库（SQL）：
-
MySql,Oracle,Sql Server,DB2.SQLlite。
-
通过表与表之间，行和列之间的关系进行数据的存储。
非关系型数据库(NoSQL)：
-
Redis，MongDB。
-
非关系型数据库，对象存储，通过对象的自身的属性来决定。
DBMS(数据库管理系统)
-
数据库的管理软件，科学有效的管理我们的数据，维护和获取数据。
-
MySql，数据库管理系统。
1.2安装MySql
下载mysql：
-
这里建议大家使用压缩版,安装快,方便.不复杂.
-
mysql5.7 64位下载地址:https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
安装mysql：
-
解压到自己想要安装到的目录，本人解压到的是E:\Evironment\mysql-5.7.44
-
添加环境变量：
-
我的电脑->属性->高级->环境变量
-
选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹
-
-
在D:\Environment\mysql目录下新建my.ini文件
-
编辑 my.ini 文件 ,注意替换路径位置
[mysqld] basedir=D:\Program Files\mysql-5.7\ datadir=D:\Program Files\mysql-5.7\data\ port=3306 skip-grant-tables -
启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)
-
再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
-
然后输入命令
net start mysql再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空，-p后面不能有空格） -
进入界面后更改root密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -
刷新权限
flush privileges; -
修改 my.ini文件删除最后一句skip-grant-tables
-
重启mysql即可正常使用（先使用exit，退出mysql）
net stop mysql net start mysql -
连接上测试出现以下结果就安装好了。
1.3安装SQLyog
Professional 版本:
注册名：luoye2562
注册码：ec38d297-0543-4679-b098-4baadf91f983
Enterprise 版本:
注册名：luoye2562
注册码: 59adfdfe-bcb0-4762-8267-d7fccf16beda
Ultimate 版本: （终极是最好的）
注册名：luoye2562
注册码: 8d8120df-a5c3-4989-8f47-5afc79c56e7c
1.4数据库基本操作
-
新建一个数据库school
每一个sqlyog的执行操作，本质就是对应了一个sql，可以在软件的历史记录中查看。
-
新建一张表student，字段有id，name，age。
-
查看表：右键student，选打开表选项
-
添加几个学生信息，点击刷新保存信息
-
通过cmd连接数据库常用命令
mysql –u root –p123456 -- 连接数据库
----------------------------------------------------------
update mysql.user set authentication_string=password('123456') where user='root'
and Host = 'localhost'; -- 修改用户密码
----------------------------------------------------------
flush privileges; -- 刷新权限
----------------------------------------------------------
show databases; -- 查看所有数据库
----------------------------------------------------------
use school; -- 切换数据库为school
----------------------------------------------------------
show tables; -- 查看当前数据库下所有的表
----------------------------------------------------------
describe student; -- 查看student表的信息
----------------------------------------------------------
create database westos; -- 创建数据库，名为westos
----------------------------------------------------------
exit; -- 退出连接
----------------------------------------------------------
-- 单行注释 /*多行注释*/2.操作数据库
-
数据库四种语言,CRUD增删改查:
-
DDL 数据库定义语言
-
DML 数据库操作语言
-
DQL 数据库查询语言
-
DCL 数据库控制语言
-
-
操作数据库->操作数据库中的表->操作表中的数据
2.1操作数据库
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] westos -- 中括号里的内容可选，代表如果不存在则创建2.删除数据库
DROP DATABASE [IF EXISTS] westos -- 中括号里的内容可选，代表如果存在则删除3.使用数据库
USE `westos` -- 使用westos数据库，如果你的表名或者字段名是一个特殊字符(例如user)，就需要带``飘4.查看所有的数据库
SHOW DATABASES -- 查看所有数据库2.2数据库的列(数据)类型
-
数值：
| 数据类型 | 含义 | 大小 |
|---|---|---|
| tinyint | 十分小的数据 | 1个字节 |
| smallint | 较小的数据 | 2个字节 |
| mediumint | 中等大小的数据 | 3个字节 |
| int | 标准的整数 | 4个字节 |
| bigint | 较大的数据 | 8个字节 |
| float | 浮点数（单精度） | 4个字节 |
| double | 浮点数（双精度） | 8个字节 |
| decimal | 字符串形式的浮点数 | 8个字节 |
-
字符串：
| 数据类型 | 含义 | 大小 |
|---|---|---|
| char | 大小固定的字符串 | 0~255 |
| varchar(对应String) | 可变字符串（保存变量使用） | 0~65535 |
| tinytext | 微型文本（一篇博客） | 2^8-1 |
| text | 文本串（保存大文本使用） | 2^16-1 |
-
时间日期：
| 数据类型 | 格式 | 备注 |
|---|---|---|
| date | YYYY-MM-DD | 日期格式 |
| time | HH:mm:ss | 时间格式 |
| datetime | YYYY-MM-DD HH:mm：ss | 最常用的时间格式 |
| timestamp | 时间戳，1970.1.1到现在的毫秒数 | 也较为常用 |
| year | 年份表示 |
-
null：
-
没有值，未知。
-
注意不要使用NULL进行运算，结果为NULL。
-
2.2数据库的字段属性（重要）
Unsigned:
-
无符号的整数。
-
声明了该列不能声明为负数（例如年龄）。
zerofill：
-
不足的位数使用0去填充（例如我设置int 整数长度为3，我输入一个5，就会显示的是005）。
自增：
-
通常理解为自动在上一条记录的基础上+1。
-
通常用来设置唯一的主键，且必须是整数类型。
-
也可以自定义设置主键自增的起始值和步值。
非空：
-
假设设置为非空，如果不给他赋值，就会报错。
默认：
-
设置默认的值。
-
例如有一个字段名为sex，我可以设置它的默认值为男，如果不手动设置为女，则显示男
2.3字段规范（拓展）
每一个表下必须有以下5个字段（规范），做项目用，表示一个记录存在的意义。
/*
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/2.4创建数据库表（重点）
/*
1.创建一个school数据库
2.创建学生表，使用sql语句创建
3.学号int，登陆密码varchar（20），姓名，性别varchar（2）,出生日期（datatime），家庭住址，email
*/
CREATE DATABASE IF NOT EXISTS `school`
-- 创建学生表 注意点使用英文括号，表的名称和字段尽量用``括起来
-- AUTO_INCREMENT 自增 COMMENT 注释 DEFAULT 默认的
-- 字符串使用单引号括起来，所有的语句最后加英文的逗号，最后一句不用加
-- 主键一般一个表只有一个，写在表的最下面方便查看
USE school
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`adress` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8格式：（中括号内的是可选内容）
CREATE TABLE [IF NOT EXISTS] `表名` (
`字段名` 数据(列)类型 [属性] [索引] [注释],
`字段名` 数据(列)类型 [属性] [索引] [注释],
.......
`字段名` 数据(列)类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
常用命令（重要）：
SHOW CREATE DATABASE `school` -- 查看创建school数据库的语句
SHOW CREATE TABLE `student` -- 查看创建student表的定义语句
DESC `student` -- 显示student表的结构2.5数据表的类型（引擎）
-- 只需要记住这两个
INNODB -- 默认使用 安全性高，事务性处理，多表多用户操作
MYISAM -- 早些年使用 节约空间，速度较快| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大(约为2倍) |
-
在物理空间存在的位置
所有数据库文件都在data目录下。本质还是文件的存储。
-
MySQL引擎在物理文件上的区别：
-
INNODB在数据库表中只有一个*.frm,以及上级目录下的ibdata1文件。
-
MYISAM对应文件：
-
*.frm 表结构的定义文件
-
*.MYD 数据文件（data）
-
*.MYI 索引文件（index）
-
-
-
设置数据库表的字符集编码：
CHARSET=utf8
不设置的话，会是mysql默认的字符集编码（Latin1）（不支持中文）。
2.6修改删除表
-
修改：
-- 修改表 ALTER TABLE `表名` RENAME AS `新表名`
ALTER TABLE `student` RENAME AS `student1`
-- 增加表的字段 ALTER TABLE `表名` ADD `新字段` 新类型(长度)
ALTER TABLE `student` ADD `grade` INT(11)
-- 修改表的字段
ALTER TABLE `student` MODIFY `age` VARCHAR(11)
ALTER TABLE `student` CHANGE `age` `age1` INT(1)
/*结论：
change用来字段重命名，不能修改字段类型和约束
modify不能用来字段重命名，只能修改字段类型和约束
*/-
删除：
所有的创建和删除的操作尽量加上判断以免报错
-- 删除表的字段
ALTER TABLE `student` DROP `age1`
-- 删除表
DROP TABLE IF EXISTS `student`3.MySQL数据管理
3.1外键（了解即可）
-
第一种方法（在创建表的时候，增加约束，太麻烦且复杂）：
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT 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 AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`adress` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生id',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`), -- 定义外键key
CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`) -- 给这个外键添加约束（执行引用）
)ENGINE=INNODB DEFAULT CHARSET=utf8删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）。
-
第二种方法（创建表成功后，添加外键约束）：
-- 创建表时没有外键关系
ALTER TABLE`student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)-
注意：以上的操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）。想使用外键去程序里面实现。
3.2DML语言（全部记住）
数据库的意义：数据存储，数据管理。
DML语言：数据操作语言。
-
insert
-
update
-
delete
3.3添加（insert）
-- 插入语句（添加）
-- 公式：insert into 表名([字段名1,字段名2,字段名3])values('值1'),('值2'),('值3'),(...)
INSERT INTO `grade`(`gradename`)VALUES('大四') -- 插入单个值
INSERT INTO `grade`(`gradename`) VALUES('大三'),('大二')-- 插入多个值
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张三','32142','男') -- 插入单个值
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张三','32142','男'),('李四','3asdf','男'),('王麻子','63453','女')-- 插入多个值
-- 如果表名后面不约束要添加的是哪几个值，就必须把字段写完整才能匹配（一般不建议这样写）
INSERT INTO `student` VALUES(5,'张三','32142','男','2000-09-01','西安','xxx.@qq.com'，1)注意：
-
字段和字段之间使用英文的逗号隔开。
-
字段是可以省略的，但后面的值必须要一一对应，不能少。
-
可以同时插入多条数据，values后面的值同样需要使用英文的逗号隔开。
3.4修改
-- 修改语句
-- 公式：update `表名` set `字段名` = '修改的内容' where 条件语句
-- 加条件语句
UPDATE `student` SET `email`='123456@qq.com' WHERE `id` = 1
-- 修改多个值,用逗号隔开
UPDATE `student` SET `email`='111111@qq.com',`adress`='北京' WHERE id>1
-- 不加条件语句，修改所有的email，没有日志无法撤回，慎重
UPDATE `stduent` SET `email`='123456@qq.com'
-- 通过多个条件定位数据，无上限
UPDATE `student` SET `birthday`='2000-08-11' WHERE `name`='王麻子' AND `adress`='北京'| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5<>6或5！=6 | true |
| > | |||
| < | |||
| <= | |||
| >= | |||
| AND | 与关系，两个条件都成立 | true AND true | true |
| BETWEEN...AND | 在某个范围之间 | [2,5] | true |
| OR | 或关系，两个条件满足一个即成立 | true AND false | true |
3.5删除
-- delete删除语句（避免这样写，没有条件会全部删除）
DELETE FROM `student`
-- delete删除指定的数据
DELETE FROM `student` WHERE `id` = 1
-- truncate清空 student表
TRUNCATE TABLE `student`delete和truncate区别：
-
相同点：都能删除数据，都不会删除表结构。
-
不同点：truncate 会重新设置自增列，计数器会归零。且不会影响事务
delete删除的问题（了解即可）：
-
在innodb引擎中，使用了delete后重启数据库，自增（计数器）会从1开始（存在内存当中，断电即失）。
-
在MySAM引擎中，使用了delete后重启数据库，继续从上一个自增量开始（存在文件中，不会丢失）。
4.DQL查询数据（重点）
4.1DQL
(Data Query Language)数据查询语言。
-
所有的查询操作都使用它 Select。
-
使用频率最高的语句。
-
数据库最核心的语言，最重要的语句。
4.2指定查询字段
-
基本查询
-- 查询语句 select 字段,... from 表名 where 条件
-- 查询student表中所有的数据
SELECT * FROM `student`
-- 查询指定字段
SELECT `name`,`pwd` FROM `student` WHERE id!=2
-- AS给结果取一个名字 可以给字段起别名，也可以给表起别名
SELECT `name` AS `名字`,`pwd` AS `密码` FROM `student` WHERE id!=2
-- 函数 concat(a,b) a是需要加的内容 b是字段名
SELECT CONCAT('姓名',`name`) AS `名字`,CONCAT('密码',`pwd`) AS `密码` FROM `student` WHERE id!=2-
去重:
作用：取出select查询出的结果中重复的数据，重复的数据只显示一条
-- 查询一下有哪些同学参加了考试
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 -- 查询自增的步长(变量)-
如果要让所有的学员成绩都加上一分查看
SELECT `StudentNo`,`StudentResult`+1 AS '加一分后' FROM `result`-
where条件子句：
作用：检索数据中符合条件的值
逻辑运算符：
运算符 语法 描述 and && a and b a&&b 逻辑与，两个都为真，结果为真 or || a or b a||b 逻辑或，其中一个为真，则结果为真 not ! not a ！a 逻辑非，真为假，假为真
-- 查询考试成绩在95~100之间
SELECT `StudentNo`,`StudentResult` FROM `result` WHERE `StudentResult`>=95 AND `StudentResult`<=100
SELECT `StudentNo`,`StudentResult` FROM `result` WHERE `StudentResult` BETWEEN 95 AND 100
-- 查询除了1000号学生以外的同学的成绩
SELECT `StudentNo`,`StudentResult` FROM `result` WHERE `StudentNo`!=1000
SELECT `StudentNo`,`StudentResult` FROM `result` WHERE NOT `StudentNo` =1000-
模糊查询（重点）：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果a为null，结果为真 |
| IS NOT NULL | a is not null | 如果a不为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 `name` FROM `student` WHERE `name` LIKE '王%' -- 姓后面有任意个字
SELECT `name` FROM `student` WHERE `name` LIKE '王_' -- 姓后面只有一个字
SELECT `name` FROM `student` WHERE `name` LIKE '王__' -- 姓后面有两个字
SELECT `name` FROM `student` WHERE `name` LIKE '%王%' -- 名字中间有王字的同学
-- 查询1001，1002，1003号的学院
SELECT `StudentNo`,`StudentResult` FROM `result` WHERE `StudentNo` IN (1001,1002,1003) -- in是具体的值
-- 查询在北京的学生
SELECT `id`,`name`,`adress` FROM `student` WHERE `adress` LIKE '北京%'
-- 查询地址为空的同学
SELECT `id`,`name` FROM `student` WHERE `adress` = '' OR `adress` IS NULL
-- 查询生日不为空的同学
SELECT `id`,`name`,`birthday` FROM `student` WHERE `birthday` IS NOT NULL4.3联表查询
| 操作 | 描述 |
|---|---|
| inner join | 返回两个表交集的部分，也就是条件为true的行 |
| left join | 会从左表中返回所有的行所需要查询字段的值，即使右表没有匹配（显示为null） |
| right join | 会从右表中返回所有的行所需要查询字段的值，即使左表没有匹配（显示为null） |
-- 联表查询
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
3.确定交叉点（两个表哪些个字段是相同的）
4.判断条件（on和where后面）： 学生表中StudentNo = 成绩表 StudentNo
*/
-- inner(交集)
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.studentNo = r.studentNo
-- right join
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentNo = r.studentNo
-- left join
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.studentNo = r.studentNo
/*
总结：
1.分析我要查询哪些数据 select...
2.从哪几个表中查 from 表 XXX join 连接的表 on 条件
3.假设存在一种多张表查询，慢慢来，先查询两张表，然后再慢慢增加
4.from a left join b on ... 以a表为基准
5.from a right join b on ... 以b表为基准
6.from a inner join b on ... 以交集为基准
*/
/*
举例多表查询：
1.查询参加考试的同学信息：学号，姓名，科目名，分数
2.分析这些字段来自三个表：student,result,subject
3.确定使用哪些连接查询。
4.找出交集：student和result有交集studentNo，result和subject有交集subjectNo
5.所以可以先查student和result的right join，然后得出的结果就是另一张新表，这张新表在和subject做inner join查询
*/
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.StudentNo = r.StudentNo
INNER JOIN `sunject` AS sub
ON r.SubjectNo = sub.SubjectNo4.4分页（limit）和排序(order by)
-
排序：升序（asc） 降序（desc）
-- 排序：升序（asc） 降序（desc）
-- order by 字段名 asc/desc
-- 查询的结果根据那个字段的升序或降序排序
SELECT s.`id`,`gradename`,`name`,`adress`
FROM `student` AS s
INNER JOIN `grade` AS d
ON s.`id` = d.`id`
ORDER BY id DESC-
分页
-- 为什么要分页？缓解是数据库压力，给用户更好的体验
-- limit是所有语句中的最后一个
-- 语法：limit 起始值，页面的大小
-- LIMIT 0,2 1~2的数据
SELECT s.id,gradename,`name`,adress
FROM `student` AS s
INNER JOIN grade AS d
ON s.id = d.id
ORDER BY id ASC
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页 limit pagesize(n-1),pagesize pagesize：页面大小4.5子查询
用where嵌套查询
-- 查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：正常使用连接查询
SELECT `StudentNo`,r.`SusbjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SusbjectNo=sub.SusbjectNo
WHERE SubjectName ='数据库结构-1'
ORDER BY StudentResult DESC
-- 方式二：用where嵌套使用子查询(有里及外)
SELECT `StudentNo`,r.`SusbjectNo`,`StudentResult`
FROM `result` r
WHERE SubjectNo = (
SELECT SubjectNo FROM `subject`
WHERE SubjectName ='数据库结构-1'
)
ORDER BY StudentResult DESC4.6分组和过滤
-- 查询不同课程的平均分，最高分，最低分，平均分要大于80分
SELECT `SubjectName`,AVG(studentResult),MAX(studentResult),MIN(studentResult)
FROM result AS r
INNER JOIN `subject` AS s
ON r.SubjectNo = s.SubjectNo
GROUP BY r.SubjectNo -- 通过什么字段来分组
HAVING AVG(studentResult) >805.MySQL函数
5.1常用函数（不常用）
-- 数学计算
SELECT ABS(-8) -- 返回绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)-- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(-10) -- 返回-1代表负数 1代表整数 0就是0
-- 字符串函数
SELECT CHAR_LENGTH('dasdasdad') -- 返回字符串的长度
SELECT CONCAT('12','23','34') -- 拼接字符串
SELECT INSERT('123456',1,2,'567890') -- 查询替换，从某个位置替换某个长度
SELECT LOWER('asdfASDgg') -- 都转为小写字母
SELECT UPPER('asdfASDgg') -- 都转为大写字母
SELECT INSTR('asdsfsaafwqe','w') -- 第二个参数在一个参数里面第一次出现的位置
SELECT REPLACE('dsadasdasd','ds','11')-- 替换指定的字符串
SELECT SUBSTR('sdasdasf',4,6) -- 截取指定的字符串
SELECT REVERSE('123456789') -- 反转
-- 时间和日期的函数（重要）
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()5.2聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
-- 聚合函数
-- 查询一个表中有多少条记录，用count（）
SELECT COUNT(id) FROM student -- count(字段)，会忽略所有的null，查看指定列用这个
SELECT COUNT(*) FROM student -- 不会忽略null值
SELECT COUNT(1) FROM student-- 不会忽略null值
SELECT SUM(`id`) AS 总和 FROM student
SELECT AVG(`id`) AS 平均分 FROM student
SELECT MAX(`id`) AS 最大值 FROM student
SELECT MIN(`id`) AS 最小值 FROM student
-- 查询不同课程的平均分，最高分，最低分，平均分要大于80分
SELECT `SubjectName`,AVG(studentResult),MAX(studentResult),MIN(studentResult)
FROM result AS r
INNER JOIN `subject` AS s
ON r.SubjectNo = s.SubjectNo
GROUP BY r.SubjectNo -- 通过什么字段来分组
HAVING AVG(studentResult) >805.3数据库级别的MD5加密
-- 加密
UPDATE `mdtest` SET `pwd`=MD5(pwd) WHERE id = 1 -- 按条件加密
UPDATE `mdtest` SET `pwd`=MD5(pwd) -- 全部加密
-- 插入时加密
INSERT INTO `mdtest`(`name`,`pwd`)VALUE ('郭勇成',MD5('2123124asda')),
('路好长',MD5('2123124asda')),
('刘畅',MD5('2123124asda')),
('乔伊',MD5('2123124asda'))
-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM `mdtest` WHERE `name` = '刘畅' AND `pwd` = MD5('2123124asda') -- md5('前端传进来的明文密码')6.事务
6.1什么是事务
-
举例：假设A有1000块，B有200块
-
A给B转400块 流程：A(1000) -----(400)------> B(200)
-
B收到A的钱 流程：A(600) B(600)
-
如果流程1和2有任意一条sql失败，这个事件都会失败，这就是事务（要么都成功，要么都失败。）。
-
事务ACID原则：
-
原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
-
一致性（Consistency）：事务前后数据的完整性必须保持一致（例如上面例子中，最终一致性：不管怎么转，两个人的钱加起来一定是1200块）。
-
隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。（例如上面的例子，如果有三个人，A给B转钱，C也给B转钱都是一样的操作，但这两个事务是分开的，互不影响即为隔离性）。
-
持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响，事务一旦提交就不可逆。（事务没有提交：恢复到原状。事务已经提交：持久化到数据库）
-
隔离所导致的一些问题：
-
脏读：
指事务读取了另外一个事务尚未提交的数据。
-
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场景不对）
-
虚读（幻读）：
指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
更多关于ACID理解博客链接：事务ACID理解-CSDN博客
6.2事务的执行
-- mysql默认是开启事务的
SET autocomnit = 0 -- 关闭
SET autocomnit = 1 -- 开启（默认）
-- 手动处理事务的操作
SET autocomnit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，在这之后的sql都在同一个事务内
INSERT xx -- 举例代码
INSERT xx -- 举例代码
-- 提交：持久化（成功就用这个提交）
COMMIT
-- 回滚：回到原来的样子（失败就用这个回滚）
ROLLBACK
-- 事务结束
SET autocomnit = 1 -- 开启自动提交
-- 以下了解即可
SAVEPOINT 保存点名 -- 设置一个事物的的保存点
ROLLBACK TO SAVEPOINT -- 回滚到保存点
RELEASE SAVEPOINT -- 撤销保存点6.3事务在java中的体现
7.索引
MySQL官方对索引的定义：索引（index）是帮助MySQL高效获取数据的数据结构，提取句子的主干，就可以得到索引本质：索引是数据结构。
7.1索引的分类
-
主键索引（PRINMAY KEY）
唯一的标识，主键里的内容不可重复，只能有一个列作为主键。
-
唯一索引（UNIQUE KEY）
避免重复的列出现，一个表可以有多个索引
-
常规索引（ KEY/INDEX）
默认的，index，key关键字来设置
-
全文索引（FullText）
快速定位数据
基础语法：
-- 索引的使用
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`name`)
-- explaion 分析sql执行的情况
EXPLAIN SELECT * FROM student -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`name`)AGAINST('王')7.2测试索引
-- 创建一个测试用户表
CREATE TABLE IF NOT EXISTS `app_user`(
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT'性别0男1女',
`password` VARCHAR(100) NOT NULL COMMENT'密码',
`age` TINYINT(4) DEFAULT '0' COMMENT'年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)ENGINE= INNODB DEFAULT CHARSET = utf8mb4 COMMENT'app用户表'
-- 插入一百万条数据（了解即可）
-- 创建函数
DELIMITER $$ -- 写函数之前必写，标志
CREATE FUNCTION mock_date()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
-- 插入语句
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'124124124@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000)))
,FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i= i+1;
END WHILE;
RETURN i;
END;
SELECT mock_date(); -- 执行插入
-- 不加索引时执行查询
SELECT * FROM app_user WHERE `name` = '用户9999' -- 执行耗时 : 1.561 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999' -- rows 992614
-- 添加索引后执行查询
-- create index 索引名 on 表(字段)
-- id_表名_字段名
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999' -- 执行耗时 : 0.062 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999' -- rows 1
结论：添加索引前执行耗时:1.561 sec和查询了992614行；添加索引后执行耗时:0.062 sec和查询了1行。
索引在小数据量的时候，用处不大，但在数据量大时，区别十分明显。
7.3索引原则
-
索引不是越多越好。
-
不要对经常变动的数据添加索引。
-
小数据量的表不需要添加索引。
-
索引一般加在经常查询的字段上。
关于mysql索引背后的数据结构及算法原理：CodingLabs - MySQL索引背后的数据结构及算法原理
8.权限管理和备份
8.1用户管理
-
sql命令操作
用户表：mysql.user
-- 本质是在操作mysql.user表
-- 创建用户 create user 用户名 identified by '密码'
CREATE USER wsn IDENTIFIED BY '123456'
-- 修改密码 （修改当前用户密码）
SET PASSWORD = PASSWORD('123456')
-- 修改密码 （修改指定用户密码）
SET PASSWORD FOR wsn = PASSWORD('123456')
-- 重命名 rename user 原名字 to 新名字
RENAME USER wsn TO zzw
-- 用户授权 GRANT ALL PRIVILEGES 授予全部的权限 on 库.表 to 用户名
-- ALL PRIVILEGES 除了不能给别人授权，其他的都能干
GRANT ALL PRIVILEGES ON *.* TO wsn
-- 查询权限 SELECT GRANT FOR 用户名
SHOW GRANTS FOR wsn -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- root用户的权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM wsn
-- 删除用户 DROP USER 用户名
DROP USER wsn8.2MySQL备份
为什么要备份：
-
保证重要的数据不丢失。
-
数据转移。
MySQL数据库备份方式：
-
直接拷贝物理文件（data文件夹）。
-
使用命令行（cmd）导出，mysqldump命令。
# 导出 # mysqldump -h 主机 -u用户名 -p密码 数据库名 表名>物理磁盘位置/文件名.sql mysqldump -hlocalhost -uroot -p123456 school student>D:/a.sql # mysqldump -h 主机 -u用户名 -p密码 数据库名 表1,表2,表3>物理磁盘位置/文件名.sql mysqldump -hlocalhost -uroot -p123456 school student>D:/a.sql # 导入 # 登陆的情况下，切换到指定的数据库 # source 备份文件 source d:/a.sql -
在sqlyog可视化工具中手动导出。
选中要备份的表或数据库右键点击选择备份或导出
1653
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
