MySQL的基本操作(针对小白2）

前言

内容有点多，建议起来再看。

这次学习和的部分是MySQL8.0官方文档的第三章里的创建和使用数据库和常见查询示例，地址是：
https://dev.mysql.com/doc/refman/8.0/en/tutorial.html

如果关于MAC版本的MySQL的安装有不懂的，可以看我之前的这篇博客https://blog.csdn.net/qq_37924224/article/details/105288363。

OK，上正文。也可以直接通过目录进行跳转哦

创建和使用数据库

创建,查看，选择和删除数据库

创建数据库：CREATE DATABASE XXX;

mysql> CREATE DATABASE menagerie; Query OK, 1 row affected (0.01 sec) 

查看数据库：SHOW DATABASES;

mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | menagerie          | | mysql              | | performance_schema | | sys                | +--------------------+ 5 rows in set (0.00 sec) 

使用数据库：USE XXX

mysql> USE menagerie Database changed 

删除数据库：DROP DATABASE XXX

mysql> DROP DATABASE menagerie; Query OK, 0 rows affected (0.00 sec) 

展示，创建和删除表格

在上面的USE menagerie之后哈
展示库里的表：SHOW TABLES;

mysql> SHOW TABLES; Empty set (0.00 sec) 

创建表：CREATE TABLE XXX（。。。）
如
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

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.00 sec) 

上面代码的意思是：表被命名为pet，包含的列有六个，分别是name,owner,species,sex,birth,death。数据类型分别为VARCHAR(20)，VARCHAR(20)，VARCHAR(20)，CHAR(1)，DATE，DATE。

接着再次展示库里的表，结果如图

获取有关表结构或查询执行计划的信息：DESCRIBE XXX;

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) 

展示的信息即为每一列的信息
删除那个表：DROP TABLE XXX;

mysql> DROP TABLE pet; Query OK, 0 rows affected (0.01 sec) 

将数据加载到表中，表中数据全清空和特定行的清空

将新行插入到现有表中：INSERT

INSERT INTO pet        VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); 

意思是往pet这个表里插入’Puffball’,‘Diane’,‘hamster’,‘f’,‘1999-03-30’,NULL数据。
检索这个表的全部信息：SELECT * FROM XXX;

mysql> select * from pet; +----------+-------+---------+------+------------+-------+ | name     | owner | species | sex  | birth      | death | +----------+-------+---------+------+------------+-------+ | Puffball | Diane | hamster | f    | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec) 

说明(‘Puffball’,‘Diane’,‘hamster’,‘f’,‘1999-03-30’,NULL)这个数据已经加载进pet这个表中。
表中数据清空：TRUNCATE TABLE XXX;

mysql> select * from pet; +----------+-------+---------+------+------------+-------+ | name     | owner | species | sex  | birth      | death | +----------+-------+---------+------+------------+-------+ | Puffball | Diane | hamster | f    | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec)  mysql> TRUNCATE TABLE pet; Query OK, 0 rows affected (0.02 sec)  mysql> select * from pet; Empty set (0.00 sec) 

清空表中某行数据：DELETE FROM XXX where XXX = “XXX”;

mysql> select * from pet; +----------+-------+---------+------+------------+-------+ | name     | owner | species | sex  | birth      | death | +----------+-------+---------+------+------------+-------+ | Puffball | Diane | hamster | f    | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec)  mysql> DELETE FROM pet where name = "Puffball"; Query OK, 1 row affected (0.01 sec)  mysql> select * from pet; Empty set (0.00 sec) 

从表中检索信息

检索用的是SELECT
学习这步操作之前先输入以下命令

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 ('Slim','Benny','snake','m','1996-04-29',NULL); INSERT INTO pet        VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); 

从而使得pet这个表是这个样子

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 | | Slim     | Benny  | snake   | m    | 1996-04-29 | NULL | | Puffball | Diane  | hamster | f    | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec) 

选择所有数据

select * from XXX;

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 | | Slim     | Benny  | snake   | m    | 1996-04-29 | NULL | | Puffball | Diane  | hamster | f    | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec) 

选择特定行

mysql> SELECT * FROM pet WHERE name = 'Bowser'; +--------+-------+---------+------+------------+------------+ | name   | owner | species | sex  | birth      | death      | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog     | m    | 1979-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec) 

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) 

使用AND 逻辑运算符

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; +--------+-------+---------+------+------------+-------+ | name   | owner | species | sex  | birth      | death | +--------+-------+---------+------+------------+-------+ | Chirpy | Gwen  | bird    | f    | 1998-09-11 | NULL | | Slim   | Benny | snake   | m    | 1996-04-29 | NULL | +--------+-------+---------+------+------------+-------+ 2 rows in set (0.00 sec) 

AND和OR可以混合使用，尽管 AND优先级高于 OR。如果同时使用这两个运算符，则最好使用括号来明确指示应如何对条件进行分组

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) 

选择特定列

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   | 1979-08-31 | | Chirpy   | 1998-09-11 | | Slim     | 1996-04-29 | | Puffball | 1999-03-30 | +----------+------------+ 8 rows in set (0.00 sec) 

结合选取特定行的特定列

mysql> SELECT name, species, birth FROM pet     -> WHERE species = 'dog' OR species = 'cat'; +--------+---------+------------+ | name   | species | birth      | +--------+---------+------------+ | Fluffy | cat     | 1993-02-04 | | Claws  | cat     | 1994-03-17 | | Buffy  | dog     | 1989-05-13 | | Fang   | dog     | 1990-08-27 | | Bowser | dog     | 1979-08-31 | +--------+---------+------------+ 5 rows in set (0.01 sec) 

排序行

使用ORDER BY子句

mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name     | birth      | +----------+------------+ | Bowser   | 1979-08-31 | | Buffy    | 1989-05-13 | | Fang     | 1990-08-27 | | Fluffy   | 1993-02-04 | | Claws    | 1994-03-17 | | Slim     | 1996-04-29 | | Chirpy   | 1998-09-11 | | Puffball | 1999-03-30 | +----------+------------+ 8 rows in set (0.00 sec) 

默认的排序顺序是升序，先是最小值。要以倒序（降序）排序，请将DESC关键字添加到要排序的列的名称上

mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name     | birth      | +----------+------------+ | Puffball | 1999-03-30 | | Chirpy   | 1998-09-11 | | Slim     | 1996-04-29 | | Claws    | 1994-03-17 | | Fluffy   | 1993-02-04 | | Fang     | 1990-08-27 | | Buffy    | 1989-05-13 | | Bowser   | 1979-08-31 | +----------+------------+ 8 rows in set (0.00 sec) 

日期计算

请使用该 TIMESTAMPDIFF()功能。它的参数是要表示结果的单位，以及两个日期之间的差值。

mysql> SELECT name, birth, CURDATE(), ->        TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age     -> FROM pet; +----------+------------+------------+------+ | name     | birth      | CURDATE() | age  | +----------+------------+------------+------+ | Fluffy   | 1993-02-04 | 2020-04-15 | 27 | | Claws    | 1994-03-17 | 2020-04-15 | 26 | | Buffy    | 1989-05-13 | 2020-04-15 | 30 | | Fang     | 1990-08-27 | 2020-04-15 | 29 | | Bowser   | 1979-08-31 | 2020-04-15 | 40 | | Chirpy   | 1998-09-11 | 2020-04-15 | 21 | | Slim     | 1996-04-29 | 2020-04-15 | 23 | | Puffball | 1999-03-30 | 2020-04-15 | 21 | +----------+------------+------------+------+ 8 rows in set (0.01 sec) 

模式匹配

