MySql_1

1. 初始MySQL

javaEE：企业级java开发 ，主要做Web开发

前端：（页面：展示，获取输入）

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

数据库：存数据

1.1 为什么学数据库

1. 岗位需求
2. 大数据时代，得数据库得天下
3. 被迫需求：大量数据需要存储，
4. 数据库是所有软件体系中最核心得存在

1.2 什么是数据库

数据库（DB DataBase）

概念：数据仓库，软件，安装在操作系统之上。

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

1.3 数据库分类

关系型数据库：Excel（行、列）

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

非关系型数据库：（NoSQL Not Only）

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

DBMS数据库管理系统

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

1.4 MySQL

使用SQL语言的关系型数据库管理系统，分为社区版和商业版，开放源码。

1.5 安装MySQL

https://blog.csdn.net/qq_33369905/article/details/105828923

• 需要用管理员身份运行cmd，cd /d切换到bin目录
• 启动：net start mysql
• 结束：net stop mysql / exit

1.6 安装SQLyog

1. 新建一个数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-464A0QN3-1601040450875)(picture/image-20200804224133964.png)]

2. 新建一个表student

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vpyuXxpr-1601040450878)(picture/image-20200804224620121.png)]

3. 查看表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tk1tnInW-1601040450881)(picture/image-20200804224848570.png)]

4. 添加记录到表中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MMXdgjf7-1601040450884)(picture/image-20200804225322881.png)]

1.7 连接数据库

命令行连接数据库：配置好环境变量时

mysql -u root -p123456
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
--更改用户密码、用户名
--两个横线为单行注释
/*
多行注释
*/
--所有的sql语句都要使用分号结尾。

flush privileges; --刷新权限
show databases;   --查看所有数据库

use school; --切换数据库 ues 库名;
show tables;--查看数据库中所有的表
describe student;--显示数据库中所有的表信息

create database westos;--新建数据库 create database 库名

exit; --退出连接

数据库 XXX语言 CRUD增删改查！

DDL 定义语言

DML 管理语言

DQL 查询语言

DCL 控制语言

2. 操作数据库

操作数据库 > 操作表 > 操作表中的数据（字段）

MySQL 关键字不区分大小写

2.1 操作数据库

1、创建数据库

create database [if not exists] westos;
create shop character set utf8 collate utf8_general_ci;

2、删除数据库

drop database if exists westos;

3、使用数据库

--如果你的表名或字段名是一个特殊字段(如user)，就需要带 ''
use `school`;

4、查看所有的数据库

show databases;

2.2 数据库的列类型

数值

• tinyint 十分小的数据 1字节
• smallint 较小的数据 2字节
• mediumint 中等大小 3字节
• int 标准的数据 4字节
• bigint 较大的数据 8字节
• float 浮点数 4字节
• double 8字节
• decimal 字符串形式的浮点数，用于金融计算。（9，2）9位长，其中两位为小数

字符串

• char 字符串固定大小的 0-255 1字节
• varchar 可变长字符串 0-65535 2字节
• tingtext 微型文本 2^8-1个字
• text 文本串，大型数据 2^16-1字

时间日期

• data YYYY-MM-DD 日期格式
• Time HH:mm:ss 时间格式
• datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 时间戳 1970年初至今的毫秒数！
• year 年份表示

null

• 未知
• 注意，不要使用null进行运算，结果一定为null

2.3 数据库的字段属性

Unsigned：

• 无符号整数，不能为负数
• 勾选后该列不能为负数

zerofill：

• 0填充
• 不足的位数使用0来填充，如：int 5位，5-- 00005

自增：

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KfLwNU9J-1601040450886)(picture/image-20200805132854961.png)]

非空：NULL not null

• 假设设置为not null，如赋值就会报错
• 不打勾为NULL，不赋值默认为NULL

默认

• 设置默认值
• sex ，默认为boy，不指定该列默认为boy

主键

• 每个表只能定义一个主键。
• 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。
• 一个字段名只能在联合主键字段表中出现一次。
• 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

扩展：

