MySql复习

2022-10-12 07:49:55

Mysql复习

DBMS(数据管理系统)

SQL的分类

SQL:关系型数据库。如MySQL,Oracle ,SQL Server ,DB2,SQLlite

NO SQL:Redis, MongoDB

基本命令

mysql -u -p --连接数据库
查询所有数据库：Show databases;
查询所有表：Show tabales;
查询表描述：describe name;
查询表结构：Desc name;
查询MySQL版本号：Select VERSION();
查询自增的步长 @@auto_increment_increment

数据库语言

DDL: 定义

DML: DML

DQL: 查询

Data Query Language

DCL: 控制

``符号

如果你的表名或者字段名是一个特殊字符，就需要带``符号

字段属性

Unsigned:

  • 生命了该列不能声明为负数

zerofile:

  • 不足的位数使用0填充

  • 只能用于int

    比如使用了zerofile，值设置为10

拓展

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

数据库引擎

Innodb ：默认使用

Myisam：早些年使用

Innodb Myisam
事务支持 支持 不支持
数据行锁定 支持 不支持
外键约束 不支持 支持
表空间大小 较大(约Myisam的2倍)
  • MYISAM 节约空间，速度较快
  • INNODB 安全性高，事务处理，外键约束

修改表名

 ALTER TABLE 旧表名 RENAME AS 新表名

插入多个字段

insert into name (‘‘),(‘‘),(‘‘)....

去重

关键字：DISTINCT

Select DISTICT 字段 FROM 表名

模糊查询

关键字：Like
如：

Select Stuname From Student where Stuname Like ‘张%‘;-- 查询以张开头的名字
Select Stuname From Student where Stuname Like ‘张_‘;-- 查询以张开头的名字，但后面只可以有一个字符，以此类推
Select Stuname From Student where Stuname Like ‘%小%‘;-- 查询小为中间的名字
Select StuID From Student where StuID In(1001,1002,1003); -- 查询id为1001,1002,1003的学生，in里面是一个具体的值，不能用%

联表查询

MySql复习

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

SELECT查询

MySql复习

分页

