MySQL学习笔记

1.数据库介绍

1.1什么是数据库

数据库：(DB,DataBase)
概念:数据仓库，软件，安装在操作系统之（windows，Linux，mac）上的！SQL,可以存储大量的数据，（500万以上需要优化）!
作用:存储数据，管理数据 Excel

1.2数据库分类

1.2.1关系型数据库:(SQL)

• MySQL, Oracle, sql Server, DB2, SQLite
• 通过表和表之间，行和列之间的关系进行数据的存储

1.2.2非关系型数据库

• Redis, MongDB
• 非关系型数据库，对象存储，通过对象自身的属性来决定。

1.3操作数据库

创建数据库

CREATE DATABASE IF NOT EXISTS student;

删除数据库

DROP DATABASE student

使用数据库

USE student;

查看数据库

SHOW DATABASES;

1.4数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小 3个字节
• int 标准的整数 4个字节（常用）
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节 （精度问题）
• decimal 字符串形式的浮点数,金融计算的时候，一般用

字符串

• char 字符串固定大小 0-255
• varchar 可变字符串 0-65535（常用）
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 (保存大文本)

时间日期

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

1.5数据库外键

方法一：创建表的时候，增加约束

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

方式二： 创建表成功后添加外键

以上的操作都是物理外键，数据库级别外键，不建议使用。（避免数据库过多造成困扰）
最佳实践

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

1.6反引号的使用

在 MySQL 中，反引号用于引用 标识符（如数据库名、表名、列名等）。使用反引号的主要目的是：

• 避免与保留字冲突：如果你的标识符与 MySQL 的保留字（如 SELECT、WHERE、ORDER 等）相同，使用反引号可以避免语法错误。

  SELECT `select`, `from` FROM `where`;
  

  处理特殊字符或空格：如果标识符中包含空格、特殊字符或以数字开头，反引号是必需的。

  SELECT `student name`, `student-result` FROM `student data`;
  

1.6.1何时可以使用反引号

如果你的标识符符合以下条件，可以省略反引号：

• 仅包含字母、数字和下划线：如 studentno、student_result。
• 不与任何保留字冲突。
• 不包含空格或特殊字符。

2.DQL查询数据

(Data Query Language) :数据查询语言
所有的查询操作都用它 Select
简单的查询，复杂的查询它都能做
数据库中最核心的语言
使用频率最高的语言

2.1语法

2.1.1 selcet from

SELECT 字段 … FROM 表

-- 查询所有学生
select * from student;

-- 查询指定字段
select `studentno`, `studentname` from student;

有时候，列名字不是那么见名知意。我们起别名 AS 字段名 AS 别名 表名 AS 别名

-- 给结果取名字 字段或表名
select `studentno` as '学号', `studentname` as '名字' from student as s;

distinct 去重

-- 去重
select distinct `studentno` from result;

2.1.2 where 条件子句

逻辑运算符

运算符	语法	结果
and &&	a and b a && b	逻辑与
or ||	a or b a
not !	not a !a	逻辑非

-- 查询成绩在95~100之间
select studentno,studentresult from result 
where studentresult >= 95 && studentresult <= 100;

select studentno,studentresult from result 
where studentresult >= 95 and studentresult <= 100;

-- 模糊查询 （区间）
select studentno,studentresult from result 
where studentresult between 95 and 100;

select studentno,studentresult from result 
where studentno != 1000;

select studentno,studentresult from result 
where not studentno = 1000;

模糊查询：比较运算符：

运算符	语法	描述
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…)	如果a在(a1,a2,a3…)之间，结果为真

-- like

-- 模糊查询 %（代表0到任意个字符） _一个字符 不能用等号 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
-- 查询 1001 1002 1003号学员
select studentno, studentname from student 
where studentno in (1001,1002,1003);

select studentno, studentname from student 
where address in ('%深圳%','北京');

select studentno, studentname from student 
where borndate is not null;

2.2连表查询

JOIN对比

连表查询思路:

1.分析需求,分析查询的字段来自哪些表(连接查询)

