Day19

1、初识MySQL

javaEE:企业级java开发 Web

前端(页面:展示,数据!)

后台(连接点:连接数据库JDBC,连接前端(控制,控制视图跳转,和给前端传递数据))

数据库(存数据)

操作系统 + 数据结构与算法

离散数学、数字电路、体系结构、编译原理 + 实战经验

1.1、为什么学习数据库

  1. 岗位需求
  2. 现在的世界,大数据时代~,得数据库者得天下
  3. 被迫需求:存数据
  4. 数据库是所有软件体系中最核心的存在 DBA

1.2、什么是数据库

数据库(DB,DataBase)

概念:数据仓库,软件,安装在操作系统(Windows,Linux,mac)之上! SQL,可以存储大量的数据。500万

作用:存储数据,管理数据

1.3、数据库分类

关系型数据库:(SQL)

  • MySQL,Oracle,Sql Server,DB2,SQLlite
  • 通过表和表之间,行和列之间的关系进行数据的存储, 学员信息表

非关系型数据库:(NotSQL) not only

  • Redis,MongDB
  • 对象存储,通过对象自身的属性来决定

DBMS(数据库管理系统)

  • 数据库的管理软件,科学有效的管理数据。维护和获取数据
  • MySQL,数据库管理系统!

1.4、MySQL简介

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

前世:瑞典MySQL AB公司

今生:属于Oracle旗下产品

MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。

开源是数据库软件~

体积小、速度快、总体拥有成本低,招人成本比较低,所有人必须会~

中小型、大型网站,集群!

官网:https://www.mysql.com/

1.5、安装MySQL

  1. 解压

  2. 把这个包放到自己的电脑环境目录下

  3. 添加环境变量

    1. 我的电脑—>属性—>高级—>环境变量

    2. 选择PATH,在其后面添加:你的mysql安装文件下面的bin文件夹

    3. 在F:\Environment\java\mysql-5.7.41下创间my.ini文件

    4. 编辑my.ini文件,注意替换路径位置

      [mysqld]
      # 目录一定要换成自己的
      basedir = F:\Environment\java\mysql-5.7.41\
      datadir = F:\Environment\java\mysql-5.7.41\data\
      port = 3306
      skip-grant-tables
      
    5. 启动管理员模式下的CMD,并将路径切换至mysql下的bin目录,然后输入mysqld -install

    6. 在输入mysqld --initialize-insecure --user=mysql 初始化数据文件

    7. 然后再次启动mysql然后用命令mysql -u root -p进入mysql管理界面(密码可为空)

    8. 进入界面后更改root密码

      update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
      

      最后输入flush privileges;刷新权限

    9. 修改my.ini文件注掉最后一句skip-grant-tables

    10. 重启mysql即可正常使用

    net stop mysql

    net start mysql

    1. 连接上测试出现以下结构就安装好了

      sc delete mysql 清空服务

1.6、安装SQLyog

  1. 无脑安装

  2. 注册

  3. 打开连接数据库

  4. 新建一个数据库school

    每一个sqlyog的执行操作,本质就是对于了一个sql,可以在历史记录中查看

  5. 新建一张表student

    字段:id,name,age
    
  6. 查看表

  7. 添加多条记录,注意保存

1.7、连接数据库

命令行连接!

--连接数据库
mysql -u root -p123456  
--修改用户密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; 
--刷新权限
flush privileges;

-----------------------------------------
--所有的语句都使用;结尾
show databases; -- 查看所有的数据库
use school;  --切换数据库  use 数据库名
show tables;  --查看数据库中所有的表
descirbe student;  --显示数据库中所有表的信息
create database westos;  --创建一个数据库

exit;  --退出连接

-- 单行注释  (SQL 本来的注释)
/*
(SQL 多行注释)
*/

**数据库 xxx 语言 ** CRUD 增删改查!

DDL 数据库 定义 语言

DML 操作管理

DQL 查询

DCL 控制

2、操作数据库

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

mysql关键字不区分大小写

2.1、操作数据库(了解)

  1. 创建数据库

    CREATE DATABASE [IF NOT EXISTS] westos;  
    
  2. 删除数据库

    DROP DATABASE [IF EXISTS] westos
    
  3. 使用数据库

    -- tab 键上边,如果表明或者字段名是一个特殊的字符,就需要带``
    USE `school`
    SELECT `user` FROM student  -- 查询
    
  4. 查看数据库

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

对比:SQLyog的可视化操作

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

2.2、数据库的列类型

数值

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

字符串

  • char 字符串固定大小的 0~255
  • varchar 可变字符串 0~65535 常用的变量 String
  • 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(默认)
  • 通常用来设置唯一的主键~index,必须是整数类型
  • 可以自定义设计主键的起始值和步长

