MySQL数据库详解（二）

紧跟上一篇文章，我们继续学习MySQL数据库。
数据库意义：数据存储，数据管理

DML语言：数据操作语言

• Insert
• update
• delete

1.添加Insert

语法:insert into 表名([字段名1,字段2,字段3])values('值1'),('值2'),('值3'，....)
举例：

-- 插入语句（添加） -- insert into 表名([字段名1,字段2,字段3])values('值1'),('值2'),
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 由于主键自增我们可以省略 (如果不写表的字段，他就会一一匹配) 
INSERT INTO `grade` VALUES('大三')
-- 插入多个字段 
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaaaa','男')

注意：

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

2.修改update

语法：UPDATE 表名 set colnum_name = value,[colnum_name = value,....] where [条 件]
举例：

-- 修改学员名字,带了简介 
UPDATE `student` SET `name`='哥哥' WHERE id = 1;
-- 不指定条件的情况下，会改动所有表！ 
UPDATE `student` SET `name`='长江7号'
-- 修改多个属性,逗号隔开 
UPDATE `student` SET `name`='爸爸',`email`='24736743@qq.com' WHERE id = 1;

注意：

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

3.删除

(1)delete命令

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

-- 删除数据 （避免这样写，会全部删除） 
DELETE FROM `student`
-- 删除指定数据 
DELETE FROM `student`WHERE id = 1;

(2)TRUNCATE 命令

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

-- 清空 student 表 
TRUNCATE `student`

(3)两者间的区别：

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

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

  注意： DELETE删除的问题 ，重启数据库，现象

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

DQL语言：(Data Query LANGUAGE : 数据查询语言)

• 所有的查询操作都用 Select
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

1.Select完整的语法：按照顺序

2.指定查询字段

语法：SELECT 字段,... FROM 表

-- 查询全部的学生   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 DISTINCT `StudentNo` FROM result 
SELECT VERSION()  -- 查询系统版本 （函数） 
SELECT 100*3-1 AS 计算结果  -- 用来计算  （表达式） 
SELECT @@auto_increment_increment -- 查询自增的步长  (变量)
-- 学员考试成绩 + 1分查看 
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result

3.where 条件子句

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

(1)逻辑运算符

举例：

-- and   && 
SELECT studentNo,`StudentResult` FROM result WHERE StudentResult>=95 && StudentResult<=100
-- !=   not 
SELECT studentNo,`StudentResult` FROM result WHERE NOT studentNo = 1000

(2)比较运算符（模拟查询）

举例：

-- like结合  %(代表0到任意个字符)   _(一个字符) 
SELECT `StudentNo`,`StudentName` FROM `student` WHERE StudentName LIKE '刘_'-- 查询姓刘的同学，名字后面只有一个字的 
SELECT `StudentNo`,`StudentName` FROM `student` WHERE StudentName LIKE '%嘉%'-- 查询名字中间有嘉字的同学 %嘉% 
--  in （具体的一个或者多个值） 
SELECT `StudentNo`,`StudentName` FROM `student` WHERE `Address` IN ('安徽','河南洛阳');
--  null  not null
SELECT `StudentNo`,`StudentName` FROM `student` WHERE address=''  OR address IS NULL

4.联表查询 :关键字:JOIN

• join （连接的表） on（判断的条件） 连接查询 -
• where 等值查询

七种方式：

主要的三种方式的区别：

操作	描述
Inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

举例说明：

-- 查询参加了考试的同学（学号，姓名，科目编号，分数） 
SELECT * FROM student S
ELECT * FROM result
/* 思路 
1. 分析需求，分析查询的字段来自哪些表，（连接查询） 
2. 确定使用哪种连接查询？ 7种 确定交叉点（这两个表中哪个数据是相同的）
3.  判断的条件 ： 学生表的中 studentNo = 成绩表 studentNo */
SELECT s.studentNO,studentName,SubjectNo,StudentResult FROM student AS s 
INNER JOIN result AS r 
ON s.studentNO = r.studentNO
-- Right Join 
SELECT s.studentNO,studentName,SubjectNo,StudentResult FROM student s 
RIGHT JOIN result r 
ON s.studentNO = r.studentNO
-- Left Join 
SELECT s.studentNO,studentName,SubjectNo,StudentResult FROM student s 
LEFT JOIN result r 
ON s.studentNO = r.studentNO

5.自连接：自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

