Day24 2021.4.10 MySQL上

Day24 2021.4.10

1.初始MySQL

JavaEE：企业级java开发 web

前段(页面：展示 数据)

后台(连接点：连接数据库JDBC，链接前段(控制，控制视图跳转，和给前段传递数据))

数据库(存数据，Txt，Excel，word)

1.1 什么是数据库

数据库（DB，DataBase）

概念：数据仓库，软件，安装在操作系统之上 SQL可以存储大量的数据

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

关系型数据库： （SQL）

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

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

• Redis、MongDB
• 对象存储，通过对象的自身属性来决定

1.2 DBMS

数据库管理系统

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

1.3 MySQL

​ MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品，是最流行的关系型数据库管理系统之一。

​ MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言，由于其体积小、速度快、开放源码等特点，一般中小型网站的开发都选择MySQL作为网站数据库。

2.基本命令行操作

命令行连接

mysql -u -p  --连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';  --修改密码

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

mysql> use school
Database changed  --使用use命令切换数据库
--------------------------------------------
show tables;  --查看数据库中所有的表

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

create database westos; --创建一个数据库
--------------------------------------------
exit;  --退出连接
--单行注释
/*sql的多行注释*/

DDL 定义

DML 操作

DQL 查询

DCL 控制

3.操作数据库

3.1操作数据库基础

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

mysql关键字不区分大小写

3.1.1创建数据库

CREATE DATABASE IF NOT EXISTS westos

3.1.2删除数据库

DROP DATABASE IF EXISTS westos

3.1.3使用数据库

--如果表名或者字段名是一个特殊符号就需要带 ` `
USE school

3.1.4查看数据库

SHOW DATABASE  --查看所有数据库

3.2数据库的列类型

3.2.1数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数(金融计算)

3.2.2字符串

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

3.2.3时间日期

• date YYYY-MM-DD 日期格式
• time HH:mm:ss 时间格式
• datetime 最常用的时间格式
• timestamp 时间戳，1970.1.1到现在的毫秒数
• year 年份表示

3.2.4null

• 没有值
• 计算后结果为null

3.3数据库的字段属性(重点)

3.3.1 Unsigner

• 无符号的整数
• 声明为该列不能声明为负数

3.3.2 zerofill

• 0填充的
• 不足的位数，使用0来填充

3.3.3 自增

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

3.3.4非空 null not null

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

3.3.5 默认

• 设置默认的值

3.4创建数据库表

--AUTO_INCREMENT 自增
--PRIMARY KEY 主键
CREATE TABLE `student1` (
`id`  int NOT NULL COMMENT '学号' ,
`name`  varchar(255) NOT NULL DEFAULT '匿名' COMMENT '姓名' ,
`pwd`  varchar(255) NOT NULL DEFAULT '123456' COMMENT '密码' ,
`sex`  varchar(255) NOT NULL DEFAULT '男' COMMENT '性别' ,
PRIMARY KEY (`id`)
)

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

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student1 -- 查看创建表的语句
DESC student --显示表的结构

3.5数据表的类型

3.5.1数据库引擎

InnoDB 默认使用

MyISAM 早些年使用的

	MyISAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持(表锁)	支持(行锁)
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规使用操作：

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

在物理空间存在的位置：

​ 所有的数据库文件都存在data目录下

在物理文件上的区别

• InnoDB在数据库中只有一个*frm文件，以及上级目录下的lbdata1文件
• MyISAM 对应很多文件

3.6修改删除表

-- 修改表名 ALTER TABLE 旧表名 RENAME as 新表名
ALTER TABLE teacher RENAME as teacher1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段 (重命名，修改约束)
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 重命名
-- 删除 表的字段
ALTER TABLE teacher1 DROP age1

4.数据管理

4.1外键

方式一：在创建表的时候，增加约束

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xzHvQx0n-1618043550313)(C:\Users\asus\AppData\Roaming\Typora\typora-user-images\image-20210410145843019.png)]

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cMAKXFF7-1618043550315)(C:\Users\asus\AppData\Roaming\Typora\typora-user-images\image-20210410150109244.png)]

以上都是物理外键

删除有外键关系的表的时候，必须先删除从表，再删除主表

最佳实践：

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

4.2 DML(数据管理)

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

DML：

• insert

• update

• delete

4.2.1添加(insert)

-- 插入语句(添加)
-- insert into 表名([字段名1,字段名2，...])values('值1','值1'),('值2','值2'),(...),...

4.2.2修改(update)

-- 修改学员名字
-- update 表名 set 列名=新值 where [条件]
--update `student` set `name`='hong' where id = 1;

条件： where子句运算符

操作符会返回boolean值

