JavaWeb_09_单表操作

《单表操作》

目录

• DML操作（精通）

• 单表查询（熟练）

一、DML操作

-- 1.数据插入
insert into teacher values
(101,'A老师',25,'19938201920','人美声甜'),
(102,'B老师',30,'19938204430','帅气逼人'),
(103,'C老师',60,'19025103396','和蔼可亲');

-- 2.指定列插入
insert into teacher(teacher_name,age) values
('D老师',35),
('E老师',26);

-- 3.查询表数据
select * from teacher;

-- 4.指定列查询
select teacher_id,teacher_name from teacher;

-- 5.修改表数据
update teacher set age=58,phone_number='19122223333' where teacher_id=103;

-- 6.删除表数据（不加where条件将全部删除）
delete from teacher where teacher_id=101;

-- 7.重置表（删除所有数据并重置自动递增值）
truncate teacher;

二、单表查询

-- 1.别名查询（针对数据库对象，as可省略）
select teacher_id as tid,teacher_name tname,phone_number `电话` from teacher;

-- 2.条件查询(=,!=,<,>,<=,>=)

select * from where age!=25;
select * from where age>25;
select * from where age<40;

-- 3.联合条件查询(and,or)
select * from teacher where age>25 and teacher_id<104;
 age=25 or age=26;

-- 4.范围查询（针对数字字段）
select * from teacher where age between 30 and 40;

-- 5.集合查询
select * from teacher where age in(25,26,27,28);

-- 6.空值查询
select * from teacher where phone_number is null;
select * from teacher where phone_number is not null;

-- 7.模糊查询（%表示匹配0个以上字符，_表示匹配一个字符）
select * from teacher where phone_number like '199%';
select * from teacher where info like '%人%';

-- 8.排序查询（asc升序，desc降序）
select * from teacher order by teacher_id desc;

-- 9.限定查询[x,y)
select * from teacher limit 3,5;

总结

重点

1. DML增删改查操作；
2. 单表查询。

难点

1. 条件查询。