MySQL数据管理

关系操作符

运算符	含义	例子	结果
=	等于	5=6	false
<> 或 !=	不等于	5<>6	true
>
<
<=
>=
!<	不小于
!>	不大于

主键

主键：对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。例如，students表的两行记录：

id	class_id	name	gender	score
1	1	小明	M	90
2	1	小红	F	95

每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义。

假设我们把name字段作为主键，那么通过名字小明或小红就能唯一确定一条记录。但是，这么设定，就没法存储同名的同学了，因为插入相同主键的两条记录是不被允许的。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可

外键（了解）

当我们用主键唯一标识记录时，我们就可以在students表中确定任意一个学生的记录：

id	name	other columns...
1	小明	...
2	小红	...

我们还可以在classes表中确定任意一个班级记录：

id	name	other columns...
1	一班	...
2	二班	...

但是我们如何确定students表的一条记录，例如，id=1的小明，属于哪个班级呢？

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。

为了表达这种一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应：

id	class_id	name	other columns...
1	1	小明	...
2	1	小红	...
5	2	小白	...

这样，我们就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录。

给一张表 students 添加外键约束：

ALTER TABLE students
ADD CONSTRAINT fk_class_id  #外键约束的名称`fk_class_id`可以任意
FOREIGN KEY (class_id) # 指定了`class_id`作为外键
REFERENCES classes (id); #指定了这个外键将关联到`classes`表的`id`列（即`classes`表的主键）

添加了外键的表(students)为从表，被外键引用的表（classes）为主表，要删除主表前需先删除从表

注意：以上外键是数据库级别外键，不推荐使用

最佳实现：

• 数据库就是单纯的表，只有行（记录）、列（字段）
• 想使用多张表数据，想用外键（用程序如Java语言实现）

DML语言（全部记住）

即数据操作语言

• 插入(增)一行（记录）：insert into...values...

insert into `表名`([字段名1，字段名2，...]) values ('值1a', '值1b'), ('值2a', '值2b'), ...;

例：

INSERT INTO `student` ( `id`, `name` )
VALUES ( 1001, '张三' ), (1002, '李四'), (1003, '王五');

• 删除一列（字段）：
  • delete
  • truncate
  • 相同：都会删除记录，表的结构和索引、约束不会变
  • 区别：
    1. truncate 重新设置自增列，计数器会归零；delete不会影响自增
    2. truncate 不会影响事务

-- 删除一条记录
delete from `student` where id = '1001';

-- 删除所有记录（不建议这样写）
delete from `student`;

-- 清空表的所有记录，但表的结构和索引不变
truncate table `student`;

对不同引擎的数据库使用delete语句后，重启数据库，现象：（了解）

• InnoDB：自增列会从1开始（因为保存在内存中）
• MyISAM：继续从上一个自增量开始（保存在文件中）

• 修改：update...set...

-- 用where指定条件
update `student` set `name`='wangwu' where `id` = `1001`;

-- 不指定条件情况下，会改动所有行（即记录）
update `student` set `name`='wangwu';

-- 修改多个字段, 用逗号隔开
update `student` set `name`='wangwu', `age`=20 where `id` = `1001`;

-- 通过多个条件定位数据, 用逻辑运算符and、or...
update `student` set `name`='wangwu' where `id` = `1001` and `age` = 18;

注意 【=】 含义：

在set这样的语句后面表示赋值；

在where后面表示关系运算符，表示比较；

DQL查询数据（最重要）

DQL（data query language：数据查询语言）

• 所有查询(简单、复杂)都用它
• 数据库中最核心、重要的语句
• 使用频率最高的语句

基本查询操作 select

1. 查询字段

   -- 查询全部字段  select 字段 from 表
   select * from `student`;
   
   -- 查询指定字段  select 字段1[, 字段2, ...] from 表;
   select name, age from student;
   
   -- 别名，给结果(字段、表。。。)起个名字 as, as也可以省略不写
   select name as `学生姓名`, age as `年龄` from `student` as `s`;
   
   -- 函数concat(a, b): 连接a、b字符串
   select concat('姓名:', name) as 新名字 from student;
   

2. 去重 distinct

   -- 去除select后重复的数据，只留下一条
   select distinct `age` from student;
   

3. 其他

   -- 查询系统版本
   select version();  -- version()是个函数
   
   -- 计算
   select 100*3-1 as 计算结果;
   
   -- 所有学员考试成绩 +1 分
   select `name`, `grade` + 1 as 提成1分后 from `student`; 
   
   -- 查询系统内置变量值
   select @@auto_increment_increment;  -- 查询自增步长
   
   

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

select 表达式 from 表;

where语句

检索数据中符合条件的值

逻辑运算符	语法	描述
and &&	a and b、 a&&b	2个都为真返回真
or `		`
not !	not a、 !a	。。。

SELECT
	id AS 学号,
	`name` AS 姓名 
FROM
	student 
WHERE
	grade > 80 
	AND age < 21;

SELECT
	`id` AS 学号,
	`name` AS 姓名,
	`grade` as 成绩
