标签：-- 数据库 sql 查询 student MySQL SELECT

初识MySQL

JavaEE:企业级Java开发 Web

前端（页面：展示，数据！）

后台（连接点：连接数据库JDBC，连接前端（控制，控制视图跳转，和前端传输数据传递））

数据库（存数据，Txt,Excel,Word）

只会写代码，学好数据库，基本混饭吃；操作系统，数据结构与算法！当一个不错的程序员。离散数学，数字电路，体系结构，编译原理。+实战经验，高级程序员--优秀的程序员。

1.1为什么学习数据库

1. 岗位需求

2. 现在的世界，大数据时代，得数据者得天下

3. 被迫需求：存数据 去IOE

4. 数据库是所有软件体积中最核心的存在 DBA

1.2什么是数据库

数据库（DB,DataBase）

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

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

1.3数据库分类

关系型数据库：(SQL)

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

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

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

DBMS（数据库管理系统）

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

1.4MySQL简洁

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

前世：瑞典MySQL AB公司。

今生：属于Oracle旗下产品。

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

开软的数据库软件。

中小型网站，或者大型网站，集群。

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

安装建议：

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

1.5安装MySQL

1.6安装Navicat

创建数据库

创建一张表

添加数据

1.7连接数据库

命令行连接

mysql -uroot -p123456  --连接数据库
flush privileges --刷新权限
所有的语句都使用分号结尾
show databases; --查看所有的数据库
mysql> use school --切换数据库使用use命令
Database changed
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表信息
create database westos;--创建一个数据库
exit;--退出连接
--单行注释（SQL）
/* (sql的多行注释)  */

数据库xxx语言

DDL：数据库定义语言

DML：数据库操作语言

DQL：数据库查询语言

DCL：数据库控制语言

操作数据库

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

mysql的关键字不区分大小写

2.1操作数据库(了解)

1. 创建数据库

   CREATE DATABASE IF NOT EXISTS westos
   

2. 删除数据库

   DROP DATABASE IF EXISTS hello
   

3. 使用数据库

--tab键上面的Piao ，如果你的名字或者字段名时一个特殊的字符，就需要带``
USE `school`

4. 查看数据库

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

   学习思路：

   • 对照
   • 固定的语法或关键字必须要强行记住

2.2数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• int 标准的存储 4个字节
• mediumint
• bigint 较大的数据 8个字节
• float 单精度浮点数 4个字节
• double 双精度浮点型 4个字节（精度问题）
• decimal 字符串形式的浮点数 金融计算的时候一般使用

字符串

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

时间日期

java.util.Date

• data 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来填充
• int(3) 5---005

自增：

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

非空：Null not null

• 假设设置为not null ,如果不给它赋值，就会报错！

• Null，如果不填写值，默认就是null

默认：

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

拓展：

每一个表都必须存在以下五个字段，表示一个记录存在的意义：

/*id   主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_updata 修改时间
*/

2.4创建数据库表

--目标：创建一个school数据库
--创建学生（列，字段）使用SQL创建
学号int登录密码varchart(20)姓名，性别varchart(2),出生日期(datatime),家庭住址，email
--注意点，使用英文(),表的名称和字段尽量使用``括起来
--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` DATABASE 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 --查看创建数据库的语句
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

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

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

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

character-set-server=utf8

2.6修改和删除表

修改

ALTER

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

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

--修改表的字段  （重命名，修改约束）
--ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE teacher1 MODIFY age VACHER(11)--修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(11)--字段重命名

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

删除

--删除表
DROP TABLE IF EXISTS teacher1

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

注意点：

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

MySQL数据管理

3.1外键（了解）

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

学生（id,name,grade）

年级

学生表的gradeid字段，要去引用年级表的gradeid

定义外键Key

给这个外键添加约束（执行引用） references引用

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

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

--创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREING KEY(`gradeid`) REFERENCES `grade` (``grageid``);
--ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREING KEY(作为外键的列)  REFERENCES 哪个表 （哪个字段）

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

最佳实践

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

3.2DML语言（全部记住）

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

DML语言：数据操作语言

• Insert
• update
• delete

3.3添加 Insert

--插入语句（添加）
INSERT INTO 表名（[字段名1，字段2，字段3]）values('值1')，('值2')，('值3'...)
INSERT INTO `grade`(`gradename`)VALUES(`大四`)
--由于主键自增，我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO `grade`VALUES(`大三`)
--一般写插入语句，我们一定要数据和字段一一对应
--插入多个字段
INSERT INTO `grade`(`gradename`)VALUES(`大二`),(`大一`)

语法：

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`='长江7号';
--语法
--UPDATE 表名 SET colnum_name = value,[colnum_name = value,...] where [条件]
--修改多个属性
UPDATE `student` SET `name` ='狂神',`email`='[email protected]` WHERE id=1;

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

