Today, let's take a look at the various commands of the database , The following commands are all from CMD Command line input instructions in the command window , First of all, if you type mysql, System prompt “mysql It's not an internal or external order . So this is actually the reason why the environment variables are not set properly , For example, I mysql The installation directory of is C:\news\mySql, Then the system variables in the environment variables PATH Path C:\news\mySql\MySQL Server 5.5\bin, Restart CMD, Then type mysql, It should work properly , Of course, the premise is mysql The service should start normally , If not, then CMD Will be displayed CANNOT
CONNECT th..........

First mysql Statements are case insensitive . Look at the first command :

mysqladmin -uroot -p123  password  456

The meaning of this sentence is to root Password changed to 456 , The original password is 123,-u Represents the user name , What follows is root user name ,-p After the password 123 Is the login password , The space in the middle can be omitted . but password The space between the password and the new password cannot be omitted .

1.1、 Connect to a remote database , The basic format is as follows :

Mysql    -h Computer name (IP Address ) -u user name  -p password

For example, my database is local , database ip The address is, Then you can write like this :

mysql  -h  -u root   -p 123

1.2、MySQL Rights management :

mysql Authority management follows the principle of minimum authority , That is, a user ( A process ), You should have the lowest level of authority to perform the tasks assigned to it . So you can set up a permission system root To perform the daily management of the database . The administrator's authority is Create temporary tables、File、Lock Tables、Process、Reload、Replication client、Replication slave、Show databases、Shutdown、Super, In addition, there are two special permissions , Namely All and Usage, It means all permissions and no permissions, that is, only the right to log in to the database without other rights , More permissions are usually granted later .

1.3、 Add new users

Log in first mysql database ,root After logging in, first select the database and then add new users ,

>use database_name;  // Select database

The following steps run in tandem , Until it ends with a semicolon , Enter does not cause the command to end ! You have to hit the semicolon and press enter to end the sentence .

>grant all

>on  database_name.*   // For all tables in a database, there are all jurisdiction

>to  rick   identified by  '123'

>with  grant  option ;

The above sentences indicate the creation of a user name of rick, The password for 123 And have access to dtabase_name All operation permissions of the database . The last sentence allows the user to grant permissions to others .grant It means to grant .

Take back the user's rights

>revoke all

>from rick;

Next, create a regular user without any authorization

>grant suage

>on  database_name.*

>to rick identified by '123' ;

You can also grant some appropriate permissions , for example select,delete wait .

>grant  select,delete,insert,alert,create,drop,update

>on database_name.*

>to rick identified by  '123';

1.4、 Delete user

mysql>drop database testDB; // Delete user's database

Delete accounts and permissions :>drop user user name @'%';

  This sentence can also be deleted :

 >drop user user name @ localhost; 

1.5、 Create database :create database name;

1.6、 Delete database :drop database name ;

