东软培训-Day2

大熙哥 2021年09月07日 164次浏览

商城数据表设计

用户模块

  • neuedu_mall_user

商品模块

  • neuedu_mall_product

分类模块

商城所有分类层级不超过三级,各大电商平台一致

  • neuedu_mall_category

购物车模块

  • neuedu_mall_cart

订单模块

  • 用户和商品多对多,因此两张表。
    neuedu_mall_order
    存储订单条目,一个用户多条订单

订单明细模块

  • neuedu_mall_order_item
  • 存储详细物品内容,一个订单多个物品

数据库操作

  • ddl 数据定义 定义表
  • dql 查询
  • dml insert update delete
  • dcl 数据库控制语言 例如 授权 取消授权
  • tpl 事务处理 rollback commit

规范

  • 关键词全大写,中括号为可写可不写,建议都写,避免引擎猜
  • 更新时间创建时间,使用sql内置函数
  • 单元测试 集成测试 系统测试

TRUNCATE和DELETE区别

  • DELETE是数据操作语言(DML - Data Manipulation Language),操作时原数据会被放到 rollback segment中,可以被回滚;
  • TRUNCATE是数据定义语言(DDL - Data Definition Language),操作时不会进行存储,不能进行回滚。
# delete - 条件删除
DELETE FROM student WHERE id = 1;
# delete - 删除整个表的数据
DELETE FROM student;
#  truncate - 删除整个表的数据
TRUNCATE TABLE student;

事务

  • 也称工作单元,是由一个或多个SQL语句所组成的操作序列,这些SQL语句作为一个完整的工作单元,要么全部执行成功,要么全部执行失败。在数据库中,通过事务来保证数据的一致性。

  • 要么同时执行成功,要么同时执行失败。

  • 一组sql(增删改)要么都执行,要么都不执行。

场景

A有10000元
B有10000元
A向B转账500,是两条更新
update bill set 余额=余额-500 where A;
update bill set 余额=余额+500 where B;
这两条数据必须要么都执行,要么都不执行,如果执行了一半,发生问题,那么执行过的sql要回滚。

事务周期

  • 事务开始 第一条更新sql执行
  • 事务结束 (commit、rollback) TPL DDL DCL(隐式提交)
  • DDL 先执行commit再操作

事务特征

  • 原子性、一致性、隔离性、持久性

事务处理

  • undo 日志 redo日志
  • 提交就redo,回滚就undo
  • begin roolback commit
  • set autocommit = 0 (关闭事务自动提交)自动提交开关
  • 提交之前是自己可看见改变,其他用户看不见
  • 提前之前,别人是无法更改需要等待用户提交(悲观锁)

隐式结束

隐式结束.jpg

设置保存点

如果在一个事务内,想要回滚到指定位置,不是回滚到事务的起始点,可以通过保存点(SAVEPOINT)来实现。

SAVEPOINT savepointname;--定义一个保存点语句;
ROLLBACK TO savepointname;--回滚到指定保存点

注意:如上两条语句不结束事务的执行。

测试代码

SET autocommit =  0

SAVEPOINT  A;
UPDATE emp SET ENAME = 'lisi' WHERE EMPNO = 7369;
SAVEPOINT  B;
UPDATE emp SET ENAME = 'lisi233' WHERE EMPNO = 7369;

SELECT * from emp;
ROLLBACK TO B;

基本select语句作用

  • 选择
  • 投影(SELECT 列1, 列2, 列3 FROM ...,让结果集仅包含指定列。这种操作称为投影查询。)
  • 连接

空值null

任何包含空值的算术表达式后的结果都为空值

select 顺序

  1. 查询中用到的关键词主要包含六个,并且他们的顺序依次为
  • select--from--where--group by--having--order by
  1. 其中select和from是必须的,其他关键词是可选的,这六个关键词的执行顺序
    与sql语句的书写顺序并不是一样的,而是按照下面的顺序来执行
  • from--where--group by--having--select--order by,
  • from:需要从哪个数据表检索数据
  • where:过滤表中数据的条件
  • group by:如何将上面过滤出的数据分组
  • having:对上面已经分组的数据进行过滤的条件
  • select:查看结果集中的哪个列,或列的计算结果
  • order by :按照什么样的顺序来查看返回的数据

