MySQL 入门这一篇就够了！

什么是数据库？

数据库（DB, DataBase） 概念：数据仓库，软件，安装在操作系统上（Windows，Linux）! SQL，可以存储大量的数据，500万。 作用：存储数据，管理数据

数据库分类

关系型数据库：（SQL）

• MySQL，Oracle，SQL server，db2，sqllite

• 通过表和表之间，行和列之间的关系进行数据的存储，学院信息表，考勤表……

非关系型数据库：（Nosql） Not Only

• Redis， MongDB

• 对象存储，通过对象的自身的属性来决定

DBMS（数据库管理系统）

• 数据库管理软件，科学有效的管理数据，维护和获取数据

• MySQL，数据库管理系统

MySQL简介

官网：MySQL ：MySQL

安装MySQL：MySQL下载安装配置教程（详细）附下载资源 - 砰砰的猿 - 博客园

安装Mysql：MySQL 5.7 安装教程（全步骤图解教程）_mysql5.7的安装教程-CSDN博客

1、新建一个数据库school

每一个SQLyog的执行操作，本质就是对于了一个sql，可以在软件的历史记录中查看。

2、新建一张表

3、查看表

4、连接数据库

mysql -u root -p --连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; -- 修改用户密码

flush privileges; -- 刷新权限

-- 所有语句都使用; 结尾 show databases; -- 查看所有数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | shengblog | | shop | | test | +--------------------+ 7 rows in set (0.00 sec)

mysql> use school; -- 切换数据库 use 数据库名; Database changed

mysql> show tables; -- 查看数据库中所有的表 +------------------+ | Tables_in_school | +------------------+ | student | +------------------+ 1 row in set (0.00 sec)

mysql> describe student; -- 显示数据库中所有表的信息 +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | int(3) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.02 sec)

mysql> create database westos; -- 创建一个数据库

exit; -- 退出连接

-- 单行注释（sql 本身的注释） /多行注释/ mysql 关键字 不区分大小写

数据库语言 CRUD 增删改查 

DDL ：定义

DML ：操作

DQL ：查询

DCL ：控制

操作数据库

操作数据库 》 操作数据库中的表 》操作数据库中表的数据

1、操作数据库（了解） 1、创建数据库 CREATE DATABASE IF NOT EXISTS westos; 
2、删除数据库 DROP DATABASE IF EXISTS westos; 
3、使用数据库 USE school; -- 如果标明或者字段名是一个特殊字符，就需要带``
4、查看数据库 SHOW DATABASES; -- 查看所有的数据库 学习思路：

• 对照sqlyog可视化历史记录查看sql

• 固定的语法或关键字必须要记住 2、操作数据库中的表

---

数据库的数据（列）类型

数值

• tinyint 十分小的数据 1个字节

• smallint 较小的数据 2个字节

• mediumint 中等大小的数据 3个字节

• int 标准的整数 4个字节 常用

• bigint 较大的数据 8个字节

• float 浮点数 4个字节

• double 浮点数 8个字节 （精度问题）

• decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal 字符串

• char 字符串固定大小 0-255

• varchar 可变字符串 0-65535 常用的 变量 String

• 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 数据库字段属性（重点）

---

1.Unsigned a.无符号的整数 b.声明了该列不能为负数 2.Zerofill a.0填充 b.不足的位数，使用0填充 int（3） ， 5 -- 005 3.自增 a.自动在上一条的基础上 +1 默认 b.通常用来设计唯一的主键 ，必须是整数类型 c.可以自定义主键自增的起始值和步长 4.非空 a.null / not null b.假设设置为非空，如果不给它赋值，就会报错 c.如果不设置，默认就是null 5.默认 a.设置默认的值 b.sex 默认值 为 男，如果不指定该列的值，则会使用默认的值 创建表 -- 目标：创建一个school 数据库 -- 创建学生表（列，字段） -- 学号 int 登录密码 varchar(20) 姓名 性别varchar(2)，出生日期（datetime）,家庭地址,email

-- 注意点，使用英文()，表的名称 和 字段 尽量使用 `` 括起来

-- 字符串使用单引号 或 双引号 括起来 -- 所有语句后面加, (英文的,) 最后一个字段不用加, -- PRIMARY KEY 主键，一般一个表只有一个唯一的主键 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 '出生日期', address VARCHAR(20) 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 -- 查看创建数据库的语句 SHOW CREATE TABLE student -- 查看创建数据表的语句 DESC student -- 显示表的结构

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

• MYISAM 节约空间，速度较快

• INNODB 安全性较高，事务的处理，多表多用户操作 所有的数据库文件都存放在 data 目录下，本质还是文件的存储 ：C:\ProgramData\MySQL\MySQL Server 5.5\data\school， 一个文件夹就是一个数据库 MySQL 引擎在物理文件上的区别

