MySQL笔记|青训营笔记

这是我参与「第三届青训营 -后端场」笔记创作活动的的第3篇笔记

1、创建数据库

CREATE DATABASE 库名 ;

2、删除数据库

DROP DATABASE 库名;

3、查找所有数据库

SHOW DATABASES; 数值

tinyint 十分小的数据 1个字节

smallint 较小的数据 2个字节

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

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

bigint 较大的数据 8个字节

float 浮点数 4个字节

double 浮点数 8个字节

decimal 字符串形式的浮点数 金融计算的时候一般使用decimal DECIMAL(P,D);(p是有效数字的长度、D是小数点后的位数) 字符串

data YYYY-MM-DD,日期格式 time HH:mm:ss 时间格式 datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式 timestamp 时间戳 1970.1.1 到现在的毫秒数 较为常用 year 年份表示

null

没有值，未知 主要是要使用null进行运算，结果为null

创建数据库表

CREATE TABLE [IF NOT EXISTS] 表名(

字段名 列类型 [属性] [索引] [注释],

字段名 列类型 [属性] [索引] [注释],

字段名 列类型 [属性] [索引] [注释], PRIMARY KEY(id))

查看创建数据库的语句

SHOW CREATE DATABASE school

查看student数据表的定义语句

SHOW CREATE TABLE student

显示表的结构--

DESC student

外键

