day26-mysql数据管理

外键

https://www.liaoxuefeng.com/wiki/1177760294764384/1218728424164736
简单来说，就是一个年级有x个班级表(一班二班)，每个班级表有x个学生(1号学生，2号学生)。那么总的学生数就需要这里通过不同的class_id对应sutdent_id，通过class_id把多个学生表串联起来(也就是分班)，这里的class_id就是外键。

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
/*
ALTER TABLE 表名
ADD CONSTRAINT 外键名(通常前缀fk)
FOREIGN KEY (字段)
REFERENCES 被引用的表(被引用的字段);
*/

注意：是分班表表需要学生表和班级表。所以是在学生总表上建立外键，学生和班级是被引用的父表。
简而言之，引用别人的表就是(从表)，类似于分班表，被引用的表就是(主表),类似于class表。是的你没有看错主从关系。
删除外键的时候，必须先删从表，再删主表，如果反着来就会报错。就像是学生表和班级表合成分班表。分班表是从表，必须先删除分班表才能删除其他两张表，仔细想想也是对的，否则先删除学生表的时候，分班表里面的结构信息就修改不了。

还有一种就是在建表的时候添加主键约束

KEY `fk_class_id`  (class_id)
CONSTRAINT `fk_class_id`
FOREIGN KEY (class_id)
REFERENCES classes (id);

以上的操作就是物理外键，数据库级别的外键，我们不建议使用！(避免数据库过多，对表无法增减)

最佳实践

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

DML语言

数据库的意义: 数据存储，数据管理

DML语言 :数据操作语言

• insert
• update
• delete

添加

-- 插入语句(添加)
-- INSERT INTO 表名([字段名1，字段2，字段3]) VALUES('值1，值2，值3')

由于主键自增我们可以省略(如果不写表的字段，他就会一个一个匹配所有字段或仅主键),如下

-- INSERT INTO 表名 VALUES('值1')
-- INSERT INTO 表名 VALUES('值1','值2')

如果是修改多个值就是用逗号和括号展开。

-- INSERT INTO 表名 VALUES('值1'),('值2')

修改

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

-- 修改所有的文件，因为没有指定所有文件
UPDATE `student` SET `name` = 'xxx';

-- 语法：
-- UPDATE 表名 SET colnum_name = value,[colnum_name2 = value2,...] WHERE [条件]

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>	…	…	…
<	…	…	…
>=	…	…	…
<=	…	…	…
BETWEEN…AND…	在…和…范围内	[2,5]
AND	和	true AND false	false
OR	或	true OR false	true

语法: UPDATE 表名 SET colnum_name = value,[colnum_name2 = value2,...] WHERE [条件]

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

删除

DELETE

语法: delete from 表名 [where 条件]

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

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

TRUNCATE

TRUNCATE `student`

DELETE 和TRUNCATE 的区别

• 相同点：都能删除数据，都不会删除表的结构
• 不同：
  • TRUNCATE 重新设置 自增列 计数器会归零
  • TRUNCATE 不会影响事务
  • DELETE 不会重置自增

简单理解为一个为删除，一个为重置

关于DELETE删除的问题，重启后会不一样

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

DQL 查询数据

Data Query Language 数据查询语言

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

-- SELECT 字段 FROM 表

SELECT * FROM student;


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

起别名

-- 别名，给结果起一个名字 AS，可以给字段或者表起别名
SELECT StudentNo AS 学号,StudentName AS 姓名 FROM student AS S;

还有一个

-- 函数 CONCAT(a,b) 拼接字符串，a，b
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student;

结果就是

姓名: xxxx
姓名: aaaa
…

去重 distinct

-- 去重，去除select查询结果中重复的数据，只显示一条
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条件子句

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

这里是mysql的文档
https://dev.mysql.com/doc/refman/8.0/en/

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or ||	a or b a ||b	逻辑或
not !	not a !a	逻辑非

表，列也不区分大小写

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为null，结果为真
IS NOT NULL	a is not null	如果操作符部位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 id,`name` FROM student WHERE `name` LIKE '刘%';

-- 查询刘x，x代表一个字符

SELECT id,`name` FROM student WHERE `name` LIKE '刘_';

-- 查询刘xx，xx代表2个字符

SELECT id,`name` FROM student WHERE `name` LIKE '刘__';

-- 查询名字中间有嘉字的同学 %嘉%
SELECT id,`name` FROM student WHERE `name` LIKE '%嘉%';

-- 查询1001，1002，1003号

SELECT id,`name` FROM student WHERE id IN (1001,1002,1003);

-- 查询在北京，上海的学生
SELECT id,`name` FROM student WHERE id IN ('北京','上海');

join

通常的思路步骤就是，

1. 分析需求，分析查询的字段来自哪些表，(连接查询)
2. 确定使用哪种连接查询？七种
3. 确定交叉点，这里个表中哪个数据是相同的
4. 判断的条件

SELECT s.studentId, studentName,studentResult FROM student AS s INNER JOIN result AS r WHERE s.studentId = r.studentResult;

ON 是先筛选后关联，WHERE是先关联后筛选
join on是连接查询
where是等值查询

1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

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

对的，左右没有错

首先要查询哪些数据select
从那几个表中查询 from 表xxx join 连接的表 on 交叉条件(并不等同于where的等值判断)
假设存在一种多张表查询，慢慢来，先查询两张表再慢慢增加

自连接

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

SELECT A.name AS '父栏目', B.name AS '子栏目' FROM student AS A， student AS B WHERE A.id = B.pid;

sql主要语法

SELECT select_list
　　[ INTO new_table ]
　　FROM table_source
　　[ WHERE search_condition ]
　　[ GROUP BY group_by_expression ]
　　[ HAVING search_condition ]
　　[ ORDER BY order_expression [ ASC | DESC ] ]