用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