MySQL - MySQL 8.0(二)基本操作：数据

此学习文是基于MySQL 8.0写的
得益于大神朋友的悉心指导解决不少坑，才写出此文，向大神奉上膝盖

DDL（数据定义语言）

创建数据库

create database if not exists {数据库名};

mysql> create database if not exists company;
Query OK, 1 row affected (0.00 sec)

1. 查找连接到了哪个数据库

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

2. 查找你有权访问的所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| company            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

3. 获知当前的数据目录
   数据库被创建为数据目录中的一个目录
   基于在线仓库安装的默认数据目录是/var/lib/mysql
   基于离线二进制文件安装的，数据目录则是/usr/local/mysql/data/

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

创建表

• 语法

create table if not exists {数据库名}.{表名}(
	字段1 数据类型,
	字段2 数据类型,
	字段3 数据类型
) Engine=InnoDB;

• 写个栗子，养成好习惯，加上前缀t_方便后面开发人员维护时好识别

mysql> create table if not exists company.t_customers(
    -> id int unsigned auto_increment primary key, 
    -> first_name varchar(20),
    -> last_name varchar(20),
    -> country varchar(20)
    -> ) Engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| t_customers         |
+-------------------+
1 row in set (0.00 sec)

其中的选项解释如下

• 句点符号：表可以使用database.table引用。 如果已经连接到数据库，则可
  以简单地使用 t_customers 而不是 company.t_customers。
• IF NOT EXISTS：如果存在一个具有相同名字的表，并且你指定了这个子句，
  MySQL只会抛出一个警告，告知表已经存在。否则，MySQL将抛出一个错误。
• id：它被声明为一个整型数，因为它只包含整型数。除此之外，还有两个关键字，AUTO_INCREMENT和PRIMARY KEY。
• AUTO_INCREMENT：自动生成线性递增序列，因此不必担心为每一行的id分配值。
• PRIMARY KEY：每行都由一个非空的UNIQUE列标识。只有一列应该在表中定义。如果一个表包含AUTO_INCREMENT列， 则它会被视为PRIMARY KEY。
• first_name、last_name和country：它们包含字符串，因此它们被定义为varchar。
• Engine：与列定义一起，还应该指定存储引擎。一些类型的存储引擎包括InnoDB、MyISAM、FEDERATED、BLACKHOLE、CSV和MEMORY。在所有引擎中，InnoDB是唯一的事务引擎，也是默认引擎。

1. 列出所有存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看表结构

SHOW CREATE TABLE {数据库名}.{表名}\G

mysql> SHOW CREATE TABLE company.t_customers\G
*************************** 1. row ***************************
       Table: t_customers
Create Table: CREATE TABLE `t_customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

或者

DESC {数据库名}.{表名};

mysql> DESC company.t_customers;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(20)      | YES  |     | NULL    |                |
| last_name  | varchar(20)      | YES  |     | NULL    |                |
| country    | varchar(20)      | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

克隆表结构

CREATE TABLE {新表名} LIKE {数据库名}.{表名};

mysql> CREATE TABLE t_customers_new LIKE company.t_customers; 
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| t_customers         |
| t_customers_new     |
+-------------------+
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE company.t_customers_new\G
*************************** 1. row ***************************
       Table: t_customers_new
Create Table: CREATE TABLE `t_customers_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

TRUNCATE TABLE

是MySQL中的DDL操作，也就是说一旦数据被清空，就不能被回滚

truncate table {数据库名}.{表名};

删除表

drop table if exists {数据库名}.{表名};

mysql> drop table if exists company.t_customers_new;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| t_customers         |
+-------------------+
1 row in set (0.00 sec)

删除数据库

drop database if exists {数据库名};

mysql> drop database if exists company;
Query OK, 0 rows affected (0.01 sec)

---

DML（数据操作语言）

INSERT、UPDATE、DELETE和SELECT操作称为数据操作语言（DML）语句
INSERT、UPDATE、DELETE也称为写操作，或者简称为写（write）
SELECT是一个读操作， 简称为读（read）

