SQL语句之DML

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培训

©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页
实付 99.00元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值