[大数据] mysql

SELECT * FROM st_211208.person;
use st_211208;
insert into person(id,code,name,age,gender,birthday)
values(1,'1001','zhangsan',20,'男','2000-10-01');
insert into person
values(1,'1001','zhangsan',20,'男','2000-10-01');

insert into person(code,name)
values('1005','dayingxiong');

-- 删除

delete from person;-- 表中全部数据
delete from person where id=2;-- id=2

-- 区别自增id会被重新初始化
truncate table person;

update person set gender = '女',age=22 where id=1;

SELECT * FROM st_211208.person;
select id,code,name,age,gender from person;

-- 别名

select id,code,name '姓名',age '年龄',gender as '性别' from person;
select id,code,name '姓名',age '年龄',gender as '性别' from person p;

-- 自定义列（常量列）

select id,code ,name, 1 ,'Java'as'班级'from person;
select id,code,name,age,(id+age)from person;

-- distinct去掉重复数据?

select distinct code from person;
select code from person; ?-- all

-- where 查询条件

select id,name,code from person where name = 'zhangsan';
select id,name,code from person where name = 'zhangsan' and id = 1;-- 与
select id,name,code from person where name = 'zhangsan' or id = 1;-- 或

select id,name,code from?
person where geder = '女'
and (age<10 or age>30)
and id>0;

-- 比较 < <= > >= <> !=

select id,name,code from person where id <> 2;
select id,name,code from person where id != 2;
select id,name,code from person where age >=20 and age <=30;
select id,name,code from person where age between 20 and 30;

-- 判空（null/空字符串）

select id,name,code from person where birthday ='';
select id,name,code from person where birthday is null;
select id,name,code from person where birthday is not null;

-- 模糊查询 like

-- 通配符 %（n>=0） ?_(1)

select id,name,code from person where name like 'zhang%'; -- 配用通配符就相当于=

-- 转义 ?\

select id,name,code from person where name like '%\%%';

-- 排序升序asc、降序dese

select id,name,code from person where id > 0
order by id desc;-- asc默认

-- 多个列的排序-从左往右的顺序，以此排序，前一个值相同，则进行下一个列的排序
select id,name,code from person where id > 0
order by code,id;

-- 聚合函数

select sum(age) from person;
select max(age) from person;
select min(age) from person;
select avg(age) from person;

-- 个数(不包含null的数据个数)

select count(id) from person; --?
select count(*) from person; -- 所有的列
select count(1) from person; --?
select count(birthday) from person;

-- where查询条件--分组前查询
-- group by 分组查询
-- having 分组（筛选）条件 -- 分组后筛选
select gender ,count(age)
from person
where id >3
group by gender
having count(gender)>2;

-- limit（分页查询）

-- 偏移+限制查询数量
select * from person limit 3;
select * from person limit 1,3;
-- select * from person limit 0,10; --(N-1)*k,k
-- select * from person limit 10,10;
-- select * from person limit 20,10;

-- 查询“软件开发部”部门中的人员信息

-- 子查询：嵌套查询

-- 子查询-从一张表中查询数据，给另一张表提供使用
select * from person p
where code_dept =?
(
SELECT code FRom depatment d
where name = '软件开发部'
);

select * from person p
where code_dept in?
(A001,A002);

-- 子查询--

select name from depatment where code in
(
select code_dept from person where name = 'dayingxiong'
);

-- 子查询：作为查询表（必须起别名）

select p2.id,p2.code
from(select p1.id,p1.name,p1.code from person p1)p2
where p2.id>3;

-- 子查询：作为查询字段(子查询的结果必须是一条记录)

select p.name,p.code_dept,
?? ?(select d.name from depatment d where d.code = p.code_dept)name_dept
from preson p
where p.name='dayingxiong'or p.name='daxingxing';

-- any只要有一个数据满足条件，整个条件成立
select * from person where age >any
(select p.age from person p where?
p.name ='daxingxing' or p.name = 'dayingxiong');
-- all对所有的数据都满足条件，整个条件成立
select * from person where age >all
(select p.age from person p where?
p.name ='daxingxing' or p.name = 'dayingxiong');

-- exists

select * from person
where exists(
?? ?select id from depatment where name = '软件开发部'
);

select * from depatment d?
where exists
(select p.id from perso
?p where p.code_dept = d.code);

-- join 关联查询

select p.code_dept
from person p
left join depatment d
on p.code_dept = d.code;
-- 内连接 --[inner] join - 左右两表有关联的才能查询出来
-- 外连接
-- 左(外)链接 -- left [outer] join- 左表中都能查询到，右表关联成功的能查到
-- 右(外)链接 -- right [outer] join- 右表中都能查询到，左表关联成功的能查到
-- 全外联 -- full [outer] join
-- 交叉连接 -- cross join?

-- 全连接 -- 笛卡尔积（效率最慢）
select *
from person p,depatment d
where p.code_dept = d.code;