mysql数据库

mysql数据库

内置函数

函数类似java中方法，封装了某些特定功能的代码片段；数据库中的函数通常需要传入相应参数(也可以不用传递)，但是函数一般都有返回值；在mysql中函数分为以下几类:

1. 字符串函数
2. 数学函数
3. 日期函数
4. 系统函数
5. …

字符串函数

concat(str1,[str2,[str3])：字符串拼接

select concat('hello','world'); 
-- helloworld

lcase(str)：转小写字符串

select lcase('HELLO'); 
-- hello

ucase(str)：转大写

select ucase('hello'); 
-- HELLO

replace(str,子串,替换内容)：替换指定字符串中子串为指定内容

select replace('13287654987','87654','*****');
-- 132*****987

substring(str,position,len)：从position位置截取指定字符串，截取len长

select substring('helloworld',6,5);
-- world

trim([both|leading|trailing] 字符串 from 目标字符串)：去除指定位置的指定字符

select trim(both 'h' from 'helloworldh');
-- elloworld

数学函数

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(‘DEF’)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,…]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注：返回类型并非均为整数，如下文：
SIGN (number2 ) //
SQRT(number2) //开平方

日期函数

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) / CURDATE() //当前日期
CURRENT_TIME ( ) / CURTIME()//当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime date_format(now(),’%Y/%m/%d’);
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW () //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0

##索引

​ 当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

​ 索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

​ 索引类似字典的目录，可通过索引快速查询到目标数据。

操作指令	代码
创建索引1(直接创建)	CREATE INDEX 索引名 ON 表名(列1[,列2…]);
创建索引2(修改表添加)	ALTER TABLE 表明 ADD INDEX 索引名(列1[,列2…])
创建索引3(建表时创建)	CREATE TABLE 表名 (列定义…, INDEX 索引名(列1[,列2…]));
创建唯一索引(索引列必须唯一)	CREATE UNIQUE INDEX 索引名 ON 表名(列1[,列2…])
删除索引	DROP INDEX 索引名 ON 表名

索引的优点:

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

###索引的缺点:

1.占用内存空间;

2.针对大表创建耗时较长;

3.当数据表中进行更新(insert,delete,update)操作时，索引也需要更新(耗时);

索引建立的原则:

1.不允许建立在重复率较高的字段上(性别列，职位列等);

2.对数据量较大的表建立

3.索引不要建立在经常更新的字段上

视图

​ 视图(view)是一种逻辑上的概念，一般认为是一张虚拟的表，这张表不在数据库服务器中占据任何的存储空间，数据库中只存储视图的定义，在使用视图的时候才会根据定义，从物理表中显示需要的数据，并且通过对于视图的操作可以影响物理表的数据。

​ 视图看上去非常像数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表（base table）数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些视图可以修改对应的基表，而有些则不能（仅仅能查询）。

操作指令	代码
创建视图	CREATE VIEW 视图名(列1，列2…) AS SELECT (列1，列2…) FROM …;
查看视图	SELECT 列1,列2… FROM 视图名
修改视图	CREATE OR REPLACE VIEW 视图名 AS SELECT [列1，列2…] FROM [表1,表2…];
显示已有视图	SHOW TABLES [like…];（可以使用模糊查找）
查看视图详情	DESC 视图名或者SHOW FIELDS FROM 视图名
视图条件限制	[WITH CHECK OPTION]（对没有where条件的视图无效）

视图的作用

1. 简化复杂的sql操作，编写sql查询后重用便捷而无需关注查询细节
2. 对重构数据库提供逻辑独立
3. 保护数据，仅部分显示数据保障机密数据安全
4. 让数据展示更加精简，清晰

视图创建的注意事项

1. 视图名与表名一样必须唯一
2. 创建视图的必须有足够权限，权限由数据库管理员授予
3. 视图可以嵌套，即可以从其他视图中检索数据创建新的视图不能在视图上建立索引，也不能关联触发器
4. 视图可以和物理表共同使用

##事务

​ 数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足ACID（原子性、一致性、隔离性和持久性）原则。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

​ MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在电商系统中，产生一个订单时，我们既需要在订单表中新增一条记录，同时也要修改商品表中的库存量，并且需要保证这两次更新操作要么同时成功，要么同时失败，这样，这些数据库操作语句集就构成一个事务！MySQL数据库只有InnoDB引擎(MyISAM不支持事务)才支持事务。

事务特征

原子性(Atomicity)

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。

一致性(Consistency)

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。

隔离性(Isolation)

由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为隔离性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。

持久性(Durability）

事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

Mysql查看和修改数据库引擎

操作指令	代码
显示所有引擎	SHOW ENGINES;
修改当前引擎(临时)	SET DEFAULT_STORAGE_ENGINE=InnoDB|MyISAM
显示指定表的存储引擎	SHOW TABLE STATUS WHERE NAME=‘tbgoods’
创建表时指定引擎	CREATE TABLE 表名称(字段信息) ENGINE=InnoDB
设置指定表的存储引擎	ALTER TABLE 表名称 ENGINE = InnoDB;

事务操作相关命令

操作指令	代码
开启事务	START TRANSACTION
设置保存点	SAVEPOINT 保存点名称
释放保存点(删除)	RELEASE SAVEPOINT 保存点名称；
提交事务	COMMIT
事务回滚	ROLLBACK
回滚到指定保存点	ROLLBACK TO 保存点名称
设置事务自动提交模式	SET AUTOCOMMIT=0 （0：禁止自动提交；1：开启自动提交）

数据库可编程性

​ 目前绝大多数dbms都支持数据库的可编程性，所谓数据库可编程即使用一些特定的语法机制，比如变量，数据类型，循环语句，条件分支等结合sql语句，实现sql语句的业务逻辑化，使得sql不再是单一的一行语句的表达方式，而是具备其他常见程序设计语言的功能(Java，c,c++,javascript…),常见的比如sqlserver（T-SQL），Oracle(PL/SQL)；Mysql也在5.0版本之后新增了对于可编程的支持，通常可编程性的支持体现在以下几个方面:

1. 存储过程(procedure)
2. 自定义函数(function)
3. 触发器

存储过程

​ 存储过程(Procedure),是数据库操作语言SQL的可编程性实现，传统的SQL语句通常是在数据库服务器中执行一条命令，命令在数据库引擎内部需要经过创建与分析的过程，因此在运行时间上开销较大；而存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划，这样，后期的使用只需通过命令调用即可，因此，在执行过程时便可节省此开销。