• INNODB 在数据库表中只有一个 *.frm 文件，以及上级目录下的 ibdata1 文件

• MYISAM 对应的文件

• *.frm -- 表结构的定义文件

• *.MYD -- 数据文件 （data）

• *.MYI -- 索引文件 设置数据库表的字符集编码 DEFAULT CHARSET=utf8 不设置的话，是MySQL默认的字符集编码。（不支持中文）

修改数据表

-- 修改表名 ALTER TABLE teacher RENAME AS teacher1 -- 增加表的字段 ALTER TABLE teacher1 ADD age INT(11) -- 修改表字段 （重命名，修改约束） ALTER TABLE teacher1 MODIFY age VARCHAR(2) -- 修改约束 ALTER TABLE teacher1 CHANGE age age1 INT(2) -- 字段重命名

-- 删除表的字段 ALTER TABLE teacher1 DROP age1

删除数据表

所有 的创建和删除操作尽量加上判断，以免报错！ -- 删除表 DROP TABLE IF EXISTS teacher1

3、操作数据库中表的数据

外键（了解） 方式一 ： 创建表的时候，增加约束，（麻烦。比较复杂） 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=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 '出生日期', gradeid INT(10) NOT NULL COMMENT '学生的年级', address VARCHAR(20) DEFAULT NULL COMMENT '家庭地址', email VARCHAR(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (id), KEY FK_gradeid (gradeid), CONSTRAINT FK_gradeid FOREIGN KEY (gradeid) REFERENCES grade (gradeid) ) ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）

方式2： 创建表成功后，添加约束 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=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 '出生日期', gradeid INT(10) NOT NULL COMMENT '学生的年级', address VARCHAR(20) DEFAULT NULL COMMENT '家庭地址', email VARCHAR(50) DEFAULT 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 哪个表 (哪个列);

以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰） 最佳实践：

• 数据库就是单纯的表，只是用来存数据的，只有行（数据）和 列（字段）

• 我们想要使用多张表的数据，想使用外键（程序去实现）

DML语言：数据操作语言

1.insert 2.update 3.delete

1、insert 添加

-- 插入数据 -- 语法： insert into 表名([字段名1,字段名2]) values('值1'),('值2') INSERT INTO grade(gradename) VALUES('大四')

-- 由于主键自增，可以省略，如果不写表的字段，就会一一匹配 INSERT INTO grade VALUES(2,'大三')

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

-- 插入多个字段 INSERT INTO grade(gradename) VALUES('大二'),('大一')

INSERT INTO student(name) VALUES('张三')

INSERT INTO student(name,pwd,sex) VALUES('张三', 'aaaaaa','男')

INSERT INTO student(name,pwd,sex) VALUES('张三', 'aaaaaa','男'),('王五', 'bbbbbb','男') 注意事项：

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

2、update 修改

-- 修改学员名字，带了条件 UPDATE student SET name='sheng' WHERE id=5

-- 不指定条件的情况下，会改动整个表 UPDATE student SET name='changcheng'

-- 修改多个属性,逗号隔开 UPDATE student SET name='aaaaa', email='739@qq.com' WHERE id=5

-- 语法： -- update 表名 set 列=值,列=值 [where 条件]

-- 通过多个条件定位数据 UPDATE student SET name='aaaaa', email='739@qq.com' WHERE name='s' AND sex='女'

条件：where 子句 运算符 id 等于 某个值，大于某个值，在某个区间内修改... 操作符含义范围结果=等于5=6false<> !=不等于><>=<=between and [2,5]闭合区间在某个范围内

注意事项： 1.column_name 数据库的列，尽量带上`` 2.条件，筛选的条件，如果没有指定则会修改所有的列 3.value，是一个具体的值 4.多个设置的属性之间，使用英文的逗号隔开

3、delete 删除

delete 命令 --语法 ： delete from 表名 [where 条件]

-- 删除数据 避免这样写(会全部删除) DELETE FROM student -- 删除指定数据 DELETE FROM student WHERE id=9 truncate 命令 : 作用，完全清空一个数据库表，表的结构和索引约束不会变 -- 清空student表 TRUNCATE TABLE student 区别：

相同点：都能删除数据，都不会删除表结构

不同：

truncate 重新设置 自增列 计数器会归零

truncate 不会影响事务 -- 测试delete truncate 区别 CREATE TABLE test( id INT(4) NOT NULL AUTO_INCREMENT, col VARCHAR(20) NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO test(col) VALUES('1'),('2'),('3')

DELETE FROM test -- 不会影响自增

TRUNCATE TABLE test -- 自增会归零

DQL查询数据（最重点）

select 语法： SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {|talbe.|[table.]field1AS alias1field2AS alias2]} FROM table_name [as table_alias] [left | right | inner join teble_name2] -- 联合查询 [WHERE…] -- 指定结果需要满足的条件 [GROUP BY…] -- 指定结果按照哪几个字段来分组 [HAVING…] -- 过滤分组的记录必须满足的次要条件 [ORDER BY…] -- 指定查询记录按一个或多个条件排序 [limit {[offset,]row_count | row_countOFFSET offset}] -- 指定查询的记录从哪条至哪条

