MySQL(3)DML详解

2020/7/10 8:54:23 人评论 次浏览 分类:学习教程

一、DML

数据操纵语言(DML)DML 用于插入、修改、删除数据记录,包括如下 SQL 语句:
INSERT:添加数据到数据库中
UPDATE:修改数据库中的数据
DELETE:删除数据库中的数据

1.1 插入数据

1 、语法

INSERT INTO 表名称 VALUES(值 1,值 2,…);
INSERT INTO 表名称 VALUES(值 1,值 2,…),(值 1,值 2,…),…;
INSERT INTO 表名称 (字段 1,字段 2,…) VALUES(值 1,值 2,…);
INSERT INTO 表名称 (字段 1,字段 2,…) VALUES(值 1,值 2,…),(值 1,值 2,…),…;

2 、说明

1、值的顺序、个数与字段列表中字段的顺序、个数一致
(1)如果个数少了就报 Column count doesn’t match value count
(2)如果 VALUES 前面的()中没有列出字段,那么默认就是为表中的所有字段赋值,那么个数与顺序与表结构中字段定义的一致。

2、关于自增长列,默认值列,允许为 NULL 列的赋值
(1)如果字段列表列出了字段名,那么值列表中就要为其赋值,哪怕它是自增长列,有默认值列,可以为 NULL 值的列。

  • InnoDB 表的自动增长列可以手动插入合适的值,但是插入的值如果是 NULL 或者 0,则实际插入的将是自动增长后的值;
  • 如果列声明了“默认约束”那么对应的位置可以赋值具体的值,也可以使用“DEFAULT”,表示使用默认值;
  • 如果列允许了 NULL 值,那么可以为对应的字段可以赋值为具体值也可以赋值为 NULL。

(2)对于没有列出的字段,像自增列就自动赋值,像默认值列就自动赋默认值,像允许 NULL 的列就自动赋 NULL值

3、VALUES 也可以写成 VALUE,但是 VALUES 是标准写法。
4、可以同时插入多行。
5、如果插入从表的数据,要注意查看主表参照字段的值是否存在。
6、值的位置可以是常量值、表达式、函数。

3 、举例

在这里插入图片描述

CREATE TABLE t_department(
	did INT PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(100) NOT NULL,
	description VARCHAR(200),
	manager_id INT
);
INSERT INTO t_department(dname,description) VALUES('教学部','技术培训'),('咨询部','课程咨询服务');
--------------------------------------
CREATE TABLE `t_job` (
	`job_id` INT(11) PRIMARY KEY AUTO_INCREMENT,
	`job_name` VARCHAR(100) DEFAULT NULL,
	`description` VARCHAR(200) DEFAULT NULL
);
INSERT INTO t_job VALUES (NULL,'JavaSE 讲师','Java 基础'),(NULL,'Web 讲师','Web 基础'),(NULL,'JavaEE 框架','框架讲解'),(NULL,'课程顾问','课程咨询');
--------------------------------------
CREATE TABLE t_employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(100) NOT NULL,
	gender CHAR NOT NULL DEFAULT '男',
	card_id CHAR(18) UNIQUE,
	tel CHAR(11),
	job_id INT,
	`mid` INT,
	birthday DATE,
	hiredate DATE,
	address VARCHAR(100),
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES
	t_department(did),
	FOREIGN KEY (job_id) REFERENCES t_job(job_id)
);
INSERT INTO `t_employee`(`eid`,`ename`,`gender`,`card_id`,`tel`,`job_id`,`mid`,`birthday`,`hiredate`,`address`,`dept_id`) VALUES (1,' 孙 红 雷 ',' 男','123456789012345678','12345678901',1,NULL,'1990-01-01','2015-01-01','白庙',1),
(2,' 张 亮 ',' 男','123456789012345677','12345678902',2,NULL,'1990-01-02','2015-01-02','天通苑北',1),
(3,' 鹿 晗 ',' 男','123456789012345676','12345678903',3,NULL,'1990-01-03','2015-01-03','北苑',1),
(4,' 邓 超 ',' 男','123456789012345675','12345678904',2,NULL,'1990-01-04','2015-01-04','和谐家园',1),
(5,' 孙 俪 ',' 女','123456789012345674','12345678905',3,NULL,'1990-01-05','2015-01-05','霍营',1),
(6,'Angelababy',' 女','123456789012345673','12345678906',4,NULL,'1990-01-06','2015-01-06','回龙观',2);
------------------------------------
CREATE TABLE t_salary(
	eid INT PRIMARY KEY,
	basic_salary DECIMAL(10,2),
	performance_salary DECIMAL(10,2),
	commission_pct DECIMAL(10,2),
	deduct_wages DECIMAL(10,2),
	FOREIGN KEY (eid) REFERENCES t_employee(eid)
);
INSERT INTO `t_salary`(`eid`,`basic_salary`,`performance_salary`,`commission_pct`,`deduct_wages`) VALUES (1,'12000.00','6000.00','0.40','0.00'),(2,'9000.00','5000.00','0.20',NULL),
(3,'11000.00','8000.00',NULL,NULL),(4,'13000.00','5000.00',NULL,NULL),
(5,'8000.00','8000.00','0.30',NULL),(6,'15000.00','6000.00',NULL,NULL);

1.2 修改数据

1 、语法

UPDATE 表名称 SET 字段名 1 = 值 1, 字段名 2=值 2,… 【WHERE 条件】;
UPDATE 表 1,表 2,… SET 表 1.字段名 1 = 值 1, 表 1.字段名 2=值 2,表 2.字段 1 = 值 1, 表 2.字段 2=值 2…【WHERE 条件】;

2 、说明

