Mysql实训作业1

#删除数据库

drop database if exists company;

#创建数据库
create database if not exists company;

#使用数据库

use company;
#创建数据表

create table empinfo(
  id int auto_increment primary key comment'序号',
    eno char(6) not null comment'工号',
    name varchar(10) not null comment'姓名',
    sex enum('男','女')comment'性别',
    age tinyint comment'年龄',
    phone char(11)comment'手机号',
  email varchar(30)comment'邮箱',
  departid char(5)comment'部门编号',
  address varchar(20)comment'住址'
)engine innodb charset gbk;
#插入数据
INSERT INTO empinfo VALUES
(NULL,'hp0003','何超', 1,41,'18231117678','c.zuimxyrmxg@prs.biz','dt001','怀水市'),
(NULL,'hp0004','李娜', 1,45,'13296234901','h.sidgqhje@vbzrk.jo','dt002','无锡市'),
(NULL,'hp0005','田磊', 1,27,'1350222431','e.vwjbdc@uqfgflyir.se','dt001','丽江市'),
(NULL,'hp0006','李明', 1,37,'1892177575','b.zrhy@pxmqbrwb.ae','dt004','徐州市'),
(NULL,'hp0007','田志勇', 1,36,'1864426347','w.hym@tuaexjm.gm','dt004','杭州市'),
(NULL,'hp0008','江艳丽', '女',33,'1514846431','n.cjzyke@hlld.bw','dt003','凉山彝族自治州'),
(NULL,'hp0009','郭丽丽', '女',42,'1511964862','m.yowoj@hxxkua.gw','dt002','迪庆土家族自治州'),
(NULL,'hp0001','傅超锋', '女',30,'1867537913','a.gijvfh@xnjv.lv','dt005','靖远市');

###2.2

create table class(
  pid int auto_increment primary key comment'序号',
    pno char(5) comment'部门编号',
    pname varchar(10) comment'部门名称'
)engine innodb charset utf8;
##插入
insert into class values
(null,'dt001','销售部'),
(null,'dt001','采购部'),
(null,'dt001','财务部'),
(null,'dt001','人事部'),
(null,'dt001','技术部');
###2.3
create table salary(
  sid int auto_increment primary key comment'序号',
    pno char(6) comment'工号',
    sdate char(6) comment'工资月份',
    pay double(10,2) comment'工资'
)engine innodb charset utf8;
INSERT INTO salary VALUES
(NULL,'hp002',202009,8187.19),
(NULL,'hp003',202009,13195.68),
(NULL,'hp004',202009,12698.19),
(NULL,'hp001',202008,9497.52),
(NULL,'hp002',202008,10692.84),
(NULL,'hp003',202008,5330.23),
(NULL,'hp004',202008,7951.63),
(NULL,'hp001',202007,9483.90),
(NULL,'hp002',202007,10280.72),
(NULL,'hp003',202007,5205.36),
(NULL,'hp004',202007,9051.26);

###插入
INSERT INTO empinfo VALUES
(NULL,'hp0003','何超', 1,41,'18231117678','c.zuimxyrmxg@prs.biz','dt001','惠州市'),
(NULL,'hp0004','李娜', 1,45,'13296234901','h.sidgqhje@vbzrk.jo','dt002','无锡市'),
(NULL,'hp0005','田磊', 1,27,'1350222431','e.vwjbdc@uqfgflyir.se','dt001','丽江市'),
(NULL,'hp0006','李明', 1,37,'1892177575','b.zrhy@pxmqbrwb.ae','dt004','徐州市'),
(NULL,'hp0007','田志勇', 1,36,'1864426347','w.hym@tuaexjm.gm','dt004','苏州市'),
(NULL,'hp0008','江艳丽', '女',33,'1514846431','n.cjzyke@hlld.bw','dt003','凉山彝族自治州'),
(NULL,'hp0009','郭丽丽', '女',42,'1511964862','m.yowoj@hxxkua.gw','dt002','迪庆藏族自治州'),
(NULL,'hp0001','傅超锋', '女',30,'1867537913','a.gijvfh@xnjv.lv','dt005','清远市');

INSERT INTO class VALUES
(NULL,'dt001','销售部'),
(NULL,'dt002','采购部'),
(NULL,'dt003','财务部'),
(NULL,'dt004','人事部'),
(NULL,'dt005','技术部');

INSERT INTO salary VALUES
(NULL,'hp002',202009,8187.19),
(NULL,'hp003',202009,13195.68),
(NULL,'hp004',202009,12698.19),
(NULL,'hp001',202008,9497.52),
(NULL,'hp002',202008,10692.84),
(NULL,'hp003',202008,5330.23),
(NULL,'hp004',202008,7951.63),
(NULL,'hp001',202007,9483.90),
(NULL,'hp002',202007,10280.72),
(NULL,'hp003',202007,5205.36),
(NULL,'hp004',202007,9051.26);
SELECT Name,phone from empinfo where age < 35 AND sex = '女';
UPDATE empinfo set sex = '女' where Name = '啊';

DELETE from empinfo WHERE eno = 'hp0010';

INSERT into class VALUES (null,'dt007','行政部');

select pno from salary where sdate = 8 AND pay = 0;

SELECT
(select count(*) from empinfo where sex=2) /
(select COUNT(*)  FROM empinfo);