USE mysql_03;
SHOW TABLES ;
SELECT *FROM dormitory;
-- 多表查询的测试
SELECT e.NAME,d.NAME
FROM employee e,dept d
WHERE e.deptId=d.id;
-- 另一种语法
SELECT e.NAME,d.NAME
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;
-- 左表查询(左右表查询本质一样)
SELECT e.NAME,d.NAME
FROM employee e
LEFT JOIN dept d
ON e.deptId=d.id;
SELECT e.NAME,d.NAME
FROM dept d
RIGHT JOIN employee e
ON d.id=e.deptId
-- 自连接查询(数据库中的某个表连接自己)
-- 给员工表中的员工添加一个字段:boosId
ALTER TABLE employee ADD COLUMN boosId INT ;
-- 查询员工表的员工以及对应的上司
SELECT e.id,e.NAME AS '员工',b.NAME AS '上司'
FROM employee e -- 自己去给当前员工指定一个别名
-- 员工表中的要显示员工姓名的数据
LEFT OUTER JOIN employee b
ON e.boosId= b.id;
SELECT *FROM employee;
SELECT DISTINCT deptId FROM employee;
SHOW TABLES;
DESC dormitory;
SELECT address ,COUNT(*)FROM dormitory GROUP BY address;
SHOW TABLES ;
SELECT *FROM dormitory;
-- 多表查询的测试
SELECT e.NAME,d.NAME
FROM employee e,dept d
WHERE e.deptId=d.id;
-- 另一种语法
SELECT e.NAME,d.NAME
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;
-- 左表查询(左右表查询本质一样)
SELECT e.NAME,d.NAME
FROM employee e
LEFT JOIN dept d
ON e.deptId=d.id;
SELECT e.NAME,d.NAME
FROM dept d
RIGHT JOIN employee e
ON d.id=e.deptId
-- 自连接查询(数据库中的某个表连接自己)
-- 给员工表中的员工添加一个字段:boosId
ALTER TABLE employee ADD COLUMN boosId INT ;
-- 查询员工表的员工以及对应的上司
SELECT e.id,e.NAME AS '员工',b.NAME AS '上司'
FROM employee e -- 自己去给当前员工指定一个别名
-- 员工表中的要显示员工姓名的数据
LEFT OUTER JOIN employee b
ON e.boosId= b.id;
SELECT *FROM employee;
SELECT DISTINCT deptId FROM employee;
SHOW TABLES;
DESC dormitory;
SELECT address ,COUNT(*)FROM dormitory GROUP BY address;