用MySQL创建公司资料库表格

创建公司资料库表格

创建员工表格
creat table `employee`(
    `emp_id` int primary key,
    `name` varchar(20),
    `birth_data` date,
    `gender` varchar(1),
    `salary` int,
    `branch_id`int,
    `sup_id` int
);

creat table `branch`(
    `branch_id` int primary key,
    `branch_name` varchar(20),
    `manager_id`int,
    foreign key (`manager_id`) references `employee`(`emp_id`)on delete set null
);

alter table `employee` add foreign key(`branch_id`)on delete set null;
alter table `employee`
add foreign key(`sup_id`)
on delete set null;
创建客户表格
creat table `client`(
    `client_id` int primary key,
`client_name`varchar(20),
    `phone` varchar(20)
);
在employee和branch两个表格之间创建关联表格works_with
creat table `work_with`(
    `emp_id` int,
    `client_id` int,
    `total_sales`int,
    primary key(`emp_id`,`client_id`),
    foreign key(`emp_id`) references `employee`(`emp_id`) on delete cascade,
    foreign key(`client_id`)references `client`(`client_id`)on delete cascade
);
创建完成，输入数据
update `branch` set `manager_id`=206 where `branch_id`=1; 
insert into `client` values(400,'dog','1305');
.....
取得用户资料
select * from `client`;
select * from `employee` order by `salary` desc;
select * from `employee` order by `salary` desc limit 3;
select `name` from `employee`; 
select distinct `gender` from `employee`;
聚合函数
select count(`sup_id`) from `employee`;
select count(*) from `employee` where `birth_date`>`1970-01-01` and `gender`=`female`;
select avg(`salary`) from `employee`;
sum()总和
max()min()
select `name` from `employee` where `salary`=(select max(`salary`)from `employee`);
wildcards 通配符%
select * from `client` where `phone` like '%335%';like模糊查询
=运算符只有两个字符完全匹配才会返回true精确查询
%代表任意个字符，_代表一个字符

联合查询union结果合并到一列
select `name` from `employee` union select `client_name` from `client`;属性数目要一致
select `emp_id` as `total_id`,`name` as `total_name` from `employee` union select `client_id`,`client_name`from `client`;as重命名

join 连接两个表，左右连接
select * from `employee` join `branch` on `employee`.`emp_id`=`branch`.`manager_id`;

from `employee` left join `branch`
这里`employee`是左表格，不管条件是否成立都会返回左表格数据，但右表格必须成立才返回数据否则返回null
right join同理
子查询
select `name` from `employee` where `emp_id`=(
    select `manager_id`
    from `branch`
    where `branch_name`='研发'
);

select `name` from `employee` where `emp_id` in(
    select `emp_id` from `works_with` where `total_sales`>50000
);

foreign key(`manager_id`) references `employee`(`emp_id`)on delete set null;
如果`emp_id` 被删除了那么manager_id被设为null

foreign key(`emp_id`)references `employee`(`emp_id`) on delete cascade
如果`emp_id` 被删除了那么其相对应的全部内容删掉
primary key的值不能被设置为null

MySQL