MySQL 数据库（上）

MySQL 数据库（上）
初识MySQL

JavaEE：企业级开发 web
前端（页面：展示，数据）
后台（连接点：连接数据库JDBC，链接前端(控制，控制视图跳转,和给前端传递数据)）
数据库（存数据）

数据库

DB database
概念：数据仓库，软件，安装在操作系统（windows，linux，mac...）之上
作用：存储数据，管理数据

数据库分类
- 按关系
1. 关系型数据库(SQL) MySQL，Oracle，SQL server，DB2，SQLlite
  通过表和表之间，行和列的关系进行数据的存储
2. 非关系型数据库(NoSQL not only sql) Redis，MongDB
  对象存储，通过对象的自身属性来决定
- DBMS 数据库管理系统
  数据库的管理软件，科学有效的管理我们的数据，维护和获取数据
  MySQL，是个DBMS
MySQL
- MySQL 是一种开放源代码的关系型数据库管理系统
  体积小、速度快、总体拥有成本低
  5.7 稳定
  8.0 新的
- 安装
1. 尽量不要用exe，注册表，卸载不干净
2. 尽可能使用压缩包安装
- 可视化工具Navicat中创建数据库
  右键数据库链接 --> 新建数据库
对应的创建数据库的语句：
CREATE DATABASE 'school' CHARACTER SET utf8 COLLATE utf8_general_ci;
工具-->历史日志(Ctrl+L)：能看到复杂操作的MySQL语句
- 新建表
键表示“主键”，不能重复
历史记录中的具体代码操作：
```
CREATE TABLE `school`.`student`  (
  `id` int(10) NOT NULL COMMENT '学生id',
  `name` varchar(100) NOT NULL COMMENT '学生姓名',
  `age` int(3) NOT NULL COMMENT '学生年龄',
  PRIMARY KEY (`id`)
)
```
- 打开表
  右键，打开表，像excel一样添加和删除数据
- 连接数据库
```
C:WINDOWSsystem32>mysql -uroot -p8098 //连接
mysql> exit; //断开
Bye
```
sql的注释：
单行 --
多行 /* */
常用命令：
```
mysql> show databases; --显示所欲的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school; --切换到指定数据库
Database changed
mysql> show tables; --显示当前数据库下的表
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> describe student; --查看表
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | int(3)       | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create database westos; --创建新的数据库
Query OK, 1 row affected (0.00 sec)
```
- 数据库xxx语言 CRUD 增删改查
  DDL 定义
  DML 操作
  DQL 查询
  DCL 控制
操作数据库

操作数据库>操作数据库中的表>操作数据库中的表的数据
MySQL语句不分大小写

操作数据库
- 创建
  CREATE DATABASE if not EXISTS westos;
- 删除
  Drop DATABASE if EXISTS westos;
- 学习思路
  可视化操作，查看日志里的语句
数据库的列类型

常用的为加粗
- 数值
  tinyint 十分小的数据 1个字节
  smallint 较小的数据 2个字节
  mediumint 中等大小的数据 3个字节
  int 标准的整数 4个字节
  bigint 较大的数据 8个字节
  float 单精度浮点数 4个字节
  double 双精度浮点数 8个字节（精度问题）
  decimal 字符串形式的浮点数金融计算时使用
- 字符串
  char 字符串固定大小的 0-255
  varchar 可变字符串 0-655635
  tinytext 微型文本 2^8-1
  text 文本串 2^16-1
- 时间日期
  date YYYY-MM-DD，日期
  time HH:MM:SS，时间
  datatime YYYY-MM-DD HH:MM:SS 日期+时间，最常用
  timestamp 时间戳 1970.1.1到现在的毫秒数
  year 年份
- null
  没有值，位置
  不用使用NULL进行运算，结果肯定为NULL
数据库的字段属性※
- Unsigned
  无符号的整数，声明该列不能声明为负数
- zerofill
  不足的位数使用0来填充，（如，int类型长度3，5会写成005）
- 自增
  自动在上一条记录的基础上+1，通常用来设计主键
  可以自定义设计主键自增的起始值和步长
- 非空
  NOT NULL，不允许为空
- 默认
  设置默认的值，不指定则为默认的值
- 拓展
```
每一个表，都必须存在以下五个字段!未来做项目用的，表示一个记录存在意义!
id 主键
`version` 乐观锁
is delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
```
创建表
- 格式
```
CREATE TABLE [IF NOT EXIST] `表名`(
    '字段名' 列类型 [属性] [索引] [注释],
    字段名' 列类型 [属性] [索引] [注释],
    ... ...
    字段名' 列类型 [属性] [索引] [注释],
    [PRIMARY KEY(`字段名`)]
)[表类型] [字符集类型] [注释]
```
- 逆向查语句
- 显示表的结构
数据表的类型
- 数据表的类型，即数据库引擎
  INNODB：默认，安全性高，事务的处理，多表多用户处理
  MYISAM：早些年使用的，节约空间，速度较快
