- MySQL
- mysql 字符集的设置
- 数据类型
- SQL语句
- SQL 分类
- DDL：操作数据库、表
- DML:增删改表中数据
- DQL：查询表中的记录
- DCL：管理用户，授权
- count(1)、count(*)与count(列名)的执行区别
- 函数
- SQL 事例
- 约束
- 数据库的设计
- 实现关系
- 三大范式
- 数据库的备份和还原
- 多表查询
- 事务
- JDBC
- JDBC管理事务
- 数据库连接池
- Spring JDBC
- 设置数据库时间
- Oracle
- 用户
- 创建表
- 查询
- NVL(Expression 1, Expression 2)
- 哑表 dual表
- between··and··
- order by
- escape
- case···when···
- 回收站
- 函数
- 类型转换
- PostgreSQL
- 存储过程
- SQL语句
- 函数
- 数据类型格式化函数
- sql事例
注释格式：
单行注释 – 内容
多行注释
单双引号格式：
字符串、时间 单引号
as（重命名）后单双都可以
大小写敏感：
语法大小写不敏感
字符串大小写不敏感（oracle字符串大小写敏感）
mysql 字符集的设置-
参考配置
- 操作系统：win10
- 数据库安装路径 ：C:Program FilesMySQLMySQL Server 5.6
-
字符集的设置
-
在cmd中进入Mysql，通过 show variables like '%char%' 语句，查看Mysql数据库默认的字符集
-
在Win10系统中，打开my.ini 文件，其位置在 C:ProgramDataMySQLMySQL Server 5.6my.ini
（注：ProgramData 是C盘下的隐藏文件夹，记得显示隐藏的项目） -
修改配置文件
在[client]下添加语句：
default-character-set=utf8
在[mysql]下添加语句：
default-character-set=utf8
在[mysqld]下添加语句：
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
collation-server=utf8_unicode_ci
skip-character-set-client-handshake -
保存配置文件
保存修改后的my.ini 文件，若发现没有修改 my.ini 文件的权限，需要对该文件的属性做如下设置
-
-
重启服务器
以管理员身份启动cmd，先执行net stop mysql 命令，然后执行net start mysql 命令
-
数据库类型：
- int，整数类型 。age int
- double，小数类型。score double（5，2）
- date：日期，只包含年月日。'yyyy-MM-dd'
- datetime：日期，包含年月日时分秒。'yyyy-MM-dd HH:mm:ss'
- timestamp：时间戳类型 包含年月日时分秒 'yyyy-MM-dd HH:mm:ss'
- 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
- varchar，字符串。name varchar（20)：姓名最大20个字符。
-
创建表实例
create table student(
id int,
name varchar(32),
age int,
score double(4,1),
birthday date,
insert_time timestamp
)；
- DDL(Data Definition Language)数据定义语言。用来定义数据库对象：数据库，表，列等。关键字：create,drop,alter等
- DML(Data Manipulation Language)数据操作语言。用来对数据库中表的数据进行增删改。关键字：insert,delete,update等
- DQL(Data Query Language)数据查询语言。用来查询数据库中表的记录（数据）。关键字：select,where等
- DCL(Data Control Language)数据控制语言。用来定义数据库的访问权限和安全级别，及创建用户。关键字：grant,revoke等。
-
操作数据库：CRUD
- C（Create）：创建
- 创建db4数据库，判断是否存在，并指定字符集为gbk: create database if not exists 数据库名称 character set gbk;
- R（Retrieve）：查询
- 查询所有数据库的名称：show databases;
- 查询某个数据库的字符集：show create database 数据库名称；`
- U（Update）：修改
- 修改数据库的字符集：alter database 数据库名称 character set 字符集名称；
- D（Delete）：删除
- drop database 数据库名称；
- 判断数据库存在，存在再删除：drop database if exists 数据库名称；
- 使用数据库
- 查询当前正在使用的数据库名称：select database（）；
- 使用数据库：use 数据库名称；
- C（Create）：创建
-
操作表
-
C（Create）：创建
-
创建表：create table 表名（
列名1 数据类型1，
列名2 数据类型2，
·····
列名n 数据类型n
）；
-
复制表（只复制字段头）：create table 表名 like 被复制的表名；
-
复制表（完全复制）：create table 表名 select * from 被复制的表名（或者是查询内容）;
-
默认时间
- DEFAULT CURRENT_TIMESTAMP 表示当插入数据的时候，该字段默认值为当前时间
- ON UPDATe CURRENT_TIMESTAMP表示每次更新这条数据的时候，该字段都会更新成当前时间
-
-
R（Retrieve）：查询
-
查询某个数据库中所有的表名称：show tables;
-
-
查询表结构：desc 表名；
-
U（Update）：修改
-
修改表名：alter table 表名 rename to 新的表名；
rename table 表名 to 新的表名；
-
修改表的字符集：alter table 表名 character set 字符集名称；
-
添加一列：alter table 表名 add 列名 数据类型；
-
删除一列：alter table 表名 drop 列名；
-
修改列名称 类型：alter table 表名 change 列名 新列名 新数据类型；
alter table 表名 modify 列名 新数据类型；
-
D（Delete）：删除
- drop table 表名；
- drop table if exists 表名；
-
- 添加数据
- 语法格式：insert into 表名 （列名1，列名2，...列名n） values （值1，值2，...值n）；
- 注意事项
- insert into 表名 values（值1，值2，...值n）。默认给所有列添加值
- 除了数字类型，其他类型需要使用引号（单双都可以）引起来
- 复制（目标表已存在）
- 表2复制到表1：insert into 表名1 select * from 表名2；
- 表2部分复制到表1：insert into 表名1（列1，列2） select 列1，列2 from 表名2；
- 复制（目标表不存在）
- CREATE TABLE s SELECt * FROM student;
- 删除数据
- 语法格式：delete from 表名 [where 条件]
- 注意事项
- 如果不加条件则删除表中所有记录
- 删除所有记录方法
- delete from 表名；--不推荐。有多少条记录就会执行多少次删除操作
- truncate table 表名；--推荐。先删除表，在创建一张表头一样的表
- 修改数据
- 语法格式：update 表名 set 列名1 = 值1，列名2 = 值2，...[where 条件]；
- 注意事项
- 如果不加任何条件，则会将表中所有记录全部修改。
-
语法格式：select * from 表名；
-
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
-
-
基础查询
- 多个字段的查询：select 字段名1，字段名2...from 表名；
- 去除重复：select distinct 字段名1，字段名2...from 表名；
- 计算列：select count（字段名） from 表名；
- 注意：ifnull（表达式1，表达式2）：null 参与的运算，计算结果都为null
- 表达式1：那个字段需要判断是否为null
- 表达式2：如果表达式1为null的替换值
- 注意：ifnull（表达式1，表达式2）：null 参与的运算，计算结果都为null
- 起别名：关键字as，as也可以省略用空格代替
-
条件查询
-
运算符
-
>、<、<=、>=、=、<>
-
BETWEEN ...AND -- 闭合 事例1 select * from student where age between 20 and 30;
事例2 select * from student where age not between 20 and 30;
事例3 时间规则 BETWEEN '1999-08-10 22:19:11' AND '1999-08-10 22:19:12';
-
IN(集合) where age in(11,2,33);
-
LIKE：模糊查询
- 占位符：
- _：单个任意字符
- %：多个任意字符。LIKE ‘%张三%’
- 占位符：
-
IS -- null 需要用is来判断。is null is not null
-
AND 或 &&
-
OR 或 ||（oracle中的|| 表示 字符串连接符）
-
NOT 或 !
-
-
-
排序查询
- 语法格式：select * from 表名 order by 排序字段1 排序方式1，排序字段2 排序方式2...
- 排序方式：ASC，升序，默认；DESC：降序
- 注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
- 排序时null最小（oracle中排序时null值最大）
-
聚合函数：将一列数据作为一个整体，进行纵向的计算。
- count：计算个数 事例：select count(age) from student ;
- 一般选择非空的列：主键
- count（*）
- max：计算最大值
- min：计算最小值
- sum：计算和
- avg：计算平均值
- std：标准差
- timestampdiff：计算时间戳数据差。TIMESTAMPDIFF(SECOND，timestamp 1, timestamp 2)
- count：计算个数 事例：select count(age) from student ;
-
分组查询（分为几种）：语法格式 ：group by 分组字段；
事例：select sex, avg(math),count(id) from where math>70 group by sex having count(id) > 2;
注意：
group by 只会返回分组后查询的一条数据。
-
分页查询：语法格式：limit 开始的索引，每页查询的条数； — 索引由0开始
— (page -1) * pagesize
limit 每页查询的条数；
-
集合查询：union用于把两个或者多个select查询的结果集合并成一个
SELECT ... UNIOn [ALL | DISTINCT] SELECT ...
默认情况下，UNIOn = UNIOn DISTINCT – 去重
UNIOn ALL不去掉结果集中重复的行
在去重操作时，如果列值中包含NULL值，认为它们是相等的
mysql> select * from t1; +------+------+ | num | addr | +------+------+ | 123 | abc | | 321 | cba | +------+------+ 2 rows in set (0.00 sec) mysql> select * from t2; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | A | +------+------+ 2 rows in set (0.00 sec) mysql> select * from t1 -> union -> select * from t2; +------+------+ | num | addr | +------+------+ | 123 | abc | | 321 | cba | | 1 | a | | 2 | A | +------+------+ 4 rows in set (0.00 sec)
需要先切换到mysql数据库下
1. 管理用户
1. 添加用户：
* 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER 'root'@'%' IDENTIFIED BY 'root';
CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';
2. 删除用户：
* 语法：DROp USER '用户名'@'主机名';
3. 修改用户密码：
SET PASSWORD FOR '用户名'@'主机名' = PASSWORd('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORd('123');
* mysql中忘记了root用户的密码？
1. cmd(在搜索框中以管理员身份运行) -- > net stop mysql57 停止mysql服务
2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables（管理员身份）
3. 打开新的cmd窗口（无需管理员身份）,直接输入mysql命令，敲回车。就可以登录成功
4. use mysql;
5. update user set authentication_string = password('root') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器，手动结束mysqld.exe 的进程（我的电脑是关闭窗口后自动消失了）
8. 启动mysql服务（管理员身份）
9. 使用新密码登录。
权限管理
1. 查询权限：
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';
2. 授予权限：
-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3. 撤销权限：
-- 撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATe ON db3.`account` FROM 'lisi'@'%';
### SQL优化
count(1)、count(*)与count(列名)的执行区别
- 执行效果上 ：
count(*)包括了所有的列，相当于行数，在统计结果的时候， 不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候， 不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为NULL - 执行效率上：
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（）最优。 - 总结
- 有主键用count（主键），会忽略列值为NULL
- 没主键用count（1），不会忽略列值为NULL
group_concat – 将一列的数据连接成一个字符串
基本语法：group_concat([DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’])
- 结合group by 语法使用
DATE_FORMAT( 时间数据 ,'%Y-%m-%d %H:%i:%s') 格式化时间数据为字符串
类型转换函数
CAST(源转换类型 AS 目标转换类型)，CONVERT(源转换类型 ，目标转换类型)
类型：
二进制,同带binary前缀的效果 : BINARY
字符型,可带参数 : CHAR 事例： CHAR 或 CHAr(10)
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
事例：
SELECT CONVERT('23',SIGNED); -- 字符串转为整数
SQL 事例
查询截止到当前时间新增的用户总数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HmO5fiCO-1656837880946)(图片/snipaste20220331_175733.PNG)]
-- 查询截止到当前时间新增的用户总数
SELECT
reg_time,
min_total AS '小计',
@total := @total + min_total AS '总计'
FROM
( SELECt date( reg_time ) AS reg_time,
sum( reg_count ) AS min_total FROM t_reg GROUP BY date( reg_time ) ) AS temp,
( SELECt @total := 0 ) AS T1
ORDER BY
reg_time;
-- 排序分组后取第一个（先排序，加distinct 字段固定表，最后分组）
select * from (select distinct user_phone,crt_date from yxcenter.cimp_cm_activity_user order by crt_date asc ) t group by user_phone
SELECt @total := 0,这句的意思是给临时变量@total设置值为0;
mysql分组后合并显示一个字段的多条数据方式
group_concat – 将一列的数据连接成一个字符串
基本语法：group_concat([DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’])
- 结合group by 语法使用
select name,group_concat(hobby separator '，') hobbies from student_hobbt group by name;
主键约束：primary key —主键有唯一属性
非空约束：not null
唯一约束：unique
外键约束：foreign key
-
非空约束：
-
CREATE TABLE student (
id INT,
NAME VARCHAr(20) NOT NULL,-- 创建表时添加非空约束
age INT)
-- 删除非空约束
ALTER TABLE student MODIFY NAME VARCHAr(20);-- 创建表后添加非空约束
``ALTER TABLE student MODIFY NAME VARCHAr(20) NOT NULL；`
-
-
唯一约束：
-
CREATE TABLE student(
id INT,
phone_number VARCHAr(20) UNIQUE-- 创建表时添加唯一约束
)-- 删除唯一约束
ALTER TABLE student DROp INDEX phone_number;-- 创建表后添加唯一约束
ALTER TABLE student MODIFY phone_number VARCHAr(20) UNIQUE;- 注意 unique 限定的值可以有多个null——null并不唯一
-
-
主键约束：
-
CREATE TABLE student(
id INT PRIMARY KEY,-- 创建表时添加主键约束
NAME VARCHAr(20)
)-- 删除主键约束
ALTER TABLE student DROP PRIMARY KEY;-- 创建表后添加主键约束
ALTER TABLE student MODIFY id INT PRIMARY KEY; -
自动增长：
-
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,-- 创建表时自动增长
NAME VARCHAr(20)
)-- id 为null时，自动写入序号
INSERT INTO student VALUES(NULL,'王佳旭');-- 删除自动增长
ALTER TABLE student MODIFY id INT;-- 创建表后添加自动增长
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
-
-
-
外键约束：
-
CREATE TABLE employee(
id INT,
NAME VARCHAr(20),
department_id INT,
CONSTRAINT emp_depId_fk FOREIGN KEY (department_id) REFERENCES department(id)-- 创建表时创建外键约束
-- 外键约束格式 ：CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
);
INSERT INTO employee VALUES(1,'翟宇飞',1);
INSERT INTO employee VALUES(2,'李卓',2);-- 删除外键约束 ALTER TABLE employee DROP FOREIGN KEY 外键名称 ;
ALTER TABLE employee DROP FOREIGN KEY emp_depId_fk;-- 创建表后添加外键约束
ALTER TABLE employee ADD CONSTRAINT emp_depId_fk FOREIGN KEY (department_id) REFERENCES department(id); -
级联删除与更新（在设置外键约束时进行添加）
- -- 级联更新（删除主键，外键为null）
CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE
-- 级联删除
CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON DELETE CASCADE
- -- 级联更新（删除主键，外键为null）
-
-
一对多（多对一的理解：多个用一个）
- 实现方式：在多的一方建立外键，指向一的一方的主键
-
多对多
-
实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
-
复合主键：PRIMARY KEY (外键名1，外键名2)
事例：CREATE TABLE tab_favorite(
rid INT,
DATE DATETIME,
uid INT,
-- 创建复合主键
PRIMARY KEY(rid,uid),
FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY (uid) REFERENCES tab_user(uid)
)
-
-
一对多和多对多的区别：判断两个关系表存储字段的形式，可以存储一个信息的是一对多，能存储多个信息的是多对多
- 第一范式：每一列都是不可分割的原子数据项
- 第二范式：在1NF的基础上，非码属性必须完全依赖码（在1NF基础上消除非主属性对主码的部分函数依赖）
- 心得：必须先找到码
- 码：如果一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码
- 第三范式：在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）
-
命令行
-
备份 ：（在cmd中）mysqldump -u用户名 -p密码 存储文件名称 > 存储文件路径
事例：mysqldump -uroot -proot db1 > D:\a.sql
-
还原：登录数据库—>创建数据库—>使用数据库—> source 还原文件路径
-
-
图形化界面（SQLyog）
- 备份：右键数据库—>备份/导出—>备份数据库，转存为SQL
- 还原：右键“root@localhost"—>执行SQL脚本
内连接查询
-
隐式内连接
-- 查询员工表的名称，性别。部门表的名称 -- 事例一 SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERe emp.`dept_id` = dept.`id`; -- 事例二（正确格式） SELECt t1.name, -- 员工表的姓名 t1.gender,-- 员工表的性别 t2.name -- 部门表的名称 FROM emp t1, dept t2 WHERe t1.`dept_id` = t2.`id`;
-
显式内连接
语法：select 字段 from 表名1 [inner] join 表名2 on 条件；
外连接查询
-
左外连接
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
注意：内连接：显示左边右边共有的；左连接：左边有的，右边没有的为null
-
右外连接
语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
子查询
-
子查询的结果是单行单列
-- 查询员工工资小于平均工资的人 SELECt * FROM emp WHERe emp.salary < (SELECt AVG(salary) FROM emp);
-
子查询的结果是多行单列
-- 查询'财务部'和'市场部'所有的员工信息 SELECt * FROM emp WHERe dept_id IN (SELECt id FROM dept WHERe NAME = '财务部' OR NAME = '市场部');
-
子查询的结果是多行多列
-- 子查询可以作为一张虚拟表参与查询 -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 SELECt * FROM dept t1 ,(SELECt * FROM emp WHERe emp.`join_date` > '2011-11-11') t2 WHERe t1.id = t2.dept_id;
概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
操作
- 开启事务：start transaction；
- 回滚：rollback；
- 提交：commit；
- mysql如果只开启事务没有回滚或者提交，则在关闭窗口后不会对源数据进行修改
事务提交方式
- 自动提交
- mysql就是自动提交的
- 一条DML（增删改)语句会自动提交一次事务
- 如果开启事务（start transaction）则为手动提交
- 手动提交
- oracle 数据库默认是手动提交事务
- 需要先开启事务，再提交
- 修改事务的默认提交方式
- 查看事务的默认提交方式：SELECt @@autocommit；-- 1代表自动提交 0代表手动提交
- 修改默认提交方式：set @@autocommit = 0;修改后如果不写commit将会在关闭窗口后失效
事务的四大特征
- 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
- 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
- 隔离性：多个事务之间。相互独立。
- 一致性：事务操作前后，数据总量不变
事务的隔离级别
-
存在问题
-
脏读（读未提交）：一个事务，读取到另一个事务中没有提交的数据。无论是回滚还是提交都是在源数据上进行了修改
-
不可重复读(虚读，读已提交)：在同一个事务中，两次读取到的数据不一样。
-
幻读：幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，同时再查询时还是发现某记录不存在。
-
举例说明： step1 T1: SELECT * FROM `users` WHERe `id` = 1; step2 T2: INSERT INTO `users` VALUES (1, 'big cat'); step3 T1: INSERT INTO `users` VALUES (1, 'big cat'); step4 T1: SELECt * FROM `users` WHERe `id` = 1; T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。 T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。 在 RR 隔离级别下，step1、step2 是会正常执行的，step3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，因为 T1 在 step1 中读取的数据状态并不能支撑后续的业务操作，T1：“见鬼了，我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以”。T1 不敢相信的又执行了 step4，发现和 setp1 读取的结果是一样的（RR下的 MMVC机制）。此时，幻读无疑已经发生，T1 无论读取多少次，都查不到 id = 1 的记录，但它的确无法插入这条他通过读取来认定不存在的记录（此数据已被T2插入），对于 T1 来说，它幻读了。
-
-
-
隔离级别
-
read uncommitted：读未提交。产生的问题：脏读、不可重复读、幻读
-
read committed：读已提交 （Oracle）。产生的问题：不可重复读、幻读
-
repeatable read：可重复读 （MySQL默认）。产生的问题：幻读
-
serializable：串行化。可以解决所有的问题
-
注意：隔离级别从小到大安全性越来越高，但是效率越来越低
-
数据库查询隔离级别：select @@tx_isolation;
-
数据库设置隔离级别（设置隔离级别后需要重新打开数据库才能生效）：set global transaction isolation level 级别字符串;
-
注意事项
- 如果事务中所有sql语句执行正确则需要自己手动提交commit；否则有任何一条执行错误，需要自己提交一条rollback，这时会回滚所有操作，而不是commit会给你自动判断和回滚。
- 新开一个事务会将该连接中的其他未提交的事务提交，相当于commit！
- 事务既没有提交也没有回滚时连接断开数据库会自动回滚
- 事务中，update语句如果没有commit的话，你再重新执行update语句，就会等待锁定，当等待时间过长的时候，就会报ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的错误。
-
setint 设置错误直接查询的结果
当age为int类型时，在数据库中也支持 where age = ‘112’ 的查询方式。
1. 概念：Java DataBase Connectivity Java 数据库连接， Java语言操作数据库 * JDBC本质：其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。 2. 快速入门： * 步骤： 1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar 1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下 2.右键-->Add As Library 2. 注册驱动 3. 获取数据库连接对象 Connection 4. 定义sql 5. 获取执行sql语句的对象 Statement 6. 执行sql，接受返回结果 7. 处理结果 8. 释放资源
* 代码实现（异常直接抛出）
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接对象 Connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/self_study", "root", "root");
//定义sql
String sql = "update bank set money=10";
//获取执行sql语句的对象 Statement
Statement statement = conn.createStatement();
//执行sql，接受返回结果
int count = statement.executeUpdate(sql);
//处理结果
System.out.println(count);
//释放资源
statement.close();
conn.close();
* 代码实现（异常进行抓取）
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///self_study","root","root");
stmt = conn.createStatement();
String sql = "update bank set money = 1";
stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3. 详解各个对象：
1. DriverManager：驱动管理对象
* 功能：
1. 注册驱动：告诉程序该使用哪一个数据库驱动jar
static void registerDriver(Driver driver) :注册与给定的驱动程序 DriverManager 。
写代码使用： Class.forName("com.mysql.jdbc.Driver");
通过查看源码发现：在com.mysql.jdbc.Driver类中存在静态代码块
注意：mysql5之后的驱动jar包可以省略注册驱动的步骤。
2. 获取数据库连接：
* 方法：static Connection getConnection(String url, String user, String password)
* 参数：
* url：指定连接的路径
* 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
* 例子：jdbc:mysql://localhost:3306/db3
* 细节：如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称
2. Connection：数据库连接对象
1. 功能：
1. 获取执行sql 的对象
* Statement createStatement()
* PreparedStatement prepareStatement(String sql)
2. 管理事务：
* 开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务
* 提交事务：commit()
* 回滚事务：rollback()
3. Statement：执行sql的对象
1. 执行sql
1. boolean execute(String sql) ：可以执行任意的sql 了解
2. int executeUpdate(String sql) ：执行DML（insert、update、delete）语句、DDL(create，alter、drop)语句
* 返回值：影响的行数
3. ResultSet executeQuery(String sql) ：执行DQL（select)语句
4. ResultSet：结果集对象,封装查询结果
* boolean next(): 游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回false，如果不是则返回true
* getXxx(参数):获取数据
* Xxx：代表数据类型 如： int getInt() , String getString()
* 参数：
1. int：代表列的编号,从1开始 如： getString(1)
2. String：代表列名称。 如： getDouble("balance")
* 注意：
* 使用步骤：
1. 游标向下移动一行
2. 判断是否有数据
3. 获取数据
* 代码实现：
Connection conn = null;
Statement state = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///self_study", "root", "root");
state = conn.createStatement();
String sql = "select * from bank";
ResultSet resultSet = state.executeQuery(sql);
while (resultSet.next()){
String name = resultSet.getString("name");
int money = resultSet.getInt("money");
//或者用以下形式
System.out.println(name + "------" + money);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (state != null){
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5. PreparedStatement：执行sql的对象（常使用，能够解决数据库中where true -- a' or 'a'='a 返回全部的问题）
经常使用PrepareStatement而不用Statement createStatement()；
conn = JDBCUtils.getConnection();
//定义sql
String sql = "select * from user where username = ? and password = ?";
//获取执行sql的对象
pstmt = conn.prepareStatement(sql);
//给?赋值
pstmt.setString(1,username);
pstmt.setString(2,password);
//4.执行查询,不需要传递sql 曾删改都是 ps.executeUpdate();
rs = pstmt.executeQuery();
6. 批处理 --- 优点：在exccutebatch时会将之前的sql语句在数据库中一次性处理完毕，节约数据库开销。
* Statement 批处理
//添加要批量执行的SQL
Statement st = conn.createStatement();
st.addBatch(sql1);
st.addBatch(sql2);
st.addBatch(sql3);
//执行批处理SQL语句
st.executeBatch();
//清除批处理命令
st.clearBatch();
* PrepareStatement 批处理
ps = conn.prepareStatement("insert into t_u_h values(?,?) ");
for (String hobby : hobbies) {
ps.setInt(1,Integer.valueOf(id));
ps.setInt(2,Integer.valueOf(hobby));
ps.addBatch();
}
ps.executeBatch();
ps.clearBatch();
JDBC管理事务
使用Connection对象来管理事务
* 开启事务：`setAutoCommit(boolean autoCommit)` ：调用该方法设置参数为false，即开启事务
* 在执行sql之前开启事务
* 提交事务：`commit()`
* 当所有sql都执行完提交事务
* 回滚事务：`rollback()` 。rollback用不用效果都一样，但是不用rollback()可能导致被锁住的数据不能及时的释放（需要等事物超时释放）,会影响下一次的事物操作。
* 在catch中回滚事务,而且要抓取所有的异常
catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
数据库连接池
- 概念：其实就是一个容器(集合)，存放数据库连接的容器。
当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。 - 实现：
-
标准接口：DataSource javax.sql包下的
- 方法：
- 获取连接：getConnection()
- 归还连接：Connection.close()。如果连接对象Connection是从连接池中获取的，那么调用Connection.close()方法，则不会再关闭连接了。而是归还连接
- 方法：
-
一般我们不去实现它，有数据库厂商来实现
- C3P0：数据库连接池技术
- Druid：数据库连接池实现技术，由阿里巴巴提供的
-
C3P0：数据库连接池技术
* 步骤：
1. 导入jar包 (两个) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar ，
* 不要忘记导入数据库驱动jar包
2. 定义配置文件：
* 名称： c3p0.properties 或者 c3p0-config.xml
* 路径：直接将文件放在src目录下即可。
3. 创建核心对象 数据库连接池对象 ComboPooledDataSource
4. 获取连接： getConnection
ComboPooledDataSource comb = new ComboPooledDataSource();
Connection conn = comb.getConnection();
* 可能出现的错误
数据库连接不上：配置文件的数据库名字没有改
5. Druid：数据库连接池实现技术，由阿里巴巴提供的
1. 步骤：
1. 导入jar包 druid-1.0.9.jar
2. 定义配置文件：
* 是properties形式的
* 可以叫任意名称
3. 加载配置文件。Properties
4. 获取数据库连接池对象：通过工厂来来获取 DruidDataSourceFactory
5. 获取连接：getConnection
* 代码：
Properties pro = new Properties();
InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
Connection conn = ds.getConnection();
Spring JDBC
* Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
* 步骤：
1. 导入jar包
commons-logging-1.2.jar
spring-beans-5.0.0.RELEASE.jar
spring-core-5.0.0.RELEASE.jar
spring-jdbc-5.0.0.RELEASE.jar
spring-tx-5.0.0.RELEASE.jar
2. 创建JdbcTemplate对象。在构造中填入DataSource对象
* JdbcTemplate template = new JdbcTemplate(DataSource);
//该DataSource对象可以利用DruidDataSourceFactory对象获得
//DataSource ds = DruidDataSourceFactory.createDataSource(pro);
3. 调用JdbcTemplate的方法来完成CRUD的操作
* update():执行DML语句。增、删、改语句
jdbcTemplate = new JdbcTemplate(DruidDataSourceFactory.createDataSource(properties));
jdbcTemplate.update("update bank set name = 'zhangsan' where name = ?","wangwu");
* queryForMap():查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合
* 注意：这个方法查询的结果集长度只能是1
Map map = jdbcTemplate.queryForMap("select * from bank where name = 'lisi'");
* queryForList():查询结果将结果集封装为list集合
List> maps = jdbcTemplate.queryForList("select * from bank");
* 注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
* query():查询结果，将结果封装为JavaBean对象（javaBean对象的属性一定要用包装类，因为查询到的数据如果为null，就不能赋值）
* query的参数：RowMapper
*（常用）一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装。javaBean对象的属性一定要用包装类，因为查询到的数据如果为null，就不能赋值
List query = jdbcTemplate.query(sql, new BeanPropertyRowMapper(Emp.class));
Emp为一个自己写的封装类，将想要查询表的信息封装在Emp类中，写上get、set、toString方法
*（不常用）实现RowMapper接口，需要自己将返回的值填充到Emp对象中
List list = template.query(sql, new RowMapper() {
@Override
public Emp mapRow(ResultSet rs, int i) throws SQLException {
Emp emp = new Emp();
String ename = rs.getString("ename");
emp.setEname(ename);
return emp;
}
* queryForObject：查询结果，将结果封装为对象
* 一般用于聚合函数的查询
Long total = template.queryForObject(sql, Long.class);
设置数据库时间
1 select now(); #查看现在的时间，可以见到跟实际时间差8小时 2 show variables like "%time_zone%"; #可以看到时区设置，可以看到是使用SYSTEM时间 3 set global time_zone = '+8:00'; #修改mysql全局时区为北京时间，即我们所在的东8区 4 set time_zone = '+8:00'; #修改当前会话时区 5 flush privileges; #立即生效Oracle
单双引号格式：
字符串、时间 单引号
as（重命名）后必须双引号
大小写敏感：
语法大小写不敏感
字符串大小写敏感
用户-
以sys超级用户名，dba角色，即超级管理员身份解锁scott方案/用户，并为scott设置一个密码为tiger
在cmd中：
C:Documents and SettingsAdministrator>sqlplus / as sysdba
SQL>alter user scott account unlock;
SQL>alter user scott identified by tiger;
普通用户scott
密码tiger -
进入与退出orcl数据库(sqlplus)
------以超级管管理员角色进入
c:/>sqlplus / as sysdba
sql>exit
------以普通用户进入
c:/>sqlplus scott/tiger
sql>exit
oracle中的数据类型有：
字符串 varchar2（10）/varchar（10） — varchar2变长/varchar 定长 。10的单位为byte（字节）,gbk中一个汉字为两个字节
— varchar2更加的节省资源，且上下兼容
数值类型 number(5,2) — 5位（包含小数点后2位），小数点后保留2位
日期类型 date
-- 创建表
create table tb_emp(
name varchar(12),
ename varchar2(12),
age number(4,2),
birth date default sysdate
)
查询
NVL(Expression 1, Expression 2)
如果Expression 1 表达式为null，则返回值为Expression 2。不为null，返回值为Expression 1
select ename || '的薪水是' || sal || '美元' from emp; — （oracle中的||表示字符串连接符，mysql中的||表示逻辑运算符）
select * from emp where hiredate between '20-2月-81' and '23-1月-82';
order by注意：
1)对于数值型，小数值在前，大数值在后
2)对于日期型，年长值在前，年小值在后（mysql不是）
null 值在排序时最大（mysql 中null值在排序时最小）
escape指定转义符号
select * from emp where ename like '%$_%' escape '$';
case···when···select
case job
when 'CLERK' then sal+10
when 'SALESMAN' then sal + 20
else sal
end
from emp;
-- 另一种方法
-- decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)
select decode(job,'CLERK',sal + 10,'SALESMAN',sal + 1,sal) from emp;
回收站
进入回收站
drop table users;
查询回收站中的对象
show recyclebin; — PLsql 中不能使用，可以在sqlplus中使用
闪回，即将回收站还原
flashback table 表名 to before drop;
flashback table 表名 to before drop rename to 新表名;
彻底删除users表
drop table users purge;
清空回收站
purge recyclebin;
[oracle函数api](资料oracle API.chm)
类型转换三大类型转换（number 和 date之间不能转换）
- varchar2变长/char定长---->number
- varchar2/char---->date
- number---->varchar2/char
- date---->varchar2/char
- 转换函数
- to_char()
- to_number()
- to_date()
-- 时期转换为字符串
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
-- 时间转换为字符串
select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual;
select to_char(sysdate,'yyyy-mm-dd HH12:MI:SS AM') from dual;
-- 数字转为字符串, to_char中有规定能够用那些字符($)
select to_char(1124,'$9999.99') from dual;--$1124.00
select to_char(1124,'L9999.99') from dual;--￥1124.00
-- 字符串转为日期
select to_date('2021-12-11 11:11:11','yyyy-mm-dd hh24:mi:ss') from dual;
-- 字符串转数字
select to_number('333') from dual;
隐式转换：
- 在转换时，要确保合法合理，否则转换会失败，例如：12月不会有32天，一年中不会有13月
-- 隐式转换案例 -- 查询1980年12月17日入职的员工（日期隐示式转换） select * from emp where hiredate = '17-12月-80';PostgreSQL
PostgreSQL 9.5.3 中文手册
存储过程 SQL语句EXISTS
-
EXISTS的参数是一个任意的SELECt语句， 或者说子查询。系统对子查询进行运算以判断它是否返回行。如果它至少返回一行，那么EXISTS的结果就为"真"； 如果子查询没有返回行，那么EXISTS的结果是"假"。
-
SELECT col1 FROM tab1 WHERe EXISTS (SELECt 1 FROM tab2 WHERe col2 = tab1.col2);
左查询
- postgresql 左查询 之后还要跟个where条件才能筛选
数组变为字符串
array_to_string(array_agg(要连接的字段 ORDER BY 排序字段 ASC/DESC),'分隔符')
- array_agg 将要连接的字段行成数组返回
- array_to_string 将数组变为字符串
字段信息变为字符串
string_agg(expression,delimiter)
- 事例：
select string_agg(name,',') from tbl_test;
张三,李四,王五
获取序列值
SELECt nextval('SYS_C0036029')
-根据序列'SYS_C0036029'获取主键的id号
主键与序列绑定
alter table 表名 alter column 列名 set default nextval('序列名');
类型转换
order by cast(字段 as 数据类型)
order by cast(pic_id as int)
条件判断函数
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END COALESCE 返回参数中的第一个非null的值数据类型格式化函数
格式化函数
| 函数 | 返回类型 | 描述 | 例子 |
|---|---|---|---|
| to_char(timestamp, text) | text | 把时间戳转成字符串 | to_char(current_timestamp, 'HH12:MI:SS') |
| to_char(interval, text) | text | 把间隔转成字符串 | to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
| to_char(int, text) | text | 把整数转成字符串 | to_char(125, '999') |
| to_char(double precision, text) | text | 把实数或双精度转成字符串 | to_char(125.8::real, '999D9') |
| to_char(numeric, text) | text | 把数字转成字符串 | to_char(-125.8, '999D99S') |
| to_date(text, text) | date | 把字符串转成日期 | to_date('05 Dec 2000', 'DD Mon YYYY') |
| to_number(text, text) | numeric | 把字符串转成数字 | to_number('12,454.8-', '99G999D9S') |
| to_timestamp(text, text) | timestamp with time zone | 把字符串转成时间戳 | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(double precision) | timestamp with time zone | 把 Unix 纪元转成时间戳 | to_timestamp(1284352323) |
日期时间格式模板
| HH | 一天中的小时 （01-12） |
|---|---|
| HH12 | 一天中的小时 （01-12） |
| HH24 | 一天中的小时 （00-23） |
| MI | 分钟 （00-59）minute (00-59) |
| SS | 秒（00-59） |
| MS | 毫秒（000-999） |
| US | 微秒（000000-999999） |
| SSSS | 午夜后的秒（0-86399） |
| AM, am, PM or pm | 正午指示器（不带句号） |
| A.M., a.m., P.M. or p.m. | 正午指示器（带句号） |
| Y,YYY | 带逗号的年（4 位或者更多位） |
| YYYY | 年（4 位或者更多位） |
| YYY | 年的后三位 |
| YY | 年的后两位 |
| Y | 年的最后一位 |
| IYYY | ISO 8601 周编号方式的年（4 位或更多位） |
| IYY | ISO 8601 周编号方式的年的最后 3 位 |
| IY | ISO 8601 周编号方式的年的最后 2 位 |
| I | ISO 8601 周编号方式的年的最后一位 |
| BC, bc, AD或者ad | 纪元指示器（不带句号） |
| B.C., b.c., A.D.或者a.d. | 纪元指示器（带句号） |
| MONTH | 全大写形式的月名（空格补齐到 9 字符） |
| Month | 全首字母大写形式的月名（空格补齐到 9 字符） |
| month | 全小写形式的月名（空格补齐到 9 字符） |
| MON | 简写的大写形式的月名（英文 3 字符，本地化长度可变） |
| Mon | 简写的首字母大写形式的月名（英文 3 字符，本地化长度可变） |
| mon | 简写的小写形式的月名（英文 3 字符，本地化长度可变） |
| MM | 月编号（01-12） |
| DAY | 全大写形式的日名（空格补齐到 9 字符） |
| Day | 全首字母大写形式的日名（空格补齐到 9 字符） |
| day | 全小写形式的日名（空格补齐到 9 字符） |
| DY | 简写的大写形式的日名（英语 3 字符，本地化长度可变） |
| Dy | 简写的首字母大写形式的日名（英语 3 字符，本地化长度可变） |
| dy | 简写的小写形式的日名（英语 3 字符，本地化长度可变） |
| DDD | 一年中的日（001-366） |
| IDDD | ISO 8601 周编号方式的年中的日（001-371，年的第 1 日时第一个 ISO 周的周一） |
| DD | 月中的日（01-31） |
| D | 周中的日，周日（1）到周六（7） |
| ID | 周中的 ISO 8601 日，周一（1）到周日（7） |
| W | 月中的周（1-5）（第一周从该月的第一天开始） |
| WW | 年中的周数（1-53）（第一周从该年的第一天开始） |
| IW | ISO 8601 周编号方式的年中的周数（01 - 53；新的一年的第一个周四在第一周） |
| CC | 世纪（2 位数）（21 世纪开始于 2001-01-01） |
| J | 儒略日（从午夜 UTC 的公元前 4714 年 11 月 24 日开始的整数日数） |
| Q | 季度（to_date和to_timestamp会忽略） |
| RM | 大写形式的罗马计数法的月（I-XII；I 是 一月） |
| rm | 小写形式的罗马计数法的月（i-xii；i 是 一月） |
| TZ | 大写形式的时区名称 |
| tz | 小写形式的时区名称 |
| OF | 时区偏移量 |
-- 根据编号分组后取第一条数据
SELECT * FROM
(SELECt ROW_NUMBER() OVER (partition BY 分组字段 order by 排序字段 desc) rowId,*
from 要查询的表) t
WHERe rowId=1
-- 日期加减
SELECt now()::timestamp + '1 year'; --当前时间加1年
SELECT now()::timestamp + '1 month'; --当前时间加一个月
SELECT now()::timestamp + '1 day'; --当前时间加一天
SELECT now()::timestamp + '1 hour'; --当前时间加一个小时
SELECT now()::timestamp + '1 min'; --当前时间加一分钟
SELECT now()::timestamp + '1 sec'; --加一秒钟
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec'; --加1年1月1天1时1分1秒
SELECT now()::timestamp + (col || ' day')::interval FROM table --把col字段转换成天 然后相加
-- 获取下一个day（如星期一）的日期。对标mysql中的next_day函数
SELECt 'TOMORROW'::date + ( 5 + 7 - extract ( dow FROM 'TOMORROW'::date))::int%7; -- 5代表星期五的序列
-- queryKeyWord不存在查询所有，存在则查询queryKeyWord = '123'的结果
?queryKeyWord is null or ?queryKeyWord = '123'