非空:null、not null

  • 假设设置为not null,如果不给它赋值,就会报错!
  • null,如果不填写值,默认为null

默认:

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

拓展:(了解)

/* 每一个表都必须存在以下五个字段!未来做项目用的,表示一个记录存在的意义
id 	     主键
`version`    乐观锁
is_delete    伪删除
gmt_create   创建时间
gmt_update   修改时间
*/

2.4、创建数据库表(重点)

-- 目标:创建一个school数据库
-- 创建学生表(列、字段) 使用sql创建
-- 学号int 登陆密码varchar(20)姓名,性别varchar(2),出身如期(datetime),家庭住址,emil

-- 注意点,使用英文(),表的名称 和 字段 尽量使用 ``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加,(英文的)最后一个字段不用加
-- 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=utf8

格式:

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

常用命令:

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

2.5、数据表的类型

-- 关于数据库引擎
/*
INNODB  默认使用~
MyISAM  早些年使用的
*/
MyISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间的大小较小较大,约为2倍

常规的使用操作:

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

在物理空间存在的位置

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

本质还是文件的存储!

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

  • InnoDB 在数据库表中只有一个 *.frm文件,以及上级目录下的 ibdata1文件
  • MyISAM 对应的文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件(data)
    • *.MYI 索引文件(index)

设置数据库表的字符集编码

CHARSET=utf8

不设置的话,会是MySQL默认的字符集编码~ (不支持中文!)

MySQL默认的编码是Latin1,不支持中文。

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

character-set-server=utf8

2.6、修改和删除表

修改

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

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

-- 修改表的字段: (重命名、修改约束)

-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11)    -- 修改约束

-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1)    -- 字段重命名
/*
change用来字段重命名,不能修改字段类型和约束
modify不用来字段重命名,只能修改字段类型和约束
*/

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

删除

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

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

注意点:

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

3、MySQL数据管理

3.1、外键(了解)

方式一:创建表的时候,增加约束(比较复杂)

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) 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 '密码',
  `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=utf8

删除有外键关系的表的时候,必须先删除引用别人表的表(从表),再删除被引用的表(主表)

方式二:创建表成功后,添加外键约束

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) 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 '密码',
  `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=utf8

-- 创建表的时候没有外键关系
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`),(`值3`......)
INSERT INTO `grade`(`gradename`)VALUES('大四')

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

-- 一般写插入语句,一定要数据和字段一一对应!
-- 插入多个字段
INSERT INTO `grade`(`gradename`)
VALUES('大二'),('大一')

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

INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('张三','aaaaaa','男')

INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('李四','aaaaaa','男'),('王五','cccccc','男')

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

注意事项:

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

3.4、修改

update 修改谁 (条件) set 原来的值 = 新值

-- 修改学员的名字,带了条件
UPDATE `student` SET `name` = '小白' WHERE id=1;  -- 改指定的

-- 不指定条件的情况下,会改动所有的表
UPDATE `student` SET `name` = '小白' ;

-- 修改多个属性,逗号隔开
UPDATE `student` SET `name` = '小强', `email` = '214135135@qq.com' WHERE id = 1;

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

-- 通过多个条件定位数据,无上限!  &&  ||
UPDATE `student` SET `name` = '小强' WHERE `name` = '小白' AND `sex` = '女';

条件:where子句 运算符 id等于某个值,大于某个值,在某个区间内…

操作符会返回布尔值

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

语法:update 表名 set colnum_name = value,[colnum_name = value,…] where [条件]

注意:

  • colnum_name 是数据库的列,尽量带上``

  • 条件,筛选的条件,如果没有指定,则会修改所有的列

  • value,是一个具体的值,也可以是一个变量

    • UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '小白' AND `sex` = '男';
      
  • 多个设置的属性之间,使用英文逗号隔开 trim

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 `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test` (`coll`)VALUES('1'),('2'),('3')

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

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

了解即可:delete删除的问题,重启数据库,现象

  • innodb 只增列会从1开始(存在内存当中,断电即失)
  • myisam 继续从上一个自增量开始(存在文件中,不会丢失)

4、DQL查询数据(最重点)

4.1、DQL

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

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

4.2、指定查询字段

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

-- 查询指定字段
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 AS 计算结果  -- 用来计算  (计算表达式)
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 分之间的
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` >= 95 AND `studentresult` <= 100

