关于sql:SQL概述

11次阅读

共计 13554 个字符，预计需要花费 34 分钟才能阅读完成。

数据库 XX 语言：DDL, DML, DQL, DCL

命令行连贯

mysql -uroot -p

update mysql.user set password = password('XXX') where user = 'root' and Host = 'localhost'; -- 批改用户明码
flush privileges; -- 刷新权限
-----------------------------------------------

show tables;
describe 表名; -- 显示表信息

exit; -- 退出连贯

create database [if not exists] 数据库名 -- 创立
drop database [if exists] 数据库名 -- 删除
use 数据库名 -- 应用 / 切换 如果数据库名是关键词，则加上 ``
show databases; -- 查看

数据类型：

数值：

整型：tinyint 1B,smallint 2B, int 4B, bigint 8B
浮点型：float 4B, double 8B, 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

每个表都必须存在以下五个字段：

id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创立工夫
gmt_update 批改工夫

CREATE TABLE [IF NOT EXISTS] `student`{`id` INT(4) [NOT NULL AUTO_INCREMENT COMMENT '学号'],
    ...
    PRIMARY KEY(`id`)
}[ENGINE=INNODB DEFAULT CHARSET=utf-8]

查看创立语句

SHOW CREATE DATABASE school -- 查看创立数据库语句
SHOW CREATE TABLE student -- 查看创立表语句
DESC student -- 显示表构造

数据表的类型

INNODB（默认）

MYISAM（早年应用）

	MYISAM	INNODB
事务反对	不反对	反对
数据行锁定	不反对	反对
外键束缚	不反对	反对
全文索引	反对	不反对
表空间大小	较小	较大，约为 2 倍

惯例应用的操作：

MYISAM：节约空间，速度较快
INNODB：安全性高，事务的解决，多表多用户操作

所有的数据库文件都存在 data 目录下，实质还是文件的存储

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

INNODB 在数据库表中只有 *.frm 文件，以及下级目录下的 ibdata1 文件
MYISAM 对应文件
- *.frm – 表构造的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）

批改（alter）

ALTER TABLE teacher RENAME AS teacher1 -- 批改表名
ALTER TABLE teacher1 ADD age INT(11) -- 减少字段

-- 批改表的字段
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 批改束缚
ALTER TABLE teacher1 CHANGE age age1 VARCHAR(1) -- 重命名

-- 删除表的字段
ALTER TABLE teacher1 DROP age1

删除

-- 删除表
DROP TABLE [IF EXISTS] teacher1

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

以上操作都是物理外键，数据库级别的外键，不倡议应用，防止数据库过多造成困扰

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

增加：insert into 表名[(字段 1,2,3...)]values(值 1,2,3...)[,(),()...]

批改：update 表名 set 字段 1 = 值 1[, 字段 2 = 值 2...] where 条件

删除：delete from 表名 where 条件

truncate 表名：齐全清空一个数据库表，表的构造和索引束缚不会变！

truncate 从新设置，自增列，计数器会归零，不会影响事务

where 子句

=, >, <, <=, >= <> 或者!=（不等于）, BETWEEN X AND Y（[X,Y]）

AND, OR

留神：没写条件则批改表的所有记录！

DQL:Data QueryLanguage 数据查询语言

select 字段 [as 别名] from 表 [as 别名]

函数

concat(a,b)

去重 distinct select distinct 字段 ...

能够 select 的表达式：文本值，列，Null，函数，计算表达式，零碎变量 …

is null,like, in

-- 查问姓刘的同学
-- like 联合 % 代表 0 到任意个字符 _代表一个字符
select `StudentNo`,`StudentName` from `student`
where StudentName like '刘 %'

-- 查问 1001,1002,1003 号学员
select `StudentNo`,`StudentName` from `student`
where StudentNo in (1001,1002,1003)

where 等值查问

join on 连贯查问

查表思路：

剖析需要：剖析查问的字段来自哪些表
确定应用哪种连贯查问（7 种）
确定交叉点（判断的条件：student.studentNo = grade.studentNo）

