MySQL基本使用

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

1. 连接MySQL服务器有两种方式，一种是连接本地的MySQL服务器，另一种是连接远程MySQL服务器，分别需要使用MySQL的本地连接账号和远程连接账号。 连接本地MySQL RossideMacBook-Pro:~ rossi$ mysql -u root -p Enter password:******* Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.17 Homebrew Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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 -p Enter password:****** Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 3 Server 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 its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 当尝试连接MySQL服务器时，有如下错误信息，则表示MySQL服务器未运行。 RossideMacBook-Pro:~ rossi$ mysql -u root -p Enter 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 menagerie Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.17 Homebrew Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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()     -> \c mysql>

三. 创建和使用数据库

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 test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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值。例： Whistler Gwen bird \N 1997-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: 1 mysql> 如果在使用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 version mysql> RossideMacBook-Pro:~ rossi$ mysql -u rossi --local-infile=1 -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.17 Homebrew Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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 names You can turn off this feature to get a quicker startup with -A Database changed 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: 1 mysql> 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: 0 mysql>