mysql-多表查询

CREATE DATABASE duobiao;
/*
动物表:动物名字、分类、雌雄、大小、入园时间、生日......
类别表：类别名称、动物管理员、区域、.....
工作人员表：姓名、性别、年龄、分管区域
简历表（工作人员简历）：姓名、性别、婚姻状况、身高、年龄....
*/

#工作人员表：姓名、性别、分管动物数量
CREATE TABLE worker(
wid INT PRIMARY KEY COMMENT '主键id',
wname VARCHAR(15) NOT NULL COMMENT '打工人名字',
wcount INT COMMENT '分管动物数量'
);

#类别表：类别名称、动物管理员、区域、....
CREATE TABLE AnimalType(
aid INT PRIMARY KEY COMMENT '主键id',
aname VARCHAR(20) NOT NULL COMMENT '类别名称',
aloction VARCHAR(30) COMMENT '区域',
awork INT COMMENT '分管人员',
CONSTRAINT awork FOREIGN KEY (awork) REFERENCES worker(wid)
);
#动物表:动物名字、分类、雌雄、大小、入园时间、生日......
CREATE TABLE animal(
anid INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
anname VARCHAR(15) NOT NULL UNIQUE COMMENT '动物名称',
ansex VARCHAR(3) DEFAULT '雄',
anweight INT,
andata DATE NOT NULL,
antype INT,
CONSTRAINT antype FOREIGN KEY (antype) REFERENCES AnimalType(aid)

);
/*
一对一：学号-->学籍 学籍-->学号
一对多：学号-->教室编号 教室编号不能唯一确定学号
多对多：
科目：
任课老师：
学生：

*/
INSERT INTO animal VALUES(1,"小胡子",)

DROP DATABASE xhdb;
CREATE DATABASE xhdb;
/*
图书管理系统：
1.图书表 book:id,书名，作者，价格，类型，vipid
2.类型表 type：id,名称，区域
3.会员 vip：id,姓名，爱好，号码，
4.借书信息：图书，会员

*/

-- 类型表 type：id,名称，区域
CREATE TABLE table_type(
tid INT PRIMARY KEY COMMENT'类型编码',
tname VARCHAR(15) NOT NULL COMMENT'类型名称',
tlocal VARCHAR(10) COMMENT'类型区域'

);

-- 会员表 vip：id,姓名，爱好，号码
CREATE TABLE table_vip(
vid INT PRIMARY KEY,
vname VARCHAR(10) NOT NULL,
vhobby VARCHAR(10),
vphone VARCHAR(11)
);
-- 图书表 book:id,书名，作者，价格，类型，vipid
CREATE TABLE table_book(
bid INT PRIMARY KEY,
bname VARCHAR(20) NOT NULL,
bauthor VARCHAR(20),
bsale DOUBLE,
btype INT,
vipid INT,
CONSTRAINT bt FOREIGN KEY (btype) REFERENCES table_type(tid),
CONSTRAINT vid FOREIGN KEY (vipid) REFERENCES table_vip(vid)
);
INSERT INTO table_type VALUES
(101,'中国历史','A'),(102,'漫画','C'),
(103,'小说','B'),(104,'自然天文','A');

INSERT INTO table_vip VALUES
(1001,'高渐离','三十六计','12345678901'),
(1002,'小庄','经典文学','13988888888');

INSERT INTO table_book VALUES
(123149,'哈利波特2','jk罗琳',38.8,103,NULL),
(214124,'手十六计','孙子',29.9,103,1001),
(452353,'兔子帮','十九番',13,102,NULL);

SELECT COUNT(*),btype,GROUP_CONCAT (bname)
FROM table_ book
GROUP BY btype;
SELECT * FROM table_book;
/*
多表查询：
   1.连接查询
	内连接 join...on join t1 join t2....
	select 字段名 from 主表 join 从表 on 条件;
	on/where:on的效率比where高，on在匹配到有效值后就不会继续往下，
	但where会把所有值进行匹配再筛选有效值
	交叉连接 cross join:笛卡尔集
*/

SELECT table_book.bname,table_type.tname,table_type.tlocal
FROM table_book
JOIN table_type
ON table_book.btype=table_type.tid;

SELECT table_book.*,table_type.tname,table_type.tlocal
FROM table_book
JOIN table_type
ON table_book.btype=table_type.tid;
JOIN table_

-- book表中插入一条类型为空的数据
INSERT INTO table_book
VALUES (123456,'史记','司马迁',45,NULL,NULL);

SELECT * FROM table_book CROSS JOIN table_type;

SELECT t1.bname,t1.author,t2.name,t2.tlocal;
FROM table_book t1
FROM table_type t2
ON t1.btype=t2

/*
公司人员信息统计:
员工信息:工号、工资、职位、部门
部门信息:部门编号、部门名字【法务、财务、人事、后勤部】、楼层
楼层职称表: id、职称名、等级

*/
DROP DATABASE zhazhen;
CREATE DATABASE zhazhen;
CREATE TABLE emp(
emid INT PRIMARY KEY,
emname ENUM('法务','财务','人事','后勤部') NOT NULL,
emlocation VARCHAR(3)
);
CREATE TABLE zc(
zcid INT PRIMARY KEY,
zcnmae VARCHAR(10) NOT NULL,
zcdeg VARCHAR(5)
);
CREATE TABLE user_table(
utid INT PRIMARY KEY,
utname VARCHAR(20) NOT NULL,
utmoeny DOUBLE,
emp INT,
zc INT,
CONSTRAINT emp FOREIGN KEY(emp) REFERENCES emp(emid),
CONSTRAINT zc FOREIGN KEY (zc) REFERENCES zc(zcid)

);

INSERT INTO emp 
VALUES(1,'法务部','A12'),
(2,'财务','A15'),
(3,'人事','B11'),
(4,'后勤部','A13'); 
SELECT * FROM emp;
INSERT INTO zc VALUES
(1,'黄金','1级'),
(2,'黄金','1级'),
(3,'黄金','2级');
SELECT * FROM zc;
DESC USER_table;
INSERT INTO user_table VALUES
(1,'徐凤年',12000,1,1),
(2,'姜泥',6000,2,1),
(3,'李淳罡',4500,3,2),
(4,'红薯',4000,4,3),
(5,'赵恒',NULL,NULL,NULL);
SELECT * FROM user_table;

SELECT utname "姓名",emp.`emname` "部门",zc.`zcnmae` "职称",
zc.`zcdeg` "职称等级"
FROM user_table AS us
JOIN emp ON us.`emp` = emp.`emid`
JOIN zc ON us.`zc` = zc.`zcid`;
/*
外连接必须用on做条件，不能是where
*/
-- 外连接：left/right join ... on
SELECT *
  FROM user_table AS t1
  LEFT JOIN emp AS t2
  ON t1.`emp` = t2.`emid`;

SELECT*
FROM user_table AS t1 RIGHT JOIN emp AS t2ON t1. 'emp '=t2. 'emid `;

有什么问题欢迎评论区下留言讨论！