小小数据库小结（上）

小小梁同学

已于 2024-11-30 18:42:06 修改

阅读量1.1k

点赞数 23

文章标签：数据库

于 2024-11-22 11:07:12 首次发布

本文链接：https://blog.youkuaiyun.com/awmlgs/article/details/143965944

版权

1.初识MySQl

javaEE ：企业级java开发 Web

前端：（页面展示数据）

后端：（连接点：连接数据库JDBC,链接前端（控制，控制视图跳转和给前端传递数据）)

数据库：（存数据，Txt , Excel,word）

只会写代码学好数据库，基本可以混饭吃

操作系统数据结构与算法当一个不错的程序员

离散数学数字电路体系结构编译原理 + 实战经验高级程序员

1.1为什么学习数据库

岗位需求
现在的世界，大数据时代
被迫需求：存数据
数据库是所有软件体系中最核心的存在，DBA

1.2 什么是数据库

数据库（DB DataBase）

概念：数据仓库，软件，安装在操作系统（window linux，mac）之上 Sql 可以存储大量的数据

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

1.3 数据库分类

关系型数据库

MySql , Oracle , Sql Server ,DB2, SQllite
通过表和表之间, 行和列之间的关系进行数据的存储, 学员信息表考勤表 …

非关系型数据库(NoSQL ) Not Only

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

DBMS(数据库管理系统)

数据库的管理软件，科学有效地管理我们的数据。维护和获取数据
MYSQL,数据库管理系统

1.4 MySQL简介

MySQL是一个关系型数据库管理系统

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

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL作为网站数据库。

1.5 安装MySQL

小小MySQL安装与配置-优快云博客

1.6 安装sqlyog

SQLyog安装配置（注册码）连接MySQL_sqlyog注册码-优快云博客

1.7 连接数据库

MySQL 数据库常用命令大全（完整版）_mysql数据库命令大全-优快云博客

命令行连接！

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

--所有的语句都使用;结尾
show databases;  --查看所有的数据库

use school --切换数据库
Database changed

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

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

creat database westos;--创建一个数据库

exit:--退出连接
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345';

--单行注释
/*
多行注释
*/

数据库xxx语言 CRUD 增删改查 CV程序员 APL程序员 CRUD程序员（业务）

DDL 定义语言

DML 操作语言

DQL 查询语言

DCL 控制语言

2.操作数据库

操作数据库 > 操作数据库里面的表 > 操作数据库里面表的数据

2.1 操作数据库

创建数据库
```
CREATE DATABASE [IF NOT EXISTS] westos
```
删除数据库
```
DROP DATABASE IF EXISTS westos
```

使用数据库

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

查看数据库

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

对比sqlyog

在这里插入图片描述

学习思路
- 对照sqlyog可视化历史记录查看
- 国定的语法或关键字要强行记住

2.2 数据库的列类型

数值

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

字符串

char 字符串固定大小 0~255
varchar 可变字符串 0~65535 常用的 String
tinytext 微型文本 2^8-1
text 文本串 2^16-1 保存大文本

时间日期

java.until.Data(java)

data yyyy-mm-dd 日期
time hh: mm: ss 时间格式
datatime yyyy-mm-dd hh: mm: ss 最常用的时间格式
timestamp 时间戳 1978.2.2到现在的毫秒数 也较为常用
year 年份

null

没有值，未知
注意，不要使用NULL计算，结果为NULL

2.3 数据库的字段属性

Unsigned:

无符号的整数
该列不能声名为负数

zerofill:

0填充的
不足的位数，使用0来填充 int(3) 5 – 005

自增

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

非空 NUll not null

假设设置为not null 如果不给他赋值，就会报错
NULL 如果不填写值，默认就是null!

默认

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

拓展：
/*
每一个表，都必须存在以下5个字段，未来做项目用的，表示一个记录存在的意义

id 主键
`version`  乐观键
is_delete  伪删除
gmt_create 创建时间
gmt_update 修改时间

*/

2.4 创建数据库表

-- 创建学生表(列，字段) 使用sql创建
-- 学号int 登录密码varchar(20) 姓名，性别varchar(2),出生日期(datatime),家庭住址，email