1.7、 Create a data table example :

        create  tablename (

id   int(2) unsigned not null auto_increment,

name   char(10) not null,

age      tinyint unsigned not null,

addr    text not null,

time    timestamp


1.8、 Backup database

There are two ways : The first method is to use... When copying database files LOCK TABLES Command to lock these tables . less , So I don't want to say , Let's talk about the second method : Use MySQLdump command .MySQLdump Command to export the database to .sql In file . The format of the command is as follows :

Mysqldump [options]  database [table]

options It's usually --opt,table Refers to the specific database name .

I don't know the command can be used mysqldump --help Command view usage .

Be careful mysqldump The order is in cmd Directly input under the window of , So you have to quit first mysql The client of , That is, it can't be in mysql> State input , Exit before operation , Because I'm in mysql So this command has been invalid , Wasted a lot of effort !

1.9、 Restore database

For example, I want to put stu.sql File import to studyphp In the database , The command is as follows :

>use studyphp

>source  c:/stu.sql;

2.0、show command

a. show tables or show tables from database_name; // Displays the names of all tables in the current database

b. show databases; // Show mysql Names of all databases in  

   c. show columns from table_name from database_name; or show columns from database_name.table_name;   // Show column names in the table  

   d. show grants for user_name;   //   Show a user's permissions , The result is similar to grant command  

   e. show index from table_name;   // Show index of table  

   f. show status;   // Display information about some system specific resources , for example , Number of threads running  

   g. show variables; // Displays the name and value of the system variable  


   h. show   processlist; // Displays all processes running in the system , That is, the query currently being executed . Most users can see  

                                  Their own process , But if they have process jurisdiction , You can see the progress of everyone , Include password . 

   i. show table status; // Displays the currently used or specified database Information for each table in . The information includes the table type and the latest update time of the table  

   j. show privileges;   // Show the different permissions supported by the server  

   k. show create database database_name; // Show create database Statement can create the specified database  

   l. show create table table_name; // Show create database Statement can create the specified database  

   m. show engies;   // Shows the storage engines and default engines available after installation . 

   n. show innodb status; // Show innoDB The state of the storage engine  

   o. show logs; // Show BDB Log storage engine  

   p. show warnings; // Displays the error generated by the last statement executed 、 Warnings and notices  

   q. show errors; // Only show the error caused by the last execution statement

First time to know MySQL All kinds of database CMD Command window under the command of more related articles

  1. mysql Applied Learning - stay cmd Create database and table in command window

    Run the following , Please confirm that you have installed and configured mysql. First run cmd.exe, Enter the command window . step1. Get into MySQL monitor If you are logged in mysql, Direct access to step2; If not, please ...

  2. One 、 First time to know MySQL database Two 、 build MySQL database ( a key ) 3、 ... and 、 Use MySQL database Four 、 know MySQL The data type of the database 5、 ... and 、 operation MySQL Database data ( a key )

    One . First time to know MySQL database ###<1> Database Overview     1.  database          Stored in a computer for a long time , Shared data sets organized by          A warehouse for storing data          file ...

  3. MySQl Database commonly used DOS command

    MySQl Database commonly used DOS command .. This is the first part .. Database connection information :jdbc:mysql://localhost:3306/shxtcom.mysql.jdbc.Driver /*jdbc:sq ...

  4. Windows Under the system, how to cmd Switch in the command window Python2.7 and Python3.6

    We may install multiple versions of... On the same system Python, After all Python2.7 And Python3.6 There are still different needs , But in use Cmd The command window is our possible default system variable, and the environment is one of the versions , When we need to be in cmd command ...

  5. Qt Independent runtime is accompanied by CMD Command window

    use Qt I wrote a little software , I'm putting the program release after , After packing and repackaging , It is found that when the program is running, it will be accompanied by cmd Command window , But I'm worried But Kung Fu is not inferior to those who have a heart , With the help of teachers and my net friends , It's over CONFIG: Specify project configuration and edit ...

  6. cmd Command window related operation guide

    cmd How the command window opens :win+R cmd 1. Drive switch d: enter (d For the disk name ) 2. View the files and folders in the current directory Input dir 3. Enter the specified directory ( If cross drive operation is required , You should switch the drive letter first ) cd( Space )+ absolute ...

  7. windows7 Next cmd There is no scroll bar in the command window

    Because of yesterday's good 123 The problem is not solved , I'd like to check the ip Address, etc , So it opens cmd window , Input ipconfig/all Command to view , But it turns out that the following window appears , Can't scroll , I can't see the details at all . And then I'm going to ...

  8. MySql Database export csv File command

    MySql Database export csv File command : MySql Database export csv File command : mysql> select first_name,last_name,email from account into ...

  9. JMeter Shortcut key icon making Get rid of cmd Command window

    Use jmeter when : If you use the default jmeter.bat Start up , There will be one CMD The command window will start later jmeter Work interface Directly enable ApacheJMeter.jar The file can be skipped CMD The command window starts jme ...

Random recommendation

  1. java File reading and file writing in : How to get content from a file and how to write content to a file

    import; import; import; import ...

  2. [Leetcode]Reverse Integer

      The core idea : Primitive pair 10 Take the remainder and assign it to the new number, then reduce it by one bit , Then, the new number is raised by one bit, and the original number is taken as the residual value next time , Until the original number drops to 0. The solution is as follows : int reverse(int x) { bool minus = false; ) ...

  3. obtain MySQL service-provided sakila database (Example Databases)

    About this database, which is the sample database , database , database , The most terrible thing is that there is no data , Right ? What can you learn without data . But , No data , I'll be myself insert, That only applies to beginners . Learning about database optimization , There's still big data ...

  4. Python Built in object list

    Python Built in object list Just written Python It's bound to happen , What would you like to write , But I don't know where to start ... In my opinion, the problem is that we don't know what we can play with , Here is a list of Python Built in objects for , Sort it out a little bit , many ...

  5. Unity WebSocket( Only applicable to WebGL platform )

    !!! Reprint note : Unity WebSocket Use 1. download YLWebSoc ...

  6. angular The study notes of this big stem

    angular Define a module (module) And controller (controller) Local declaration method for : var app=angular.module("Myapp",[]); ...

  7. tokuDB Installation and backup notes

    Online data is growing faster and faster , The amount of data is growing , Especially log data , This is for data migration . In terms of data backup and recovery , It's a very big challenge . So I thought of 13 In open source tokuDB Storage engine , To solve our urgent capacity problem ...

  8. &lt; computer network &gt; Transport layer

    Port number : Usually, multiple network applications run on a single host ,IP The address identifies a host , The port number identifies the specific process . The port is a 16bits Number of numbers , It's the size of 0-65535 Between .0-1023 The port number between them is called the slogan of Zhouzhi Socket : Slave network ...

  9. 51Nod1626 B Your dream Pressure dp matrix

    Link to the original text Subject portal - 51Nod1626 The question Answer key First of all, consider the thinking space in the image imagination . ...

  10. java Custom comments in @interface Usage of

    One . What is annotation      Speaking of notes , Let's talk about metadata first (metadata). Metadata is the data of data . in other words , Metadata describes data . Just like fields in a data table , Each field describes the meaning of the data under this field . and J ...