常用sql

最新推荐文章于 2023-11-02 14:19:29 发布

金善博

最新推荐文章于 2023-11-02 14:19:29 发布

阅读量311

点赞数

分类专栏：日记

本文链接：https://blog.csdn.net/qq_38325853/article/details/88218691

版权

日记专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Java 数据库操作（菜鸟随笔）

一、JDBC

1.通过JDBC访问数据库

步骤
1）导入目标数据库产品的JDBC驱动。
2）通过Class.forName加载JDBC驱动，注册到DriverManager中。
3）建立连接，DirverManager.GetConnection获取COnnection对象。
4）创建Statement对象或预编译的PreparedStatement对象。
5）执行sql，查询操作结果
6）倒序关闭。

最简示例代码段-顺序同上

//引入驱动包
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getCOnnection("jdbc:mysql://localhost:3306/DBName","yonghuming","mima")：
Statement statement=con.createStatement();
//只做查询
ResultSet rs = statement.executeQuery("select * from tableName");
while(rs.next)
{
    //假设表的第一列是id数值类型，第二列name字符串类型 列索引从1开始
    System.out.print(rs.getInt(1))
}
//依次关闭rs、statement、con释放所占用资源

CLass.forName(“com.mysql.jdbc.Driver”)作用：
通过类加载机制来创建对象，增加可扩展性，此行代码执行时JVM会加载com.mysql.jdbc.Driver对应的Driver类，类中实现了向DriverManager注册自己

2.Statement、PreparedStatement、CallableStatement

1.statement：执行不带参数的简单sql，每次执行SQL语句，数据库对此语句进行编译。
2.PreparedStatement：执行带参数的sql语句，通过占位符传递参数，首次执行SQL语句时，命令会被编译解析，存入缓冲区。再次实行同一PareparedStatement将会使用预编译好的命令，但仍需再次解析
    优点：
    1）执行少一次编译，效率更高。
    2）放置sql注入。
    3）可读性更好。
3.CallableStatement：用于调用存储过程。**类似ADO.NET 中设置Command的CommandType为StoredProcedure**

3.JDBC处理事务

事务：不可分割的操作，每一步都正常执行一个事务才提交完成执行，否则回归原样（fh）。
采用commit或rollback结束事务操作...（fh）
1.JDBC中事务默认自动提交。通过以下实现将对几个数据库操作作为事务事务：
    1）setAutoCommit(false)禁止自动提交。
    2）依次执行每一步操作，完成的话Commit()提交，其中任一步出现错误，捕获异常的代码块中rollback。
（ADO.NET 中 使用连接类SqlConnection中定义了BeginTransaction()方法得到SqlTransaction对象，给SqlCommand对象的Transaction属性绑定，调用SqlTransaction对象的提交和回滚方法。而在JDBC中提交和回滚是Connection类的子方法）。
2.多线程操作相同数据使用锁：JDBC事务隔离级别
    事务隔离级别（通过setTransactionLevel方法来设置,get获取）：
    （TRANSACTION_省略）
    1）NONE_JBD：不支持事务隔离。
    2）READ_UNCOMMITTED：读未提交数据容易导致：1 2 3
    3）READ_COMMITTED：读已提交数据，可能导致： 2 3
    4) REPEATABLE_READ：可重复读取数据，可能导致：3
    5）SERIALIZAABLE：可序列化。不会出现：1 2 3
    
    级别越高避免冲突消耗的资源也越多
    
    常见错误附
    1 脏数据：事务A读取到的并发执行的事务B更新但并未提交的无效数据
    2 不可重复读：事务A读取一次数据后，并发执行的事务B更新了数据，导致A再次查询的时候访问数据不一样
    3 虚读：事务A执行查询数据后，并发执行的事务B执行增删一条满足查询条件的数据导致A第二次查询到的结果和前次不同（多或少n条记录）

4.ResultSet结果集

get所需要数据类型(列索引（1开始）或 列明)：
将指定列的数据按所需的数据类型获取。
这几种方法将会在被调用的时候，一次性把数据放到内存中。
getObject方法数据不会被一次性读到内存中去，每次调用从数据库获取数据。

5.注意

JDBC连接池限制连接的数目，连接占用资源，编程过程中要确保释放无用的资源。
每次conn.createStatement()或conn.prepareStatement相当于在数据库中打开一个游标，所以尽量不要放在循环中
JDBC驱动包和Mysql版本一定要兼容、JDBC驱动包和Mysql版本一定要兼容、JDBC驱动包和Mysql版本一定要兼容---出现过错误印象深刻，把最开始的一步看版本给忘了。

JDO：JDBC的补充，觉得类似ADO.NET。

二、SQL

SQL（Structured Query Language）功能包括数据的查询、操控、定义、控制。

1.基本sql语句

select

select 字段名 from 表名（视图或查询结果） where 条件

insert

