DML和视图

DML（数据管理语言）

1、DML简介

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

2、插入数据

• 使用 INSERT 语句向表中插入数据。
• 使用这种语法一次只能向表中插入一条数据。
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
• 为每一列添加一个新值。
• 按列的默认顺序列出各个列的值。
• 在 INSERT 子句中随意列出列名和他们的值。
• 字符和日期型数据应包含在单引号中。
INSERT INTO t_mysql_departments(department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);

INSERT INTO t_mysql_employees(employee_id,last_name,email,hire_date,job_id)
VALUES (300,’Tom’,’tom@126.com’,to_date(‘2012-3-
21’,’yyyy-mm-dd’),’SA_RAP’);

向表中插入空值
	• 隐式方式: 在列名表中省略该列的值。
	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);

从其它表中拷贝数据
	• 在 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%';
	• 不必书写 VALUES 子句。 
	• 子查询中的值列表应与 INSERT 子句中的列名对应

3、更新数据

一次修改多表的数据
	1：修改张无忌的女朋友的手机号为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`='张无忌';
		
	2：修改没有男朋友的女神的男朋友编号都为2号
		UPDATE t_mysql_boys bo
		RIGHT JOIN t_mysql_beauty b ON bo.`id`=b.`boyfriend_id`
		SET b.`boyfriend_id`=2
		WHERE bo.`id` IS NULL;

4、删除数据

一次删除多表的数据
	1、删除张无忌的女朋友的信息
		DELETE b
		FROM t_mysql_beauty b
		INNER JOIN t_mysql_boys bo ON b.`boyfriend_id` = bo.`id`
		WHERE bo.`boyName`='张无忌';
		
		
	2、删除黄晓明的信息以及他女朋友的信息
		DELETE b,bo
		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
	查询语句;
	*/
	
	1.查询姓名中包含a字符的员工名、部门名和工种信息
		①创建
			CREATE VIEW myv1 AS
				SELECT last_name,department_name,job_title FROM employees eJOIN 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%';

	2.查询各部门的平均工资级别
		①创建视图查看每个部门的平均工资
			CREATE VIEW myv2 AS
				SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id;

		②使用
			SELECT myv2.`ag`,g.grade_level FROM myv2 JOIN job_grades g ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

	3.查询平均工资最低的部门信息
			SELECT * FROM myv2 ORDER BY ag LIMIT 1;

	4.查询平均工资最低的部门名和工资
		CREATE VIEW myv3 AS
			SELECT * FROM myv2 ORDER BY ag LIMIT 1;

		SELECT d.*,m.ag FROM myv3 m JOIN departments d ON m.`department_id`=d.`department_id`;

二、视图的修改
	方式一：
		/*
		create or replace view  视图名
		as
		查询语句;
		
		*/
		SELECT * FROM myv3  CREATE OR REPLACE VIEW myv3 AS
			SELECT AVG(salary),job_id FROM employees GROUP BY job_id;

	方式二：
		/*
		语法：
		alter view 视图名
		as 
		查询语句;
		*/
		ALTER VIEW myv3 AS
			SELECT * FROM employees;

三、删除视图
	/*
	语法：drop view 视图名,视图名,...;
	*/
	DROP VIEW emp_v1,emp_v2,myv3;

四、查看视图
	SHOW CREATE VIEW myv3;

五、视图的更新
	CREATE OR REPLACE VIEW myv1 AS
		SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary" FROM employees;

	CREATE OR REPLACE VIEW myv1 AS
		SELECT last_name,email FROM employees;

	SELECT * FROM myv1;

	SELECT * FROM employees;

	1.插入
		INSERT INTO myv1 VALUES('张飞','zf@qq.com');

	2.修改
		UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

	3.删除
		DELETE FROM myv1 WHERE last_name = '张无忌';
		具备以下特点的视图不允许更新
			①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
				CREATE OR REPLACE VIEW myv1 AS
					SELECT MAX(salary) m,department_id FROM employees GROUP BY department_id;

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

			②常量视图
				CREATE OR REPLACE VIEW myv2 AS
					SELECT 'john' NAME;

			更新
				UPDATE myv2 SET NAME='lucy';

			③Select中包含子查询
				CREATE OR REPLACE VIEW myv3 AS
					SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资 FROM departments;

			更新
				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;

			更新
				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';