数据库查询方式

于 2018-08-05 14:56:53 发布
阅读量462 收藏 3
点赞数
CC 4.0 BY-SA版权
文章标签： 数据库查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/jiangwudidebaba/article/details/81431238
本文介绍了SQL中的多种查询技巧，包括合并查询、交叉查询、内外连接、子查询及分组查询，并提供了具体示例，同时涵盖了数据库优化的相关知识点。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

数据准备：

Create Table

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `user_tel` varchar(20) DEFAULT NULL,
  `user_account` varchar(20) DEFAULT NULL,
  `user_level` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1



Create Table

CREATE TABLE `orderhis` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_good` varchar(20) DEFAULT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  `order_is_pay` varchar(20) DEFAULT NULL,
  `order_good_num` int(11) DEFAULT NULL,
  `order_type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
合并查询
使用的关键字 union，unionall

注意事项：

 1. union查询出的结果，不会出现重复的值，union all的结果集中可以出现重复的值
 2. 使用它们必须有相同的列结构
 3.UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

例子：

SELECT * FROM USER UNION ALL SELECT* FROM USER

SELECT * FROM USER UNION  SELECT* FROM USER
交叉查询：交叉连接返回的结果，是被连接的两个表中所有数据行的笛卡尔积，也就是返回第一个表中符合查询条件的数据行数，乘以第二个表中符合查询条件的数据行数

说明：比如，Department表中有4个部门，employee表中有4个员工，那么，交叉连接的结果就有16条数据
SELECT * FROM 表1 CROSS JOIN 表2；

不带关系的表连接

SELECT user1.user_id,user1.user_level,user2.user_name FROM USER user1 ,USER user2

内连接：根据相关的表字段的关联将数据查出

SELECT * FROM USER u,orderhis o WHERE u.user_id = o.order_user_id
外连接：
  1. 左外连接：关键字为 left join or left outer join ，特点为左表中有数据与之关联的右表中无数据则左表中查询出的结果为空

  2. 右连接：关键字为right join or right outer join 特点为右表中有数据，查询出的与之关联的左表中的数据没有数据则为不出现相关结果

  3. 全外连接：关键字为full join，full outer join完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。(mysql不支持这个但是orcal支持这个)

  4. 内连接：如果2个表中都有匹配的数据那么数据将被查出

    例子：

SELECT * FROM USER u LEFT JOIN orderhis o ON u.user_id = o.order_user_id

这就是一个例子

SELECT * FROM USER u RIGHT JOIN orderhis o ON u.user_id = o.order_user_id

这里写图片描述

SELECT * FROM USER u INNER JOIN orderhis o ON u.user_id = o.order_user_id

这里写图片描述

子查询：
  1. 标量子查询：返回单一值的标量，最简单的形式。
  2. 列子查询：返回的结果集是 N 行一列。
  3. 行子查询：返回的结果集是一行 N 列。

  4. 表子查询：返回的结果集是 N 行 N 列。

    标量字查询：查询出单个结果

例子：
SELECT * FROM USER WHERE user_level >(SELECT AVG(user_level) AS avglevel FROM USER)

列子查询：

SELECT * FROM USER WHERE user_name IN (SELECT user_name FROM USER );


CREATE TABLE userlevelist(
user_level INT NOT NULL
)

INSERT userlevelist(user_level) SELECT user_level FROM USER


SELECT * FROM USER WHERE user_level>ANY(SELECT user_level FROM userlevelist)

对于列子查询的操作是对集合的操作。

行子查询：

　指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。 
SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
注：(1,2) 等同于 row(1,2)
SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)
关键字子查询

in ，any ，all，null，exists

例子：

分组查询（带条件）

条件关键字的区别：

where on having：

on在表连接的时候开始执行，where在表连接以后进行过滤，having在分组查询的时候条件里面进行过滤的
.
数据准备：

/*
SQLyog Ultimate v11.33 (64 bit)
MySQL - 5.7.22-log : Database - testsql
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`testsql` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `testsql`;

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(20) DEFAULT NULL,
  `student_subject` varchar(20) DEFAULT NULL,
  `studen_score` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

/*Data for the table `student` */

insert  into `student`(`student_id`,`student_name`,`student_subject`,`studen_score`) values (1,'zs','chinese',65),(2,'zs','math',86),(3,'ls','math',96),(4,'ls','chinese',86),(5,'ww','math',52),(6,'ww','chinese',96),(7,'ww','english',45);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

例子：

查询出每个学生如果课程大于平均成绩则被查出


提供一种发方法

CREATE TABLE avgscoresub(
subject_name VARCHAR(20) NOT NULL,
subject_avgscore INT
)

