SQL语句（二）

SQL 语句

1.sql的where各种条件

select * from rxdata where name = wjw; //查询name为wjw的数据
select * from rxdata where name = wjw and age = 24;//查询name为wjw并且age为24的数据
select * from rxdata where name = wjw or age = 24;//查询name为wjw或age为24的数据
select*  from rxdata where name like '%j%'; //查询name存在j的数据w
select*  from rxdata where name like 'w%'; //查询name存在开头为w的数据
select*  from rxdata where name like '%'w; //查询name存在结尾为w的数据
select*  from rxdata where name like '_j%'; //查询name存在第二个为j的数据
select*  from rxdata where name not like 'w%'; //查询name存在开头不为w的数据

2.union合并

先创建a，b表，并插入数据

create table a(id int, aname varchar(200),age int );
create table b(id int, bname varchar(200),address varchar(200));

union 和union all

select id,aname from a  union select id,bname from b;

select id,aname from a  union all select id,bname from b;

3.join语句

左连接 left join

select a.*,b.* from a left join b on a.id=b.id;

id	aname	age	id	bname	address
1	wjw1	24	1	wjw1	hf
3	wjw3	26	3	wjw3	bj
2	wjw2	25	null	null	null

右连接 right join

select a.*,b.* from a right join b on a.id=b.id;

id	aname	age	id	bname	address
1	wjw1	24	1	wjw1	hf
3	wjw3	26	3	wjw3	bj
null	null	null	5	wjw5	sh

内连接 inner join

select a.*,b.* from a inner join b on a.id=b.id;

id	aname	age	id	bname	address
1	wjw1	24	1	wjw1	hf
3	wjw3	26	3	wjw3	bj

4.统计语句

select * from a ；

id	aname	age
1	wjw1	24
2	wjw2	25
3	wjw3	26
4	wjw1	23
5	wjw2	22

//sum求和
select sum(age) as agesum from a;  #数值求和   

agesum
120

//count统计数据条数
select count(*)  from a;  #数值求和 求条数  1+1+1=3

count(*)
5

注意点
a.count(*)少用 count(id) count(0)
b.算age数据多少 sum(age)
c.count(distinct id) 去重

#求各个名称下的sum（age）
select aname,sum(age) from  a  group by aname;

aname	sum(age)
wjw1	47
wjw2	47
wjw3	26

#求age>22的各个名称的年龄和

select t.aname,sum(t.age) from (select * from a where age > 22) as t group by t.aname; //子查询

select aname,sum(age) from a where age>22 group by aname;

aname	sum(age)
wjw1	47
wjw2	25
wjw3	26