mysql（作业2）

目录

一、创建数据库

二、创建数据表

三、插入表数据 

 四、作业​编辑

五、练习题

---

一、创建数据库

create database company;

二、创建数据表

create table dept(
DEPTNO int comment '部门编号',
    DNAME varchar(14) comment '部门名称',
    LOC varchar(14) comment '部门地点'
);

CREATE TABLE `emp`(
  `EMPNO` int COMMENT '员工编号',
  `ENAME` varchar(10) COMMENT '员工姓名',
  `JOB` varchar(9) COMMENT '员工职位',
  `MGR` int COMMENT '入职日期',
  `salary` double COMMENT '工资',
  `COMM` double COMMENT '奖金',
  `DEPTNO` int  COMMENT '部门编号'
);

 

CREATE TABLE `salgrade`  (
  `GRADE` int  COMMENT '工资等级',
  `LOSAL` int  COMMENT '最低工资',
  `HISAL` int  COMMENT '最高工资'
);

三、插入表数据 

INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');


INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-01-17', 800, NULL, 20),
 (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
 (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'NAMAGER', 7839, '1981-04-02', 2975, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);


INSERT INTO `salgrade` VALUES 
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);

 四、作业

alter table emp change sal salary double;
select * from emp where salary*12 between 20000 and 30000
order by salary desc;
select * from emp where ENAME like '%A%';
select * from emp where ENAME like '%A%E%';
select * from emp where JOB='SALESMAN';
update emp set salary=salary+200 where salary<2000;
select * from emp where COMM is null;
update dept set LOC='xian' where DEPTNO = 40;
select GRADE from salgrade where LOSAL<=2000 and HISAL >=2000;
update emp set HIREDATE = '1982-2-23' where ENAME='MILLER';

五、练习题

1、给goods 表 时间加一天

 update goods set create_time=date_add(create_time,interval 1 day) where good_no =5;

date_add(列名,interval 天数 day)

date_add(create_time, interval 1 year) 

date_add(create_time, interval 1 month)

date_add(create_time, interval 1 day)

date_add(create_time, interval 1 hour)

date_add(create_time, interval 1 minute) 

date_add(create_time, interval 1 week)

2、笛卡尔积

select goods.*, cart.* from goods,cart;

3、火腿肠被谁买了

select name ,good_no,goods_name from goods,cart,account where goods_name ='火腿肠' and cart.goods_no = goods.good_no and account.id = cart.account_id;

4、零食被谁买了

select account.name,category.name 
from category,account,cart,goods
where category.name = '零食' 
and cart.goods_no = goods.good_no 
and account.id = cart.account_id 
and category.no = goods.create_no;

5、 张三买了多少钱的东西

select price * num as 金额 from 
goods,cart,account 
where account.name = '张三' 
and  account.id = cart.account_id 
and cart.goods_no = goods.good_no;

6、全部人总共买了多少钱的东西

select sum(goods.price * cart.num) as 金额 
from goods,cart,account 
where account.id = cart.account_id
and cart.goods_no = goods.good_no;

7、营业额最高的是星期几

select sum(price * num)as 营业额 , dayofweek(goods.create_time) as weekday 
from goods,cart 
where goods.good_no 
and cart.goods_no 
group by goods.create_time
order by 营业额 desc;

在 MySQL 中，可以使用 DAYOFWEEK() 函数将日期时间型转换为星期几。DAYOFWEEK() 函数返回一个数字，表示一周中的第几天，其中 1 表示星期日，2 表示星期一，依此类推，0 表示星期六。

8、张三买东西的时间

 select account.name ,goods.goods_name,cart.goods_no,num,price,goods.create_time ,price * num as 金额 
from goods,cart,account 
where account.name = '张三' 
and  account.id = cart.account_id 
and cart.goods_no = goods.good_no;

 9、利润是多少

select sum((goods.price -goods.cost) * cart.num) as 金额 
from goods,cart,account 
where account.id = cart.account_id 
and cart.goods_no = goods.good_no;

10、商品的利润率

select goods.goods_name,goods.cost,goods.price, (price-cost)/cost*100 
from goods,cart,account 
where account.id = cart.account_id 
and cart.goods_no = goods.good_no;



select goods_name,ROUND(((price-cost)/cost),2) as '利润率' 
from goods order by 利润率 desc;

11、求2023年3月12日前一周销售的商品

select goods_name from goods where create_time<'2023-03-12';