mysql查询语法

发表于 | 分类于
字数统计: 1.4k | 阅读时长 ≈ 7

创建三个表并添加数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
--部门表
dept部门表(deptno部门编号/dname部门名称/loc地点)
create table dept (
    deptno numeric(2),
    dname varchar(14),
    loc varchar(13)
);

insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');

--工资等级表
salgrade工资等级表(grade 等级/losal此等级的最低/hisal此等级的最高)
create table salgrade (
    grade numeric,
    losal numeric,
    hisal numeric
);

insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);


--员工表
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
工资 ＝ 薪金 ＋ 佣金

1.表自己跟自己连接

create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);



insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

聚合函数：

1
sum() max() min() count()

分组函数：

1
group by  ....  having ....

列题：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#1.求员工表 所有人的薪水和      as salsum 别名
select
sum(sal) as salsum
from emp;

#2.求每个部门的所有人的薪水和
select
deptno,
sum(sal) as salsum
from emp
group by deptno;

#总结: group by 后面出现多少个字段，那么select 后面也要一模一样
#每/各 是为分组字段      

#3.求每个部门的每个岗位的所有人的薪水和
select
deptno,job,
sum(sal) as salsum
from emp
group by deptno,job;



#4.求每个部门的每个岗位的所有人的薪水和，及人数
select
deptno,job,
sum(sal) as salsum,
count(deptno) as pnum
from emp
group by deptno,job;

group by deptno --》3
group by deptno , job 
#5.求薪水和大于1500的哪些部门  ==》 每个部门的薪水和 ==》 哪些薪水和大于1500的部门
select
deptno,
sum(sal) as salsum
from emp
group by deptno 
having sum(sal)>1500;

#子查询
select 
t.* 
from 
(select
deptno,
sum(sal) as salsum
from emp
group by deptno) as t
where t.salsum > 1500;

关联 left join,right join,inner join

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
重新创建表：
drop table testa;
create table testa(aid int,aname varchar(100),address varchar(100));
create table testb(bid int,bname varchar(100),age int);
create table testsal(cid int,sal int);

delete from testsal;
insert into testsal values(1,100);
insert into testsal values(2,300);
insert into testsal values(4,700);
insert into testsal values(8,1300);
insert into testsal values(9,3300);

select * from testsal;


insert into testa values(1,'xiao1','SH1');
insert into testa values(2,'xiao2','SH1');
insert into testa values(3,'xiao3',null);
insert into testa values(4,'xiao4','SH2');
insert into testa values(5,'xiao5','SH2');


insert into testb values(1,'xiao1',10);
insert into testb values(2,'xiao2',20);
insert into testb values(3,'xiao3',30);
insert into testb values(4,'xiao4',40);
insert into testb values(4,'xiao44',440);

insert into testb values(7,'xiao7',70);
insert into testb values(8,'xiao8',80);
insert into testb values(9,'xiao9',90);

## left join：

	 select  a.*,b.*from testa a  left join testb b on a.aid=b.bid      a <-- b
	 #以左表为主，左表数据最全，右表来匹配的，匹配多少算多少
	 aid	aname	bid	bname	age
	 1	xiao1	1	xiao1	10
	 2	xiao2	2	xiao2	20
	 3	xiao3	3	xiao3	30
	 4	xiao4	4	xiao4	40
	 5	xiao5	null null   null	
	 

## right join：
	select a.*,b.*from testa a right join testb b on a.aid=b.bid
	#以右表为主，右表数据最全，左表来匹配的，匹配多少算多少     a --> b
	 1	xiao1	1	xiao1	10
	 2	xiao2	2	xiao2	20
	 3	xiao3	3	xiao3	30
	 4	xiao4	4	xiao4	40
				7	xiao7	70
				8	xiao8	80
				9	xiao9	90


## inner join
	 select a.*,b.*from testa a inner join testb b on a.aid=b.bid
	 #等值连接  两边都要存在
	 1	xiao1	1	xiao1	10
	 2	xiao2	2	xiao2	20
	 3	xiao3	3	xiao3	30
	 4	xiao4	4	xiao4	40


## 笛卡尔集
	 select a.*,b.*from testa a, testb b;


## 等值连接
	 select a.*,b.*from testa a, testb b where a.aid = b.bid;

jion相关联的题：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#哪些人   有薪水的有年龄的有地址
select  s.*,b.*,a.*
from testsal s
left join testb b on s.cid =b.bid
left join testa a on s.cid =a.aid
where 
s.sal is not null 
and b.age is not null 
and a.address is not null;



select 
s.*,b.*,a.*
from testsal s
left join testb b on s.cid =b.bid
left join testa a on b.bid =a.aid
where 
s.sal is not null 
and b.age is not null 
and a.address is not null;


select 
s.*,b.*,a.*
from testsal s
left join testb b on s.cid =b.bid
right join testa a on b.bid =a.aid;

#哪些人   有薪水的   有年龄的有地址
select 
s.*,t.*
from testsal s
left join 
(select 
b.*,a.*
from testb b 
right join testa a on b.bid =a.aid) t on s.cid = t.aid
where 
s.sal is not null 
and t.age is not null 
and t.address is not null;


select 
s.*,t.*
from testsal s
inner join 
(select 
b.*,a.*
from testb b 
inner join testa a on b.bid =a.aid) t on s.cid = t.aid;

本文标题:mysql查询语法

文章作者:skygzx

发布时间:2019年04月05日 - 19:37

最后更新:2019年04月05日 - 19:41

原始链接:http://yoursite.com/2019/04/05/mysql中的查询语法/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

-------------本文结束感谢您的阅读-------------
0%