数据库操作-建表 | lorry

sql

MySql 2018-12-27

1.创建数据库

– 如果存在renliziyuan的数据库就删除它（慎重），之后创建数据库并且进入数据库。

drop database if exists renliziyuan;
create database renliziyuan default charset utf8;
use renliziyuan;

例子：创建人力资源管理系统数据

2.创建部门表

添加属性

drop table if exists tb_emp;
create table tb_dept
(
dno int comment '部门编号',
dname varchar(10) not null comment '部门名称',
dloc varchar(20) not null comment '部门所在地',
primary key (dno)
);

添加部门记录

insert into tb_dept values 
    (10, '会计部', '北京'),
    (20, '研发部', '成都'),
    (30, '销售部', '重庆'),
    (40, '运维部', '深圳');

3.创建员工表

drop table if exists tb_dept;
create table tb_emp
(
eno int comment '员工编号',
ename varchar(20) not null comment '员工姓名',
job varchar(20) not null comment '员工职位',
mgr int comment '主管编号',
sal int not null comment '员工工资',
comm int comment '每月补贴',
dno int comment '所在部门编号',
primary key (eno),
foreign key (dno) references tb_dept (dno)
);

3.添加员工信息

insert into tb_emp values 
    (7800, '张三丰', '总裁', null, 9000, 1200, 20),
    (2056, '乔峰', '分析师', 7800, 5000, 1500, 20),
    (3088, '李莫愁', '设计师', 2056, 3500, 800, 20),
    (3211, '张无忌', '程序员', 2056, 3200, null, 20),
    (3233, '丘处机', '程序员', 2056, 3400, null, 20),
    (3251, '张翠山', '程序员', 2056, 4000, null, 20),
    (5566, '宋远桥', '会计师', 7800, 4000, 1000, 10),
    (5234, '郭靖', '出纳', 5566, 2000, null, 10),
    (3344, '黄蓉', '销售主管', 7800, 3000, 800, 30),
    (1359, '胡一刀', '销售员', 3344, 1800, 200, 30),
    (4466, '苗人凤', '销售员', 3344, 2500, null, 30),
    (3244, '欧阳锋', '程序员', 3088, 3200, null, 20),
    (3577, '杨过', '会计', 5566, 2200, null, 10),
    (3588, '朱九真', '会计', 5566, 2500, null, 10);

练习操作

查询薪资最高的员工姓名和工资

select ename,sal from tb_emp where sal=(select max(sal) from tb_emp)

查询员工的姓名和年薪((月薪+补贴)*12)

select ename,(sal+ifnull(comm,0)*12) as 年薪 from tb_emp order by 年薪 desc;

查询有员工的部门的编号和人数
```
select ename,count(dno) from tb_emp group by dno;
```
- 查询所有部门的名称和人数（当列有二义性的时候需要使用完全限定名）
```
select dname,ifnunll(total,0) from tb_dept as t1 left join (select dno,count(dno) as total from tb_emp group by dno) as t2 on t1.dno=t2.dno order by total desc;
```
- 查询薪资最高的员工(Boss除外)的姓名和工资
- 查询薪水超过平均薪水的员工的姓名和工资
- 查询薪水超过其所在部门平均薪水的员工的姓名、部门编号和工资
- 查询部门中薪水最高的人姓名、工资和所在部门名称
- 查询主管的姓名和职位(集合运算和去重性能很差，开发过程中尽量避免使用)可以使用exists和notexists来代替集合运算和去重