1、初识MySQL
javaEE : 企业级java开发 Web
前端 （页面：展示，数据！）
后台 （连接点：连接数据库JDBC，连接前端（控制，控制视图跳转，和给前端传递数据））
数据库（存数据，Txt，Excel，word）
只会写代码，学好数据库，基本混饭吃
操作系统，数据结构与算法！当一个不错的程序员！
离散数据，数字电路，体系结构，编译原理。+实战经====
1.1、为什么学习数据库
- 岗位xuqiu
- 现在的世界，大数据时代~，得数据者得天下
- 被迫需求：存数据
- 数据库是所有软件体系中最核心的存在 DBA
1.2、什么是数据库
数据库（DB，DataBase）
概念：数据仓库，软件，安装在操作系统（windows,linux,mac…）之上！SQL,可以存储大量的数据，500万！
作用：存储数据，管理数据
1.3、数据库分类
关系新数据库： （SQL）
- MySQL,Oracle, Sql Service, DB2, SQLlite
- 通过表和表之间，行和列之间的关系进行数据的存储， 学员信息表，考勤表
非关系型数据库： （NoSQL） Not Only
- Redis ，MongD8
- 非关系型数据库 ，对象存储，通过对象的自身的属性来决定
DMBS(数据库管理系统)
- 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；
- MySQL ， 数据管理系统！
1.4、MySQL简介
MySQL是一个关系型数据库管理系统
前世 ：瑞典MySQL AB公司
今生 ：属于Oracle旗下产品
MySQL是最好的RDBMS（Relational Database Management System,关系数据库管理系统）应用软件之一。
开源的数据库软件~
体积小、速度快、总体拥有成本低，所有人必须会~
中小型网站，或者大型网站，集群！
官网：http://www.mysql.com
安装建议：
- 尽量不要使用exe,
- 尽可能使用压缩包安装
1.5、安装MySQL
教程：http://www.cnblogs.com/hellokuangshen/p/10242958.html
1.6、安装SQLyog
- 无脑安装
- 注册
- 打开连接数据库
- 新建一个数据库 school
- 新建一张表 student
字段 ： id ,name,age
- 查看表
- 自己尝试添加多条记录
1.7、连接数据库
命令行连接！
mysql -uroot -p123456 -- 连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 修改密码
flush privileges; -- 刷新权限
-------------------------------------------
-- 所有的语句都使用;结尾
show databases; -- 查看所有的数据库
mysql> use school -- 切换数据库 use 数据库名
Database changed
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息
create database westos; -- 创建一个数据库
exit; -- 退出连接
-- 单行注释（SQL的本来的注释）
/*
(SQL的多行注释)
*/
数据库xxx语言 CRUD增删改查！ CV程序员 API程序员 CRUD程序员！（业务！）
DDL 定义
DML 操作，管理
DQL 查询
DCL 控制
2、操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
2.1、操作数据库（了解）
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
- 删除数据库
DROP DATABASE [IF EXISTS] westos;
- 使用数据库
-- tab键的上面，如果你的表名或者字段名是一个特殊字符，就需带``
USE `school`
- 查看数据库
SHOW DATABASES -- 查看所有的数据库
对比：SQLyog的可视化操作
学习思路：
- 对比SQLyog可视化历史记录sql
- 固定的语法或关键字必须强行记住！
2.2、数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节 常用的int
- bigint 较大的数据 8个字节
- float 浮点数 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来填充，int（3）， 5 ----005
自增
- 通常理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键 ~ index，必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 NULL not null
- 假设设置为 not null ,如果不给它赋值，就会报错！
- NULL，如果不填写值，默认就是null!
默认:
- 设置默认的值！
- sex ,默认值为男，如果不指定该列的值，，则会有默认的值！
拓展：听听就好
/*
id 主键
`verson` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4、创建数据库表(创建)
-- 目标 ：创建一个school数据库
-- 创建学生表（列，字段），使用SQL创建
-- 学号int 登录密码varchar(20) 姓名 ，性别varchar(2),出生日期(datetime),家庭住址，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` datetime DEFAULT NULL COMMENT '出生日期',
`address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
格式
CREATE TABLE [IF NOT EXISTS] `表明`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
………
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE TABLE `student` -- 查看创建数据库的语句SHOW CREATE DATABASE `school` -- 查看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中配置默认的编码
charset-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 age1 VARCHAR(11) -- 修改约束-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]ALTER TABLE student1 CHANGE age age1 INT(1) -- 字段重命名-- 删除表的字段： 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 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 '性别', `gradeid` INT(10) NOT NULL COMMENT '学生的年级', `birthday` DATETIME DEFAULT 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 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 '性别',
`gradeid` INT(10) NOT NULL 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
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) PEFERENCES 哪个表(哪个字段)
以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰，这里了解即可）
最佳实践
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 我们想使用多张表的数据，想使用外键（程序去实践）
3.2、DEL语言（全部记住）
数据库的意义：数据存储，数据管理
DML语言：数据操作语言
- insert
- update
- delete
3.3、添加
insert
语法： insert into 表名([字段名1,字段名2，字段名3]) values(‘值1’),(‘值2’),(‘值3’,……)
-- 插入语句（添加）-- insert into 表名([字段名1,字段名2，字段名3]) values('值1'),('值2'),('值3',……)INSERT INTO `grade`(`gradename`) VALUE('大四'); -- 由于主键自增我们可以省略(如果不写表的字段，他就会一一匹配)INSERT INTO `grade` VALUE('大三'); -- 一般写插入语句，我们一定要数据和字段一一对应！-- 插入多个字段INSERT INTO `grade`(`gradename`) VALUE('大四'),('大一'),('大三') INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`) VALUES('张三','aaa','男',1),('李四','aaaaaaa','男',2)
注意事项：
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的，但后面的值必须要一一对应，不能少
- 可以同时插入多条数据，VALUES 后面的值，需要使用，隔开即可 VALUES(),() ……
3.4、修改
语法 ：UPDATE 表名 set column_name = value,[column_name = value,……] where [条件]
update 修改谁（条件） set 原来的值 = 新值
-- 修改学员名字，带了简介UPDATE `student` SET `name`='狂神' WHERE id = 1;-- 不指定条件的情况下，会改动所有表!UPDATE `student` SET `name` = '长江七号'-- 语法：-- UPDATE 表名 set column_name = value,[column_name = value,……] where [条件]条件：
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5 = 6 | false |
| <> 或 != | 不等于 | 5<>6 | true |
| < | |||
| > | |||
| <= | |||
| >= | |||
| BETWEEN… and … | 在某个范围内 | [2,5] | |
| AND | 我和你&& | 5>1 and 1 >2 | false |
| OR | || | 5>1 or 1 > 2 | true |
-- 通过多个条件定位数据UPDATE `student` SET `name` = '长江七号' WHERE `name` = '狂神44' AND sex = '女'
注意：
- column_name 是数据库的列，尽量带上``
- 条件，筛选的条件，如果没有指定，则会修改所有的列
- value，是一个具体的值，也可以是一个变量
- 多个设置的属性之间，使用英文逗号隔开
UPDATE `student` SET `birthday` = CRUUENT_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 `test`( `id` INT(4) NOT NULL AUTO_INCREMENT, `coll` VARCHAR(20) NOT NULL, PRIMARY KEY(`id`))ENGINE = INNODB DEFAULT CHARSET = utf8INSERT 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 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 -- 查询有哪些同学参加了考试2
SELECT DISTINCT `studentno` FROM result -- 去重
数据库的列（表达式）
SELECT VERSION() -- 查询系统版本（函数）
SELECT 5*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 ! b | …… |
尽量使用英文字母
SELECT `studentno`,`studentresult` FROM result-- 查询考试成绩在95~100分之间SELECT `studentno`,`studentresult` FROM resultWHERE studentresult >=95 AND studentresult <= 100SELECT `studentno`,`studentresult` FROM resultWHERE studentresult >=95 AND studentresult <= 100SELECT `studentno`,`studentresult` FROM resultWHERE studentresult BETWEEN 95 AND 100-- 查询除了1000号学生之外的同学的成绩SELECT `studentno`,`studentresult` FROM resultWHERE NOT studentno = 1001SELECT `studentno`,`studentresult` FROM resultWHERE studentno != 1001
模糊查询 ： 比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is NULL | a is null | 如果操作符为NULL，结果为真 |
| is NOT NULL | a is not null | 如果操作符不为NULL,结果为真 |
| BETWEEN | a between b and c | 若 a 在 b 和 c之间，则结果为真 |
| Like | a like b | SQL 匹配，如果a匹配b,则结果为真 |
| In | a in (a1,a2,a3……) | 假设a在a1,或者a2……其中的某一个值中，结果为真 |
-- =====================模糊查询===================-- 查询姓刘的同学-- like结合 %(代表0到任意个字符) _(一个字符)SELECT `studentno`,`studentname` FROM `student`WHERE `studentname` LIKE '刘%'-- 查询姓刘的同学，名字后面只有一个字的SELECT `studentno`,`studentname` FROM `student`WHERE `Address` LIKE '刘_'-- 查询姓刘的同学，名字后面有两个字的SELECT `studentno`,`studentname` FROM `student`WHERE `Address` LIKE '刘__'-- 查询名字中间有嘉字的同学 %嘉%SELECT `studentno`,`studentname` FROM `student`WHERE `Address` 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
-- Inner joinSELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`FROM `student` AS sINNER JOIN `result` AS rWHERE s.studentno = r.studentno-- Right JoinSELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`FROM `student` AS sRIGHT JOIN `result` AS rON s.studentno = r.studentno-- Left JoinSELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`FROM `student` AS sLEFT JOIN `result` AS rON s.studentno = r.studentno
| 操作 | 描述 |
|---|---|
| Inner join | 如果表中至少有一个匹配，就返回行 |
| Left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| Right join | 会从右表中返回所有的值，即使左表中没有匹配 |
-- =================== 联表查询 join ==================-- 查询参加了考试的同学（学号，姓名，科目编号，分数）SELECT * FROM studentSELECT * FROM result/*思路1. 分析需求，分析查询的字段来自哪些表（连接查询）2. 确定使用哪种连接查询？ 7种确定交叉点（这两个表中哪个数据是相同的）判断的条件： 学生表中的 studentno = 成绩表 studentno*/-- join (连接的表) on （判断的条件） 连接查询-- where 等值查询SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`FROM `student` AS sINNER JOIN `result` AS rON s.`studentno` = r.`studentno`-- 查询缺考的同学SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`FROM `student` AS sLEFT JOIN `result` AS rON s.`studentno` = r.`studentno`WHERE studentresult IS NULL-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）/**/SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`FROM `student` AS sRIGHT JOIN `result` AS rON s.`studentno` = r.`studentno`INNER JOIN `subject` AS DON r.`subject` = sub.`subject`-- 我要查询哪些数据 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 | 美术设计 |
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 查询父子信息：把一张表看为两个一模一样的表SELECT a.`categoryName`,b.`categoryName`FROM `category` AS a,`category` AS bWHERE a.categoryid = b.pid
注意用WHERE
4.5、分页和排序
排序
-- 排序：升序 ASC ，降序 DESC-- ORDER BY 通过哪个字段排，怎么排-- 查询的结果排序，成绩降序，升序SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`FROM `student` AS sRIGHT JOIN `result` AS rON s.`studentno` = r.`studentno` LEFT JOIN `subject` AS subON sub.`subjectno` = r.`subjectno`WHERE subjectname = '数据库结构-1'ORDER BY `studentresult` ASC
分页
-- 100万-- 为什么要分页？-- 缓解数据库压力，给人的体验更好， 瀑布流-- 分页 ， 没页只显示五条数据-- 语法 ： limit 起始值，页面的大小-- 网页应用 ： 当前，总的页数，页面的大小-- LIMIT 0,5 1~5-- LIMIT 1,5 2~6-- LIMIT 6,5 SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`FROM `student` AS sRIGHT JOIN `result` AS rON s.`studentno` = r.`studentno` LEFT JOIN `subject` AS subON sub.`subjectno` = r.`subjectno`WHERE subjectName = '数据库结构-1'ORDER BY `studentresult` ASCLIMIT 5,5-- 第一页 limit 0,5 (1-1) * 5-- 第二页 limit 5,5 (2-1) * 5-- 第三页 limit 10,5 (3-1) * 5-- 第N页 limit 0,5 (n-1) * pageSize-- 【pageSize : 页面大小】-- 【(n-1)*pageSize :起始值】-- 【n: 当前页数】-- 【数据总数 / 页面大小 = 总页数】
语法 ： limit(查询起始下标,pageSize)
select * from 表名 limit 0,10;
表示取表中的前10条数据（从第1条开始，取10条）
4.6、子查询
where (这个值是计算出来的)
本质 ： 在where语句中嵌套一个子查询语句
-- 1、查询高等数学-1的所有考试结果（学号，科目编号，成绩），降序排列-- 方式一：使用连接查询SELECT `studentno`,`subjectname`,`studentresult`FROM `result` AS rLEFT JOIN `subject` AS subON sub.`subjectno` = r.`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-- ============== 查询 高等数学-1 分数不小于80分的学生的学号和姓名SELECT DISTINCT s.`studentno`,`studentname`FROM `student` AS sINNER JOIN `result` AS rON s.studentno = r.studentnoINNER JOIN `subject` AS subON r.`subjectno` = sub.`subjectno`WHERE `studentresult` >= 80 AND `subjectname` = '高等数学-1'-- ============= 子查询SELECT s.`studentno`,`studentname`FROM `student` AS sINNER JOIN `result` AS rON s.`studentno` = r.`studentno`WHERE `studentresult` >= 90 AND `subjectno` = ( SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-1')-- 再改造（由里及外） (学号很多，所以用 IN)SELECT `studentno`,`studentname` FROM `student` WHERE studentno IN ( SELECT `studentno` FROM `result` WHERE `studentresult` >= 80 AND `subjectno` = ( SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-1' ))
4.7、分组和过滤
-- 查询不同课程的平均分，最高分，最低分，平均分大于80-- 核心： （根据不同的课程分组）SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分FROM `result` AS rINNER JOIN `subject` AS subON r.`subjectno` = sub.`subjectno`GROUP BY r.`subjectno` -- 通过什么字段来分组HAVING 平均分 > 80
4.8、Select 小结
5、MySQL函数
官网 ：http://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
5.1、常用函数
在这里插入图片描述
5.2、聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计算 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| …… | …… |
-- =====================聚合函数 =================
-- 都能够统计 表中的数据（想查询一个表中有多少个记录，就使用这个count()）
SELECT COUNT(studentname) FROM student; -- Count(字段),会忽略所有的null 值
SELECT COUNT(*) FROM student -- Count(*),不会忽略null值，把所有列走一遍
SELECT COUNT(1) FROM student -- Count(*),不会忽略null值，只走一列
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 加密 ==========
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')
/* drop table testmd5
use school */
-- 加密
UPDATE testmd5 SET pwd = MD5(pwd) WHERE id = 1
UPDATE testmd5 SET pwd = MD5(pwd) -- 加密全部的密码
-- 插入的时候加密
INSERT INTO testmd5 VALUES(5,'xiaoming',MD5('23456'))
-- 如何校验 : 将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT *FROM testmd5 WHERE `name` = 'xiaoming' AND pwd = MD5('123456')
6.事务
6.1、什么是事务
执行事务
模拟场景
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ciUSE shop`account`CREATE TABLE `account`( `id` INT(3) NOT NULL AUTO_INCREMENT, `name` VARCHAR(30) NOT NULL, `money` DECIMAL(9,2) NOT NULL, PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8INSERT 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减500UPDATE ACCOUNT SET money = money+500 WHERE `name` = 'B' -- B加500COMMIT; -- 提交事务，就被持久化了！ROLLBACK; -- 回滚SET autocommit = 1; -- 回复默认值
7.索引
7.1索引的分类
7.2测试索引
CREATE TABLE `app_user`( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称', `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱', `phone` VARCHAR(20) DEFAULT '' COMMENT '手机号', `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0:男; 1:女）', `password` VARCHAR(100) NOT NULL COMMENT '密码', `age` TINYINT(4) DEFAULT '0' COMMENT '年龄', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`))ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'-- 插入100w条数据DELIMITER $$ -- 写函数之前必须要写标志CREATE FUNCTION mock_data()RETURNS INTBEGIN 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),'431975683@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))), FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100)); SET i = i+1; END WHILE; RETURN i; END;SELECT mock_data();
索引在小数据量的时候，用处不大，但在大数据的时候，区别十分明显
7.3索引原则
8、权限管理和备份
8.1、用户管理
SQL yog 可视化管理
SQL 命令操作
用户表 ： mysql.user
本质： 读这张表进行增删改查
8.2、数据库备份
假设你要备份数据库，防止数据丢失。
把数据库给朋友！sql文件给别人即可！
9、规范数据库设计
9.1、为什么需要设计
9.2、三大范式
10、JDBC(重点)
10.1、数据库驱动
我们的程序会通过 数据库 驱动，和数据库打交道！
10.2、JDBC
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p9c9Kir9-1630673830777)(D:\图片\image-20210725140113387.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sJyY0TAO-1630673830778)(D:\图片\image-20210725140134244.png)]
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar
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','zhangsan','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、导入数据库驱动
3、 编写测试代码
package com.yang.lesson01;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=false"; String username = "root"; String password = "123456"; //3. 连接成功，数据库对象 Connection connection = DriverManager.getConnection(url, username, password); //4. 执行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("pwd=" + resultSet.getObject("PASSWORD")); System.out.println("email=" + resultSet.getObject("email")); System.out.println("birth=" + resultSet.getObject("birthday")); System.out.println("========================"); } //6. 释放连接 resultSet.close(); statement.close(); connection.close(); }}
步骤总结：
- 加载驱动
- 连接数据库DriverManager
- 获得执行sql的对象Statement
- 获得返回的结果集
- 释放连接
10.4、statement对象
代码实现
1、提取工具类
package com.yang.lesson02.utils;
import java.io.InputStream;
import java.sql.*;
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 (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接资源
public static void release(Connection conn , Statement st , ResultSet rs)
{
if(rs!=null){
try{
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、编写增删改的方法， executeUpdate
package com.yang.lesson02;import com.yang.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestInsert { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" + "VALUES(4,'kuangshen','123456','431975683@qq.com','2020-01-01')"; int i = st.executeUpdate(sql); if(i>0){ System.out.println("插入成功！"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }}
package com.yang.lesson02;import com.yang.lesson02.utils.JdbcUtils;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 conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "DELETE FROM users WHERE id = 4"; int i = st.executeUpdate(sql); if(i>0){ System.out.println("删除成功！"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }}
package com.yang.lesson02;
import com.yang.lesson02.utils.JdbcUtils;
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 conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "UPDATE users SET `NAME` = 'kuangshen',`email` = '431975683@qq.com' WHERE id = 1";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("修改成功！");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、查询executeQuery
package com.yang.lesson02;
import com.yang.lesson02.utils.JdbcUtils;
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 conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//SQL
String sql = "select * from users where id = 1";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQl注入问题
sql存在漏洞，会被攻击导致数据泄露，SQL会被拼接 or
package com.yang.lesson02;import com.yang.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class SQL注入 { public static void main(String[] args) { //login("kuangshen","123456"); login(" 'or '1=1"," 'or '1=1"); // 技巧 } // 登录业务 public static void login(String username,String password){ Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); //SELECT * FROM users WHERE `name` = 'kuangshen' AND `password` = '123456'; String sql = "select * from users where `NAME`='"+username+"' AND `password` = '"+password+"'"; rs = st.executeQuery(sql); while(rs.next()){ System.out.println(rs.getString("NAME")); System.out.println(rs.getString("password")); System.out.println("======================"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }}
10.5、PreparedStatement对象
PreparedStatement 可以防止SQL注入。效率更好！
1、新增
package com.yang.lesson03;import com.yang.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) { 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); // 手动给参数赋值 st.setInt(1,4); st.setString(2,"qinjiang"); st.setString(3,"1232112"); st.setString(4,"24734673@qq.com"); st.setDate(5,new java.sql.Date(new Date().getTime())); int i = st.executeUpdate(); if(i>0){ System.out.println("插入成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,null); } }}
2、删除
package com.yang.lesson03;import com.yang.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Date;public class TestDelete { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; try { conn = JdbcUtils.getConnection(); // 区别 // 使用 ? 占位符代替参数 String sql = "delete from users where id=?"; st = conn.prepareStatement(sql); // 手动给参数赋值 st.setInt(1,4); int i = st.executeUpdate(); if(i>0){ System.out.println("删除成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,null); } }}
3、更新
package com.yang.lesson03;import com.yang.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class TestUpdate { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; try { conn = JdbcUtils.getConnection(); // 区别 // 使用 ? 占位符代替参数 String sql = "update users set `name`=? where id=?"; st = conn.prepareStatement(sql); // 手动给参数赋值 st.setString(1,"狂神"); st.setInt(2,1); int i = st.executeUpdate(); if(i>0){ System.out.println("更新成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,null); } }}
4、查询
package com.yang.lesson03;
import com.yang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from users where id = ?";
st = conn.prepareStatement(sql);
st.setInt(1,2);
rs = st.executeQuery(); //执行
if(rs.next())
{
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
5、防止SQL注入
package com.yang.lesson03;
import com.yang.lesson02.utils.JdbcUtils;
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) {
login("lisi","123456");
//login(" 'or '1=1"," 'or '1=1"); // 技巧
}
// 登录业务
public static void login(String username,String password){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//SELECT * FROM users WHERE `name` = 'kuangshen' AND `password` = '123456';
String sql = "select * from users where `NAME`=? and `password` =? ";
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();
while(rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
System.out.println("======================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.7、使用IDEA连接数据库
连接成功后，选择数据库
双击数据库
更新数据
若连接失败，查看原因
10.8、事务
要么都成功，要么都失败
代码实现
- 开启事务 conn.setAutoCommit(false);
- 一组业务执行完毕，提交事务
- 可以在catch语句中显示的定义回滚语句，但默认失败就会回滚。
package com.yang.lesson03;
import com.yang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交，自动会开启事务。
conn.setAutoCommit(false); //开启事务
String sql1 = "update account set money = money - 100 where name = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
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) {
//若失败，则回滚
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.9、数据库连接池
| 
Druid
Apache
983
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