操作符会返回布尔值

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

--通过多个条件定位数据
UPDATE `student` SET `name`='长江7号' WHERE `name`='狂神44' AND sex='女'

语法：

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

注意：

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

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

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

• 多个设置的属性之间，使用英文逗号隔开

  UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='狂神44' 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 `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继续从上一个自增量开始（存在文件中的，不会丢失）

DQL查询数据（最重点）

4.1DQL

（Data Query LANGUAGE:数据查询语言）

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

SELECT完整语法：

SELECT[ALL|DISTINCT]
{*|table.*|[table.field[as alias1][,table.field[as alias2]][......]]}
FROM table_name[as table_alias]
[left|right|inner join table_name2]--联合查询
[WHERE...] --指定结果需要满足的条件
[GROUP BY...]--指定结果按照哪几个字段来分组
[HAVING]--过滤分组的记录必须满足的次要条件
[ORDER BY...]-- 指定查询记录安一个或多个条件排序
[LIMIT{[offset,]row_count|row_countOFFSET offser}];--指定查询的记录从哪条至哪条
注意：[]括号代表可选的，{}括号代表必选的

4.2指定查询字段

--查询全部的学生     SELECT 字段 FROM 表
SELECT *FROM student
--查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
--别名，给结果起一个名字 AS   可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentNae` 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.3where条件字句

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

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

逻辑运算符

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

尽量使用英文字符

SELECT studentNo,`StudentResult` FROM result
--查询考试成绩在95-100之间
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 ANDStudentResult<=100
--模糊查询（区间）
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
--除了1000号学生之外的学生成绩
SELECT studentNo,`StudentResult` FROM result
WHERE studentNo!=1000;
--！=  not
SELECT studentNo,`StudentResult` FROM result
WHERE NOT 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)	假设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 对比

七种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

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

--查询缺考的同学
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 AS s
RIGHT JOIN result AS r
ON r.studentNo=s.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo=sub.subjectNo
--我要查询哪些数据 select...
--从哪几个表中查 FROM 表 XXX JOIN 连接的表 ON 交叉条件
--假设存在一种多张表查询，慢慢类，先查询两张表然后再慢慢增加
--FROM a left join b
--FROM a right join b

自连接

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

父类

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

子类

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

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

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

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

--练习：查询学员所属的年级（学号，学生姓名，年级名称）
SELECT studentNo,studentName,`GradeName`
FROM student s
INNER JOIN `grade` AS g
ON s.`GradeID`=g.`GradeID`
--查询科目所属的年级（科目名称，年级名称）
SELECT `SubjectName`,`GradeName`
FROM `subject` AS sub
INNER JOIN `grade` AS g
ON sub.`GradeID`=g.`GradeID`

--思考题：查询参加了数据库结构-1考试学生的信息：学号，学生姓名，科目名称，分数
SELECT `studentNo`,`studentName`,`SubjectName`,`StudentResult`
FROM student
INNER JOIN `result` AS s
ON s.StudentNo=r.StudentNo
INNER JOIN `subject` AS sub
ON r.`studentNo`=sub.`studentNo`
WHERE subjectName='数据库结构'

4.5分页和排序

分页 LIMIT

--为什么要分页？环节数据库压力，给人的体验更好 瀑布流
--分页，每页只显示5条数据
--语法：limit 当前页，页面的大小
--网页应用：当前，总的页数
--当前页的计算公式  第N页 (n-1)*pageSize, 
--pageSize:页面的大小
--(n-1)*pageSize：起始值
--n:当前页
--数据总数/页面大小=总页数
--LIMIT 0,5 1~5
--LIMIT 1,5 2~6
SELECT `studentNo`,`studentName`,`SubjectName`,`StudentResult`
FROM student
INNER JOIN `result` AS s
ON s.StudentNo=r.StudentNo
INNER JOIN `subject` AS sub
ON r.`studentNo`=sub.`studentNo`
WHERE subjectName='数据库结构'
ORDER BY StudentResult ASC
LIMIT 6,5

语法：

limit(查询起始下标，pageSize)

