创建公司资料库表格
创建员工表格
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