1、指定查询字段

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

-- 查询指定字段 SELECT StudentNo, StudentName FROM student

-- 别名,给结果起一个名字 as 可以给 字段 和 表 起别名 SELECT StudentNo AS 学号, StudentName AS 学生姓名 FROM student AS s

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

语法：select 字段 from 表 有时候，列名字不是那么见名知意，可以起别名， as 字段名 as 别名 表名 as 别名

去重复：distinct

作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条 -- 查询 有那些同学有成绩 SELECT * FROM result SELECT StudentNo AS 学号 FROM result SELECT DISTINCT StudentNo AS 学号 FROM result -- 发现重复数据，会去重

数据库 的列（表达式）

-- 查系统版本 SELECT VERSION() -- 用来计算 SELECT 100*30-1 AS 计算结果 -- 查询自增的步长(变量) SELECT @@auto_increment_increment

-- 学员考试成绩 +1 分查看 SELECT StudentNo, StudentResult+1 AS 提分后 FROM result

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

select 表达式 from 表

2、where 条件子句

作用：检索数据中 符合条件 的值 搜索的条件就是由一个或者多个表达式组成，返回结果都是一个bool值 逻辑运算符 运算符语法描述and &&a and b a && b逻辑与or || a or b a || b逻辑或not !not a ! a逻辑非 -- 查所有人的成绩 SELECT StudentNo,StudentResult FROM result

-- 查询成绩在95到100 SELECT StudentNo,StudentResult FROM result WHERE StudentResult BETWEEN 95 AND 100 -- and && SELECT StudentNo,StudentResult FROM result WHERE StudentResult>=95 && StudentResult<=100

-- 模糊查询（区间） SELECT StudentNo,StudentResult FROM result WHERE StudentResult>=95 AND StudentResult<=100

-- 除了学号1000之外的学生 SELECT StudentNo,StudentResult FROM result WHERE NOT StudentNo=1000 -- not ! SELECT StudentNo,StudentResult FROM result WHERE StudentNo!=1000 模糊查询：比较运算符 运算符语法描述is null a is null 如果a是null 结果为真is not null a is not null 如果a 不是null 结果为真between anda between x and y若 a 在 b 和 c之间，结果为真likea like b 可以由通配符sql 匹配 如果a匹配b，结果为真ina in (a1, a2, a3) 要具体的值假设a在a1，或者a2……其中一个，结果为真 SELECT * FROM student

-- 模糊查询，查询姓张的同学 -- 使用like 结合 %(代表0到多个字符) （一个字符） SELECT StudentNo, StudentName FROM student WHERE StudentName LIKE '张' -- 查询名字中间有伟的同学 SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE '%伟%' -- in （具体的一个或多个值） -- 查询1000，1001的学员 SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (1000,1001)

-- 查询名字中还有张，赵的学生 SELECT StudentNo,StudentName FROM student WHERE StudentName IN ('张伟','赵强') -- null not null -- 查询地址为为''的学生 SELECT StudentNo,StudentName FROM student WHERE Address='' -- 查询BornDate 为null的 SELECT * FROM student WHERE BornDate IS NULL

-- 查询BornDate 不为 null的 SELECT * FROM student WHERE BornDate IS NOT NULL

3、连表查询

join 对比

-- 联表查询 -- 查询参加了考试的同学 (学号，姓名，科目编号，分数) SELECT * FROM student; SELECT * FROM result

/* 思路

分析需求，分析查询的字段来自哪些表，（连接查询）

确定使用哪种连接查询 确定交叉点（这两个表中哪个数据是相同的） 判断的条件：学生表中的 StudentNo 和 成绩表中的 StudentNo */ -- inner join SELECT s.StudentNo,StudentName,SubjectNo,StudentResult FROM student AS s INNER 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 -- right join SELECT s.StudentNo,StudentName,SubjectNo,StudentResult FROM student AS s RIGHT JOIN result AS r ON s.StudentNo=r.StudentNo

不同的 SQL JOIN 在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

INNER JOIN：如果表中有至少一个匹配，则返回行

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

FULL JOIN：只要其中一个表中存在匹配，则返回行 -- 查询参加考试的同学信息 （学号，学生姓名，科目名称，分数）

SELECT s.StudentNo,StudentName,SubjectName,StudentResult FROM student s RIGHT JOIN result r ON s.StudentNo=r.StudentNo INNER JOIN subject sub ON r.SubjectNo=sub.SubjectNo