--每一个表，都必须包含以下五个字段，未来做项目用，表示一个记录存在的意义
id        主键  
'verson'  乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间

2.4 创建数据库表DDL

Data Definition Language

not null --非空
auto_increment  --自增
comment '注释'   --注释
default '默认值' --默认值
primary key ('id')--主键，唯一标识
--注意区分单引号和反单引号，单引号用于字符串，反单引号用于字段、表名
--
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'性别',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

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

逆向操作：

SHOW CREATE DATABASE school
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */

--可以先用软件创建，然后show一下

SHOW CREATE TABLE student
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

DESC student --展示数据表格式

2.5 数据表类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-w3rKpcci-1601040450888)(picture/image-20200806094251897.png)]

/*
数据库引擎
INNIDB
MYSAM
*/

	InnoDB	MyISAM
事务支持	支持	不支持
数据行锁定	支持（锁单行）	不支持（锁表）
外键约束	支持	不支持
全文索引	不支持	支持
表空间大小	较大（比MYSAM大两倍）	较小

常规使用操作：

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

在物理空间中的位置

所有的数据库文件都在data文件夹下，一个文件夹对应一个数据库

本质还是文件的存储

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

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

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

CHARSET=utf8

MySQL默认字符编码不支持中文

在创表时需要添加编码规则

2.6 修改删除表

--修改表名
alter table `teacher` rename as `teachers`;

--添加表的字段
alter table `teacher1` add `age` int(4)

--修改表的字段（重命名，约束）
alter table `teacher1` modify `age` varchar(11) --约束
alter table `teacher1` change `age` `age1`      --重命名

--删除表的字段
alter table `teacher` drop `age1`

--删除表(如果存在)
drop table if exists `teacher`

注意点：

• 字段名用反引号包裹
• 注释 – /**/
• sql大小写不敏感，建议小写，容易分辨
• 所有符号又要用英文

3. MySQL数据库

3.1 外键

方式一

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(10) 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'学生年级',
	PRIMARY KEY(`id`),
	KEY `FK_gradeid`(`gradeid`),
	constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除表要先删除student这样的从表，在上出被引用的grade表（主表）

方式二

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(10) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET = utf8

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'学生年级',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

以上外键都是物理外键，不建议使用，会造成数据库彼此之间引用过多。

最佳实践

• 数据库就是单纯的表，只用来存储数据，只有行（数据）和列（字段）
• 如果想使用多张表的数据，可以使用逻辑外键（程序实现）

3.2 DML语言（重点）

数据库管理语言

Data Management Language

• insert
• update
• delete

3.3 添加

insert into 表名([字段名1，字段名2，字段名3])values(`值1`)，(`值2`),(`值3`);
-- 写插入语句，一定要字段和数据一一对应！
-- 为表单独的字段添加单行、多行信息
INSERT INTO `grade`(`gradename`)VALUES('大四');
INSERT INTO `grade`(`gradename`)VALUES('大一'),('大二');
-- 为表的 多列字段 添加 多行信息
INSERT INTO `grade`(`gradeid`,`gradename`)VALUES( 1 ,'大一'),( 2 ,'大二');

--省略写法，需要将每列的完整信息写入
INSERT INTO `grade` VALUES( 1 ,'大一'),( 2 ,'大二');

注意事项：字段之间要用逗号隔开

3.4 修改

--指定表的字段和选择条件
update `student` set `name` = 'BuYi' where id = 1;

--指定表单字段，不加选择条件会默认全部，狼人行为
update `student` set `name` = 'BuYi';

update `student` set `name` = 'BuYi',`pwd` = 10 where id = 1;
--格式
update 表名 set 列名=值,[列名=值] where 列名 = 值;
where id between 1 and 3

-- 多条件定位
UPDATE `student` SET `name` = 'Bll',`pwd` = 19 WHERE id BETWEEN 1 AND 2 AND `gradeid`=3;

--变量赋值
UPDATE `student` SET `pwd` = CURRENT_TIME WHERE id =1;

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

操作符会返回布尔值