-- 查问缺考的学生
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`

limit & order by

排序：升序 ASC 降序 DESC

-- 分页：起始值，页面大小
limit start(0-),size
limit 0,5 -- 第一页
limit 5,5 -- 第二页
...
limit (N-1)*pageSize,pageSize -- 第 N 页

select 字段 from 表

where(子查问)

-- 由里及外
select StudentNo,StudentName from student where StudentNo in (
    select StudentNo from result where StudentResult>80
    and SubjectNo = (select SubjectNo from `subject` where `SubjectName` = '高等数学 -2')
)

官网：https://dev.mysql.com/doc/ref…

-- 数学函数
select abs(-8) -- 8
select ceiling(9.4) -- 10
select floor(9.4) -- 9
select rand() -- 0~1
select sign(-10/0/10) -- -1,0,1

-- 字符串函数
select char_length('xxxxx')
select concat('a','b','c')
select insert('xxx',pos,length,'yyy')
select lower('XXX')
select upper('xxx')
select instr('xyz','x') -- 返回子串第一次呈现的索引
select replace('abc','b','d')
select substr('abcdefg',pos,length)
select reverse('cba')

-- 查问姓周的同学, 替换成邹
select replace(studentname,'周'' 邹 '）from student
where studentname like '周 %'
               
-- 工夫和日期函数（记住）select current_date()
select curdate()
select now() -- 以后工夫
select localtime()
select sysdate()

-- 零碎
select system_user()    
select user()
select version()

select count(字段) from 表 -- 疏忽 null 值，有主键，count(字段)查问效率高
select count(*) from 表
select count(1) from 表
select sum(`StudentResult`) as 总和 from result
select avg(`StudentResult`) as 平均分 from result
select max(`StudentResult`) as 最高分 from result
select min(`StudentResult`) as 最低分 from result

-- 分组：查问不同课程的平均分，最高分，最低分
select SubjectName,avg(StudentResult) as 平均分,max(StudentResult) as 最高分,min(StudentResult) as 最低分
from result r
inner join `subject` sub
on r.`SubjectNo` = sub.`SubjectNo`
group by r.SubjectNo -- 分组字段
having 平均分 >80

不可逆

破解原理：暴力破解，一个个试

MD5(pwd)

将用户传递进来的明码，进行 MD5 加密，而后比对加密后的值

将一组 SQL 放在一个批次中执行

准则：ACID（原子性，一致性，隔离性，持久性）

原子性（Atomicity）

要么都胜利，要么都失败

一致性（Consistency）

事务前后的数据完整性要保障统一

隔离性（Isolation）

事物之间要互相隔离

持久性（Durability）

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

隔离导致的问题：脏读（读到另一事务未提交的数据）、不可反复读（屡次读取后果不同）、幻读（读取别的事务插入的数据）

-- mysql 默认开启事务主动提交
set autocommit = 0 -- 敞开
set autocommit = 1 -- 开启（默认）-- 手动处理事务
set autocommit = 0
-- 事务开启
start transaction
insert XXX
insert yyy
-- 提交
commit
-- 回滚
rollback
-- 事务完结
set autocommit = 1

-- 理解
savepoint 保留点名
rollback to savepoint 保留点名
release savepoint 保留点名

MySQL 官网对索引的定义为：索引（Index）是帮忙 MySQL 高效获取数据的数据结构。提取句子骨干，就能够失去索引的实质：索引是数据结构。

主键索引（primary key）
惟一索引（unique key）
惯例索引（key/index）
全文索引（fulltext）

-- 索引的应用
-- 1. 在创立表的时候给字段减少索引
-- 2. 创立结束后，减少索引

-- 显示所有的索引信息
select index from 表名

-- 减少一个全文索引
alter table 数据库. 表名 add fulltext index 索引名(字段名)

-- explain 剖析 sql 执行的情况
explain select * from student -- 非全文索引
explain select * from student where match(studentName) against('刘')

插入 100 万条数据测试索引

--  插入 100 万条数据
delimiter $$ -- 写函数前必须写，标记

create function mock_data()
returns int
begin
    declare num int default 1000000;
    declare i int default 0;
    while i<num do
        -- 插入语句
        insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) values(concat('用户',i),'178343244@qq.com',concat('18',floor(rand()*(999999999-100000000))+100000000),floor(rand()*2),uuid(),floor(rand()*100));
        set i = i+1;
    end while;    
    return i;
end;
select mock_data();

-- 测试索引
select * from app_user where `name` = '用户 9999'; -- 0.0993 sec
explain select * from app_user where `name` = '用户 9999'; -- 查问 99 万条数据

-- 创立一般索引
-- create index 索引名 on 表(字段)
-- 索引名：id_表名_字段名
create index id_app_user_name on app_user(`name`);

select * from app_user where `name` = '用户 9999'; -- 0.001 sec
explain select * from app_user where `name` = '用户 9999'; -- 查问 1 条数据

总结：索引在小数据量的时候用途不大，但在大数据的时候，区别非常显著

索引不是越多越好
不要对常常变动的数据加索引
小数据量的表不须要加索引
索引个别加在常常查问的字段上

索引的数据结构

Hash 类型的索引

Btree：InnoDB 默认

http://blog.codinglabs.org/ar…

SQL 命令操作：实质是对用户表 mysql.user 进行改变

-- 创立用户
create user xxx identified by '123456'
-- 批改明码
set password [for xxx] = password('123456')
-- 重命名
rename user xxx to yyy
-- 用户受权 库. 表
grant all privileges on *.* to xxx -- all privileges 除受权权限
-- 查问权限
show grants for xxx
show grants for root@localhost
-- 撤销权限
revoke all privileges on *.* from xxx
-- 删除用户
drop user xxx

目标：保证数据不失落，数据转移

形式：

间接复制物理文件
在 Navicat 这种可视化工具中手动导出
应用命令行导出 mysqldump

mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# 导入
# 登录的状况下，切换到指定的数据库
# source 备份文件
source d:/a.sql

mysql - u 用户名 - p 明码 库名 < 备份文件

数据库设计：剖析需要→概要设计（E- R 图）

三大范式：

第一范式（1NF）：要求数据库表的每一列都是 不可分割 的原子数据项。
1NF 保障每一列不可再分
第二范式（2NF）：满足 1NF 的前提下，非码属性必须 齐全依赖 于候选码（在 1NF 根底上打消非主属性对主码的局部函数依赖）
2NF 保障每张表只形容一件事物
第三范式（3NF）：满足 2NF 的前提下，任何非主属性不依赖于其它非主属性（在 2NF 根底上 打消传递依赖）
3NF 保障表中每一列数据都与主键间接相干

规范性和性能的问题：

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

思考商业化的需要和指标（老本，用户体验）数据库的性能更加重要
在标准性能的问题的时候，须要适当思考规范性
成心给某些表减少一些冗余的字段。（多表查问→单表查问）
成心减少一些计算列（大数据量查问→小数据量查问）

Java 操作数据库

程序通过数据库驱动操作数据库

须要导入一个数据库驱动包：mysql-connector-java-X.X.XX.jar

public class JDBCFirstDemo {public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，主动加载 DriverManager.registerDriver(new com.mysql.jdbc,Driver());
        // 2. 用户信息和 url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=true"; // 协定(jdbc:mysql):// 主机地址: 端口号 / 数据库名? 参数
        String username = "root";
        String password = "1031";
        // 3. 连贯胜利，数据库对象 Connection
        Connection connection = DriverManager.getConnection(url,username,password);
        // 4. Statement 执行 SQL 对象
        Statement statement = connection.createStatement();
        String sql = "select * from users";
        ResultSet resultSet = statement.executeQuery(sql);
        // 5. 查看返回后果
        while(resultSet.next()){System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("pwd="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("=================");
        }
        // 6. 开释连贯，耗资源，用完关掉
        resultSet.close();
        statement.close();
        connection.close();}
}

Java 应用 JDBC 连贯数据库步骤：

加载驱动
连贯数据库 DriverManager
获取执行 sql 的对象 Statement
获取返回的后果集
开释连贯

Statement & ResultSet

Connection connection = DriverManager.getConnection(url,username,password);
Statement statement = connection.createStatement();
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql); // 查问操作返回 ResultSet
statement.execute(); // 执行任何 SQL
statement.executeUpdate(); // 更新、插入，删除，返回一个受影响的行数

// ResultSet
resultSet.getObject("id") // 不晓得返回类型
resultSet.getString("id") // 晓得返回类型
// 遍历指针（光标）resultSet.next() // 挪动到下一行
resultSet.beforeFirst() // 挪动到最前
resultSet.afterLast() // 挪动到最初
resultSet.previous()  // 挪动到前一行
resultSet.absolute(row) // 挪动到指定行

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 | ClassNotFoundException e) {e.printStackTrace();
        }
    }
    // 获取连贯
    public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url,username,password);
    }

    // 开释连贯
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        try {if (resultSet != null){resultSet.close();
            }
            if (statement != null){statement.close();
            }
            if (connection != null) {connection.close();
            }
        } catch (SQLException e) {e.printStackTrace();
        }
    }
}

测试用 JDBC 封装类来操作数据库

import com.zhg.lesson02.utils.JDBCUtils;

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

public class TestInsert {public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(4,'zhg','123456','zhomgun@qq.com','2020-01-01')"; // 删改只需改 sql 语句
            int i = statement.executeUpdate(sql);
            if(i > 0){System.out.println("插入胜利！");
            }
        } catch (SQLException e) {e.printStackTrace();
        }finally {JDBCUtils.release(connection,statement,resultSet);
        }
    }
}

import com.zhg.lesson02.utils.JDBCUtils;

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

public class TestSelect {public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users"; // 删改只需改 sql 语句
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){System.out.println("id="+resultSet.getObject("id"));
                System.out.println("name="+resultSet.getObject("name"));
                System.out.println("pwd="+resultSet.getObject("password"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("birthday="+resultSet.getObject("birthday"));
                System.out.println("=================");
            }
        } catch (SQLException e) {e.printStackTrace();
        }finally {JDBCUtils.release(connection,statement,resultSet);
        }
    }
}

SQL 注入即是指 web 应用程序对用户输出数据的合法性没有判断或过滤不严，攻击者能够在 web 应用程序中当时定义好的查问语句的结尾上增加额定的 SQL 语句，在管理员不知情的状况下实现非法操作，以此来实现坑骗数据库服务器执行非受权的任意查问，从而进一步失去相应的数据信息。

import com.zhg.lesson02.utils.JDBCUtils;

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

public class SQLInjection {public static void main(String[] args) {login("zhg","123456"); // 失常登录
        login("'or'1=1","'or'1=1"); //SQL 注入：用 or 拼接 SQL 语句...
    }
    // 登录业务
    public static void login(String username,String password){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users where `NAME` ='"+username+"'and `PASSWORD` ='"+password+"'"; // 删改只需改 sql 语句
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){System.out.println("id="+resultSet.getObject("id"));
                System.out.println("name="+resultSet.getObject("name"));
                System.out.println("pwd="+resultSet.getObject("password"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("birthday="+resultSet.getObject("birthday"));
                System.out.println("=================");
            }
        } catch (SQLException e) {e.printStackTrace();
        }finally {JDBCUtils.release(connection,statement,resultSet);
        }
    }
}

PreparedStatement 能够避免 SQL 注入

import com.zhg.lesson02.utils.JDBCUtils;

import java.sql.*;
import java.util.Date;

public class TestInsert {public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {connection = JDBCUtils.getConnection();
            // 应用? 占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)"; // 删改只需改 sql 语句
            statement = connection.prepareStatement(sql); // 预编译的 SQL，先写 SQL，而后不执行
            // 手动给参数赋值
            statement.setInt(1,5);
            statement.setString(2,"hugh");
            statement.setString(3,"123456");
            statement.setString(4,"24245525@qq.com");
            // 留神点：sql.Date    数据库   new java.sql.Date(工夫戳)
            //        util.Date   Java    new Date().getTime() 取得工夫戳
            statement.setDate(5,new java.sql.Date(new Date().getTime()));
            int i = statement.executeUpdate();
            if(i > 0){System.out.println("插入胜利！");
            }
        } catch (SQLException e) {e.printStackTrace();
        }finally {JDBCUtils.release(connection,statement,resultSet);
        }
    }
}

PreparedStatement 能够避免 SQL 注入的实质：把传递进来的参数当作字符，如果存在转义字符，就间接本义

数据库连贯→执行→开释，十分节约系统资源

池化技术：筹备一些事后的资源，过去就连贯事后筹备好的

编写连接池，实现接口 DataSource

开源数据源实现

DBCP

C3P0

Druid：阿里

应用这些数据库连接池之后，咱们在我的项目开发中不须要编写数据库的代码了！

DBCP，须要 jar 包：commons-dbcp-1.4, commons-pool-1.6

C3P0，须要 jar 包：c3p0-0.9.5.5, mcharge-commons-java-0.2.19

无论应用什么数据源，实质还是一样的，DataSource 接口不会变，办法就不会变

正文完

sql

发表至： sql

2020-10-11

0

关于sql:基于FlinkClickHouse构建实时游戏数据分析最佳实践

关于sql:下载实时数仓王炸组合实时计算-Flink-版-Hologres来啦

关于sql:MySQL基础合集

关于sql:MaxCompute-存储设计

关于golang:开源项目-promeshard-consulwatch动态分片实现Prometheus采集端高可用

关于sql:SQL概述

1 数据库

1.1 连贯数据库

1.2 操作数据库

2 表

2.1 创立表

2.2 批改删除表

3 MySQL 数据管理

3.1 外键

3.2 DML 语言

4 DQL 查问数据

4.1 含糊查问

4.2 联表查问

自连贯

4.3 分页和排序

4.4 嵌套查问

5 MySQL 函数

5.1 罕用函数

5.2 聚合函数

5.3 MD5 加密

6 事务

7 索引

7.1 索引的分类

7.2 测试索引

7.3 索引准则

8 权限治理和备份

8.1 权限治理

8.2 MySQL 备份

9 标准数据库设计

10 JDBC

10.1 封装 JDBC（JDBCUtils.java）

10.2 SQL 注入

10.3 PreparedStatement 对象

10.4 数据库连接池

站内搜索