操作符	含义	范围	结果
=	等于	5=6	false
<> / !=	不等于	5<>6	true
>	大于	4>6	false
<	小于	4<6	true
>=	大于等于	4>=6	false
<=	小于等于	4<=6	true
BETWEEN…AND…	在某个范围内
AND	和
OR	或

4.2.3删除

-- delete 删除
-- delete from 表名 [where 条件];
----------------------------------------------------
-- truncate 完全清空一个数据库表，表的结构和索引约束不会变
-- truncate 表名

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

5.DQL(数据查询)

• 所有的查询操作都用它 select
• 简单的查询，复杂的查询都能做
• 数据库中最核心的语言

5.1 简单查询 select

-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `` FROM student
-- 别名，给结果起一个名字 AS
SELECT `` AS `别名` FROM student AS s
-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT(a,b) as `新名字` FROM student AS s

5.2 去重 distinct

-- 查询一下有哪些同学参加了考试,成绩
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

5.3 where条件子句

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

逻辑运算符

运算符	语法	描述
and	a and b	逻辑与
or	a or b	逻辑或
Not	not a	逻辑非

--select `studentNo` from `student` where id = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fc8AgUeq-1618043550316)(C:\Users\asus\AppData\Roaming\Typora\typora-user-images\image-20210410155820011.png)]

5.4 模糊查询

运算符	语法	描述
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在括号中的某个值，则真
%	a%	代表0到任意个字符(以a开头)
_	a_	代表一个字符(a开头，两个字)

5.5联表查询(JOIN)

JOIN对比

1.内连接

内连接查询出的数据是两张表的交集，即上图中红色所表示的部分。

2.左外连接

左外链接如图中红色部分的内容，即包含左边表的全部行（不管右边的表中是否存在与它匹配的行），和右边表中全部匹配的行。

3.右外链接

右外链接如图中红色部分的内容，即包含右边表的全部行（不管左边的表中是否存在与它匹配的行），和左边表中全部匹配行。

4.左连接

左连接如图中红色部分的内容，查询出左表独有的数据

解析:其实上图就是在左外连接的基础上进行的，左外连接得到了左表，但是左表里还包含了一部分左右表都具有相同数据的区 域，这时需要将这部分相同数据去掉，去除的条件就是B.key IS NULL

5.右连接

如果看懂了上面的左连接，那么右连接大同小异，就是查询出右表独有的数据

6.全连接(Mysql中不支持下图写法)

查询出左右两表的所有数据

但是！MySQL中并不支持这种写法，所以只能通过别的方法。

A、B的所有也就是A的独有、B的独有 和A、B的共同拥有的数据

Mysql中可以使用：

select * from Table A left join Table B on A.Key = B.Key       --(找出A的所有)
union	--(去重)
select * from Table A right join Table B on A.Key = B.Key       --（找出B的所有)

7.全外连接(Mysql中不支持下图写法)

左右表的共有数据之外的数据查询

筛选出对于A表而言B为空，对于B表而言A为空的

MySQL中也不支持这种写法，所以只能通过别的方法。

其实全外连接也就是A的独有+B的独有

Mysql语法：

select * from Table A left join Table B on A.Key = B.Key  where B.Key is null      --(找出A的独有)
union            --(去重)
select * from Table A right join Table B on A.Key = B.Key where A.Key is null       --（找出B的独有)

案例分析

操作	描述
INNER JOIN	如果表中至少有一个匹配，就返回
LEFT JOIN	即使右表中没有匹配，也会返回左表中的全部值
RIGHT JOIN	即使左表中没有匹配，也会返回右表中的全部值

-- 查询参加了考试的同学(学号，姓名，科目编号，分数)
SELECT * FROM student
SELECT * FROM result

/*
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
确定交叉点(这两个表中哪个数据是相同的)
判断条件：学生表中 studentNo = 成绩表 studentNo
*/
-- INNER JOIN 交集 where 等值查询    on 连接查询
SELECT s.studentNo,studentName,`subjectNo`,studentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo = r.studentNo

-- RIGHT JOIN   on是先筛选后关联(批量)，where是先关联后筛选(单个)
SELECT studentNo,studentName,`subjectNo`,studentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo 

-- LEFT JOIN   on是先筛选后关联(批量)，where是先关联后筛选(单个)
SELECT studentNo,studentName,`subjectNo`,studentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo 

询
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo = r.studentNo

– RIGHT JOIN on是先筛选后关联(批量)，where是先关联后筛选(单个)
SELECT studentNo,studentName,subjectNo,studentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo

– LEFT JOIN on是先筛选后关联(批量)，where是先关联后筛选(单个)
SELECT studentNo,studentName,subjectNo,studentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo