对于关系数据库而言,最基本的数据存储单元是数据表。
SQL:Structured Query Language,结构化查询语言。
主要操作数据库对象。
CREATE TABLE [模式名.] 表名
(
columnName1 datatype [default expr],
...
)
利用子查询建表
CREATE TABLE [模式名.] 表名 [col[, col]]
as
select * from user_inf;
ALTER TABLE 表名
add
(
columnName1 datatype [default expr],
...
);
字符串值由单引号引起。
ALTER TABLE 表名
modify columnName datatype [default expr] [first|afterColName];
first、afterColName指定需要将目标修改到指定位置。
ALTER TABLE 表名
drop columnName;
DROP TABLE 表名;
表结构删除,表对象不再存在;表的所有数据被删除;该表所有相关的索引、约束也被删除。
TRUNCATE 表名;
删除表内的全部数据,但保留表结构。
ALTER TABLE 表名 rename to 新表名;
ALTER TABLE 表名 CHANGE 列名 新列名 type [default expr] [first|afterColName]
5种完整性约束
1、NOT NULL:非空约束,指定某列不能为空。
CREATE TABLE 表名 (id int NOT NULL);
2、UNIQUE:唯一约束,指定某列或者某几列组合不能重复。
1、列级约束
name VARCHAR(255) UNIQUE;
2、表级约束(为多列组合建立唯一约束,或想自行指定约束名)
CREATE TABLE 表名(
# 表级约束语法建立唯一约束
UNIQUE (name),
# 并指定约束名
CONSTRAINT 新约束名 UNIQUE(pass),
# 指定两列组合不允许重复
CONSTRAINT 新约束名 UNIQUE(name, pass)
)
3、MySQL删除约束
ALTER TABLE 表名 DROP INDEX 约束名;
4、大部分数据库删除约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名;
3、PRIMARY KEY:主键,指定该列的值可以唯一地标识该条记录。
1、列级约束
CREATE TABLE 表名 (id INT PRIMARY KEY);
2、表级约束
CREATE TABLE 表名 (
id INT,
CONSTRAINT 约束名 PRIMARY KEY(id)
);
3、建立多列组合的主键约束
CREATE TABLE 表名 (
id INT,
name VARCHAR(255),
PRIMARY KEY(id, name)
);
4、删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;
5、增加主键约束
MODIFY:采用列级约束语法
ADD:采用表级约束语法
6、主键自增长
id INT AUTO_INCREMENT PRIMARY KEY
4、FOREIGN KEY:外键,指定该行记录从属于主表中的一条记录,主要用于保证一个或两个数据表之间的参照完整性。
1、指定两列的联合外键
CREATE TABLE 表名(
Sname VARCHAR(255),
Spass VARCHAR(255),
CONSTRAINT 约束名 FOREIGN KEY(Sname, Spass) REFERENCES 主表名(Fname, Fpass)
);
2、级联删除(定义当删除主表记录时,从表记录也会随之级联删除/从表记录的外键设置为null)
CREATE TABLE 表名(
Sname VARCHAR(255),
FOREIGN KEY(Sname) REFERENCES 主表名(Fid) ON DELETE CASCADE # 也可用ON DELETE SET NULL
);
5、CHECK:检查,制定一个布尔表达式,用于指定对应列的值必须满足该表达式。(MySQL不支持)
CREATE TABLE 表名(
key INT,
CHECK(key>10)
);
创建索引的唯一作用是加速对表的查询,索引通过使用快速路径访问方法来快速定位数据,从而减少了磁盘的I/0。
创建索引:
1、自动
当在表上定义主键约束、唯一约束、外键约束时,系统自动创建对应的索引。
2、手动
CREATE INDEX 索引名 ON 表名(name, pass);
删除索引:
1、自动
数据表被删除时,该表上的索引自动被删除。
2、手动
DROP INDEX 索引名 ON 表名;
数据表中数据的逻辑显示。
创建视图,且不允许修改数据:
CREATE OR REPLACE VIEW 视图名 AS 查询语句 WITH CHECK OPTION;
删除视图
DROP VIEW 视图名;
主要操作数据表里的数据。由insert into、update、delete from三个命令组成。
向数据表中插入记录:
INSERT INTO 表名 (id, name, age) VALUES(1, "sxf", null);
带子查询的插入(要求所选的数据列个数相等、类型匹配):
INSERT INTO 表名 (name) SELECT name FROM 表2;
MySQL多条插入
INSERT INTO 表名 (id, name) VALUES(1, "a"), (2,"b");
修改数据表的记录。
UPDATE 表名 SET name="sxf", pass="123" WHERE id=1;
删除指定数据表的记录。总是整行删除。
DELETE FROM 表名 WHERE id>1 AND id<5;
字符串连接用:concat();为数据列和表达式起别名用:as或空格;为表起别名用:as或空格;去除重复行用:distinct;
SELECT DISTINCT CONCAT("na", "me") AS myName FROM table t WHERE id*2>4;
1、expr2<=expr1<=expr3
expr1 BETWEEN expr2 AND expr3;
2、expr1等于括号里的任意一个表达式的值
expr1 IN(expr2, expr3, ...);
3、字符串匹配,下划线代表一个字符,百分号代表任意多个字符
like ‘_%’;
4、是否为null
is null;
desc降序,默认asc升序。如果按多列排序,则每列的asc、desc必须单独设定。
SELECT * FROM table ORDER BY name DESC, id ASC;
多用在select和where后面。
选出字符长度
SELECT char_length(ip) FROM IP;
计算sin值
SELECT sin(1.57);
为日期添加一定时间
SELECT ADDDATE('1998-01-02', 3);
获取当前日期
SELECT CURDATE();
获取当前时间
SELECT curtime();
如果expr1=null,返回expr2,否则返回expr1
ifnull(expr1, expr2)
如果expr1=expr2,返回null,否则返回expr1
nullif(expr1, expr2)
类似于三目运算符“:?”
if(expr1, expr2, expr3)
如果expr1为null,返回true,否则返回false
isnull(expr1)
选择流程控制
CASE value
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE result
END
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END
将一组记录作为整体计算,返回一个结果。
计算多行expr的平均值,数据类型必须是数值型。distinct不计算重复值;默认all计算重复值
avg([distinct|all] expr)
计算多行expr的总条数,数据类型可以是任意类型。*表示统计该表内的记录行数;distinct不计算重复值
count({*|[distinct|all] expr})
计算多行expr的最大值
max(expr)
计算多行expr的最小值
min(expr)
计算多行expr的综合
sum([distinct|all] expr)
对记录进行显示分组。
相同的记录当成一组,并过滤组having
SELECT * FROM ip GROUP BY ip HAVING count(*)>2;
多列的值完全相同时才会被当成一组
SELECT count(*) FROM ip GROUP BY id, ip;
无需任何连接条件。
SELECT s.*, name FROM studentTable s CROSS JOIN teacherTable t;
以两个表中的同名列作为连接条件;若没有同名列,则效果等同交叉连接。
SELECT s.*, name FROM studentTable s NATURAL JOIN teacherTable t;
显式指定两个表中的哪些同名列作为连接条件,要求必须有同名列;自然连接无法指定。
SELECT s.*, name FROM studentTable s JOIN teacherTable t USING(id);
每个on子句只能指定一个连接条件。如果需要进行N表连接,则需要N-1个join…on对。
SELECT s.*, name FROM studentTable s JOIN teacherTable t ON s.teacher=t.id;
分别使用left[outer]join、right[outer]join、full[outer]join,连接条件通过on子句指定。
SELECT s.*, name FROM studentTable s LEFT JOIN teacherTable t ON s.teacher=t.id;
在查询语句中嵌套另一个查询,支持多层嵌套。
SELECT * FROM (SELECT * FROM ip) t WHERE t.id>1;
SELECT * FROM ip WHERE id>(SELECT Sid FROM Stable WHERE Sname='SXF');
SELECT 语句 UNION SELECT 语句;
MySQL不支持,使用not in代替。
SELECT 语句 MINUS SELECT 语句;
SELECT XX FROM XX WHERE (XX, XX) NOT IN (XX, XX);
MySQL不支持,使用join…on代替。
SELECT 语句 INTERSECT SELECT 语句;
SELECT XX FROM XX JOIN XX ON (XX=XX) WHERE XX=XX;
Java 连接 MySQL 需要驱动包,最新版下载地址为:http://dev.mysql.com/downloads/connector/j/,解压后得到 jar 库文件,然后在对应的项目中导入该库文件。
1、加载数据库驱动
通常使用Class类的forName()静态方法来加载驱动。
Class.forName("com.mysql.ch.hdbc.Driver");
Class.forName("oracle.jdbc.driver.OracleDriver");
2、通过DriverManager获取数据库连接
DriverManager.getConnection("jdbc:mysql://hostname:port/databasename", "user", "pwd");
3、通过Connection对象创建Statement对象
4、使用Statement执行SQL语句
5、操作结果集
6、回收数据库资源
package com.sxf.sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception{
// 1、加载驱动,使用反射知识。
Class.forName("com.mysql.cj.jdbc.Driver");
try {
// 2、使用DriverManager获取数据库连接。
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/tz_music?useSSL=false&serverTimezone=UTC",
"root", "1061700625");
// 3、使用Connection创建Statement对象。
Statement state = conn.createStatement();
// 4、执行SQL语句。
// execute:可执行任何SQL语句,返回一个boolean值
// executeQuery:执行查询语句,返回一个结果集
// executeUpdate:执行DML语句,返回一个整数
ResultSet res = state.executeQuery("select * from tz_music;");
// 5、获取数据。
// next:将记录指针下移一行,若有效则返回true
// getXxx:通过列名或列索引获取记录指针
while (res.next()){
System.out.println(res.getString("music_name"));
}
}catch (Exception e){
e.printStackTrace();
}
}
}
; mysql.ini文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/tz_music?useSSL=false&serverTimezone=UTC
user=root
pwd=1061700625
package com.sxf.sql;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
class Sql{
private String driver;
private String url;
private String user;
private String pwd;
public void initParam(String paramFile) throws Exception{
var props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pwd = props.getProperty("pwd");
}
public void createTable(String sql) throws Exception{
Class.forName(driver);
try (
Connection conn = DriverManager.getConnection(url, user, pwd);
Statement state = conn.createStatement();
)
{
state.executeUpdate(sql);
}
}
}
public class Main {
public static void main(String[] args) throws Exception{
Sql sql = new Sql();
sql.initParam("mysql.ini");
sql.createTable("create table jdbc(id int primary key, name varchar(255));");
System.out.println("OK");
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wfh2YzCv-1590568160482)(C:\Users\SXF\AppData\Roaming\Typora\typora-user-images\image-20200525151944809.png)]
PreparedStatement的好处与作用:
class Sql
{
... ...
public void insertTable() throws Exception{
Class.forName(driver);
try (
Connection conn = DriverManager.getConnection(url, user, pwd);
PreparedStatement state = conn.prepareStatement("insert into jdbc values(?, ?);");
)
{
state.setInt(1, 1);
state.setString(2, "a");
state.executeUpdate();
state.setInt(1, 2);
state.setString(2, "a");
state.executeUpdate();
}
}
}
public class Main {
public static void main(String[] args) throws Exception{
Sql sql = new Sql();
sql.initParam("mysql.ini");
sql.insertTable();
System.out.println("OK");
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EN8mEgxf-1590568160485)(C:\Users\SXF\AppData\Roaming\Typora\typora-user-images\image-20200525154002693.png)]
调用存储过程的SQL语句格式:{call 过程名(? ,?, ?..)},其中的问号作为存储过程参数的占位符。
存储过程的参数既有传入参数,也有传出参数。
public void callProc() {
try {
Class.forName(driver);
Connection conn= DriverManager.getConnection(url, user, pwd);
CallableStatement cstmt = conn.prepareCall("{call add_pro(?, ?, ?)}");
cstmt.setInt(1, 4);
cstmt.setInt(2, 5);
// 注册第三个参数是int类型的输出参数
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.execute();
System.out.println(cstmt.getInt(3));
}catch (Exception e){
e.printStackTrace();
}
}
可滚动:可以使用方法自由移动记录指针的ResultSet。
可更新:可调用ResultSet的方法来修改记录指针所指记录、特定列的值。
public void query(String sql) throws Exception
{
Class.forName(driver);
try (
Connection conn = DriverManager.getConnection(url, user, pwd);
// 传入控制结果集可滚动、可更新的参数
PreparedStatement state = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet res = state.executeQuery();
)
{
res.last();
int rowCount = res.getRow();
for (int i = rowCount; i >0; i--) {
res.absolute(i);
System.out.println(res.getString(1)+" - "+res.getString(2));
res.updateString(2, "abc");
res.updateRow();
}
}
}
sql.query("select * from jdbc");
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JEnHurJ1-1590568160487)(C:\Users\SXF\AppData\Roaming\Typora\typora-user-images\image-20200526175750855.png)]
public void blobDemo(String fileName) throws Exception
{
Connection conn;
PreparedStatement state;
PreparedStatement query;
Class.forName(driver);
try {
conn = DriverManager.getConnection(url, user, pwd);
state = conn.prepareStatement("insert into jdbc values (?,?,?)", Statement.RETURN_GENERATED_KEYS);
query = conn.prepareStatement("select image from jdbc");
String imageName = fileName.substring(fileName.lastIndexOf('\\')+1, fileName.lastIndexOf('.'));
var f = new File(fileName);
var is = new FileInputStream(f);
state.setObject(1, 8);
state.setObject(2, "img");
state.setBinaryStream(3, is, (int)f.length());
int affect = state.executeUpdate();
System.out.println(affect);
ResultSet res = query.executeQuery();
if(res.next()){
Blob imgBlob = res.getBlob(3);
var out = imgBlob.getBinaryStream();
}
}catch (Exception e){
e.printStackTrace();
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kpyagRVy-1590568160489)(C:\Users\SXF\AppData\Roaming\Typora\typora-user-images\image-20200526213528489.png)]
描述其他数据的数据,用以获取关于ResultSet的描述信息。通过**getMetaData()**方法获得ResultSetMetaData对象。需要一定的系统开销。
三个常用方法:
public void metaData() throws Exception
{
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, pwd);
Statement state = conn.createStatement();
ResultSet set = state.executeQuery("select * from jdbc");
ResultSetMetaData rsmd = set.getMetaData();
System.out.println(rsmd.getColumnCount());
System.out.println(rsmd.getColumnName(2));
System.out.println(rsmd.getColumnType(2));
}
RowSet接口下包含jdbcRowSet、CachedRowSet、FilteredRowSet、JoinRowSet、WebRowSet常用子接口。
jdbcRowSet需要保持与数据库的连接;其余4个是离线的RowSet,无需保持连接。
RowSet常用方法:
setUrl(String url)、setUsername(String name)、setPassword(String pwd)、setCommand(String sql)、execute()
public void update(String sql) throws Exception
{
Class.forName(driver);
// 创建RowSetFactory
RowSetFactory factory = RowSetProvider.newFactory();
// 创建实例
JdbcRowSet jdbcRs = factory.createJdbcRowSet();
// 设置连接信息
jdbcRs.setUrl(url);
jdbcRs.setUsername(user);
jdbcRs.setPassword(pwd);
jdbcRs.setCommand(sql);
// 执行查询
jdbcRs.execute();
jdbcRs.afterLast();
// 向前滚动结果集
while (jdbcRs.previous()){
System.out.println(jdbcRs.getString(1));
// 修改记录行
jdbcRs.updateString(2, "123");
// 提交修改
jdbcRs.updateRow();
}
}
直接将底层数据读入内存中,封装成RowSet对象,而RowSet对象完全可以当成Java Bean来使用。
public void query(String sql) throws Exception
{
Class.forName(driver);
// 获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pwd);
Statement stmt = conn.createStatement();
ResultSet res = stmt.executeQuery(sql);
// 创建RowSetFactory
RowSetFactory factory = RowSetProvider.newFactory();
// 创建实例
CachedRowSet cachedRs = factory.createCachedRowSet();
// 装填RowSet
cachedRs.populate(res);
// 关闭资源
res.close();
stmt.close();
conn.close();
cachedRs.afterLast();
// 重新获取数据库连接
Connection conn2 = DriverManager.getConnection(url, user, pwd);
conn2.setAutoCommit(false);
// 把对RowSet所做的修改同步到底层数据库
cachedRs.acceptChanges(conn2);
}
事务ACID特性:原子性atomicity、一致性consistency、隔离性isolation、持续性durability
事务提交:
事务回滚:
普通的提交、回滚都会结束当前事务,但回滚到指定中间点因为依然处于事务之中,所以不会结束当前事务。
调动Connection的**setAutoCommit()**方法来关闭自动提交,开启事务。
当Connection遇到一个未处理的SQLException异常时,系统将会非正常退出,事务也会自动回滚。但如果程序捕获了该异常,则需要再异常处理块中显式地回滚事务。
// 关闭自动提交,开启事务
conn.setAutoCommit(false);
// 提交事务
conn.commit();
// 回滚事务
conn.rollback();
// 创建保存点
point = conn.setSavePoint();
// 回滚到保存点
conn.rollback(point);
Statement stmt = conn.createStatement();
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
... ...
stmt.executeLargeBatch();