-- 注意点，使用英文().表的名称和字段尽量使用``括起来
-- NULL AUTO_INCREMENT 自增
-- 字符串使用，单引号括起来
-- 所有的语句后面加 , （英文的）最后一个不用加 
-- DEFAULT 默认的
-- PRIMARY KEY 主键，一般一个表只有一个
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 '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' 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=utf16

格式

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

常用命令

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构

2.5 数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/

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

常规使用操作：

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

在物理空建存在的位置

所有的数据库文件都存在data目录下,一个文件夹对应一个数据库

本质还是文件的存储！

MySQL引擎在物理文件上的区别

INNODB 在数据库里面只有一个*.frm 文件，以及上级目录下的ibdata1 文件
MYISAM 对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）

设置数据库的字符集编码

CHARSET=utf8
-- CHARSET=utf16

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

mysql的默认字符编码的设置（my.ini设置字符编码） - 简书

MySQL的默认编码是Latin1

在my.ini 中配置默认的编码

character-set-server=utf8

2.6 修改和删除表

修改

-- 修改表
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME teacher1

-- 增加字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(10)

-- 修改表的字段 (重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 列属性(11)
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性(11)
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher1 CHANGE age agel INT(11) -- 字段重命名
-- change用来字段重名，不能修改字段类型和约束
-- modify不能用来字段重命名，只能修改字段类型和约束

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP agel

删除

-- 删除表(如果表存在在删除)
DROP TABLE IF EXISTS teacher1

所有的创建和删除尽量加上判断，以免保错

注意点：

`` 字段名，使用这个包裹！
注释 – /**/
sql关键字大小写不敏感，建议小写
所有的符号全部用英文

3.MySQl数据管理

3.1 外键（了解就好）

方式1，在创建表的时候，增加约束，（麻烦，比较复杂）

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

-- 学生表的gradeid字段，要去引用年级表的gradeid
-- 定义外键key
-- 给这个表添加约束 （执行引用）

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 '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	`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=utf16

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

方式2：创建表成功后，添加外键约束

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


-- 学生表的gradeid字段，要去引用年级表的gradeid
-- 定义外键key
-- 给这个表添加约束 （执行引用）
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 '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf16

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`)

-- ALTER TABLE 表 
-- ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表 (那个字段)

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

最佳实践

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

3.2 DML语言（全部记住）

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

DML语言：数据操作语言

insert (添加)
update (修改)
delete(删除)

3.3 添加

insert

-- 插入语句（添加）
-- insert into 表名 ([字段1,字段2,字段3])values('值1'),('值2')...
INSERT INTO `grade` (`gradename`) VALUES ('大四')

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

-- 一般写插入语句，我们一定要数据和字段一一对应

-- 插入多个字段
INSERT INTO `grade` (`gradename`) 
VALUES ('大一'),('大二')

INSERT INTO `student` (`name`) VALUES ('张三')

INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES ('张三','aaa','男')
INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES ('李是','aas','男')

INSERT INTO `student` (`name`,`pwd`,`sex`) 
VALUES ('李是','aas','男'),('张三','aaa','男')

语法:insert into 表名 ([字段1,字段2,字段3])values(‘值1’),(‘值2’)…

注意事项：

字段和字段之间使用英文逗号
字段是可以省略的，但是后面的值必须一一对应，不能少
可以同时插入多条数据，VALUES后的值，需要使用，隔开即可 VALUES(),()…

3.4 修改

update 修改谁（条件） set 原来的值 = 新值

-- 修改学员的名字,带了条件
UPDATE `student` SET `name`='光胜' WHERE id = 1

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

-- 修改多个属性,逗号隔开
UPDATE `student` SET `name` = '光胜',`email`='1066096478@qq.com' WHERE id = 1

-- BETWEEN...AND...
UPDATE `student` SET `name`='光明' WHERE id BETWEEN 2 AND 5

-- 通过多个条件定位数据，无上限
UPDATE `student` SET `name`='光胜' WHERE `name`='光明' AND `sex`='男'

-- 语法:
-- UPDATE 表名 set colnum_name = value,[colnum_name = value,.....] where [条件]

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

操作符	含义	范围	结果
=	等于	5=6	false
<> !=	不等于	5<>6	true
>	大于	5>6	false
<	小于	5<6	true
>=	大于等于	4>=8	false
<=	小于等于	4<=8	true
BETWEEN…AND…	在某个范围内	[2,5]
AND	我和你 &&	5>1 and 1>2	false
OR	我或你 \|\|	5>1 or 1>2	true

语法：UPDATE 表名 set colnum_name = value,[colnum_name = value,…] where [条件]

注意：

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

UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='光明' AND `sex`='男'

3.5 删除

delete 命令

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

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

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

TRUNCATE 命令

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

-- 清空student表
TRUNCATE `student`

delete 和 TRUNCATE 的区别

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

-- 测试delete 和 TRUNCATE
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 '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' 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=utf16

INSERT INTO `student` (`name`) VALUES ('张三')

INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES ('张三','aaa','男')
INSERT INTO `student` (`name`,`pwd`,`sex`) VALUES ('李是','aas','男')


DELETE FROM `student` -- 不会影响自增

TRUNCATE TABLE `student` -- 自增会归零

了解就可以：delete删除的问题，重启数据库，现象

InnoDB 自增列会重新开始（存在内存当中的，断电即失）
MyISAM 继续从上一个自增量开始（存在文档中的，不会丢失）

4.DQL查询数据（重点）

4.1 DQL

(Data Query language : 数据查询语言)

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

4.2 指定查询字段

-- 测试数据
/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 5.7.33 : Database - school
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE = ''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;
/*!40101 SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES = @@SQL_NOTES, SQL_NOTES = 0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS */`school` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `school`;

/*Table structure for table `grade` */

DROP TABLE IF EXISTS `grade`;

CREATE TABLE `grade`
(
    `GradeID`   INT(11)     NOT NULL AUTO_INCREMENT COMMENT '年级编号',
    `GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`GradeID`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8;

/*Data for the table `grade` */

INSERT INTO `grade`(`GradeID`, `GradeName`)
VALUES (1, '大一'),
       (2, '大二'),
       (3, '大三'),
       (4, '大四'),
       (5, '预科班');

/*Table structure for table `result` */

DROP TABLE IF EXISTS `result`;

CREATE TABLE `result`
(
    `StudentNo`     INT(4)   NOT NULL COMMENT '学号',
    `SubjectNo`     INT(4)   NOT NULL COMMENT '课程编号',
    `ExamDate`      DATETIME NOT NULL COMMENT '考试日期',
    `StudentResult` INT(4)   NOT NULL COMMENT '考试成绩',
    KEY `SubjectNo` (`SubjectNo`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8;

/*Data for the table `result` */

`result``student``result`

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student`
(
    `StudentNo`    INT(4)       NOT NULL COMMENT '学号',
    `LoginPwd`     VARCHAR(20) DEFAULT NULL,
    `StudentName`  VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `Sex`          TINYINT(1)  DEFAULT NULL COMMENT '性别，0或1',
    `GradeId`      INT(11)     DEFAULT NULL COMMENT '年级编号',
    `Phone`        VARCHAR(50)  NOT NULL COMMENT '联系电话，允许为空',
    `Address`      VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `BornDate`     DATETIME    DEFAULT NULL COMMENT '出生时间',
    `Email`        VARCHAR(50)  NOT NULL COMMENT '邮箱账号允许为空',
    `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`StudentNo`),
    UNIQUE KEY `IdentityCard` (`IdentityCard`),
    KEY `Email` (`Email`)
) ENGINE = MYISAM
  DEFAULT CHARSET = utf8;

/*Data for the table `student` */

INSERT INTO `student`(`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`,
                      `IdentityCard`)
VALUES (1000, '123456', '张伟', 0, 2, '13800001234', '北京朝阳', '1980-01-01 00:00:00', 'text123@qq.com',
        '123456198001011234'),
       (1001, '123456', '赵强', 1, 3, '13800002222', '广东深圳', '1990-01-01 00:00:00', 'text111@qq.com',
        '123456199001011233');

/*Table structure for table `subject` */

DROP TABLE IF EXISTS `subject`;

CREATE TABLE `subject`
(
    `SubjectNo`   INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `ClassHour`   INT(4)      DEFAULT NULL COMMENT '学时',
    `GradeID`     INT(4)      DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`SubjectNo`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8;

/*Data for the table `subject` */

INSERT INTO `subject`(`SubjectNo`, `SubjectName`, `ClassHour`, `GradeID`)
VALUES (1, '高等数学-1', 110, 1),
       (2, '高等数学-2', 110, 2),
       (3, '高等数学-3', 100, 3),
       (4, '高等数学-4', 130, 4),
       (5, 'C语言-1', 110, 1),
       (6, 'C语言-2', 110, 2),
       (7, 'C语言-3', 100, 3),
       (8, 'C语言-4', 130, 4),
       (9, 'Java程序设计-1', 110, 1),
       (10, 'Java程序设计-2', 110, 2),
       (11, 'Java程序设计-3', 100, 3),
       (12, 'Java程序设计-4', 130, 4),
       (13, '数据库结构-1', 110, 1),
       (14, '数据库结构-2', 110, 2),
       (15, '数据库结构-3', 100, 3),
       (16, '数据库结构-4', 130, 4),
       (17, 'C#基础', 130, 1);

/*!40101 SET SQL_MODE = @OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES = @OLD_SQL_NOTES */;

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

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

-- 别名，给结果起一个名字 AS  可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生名字 FROM student AS s
 
-- 函数  Concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

语法：SELECT 字段,... FROM 表

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

去重distinct

作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询一下那些同学参加了考试，成绩

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

-- 查询有那些同学参加了考试
SELECT `StudentNo` FROM result 

-- 发现重复数据，去重
SELECT DISTINCT `StudentNo` FROM result

数据库的列

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

-- 用来计算（表达示）
SELECT 100*3-1 

-- 查询自增的步长（变量）
SELECT @@auto_increment_increment 
 
 -- 学员考试成绩 +1分 查看
 SELECT `StudentNo`,`StudentResult`+1 AS '加分后' FROM  result

数据库中的表达示：文本字段，列，NULL，函数，计算表达示，系统变量

select 表达示 from 表

4.3. where 条件字段

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

搜索的条件由一个或者多个表达式组成，结果为布尔值

逻辑运算符

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

尽量使用英文字母

--   ======= where =======
 SELECT `StudentNo`,`StudentResult`FROM result

-- 查询考试成绩在95~100分之间   and
c

-- 查询考试成绩在95~100分之间   &&
 SELECT `StudentNo`,`StudentResult`FROM result
 WHERE `StudentResult`>=95 && `StudentResult`<=100

-- 模糊查询（区间）
 SELECT `StudentNo`,`StudentResult`FROM result
 WHERE `StudentResult` BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩 !=
 SELECT `StudentNo`,`StudentResult`FROM result
 WHERE `StudentNo` != 1000

-- 除了1000号学生之外的同学的成绩 not 
 SELECT `StudentNo`,`StudentResult`FROM result
 WHERE NOT `StudentNo`=1000

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL,结果为真
IS NOT NULL	as is not null	如果操作符为 not null ,结果为真
BETWEEN	a between b and c	若a在b和c之间，则结果为真
Like	a like b	sql匹配，如果匹配b,则结果为真
IN	a in (a1,a2,a3…)	假设a在a1,或者a2…其中的某一个值中，结果为真

-- ======模糊查询=====
-- 查询姓刘的同学
-- 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 '刘__'
-- 中间带光字的
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 ('北京');
-- null not null
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` OR address IS NULL
-- 查询有出生日期的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL
-- 查询没有出生日期的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL

4.4 联表查询

JOIN

7种方法

/*思路
1.分析需求，分析查询的字段来自那张表
2.确定使用那种连接查询 ？ 7种
确定交叉点（这两个表中的那个数据是相同的）
判断的条件：学生表中的studentNo == 成绩表中的studentNo
*/
-- join on 连接查询
-- where   等值查询
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.StudentNo = r.studentNo

-- Right Join 
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.StudentNo = r.studentNo

-- Left Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.StudentNo = r.studentNo

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

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

/*思路
1.分析需求，分析查询的字段来自那张表
2.确定使用那种连接查询 ？ 7种
确定交叉点（这两个表中的那个数据是相同的）
判断的条件：学生表中的studentNo == 成绩表中的studentNo
*/
-- join（连接的表） on(判断的条件) 连接查询
-- where   等值查询
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.StudentNo = r.studentNo

-- Right Join 
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.StudentNo = r.studentNo

-- Left Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.StudentNo = r.studentNo

-- -- 查询缺考的同学
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.StudentNo = r.studentNo
WHERE StudentResult IS NULL 

-- 思考题(查询了参加考试的同学：学号、学生姓名、科目名和分数)
/*思路
1.分析需求，分析查询的字段来自那张表 student result subject 
2.确定使用那种连接查询 ？ 7种
确定交叉点（这两个表中的那个数据是相同的）
判断的条件：学生表中的studentNo == 成绩表中的studentNo
*/
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
RIGHT JOIN result r
ON r.StudentNo=s.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo

-- 我要查询哪些数据
-- 从哪几个表中 FROM 表 XXX Join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两种表然后再慢慢增加

-- From a (基准) left join b
-- From a left join b（基准）
    
-- ===== 联表查询练习 =====  
-- 查询学员所属年级(学号、姓名、年级名称)
SELECT studentNo,studentName,`GradeName`
FROM student s
INNER JOIN `grade` g
ON s.`GradeID` = g.`GradeID`
-- 查询科目所属的年级（科目名称、年级名称）
SELECT subjectName,gradeName
FROM `subject` s
INNER JOIN `grade` g
ON s.`GradeID` = g.`GradeID`
-- 查询了参加 C语言-1 考试的同学：学号 ，学生姓名，科目名，分数
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 `SubjectName` = 'C语言-1'

自连接

CREATE TABLE `category` (
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
INSERT INTO `category`(`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- ======== 自连接 ==========
-- 查询父子信息
SELECT a.`categoryName` AS '父',b.`categoryName` AS '子'
FROM `category` AS a,`category` AS b       
WHERE a.`categoryid`=b.`pid`

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

父类

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

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

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

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

4.5 排序和分页

排序

-- ====== 分页 limit 和排序  order by ===========
-- 排序 ：升序 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 `SubjectName` = 'C语言-1'
ORDER BY `StudentResult` DESC

分页

-- 分页
-- 为什么要分页
-- 缓解数据库压力   现在是瀑布流
-- 分页，每页只显示五条数据
-- 语法limit当前页，起始值 ，页面大小
-- 网页应用 ： 当前，总的页数，页面的大小
-- LIMIT 0,5 1-5
-- LIMIT 1,5 2-6
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 `SubjectName` = 'C语言-1'
ORDER BY `StudentResult` DESC
LIMIT 1,5

-- 第一页 LIMIT 0,5      （1-1）*5
-- 第二页 LIMIT 5,5      （2-1）*5
-- 第三页 LIMIT 10,5      （3-1）*5
-- 第N页 LIMIT 10,5      （n-1）*pagesize
--  pagesize 页面大小  
--  n-1）*pagesize 起始值 
--  n 当前页
--  数据总数/页面大小 = 总页数

语法： limit(查询起始值下标，pagesize)

-- 思考
-- 查询 C语言-1 课程成绩排名前10的学生，并且分数要大于80的信息（学号 姓名 课程名称 分数）
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 `SubjectName` = 'C语言-1' AND `StudentResult`>=80
ORDER BY `StudentResult` DESC
LIMIT 0,10

4.6 子查询

where (这个值是计算出来的)

本质：*在where语句中嵌套一个子查询语句*

--  ======= where =====
-- `SubjectNo`查询高等数学-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

-- -- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult >= 80

-- 在这个基础上增加一个科目，高等数学-2
-- 查询 高等数学-2 的编号
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult >= 80 
AND `SubjectNo`=(
   SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = '高等数学-2'
)


 -- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
SELECT s.StudentNo,StudentName
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '高等数学-2' AND StudentResult >= 80

-- 再改造(由里即外)
SELECT StudentNo,StudentName FROM `student` WHERE `StudentNo`IN (
   SELECT `StudentNo` FROM `result` WHERE `StudentResult` > 80 AND `SubjectNo`=(
      SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = '高等数学-2'
   )
)

4.7 分组和过滤

-- 查询不同课程的平均分、最高分和最低分
-- 核心： 根据不同课程进行分组
SELECT sub.SubjectName,   
       AVG(r.StudentResult) AS AverageResult,   
       MAX(r.StudentResult) AS MaxResult,   
       MIN(r.StudentResult) AS MinResult   
FROM result AS r   
INNER JOIN `subject` AS sub 
ON r.SubjectNo = sub.SubjectNo
GROUP BY sub.SubjectName  -- 通过什么来分组
HAVING AverageResult > 80

4.8 select 小结

顺序很重要
select 去重 要查询的字段 from 表 (注意：表和字段可以取别名)
xxx join 要连接的表 on 等值判断
where (具体的值 子查询语句)
Group By (通过那个字段来分组)
Having (过滤分组后的信息，条件和where是一样的，位置不同)
Order By (通过那个字段排序)[升序，降序]
limit startIndex, pagesize

业务层面
查询：跨表，跨数据库、、、、

5.MYSQL函数

官网：MySQL :: MySQL 8.4 Reference Manual :: 14 Functions and Operators

5.1 常用函数

-- ============== 常用函数 ==============
-- 数学运算
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND()  -- 返回一个0-1的随机数
SELECT SIGN(-2)  -- 判断一个数的符号
-- 字符串函数
SELECT CHAR_LENGTH('哈哈') -- 字符串长度
SELECT CONCAT('我','爱','你嘿嘿')  -- 拼接字符串
SELECT INSERT ('我爱编程',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('LiangGunaSheng') -- 小写字母
SELECT UPPER('LiangGunaSheng') -- 大写字母
SELECT INSTR('kuangshen','h') -- 返回第一次出现的索引
SELECT REPLACE('坚持就能成功','坚持','成功') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功坚持就能成功',4,6) -- 返回指定的子字符串
SELECT REVERSE('坚持就能成功') -- 反转字符串中的字符
-- 查询姓张的同学,换成李
SELECT REPLACE(studentname,'张','李') FROM student
WHERE studentname LIKE '张%'
-- 时间和日期函数（记住）
SELECT CURRENT_DATE() -- 查询当前日期
SELECT CURDATE()  -- 查询当前日期
SELECT NOW() -- 查询当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW()) -- 返回年
SELECT MONTH(NOW()) -- 返回月
SELECT DAY(NOW()) -- 返回日
SELECT HOUR(NOW()) -- 返回时
SELECT MIN(NOW()) -- 返回秒
-- 系统用户
SELECT SYSTEM_USER() -- 系统用户
SELECT USER() -- 系统用户
SELECT VERSION() -- 版本

5.2 聚合函数

函数名	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
…	…

-- ====== 聚合函数 ====
-- 都能统计表中的数据（想查询一个表中有多少个记录 就使用这个count）
SELECT COUNT(studentname) FROM student; -- 查询字段，会忽略所有null值 Count (字段)
SELECT COUNT(*) FROM student; -- 不会忽略null 相当于所有列 计算行数 Count (*)
SELECT COUNT(1) FROM student; -- 不会忽略null 相当于忽略所有列计算行数 Count (1)

SELECT SUM(`StudentResult`) AS 总和 FROM result 
SELECT AVG(`StudentResult`) AS 平均分 FROM result 
SELECT MAX(`StudentResult`) AS 最高分 FROM result 
SELECT MIN(`StudentResult`) AS 最低分 FROM result

5.3 数据库级别的MD5加密（扩展）

什么是MD5?

主要是增强算法复杂度和不可逆性 MD5不可逆，具体的值是MD5是一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

-- ====== 测试  MD5加密 =========
CREATE TABLE `testmd5`(
   `id` INT(4) NOT NULL,
   `name` VARCHAR(20) NOT NULL,
   `pwd` VARCHAR(50) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密id==1的密码
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id==1
-- 加密全部的密码
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456')) 
-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='小明' AND pwd=MD5('123456')