MySQL

1、初始MySQL

1.1 学习数据库的目的

1、岗位需求

2、现在的世界，大数据时代~

3、存数据

4、数据库是所有软件体系中最核心的存在

1.2 什么是数据库

数据库 (DB , DataBase)

概念：数据仓库，软件，安装在操作系统之上

作用：存储数据，管理数据

1.3 数据库分类

关系型数据库

• MySQL ，Oracle ，SqlServer，DB2，SQLlite
• 通过表核表之间，行和列之间的关系进行数据的存储

非关系型数据库

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

DBMS（数据库管理系统）

• 数据库的管理软件，科学有效的管理我们的数据，维护和获取数据
• MySQL，数据库管理系统

1.4 MySQL简介

​ MySQL是一个关系型数据库管理系统，由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

​ 体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站、或大型网站（集群）的开发都选择 MySQL 作为网站数据库。

1.5 SQLyog

SQLyog中每个操作都对应了一条SQL语句

1.6 连接数据库

1、命令行连接、以及相关操作

mysql -uroot -p990818 --连接数据库

--所有语句结尾使用;
--单行注释
/*
多行注释
*/

show databases; --查看所有的数据库

use school; --切换数据库

show tables; --查看数据库中所有的表

describe student; --显示数据库中所有的表的信息

create database yl; --创建一个数据库

exit; -- 退出连接

数据库 xxx 语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

操作数据库 > 操作数据库中的表 > 操作数据库中的表中的数据

2.1 操作数据库

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] yl;

2、删除数据库

DROP DATABASE [IF EXISTS] yl;

3、使用数据库

-- 如果你的表名或者字段名是一个特殊字符，就需要带``
USE `school`;

4、查看数据库

--查看所有的数据库
SHOW DATABASES;

学习思路：

• 对照sqlyog的历史记录查看sql
• 固定的语法或者关键字必须要强行记住

2.2 数据库的列类型

加粗字体为常用值

数值

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

字符串

• char 字符串固定大小 0~255

• varchar 可变字符串 0~65535

• tinytext 微型文本 2^8-1

• text 文本串 2^16-1 保存较大文本

时间日期

java.util.Date

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

null

• 没有值，未知
• 不要使用NULL进行运算，结果为NULL

2.3 数据库的字段属性（重点）

Unsigned:

• 无符号的整数
• 声明了该列不能声明为负数

zerofill：

• 0填充的
• 不足的位数，使用0来填充

自增：

• 通常理解为自增，自动再上一条记录的基础上+1（默认值为1，可修改）
• 通常用来设计唯一的主键~index，必须是整数类型
• 可以自定义设计主键自增的起始值和步长

非空 NULL 、 NOT NULL：

• NOT NULL ,如果不给它赋值，就会报错
• NULL , 如果不填写值，默认就是null

默认：

• 设置默认的值
• 例如： sex，默认值为 男 ，如果不指定该列的值，则会有默认的值 男

2.4 创建数据库表（重点）

--AUTO_INCREMENT 自增
--字符串使用单引号括起来
--所有语句后加， 最后一句不加
--表名和字段名用``括起来
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

格式：

CREATE TABLE [IF NOT EXISTS] `表名`（
	'字段名' 列类型 [属性] [索引] [注释],
	'字段名' 列类型 [属性] [索引] [注释],
	...
	'字段名' 列类型 [属性] [索引] [注释],
）[表类型] [字符集设置] [注释]

常用命令：

--查看创建数据库的语句
SHOW CREATE DATABASE school

--查看student数据表的定义语句
SHOW CREATE TABLE student

--显示表的结构
DESC student

2.5 数据表的类型

