1. Connect to the database
Format: mysql -h host address -u username -p user password
1.1. Connect to MYSQL on this machine.
First open the DOS window, then enter the directory mysqlbin, and then type the command mysql -u root -p. After pressing Enter, you will be prompted to enter the password.
Note that the user name may or may not have spaces before it, but there must be no spaces before the password, otherwise it will be You re-enter your password.
If MYSQL has just been installed, the super user root does not have a password, so just press Enter to enter MYSQL. The MYSQL prompt is: mysql>

1.2 Connect to MYSQL on the remote host.

Assume that the IP of the remote host is: 110.110.110.110, the user name is root, and the password is abcd123. Then type the following command:
mysql -h110.110.110.110 -u root -p 123; (Note: There is no need to add a space between u and root, and the same is true for others)
1.3 Exit the MYSQL command: exit (Enter)

2. Add a user
Format: grant select on database.* to username@login host identified by “password”
2.1 Add a user test1 with password abc, so that he can log in on any host and query all databases. Permissions to insert, modify, and delete.

First connect to MYSQL as the root user, and then type the following command:

grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”;

But adding users is very dangerous. If someone knows the password of test1, then he can log in to any computer on the Internet. Log in to your mysql database and do whatever you want with your data. See 2.2 for the solution.
2.2 Add a user test2 with the password abc, so that he can only log in on localhost, and can query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), In this way, even if the user knows the password of test2, he cannot directly access the database from the Internet, and can only access it through the web page on the MYSQL host.

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;

If you don’t want test2 to have a password, you can type another command to eliminate the password.

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”

3. Operating the database

3.1 Create database
Note: You must connect to the Mysql server before creating the database
Command: create database
Example 1: Create a database named xhkdb

mysql> create database xhkdb;

Example 2 : Create a database and assign users
①CREATE DATABASE database name;
②GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON database name.* TO username@localhost IDENTIFIED BY 'password';
③SET PASSWORD FOR 'database name '@'localhost' = OLD_PASSWORD('password');
Execute 3 commands in sequence to complete the database creation.
Note: The Chinese “password” and “database” need to be set by the user themselves.

3.2 Show databases
Command: show databases (note: there is an s at the end)

mysql> show databases

3.3 Delete database
Command: drop database
For example: delete the database named xhkdb

mysql> drop database xhkdb;

Example 1: Delete a database that is sure to exist

mysql> drop database drop_database;
Query OK, 0 rows affected (0.00 sec)

Example 2: Delete a database that is not sure to exist

mysql> drop database drop_database;
ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
//發(fā)生錯誤，不能刪除'drop_database'數(shù)據(jù)庫，該數(shù)據(jù)庫不存在。
mysql> drop database if exists drop_database;
Query OK, 0 rows affected, 1 warning (0.00 sec)//產(chǎn)生一個警告說明此數(shù)據(jù)庫不存在
mysql> create database drop_database;
Query OK, 1 row affected (0.00 sec)
mysql> drop database if exists drop_database;//if exists 判斷數(shù)據(jù)庫是否存在，不存在也不產(chǎn)生錯誤
Query OK, 0 rows affected (0.00 sec)

3.4 Connect to the database
Command: use
For example: If the xhkdb database exists, try to access it: mysql> ; use xhkdb;
Screen prompt: Database changed
The use statement can notify MySQL to use the db_name database as the default (current) database for subsequent statements. This database remains the default database until the end of the segment, or until a different USE statement is issued:

mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
mysql> USE db2;
mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable

Using a USE statement to mark a specific current database does not prevent you from accessing tables in other databases. The following example can access the author table from the db1 database and the edit table from the db2 database:

mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
 ->  WHERE author.editor_id = db2.editor.editor_id;

To exit the database or connect to other databases, just user 'other database name'.

3.5 Current database selection
Command: mysql> select database();
The SELECT command in MySQL is similar to print or write in other programming languages. You can use it to display the results of a string, number, mathematical expression, etc. wait. How to use the special features of the SELECT command in MySQL?

(1). Display the MYSQL version

mysql> select version(); 
+-----------------------+ 
| version()    | 
+-----------------------+ 
| 6.0.4-alpha-community | 
+-----------------------+ 
1 row in set (0.02 sec)

(2). Display the current time

mysql> select now(); 
+---------------------+ 
| now()    | 
+---------------------+ 
| 2009-09-15 22:35:32 | 
+---------------------+ 
1 row in set (0.04 sec)

(3). Display the year, month and day

SELECT DAYOFMONTH(CURRENT_DATE); 
+--------------------------+ 
| DAYOFMONTH(CURRENT_DATE) | 
+--------------------------+ 
|      15 | 
+--------------------------+ 
1 row in set (0.01 sec) 
  
SELECT MONTH(CURRENT_DATE); 
+---------------------+ 
| MONTH(CURRENT_DATE) | 
+---------------------+ 
|     9 | 
+---------------------+ 
1 row in set (0.00 sec) 
  