--练习：查询JAVA第一学年课程成绩排名前十的学生并且分数大于80分的学生信息（学号，姓名，课程名称，分数）
SELECT s.`studentNo`,`studentName`,`SubjectName`,`StudentResult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.studentNo=r.studentNo
INNER JOIN `subject` AS sub
ON sub.`studentNo`=r.`studentNo`
WHERE SubjectName='JAVA第一学年' AND StudentResult>=80
ORDER BY  StudentResult DESC
LIMIT 0,10

排序 ORDER BY

升序ASC

降序DESC

--查询的结果根据成绩降序排序
--ORDER BY 通过哪个字段排序，怎么排
SELECT `studentNo`,`studentName`,`SubjectName`,`StudentResult`
FROM student
INNER JOIN `result` AS s
ON s.StudentNo=r.StudentNo
INNER JOIN `subject` AS sub
ON r.`studentNo`=sub.`studentNo`
WHERE subjectName='数据库结构'
ORDER BY StudentResult ASC

4.6子查询

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

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

--1.查询数据库结构-1的所有考试结果（学号，科目编号，成绩）降序排列
--方式一：使用连接查询
SELECT `studentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` AS r
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo
WHERE SubjectName='数据库结构-1'
ORDER BY StudentResult DESC
--方式二：使用子查询（由里及外）
SELECT `studentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` 
WHERE SubjectNo=(SELECT SubjectNo FROM `subject` 
                 WHERE SubjectName='数据库结构-1'
                )ORDER BY StudentResult DESC
--查询数据库结构-1的学生学号
SELECT SubjectNo FROM `subject` WHERE SubjectName='数据库结构-1'

--查询分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`，`StudentName`
FROM student AS s
INNER JOIN result AS r
ON r.StudentNo=s.StudentNo
WHERE `StudentResult`>=80
--在这个基础上增加一个科目，高等数学-2 查询高等数学-2的编号
SELECT DISTINCT s.`StudentNo`，`StudentName`
FROM student AS s
INNER JOIN result AS r
ON r.StudentNo=s.StudentNo
WHERE `StudentResult`>=80 AND `studentNo`=(
    SELECT SubjectNo FROM `subject` 
    WHERE `SubjectName`='高等数学-2')
--查询高等数学-2且分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`，`StudentName`
FROM student AS s
INNER JOIN result AS r
ON r.StudentNo=s.StudentNo
INNER JOIN `subject` AS sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName`='高等数学-2'AND `StudentResult`>=80
--再改造(由里及外)
SELECT DISTINCT s.`StudentNo`，`StudentName`
FROM student WHERE StudentNo IN(
SELECT StudentNo FROM result WHERE StudentResult>=80 AND SubjectNo=(
SELECT SubjectNo FROM `subject` WHERE `SubjectName`='高等数学-2')
)

--练习：查询C语言-1前5名同学的信息（学号，姓名，分数）使用子查询

4.7分组和过滤

SELECT SUM(`StudentResult`)AS 总和 FROM result
SELECT AVG(`StudentResult`)AS 平均数 FROM result
SELECT MAX(`StudentResult`)AS 最高分 FROM result
SELECT MIN(`StudentResult`)AS 最低分 FROM result
--查询不同课程的平均分，最高分，最低分，平均分大于80分
--核心：根据不同的课程分组
SELECT SubjectName,AVG(StudentResult)AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
GROUP BY r.SubjectNo--通过什么字段来分组
HAVING 平均分>80

MySQL函数

5.1常用函数

--数学运算
SELECT ABS(-8)    --ABS绝对值
SELECT CEILING(9.4) --向上取整
SELECT FLOOR(9.4)   --向下取整
SELECT RAND()   --返回0-1之间的随机数
SELECT SING() --判断一个数的符号 0-0 负数返回-1 整数返回1
--字符串函数
SELECT CHAR_LENGTH() -- 字符串长度
SELECT CONCAT('我','爱’)  --拼接字符串
SELECT INNSERT('我爱',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER()--转小写字母
SELECT UPPER()--转大写字母
SELECT INSTR('kuangshen','h')--返回第一次出现的子串索引
SELECT REPLACE('坚持就能成功','坚持'，'努力')--替换出现的指定字符串
SELECT SUBSTR('坚持就能成功',4,3)--返回指定的字符串(源字符串，截取的位置，截取的长度)
SELECT REVERSE()--反转

--查询姓周的同学，名字 邹
SELECT REPLACE (studentname,'周','邹')FROM student
WHERE student 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聚合函数（常用）

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

--都能够统计表中的数据（想查询一个表中有多少个记录，就用这个)
SELECT COUNT(studentname) FROM student;--COUNT(指定列)，会忽略Null值
SELECT COCUNT(*)FROM student;----COUNT(*)，不会忽略Null值，本质计算行数
SELECT COUNT(1) FROM student;--COUNT(1)，不会忽略Null值，本质计算行数

5.3数据库级别的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,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
--加密
UPDATE testmd5 SET pwd=MD5(pwd)  WHERE id=1
UPDATE testmd5 SET pwd=MD5(pwd) --加密全部的密码
--插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
--如何校验：将用户传进来的密码，进行md5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

事务 Transaction

6.1什么是事务

要么都成功，要么都失败

1. SQL执行，A 给B转账
2. SQL执行，B收到A的钱

将一组SQL放在一个批次中去执行

事务原则：ACID原则 原子性，一致性，隔离性，持久性

原子性(Atomicity)：要么都成功，要么都失败

一致性(Consistency)：事务前后的数据完整性要保证一致。

隔离性(Isolation):事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability):事务一旦提交则不可逆，被持久化到数据库中。

隔离所导致的一些问题

脏读：指一个事务读取了另外一个事务未提交的数据。

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。

幻读（需读）：是指在一个事务内读到了别的事务插入的数据，导致前后读取不一致。

执行事务

--mysql是默认开启事务自动提交的
SET autocommit=0/*关闭*/
SET autocommit=1/*开启（默认的）*/
--手动开启事务
--事务开启
START TRANSACTION --标记一个事务的开始，从这个之后的Sql都在同一个事务内
INSERT XX
INSERT XX
--提交：持久化（成功！）
COMMIT
--回滚：回到的原来的样子（失败！）
ROLLBACK
--事务结束
SET autocommit=1--开启自动提交
--了解
SAVEPOINT 保存点名--设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名--回滚到保存点
RELEASE SAVEPOINT 保存点名--撤销保存点

模拟场景

--转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf_8_genneral_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCERMENT,
`name` VARCHAR(30) NOT NULL,
`money`  DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)
--模拟事务，转账
SET autocommit = 0;--关闭自动提交
START TRANSACTION --开启一个事务（一组事务）
UPDATE account SET money=money-500 WHERE `name`='A'--A减500
UPDATE account SET money=money-500 WHERE `name`='B'--A加500
COMMIT;--提交事务，就被持久化了
ROLLBACK；--回滚
SET autocommit=1;--恢复默认值

索引

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据库的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引(PRIMARY KEY)

  • 唯一的标识，主键不可重复，只能有一个列作为主键

• 唯一索引(UNIQUE KEY)

  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

• 常规索引(KEY/INDEX)

  • 默认的，Index,key关键字来设置

• 全文索引(FULLTXET)

  • 在特定的数据库引擎下才有，MyISAM

  • 快速定位数据

基础语法：

--1.在创建表的时候，给字段增加索引
--2.创建完毕后，增加索引
--显示所有的索引信息
SHOW INDEX FROM student
--增加一个索引(索引名)列名
ALTER TABLE `student` ADD FULLTEXT INDEX `studentName`(`studentName`);
--EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student;--非全文索引
EXPLAIN SELECT * FROM student WHERW MATCH(studentName) AGAINST('刘');

7.2测试索引

