网上做论文的网站有哪些,洛阳网站建设培训学校,三网合一 网站,模板和网站的区别视图#xff1a;VIEW 概念 ① 虚拟表#xff0c;本身不存储数据#xff0c;可以看做是存储起来的SELECT语句 ② 视图中SELECT语句中涉及到的表#xff0c;称为基表 ③ 针对视图做DML操作#xff0c;对影响到基表中的数据#xff0c;反之亦然 ④ 创建、删除视图本身#…视图VIEW 概念 ① 虚拟表本身不存储数据可以看做是存储起来的SELECT语句 ② 视图中SELECT语句中涉及到的表称为基表 ③ 针对视图做DML操作对影响到基表中的数据反之亦然 ④ 创建、删除视图本身不会影响到基表 创建视图 CREATE VIEW view_emp_avg_salary(dept_id, avg_salary)
AS
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;查看视图DESC 视图名; 修改视图 # 方式一
CREATE OR REPLACE VIEW 视图名
AS
SELECT ...#方式二
ALTER VIEW 视图名
AS
SELECT ...删除视图 DROP VIEW 视图名;索引INDEX
用于提高查询性能
存储过程PROCEDURE
用于完成一次完整的业务处理没有返回值但可以通过传出参数将多个值传给调用者
创建 ① 无参数无返回值# 查询所有员工信息
DELIMITER $
CREATE PROCEDURE select_all_emp()
BEGINSELECT * FROM employees;
END $
DELIMITER;
# 调用
CALL select_all_emp();② 无参数有返回值OUT# 查询员工中的最低工资并将最低工资输出到ms
DELIMITER $
CREATE PROCEDURE select_min_sal(OUT ms DECIMAL)
BEGINSELECT MIN(salary) INTO msFROM employees;
END $
DELIMITER;
# 调用
CALL select_min_sal(ms);
# 查看参数结果
SELECT ms;③ 有参数无返回值IN# 查询指定姓名的员工工资
DELIMITER $
CREATE PROCEDURE show_salary_by_lastname(IN lastName VARCHAR(50))
BEGINSELECT salaryFROM employeesWHERE last_name lastName;
END $
DEIMITER;
# 调用
CALL show_salary_by_lastname(Popp);④ 有参数有返回值IN、OUT# 查询指定姓名的员工工资和部门并从参数中返回出来
DELIMITER $
CREATE PROCEDURE show_salary_by_lastname2(IN lastName VARCHAR(50), OUT sal DECIMAL, OUT dept_id INT)
BEGINSELECT salary,department_id INTO sal, dept_idFROM employeesWHERE last_name lastName;
END $
DELIMITER;
# 调用
CALL show_salary_by_lastname2(Popp, sal, deptId);
# 查询结果
SELECT sal, deptId;⑤ 有参数有返回值INOUT# 查询指定员工的领导姓名并从参数中返回出来
DELIMITER $
CREATE PROCEDURE show_manager_by_lastname(INOUT lastName VARCHAR(50))
BEGINSELECT m.last_name INTO lastNameFROM employees e JOIN employees mON e.manager_id m.employee_idWHERE e.last_name lastName;
END $
DELIMITER;
# 调用
SET lastname Popp;
CALL show_manager_by_lastname(lastname);
# 查询结果
SELECT lastname; characteristics ① LANGUAGE SQL表明存储过程执行体是由SQL语句构成的 ② [NOT] DETERMINISTIC存储过程的执行结果是否确定默认为不确定 ③ CONTAINS SQL存储过程的执行体包含SQL语句但不包含读写数据的SQL语句默认是这个 ④ NO SQL存储过程的执行体不包含任何SQL语句 ⑤ READS SQL DATA存储过程的执行体包含读数据的SQL语句 ⑥ MODIFIES SQL DATA存储过程的执行体包含写数据的SQL语句 ⑦ SQL SECURITY {DEFINER | INVOKER}执行权限 ⑧ COMMENT备注信息查看创建信息SHOW CREATE PRODECURE 存储过程名称;查看状态信息SHOW PROCEDURE STATUS LIKE 存储过程名称;修改特性ALTER PROCEDURE 存储过程名称
SQL SECURITY INVOKER
COMMENT 备注信息...;删除DROP PROCEDURE 存储过程名称;
存储函数FUNCTION
用于完成一次特定的计算有一个返回值
# 查询指定部门的员工人数
DELIMITER $
CREATE FUNCTION select_count_by_dept(dept_id INT)
RETURNS INT
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGINRETURN(SELECT COUNT(*) FROM employees WHERE department_id dept_id);
END $
DELIMITER;
# 使用
SELECT select_count_by_dept(50);触发器TRIGGER
相当于事件监听器当数据库发生特定事件时触发器被触发完成相应的处理 创建 代码格式 CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW BEGIN 代码块; END; ① 案例一向一张表中添加数据之前同时向另一张表添加数据 DELIMITER //
CREATE TRIGGER test_before_trigger
BEFORE INSERT ON table1
FOR EACH ROW
BEGININSERT INTO table2(name)VALUES (before_name...);
END //
DELIMITER;
# 测试
INSERT INTO table1(age)
VALUES(18);② 案例二在向employees表中添加员工信息之前检验此员工的工资是否大于领导的工资大于的话报错否则添加成功NEW代表添加的数据信息OLD代表删除的数据信息 DELIMITER //
CREATE TRIGGER verify_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGINDECLARE manager_sal DECIMAL DEFAULT 0.0;SELECT salary INTO manager_sal FROM employees WHERE employee_id NEW.manager_id;IF NEW.salary manager_sal# 抛出错误THEN SIGNAL SQLSTATE HY000 SET MESSAGE_TEXT 添加失败薪资高于领导工资;END IF;
END //
DELIMITER;查看 ① 查看数据库中的所有触发器SHOW TRIGGERS; ② 查看某个触发器的定义SHOW CREATE TRIGGER 触发器名称; 删除DROP TRAGGER 触发器名称;
变量
系统变量以开头 ① 查看全局系统变量SHOW GLOBAL VARIABLES; ② 查看会话系统变量SHOW SESSION VARIABLES;
SHOW VARIABLES;③ 查看指定系统变量SELECT global.max_connections; # 查看服务器最大连接数
SELECT global.character_set_client; # 查看字符集
SELECT session.character_set_client; # 查看字符集
SELECT session.pseudo_thread_id; # 查看当前会话的MySQL连接ID
SELECT character_set_client; # 先查看会话的字符集若没有再查看全局的字符集④ 修改系统变量的值SET global.max_connections 166; # 修改全局变量方式一
SET GLOBAL max_connection 167; # 修改全局变量方式二SET session.character_set_client gbk; # 修改会话变量方式一
SET SESSION character_set_client gbk; # 修改会话变量方式二用户变量 ① 会话用户变量以开头# 声明和初始化
SET a 1;
SET b : 2;
SELECT avg_sal : AVG(salary) FROM employees; # 这种情况必须用:
SELECT COUNT(*) INTO emp_total FROM employees;
#使用
SELECT a, b, avg_sal, emp_total;② 局部变量 说明 a、使用关键字DECLARE b、只能在存储过程和存储函数中使用 c、必须写在BEGIN...END的首行 d、格式DECLARE 变量名 类型 [DEFAULT 值] 若不指明DEFAULT则默认是NULLDELIMITER $
CREATE PROCEDURE test()
BEGIN# 声明局部变量DECLARE a INT DEFAULT 0;DECLARE b INT;DECLARE name VARCHAR(30);#赋值SET a 5;SET b : 6;SELECT last_name INTO nameFROM employeesWHERE employee_id 100;#使用SELECT a, b, name;
END $
DELIMITER;定义条件与处理程序
定义条件 ① 格式DECLARE 错误名称 CONDITION FOR 错误码; ② 目的给错误码命名 ③ 错误码分类MySQL_error_code、sqlstate_value定义处理程序 ① 格式DECLARE 处理方式 HANDLER FOR 错误类型 处理语句; ② 处理方式分类 a、CONTINUE遇到错误不处理继续执行 b、EXIT遇到错误马上退出 c、UNDO遇到错误后撤回之前的操作 ③ 错误类型分类 a、SQLSTATE 字符串错误码字符串类型错误码长度为5 b、MySQL_error_code数值类型错误码 c、错误名称定义条件时声明的名称 d、SQLWARNING匹配所有以01开头的错误码 e、NOT FOUND匹配所有以02开头的错误码 f、SQLEXCEPTION匹配没有被d、e匹配到的错误码案例DELIMITER $
CREATE PROCEDURE UpdateTest()
BEGIN# 因email是NOT NULL所以执行第一个UPDATE会报错“1048 - Column email cannot be null”# 在此声明错误处理DECLARE CONTINUE HANDLER FOR 1048 SET message aaa;SET a 1;UPDATE employees SET email NULL WHERE employee_id 100;SET a 2;UPDATE employees SET email 123qq.com WHERE employee_id 100;SET a 3;
END $
DELIMITER;# 调用
CALL UpdateTest();
# 查看变量
SELECT a, message; // 3, aaa流程控制
分支结构 IF DELIMITER //
CREATE PROCEDURE testIf()
BEGINDECLARE score DECIMAL(10,2) DEFAULT 85;IF score 90 THEN SELECT A;ELSEIF score 80 AND score 90 THEN SELECT B;ELSEIF score 60 AND score 80 THEN SELECT C;ELSE SELECT D;END IF;
END //
DELIMITER;CALL testIf();CASE ① CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END CASE; DELIMITER //
CREATE PROCEDURE testCase()
BEGINDECLARE n INT DEFAULT 2;CASE nWHEN 1 THEN SELECT n 1;WHEN 2 THEN SELECT n 2;ELSE SELECT n 9;END CASE;
END //
DELIMITER;② CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END CASE; DELIMITER //
CREATE PROCEDURE testCase()
BEGINDECLARE n INT default 10;CASEWHEN n 20 THEN SELECT n 20;WHEN n 10 THEN SELECT n 10;ELSE SELECT n 10;END CASE;
END //
DELIMITER;循环结构 LOOP DELIMITER //
CREATE PROCEDURE testLoop()
BEGINDECLARE n INT DEFAULT 1; # n 1# 开始循环aaa: LOOPIF n 8THEN LEAVE aaa;END IF;SET n n 1;END LOOP aaa;SELECT n;
END //
DELIMITER;WHILE DELIMITER //
CREATE PROCEDURE testWhile()
BEGINDECLARE n INT DEFAULT 1;WHILE n 10DOSET n n 1;END WHILE;SELECT n;
END //
DELIMITER;REPEAT DELIMITER //
CREATE PROCEDURE testRepeat()
BEGINDECLARE n INT DEFAULT 1;REPEATSET n n 1;UNTIL n 10END REPEAT;SELECT n;
END //
DELIMITER;跳转语句 LEAVE ① 同BREAK格式LEAVE 循环名; ② 使用场景循环体中、BEGIN...END中 DELIMITER //
CREATE PROCEDURE testLeave(IN num INT)
label_test: BEGINIF num 0 THEN LEAVE label_test;ELSEIF num 5 THEN SELECT num;ELSE SELECT num 5;END IF;SELECT is end;
END //
DELIMITER;ITERATE ① 同CONTINUE格式ITERATE 循环名; ② 使用场景循环体中 DELIMITER //
CREATE PROCEDURE test_iterate()
BEGINDECLARE num INT DEFAULT 0;iterate_test: LOOPSET num num 1;IF num 5 THEN LEAVE iterate_test;ELSEIF num 2 THEN ITERATE iterate_test;ELSE SELECT is end;END IF;END LOOP;SELECT num;
END //
DELIMITER;游标
随意的定位到某一条记录可以逐条读取数据。在使用游标过程中会对数据加锁不适合高并发场景。
DELIMITER //
CREATE PROCEDURE test_cursor(IN limit_total_salary DECIMAL, OUT total_count INT)
BEGIN# 声明变量DECLARE sum_sal DECIMAL DEFAULT 0.0;DECLARE emp_count INT DEFAULT 0;DECLARE emp_sal DECIMAL;# 声明游标DECLARE cursor_test CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;# 打开游标OPEN cursor_test;# 使用游标WHILE sum_sal limit_total_salaryDOFETCH cursor_test INTO emp_sal;SET sum_sal sum_sal emp_sal;SET emp_count emp_count 1;END WHILE;SET total_count emp_count;# 关闭游标CLOSE cursor_test;
END //
DELIMITER;
# 调用
CALL test_cursor(100000, count);
SELECT count;