关键字：Limit (起始值，显示的数据行数)

 limit 0,5     -- (1-1)*5 第一页
 limit 5,5     -- (2-1)*5 第二页
 limit 5,5     -- (3-1)*5 第三页
 limit (n-1)*pageSize,pageSize    -- (第n页

-- PageSizi 页面大小
-- n当前页
-- (n-1)*pageSize 起始值
-- 数据总数/页面大小=总页数

MySQL函数

-- 数学函数
Select ABS(-1) -- 绝对值
Select Ceiling (9.1) -- 向上取整
Select Follr(9.1) -- 向下取整
Select Rand() -- 返回0-1之间的随机数

-- 字符串函数
Select char_length(‘你好啊‘);-- 字符串长度
Select Concat(‘字符‘,字段名) From 表名
Select Insert(‘Hello‘,2,1,"a") --替换，结果为hallo
Select LOWER(‘NIHAO‘)-- 转小写
Select Upper（‘nihao‘）-- 转大写
Select instr(‘hello‘,‘e‘) -- 返回第一次出现的字符的索引 结果为2
Select replace(‘hello‘,‘e‘,‘a‘) -- 替换,结果为hallo
Select substr(‘hello‘,2,3) --截取字符串，结果为ell
Select Reverse(‘张三‘) -- 反转 结果为三张

-- 时间和日期函数
Select current_date() -- 获取当前日期
Select Now() -- 获取当前时间，时分秒
Select Localtime()-- 本地时间
SELECT SYSDATE() -- 系统时间

-- 系统
Select user()

MD5

-- 首先创建表
CREATE TABLE TEST(
ID INT NOT NULL,
`NAME` VARCHAR(20) NOT NULL,
PWD VARCHAR(20) NOT NULL,
PRIMARY KEY(ID)
)ENGINE=INNODB DEFAULT CHARSET=UTF8

-- 插入数据
INSERT INTO TEST VALUES(1,‘张三‘,‘123456‘),
(2,‘李四‘,‘654321‘),
(3,‘王五‘,‘5211314‘)
-- 加密pwd字段
UPDATE TEST SET PWD=MD5(PWD);

-- 在插入数据的时候加密
INSERT INTO TEST VALUES(4,‘赵六‘,MD5(‘123456‘))
-- 查询
SELECT * FROM TEST WHERE id=1 AND PWD=MD5(‘123456‘);

事务

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

原子性 （Atomicity） ：要么都成功，要么都失败

一致性 （Consistency） ：事务前后的数据完整性要保持一致

隔离性 （Isolation） ： 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性 （Durability）事务提交 ：事务一旦提交就不可逆，被持续化到数据库中

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

-- mysql是默认开启事务自动提交的
SET AUTOCOMMIT =0  -- 关闭
SET AUTOCOMMIT =1  -- 开启
-- 手动处理事务
START TRANSACTION -- 标记一个事务的开始，从这之后sql都在同一事务内
-- 提交
COMMIT 
-- 回滚
ROLLBACK

-- 了解
SAVEPOINT 保存点名-- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 删除保留点

索引

索引(index)是帮助MySQL高效获取数据的数据结构

提取句子主干，就可以得到索引的本质：索引是数据结构

索引的分类

主键索引只能有一个，唯一索引有多个

  • 主键索引（PRIMARY KEY）
    • 唯一的标识，不可重复
  • 唯一索引 （UNIQUE KEY）
    • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
  • 常规索引 （KEY/INDEX）
    • 默认的，index/key 关键字来设置
  • 全文索引（FullTest）
    • 在特定的数据库引擎下才有

显示所有的索引信息

SHOW INDEX FROM TABLENAME

-- 增加一个全文索引
alter table 数据库名.表名 ADD FULLTEXT INDEX 索引名(字段名)

blog.codinglabs.org/articles/theory-of-mysql-index.html

权限管理与备份

命令操作

用户表：msql.user

本质：对这张表进行增删改查

-- 创建用户
create user 用户名 identified by 密码
-- 修改密码
Set password= password(‘‘)
-- 修改指定用户密码
set password for 用户 = password();
-- 重命名
rename user 旧名 TO 新名字
-- 用户授权
gant all privileges on *.* to 用户(所有权限) 
-- 查看权限
show grants for 用户
-- 撤销权限
revoke  all privillleces on 权限 to 用户

备份

方式：

  • 物理备份

  • 在可视化工具中手动导出

  • 命令行

    • -- 数据库
mysqldump -h 主机 -u 用户名 -p 密码 数据库名 > 路径

-- 导出表
mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 > 路径
-- 导入
soure 路径

三大范式

第一范式

原子性：要求数据库的每一列都是不可再分的项

第二范式

前提：满足第一范式

每张表只描述意见事情

第三范式

前提：满足第一范式和第二范式

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

规范性和性能的问题

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

  • 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
  • 在规范性能的问题的时候，需要适当的考虑一下规范性！
  • 故意给某些表增加一些冗余的字段（多表查询变为单表查询）
  • 故意增加一些计算列

JDBC

1.数据库驱动

驱动：声卡，显卡，数据库

mysql-connector-java.jar包

第一个JDBC项目

CREATE DATABASE jdbcstudy CHARACTER SET utf8 COLLATE utf8_general_ci;
use idbcstudy
create table users(
id int primary key,
    name varchar(20),
    password varchar(20),
    email varchar(20),
    birthday Date
);

insert into users values(1,‘zhangsan‘,‘123456‘,‘110@qq.com‘,‘1980-12-04‘),
(2,‘zhangsan2‘,‘123456‘,‘110@qq.com‘,‘1980-12-04‘),
(3,‘zhangsan3‘,‘123456‘,‘110@qq.com‘,‘1980-12-04‘)

步骤总结：

  1. 加载驱动
  2. 简介数据库 DriverManager
  3. 获得执行sql的对象 Statement
  4. 获得返回的结果集
  5. 释放连接
package com.sec.age;

import java.sql.*;

public class Test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息和url
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8";
        String username="root";
        String password="";
        //3.连接成功，数据库对象 connection代表数据库   Connection：连接  Driver：驱动 Manager：管理
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.执行sql的对象
        Statement statement = connection.createStatement();
        //5.执行sql的对象去执行SQL语句 ,
        String sql="Select * from users";
        // execute :执行  Query：查询  ResultSet：结果集
        ResultSet resultSet = statement.executeQuery(sql);

        while(resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
            System.out.println(resultSet.getObject("password"));
            System.out.println(resultSet.getObject("email"));
            System.out.println(resultSet.getObject("birthday"));
        }
        //释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

SQL注入的问题

存在漏洞，会被攻击导致数据泄露

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

数据库连接池

数据库连接-- 执行完毕 -- 释放

连接 -- 释放过程十分浪费系统资源

池化技术：准备一些预先的资源，过来就连接须先准备好的！

比如银行业务，如果按照“数据库连接-- 执行完毕 -- 释放”这个流程的话，那么就是来一个人开门，服务，关门。

而连接池就是开门--接待员：等待-- 服务--关门

接口：DataSource

使用数据库连接池后，在项目开发中就不需要编写连接数据库的代码了！

DBCP

MySql复习

上一篇：jQuery学习--------jQuery过滤器


下一篇：MySql授权和撤销权限操作