一. 连接及断开连接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命令连接,无需其他选项
$ mysql2. 断开连接,可在命令行输入quit(或\q)
mysql> quit3. 在连接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>