Home >Database >Mysql Tutorial >mysql common commands and operations

mysql common commands and operations

伊谢尔伦
伊谢尔伦Original
2016-12-03 11:00:211141browse

出1. Login and exit 登 1) Login

Under the DOS command window under the DOS command window under the DOS command window to enter mysql Enter;
Linux Enter the server using Putty to connect to mysql, and then enter: mysql -u username -P password is that it is You can enter the mysql> interface.
2) Exit
Execute exit and press Enter.
3) Change password
mysql -u username-p password password new password

2. Basic database operations

1) Show database

mysql>show databases;
2) Create database
mysql>create database name; //here The name refers to the name of the database that needs to be created.
3) Delete database
Mysql>drop database name; //The name here refers to the name of the database that needs to be deleted.
4) Select the database
Mysql>use databasename; //The databasename here refers to the name of the selected database.
5) Check the currently used database
mysql>select database();

3. Basic operations of the table

Note: Use databasename must be used before all operations on the table; to indicate which database is selected.

1) Display tables s mysql & gt; show tables;
2) Show specific table structure
mysql & gt; description to tableename;
3) Create tables
mysql & gt; create table table (Col1 Type, Col2 Ty per ....); // The tablename here refers to the name of the table to be created.
4) Delete the table
Mysql>drop table tablename; //The tablename here refers to the name of the table to be created.
5) Insert data
into tablename values(col1 value,col2 value....);
6) Query data
select * from tablename where.....;
7) Update data
update tablename set col1 = newvalue where .....;
8) Delete data
delete from tablename where ......;

4. File import
1) Import .sql file command (for example, D:/mysql.sql)

mysql>use databasename;

mysql>source d:/mysql.sql;
2) Use text mode to import data into the database table
mysql>load data local infile "filename" into table tablename;

5. User permission operations
1) Add new users

      grant select on databasename.* to   username@localhost identified by "password"       2) Add all privileges to the user  

          grant all privileges on *.* to   username@localhost identified by "password";  
  3) Add specific operations of the database to the user
Grant select, insert, update on databasename.* to username@localhost identified by "password"
4) Add operation permissions to a certain table in the database to the user
Grant update, delete on databasename.tablename to username@localhost identified by "password"
5) Delete permissions
revoke all privileges on *.* from username@localhost
6) flush privileges;

6. MySQL database backup and migration
1) Remote database backup
mysqldump -h 10.201.10.243 -udiscuz -p discuz >discuz_69.sql
2) Import the backed up database
=> mysql -ushenweiyan -p //Log in to MySQL
                                                                                use using newucdb;

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn