SQL 基本概念

SQL，Structured Query Language，操作关系型数据库编程语言
DB，Database，数据库
DBMS，Database Management System

数据库→数据表→数据记录（行）→字段（列）

SQL 分类

DDL，Data Definition Language，数据定义语言，用于操作数据库对象
DML，Data Manipulation Language，数据操作语言，用于对数据表中的数据进行CRU
DQL，Data Query Language，数据查询语言，用于对数据表中的数据进行查询
DCL，Data Control Language，数据控制语言，创建数据库用户、更改权限等

SQL 语法格式

1、SQL语句可单行或多行，结尾带; 可用空格或缩进来增强可读性
2、不区分大小写

注释：
1、单行注释:

1
2
-- 注释内容
# 注释内容

2、多行注释:

1
/* 注释内容 */

DDL 语法

数据库操作

下列语法中，database可换成schema

1
2
3
4
5
6
7
8
9
10
# 查询所有数据库
show databases;
# 查询当前数据库
select database();
# 切换数据库
use 数据库名;
# 创建数据库
create database [if not exists] 数据库名 [default charset utf8mb4];
# 删除数据库
drop database [if exists] 数据库名;

表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 查询库中所有数据表
show table;
# 查询表结构
desc 表名;
# 查询建表语句
show create table 表名;

# 添加字段
alter table 表名 add 字段名 类型 [comment 注释] [约束];
# 修改字段类型
alter table 表名 modify 字段名 新类型;
# 修改字段名与字段类型
alter table 表名 change 旧名 新名 新类型 [comment 注释] [约束];
# 删除字段
alter table 表名 drop column 字段名;
# 修改表名
alter table 表名 to 新名;
# 删除表
drop table [if exists] 表名;

# 创建数据表
create table table_name(
    字段1 字段类型 [约束] [comment 字段1注释],
    ...
    字段n 字段类型 [约束] [comment 字段n注释]
)[comment 表注释];

约束：作用于表上对应字段的规则。同一字段可添加多个约束，之间以空格分隔

约束 描述 关键字
非空约束 限制字段非空 not null
唯一约束 字段值唯一不重复 unique
主键约束 字段作为一行数据的唯一标识，非空且唯一 primary key [auto_increment]
默认约束 设置字段默认值 default
外键约束 两张表之间建立连接的key foreign key

注意：auto_increment，实现主键自增，id只会一直从最新id增长，中间删去不影响下一id的生成。

数据类型

数值类型

类型 描述 大小
tinyint tiny整数值 1
smallint small整数值 2
mediumint medium整数值 3
int 整数值 4
bigint big整数值 8
float 单精度浮点 4
double 双精度浮点 8
decimal 小数值 -

decimal(8,2) 表示共8位，小数占2位
unsigned标识，写在数据类型后

字符串类型

类型 描述 大小
char 定长字符串 0-255 bytes
varchar 变长字符串 0-65535 bytes
tinytext 短文本字符串 0-255 bytes
text 文本字符串 -
mediumtext 中文本字符串 -
longtext 长文本字符串 -
tinyblob 短二进制数据 0-255 bytes
blob 二进制数据 -
mediumblob 中二进制数据 -
longblob 长二进制数据 -

char, 定长性能好
varchar, 变长更灵活
sql中，一般以单引号圈定字符串

日期类型

类型 描述 格式
time 时间值或持续时间 HH:MM:SS
year 年份 YYYY
date 日期 YYYY-MM-DD
datetime 日期时间 YYYY-MM-DD HH:MM:SS
timestamp 日期时间或时间戳 YYYY-MM-DD HH:MM:SS

now()函数，返回当前时间，datetime格式

DML 语法

insert，新增

1
2
3
4
5
6
7
8
# 指定字段添加数据(在某行某列添加数据)
insert into 表名(字段1，字段2) values (值1，值2);
# 全部字段添加数据(添加一行数据)
insert into 表名 values (值1，值2...);
# 批量添加数据(指定字段)
insert into 表名 (字段1，字段2) values (值1，值2), (值1，值2)...;
# 批量添加数据(全部字段)
insert into 表名 values (值1，值2...), (值1，值2...)...;

update，修改

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

不声明where，则默认修改全部记录

delete，删除

1
delete from 表名 [where 条件];

delete会删除整条记录，不能用于删除某字段。若未声明where，默认删除全部。

DQL 语法

基本查询

1
2
3
4
5
6
# 查询指定字段, as可省略
select 字段1 [as 别名1], 字段2 [as 别名2] from 表名;
# 查询所有字段
select * from 表名;
# 去除重复记录
select distinct 字段列表 from 表名;

条件查询