-- and  &&
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` != 1001;

-- !=  not
SELECT `studentno`,`studentresult` FROM result
WHERE NOT `studentno` = 1001;

模糊查询:比较运算符

运算符语法描述
IS NULLa is null如果操作符为null,结果为真
IS NOT NULLa is not null如果操作符为not null,结果为真
BETWEENa between b and c若a在b和c之间,则结果为真
Likea like b如果a能够匹配到b,则结果为真
Ina in(a1,a2,a3…)假设a在a1,或者a2…其中的某一个值中,则结果为真
-- =========================== 模糊查询 ============================
-- ========== like ===========
-- 查询姓张的同学
-- 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 对比

-- =========================== 联表查询 join ============================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student
SELECT * FROM result
/*
  思路
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种查询? 七种
确定交叉点(两个表中哪个字段是相同的)
判断的条件:学生表中的studentno = 成绩表中的studentno
*/
-- Join (连接的表) On(判断条件)连接查询
-- where   等值查询

-- INNER JOIN
SELECT s.studentno,s.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 s
RIGHT JOIN result r
ON s.studentno = r.studentno

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

-- 思考题(查询了参加考试的同学的信息:学号,学生姓名,科目名称,分数)
/*
  思路
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种查询? 七种
确定交叉点(两个表中哪个字段是相同的)
判断的条件:学生表中的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

-- 我要查询哪些数据 select...
-- 从那几个表中查 FROM 表 XXX Join 连接的表 on 交叉条件
-- 假设存在多张表查询,先查询两张表,然后再叠加

-- from a left join b
-- form a right join b

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

自连接(了解)

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

父类

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

子类

pidcategoryidcategoryname
34数据可以
28办公信息
36web开发
57ps技术

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

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps技术
-- =========================== 自连接 ============================
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','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');

-- 查询父子信息:把一张表看为两个一模一样的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

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 ASC

语法:ORDER BY 排序依据字段 ASC

分页

-- 100万条数据能否显示
-- 为什么要分页?
-- 缓解数据库压力,给人的体验更好,(图片:瀑布流)

-- 分页,每页只显示五条数据
-- 语法:limit 起始值,页面的大小
-- 网页应用: 当前页,总的页数,页面大小
-- LIMIT 0,5  1~5 
-- LIMIT 1,5  2~6
-- LIMIT 2,5  3~7
-- ....
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 ASC
LIMIT 2,5

-- 第一页 limit 0,5		(1-1)*5
-- 第二页 limit 5,5		(2-1)*5
-- 第三页 limit 10,5		(3-1)*5
-- 第N页  limit 5*(N-1),5	(n-1)*pageSize,pageSize
-- [pageSize:页面大小]   
-- [(n-1)*pageSize:起始值]
-- [n:当前页]
-- [数据总数/页面大小 = 总页数]

语法:limit(查询起始下标,pageSize)

4.6、子查询

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

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

where(select * from)

-- =========================== 子查询 where ============================
-- 1、查询 c语言-1 的所有考试结果(学号,科目序号,成绩),降序排列
-- 方式一:使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = 'c语言-1'
ORDER BY studentresult DESC

-- 方式二:使用子查询(由里及外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result` 
WHERE subjectno = (
	SELECT subjectno FROM `subject` 
	WHERE subjectname = 'c语言-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
-- 在这个基础上增加科目,c语言-1  
-- 查询 c语言-1 的编号
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 = 'c语言-1'
)

-- 查询课程为 c语言-1 且分数不小于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 = 'c语言-1' 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 = 'c语言-1'
	)
)

-- 练习:查询 c语言-1 前5名同学的成绩信息(学号,姓名,分数)
-- 使用子查询
SELECT s.studentno,studentname,studentresult 
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE r.subjectno IN (
	SELECT subjectno FROM `subject`
	WHERE subjectname = 'c语言-1'
)
ORDER BY studentresult DESC
LIMIT 0,5

5、MySQL函数

官网:MySQL :: MySQL 8.0 参考手册 :: 12.1 内置函数和运算符参考

5.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('小白')			-- 字符串长度
SELECT CONCAT('小白','小强')			-- 字符串拼接
SELECT INSERT('小白爱编程helloworld',1,2,'超级')-- 插入,替换(从某个位置开始替换某个长度)
SELECT LOWER('XiaoBai')				-- 大写转小写
SELECT UPPER('xiaobai')				-- 小写转大写
SELECT INSTR('xiaobai','x')			-- 第一次出现的位置
SELECT REPLACE('小白顶住','小白','小强')	-- 替换指定字符
SELECT SUBSTR('小白顶住',2,1)			-- 截取指定位置字符(源字符串,截取的位置,截取的长度)
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 MINUTE(NOW())	
SELECT SECOND(NOW())	

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.2、聚合函数(常用)

待补全

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值