- MariaDB数据库操作
- MariaDB表操作
- MariaDB表结构修改
- MariaDB增删改查操作
- MariaDB子句
- MariaDB聚合函数
- MariaDB连接
- MariaDB操作符
- MariaDB正则与条件
- MariaDB约束
- MariaDB导出与导入
- MariaDB权限与权限管理
比较操作符
在WHERE
子句中使用比较运算符来确定要选择的记录。以下是可以在MariaDB中使用的比较运算符的列表:
编号 | 比较运算符 | 描述 | 示例 |
---|---|---|---|
1 | = | 比较等于 | select * from students where id=100 |
2 | <=> | 比较等于(安全比较NULL 值) | select * from students where student_name<=>'Maxsu' |
3 | <> | 比较不等于 | select * from students where student_name<>'Maxsu' |
4 | != | 比较不等于 | select * from students where student_name!='Maxsu' |
5 | > | 比较大于 | select * from students where student_id>5 |
6 | >= | 比较大于或等于 | select * from students where student_id>=5 |
7 | < | 比较小于 | select * from students where student_id<5 |
8 | <= | 比较小于或等于 | select * from students where student_id<=5 |
9 | in ( ) | 匹配列表中的值 | select * from students where student_id IN(1,3,6) |
10 | not | 否定一个条件 | select * from students where student_id NOT IN(1,3,6) |
11 | between | 匹配在一个范围内(含) | select * from students where student_id between 1 AND 3) |
12 | is null | 判断是否为NULL 值 | select * from students where student_address IS NULL |
13 | is not null | 判断是否为非NULL 值 | select * from students where student_address IS NOT NULL |
14 | like | 与% 和_ 模式匹配 | select * from students where student_name LIKE 'Ma%' |
15 | exists | 如果子查询返回至少一行,则满足条件。 | — |
UNION
运算符
在MariaDB数据库中,UNION
运算符用于组合两个或更多SELECT
语句的结果集。它删除各种SELECT
语句之间的重复行。
语法
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
SQL
注意:
UNION
运算符中的每个SELECT
语句在具有相似数据类型的结果集中必须具有相同数量的字段。
1. 使用UNION操作符返回单个字段
下面来看一个从多个SELECT
语句返回一个字段的UNION
运算符的例子。(两个表具有相同的公共字段)。
假设有两张表:students
和teachers
。对应的表结构和数据如下 -
students
表中的数据:
MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
| 1 | Maxsu | Haikou | 2017-01-07 |
| 2 | JMaster | Beijing | 2016-05-07 |
| 3 | Mahesh | Guangzhou | 2016-06-07 |
| 4 | Kobe | Shanghai | 2016-02-07 |
| 5 | Blaba | Shenzhen | 2016-08-07 |
+------------+--------------+-----------------+----------------+
5 rows in set (0.00 sec)
SQL
teachers
表中的数据:
USE testdb;
CREATE TABLE teachers(
teacher_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
address VARCHAR(40) NOT NULL,
admission_date DATE,
PRIMARY KEY ( teacher_id )
);
-- 插入数据
INSERT INTO teachers
(teacher_id, name, address, admission_date)
VALUES(1,'陈小祥','上海','2013-06-07 00:00:00');
INSERT INTO teachers
(teacher_id, name, address, admission_date)
VALUES(2,'张天经','广州','2013-08-08 00:00:00');
INSERT INTO teachers
(teacher_id, name, address, admission_date)
VALUES(3,'李四光','三亚','2014-09-07 00:00:00');
SQL
经过上创建和插入数据,现在teachers
表中拥有以下数据记录 -
MariaDB [testdb]> select * from teachers;
+------------+--------+---------+----------------+
| teacher_id | name | address | admission_date |
+------------+--------+---------+----------------+
| 1 | 陈小祥 | 上海 | 2013-06-07 |
| 2 | 张天经 | 广州 | 2013-08-08 |
| 3 | 李四光 | 三亚 | 2014-09-07 |
+------------+--------+---------+----------------+
3 rows in set (0.08 sec)
SQL
假设现在要查询所有学生和老师的姓名,以及他们的地址,可参考以下查询语句 -
SELECT student_name AS name, student_address as address
FROM Students
UNION
SELECT name,address
FROM teachers;
执行上面查询语句,得到以下结果 -
MariaDB [testdb]> SELECT student_name AS name, student_address as address
-> FROM Students
-> UNION
-> SELECT name,address
-> FROM teachers;
+---------+-----------+
| name | address |
+---------+-----------+
| Maxsu | Haikou |
| JMaster | Beijing |
| Mahesh | Guangzhou |
| Kobe | Shanghai |
| Blaba | Shenzhen |
| 陈小祥 | 上海 |
| 张天经 | 广州 |
| 李四光 | 三亚 |
+---------+-----------+
8 rows in set (0.05 sec)
Shell
它显示了两个表中并删除重复的name
列的值。
2. 具有ORDER BY子句的UNION运算符
使用ORDER BY
子句的UNION
运算符从两个表中检索多个列。参考以下语句 -
SELECT student_id, student_name
FROM Students
WHERE student_name = 'Maxsu'
UNION
SELECT teacher_id, address
FROM teachers
WHERE teacher_id > 1
ORDER BY 1;
执行上面示例代码,得到以下结果 -
MariaDB [testdb]> SELECT student_id, student_name
-> FROM Students
-> WHERE student_name = 'Maxsu'
-> UNION
-> SELECT teacher_id, address
-> FROM teachers
-> WHERE teacher_id > 1
-> ORDER BY 1;
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1 | Maxsu |
| 2 | 广州 |
| 3 | 三亚 |
+------------+--------------+
3 rows in set (0.00 sec)
Shell
3. 一些其它的用法
具有不同列名的表之间的UNION:
(SELECT e_name AS name, email FROM employees)
UNION
(SELECT c_name AS name, email FROM customers);
指定UNION的全局顺序并限制总行数:
(SELECT name, email FROM employees)
UNION
(SELECT name, email FROM customers)
ORDER BY name LIMIT 10;
添加一个常数行:
(SELECT 'John Doe' AS name, 'john.doe@example.net' AS email)
UNION
(SELECT name, email FROM customers);
按照每个SELECT使用排序列的顺序返回结果:
(SELECT 1 AS sort_column, e_name AS name, email FROM employees)
UNION
(SELECT 2, c_name AS name, email FROM customers) ORDER BY sort_column;
UNION ALL
运算符
MariaDB UNION ALL
操作符与UNION
操作符相同,但不会删除重复的记录。它返回查询中的所有行,并且不删除各种SELECT
语句之间的重复行。
语法
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION ALL
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
SQL
注意:
UNION All
运算符中的每个SELECT
语句在具有相似数据类型的结果集中必须具有相同数量的字段。
1. 使用UNION ALL操作符返回单个字段
下面来看一个从多个SELECT
语句返回一个字段的UNION
运算符的例子。(两个表具有相同的公共字段)。
假设有两张表:students
和teachers
。对应的表结构和数据如下 -
students
表中的数据:
MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
| 1 | Maxsu | Haikou | 2017-01-07 |
| 2 | JMaster | Beijing | 2016-05-07 |
| 3 | Mahesh | Guangzhou | 2016-06-07 |
| 4 | Kobe | Shanghai | 2016-02-07 |
| 5 | Blaba | Shenzhen | 2016-08-07 |
+------------+--------------+-----------------+----------------+
5 rows in set (0.00 sec)
SQL
teachers
表中的数据:
USE testdb;
DROP TABLE teachers;
CREATE TABLE teachers(
teacher_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
address VARCHAR(40) NOT NULL,
admission_date DATE,
PRIMARY KEY ( teacher_id )
);
-- 插入数据
INSERT INTO teachers
(teacher_id, name, address, admission_date)
VALUES(1,'Maxsu','Haikou','2013-06-07 00:00:00');
INSERT INTO teachers
(teacher_id, name, address, admission_date)
VALUES(2,'张天经','广州','2013-08-08 00:00:00');
INSERT INTO teachers
(teacher_id, name, address, admission_date)
VALUES(3,'李四光','三亚','2014-09-07 00:00:00');
经过上创建和插入数据,现在teachers
表中拥有以下数据记录 -
MariaDB [testdb]> select * from teachers;
+------------+--------+---------+----------------+
| teacher_id | name | address | admission_date |
+------------+--------+---------+----------------+
| 1 | Maxsu | Haikou | 2013-06-07 |
| 2 | 张天经 | 广州 | 2013-08-08 |
| 3 | 李四光 | 三亚 | 2014-09-07 |
+------------+--------+---------+----------------+
3 rows in set (0.00 sec)
假设现在要查询所有学生和老师的姓名,以及他们的地址,可参考以下查询语句 -
SELECT student_name AS name, student_address as address
FROM Students
UNION ALL
SELECT name,address
FROM teachers;
执行上面查询语句,得到以下结果 -
MariaDB [testdb]> SELECT student_name AS name, student_address as address
-> FROM Students
-> UNION ALL
-> SELECT name,address
-> FROM teachers;
+---------+-----------+
| name | address |
+---------+-----------+
| Maxsu | Haikou |
| JMaster | Beijing |
| Mahesh | Guangzhou |
| Kobe | Shanghai |
| Blaba | Shenzhen |
| Maxsu | Haikou |
| 张天经 | 广州 |
| 李四光 | 三亚 |
+---------+-----------+
8 rows in set (0.00 sec)
Shell
注:如果这里不使用
UNION ALL
,而是使用UNION
,那么查询结果是什么?
它显示了两个表中重复的name
列的值 - 'Maxsu'
。
2. 具有ORDER BY子句的UNION运算符
使用ORDER BY
子句的UNION
运算符从两个表中检索多个列。参考以下语句 -
SELECT student_id, student_name
FROM Students
WHERE student_name = 'Maxsu'
UNION ALL
SELECT teacher_id, address
FROM teachers
WHERE teacher_id < 3
ORDER BY 1;
执行上面示例代码,得到以下结果 -
MariaDB [testdb]> SELECT student_id, student_name
-> FROM Students
-> WHERE student_name = 'Maxsu'
-> UNION ALL
-> SELECT teacher_id, address
-> FROM teachers
-> WHERE teacher_id < 3
-> ORDER BY 1;
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1 | Haikou |
| 1 | Maxsu |
| 2 | 广州 |
+------------+--------------+
3 rows in set (0.00 sec)
Shell
3. 一些其它的用法
具有不同列名的表之间的UNION:
(SELECT e_name AS name, email FROM employees)
UNION ALL
(SELECT c_name AS name, email FROM customers);
SQL
指定UNION的全局顺序并限制总行数:
(SELECT name, email FROM employees)
UNION ALL
(SELECT name, email FROM customers)
ORDER BY name LIMIT 10;
添加一个常数行:
(SELECT 'John Doe' AS name, 'john.doe@example.net' AS email)
UNION ALL
(SELECT name, email FROM customers);
按照每个SELECT使用排序列的顺序返回结果:
(SELECT 1 AS sort_column, e_name AS name, email FROM employees)
UNION ALL
(SELECT 2, c_name AS name, email FROM customers) ORDER BY sort_column;