MySQL函数 聚合函数 日期函数 数值函数 字符函数 MySQL事务 MySQL索引 MySQL视图 MySQL触发器

常用函数

聚合函数

1，count() 求满足列条件的总的行数。
select count (字段名) from 表格名称；——》查询表格中字段名出现的次数。
select count（字段名） from 表格名称 where 判断条件；——》查询表格中在满足判断条件下的字段名出现的次数
注意：除了COUNT(*)以外，其他字段都会忽略NULL值;


2，sum（）求总和。
select sum（字段名） from 表格名称 ；——》求出字段名对应的值的总和。
sum(这里可以是多个字段名相加)
例如：
select sum（语+数+英）from score；——》求出score 中 语数英 三科成绩的总和。


3，avg（）求平均数。
select avg（字段名） from 表格名称 ；——》求出字段名对应值的平均值。
用法和 sum（）一样。


4，min（）和max（）求出最小值和最大值。
select min（字段名）,max（字段名） from 表格名称 ；——》求字段名定对应值的最小与最大。
例如：
select min（math），max（math) from score;——》求成绩表中的数学成绩的最小与最大。


5， group by 分组
GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。
分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录。分组函数忽略空值。
select 字段名 from 表格名称 group by 字段名；

例子；查询成绩表中至少有5名学生选修的并且以3开头的课程的平均分数
select avg（degree） from score where cno like ‘3%’ group by cno having count（sno）>5;


注意: 
（1）、分组函数的重要规则
如果使用了分组函数，或者使用 GROUP BY 的查询：出现在SELECT列表	中的字段，要么出现在聚合函数里，要么出现在GROUP BY 子句中。

（2）、having where 的区别
①、where和having都是用来做条件限定的,
②、WHERE是在分组(group by)前进行条件过滤， 
③、HAVING子句是在分组(group by)后进行条件过滤，
④、WHERE子句中不能使用聚合函数,HAVING子句可以使用聚合函数。
⑤、HAVING子句用来对分组后的结果再进行条件过滤
having sum(price)>2000 相当于 拿着 列名为sum(price)去查询。

日期函数

select curdate();——》返回当前日期
或 select curdate() from dual; 注: dual 是虚拟表，为了凑sql语句的格式;

select curtime();——》返回当前时间

select now();——》返回当前的日期和时间

select week(date);——》返回指定日期为一年中的第几周

select year(date);——》返回日期的年份

select hour(time);——》返回time的小时值

select minute(time);——》返回time的分钟值

select monthname(time);——》返回date的月份值

select date_format(data,fmt);——》返回按字符串fmt格式化日期date值

select datediff(start,end);——》返回起始时间和结束时间之间的天数

select date_add(date,interval exp type);——》返回一个日期或时间值+一个时间间隔的时间值

数值函数

select abs（x）;——》返回x的绝对值

select ceil（x）;——》返回向上取整的值

select floor（x）;——》返回向下取整的值

select mod（x，y）;——》返回x/y的模

select rand（）;——》返回一个0-1之间随机的浮点值v（0=<v<=1）

select round（x,y）;——》返回参数x的四舍五入的有y位小数的值

select truncate（x,y）;——》返回数字x截断为y位小数的结果

字符串函数

select  concat（str1，str2）；——》连接字符串

select insert（str，a，b，newstr）；——》字符串str从a位置开始的b个字符替换为newstr

select lower（str）；——》转成小写

select upper（str）；——》转成大写

select length（str）

select lpad（str，len，pastr）；——》返回字符串，其左边由pastr填补到len长度

select rpad（str，len，pastr）；——》返回字符串，其右边由pastr填补到len长度

select trim（str）；——》去掉str前缀和后缀的空格

select repeat（str，count）;——》返回str重复count次的结果

select replace（str，str1，str2）；——》用字符串str2替换str中的所有str1

select substring（str，pos，len）；——》返回从字符串str的pos位置起len个长度的字符

其他函数

select database（）；——返回当前数据库名

select version（）；——》返回当前数据库版本

select user（）；——》返回当前登录用户名

select inet_aton(ip)；——》返回ip地址的数字表达

select inet_ntoa(ip)；——》返回数字代表的ip地址

select password(str)；——》返回str加密版本，加密是单向的，适用于MySQL数据库的用户密码加密。

事务

事务是由一组DML（数据操作语言）语句组成，完成一个相对完整的功能,这一组DML语句要么全部执行，要么全部放弃执行。

数据库开始事务命令

start transaction 或者 begin 开启事务

