本文共 9472 字，大约阅读时间需要 31 分钟。

联表查询不需要外键约束

1、内连接inner join

1.1 思考逻辑

1. 需要查什么？
2. 从哪里查？哪种形式链接哪张表？
3. 查询的条件是什么？表与表之间条件？额外筛选条件？

1.2 代码

mysql > select e.id as "编号", e.empName as "姓名", d.deptName as "部门"from employee e -- 从哪里查？这里定义了简写inner join dept d -- 使用内连接查询，去连接另一张表，定义了简写on e.deptId = d.id -- 使用 on 来约束条件，查询条件，id一一对应where e.name = "Jack" -- 其他约束条件order by e.id asc; -- id顺序输出

2、外连接

内连接会排空，导致相关数据查不到，所以需要外连接。可以专门用来查询空的值，即不符合条件的。

2.1 左外连接，左对齐 left outer join

左表数据一定全部展示，右表来匹配左表数据，如果右表没有数据，显示null。

mysql > select d.deptName as "部门", e.empName as "姓名"from dept dleft outer join employee eon d.id = e.deptId;+-----------+-----------+| 部门      | 姓名      |+-----------+-----------+| 1        | Jack      || 2        | Jone      || 3        | LUcy      || 4        | Peter     || 5        | Harry     || 6        | Sparrow   || 7        | NULL      | -- 就是因为左表对应的右表没有数据，显示为null+-----------+-----------+

2.2 右外连接，右对齐 right outer join

右表数据一定全部展示，左表来匹配右表数据，如果左表没有数据，显示null

mysql > select  e.empName as "姓名",  d.deptName as "部门"from employee eright outer join dept don e.deptId = d.idwhere d.id is null;

3、一对多

一个学生可能会有多次考试，但是每次考试对应的只属于一个学生。

将学生的id和score_record中的stuId建立内连接，进行查询，导出所有结果

-- 1. 找出 某人的所有成绩记录mysql > select s.id, s.name, sr.javaScore, sr.cScore, sr.htmlScore -- 查什么from student s -- 从哪里查inner join score_record sr -- 连接哪一张表on s.id = sr.stuId -- 表之间的条件where s.name = "翘翘"; -- 要求的查询学生的姓名-- 2. 找出某人的所有成绩的平均分mysql > select s.id, s.name, avg(sr.javaScore), avg(sr.cScore), avg(sr.htmlScore) -- 查什么from student s -- 从哪里查inner join score_record sr -- 连接哪一张表on s.id = sr.stuId -- 表之间的条件where s.name = "翘翘"-- 要求的查询学生的姓名group by s.id;-- 3. 找出某人考试了几次???mysql >select s.id, s.name, count(sr.stuId)from student sinner join score_record sron s.id = sr.stuIdwhere s.name = "翘翘"group by s.id;-- 4.输出某人各科成绩，并每次考试都有计算各科成绩之和totalScore,可以使用AS定义SELECT s.`id`, s.`name`, sr.`javaScore`, sr.`cScore`, sr.`htmlScore`, (sr.`javaScore` + sr.`cScore` + sr.`htmlScore`) AS "totalScore"FROM student sINNER JOIN score_record srON s.`id` = sr.`stuId`WHERE s.`name` = "翘翘";+----+--------+-----------+--------+-----------+-----------+| id | name   | javaScore | cScore | htmlScore | totalScore|+----+--------+-----------+--------+-----------+ ---------+|  1 | 翘翘   |     10.50 |  20.50 |     35.50 |    66.50  ||  1 | 翘翘   |     12.50 |  22.50 |     33.50 |    68.5   |    |  1 | 翘翘   |     13.50 |  21.50 |     30.50 |    65.5   |+----+--------+-----------+--------+-----------+----------+

4、多对多

多对多要拆分出一对多的情况进行搭桥解决。

比如多个用户，权限不同，多个不同权限对应多个不同的功能。

mysql > create table user(    id int primary key auto_increment,    name varchar(20) not null,     roleId int not null );mysql > create table role(    id int primary key auto_increment,    roleName varchar(20) not null);mysql > create table privilege(    id int primary key auto_increment,    pName varchar(20) not null);mysql > create table role_to_privilege(    id int primary key auto_increment,    roleId int not null,    pId int not null);mysql > insert into user(name, roleId) values("张三", 1);mysql > insert into user(name, roleId) values("李四", 2); mysql > insert into role(roleName) values("管理员");mysql > insert into role(roleName) values("普通用户");mysql > insert into privilege(pName) values("删除用户"); -- 管理mysql > insert into privilege(pName) values("添加用户"); -- 管理mysql > insert into privilege(pName) values("修改用户"); -- 管理 用户mysql > insert into privilege(pName) values("查看指定用户"); -- 管理 用户mysql > insert into privilege(pName) values("查看所有用户"); -- 管理-- role_to_privileg 根据id号，将用户类别和可用权限进行对接mysql > insert into role_to_privilege(roleId, pId) values(1, 1);mysql > insert into role_to_privilege(roleId, pId) values(1, 2);mysql > insert into role_to_privilege(roleId, pId) values(1, 3);mysql > insert into role_to_privilege(roleId, pId) values(1, 4);mysql > insert into role_to_privilege(roleId, pId) values(1, 5);mysql > insert into role_to_privilege(roleId, pId) values(2, 3);mysql > insert into role_to_privilege(roleId, pId) values(2, 4);-- 1. 我想找到张三的权限有什么???-- u.name privilege.pNamemysql >select u.name, p.pNamefrom user uinner join role ron u.roleId = r.idinner join role_to_privilege rpon r.id = rp.roleIdinner join privilege pon rp.pId = p.idwhere u.name = "张三";mysql > select u.name, p.pNamefrom user uinner join role ron u.roleId = r.idinner join role_to_privilege rpon r.id = rp.roleIdinner join privilege pon rp.pId = p.idwhere u.name = "李四";

5、一对一

比如一个员工对应一份简历，解决办法：

1. 员工和简历，各自【主键】统一为同一个值
2. 简历表创建一个【empId】用【unique】修饰，对应员工的主键id。

6、合并结果集

UNION 排重合并

UNION ALL 全部合并 由于可能存在列的不同，所以需要select进行选择。

SELECT name, age from test1 UNION all SELECT name,age from test2;

7. 子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置：

​ a. where后，作为被查询的条件的一部分； ​ b. from后，作临时表；

当子查询出现在where后作为条件时，还可以使用如下关键字：

​ a. any ​ b. all

子查询结果集的常见形式：

​ a. 单行单列（用于条件） ​ b. 多行单列（用于条件） ​ c. 多行多列（用于表）

#子查询#找出工资高于JONES的员工SELECT * from emp where sal > (select sal from emp where ename='JONES');#工资高于30号部门所有人的SELECT * from emp WHERE sal>(SELECT MAX(sal) from emp where deptno=30)SELECT * from emp WHERE sal> ALL (SELECT sal from emp WHERE deptno=30);#工资低于30号部门所有人的SELECT * from emp WHERE sal< ANY (SELECT sal from emp WHERE deptno=30);#from后面子查询SELECT ename,job,hiredate FROM (SELECT ename,job,hiredate FROM emp WHERE hiredate>'1987-1-1') AS temp;

8、数据库的导入导出和权限设定

8.1 使用DOS

-- 导出数据库表mysqldump -uroot -p 数据库名 > school.sql-- 导入数据库表mysql -u root -pmysql>use 数据库--然后使用source命令，后面参数为脚本文件(如这里用到的.sql)mysql>source d:/dbname.sql

8.2 使用可视化工具（SQLyog为例）

1、先备份，右击数据库选择备份—》备份数据库，转储到sql

2、导入，右击选择 “执行SQL脚本”

8.3 创建用户和授权，不同用户访问同一个数据库，对应不同的权限

1. 创建用户

-- 设定帐号和密码-- @后面是localhost表示本机可以登录，%是所有人都可以登录，或者可以写ip地址指定某人登录CREATE USER `zhangsan` IDENTIFIED BY '123';CREATE USER `zhangsan`@`localhost` IDENTIFIED BY '123';CREATE USER `zhangsan`@`%` IDENTIFIED BY '123';CREATE USER `zhangsan`@`10.9.21.188` IDENTIFIED BY '123';

2. 授权

-- all是所有权限，这里可以只写部分列条目GRANT ALL ON school.* TO `zhangsan`;

3. 撤销权限

REVOKE ALL ON school.* FROM `zhangsan`;

4. 删除用户

DROP USER `zhangsan`;

9、商城数据库表案例

分为五张表：

1. 用户表，记录用户的id、用户名、密码、地址和手机号
2. 订单表，记录用户id，总价格和订单项表id（与用户表构成了一对多）
3. 订单项表，记录了订单表id，产品id，商品数量（与订单表构成了一对多）
4. 产品表，记录了产品id，产品名字，产品价格和品类id（与订单项表构成一对多）
5. 品类表，记录了品类的id和品类名字（与产品表构成了一对多）