CREATE TABLE `category` (  
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',  
`pid` INT(10) NOT NULL COMMENT '父id',  
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',  
PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`) VALUES('2','1','信息技术'), ('3','1','软件开发'), ('4','3','数据库'), ('5','1','美术设计'), ('6','3','web开发'), ('7','5','ps技术'), ('8','2','办公信息');
-- 查询父子信息: 把一张表看为两个一模一样的表 
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目' 
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

6.分页和排序

(1)排序

-- 排序 ： 升序 ASC ， 降序DESC 
-- ORDER BY 通过那个字段排序，怎么排 
ORDER BY StudentResult ASC
ORDER BY StudentResult DESC

(2)分页

-- 语法： limit 起始值，页面的大小 
-- 第一页  limit 0,5      （1-1）*5 
-- 第二页  limit 5,5       （2-1）*5 
-- 第N页   limit 0,5       （n-1）* pageSize，pageSize 
-- 【pageSize：页面大小】 
-- 【(n-1)* pageSize:起始值】 
-- 【n ：当前页 】 
-- 【数据总数/页面大小 = 总页数】

7.子查询（由里及外）

本质 ： 在where语句中嵌套一个子查询语句

-- 查询所有数据库结构-1 的学生学号 
SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM `result` 
WHERE SubjectNo = (     
            SELECT SubjectNo FROM `subject`      
            WHERE SubjectName = '数据库结构-1'
 ) ORDER BY StudentResult DESC

8.分组和过滤

-- 查询不同课程的平均分，高分，低分，平均分大于80 -- 核心： （根据不同的课程分组） 
SELECT SubjectName, AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 高 分,MIN(StudentResult) AS 低分 FROM result r 
INNER JOIN `subject` sub 
ON r.`SubjectNo` = sub.`SubjectNo` 
GROUP BY r.SubjectNo              分组     
HAVING 平均分>80                   过滤

MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

1.常用函数

-- 数学运算 
SELECT ABS(-8)  
-- 绝对值 
SELECT CEILING(9.4) 
-- 向上取整 
SELECT FLOOR(9.4)  
-- 向下取整 
SELECT RAND() 
-- 返回一个 0~1 之间的随机数 
SELECT SIGN(10)   -- 判断一个数的符号   0-0   负数返回-1，正数返回 1
-- 字符串函数 
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度 
SELECT CONCAT('我','爱','你们') -- 拼接字符串 
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长 度 SELECT LOWER('KuangShen') -- 小写字母 
SELECT UPPER('KuangShen') -- 大写字母 
SELECT INSTR('kuangshen','h') -- 返回第一次出现的子串的索引 
SELECT REPLACE('坚持就能成功','坚持','努力') --    替换出现的指定字符串 
SELECT SUBSTR('坚持就能成功',4,6) -- 返回指定的子字符串 (源字符串,截取的位置，截 取的长度) 
SELECT REVERSE('清晨我上马') -- 反转
-- 查询姓 周的同学，替代名字 邹 
SELECT REPLACE(studentname,'周','邹') FROM student WHERE studentname LIKE '周%'
-- 时间和日期函数 （记住） 
SELECT CURRENT_DATE() -- 获取当前日期 
SELECT CURDATE() -- 获取当前日期 
SELECT NOW() -- 获取当前的时间 
SELECT LOCALTIME() -- 本地时间 
SELECT SYSDATE() -- 系统时间
-- 系统 
SELECT SYSTEM_USER() 
SELECT USER() 
SELECT VERSION()

2.聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

-- 都能够统计 表中的数据 （ 想查询一个表中有多少个记录，就使用这个count() ） 
SELECT COUNT(`BornDate`) FROM student; -- Count(字段)，会忽略所有的 null 值 
SELECT COUNT(*) FROM student;  -- Count（*），不会忽略 null 值， 本质 计算行数 
SELECT COUNT(1) FROM result;   -- 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

索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

1.索引的分类

• 主键索引 （PRIMARY KEY ） ：唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引 （UNIQUE KEY） ：避免重复的列出现，唯一索引可以重复，多个列都可以标识位 唯一索引
• 常规索引 （KEY/INDEX） 默认的， index。key 关键字来设置
• 全文索引 （FullText）
  – 在特定的数据库引擎下才有，MyISAM
  – 快速定位数据

2.索引的基本语法

-- 显示所有的索引信息 
SHOW INDEX FROM student
-- 增加一个全文索引 （索引名）  列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);
-- EXPLAIN 分析sql执行的状况 
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

3.索引的原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上！

事务：要么都成功，要么都失败

1.事务的原则

• 原子性（Atomicity）：要么都成功，要么都失败
• 一致性（Consistency）：事务前后的数据完整性要保证一致
• 持久性（Durability）— 事务提交：事务一旦提交则不可逆，被持久化到数据库中！
• 隔离性（Isolation）
  事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作 数据所干扰，多个并发事务之间要相互隔离。

2.事务执行

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