目录
SELECT * FROM st_211208.person;
use st_211208;
insert into person(id,code,name,age,gender,birthday)
values(2,'1001','zhangsan',20,'男','2000-10-01');
insert into person(id,code,name)
values(1,'1001','zhangsan');
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;