DML(Data Manipulation Language)

1.插入数据

语法：

INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2,…)

INSERT INTO stus(sid, sname,age,gender) VALUES(‘s_1001’, ‘zhangSan’, 23, ‘male’);

INSERT INTO stus(sid, sname) VALUES(‘s_1001’, ‘zhangSan’);

语法：

INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：

INSERT INTO stus VALUES(‘s_1002’, ‘liSi’, 32, ‘female’);

注意：所有字符串数据和日期型数据必须使用单引号

可以把插入语句和一个查询语句结合起来

— 复制EMP表的结构,创建新表EMP_BAK;CREATE TABLE EMP_BAK LIKE EMP;— 从EMP表中查询数据插入到EMP_BAK表中INSERT INTO EMP_BAKSELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

 

— 复制EMP表的结构,创建新表EMP_BAK;CREATE TABLE EMP_BAK LIKE EMP;— 从EMP表中查询数据插入到EMP_BAK表中INSERT INTO EMP_BAKSELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

 

— 复制EMP表的结构,创建新表EMP_BAK;CREATE TABLE EMP_BAK LIKE EMP;— 从EMP表中查询数据插入到EMP_BAK表中INSERT INTO EMP_BAKSELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

 

— 复制EMP表的结构,创建新表EMP_BAK;CREATE TABLE EMP_BAK LIKE EMP;— 从EMP表中查询数据插入到EMP_BAK表中INSERT INTO EMP_BAKSELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

 

— 复制EMP表的结构,创建新表EMP_BAK;CREATE TABLE EMP_BAK LIKE EMP;— 从EMP表中查询数据插入到EMP_BAK表中INSERT INTO EMP_BAKSELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

 

— 复制EMP表的结构,创建新表EMP_BAK;CREATE TABLE EMP_BAK LIKE EMP;— 从EMP表中查询数据插入到EMP_BAK表中INSERT INTO EMP_BAKSELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

2.修改数据

单表修改语法：

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

UPDATE stus SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;

UPDATE stus SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;

UPDATE stus SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;

UPDATE stus SET gender=’female’ WHERE gender IS NULLUPDATE stus SET age=age+1 WHERE sname=’zhaoLiu’;

 

UPDATE stus SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;

UPDATE stus SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;

UPDATE stus SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;

UPDATE stus SET gender=’female’ WHERE gender IS NULLUPDATE stus SET age=age+1 WHERE sname=’zhaoLiu’;

多表修改语法：

UPDATE 表1 【inner】 john 表2 on 表 SET 列名1= 新值1,列名2 =新值2 【where 筛选条件】

UPDATE EMP EINNER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)SET E.ENAME= ‘张三’ , D.DNAME=’开发部’WHERE E.EMPNO=7499;

 

UPDATE EMP EINNER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)SET E.ENAME= ‘张三’ , D.DNAME=’开发部’WHERE E.EMPNO=7499;

 

UPDATE EMP EINNER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)SET E.ENAME= ‘张三’ , D.DNAME=’开发部’WHERE E.EMPNO=7499;

 

UPDATE EMP EINNER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)SET E.ENAME= ‘张三’ , D.DNAME=’开发部’WHERE E.EMPNO=7499;

3.删除数据

单表删除语法：

①DELETE FROM 表名 [WHERE 条件]

DELETE FROM stus WHERE sid=’s_1001’003B

DELETE FROM stus WHERE sname=’chenQi’ OR age > 30;

DELETE FROM stus;

②语法：

TRUNCATE TABLE 表名

TRUNCATE TABLE stus;

【面试题】二者的区别：

• 虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！
• TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的.

多表删除语法：

DELETE 别名1 FROM 表1 别名1 [INNER] JOIN 表2 别名2

on 连接条件

【AND 筛选条件】

DELETE E FROM EMPLOYEES E INNER JOIN JOBS JON (E.JOB_ID=J.JOB_ID)WHERE E.EMPLOYEE_ID=101;

 

DELETE E FROM EMPLOYEES E INNER JOIN JOBS JON (E.JOB_ID=J.JOB_ID)WHERE E.EMPLOYEE_ID=101;

 

DELETE E FROM EMPLOYEES E INNER JOIN JOBS JON (E.JOB_ID=J.JOB_ID)WHERE E.EMPLOYEE_ID=101;

Java培训