commit 提交事务，提交未存储的事务

rollback 回滚事务,即撤销指定的sql语句(只能回退insert delete update语句)

savepoint 保留点 ，事务处理中设置的临时占位符 你可以对它发布回退。

start TRANSACTION; 开启事务
insert into t5(name,gender) values('张飞','man');
SAVEPOINT s1; 保留点

insert into t5(name,gender) values('关羽','man');
SAVEPOINT s2;

insert into t5(name,gender) values('刘备','man');
SAVEPOINT s3;
insert into t5(name,gender) values('赵云','man');

select * from t5;

ROLLBACK to s2; 回滚事务

select * from t5;
COMMIT;提交事务

事务的特性

事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。这四个特性也简称ACID性。

• **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

索引

索引在Mysql中是存储引擎用于快速找到记录的一种数据结构

使用索引的目的：提高查询速度。

索引特点：创建和维护索引会消耗很多时间与磁盘空间，但是查询速度大大提高。

（创建字典目录需要时间，字典目录需要占字典大小）

语法

语法1：创建表时，添加索引。

create table 表名(
字段名1 数据类型，
字段名2 数据类型,
...，
Index | key  [索引名称]  (字段名) 
);


语法2：创建完表后，添加索引。
create table 表名(
字段名1 数据类型，
字段名2 数据类型 
);
Alter table 表名 add {unique | primay key| index...索引种类} 索引名(字段名) 

分类

普通索引 ；就是一个普通的索引，可以为空，可以重复。

唯一索引 ；可以为空，不可以重复。

主键索引； 不可以为空，不可以重复。

多列索引。

ALTER TABLE teacher ADD INDEX name_index(column); ——》普通索引


ALTER TABLE teacher ADD UNIQUE(column); ——》 唯一索引 
ALTER TABLE teacher ADD column type UNIQUE; 

ALTER TABLE teacher ADD PRIMARY KEY(column); ——》主键索引
只要是主键，就是索引。

ALTER TABLE teacher ADD INDEX(column1,column2,column3); ——》多列索引

Drop index 索引名 on 表名；——》删除索引

注意

使用索引：

1、少量数据不使用索引

2、查询次数少不使用索引

3、查询要携带索引字段，通常放在select之后第一个

4、索引提高了查询的效率，占有跟多的资源。索引不是越多越好。（一本书，半本书是目录不可以）。

视图

视图就是一条SELECT语句执行后返回的结果集,

视图是一个虚拟表，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表。

作用

（1）简化用户的操作，关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作；

（2）对机密数据提供保护作用，不希望用户访问表中某些含敏感信息的列，比如salary…

操作

（1）创建视图:

Create view 视图名称 as DQL语句(select …);

（2）删除视图:

Drop view 视图名称;

create view  view_employee as select * from t_employee——》创建视图

select * from view_employee 显示结果

drop view view_employee   删除视图

更新视图

在MySQL中，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，都是转换到基本表来更新。

更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

注意：

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

触发器

触发器（trigger）：监视某种情况，并触发某种操作，它的执行是由事件来触发的，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：

1.监视地点(table)

2.监视事件(insert/update/delete)

3.触发时间(after/before)

4.触发事件(insert/update/delete)

操作

创建触发器：

Create trigger trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt;

trigger_time ：触发器的触发事件，可以为before（在检查约束前触发）或after（在检查约束后触发）；

trigger_event：是触发器的触发事件，包括insert、update和delete；

tb_name： 表示建立触发器的表名，就是在哪张表上建立触发器

trigger_stmt： 触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句；

FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器。

删除触发器：Drop trigger 触发器名称。

new和old

NEW.columnname：新增行的某列数据

OLD.columnname：删除行的某列数据

触发器类型

insert：new表示将要或者已经新增的数据

update：new表示将要或者已经修改的数据，old表示将要或者已经修改的数据

delete；old表示将要或者已经删除的数据

比如：商品数量与购物车  new的用法——购物车增加多少，商品数量减少多少
                    old的用法--删除购物车增加的，商品数量恢复

delimiter $
create trigger tg1 after insert on o for each row 
BEGIN
update g  set num =num+new.much where id=new.gid;
end $
delimiter;
insert into o(gid,much) values(1,3)  触发器 new

drop TRIGGER tg1;删除触发器

delimiter $
CREATE TRIGGER tg3 after delete on o for each ROW
BEGIN
UPDATE g set num=num+old.much where id = old.gid;
END $
delimiter ;

DELETE from o where oid = 4;