接下来。装逼开始....
mysql> select * from test_user;
+----+--------+-------------+----------+-----------+
| id | name | mobile | address | country |
+----+--------+-------------+----------+-----------+
| 1 | JAVA | 18847474541 | Beijing | China |
| 2 | Python | 17748484242 | Nanjing | Singapore |
| 3 | Php | 17748484246 | Beijing | China |
| 4 | C# | 17748484141 | Shanghai | Singapore |
| 5 | Go | 18847474142 | Shanghai | China |
| 6 | Swift | 17748484142 | Beijing | China |
+----+--------+-------------+----------+-----------+
6 rows in set (0.00 sec)
mysql> select * from test_user_copy;
+----+--------+-------------+----------+-----------+
| id | name | mobile | address | country |
+----+--------+-------------+----------+-----------+
| 1 | JAVA | 18847474541 | Beijing | China |
| 2 | Python | 17748484242 | Nanjing | Singapore |
| 3 | SQL | 17748484246 | Beijing | China |
| 4 | R | 17748484141 | Shanghai | Singapore |
+----+--------+-------------+----------+-----------+
4 rows in set (0.00 sec)
上述两张表,表结构是一致的,里面存储的数据有一致的,也不一致的;下面开始使用上述表的数据执行SQL中含UNION语句…
–
UNION 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION 实例:
mysql> select name, country from test_user
-> union
-> select name, country from test_user_copy
-> order by country;
+--------+-----------+
| name | country |
+--------+-----------+
| Swift | China |
| SQL | China |
| JAVA | China |
| Php | China |
| Go | China |
| R | Singapore |
| Python | Singapore |
| C# | Singapore |
+--------+-----------+
8 rows in set (0.00 sec)
–
UNION ALL 语句:用于将不同表中相同列中查询的数据展示出来;(包括重复数据)
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
UNION ALL 实例:
mysql> select name, country from test_user
-> union all
-> select name, country from test_user_copy
-> order by country;
+--------+-----------+
| name | country |
+--------+-----------+
| Swift | China |
| JAVA | China |
| SQL | China |
| JAVA | China |
| Php | China |
| Go | China |
| Python | Singapore |
| R | Singapore |
| Python | Singapore |
| C# | Singapore |
+--------+-----------+
10 rows in set (0.00 sec)
从上述的示例中可以总结到:UNION 操作符用于合并两个或多个 SELECT 语句的结果集,并把数据展示出来…
–
如果test_user_copy表比test_user表多出一列数据,那该怎么处理呢 ?
mysql> select * from test_user_copy;
+----+--------+-------------+----------+-----------+---------------------+
| id | name | mobile | address | country | school |
+----+--------+-------------+----------+-----------+---------------------+
| 1 | JAVA | 18847474541 | Beijing | China | Beijing University |
| 2 | Python | 17748484242 | Nanjing | Singapore | Nanjing University |
| 3 | SQL | 17748484246 | Beijing | China | Beijing University |
| 4 | R | 17748484141 | Shanghai | Singapore | Shanghai University |
+----+--------+-------------+----------+-----------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from test_user;
+----+--------+-------------+----------+-----------+
| id | name | mobile | address | country |
+----+--------+-------------+----------+-----------+
| 1 | JAVA | 18847474541 | Beijing | China |
| 2 | Python | 17748484242 | Nanjing | Singapore |
| 3 | Php | 17748484246 | Beijing | China |
| 4 | C# | 17748484141 | Shanghai | Singapore |
| 5 | Go | 18847474142 | Shanghai | China |
| 6 | Swift | 17748484142 | Beijing | China |
+----+--------+-------------+----------+-----------+
6 rows in set (0.00 sec)
–
python脚本执行UNION语句 :
import pymysql
class MysqlData:
def __init__(self, host="localhost", user="root", password="123456", database="testing", port=3306):
# 连接数据库
self.connection = pymysql.connect(host=host, user=user, password=password, database=database, port=port,
charset='utf8', cursorclass=pymysql.cursors.DictCursor)
def modify_data(self, sql, args=None):
"""
Args:
sql: sql语句
args: 接收格式化参数
Returns:
"""
try:
with self.connection:
self.connection.ping(reconnect=True)
with self.connection.cursor() as cursor:
# 执行sql语句
cursor.execute(sql, args=args)
# 提交sql语句
self.connection.commit()
except pymysql.err.MySQLError as _error:
raise _error
def get_data(self, sql, args=None, is_data=False):
"""
Args:
sql: sql语句
args: 接收格式化参数
is_data: 判断获取多条数据还是单个数据,默认获取单个数据
Returns:
"""
try:
with self.connection:
self.connection.ping(reconnect=True)
with self.connection.cursor() as cursor:
# 执行sql语句
cursor.execute(sql, args=args)
# 返回查询出的数据信息
return cursor.fetchall() if is_data else cursor.fetchone()
except pymysql.err.MySQLError as _error:
raise _error
if __name__ == '__main__':
# 初始化类,创建对象
info = MysqlData()
# 调用查询方法并打印出来
print(info.get_data("SELECT * FROM test_user UNION SELECT * FROM test_user_copy"))
Traceback (most recent call last):
File "F:/project_gitee/Test/pythonScripts/python_mysql.py", line 63, in <module>
print(info.get_data("SELECT * FROM test_user UNION SELECT * FROM test_user_copy"))
File "F:/project_gitee/Test/pythonScripts/python_mysql.py", line 53, in get_data
raise _error
File "F:/project_gitee/Test/pythonScripts/python_mysql.py", line 47, in get_data
cursor.execute(sql, args=args)
File "D:\Python\Python37\lib\site-packages\pymysql\cursors.py", line 170, in execute
result = self._query(query)
File "D:\Python\Python37\lib\site-packages\pymysql\cursors.py", line 328, in _query
conn.query(q)
File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
result.read()
File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 1075, in read
first_packet = self.connection._read_packet()
File "D:\Python\Python37\lib\site-packages\pymysql\connections.py", line 684, in _read_packet
packet.check_error()
File "D:\Python\Python37\lib\site-packages\pymysql\protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "D:\Python\Python37\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (1222, 'The used SELECT statements have a different number of columns')
Process finished with exit code 1
然后就报错了:The used SELECT statements have a different number of columns
翻译过来就是:所使用的SELECT语句有不同数量的列
由此得到结论是:UNION 内部的 SELECT 语句必须拥有相同数量的列,也尽可能的保证数据类型也是一致的…
当然,上面是查询两张表的全部数据,但是如果指定两张表相同的属性去查询就不会报错了,UNION中也是允许跟随WHERE子句的…
–
python脚本执行带有 WHERE 子句的 UNION ALL 语句 :
if __name__ == '__main__':
# 初始化类,创建对象
info = MysqlData()
# 调用查询方法并打印出来
results = info.get_data("""
SELECT
country, name
FROM
test_user_copy
WHERE
country = 'China'
UNION ALL
SELECT
country, name
FROM
test_user_copy
WHERE
country = 'China'
ORDER BY
country
""", is_data=True)
for i in results:
print(i)
{'country': 'China', 'name': 'JAVA'}
{'country': 'China', 'name': 'SQL'}
{'country': 'China', 'name': 'JAVA'}
{'country': 'China', 'name': 'SQL'}
Process finished with exit code 0
至此,UNION操作查询完成…
以上总结或许能帮助到你,或许帮助不到你,但还是希望能帮助到你,如有疑问、歧义,直接私信留言会及时修正发布;感觉还不错记得点赞呦,谢谢!
未完,待续…
一直都在努力,希望您也是!
微信搜索公众号:就用python