Mysql 学习笔记

1.初识MySQL
JAVAEE 企业级java开发
前端（页面，展示，数据）
后台（连接点：连接数据库JDBC 连接前端 （控制，视图跳转，和给前端传递数据））
数据库（存数据，txt，excel，word）

1.1为什么要学习数据库？
1.岗位需求
2.现在的时代是大数据时代 得数据者得天下
3.被迫需求 存数据 去IOE
4.数据库是所有软件中最核心的存在 DBA

1.2 什么是数据库
数据库（DB，Database）
概念：数据仓库 软件 安装在操作系统（window linux mac ,…）之上的 SQl 可以存储大量的数据 500万！
作用：存数据 管理数据 Eccel

1.3 数据库分类
关系型数据库 ： 行 列 （SQL)
MySQL Oracle Sql server DB2 SQLlite
通过表与表之间 行与列之间的关系进行数据的存储 学员信息表 考勤表，。。。

非关系型数据库： key value (Nosql)
Redis MongoDB
对象存储 通过对象的自身属性来决定

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

1.4 Mysql简介
Mysql是关系型数据库
前世：瑞典 Mysql公司
今生：Oracle公司
Mysql是最好的DBMS软件之一
开源的数据库软件
体积小 速度快 总体拥有的成本比较低 招人成本比较低 所有人必须会
中小型网站 大型网站 集群

安装建议
1。尽量不要用exe 注册表
2. 尽可能使用压缩包安装

1.5安装Mysql

1.6 建立一个数据库
基字符集 utf8
数据库排序规则 utf8_general_ci
每一个sqlyog的执行操作 本质就是对应了一个 可以在软件的历史纪录中查看

1.查看表
右键点击
2.尝试添加多条记录

1.7连接数据库
命令行连接
mysql -uroot -p123456 --连接数据库
–修改用户密码
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘123456’
–刷新权限
flush privileges

所有的语句都使用分号结尾
show databases —查看所有的数据库

mysql> use school
Database changed --切换数据库 use 数据库名

show table; --查看所有的表

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

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

exit; – 退出连接；

– 单行注释 sql本来的注释
/*
sql的多行注释
*/

数据库xxx语言 CRUD 增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制

2.操作数据库
操作数据库 》操作数据库中的表 》操作数据库中的数据
mysql关键字不区分大小写
–操作代码中没有 【可选】 {必选}
2.1操作数据库 （了解）

1.添加数据库
CREATE DATABASE [IF NOT EXISTS] zhaochen;

2.删除数据库
drop database [IF EXISTS]zhaochen;
3.使用数据库
–如果你的表名或者你的字段名是个特殊字符 需要``
USE school;
USE User;

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

学习思路：
1.对比SQLyog 的可视化操作
2.固定语法的关键字必须得强行记住！

2.2数据库的列类型
数值
tinyint 很小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节 常用的
big 较大的整数 8个字节
float 单精度浮点数 4个字节
double 双精度浮点数 8个字节 （精度问题）
decimal 字符串形式浮点数 金融计算时候 会经常使用decimal

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

时间日期
java.until.date
date YYYY-MM-DD 日期格式
time hh:mm:ss 时间格式
datetime YYYY-MM-DD hh:mm:ss 最常用的时间
timeStamp 时间戳 1970.01.01到现在的毫秒数 也比较常用
year 年份表示

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

2.3数据库的字段属性（重点）
Unsigned
无符号的整数
声明了该列不能为负数
zerofill
零填充的
不足的位数利用零来填充 int（3） 5----005

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

非空 null not null
假设设置为not null 如果不给它赋值 就会报错
null 如果不填写值 默认就是null

默认：
设置默认的值！
sex 默认值是男 如果不给他设定该列的值 则会有默认

利用sql创建()表的名称和字段 尽量使用 括起来 -- 目标创建一个school数据库 CREATE DATABASE IF NOT EXISTS `school`; USE school; -- 创建学生表（列，字段） 使用sql建立 -- 学号int 登陆密码varchar(20) 姓名 性别 varchar（2） 出生日期（datetime）家庭住址，email -- 注意点：使用英文() 表的名称和字段尽量使用括起来
– AUTO_INCREMENT 自增
– 字符串使用’'括起来
– comment 注解
– default 默认值
– 所有的语句加, （英文的） 最后一个字段不用加
– primary key 主键 一般一个表只有一个唯一的主键

CREATE TABLE IF NOT EXISTS `Studens`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` 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 EXISTIS】`表名`(
`字段名` 列的类型【属性】【索引】 【注释】,
`字段名` 列的类型【属性】【索引】 【注释】,
····
`字段名` 列的类型【属性】【索引】 【注释】
) 【表类型】【字符集设置】【注释】

常用命令

SHOW CREATE DATABASE school; -- 查看创建数据库的语句
SHOW CREATE TABLE studens; -- 查看创建表的语句
DESCRIBE studens; -- 查看具体表的结构

2.5数据表的类型
– 关于数据库的引擎
/*
INNODB 默认使用
MYISAN 早些年使用
*/
MYISAN: INNODB:
事务支持： 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大 约为MYISAN的两倍

常规使用操作
Nyisan: 节约空间 速度较快
INNODB: 安全性高 事物的处理 ，支持多表多用户的操作

在物理空间存在的位置
所有的数据库文件都存在于data目录下 一个文件夹就对应着一个数据库

本质还是文件的存储
MySQL引擎在物理文件上的区别
innoDB在数据库表中只有一个*.frm文件 以及上级目录的ibdata1文件
MYISAN对应的文件：
·.frm --表结构的定义文件
·.myd --数据文件（data）
·*.myi --索引文件

设置数据库表的字符集编码：
CHARSET=utf8

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

不建议去使用
Mysql的默认字符集是Latin1 不支持中文
在my.ini中配置默认的编码
character-set-server = utf-8

2.6修改和删除表
修改

ALTER
 -- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE studens RENAME AS students;
-- 增加表的字段 ALTER TABLE 表名 ADD 列的字段名 列属性;
ALTER TABLE students ADD age INT(12);
-- 修改表的字段 重命名 修改约束
ALTER TABLE 表名 MODIFY 字段名 列属性; 
ALTER TABLE students MODIFY age VARCHAR(11); -- 修改约束
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性；
ALTER TABLE students CHANGE age age1 INT(1); -- 重命名属性

删除
– 删除表中的字段

ALTER TABLE 表名 DROP 字段名;
ALTER TABLE students DROP age1; 

– 删除表

DROP TABLE 【IF EXISTS】 表名;
DROP TABLE IF EXISTS Student;

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

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

3.MYSQL 数据管理

3.1 外键（了解即可）
方式一 ： 在创建表时增加约束 （麻烦 比较复杂）

CREATE TABLE IF NOT EXISTS `Studens`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` 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 IF NOT EXISTS `Students`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` 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 Students 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’。。。）,（‘值1’，‘值2’，‘值3’。。。）,（‘值1’，‘值2’，‘值3’。。。）,…
插入多个字段

INSERT INTO `grade` (`gradename`)VALUE ('大二'),('大三'),('大四')

1. 字段与字段之间 使用英文逗号隔开
2. 字段是可以省略的 但是最后面的值必须要一一对应 不能少
3. 可以同时插入多条数据 value的值 需要使用逗号隔开即可value（），（），（）

3.4 修改 update

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

– 修改学员的名字

UPDATE `students` SET `name`= '赵宸' WHERE id=1;

– 不执行条件的情况下 会改动所有的数据

UPDATE `students` SET `name`= '赵宸'; 

– 修改多个属性，逗号隔开

UPDATE `students` SET `name` = '赵宸',email = '2652362127@qq.com' WHERE id = 1;

语法

update 表名 set 列名1 = 数值,列名2 = 数值，。。。。 where 条件;

条件 where 子句运算符 id等于某个值,id大于某个值,在某个区间进行修改

操作符 含义 范围 结果
= 等于 5=6 false
<>或者!- 不等于 5!=6 true
> 大于 5>6 false
< 小于 5<6 true
<= 小于等于 5<=6 true
>= 大于等于 5>=6 false
between…and… 闭合区间 在某个区间 between 5 and 6 5 6
and && 1>2 and 5<6 false
or || 1>2 or 5<6 true

and 和 or 无上限

UPDATE `students` SET `name` = '赵宸2' WHERE  id BETWEEN  1 AND 4
UPDATE `students` SET `name` = '赵宸2' WHERE `name`= '赵宸' AND `password` = 1234 
UPDATE `students` SET `name` = '赵宸2' WHERE `id` = 1 or `id` =3
 

注意：
列名必须是数据库的列名 尽量带上``
条件 where 筛选的条件 如果没有制定 就会修改所有的列
value 是一个具体的值 也可以是一个变量
多个设置的属性 使用英文逗号隔开

3.5 删除 delete
语法 delete from 表名 where 条件
– 避免这样删除 (会全部删除)
delete from students
删除指定数据
delete from students where id = 1

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

清空一张表
truncate students

delete 和 truncate的 区别
相同点：都能删除表的数据 都不会删除表的结构
不同点: truncate 会重新设计自增列 计数器回归零
truncate 不会影响事物

了解即可 delete 删除的问题 重启数据库 现象
innoDB 自增列会从1开始 （存在内存当中的，断电即失）
MYISAN 继续从上一个自增量开始（存在文件中的，不会丢失）

DQL查询数据（最重要）

4.1DQL```

DATA QUERY LANGUAGE : 数据查询语言
所有的查询操作都用它 Select
简单的查询 复杂的查询 它都能做
数据库最核心的语言 ，最重要的
使用频率最高的语言

4.2 查询指定字段
select语法：
顺序不能颠倒
select
from table
left \ right \ inner join – 联合查询
where – 查询结果需要满足的条件
group by – 指定结果依据那个字段来分组
having – 过滤分组的记录必须满足的次要条件
order by – 指定查询记录按一个或多个条件排序
limit(查询起始下标，规定的页面大小) – 指定查询的记录从哪条到哪条

– 查询全部的学生

SELECT * FROM Student

SELECT * FROM result

– 查询指定字段

SELECT `studentno`,`studentname` FROM `student`

– 别名 给结果起个名字 as 可以给字段起别名 也可以给表起别名
SELECT studentno AS 学生学号,studentname AS 学生姓名 FROM student

– 函数 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 3*3-1 AS 计算结果 

– 查询自增的步长（变量）

SELECT @@auto_increment_increment

– 查询所有的学生成绩 并且将每个学生的成绩加一分
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 分之间的
WHERE `studentresult` >=95 && `studentresult`<=100

SELECT `studentno`,`studentresult` FROM result
-- 查询考试成绩在95 - 100 分之间的
WHERE `studentresult` >=95 AND `studentresult`<=100

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

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

模糊查询 ： 比较运算符
运算符 语法 描述
is null a is null 如果操作符为null 则结果为真
is not null a is not null 如果操作符结果为not null 结果为真
between and 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… 其中的某一个 则结果为真

