数据准备:
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
外连接:
- 左外连接:关键字为 left join or left outer join ,特点为左表中有数据与之关联的右表中无数据则左表中查询出的结果为空
右连接:关键字为right join or right outer join 特点为右表中有数据,查询出的与之关联的左表中的数据没有数据则为不出现相关结果
全外连接:关键字为full join,full outer join完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。(mysql不支持这个但是orcal支持这个)
内连接:如果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
子查询:
- 标量子查询:返回单一值的标量,最简单的形式。
- 列子查询:返回的结果集是 N 行一列。
- 行子查询:返回的结果集是一行 N 列。
表子查询:返回的结果集是 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.csdn.net/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)