SELECT YEAR(CURRENT_DATE); 
+--------------------+ 
| YEAR(CURRENT_DATE) | 
+--------------------+ 
|    2009 | 
+--------------------+ 
1 row in set (0.00 sec)

(4). Display the string

mysql> SELECT "welecome to my blog!"; 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
1 row in set (0.00 sec)

(5). When calculating The tool uses

select ((4 * 4) / 10 ) + 25; 
+----------------------+ 
| ((4 * 4) / 10 ) + 25 | 
+----------------------+ 
|    26.60 | 
+----------------------+ 
1 row in set (0.00 sec)

(6) to concatenate strings

select CONCAT(f_name, " ", l_name) 
AS Name
from employee_data 
where title = 'Marketing Executive'; 
+---------------+ 
| Name   | 
+---------------+ 
| Monica Sehgal | 
| Hal Simlai | 
| Joseph Irvine | 
+---------------+ 
3 rows in set (0.00 sec)

Note: The CONCAT() function is used here to concatenate strings. In addition, we also used the AS we learned before to give the result column 'CONCAT(f_name, " ", l_name)' a pseudonym

4. Table operations
4.1 Create table
Command: create table

[,.. ]);
For example, create a table named MyClass

mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));

4.2 Get the table structure
Command: desc table name, or show columns from table name

mysql> desc MyClass;
mysql> show columns from MyClass;

使用MySQL數(shù)據(jù)庫desc 表名時，我們看到Key那一欄，可能會有4種值，即' '，'PRI'，'UNI'，'MUL'。
(1).如果Key是空的, 那么該列值的可以重復, 表示該列沒有索引, 或者是一個非唯一的復合索引的非前導列；
(2).如果Key是PRI, 那么該列是主鍵的組成部分；
(3).如果Key是UNI, 那么該列是一個唯一值索引的第一列(前導列),并別不能含有空值(NULL)；
(4).如果Key是MUL, 那么該列的值可以重復, 該列是一個非唯一索引的前導列(第一列)或者是一個唯一性索引的組成部分但是可以含有空值NULL。
如果對于一個列的定義，同時滿足上述4種情況的多種，比如一個列既是PRI,又是UNI，那么"desc 表名"的時候，顯示的Key值按照優(yōu)先級來顯，PRI->UNI->MUL。那么此時，顯示PRI。
一個唯一性索引列可以顯示為PRI,并且該列不能含有空值，同時該表沒有主鍵。
一個唯一性索引列可以顯示為MUL, 如果多列構(gòu)成了一個唯一性復合索引，因為雖然索引的多列組合是唯一的，比如ID+NAME是唯一的，但是沒一個單獨的列依然可以有重復的值，只要ID+NAME是唯一的即可。

4.3刪除表
命令：drop table <表名>
例如：刪除表名為 MyClass 的表

mysql> drop table MyClass;

DROP TABLE用于取消一個或多個表。您必須有每個表的DROP權(quán)限。所有的表數(shù)據(jù)和表定義會被取消，所以使用本語句要小心！
注意：對于一個帶分區(qū)的表，DROP TABLE會永久性地取消表定義，取消各分區(qū)，并取消儲存在這些分區(qū)中的所有數(shù)據(jù)。DROP TABLE還會取消與被取消

的表有關聯(lián)的分區(qū)定義（.par）文件。
對與不存在的表，使用IF EXISTS用于防止錯誤發(fā)生。當使用IF EXISTS時，對于每個不存在的表，會生成一個NOTE。
RESTRICT和CASCADE可以使分區(qū)更容易。目前，RESTRICT和CASCADE不起作用。

4.4向表插入數(shù)據(jù)
命令：insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]
例如：往表 MyClass中插入二條記錄, 這二條記錄表示：編號為1的名為Tom的成績?yōu)?6.45, 編號為2 的名為Joan 的成績?yōu)?2.99， 編號為3 的名為

Wang 的成績?yōu)?6.5。
mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);
注意：insert into每次只能向表中插入一條記錄。

4.5查詢表
(1)、查詢所有行
命令： select <字段1，字段2，...> from < 表名 > where < 表達式 >
例如：查看表 MyClass 中所有數(shù)據(jù)
mysql> select * from MyClass;
(2）、查詢前幾行數(shù)據(jù)
例如：查看表 MyClass 中前2行數(shù)據(jù)
mysql> select * from MyClass order by id limit 0,2;
select一般配合where使用，以查詢更精確更復雜的數(shù)據(jù)。

4.6刪除表
命令：delete from 表名 where 表達式
例如：刪除表 MyClass中編號為1 的記錄
mysql> delete from MyClass where id=1;

4.7修改表中的數(shù)據(jù)
語法：update 表名 set 字段=新值,… where 條件
mysql> update MyClass set name='Mary' where id=1;

例子1：單表的MySQL UPDATE語句：

 UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

例子2：多表的UPDATE語句：

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

UPDATE語法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要給予哪些值。WHERE子句指定應更新哪些行。如果沒有WHERE子句，則更新所有的行。如果指定了ORDER BY子句，則按照被指定的順序?qū)π羞M行更新。LIMIT子句用于給定一個限值，限制可以被更新的行的數(shù)目。