--插入100万条数据
DWLMITER $$--写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURN INT 
BEGIN
 DECLARE num INT DEFAULT 1000000;
 DECLARE i INT DEFAULT 0;
 WHILE i<num DO
   INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
   VALUES(CONCAT('用户',i),'[email protected],CONCAT('18',FLOOR(RAND()*((999999-1000000)+1000000)))
,FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i=i+1;
 END WHILE;
 RETURN i;
END;
SELECT mock_data();
SELECT * FROM app_user WHERE `name`='用户9999';
--id_表名_字段名
CREATWE INDEX id_app_user_name ON app_user(`name`);

索引在小数据量的时候，用户不大，但是在大数据的时候，区分十分明显

7.3索引原则

• 索引不是越多越好

• 不要对进程变动数据加索引

• 小数据量的表不需要加索引

• 索引一般在常用来查询的字段上

索引的数据结果

Hash类型的索引

Btree：InnoDB的默认数据结构

阅读：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

权限管理和备份

8.1用户管理

SQL命令操作

用户表：mysql.user

本质：对这张表进行增删改查

--创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER kuangshen IDENTIFIED BY '123456'
--修改密码（修改当前用户密码）
SET PASSWORD =PASSWORD('123123')
--修改密码（修改指定用户密码）
SET PASSWORD FOR kuangshen=PASSWORD('123123')
--重命名 RENAME USER 原来的名字 TO 新的名字
RENAME USER kuangshen TO kuangshen2
--用户授权 GRANT ALL PRIVILEGES 全部的权限，库，表
--ALL PRIVILEGES除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
--查询授权
SHOW GRANTS FOR kuangshen2 --查看指定用户的权限
SHOW GRANTS FOR root@localhost --查看管理员权限
--撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
--删除用户
DROP USER kuangshen

8.2MySQL备份

为什么要备份：

• 保证重要的数据不丢失；

• 数据转移

MySQL数据库备份的方式：

• 直接拷贝物理文件

• 在MySQL可视化工具中手动导出

• 使用命令行导出 musqldump 命令行

  --mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
  mysqldump -hlocalhost -uroot -p123456 shool student >D:/a.sql
  --mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3>物理磁盘位置/文件名
  

--导入，先登录
#登录的情况下，切换到指定的数据库
source 备份文件
source d:/a.sql
#未登录的情侣
mysql -u用户名 -p密码 库名< 备份文件

假设你要备份数据库，防止数据丢失。

把数据库给朋友，sql文件给别人即可。

规范数据库设计

9.1为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据插入和删除都会麻烦、异常【屏蔽使用物理外键】
• 程序的性能差

良好的数据库设计：

• 节省内存空间
• 保证数据的完整性
• 方便我们开发系统

软件开发中，关于数据库的设计：

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图E-R图

设计数据库的步骤：（个人博客）

• 收集信息，分析需求

  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 评论表
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键字，或者一些主字段）key:value
  • 说说表（发表心情..id...content...create_time)

• 标识实体（把需求落地到每个字段）

• 标识实体之间的关系

  • 写博客user--->blog
  • 创建分类user--->category
  • 关注user---->user
  • 友链Link
  • 评论表user--->user-blog

9.2三大范式

为什么需要数据规范化？

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常

三大范式（了解）

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：必须满足第一范式

每张表只描述一件事情

第三范式（3NF）

前提：必须满足第一、二范式

第三范式需要确保数据表中的每一列都和主键直接相关，而不能间接相关

规范性和性能的问题

关联查询的表不得超过三张表：

• 考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
• 在规范性能问题的时候，需要适当的考虑一下规范性
• 故意给某些表增加一些冗余字段（从多表查询中变为单表查询）
• 故意增加一些计算列（从大数据量降低为小数据量的查询，增加索引）

JDBC（重点）

10.1数据库驱动

驱动：声卡、显卡、数据库

我们的程序会通过数据库驱动和数据库打交道。

10.2JDBC

SUN公司为了简化开发人员（对数据库统一）操作，提供了一个（JAVA操作数据库的 ）规范，俗称JDBC

这些规范的实现由具体的厂商去做

对于开发人员,我们只需要掌握JDBC接口的操作即可。

Java.sql

Javax.sql

还需要导入一个数据库驱动包

10.3第一个JDBC程序

创建测试数据库

1. 创建一个普通项目
2. 导入数据库驱动
3. 编写测试代码

package JDBC;

import javax.xml.transform.Result;
import java.sql.*;

//我的第一个JDBC程序
public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息的url
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="123456";
        //3.连接成功，数据库对象
        Connection connection= DriverManager.getConnection(url,username,password);
        //4.执行SQL的对象
        Statement statement=connection.createStatement();
        //5.执行SQL，可能存在结果
        String sql="SELECT * FROM user";
       ResultSet 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("pwd="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
    
}

步骤总结：

1. 加载驱动
2. 连接数据库
3. 获得执行sql的对象
4. 获得返回的结果集
5. 释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver);
        Class.forName("com.mysql.jdbc.Driver");
Connection connection= DriverManager.getConnection(url,username,password);
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

 String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql--3306
//协议//主机地址：端口号/数据库名?参数1&参数2&参数3
//oracle--1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement 执行SQL的对象 PrepareStatement 执行SQL的对象

String sql="SELECT * FROM user";//编写SQL
statement.executeQuery();//查询操作，返回ResultSet
statement.execute();//执行任何SQL
statement.executeUpdate();//更新，插入，删除，都是用这个返回一个受影响的行数
statement.executeBatch();

ResultSet查询的结果集：封装了所有的查询结果

获得指定的数据类型

resultSet.getObject();//在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();

遍历,指针

resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行

释放资源

