MySQL3 存储过程 变量 触发器 视图 函数 索引 权限 远程登录 表设计

存储过程 procedure

-- 概念：存储过程是数据库中的一个对象，存储在服务端，用来封装多条SQL语句且带有逻辑性，可以实现一个功能，由于他在创建时，就已经对SQL进行了编译，所以执行效率高，而且可以重复调用，类似于我们java中的方法。
-- 语法：
delimiter $$
	create procedure myTest()
	begin
	
	end $$
	
-- 输入值和输出值
delimiter $$
	create 
	procedure `mydb`.`mypro2`(in num int,out sum int)
	begin 
	delete from myemp where empno=num;
	select count(*) from myemp into sum;
	end $$
	delimiter;
-- 定义一个会话变量
set @sum=0;
call mypro2(7499,@sum);

-- 查询会话变量的值
select @sum;

-- 删除存储过程
语法：drop procedure myTestPro；



-- 带有if逻辑的储存过程
DELIMITER $$
	CREATE 
	PROCEDURE `mypro`(IN num INT,OUT str VARCHAR(10))
	BEGIN 
	IF num=1 THEN
	 SET str='星期一';
	 ELSEIF num=2 THEN
	 SET str='星期二';
	 ELSEIF num=3 THEN
	 SET str='星期三';
	 ELSE 
	 SET str='未知';
	 END IF;
	END $$
	
    SET @str='a';
    CALL `mypro`(1,@str);
    SELECT @str;

变量

-- 全局变量 内置变量 在任何会话中都能够访问到
-- 会话变量 在同一个会话中可以访问，会话结束变量也就消失了
-- 局部变量 定义在函数中或存储过程中的变量，只要存储过程执行完毕，局部变量就丢失

-- 会话：mysql 客户端和mysql服务端 建立的一次连接，我们就称之为一次会话。

-- 查看所有的内置变量
show cariables;

-- 查看某个全局变量的值
select @@version;

-- 设置全局变量的值
select @@character_set_client   -- 查看数据库编码
set character_set_client=utf8; -- 设置数据库编码

-- 定义会话变量 
set @变量=值

-- 查看会话变量
select @变量

触发器 Trigger

-- 概念：
-- 触发器：数据库中的一个对象，相当于JS中的监听器，触发器可以监听增删改 三个动作。

-- 语法：
DELIMITER $$

CREATE
    TRIGGER `mytestdb`.`myTriger` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `mytestdb`.`<Table Name>`
    FOR EACH ROW 
    BEGIN
     
    END$$

DELIMITER ;
BEFORE 行为发生之前就触发
AFTER 行为发生之后触发
 FOR EACH ROW 行级触发，每操作一行就触发 
 
-- 我往一张表test中插入了数据，在日志表logger中添加一条记录
DELIMITER $$

CREATE TRIGGER `mytestdb`.`MyTri3` AFTER DELETE ON test FOR EACH ROW 
    BEGIN
	INSERT INTO logger VALUES(NULL,"你删除了一条数据",NOW());
    END$$

DELIMITER ;

-- old和new
old.字段 可以获取到被监听的表中的字段的旧值
new.字段 可以获取到被监听表中更新后字段的新值 比如插入新值或者修改旧值

例：在表t1中添加一条数据，另一张表t2也要添加一条同样的数据
  DELIMITER $$
CREATE
    TRIGGER `mytestdb`.`myTri6` AFTER INSERT
    ON `mytestdb`.`t1`
    FOR EACH ROW BEGIN
	INSERT INTO t2 VALUES(new.id,new.username,new.age);
    END$$

DELIMITER ;

视图 View

-- 视图的出现是为了简化数据的查询。
-- 视图本身不会去存储数据，他的数据来自查询的基表中的数据。


select * from emp;
-- 创建单表视图
create view my_view as select * from emp;

-- 查询视图 和查询表的语句是一样的
select * from my_view;

select user.*,orders.'id' as oid,orders.'totalprice',orders.'user_id' from user,orders where user.'id'=orders.'user_id';
-- 创建多表视图
create view my_view3 as select user.*,orders.'id' as oid,orders.'totalprice',orders.'user_id' from user,orders where user.'id'=orders.'user_id';

-- 多表视图不支持子查询。

-- 删除视图
drop view my_view3;

-- 修改视图 视图本身不能修改 只能修改数据来源

函数(方法)

函数：包括内置函数，和自定义函数
自定义函数语法：
delimiter $$
cerate
	function 'shoptest'.'myFun'(n INT) --(形参)
	returns int -- 定义返回的数据类型
	
	begin
		declare num int default 100;
		set num=200;
	return num+n;
	
	end$$
	
delimiter;

-- mysql 中的函数一定有返回值。
-- 调用函数
select muFun(20);

