企业网站推广的好处,成都网站建设外贸,官方网站建设方案图,备案二级域名文章目录 1.从简单的查询开始查找所有记录(SELECT *)查找记录中的所有登录名(SELECT)查找登录名为admin的密码(WHERE)查找电话号码非空的记录(IS NOT NULL)查找所在城市为北京或者用户名字是李四的记录(OR)查找所在城市为北京并且用户名字是张三的记录(AND)查找用户名字是李四或… 文章目录 1.从简单的查询开始查找所有记录(SELECT *)查找记录中的所有登录名(SELECT)查找登录名为admin的密码(WHERE)查找电话号码非空的记录(IS NOT NULL)查找所在城市为北京或者用户名字是李四的记录(OR)查找所在城市为北京并且用户名字是张三的记录(AND)查找用户名字是李四或者王五的记录(IN)查找注册时间在2022年到2023年之间的记录(BETWEEN)查找姓张的用户记录(LIKE)查找所有记录并根据积分排序(ORDER BY)查找所有记录并根据积分降序排序(DESC)查找前两条记录(LIMIT)查找第两条记录(LIMIT a,b) 2.表的连接查找用户所在城市、积分、是否是VIP(JOIN)查找ID对应的直接上级(如果有)的姓(自连接)查找ID(返回所有ID)对应的直接上级的姓(LEFT JOIN)查找ID对应的工作时间(USING)查找各员工的身份(UNION) 3.增加、修改和删除添加一个测试用户(INSERT INTO)获取上一个插入数据的ID(LAST_INSERT_ID)创建employees的表复制(CREATE TABLE)更新表中ID为6的员工信息(UPDATE)删除员工ID为6的记录(DELETE) 4.复杂查询聚合函数(MAX MIN AVG SUM COUNT)查找部门总工资(GROUP BY)查找总工资大于20000的部门(HAVING)查找部门工资并且汇总(WITH ROLLUP)查询工作时间为3年的员工薪水(IN)查找比所有姓张的员工工资高的工资(ALL)查找各部门大于部门平均工资的员工ID(相关子查询)查询工作时间为3年的员工薪水(EXISTS)查询ID不为1的员工工资、姓、以及平均工资(SELECT和FROM中的子查询)调整商品价格(ROUND TRUNCATE CEILING FLOOR ABS RAND)字符串处理(LENGTH UPPER LOWER LTRIM TRIM LEFT SUBSTRING LOCATE REPLACE CONCAT)日期处理(NOW CURRENT_DATE CURRENT_TIME YEAR EXTRACT)日期计算(DATE_FORMAT DATE_ADD DATE_SUB DATEDIFF TIME_TO_SEC)查找各ID员工和上级,标出无上级的员工(IFNULL)查找各ID员工和上级,标出无上级的员工部门(COALESCE)查找各ID员工工资水平(IF)查找各ID员工工资水平(CASE) 5.视图、存储过程、触发器和事务创建视图(CREATE VIEW)删除或更改视图(DELETE REPLACE)对视图进行操作时不允许记录消失(WITH CHECK OPTION)创建并调用查询员工信息的存储过程(CREATE PROCEDURE、CALL)删除(如果存在)查询员工信息的存储过程(DROP PROCEDURE)查找指定部门的员工信息(带参存储过程)查找指定部门的员工信息(存储过程默认参数)更新ID为5的员工工作时间(存储过程参数验证)查找指定ID的员工工资(存储过程输出参数)将员工工资换算为美元(函数 FUNCTION)计算公司所发月工资(触发器TRIGGER)删除计算月工资的触发器(DROP TRIGGER)创建员工工龄记录事件(事件EVENT)查看、删除和更改事件加入新员工后更新月总工资(事务TRANSACTION) 1.从简单的查询开始
查找所有记录(SELECT *)
login表
userNamepasswordadmin123456user123
SELECT * FROM login;查找记录中的所有登录名(SELECT)
login表
userNamepasswordadmin123456user123
SELECT userName FROM login;查找登录名为admin的密码(WHERE)
login表
userNamepasswordadmin123456user123
SELECT password FROM login WHERE userName admin;查找电话号码非空的记录(IS NOT NULL)
userinfo 表
NameCityPhoneTime张三北京1234567892023-11-19 21:13:21李四上海2022-03-26 11:30:02王五深圳1111111112020-01-22 09:50:13
SELECT * FROM userinfo WHERE Phone IS NOT NULL;查找所在城市为北京或者用户名字是李四的记录(OR)
userinfo 表
NameCityPhoneTime张三北京1234567892023-11-19 21:13:21李四上海2022-03-26 11:30:02王五深圳1111111112020-01-22 09:50:13
SELECT * FROM userinfo WHERE City 北京 OR Name 李四;查找所在城市为北京并且用户名字是张三的记录(AND)
userinfo 表
NameCityPhoneTime张三北京1234567892023-11-19 21:13:21李四上海2022-03-26 11:30:02王五深圳1111111112020-01-22 09:50:13张二北京2222023-11-24 20:47:16
SELECT * FROM userinfo WHERE City 北京 AND Name 张三;查找用户名字是李四或者王五的记录(IN)
userinfo 表
NameCityPhoneTime张三北京1234567892023-11-19 21:13:21李四上海2022-03-26 11:30:02王五深圳1111111112020-01-22 09:50:13张二北京2222023-11-24 20:47:16
SELECT * FROM userinfo WHERE Name IN (李四, 王五);查找注册时间在2022年到2023年之间的记录(BETWEEN)
userinfo 表
NameCityPhoneTime张三北京1234567892023-11-19 21:13:21李四上海2022-03-26 11:30:02王五深圳1111111112020-01-22 09:50:13张二北京2222023-11-24 20:47:16
SELECT * FROM userinfo WHERE Time BETWEEN 2022-01-01 00:00:00 AND 2023-12-31 23:59:59;查找姓张的用户记录(LIKE)
userinfo 表
NameCityPhoneTime张三北京1234567892023-11-19 21:13:21李四上海2022-03-26 11:30:02王五深圳1111111112020-01-22 09:50:13张二北京2222023-11-24 20:47:16
SELECT * FROM userinfo WHERE Name LIKE 张%;查找所有记录并根据积分排序(ORDER BY)
pointinfo表
UserNamePointVip张三101李四23000王五1001张二5010
SELECT * FROM pointinfo ORDER BY Point;查找所有记录并根据积分降序排序(DESC)
pointinfo表
UserNamePointVip张三101李四23000王五1001张二5010
SELECT * FROM pointinfo ORDER BY Point DESC;查找前两条记录(LIMIT)
pointinfo表
UserNamePointVip张三101李四23000王五1001张二5010
SELECT * FROM pointinfo LIMIT 2;查找第两条记录(LIMIT a,b)
pointinfo表
UserNamePointVip张三101李四23000王五1001张二5010
SELECT * FROM pointinfo LIMIT 1,1;2.表的连接
查找用户所在城市、积分、是否是VIP(JOIN)
userinfo 表
NameCityPhoneTime张三北京1234567892023-11-19 21:13:21李四上海2022-03-26 11:30:02王五深圳1111111112020-01-22 09:50:13张二北京2222023-11-24 20:47:16
pointinfo表
UserNamePointVip张三101李四23000王五1001张二5010
SELECTName, City,Point,Vip
FROMuserinfoJOIN pointinfo ON userinfo.NAME pointinfo.UserName;查找ID对应的直接上级(如果有)的姓(自连接)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTt1.ID,t2.FirstName AS SuperiorFirstName
FROMemployees t1JOIN employees t2 ON t1.Superior t2.ID;查找ID(返回所有ID)对应的直接上级的姓(LEFT JOIN)
哪边要保留全表就往哪个方向连接 employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTt1.ID,t2.FirstName AS SuperiorFirstName
FROMemployees t1LEFT JOIN employees t2 ON t1.Superior t2.ID;查找ID对应的工作时间(USING)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
worktimeinfo表
IDWorkTime132331043510
SELECTemployees.ID,worktimeinfo.WorkTime
FROMemployeesJOIN worktimeinfo USING(ID);查找各员工的身份(UNION)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTID,FirstName,LastName,主管 AS Identity
FROMemployees
WHERESuperior IS NULL UNION
SELECTID,FirstName,LastName,普通员工 AS Identity
FROMemployees
WHERESuperior IS NOT NULL;3.增加、修改和删除
添加一个测试用户(INSERT INTO)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
INSERT INTO employees ( FirstName, LastName, Department, Salary )
VALUES( 赵, 一, 测试部门, 3000 ); 获取上一个插入数据的ID(LAST_INSERT_ID)
在执行了上面的INSERT语句后使用,需要设置主键和自动递增
SELECT LAST_INSERT_ID();创建employees的表复制(CREATE TABLE)
CREATE TABLE employees_backup AS
SELECT * FROM employees;更新表中ID为6的员工信息(UPDATE)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门2120006赵一测试部门3000
UPDATE employees
SET Department 部门1,Superior 3
WHEREID 6;删除员工ID为6的记录(DELETE)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门2120006赵一3部门13000
DELETE FROM employees WHERE ID 6;4.复杂查询
聚合函数(MAX MIN AVG SUM COUNT)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTMAX( Salary ),MIN( Salary ),AVG( Salary ),SUM( Salary ),COUNT( Salary )
FROMemployees;查找部门总工资(GROUP BY)
一般情况下GROUP BY的字段就是SELECT里面选择的非聚合函数的字段 employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTDepartment,SUM( Salary )
FROMemployees
GROUP BYDepartment;查找总工资大于20000的部门(HAVING)
HAVING就是GROUP BY后的WHERE(条件) employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTDepartment,SUM( Salary ) AS total_salary
FROMemployees
GROUP BYDepartment
HAVINGtotal_salary 20000;查找部门工资并且汇总(WITH ROLLUP)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTDepartment,SUM( Salary )
FROMemployees
GROUP BYDepartment WITH ROLLUP;查询工作时间为3年的员工薪水(IN)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
worktimeinfo表
IDWorkTime132331043510
SELECTSalary
FROMemployees
WHEREID IN ( SELECT ID FROM worktimeinfo WHERE WorkTime 3 );查找比所有姓张的员工工资高的工资(ALL)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTSalary
FROMemployees
WHERESalary ALL ( SELECT Salary FROM employees WHERE FirstName LIKE 张% );查找各部门大于部门平均工资的员工ID(相关子查询)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTt1.ID
FROMemployees t1
WHEREt1.Salary ( SELECT AVG( Salary ) FROM employees t2 GROUP BY Department HAVING t1.Department t2.Department
);查询工作时间为3年的员工薪水(EXISTS)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
worktimeinfo表
IDWorkTime132331043510
SELECTSalary
FROMemployees
WHEREEXISTS ( SELECT ID FROM worktimeinfo WHERE worktimeinfo.ID employees.ID AND WorkTime 3 );查询ID不为1的员工工资、姓、以及平均工资(SELECT和FROM中的子查询)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTFirstName,Salary,( SELECT AVG( Salary ) FROM employees ) AS Average
FROM( SELECT FirstName, Salary FROM employees WHERE ID 1 ) AS select_table;调整商品价格(ROUND TRUNCATE CEILING FLOOR ABS RAND)
productprice 表
IDNamePrice1炒饭10.36
SELECTID,NAME,Price,ROUND( Price ) AS 四舍五入,ROUND( Price, 1 ) AS 四舍五入保留一位小数,TRUNCATE ( Price, 1 ) AS 截断保留一位小数,CEILING( Price ) AS 大于该数的最小整数,FLOOR( Price ) AS 小于该数的最大整数,ABS( Price ) AS 绝对值,RAND( ) AS 0-1随机浮点数
FROMproductprice
WHEREID 1;字符串处理(LENGTH UPPER LOWER LTRIM TRIM LEFT SUBSTRING LOCATE REPLACE CONCAT)
SELECTLENGTH( test ) AS 字符数,UPPER( test ) AS 大写,LOWER( TEST ) AS 小写,LTRIM( test ) AS 左边去掉空格,TRIM( test ) AS 去掉空格,LEFT ( test, 2 ) AS 前2个字符,SUBSTRING( test, 2, 2 ) AS 位置2长度2的子字符串,LOCATE( st, test ) AS 匹配字符串的位置,REPLACE ( test, st, xt ) AS 字符串替换,CONCAT( FirstName, , LastName ) AS 字符串拼接;日期处理(NOW CURRENT_DATE CURRENT_TIME YEAR EXTRACT)
SELECTNOW( ) AS 当前日期时间,CURRENT_DATE ( ) AS 当前年份,CURRENT_TIME ( ) AS 当前时间,YEAR ( NOW( ) ) AS 获取年份,EXTRACT( MONTH FROM NOW( ) ) AS 从当前格式中提取月份;日期计算(DATE_FORMAT DATE_ADD DATE_SUB DATEDIFF TIME_TO_SEC)
SELECTNOW( ) AS 当前时间,DATE_FORMAT( NOW( ), %Y %m %d ) AS 格式化日期,DATE_ADD( NOW( ), INTERVAL 1 HOUR ) AS 增加一小时,DATE_SUB( NOW( ), INTERVAL 1 HOUR ) AS 减少一小时,DATEDIFF( 2023-12-1, 2023-12-3 ) AS 日期间隔,TIME_TO_SEC( 22:25 ) - TIME_TO_SEC( 22:00 ) AS 时间间隔(s);查找各ID员工和上级,标出无上级的员工(IFNULL)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT ID, IFNULL(Superior, 无上级) AS 上级ID FROM employees;查找各ID员工和上级,标出无上级的员工部门(COALESCE)
从前往后返回非空的查找值 employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT ID, COALESCE(Superior, Department, 无上级) AS 上级ID或部门 FROM employees;查找各ID员工工资水平(IF)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT ID, IF(Salary 7000, 高工资, 普通工资) AS 工资级别, Salary FROM employees;查找各ID员工工资水平(CASE)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECTID,
CASEWHEN Salary 8000 THEN 高工资 WHEN Salary 5000 AND Salary 8000 THEN 中工资 WHEN Salary 5000 THEN 普通工资
END AS 工资级别,Salary
FROMemployees;5.视图、存储过程、触发器和事务
创建视图(CREATE VIEW)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
worktimeinfo表
IDWorkTime132331043510
CREATE VIEW work_time_info AS
SELECTemployees.ID,employees.FirstName,worktimeinfo.WorkTime
FROMemployeesJOIN worktimeinfo USING(ID);删除或更改视图(DELETE REPLACE)
CREATE OR REPLACE VIEW work_time_info AS
SELECTemployees.ID,employees.FirstName,worktimeinfo.WorkTime
FROMemployeesJOIN worktimeinfo USING(ID);DROP VIEW work_time_info;对视图进行操作时不允许记录消失(WITH CHECK OPTION)
避免因为修改后记录不符合某一条件而被从视图中移除的情况,可以在创建视图时添加WITH CHECK OPTION
CREATE VIEW work_time_info AS
SELECTemployees.ID,employees.FirstName,worktimeinfo.WorkTime
FROMemployeesJOIN worktimeinfo USING(ID)
WITH CHECK OPTION;创建并调用查询员工信息的存储过程(CREATE PROCEDURE、CALL)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
DELIMITER:修改分隔符(通常使用$$,可以改成其他的),目的是把CREATE到END的整个语句视为一体,最后把分隔符再改回分号
DELIMITER $$
CREATE PROCEDURE get_employee()
BEGINSELECT * FROM employees;
END$$
DELIMITER ;调用
CALL get_employee();删除(如果存在)查询员工信息的存储过程(DROP PROCEDURE)
DROP PROCEDURE IF EXISTS get_employee;查找指定部门的员工信息(带参存储过程)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
带参的存储过程:CHAR(3)表示有三个字符的字符串,名字为DepartmentName
DELIMITER $$
CREATE PROCEDURE get_employee_from_Department(DepartmentName CHAR(3))
BEGINSELECT * FROM employees WHERE Department DepartmentName;
END$$
DELIMITER ;CALL get_employee_from_Department(部门1);查找指定部门的员工信息(存储过程默认参数)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
带参的存储过程:CHAR(3)表示有三个字符的字符串,名字为DepartmentName
DELIMITER $$
CREATE PROCEDURE get_employee_from_Department(DepartmentName CHAR(3))
BEGINIF DepartmentName IS NULL THEN SET DepartmentName 部门2;END IF;SELECT * FROM employees WHERE Department DepartmentName;
END$$
DELIMITER ;即使有默认参数,在调用的时候也需要传入空值,否则会报错.
CALL get_employee_from_Department(NULL);更新ID为5的员工工作时间(存储过程参数验证)
worktimeinfo表
IDWorkTime132331043510
带参的存储过程:CHAR(3)表示有三个字符的字符串,名字为DepartmentName
DELIMITER $$
CREATE PROCEDURE update_work_time(time INT)
BEGINIF time 0 THEN SIGNAL SQLSTATE 22003SET MESSAGE_TEXT 错误的工作时间;END IF;UPDATE worktimeinfo SET WorkTime time WHERE ID 5;
END$$
DELIMITER ;SQLSTATE各种数字代表的具体意义可自行搜索,在这里22003表示数值异常
CALL update_work_time(-2);查找指定ID的员工工资(存储过程输出参数)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
参数中的OUT表示这是输出参数,需要传入变量
DELIMITER $$
CREATE PROCEDURE get_salary_from_employee(ID INT, OUT find_salary INT)
BEGINSELECT Salary INTO find_salary FROM employees t1 WHERE t1.ID ID;
END$$
DELIMITER ;注意此处变量find_salary 需要加前缀
SET find_salary 0;
CALL get_salary_from_employee(3, find_salary);
SELECT find_salary AS 工资;将员工工资换算为美元(函数 FUNCTION)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
READS SQL DATA表示函数选项:函数体中包含SELECT查询语句但不包含更新语句 DECLARE:在函数中定义一个变量.DEFAULT是默认值
CREATE FUNCTION dollar_salary(ID INT)
RETURNS DECIMAL(10, 2)
READS SQL DATA
BEGINDECLARE dollar DECIMAL(10, 2) DEFAULT 0;DECLARE iSalary INT;SELECT Salary INTO iSalary FROM employees t1 WHERE t1.ID ID;SET dollar iSalary / 7;RETURN dollar;
END
创建完函数就可以当成函数来用了
SELECT ID, Salary, dollar_salary(ID) AS 美元工资 FROM employees;计算公司所发月工资(触发器TRIGGER)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
salaryinfo表
TotalSalary37500
NEW指更新后行的数据,OLD指更新前行的数据,可以用来取其中的字段
DELIMITER $$
CREATE TRIGGER totalSalary_after_updateAFTER UPDATE ON employeesFOR EACH ROW
BEGINUPDATE salaryinfo SET TotalSalary TotalSalary NEW.Salary - OLD.Salary;
END$$
DELIMITER ;UPDATE employees SET Salary 8000 WHERE ID 1;我们再把数据改回来
UPDATE employees SET Salary 5000 WHERE ID 1;创建好的触发器是无法直接看到的,我们可以使用命令查看,大概是这样
SHOW TRIGGERS;查找特定名称的触发器,information_schema是触发器所在的数据库的数据表triggers ,触发器都存储在里面,触发器使用特定命名方式可以方便地查找表或者操作对应的触发器
SELECT * FROM information_schema.triggers WHERE trigger_name LIKE totalSalary%;删除计算月工资的触发器(DROP TRIGGER)
DROP TRIGGER IF EXISTS totalSalary_after_update;创建员工工龄记录事件(事件EVENT)
查看事件管理器是否打开,只有打开才能相应事件
SHOW VARIABLES LIKE event%;这里自动关闭了,把它打开
SET GLOBAL event_scheduler ON;worktimeinfo表
IDWorkTime132331043510
EVERY指多次执行,单次执行用AT
DELIMITER $$
CREATE EVENT yearly_update_worktimeinfo
ON SCHEDULEEVERY 1 YEAR STARTS 2023-01-01
DO BEGINUPDATE worktimeinfo SET WorkTIme WorkTIme 1;
END$$
DELIMITER ;查看、删除和更改事件
SHOW EVENTS;
#或者筛选特定名字的事件
SHOW EVENTS LIKE yearly%;删除事件
DROP EVENT IF EXISTS yearly_update_worktimeinfo;更改事件,使用ALTER代替CREATE即可
DELIMITER $$
ALTER EVENT yearly_update_worktimeinfo
ON SCHEDULEEVERY 1 YEAR STARTS 2023-01-01
DO BEGINUPDATE worktimeinfo SET WorkTIme WorkTIme - 1;
END$$
DELIMITER ;禁用或启用事件
ALTER EVENT yearly_update_worktimeinfo DISABLE;ALTER EVENT yearly_update_worktimeinfo ENABLE;加入新员工后更新月总工资(事务TRANSACTION)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
salaryinfo表
TotalSalary37500
注意表结构,此时插入数据失败,salaryinfo表也不会更新
START TRANSACTION;
UPDATE salaryinfo SET TotalSalary TotalSalary 8000;
INSERT INTO employees (ID, FirstName, LastName, Salary) VALUES(2, 赵, 四, 8000);
COMMIT;仍然是37500
