Basic Review of MYSQL and JDBC (I), basic review of mysqljdbc

Basic Review of MYSQL and JDBC (I), basic review of mysqljdbc

1. Basic Database review:

1.1 What is a database? In essence, it is a file system. You can use SQL to add, delete, modify, and view data in the database.

1.2 common databases: MYSQL is commonly used in web and small and medium-sized enterprises. It is an open-source free and small-sized database that is usually used in combination with the PHP language and has been acquired by Oracle.

Oracle: charged, medium and large databases, produced by Oracle.

DB2: it is a medium-and large-sized database developed by IBM and is charged. It is often used in banking systems.

SQLServer: medium and large databases of Microsoft, which are charged and commonly used in Microsoft's own language, such as c #,. net.

SyBase: fades out of the historical stage, but its PowerDesigner is powerful and is used in Java.

SQLite: a database used in embedded systems for small devices.

1.3 SQL classification: Data Definition Language (DDL): used to define database objects, such as database, table, and column. keywords such as create, alter, and drop.

Data operation Language: DML (Data Manipulation Language): Used to update Data in the database, such as insert, delete, and updata.

Data Query Language: DQL (Data Query Language): Used to Query records of database tables, such as select, from, where, etc.

Data Control Language: DCL (Data Control Language): used to define database security and access levels and create users, such as grant.

1.4 database operations:

1.4.1 create: create database name;

Create database name character set XXX (encoding format );

1.4.2 Delete: drop database name;

1.4.3 usage: use library;

1.4.4 view the database with normal operations: select database ();

1.5 data table operations:

1.5.1 creation: create table Name (field name type (length) [constraint], field name type (length) [constraint]);

1.5.2 view: show tables;

View the table structure: desc table name;

1.5.3 delete a table: drop table name;

1.5.4 modify a table:

4.1 add a column: alter table name add field name type (length) [constraint];

　　　　　　　　　　　　　　

4.2 modify the column type (length, constraints): alter table name modify the field name type (length) to be modified [constraints];

　　　　　　　　　　　　　　

4.3 modify the column name: alter table name change old column name new column name type (length) [constraint];

　　　　　　　　　　　　　　

4.4 delete a table column: alter table Name drop column name;

4.5 Modify table Name: rename table name to new table name;

4.6 modify the character set of a table: alter table name character set encoding;

1.6 Insert table records in the database

1.6.1 insert record: insert into Table Name (column name 1, column name 2, column name 3...) values (value 1, value 2, value 3 ...);

For example, insert into tal_user (uid, uname, upasd) values (null, 'hangsan', '123 ');

Or: insert into table name values (value 1, value 2, value 3...); (commonly used)

　　　　　　　　Insert Chinese garbled characters: method 1 (not recommended): directly modify row 57th of the my. ini file in the database installation directory: default-character-set = UTF-8;

Method 2: set names gbk;

　　　　　　　　　　　　　　　　　　　　

　　1.7 Modify Table records in the database

1.7.1 without conditions: update table name set field name = value, field name = value, field name = value ......;

It will change all records of the modified column.

1.7.2 conditional: update table name set field name = value, field name = value, field name = value ...... Where condition;

　　　　　　　　　　　　　　　　

1.8 Delete table records in the database

1.8.1 conditional: delete from table name where condition (uid is not reset after deletion );

1.8.2 without conditions: delete from table name;

　　　　1.8.3What is the difference between delete and truncate?

The delete operation is a delete record. It works with transactions to retrieve the deleted data.

Truncate is used to delete the entire table and create an identical table. The deleted data cannot be retrieved.

　　　　　　　　　　(Delete, uid will not be reset! The uid will be reset when the truncate operation is used, because it deletes the table structure and creates an identical table, so the data inserted again starts from 1 ).

1.9 query table records in the database

Query is the most important and complex function in a database. Therefore, it is described in detail in the next section.