MySQL View database command

MySQL View database command

command to open a database

mysql> use MySQL

Database changed

Commands to view the database

mysql> show databases;

View detailed structure of a data table

Mysql> desc func;

New Database

Mysql> CREATE DATABASE School;

Query OK, 1 row Affected (0.00 sec)

New Table

Mysql> CREATE TABLE User01 (

ID varchar () not NULL,

-UserName varchar (+) 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.02sec) MYSQL>DESC student;

Inserting and deleting data from a table

Create table student (stuname varchar), age varchar (idvarchar), 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 a 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 on * * To[email protected] identified by ' 1234 '

with GRANT option;

Change the MySQL user password

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

Enter Password: * * *

Backing up databases and tables

We use the mysqldump command to back up the database.

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

Executing this statement will back up the mydb to the D-disk Backup.sql file

Backing up multiple database tables

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

The meaning of this sentence is to back up the contents 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 these 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

MySQL View database command