MySQL基本语法

操作系统：Debian11
数据库工具：MariaDB
Ver 9.1 Distrib 10.5.18-MariaDB, for debian-linux-gnu on x86_64
参考教程：菜鸟教程+数据管理课程教学内容

每条指令均以，作为结束标志

连接

xxx@debian:~/桌面$ sudo mysql -u root -p
[sudo] xxx 的密码：
Enter password:

成功登陆出现以下提示:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.5.18-MariaDB-0+deb11u1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

若是出现以下报错

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)

考虑是不是数据库未开启，查看状态

$ sudo systemctl status mariadb

若是发现

Status: "MariaDB server is down"

表明数据库服务端已关闭，使用以下指令开启

sudo systemctl start mariadb

数据库创建

CREATE DATABASE xxx;

实例：创建employees数据库

MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.000 sec)

看看现在数据库有无变化

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

成功创建employees数据库

反复添加会如何

MariaDB [(none)]> CREATE DATABASE employees;
ERROR 1007 (HY000): Can't create database 'employees'; database exists

ERROR,该数据库已存在

数据库删除

DROP DATABASE xxx;

实例：删除employees数据库

MariaDB [(none)]> DROP DATABASE employees;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

数据库选择

use xxx;

实例：选择employees数据库

MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use employees;
Database changed

MariaDB [employees]> 
//不得不讲这就比MySQL优雅挺多的，直接显示出所操作的数据库

数据表创建

创建的数据表有三要素：表名，表字段名，定义每个表的字段

通用语法：

CREATE TABLE table_name (column_name column_type);

实例：在RUNOOB数据库中创建runoob_tbl数据表

MariaDB [(none)]> CREATE DATABASE RUNOOB;
Query OK, 1 row affected (0.022 sec)

MariaDB [(none)]> use RUNOOB;
Database changed

MariaDB [RUNOOB]> CREATE TABLE IF NOT EXISTS `runoob_tbl`(
    ->    `runoob_id` INT UNSIGNED AUTO_INCREMENT,
    ->    `runoob_title` VARCHAR(100) NOT NULL,
    ->    `runoob_author` VARCHAR(40) NOT NULL,
    ->    `submission_date` DATE,
    ->    PRIMARY KEY ( `runoob_id` )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.019 sec)

MariaDB [RUNOOB]> show tables;
+------------------+
| Tables_in_RUNOOB |
+------------------+
| runoob_tbl       |
+------------------+
1 row in set (0.001 sec)

数据表删除

通用语法：

DROP TABLE table_name ;

实例：在RUNOOB数据库中删除runoob_tbl数据表

MariaDB [RUNOOB]> drop table runoob_tbl;
Query OK, 0 rows affected (0.011 sec)

MariaDB [RUNOOB]> show tables;
Empty set (0.001 sec)

插入数据

通用语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

实例：

MariaDB [RUNOOB]> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("JAVA", "RUNOOB", '2023-03-26');

读取数据表

通用语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

特别的：可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

实例：

MariaDB [RUNOOB]> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2023-03-25      |
|         2 | JAVA         | RUNOOB        | 2023-03-26      |
+-----------+--------------+---------------+-----------------+

MariaDB [RUNOOB]> select runoob_id, runoob_title from runoob_tbl;
+-----------+--------------+
| runoob_id | runoob_title |
+-----------+--------------+
|         1 | 学习 PHP     |
|         2 | JAVA         |
+-----------+--------------+

WHERE子句

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

实例：

MariaDB [RUNOOB]> SELECT submission_date from runoob_tbl WHERE runoob_id <= 1;
+-----------------+
| submission_date |
+-----------------+
| 2023-03-25      |
+-----------------+

MariaDB [RUNOOB]> SELECT * from runoob_tbl WHERE runoob_id <= 1;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2023-03-25      |
+-----------+--------------+---------------+-----------------+

区分大小写

插入一行小写的数据与原先数据进行对比

MariaDB [RUNOOB]> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("java", "runoob", '2023-04-19');

查看一下

MariaDB [RUNOOB]> SELECT * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2023-03-25      |
|         2 | JAVA         | RUNOOB        | 2023-03-26      |
|         3 | java         | runoob        | 2023-04-19      |
+-----------+--------------+---------------+-----------------+

使用where语句选出其中title为java的

MariaDB [RUNOOB]> SELECT * from runoob_tbl WHERE runoob_title="java";
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         2 | JAVA         | RUNOOB        | 2023-03-26      |
|         3 | java         | runoob        | 2023-04-19      |
+-----------+--------------+---------------+-----------------+

发现大小写均被选出

MySQL 的 WHERE 子句的字符串比较是不区分大小写的,可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

MariaDB [RUNOOB]> SELECT * from runoob_tbl WHERE BINARY runoob_title="java";
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         3 | java         | runoob        | 2023-04-19      |
+-----------+--------------+---------------+-----------------+

UPDATE更新

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。

通用语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

实例：

MariaDB [RUNOOB]> UPDATE runoob_tbl SET runoob_title='python' WHERE runoob_id=3;

update后结果

MariaDB [RUNOOB]> SELECT * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2023-03-25      |
|         2 | JAVA         | RUNOOB        | 2023-03-26      |
|         3 | python       | runoob        | 2023-04-19      |
+-----------+--------------+---------------+-----------------+

实例2：

MariaDB [RUNOOB]> UPDATE runoob_tbl SET runoob_title='python' WHERE runoob_id>=1;

update后

MariaDB [RUNOOB]> SELECT * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | python       | 菜鸟教程      | 2023-03-25      |
|         2 | python       | RUNOOB        | 2023-03-26      |
|         3 | python       | runoob        | 2023-04-19      |
+-----------+--------------+---------------+-----------------+

DELETE语句

你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

通用语法：

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除

实例：

删除runoob_tbl第三条记录

MariaDB [RUNOOB]> DELETE FROM runoob_tbl WHERE runoob_id=3;

查看删除后结果

MariaDB [RUNOOB]> SELECT * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | python       | 菜鸟教程      | 2023-03-25      |
|         2 | python       | RUNOOB        | 2023-03-26      |
+-----------+--------------+---------------+-----------------+

LIKE子句

类似于正则表达式对数据进行筛选

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

通用语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

实例：

筛选出上传时间为三月的数据

MariaDB [RUNOOB]> SELECT * from runoob_tbl WHERE submission_date LIKE '%03%';
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | python       | 菜鸟教程      | 2023-03-25      |
|         2 | python       | RUNOOB        | 2023-03-26      |
+-----------+--------------+---------------+-----------------+

注意要筛选部分的组成结构，如：

MariaDB [RUNOOB]> SELECT * from runoob_tbl WHERE submission_date LIKE '%03';
Empty set (0.001 sec)

UNION

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

ALL: 可选，返回所有结果集，包含重复数据。

实例：

先创建两张tables

MariaDB [RUNOOB]> CREATE TABLE IF NOT EXISTS `Websites`(
    -> `id` INT,
    -> `name` VARCHAR(20),
    -> `url` VARCHAR(20),
    -> `alexa` INT,
    -> `country` VARCHAR(10),
    -> PRIMARY KEY (`id`)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.011 sec)

MariaDB [RUNOOB]> CREATE TABLE `apps`(
    -> `id` INT,
    -> `app_name` VARCHAR(20),
    -> `url` VARCHAR(20),
    -> `country` VARCHAR(10),
    -> PRIMARY KEY (`id`)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.010 sec)

MariaDB [RUNOOB]> SHOW TABLES;
+------------------+
| Tables_in_RUNOOB |
+------------------+
| Websites         |
| apps             |
| runoob_tbl       |
+------------------+
3 rows in set (0.001 sec)

为表格填充数据

MariaDB [RUNOOB]> INSERT INTO Websites
    -> (id, name, url, alexa, country)
    -> VALUES
    -> (1, 'Google', 'www.google.com', 1, 'USA');
Query OK, 1 row affected (0.003 sec)

MariaDB [RUNOOB]> INSERT INTO Websites
    -> (id, name, url, alexa, country)
    -> VALUES
    -> (2, '淘宝', 'www.taobao.com', 13, 'CN');
Query OK, 1 row affected (0.003 sec)

MariaDB [RUNOOB]> INSERT INTO Websites
    -> (id, name, url, alexa, country)
    -> VALUES
    -> (3, '菜鸟教程', 'www.runoob.com', 4689, 'CN');
Query OK, 1 row affected (0.004 sec)

MariaDB [RUNOOB]> INSERT INTO Websites
    -> (id, name, url, alexa, country)
    -> VALUES
    -> (4, '微博', 'weibo.com', 20, 'CN');
Query OK, 1 row affected (0.004 sec)