图示：

# 创建用户表create table user(	 userId int primary key auto_increment,  	 username varchar(20) not null,  	 password varchar(18) not null,     address varchar(100),     phone varchar(11));#一对多的实现#创建分类表create table category(  cid varchar(32) PRIMARY KEY ,  cname varchar(100) not null		#分类名称);# 商品表CREATE TABLE `products` (  `pid` varchar(32) PRIMARY KEY,  `name` VARCHAR(40) ,  `price` DOUBLE(7,2),   category_id varchar(32),   constraint foreign key(category_id) references category(cid));#多对多的实现#订单表create table `orders`(  `oid` varchar(32) PRIMARY KEY ,  `totalprice` double(12,2), #总计  `userId` int,   constraint foreign key(userId) references user(userId) #外键);# 订单项表create table orderitem(  oid varchar(32),	#订单id  pid varchar(32),	#商品id  num int ,         #购买商品数量  primary key(oid,pid), #主键  foreign key(oid) references orders(oid),  foreign key(pid) references products(pid));#用户表添加数据INSERT INTO USER(username,PASSWORD,address,phone) VALUES('张三','123','北京昌平沙河','13812345678');INSERT INTO USER(username,PASSWORD,address,phone) VALUES('王五','5678','北京海淀','13812345141');INSERT INTO USER(username,PASSWORD,address,phone) VALUES('赵六','123','北京朝阳','13812340987');INSERT INTO USER(username,PASSWORD,address,phone) VALUES('田七','123','北京大兴','13812345687');#给商品表初始化数据insert into products(pid,name,price,category_id) values('p001','联想',5000,'c001');insert into products(pid,name,price,category_id) values('p002','海尔',3000,'c001');insert into products(pid,name,price,category_id) values('p003','雷神',5000,'c001');insert into products(pid,name,price,category_id) values('p004','JACK JONES',800,'c002');insert into products(pid,name,price,category_id) values('p005','真维斯',200,'c002');insert into products(pid,name,price,category_id) values('p006','花花公子',440,'c002');insert into products(pid,name,price,category_id) values('p007','劲霸',2000,'c002');insert into products(pid,name,price,category_id) values('p008','香奈儿',800,'c003');insert into products(pid,name,price,category_id) values('p009','相宜本草',200,'c003');insert into products(pid,name,price,category_id) values('p010','梅明子',200,null);#给分类表初始化数据insert into category values('c001','电器');insert into category values('c002','服饰');insert into category values('c003','化妆品');insert into category values('c004','书籍');#添加订单insert into orders values('o6100',18000.50,1);insert into orders values('o6101',7200.35,1);insert into orders values('o6102',600.00,2);insert into orders values('o6103',1300.26,4);#订单详情表insert into orderitem values('o6100','p001',1),('o6100','p002',1),('o6101','p003',1);

代码中使用了外键约束，其实可以不用外键约束，直接使用内外连接同样可以达到查询目的。如果使用外键约束，注意数据添加的时候，先添加主表数据，再添加从表数据，否则报错。

• 查询所有用户的订单详情（由于会有空值，这里必须用外连接）

select u.userId,u.username,o.totalprice,p.name,p.price,cg.cnamefrom USER uLEFT JOIN orders oON u.userId = o.userIdLEFT JOIN orderitem oiON o.oid = oi.oidLEFT JOIN products pON oi.pid = p.pidLEFT JOIN category cgON p.category_id=cg.cid

• 查询订单的价格大于800的所有用户信息，这里注意排重

-- 第一种方式，使用子查询，这里注意必须是in，因为括号内返回值有很多，DISTINCT为排重关键字-- 该方式是建立在【外键约束】基础上的，才能利用orders表的结果返回给user表select * from user where userId in (select DISTINCT userId from orders where totalprice > 800);-- 第二种方式，使用内连接，不需要建立在外键约束基础上就可以完成-- 这里使用uer的ID，因为唯一性，排重select *  from user uINNER JOIN orders oON u.userId=o.userIdwhere o.totalprice>800GROUP BY u.userId;

10、数据库环境

# 创建数据库CREATE DATABASE db_shopping;# 创建用户 shopCREATE USER shop  IDENTIFIED BY '123';# 给shop用户授权GRANT ALL ON db_shopping.* TO shop;#使用 shop账户登录创建表结构

转载地址：http://zsgzi.baihongyu.com/