Learning notes for "DAY22" MySQL

Rdbms

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

Table:field, Record,

Sql

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

Crud

INSERT INTO TableName (ID,...) VALUES (1, ' SSS ',...);

Delete from tablename where id = 1 or id = 3 or id = 5;

Delete from TableName where ID in (1,3,5); //in () range operation = = or

Delete from TableName where ID between A and b//id >= A and ID <= b

Update tablename Set name = ' xx ', age = XXX where id = xxx;

Select 1 + 1; //Expression

Select Age + 1 from Stus; //

Select (age + 1) as NewAge, name, age from Stus; //as The alias of the specified field, as can be omitted

Select concat (' Hello ', name) from Stus; //concat () Join function

SOURCE//execute external SQL script

SOURCE D:/mysql.sql

Aggregation functions

COUNT (*)//query number of records

SUM ()//Sum

Max ()//MAX

Min ()/min

AVG ()//Average

Comments

/* */Block Comment

- -line comment, which must be followed by a space.

SQL injection

SELECT * from stus WHERE name = ' 1 ' or 1= 1;--' and password = ' xxx '

Truncate Stus; //TRUNCATE TABLE

Performance Reviews (15,000)

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

preparedstatement:782 Ms 7389

Statement :

Querying Data time

Select Now (); //

Statement < PreparedStatement < callablestatement{stored procedure}

PreparedStatement

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

1. Placing SQL injection

2. Implement batch Processing

Ppst.addbatch (); //7268

Ppst.executebatch ();

Ddl

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

ALTER TABLE Stus add

BLOB//binary Large object, binary large objects

Text//mysql A large text type, character large object, field type.

MySQL table-level lock

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

1. LOCK TABLE T WRITE | Read;

Table-level read locks and write locks.

Read lock : This thread can read only and other threads can read only and cannot be written.

Write lock : This thread can read and write, other threads cannot read and write.

MySQL holds large objects

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

1. When designing a table, use the Longblob field type

ALTER TABLE Stus add pic longblob;

ALTER TABLE Stus modify PIC Longblob;

2.java using PreparedStatement;

Ppst.setblob (...); //This method is not available and is not implemented

Ppst.setbinarystream (int index,inputstream is); //The method is not available and is not implemented.

Ppst.setbinarystream (int index,inputstream is,int length);

3.mysql Terminal Query

Select ID from Stus where pic is not null;

Stored Procedures

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

1.mysql Client--Modify line terminator;

Delimiter//

2.create procedure Procadd (in|out|inout a int,in b int, out C int)

Begin

--Set c = a + B;

Select a + b into C;

End

//

--Mass insertion

Delimiter//--set line terminator

CREATE PROCEDURE Procbatchinsert (in n int)

Begin

DECLARE I int;

Set i = 0; --assignment operation

Start transaction; --Start a transaction

While I < n do--cyclic operation

Insert into Stus (id,name) VALUES (I,concat (' Tom ', I));

Set i = i + 1;

End while;

commit;

End

//

3.java programming

Connection conn = drivermanager.getconnection (URL, username, password);

Stored procedure calls "{call ProcName (?,?,?,...)}"

CallableStatement CST = Conn.preparecall ("{Call Procadd (?,?,?)}");

Cst.setint (1, 1); //Bind input parameters,

Cst.setint (2, 2);

Cst.registeroutparameter (3, Types.integer); //Registered output type

Cst.execute (); //execute stored Procedure

int r = Cst.getint (3); //Get return results

Transaction concurrency execution

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

1. Dirty reading, dirty reading

Read not submitted.

2. Non-repeatable reading

I can't read it back. Other transactions have been made update.

3. Phantom Reading.

Read a lot.

Isolation level of a transaction

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

The non-submission may result in dirty reading, non-repeatable reading, and Phantom reading.

2--Read has been submitted to avoid dirty reading, non-repeatable reading, Phantom reading can also occur.

4--can be read repeatedly, avoid dirty reading and non-repeatable reading, can also occur.

8--Serialization.

SELECT @ @session. tx_isolation; --session isolation Level

SELECT @ @global. tx_isolation; --Global level

Set transaction

Property externalities

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

Properties prop = new properties ();

InputStream is = DBUtil.class.getClassLoader (). getResourceAsStream ("jdbc.properties");

Prop.load (IS);

Learning notes for "DAY22" MySQL