MySQL数据管理

1、物理外键（了解即可）

方式一、在创建表的时候，增加约束（麻烦）

方式二、创建表成功后，添加外键约束

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

最佳实践

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

2、DML语言（全部记住，增删改）

DQL(查)

1、数据库意义：数据存储，数据管理

2、DML语言：数据操作语言

• insert
• update
• delete

2.1、添加（insert）

-- 插入语句（添加）
-- INSERT INTO 表名([字段1],[字段2],[字段3]) VALUES('值1'),('值2')
-- 往grade表里的gradename中插值
INSERT INTO `grade`(`gradename`) VALUES('大四')

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

-- 一般写插入语句，一定要数据和字段一一兑现

-- 插入多个字段
INSERT into `grade`(`gradename`) 
VALUES('大一'),('大二')
INSERT INTO `grade`(`gradename`,`age`,`sex`) 
VALUES ('吴','3','男'),('学','6','女')

**语法：**INSERT INTO 表名([字段1],[字段2],[字段3]) VALUES(‘值1A’,‘值1B’),(‘值2A’,‘值2B’)

注意事项：

1、字段和字段之间使用英文逗号隔开

2、字段是可以省略的，但是后面的值必须要一一对应

                            id   namee age sex
INSERT INTO `grade` VALUES ('11','敏','7','女')

3、可以同时插入多条数据，values 后面的值需要使用，隔开即可 VALUES(‘值1’),(‘值2’)

2.2、修改（update）

update 修改某个条件 set 原来的值=新值

-- 修改学员名字
UPDATE `student` SET `name`='吴' WHERE id=1;

-- 不指定条件的情况下，会改动所有的表
UPDATE `student` SET `name`='看'

-- 修改多个属性
UPDATE `student` SET `name`='我', `age`='6' WHERE id=2;

-- 语法：update 表名 set colnum_name ='值1','值2' where[条件]

条件： where字句运算符 id小于/等于/大于某个值，在某个区间内修改

操作符	含义	范围	结果
=	等于	5=6	false
!=或<>	不等于	5!=6	true
>
<
>=
<=
between A and B	在A和B之间	[2,5]
AND	我和你 &&	5>1 and 1>2	false
OR	我或你 ||

-- 通过多个条件定位数据
UPDATE `student` SET `name`='我' WHERE id =3 AND age ='8'

**语法：**update 表名 set colnum_name =‘值1’,‘值2’ where[条件]

注意：

• colnum_name是数据库的列，带上``
• 条件：筛选的条件，如果没有指定则会修改所有的列
• values：是一个具体的值，也可以是一个变量（比如时间）

UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='我' AND `SEX`='女'

• 多个设置的属性之间，使用英文逗号隔开

2.3、删除（delete）