1
2
# 条件查询，字段列表就是多个字段，中间以逗号分隔
select 字段列表 from 表名 where 条件列表;
运算符
大于 >
大于等于 >=
小于 <
小于等于 <=
等于 =
不等于 <> 或 !=
范围内 between … and …
集合 in(…)
模糊匹配 like 占位符
判空 is null
判非空 is not null
与 and 或 &&
或 or 或 ||
非 not 或 !

模糊匹配中，_匹配单个字符，%匹配任意个字符。

1
2
# 查询name中含有 小 的所有记录
select * from table_name where name like '%小%';

组函数

在未使用分组时，默认结果为一组。组函数作用在每个组上。

1
2
3
4
5
6
7
8
9
10
11
12
13
# 统计字段非空的记录数
select count(字段名) from 表名;
# 统计全部记录条数，推荐使用*，mysql对其进行了优化
select count(*) from 表名;
select count(常量) from 表名;
# 最大值
select max(字段名) from 表名;
# 最小值
select min(字段名) from 表名;
# 平均值
select avg(字段名) from 表名;
# 求和
select sum(字段名) from 表名;

在返回的字段列表中，只能有组函数、分组字段。

null值不参与组函数计算。

分组查询

1
select 字段列表 from 表名 [where 条件列表] group by 分组用字段 [having 分组后过滤条件];

可以指定多个排序字段，之间以逗号分隔。只有当前面的排序相同时，才会考虑后一字段排序。

where 与 having 的对比

1、时机不同：where作用分组前，having作用分组后
2、条件不同：where不能用组函数，having可以

1
2
3
4
# 复杂示例
select job, count(*) cnt from emp where entry_date <= '2015-01-01' group by job having cnt >= 2;
# 1、查询entry_date 在 2015-01-01 之前的 emp
# 2、以 job 分组， 并筛选出 emp 数量大于2的 job

排序查询

1
2
# asc，升序 desc，降序;  默认为升序
select 字段列表 from 表名 [where 条件列表] [group by 分组字段 [having 分组后过滤条件]] order by 排序用字段 排序方式;

分页查询

1
2
# 起始索引从0开始
select 字段 from 表名 [where 条件列表] [group by 分组字段 [having 分组后过滤条件]] [order by 排序字段 排序方式] limit 起始索引,查询记录数;

MySQL连接

1
mysql -u用户名 -p密码 [-h数据库服务ip地址 -P端口号]

JDBC：Java引入MySQL

JDBC 快速入门

JDBC，Java Database Connectivity，Java提供的数据库连接接口。实现通过java代码操作数据库。由java规定接口，由具体语言实现。

1、引入MySQL、Junit依赖

1
2
3
4
5
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

2、java操作mysql代码示例

1
2
3
4
5
6
7
8
9
10
11
12
13
// 注册驱动，可省略，由于SPI(Service Provider Interface)机制，会自动载入驱动至jvm中
Class.forName("com.mysql.cj.jdbc.Driver")
// 获取连接
Connection connection = DriverManager.getConnection(
    "jdbc.mysql://localhost:3306/web", "root", "root@密码"
);
// 获取SQL语句执行对象
Statement statement = connection.createStatement();
// 执行SQL，sql语句中字符串以单引号圈起的作用体现出来了！
statement.executeUpdate("update user set password = '1234' where id = 1");
// 释放资源
statement.close();
connection.close();

JDBC 对象详解

1、DriverManager，驱动管理器对象

用于获取与MySQL数据库间的Connection对象

1
2
// url: jdbc:mysql//ip地址:端口/数据库名?参数键值对1&参数键值对2
DriverManager.getConnection(url,user,password);

2、Connection对象、Statement对象

用于获取Statement对象

1
2
3
4
// 获取执行普通SQL的对象
connection.createStatement()
// 获取执行预编译SQL的对象
connection.prepareStatement()

用于执行SQL语句

1
2
3
4
// 执行DDL、DML语句，返回int，表示影响的行数
executeUpdate(sql)
// 执行DQL语句，返回ResultSet，封装了查询结果
executeQuery(sql)

3、 ResultSet对象

DQL查询的返回结果对象

1
2
3
4
5
6
7
8
// 游标前进，判断当前行是否有数据，有则返回true；游标默认指向第一行之前
resultSet.next()
// 获取当前行数据，列编号从1开始
resultSet.getXxx(字段名 或 列编号)
// 解析代码示例
while(resultSet.next()){
    int id = resultSet.getInt("id");
}

预编译SQL vs 普通SQL

1、安全：为了解决SQL注入漏洞（攻击手段，通过传入特殊字符使sql语句失效）而采取预编译。

2、性能更高：对于批量SQL执行，预编译能分辨出重复部分，会把SQL语句相同部分缓存，减少拼接sql语句的负担。