MYSQL database practical learning materials-common command set-MySQL

Mysql database is a multi-user, multi-threaded relational database and an application in the client server structure. It is free for individual and commercial users. mysql database has the following advantages: 1. the number of users accessing the database is unlimited. it can store more than 10 million records. 3. is currently the fastest running of existing database products on the market

Mysql database is a multi-user, multi-threaded relational database and a client/server structure application. It is free for individual and commercial users.

Mysql databases have the following advantages:

1. the number of users accessing the database at the same time is unlimited.

2. more than 10 million records can be saved

3. it is the fastest running database system in existing database products on the market.

4. the user permission settings are simple and valid.

Common Mysql database commands:

Start Mysql database

C: \> cd Mysql5.0 \ bin

C: \ Mysql5.0 \ bin> mysqld-install Mysql service

C: \ Mysql5.0 \ bin> net start mysql start Mysql service

The requested service has been started.

Connect to mysql

You need to provide the Mysql User name and password to connect to the server. if the server is not in the local machine, you need a host name or IP address to specify the server location.

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 opening a database

Mysql> use mysql

Database changed

View database commands

Mysql> show databases;

View the detailed structure of a data table

Mysql> desc func;

Create database

Mysql> create database school;

Query OK, 1 row affected (0.00 sec)

Create a table

Mysql> create table user01 (

-> Id varchar (20) not null,

-> UserName varchar (10) not null,

-> Age int (11) 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;

Insert and delete table data

Create table student (stuName varchar (20), age varchar (20), id varchar (20), 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 the 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 grant permissions

Mysql> grant all privileges on *. * to dbuser @ localhost identified by '123'

With grant option;

Change the Mysql User password

C: \ Mysql5.0 \ bin> mysqladmin-u root-p password 1234

Enter password :****

Back 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

Execute this statement to back up mydb to the backup. SQL file of drive D.

Back up multiple database tables

C: \ mysql \ bin \> mysqldump-u root-p 3306 school user01 user> d: \ backup. SQL

The user01 table and user table content and table definitions in the school database are backed up to the backup. SQL file on the D disk.

Back up all databases

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

Restore a 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)

Disable mysql service

C: \ mysql \ bin> net mysql