-- 要查询哪些数据，select .... -- 从哪几个表查，from ... join ... on 交叉条件 -- 假设存在一种多张表查询，先查询两张表开始，然后再慢慢增加

4、分页和排序

select 语法： SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {|talbe.|[table.]field1AS alias1field2AS alias2]} FROM table_name [as table_alias] [left | right | inner join teble_name2] -- 联合查询 [WHERE…] -- 指定结果需要满足的条件 [GROUP BY…] -- 指定结果按照哪几个字段来分组 [HAVING…] -- 过滤分组的记录必须满足的次要条件 [ORDER BY…] -- 指定查询记录按一个或多个条件排序 [limit {[offset,]row_count | row_countOFFSET offset}] -- 指定查询的记录从哪条至哪条

-- 要查询哪些数据，select .... -- 从哪几个表查，from ... join ... on 交叉条件 -- 假设存在一种多张表查询，先查询两张表开始，然后再慢慢增加

-- 分页 limit 和 排序 order by -- 升序 asc 降序 desc

-- 语法：limit 起始值，页面大小 -- 网页应用： 当前，总的页数，页面的大小 -- 第一页： limit 0,5 -- 第二页： limit 5,5 -- 第三页： limit 10,5 -- 第N页： limit (n-1)5,5 -- 公式： limit (n-1)pagesize,pagesize



SELECT s.StudentNo,StudentName,SubjectName,StudentResult FROM student s RIGHT JOIN result r ON s.StudentNo=r.StudentNo INNER JOIN subject sub ON r.SubjectNo=sub.SubjectNo ORDER BY StudentResult LIMIT 1,3

5、子查询和嵌套查询

where （这个值是计算出来的） 本质：在where语句中嵌套一个子查询语句 where （select * from ....） -- 查询 高等数学-3 的所有考试结果（学号,科目,成绩） 降序排列 SELECT StudentNo, r.SubjectNo, StudentResult FROM result r INNER JOIN subject sub ON r.SubjectNo=sub.SubjectNo WHERE SubjectName='高等数学-3' ORDER BY StudentResult DESC -- 使用子查询

-- 1. 查询所有的 高等数学-3 学生科目编号 SELECT SubjectNo FROM subject WHERE SubjectName='高等数学-3'

-- 2. 将上面的结果 作为 条件进行查询 SELECT StudentNo, SubjectNo, StudentResult FROM result WHERE SubjectNo=(SELECT SubjectNo FROM subject WHERE SubjectName='高等数学-3') ORDER BY StudentResult DESC

6、MySQL函数

官网：MySQL :: MySQL 5.7 Reference Manual :: 12.1 Built-In Function and Operator Reference SELECT ABS(-8) SELECT MOD(5,3) SELECT CEIL(2.1) SELECT FLOOR(2.9) SELECT RAND() SELECT SIGN(12)

SELECT CHAR_LENGTH('asdf') SELECT CONCAT('i','love') SELECT INSERT('我爱hello',1,2,'超爱') SELECT REPLACE('我爱hello','hello','hahha') SELECT LOWER('AASFGASdsad') SELECT UPPER('AASFGASdsad')

SELECT CURRENT_DATE() SELECT CURRENT_TIME() SELECT NOW() SELECT LOCALTIME()

SELECT YEAR(NOW())

SELECT USER() SELECT VERSION() 聚合函数（常用） 函数名称描述count()计数sum()求和avg()平均值max()最大值min()最小值 -- 聚合函数

SELECT COUNT(StudentName) FROM student -- count(列) 会忽略所有的null值 SELECT COUNT() FROM student -- count() 不会忽略所有的null值 SELECT COUNT(1) FROM student -- count(1) 不会忽略所有的null值

SELECT SUM(StudentResult) AS 总和 FROM result SELECT AVG(StudentResult) AS 平均 FROM result SELECT MAX(StudentResult) AS 最大 FROM result SELECT MIN(StudentResult) AS 最小 FROM result

7、分组和分组后筛选

group by ... having ...

8、select 小结

select 语法： SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {|talbe.|[table.]field1AS alias1field2AS alias2]} FROM table_name [as table_alias] [left | right | inner join teble_name2] -- 联合查询 [WHERE…] -- 指定结果需要满足的条件 [GROUP BY…] -- 指定结果按照哪几个字段来分组 [HAVING…] -- 过滤分组的记录必须满足的次要条件 [ORDER BY…] -- 指定查询记录按一个或多个条件排序 [limit {[offset,]row_count | row_countOFFSET offset}] -- 指定查询的记录从哪条至哪条

4、事务

什么是事务？

