文章目录
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
SELECT
Name,
City,
Point,
Vip
FROM
userinfo
JOIN pointinfo ON userinfo.NAME = pointinfo.UserName;
查找ID对应的直接上级(如果有)的姓(自连接)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT
t1.ID,
t2.FirstName AS SuperiorFirstName
FROM
employees t1
JOIN employees t2 ON t1.Superior = t2.ID;
查找ID(返回所有ID)对应的直接上级的姓(LEFT JOIN)
哪边要保留全表就往哪个方向连接 employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT
t1.ID,
t2.FirstName AS SuperiorFirstName
FROM
employees t1
LEFT JOIN employees t2 ON t1.Superior = t2.ID;
查找ID对应的工作时间(USING)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
worktimeinfo表
IDWorkTime132331043510
SELECT
employees.ID,
worktimeinfo.WorkTime
FROM
employees
JOIN worktimeinfo USING(ID);
查找各员工的身份(UNION)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT
ID,
FirstName,
LastName,
"主管" AS Identity
FROM
employees
WHERE
Superior IS NULL
UNION
SELECT
ID,
FirstName,
LastName,
"普通员工" AS Identity
FROM
employees
WHERE
Superior 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
WHERE
ID = 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
SELECT
MAX( Salary ),
MIN( Salary ),
AVG( Salary ),
SUM( Salary ),
COUNT( Salary )
FROM
employees;
查找部门总工资(GROUP BY)
一般情况下GROUP BY的字段就是SELECT里面选择的非聚合函数的字段 employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT
Department,
SUM( Salary )
FROM
employees
GROUP BY
Department;
查找总工资大于20000的部门(HAVING)
HAVING就是GROUP BY后的WHERE(条件) employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT
Department,
SUM( Salary ) AS total_salary
FROM
employees
GROUP BY
Department
HAVING
total_salary > 20000;
查找部门工资并且汇总(WITH ROLLUP)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT
Department,
SUM( Salary )
FROM
employees
GROUP BY
Department WITH ROLLUP;
查询工作时间为3年的员工薪水(IN)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
worktimeinfo表
IDWorkTime132331043510
SELECT
Salary
FROM
employees
WHERE
ID IN ( SELECT ID FROM worktimeinfo WHERE WorkTime = 3 );
查找比所有姓张的员工工资高的工资(ALL)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT
Salary
FROM
employees
WHERE
Salary > ALL ( SELECT Salary FROM employees WHERE FirstName LIKE "张%" );
查找各部门大于部门平均工资的员工ID(相关子查询)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
SELECT
t1.ID
FROM
employees t1
WHERE
t1.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
SELECT
Salary
FROM
employees
WHERE
EXISTS ( 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
SELECT
FirstName,
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
SELECT
ID,
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随机浮点数"
FROM
productprice
WHERE
ID = 1;
字符串处理(LENGTH UPPER LOWER LTRIM TRIM LEFT SUBSTRING LOCATE REPLACE CONCAT)
SELECT
LENGTH( "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)
SELECT
NOW( ) 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)
SELECT
NOW( ) 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
SELECT
ID,
CASE
WHEN Salary >= 8000 THEN "高工资"
WHEN Salary >= 5000 AND Salary < 8000 THEN "中工资"
WHEN Salary < 5000 THEN "普通工资"
END
AS "工资级别",
Salary
FROM
employees;
5.视图、存储过程、触发器和事务
创建视图(CREATE VIEW)
employees表
IDFirstNameLastNameSuperiorDepartmentSalary1张三3部门150002李四5部门235003王五部门1100004张二3部门170005赵六部门212000
worktimeinfo表
IDWorkTime132331043510
CREATE VIEW work_time_info AS
SELECT
employees.ID,
employees.FirstName,
worktimeinfo.WorkTime
FROM
employees
JOIN worktimeinfo USING(ID);
删除或更改视图(DELETE REPLACE)
CREATE OR REPLACE VIEW work_time_info AS
SELECT
employees.ID,
employees.FirstName,
worktimeinfo.WorkTime
FROM
employees
JOIN worktimeinfo USING(ID);
DROP VIEW work_time_info;
对视图进行操作时不允许记录消失(WITH CHECK OPTION)
避免因为修改后记录不符合某一条件而被从视图中移除的情况,可以在创建视图时添加WITH CHECK OPTION
CREATE VIEW work_time_info AS
SELECT
employees.ID,
employees.FirstName,
worktimeinfo.WorkTime
FROM
employees
JOIN 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()
BEGIN
SELECT * 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))
BEGIN
SELECT * 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))
BEGIN
IF 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)
BEGIN
IF time <= 0
THEN SIGNAL SQLSTATE "22003"
SET 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)
BEGIN
SELECT 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
BEGIN
DECLARE 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_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
UPDATE 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 SCHEDULE
EVERY 1 YEAR STARTS "2023-01-01"
DO BEGIN
UPDATE 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 SCHEDULE
EVERY 1 YEAR STARTS "2023-01-01"
DO BEGIN
UPDATE 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
好文链接
发表评论