2.确定使用哪种连接查询?

确定交叉点(这两个表中的哪个数据是相同的)

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

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

-- LEFT JOIN
SELECT s.studentno,studentname, subjectno, studentresult 
FROM student s
LEFT JOIN result 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;

-- 查询缺考的同学
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 AS 学生编号, studentname, r.subjectno,  subjectname, studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
LEFT JOIN subject sub 
ON r.subjectno = sub.subjectno;

-- 查询学员所属的年级（学号，姓名，年级）
SELECT studentno, studentname, gradename
FROM student s  
LEFT JOIN grade g 
ON s.gradeid = g.gradeid;

-- 查询参加c语言-2考试的同学信息 学号 姓名 科目名 分数
SELECT s.studentno, studentname, subjectname, studentresult
FROM student s 
RIGHT JOIN result r 
ON s.studentno = r.studentno
LEFT JOIN subject sub 
ON sub.subjectno = r.subjectno
WHERE subjectname = 'C语言-2';

自连接

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

父类:

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

子类:

pid	categoryid	categoryname
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

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

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

-- 查询父子信息 子类的pid等于父类的id
select a.categoryname as '父栏目',b.categoryname as '子栏目'
from category as a, category as b
where a.categoryid = b.pid

2.3分页和排序

排序

--  排序 升序asc 降序 desc 
-- order by 通过哪个字段排序 顺序
select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on s.studentno = r.studentno
inner join subject sub
on r.subjectno = sub.subjectno
where sub.subjectname = 'C语言-2'
order by studentresult asc

分页

-- 为什么要分页
-- 缓解数据库压力，给人的体验更好
-- 分页，每页显示五条数据

-- 语法： limit 起始页，页面的大小
-- limit 0,5 -- 1-5
-- limit 1,5 -- 2-6
-- limit 6,5

-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit pagesize*（n-1）,pagesize
-- [pagesize*（n-1）,pagesize]
-- pagesize 页面大小
-- n 当前页
-- 【数据总数/页面大小=总页数】

2.4子查询嵌套查询

什么是子查询?

在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句

嵌套查询可由多个子查询组成,求解的方式是由里及外;

子查询返回的结果一般都是集合,故而建议使用IN关键字;

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,studentresult
FROM result 
WHERE subjectno = (
      SELECT subjectno FROM subject
      WHERE subjectname = '数据库结构-1'
      )
ORDER BY studentresult DESC;

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno, studentname 
FROM result r
INNER JOIN `subject` sub 
ON r.subjectno = sub.subjectno
INNER JOIN student s 
ON s.studentno = r.studentno
WHERE subjectname = '高等数学-2' && studentresult >= 80

-- 分数不小于80分的学生的学号和姓名 课程为 高等数学-2 
-- 方法二：使用子查询(执行顺序:由里及外)
SELECT DISTINCT s.studentno, studentname
FROM student s
INNER JOIN result r 
ON s.studentno = r.studentno
WHERE studentresult >= 80 AND subjectno = (
				SELECT subjectno
				FROM `subject`
				WHERE subjectname = '高等数学-2')
				
-- 方法三: 	嵌套查询
SELECT studentno,studentname
FROM student
WHERE studentno IN (
		SELECT studentno FROM result WHERE studentresult >= 80 AND subjectno =(
				SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'))

3.MySQL函数

3.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('2323232') -- 返回字符串长度
SELECT CONCAT('我','233') -- 拼接字符串
SELECT INSERT('java',1,2,'cccc') -- 从某个位置开始替换某个长度 (str,pos,len,newstr) -->ccccva
SELECT INSTR('kuangshen','h') --返回首次出现的位置7
SELECT UPPER('abc') 
SELECT LOWER('ABC')
SELECT SUBSTR('123456',4,6) -- 返回从4开始的长度为6的字符串

SELECT REPLACE('坚持就能成功','坚持','努力')
-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student WHERE studentname LIKE '周%'

-- 时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME()  -- 本地时间
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()