/*
INNODB 默认使用
MYISAM 早年使用
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规操作：

• MYISAM 节约空间，速度较快
• INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在：C:\ProgramData\MySQL\MySQL Server 8.0\Data

一个文件夹就对应了一个数据库，本质还是文件存储！

设置数据库的字符集编码

CHARSET = utf8

不设置的话，会是mysql默认的字符集编码~（不支持中文）

• MySQL的默认编码是Latin1，不支持中文

2.6 修改表和删除表

修改表

-- 修改表名：
ALTER TABLE student RENAME AS students;

-- 增加表的字段
ALTER TABLE students ADD score INT(3);

-- 修改表的字段
-- 修改约束
ALTER TABLE students MODIFY score INT(5);

-- 字段重命名
ALTER TABLE students CHANGE score scores INT(10);

-- 删除表的字段
ALTER TABLE students DROP scores;

删除表

DROP TABLE IF EXISTS student;

所有的创建和删除操作尽量加上判断，避免报错

注意：

• 字段名 `` 包裹
• sql关键字大小写不敏感，尽量使用小写，方便查看
• 所有的符号全部使用英文

3、MySQL数据管理

3.1 外键（了解）

方式一

CREATE TABLE `grade`(
  `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `gradename` VARCHAR(10) NOT NULL COMMENT '年级名',
  PRIMARY KEY(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references引用
CREATE TABLE `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `gradeid` INT(10) NOT NULL COMMENT '年级id',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid`(`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

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

方式二

-- 创建表的时候没有外键约束，后来添加
ALTER TABLE student 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES grade(`gradeid`)

以上的操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰，了解即可）

最佳实践：

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

3.2 DML语言（记住）

**数据库意义：**数据存储，数据管理

DML语言：数据操作语言

• insert
• update
• delete

3.3 添加

insert

-- 插入语句 insert into 表名([字段1，字段2,...]) values('','',...),()...
INSERT INTO `grade`(`gradeid`,`gradename`)
VALUES(1,'哈哈')

-- 不在表名后跟上表的字段，字段会自动一一对应
INSERT INTO `grade`
VALUES(2,'嘿嘿')

-- 一次插入多条数据
INSERT INTO `grade`
VALUES(3,'喂喂'),(4,'嘟嘟')

注意事项：

1. 字段和字段之间使用 英文逗号 隔开
2. 字段是可以省略的，但是后面的值必须要一一对应
3. 可以用是插入多条数据，values 后面的值，需要使用，隔开

3.4 修改

update

-- 修改语句
UPDATE `student` SET `birthday`='2001-01-01' WHERE `name`='黄忠'

-- 修改多个属性，用逗号隔开
-- update 表名 set 字段名='值',[字段名='值'...] where [条件]
UPDATE `student` SET `name`='诸葛亮',address='南阳' WHERE gradeid=5

条件：where子句运算符id等于，大于某个值，在一定的区间内修改…

操作符

操作符	含义
=	等于
<> 或者 ！=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN … AND…	在某个范围内 [a,b]
AND	同时满足时成立 （&&）
OR	满足一个及以上成立 (||)

语法：

UPDATE 表名 conlnum_name = value , [  conlnum_name = value ...] where [条件]

注意：

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

3.5 删除

delete

语法：

delete from 表名 [where 条件] 


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

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

truncate 命令

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

-- 清空 student 表
truncate `student`

4、DQL查询数据（最重点）

4.1 DQL

（Data Query Language : 数据库查询语言）

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

4.2 基础查询

-- 查询全部学生
SELECT * FROM `student`

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

-- 可以给查询的字段起别名
SELECT `studentno` AS 学号,`gradeid` AS 年级编号 FROM `student`

-- AS 可以 省略 效果相同
SELECT `studentno` 学号,`gradeid` 年纪编号 FROM `student`

-- 给表起别名
SELECT * FROM `student` AS stu

-- 函数 Concat(a,b)
SELECT CONCAT('学号：',`studentno`) FROM `student`

可以通过起别名的方式来让表格更加直观，起别名时 AS 可以省略

distinct 去掉重复数据

-- 查询参加考试学生的学号
SELECT `studentno` FROM result	-- 相同学生不同科目也会被查询出来

SELECT DISTINCT `studentno` FROM result -- 去掉重复的学号学生数据

数据库的列

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

-- 计算（表达式）
SELECT 200*200 AS 值

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

-- 学生考试成绩 +1 分 查看
SELECT `studentno`,`studentresult`+1 AS '+1分后的成绩' FROM result

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

4.3 where条件子句

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

搜索的条件由一个或者多个表达式组成（结果是布尔值）：

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or ||	a or b a||b	逻辑或，其中一个为真，则结果为真
Not ！	not a !a	逻辑非，真为假，假为真

尽量使用英文字母

-- 查询学号是1000，成绩在70分以上的课程编号
SELECT `subjectno` FROM result WHERE `studentresult`>70 AND `studentno`=1000

-- 查询学号是1000，成绩在70分以上的课程编号
SELECT `subjectno` FROM result WHERE `studentresult`>70 AND `studentno`=1000

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

-- 查询除了学号为1000 的学生信息
SELECT * FROM student WHERE `studentno`!=1000

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a IS NULL	如果a为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… 当中的某个值中，结果为真

-- 查询电话号码不为空的学生学号
SELECT `studentno` FROM `student` WHERE `phone` IS NOT NULL

-- 查询没有登录密码的学生学号 loginpwd(varchar)
SELECT `studentno` FROM `student` WHERE `loginpwd` = '' OR `loginpwd` IS NULL

-- 查询学号在1000-1002 之间的学生姓名
SELECT `studentname` FROM `student` WHERE `studentno` BETWEEN 1000 AND 1002

-- 查询姓张的同学学号和姓名
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张%'

-- 查询名字第二个字是 伟 的同学学号及姓名
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '_伟%'

-- 查询地址在 广东深圳和河南开封 的学生学号
SELECT `studentno` FROM `student` WHERE `address` IN ('广东深圳','河南开封')

4.4 联表查询

/*
思路：
1.分析需求，分析查询的字段来自哪些表（连接查询）
2.确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 例如： student.studentno = result.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

-- inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno

操作	描述
INNER JOIN	如果表中至少有一个匹配，就返回行
LEFT JOIN	会从左表中返回所有的值，即使右表中没有匹配
RIGHT JOIN	会从右表中返回所有的值，即使左表中没有匹配

-- 查询学号，学生姓名，科目名称，成绩 (三张表)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

自连接

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

-- 建表
CREATE TABLE `school`.`category`( `categoryid` INT(3) NOT NULL COMMENT 'id', 
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
 PRIMARY KEY (`categoryid`) 
 ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES (2, 1, '信息技术');
INSERT INTO `school`.`CATEGOrY&