MySQL installation, configuration, use, and JDBC links

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

Select No to Juststart my download.

Mysql-5.5.36-win32.msi 33.7M

Mysql-5.5.36-winx64.msi 35.3M

Navicat V8.2.12formysql Use:

1. Click on the connection, enter the custom connection name and root account password, established the connection.

2. Open the connection, after the table and view can be manipulated, very convenient.

Attention:

1. How to install locally, just choose development low connection number on it, less memory, install the time by the way with the password.

2. After the installation of MySQL, it is best to install a MySQL plugin

Navicat V8.2.12formysql, visualization, friendly interface, is a very good management tool.

Use:

1. After installation, you can see the MySQL 5.5 Command line Client in the boot bar

2. Click and enter the password, you can enter the working mode, or in the run bar input

Mysql–uroot–p password can also be entered.

Mysql>

Attention:

1 Connection Remote host command: MYSQL–H host address –u username –p address

Assume host: 192.168.0.1 User:root pwd:1234

Then command: mysql–h198.168.0.1–uroot–p1234

2 space can be used here, but not, so you can write

Mysql–h 192.168.0.1–u root–p 1234

3. Exit

Exit

Modify Password:

1. Add new password: DOS into the Mysql/bin directory, execute the following command:

Mysqladmin-u root–p1234//à1234 for the newly added password.

2. Change Password:

Mysqladmin-u root–p1234 password ABCD//à new password: ABCD

User management:

Format: Grant Select on database. * To User name @ login host identified by "password"

Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First connect the root user to MySQL, and then type the following command:

Grant Select,insert,update,delete on *.* to test1@ "%" identified by "ABC";

But for example 1 the increased user is very dangerous and you want someone who knows Test1 's password so that he can log on to your MySQL database on any computer on the Internet and can do whatever it wants with your data, see the following example

Example 2, add a user test2 password for ABC, so that he can only log on the localhost, and can query the database mydb, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database of the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, only through a Web page on the MySQL host.

Grant Select,insert,update,delete on mydb.* to Test2@localhostidentified by "ABC";

If you do not want to test2 the password, you can make another command to eliminate the password.

Grant Select,insert,update,delete on mydb.* to Test2@localhostidentified by "";

Common commands:

1. Display 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 self-with database

Mysql> Show tables; à see how many watches

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

| 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. Building a library

Mysql> CREATE DATABASE School; Àschool database name

Drop database if exists school; à existence is deleted;

5. Table Building

Mysql> Use school; À opens the library. You must first select a database for a table operation

Mysql>

Create Tableteacher

(

ID int (3) auto_increment NOT null primary key,

Name Char (notnull),

Addressvarchar (m) default ' Chengdu ',

Year Date

);

6. Delete the list

mysql> drop Database School;

mysql> drop table teacher;

7. Insert | update

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

INSERT into Teachervalues (002, ' Jiang ', ' SH ', ' 1999-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. Show number of rows | number of columns

Select COUNT (*) from teacher;

Select SUM (*) from teacher;

Note: If there are many commands, Dos goes under Mysql/bin.

Mysql-u ROOT-PABCD

For example: Take the following command and put it in the School.sql, while completing the library, table, insert operation.

Drop database if exists school;

Create Database School;

Use school;

CREATE TABLE Teacher

(

ID int (3) auto_increment NOT null primary key,

Name Char (a) NOT NULL,

Address varchar (m) Default ' BeiJing ',

Year Date

);

Insert INTO Teachervalues (' 001 ', ' Tom ', ' Chengdu ', ' 1976-10-10 ');

Insert INTO Teachervalues (' 002 ', ' Bob ', ' Shengzh ', ' 1975-12-23 ');

Netbeansà drive à access MySQL

1. Download the database driver: mysql-connector-java-5.0.8, and Mysql-connector-java-5.0.8-bin.jar the files under this directory to%java_home%lib.

Note: If you don't put it here, you need to add the directory of the file to Classpath.

2. Open NetBeans

Libraries the project's right key add Jar/folder Select the JAR file you just made.

3. Open Netbeans:windowàservicesàdatabase

In the MySQL Server at Localhost:3306[root] on the right key àproperties on the following configuration:

Attention:

1. In the Path/url to admin Tool: This one fills in the MySQL management tool, here uses the Navicat V8.2.12formysql, therefore fills in here the tool executable file the specific directory, other options will generate by default.

2. Server HostName:

If it's local, fill in: localhost or 127.0.1

If it is remote, fill in: MySQL database server IP.

Here's a complete example of NetBeans that connects MySQL and operates on it:

Import java.sql.Connection; Connection class

Import Java.sql.DriverManager; Drive class

Import Java.sql.ResultSet; Query to 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 linked object

Class.forName ("Com.mysql.jdbc.Driver"). newinstance ();

MySQL Driver

Con =drivermanager.getconnection ("Jdbc:mysql://127.0.0.1:3306/school", "root", "ABCD"); School is the database name, and ABCD is the password for the root account.

Statement stmt; Create a declaration

stmt = Con.createstatement (); Add a new piece of data

Stmt.executeupdate ("Insertinto User (username, password) VALUES (' Qgao ', ' 123456 ')");

ResultSet Res =stmt.executequery ("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 =stmt.executeupdate (SQL);

If 0 is not deleted and if greater than 0, the number of deleted bars is recorded

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

Update a piece of data

String updatesql = "updateuser SET password = ' 1234 ' WHERE username= ' Gao shou '";

Long Updateres =stmt.executeupdate (updatesql);

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

Querying data and outputting

String selectsql = "Select *from user";

ResultSet selectres =stmt.executequery (selectsql);

while (Selectres.next ()) {//Loop output result set

String username =selectres.getstring ("username");

String password =selectres.getstring ("password");

System.out.print ("Rnrn");

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

}

catch (Exception e) {

System.out.print ("Mysqlerror:" + e.getmessage ());

}

}

}