**此种数据操纵操作如果用于视图，可通过触发器来实现操作**
insert into table（字段-可选）values （值1、值2） -添加字段名和后面值对应，如果未设置字段名那么values中必须添加所有所需信息，例自增主键则不需要

update

update table set 字段名=字段值 where 条件语句（级联更新 删除外键依赖，重建外键依赖加上ON UPDATE CASCADE）

delete

delete from 表名 where 条件语句 （级联删除 删除外键依赖，重建外键依赖加上ON DELETE CASCADE）

truncate

truncate table 表名 -DELETE 速度快，且使用的系统和事务日志资源少。
注意
1）只记录一条删除记录，而delete会逐条记录
2）不能用于删除被外键依赖的主键表（删除只记录一条无法激活触发器）
3）列自增标识将会被重新初始
4）不会被rollback撤销操作。

create

create table 表名(
    列名 类型 约束···
    ```
)

drop

drop table tablename 作为被依赖的主键表，首先删除约束。

grant

例：权限可多选，这里赋予选择权限
grant select on 表名（数据库.*）to 角色名  with grant option （可选，为用户赋予-赋予其他用户其拥有权限-的权限）

revoke

跟grant语法相同 to - trom

2.索引、约束、触发器

sql索引

索引可看做特殊的目录，使用索引可以使查询的速度更快
聚集索引或非聚集索引

聚集索引：实际存储顺序和索引顺序是相同的，即物理存储顺序和聚集索引顺序一致。
作用：快速缩小查找范围，实现快速查找
每张表只能按一定顺序存储，故而聚集索引只能存在一个。
聚集索引的
非聚集索引：实际存储顺序和物理存储顺序不一致。
速度上因为物理存储位置和索引顺序不相符，查找顺序不如聚集索引(仅当索引列相同时，按列查询)
理解：非聚集索引目录存储的是每一列的位置，如果表中有聚集索引那么该位置就是聚集索引中对应该列的key，如果该表没有聚集索引那么存储的就是对应列的rid 相对聚集索引的一步到位，非聚集索引需要两步。

合理使用索引
索引使用相关：
索引：列经常被分组和排序，相同值很少。
聚集索引：返回某数据范围内的数据、小数目的不同值，主外键列。
非聚集索引：大数目不同值，频繁更新的列，主外键，频繁修改索引。
对上述的大数目不同值，小数目不同值理解不来
理解
聚集索引最好建立在实际需要依靠进行查询最多，且按照范围查询的列，不要盲目建立在主键。
建立无用的索引并不会提高查询速度
可以把需要快速查询的字段加入聚集索引（复合索引）。用复合索引的起始列来查询能显著提高查询速度，想象例如：英文字典查询search 通过首字母s来判断位置再根据第二个字母、第三个字母…来进一步查询。直接查询第二个字母e来查这个就很诡异，直接用最后一个字母h查询累死你。
注意
创建索引将会在提高查询速度的同事降低更新表的速度。
建立索引会占用索引文件，当一张表中出现多个复合索引的时候索引文件将会快速增长。
where中使用了索引那么order by不会使用索引。
首字母使用通配符来匹配不会使用索引。
对索引列不要进行运算操作。
不要使用否定的运算符：如<>

创建索引语句：

ALTER TABLE tableName ADD INDEX（UNIQUE、 PRIMARY KEY,FULLTEXT ） index_name (列或列集合[复合索引])
创建主键：默认将会自动创建主键的聚集索引。

可以看做mysql中主键就是聚集索引
mysql 中的索引分类

1.普通索引 INDEX
2.唯一索引 UNIQUE
3.主键索引：PRIMARY KEY
4.组合索引：上面提到的复合索引。
5.全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。FULLTEXT 。

…
先到这过```
…
####sql约束
约束用于限制加入表的数据的类型。
常见几种：
1.NOT NULL
2.UNIQUE
3.PRIMARY KEY ≈ NOT NULL + UNIQUE
4.FOREIGN KEY
5.CHECK
6.DEFAULT
字面意思…

sql 语句（创建过程中略…）

1：ALTER TABLE 表名 MODIFY 列名列类型 NOT NULL。
2-3：ALTER TABLE 表名 ADD CONSTRAINT 约束名约束类型 (列或列集合)。
4：ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (外键列名) REFERENCES 主键表名(主键表的主键列名)。
5：ALTER TABLE 表名 ADD CHECK (约束条件之间用or连接例：A=1 OR A=2)。
6：ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值。

过。。

SQL触发器

也是一种存储过程，由事件触发，类似响应函数。
特点：
1）数据操纵DML（常用）语句发生时隐式调用-DML触发器。DDL触发器是响应数据定义事件的存储过程。
2）触发器内禁止commit和rollback。
3）不接受参数。

使用触发器好处：
1）增加安全性。
2）记录用户进行的修改以及相关信息，跟踪用户操作。
3）实现复杂完整性约束（例如：修改主键表某一列，先修改外键表的依赖列）
4）自动触发，每次对表数据进行修改都会被激活，可以进行值累加。对累加值做判断进行进一步处理

简单创建触发器 常用增删改。
 CREATE TRIGGER trigger_name 
 trigger_time trigger_event 
 ON tb_name 
 FOR EACH ROW
 trigger_stmt

After和Instead Of

after：sql语句执行后触发。
INstead of：sql语句执行前截获并不会执行相应sql语句，只有触发器中的事件才会触发

mysql中只支持after和before

sql 执行前后。

inserted、deleted 对应 mysql 中的 new old…
mysql只支持行级触发器（每行受影响触发器都执行一次），必须加foreach rows

mysql 查看所有触发器、存储过程、函数、视图、表____https://www.cnblogs.com/sx2zx/p/6245887.html

3.SQL存储过程

create procedure 存储过程名 @[参数名][类型]
as
begin
end

附：
DELIMITER的使用：默认;符号后回车执行sql语句 可DELIMITER 其他符号如（$$）来设置结束符号--使用完记得重新设置为;
out，in 输入输出参数：
    定义存储过程是后在参数前面加上in 或者 out
    调用：
    out: call 存储过程名(@输出参数); 不加out关键字。
    in：call 存储过程名(参数);
    out+in call 存储过程名(参数，@输出参数)--11对应

和函数的差别：
1）独立部分执行，函数可作为查询结果(返回值都没有做个鬼查询结果)。
2）实现过程比较复杂，函数的针对性较强。
3）函数只能返回一个值（或表对象），，存储过程能返回多个out参数。
4）存储过程不可以嵌入到sql中使用。
5）函数不能直接操作实体表，只能操作内建表。
6）存储过程创建时就在服务器上进行编译，执行速度比函数快。

附：函数创建方法：
函数不另外说
CREATE FUNCTION 函数名(参数 类型)  
RETURNS 返回值类型  
BEGIN  
    执行语句
END

###4.sql常用函数
太多了现用现找…
###5.其他
sql 范式

第一范式：每一列不可分割，能唯一确定，例如【学号班级+姓名】分解成【学号年级+班级+姓名】班级字段可拆分。
第二范式：非关键字段对每一个候选关键字段都不存在部分依赖例如：候选码AB 中的A 就可以唯一确定非主属性C。则存在部分依赖。不符合部分依赖。
第三范式：非主属性对任何候选关键字都不存在传递依赖。传递依赖： A得到B B得到C 反过来均不成立。例如学号生源地编号生源地名称表学号是候选键和生源地名之间是传递依赖。
BCNF：对任何属性对候选关键字都不存在传递依赖。表中字段 ABCD A→B D为非主属性 AC 和 BC都能唯一确定 D 而且D对AC 和BC都不存在传递依赖满足第三范式但不满足BCNF。 AB关键字段相互依赖不符合
第四范式：将表中1对多的列，另外建表。

sql 视图

是虚拟表不物理存在的。数据库中只存在了对视图逻辑的定义而没有这张‘表的存在’
例：两行表 A学号→姓名 B学号→身高
想得到学号+姓名+身高的逻辑窗口
CREATE VIEW view_name AS
SELECT A.学号 as number,A.姓名 as name,B.身高 as heigh
FROM A,B
INNER JOIN A.学号=B.学号
可以得到展示学号+姓名+身高的‘虚拟表’视图实际没有这张表存在，可以对视图上进行操作例如进行查询 select * from view_name where heigh>160 查询得到身高大于160的学生的姓名学号和身高。

内外左右连接

假设有两张表A B 通过 AB两张表各自的id进行连接 A连接B
内连接两张表共有的id集合
外连接两张表所有id，包含A和B单独有的id 集合
左连接 A有不管B有没有的id集合
右连接 B有不管A有没有的id集合

得到所有id在集合内的数据用id连接显示。
差不多这意思。

sql 查询常用

SELECT DISTINCT 列名称 FROM 表名称
如果想查询指定列唯一的所有数据：distinct必须在开头位置，可通过加个运算将distinct在里边起作用

聚集函数
ACG、COUNT、MAX、MIN、SUM。
Group分组
Group by 子句可以包含n个列。
select 子句中每列都必须在group by 子句中出现(除了聚集函数)。
other

UNION 合并两个或多个‘SELECT’语句的结果集。
TOP 跟行数量或者百分数+PERCENT ，mysql中使用limit 如果使用两个参数 1是偏移量 2是获取>条数。。
别名:
A as B = A B
通配符：
% 1或者n个字符
- 一个字符
[charlist] 任一字符 [^cjharList] 集合外任意字符

先看到这。。以后遇到问题再补充

金善博

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
常用sql

Java 数据库操作（菜鸟随笔）一、JDBC1.通过JDBC访问数据库步骤1）导入目标数据库产品的JDBC驱动。2）通过Class.forName加载JDBC驱动，注册到DriverManager中。3）建立连接，DirverManager.GetConnection获取COnnection对象。4）创建Statement对象或预编译的PreparedStatement对象。5）执...
复制链接

扫一扫