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