数据库增删改查复习

增删改查

插入数据

#插入一行 insert into student values(xwz,'男'，18); #插入多行数据 insert into student values(xwz,'男'，18)，(jesse,'女',12); #插入行的一部分 insert into studnet(name,gender,age) values(xwz,'男',18) #插入查询出来的数据 insert into student(name) select name from account;

更新数据

update student set username = 'jesse' and passward = '123' where username = 'xwz';

删除数据

#delete 删除表中记录 delete from student where name = 'jesse'; #truncate 清空表数据 truncate table studnet;

查询数据

#查询单列 select username from student; #查询多列 select username,password from student; #查询所有列 select * from studnet; #查询不同值 select distinct username from student; #限制查询条件 select username from student limit 5; select username from student limit 0,5; select username from studnet limit 5,10;

排序

select * from student order by math_score desc, chinese_score asc;

分组

#普通分组 select name,count(age) as ages from student group by name #分组后排序 select name,count(age) as ages from studnet group by name order by age desc; #使用 where 和 having 过滤 select name,count(age) as ages from student where gender is not null group by name having count(*)>=1;

子查询

#子查询用于 where 子句的子查询 select name from emp where class_name operator (select class_name from class); #用于 from 语句的子查询 select t1.name,t2.name from emp t1,(select * from class) t2 where t1.dept_id = t2.id; #子查询的子查询 select * from student where id in (select id from studnet2 where student.id in (select id from studnet3));

where

#select 中 where select * from student where name = 'jesse'; #update 中 where update student set name = 'jesse' where name = 'xwz'; #delete 中 where delete from student where name = 'jesse';

in 和 between

select * from student where age in (11,12,13); select * from student where age between 11 and 13;

and 和 or 和 not

select * from student where name = 'jesse' and age = '20'; select * from student where name = 'jesse' or age = '20'; select * from studnet where name = 'jesse' not between 11 and 13;

like

select * from student where name like 'j%'; select * from student where name like '_e%'; select * from student where name like '%s%';

连接

select emp.name,dept.name from emp join dept on emp.dept_id = dept.id; #隐式内连接 select * from emp,dept where emp.dept_id = emp.id; #显示内连接 select * from emp inner join dept on emp.dept_id = dept.id;

组合

select * from student1 union all select * from student2;