- 在物理空间存在的位置
  所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库，本质还是文件的存储!
  MySQL引擎在物理文件上的区别
  ●InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata文件
  ●MYISAM对应文件
  *.frm 表结构的定义文件
  *.MYD 数据文件(data)
  *.MY| 索引文件(index)
修改删除表

字段名加上tab键上面的符号包住
所有操作加上 IF EXISTS 防止报错
```
# 修改表名 
ALTER TABLE `studentinf` RENAME AS `student`;
# 增加表的字段
ALTER TABLE `student` ADD `score` INT(3);
# 修改表的字段：1.重命名 2.修改约束
ALTER TABLE `student` MODIFY `score` SMALLINT(3);
ALTER TABLE `student` CHANGE `score` `degree` INT(1);
# 删除表的字段
ALTER TABLE `student` DROP `degree`;
# 删除表
DROP TABLE IF EXISTS `ke`;
```
MySQL数据管理

外键
- 方式一 (一般不用)
  在创建表的时候，增加约束(麻烦，比较复杂)
```
# 创建时添加
KEY `FK_gradeid` (`gradeid`) ,
CONSTRINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
```
删除有外键关系的表的时候，必须要先删除引用别人的表(从表) ，再删除被引用的表(主表)
- 方式二 ※
  在创建表的时候，没有外键关系，创建表成功后添加外键约束
```
# 单独加的语句
ALTER TABLE `student`
ADD CONSTRINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
```
- 以上两种方式的操作都是物理外键，数据库级别的外键，我们不建议使用! (避免数据库过多造成困扰，了解即可)
- 最佳实践
1. 数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)
2. 我们想使用多张表的数据，想使用外键(程序去实现)
DML全部记住
- 数据库意义
  数据存储，数据管理
- DML语言：数据操作语言
  insert update delete
添加
```
# 插入语句（添加）
# INSERT INTO 表名(字段1,字段2...)VALUES(值1,值2...)
INSERT INTO `student`(`name`)VALUES('小明');
# 主键自增，所以我们可以省略
# 数据和字段一定要一一对应
INSERT INTO `student`(`name`,`sex`)VALUES('小刚','男');
INSERT INTO `student`(`name`,`sex`,`password`)
VALUES('张三','男',5678)
# 字段可以不写，但值要写全，并且一一对应
# 可以同时插入多条数据，VALUES 后面的值，需要使用，隔开即可 VALUES(),(),//...
INSERT INTO `student`(`name`)
VALUES('TOM'),('JERRY');
```
修改
```
# 修改语句 修改条件 设置新值
# 语法 UPDATE 表名 SET 列名=新值[,列名=新值,...] [WHERE 条件];
UPDATE `student` SET `name`='小红' WHERE id = 1;
```
- 条件
  WHERE 子句运算符id等于某个值，大于某个值，在某个区间内修改...
  操作符返回布尔值，确定要不要执行
删除
- 删除数据
```
# 删除数据
# 语法 DELETE FROM 表名 [WHERE 条件]
DELETE FROM `student` WHERE `password`=5678;
```
- 清空
```
DELETE FROM `student`; --不加约束，全删了
TRUNCATE `student`; --也是清空，推荐使用
```
- delete的TRUNCATE区别
  相同点：都能删除数据，都不会删除表结构，不会影响自增
  不同：
  TRUNCATE 重新设置自增列计数器会归零
  TRUNCATE 不会影响事务
DQL查询数据※※

DQL
- Data Query Language
1. 所有的查询操作都用它Select
2. 简单的查询，复杂的查询它都能做~
3. 数据库中最核心的语言，最重要的语句
4. 使用频率最高的语句
指定查询字段
- SELECT完整语法
- 查询语法
  SELECT 字段,... FROM 表
- 查询所有
  SELECT * FROM 表
- 给结果起别名 AS
  SELECT 字段 [AS 字段别名] FROM 表 [AS 表别名]
- 函数 CONCAT(a,b)
  SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student
- 去重 distinct
  查询哪些同学参加了考试
  SELECT * FROM result --查询全部考试成绩
  SELECT StudentNo FROM result 有重复
  SELECT DISTINCT StudentNo FROM result 去重
- 用来计算
  SELECT 100*3-1 AS 计算结果
- 查询自增步长（变量）
  SELECT @@auto_increment_increment
- 所有学生考试成绩 +1分查看
  SELECT StudentNo,StudentResult+1 AS '提分后' FROM result
- 数据中的列（表达式）：文本值，列，Null，函数，计算表达式，系统变量
where条件子句
- 作用
  检索数据中符合条件的值
- 逻辑运算符
尽量使用英文
- 查询考试成绩95-100之间
```
SELECT StudentNo,StudentResult FROM result
WHERE StudentResult>=95 AND StudentResult<=100
```
- 模糊查询：比较运算符
- 查询姓刘的同学
  like结合 %（代表0到任意个字符）_（一个字符）
```
SELECT StudentNo,StudentResult FROM result
WHERE StudentName LIKE '刘%'
```
- 查询名字中有佳的同学
```
SELECT StudentNo,StudentResult FROM result
WHERE StudentName LIKE '%佳%'
```
- 查询1，2，3班的同学
  IN不能用LIKE的写法，IN必须用具体的词
```
SELECT StudentNo,StudentResult FROM result
WHERE StudentClass IN (1,2,3)
```
- 查询地址为空的学生（null 或 ''）
```
SELECT StudentNo,StudentResult FROM result
WHERE Address='' OR Address IS NULL
```
联表查询
- JOIN (链接的表) ON (条件)
- 查询参加了考试的同学（学号，姓名，科目编号，分数）
  思路：
  1.分析需求，分析查询的字段来自哪些表，连接查询
  2.确定使用哪些连接查询？（7种）
  3.确定交叉点（这两个表中哪些数据是相同的）
  判断的条件：学生表中的 StudentNo = 成绩表中的 StudentNo
```
# INNER JOIN 
SELECT s.StudentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo

# RIGHT JOIN 只有这个可以
SELECT s.StudentNo,studentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo

# LEFT JOIN 
SELECT s.StudentNo,studentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo
```
- 查询缺考同学
```
# LEFT JOIN 
SELECT s.StudentNo,studentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo
WHERE StudentResult IS NULL
```
- 三表查询
自连接

自己的表和自己的表连接，核心：一张表拆为两张一模一样的表即可
```
# 查询父子信息
SELECT a.`categoryName` AS '父栏目' , b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` as b
WHERE a.`categoryId` = b.`pid`
```
分页和排序
- 排序 ORDER BY
  升序 ASC
  降序 DESC
```
#成绩排序
SELECT name,score FROM student
ORDER BY score DESC
```
- 分页
1. 缓解数据库压力
2. 好的视觉效果，相反--瀑布流
  语法：LIMIT 起始值,页面的大小
  LIMIT 0,5 第一页1-5条数据
  LIMIT 5,5 第二页6-10条数据
  第n页：LIMIT (n-1)*pageSize,pageSize
  pageSize：页面大小
  LIMIT (n-1)*pageSize：起始值
  n：当前页
  [页面总数/页面大小]向上取整 = 总页数
子查询
- where（这个值是计算出来的）
  本质：在where嵌套一个子查询
  where（select ....）
SQL函数

常用函数（并不常用）
- 数学运算
```
SELECT ABS(-5) --绝对值
SELECT CEILING(9.4) --向上取整
SELECT FLOOR(9.4) --向下取整
SELECT RAND() --随机数0-1之间
SELECT SIGN(0) --判断一个数的符号 signal函数，返回值为-1，0，1
```
- 字符串函数
```
SELECT CHAR_LENGTH('你好') --字符串长度
SELECT CONCAT('我','和','你') --拼接字符串
SELECT INSERT('Hello，world',1,5,'Bye') --某个位置开始替换某个长度
SELECT LOWER() --字符串转小写
SELECT UPPER() --字符串转大写
SELECT INSTR('hello','o') --字符在字符串的第几个位置
SELECT REPLACE('HELLO JAVA','JAVA','MYSQL') --替换指定字符串
SELECT SUBSTR('HELLO',2,3) --第二个字母开始，截取三个
SELECT REVERSE() --反转字符串
```
- 时间和日期※
```
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()
```
聚合函数（真·常用）
```
# 查询表中计数
SELECT COUNT(studentName) FROM student; --COUNT(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student; --不会忽略null值，本质，计算行数
SELECT COUNT(1) FROM student; --不会忽略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;
```
- 分组查询
数据库级别的MD5加密
- 什么是MD5?
  主要增强算法复杂度和不可逆性。
  MD5 不可逆，具体的值的md5是一样的
  MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值
```
# 加密密码
UPDATE testmd5 SET pwd=MD5(pwd); --加密全部密码
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456')) --插入时加密
# 如何校验：输入正常的，再加密，和之前加密后的对比
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')
```
SELECT小结
相关阅读:
url 记录
 tvm
const flold
spring
java连接mysql数据库
 linux常用命令记录
 pikachu漏靶场洞测试
 Starting.....
IOS App提交流程
 InApp Purchase（iap）快速指南
原文地址：https://www.cnblogs.com/peng8098/p/db_01.html

MySQL 数据库（上）

初识MySQL

数据库

数据库分类

MySQL

操作数据库

操作数据库

数据库的列类型

数据库的字段属性※

创建表

数据表的类型

修改删除表

MySQL数据管理

外键

DML全部记住

添加

修改

删除

DQL查询数据※※

DQL

指定查询字段

where条件子句

联表查询

自连接

分页和排序

子查询

SQL函数

常用函数（并不常用）

聚合函数（真·常用）

数据库级别的MD5加密

SELECT小结