【MySQL】基础知识

1、初识mysql

前端（页面：展示，数据）

后台（连接点：JDBC，链接前端）

数据库（存数据，txt，excel，word）

1.2 什么是数据库

存数据，管理数据

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

1.3 数据库分类

关系型数据库SQL

• mysql oracle sqlServer sqllite
• 通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库 NoSQL(not sql)

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

DBMS 数据库管理系统

• 数据库的管理软件，维护和获取数据

1.4 mysql

Oracle旗下产品 使用SQL语言 体积小，速度快，开源

1.5 安装mysql

官网搜索 建议安装5.7版本的

安装建议：

1.尽量不要使用exe

2.尽可能使用压缩包安装

1.解压

2.把这个包放到自己的环境目录下

3.配置环境变量 我的电脑 高级 环境变量 把mysql bin目录的路径新增为环境变量

4.新建my.ini

[mysqld]
#目录是自己安装的
basedir=D:\\...\\bin\\
datadir=D:\\...\\data\\
port=3306
skip-grant-tables

5.启动管理员模式的CMD，运行所有的命令

6.切换到mysql bin目录下 输入mysqld -install

7.输入 初始化数据库文件

mysqld --initialize-insecure --user=mysql

没有报错，成功

8.启动mysql 设置命令

net start mysql

9.进入mysql -p后面不要有空格

mysql -u root -p

不用输密码，直接回车

10.更改密码

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';

输入flush privileges;刷新权限

11.删除my.ini文件最后一句 skip-grant-tables

12.重启mysql exit 退出

net stop mysql
net start mysql

1.6 安装sqlyog

kuangshen
8d8120df-a5c3-4989-8f47-5afc79c56e7c

1.7 创建数据库

基字符集 utf8

数据库排序规则 utf8_general_ci

每一个sqlyog操作其实都是mysql语句的操作，可以在历史记录中查看

表的默认

引擎为： InnoDB

字符集: utf8

核对：utf8_general_ci

1.8 链接数据库

管理员打开cmd

mysql -u root-p123456   --单行注释

-- 所有语句都是用;结尾
show  databases;  --查看所有数据库
use schooll --切换数据库

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

create database test; --创建一个数据库

exit;退出连接
--单行注释（sql本质的注释）
/*
多
行
注
释
*/

2、操作数据库

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

mysql关键字不区别大小写

2.1 操作数据库

1.创建数据库

【】为可选

create database [if not exists] 数据库名

2.删除数据库

dorp database [if exists] 数据库名

3.使用数据库

use 数据库  --使用数据库
-- tab 键的上面 反引号  
--如果表名或者字段名是一个特殊字符，需要带`符号
use `school`

4.查看数据库

show database; --查看所有的数据库

2.2 数据库的列类型

数值

• tinyint 十分小的数据，一个字节
• smallint 较小的数据，两个字节
• mediumint 中等的 三个字节
• int 标准的证书，四个字节 常用
• big 较大的数据 8个字节
• float 浮点数 4字节
• double 8字节
• decimal 字符串形式的浮点数 金融计算的时候使用

字符串

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

时间日期

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

null

• 没有值，位置
• 注意，不要使用null类型进行计算

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

Unsigned：

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

zerofill:

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

自增：

• 通常理解为自增，自动在上一条记录加一
• 通常用来设计唯一的主键，必须是整数
• 可以自己设置起始值和步长

非空 null not null

• 设置为not null，不给他赋值，会报错

默认：

• 设置默认值
• sex,默认值为 男，如果不设置值，就用默认的值

扩展：听听就好

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

2.4 创建表

-- 注意点 ，使用英文() ,表的名称和字段 尽量用`扩起来
-- 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
-- 查看创建表的语句
show create table student

--显示表的结构
desc student

2.5 数据表的类型

innodb 默认使用

myisam 早些年使用的

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为２倍

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

本质还是文件的存储

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

• innodb 在数据库表中只有一个*.from文件
• MYISAM对应文件　３个文件

设置数据库表的字符集

默认的编码格式不支持中文　　Latin１

2.6 修改删除表

-- 修改表名
ALTER TABLE 旧的表名 RENAME AS 新的表名
-- 增加表的字段
ALTER TABLE 表名 ADD 字段名 列属性
-- 修改表的字段
  -- 修改约束
  ALTER TABLE 表名 MODIFY 字段名 类属性
  -- 字段重命名
  ALTER TABLE 表名 CHANGE 旧列名 新列名 列属性
  