– 模糊查询
– 查询姓刘的同学
– like结合 %（代表0到另一个字符） _(一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE ‘刘%’

– 查询姓刘的同学 后面只有一个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE ‘刘_’

– 查询姓刘的同学 后面只有2个字的
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 (1000,1001,1002)
– 查询在北京的学生
SELECT studentno,studentname FROM student
WHERE studentno IN (‘北京’)

– =not null null=
– 查询地址为空的学生 -----
SELECT studentno,studentname,address FROM student
WHERE address= ‘’ OR address IS NULL

– 查询有出生日期的同学
SELECT studentno,studentname,borndate FROM student
WHERE borndate IS NOT NULL
– 查询没有出生日期的同学
SELECT studentno,studentname,borndate FROM student
WHERE borndate IS NULL

4.4 联表查询
join 对比
– 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,s.studentName,subjectNo,studentResult
FROM student s
RIGHT JOIN result r
ON s.studentNO - r.studentNO

– left join
SELECT s.studentNO,s.studentName,subjectNo,studentResult
FROM student s
LEFT JOIN result r
ON s.studentNO - r.studentNO

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

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

/*
思路：
1.分析需求 分析查询的字段来自哪些表 （连接查询）
2.确定使用哪种连接查询？ 7种

确定交叉点 （这两个表中 那个数据的相同的）
判断的条件：学生表中的studentno 等于 成绩表中的studentno
*/
– 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,s.studentName,subjectNo,studentResult
FROM student s
RIGHT JOIN result r
ON s.studentNO - r.studentNO

– 查看缺考的同学
SELECT s.studentNO,s.studentName,subjectNo,studentResult
FROM student s
LEFT JOIN result r
ON s.studentNO - r.studentNO
WHERE Studentresult IS NULL

– join in 连接查询
– where 等值查询

– 思考题（查询了参加考试的同学信息:学号，学生姓名，科目名，分数）

/*
思路：
1.分析需求 分析查询的字段来自哪些表 student result subject （连接查询）
2.确定使用哪种连接查询？ 7种
确定交叉点 （这两个表中 那个数据的相同的）
判断的条件：学生表中的studentno 等于 成绩表中的studentno
*/

SELECT s.studentno, s.studentname , sub.subjectname, r.studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON s.studentno=r.subjectno

– 我要查询哪些数据 select…
– 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
– 假设存在一种多张表查询，慢慢来 先查询两张表然后在慢慢添加

– from a left join b 以左边的表为基准查找
– from a right join b 以右边的表为基准查找

自链接：
父类：
自己的表和自己的表连接 核心 ： 一张表拆为两张一样的表即可
catagoryid catagoryName
2 信息技术
3 软件开发
5 美术设计

子类
pid catagoryid catagoryName
3 4 数据库
2 8 办公信息
3 6 web开发
5 7 ps技术

操作： 查询父类对应的子类关系
父类 子类
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 ps技术

– 查询父子信息: 把一张表看成两张一模一样的表

SELECT a.`categoryname` AS '父栏目' , b.`categoryname` AS '子栏目'
FROM `categoryid` AS a, `categoryid` AS b
WHERE a.`categoryname` = b.`pid`

4.5 分页和排序

排序

– =====分页limit 与排序 order by=

– 排序： 升序ASC 降序 DESC
– 查询的结构 根据成绩降序 排序
SELECT subjectno,studentresult
FROM result
ORDER BY studentresult ASC

SELECT subjectno,studentresult
FROM result
ORDER BY studentresult DESC

– =====分页limit 与排序 order by=

– 排序： 升序ASC 降序 DESC
– 查询的结构 根据成绩降序 排序
SELECT subjectno,studentresult
FROM result
ORDER BY studentresult ASC

– 为什么要分页
– 缓解数据库压力 瀑布流

– 分页 每页只显示五条数据
– 语法 : limit 起始值,页面的大小
– 网页应用: 当前，总的页数，页面的大小
SELECT subjectno,studentresult
FROM result
ORDER BY studentresult ASC
LIMIT 0,4 – 1-4
LIMIT 1,4 – 2-5

– 第一页 limit 0,5
– 第二页 limit 5,5
– 第三页 limit 10,5
– 第n页 limit （n-1)*p,p
– [p:页面大小，（n-1)*p起始值，n 当前页]
– [数据总数/页面大小 = 总页数]

语法： limit(查询起始下标，规定的页面大小)

4.6 子查询
where(这个值是计算出来的)
本质： 在where语句中嵌套一个子查询语句
where(select * from)
– =where 子查询和嵌套查询========
– 1.查询高等数学的所有考试结果（学号，科目编号，成绩），降序排列
– 方式一：使用连接查询
SELECT r.studentno,subjectname,studentresult
FROM result r
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE subjectname LIKE ‘高等数学%’
ORDER BY studentresult DESC

– 方式二：使用子查询（）
SELECT studentno,sub.subjectname,studentresult
FROM result r
WHERE subjectname = (
SELECT subjectname FROM subject WHERE subjectname LIKE ‘高等数学%’
)
ORDER BY studentresult DESC
– 由里到外
– 查询所有高等数学的学生学号
SELECT subjectname FROM subject WHERE subjectname LIKE ‘高等数学%’

– 分数不小于80分的学生的学号和姓名
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE studentresult >= 80

– 在这个基础上增加一个科目，高等数学-2
– 查询高等数学-2的编号
SELECT 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’)

SELECT subjectno FROM subject WHERE subjectname = ‘高等数学-2’

– 再改造
SELECT s.studentno,studentname
FROM student s
WHERE studentno IN(SELECT studentno FROM result WHERE studentresult>=80 AND subjectno = (SELECT subjectno FROM subject WHERE subjectname LIKE ‘高等数学%’))

4.7 分组和过滤
– 查询不同组的平均分
– 核心：分组的概念
SELECT subjectname, AVG(studentresult), MIN(studentresult),MAX(studentresult),AVG(studentresult)
FROM result r
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno – 通过什么字段来分组
HAVING AVG(studentresult) >= 80 – 过滤条件
5. mysql 函数
– =常用函数======
5.1 常用函数
– 数学运算
– =常用函数======
5.1 常用函数
– 数学运算
SELECT ABS() – 绝对值
SELECT CEILING() – 向上取整
SELECT FLOOR() – 向下取整
SELECT RAND() – 随机数
SELECT SIGN() – 判断一个数的符号 正数返回1 负数返回-1

– 字符串函数
SELECT CHAR_LENGTH(‘即使再小的帆，也能远航’) – 返回一个字符串的长度
SELECT CONCAT(‘我’,‘爱’,‘你’) – 拼接字符串
SELECT INSERT(‘我爱编程’,1,1,‘你’) – 从某个位置开始替换某个长度
SELECT LOWER(‘ZhaoChen’) – 转小写
SELECT UPPER(‘ZhaoChen’) – 转大写
SELECT INSTR(‘zhaochen’,‘z’) – 返回字符串第一次出现的位置
SELECT REPLACE(‘zhaochen’,‘ao’,‘ou’)-- 替换指定的字符串为自己的字符串
SELECT SUBSTR(‘zhaochen’,4,2) – 返回指定的字符串 （原字符串，截取的位置，截取的长度）
SELECT REVERSE(‘zhaochen’) – 反转字符串

查询姓周的同学 改为粥
SELECT REPLACE(studentname,‘周’,‘粥’) FROM student
WHERE studentnameLIKE ‘周%’

– 时间和日期函数 (记住)
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聚合函数
函数名称 描述
count() 计数
sum() 求和
AVG() 平均值
max() 最大值
min() 最小值
。。。 。。。
– =聚合函数========
– 都能够统计表中的数据（想查询一个表中有多少记录就使用它）
SELECT COUNT(studentname) FROM student – count(字段） 会忽略所有的null值
SELECT COUNT() FROM student – count() 不会忽略null值 本质计算行数
SELECT COUNT(1) FROM student – count(1) 不会忽略所有的 null值 本质计算行数

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

10 JDBC （重点）
10.1 数据库驱动
驱动： 声卡 显卡 数据库

   应用程序
      |
	  jdbc         开发人员

| |
mysql驱动 oracle 驱动 数据库厂商
| |
数据库 数据库

我们的数据会通过数据库驱动和数据库打交道
10.2 jdbc
sun公司为了简化开发人员的（对数据库的统一管理）操作 提供了一个规范（java操作数据库的规范） 简称jdbc
这些规范的实现 由具体的厂商去做
对于开发人员来说 我们只需要掌握jdbc接口的操作

java.sql
javax.sql
还需要导入一个数据库驱动包
10.3 第一个jdbc程序
创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

1.创建一个普通项目
2.导入jar包 加入库中
3. 编写测试代码

package com.zhaochen;

import java.sql.*;

// 我的第一个JDBC程序
public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
       Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法


        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF8&&useSSL=true";
        String username = "root";
        String password = "123456";
        //3.用户信息,数据对象 (connection代表数据库)
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.执行sql的对象 statement 执行sql的对象
        Statement statement = connection.createStatement();
        //5.执行sql的对象 去执行  Sql ，可能存在结果  ，查看返回结果
        String sql = "SELECT * FROM `users`";
        ResultSet resultSet = statement.executeQuery(sql); //返回的结果值 结果集中值内封装了我们全部的查询出来的结果
        while(resultSet.next()) {
            System.out.println("id = " + resultSet.getObject("id"));
            System.out.println("name = " + resultSet.getObject("NAME"));
            System.out.println("password = " + resultSet.getObject("PASSWORD"));
            System.out.println("email = " + resultSet.getObject("email"));
            System.out.println("birthday = " + resultSet.getObject("birthday"));
        } 

        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：
1.加载驱动
2.连接数据库 driverManger
3.获取执行sql的对象
statement
4.执行sql语句 获得返回的结果值
返回值为 resultSet 类型
5. 释放连接

DriverManger

      // DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
   Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法
// connection 代表数据库
// 数据库设置自动提交
// 事物提交
// 事物回滚
    connection.commit();
    connection.rollback();
    connection.setAutoCommit();

url

// mysql 默认3306  设置端口号 默认3306
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF8&&useSSL=true";

    String url = "协议://主机地址:端口号/数据库名？参数1&参数2&参数3
    
   
statement 执行sql的对象 prepareStatement执行sql的对象


jdbc中的statementd对象用于向数据库发送sql语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可

statement对象的executeupdate方法，用于向数据库发送增 删 改 查的sql语句，executeupdate 语句执行完后，会返回一个整数（即增删改查导致了数据库几行数据发生了变化）
statement executeQuery方法 用于向数据库发送查询语句 executeQuery方法返回代表查询结果的resultset对象

string url = "" ； //编写sql
       statement.executeQuery(); //查询执行 返回statement
        statement.execute();// 执行任何sql
        statement.executeUpdate(); // 更新 插入 删除都是用这个 返回一个受到影响的行数
		
		
resultset 查询的结果值 封装了所有的查询结果
获得指定的数据类型
       ResultSet resultSet = statement.executeQuery(sql); //返回的结果值 结果集中值内封装了我们全部的查询出来的结果
         //想查询那种类型的数据 就是用get哪种类型的数据
        resultSet.getObject(); //在不知道数据类型的情况下使用
        resultSet.getInt(); // 获取int类型
        resultSet.getBoolean();// 获取boolean类型
        resultSet.getFloat();// 获取float类型
        resultSet.getDouble();// 获取double类型
        resultSet.getDate(); // 获取data类型
		
遍历 指针	
        // 遍历 指针
        resultSet.beforeFirst(); //移动到最前面
        resultSet.afterLast(); // 移动到最后面
        resultSet.next()； //移动到下一个数据
		resultSet.previous(); //移动到前一行
		  resultSet.absolute(row) // 移动到指定行
		  
		  
		  释放资源必须做
		  //关闭连接
	    resultSet.close();
        statement.close();
        connection.close();

代码实现
1.提取工具类

 package com.useSql;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class useSql {
    useSql(){}
    private static String drive = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
static {
    try{
        InputStream inputStream = useSql.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
        drive = properties.getProperty("driver");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
//1.驱动只用加载一次
        Class.forName(drive);

    } catch (Exception e) {
        e.printStackTrace();
    }

}

//获取链接
   public static Connection getConnection() throws SQLException {
       Connection connection = DriverManager.getConnection(url, username, password);
       return  connection;
   }
//释放连接资源
    public static void release(Connection con, Statement st, ResultSet re){
     if(re!=null){
         try {
             re.close();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
         }
     }
    if(st!=null){
        try {
            st.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
    if(con != null){
        try {
            con.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    }

}

2.编写增删改的方法
增

package com.zhaochen;

import com.useSql.useSql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class testIInsert {
    public static void main(String[] args) {
        Connection  con = null;
        Statement sta = null;
        ResultSet re = null;
        try {
             con = useSql.getConnection();//获取数据库连接
            sta = con.createStatement(); //获得sql的使用对象
            String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUE (4,'zhaoliu','123456','zhaoliu@163.com','1979-12-05')";
            int i = sta.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            useSql.release(con,sta,re);
        }
    }
}

删

package com.zhaochen;

import com.useSql.useSql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class testDelete {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = useSql.getConnection();
            statement = connection.createStatement();
            String sql = "DELETE FROM `users` WHERE `id` = 4 ";
            int i =statement.executeUpdate(sql);
            if(i >= 1){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();

        }
        finally{
            useSql.release(connection,statement,resultSet);
        }
    }
}

改

package com.zhaochen;

import com.useSql.useSql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class testUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = useSql.getConnection();
            statement = connection.createStatement();
            String sql = "UPDATE `users` SET `NAME` = 'zhaochen' WHERE id = 1 ";
            int i =statement.executeUpdate(sql);
            if(i >= 1){
                System.out.println("修改成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();

        }
        finally{
            useSql.release(connection,statement,resultSet);
        }
    }
}

3.查询

package com.zhaochen;

import com.useSql.useSql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class testSelect {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet re  = null;
        try {
            con = useSql.getConnection();
            st =  con.createStatement();
            String sql = "SELECT * FROM `users` where id = 1";
             re       = st.executeQuery(sql);//查询完毕 会返回一个结果值
            while(re.next()){
                System.out.println("id = "+ re.getInt("id"));
                System.out.println("name = "+ re.getString("NAME") );
                System.out.println("password= "+ re.getString("PASSWORD") );
                System.out.println("email = "+ re.getString("email"));
                System.out.println("birthday = "+ re.getString("birthday"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            useSql.release(con,st,re);
        }
    }
}

sql的注入问题

sql存在漏洞 会被攻击 导致数据泄露

sql会被拼接

package com.zhaochen;

import com.useSql.useSql;

import java.sql.*;

public class sql注入 {
    public static void main(String[] args) {
        sql注入.login("zhaochen","123456");
    }
    public static void login(String username,String password) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = useSql.getConnection();
            statement = connection.createStatement();
            String sql = "SELECT * FROM `users` WHERE `NAME` = `"+username+"` AND `PASSWORD` = `"+password+"`";
             resultSet = statement.executeQuery(sql);
           while (resultSet.next()){
               System.out.println("name = " + resultSet.getString("NAME"));
               System.out.println("password = "+ resultSet.getString("PASSWORD"));
           }


        } catch (SQLException throwables) {
            throwables.printStackTrace();

        }
        finally{
            useSql.release(connection,statement,resultSet);
        }
    }
}

10.5 PreparedStatement对象
PreparedStatement 可以防止sql注入 并且效率更高
1.新增

 package com.zhaochen1;

import com.useSql.useSql;
import java.util.Date;
import java.sql.*;

public class testInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 区别 使用问号占位符 代替参数
            String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUE (?,?,?,?,?) ";
            connection = useSql.getConnection();
            //区别
            preparedStatement = connection.prepareStatement(sql); //预编译sql 先写sql 然后不执行
             //手动给参数赋值
            preparedStatement.setInt(1,4);//（参数下标，要赋值给的值）
            preparedStatement.setString(2,"zhaoliu");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"1111");
            //注意点： sql.Date 数据库用的
            //         util.Date java用的  new Date().getTime() 获得时间戳
            preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));
            // 执行
            int i = preparedStatement.executeUpdate();
            if(i >= 0 ){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            useSql.release(connection,preparedStatement,resultSet);
        }
    }
}

