MySQL之DML（数据管理语言）、视图

前言

之前我们已经讲过了MySQL一部分知识点，今天我们再讲讲MySQL的DML（数据管理语言），也就是我们俗称的增删改SQL语句！

指路：

MySQL安装卸载以及使用
MySQL之账号管理，四大引擎以及DDL（数据定义语言）
MySQL常见查询、表达式、排序、分组查询基础
MySQL分组查询、连表查询、子查询、分页查询

DML（数据管理语言）

概念

• DML(Data Manipulation Language 
–数据操纵语言) 可以在下列条件下执行: – 向表中插入数据
– 修改现存数据
– 删除现存数据
• 事务是由完成若干项工作的DML语句组成的

插入数据

• 使用 INSERT 语句向表中插入数据。
• 使用这种语法一次只能向表中插入一条数据。

• 为每一列添加一个新值。
• 按列的默认顺序列出各个列的值。
• 在 INSERT 子句中随意列出列名和他们的值。
• 字符和日期型数据应包含在单引号中。

语法：

	INSERT INTO table [(column [, column...])]
	VALUES (value [, value...]);

向表中插入空值

隐式方式: 在列名表中省略该列的值。
	INSERT INTO t_mysql_departments (department_id, department_name )
	VALUES (30, 'Purchasing');

显示方式: 在VALUES 子句中指定空值。
	INSERT INTO t_mysql_departments
	VALUES (100, 'Finance', NULL, NULL);

插入指定的值

	INSERT INTO t_mysql_employees (employee_id, first_name, last_name, email, phone_number,hire_date, job_id, salary, commission_pct, manager_id,department_id)
	VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567', NOW(), 'AC_ACCOUNT', 6900, NULL, 205, 100);

从其它表中拷贝数据

• 不必书写 VALUES 子句。 
• 子查询中的值列表应与 INSERT 子句中的列名对应

在 INSERT 语句中加入子查询。
	INSERT INTO emp2 
	SELECT * 
	FROM t_mysql_employees
	WHERE department_id = 90;
	
	INSERT INTO sales_reps(id, name, salary, commission_pct)
	SELECT employee_id, last_name, salary, commission_pct
	FROM t_mysql_employees
	WHERE job_id LIKE '%REP%';

更新(修改)数据

• 使用 UPDATE 语句更新数据。
	UPDATE table
	SET column = value [, column = value, ...]
	[WHERE condition];

• 可以一次更新多条数据。
• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

语法：

• 使用 WHERE 子句指定需要更新的数据。
	UPDATE t_mysql_employees
	SET department_id = 70
	WHERE employee_id = 113;

• 如果省略 WHERE 子句，则表中的所有数据都将被更新
	UPDATE copy_emp
	SET department_id = 110;

一次修改多表的数据

修改张无忌的女朋友的手机号为114
	UPDATE t_mysql_boys bo
	INNER JOIN t_mysql_beauty b ON bo.`id`=b.`boyfriend_id`
	SET b.`phone`='119',bo.`userCP`=1000
	WHERE bo.`boyName`='张无忌';

删除数据

使用 DELETE 语句从表中删除数据。
语法：
	DELETE FROM table
	[WHERE condition];

• 使用 WHERE 子句删除指定的记录。
	DELETE FROM t_mysql_departments
	WHERE department_name = 'Finance';

• 如果省略 WHERE 子句，则表中的全部数据将被删除
	DELETE FROM copy_emp

;一次删除多表的数据

删除张无忌的女朋友的信息
	DELETE b
	FROM t_mysql_beauty b
	INNER JOIN t_mysql_boys bo ON b.`boyfriend_id` = bo.`id`
	WHERE bo.`boyName`='张无忌';

视图

含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

创建语法的关键字	是否实际占用物理空间	使用
视图 create view	只是保存了sql逻辑	增删改查，只是一般不能增删改
表create table	保存了数据	增删改查

创建视图

语法：

create view 视图名 as 查询语句;

案例

查询姓张的学生名和专业名
	一般查询：
		SELECT stuname,majorname
		FROM stuinfo s
		INNER JOIN major m ON s.`majorid`= m.`id`
		WHERE s.`stuname` LIKE '张%';

	创建视图语法将数据查询：
		CREATE VIEW v1
		AS
		SELECT stuname,majorname
		FROM stuinfo s
		INNER JOIN major m ON s.`majorid`= m.`id`;
	
	最后我们可以直接查询视图做到简洁的查询：
		SELECT * FROM v1 WHERE stuname LIKE '张%';

查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
	CREATE VIEW myv1
	AS
	SELECT last_name,department_name,job_title
	FROM employees e
	JOIN departments d ON e.department_id  = d.department_id
	JOIN jobs j ON j.job_id  = e.job_id;

#②使用
	SELECT * FROM myv1 WHERE last_name LIKE '%a%';

视图的修改

语法

	方式一：create or replace view  视图名 as 查询语句;
	方式二：alter view 视图名 as  查询语句;

删除视图

语法

drop view 视图名,视图名,...;

查看视图

语法

DESC myv3;

SHOW CREATE VIEW myv3;

具备以下特点的视图不允许更新

1、包含以下关键字的sql语句：分组函数、distinct、group  by、having、union
	或者union all
2、常量视图
3、Select中包含子查询

1、包含关键字的sql语句
	CREATE OR REPLACE VIEW myv1
	AS
	SELECT MAX(salary) m,department_id
	FROM employees
	GROUP BY department_id;
	
	SELECT * FROM myv1;

#更新
	UPDATE myv1 SET m=9000 WHERE department_id=10;
	

2、常量视图

CREATE OR REPLACE VIEW myv2
	AS

	SELECT 'john' NAME;

	SELECT * FROM myv2;

#更新
	UPDATE myv2 SET NAME='lucy';

3、Select中包含子查询

	CREATE OR REPLACE VIEW myv3
	AS
	
	SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
	FROM departments;

#更新
	SELECT * FROM myv3;
	UPDATE myv3 SET 最高工资=100000;

join

	CREATE OR REPLACE VIEW myv4
	AS
	SELECT last_name,department_name
	FROM employees e
	JOIN departments d
	ON e.department_id  = d.department_id;

#更新

	SELECT * FROM myv4;
	UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';
	INSERT INTO myv4 VALUES('陈真','xxxx');

from一个不能更新的视图

	CREATE OR REPLACE VIEW myv5
	AS
	SELECT * FROM myv3;

#更新

	SELECT * FROM myv5;
	
	UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;

where子句的子查询引用了from子句中的表

	CREATE OR REPLACE VIEW myv6
	AS
	
	SELECT last_name,email,salary
	FROM employees
	WHERE employee_id IN(
		SELECT  manager_id
		FROM employees
		WHERE manager_id IS NOT NULL
	);

#更新
	SELECT * FROM myv6;
	UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

总结

MySQL知识点今天到这里分析结束啦，有问题欢迎评论区留言哦摆摆！