Basic Database Operation statements, JDBC programming steps, and jdbc programming steps

Basic Database Operation statements, JDBC programming steps, and jdbc programming steps

We don't know where to start when we get started with data, but in fact we only need to know a few basic operation statements, below I will briefly introduce the basic database operation statements and JDBC programming steps:

Connection command: mysql-h [host address]-u [user name]-p [User Password]

Create database: create database [database name]

Show all databases: show databases;

Open Database: use [database name]

Status of the currently selected DATABASE: select database ();

CREATE a data TABLE: create table [TABLE name] ([field name] [field type] ([field requirements]) [field parameters], ......);

Display the data table field: describe table name;

Current Database Data Table Structure: show tables;

Basic DDL (database object) Statements

Table creation statement

Create table [schema name.] table Name

() Brackets are used to add multiple tables at the same time.

Create a new table based on an existing table:

A: create table tab_new like tab_old (use the old table to create A new table)

B: create table tab_new as select col1, col2... From tab_old definition only

Modify Table statements

Select a table first

Alter table name

(

Xxx yyy. lll

)

1. Add column Definitions

Add column name column type

2. Modify column Definitions

Modify column_name datatype [default exper] [first | after col_name]

Modify the new column type of the column name to be modified

3. Delete Columns

Drop colum_name

Directly drop and add column names

Delete table

After the drop table name is deleted, the corresponding table structure, data, table-related indexes, and constraints are also deleted. table objects no longer exist.

4. Rename

Rename to new table name

Name a new column name

Change old_column_name new_column_name type [default exper] [first | after col_name]

Change old column name new column name column Definition

Truncate

Truncates data from the entire table at one time.

Truncate table name

Add, delete, modify, and query:

1. Add:

Insert into table name values (0, 'test ');

Note: In the preceding statement, the table structure contains Automatically increasing columns, and you must specify a value for the column, usually 0.

Insert into Table name (id, name) values (0, 'zhang san') -- same as above

2. delete data:

Delete from table name;

Delete from table name where id = 1;

Delete structure:

Delete database: drop database name;

Delete table: drop table name;

Delete columns in the table: alter table Name drop column name;

3. Change:

Modify all: updata table name set column name = 'new value, non-numeric single quote ';

Conditional modification: updata table name set column name = 'new value, non-numeric single quotation marks 'where id = 6;

4. check:

Query all data: select * from table name;

Conditional query:

Select * from table name where column name = condition value;

Select * from table name where column name not like (like) 'character value'

Paging query: select * from table name limit offset per page;

The following describes how to execute database operations in java statements, that is, JDBC programming. The basic steps are as follows:

1. Load the database driver

Class. forName (driverClass)

Load MySQL driver

Class. foename ("com. mysql. jdbc. Driver ")

2. Get database connection

DriverManager. getConnection (String url, String user, String pass );

User name and password for url-based database Login

3. Create a Statement object through the connection object

CreateStatement (): Creates a basic Statement object.

PrepareStatement (String SQL): Creates a pre-compiled Statement object based on the input SQL Statement.

PrepareCall (String SQL): Creates a CallableStatement object based on the input SQL statement.

4. Use Statement to execute SQL statements

Excute (): it can execute any SQL statement, but it is troublesome.

ExcuteUpdate (): it is mainly used to execute DML and DDL statements to execute DML statements to return the number of rows affected by SQL statements, and to execute DDL statements to return 0

ExcuteQuery (): Only query statements can be executed.

5. creation result set

Next (), previous (), first (), last (), beforeFirst (), afterLast (), absolute () Move record pointer Method

GetXxx () obtains the value of the Record Pointer pointing to a row or a specific column.

6. Collect data and resources

Disable resources such as ResultSet Statement and Connection.