要么都成功，要么都失败，将一组SQL放到一个批次中执行 事务原则：acid原则，原子性，一致性，隔离性，持久性…… 参考链接：事务ACID理解-CSDN博客 原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 一致性（Consistency） 事务前后数据的完整性必须保持一致。 隔离性（Isolation） 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

执行事务

-- mysql 是默认开启事务自动提交的

-- 手动处理事务 SET autocommit = 0 -- 关闭自动事务

-- 事务开启 START TRANSACTION -- 标记一个事务的开始，从这个 之后的 sql 都在同一个事务内

-- 提交：持久化(成功) commit COMMIT -- 回滚：回到原来的样子（失败） rollback ROLLBACK

-- 事务结束 SET autocommit = 1 -- 开启自动事务

模拟事务场景 -- 转账 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.00),('B',10000.00)

-- 模拟转账: 事务 SET autocommit = 0 -- 关闭自动提交事务

START TRANSACTION -- 开启一个事务 （一组事务）

UPDATE account SET money=money-500 WHERE name='A' -- A减500 UPDATE account SET money=money+500 WHERE name='B' -- B加500

COMMIT; -- 提交事务 ROLLBACK; -- 回滚

SET autocommit = 1 -- 恢复自动提交事务

5、索引

mysql 对索引的定义是：索引是帮助 MySQL 高效获取数据的数据结构，

5.1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个 1.主键索引 （primary key） a.唯一的标识，主键不可以重复，只能有一个列作为主键 2.唯一索引 （unique key） a.避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引 3.常规索引 （key / ndex） a.默认的，key 或者 index 关键字设置 4.全文索引 （full key） a.在特定的数据库引擎下才有，MYISAM b.快速定位数据

5.2、测试索引

create index 索引名 on 表名(字段) ：给哪个表哪个字段添加索引 -- id表名字段名 -- create index 索引名 on 表名(字段)

-- explain 分析sql执行的状况 EXPLAIN SQL语句

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

• 索引不是越多越好

• 不要对经常变动的数据加索引

• 小数据量的表不需要加索引

• 索引一般加在常用来查询的字段上！

6、权限管理 和 备份

6.1、用户管理

sqlyog 可视化管理

用户表：mysql.user 本质：对这张表进行增删改查 -- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码' CREATE USER sheng IDENTIFIED BY '123456'

-- 修改密码 （修改当前用户密码） SET PASSWORD = PASSWORD('111111')

-- 修改密码 （修改指定用户密码） SET PASSWORD FOR sheng = PASSWORD('123456')

-- 重命名 RENAME USER 旧名字 TO 新名字 RENAME USER sheng2 TO sheng

-- 用户授权 ALL PRIVILEGES 全部的权限, 库.表 GRANT ALL PRIVILEGES ON . TO sheng

-- 查询权限 SHOW GRANTS FOR sheng -- 查看指定用户的权限 SHOW GRANTS FOR root@localhost

-- GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION -- GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 REVOKE 哪些权限，在哪个库，给谁撤销 REVOKE ALL PRIVILEGES ON . FROM sheng

-- 删除用户 DROP USER sheng

6.2、MySQL备份

备份方式：

• 直接拷贝物理文件

• 在sqlyog等可视化工具备份

• 使用命令行导出，mysqldump 命令行使用

mysqldump -h主机 -u用户 -p密码 数据库 表名 > 位置

C:\Users\sheng>mysqldump -hlocalhost -uroot -p123456 school student > d:/a.sql

导入： 登录的情况下，切换到指定的数据库 先登录上去 source 备份文件 mysql -uroot -p123456

source d:\a.sql

未登录的 mysql -uroot -p123456 库名 < 备份文件

7、数据库的设计

7.1、为什么要设计

当数据库比较复杂的时候，就需要设计了 糟糕的数据库设计：

• 数据冗余，浪费空间

• 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】

• 程序的性能差 良好的数据库设计：

• 节省内存空间

• 保证数据库的完整性

• 方便我们开发系统 软件开发中，关于数据库的设计

• 分析需求，分析业务和需要处理的数据库的需求

• 概要设计，设计关系图E-R图 设计数据库的步骤：（个人博客）

• 收集信息，分析需求

• 用户表（用户登录注销，用户的个人信息，写博客，创建分类）

• 分类表（文章分类，谁创建的）

• 文章表（文章的信息）

• 友链表（友链信息）

• 自定义表（系统信息，某个关键i的字，或者一些主子段）

• 标识实体（把需求落地到每个字段）

• 标识实体 之间的关系

• 写博客 user -> blog

• 创建分类 user -> category

• 关注 user -> user

• 友链：links

• 评论: user -> user -> blog

7.2、三大范式

为什么要数据规范化？

• 信息重复

• 更新异常

• 插入异常

• 无法正常显示信息

• 删除异常

