Shulou(Shulou.com)06/01 Report--

This article mainly introduces what MySQL commonly used necessary commands have, which can be used for reference by interested friends. I hope you can learn a lot after reading this article. Let's take a look at it.

1. Export test_db database

Command: mysqldump-u user name-p database name > exported file name

Mysqldump-u root-p test_db > test_db.sql

1.1) Export all databases

Mysqldump-u root-p-all-databases > mysql_all.sql

two。 Export a table

Command: mysqldump-u user name-p database name table name > exported file name

Mysqldump-u root-p test_db test1 > test_test1.sql

3. Export a database structure

Mysqldump-u root-p-d-- add-drop-table test_db > test.sql

-d No data-- add-drop-table adds a drop table before each create statement

4. Import database

① common source commands

Go to the mysql database console

Such as

Mysql-u root-p

Then use the source command, followed by a script file (such as .sql used here)

Mysql > source wcnc_db.sql

② uses the mysqldump command

Mysqldump-u username-p dbname

< filename.sql ③使用mysql命令 mysql -u username -p -D dbname < filename.sql 5.mysql进入与退出 进入： mysql -uroot -p //进入mysql控制台 mysql -uroot -p password //进入mysql控制台 mysql -p //进入mysql控制台 退出： quit或exit 6.数据库操作 1)、创建数据库 命令：create database 例如：建立一个名为test_db的数据库 mysql>

Create database test_db

2), show all databases

Command: show databases (Note: * there is an s)

Mysql > show databases

3), delete the database

Command: drop database

For example: delete the database named test_db

Mysql > drop database test_db

4), connect to the database

Command: use

For example: access to test_db database

Mysql > use test_db

Screen Tip: Database changed

5) View the database currently in use

Mysql > select database ()

6), the table information contained in the current database

Mysql > show tables; (Note: * there is an s)

7), view the database character set

Mysql > show variables like'% char%'

7. Table operation, you should connect to a database before operation

1), build the table

Command: create table ([,.. ])

For example: create a table called test01, and create two fields, id, name, and data length (using characters to define length units)

Mysql > create table test01 (id varchar (20), name varchar (20))

2), view the table structure

Command: desc table name, or show columns from table name

For example: view test table structure

Mysql > desc test; mysql > show columns from test; mysql > describe test; mysql > show create table test

3), delete the table

Command: drop table

For example: delete a table named test_db

Mysql > drop table test_db

4), insert data

Command: insert into [[,.. ])] Values (value 1) [, (value n)]

For example, insert two records into the table test, which indicate that the number is 001 and the name is yangxz

Mysql > insert into test values ("001", "yangxz")

5) query the data in the table

(1) query all rows

Command: select from

< 表名 >

Where

< 表达式 >

For example: view all the contents (data) in the table test

Mysql > select * from test

For example: find the id=001 content in the test table

Mysql > select * from test where id=001

For example: find the content in the test table that already starts with id 0

Mysql > select * from test where id like "0%"

(2) query the first few rows of data

For example: view the first two rows of data in table test

Mysql > select * from test order by id limit 0Pol 2

Or:

Mysql > select * from test limit 0Pol 2

6), delete the data in the table

Command: delete from table name where expression

For example: delete the record numbered 001 in table test

Mysql > delete from test where id=001

7) modify the data in the table

Command: update table name set field = new value, … Where condition

For example: modify the contents of the name field in test table

Mysql > update test set name='admin' where id=002

For example: modify the length of the name field in the test table

Mysql > alter table test modify column name varchar (30)

8) add fields to the table

Command: alter table table name add field type other

For example, a field passtest is added to the table test, the type is int (4), and the default value is 0

Mysql > alter table test add passtest int (4) default'0'

9). Change the table name:

Command: rename table original table name to new table name

For example: change the name of test to test1 in the table

Mysql > rename table test to test1

8. Modify the password

Mysqladmin-uroot-p Old password password New password

Mysql > use mysql; mysql > update mysql.user set password=' new password 'where user=' username'; mysql > flush privileges; mysql > set password for username @ localhost=password ('your password'); mysql > flush privileges

9. Increase users

For example: add a test user with a password of 1234

Mysql > insert into mysql.user (Host,User,Password) values ("localhost", "test", password ("1234"); mysql > flush privileges

10. Delete user

For example: delete test user

Mysql > delete from user where user='test' and host='localhost'; mysql > flush privileges

11. Database authorization

Command: grant permission on database name. * to user name @ localhost identified by 'password'

For example: authorized test users have all permissions of the test_ DB library

Grant all on test_db.* to test@localhost identified by '123456'

For example: authorized test user to have select,update permission of test_ DB library

Grant select,update on test_db.* to test@localhost

twelve。 Lock table

Mysql > flush tables with read lock

Unlock:

Mysql > unlock tables

13. View current user

Mysql > select user ()

14.MYSQL password cracking method

Stop the Mysql service and start it by skipping permissions, with the following command:

Service mysqld stop / usr/local/mysql/bin/mysqld_safe-user=mysql-skip-grant-tables &

Enter mysql at the shell terminal and press enter to enter the mysql command line

Since MYSQL users and password authentication information are stored in the user table in the mysql database, you need to enter the mysql library.

Mysql > use mysql; mysql > update user set password=password ('123456') where user='root'; mysql > flush privileges

After the MYSQL root password has been modified, you need to stop the startup process of skipping the permission table with Mysql, start MYSQL in a normal way, and log in with a new password to enter the Mysql database.

15. View Mysql provides storage engine

Mysql > show engines

View the mysql default storage engine

Mysql > show variables like'% storage_engine%'

View mysql system version

Mysql > select version ()

View all the tables in the mysql library

Mysql > show tables from mysql

View Mysql port

Mysql > show variables like 'port'

View user,host information in the user table of the mysql library

Mysql > select user,host from mysql.user; Thank you for reading this article carefully. I hope the article "what are the Common necessary commands for MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.