-- 存储过程和函数的区别
-- 1.存储过程没有返回值，函数必须要有返回值。但是存储过程可以用out能实现返回值这个作用
-- 2.存储过程有 in out inout 这几个参数类型，函数的参数全是用来收实参


-- 函数调用是 select
select ceil(3.14);  -- 向上取整

select truncate(3.14，1);  -- 保留小数位数

select substring(原字符串,从哪个地方,截取几个); -- 截取字符串，mysql中下标从1开始算

set @username='abc';
select char_length(@username) -- 字符长度 4个
select length(@username) -- 字节长度12个

select insetr(@username,'a') -- 查找某个字符在一个字符串中的位置 如果找到返回字符串索引，没有找到返回0

select lpad(@username,10,'要填充的字符') -- 左填充：将字符串 按照指定长度填充到就字符的左边 注意10个字符长度是算上之原来的字符总共的长度
select rpad 右填充

select insert(@username,2,2,'哈哈'); -- 从第二字字符开始替换两个字符 替换成哈哈


-- 时间函数
select now(); -- 当前日期

-- 数据库中密码不允许明文插入

-- MD5加密
select MD5('123456')
select password('123456')

MySQL索引

-- 索引正mysql中也叫'键 key'，是存储引擎用于快速找到记录的一种数据结构。
-- 索引对于良好的性能非常关键,尤其是当表中的数据越来越多,索引对于性能的影响愈发重要.
-- 索引优化应该是对查询性能优化的最有效手段了,索引能够轻易将查询性能提高好几个数量级.
-- 索引相当于新华字典的音序表,如果要查某个汉字,不使用音序表,则需要从几百页中的数据中去找找个汉字
-- 简单理解就是,索引就像一本书的目录,让你很快能够查询到你想要的数据.

-- 分类：普通索引 唯一索引 全文索引 单列索引 多列索引 空间索引

-- 语法：
create table student (
		sid int,
    	sname varchar(32),
    	index(sname) -- 给sname字段建立普通索引 注意字段不要带引号
);

-- 通过查看建表语句来看某个字段有没有索引
show create table student；

create table student (
		tid int,
    	sname varchar(32),
    	unique index(sname) -- 创建唯一索引，这列的值不能重复
);

show create table student；

create table student (
		tid int,
    	tname varchar(32),
    	tlog varchar(100),
    	index my_Index(tname,tlog) -- 给tname和tlog两个字段设置索引
);

-- 表已经存在我们给表中的某个字段建立索引
	-- 创建普通索引
		create index myIndex on syudent (sname);
	-- 创建全文索引
		create unique index myIndex on syudent (sname);
	-- 创建多列索引
		create index myIndex on syudent (sname,slog);	 -- 或
		alter table student add index myIndex(sname); 

数据库的权限（DCL）DBA数据库管理员

-- 权限：select insert delete update drop create 或 all
grant select,drop on mydb.`emp` to 'zhangsan'@'localhost' identified by '123456';
-- mydb.*  mydb库下的下的所有表
-- **      所有库习的所有表

远程登录数据库

-- 1.切换库
use mysql;
-- 2.授权远程访问
grant all pribileges on *.* to 'root'@'%' identified by '123456' with grant option;  -- 授权所有用户所有表将root改为% 
-- 3.刷新
flush privileges;
-- 4.退出mysql
exit；
-- 5.启用服务
service mysqld start；
-- 6.查看状态
service mysqld status；
-- 7.远程登录
mysql -h192.168.17.123 -p3306 -uroot -p123456

数据库表设计

-- 数据库设计
-- 需求分析- 需求分析师- 原始需求- 抽取业务模型
-- 			图书模型：图书名称，版本号，作者。。。
-- 			学生模型：学号，姓名，手机。。。
-- 			.......
-- 			角色：学生 老师 图书管理员。。。。
--          《需求说明书》

-- 需求设计
-- 			概要设计：
-- 			抽取实体：业务模型- 实体模型（java类 c++类）内存 class book{ name,bookNo,author }
-- 			数据库设计：
-- 			业务模型/实体模型- 数据模型(硬盘)

-- 三大范式
-- 第一范式：要求表的每个字段必须是不可分割的独立单元
			student  ： name   -- 违反第一范式（可以有曾用名 需要区分）
			student  ： name  old_name  -- 符合第一范式
			
-- 第二范式：在第一范式基础上，要求每张表只表达一个意思，表的每个字段都和表的主键有依赖。
			employee(员工)：员工编号 员工姓名 部门名称 订单名称   -- 违反第二范式
			员工表：员工编号 员工姓名 部门名称
			订单表：订单编号 订单名称  -- 符合第二范式

-- 第三范式：在第二范式的基础放，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
			员工表：员工编号(主键) 员工姓名 部门编号 部门名 -- 符合第二范式，违反第三范式 (数据冗余高)
			员工表：员工编号(主键) 员工姓名 部门编号 -- 符合第三范式(降低数据冗余)
			部门表：部门编号 部门名