• 丢失有效的信息 1．第一范式(确保每列保持原子性) 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。 第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。 2．第二范式(确保表中的每列都和主键相关) 第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。 比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。 订单信息表

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。 而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。 3．第三范式(确保每列都和主键列直接相关,而不是间接相关) 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。 比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。 注意事项： 1.第二范式与第三范式的本质区别：在于有没有分出两张表。 第二范式是说一张表中包含了多种不同实体的属性，那么必须要分成多张表，第三范式是要求已经分好了多张表的话，一张表中只能有另一张标的ID，而不能有其他任何信息，（其他任何信息，一律用主键在另一张表中查询）。 2.必须先满足第一范式才能满足第二范式，必须同时满足第一第二范式才能满足第三范式。

show DATABASES;

use school;

CREATE DATABASE db;

show CREATE DATABASE db;

use db;

SELECT DATABASE();

show tables;

show DATABASES;



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 COMMENT '出生日期', address VARCHAR(100) COMMENT '家庭住址', email VARCHAR(50) COMMENT '邮箱', PRIMARY KEY (id) )ENGINE=INNODB DEFAULT CHARSET=utf8



show CREATE DATABASE db;

show CREATE TABLE student;

desc student;

-- 修改表

alter table student rename as st; -- 表重命名

alter table st add sum int(10) not null DEFAULT 100 comment '总分'; -- 增加一列

desc st; -- 显示表字段信息

alter table st MODIFY sum VARCHAR(10); -- 修改字段类型

alter TABLE st change sum s int(10); -- 改字段名 并 改类型(必要的)

alter table st change s sum varchar(10);

DROP table if exists st; -- 删表





-- dml

desc student;

alter table student rename as st;

use db; desc st;

-- 增

INSERT Into st VALUES ();

INSERT into st (name) values ('dashuaige'); -- 插入单行数据

SELECT * FROM st;

INSERT into st (name) VALUES ('a1') ,('a2'); -- 插入多行数据

INSERT into st (name, pwd) VALUES ('a1', '121') ,('a2', '3424'); -- 插入多行数据

-- 删

DELETE FROM st where id = 5;

DELETE FROM st; -- 清空表数据

TRUNCATE st; -- 清空表数据

-- 改

update st set name = 'b1'; -- 更新所有字段

update st set name = 'c1' where id = 1; -- 更新单行字段

update st set name = 'd1' , sex = 'ma' where id = 2; -- 更新单行多个字段

update st set name = 'e1' where id = 3 and pwd = 123456; -- 根据多条件更新

-- 查

alter table st MODIFY pwd int(11);

select * from st;

desc st;

INSERT into st (pwd) VALUES ('abd');

alter table st MODIFY name int(11);

alter TABLE st change pwd passw varchar(10);



8、MySQL函数 数值、字符串、日期和时间 8.1、日期和时间函数 1、获取日期、时间

函数用法curdate(), current_date()返回当前日期，只包含年，月，日curtime(), current_time();返回当前时间，只包含时，分，秒now(), sysdate(), current_timestamp(), localtimestamp(), localtime(); 返回当前系统日期和时间utc_date()返回utc（世界标准时间）日期utc_timestamp();返回utc（世界标准时间）时间utc_timestamp();返回utc（世界标准时间）日期和时间

select curdate(), current_date(); select curtime(), current_time(); select now(), sysdate(), current_timestamp(), localtimestamp(), localtime(); select utc_date(), utc_time(), utc_timestamp(); 2、日期和时间戳的转换

函数用法unix_timestamp();以unix时间戳的形式返回当前时间。unix_timestamp(date);将时间date以unix时间戳的形式返回。from_unixtime(timestamp); 将unix时间戳的时间转换为普通格式的时间。

select unix_timestamp(); select unix_timestamp(date); select from_unixtime(timestamp);

select unix_timestamp(); select unix_timestamp(20230202121212); select from_unixtime(1675311132);

select unix_timestamp(); select unix_timestamp('20230202121212'); select from_unixtime('1675311132'); 3、获取月份，星期，星期数，天数等函数

函数用法year(date), month(date), day(date);返回具体的日期值hour(time), minute(time), second(time);返回具体的时间值monthname(date);返回月份， January ....dayname(date);返回星期几：Monday .... weekday(date);返回周几，周一是0 ... quarter(date);返回日期对应的季度，范围1-4week(date), weekofyear(date);返回一年中的第几周dayofyear(date);返回日期是一年中的第几天dayofmonth(date); 返回日期位于所在月份的第几天dayofweek(date); 返回周几，注意：周日是1，周一是2 ....

select year(date), month(date), day(date); select hour(time), minute(time), second(time); select monthname(date); select dayname(date); select weekday(date); select quarter(date); select week(date), weekofyear(date); select dayofyear(date); select dayofmonth(date); select dayofweek(date);