delete命令

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

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`

-- 删除指定的数据
DELETE FROM `student` WHERE id=1;

TRUNCATE 命令

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

-- 清空student表
TRUNCATE `student`

• delete和TRUNCATE区别

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

2、不同点：

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

3、了解即可：delete删除问题，重启数据库后的现象

• 引擎为INNODB 自增列从1开始，因为是存在内存中，断电即失
• MYISAM 继续从上一个自增量开始，因为是存在文件中，不会丢失

3、DQL 查询数据（最重点！）

3.1、DQL

1、Data Query Language ：数据查询语言

• 所有的查询操作都用它 select
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言
• 使用频率最高的语句

3.2、基本SELECT语句和别名使用

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

-- 查询全部的成绩
SELECT * FROM result

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 别名，给查出来的列名或表起一个名字  AS  
SELECT `studentno` AS 学号, `studentname` AS 学生姓名 FROM student AS 学生表

-- 函数 CONCAT(str1,str2,...) 拼接字符串
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student

• 语法：SELECT 字段 FROM 表

• 有时候列名字不是那么见名知意，我们起别名，用AS

  字段名 AS 别名   或    表名 AS 别名
  

3.3、去重及据库的表达式

作用：去除select 查询出来的结果中重复的数据

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

3.4、数据库的列（表达式）

-- 查询系统的版本（函数）
SELECT VERSION()

-- 用来计算（表达式）
SELECT 100*3-1 AS 计算结果

-- 查询自增的步长（变量）
SELECT @@AUTO_INCREMENT_INCREMENT

-- 学员考试成绩加一分
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result

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

• SELECT 表达式 FROM 表

3.5、where条件子句之逻辑运算符

• 作用：检索数据中符合条件的值

• 逻辑运算符

运算符	语法	结果描述
and && 与	A and B A && B	逻辑与：两个为真，结果真
or || 或	A or B A|| B	逻辑或：一个为真，结果真
not ! = 非	not A ! =A	逻辑非：真为假，假为真

尽量使用英文字母表达

-- =============================   WHERE  =======================  
-- 查询成绩在105-115分之间     与
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult>=105 AND studentresult<=115

-- 模糊查询（区间）
SELECT `studentno`,`studentresult`FROM result
WHERE studentresult BETWEEN 105 AND 115

-- 除了12号之外的学生成绩     非
SELECT `studentno`,`studentresult` FROM result
WHERE STUDENTNO !=12

SELECT `studentno`,`studentresult` FROM result
WHERE NOT studentno =12

3.6、模糊查询：本质是比较运算符

运算符	语法	描述（结果为布尔值）
IS NULL	A IS NULL	操作符为NULL,结果为真
IS NOT NULL	A IS NOT NULL	操作符为NOT 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,a3…其中某一个值中，结果为真

-- ============ 模糊查询 ==================
-- 查询姓吴的同学
-- 通过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 '%吴%'

-- =========  in(具体一个或多个值) ==============
-- 查询12，13，14号学员信息
SELECT `studentno`,`studentname` FROM student
WHERE studentno IN(12,13,14);

-- 查询在湖北武汉的学生
SELECT  `studentname`,`address` FROM student
WHERE address IN('湖北武汉')

-- ========= null  not null ================
-- 查询地址为空的学生（null）
SELECT  `studentname`,`address` FROM student
WHERE address IS NULL OR address=''

-- 查询有出生日期的同学（不为空 not null）

SELECT  `studentname`,`borndate` FROM student
where borndate is null 

3.7、联表查询JoinON详解（重难点）

join 对比

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

/*思路
1、分析需求，查询哪些字段（考试的同学 学号，姓名，科目编号，分数），来自哪些表（连接查询）
2、确定使用哪种连接查询（七种join查询）
3、确定两个表中的交集（这两个表中哪个数据是相同的）
4、连接任一交集：学生表中的studentno = 成绩表中 studentno (可用where或on连接)
*/

SELECT s.studentno,s.studentname,subjectno,studentresult FROM student s
INNER JOIN result r
WHERE s.studentno = r.studentno

-- RIGHT JOIN
SELECT s.studentno,s.studentname,subjectno,studentresult FROM student s
RIGHT JOIN result r
ON s.studentno =r.studentno

-- LEFT JOIN
SELECT s.studentno,s.studentname,subjectno,studentresult FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno

-- 查询缺考的同学
SELECT s.studentno,s.studentname,subjectno,studentresult FROM student s
LEFT JOIN result r
on s.studentno =r.studentno
WHERE studentresult is null

-- 思考题：查询参加了考试的同学信息：学号，学生姓名，科目名，分数
-- 表：student result  subject
SELECT s.studentno,s.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 表  XXX JOIN  连接的表   ON 交叉条件
-- 假设存在多张表查询，慢慢来，先查询两张表然后慢慢增加

-- FROM A LEFT JOIN B  以左边表（A表）为准
-- FROM A RIGHT JOIN B 以右边表（B表）为准


-- on是建立连接，where是结果筛选

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

3.8 、自连接（了解）及联表查询练习

1、核心：自己的表和自己的表连接，核心：一张表拆成两张一样的表

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid（父id）	categoryid(子类id)	categoryName
3	4	数据库
3	6	web开发
5	7	ps技术
2	8	办公信息

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	web开发
软件设计	数据库
美术设计	ps技术

CREATE TABLE `school`.`category`( 
`categoryid` INT(3) NOT NULL COMMENT 'id', 
`pid` INT