数据库数据操作3

1. 唯一约束(unique)
   特点: 值唯一 可以为空
   create table stu5(sid int primary key,sname varchar(20) unique);
   自动增长列
   自动加1 并且出现过的 就不会在出现了
   create table stu6(sid int primary key auto_increment,sname varchar(20) unique);
   insert into stu6 (sname) values (‘haha’);
   insert into stu6 (sname,sid) values (‘ha’,null);
   域完整性
   作用:限制单元格内的数据的完整性
   create table stu7(sid int primary key auto_increment,saname varchar(20) not null);
2. 引用完整性(参照物约束)
   外键约束：FOREIGN KEY
   主表和从表有依赖关系 从表依赖主表 这时可以给从表添加一个约束 外键约束
   create table student1 (sid int primary key,sname varchar(20));
   create table score1 (
   sid int,
   score int,
   constraint fk_stu_score_sid FOREIGN KEY (sid) REFERENCES student1(sid)
   );
3. 给score表添加外键
   alter table score1 add constraint fk_student_score_sid1 foreign key (sid)
   references student1 (sid);
   删除时要使用约束的别名来删除
   一个表可以有多个外键 需要使用约束的名字
   注意:约束的名字不能重复
   alter table score drop foreign key fk_student_score_sid;
   表和表之间的关系
   多对多 利用第三章表来表示关系的
   并且第三张表作为从表 作为其他两个主表的外键
   创建学生表 老师表 中间表 并建立关系
   create table student(
   sid int primary key,
   sname varchar(20)
   );
   create table teacher(
   tid int primary key,
   sname varchar(20)
   );
   create table teacher_student (
   sid int,
   tid int
   );
   constraint 约束
   references参考
   alter table teacher_student add constraint fk_teacher_tid foreign key (tid) references teacher (tid);
   alter table teacher_student add constraint fk_student_sid foreign key (sid) references student (sid);
4. 插入数据
   create table A(
   name varchar(10),
   score int
   );
   create table B(
   name varchar(10),
   score int
   );
   insert 插入
   insert into A values(‘a’,10),(‘b’,20),(‘c’,30);
   insert into B values(‘a’,10),(‘b’,20),(‘d’,40);
5. 合并查询
   关键词union可以取两个表的并集 (字段名 类型相同)
   union all可以把两个表的数据合并一起
   连接两个表的查询结果集，重复的不显示
   select * from a union select * from b;
   select * from a union all select * from b;
6. – 3个表查询
   select s.stuname,c.score,u.cname from student s,score c,course u where s.stuid=c.stuid and c.courseid=u.courseid;
   连接查询(多表查询)
   内连接(inner)可以省略
   on后面是去除重复的条件
   select * from student s join score c on s.stuid=c.stuid;
   3个表一起内连接查询
   select * from student s join score c on s.stuid=c.stuid join course o on c.courseid=o.courseid;
   查询表中80分以上的学生姓名 分数 科目信息
   select s.stuname,c.score,o.cname from student s join score c on s.stuid=c.stuid join course o on c.courseid=o.courseid where c.score>80;
   外连接 左外连接 右外链接
   左外连接就是以左边的表为主 会查询出左边的所有数据
   关键词outer可以省略
   学生和分数表
   select * from student left join score on student.stuid=score.stuid;
   select * from score left join student on student.stuid=score.stuid;
   自然连接 关键词natural
   可以自动匹配两个表中相同的字段的值
   要求字段名和类型相同
   select * from student natural join score;
   子查询(嵌套查询)
   员工表和部门表查询
   查询工资高于Jones的员工信息
   select * from emp where sal>(select sal from emp where ename=’jones’);
   select * from emp where ename=’jones’;
   查询与SCOTT同一个部门的员工
   select * from emp where deptno=(select deptno from emp where ename=’scott’);
   工资高于30号部门所有人的员工信息
   select * from emp where sal>(select max(sal) from emp where deptno=30);
   查询工作和工资与MARTIN（马丁）完全相同的员工信息
   select * from emp where (job,sal) in(select job,sal from emp where ename=’martin’);
   有2个以上直接下属的员工信息
   看mgr 出现几次 出现一次就一个下属
   select * from emp where empno in (select mgr from emp group by mgr having count(mgr)>=2);
   查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
   select e.ename,e.sal,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and e.empno=7788;
   求各个部门薪水最高的员工所有信息
   把查询出来结果当做一张表
   select * from emp e1,(select deptno, max(sal) msal from emp group by deptno) e2 where e1.sal=e2.msal and e1.deptno=e2.deptno;
   select * from emp e1,(select deptno, max(sal) msal from emp group by deptno) e2 where e1.sal=e2.msal and e1.deptno=e2.septno;