resultSet.close();
statement.close();
connection.close();

10.4Statement对象

==JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据的增删改查，只需要通过这个对象发送增删改查语句即可。

statement对象的executeUpdate方法，用于数据库发送增删改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变换）。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

Statement st= connection.createStatement();
String sql="insert into user(...) values(...)";
int num=st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功！");
}

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作，示例操作：

Statement st= connection.createStatement();
String sql="detele from user where id=1";
int num=st.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功！");
}

CRUD操作-update

使用executeUpdate(String sql)方法完成数据修改操作，示例操作：

Statement st= connection.createStatement();
String sql="update user set name='' where name=''";
int num=st.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功！");
}

CRUD操作-read

使用executeUpdate(String sql)方法完成数据查询操作，示例操作：

Statement st= connection.createStatement();
String sql="select * from user where id=1";
int num=st.executeQuery(sql);
while(rs.next()){
    //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

代码实现

1. 提取工具类

   package JDBC.lesson02.utils;
   
   import javax.xml.transform.Result;
   import java.io.IOException;
   import java.io.InputStream;
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.Properties;
   
   public class JdbcUtils {
       private static String driver =null;
       private static String url =null;
       private static String username =null;
       private static String password =null;
       static {
           try{
               InputStream in=JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
               Properties properties=new Properties();
               properties.load(in);
               driver= properties.getProperty("driver");
               url= properties.getProperty("url");
               username= properties.getProperty("username");
               password= properties.getProperty("password");
               //1.驱动只用加载一次
               Class.forName(driver);
           } catch (IOException e) {
               throw new RuntimeException(e);
           } catch (ClassNotFoundException e) {
               throw new RuntimeException(e);
           }
       }
       //获取连接
       public static void getConnection(){
           Connection connection;getConnection(url,username,password);
       }
   
       private static void getConnection(String url, String username, String password) {
       }
   
       //释放连接资源
       public static void release(Connection connection, Statement st, ResultSet rs) throws SQLException {
           if(rs!=null){
               rs.close();
           }
           if (st!=null){
               st.close();
           }
           if (connection!=null){
               connection.close();
           }
       }
   }
   

2. 编写增删改的方法executeUpdate

SQL注入的问题

sql存在漏洞会被攻击导致数据泄漏。SQL会被拼接 or

10.5PreparedStatement对象

PreparedStatement可以防止SQL注入，效率更好！

1. 新增

2. 删除

3. 更新

4. 查询

package JDBC.lesson03;

import JDBC.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement st=null;
        
        try {
            conn = JdbcUtils.getConnection();
            //区别
            String sql="insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?)";
           st= conn.prepareStatement(sql);//预编译SQL：先写SQL，然后不执行
            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"qinjiang");
            st.setString(3,"1232112");
            st.setString(4,"[email protected]");
            //注意点：sql.Date 数据库 java.sql.Date
            //util.Date   Java  new Date().getTime()获得时间量
            st.setDate(5,new java.sql.Date(new Date().getTime()));
            //执行
            int i=st.executeUpdate();
            if(i>0){
                System.out.println("插入chengg");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

10.6使用IDEA连接数据库

连接成功后，可以选则数据库。

10.7JDBC操作事务

要么都成功，要么都失败

ACID原则

package JDBC.lesson04;

import JDBC.lesson02.utils.JdbcUtils;

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

public class TestTransaction01 {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        try {
            JdbcUtils.getConnection();
            //关闭数据库的自动提交，自动会开启事务
            conn.setAutoCommit(false);//开启事务
            String sql1="update account  set money = money-100 where name = 'A'";
            st=conn.prepareStatement(sql1);
            st.executeUpdate();
            int x=1/0;//报错
            String sql2="update account  set money = money-100 where name = 'B'";
            st=conn.prepareStatement(sql2);
            st.executeUpdate();
            //业务完毕，提交事务
            conn.commit();
            System.out.println("成功");
        }catch (SQLException e){
            conn.rollback();//如果失败则回滚事务
            e.printStackTrace();
        }
      finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.8数据库连接池

数据库连接——执行完毕——连接——释放

连接——释放十分浪费资源

池化技术：准备一些预先的资源，过来就连接预先准备好的

开源数据源实现

DBCP

C3P0

Druid

使用了这些数据库连接池之后，我们在项目中就不再需要编写连接数据库的代码了。

DBCP

标签：--,数据库,sql,查询,student,MySQL,SELECT
From： https://www.cnblogs.com/sx-xiaoL/p/17536064.html