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.