离散型笔记
一、基本数据库知识
域约束:
规定一个属性的允许值。
主键约束:
用来标识一个关系中的元组。
外键约束:
定义了关系之间的关系。
强制完整性约束:
数据库管理系统强制性执行完整性约束并且拒绝违反约束的操作。
超键:
是一个属性或一组属性,它唯一地标识了一个关系。
键(key):
是一个最小的超键。
二、SQL
创建表
create table Course (
courseId char(5),
subjectId char(4) not null,
courseNumber integer,
title varchar(50) not null,
numOfCredits integer,
primary key (courseId)
);
创建了一个名为Course的表,包含属性courseId,subjectId,courseNumber,title和numOfCredits。
每一个属性都有一个数据类型。
drop table Course;
删除一个表。
insert into tableName [(column1,column2,...,columnn)]
values (value1,value2,...,valuen);
插入一个表。
更新表格
update tableName
set column1 = newValue1 [,column2 = newValue2,...]
[where codition];
从表中删除记录的一般语法
delete [from] tableName
[where condition];
从表中获取信息
select column-list
from table-list
[where condition];
select子句列出所选定的列。from子句指定查询所涉及的表。可选的where子句指明选择行的条件。
运算符
=等于
<>或!=不等于
<小于
<=小于等于
>大于
>=大于等于
not逻辑非
and逻辑与
or逻辑或
运算符like,between-and和is null
检验字符串s是否含有模式p的语法
s like p或s not like p
检查值v是否在值v1和v2之间
v between v1 and v2或v not between v1 and v2
检查值v是否为null
v is null或v is not null
使用列的别名
select columnName [as] alias
算术运算示例
select title,50 * numOfCredits as "Lecture Minutes Per Week"
from Course
where subjectId = 'CSCI';
显示互不相同的元组
select distinct subjectId as "Subject ID"
from Course;
显示有序的元组
select column-list
from table-list
[where condition]
[order by columns-to-be-sorted];
联结表
select distinct lastName,firstName,courseId
from Student,Enrollment
where Student.ssn = Enrollment.ssn and lastName = 'Smith' and firstName = 'Jacob';
三、JDBC
数据库应用程序的Java API称为JDBC,JDBC API是一个Java应用程序接口,使SQL数据库一般化。
主要接口:Driver,Connection,Statement,ResultSet
JDBC API定义了这些接口,JDBC驱动程序为这些接口提供实现,程序员使用这些接口。
JDBC应用程序使用Driver接口加载一个合适的驱动程序,
使用Connection接口连接到数据库,
使用Statement接口创建和执行SQL语句,
如果语句返回结果的话,使用ResultSet接口处理结果
访问数据库
加载驱动程序
Class.forName("JDBCDriverClass");//驱动程序是一个实现接口java.sql.Driver的具体类。
建立连接
Connection connection = DriverManager.getConnection(databaseURL);
创建语句
Connection缆道,Statement缆车,它为数据库传输SQL语句,把结果返回程序
Statement statement = connection.createStatement();
执行语句
statement.executeUpdate("create table Temp (coll char(5),col2 char(5))");//executeUpdate(String sql)执行SQL DDL或更新语句
ResultSet resultSet = statement.executeQuery("select firstName,mi,lastName from Student where lastName " + " = 'Smith'");//执行SQL查询语句
处理ResultSet
结果集ResultSet存在一个表,当前行可以访问,当前行的初始位置是null
while (resultSet.next())
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + ". " + resultSet.getString(3));
例子
import java.sql.*;
public class SimpleJdbc {
public static void main(String[] args) throws SQLException,ClassNotFoundException {
Class.forName("com.mysql,jdbc.Driver");
System.out.println("Driver loaded");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook","scott","tiger");
System.out.println("Database connected");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select firstName,mi,lastName from Student where lastName " + " = 'Smith'");
while (resultSet.next())
System.out.println(resultSet.getString(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(3));
connection.close();
}
}
package data;
import javax.swing.*;
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
public class FindGrade extends JApplet {
private JTextField jtfSSN = new JTextField(9);
private JTextField jtfCourseId = new JTextField(5);
private JButton jbtShowGrade = new JButton("Show Grade");
private Statement stmt;
public void init() {
initializeDB();
jbtShowGrade.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(ActionEvent e) {
jbtShowGrade_actionPerformed(e);
}
});
JPanel jPanel1 = new JPanel();
jPanel1.add(new JLabel("SSN"));
jPanel1.add(jtfSSN);
jPanel1.add(new JLabel("Course ID"));
jPanel1.add(jtfCourseId);
jPanel1.add(jbtShowGrade);
add(jPanel1,BorderLayout.NORTH);
}
private void initializeDB() {
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver loaded");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook","scott","tiger");
System.out.println("Database connected");
stmt = connection.createStatement();
}
catch (Exception ex) {
ex.printStackTrace();
}
}
private void jbtShowGrade_actionPerformed(ActionEvent e) {
String ssn = jtfSSN.getText();
String courseId = jtfCourseId.getText();
try {
String queryString = "select firstName,mi, " + "lastName,title,grade from Student,Enrollment,Course " + "where Student.ssn = '" + ssn + "' and Enrollment.courseId " + "= '" + courseId + "' and Enrollment.courseId = Course.courseId " + " and Enrollment.ssn = Student.ssn";
ResultSet rset = stmt.executeQuery(queryString);
if (rset.next()) {
String lastName = rset.getString(1);
String mi = rset.getString(2);
String firstName = rset.getString(3);
String title = rset.getString(4);
String grade = rset.getString(5);
JOptionPane.showMessageDialog(null, firstName + " " + mi + " " + lastName + "'s grade on course " + title + " is " + grade);
}
else {
JOptionPane.showMessageDialog(null, "Not found");
}
}
catch (SQLException ex) {
ex.printStackTrace();
}
}
}
PreparedStatement
它是Statement接口扩展而来的,用于执行含有或不含有参数的预编译的SQL语句。
Statement preparedStatement = connection.prepareStatement("insert into Student (firstName,mi,lastName) " + "values (?,?,?)");//三个问号用作参数的占位符,表示表Student中一条记录的firstName,mi,lastName的值。
设置参数的方法
setX(int parameterIndex,X value);//X是参数的类型,parameterIndex是语句中参数的下标。下标从1开始。
获取库元数据
DatabaseMetaData dbMetaData = connection.getMetaData();
结果集元数据
ResultSetMetaData rsMetaData = resultSet.getMetaData();
import java.sql.*;
public class TestResultSetMetaData {
public static void main(String[] args) throws SQLException,ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver loaded");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook","scott","tiger");
System.out.println("Database connected");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from Enrollment");
ResultSetMetaData rsMetaData = resultSet.getMetaData();
for (int i = 1;i <= rsMetaData.getColumnCount();i++)
System.out.printf("%-12s\t",rsMetaData.getColumnName(i));
System.out.println();
while (resultSet.next()) {
for (int i = 1;i <= rsMetaData.getColumnCount();i++)
System.out.printf("%-1s\t",resultSet.getObject(i));
System.out.println();
}
connection.close();
}
}