时间：2021-07-01 10:21:17 帮助过：7人阅读
mysql --help
2.2.1 使用以下命令连接到MySQL
mysql -h localhost -u root -p
命令详解：
-h后面参数表示主机名。如果MySQL安装在了本地，后面的参数可以使用localhost表示本地。也可以省略-h参数。
-u后面的参数表示使用的账户名。可以使用root用户，前提是你得记得安装MySQL时输入的root用户密码。
-p参数表示需要输入密码。一般来说-p参数是必须的。
2.1.2 使用quit命令退出mysql命令行
quit
三 键入查询语句
3.1 查询MySQL版本和当前时间
SELECT VERSION(), CURRENT_DATE(); +-----------+----------------+ | VERSION() | CURRENT_DATE() | +-----------+----------------+ | 5.7.21 | 2018-02-14 | +-----------+----------------+ 1 row in set (0.06 sec)
注意：SQL语句后面有个分号，quit命令后没有分号。
3.2 使用MySQL做简单计算
SELECT SIN(PI()/2), (4+1)*5; +-------------+---------+ | SIN(PI()/2) | (4+1)*5 | +-------------+---------+ | 1 | 25 | +-------------+---------+ 1 row in set (0.05 sec)
3.3 将多条命令写在同一行
SELECT VERSION(); SELECT NOW(); +-----------+ | VERSION() | +-----------+ | 5.7.21 | +-----------+ 1 row in set (0.00 sec) +---------------------+ | NOW() | +---------------------+ | 2018-02-14 15:11:56 | +---------------------+ 1 row in set (0.00 sec)
3.4 将一条命令写在多行中
SELECT USER() , CURRENT_DATE(); +----------------+----------------+ | USER() | CURRENT_DATE() | +----------------+----------------+ | root@localhost | 2018-02-14 | +----------------+----------------+ 1 row in set (0.05 sec)
注意：在MySQL中，识别一条SQL命令的标志是分号。所以我们可以把一条命令写在多行中。
3.5 撤销当前输入
mysql> SELECT -> USER() -> \c mysql>
注意：c是小写的。
四 数据库的创建和使用
4.1 查看当前系统中的数据库
SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.15 sec)
注意：SHOW DATEBASES;只会将当前用户具有的SHOW DATABASES命令权限的数据库打印出来。
4.2 使用（进入)数据库
USE sys 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
注意：USE和前面的QUIT一样，后面是不接分号的。与之前命令不一样的是USE命令必须写在单独一行中，不能跨越多行。
4.3 创建一个数据库
CREATE DATABASE menagerie; Query OK, 1 row affected (0.07 sec)
注意：在Linux下数据库名是大小写敏感的。
4.4 在连接MySQL时就指定使用menagerie数据库
mysql -u root -p menagerie
4.5 查看当前使用的是哪个数据库
SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+ 1 row in set (0.00 sec)
4.6 查看当前数据库中有哪些表
SELECT TABLES; Empty set (0.00 sec)
注意：刚刚创建的数据库中是没有表的。
4.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.15 sec) mysql> SHOW TABLES; +---------------------+ | Tables_in_menagerie | +---------------------+ | pet | +---------------------+ 1 row in set (0.01 sec)
4.8 查看表的信息
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)
4.9 向表中输入信息
第一步：创建一个pet.sql文件，文件内容如下。
INSERT INTO pet VALUES (‘Fluffy‘, ‘Harold‘, ‘cat‘, ‘f‘, ‘1993-02-04‘, NULL); INSERT INTO pet VALUES (‘Claws‘, ‘Gwen‘, ‘cat‘, ‘m‘, ‘1994-03-17‘, NULL); INSERT INTO pet VALUES (‘Buffy‘, ‘Harold‘, ‘dog‘, ‘f‘, ‘1989-05-13‘, NULL); INSERT INTO pet VALUES (‘Fang‘, ‘Benny‘, ‘dog‘, ‘m‘, ‘1990-08-27‘, NULL); INSERT INTO pet VALUES (‘Bowser‘, ‘Diane‘, ‘dog‘, ‘m‘, ‘1979-08-31‘, ‘1995-07-29‘); INSERT INTO pet VALUES (‘Chirpy‘, ‘Gwen‘, ‘bird‘, ‘f‘, ‘1998-09-11‘, NULL); INSERT INTO pet VALUES (‘Whistler‘, ‘Gwen‘, ‘bird‘, NULL, ‘1997-12-09‘, NULL); INSERT INTO pet VALUES (‘Slim‘, ‘Benny‘, ‘snake‘, ‘m‘, ‘1996-04-29‘, NULL); INSERT INTO pet VALUES (‘Puffball‘, ‘Diane‘, ‘hamster‘, ‘f‘, ‘1999-03-30‘, NULL);
第二步：使用下列命令执行sql文件，随后查看表的内容。
mysql> source ~/pet.sql Query OK, 1 row affected (0.13 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.02 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ 9 rows in set (0.00 sec)
五 SELECT语句
5.1 选择所有的列
mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ 9 rows in set (0.00 sec)
5.2 修改数据
mysql> UPDATE pet SET birth = ‘1989-08-31‘ WHERE name = ‘Bowser‘; Query OK, 1 row affected (0.17 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT birth FROM pet WHERE name = ‘Bowser‘; +------------+ | birth | +------------+ | 1989-08-31 | +------------+ 1 row in set (0.00 sec)
5.3 清空表内容
mysql> DELETE FROM pet; Query OK, 9 rows affected (0.08 sec) mysql> SELECT * FROM pet; Empty set (0.00 sec)
5.4 选择特定行
mysql> SELECT * FROM pet WHERE name = ‘Bowser‘; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)
5.5 根据条件选择
mysql> SELECT * FROM pet WHERE birth >= ‘1998-1-1‘; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 2 rows in set (0.00 sec)
5.6 根据组合条件选择
5.6.1
mysql> SELECT * FROM pet WHERE species = ‘dog‘ AND sex = ‘f‘; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 1 row in set (0.04 sec)
5.6.2
mysql> SELECT * FROM pet WHERE -> (species = ‘cat‘ AND sex = ‘m‘) -> OR (species = ‘dog‘ AND sex = ‘f‘); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 2 rows in set (0.00 sec)
注意：AND优先级高于OR。
5.7 选择特定列
5.7.1
mysql> SELECT name, birth FROM pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Puffball | 1999-03-30 | +----------+------------+ 9 rows in set (0.00 sec)
5.7.2
mysql> SELECT owner FROM pet; +--------+ | owner | +--------+ | Harold | | Gwen | | Harold | | Benny | | Diane | | Gwen