2.删除

package com.zhaochen1;

import com.useSql.useSql;
import java.util.Date;
import java.sql.*;

public class testDelete {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 区别 使用问号占位符 代替参数
            String sql = " delete from `users` where id = ?";
            connection = useSql.getConnection();
            //区别
            preparedStatement = connection.prepareStatement(sql); //预编译sql 先写sql 然后不执行
            //手动给参数赋值
            preparedStatement.setInt(1,4);//（参数下标，要赋值给的值）
            //注意点： sql.Date 数据库用的
            //         util.Date java用的  new Date().getTime() 获得时间戳
            // 执行
            int i = preparedStatement.executeUpdate();
            if(i > 0 ){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            useSql.release(connection,preparedStatement,resultSet);
        }
    }
}

3.更新

package com.zhaochen1;

import com.useSql.useSql;
import java.util.Date;
import java.sql.*;

public class testUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 区别 使用问号占位符 代替参数
            String sql = "UPDATE `users` SET `NAME` = ? WHERE id = ?";
            connection = useSql.getConnection();
            //区别
            preparedStatement = connection.prepareStatement(sql); //预编译sql 先写sql 然后不执行
            //手动给参数赋值
            preparedStatement.setString(1,"zhaochen111");//（参数下标，要赋值给的值）
            preparedStatement.setInt(2,3);
            //注意点： sql.Date 数据库用的
            //         util.Date java用的  new Date().getTime() 获得时间戳
            // 执行
            int i = preparedStatement.executeUpdate();
            if(i > 0 ){
                System.out.println("修改成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            useSql.release(connection,preparedStatement,resultSet);
        }
    }
}

