Summary of database development for JSP learning

This paper summarizes the database development methods of JSP learning. Share with you for your reference. The details are as follows:

SQL language composition:

1 > The data definition language DDL is used to define database objects such as SQL schemas, tables, views, and indexes
2 > Data manipulation language DML data query and data update language
3 > Data Control Language DCL sets or changes database users or roles
4 > SQL statements are embedded in the host language

Data type:

1 > INTEGER SMALLINT REAL NUMERIC DECIMAL FLOAT DOUBLE...
2 > Date and time type TIMESTAMP DATE TIME...
3 > Character and string types CHARACTER CHAR VARCHAR...

Aggregation function: AVG (), COUNT (), MAX (), MIN (), SUM ()...

Scalar function:

Arithmetic function (take absolute value, square)
String function (find string length)
Time-date function (returns the current system time)
Relay data functions

Mode:

The collection of database tables is called a schema and consists of the schema name and the schema owner

CREATE SCHEMA student AUTHORIZATION stu;
DROP SCHEMA student CASCADE;
Database objects in schema are deleted from CASCADE1
The RESTRICT schema does not allow deletion when database objects exist

CREATE TABLE student(
 xuehao CHAR(7) PRIMARY KEY,
 name CHAR(8) NOT NULL,
 sex CHAR(2),
 age SMALLINT,
 jiguan CHAR(20),
 dept CHAR(10) DEFAULT ' The computer ');

Create, modify and delete data tables

ALTER TABLE student ADD address CHAR(30); Add address column
DROP TABLE student CASCADE|RESTRICT

Index creation and deletion

CREATE INDEX xuehao_index ON student(xuehao)
DROP INDEX xuehao_index

UPDATE student SET age=age+1 WHERE xuehao='2004007' UPDATE student SET age= '2004007' WHERE age +1 WHERE xuehao='2004007'

DELETE FROM student;

JDBC programming

jdbc:subprotocal:data source identifier
jdbc:odbc:university connects to the university database as an jdbc odbc bridge
jdbc:odbc:university?user=username & password=password Connection with parameters

Format to connect to a network database

jdbc:subprotocal://[hostname][:port][dbname][?parameter1=value1][ & parameter2=value2]......
jdbc:microsoft:sqlserver://localhost:1433;dataBase=university?user=username & password=password

Use the JDBC driver to connect to the sqlserver database:

Try{
forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=university"
conn=DriverManager.getConnection(url,userName,password);
catch(Exception e){
 System.out.println(e);
}

Use the JDBC-ODBC bridge to connect:

Try{
forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
url="jdbc:odbc:university"
conn=DriverManager.getConnection(url,userName,password);
catch(Exception e){
 System.out.println(e);
}

The Statement object is mainly used to execute SQL statements. One Statement object can be created using the createStatement() method of the Connection object

Statement statement=conn.createStatement();
String sql="select * from student";
ResultSet rs=statement.executeQuery(sql);

ResultSet accepts the return result
If you want to perform insert, delete update, create, drop statements, executeUpdate method should be used

String sql="delete from student where xuehao="+"'0741210'";
int i=statement.executeUpdate(sql);// The return value is the number of affected rows 
System.out.println(i);
public boolean execute()throws Exception

Used to execute SQL statements of unknown type, and used to dynamically process unknown SQL statements

I hope this article is helpful to the JSP program design.