mariadb 数据表名区分大小写 mariadb select

转载

mob64ca14150f43 2024-03-18 11:28:42

文章标签 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;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：容器和进程的性能对比容器线程

下一篇：kafka内存占用大吗 kafka占用内存情况

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