INSERT

• INSERT IGNORE INTO与INSERT INTO的区别就是
  INSERT IGNORE INTO会忽略数据库中已经存在的数据；
  如果数据库没有数据，就插入新的数据；如果有数据的话就跳过这条数据；
  这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

insert [ignore] into {数据库}.{表名} (字段1, 字段2,……) values (数据1, 数据2,……);

mysql> insert ignore into company.t_customers (
    -> first_name,
    -> last_name,
    -> country
    -> ) values 
    -> ('Mike', 'Christensen', 'USA'),
    -> ('Andy', 'Hollands', 'Australia'),
    -> ('Ravi', 'Vedantam', 'India'),
    -> ('Rajiv', 'Perera', 'Sri Lanka');
Query OK, 4 rows affected (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from company.t_customers;
+----+------------+-------------+-----------+
| id | first_name | last_name   | country   |
+----+------------+-------------+-----------+
|  1 | Mike       | Christensen | USA       |
|  2 | Andy       | Hollands    | Australia |
|  3 | Ravi       | Vedantam    | India     |
|  4 | Rajiv      | Perera      | Sri Lanka |
+----+------------+-------------+-----------+
4 rows in set (0.00 sec)

# insert ignore into再插入相同id，则只会警告不会报错
mysql> insert ignore into company.t_customers (id,first_name, last_name, country ) values  (1,'Mike', 'Christensen', 'USA');
Query OK, 0 rows affected, 1 warning (0.00 sec)

DELETE

delete from {数据库名}.{表名} where 字段 = '{过滤数据}';

mysql> delete from schema.table_name where column_name = 'filter_data';

UPDATE

update {数据库名}.{表名} set 列名 = '{更新数据}' where 列名 = '{过滤数据}';

mysql> update schema.table_name set column_name = 'update_data' where column_name = 'filter_data';

SELECT

所有的过滤条件都是通过WHERE子句给出的，除整型数和浮点数之外，其他所有内容都应放在引号内。

select 列名1, 列名2,…… from {数据库名}.{表名} where 字段 = '{过滤数据}';

mysql> select * from schema.table_name where column_name = 'filter_data';
mysql> select * from schema.table_name where column_name <> 'filter_data';
mysql> select * from schema.table_name where column_name like '%filter_data%';
mysql> select * from schema.table_name where column_name not like '%filter_data%';
mysql> select * from schema.table_name where column_name in ('filter_data');
mysql> select * from schema.table_name where column_name not in ('filter_data');
mysql> select * from schema.table_name where column_name between 'filter_data1' and 'filter_data2';
mysql> select * from schema.table_name where exists (filter_data);
mysql> select * from schema.table_name where not exists (filter_data);
# 正则过滤
mysql> select * from schema.table_name where column_name rlike 'filter_data';
mysql> select * from schema.table_name where column_name regexp 'filter_data';
mysql> select * from schema.table_name where column_name not regexp 'filter_data';
# 取顺序/倒叙12条
mysql> select * from schema.table_name where column_name = 'filter_data' order by column_name asc/desc limit 12;
# 聚合
mysql> select column_name, count(*) from schema.table_name where column_name = 'filter_data' group by column_name;
mysql> select column_name, sum(column_name) from schema.table_name where column_name = 'filter_data' group by column_name;
mysql> select column_name, avg(column_name) from schema.table_name where column_name = 'filter_data' group by column_name;
# 去重
mysql> select distinct column_name from schema.table_name where column_name = 'filter_data';
# having过滤
mysql> select column_name, avg(column_name) as col_avg from schema.table_name where column_name = 'filter_data' group by column_name having col_avg > 12;

• 正则表达式

表达式	描述
*	零次或多次重复
+	一个或多个重复
?	可选字符
.	任何字符
\.	区间
^	以……开始
$	以……结束
[abc]	只有a、b或c
[^abc]	非a，非b，亦非c
[a-z]	字符 a 到 z
[0-9]	数字 0 到 9
^……$	开始和结束
\d	任意数字
\D	任意非数字字符
\s	任意空格
\S	任何非空白字符
\w	任何字母数字字符
\W	任何非字母数字字符
{m}	m 次重复
{m,n}	m 到 n 次重复

查询并保存到新表

• 创建表时保存

create table if not exists {shcema}.{table_new} as select 列名1, 列名2,…… from {schema}.{table} where {filter};

• 已有表时保存
  为避免重复，可以使用INSERT IGNORE INTO

insert ignore into {shcema}.{table_new} select 列名1, 列名2,…… from {schema}.{table} where {filter};

REPLACE、 INSERT、 ON DUPLICATE KEY UPDATE

1. 在很多情况下，我们需要处理重复项：
   行的唯一性由主键标识
   如果行已经存在，则replace into会简单地删除行并插入新行；
   如果行不存在，则replace into等同于insert into。
   整体效果类似Oracle的merge into，表现为UpdateInsert现象

replace into {数据库名}.{表名} values (data1, data2,……);

mysql> replace into company.t_customers values (1,'Mike_replace', 'Christensen', 'USA');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from company.t_customers;
+----+--------------+-------------+-----------+
| id | first_name   | last_name   | country   |
+----+--------------+-------------+-----------+
|  1 | Mike_replace | Christensen | USA       |
|  2 | Andy         | Hollands    | Australia |
|  3 | Ravi         | Vedantam    | India     |
|  4 | Rajiv        | Perera      | Sri Lanka |
+----+--------------+-------------+-----------+
4 rows in set (0.00 sec)

可以看到有两行受到影响，一个重复行被删除，一个新行被插入

2. 如果你想在行已经存在的情况下处理重复项，则需要使用ON DUPLICATE KEY UPDATE。如果指定了ON DUPLICATE KEY UPDATE选项，并且INSERT语句在PRIMARY KEY中引发了重复值，则MySQL会用新值更新已有行。

insert into {数据库名}.{表名} values (key, value) on duplicate key update value_name = value_name + values(value_name);

mysql> CREATE TABLE company.payments (
    -> customer_name varchar(20) PRIMARY KEY,
    -> payment float
    -> );
Query OK, 0 rows affected (0.02 sec)

# 初始化数据
mysql> INSERT INTO company.payments VALUES ('Mike Christensen', 200) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO company.payments VALUES ('Ravi Vedantam', 500) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);
Query OK, 1 row affected (0.00 sec)

