What are the common commands in mysql?

Mysql common commands are: 1. "create database name;"; 2. "use databasename;"; 3. "drop database name"; 4. "show tables;"; 5. "select version" etc.

Recommended: "mysql video tutorial"

##MySQL Common database commands

1. Common MySQL commands

create database name; 创建数据库
use databasename; 选择数据库
drop database name 直接删除数据库，不提醒
show tables; 显示表
describe tablename; 表的详细描述
select 中加上distinct去除重复字段
mysqladmin drop databasename 删除数据库前，有提示。
显示当前mysql版本和当前日期
select version(),current_date;

2. Modify the root password in mysql:

shell>mysql -u root -p
mysql> update user set password=password(”xueok654123″) where user=’root’;
mysql> flush privileges //刷新数据库
mysql>use dbname； 打开数据库：
mysql>show databases; 显示所有数据库
mysql>show tables; 显示数据库mysql中所有的表：先use mysql；然后
mysql>describe user; 显示表mysql数据库中user表的列信息）；

3. Grant

Create A full superuser who can connect to the server from anywhere, but must use a password something to do this

mysql> grant all privileges on *.* to user@localhost identified by ’something’ with

Add new user

格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”
GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY ’something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY ’something’ WITH GRANT OPTION;

Remove authorization:

mysql> revoke all privileges on *.* from root@”%”;
mysql> delete from user where user=”root” and host=”%”;
mysql> flush privileges;

Create A user custom logs in to the specific client it363.com and can access the specific database fangchandb

mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’

Rename table:

mysql > alter table t1 rename t2;

4, mysqldump

Backup database

shell> mysqldump -h host -u root -p dbname >dbname_backup.sql

Restore the database

shell> mysqladmin -h myhost -u root -p create dbname
shell> mysqldump -h host -u root -p dbname < dbname_backup.sql

If you only want to unload the table creation command, the command is as follows:

shell> mysqladmin -u root -p -d databasename > a.sql

If you only want to unload the sql command for inserting data, without the table creation command, The command is as follows:

shell> mysqladmin -u root -p -t databasename > a.sql

So what should I do if I only want data and not any sql commands?

mysqldump -T./ phptest driver

Among them, the plain text file can be unloaded only if the -T parameter is specified, indicating the directory to unload the data. ./ indicates the current directory, which is the same directory as mysqldump. If the driver table is not specified, the entire database data will be unloaded. Each table will generate two files, one is a .sql file, including table creation execution. The other is a .txt file that only contains data and no sql instructions.

5. You can store the query in a file and tell mysql to read the query from the file instead of waiting for keyboard input. You can use the shell type redirection utility to do this.

For example, if there are queries

stored in the file my_file.sql, you can execute these queries as follows:

For example, if you want to write the table creation statement in advance sql.txt:

mysql > mysql -h myhost -u root -p database < sql.txt

The above is the detailed content of What are the common commands in mysql?. For more information, please follow other related articles on the PHP Chinese website!