4.查询

package com.zhaochen1;

import com.useSql.useSql;
import java.util.Date;
import java.sql.*;

public class testSelect {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 区别 使用问号占位符 代替参数
            String sql = " SELECT * FROM `users`";
            connection = useSql.getConnection();
            //区别
            preparedStatement = connection.prepareStatement(sql); //预编译sql 先写sql 然后不执行
            //手动给参数赋值

            //注意点： sql.Date 数据库用的
            //         util.Date java用的  new Date().getTime() 获得时间戳
            // 执行
             resultSet = preparedStatement.executeQuery();
             while(resultSet.next()){
                 System.out.println("id ="+ resultSet.getInt("id"));
                 System.out.println("Name ="+resultSet.getString("NAME"));
                 System.out.println("password = " +resultSet.getString("PASSWORD"));
                 System.out.println("email = " +resultSet.getString("email"));
                 System.out.println("data = " +resultSet.getDate("birthday"));
             }        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            useSql.release(connection,preparedStatement,resultSet);
        }
    }
}

5.防止sql注入
prepareStatement防止sql注入的本质是 把传递进来的参数当作字符
假设 其中存在转义字符 就直接把它忽略掉
引号直接被转义

10.7 使用idea连接数据库

配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=UTF8&&useSSL=true
username=root
password=123456