MySQL基本使用

一. 连接及断开连接MySQL服务器

1. 连接MySQL服务器有两种方式，一种是连接本地的MySQL服务器，另一种是连接远程MySQL服务器，分别需要使用MySQL的本地连接账号和远程连接账号。

连接本地MySQL

RossideMacBook-Pro:~ rossi$ mysql -u root -pEnter password:*******Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.17 HomebrewCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

连接远程MySQL

RossideMacBook-Pro:~ rossi$ mysql -h 192.168.1.9 -u rossi -pEnter password:******Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

当尝试连接MySQL服务器时，有如下错误信息，则表示MySQL服务器未运行。

RossideMacBook-Pro:~ rossi$ mysql -u root -pEnter password:******ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)RossideMacBook-Pro:~

如果MySQL允许匿名账号连接则可以直接使用mysql命令连接，无需其他选项

$ mysql

2. 断开连接，可在命令行输入quit(或\q)

mysql> quit

3. 在连接MySQL服务器时，同时指定要使用的数据库，如此可在连接后不用再使用use语句指定要使用的数据库

RossideMacBook-Pro:~ rossi$ mysql -u rossi -p menagerieEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.17 HomebrewCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

二. 输入SQL语句

1. 连接MySQL服务器后，可以在mysql提示符后输入SQL语句

• SQL语句总是以“;”结尾
• SQL语句不区分大小

单行语句：

mysql> select version(), current_date;+-----------+--------------+| version() | current_date |+-----------+--------------+| 5.7.17    | 2017-03-18   |+-----------+--------------+1 row in set (0.00 sec)mysql>

multiple statements on a single line:

mysql> select version(); select now();+-----------+| version() |+-----------+| 5.7.17    |+-----------+1 row in set (0.00 sec)+---------------------+| now()               |+---------------------+| 2017-03-18 16:47:15 |+---------------------+1 row in set (0.00 sec)mysql>

simple multiple-line statement:

mysql> select    -> user(),    -> current_date;+-----------------+--------------+| user()          | current_date |+-----------------+--------------+| rossi@localhost | 2017-03-18   |+-----------------+--------------+1 row in set (0.00 sec)mysql>

取消未输入完成的语句：

mysql> select    -> user()    -> \cmysql>

三. 创建和使用数据库

1. SHOW语句，查看在MySQL服务器上有哪些数据库

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               |+--------------------+6 rows in set (0.01 sec)mysql>

其中mysql描述了用户进入权限，test作为用户测试时的工作台

2. USE语句，选择要使用的数据库

mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql>

USE语句和QUIT一样，不需要";"结尾。

3. CREATE语句，创建数据库

mysql> create database menagerie;Query OK, 1 row affected (0.00 sec)mysql>

4. GRANT语句，设置数据库权限

mysql> grant all on menagerie.* to 'your_mysql_name'@'your_client_host';

5. 查看当前使用的数据库

mysql> select database();+------------+| database() |+------------+| menagerie  |+------------+1 row in set (0.00 sec)mysql>

6. 查看当前使用的数据库下的表

mysql> show tables;Empty set (0.01 sec)mysql>

7. 创建表

mysql> create table pet (    -> name varchar(20),    -> owner varchar(20),    -> species varchar(20),    -> sex char(1),    -> birth date,    -> death date);Query OK, 0 rows affected (0.01 sec)mysql>

8. DESCRIBE语句，查看表结构

mysql> describe pet;+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| name    | varchar(20) | YES  |     | NULL    |       || owner   | varchar(20) | YES  |     | NULL    |       || species | varchar(20) | YES  |     | NULL    |       || sex     | char(1)     | YES  |     | NULL    |       || birth   | date        | YES  |     | NULL    |       || death   | date        | YES  |     | NULL    |       |+---------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)mysql>

四. 将数据插入表中

往表中添加数据可以通过LOAD DATA和INSERT语句来完成

1. LOAD DATA语句

创建一个文本文件，每一行表示一条数据，然后使用load data语句将文件中的内容导入表中。文件的一行数据，各个值按见表时的顺序用tab分割，对无值的列，可以使用"\N"表示NULL值。例：

WhistlerGwenbird\N1997-12-09\N

mysql> load data local infile '/Users/rossi/Desktop/pet.txt' into table pet;Query OK, 1 row affected, 1 warning (0.00 sec)Records: 1  Deleted: 0  Skipped: 0  Warnings: 1mysql>

如果在使用load data语句时报1148错误，提示此命令该版本的MySQL不支持，可能的原因是local-infile参数默认设为了0，即不支持从本地load文件，可以在连接MySQL时手动将该参数设置为1。

mysql> load data local infile '/Users/rossi/Desktop/pet.txt' into table pet;ERROR 1148 (42000): The used command is not allowed with this MySQL versionmysql>

RossideMacBook-Pro:~ rossi$ mysql -u rossi --local-infile=1 -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.7.17 HomebrewCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use menagerie;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> load data local infile '/Users/rossi/Desktop/pet.txt' into table pet;Query OK, 1 row affected, 1 warning (0.00 sec)Records: 1  Deleted: 0  Skipped: 0  Warnings: 1mysql>

2. INSEERT语句

mysql> insert into pet    -> values    -> ('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL);Query OK, 1 row affected (0.00 sec)mysql>

五. 从表中获取数据

SELECT语句，从表中获取数据，其一般形式如下：

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy;

六. 删除表中所有数据

DELETE语句，删除指定表中所有数据

mysql> delete from pet;Query OK, 2 rows affected (0.01 sec)

七. 更新表中数据

UPDATE语句，更新表中的数据

mysql> update pet set birth = '1989-08-31' where name = 'Puffball';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql>