MySQL installation, configuration, use, and JDBC connection _ MySQL

MySQL installation, configuration, use, and JDBC link bitsCN.com

Download:

URL: http://dev.mysql.com/downloads/mysql/5.5.html#downloads

Select No thanks, juststart my download.

Mysql-5.5.36-win32. Msi 33.7 M

Mysql-5.5.36-winx64. Msi 35.3 M

Navicat V8.2.12ForMySQLUsage:

1. Click Connect and enter the custom connection name and password of the root account to establish a connection.

2. after the connection is enabled, you can perform operations on tables and views, which is very convenient.

Note:

1.For local installation, you only need to select the number of low connections of Development to reduce the memory usage, and configure the password when installing it..

2.After installing MySQL, it is best to install another MySQL plug-in.

Navicat V8. 2.12 ForMySQLVisualized and user-friendly. it is a very good management tool.

Usage:

1. after installation, you can see it in the startup barMySQL 5.5 Command Line Client

2. click and enter the password to enter the working mode, or enter the password in the running column.

Mysql-uroot-pEnter the password again.

Mysql>

Note:

1) connect to the remote host command: mysql-h host address-u username-p address

Assume host: 192.168.0.1 user: root pwd: 1234

Command: mysql-h198.168.0.1-uroot-p1234

2) space is available here, so it can be written

Mysql-h 192.168.0.1-u root-p 1234

3. exit

Exit

Change password:

1.Add new password: enter DosMysql/binDirectory, run the following command:

Mysqladmin-u root-p1234 // à 1234 new password.

2. change the password:

Mysqladmin-u root-p1234 password abcd // à New password: abcd

User Management:

Format:Grant select on database. * to username @Login hostIdentified by "password"

Example 1: add a user named "test1" with the password "abc" so that the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MYSQL, and then type the following command:

Grant select, insert, update, delete on *. * to test1 @ "%" Identified by "abc ";

However, the user added in Example 1 is very dangerous. if someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. for the solution, see the following example.

Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that the user knows the password of test2, he cannot access the database directly from the internet, but can only access the database through the web pages on the MYSQL host.

Grant select, insert, update, delete on mydb. * to test2 @ localhostidentified by "abc ";

If you do not want test2 to have a password, you can run another command to remove the password.

Grant select, insert, update, delete on mydb. * to test2 @ localhostidentified "";

Common commands:

1. display the database list

Mysql>Show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Mysql |

| Performance_schema |

| Test |

+ -------------------- +

4 rows in set (0.05 sec)

2. use a database.

Mysql>Use mysql;// À mysql built-in database

Mysql>Show tables;// How many tables can be viewed?

+ --------------------------- +

| Tables_in_mysql |

+ --------------------------- +

| Columns_priv |

| Db |

| Plugin |

+ --------------------------- +

3 rows in set (0.25 sec)

3. display table structure

Mysql>Desc proc;

+ ---------------------- + -----------------------------

| Field | Type

+ ---------------------- + -----------------------------

| Db | char (64)

| Name | char (64)

+ ---------------------- + -----------------------------

2 rows in set (0.21 sec)

4. create a database

Mysql>Create database school;// A. school database name

Drop database if exists school;// Delete a file if it exists;

5. create a table

Mysql>Use school;// À open the database. you must select a database for table operations.

Mysql>

Create tableteacher

(

Id int (3) auto_increment not null primary key,

Name char (10) notnull,

Addressvarchar (50) default 'hangzhou ',

Year date

);

6. delete a database | delete a table

Mysql>Drop database school;

Mysql>Drop table teacher;

7. Insert | update

Insert into teacher values (001, 'Wang ', 'BJ', '2017-10-10 ');

Insert into teachervalues (002, 'Jiang ', 'sh', '2017-10-10 ');

Update teacher set address = 'CD' wherename = 'Wang'

8. delete | query

Mysql>Delete * from teacher wherename = 'Jiang ';

Mysql>Select * from teacher;// The à statement ends with a semicolon

9. number of displayed rows | number of columns

Select count (*) from teacher;

Select sum (*) from teacher;

Note: If there are many commands, DOS enters mysql/bin and runs

Mysql-u root-pabcd

For example, place the following command in school. SQL to complete database creation, table creation, and insertion.

Drop database if exists school;

Create database school;

Use school;

Create table teacher

(

Id int (3) auto_increment not null primary key,

Name char (10) not null,

Address varchar (50) default 'Beijing ',

Year date

);

Insert into teachervalues ('001', 'Tom ', 'hangzhou', '2017-10-10 ');

Insert into teachervalues ('002 ', 'Bob', 'shengzh', '2017-12-23 ');

NetBeans à driver à access MySQL

1. download the database driver: mysql-connector-java-5.0.8 and put the file mysql-connector-java-5.0.8-bin.jar under this directory under % JAVA_HOME %/lib.

Note: If not, add the directory where the file is located to classpath.

2. open NetBeans

Right-click Libraries of the project to Add JAR/Folder and select the jar file.

3. open NetBeans: Window à Services à Database

Right-click MySQL Server at localhost: 3306 [root] and choose Properties to configure the following:

Note:

1. in Path/URL to admin Tool: Fill in the MySQL management Tool, here Navicat V8.2.12ForMySQL is used, so here fill in the specific directory of the Tool executable file, other options are generated by default.

2. Server HostName:

If it is local, enter localhost or 127.0.1.

If it is remote, enter the IP address of the MySQL database server.

The complete example in NetBeans below is to connect to MySQL and perform operations on it:

Import java. SQL. Connection; // Connection class

Import java. SQL. DriverManager; // driver class

Import java. SQL. ResultSet; // The queried SQL result set class.

Import java. SQL. Statement; // SQL Statement class

Public class MySQLTest {

Public static void main (String arg []) {

Try {

Connection con = null; // define a MYSQL link object

Class. forName ("com. mysql. jdbc. Driver"). newInstance ();

// MySQL driver

Con = DriverManager. getConnection ("jdbc: mysql: // 127.0.0.1: 3306/school", "root", "abcd"); // school indicates the database name and abcd indicates the password of the root account.

Statement stmt; // Create a declaration

Stmt = con. createStatement (); // add a new data entry

Stmt.exe cuteUpdate ("INSERTINTO user (username, password) VALUES ('qgao', '123 ')");

ResultSet res into stmt.exe cuteQuery ("select LAST_INSERT_ID ()");

Int ret_id;

If (res. next ()){

Ret_id = res. getInt (1 );

System. out. print (ret_id );

}

// Delete a piece of data

String SQL = "DELETE FROMuser WHERE username = 'Li Si '";

Long deleteRes into stmt.exe cuteUpdate (SQL );

// If the value is 0, no delete operation is performed. if the value is greater than 0, the number of deleted records is recorded.

System. out. print ("DELETE:" + deleteRes );

// Update a piece of data

String updateSql = "UPDATEuser SET password = '000000' WHERE username = 'Gao Shou '";

Long updateRes extends stmt.exe cuteUpdate (updateSql );

System. out. print ("UPDATE:" + updateRes );

// Query and output data

String selectSql = "SELECT * FROM user ";

ResultSet selectRes implements stmt.exe cuteQuery (selectSql );

While (selectRes. next () {// loop output result set

String username = selectRes. getString ("username ");

String password = selectRes. getString ("password ");

System. out. print ("/r/n ");

System. out. print ("username:" + username + "password:" + password );

}

} Catch (Exception e ){

System. out. print ("MYSQLERROR:" + e. getMessage ());

}

}

}

BitsCN.com