MySQL数据库练习

1.1  SQL语句的多表操作:

1.1.1    多表的设计:

一对多:

* 一对多:

* 一个部门有多个员工,一个员工只能属于某一个部门.

* 一个用户产生多个订单,一个订单只能属于某一个用户.

 

* 创建表:

*  create database web016;

*  use web016;

*  create table dept(

did int primary keyauto_increment,

dname varchar(20)

);

 

create table employee(

eid int primary keyauto_increment,

ename varchar(20),

sex varchar(20),

age int,

dno int

);

 

insert into dept values (null,'市场部');

insert into dept values (null,'研发部');

insert into dept values (null,'人事部');

 

insert into employee values (null,'张三','男',23,1);

insert into employee values (null,'李四','男',28,1);

insert into employee values (null,'王五','女',25,3);

insert into employee values (null,'赵六','女',31,2);

insert into employee values (null,'孙七','男',22,3);

 

假设需要删除市场部!市场部对应的员工应该如何进行处理?

多表之间约束:（约束:保证数据的完整性）

* 外键约束:

* alter table employee add foreign key(dno)references dept(did);

 

***** 一对多的建表原则:

* 在多的一方创建一个字段，作为外键指向一的一方的主键.

多对多:

* 多对多:

* 一个学生可以选择多门课程,一门课程可以被多个学生所选择.

* 一个角色可以有多个权限,一个权限也可以属于多个角色.

 

创建表:

create table student(

sid intprimary key auto_increment,

snamevarchar(20)

);

 

create table course(

cid intprimary key auto_increment,

cnamevarchar(20)

);

 

insert into student values (null,'张三');

insert into student values (null,'李四');

insert into student values (null,'王五');

 

insert into course values (null,'Java');

insert into course values (null,'PHP');

insert into course values (null,'IOS');

insert into course values (null,'C++');

 

create table stu_cour(

sno int, 

cno int

);

alter table stu_cour add foreign key(sno)references student(sid);

alter table stu_cour add foreign key(cno)references course(cid);

insert into stu_cour values (1,1);

insert into stu_cour values (1,2);

insert into stu_cour values (1,3);

insert into stu_cour values (2,2);

insert into stu_cour values (2,4);

insert into stu_cour values (3,3);

***** 多对多建表原则:

* 创建一个中间表:(第三张表),中间表中需要有两个字段.分别作为外键指向多对多双方的主键.

一对一:

* 一对一:

* 一个公司对应一个注册地址.同时一个注册地址只能被一个公司注册.

* 实际开发中一对一的关系并不是很常用.

 

* 一对一的建表原则:

* 唯一外键对应:

* 假设一对一的双方任意一方是多.（假设为一对多）.需要在多的一方创建外键指向一的一方的主键.但同时将这个外键设置为唯一的unique.

* 主键对应:
                * 在任意的一方的主键上添加一个外键约束.指向另一方的主键.

 购物网站的建表分析:

 

1.2  SQL的多表查询:

1.2.1    多表的查询:

笛卡尔积查询:(交叉连接查询)

* 交叉连接:

* 语法:

* select * from A,B;

 

* 两个表的交叉连接:得到的记录是两个表的笛卡尔积.

 

内连接查:经常使用.

* 内连接:inner join.

* 内连接查询的结果:实质上是两个表的交集.

* 语法:

* select * from A inner joinB on 条件;(inner 可以省略)

* 隐式内连接:

* select * from A,B where 条件;

* 显式内连接:

* select * from A inner join B on 条件;

 

* 练习:

* SELECT * FROM dept d INNER JOIN employee e  ON d.did = e.dno;    ---显式内连接.

* SELECT * FROM dept d,employee e WHERE d.did = e.dno;             ---隐式内连接.

外连接查询:

* 外连接:outer join

* 左外连接:

* 查询的结果:是左边表的全部以及两个表的交集.

* 语法:

* select * from A left outer joinB on 条件;

 

* 右外连接:

* 查询的结果:是右边表的全部以及两个表的交集.

* 语法:

* select * from A right outer joinB on 条件;

 

* 练习:

* SELECT * FROM dept d LEFT OUTER JOIN employee e ON d.did = e.dno;

* SELECT * FROM dept d RIGHT OUTER JOIN employee e ON d.did = e.dno;

1.2.2    子查询:

什么是子查询:

子查询：指的是一条语句的查询结果需要依赖另一条语句的查询结果.

 

案例:

* 查询员工年龄大于平均年龄的员工的信息.

* 求平均年龄:

* select avg(age) from employee;

* 求员工年龄大于平均值:

* select * from employee where age > 平均值.

* 完整语句:

* select * from employee where age > (select avg(age) fromemployee);

 

在子查询中使用的关键字:any、all.

* any:任意一个.

* all:所有.

 

* any:

案例:

* 查询员工的年龄大于任意一部门员工的年龄.

* 分析:

* 查询一号部门的员工的年龄.

* select age from employee where dno = 1;

* any代表就是任意:

* select * from employee where age > any (select age fromemployee where dno = 1);

* 查询员工的年龄小于任意一部门员工的年龄.

* 分析:

* 查询一号部门的员工的年龄.

* select age from employee where dno = 1;

* any代表任意:

* select * from employee where age < any (select age fromemployee where dno = 1);

 

* all:所有:

* 案例:

* 查询员工的年龄大于所有一部门的员工的年龄.

* 分析:

* 查询一号部门的员工的年龄.

* select age from employee where dno = 1;

* all代表就是所有:

* select * from employee where age > all (select age fromemployee where dno = 1);

* 查询员工的年龄小于所有一部门的员工的年龄.

* 分析:

* 查询一号部门的员工的年龄.

* select age from employee where dno = 1;

* all代表就是所有:

* select * from employee where age < all (select age fromemployee where dno = 1);

1.2.3    多表查询的练习:

练习:

* 按照部门名称统计每个部门的员工的个数.

* 统计个数：selectcount(*) from employee;

* 分组统计: group by.

* 按照部门的编号统计员工的个数:selectdno ,count(*) from employee group by dno;

* 如果按照名称统计,名称没有在员工表中,需要查询部门表.

* select d.dname,count(*) from dept d,employee e where d.did =e.dno group by d.dname;

 

* 按照部门名称统计每个部门的员工的平均年龄.

* select d.dname,avg(age) from dept d,employee e where d.did = e.dnogroup by d.dname;

 

* 查询哪些员工的年龄大于任意部门的平均年龄.

* 查询每个部门的平均年龄.

* select avg(age) from dept d,employee e where d.did = e.dno groupby d.dname;

* 完整的语句:

* select * from employee where age > any (select avg(age) fromdept d,employee e where d.did = e.dno group by d.dname);

* 查询哪些员工的年龄大于等于所有部门的平均年龄.

* 完整的语句:

* select * from employee where age >= all(select avg(age) fromdept d,employee e where d.did = e.dno group by d.dname);

 

* 查询姓名为张三的学生选择了哪几门课程的名称.

* 查询每个学生选择的课程

* select * from student s,stu_cour sc,course c where s.sid = sc.snoand sc.cno = c.cid;

* 查询姓名为张三:

* SELECT c.cname FROM student s,stu_cour sc,course c WHERE s.sid =sc.sno AND sc.cno = c.cid AND s.sname='张三';

 

* 查询课程名称为Java的课程被哪些学生选择.

* select s.sname from student s,stu_cour sc,course c WHERE s.sid =sc.sno AND sc.cno = c.cid and c.cname = 'Java';