-- 学生表的 gradeid 字段，要去引用年级表的 gradeid -- 定义外键key -- 给这个外键添加约束（执行引用） reference 引用

 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 '出生日期',`
   `gradeid INT(10) NOT NULL COMMENT '学生的年级',`
   `address VARCHAR(100) 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`
 ​
 ​

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

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

  最佳实践：

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段） 我们想要使用多张表的数据，想使用外键（程序去实现）

DML语言

• 数据库意义：数据存储，数据管理
• DML（Data Manipulation Language）数据库操作语言

添加

insert

 -- 插入语句（添加）
 -- insert into 表名([字段名1,字段名2,字段名3]) values('值1'),('值2'),('值3'),...)
 INSERT INTO `grade` (`gradename`) VALUES('大四')
 ​
 -- 由于主键自增我们可以省略字段名，但是它会一一匹配，所以参数必须包含所有字段的值
 INSERT INTO `grade` VALUES (3, '大三')
 ​
 -- 插入多个字段
 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','男')

• 注意事项：

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

• 注意事项：

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

修改

 update
 ​
 -- 修改学员名字
 UPDATE `student` SET `name`='123' WHERE id = 1;
 ​
 -- 不指定条件的情况下，会改动所有表！
 UPDATE `student` SET `name`='长江七号';
 ​
 -- 修改多个属性
 UPDATE `student` SET `name`='123',email = '552456727@qq.com' WHERE id = 1;
 ​
 -- 通过多个条件定位数据
 UPDATE `student` SET `name` = '阿巴' WHERE `name`='123' AND sex='女'
 ​
 -- 语法：update 表名 set column_name = value,[set column_name = value...] where [条件]

• where 语句操作符

  • <> 或者 != ：不等于
  • between A and B ：AB之间（包括AB）

删除

delete命令

• 语法：delete from 表名 [where 条件]

•  - -- 删除数据
     DELETE FROM `student` WHERE id=1;
   ​
   ​
  

TRUNCATE 命令

• 作用：完全清空一个数据库表，表的结构和索引约束不会变！

 -- 清空 student 表
 TRUNCATE `student
 `

delete命令 和 TRUNCATE 命令 的区别

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

• 不同点：

  • TRUNCATE 重新设置 自增列 计数器会归零
  • TRUNCATE 不会影响事务

• 测试区别：

•  -- 测试 detele 和 TRUNCATE 区别
   CREATE TABLE `test`(
     `id` INT(4) NOT NULL AUTO_INCREMENT,
     `coll` VARCHAR(20) NOT NULL,
     PRIMARY KEY (`id`)
   )ENGINE=INNODB DEFAULT CHARSET=utf8
   ​
   INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')
   ​
   DELETE FROM `test` -- 不会影响自增
   ​
   TRUNCATE `test` -- 自增会归零
  

• delete 删除的问题：重启数据库现象

  • InnoDB：自增列会从1开始（存在内存当中的，断电即失）
  • MyISAM：继续从上一个自增量开始（存在文件中的，不会丢失）

DQL 查询数据（最重点）

Select 完整的语法：

 select [ALL | DISTINCT]
 {* | table.* | [table.field1[as alias1],[table.field2[as alias2],[...],...}
 from table_name [as table_alias]
     [left | right | inner + join table_name2]  -- 联合查询
     [where ...]  -- 指定结果需满足的条件
     [group by ...]  -- 分组
     [having]  -- 过滤分组的记录必须满足的次要条件
     [order by]  -- 排序 DESC 降序 ASC 升序
     [limit (起始位置，查询长度)] 

基本概念

• Data Query Language：数据查询语言
• 所有的查询操作都使用：select
• 简单复杂的查询它都能做，最重要！

指定查询字段

 -- 查询全部的学生：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 别名

去重

• 作用：去除 select 查询出来的结果中重复的数据，重复的数据只显示一条

 -- 查询一下有哪些同学参加了考试（有成绩）
 SELECT * FROM result -- 查询全部的考试成绩
 SELECT `studentno` FROM result -- 查询有哪些同学参加了考试
 SELECT DISTINCT `studentno` FROM result -- 发现重复数据，去重

数据库的列（表达式）

 SELECT VERSION()  -- 查询系统版本（函数）
 SELECT 100*3-1 AS 计算结果 -- 用来计算（表达式）
 SELECT @@auto_increment_increment -- 查询自增的步长（变量）
 ​
 -- 学员考试成绩 + 1分 查看
 SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result

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

• select 表达式 from 表

where 条件子句

• 作用：检索数据中符合条件的值
• 搜索的条件由一个或者多个表达式组成！结果为：布尔值

逻辑运算符

 SELECT studentno, `studentresult` FROM result
 ​
 -- 查询考试成绩在 95-100 分之间的
 SELECT studentno, `studentresult` FROM result
 WHERE studentresult>=95 AND studentresult<=100
 ​
 -- and &&
 SELECT studentno,`studentresult` FROM result
 WHERE studentresult>=95 && studentresult<=100
 ​
 -- 模糊查询（区间）
 SELECT studentno,`studentresult` FROM result
 WHERE studentresult BETWEEN 80 AND 100
 ​
 -- 查询除了1000号学生以外的同学的成绩
 SELECT studentno,`studentresult` FROM result
 WHERE studentno != 1000
 SELECT studentno,`studentresult` FROM result
 WHERE NOT studentno = 1000

 -- ===================模糊查询====================
 -- 查询姓李的同学
 -- 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 '%华%'
 ​
 ​
 -- ==========in（具体的一个或者多个值，不能用%，%是和like搭配使用的）===============
 -- 查询 1001，1002，1003号学员
 SELECT `studentno`,`studentname` FROM `student`
 WHERE `studentno` IN (1001,1002,1003)
 ​
 -- 查询在北京的学生
 SELECT `studentno`, `studentname` FROM `student`
 WHERE `address` IN ('北京')
 ​
 -- ==== null   not null ====
 -- 查询地址为空的学生 null ''
 SELECT `studentno`, `studentname` FROM `student`
 WHERE address='' OR address IS NULL
 ​
 -- 查询有出生日期的同学 不为空
 SELECT `studentno`, `studentname` FROM `student`
 WHERE `borndate` IS NOT NULL

联表查询

• on 和 where 的区别：不管 on 上的条件是否为真都会返回 left 或 right 表中的记录

JOIN对比

• 测试

 /*
 连接查询
   如果需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询
 ​
 - 内连接 inner join
   查询两个表中的结果集中的交集
 - 外连接 outer join
   - 左外连接 left join
     以左表作为基准，右表来一一匹配，匹配不上的，返回左表的记录，右表以NULL填充
   - 右外连接 right join
     以右表作为基准，左表来一一匹配，匹配不上的，返回右表的记录，左表以NULL填充
     */
 ​
 /*
 1.分析需求，分析查询的字段来自哪些表
 2.确定使用哪种连接查询：7选
 3.确定交叉点（这两个表中哪个数据是相同的）
 4.判断的条件： 学生表中 studentNo = 成绩表中 studentNo
 */
 ​
 --  ==========联表查询============
 -- Join（表）on (判断的条件) 连接查询
 -- where 等值查询
 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 r.`studentno` = s.`studentno`
 ​
 -- left 左连接（查询所有同学，不考试的也会查出来）
 SELECT s.studentno, studentname, subjectno, studentresult
 FROM student s
 LEFT JOIN result r
 ON s.`studentno` = r.`studentno`
 ​
 -- 查缺考的同学（左连接应用场景）
 SELECT s.studentno, studentname, subjectno, studentresult
 FROM student s
 LEFT JOIN result r
 ON s.`studentno` = r.`studentno`
 WHERE studentresult IS NULL
 ​
 -- 思考题：查询参加了考试的同学信息（学号，学生姓名，科目名，分数）
 SELECT s.`studentno`, `studentname`, `subjectname`, `studentresult`
 FROM student AS s,
 INNER JOIN result AS r,
 ON s.`studentno` = r.`studentno`
 INNER JOIN `subject` AS sub
 ON sub.subjectno = r.subjectno

什么是事务 要么都成功，要么都失败

SQL执行， A给B转账 A 1000 –> 200 B 200 SQL 执行， B收到A的钱 A 800 — B 400

将一组SQL放在一个批次中执行

事务原则 ： ACID原则

原子性是事务的基础，持久性和隔离性是手段，一致性是目的

原子性（Atomic）

要么都成功，要么都失败 一致性（Consist）

事务前后的数据完整性要保持一致 隔离性（Isolated）

事务发生，在有多个用户并发时，互不干扰 持久性（Durable）

事务一旦提交就不可逆转，被持久化到数据库中 事务隔离级别

脏读：

指一个事务读取了另外一个事务未提交的数据。 不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对） 虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。 （一般是行影响，多了一行） 执行事务

 -- ================ 事务 =====================
 ​
 -- mysql 是默认开启事务自动提交的
 SET autocommit = 0 /* 关闭 */
 SET autocommit = 1 /* 开启（默认的） */
 ​
 -- 手动处理事务
 SET autocommit = 0 -- 关闭自动提交
 ​
 -- 事务开启
 START TRANSACTION -- 标记一个事务的开始，从这个之后的 sql 都在同一个事务内
 ​
 ​
 -- 提交：持久化（成功）
 COMMIT
 -- 回滚：回到原来的样子（失败）
 ROLLBACK
 ​
 -- 事务结束
 SET autocommit = 1 -- 开启自动提交
 ​
 -- 保存点  
 SAVEPOINT -- 设置一个事务的保存点
 ROLLBACK TO SAVEPOINT -- 回滚到保存点
 RELEASE SAVEPOINT -- 删除保存点

索引 作用

提高查询速度 确保数据的唯一性 可以加速表和表之间的连接 , 实现表与表之间的参照完整性 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间 全文检索字段进行搜索优化 7.1、分类 主键索引 (Primary Key) 唯一索引 (Unique) 常规索引 (Index) 全文索引 (FullText) 主键索引

主键 : 某一个属性组能唯一标识一条记录

最常见的索引类型 确保数据记录的唯一性 确定特定数据记录在数据库中的位置 唯一索引

作用 : 避免同一个表中某数据列中的值重复 与主键索引的区别： 主键索引只能有一个 唯一索引可能有多个

MySQL 备份

• 数据库备份必要性

  • 保证重要数据不丢失
  • 数据转移

• MySQL数据库备份方法

  • mysqldump备份工具
  • 数据库管理工具，如SQLyog

• 直接拷贝数据库文件和相关配置文件
• 使用命令行导出 mysqldump 命令行使用

-- 导出 -- 1. 导出一张表 -- 　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql) 　　mysqldump -uroot -p123456 school student >D:/a.sql -- 2. 导出多张表 -- mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql) mysqldump -uroot -p123456 school student result >D:/a.sql

-- 3. 导出所有表 -- mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql) mysqldump -uroot -p123456 school >D:/a.sql

-- 4. 导出一个库 -- mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql) mysqldump -uroot -p123456 -B school >D:/a.sql

可以-w携带备份条件

-- 导入 -- 1. 在登录mysql的情况下：source 备份文件 source D:/a.sql 　　 -- 2. 在不登录的情况下 　　mysql -u用户名 -p密码 库名 < 备份文件

\