创建表并插入数据
## 创建部门表
create table department(
d_id int primary key, ## 部门id
d_name varchar(255) not null, ## 部门名称
unique(d_name)
);
## 创建薪资表
create table salary(
s_grade varchar(10) primary key, ## 薪资等级
s_money int ## 底薪
);
## 创建员工表
create table employee(
e_id int primary key, ## 员工号
e_name varchar(255) not null, ## 员工姓名
e_date date default '2020-6-10', ## 入职日期
e_department int not null, ## 所属部门
e_grade varchar(10) not null, ## 薪资等级
foreign key(e_department) references department(d_id),
foreign key(e_grade) references salary(s_grade)
);
## 创建管理表
create table manage(
employee_id int not null, ## 员工号
m_name varchar(255), ## 职能
foreign key(employee_id) references employee(e_id)
);
insert into department values
(1,'人事部'),
(2,'财务部'),
(3,'c++'),
(4,'java');
insert into salary values
('CEO',null),
('A',8000),
('B',5000),
('C',2000);
insert into employee values
(1,'CEO',default,1,'CEO'),
(2,'员工A',default,2,'A'),
(3,'员工B',default,2,'A'),
(4,'员工C',default,3,'B'),
(5,'员工D',default,3,'B'),
(6,'员工E',default,4,'B'),
(7,'员工F',default,4,'B');
insert into manage value(2,'管理员');
交叉连接
不带WHERE条件子句,它将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积。有where子句,往往会先生成两个表行数乘积的数据表,然后才根据where条件从中选择。
语法格式:
select <表名1,属性名1>...<表名1,属性名n>,
<表名2,属性名1>...<表名2,属性名n>,
from <表名1>,<表名2>
where <条件表达式>;
select * from salary,department;
内连接 inner join … on
语法格式:
select <表名1,属性名1>...<表名1,属性名n>,
<表名2,属性名1>...<表名2,属性名n>,
from <表名1>
inner join <表名2>
on 条件表达式;
## 内连接查询,通过相同的条件进行连接
select * from
employee inner join department
on e_depart=d_id;
## inner 可以不写 但是建议写上
select * from
employee join salary
on e_grade=s_grade;
左外连接 left join
是以左表为基准,显示左表查询字段所有记录,右表只显示和左表匹配的记录,没有匹配的用NULL代替。
select <表名1,属性名1>...<表名1,属性名n>,
<表名2,属性名1>...<表名2,属性名n>,
from <表名1>
left join <表名2>
on 条件表达式;
select * from
employee left join manage
on e_id = employee_id;
右外连接
是以右表为基准,显示右表查询字段所有记录,左表只显示和右表匹配的记录,没有匹配的用NULL代替。
select <表名1,属性名1>...<表名1,属性名n>,
<表名2,属性名1>...<表名2,属性名n>,
from <表名1>
right join <表名2>
on 条件表达式;
select * from
employee right join manage
on e_id = employee_id;
全外连接 full join
全连接的左右表不做限制,显示全部数据,没有匹配的数据用NULL代替。MySQL不支持全连接,使用左连接和右连接加上 union 合并实现全连接。
- 取别名 as
在查询时可以给属性和表取别名,使得读取出的数据更直观,别名只在一条命令中生效。
select <属性名> as <别名> from <表名> as <别名>;
select e.e_id,e.e_name,s.s_grade,s.s_money from
employee as e join salary as s
on e_grade=s_grade;
# as 可以给列名也起别名
select e_name as 姓名,d_name as 部门,s_money as 薪资,m_name as 职能
from employee
inner join department on d_id= e_depart
inner join salaryon s_grade = e_grade
left join management on employee_id = e_id;
- 合并的使用 union [all]
UNION 语句:用于将不同表中相同列中查询的数据展示出来。(不包括重复数据)
ALL: 可选,包含重复数据。
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中
多个 SELECT 语句会删除重复的数据。
select <属性名> from <表名1> union select <属性名> from <表名2>;
MySQL UNION ALL 操作符用于合并多个select语句的结果,要求第一个sql语句返回的列与第二个sql语句返回的列相同。
select <属性名> from <表名> union all select <属性名> from <表名>;
利用左连接和右链接实现全连接
select * from department
left join salary on not null
union
select * from department
right join salary on not null;
还可以这么使用
select 1,'小段' union select 2,'我们还剩下什么';
多表查询
格式:
select <表名1.属性名1,……,表名n.属性名n>
from <表名1>
inner join <表名2> on <条件表达式>
inner join <表名3> on <条件表达式>
left join <表名n> on <条件表达式>
right join
......
select e_name,d_name,s_money
from employee
inner join department on d_id = e_depart
inner join salary on s_grade = e_grade;