初识MySQL
JavaEE :企业级Java开发Web
前端(页面:展示，数据! )
后台（连接点:连接数据库JDBC，链接前端（控制，控制视图跳转，和给前端传递数据))
数据库（存数据，Txt，Excel，word)
只会写代码，学好数据库，基本混饭吃；
操作系统，数据结构与算法！不错的程序员！
离散数学，数据电路，体系结构，编译原理。 +实战经验，高级，优秀程序员
1.1、为什么学习数据库
1、岗位需求
2、现在的世界，大数据时代~，得数据者得天下。
3、被迫需求：存数据
4、数据库是所有软件体系中最核心的存在 DBA
1.2、什么是数据库
数据库（DB，DataBase）
概念：数据仓库，软件，安装在操作系统（window，linux，mac、.....）之上！SQL，可以储存大量的数据。500万！
作用：储存数据，数据管理 Excel
1.3、数据库分类
关系型数据库：（SQL）
-
MySQL, Oracle, Sql Server, DB2, SQLlite
-
通过表和表之间，行和列之间的关系进行数据的存储， 学员信息表，考勤表，........
非关系型数据库：（NO SQL） Not Only
-
Redis, MongDB
-
非关系型数据库，对象存储，通过对象的自身的属性来决定。
DBMS(数据库管理系统)
-
数据库的管理软件，科学有效的管理我们的数据。维护和获取数据;
-
MySQL，数据库管理系统!
1.4、MySQL简介
MySQL是一个关系型数据库管理系统
前世:瑞典MySQL AB公司
今生:属于 Oracle 旗下产品
MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
开元的数据库软件~ 体积小、速度快、总体拥有成本低，招人成本低，所有人必须会~
中小型网站、或者大型网站，集群！
官网下载地址:https://dev.mysql.com/downloads/mysql
安装建议︰
-
尽量不要使用exe，注册表
-
尽可能使用压缩包安装~
1.5、安装MySQL
教程: https://www.cnblogs.com/hellokuangshen/p/10242958.html
1、解压
2、把这个包放到自己的电脑环境目录下~
3、配置环境变量
4、新建mysql配置文件ini
[mysq1d]
#目录一定要换成自己的
basedir=D : \Environment\mysq1-5.7.19\
datadir=D : \Environment\mysq1-5.7.19\data\
port=3306
skip-grant-tab1es
5、启动管理员模式下的CMD，运行所有的命令
6、安装mysql服务
7、初始化数据库文件
8、启动mysql，进去修改密码~
9、进入mysq|通过命令行（-p后面不要加空壳)，修改密码﹐(sql语句后面一定要加分号! )
10、注掉 ini 中的跳过密码
11、重启mysql。连接测试，如果连接成功就OK了!
-
缺少组件.dll
-
命令输出
sc delete mysql , 清空服务
1.6、安装SQLyog
1、无脑安装
2、注册
3、打开连接数据库 MySQL默认端口号3306
4、新建一个数据库 school
5、新建一张表 student
字段： id，name，age
6、查看表
7、自己尝试添加多条记录
1.7、连接数据库
命令行连接！
mysql -uroot -p00000 --连接数据库
update mysq1.user set authentication_string=password('00000') where user='root' and Host =' localhost'; --修改用户密码
flush privileges;--刷新权限
------------------------------------
--所有的语句都使用;结尾
show databases;--查看所有的数据库
mysq1> use school --切换数据库use数据库名
Database changed
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息
create database westos; --创建一个数据库
exit;--退出连接
数据库xxx语言 CRUD增删改查! CV程序猿 API程序猿 CRUD程序猿!（业务！）
DDL 数据库定义语言
DML 数据库操作语言
DQL 数据库查询语言
DCL 数据库控制语言
2、操作数据库
操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
mysql关键字不分区大小写
2.1、操作数据库(了解)
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] westos
2、删除数据库
DROP DATABASE [IF EXISTS] westos
3、使用数据库
-- tab键的上面,如果你的表名或者字段名是一个特殊字符，就需要带``
USE `school`
4、查看数据库
SHOw DATABASES --查看所有的数据库
学习思路：
-
对照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 ，如果不给它赋值，就会报错！
-
MULL ， 如果不填写值，默认就是null！
默认：
-
设置默认的值！
-
sex，默认值 为 男，如果不指定该列的值，则会有默认的值！
/*每一个表，都必须存在以下五个字段! 未来做项目用的，表示一个记录存在意义！
id 主键
'version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4、创建数据库表
-- 目标︰创建一个schoo1数据库
-- 创建学生表(列,字段)使用sQL创建
-- 学号int 登录密码varchar(20）姓名,性别varchar(2),出生日期(datatime),家庭住址, emai7
-- 注意点，使用英文，表的名称和字段尽量使用`括起来
-- 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 DATABASE school --查看school数据库的语句 SHOW CREATE TABLE student -- 查看student数据表的定义语句 DESC student --显示表的结构
2.5、数据表的类型
-- 关于数据库引擎 /* INNODB 默认使用~ MYISAM 早些年使用的 */
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为MYISAM的2倍 |
常规使用操作：
-
MYISAM 节约空间，速度较快
-
INNODB 安全性高，事务的处理，多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库本质还是文件的存储!
-
InnoDB在数据库表中只有一个*.frm 文件，以及上级目录下的 ibdata1文件
-
MYISAM对应文件
-
*.frm 表结构的定义文件
-
*.MYD 数据文件 （data）
-
*.MYI 索引文件（index）
-
设置数据库表的字符集编码
CHARSET=utf8
不设置的话，会是mysql默认的字符集编码（不支持中文！）
MySQL的默认编码是Latin1，不支持中文
在my.ini中配置默认的编码
character-set-server=utf8
2.6、修改删除表
修改
-- 修改表名 ALTER TABLE 前表名 RENAME AS 修改后表名 ALTER TABLE teacher RENAME AS teacher1 -- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性 ALTER TABLE teacher1 ADD age INT(11) -- 修改表的字段 (重命名，修改约束!) --ALTER TABLE 表名 MODIFY 字段名 列属性[] ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束 --ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[] ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名 -- 删除表的字段 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(100) NOT NULL COMMENT '年级名字', PRIMARY KEY (`gradeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -- 学生表的gradeid字段要去引用年级表的gradeid -- 定义外键key -- 给这个外键添加约束(执行引用)references引用 CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` varchar(30) NOT NULL DEFAULT '默认名' COMMENT '姓名', `pwd` varchar(30) NOT NULL DEFAULT '00000' COMMENT '密码', `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别', `birthday` datetime DEFAULT NULL COMMENT '出生日期', `gradeid` int(10) NOT NULL COMMENT '学生的年级', `address` varchar(90) 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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
删除有外键关系的表的时候，必须要先删除引用别人的表（从表)，再删除被引用的表（主表)
方式二：创建表成功后，添加外键约束
CREATE TABLE `grade` ( `gradeid` int(10) NOT NULL AUTO_INCREMENT COMMENT '年级id', `gradename` varchar(100) NOT NULL COMMENT '年级名字', PRIMARY KEY (`gradeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` varchar(30) NOT NULL DEFAULT '默认名' COMMENT '姓名', `pwd` varchar(30) NOT NULL DEFAULT '00000' COMMENT '密码', `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别', `birthday` datetime DEFAULT NULL COMMENT '出生日期', `gradeid` int(10) NOT NULL COMMENT '学生的年级', `address` varchar(90) 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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`) -- ALTER TABLE `表名` ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES `那个表`(哪个字段)
以上的操作都是物理外键，数据库级别的外键，我们不建议使用!(避免数据库过多造成困扰，这里了解即可~)
最佳实践
-
数据库就是单纯的表，只用来存数据，只有行（数据)和列(字段)
-
我们想使用多张表的数据，想使用外键(程序去实现)
3.2、DML语言（全部记住）
数据库意义： 数据存储，数据管理
DML 语言： 数据操作语言
-
Insert
-
update
-
delete
3.3、添加
insert
-- 插入语句（添加）
-- insert into 表名([字段名，字段2，字段3])values('值1')，('值2')，('值3')，(...)
INSERT INTO `grade`(`gradename`)VALUES('大一')
-- 由于主键自增我们可以省略 (如果不写表的字段，他就会一一匹配)
INSERT INTO `grade` VALUES('大二')
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES('大二',null)
-- 一般写插入语句，我们一定要数据和字段一一对应！
-- 插入多个字段
INSERT into `grade`(`gradename`)VALUES('大二'),('大四')
INSERT INTO student(`name`)VALUES('张三')
INSERT INTO `student`(`name`,`pwd`,`sex`,`address`)
VALUES ('轩辕','00010','女','开天'),('王五','aaaaa','男','北京')
语法：insert into 表名([字段名，字段2，字段3])values('值1')，('值2')，('值3')，(...)
注意事项：
-
字段和字段之间使用 英文逗号 隔开
-
字段是可以省略的，但是后面的值必须要一一对应，不能少
-
可以同时插入多条数据，VALUES后面的值，需要使用 ，隔开即可
values(),()
3.4、修改
update 修改谁 (条件) set原来的值 = 新值
-- 修改学员的名字,带了条件 UPDATE student SET `name` = '小康' WHERE id = 1 -- 不指定条件的情况下，会改动所有表！ UPDATE student SET `name` = '黄河水' -- 修改多个属性，逗号隔开 UPDATE student SET `name`='轩辕',`email`='12321321@qq.com' WHERE id = 3 -- 语法： -- UPDATE 表名 SET `属性名`='属性值',[`属性名`='属性值'] WHERE [条件]
条件：where 子句 运算符 id等于某个值，大于某个值，在某个区间内修改...
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5-6 | false |
| <>或!= | 不等于 | 5<>6 | true |
| > | |||
| < | |||
| >= | |||
| <= | |||
| BETWEEN...AND... | 在某个范围内 | [2,5] | |
| AND | 我和你&& | 4>3 and 4>5 | false |
| OR | 我或你|| | 4>3 or 4>5 | true |
-- BETWEEN...AND... 在。。。之间用法
UPDATE student SET `name`='小芳' WHERE id BETWEEN 2 AND 4
-- 通过多个条件定位数据
UPDATE student SET `name`= '起飞' WHERE `name`= '小芳' and `sex` = '男'
UPDATE student SET `name`= '长江一号77' WHERE `name`= '起飞' OR `sex` = '男'
语法：UPDATE 表名 set colnum_name = value,[colnum_name = value,...] where [条件]
注意：
-
colnum_name 是数据库的列,尽量带上``
-
条件, 筛选的条件, 如果没有指定， 则会修改所有的列
-
value，是一个具体的值，也可以是一个变量
-
多个设置的属性之间，使用英文逗号隔开
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '匿名' AND sex='男'
3.5、删除
delete命令
语法:delete from 表名 [where 条件]
-- 删除数据，(避免这样写，会全部删除)
DELETE from `student`
-- 删除指定数据
DELETE from student WHERE id=5
TRUNCATE命令 truncate
作用：完全清空一个数据库表, 表的结构和索引约束不会变！
-- 清空 student 表 TRUNCATE `student` -- 清空 student 表 delete from `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('你好'),('你好'),('你好'),('你好'),('你好')
DELETE FROM `test` -- 再插入 不会影响自增
TRUNCATE TABLE `test` -- 再插入 自增会归零
了解即可：DELETE删除问题，重启数据库, 现象
-
InnoDB 自增量会从1开始 （存在内存中的，断电即失）
-
MyISAM 继续从上一个子增量开始 （存在文件中的，不会丢失）
4、DQL查询数据（最重点）
4.1、DQL
(Data Query LANGUAGE :数据查询语言)
-
所有的查询操作都用它 Selete
-
简单的查询，复杂的查询它都能做~
-
数据库中最核心的语言，最重要的语句
-
使用频率最高的语句
Select完整的语法:
SELECT语法
SELECT [ ALL | DISTINCT]
{*| table.* | [table.field1[as alias1][,table.field2[as alias2]][,.. .]]}
FROM table_name [as table_alias]
[1eft | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,] row_count | row_countOFFSET offset]];
--指定查询的记录从哪条至哪条
注意：[]括号代表可选的，{}括号代表必选的
4.2、指定查询字段
-- 查询所有的学生 SELECT 字段 from 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s
-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student
语法：SELECT 字段，... from 表
有的时侯，列名字不是那么见名知意。我们起别名 AS 字段名 as 别名 表名 as 别名
AS 起别名时 可以省略
去重 distinct
作用：去除select查询出来的结果中重复的数据，只显示一条
-- 查询一下有哪些同学参加了考试，成绩 SELECT * FROM result -- 查询全部的考试成绩 SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试 SELECT DISTINCT `StudentNo` FROM result -- 发现重复数据，去重
数据库的列（表达式）
SELECT VERSION() -- 查询系统版本 （函数） SELECT 1024*5 AS 计算结果 -- 用来计算 （表达式） SELECT @@auto_increment_increment -- 查询自增的步长 （变量） -- 学员成绩 +1 分查看 SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result
数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量....
select 表达式 from 表
4.3、where条件子句
作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成！结果 布尔值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与，两个都为真，结果为真 |
| or || | a or b a||b | 逻辑或，其中一个为真，则结果为真 |
| Not ！ | not a ！a | 逻辑非，真为假，假为真！ |
尽量使用英文字母
-- =================== where ======================
-- 查询考试成绩在 95~100 分之间
SELECT `StudentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 AND StudentResult <=100
-- and &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 && StudentResult <=100
-- 模糊查询（区间）
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult` BETWEEN 95 AND 100
-- 查询除了1000号学生以外的同学的成绩
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNO`!=1000
-- !=1000 not
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT`StudentNO`=1000
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| 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 | 若 a 匹配 b ，则结果为真 |
| In | a in (a1,a2,a3...) | 假设a在a1，或者a2....其中的每一个值中，结果为真 |
-- =================== 模糊查询 ======================
-- 查询姓王的同学
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（具体的一个或者多个值，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
*/
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
on s.StudentNo = r.StudentNo -- 19条数据，无空值
-- RIGHT JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo=r.StudentNo -- 19条数据，无空值
-- LEFT JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo=r.StudentNo -- 20条数据,有一位同学没参加考试，显示成绩为null
-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN
result r
ON s.StudentNo=r.StudentNo
WHERE StudentResult IS NULL -- 注意StudentResult is null能查出来 StudentResult=null是错误的写法
-- 思考题（查询了参加考试的同学信息:学号，学生姓名，科目名，分数)
/* 思路（）
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（两个表中哪个数据是相同的）
判断的条件：学生表中的 StudentNo = 成绩表中的 StudentNo
*/
SELECT s.StudentNo,StudentName,SubjectName,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo=r.StudentNo
INNER JOIN subject j
ON r.SubjectNo=j.SubjectNo
-- 我要查询哪些数据 select ...
-- 从那几个表中查 FROM 表 XXX Join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
-- FROM a LEFT JOIN b 左边的表为准
-- FROM a RIGHT JOIN b 右边的表为准
| 操作 | 描述 |
|---|---|
| Inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
MySQL中On和Where的区别：
-- join（连接的表） on（判断的条件） 连接查询
-- where 等值查询
-
先执行on，后执行where；on是建立关联关系在生成临时表时候执行，where是在临时表生成后对数据进行筛选的。
-
比如两个表进行left join连接查询结果，where会对查询的结果进行过滤， on会返回驱动表的所有数据。
-
left join按照条件关联，不管是否为真，都返回左边表的数据。
On和Where使用时机的分析
-
先抛开where条件，看On条件。left join时，不管on条件是否成了，左表的所有行数据都会select出来（不管右表条件是否成立），右表成立就select对应数据，不成立就置null。有可能是一对多。
-
当两张表是在left join来关联时，条件跟在on后面:无论on条件是否成立，都会返回左表的值；
-
当两张表是在left join来关联时，当条件放在where后面时：只有满足条件的记录会返回。
-
自连接(了解)
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
CREATE TABLE `category` ( `categoryid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主题id', `pid` int(10) NOT NULL COMMENT '父id', `categoryName` varchar(50) NOT NULL COMMENT '主题名字', PRIMARY KEY (`categoryid`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
父类
| 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 a,category b WHERE a.categoryid = b.pid
练习：
-- 查询学员所属的年级 （学号，学生的姓名，年级名称）,学号升序排列 SELECT studentNo,studentName,gradeName FROM student s INNER JOIN grade g ON s.GradeId=g.GradeId WHERE StudentNo ORDER BY `StudentNo` ASC -- 查询科目所属的年级（科目名称，年级名称） SELECT subjectName,gradeName FROM subject s INNER JOIN grade g ON s.gradeId=g.gradeId -- 查询了参加数据库结构-1 考试的同学信息: 显示学号，学生姓名，科目名，分数 SELECT s.studentNo,studentName,subjectName,studentResult FROM student s INNER JOIN subject sb ON s.gradeId= sb.gradeId INNER JOIN result r ON s.studentNo=r.studentNo WHERE `subjectName`='数据库结构-1'
4.5、分页和排序
排序
-- =============== 分页 limit 和排序 order by ================ -- 排序 ： 升序 asc 降序 desc -- ORDER BY 通过那个字段排序，怎么排 -- 查询的结果，根据成绩升序排列 SELECT s.studentNo,studentName,subjectName,studentResult FROM student s INNER JOIN subject sb ON s.gradeId= sb.gradeId INNER JOIN result r ON s.studentNo=r.studentNo WHERE `subjectName`='数据库结构-1' ORDER BY `StudentResult` ASC
分页
-- 100万条数据
-- 为什么要分页
-- 缓解数据库压力，给人的体验更好， 瀑布流
-- 分页，每页只显示五条数据
-- 语法：limit 起始值，页面的大小
-- 网页应用：当前页，总的页数，页面的大小
-- LIMIT 0,5 1~5
-- LIMIT 1,5 2~6
-- LIMIT 5,5 第二页的数据
SELECT s.studentNo,studentName,subjectName,studentResult
FROM student s
INNER JOIN subject sb
ON s.gradeId= sb.gradeId
INNER JOIN result r
ON s.studentNo=r.studentNo
WHERE `subjectName`='高等数学-1'
ORDER BY `StudentResult` ASC
LIMIT 5,5
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第n页 limit (n-1)*5,5 (n-1)*pageSize,pageSize
-- 【pageSize:页面大小】
-- 【(n-1)*pageSize：起始值】
-- 【n：当前页】
-- 【数据总数/页面大小 = 总页数】
语法：limit(查询起始下标，pageSize)
扩展题：
-- 思考：
-- 查询 JAVA第四学年课程成绩排名前十的学生，并且分数要大于80 的学生信息（学号，姓名，课程名称，分数)
SELECT s.studentNo,studentName,subjectName,studentResult
FROM student s
INNER JOIN subject sb
ON s.GradeId = sb.GradeId
INNER JOIN result r
ON s.StudentNo = r.StudentNo
WHERE `SubjectName`='JAVA第四学年' AND `StudentResult`>80
ORDER BY `StudentResult` DESC
LIMIT 0,10
4.6、子查询
where （这个值是计算出来的）
本质：在where语句中嵌套一个子查询语句
-- ================= where ==================
-- 1、查询数据库JAVA第四学年 的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一： 使用连接查询
SELECT studentNo,r.subjectNo,studentResult
FROM result r
INNER JOIN `subject` s
ON r.SubjectNo = s.SubjectNo
WHERE `SubjectName`='JAVA第四学年'
ORDER BY `StudentResult` DESC
-- 方式二：使用子查询（由里及外）
SELECT studentNo,subjectNo,studentResult
FROM result
WHERE subjectNo = (
-- 查询所有JAVA第四学年 的学生学号
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='JAVA第四学年'
)
ORDER BY `StudentResult` DESC
-- 查询课程为JAVA第四学年 且分数不小于80 的同学的学号和姓名
SELECT DISTINCT s.studentNo,studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sb
ON s.gradeId = sb.gradeId
WHERE `studentResult`>=80 AND `subjectName`='JAVA第四学年'
-- 分数不小于80分的学生的学号和姓名(联表查询)
SELECT DISTINCT s.studentNo,studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
WHERE `studentResult`>=80
-- 查询课程为JAVA第四学年 且分数不小于80 的同学的学号和姓名
-- 在这个基础上增加一个科目，JAVA第四学年
-- 查询JAVA第四学年 的编号
SELECT DISTINCT s.studentNo,studentName
FROM student s
INNER JOIN result r
ON s.studentNo = r.studentNo
WHERE `studentResult`>=80 AND `subjectNo`=(
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='JAVA第四学年'
)
-- 再改造（由里及外） （DISTINCT 去重，可用可不用）
SELECT studentNo,studentName FROM student WHERE studentNo IN(
SELECT studentNo FROM result WHERE `studentResult`>=80 AND `subjectNo`=(
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='JAVA第四学年'
)
)
练习：子查询没写出来
-- 练习：查询JAVA第四学年 前五名同学的成绩的信息（学号，姓名，分数） SELECT s.StudentNo,StudentName,StudentResult FROM student s INNER JOIN result r ON s.StudentNo = r.StudentNo INNER JOIN `subject` sb ON s.GradeId = sb.GradeId WHERE `SubjectName`='JAVA第四学年' ORDER BY `StudentResult` DESC LIMIT 0,5 -- 使用子查询 ?
4.7、分组和过滤
-- 查询不同课程的平均分，最高分，最低分,平均分大于80 -- 核心：（根据不同的课程分组） 聚合函数不能用where 只能用：having SELECT subjectName 科目名,AVG(studentResult) 平均分,MAX(studentResult) 最高分,MIN(studentResult) 最低分 FROM result r INNER JOIN subject sb ON r.SubjectNo = sb.SubjectNo GROUP BY `subjectName` HAVING AVG(studentResult) >80
4.8、 select 小结
5、MySQL函数
官网：
5.1、常用函数
-- ================= 常用函数 ==================
-- 数学运算符
SELECT ABS(-6) -- 绝对值
SELECT CEILING(5.1) -- 向上取整 6
SELECT FLOOR(5.9) -- 向下取整 5
SELECT RAND() -- 返回0-1之间的随机数
SELECT SIGN(1) -- 判断一个数的符号 0-0 负数为-1 正数为1
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('我','喜欢','这份工作') -- 拼接字符串
-- 1,2意思是 从第一个字符开始，替换掉两个，用后边的字符填充、
SELECT INSERT('我爱编程helloworld',1,2,'超级热心')
SELECT LOWER('WeiLeTa') -- 全部转小写
SELECT UPPER('WeiLeTa') -- 全部转大写
SELECT INSTR('weileta','i') -- 3 返回第一次出现的字串的索引
SELECT REPLACE('为了他','他','她') -- 替换指定字符串的匹配项 为了她
SELECT SUBSTR('狂神说努力就能成功',4,6) -- 返回指定的子字符串(从索引4开始取6个长度的字符)
SELECT REVERSE('爸爸你是我') -- 反转字符串中的字符 我是你爸爸
-- 查询姓 周的同学，性 走 REPLACE替换指定字符串的匹配项
SELECT INSERT(studentName,1,1,'走') FROM student
WHERE `studentName` LIKE '周%'
-- 两个相同点 ：查出来的能改名字，但是数据库里的数据并不会改动！
SELECT REPLACE(studentName,'周','走') FROM student
WHERE `studentName` LIKE '周%'
-- 时间和日期函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期 and 时间
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（） | 最小值 |
| ...... | ...... |
-- ================ 聚合函数 =============== -- 都能够统计 表中的数据（ 想查询一个表中有多少个记录，就使用这个count()） 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 AVG(studentResult) as 平均分 FROM result SELECT MAX(studentResult) as 最高分 FROM result SELECT MIN(studentResult) as 最低分 FROM result -- 查询不同课程的平均分，最高分，最低分,平均分大于80 -- 核心：（根据不同的课程分组） 聚合函数不能用where 只能用：having SELECT subjectName 科目名,AVG(studentResult) 平均分,MAX(studentResult) 最高分,MIN(studentResult) 最低分 FROM result r INNER JOIN subject sb ON r.SubjectNo = sb.SubjectNo GROUP BY `subjectName` HAVING AVG(studentResult) >80
5.3、数据库级别的MD5加密（扩展）
什么是MD5？（MD5信息摘要算法）
MD5由
MD5不可逆，具体的值的 md5 是一样的
MD5破解网站的原理，背后有一个字典，MD5加密后的值﹐加密前的值
-- ========== 测试MD5 加密 ===========
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL auto_increment,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(100) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5(`name`,`pwd`) VALUES('xiaolai','123456'),
('xiaofang','123456'),
('xiaoming','123456')
-- 加密
UPDATE testmd5 SET pwd = MD5(pwd) WHERE id = 1
UPDATE testmd5 SET pwd = MD5(pwd) WHERE -- 加密全部的密码
-- 插入的时候加密
INSERT INTO testmd5(`name`,`pwd`) VALUES('admin',MD5('00000'))
-- 如何效验：将用户传递进来的密码，进行md5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='admin' AND `pwd`=MD5('00000')
6、事务
6.1、什么是事务
要么都成功，要么都失败
一一一一一一
1、SQL执行 A 给 B 转账 A 1000 -->200 B 200
2、SQL执行 B 收到 A 的钱 A 800 一> B 400
一一一一一一
将一组SQL放在一个批次中去执行~
InnoBD支持事务，MYISAM最新版也支持事务了
事务原则：ACID原则 原子性，一致性，隔离性，持久性 （脏读，幻读......）
参考博客链接：https://blog,csdn.net/dengjili/article/details/82468576
原子性（Atomicity）
-
要么都成功，要么都失败!
一致性（Consistency
-
事务前后的数据完整性要保证一致，如（最开始俩人一共有1000，不可能最后的结果不是1000）!
隔离性（Isolation）
-
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离.
持久性（Durability）
-
事务一旦提交则不可逆，被持久化到数据库中!
隔离所导致的一些问题
脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。 （一般是行影响，如下图所示：多了一行）
执行事务
-- ================ 事务 ================
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启（默认的） */
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
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 utf8_general_ci
USE shop
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=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`='B'; -- A减500
UPDATE account SET money=money+500 WHERE `name`='B';-- B加500
COMMIT; -- 提交事务，就被持久化了！
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值（开启自动提交）
7、索引
MySQL官方对索引的定义为: 索引(Index)是帮助MySQL高效获取数据的数据结构。0.5s 0.00001s
提取句子主干，就可以得到索引的本质:索引是数据结构。
7.1、索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
-
主键索引（PRIMARY KEY）
-
唯一的标识，主键不可重复，只能由一个列作为主键
-
-
唯一索引（UNIQUE KEY）
-
避免重复的列出现，唯一索引可以重复，多个列都可以标识 唯一索引
-
-
常规索引（KEY / INDEX）
-
默认的，index,key 关键字来设置
-
-
全文索引（FullText）
-
在特定的数据库引擎下才有，MyISAM
-
快速定位数据
-
基础语法
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
USE school
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引（索引名） 列名
ALTER TABLE student ADD FULLTEXT INDEX studentName(studentName)
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('王')
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 '年龄',
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT 'app用户表'
-- 插入100 万条数据
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURN INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHERE i<num DO;
-- 插入语句
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户1','546545@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();
SELECT * FROM app user WHERE 'name' = '用户9999'; -- 0.993sec
SELECT * FROM app user WHERE 'name' = '用户9999'; -- 0.853se
SELECT * FROM app user WHERE 'name' = '用户9999'; -- 0.943se
EXPLAIN SELECT * FROM app user WHERE 'name' = '用户9999';
SELECT * FROM student
-- id_表名_字段名
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`)
SELECT * FROM app user WHERE 'name' = '用户9999'; -- 0.001sec
EXPLAIN SELECT * FROM app user WHERE 'name' = '用户9999';
索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显~
自己总结：
数据量巨大的时候：有索引差的快，像坐火箭，没索引查的慢，像在步行
在小数据量的时候，用处不大
7.3、索引原则
-
索引不是越多越好
-
不要对经常变动的数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上
索引的数据结构
Btree： InnoDB 的默认数据结构~
阅读：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
8、权限管理和备份
8.1、用户管理
SQL yog可视化管理
SQL命令操作
用户表：mysql.user
本质：读这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER weileta IDENTIFIED BY '00000'
-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR seileta = PASSWORD('00000')
-- 重命名 RENAME USER 原来名字 TO 新名字
RENAME USER weileta TO weileta2
-- 用户授权 GRANT ALL PRIVILEGES 全部的权限，库，表
-- ALL PRIVILEGES 除了给别人授权，其他的都能干
GRANT ALL PRIVILEGES ON *.* TO weileta2
-- 查询权限
-- GRANT ALL PRIVILEGES ON *.* TO 'weileta2'@'%'
SHOW GRANTS FOR weileta2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- ROOT用户权限 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM weileta2
-- 删除用户
DROP USER weileta
9、规范数据库设计
10、JDBC（重点）
