Learning notes for "DAY21" JDBC and MySQL

Properties:

----------------

1.extends Hashtable, Thread-safe.

2.key-value

Sql:structurual Query Language, structured querying language.

-------------------------------------------------

Database:

--------------

1. Database

2. A collection of tables.

3. Operation

1. Increase

ID | name | Age

-------------------

1 | Tom | 24

....

INSERT INTO table1 values (+, ' Jerry ', 23);

Insert INTO table1 (id,name,age) VALUES (+, ' Jerry ', 23);

Insert INTO table1 (name,age,id) VALUES (' Jerry ', 23,1000);

Insert INTO table1 (name) VALUES (' Dick '); //

2. Delete

Delete

Delete from students where name is null; //where Conditional clauses

3. Update

Update

Update students set name = ' Tomaslee ', age=33,id=19 where id = 23;//

4. Enquiry

Select: Choose

SELECT * FROM table1 where name = ' Tom ';

SELECT * FROM table1 where name like ' t% ' or age = 12;

SELECT * from students where-is null; //query is a NULL record

Select Id,name from students where the age was not null; //query is a NULL record

Select ... order by ID ASC | Desc //asc Ascending | Desc Descending

SELECT *//full field scan

Select ... [No where] //Full table scan

Select Id,name from Stus; //Select several fields to find, projection query project

SELECT * from Stus where ... limit; //1-offset, offset, 2-length, length, number of records that need to be queried.

5. Crud:create of Data | Retrieve | Update | Delete

.

Table

--------------

1. Two dimensions.

2. Line: Row,record, record.

3. Columns: field, fields.

Rdbms:relation database manage System.

---------------------------------------

1. Structured data

Jdbc:

---------------

JDBC (Java Data Base Connectivity,java database connection) is a Java API for executing SQL statements that provides unified access to a variety of relational databases, consisting of a set of classes and interfaces written in the Java language.

Install MySQL

----------------

1 .....

2.custom Installation

3. Install address C:\myprograms\MySQL Server 5.5\

4 .....

5. Start the Configuration Wizard

6.detailed

7.developer Machine

8.multifunction Database

9 .... Next

10 .....

11. Select the default character set

Manually select a character set (third option)--UTF8

. 12. Create Security settings

Root password:

Enable remote host access.

13. Select Execute

14.ok

15. Verify that MySQL is installed

1. Control Panel---------------mysql--state = = started--OK

2.cmd--Services.msc directly open the Services window

Primary key: Primary key

--------------------

1. Unique, non-repeating

2. Cannot be null

3.create table stus (id int,...) primary key ID;

MySQL Common operations

-------------------

1. Operation 1

Mysql>show databases; //show which databases

Information_schema//Meta data information

Test//testing

Mysql>use test;

Mysql>show tables; //Show all tables in the current library

2.DDL

Database define language, DB definition language.

Create Database MyBase; //Create a database

Drop Database MyBase; //Delete database

Data type int varchar (50)

CREATE TABLE Stus (ID int primary key,name varchar (), age int); //

drop table Stus; //delete table

Desc[ribe] Stus; //view table structure

3.DML: Data Manipulation language

SELECT * from Stus; //Enquiry

INSERT into Stus values (1, ' Tom ', 12);

4.

Jdbc

---------------

1.java Database Connection,java Connection

Preparatory work

2. Download the driver

Mysql-connector-java-5.0.8.jar

3. Add the drive to the classpath.

Create Lib Folder

Copy to Lib

Added to the BuildPath.

Programming

4. Registration Driver

5. Establish a connection

6. Create statement (statement)

7. Implementation

8. Process the results.

9. Release resources.

Transaction

-------------------

1.transaction: Transaction.

2.acid

A:atomic, atomic, indivisible. All success, all failure.

C:consistent, consistency

I:isolate, isolation

D:durable, permanent.

Auto Commit: Commit.

Transaction operations:

----------

1.commit,

2.rollback.,

3. Set MySQL client transaction auto-commit shutdown

Set autocommit = 0;

Start a transaction

Start transaction;

4.

Unit Test

-----------------

[Email protected] JUnit

Delete Test

----------------

1.delete from ... where ID < 5;

2.delete from ... where ID > 5;

Transactional operations under the MySQL client

------------------------

1. Turn off auto-commit

Set autocommmit = 0; 1 = True

2. Turn on the transaction

Start transaction;

3. Commit a transaction

commit;

4. Rollback

Rollback;

ResultSet

-------------------

1.***** field index is base 1 * * * * *

Sql

------------------

1. Syntax parsing

2. Semantic parsing

Statement

----------------

St.execute ("INSERT into Stus (id,name,age) VALUES (1, '", 12) ");

PreparedStatement

-----------------

1. Pre-processing statements.

2.conn.preparedstatement ("INSERT into Stus (id,name,age) VALUES (?,?,?)");

3.SQL Injection

where name = ' 1 ' or ' 1 ' = ' 1 ' and password = ' xxx '

Learning notes for "DAY21" JDBC and MySQL