SQL- DML-数据操作语言-增删改

# DML 数据操作语言，对数据库中表的数据记录进行增改删除

# 一、添加：

# 给指定字段添加 
insert into 表名(字段1,字段2...)values(值1,值2...);
# 给全部字段添加数据 
insert into 表名 values(值1,值2...);
# 批量添加数据 
insert into 表名(字段1,字段2...)values(值1,值2...),(值1,值2...),(值1,值2...);
insert into 表名 values(值1,值2...),(值1,值2...),(值1,值2...);
# 字符串和日期型数据应该包含在引号中



# 二、修改

update 表名 set 字段1=值1, 字段2=值2, ...[where 条件];
# 没有条件修改整张表


# 三、删除
delete from 表名 [where  条件];
# delete 语句不能删除某一个字段的值(可以用update)
# 没有条件会删除整个表的数据


示例：
SHOW TABLES;
desc employee;
select * from employee; # 显示这张表
insert into employee (id, workno, name, gender, age, idcard, entrydate)values(1,'66','人2','女',19,'123456789123456789','2019-09-10');
insert into  employee values(2,'67','人1','男',19,'123456789123456789','2019-09-10');
insert into  employee values(3,'67','人3','男',19,'123456789123456789','2019-09-10'),(4,'67','人4','男',19,'123456789123456789','2019-09-10');
update  employee set id = '3' where name='人2'; # 修改一行数据
update  employee set age =20;
delete from employee where id=1;






# 案例20240912
create table  salgrade(
    grade int,
    losal int,
    hisal int
)comment '薪资等级表';
insert into salgrade values(1,0,3000),(2,3001,5000),(3,5001,10000),(4,10001,15000),(5,15001,20000),(6,20001,25000),(7,25001,30000);

# 隐形内连接
select u.name,u.age,d.name from user u,dept d where u.dept_id = d.id;# 但是没有部门id的员工不显示
# 显示内连接
select u.name,u.age,d.name from user u inner join dept d  on  u.dept_id = d.id where u.age < 30;
# 交集(内连接)查询有员工的部门
select distinct d.id , d.name from user u,dept d where u.dept_id = d.id;
# 大于20 ,没有部门也要显示
select u.name,d.name from user u left join dept d  on  u.dept_id = d.id where u.age > 20;
# 查询全部员工工资等级
select distinct s.grade from user u left join salgrade s  on  u.salary >= s.losal and u.salary<=s.hisal;
select distinct s.grade from user u , salgrade s  where  u.salary between s.losal and s.hisal;
# 查询某部门的员工的信息及薪资等级
select u.*,s.grade from user u
    left join dept d  on  u.dept_id = d.id
    left join salgrade s on  u.salary between s.losal and s.hisal
    where d.name = '业务部';
select u.*,s.grade from user u, dept d, salgrade s
                   where u.dept_id = d.id and (u.salary between s.losal and s.hisal) and d.name = '业务部';
# 某部门平均工资
select avg(u.salary) from user u,dept d where u.dept_id = d.id  and d.name = '业务部';
# 工资比某人Tom4高的员工信息
select * from user where salary > (select salary from user where name = 'Tom4');
# 比平均工资高的
select * from user where salary > (select avg(salary) from user );
# 低于部门平均工资
select u2.* from user u2 where u2.salary < (select avg(u1.salary) from user u1 where u1.dept_id = u2.dept_id);
# 全部部门信息及部门员工数 子查询
select  d.*, (select count(*) from user u where u.dept_id = d.id ) '人数' from dept d ;
# 全部学生的选课情况
select s.name '姓名', s.no '学号',c.name '所选课程'
    from student s,course c ,student_score s1
    where s.id = s1.studentid and c.id = s1.courseid;