Mysql_ detailed basic commands

To modify a new password:
Use MySQL;
Update user set password= ' new password ' where user= ' username ';
Flush privileges; Update permissions

Add new users:
Grant SELECT, Insert,update,delete on database. Table to username @ Login host identified by ' password ';
Flush privileges; Update permissions

mysql> INSERT INTO Mysql.user (Host,user,password) VALUES ("localhost", "Test", Password ("1234"));

Example 1: If you do not want root to have the password operation database "MyDB" in the data table, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by ';

Delete User
[Email protected]:~$ mysql-u user name-p password
Mysql>delete from user where user= ' user name ' and host= ' localhost ';
Mysql>flush privileges;
Delete a user's database
Mysql>drop database dbname;

Database operations:
Show all databases
mysql> show databases; (note: There is a last s)

Create a database
mysql> CREATE DATABASE test;

Enter the database
mysql> use library name;

Table information contained in the current database
Mysql> Show tables; (Note: There is a last s)

Deleting a database
mysql> drop database name;

Table Operation:
Note: You should connect to a database by using use < database name > before the operation.
Build table
Command: CREATE TABLE table name (< Field name 1> < type 1> [,.. < Field name N> < type n>]);
Example:
Mysql> CREATE TABLE Table name (
> ID int (4) NOT null primary key auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

Get table structure
Command: DESC table name, or Show columns from table name
Example:
Mysql> describe student;
mysql> desc Student;
Mysql> show columns from student;

Delete a table
Command: drop table name;
Example: Deleting a table with a table named student
mysql> drop table student;

Inserting data
Command: INSERT into table name (field name 1,.. Field name N) VALUES (' Value 1 ', ' value n ');
INSERT into student (name,age,register_date) VALUES (' Liudong ', 18, ' 2016-10-24 ');

Querying data in a table
Querying all Rows
Mysql> select * from student;

Querying the first few rows of data
Example: Viewing the first 2 rows of data in a table student
Mysql> SELECT * FROM student order by ID limit 0, 2;
Or
Mysql> SELECT * FROM student limit 0, 2;

Delete data from a table
Command: Delete from table name where expression
Example: Deleting a record with number 1 in table student
mysql> Delete from student where id=1;

modifying data in a table
Command: Update table name SET field = new value,... WHERE condition
Mysql> Update student set name= ' Liudong ' where stu_id=1;

Change table name
Command: Rename table name to new table name;
For example, change the name of the table student to students
Mysql> Rename table student to students;

Update Field Contents
Command: Update table name set field name = new Content
Update table name set field name = Replace (field name, ' old content ', ' new content ');
For example: Add 4 spaces in front of the article
Update student Set Age=concat (", age);

Follow the three steps below to quickly import this SQL file

Mysql>use yourdatabasename;mysql>set Names utf8;mysql>source/tmp/database.sql;

1. Create a database

mysql> CREATE database cacti;

2. Import the database

Mysql-uroot-p cacti < cacti.sql

3. Create a database user

Mysqldump
Command line Export database:
1. Go to the Bin folder in the MySQL directory: cd MySQL to the Bin folder directory
As I entered the command line:CD C:\Program files\mysql\mysql Server 4.1\bin

2, Export database: Mysqldump-u user name-p database name > exported file name
As I entered the command line:mysqldump-u root-p News > News.sql (input will let you enter the password into MySQL)
(If you export a single table, enter the table name after the database name), you will see the file News.sql automatically generated to the bin file

Command line Import Database:
1, move the. sql file to be imported into the bin file, such a path is more convenient
2, the 1th step with the above export

3, enter mysql:mysql-u user name-P
As I entered the command line: mysql-u root-p (Input the same will let you enter the Ysql password)

4, create a new database you want to build in Mysql-front, this is an empty database, such as a new target database named News (Mysql>create.)
5, Input: Mysql>use target database name

As I entered the command line:Mysql>use news;

6, import file: mysql>source import filename;
As I entered the command line:mysql>source news.sql;

Instance:

Export
#> CD/USR/LOCAL/SRC
#>/usr/local/mysql/bin/mysqldump-uroot-p123456 cacti > cacti.sql;
This will see the cacti.sql exported to the/usr/local/src/directory

1. Export the entire database
Mysqldump-u user name-p database name > exported file name
Mysqldump-u root-p ABC > Abc.sql

2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u root-p ABC users> abc_users.sql

3. Export a database structure
Mysqldump-u Root-p-D--add-drop-table ABC >/usr/local/abc_db.sql
-D No data--add-drop-table add a drop table before each CREATE statement

Import
#>/usr/local/mysql/bin/mysql-uroot-p
mysql> CREATE database cacti; The new database is named cacti
mysql> use cacti; Select Target Database
mysql> source/usr/local/src/cacti.sql Import Database
This imports the Cacti.sql file

MySQL Root does not have permission to solve problems

The solution is as follows:

1.

Locate the [C:\Program files\mysql\mysql Server 5.5\my.ini] file.

Edit the file add a line of code "Skip-grant-tables" at the end of the file, save the file and close it.

2.

Restart MYSQL5 Service

3.

Change the password of the root account;

Open the Doc window,

Then execute the command "Mysql-u root-p" without a password to enter directly.

Continue to execute command "use MySQL"

Continue to execute the command "update user set Password=password (" root ") where user=" root ";

The last command is to change the user root password to root

4.

Restore [C:\Program files\mysql\mysql Server 5.5\my.ini] configuration file

Restart MYSQL5 Service

User: Root

Password: root

Ok! Successfully connected to MYSQL5. Thank you!

mysql Add user, delete user and authorization

MySQL add user, new database, user authorization, delete user, change password (note that each line followed by A; indicates that a command statement ends):

1. Create a new user

1.1 Log in to MySQL:

@>mysql-u root-p

@> Password

1.2 Create User:

mysql> INSERT INTO Mysql.user (Host,user,password) VALUES ("localhost", "Test", Password ("1234"));

This creates a user named: Test with a password of: 1234.

Note: "localhost" here means that the user can only log on locally and cannot telnet to another machine. If you want to telnet, change "localhost" to "%", which means you can log on on any computer. You can also specify that a machine can log on remotely.

1.3 Then log in:

mysql>exit;

@>mysql-u test-p

@> Enter password

Mysql> Login Successful

2. Authorizing the user

Authorization format: Grant permissions on database. * To User name @ login host identified by "password";

2.1 Log in to MySQL (rooted), log in as root here:

@>mysql-u root-p

@> Password

2.2 First create a database for the user (TestDB):

Mysql>create database TestDB;

2.3 Authorization test The user has all permissions for the TestDB database (all permissions for a database):

Mysql>grant all privileges in testdb.* to [e-mail protected] identified by ' 1234 ';

Mysql>flush privileges;//Refresh System Permissions Table

Format: Grant permissions on database. * To User name @ login host identified by "password";

2.4 If you want to specify a partial permission to a user, you can write:

Mysql>grant select,update on testdb.* to [e-mail protected] identified by ' 1234 ';

Mysql>flush privileges; Refresh System Permissions Table

2.5 Authorization test The user has some permissions for all databases:

Mysql>grant Select,delete,update,create,drop On * * to [e-mail protected] "%" identified by "1234";

The test user has Select,delete,update,create,drop permissions on all databases.

@ "%" indicates authorization for all non-local hosts, excluding localhost. (The localhost address is set to 127.0.0.1, if set to the real local address, do not know whether it can, no authentication.) ）

Authorization to localhost: plus a grant all privileges on testdb.* to [email protected] identified by ' 1234 ';

3. Delete a user

@>mysql-u root-p

@> Password

Mysql>delete from user Where user= ' test ' and host= ' localhost ';

Mysql>flush privileges;

Mysql>drop database TestDB; Delete a user's database

Delete account and permissions: >drop user username @ '% ';

>drop user username @ localhost;

4. Modify the specified user password

@>mysql-u root-p

@> Password

Mysql>update Mysql.user Set Password=password (' New password ') where user= "test" and host= "localhost";

Mysql>flush privileges;

5. In the Mysqldump official tool, how can I restore only one library?

Full Library Backup

[Email protected] ~]# mysqldump-uroot-p--single-transaction-a--master-data=2 >dump.sql

Restore The contents of the ERP library only

[Email protected] ~]# Mysql-uroot-pmanager ERP--one-database <dump.sql

It can be seen that the main parameter used here is the--one-database shorthand-o parameter, which greatly facilitates our recovery flexibility.

create a regular user and authorize the sample (log in with root and assume you have created the Openscannerstore database): mysql > Use mysql; # Create Openscanner user with password and set to local access from the machine where the MySQL service is installed MySQL > grant all on openscannerstore.* to ' openscanner ' @ ' localhost ' Identified by ' scanner888 '; #设置openscanner用户与密码 and accessible from any machine Mysqlmysql > grant all on openscannerstore.* to ' Openscanner ' @ '% ' identified by ' scanner888 '; MySQL > Flush privileges;            #刷新才会生效

Mysql_ detailed basic commands