DML_数据操纵语言
2024-09-07 13:57:46
DML语法:
insert 注意点:1.在表后可以有括号,表明 所插入的值是哪几列,但是一定要包括所有的not null属性
2.当要为一个表插入的一行数据中有FK,先看FK的值是否存在,
若不存在,则先添加外键所在表中的值,之后再插入
/*
* insert 添加数据
* insert into tableName values('所有数据')
* insert into tableName(部分列名 - 必须包含所有非空列) values('部分列数据')
*/
insert into course values('C01', 'JavaSE', 100);
insert into course values('C02', 'JavaSE', '');
insert into course(courseno, coursename) values('C03', 'Oracle');
--insert into course(courseno) values('C04'); -- wrong
-- char固定长度
insert into school values('S8372222', '中国大学');
insert into school values('S8372223', '中国三美大学');
insert into faculty values('01', '计算机系', 'S8372223');
insert into faculty values('02', '土木工程系', 'S8372223');
insert into faculty values('03', '计算机系', 'S8372222');
insert into faculty values('04', '土木工程系', 'S8372222');
insert into major values('M0001', '软件工程', '01');
insert into major values('M0002', '网络工程', '01');
insert into major values('M0003', '建筑', '02');
insert into major values('M0004', '建筑美学', '02');
insert into major values('M0005', '软件工程', '03');
insert into major values('M0006', '网络工程', '03');
insert into major values('M0007', '建筑', '04');
insert into major values('M0008', '建筑美学', '04');
insert into studentcard values('S87232', '中国大学', '张思思', '11级软件04班');
-- insert添加 FK引入PK的值 有值直接使用 无值先insert数据再插入
insert into student values('S0001', '张思思', '', null, null, '无锡', '', 'M0008', 'S87232');
insert into studentcard values('S87233', '中国大学', '张珊珊', '11级软件04班');
insert into student values('S0002', '张珊珊', '女', 20, null, '北京', '68', 'M0005', 'S87233');
update 注意点:update tableName set 列名 = value, ... where ...
- update tableName set 列名 = value, ... where ...
-- 部分数据更新 where条件
update student set age = 20;
-- 某一条 PK UK
select * from student; -- PK UK
update student set name = '张三散', sex = '男' where stuNo = 'S9999';
select * from course;
update course set hour = 200 where courseno = 'C05';
用户登录
update users set password = '123456' where userName = 'admin';
update users set info = 'newInfo' where userName = 'admin';
中国大学 - 中国社会大学
update school set schoolName = '中国社会大学' where schoolname = '中国大学';
-- 某几条数据 FK
-- 教学部 所有员工 奖金+200
update employee set bonus = bonus + 200
where deptNo = (select deptNo from dept where deptName = '教学部');
-- 软件工程 专业学生 总+10
update student set score = score + 10
where majorno in (select majorNo from major where name = '软件工程');
-- 计算机部 学生总分 + 5
update student set score = score + 5
where unionno = (select unionno from studentunion where unionname = '计算机部');
-- 所有员工工资 + 500
update employee set salary = salary + 500;
delete 注意点:1.当只想删除一条记录时,where后的条件属性必须是FK 或者 UK
2.当一张表中有FK时,有两种方案可以删除(按具体情况使用):
a. 同时删除(例:删帖子,那么帖子下的回复内容自然同时删除了)
b.先更新再删除
delete from tableName where ...
-- 删除所有数据
delete from course;
-- 删除某一条数据 PK UK
select * from course;
delete from course where courseno = 'C05';
某员工辞职
delete from employee where empNo = 'empNo';
select * from student;
delete from student where stuNo = 'S9008';
某商品下架
-- 中国大学 计算机系 撤销
-- 删除数据 数据FK被引用
--学生
update student set majorno = 'M0007'
where majorno in (select majorno from major
where facultyno =(select facultyno from faculty
where fname = '计算机系' and schoolcode
= (select schoolcode from school
where schoolname = '中国大学')));
--专业
delete from major where facultyno = (select facultyno from faculty
where fname = '计算机系' and schoolcode
= (select schoolcode from school
where schoolname = '中国大学'));
--院系
delete from faculty where fname = '计算机系'
and schoolcode = (select schoolcode from school
where schoolname = '中国大学');
-- 同时删除
课程C01 JavaSE 100 开设有问题
delete from studentcourse where courseno = 'C01';
delete from course where courseno = 'C01';
BBS论坛
帖子 - 回复 帖子内容有严重问题
delete from reply where postId = '0001';
delete from post where postId = '0001';
-- 更新 再删除
-- 大学毕业 学生证失效
update student set cardno = null;
delete from studentcard;
最新文章
- bug注意事项记录
- 腾讯云>;>;云通信>;>;TLS后台API在mac上JAVA DEMO搭建
- gcc 和g++区别
- Python OpenCV —— Arithmetic
- 从题目中学习java语法
- Bootstrap--全局css样式之表单
- OpenStack G版以后的Availability Zone与Aggregate Hosts
- django-celery提供给顾客使用实例
- 大数据学习系列之二 ----- HBase环境搭建(单机)
- eclipse使用javaFX写一个HelloWorkld
- maven生成项目慢解决办法
- python 模块 wmi 远程连接 windows 获取配置信息
- 49.字符串转int
- byte &; 0xff char 转换
- Android-TabLayout设置内容宽度以及下划线宽度
- 自定义Team Foundation Server (TFS) 与Project Professional的集成字段
- 编程开发之--java多线程学习总结(1)问题引入与概念叙述
- openstack架构设计(一)
- 使用location.hash保存页面状态
- Appium+python自动化29-toast消息(亲测 ok)