FROM
	student 
WHERE
	grade BETWEEN 70 AND 90;

模糊查询

运算符	语法	描述
is null	a is null	如果a是null，则为真
is not null	a is not null	如果a不是null，则为真
between	a between b and c	若a在b和c之间，结果为真
like	a like b	sql匹配，若a匹配b，结果为真
in	a in(a1,a2,a3,a4,...)	若a是a1,a2,a3,a4...中的一个值，结果为真

like：通常结合 %、_ 来使用

-- 查找以王字开头的name  
-- “%”表示【任意多个】【任意的】字符，“_”表示任意【一个】字符
SELECT `id`, `name` FROM student
WHERE `name` LIKE '王%'; -- 表示以【王】字开头


-- 查找以王字开头且【名字为2个字】的name
SELECT `id`, `name` FROM student
WHERE `name` LIKE '王__';  -- 两个下划线 


-- 查找生日不是null的
SELECT `id`, `name` FROM student
WHERE `birthday` is not null;

联表查询 join on

同时在2个表中查找数据(必须要有交集)

/**
有2张表(都有id、name字段)：
1. `student`：班级学生基本信息
2. `exam`：年级学生考试信息

【问题：查找出本班级学生的考试信息】
*/

-- =====================================
-- 取交集 inner join
select s.id as 学号, s.name as 姓名 
from student as s inner join exam as e   -- as可以省略
on s.id = e.id;

-- =====================================
-- 仅左边的表有，left join   
SELECT
	s.id  学号,       -- 省略as
	s.NAME  姓名 
FROM
	student s
	LEFT JOIN exam e ON s.NAME = e.NAME 
WHERE
	e.`name` IS NULL;
-- =====================================

-- 两个表的所有人 

mysql 不支持 full outer join

自连接及联表查询

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

分页和排序

• 排序 order by

  • 升序用 ASC， 降序用DESC

  • order by 表示查询结果怎么排（升序、降序），通过哪个字段排

    比如：

    -- order 字段  ASC 升序
    -- order 字段  DESC 降序
    SELECT * FROM exam ORDER BY `grade` ASC;  -- 成绩按升序排
    

• 分页 limit：可以缓解数据库压力、给人体验更好、瀑布流

  • 语法：

    -- limit 起始下标(从0开始) 页面大小
    
    -- 从第0条数据开始，显示2条数据
    SELECT * FROM exam ORDER BY `id` DESC LIMIT 0,2;
    

子查询与嵌套查询

where 表达式【这个值是计算出来的】

即：where 后面还跟select语句

MySQL常用函数

数学运算

abs()

ceiling()  -- 向上取整  9.1 -> 10
floor() -- 向下取整  9.1 -> 9

rand()  -- 返回0~1随机数

sign()  -- 判断一个数符号 0->0  负数->-1  正数->1

字符串

char_length()  -- 字符串长度

concat('a', 'b', 'c') -- 字符串连接 返回abc

-- 字符索引从1开始，替换时，把第n1开始的共len个字符替换为str2
insert(str1, n1, len, str2)
-- 如
select insert('abcdef', 2, 3, '5555'); -- 返回 a5555ef

-- 将一个字符串str1中的指定子字符串substr替换为新字符串str2
replace(str1, substr, str2)

-- 截取指定字符串
substr(str, a, len); -- 截取str中第a个开始，共len个字符的子串

-- 大小写转换
upper()
lower()

-- 返回在str第一次出现的字符串substr的索引
instr(str, substr)  -- 没有则返回0

-- 反转字符串
reverse(str)

时间-日期函数

-- 当前日期
SELECT CURRENT_DATE()
SELECT CURDATE()

SELECT NOW(); -- 当前时间 2021-04-20 20:05:44
SELECT LOCALTIME(); -- 本地时间
SELECT SYSDATE(); -- 系统时间

SELECT YEAR(NOW());  -- 年
SELECT MONTH(NOW()); -- 月
SELECT DAY(NOW());	-- 日
SELECT HOUR(NOW());	-- 时
SELECT MINUTE(NOW()); -- 分
SELECT SECOND(NOW()); -- 秒

系统函数

SELECT SYSTEM_USER();  -- 当前系统用户
SELECT USER()			-- 当前用户
SELECT VERSION()		-- 版本号

聚合函数（常用）

count() -- 计数有多少个记录
	select count(字段) from tablename;   -- 忽略所有null值
	select count(*) from tablename;  -- 不会忽略null值，计算行数
	select count(1) from tablename; -- 不会忽略null值，与上一个基本相同
	
sum()
	select sum(字段) from tablename;
	
avg()
	select avg(字段) from tablename;

max()
	select max(字段) from tablename;

min()
	select min(字段) from tablename;

select语法总结


数据库级别的md5摘要算法

MD5消息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的【密码散列函数】

md5不可逆，具体值的md5值是一样的

md5破解网站破解原理，其背后有一个巨大的字典库，你给它一个md5值，它就会搜索字典中的哪个字符串经过md5转换后与你输入的一样

SQL使用函数 MD5()进行加密