MySQL提供了标准的SQL模式匹配以及基于扩展的正则表达式的模式匹配形式，该正则表达式类似于vi，grep和 sed之类的Unix实用程序使用的扩展正则表达式 。
查找以b开头的name：

mysql> SELECT * FROM pet WHERE name LIKE 'b%'; +--------+--------+---------+------+------------+------------+ | name   | owner  | species | sex  | birth      | death      | +--------+--------+---------+------+------------+------------+ | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL | | Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 2 rows in set (0.00 sec) 

查找以fy结尾的name

mysql> SELECT * FROM pet WHERE name LIKE '%fy'; +--------+--------+---------+------+------------+-------+ | name   | owner  | species | sex  | birth      | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat     | f    | 1993-02-04 | NULL | | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ 2 rows in set (0.00 sec) 

查找包含w的name

mysql> SELECT * FROM pet WHERE name LIKE '%w%'; +--------+-------+---------+------+------------+------------+ | name   | owner | species | sex  | birth      | death      | +--------+-------+---------+------+------------+------------+ | Claws  | Gwen  | cat     | m    | 1994-03-17 | NULL | | Bowser | Diane | dog     | m    | 1979-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 2 rows in set (0.00 sec) 

查找正好包含五个字符的名称，使用_

mysql> SELECT * FROM pet WHERE name LIKE '_____'; +-------+--------+---------+------+------------+-------+ | 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) 

计数行

COUNT(*)计算行数
计算pet表中有多少行

mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 8 | +----------+ 1 row in set (0.01 sec) 

如果要找出每个主人有多少只宠物

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner  | COUNT(*) | +--------+----------+ | Harold | 2 | | Gwen   | 2 | | Benny  | 2 | | Diane  | 2 | +--------+----------+ 4 rows in set (0.00 sec) 

每个动物的种类的数量：

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; +---------+----------+ | species | COUNT(*) | +---------+----------+ | cat     | 2 | | dog     | 3 | | bird    | 1 | | snake   | 1 | | hamster | 1 | +---------+----------+ 5 rows in set (0.00 sec) 

每个动物和性别组合的数量：

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ | species | sex  | COUNT(*) | +---------+------+----------+ | cat     | f    | 1 | | cat     | m    | 1 | | dog     | f    | 1 | | dog     | m    | 2 | | bird    | f    | 1 | | snake   | m    | 1 | | hamster | f    | 1 | +---------+------+----------+ 7 rows in set (0.00 sec) 

仅对狗和猫执行的前一个查询（每个动物和性别组合的数量）

mysql> SELECT species, sex, COUNT(*) FROM pet     -> WHERE species = 'dog' OR species = 'cat' -> GROUP BY species, sex; +---------+------+----------+ | species | sex  | COUNT(*) | +---------+------+----------+ | cat     | f    | 1 | | cat     | m    | 1 | | dog     | f    | 1 | | dog     | m    | 2 | +---------+------+----------+ 4 rows in set (0.00 sec) 

获取有关数据库和表的信息

使用
SHOW或SELECT或DESCRIBE
SHOW DATABASES;

mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | menagerie          | | mysql              | | performance_schema | | sys                | +--------------------+ 5 rows in set (0.00 sec) 

SELECT DATABASE();

mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie  | +------------+ 1 row in set (0.00 sec) 

SHOW TABLES;

mysql> SHOW TABLES; +---------------------+ | Tables_in_menagerie | +---------------------+ | pet                 | +---------------------+ 1 row in set (0.01 sec) 

DESCRIBE pet;

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.01 sec) 

结束语

身为一个菜鸡程序员，目前对自己的要求是博客周更。每周学习新的技能，然后再技能。这周学习MySQL并通过这个博客和巩固自己学到的知识。
这篇博客内容可能有点多，但其实只是原定内容的一半。下一半只能留给下一篇博客了。这个MySQL的系列会写得很细，欢迎哦。如果我的对你有帮助，麻烦点个赞吧，谢谢各位。