MariaDB [RUNOOB]> INSERT INTO Websites
    -> (id, name, url, alexa, country)
    -> VALUES
    -> (5, 'Facebook', 'www.facebook.com', 3, 'USA');
Query OK, 1 row affected (0.003 sec)

MariaDB [RUNOOB]> INSERT INTO Websites
    -> (id, name, url, alexa, country)
    -> VALUES
    -> (7, 'stackoverflow', 'stackoverflow.com', 0, 'IND');
Query OK, 1 row affected (0.003 sec)

MariaDB [RUNOOB]> SELECT * FROM Websites;
+----+---------------+-------------------+-------+---------+
| id | name          | url               | alexa | country |
+----+---------------+-------------------+-------+---------+
|  1 | Google        | www.google.com    |     1 | USA     |
|  2 | 淘宝          | www.taobao.com    |    13 | CN      |
|  3 | 菜鸟教程      | www.runoob.com    |  4689 | CN      |
|  4 | 微博          | weibo.com         |    20 | CN      |
|  5 | Facebook      | www.facebook.com  |     3 | USA     |
|  7 | stackoverflow | stackoverflow.com |     0 | IND     |
+----+---------------+-------------------+-------+---------+
6 rows in set (0.001 sec)

MariaDB [RUNOOB]> INSERT INTO apps
    -> (id, app_name, url, country)
    -> VALUES
    -> (1, 'QQ APP', 'im.qq.com', 'CN');
Query OK, 1 row affected (0.003 sec)

MariaDB [RUNOOB]> INSERT INTO apps
    -> (id, app_name, url, country)
    -> VALUES
    -> (2, '微博 APP', 'weibo.com', 'CN');
Query OK, 1 row affected (0.003 sec)

MariaDB [RUNOOB]> INSERT INTO apps
    -> (id, app_name, url, country)
    -> VALUES
    -> (3, '淘宝 APP', 'www.taobao.com', 'CN');
Query OK, 1 row affected (0.003 sec)

MariaDB [RUNOOB]> SELECT * FROM apps;
+----+------------+----------------+---------+
| id | app_name   | url            | country |
+----+------------+----------------+---------+
|  1 | QQ APP     | im.qq.com      | CN      |
|  2 | 微博 APP   | weibo.com      | CN      |
|  3 | 淘宝 APP   | www.taobao.com | CN      |
+----+------------+----------------+---------+
3 rows in set (0.001 sec)

问：INSERT语句一次只能插入一条吗？我记得应该有插入大量数据的方法吧

哦，是我犯蠢了，每条语句后添加逗号即可，尝试一下

MariaDB [RUNOOB]> DELETE FROM apps;
Query OK, 3 rows affected (0.004 sec)

MariaDB [RUNOOB]> SELECT * FROM apps;
Empty set (0.001 sec)

MariaDB [RUNOOB]> INSERT INTO apps
    -> (id, app_name, url, country)
    -> VALUES
    -> (1, 'QQ APP', 'im.qq.com', 'CN'),
    -> (2, '微博 APP', 'weibo.com', 'CN'),
    -> (3, '淘宝 APP', 'www.taobao.com', 'CN');
Query OK, 3 rows affected (0.003 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [RUNOOB]> SELECT * FROM apps;
+----+------------+----------------+---------+
| id | app_name   | url            | country |
+----+------------+----------------+---------+
|  1 | QQ APP     | im.qq.com      | CN      |
|  2 | 微博 APP   | weibo.com      | CN      |
|  3 | 淘宝 APP   | www.taobao.com | CN      |
+----+------------+----------------+---------+
3 rows in set (0.001 sec)

试一试union吧

从 “Websites” 和 “apps” 表中选取所有不同的country（只有不同的值）：

MariaDB [RUNOOB]> SELECT country FROM Websites
    -> UNION
    -> SELECT country FROM apps
    -> ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| IND     |
| USA     |
+---------+
3 rows in set (0.001 sec)

使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的country（有重复的值）：

MariaDB [RUNOOB]> SELECT country FROM Websites
    -> UNION ALL
    -> SELECT country FROM apps
    -> ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| IND     |
| USA     |
| USA     |
+---------+
9 rows in set (0.001 sec)

ORDER BY干嘛用的？去掉好像也没影响？

貌似是排序语句，接下来再说吧。

markdown太长了也比较卡了，分上中下记录吧，此处暂时作为上篇的完结。