操作符	含义	范围	结果
=	等于	5 = 8	false
<>或！=	不等于	4<>4	true
<
>
<=
>=
BETWEEN(between and)AND	闭合区间	between 3 and 9	[3,9]
AND	&&与	5>1 AND 4<2
OR	||或	OR

3.5 删除

delete 命令

语法：`delete from 表名[where]

delete from `student` ; -- 删库跑路

--删除指定数据
delete from `student` where id =1;

truncate 命令

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

truncate `student` -- 删库跑路

delete 和 truncate区别

• 相同点：都能删除数据，但不会删除表结构
• 不同点：
  • truncate 重新设置 自增列 计数器归零
  • truncate 不会影响事务
  • delete 不清空计数器

了解：delete删除问题 ，重启数据库后的现象

• innoDB引擎 自增列会从1重新开始，因为存在内存中，重启后便消失了
• MyISAM引擎 自增列会从上一个自增量继续，因为存在文件中，重启后不会丢失

4. DQL查询数据（重点）

4.1 DQL

（Data Query Language：数据查询语言）

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

4.2 指定查询字段select

-- select 字段 from 表
SELECT * FROM student

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

顺序很重要
select 去重 要查询的字段
from 表
xxx join 表
on 等值判断
where 具体的值，子查询语句
group by 通过哪个字段分组
having 过滤分组后的信息
order by 排序
limit startindex，pagesize

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vjujQOuJ-1601040450889)(picture/image-20200806205218875.png)]

别名：给结果起个新名字，让列名更容易理解 as

可以给字段、表等起别名
SELECT `studentno` as `学号`,`studentname` as `姓名` FROM student

--函数
select concat('姓名：',studentname) as `新名` from `student`

去重 distinct

作用：对select查询的结果去重

select distinct `studentno` from `student` 
-- 以去重的结果输出

数据库的列（表达式）

SELECT VERSION() -- 查询系统版本（函数）
SELECT 100*4-1 AS `计算结果` -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增步长（变量）

SELECT `studentno`,`studentresult`+10 AS '提分后' FROM result

数据库中的表达式：文本值、列、Null、函数、计算表达式，系统变量

4.3 where条件子句

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

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	与
or ||	a or b a || b	或
not ！	not a ！a	非

-- 列字段不区分大小写
SELECT `studentno`,`Studentresult` FROM result WHERE studentresult>50 AND studentresult<=90

-- 模糊查询(区间)
SELECT `studentno`,`Studentresult` FROM result WHERE studentresult BETWEEN 10 AND 90

-- 查询除了1000之外的学生的成绩
SELECT `studentno`,`Studentresult` FROM result WHERE studentno != 1000
SELECT `studentno`,`Studentresult` FROM result WHERE NOT studentno = 1000

4.4 模糊查询

运算符	语法	描述
is null	a is null	如果a为null，结果为真
is not null	a is not null	如果a不为null，结果为真
between… and	a between b and c	如果a在b到c之间，结果为真
like	a like b	如果a能匹配到b，结果为真
in	a in （a1,a2,a3)	如果a在a1，a2…其中某个值中，结果为真

-- 模糊查询
-- 查询姓刘的同学
-- ===============like=====================
-- like结合%（代表0到任意个字符） _（一个字符）
SELECT `studentno`,`studentname`FROM `student`
WHERE `studentname` LIKE '赵%';
SELECT `studentno`,`studentname`FROM `student`
WHERE `studentname` LIKE '赵_';
-- 查询名字中间有'日'的人
SELECT `studentno`,`studentname`FROM `student`
WHERE `studentname` LIKE '%日%';

-- ================in======================
-- in操作符(具体的值） 查询学号为1000 1003 1002的人
SELECT `studentno`,`studentname`FROM `student`
WHERE `studentno` IN (1002,1003,1000)

-- 查询在北京的学生
SELECT `studentno`,`studentname`,`address`FROM `student`
WHERE `address` IN('北京朝阳','北京通州')

-- ============is null======================
-- 查询地址为空的学生 null ''
SELECT `studentno`,`studentname`,`address`FROM `student`
WHERE `address`='' OR `address` IS NULL

SELECT `studentno`,`studentname`,`address`FROM `student`
WHERE  `address` IS NOT NULL

4.4 联表查询

jion 对比

本质有三种

• inner join
• left join
• right join

扩展有七种

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dLpq4Pso-1601040450891)(picture/image-20200807104210826.png)]

-- ===========联表查询=================
-- 查询参加考试的同学（学号、姓名、考试编号、分数）
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
ON s.studentno = r.studentno

-- right join  as 可省略

SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno

-- left join 
-- 左连接：以左表为主表，然后根据on的字段，去右表匹配，结果一定包含左表的全部数据，右表的字段只显示能和左表匹配上的（符合on的），如果不符合on的则为null
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno

-- 查询参加考试的同学的信息
-- 首先要包含所有的学生信息，也就是student表全部，去和考试结果result匹配
-- 然后根据课序号去和课程表匹配
SELECT s.studentno,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FgPriun0-1601040450892)(picture/image-20200807134223629.png)]

自连接

自己的表和自己的表连接，一张表拆成两张一样的表

父类表

categoryid 自己id	categoryname
2	信息技术
3	软件开发
5	美术设计

子类表

pid 父id	categoryid 自己id	categoryname
2	8	办公信息
3	4	数据库
3	6	web开发
5	7	ps技术

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

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

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

原表[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RhO2gq2E-1601040450893)(picture/image-20200807173157427.png)]

查询后的表[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rI9mSYbK-1601040450898)(picture/image-20200807173221324.png)]

4.5 分页和排序

排序

-- 排序order by
-- 升序ASC 降序DESC
-- 语法：order by 字段 升降序
SELECT s.studentno,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
ORDER BY `studentresult` ASC

分页

作用：过多的数据无法显示在一张网页上，为了换届数据库压力，给人更好的使用体验，可以设置分页

SELECT s.studentno,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
ORDER BY `studentresult` ASC
LIMIT 0,7
-- 分页，煤业只显示五条数据
-- 语法：limit 起始数据行值，单页总容量
-- 网页应用：当前页，总页数，页面容量

-- 第N页， 起始数据是 第【（N-1）*单页总容量 】条
-- 总页数：【数据总数/单页总容量】

习题：

-- 查询 高等数学1 课程成绩排名前十的学生，并且分数大于50的学生信息（学号、姓名、课程名称、分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE `subjectname` = '高等数学-1' AND `studentresult`>=50 --筛选
ORDER BY `studentresult` DESC --排序
LIMIT 0,10 --分页

4.6 子查询

where（）

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

-- 方式一
-- ============查询'高等数学-1'的所有考试结果（学号，科目名字，成绩），降序排序
SELECT `studentno`,r.`subjectno`,`studentresult`,`subjectname`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '高等数学-1'
ORDER BY studentresult DESC

-- 方式二：使用子查询（由里到外）
-- 此种方法只能查询result里面的字段
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
WHERE subjectno = (
	SELECT subjectno FROM `subject`
	WHERE `subjectname` = '高等数学-1'
)
ORDER BY `studentresult` DESC

练习题

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT `studentno`,`studentname`
FROM `student` 
WHERE studentno = ( -- 此种方式只能返回一个studentno，所以无法包含多条的数据
	SELECT `studentno` FROM`result`
	WHERE `studentresult`=70
)
-- 改造方法
SELECT DISTINCT `studentno`,`studentname`
FROM `student` 
WHERE studentno in(
	select studentno from result where studentresult>80
    )
-- 继续增加条件 学课名
SELECT DISTINCT `studentno`,`studentname`
FROM `student` 
WHERE studentno in(
	select studentno from result where studentresult>80 and subjectno = (
    	select subjectno from `subject` where subjectname = '高等数学-1' )
)


-- 分数不小于80分的高数1 学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student`  s
INNER JOIN result r
ON r.`studentno` = s.`studentno`
WHERE studentresult >=80 AND subjectno = (
	SELECT `subjectno`FROM `subject`
	WHERE subjectname = '高等数学-1'
)