INSERT avgscoresub(subject_name,subject_avgscore) SELECT student_subject AS subject_name,AVG(student_score) AS subject_avgscore FROM student  GROUP BY student_subject


SELECT * FROM avgscoresub


SELECT * FROM (SELECT * FROM student st  LEFT JOIN  avgscoresub av ON st.student_subject = av.subject_name) AS ss WHERE ss.student_score >=ss.subject_avgscore

这里写图片描述

行列转换

行列转换可以使用2种方法：使用子查询或者是case when then end as

参考：https://blog.youkuaiyun.com/truelove12358/article/details/49453679

例子：

create table tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);

insert into tx values
(1 ,’A1’,’B1’,9),
(2 ,’A2’,’B1’,7),
(3 ,’A3’,’B1’,4),
(4 ,’A4’,’B1’,2),
(5 ,’A1’,’B2’,2),
(6 ,’A2’,’B2’,9),
(7 ,’A3’,’B2’,8),
(8 ,’A4’,’B2’,5),
(9 ,’A1’,’B3’,1),
(10 ,’A2’,’B3’,8),
(11 ,’A3’,’B3’,8),
(12 ,’A4’,’B3’,6),
(13 ,’A1’,’B4’,8),
(14 ,’A2’,’B4’,2),
(15 ,’A3’,’B4’,6),
(16 ,’A4’,’B4’,9),
(17 ,’A1’,’B4’,3),
(18 ,’A2’,’B4’,5),
(19 ,’A3’,’B4’,2),
(20 ,’A4’,’B4’,5);

mysql> select * from tx;
+—-+——+——+——+
| id | c1 | c2 | c3 |
+—-+——+——+——+
| 1 | A1 | B1 | 9 |
| 2 | A2 | B1 | 7 |
| 3 | A3 | B1 | 4 |
| 4 | A4 | B1 | 2 |
| 5 | A1 | B2 | 2 |
| 6 | A2 | B2 | 9 |
| 7 | A3 | B2 | 8 |
| 8 | A4 | B2 | 5 |
| 9 | A1 | B3 | 1 |
| 10 | A2 | B3 | 8 |
| 11 | A3 | B3 | 8 |
| 12 | A4 | B3 | 6 |
| 13 | A1 | B4 | 8 |
| 14 | A2 | B4 | 2 |
| 15 | A3 | B4 | 6 |
| 16 | A4 | B4 | 9 |
| 17 | A1 | B4 | 3 |
| 18 | A2 | B4 | 5 |
| 19 | A3 | B4 | 2 |
| 20 | A4 | B4 | 5 |
+—-+——+——+——+
20 rows in set (0.00 sec)

mysql>

期望结果

+——+—–+—–+—–+—–+——+
|C1 |B1 |B2 |B3 |B4 |Total |
+——+—–+—–+—–+—–+——+
|A1 |9 |2 |1 |11 |23 |
|A2 |7 |9 |8 |7 |31 |
|A3 |4 |8 |8 |8 |28 |
|A4 |2 |5 |6 |14 |27 |
|Total |22 |24 |23 |40 |109 |
+——+—–+—–+—–+—–+——+

SELECT c1,
    SUM(IF(c2='B1',C3,0)) AS B1,
  SUM(IF(c2='B2',C3,0)) AS B2,
     SUM(IF(c2='B3',C3,0)) AS B3,
    SUM(IF(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
    FROM tx
     GROUP BY C1
   UNION
   SELECT 'TOTAL',SUM(IF(c2='B1',C3,0)) AS B1,
   SUM(IF(c2='B2',C3,0)) AS B2,
    SUM(IF(c2='B3',C3,0)) AS B3,
   SUM(IF(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX

或者使用

SELECT   c1,

SUM(CASE  WHEN c2='B1'  THEN c3 END )AS B1,
SUM(CASE  WHEN c2='B2'  THEN c3 END )AS B2,
SUM(CASE  WHEN c2='B3'  THEN c3 END )AS B3,
SUM(CASE  WHEN c2='B4'  THEN c3 END )AS B4,
SUM(c3) AS total

FROM tx  GROUP BY c1
UNION 

SELECT 'total',SUM(CASE  WHEN c2='B1'  THEN c3 END )AS B1,
SUM(CASE  WHEN c2='B2'  THEN c3 END )AS B2,
SUM(CASE  WHEN c2='B3'  THEN c3 END )AS B3,
SUM(CASE  WHEN c2='B4'  THEN c3 END )AS B4,
SUM(c3)
 FROM  tx
去重查询
SELECT * FROM USER WHERE user_id IN(SELECT  MIN(user_id) FROM USER GROUP BY user_name ,user_tel,user_account ,user_level)
数据库优化相关
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值