like运算符

  • 使用escape标识转义 % _
SELECT * FROM student t where t.name like '%/%' escape '/';

ORDER BY子句

  • 使用ORDER BY子句能对查询结果集进行排序
  • ORDER BY在SELECT之后
  • ASC 升序 DESC 降序

排序规则(以升序为例)

  • 数字升序排列小值在前,大值在后。即按照数字大小顺序由小到大排列。
  • 日期升序排列相对较早的日期在前,较晚的日期在后。
  • 字符升序排列按照字母由小到大的顺序排列。即由A-Z排列;中文升序按照字典顺序排列。
  • 空值在升序排列中排在最前面,在降序排列中排在最后。

作业1

-- 1.向部门表新增一个部门,部门编号为50,部门名称为HR,工作地点为SY。
INSERT INTO dept (dept.deptno,dept.dname,dept.loc) VALUES(50, 'HR', 'SY');
-- 2.向部门表新增一个部门,部门编号为60,部门名称为MARKET。
INSERT INTO dept (dept.deptno,dept.dname) VALUES(60, 'MARKET');
-- 1.向员工表中新增一个员工,员工编号为8888,姓名为BOB,岗位为CLERK,经理为号7788,入职日期为1985-03-03,薪资3000,奖金和部门为空。
INSERT INTO emp (emp.EMPNO,emp.ENAME,emp.JOB,emp.MGR,emp.HIREDATE,emp.SAL)
VALUES (8888,'BOB','CLERK',7788,'1985-03-03',3000)
-- 1.创建emp_back表,拷贝下来即可。
CREATE TABLE emp_back as SELECT * FROM EMP WHERE 1=0
-- 2.把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。
INSERT INTO emp_back SELECT * FROM emp WHERE emp.HIREDATE > '1982-01-01'
-- 1.修改部门20的员工信息,把82年之后入职的员工入职日期向后调整10天
UPDATE emp 
SET emp.HIREDATE = DATE_ADD(emp.HIREDATE,INTERVAL 10 DAY)
WHERE emp.DEPTNO = 20 AND emp.HIREDATE > '1982-01-01'
-- 修改奖金为null的员工,奖金设置为0
UPDATE emp SET emp.COMM = 0 WHERE  ISNULL(emp.COMM)
-- 修改工作地点在NEW YORK或CHICAGO的员工工资,工资增加500
UPDATE emp SET emp.COMM = emp.COMM+500
WHERE  emp.DEPTNO IN (
SELECT DEPTNO FROM dept
WHERE dept.LOC = 'NEW YORK' OR dept.LOC = 'CHICAGO'
)

UPDATE `emp` JOIN `dept`
ON emp.DEPTNO = dept.DEPTNO
SET SAL = SAL+500
WHERE LOC = 'NEW YORK';
-- 删除经理编号为7566的员工记录
DELETE FROM `emp` WHERE MGR = 7566;
-- 删除工作在NEW YORK的员工记录
DELETE FROM `emp` WHERE emp.DEPTNO IN  (SELECT DEPTNO FROM `dept` WHERE LOC = 'NEW YORK');
-- 删除工资大于所在部门平均工资的员工记录
DELETE FROM `emp` WHERE emp.SAL > (select AVG(emp.SAL) from emp)
-- test表为空表,分析如下语句操作后,最后test表的状态。
CREATE TABLE test(
 id INT NOT NULL,
 name VARCHAR(10)
);
BEGIN
INSERT INTO test(id,name) values(1, 'a');
INSERT INTO test(id,name) values(2, 'b');
SAVEPOINT s1;
INSERT INTO test(id,name) values(3, 'c');
INSERT INTO test(id,name) values(4, 'd');
DELETE FROM test WHERE id in (1,3);
ROLLBACK TO s1;
DELETE FROM test WHERE id in (2,4);
COMMIT;
ROLLBACK;
-- 有一个事务,插入两行,打点,插入两行,删除编号1,3行。回滚到打点1,删除表2,4行,最终表只有1行,1,‘a’