-- 删除表的字段
ALTER TABLE 表名 DROP 列名
--删除表
DROP TABLE IF EXISTS 表名

所有的创建和删除尽量加上判断

注意点：

• 字段名用``包裹，安全
• 注释是 – /**/
• sql 关键字大小写不敏感，建议大家写小写
• 所有的符号用英文

3、mysql数据管理

3.1 外键（了解）

约束

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

-- 学生表的gradeid 字段要去引用年级表的gradeid
--定义外键key  
--给这个外键添加约束(执行引用) references 引用
create table `grade`(
   `gradeid` int(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename` varchar(20) NOT NULL COMMENT '年级名称',
    primary key( `gradeid`)
)engine=innodb default charset=utf8

create table `student`(
	`id` int(4) not null auto_increment 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

删除有外键关系的表的时候，先删除引用别人的表(主表)，在删除被引用的表(从表)

方式二：创建表的时候没有外键关系

alter table `stduent`
add constraint `FK_gradeid` foreign key(`gradeid`)
references `grade`(`gradeid`);

-- alter table 表名 add constraint 约束名
-- foreige key(作为外键的列) references 哪个表(哪个字段)

以上为 物理外键，即数据库级别的外键，我们不建议使用

最佳实现

• 数据库就是单纯的表，只用来存数据，只有数据和字段
• 想使用多张表的数据，使用外键，使用程序实现

3.2 DML语言（记住）

DML 语言：数据操作语言

• insert
• update
• delete

3.3 添加

insert into 表名([字段名1,字段名2,...]) values ('值1','值2',...)[,(''),('')];
-- 如果不写表的字段，会一一匹配
-- 一般写插入语句，一定要数据和字段一一对应

注意：

1.字段与字段之间使用 英文逗号隔开

2.字段名可以省略，但是字段值要一一对应

3.可以同时插入多条数据

3.4 修改

-- 带条件的
update `表名` set `字段名`=`新值`,[...]  where 字段名 = 字段值

条件 where 语句 运算符

操作符会返回布尔值

操作符	含义	范围	结果
=	等于		true,false
<>,!=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEN …AND…	闭合区间	[2,5]
AND	我和你&&
or	我或你||

update `student` set `birthday`=CURRENT_TIME where `name`='xxx'

字段值可以设置为变量

3.5 删除

delete from 表名[where 条件]

-- 避免这样写
delete from 表名

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

truncate table `表名`

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

4、DQL查询数据(最重点)

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

4.1 指定查询字段

-- 查询全部的学生
select * from 表名

-- 查询指定字段
select 字段名[,字段名2] from 表名

--别名，给结果起别名 可以给字段和表起别名
select 字段名 as 别名[,字段 as 别名] from 表名 as 表的别名

-- 函数 concat(a,b)
select concat('姓名:',字段名) as 新名字 from 

去重复 distinct

-- 
select distinct 字段名[,字段2] from 表名

作用：

去除select查询出来的结果中重复的数据

-- select 的其他使用
select version() --查询系统版本(函数)
select 100*9-10 AS 计算结果  --用来计算（表达式）
select @@auto_increment_increment --查询自增的步长（变量）

select 字段名+1  from 表名 --对查询后的数据处理

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

4.2 where条件子句

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

搜索的条件，由一个或者多个表达式组成

运算符	语法	描述
and &&	a and b a&&n	逻辑与
or ||	a or b a||b	逻辑或
not !	not a !a	逻辑非

-- 模糊查询 【区间】
select 字段 from 表名
where 字段  between 90 and 100

模糊查询：本质是比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null,结果为真
is not null	a is nout null	如果操作符为not 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,a3中的某一个值，结果为真

-- 查询姓刘的同学
-- like 结合%(代表0到任意个字符)  _(代表一个字符)
select name,id  from student
where name like '刘%'

-- 查询1001 1002 1003 学员信息
select * from student
where studentNo in (1001,1002,1003)
--查询在北京的学生
select * from student
where address in ('北京')

4.3 联表查询

JOIN 对比

百度7种join理论

-- 思路
/*
分析需求，字段来自哪张表
确定使用哪种连接
确定交叉点(几个表中的哪个数据是相同的)
*/
select s.studentNo,studentName,SubjectNo,
studentResult
from student AS s
inner join result AS r
where s.studentNo = r.studentNo

--right join
select s.studentNo,studentName,SubjectNo,
studentResult
from student AS s
right join result r
on s.studentNo = r.studentNo

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	即使右表中没有匹配，会返回所有的值
right join	即使左表中没有匹配，会返回所有的值

-- join on 连接查询
-- where  等值查询

左连接以左表为基准
右连接以右表为基准

select s.studentNo,studentName,subjectName,studentResult
from student s   --从studnet找
right join result r  -- 右连接result表
on r.studentNo = s.studentNo  --交叉点
inner join subject sub   -- 上面连接查询的结果为左表，subject为右表
on r.subjectno = sub.subjectno -- 交叉点

自连接

核心：一张表拆分为两个一样的表

select a.name as '父',b.name as '子'
from category as a,category as b
where  a.categoryid=b.pid

4.5 分页和排序

--order by
-- 升序ASC  降序DESC

select * from  表名
where  条件
order by 字段   asc|desc

-- 为什么要分页？
-- 缓解数据库压力
-- limit是在所有语句的最下面
--语法 ： limit  起始位置,页面大小
-- limit 0,5  第一页  一页5个数据
-- limit 5,5  第二页
-- limit  10,5 第三页
--第n页  limit (n-1)*页面大小(pageSize)
--总页数=数据总数/页面大小(向上取整)

4.6 子查询

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

(由里及外)

4.7 分组

select 语法顺序

select  
from
inner join  --联合查询
where  --指定结果需要满足的条件
group by --指定结果按照哪几个字段来分组
having --过滤分组的记录必须满足的次要条件
order by --指定查询结果排序
limit --指定结果从哪条到那条

5、Mysql函数

5.1 常用函数(并不常用)

--常用函数
select abs(-8)  --绝对值
select ceiling(9.4) --向上取整
select floor(9.4)  --向下取整
select rand()  -- 返回一个0-1之间的随机数
select sign()  --判断一个数的符号 0-0 负数- -1 正数-1

--字符串
select char_length('ewrwe')   --字符串长度
select concat('1','s','3')  --拼接字符串
select lower()  --转小写
select upper()  --转大写
select replace('aabbcc','a','d') --替换出现的指定字符串
select reverse()  --反转

---时间日期函数（记住）
select current_date()  --获取当前日期
select curdate() --获取当前日期
select now() --获取当前的时间
select localtime()  --本地时间
select sysdate()  --系统时间
select year(now()) --年
month,day,hour,minute,second

5.2 聚合函数(常用)

函数名称	描述
count()	计数
sum()	求和
avg()	平均数
max()	最大
min()	最小

count(字段名)   会忽略所有的null值
count(*)  不会忽略null值
count(1)   不会忽略null值

5.3 扩展：数据库级别的MD5加密

什么是MD5

md5不可逆,具体的md5值是一样的

md5破解网站的原理，背后有一个字典

update 表名 set pwd=MD5(pwd)


--如何校验
--将用户传递进来的密码，进行md5加密，然后比对加密后的值

6、事务

6.1 什么是事务

要么都成功，要么都失败

---

将一组sql放在一个批次中去执行

事务原则：ACID原则： 原子性，一致性，隔离性，持久性

原子性：

要么都成功，要么都失败

一致性

事务前后的数据完整性要保证一致

持久性

事务一旦提交不可逆，被持久化到数据库中

隔离性

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不会被其他事务所干扰

隔离性出现的问题：

脏读：一个事务读取了另一个事务未提交的数据

不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同

虚读(幻读)：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

执行事务：

mysql 是默认开启事务自动提交的

set autocommit = 0 --关闭

set autocommit = 1 – 开启，默认的

--关闭自动提交
set autocommit = 0
--事务开启
start transaction

--提交：持久化
commit
--回滚：回到最初
rollback
---事务结束
set autocommit=1 --开启自动提交

模拟场景

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`='A'
update account set money=money+500 where `name`='B'
commit; --提交
rollback;  -- 回滚
set autocommit=1; --回复默认

7、索引

索引的本质是：数据结构

通过索引可以高速获取数据

7.1 索引的分类

• 主键 primary key
  • 唯一的标识，主键不可重复
• 唯一 unique key
  • 避免重复的列出现，唯一索引可以重复
• 常规 key/index
  • 默认的，可以用index,key关键字来设置
• 全文索引 fullText

-- 索引的使用
-- 在创建表的时候给字段增加索引
-- 创建完毕后，增加索引

-- 显示所有的索引信息
show index from student
-- 增加一个全文索引(索引名)(列名)
alter  table 表名 add fulltext index 索引名(列名)

-- explain 分析sql执行的状况
explain select * from 表名

7.2 测试索引

7.3 原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 索引一般加到常用来查询的字段上

8、权限管理和备份

8.1 用户管理

SQL yog 可视化管理

SQL 命令操做

用户表：mysql.user

本质：

-- 创建用户
create user xx identified by '123456'

-- 修改当前用户密码
set password =password('111111')
-- 修改指定用户密码
set password for xxx=password('123456')

--重命名
rename user xxx to xx2

-- 授权
-- 除了给别人授权，其他都能干
grant all privileges on *.* to xx2
-- 查看权限
show grants for xx2

show grants for root@localhost -- 查看管理员权限
-- 撤销所有权限
revoke all privileges on *.* from xx2

-- 删除用户
drop user xx

8.2 数据库备份

• 保证重要的数据不丢失
• 数据转移

MySQL数据库备份的方式

• 直接拷贝物理文件
• 在sqlyog这种可视化工具中手动导出
• 使用命令行到处 mysqldump

#mysqldump -h主机 -u 用户名 -p密码 数据库名 表1,表2,表3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

导入：先登录mysql

source d:/a.sql

9、规范数据库设计

9.1 为什么需要设计

当数据库比较复杂时，需要设计

糟糕的：

• 数据冗余，浪费空间
• 数据插入和删除都会麻烦、异常
• 程序的性能差

良好的：

• 节约内存空间
• 保证数据库的完整性
• 方便我们开发系统

软件开发中，关于数据库的设计

• 分析需求
• 概要设计 E-R图

个人博客数据库设计

• 用户表
• 分类表
• 文章表
• 友链表
• 自定义表(系统信息，某个关键的字，或者一些主字段)
• 评论表

9.2 三大范式

为什么需要数据规范化

• 信息异常
• 插入异常
• 更新异常
• 删除异常

三大范式

第一范式：

原子性：保证每一列不可再分

第二范式：

前提：满足第一范式

每张表只描述一件事情

第三范式：

前提：满足第二范式

确保数据表中的每一列数据都和主键直接相关，而不能间接相关

规范化和性能的问题

关联查询的表不能超过三张表

10、JDBC(重点)

SUN公司为了简化开发人员的操作，提供了一个规范，就是JDBC

对于开发人员来说，只需要掌握JDBC接口即可

在maven中搜索下载

Class.forName(“com.mysql.jdbc.Driver”);

Connection connection = DriverManager.getConnection(url,username,password);

url

String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";

Statement 执行sql的对象

String sql =""; //编写sql
statement.executeQuery(); //查询操作 返回ResultSet
statement.execute()l//执行任何SQL
    

ResultSet 查询的结果集

释放资源(必须做)

例子：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcFirstDemo {
    public static void main(String[] args) throws Exception {
        //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 name = "root";
        String password = "123456";

        //3. 连接成功，返回数据库对象  connection代表数据库
        Connection connection= DriverManager.getConnection(url,name,password);
        //4. 执行SQL的对象 statement 执行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("password+"+resultSet.getObject("PASSWORD"));
            System.out.println("email+"+resultSet.getObject("email"));
            System.out.println("birthday+"+resultSet.getObject("birthday"));
        }
        //6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

提取工具类

package com.kuang.lesson02.utils;

import java.io.IOException;
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 (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    //2.获取连接
    public static Connection getConnection() throws Exception{
        return DriverManager.getConnection(url, username, password);
    }
    //3.释放资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {

        if(rs!=null){
            rs.close();
        }
        if (st!=null){
            st.close();
        }
        if(conn!=null){
            conn.close();
        }

    }
}

PreparedStatement对象

可以防止SQL注入，效率更高

案例

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args) {
        Connection connection= null;
        PreparedStatement pstm=null;
        try {

            connection = JdbcUtils.getConnection();
            //区别
            //使用问好占位符代替参数
            String sql = "insert into users(id,`NAME`) values(?,?)";
            pstm = connection.prepareStatement(sql);//预编译sql，先写sql然后不执行
            //手动赋值
            pstm.setInt(1,8);
            pstm.setString(2,"SANJIN");

            //执行
            int i = pstm.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(connection,pstm,null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}