mysql> select * from payments;
+------------------+---------+
| customer_name    | payment |
+------------------+---------+
| Mike Christensen |     200 |
| Ravi Vedantam    |     500 |
+------------------+---------+
2 rows in set (0.00 sec)

# 'Mike Christensen' 追加了 300
mysql> INSERT INTO company.payments VALUES ('Mike Christensen', 300) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from payments;
+------------------+---------+
| customer_name    | payment |
+------------------+---------+
| Mike Christensen |     500 |
| Ravi Vedantam    |     500 |
+------------------+---------+
2 rows in set (0.00 sec)

VALUES(payment)：指INSERT语句中给出的值，payment指的是表中的列

加载示例数据

MySQL 提供了一个示例employees数据库和大量数据供我们学习使用

下载&解压

# nangy @ nangy-vm in ~/Downloads [15:38:52] 
$ wget https://codeload.github.com/datacharmer/test_db/zip/master -O master.zip

# nangy @ nangy-vm in ~/Downloads [15:38:52] 
$ unzip master.zip

加载数据

# nangy @ nangy-vm in ~/Downloads [15:38:52] 
$ cd test_db-master
# nangy @ nangy-vm in ~/Downloads/test_db-master [16:31:29] 
$ mysql -u root -p < employees.sql 
Enter password: 
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:01:09

验证数据

# nangy @ nangy-vm in ~/Downloads/test_db-master [16:33:32] 
$ mysql -u root -p employees -A
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

# 查看表结构
mysql> DESC employees\G