MySQL Common commands

MySQL database is a multi-user, multi-threaded relational database, is a client/server architecture application. It is free to individual users and business users.

The MySQL database has the following advantages:

1. Unlimited number of users accessing the database at the same time

2. Can save more than 50 million records

3. is currently the market in the current database products running fastest database system

4. User rights are set to be simple and effective.

MySQL Database common commands:

Start the MySQL database

C:\>CD Mysql5.0\bin

C:\mysql5.0\bin>mysqld–install Install MySQL Service

c:\mysql5.0\bin>net start MySQL boot mysql service

The requested service has been started.

Connect MySQL

Users need to provide MySQL username and password to connect to the server, if the server is not on the local computer, you will also need a host name or IP to specify the location of the server.

C:\mysql5.0\bin>mysql-h Localhost-u Root-p

Enter Password: * * *

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 6 to server version:5.0.18-nt

Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.

Mysql>

Use a simple query statement

Mysql> Select Version (), current_date;

Mysql> Select Version (), select Now ();

Create or delete a database

Mysql>create database mydb;

mysql> drop Database mydb;

Commands for open databases

mysql> use MySQL

Database changed

View commands for a database

mysql> show databases;

View the detail structure of a datasheet

Mysql> desc func;

New Database

Mysql> CREATE DATABASE School;

Query OK, 1 row Affected (0.00 sec)

Create a new table

Mysql> CREATE TABLE User01 (

-> ID varchar (not NULL),

-> userName varchar (TEN) is not NULL,

-> age int (one) default ' 0 ',

-> sex char (2) Not NULL default ' m ',

-> PRIMARY KEY (ID)

->) Type=innodb;

Query OK, 0 rows affected, 1 Warning (0.02 sec) Mysql>desc student;

Inserting and deleting data from a table

Create table Student (stuname varchar, age varchar, id varchar (), set0 char (1));

Insert

mysql> INSERT into student (Id,stuname) VALUES (' 1 ', ' Tomcat ');

Query OK, 1 row Affected (0.00 sec)

Delete

mysql> Delete from student where id= ' 1 ';

Query OK, 1 row affected (0.01 sec)

Delete all data in a table

mysql> TRUNCATE TABLE student;

Query OK, 1 row affected (0.01 sec)

Delete Table

Mysql> CREATE TABLE Temp (t varchar (1));

Query OK, 0 rows Affected (0.00 sec)

mysql> drop Table temp;

Query OK, 0 rows Affected (0.00 sec)

Create a new user and give permission

Mysql> grant all privileges in *.* to dbuser@localhost identified by ' 1234 '

with GRANT option;

Change the MySQL user password

C:\mysql5.0\bin>mysqladmin-u root-p Password 1234

Enter Password: * * *

Backing up databases and tables

We use the mysqldump command to back up the database.

C:\mysql\bin\>mysqldump–u root–p 3306 Mysql>d:\backup.sql

Executing this statement will back up MyDB to the Backup.sql file in D disk

Back up multiple database tables

C:\mysql\bin\>mysqldump–u root–p 3306 School User01 user >d:\backup.sql

The meaning of this sentence is to back up the contents and table definition of the User01 table and the user table in the school library to the D disk Backup.sql file.

Back up all the databases

C:\myql\bin>mysqldump–u root–p 3306–all–database>d:backup.sql

Restore MySQL Database

C:\mysql\bin\mysql–u Root–p 3306 School

Restore one of the tables

Mysql> source D:\books.sql;

ERROR:

Unknown command ' \b '.

Query OK, 0 rows Affected (0.00 sec)

Query OK, 1 row Affected (0.00 sec)

Exit MySQL Connection

Mysql>quit (Exit)

Turn off MySQL service

C:\mysql\bin>net MySQL