文章目录

  • DDL(数据定义语言)
  • 创建数据库
  • 创建表
  • 查看表结构
  • 克隆表结构
  • TRUNCATE TABLE
  • 删除表
  • 删除数据库
  • DML(数据操作语言)
  • INSERT
  • DELETE
  • UPDATE
  • SELECT
  • 查询并保存到新表
  • REPLACE、 INSERT、 ON DUPLICATE KEY UPDATE
  • 加载示例数据
  • 下载&解压
  • 加载数据
  • 验证数据


此学习文是基于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)
  1. 查找你有权访问的所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| company            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
  1. 获知当前的数据目录
    数据库被创建为数据目录中的一个目录
    基于在线仓库安装的默认数据目录是/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_INCREMENTPRIMARY KEY
  • AUTO_INCREMENT:自动生成线性递增序列,因此不必担心为每一行的id分配值。
  • PRIMARY KEY:每行都由一个非空的UNIQUE列标识。只有一列应该在表中定义。如果一个表包含AUTO_INCREMENT列, 则它会被视为PRIMARY KEY。
  • first_name、last_name和country:它们包含字符串,因此它们被定义为varchar。
  • Engine:与列定义一起,还应该指定存储引擎。一些类型的存储引擎包括InnoDBMyISAMFEDERATEDBLACKHOLECSVMEMORY。在所有引擎中,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、DELETESELECT操作称为数据操作语言(DML)语句
INSERT、UPDATE、DELETE也称为写操作,或者简称为写(write)
SELECT是一个读操作, 简称为读(read)

INSERT

  • INSERT IGNORE INTOINSERT 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)

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

  1. 如果你想在行已经存在的情况下处理重复项,则需要使用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