3rd days: Java Database Programming JDBC + MySql

MySQL/jdbc
1. Database test:/usr/local/MySQL/bin
Start: Net start MySQL
Stop: net stop MySQL
Connection: mysql-uroot-P
Show database;
Create Database dbname;
Use: use database;
Display table: show tables;
Display details: DESC table_name;
2. Database Data Types
There are three types: numeric, string, and date and time;
(1) Value Type:
Integer
Tinyint: a positive integer with a very small 1 byte
Smallint: 2-byte small integer
Mediuint: A 3-byte integer of medium size.
INT: 4-byte standard integer
Bigint: 8-byte big integer
Floating Point Type
Float: 4-byte Single-precision floating point number
Double: 8-byte double-precision floating point number
Decimal: floating point number stored in string format in m + 2 bytes
Attribute of a numeric data column
Unsigned: negative data is not allowed
Zerofill: If the value is smaller than the defined width, add 0 before the value.
Null/not null: determines whether the data column is null.
Default: Specifies the default value of the data column.
Auto_increment: This column can generate a unique sequence.
(2) string type: (it can store any value, text, images, sounds, and compressed packages)
CHAR: Fixed Length. When storing data, fill in spaces on the right that are not long enough.
Varchar: variable length type, which is stored according to the actual length during data storage.
BLOB: a binary string that stores large volumes of information.
Text: A non-binary string that stores large amounts of information.
(3) Date and Time type:
Date: "YYYY-MM-DD"
Time: "HH: mm: SS"
Datetime: "YYYY-MM-DD hh: mm: SS"
Year: "YYYY"
3. Basic operations in the database: Create Select Insert update Delete
Create Table t_name (is int (10 ),...);
Drop table t_name;
Delete from t_name;
Insert into t_name values (...);
Select * From t_name;
Select ID, name from t_name;
Update t_name Set ID = 2 where id = 1;
Alter t_name add column_name char (5 );
Alter field name and type: Alter t_name change old_column_name new_column_name new_type;
Delete field: Alter t_name drop column_name;
Modify Field attributes: Alter t_name modify column_name date;
Example:
Drop table if exists user;
Create Table if not exists user (
Id int not null primary key auto_increment,
Name varchar (40) not null unique,
Age int,
Sex Boolean default false,
Email varchar (50 ),
Phone int (11 ),
Idcard char (18 ),
Qq varchar (10)
) Engine = InnoDB;
4. pagination
MySQL: (query K Records starting from entry m)
Page n of pagesize per page
M = (n-1) * pagesize;
K = pagesize;
Select * From t_name limit M, K;
ORACLE:
Select ID, name, age from (
Select ID, name, age, rownum rn from user where rn <m + k
) Where rn> = m;
5. JDBC
(1) Registration driver
Class. forname ("com. MySQL. JDBC. Driver"); Driver: mysql-connection-java-bin.jar;
Class. forname ("oracle. JDBC. Driver. oracledriver"); driver name: ojdbc14.jar;
(2) create a connection
Connection conn = drivermanager. getconnection ("JDBC: mysql: // localhost: 3306/db_name", username, password );
Connection conn = drivermanager. getconnection ("JDBC: oracle: thin :@ localhost: 1521: db_name", username, password );
(3) create a statement object through connection
Statement stmt = conn. createstatement ();
Preparedstatement PS = conn. preparestatement (SQL );
(4) Execute SQL statement in statement
Stmt.exe cutequery ();
Ps.exe cutequery ();
PS. Set (Key, value );
Three methods for SQL Execution:
Resultset executequery (); --- Select
Int executeupdate (); --- insert update Delete
Boolean execute (); --- the preceding two types can be executed.
(5) processing result and return
(6) Close Resources
Rs. Close ();
Stmt. Close ();
Conn. Close ();
6. Supplement (encoding settings)
(1) Start MySQL parameters when creating a database
Mysql-uroot -- default-character-set utf8;
(2) create a database
Create Database summer (Database Name) default Character Set utf8;
Execute scripts
(3) create a table
Create Table EMP (Table Name) (List ......) Default Character Set utf8;
(4) SQL Script Execution
Source/home/XX. SQL
(5) modify the MySQL database connection
JDBC: mysql: // localhost: 3306/Dy (Database Name )? Useunicode = true & characterencoding = utf8;