1、如果不写 where 条件,会修改所有行
2、值可以是常量值、表达式、函数
3、可以同时更新多张表
如果两个表没有建立外键,但逻辑上有外键关系
4、如果修改从表外键字段的数据,要注意查看主表参照字段的值是否存在
5、如果修改主表的被参考的字段的值,要注意查看从表的外键是否有依赖该值,如果有
(1)如果外键是 on update RESTRICT 或 on update NO ACTION,那么要先处理从表的数据,才能修改
(2)如果外键是 on update SET NULL 或 on update CASCADE,那么直接修改,从表的外键字段会自动处理

3、举例

#修改所有人的基本工资,涨薪 5%
UPDATE t_salary SET basic_salary = basic_salary * 1.05;

#修改"孙俪"的手机号码为"13709098765",生日为"1982-09-26"
UPDATE t_employee SET tel = '13709098765',birthday = '1982-09-26' WHERE ename = '孙俪';

#修改"邓超"的入职日期为今天
UPDATE t_employee SET hiredate = CURDATE() WHERE ename ='邓超';

#修改"咨询部"的主管 id 为 6
UPDATE t_department SET manager_id =6 WHERE did = 2;

#修改"教学部"的主管 id 为 1
UPDATE t_department SET manager_id =1 WHERE did = 1;

#修改"教学部"的主管 id 为"孙红雷"的编号
UPDATE t_department,t_employee 
SET t_department.manager_id =t_employee.eid
WHERE t_department.`dname` = '教学部'
AND t_department.`did` = t_employee.`dept_id`
AND t_employee.ename = '孙红雷';

#修改所有员工的领导编号为该员工所在部门的主管编号
UPDATE t_employee,t_department
SET t_employee.mid = t_department.manager_id
WHERE t_employee.dept_id = t_department.did;

1.3 删除数据

1 、语法

delete from 表名 【where 条件】;
delete 表 1,表 2,… from 表 1,表 2,… 【where 条件】;

2 、说明

1、如果不加 where 条件,表示删除整张表的数据,表结构保留。
delete from 表名;
删除整张表的数据还可以使用 truncate 表名;

区别:
truncate 相当于删除表再重建一张同名结构的表,操作后得到一张全新表,而 delete 是在原有表中删除数据。如果决定清空一张表的数据,truncate 速度更快一些。
TRUNCATE 语句不能回滚

2、如果删除主表的记录,要注意查看从表的外键是否有依赖该行的值,如果有
(1)如果外键是 on delete RESTRICT 或 on delete NO ACTION,那么要先处理从表的数据,才能删除
(2)如果外键是 on delete SET NULL 或 on delete CASCADE,那么删除时从表的对应记录也会被置空或跟着删除。

3、可以一次删除多个表的数据
例如:两个表没有建立外键,但逻辑上有外键关系,也可以通过删除多个表的数据来实现级联删除

3、举例

#删除学号为 9 的学生信息
DELETE FROM t_stu WHERE sid = 9;
#注意:前提是没有外键或外键是 on delete cascade

#删除所有“教学部”的员工信息和薪资信息和“教学部”部门信息
DELETE t_employee,t_department,t_salary
FROM t_employee,t_department,t_salary
WHERE t_department.`dname` ='教学部'
AND t_employee.`dept_id`=t_department.`did`
AND t_employee.`eid` = t_salary.eid;

1.4 查询

1、语法

SELECT 查询列表
FROM 表名或视图列表
【WHERE 条件表达式】
【GROUP BY 字段名 【HAVING 条件表达式】】
【ORDER BY 字段 【ASC|DESC】】
【LIMIT m,n】;

举例
#查询表中的所有行所有列
#使用*表示,查询所有字段,即查询所有行
select * from t_stu;

#查询部分字段,查询部分列
select sname,major from t_stu;

#查询所有列,部分行
select * from t_stu where major = 'JavaEE';

#查询部分行,部分列
select sname,major from t_stu where major = 'JavaEE';

说明:
(1)如果 SELECT 后面是*,那么表示查询所有字段
(2)SELECT 后面的查询列表,可以是表中的字段,常量值,表达式,函数
(3)查询的结果是一个虚拟的表
(4)select 语句,可以包含 5 种子句:依次是 where、 group by、having、 order by、limit 必须照这
个顺序。

2、别名 AS

语法:AS 别名
说明:
(1)可以给字段取别名、可以给表名取别名
(2)AS 可以省略
(3)如果给字段取别名,如果别名中包含特殊符号,例如“空格”等,建议给别名加上双引号或单引号
(4)如果是给表名取别名,那么不能加双引号或单引号,也不能有特殊符号,例如“空格”等
(5)建议别名简短,见名知意

举例
UPDATE t_department AS d,t_employee AS e
SET d.manager_id =e.eid
WHERE d.dname = '教学部'
AND d.did = e.`dept_id`
AND e.ename = '孙红雷';

#查询员工姓名以及手机号码
SELECT ename AS '员工姓名',tel AS '手机号码'
FROM t_employee;

3 、去重 DISTINCT

#查询员工表的部门编号
SELECT DISTINCT dept_id FROM t_employee;

#统计员工表中员工有几个部门
SELECT COUNT(DISTINCT dept_id) FROM t_employee;

4 、着重号

例如:select name from t_stu;
可以给字段或表名加着重号
如果字段名或表名与关键字一样更要加着重号了


如果有收获!!! 希望老铁们来个三连,点赞、收藏、转发
创作不易,别忘点个赞,可以让更多的人看到这篇文章,顺便鼓励我写出更好的博客

相关资讯

    暂无相关的资讯...

共有访客发表了评论 网友评论

验证码: 看不清楚?
    -->