4.8增加表字段
命令：alter table 表名 add字段 類型 其他;
例如：在表MyClass中添加了一個字段passtest，類型為int(4)，默認值為0
mysql> alter table MyClass add passtest int(4) default '0'[/code]
加索引：mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);
例子： mysql> alter table employee add index emp_name (name);
加主關鍵字的索引：mysql> alter table 表名 add primary key (字段名);
例子： mysql> alter table employee add primary key(id);
加唯一限制條件的索引：mysql> alter table 表名 add unique 索引名 (字段名);
例子： mysql> alter table employee add unique emp_name2(cardnumber);
刪除某個索引：mysql> alter table 表名 drop index 索引名;
例子： mysql>alter table employee drop index emp_name;
增加字段：mysql> ALTER TABLE table_name ADD field_name field_type;
修改原字段名稱及類型：mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
修改字段類型：mysql>ALTER TABLE table_name MODIFY colum_name field_type new_type
刪除字段：MySQL ALTER TABLE table_name DROP field_name;

4.9修改表名
命令：rename table 原表名 to 新表名;
例如：在表MyClass名字更改為YouClass

mysql> rename table MyClass to YouClass;

當你執(zhí)行 RENAME 時，你不能有任何鎖定的表或活動的事務。你同樣也必須有對原初表的 ALTER 和 DROP 權(quán)限，以及對新表的 CREATE 和 INSERT 權(quán)限。
如果在多表更名中，MySQL 遭遇到任何錯誤，它將對所有被更名的表進行倒退更名，將每件事物退回到最初狀態(tài)。
RENAME TABLE 在 MySQL 3.23.23 中被加入。

五、備份數(shù)據(jù)

命令在DOS的[url=file://\\mysql\\bin]\\mysql\\bin[/url]目錄下執(zhí)行
(1).導出整個數(shù)據(jù)庫
導出文件默認是存在mysql\bin目錄下
mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 > 導出的文件名
mysqldump -u user_name -p123456 database_name > outfile_name.sql
(2).導出一個表
mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 表名> 導出的文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql
(3).導出一個數(shù)據(jù)庫結(jié)構(gòu)
mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
-d 沒有數(shù)據(jù) –add-drop-table 在每個create語句之前增加一個drop table
(4).帶語言參數(shù)導出
mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql
例如，將aaa庫備份到文件back_aaa中：

[root@test1 root]# cd　/home/data/mysql
[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

六、一個完整的數(shù)據(jù)庫創(chuàng)建實例

drop database if exists school; //如果存在SCHOOL則刪除
create database school; //建立庫SCHOOL
use school; //打開庫SCHOOL
create table teacher //建立表TEACHER
(
 id int(3) auto_increment not null primary key,
 name char(10) not null,
 address varchar(50) default ''深圳'',
 year date
); //建表結(jié)束
 
//以下為插入字段
insert into teacher values('''',''glchengang'',''深圳一中'',''1976-10-10'');
insert into teacher values('''',''jack'',''深圳一中'',''1975-12-23'');

注：在建表中
(1)、將ID設為長度為3的數(shù)字字段:int(3)；并讓它每個記錄自動加一:auto_increment；并不能為空:not null；而且讓他成為主字段primary key。
(2)、將NAME設為長度為10的字符字段
(3)、將ADDRESS設為長度50的字符字段，而且缺省值為深圳。

如果你在mysql提示符鍵入上面的命令也可以，但不方便調(diào)試。
(1)、你可以將以上命令原樣寫入一個文本文件中，假設為school.sql，然后復制到c:\\下，并在DOS狀態(tài)進入目錄[url=file://\\mysql\\bin]\

\mysql\\bin[/url]，然后鍵入以下命令：mysql -uroot -p密碼 < c:\\school.sql
如果成功，空出一行無任何顯示；如有錯誤，會有提示。（以上命令已經(jīng)調(diào)試，你只要將//的注釋去掉即可使用）。
(2)、或者進入命令行后使用 mysql> source c:\\school.sql; 也可以將school.sql文件導入數(shù)據(jù)庫中。

以上就是完整版的Mysql命令大全，希望對大家熟練使用Mysql命令有所幫助。

Statement of this Website

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

Hot AI Tools

Undress AI Tool

Undress images for free

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Show More

Hot Article

Grass Wonder Build Guide | Uma Musume Pretty Derby

1 months ago By Jack chen

Roblox: 99 Nights In The Forest - All Badges And How To Unlock Them

4 weeks ago By DDD

Uma Musume Pretty Derby Banner Schedule (July 2025)

1 months ago By Jack chen

RimWorld Odyssey Temperature Guide for Ships and Gravtech

3 weeks ago By Jack chen

Windows Security is blank or not showing options

1 months ago By 下次還敢

Show More

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Show More

Hot Topics

Laravel Tutorial

1600

29

PHP Tutorial

1502

276

Show More

<label id="hcjxm"></label>