select year(curdate()), month(curdate()), day(curdate()); select hour(curtime()), minute(curtime()), second(curtime()); select monthname(now()); select dayname('20230617'); select weekday(20230617); select quarter(20230617); select week('2023-06-17'), weekofyear(20230617); select dayofyear(now()); select dayofmonth(localtime()); select dayofweek(localtimestamp());

4、日期操作函数

函数用法extract(type from date);返回指定日期中特定的部分，type指定返回的值 extract(type from date) 函数中的type的取值和含义：

type 取值含义microsecond返回毫秒数second返回秒数minute返回分钟数hour返回小时数day返回天数week返回日期在一年中的第几个星期month返回日期在一年中的第几个月quarter返回日期在一年中的第一个季度year返回日期的年份second_microsecond返回秒和毫秒数minute_microsecond返回秒和毫秒值minute_second返回分钟和毫秒值hour_microsecond返回小时和毫秒值hour_minute返回小时和分钟值hour_second返回小时和秒值day_microsecond返回天和毫秒值day_hour返回天和小时值day_minute返回天和分钟值day_second返回天和秒值year_month返回年和月

select extract(year_month from now()), extract(day from now()), extract(day_hour from now()); select extract(year from '2023-01-01 00:12:23'); 5、时间和秒钟的转换的函数

函数用法time_to_sec(time);将time转化为秒并返回结果值，转化的公式为：小时3600+分钟60+秒sec_to_time(second); 将second描述转化为包含小时，分钟和秒的时间

select time_to_sec(curtime()); select sec_to_time(72780); 6、计算日期和时间的函数

函数用法date_add(datetime, interval expr type)adddate(date, interval expr type)返回与给定日期时间相差interval时间段的日期时间date_sub(date, interval expr type)subdate(date, interval expr type);返回与date相差interval时间间隔的日期

间隔类型含义hour小时minute分钟year年month月day日year_month年和月day_hour日和小时day_minute日和分钟day_second日和秒hour_minute小时和分钟hour_second小时和秒minute_second分钟和秒

select date_add(now(), interval 1 day); select date_add(now(), interval 1 year); select date_add(now(), interval '1_10' hour_minute); -- 需要引号 select date_add(now(), interval -1 day); -- 相当于date_sub

7、日期的格式化与解析

函数用法date_format(date, fmt);按照字符串fmt格式化 日期date值time_format(time, fmt);按照字符串fmt格式化时间time值get_format(date_type, format_type);返回日期字符串的显示格式str_to_date(str, fmt);按照字符串fmt对str进行解析，解析为一个日期

格式符说明格式符说明%Y4位数表示年份%y2位数表示年份%M月名表示月份（January ...）%m2位数表示月份(01,02...)%b缩写的月名（Jan, Feb...）%c数字表示月份（1,2,3...）%D英文后缀表示月中的天数（1st,2nd,3rd...）%d两位数表示月中的天数（01，02...）%e数字形式表示月中的天数（1，2，3....）%H两位数字表示小时，24小时制（01，01）%h 和 %I两位数字表示小时，12小时制（01，02）%k数字形式的小时，24小时制（）%l数字形式表示小时，12小时制%i两位数表示分钟（01，02...）%S 和 %s两位数表示秒（00,01,02）%W一周中的星期名称（sunday）%a一周中的星期缩写（sun,....）

select date_format(date, fmt); select time_format(time, fmt); select get_format(date_type, format_type); select str_to_date(str, fmt);

select date_format('20230101', '%Y-%m-%d'); select date_format(curdate(),'%y-%M-%d'); select date_format(curdate(),'%Y-%M-%D'); select date_format(sysdate() ,'%Y-%M-%D %H:%i:%s'); select str_to_date('20200310', '%Y%m%d');

select time_format(curtime(), '%h:%i:%s'); -- 12 小时制 select time_format(curtime(), '%H:%i:%s'); -- 24 小时制 8.2、字符串函数

函数用法ascii(s);返回第一个字符的ascii 码char_length(s);返回字符数length(s);返回字节数，和字符集有关concat(s1,s2....);连接s1,s2...为一个字符串concat_ws(x,s1,s2...);同concat(s1,s2....); 但每个字符串之间加上xinsert(str, idx, len, replacestr);将字符串str从第idx位置开始，len个字符长的子串替换为replacestrreplace(str, a, b);用字符串b替换字符串中所有出现的字符串aupper(s);大写lower(s);小写left(s，n);返回字符串最左边n个字符right(s, n);返回字符串最右边n个字符lpad(s, len, pad);用字符串pad对str最左边进行填充，str长度为len为止rpad(s, len, pad);用字符串pad对str最右边进行填充，str长度为len为止ltrim(s);去掉s左侧的空格rtrim(s);去掉s右侧的空格trim(s);去掉s开始与结尾的空格trim(s1 from s);去掉s开始与结尾的s1trim(leading s1 from s);去掉s开始的s1trim(trailing s1 from s);去掉s结尾的s1repeat(s, n);返回s重复n次的结果space(n);返回n个空格strcmp(s1, s2);比较字符串s1，s2ascii码的大小substr(s, idx, len);返回s index位置的len个字符locate(substr, s);返回substr 首次出现的位置elt(m, s1, s2....);返回指定位置的字符串，m=1,返回s1,。。。field(s,s1,s2....);返回s在字符串列表中第一次出现的位置find_in_set(s1, s2); 返回字符串s1在字符串s2 中出现的位置

select ascii(s); select char_length(s); select length(s); select concat(s1,s2....); select concat_ws(x,s1,s2...); select insert(str, idx, len, replacestr); select replace(str, a, b); select upper(s); select lower(s); select left(s); select right(s); select lpad(s, len, pad); select rpad(s, len, pad);

-- 字符串的索引是从 1 开始的 select ascii('avb'); select char_length('abd哎哈哈'); select length('abd哎'); select concat('a','b','c'); select concat_ws('----','b','c','d'); select insert('abc', 1, 3, 'x'); select replace('abca', 'a', 'x'); select upper('abcdA'); select lower('abcdA'); select left('aaabaaaa', 4); select right('aaaaaabaaaaaab', 3); select lpad('aaabaaaa', 40, '-'); select rpad('aaabaaaa', 40, '-');

select ltrim(s); select rtrim(s); select trim(s); select trim(s1 from s); select trim(leading s1 from s); select trim(trailing s1 from s); select repeat(s, n); select space(n); select strcmp(s1, s2); select substr(s, idx, len); select locate(substr, s); select elt(m, s1, s2....); select field(s,s1,s2....); select find_in_set(s1, s2);

8.3、数值函数 1、基础函数

函数用法abs(x);返回x绝对值sign(x);返回x的符号，正数1，负数-1，0返回0pi();返回圆周率ceil(x), ceiling(x);返回大于或等于某个值的最小整数floor(x); 返回小于或等于某个值的最大整数least(e1,e2...);返回列表中的最小值greatest(e1,e2...);返回列表中的最大值mod(x, y);返回x除以y以后的余数rand();返回0-1之间的随机值round(x);返回一个对x进行四舍五入后，最接近x的整数round(x, y);返回一个对x进行四舍五入后，最接近x的值，保留小数点后y位truncate(x,y); 返回数字x截断为y位小数的结果sqrt(x); 返回x的平方根，当x的值为负数时，返回null

select abs(x); select sign(x); select pi(); select ceil(x), ceiling(x); select floor(x); select least(e1,e2...); select greatest(e1,e2...); select mod(x, y); select rand(); select round(x); select round(x, y); select truncate(x,y); select sqrt(x);

select abs(-123), abs(23), sign(-12.1); select pi(), ceil(32.23), floor(232.67), ceil(-2.6), floor(-32.23); -- 天花板， 地板 select mod(12,5); select rand(); select round(pi()), round(pi(),4); select truncate(10.1213, 3); -- 截断 select sqrt(2); -- 平方根 select truncate(round(12.345, 2), 0); -- 嵌套 2、角度与弧度互换函数

函数用法radians(10);将角度转化为弧度，x是角度degrees(0.17453292519943295);将弧度转化为角度，x是弧度

select radians(10); select degrees(0.17453292519943295);

select radians(180); select degrees(3.141592653589793); 3、三角函数 参数x是 - 弧度制 函数用法sincostanasinacosatanatan2(m,n)返回两个参数的反正切值cot返回x的余切值

select sin(0.4), cos(0.4), tan(0.4), asin(0.4), acos(0.4), atan(0.4), atan2(0.4), cot(0.4)； select sin(radians(30)); select cos(radians(60));

4、指数与对数

函数用法pow(x,y), power(x,y);返回x的y次方exp(x);返回e的x次方，其中e是常数：2.718281828459045ln(x), log(x);返回以e为底x的对数，当x<=0时，返回结果为nulllog10(x);返回以10为底的x的对数，当x<=0时，返回结果为nulllog2(x);返回以2为底的x的对数，当x<=0时，返回结果为null

select pow(x,y), power(x,y); select exp(x); select ln(x), log(x); select log10(x); select log2(x); select exp(1);

select pow(2,5), power(2,4); select exp(2); select ln(exp(2));

5、进制间的转换

函数用法bin(x);返回x的二进制编码hex(x);返回x的十六进制编码oct(x);返回x的八进制编码conv(x, f1, f2); 返回f1进制数变成f2进制数

select bin(x); select hex(x); select oct(x); select conv(x, f1, f2);

select bin(8); -- 1000 select hex(15); -- F select oct(8); -- 10