数据库–Database
----------------------------------用于存储和管理数据的仓库。
使用MySQL客户端----Navicat for MySQL--------使用介绍
1、概述：
特点：
1、可以持久化地存储数据，本身是一个文件系统。
2、可以方便地存储和管理数据。
3、使用统一的SQL来操作数据库。
data/mysql文件夹下 ：文件夹对应数据库， .frm文件都是一些表
安装目录下，my.ini 是配置文件。
2、SQL：
1、什么是SQL？
结构化查询语言（Structured Query Language）
是定义了操作所有关系型数据库的规则。---对接各种关系数据库（MySQL、Oracle等）
每一种数据库操作的方式存在不一样的地方---“方言”。
2、SQL通用语法
1、SQL语句可以单行或多行书写，以分号结尾。
2、可使用空格和缩进来增强语句的可读性。
3、MySQL数据库的SQL语句不区分大小写，但是关键字建议使用大写。
4、三种注释：
1、-- + 空格 + 注释内容
2、# + 注释内容
3、/* */ 中间放多行注释
3、SQL分类
1、DDL--Data Definition Language 数据定义语言： 操作数据库、表
2、DML--Manipulation 操作语言： 增删改 表中的数据
3、DQL--Query 查询语言： 查询表中的数据
4、DCL--Control 控制语言： 授权操作
1、DDL：操作数据库、表
1、操作数据库：CRUD
1、Create： 创建
* 创建数据库：create database 数据库名称;
* 创建数据库，判断不存在，再创建： create database if not exists 数据库名称;
* 创建数据库，并指定字符集： create database 数据库名称 character set 字符集名;
* 练习： 创建db4数据库，判断是否存在，并制定字符集为gbk
* create database if not exists db4 character set gbk;
2、Retrieve : 查询
* 查询所有数据库的名称：------show databases;
* 查询某个数据库的字符集：查询某个数据库的创建语句：----show create database 数据库名称;
3、Update ： 修改
* 修改数据库的字符集：alter database 数据库名称 character set 字符集名称；
4、Delete ： 删除
*删除数据库： drop database 数据库名称；
* 判断数据库存在，存在再删除： drop database if exists 数据库名称;
5、使用数据库
*查询当前正在使用的数据库名称： select database();
*使用数据库：use 数据库名称;
2、操作表
1、Create： 创建
1、语法：
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
....
列名n 数据类型n
);
* 注意：最后一列，不需要加逗号（,）
2、数据库类型：
1. int：整数类型
* age int,
2. double:小数类型
* score double(5,2)
3. date:日期，只包含年月日，yyyy-MM-dd
4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5. timestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6. varchar：字符串
* name varchar(20):姓名最大20个字符
* zhangsan 8个字符 张三 2个字符
3、 创建表
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);
4、 复制表： create table 表名 like 被复制的表名;
2、Retrieve : 查询
*查询某个数据库中所有的表名称： show tables；
*查询表结构：desc 表名称;
*查询某个表的创建语句： show create table 表名称；
3、Update ： 修改
1. 修改表名：alter table 表名 rename to 新的表名;
2. 修改表的字符集：alter table 表名 character set 字符集名称;
3. 添加一列：alter table 表名 add 列名 数据类型;
4. 修改列名称 类型
alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;
5. 删除列
alter table 表名 drop 列名;
4、Delete ： 删除
* drop table 表名;
* drop table if exists 表名 ;
2、DML : 增删改表中的数据
1、添加数据
语法：
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
注意：
1. 列名和值要一一对应。
2. 如果表名后，不定义列名，则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);
3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
2、删除数据
语法：
delete from 表名称 [where 条件] ;
注意：
1、如果不加条件，则会删除表中所有记录。
2、如果要删除所有记录：
1、delete from 表名称; -------不推荐使用，有多少条记录就会执行多少次删除操作，效率低。
2、TRUNCATE TABLE 表名称;-----推荐使用，效率更高，先删除表然后再创建一张一样的表。-----drop+create
3、修改数据
语法： update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
-----UPDATE stud SET age = 234, score = 100 WHERE id = 3;
注意： 如果不添加任何where条件，则会将表中所有记录全部修改。
3、DQL：查询表中的记录
-------新建一张表----------------
CREATE TABLE boss (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);
INSERT INTO boss(id,NAME,age,sex,address,math,english) VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
SELECT *FROM boss
------------------------------------------------------
select * from 表名称；
**1、语法：**
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
**2、基础查询**
1. 多个多个字段的查询
select 字段名1，字段名2... from 表名；
* 注意：
* 如果查询所有字段，则可以使用*来替代字段列表。
2. 去除重复：
* distinct
3. 计算列
* 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
* ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
* 表达式1：哪个字段需要判断是否为null
* 如果该字段为null后的替换值。
4. 起别名：
* as：as也可以省略
------------SQL语句记录---------------------
SELECT *FROM boss;
SELECT NAME,age FROM boss;
-- 格式化 查询姓名和年龄
SELECT
NAME， -- 姓名
age -- 年龄
FROM
student; -- 学生表
SELECT address FROM boss;
SELECT DISTINCT address FROM boss; -- 去重
SELECT DISTINCT NAME,address FROM boss;
-- 计算两门成绩的和
SELECT name, math, english, math + english FROM boss;
-- 问题： 如果有null参与的运算，计算结果都为null
SELECT name, math, english, math + IFNULL(english,0) FROM boss;
-- 起别名
SELECT name, math, english, math + IFNULL(english,0) AS 总分 FROM boss;
SELECT name, math 数学, english 英语, math + IFNULL(english,0) 总分 FROM boss;
--------------------------------------------
**3、条件查询**
1. 条件查询语句---在where子句后跟条件
2. 运算符
* > 、< 、<= 、>= 、= 、<>
* BETWEEN...AND
* IN( 集合)
* LIKE：模糊查询
* 占位符：
* _:单个任意字符
* %：多个任意字符
* IS NULL
* and 或 &&
* or 或 ||
* not 或 !
------------查询SQL语句-------------------
-- 条件查询
-- 查询年龄大于20岁
SELECT *FROM boss WHERE age > 20;
SELECT *FROM boss WHERE age <= 20;
-- 查询年龄等于20岁、不等于20岁
SELECT *FROM boss WHERE age = 20;
SELECT *FROM boss WHERE age != 20;
SELECT *FROM boss WHERE age <> 20;
-- 查询年龄大于等于20小于等于30岁
SELECT *FROM boss WHERE age >= 20 && age <= 30; -- 不推荐
SELECT *FROM boss WHERE age >= 20 AND age <= 30; --
SELECT *FROM boss WHERE age BETWEEN 20 AND 30; -- between .. and
-- 查询年龄22岁，19岁，25岁的信息
SELECT * FROM boss WHERE age = 22 OR age = 19 OR age = 25;
SELECT * FROM boss WHERE age in (22,19,25);
-- 查询英语成绩为null、不为null
SELECT * FROM boss WHERE english = NULL; -- 这种写法不对，null值不能用= （！= ）判断
SELECT * FROM boss WHERE english is NULL;
SELECT * FROM boss WHERE english is not NULL;
-- 查询姓马的有哪些？--like子句
SELECT * FROM boss WHERE name LIKE '马%';
-- 查询姓名第二个字是化的人
SELECT * FROM boss WHERE NAME LIKE "_化%";
-- 查询姓名是3个字的人
SELECT * FROM boss WHERE NAME LIKE '___';
-- 查询姓名中包含德的人
SELECT * FROM boss WHERE NAME LIKE '%德%';
------------------------------------------------
**4、排序查询**
* 语法：order by 子句
* order by 排序字段1 排序方式1 ， 排序字段2 排序方式2...
* 排序方式：
* ASC：升序，默认的。
* DESC：降序。
* 注意：
* 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
------------------------------------------------------------------------------------
SELECT * FROM boss ORDER BY math ASC;-- 升序
SELECT * FROM boss ORDER BY math DESC;-- 降序
-- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名
-- 首先按照第一条件进行排序，如果第一条件相同，再按照第二条件排序
SELECT * FROM boss ORDER BY math ASC,english ASC;
SELECT * FROM boss ORDER BY math ASC,english DESC;
----------------------------------------------------------------------------------------------
**5、聚合函数：**
将一列数据作为一个整体，进行纵向的计算。----比如计算数学成绩平均分这些。
1. count：计算个数
1. 一般选择非空的列：主键
2. count(*)
2. max：计算最大值
3. min：计算最小值
4. sum：计算和
5. avg：计算平均值
* 注意：聚合函数的计算，排除null值。
解决方案：1. 选择不包含非空的列进行计算 2. IFNULL函数
----------------------------------------------
SELECT COUNT(NAME) FROM boss; -- 这里是排除null值
SELECT COUNT(english) FROM boss;
SELECT COUNT(IFNULL(english,0)) FROM boss;-- IFNULL函数
SELECT COUNT(id) FROM boss;
SELECT COUNT(*) FROM boss;
SELECT MAX(math) FROM boss;
SELECT MIN(math) FROM boss;
SELECT SUM(math) FROM boss;
SELECT SUM(english) FROM boss;
SELECT AVG(math) FROM boss;
--------------------------------------------------------------
**6、分组查询:**
1. 语法：group by 分组字段；
2. 注意：
1. 分组之后查询的字段：分组字段、聚合函数 ----sex，avg(),count 别的像name这些没有意义
2. where 和 having 的区别？
1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。
-----------------------------------------------------------------------------------
-- 按照性别分组，分别查询男女同学的平均分,人数
SELECT sex, AVG(math), COUNT(id) FROM boss GROUP BY sex;
-- 如果分数低于70分，不参与分组。-- 分组前进行一些限定
SELECT sex, AVG(math), COUNT(id) FROM boss WHERE math > 70 GROUP BY sex;
-- 分组之后要大于2人-- having语句
-- where作用在分组之前，如果不满足条件则不参与分组，having则是在分组之后限定，不满足条件则不会查询出来
-- where 后不能跟聚合函数，having可以进行聚合函数的判断
SELECT sex, AVG(math), COUNT(id) FROM boss WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
-- 起别名操作
SELECT sex, AVG(math), COUNT(id) 人数 FROM boss WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
-----------------------------------------------------------------------
**7、分页查询** ---limit子句
1. 语法：limit 开始的索引, 每页查询的条数;
2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
3. 分页操作 limit 是一个MySQL"方言"
---------------------------------------------------------------------------
-- 每页显示3条记录
SELECT * FROM bossLIMIT 0,3; -- 第1页---显示出123
SELECT * FROM boss LIMIT 3,3; -- 第2页---显示出456----显示出来的数字是开始索引加一
SELECT * FROM boss LIMIT 6,3; -- 第3页---显示出78 --没有那么多，就全部显示
------------------------------------------------------------------------------------------------
3、多表查询
查询语法：
select 列名列表 from 表明列表 where ...
准备SQL：代码放在代码块。
笛卡尔积：
有两个集合A,B，取这两个集合的所有组成情况。
要完成多表查询，需要消除无用的数据。
多表查询的分类：
1、内连接查询：--查询 的就是交集部分
1、隐式内连接：使用where条件消除无用数据
SELECT *FROM emp, dept WHERE emp.dept_id = dept.id;
2、显示内连接：
语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
-- 显式内连接
SELECT *FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
SELECT *FROM emp JOIN dept ON emp.dept_id = dept.id;
3、内连接查询：
1、从哪些表中查询数据
2、条件是什么
3、查询哪些字段
2、外连接查询：-- 左外查询和右外查询相对应
1、左外连接：
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
查询的是左表所有数据以及其交集部分
* 例子：
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
2、右外连接：
语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
查询的是右表所有数据以及其交集部分。
* 例子：
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
3、子查询：
概念：在查询中嵌套查询，称嵌套查询为子查询
SELECT *FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);--后面括号里就是子查询
子查询不同情况：
1、子查询的结果是单行单列的
子查询可以作为条件，使用运算符去判断。大于小于等于。运算符： > >= < <= =
-- 子查询作为条件
-- 查询员工工资小于平均工资的员工信息
SELECT *FROM emp WHERE (salary < (SELECT AVG(salary) FROM emp));
2、子查询的结果是多行单列的
子查询可以作为条件，使用运算符in进行判断
-- 查询财务部和市场部所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT *FROM emp WHERE dept_id IN (2,3);
-- 子查询
SELECT *FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
3、子查询的结果是多行多列的
子查询可以作为一张虚拟表来参与查询
-- 多行多列的子查询结果，可以做一个虚拟表
-- 把这个查询到的表称为虚拟表，和部门信息作关联
SELECT *FROM dept t1, (SELECT *FROM emp WHERE join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
USE db3
SHOW TABLES
# 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
)
insert into dept (name) values ('开发部'),('市场部'),('财务部');
# 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
)
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男
',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男
',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女
',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女
',4500,'2011-03-14',1);
SELECT *From emp
SELECT *FROM dept
SELECT *FROM emp,dept -- 笛卡尔积 比较冗余，要消除无用数据
-- 查询所有员工信息和对应的部门信息
SELECT *FROM emp, dept WHERE emp.dept_id = dept.id;
SELECT *FROM emp, dept WHERE emp.'dept_id' = dept.'id'; -- 有无单引号不影响
-- 查询员工表的名称，性别，部门表的名称
SELECT emp.name, emp.gender, dept.name FROM emp, dept WHERE emp.dept_id = dept.id;
-- 正规的写法，是将每个都变成一行
SELECT
t1.name, -- 员工表的姓名
t1.gender, -- 员工表的性别
t2.name -- 部门表的名称
FROM
emp t1,
dept t2 -- 给表取别名。理论上会有列名的提示，但是navicat没有提示
WHERE
t1.dept_id = t2.id;
-- 显式内连接
SELECT *FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
SELECT *FROM emp JOIN dept ON emp.dept_id = dept.id;
-- 外连接查询
SELECT *FROM emp; -- 没有部门信息的也会被查询
-- 查询所有员工信息，如果员工有部门，则查询部门名称；没有部门则不显示部门名称
SELECT
t1.*, t2.name
FROM
emp t1, dept t2
WHERE
t1.dept_id = t2.id; -- 此时没有部门iD的员工信息不会被查询到。
-- 左外连接 这样就能查询没有部门信息的员工信息
SELECT t1.*, t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;
-- 右外连接 -- 这样的话就没有交集部分了，没有部门信息的员工不会被查到
SELECT t1.*, t2.name FROM emp t1 RIGHT JOIN dept t2 ON t1.dept_id = t2.id;
SELECT t1.*, t2.name FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;
-- 子查询：
-- 查询工资最高的员工信息
-- 1、查询最高的工资是多少 -- 9000
SELECT MAX(salary) FROM emp;
-- 2、查询员工信息，并且工资等于9000的
SELECT *FROM emp WHERE salary = 9000;
-- 一条SQL完成这两步操作
SELECT *FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
-- 子查询作为条件
-- 查询员工工资小于平均工资的员工信息
SELECT *FROM emp WHERE (salary < (SELECT AVG(salary) FROM emp));
-- 查询财务部所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部';
SELECT *FROM emp WHERE dept_id = 3;
SELECT *FROM emp WHERE dept_id = (SELECT id FROM dept WHERE NAME = '财务部');
-- 查询财务部和市场部所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT *FROM emp WHERE dept_id = 3 OR dept_id = 2;
SELECT *FROM emp WHERE dept_id IN (2,3);
SELECT *FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
-- 查询员工的入职信息诶2011-11-11之后的员工信息和部门信息
SELECT *FROM emp WHERE join_date > '2011-11-11';
-- 多行多列的子查询结果，可以做一个虚拟表
-- 把这个查询到的表称为虚拟表，和部门信息作关联
SELECT *FROM dept t1, (SELECT *FROM emp WHERE join_date > '2011-11-11') t2
WHERE t1.id = t2.dept_id;
-- 普通内连接
SELECT *FROM emp t1, dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11';
多表查询练习
新建数据库及表的代码：
use db1
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
需求：
1、查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
2、查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
3、查询员工姓名，工资，工资等级
4、查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
5、查询出部门编号、部门名称、部门位置、部门人数
6、查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
-- 1、查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/* 分析：
1、首先是分析需要查询的表，需要查询emp表中的员工编号、员工姓名、工资，需要查询job表中的职务名称、职务描述
2、查询条件是emp.job_id = job.id
*/
SELECT
t1.id, -- 员工编号
t1.ename, -- 员工姓名
t1.salary, -- 员工工资
t2.jname, -- 职务名称
t2.description-- 职务描述
FROM
emp t1, job t2
WHERE
t1.job_id = t2.id;
-- 2、查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
-- 需要查询emp表中的员工编号，员工姓名，工资，job表中的职务名称，职务描述，dept表中的部门位置和部门名称
SELECT
t1.id,-- 员工编号
t1.ename,-- 员工姓名
t1.salary, -- 员工工资
t2.jname, -- 职务名称
t2.description, -- 职务描述
t3.dname, -- 部门名称
t3.loc -- 部门位置
FROM
emp t1, job t2, dept t3
WHERE
t1.dept_id = t3.id AND t1.job_id = t2.id
-- 3、查询员工姓名、工资、工资等级 --- 条件为between and
-- 需要查询emp表中的员工姓名、工资以及salarygrade表中的工资等级
-- 条件： emp。salary 在lo和hisalary之间 between and关键字
SELECT
t1.ename,-- 员工姓名
t1.salary, -- 员工工资
t2.grade -- 工资等级
FROM
emp t1, salarygrade t2
WHERE
t1.salary BETWEEN t2.losalary AND t2.hisalary
-- 4、查询员工姓名、工资、职务名称、职务描述、部门名称、部门位置、工资等级
-- 查询emp表中员工姓名、工资， job表中职务名称、职务描述，dept表中部门名称、部门位置，salarygrade表中工资等级
-- 条件是 emp.job_id = job.id and emp.dept_id = dept.id and emp.salary between salarygrade.losalary and salarygrade.hisalary
SELECT
t1.id,-- 员工编号
t1.ename,-- 员工姓名
t1.salary, -- 员工工资
t2.jname, -- 职务名称
t2.description, -- 职务描述
t3.dname, -- 部门名称
t3.loc, -- 部门位置
t4.grade -- 工资等级
FROM
emp t1, job t2, dept t3, salarygrade t4
WHERE
t1.job_id = t2.id and t1.dept_id = t3.id and t1.salary between t4.losalary and t4.hisalary
-- 5、查询出部门编号、部门名称、部门位置、部门人数
-- 查询dept表中的部门编号、部门名称、部门位置 以及emp表中dept_id
-- 使用分组查询，按照emp.dept_id完成分组，查询count(id)
-- 使用子查询将上一步查询的结构和dept表进行关联查询
SELECT
t1.id,
t1.dname,
t1.loc,
t2.total
FROM
dept t1,
(SELECT
dept_id, COUNT(id) total -- 起别名
FROM
emp
GROUP BY dept_id) t2
WHERE
t1.id = t2.dept_id
-- 评论中有老哥说这个查询不到财务部的信息，因为没有员工。
-- 这里使用外连接进行一个查询
SELECT
t1.*,
t2.total
FROM
dept t1
LEFT JOIN
(SELECT
dept_id, COUNT(id) total -- 起别名
FROM
emp
GROUP BY dept_id) t2
ON
t1.id = t2.dept_id
-- 6、查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
-- 分析需求： 需要查询emp 的员工姓名，直接上级的姓名 emp ---- emp表中的id和mgr进行自关联
-- emp.id = emp.mgr
-- 查询左表的所有数据和交集数据 --- 使用左外连接查询
-- 自关联映射 -- 这样查不到没有领导的员工
SELECT
t1.ename,
t1.mgr,
t2.id,
t2.ename
FROM
emp t1, emp t2
WHERE
-- t1.id = t2.mgr
t1.mgr = t2.id
-- 使用左外连接进行查询
SELECT
t1.ename,
t1.mgr,
t2.id,
t2.ename
FROM
emp t1
LEFT JOIN
emp t2
ON
t1.mgr = t2.id
扫一扫
614
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
