#删除年龄比肖梅大的学生信息
DELETE FROM student WHERE borndate<(SELECT borndate FROM student WHERE `name`='肖梅');
#查询s1的学生信息
SELECT * FROM student WHERE gradeid IN(SELECT gradeid FROM grade WHERE gradename='s1');
#查询s1和s2的学生信息
SELECT * FROM student WHERE gradeid IN(SELECT gradeid FROM grade WHERE gradename='s1' OR gradename='s2')
#查询姓张的学生信息
SELECT * FROM student WHERE `name` LIKE'张%';
#查询海淀区的学生
SELECT * FROM student WHERE address LIKE '%海淀区%';
#查询除北京以外的学生
SELECT * FROM student WHERE address NOT LIKE '%北京%';
#查询手机号包含数组9的学生
SELECT * FROM student WHERE phone LIKE '%9%';
SELECT * FROM student WHERE EXISTS(SELECT gradeid FROM grade WHERE gradename='s1' OR gradename='s2')
#模拟银行转账的功能
CREATE TABLE bank(
`name` VARCHAR(20) PRIMARY KEY,
money DOUBLE NOT NULL
)
#添加测试数据
INSERT INTO bank VALUES('ww',1000),('wy',200);
UPDATE bank SET money=money-100 WHERE `name`='wy';
#转账
BEGIN;
UPDATE bank SET money=money-500 WHERE `name`='ww';
UPDATE bank SET mony=mony+200 WHERE `name`='wy';
#提交事务
COMMIT;
#撤销事务
ROLLBACK;
SHOW VARIABLES;
#关闭数据库的自动提交功能
SET autocommit=0;
SELECT * FROM bank;
本文地址:https://blog.csdn.net/qq_45562888/article/details/107320437
如对本文有疑问, 点击进行留言回复!!
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
网友评论