mysql视图

#概念
数据库视图是虚拟表或逻辑表;
因为数据库视图与数据库表类似,它由行和列组成,因此可以根据数据库表查询数据;
允许通过先决条件的数据库视图来更新基础表中的数据;
当基础表的数据发生变化时,视图也反映了这些数据的变化;

#优点
简化查询:使用数据库视图可隐藏最终用户和程序基础表的复杂性;只需要简单的sql语句就能拿到想要的结果;
权限控制:可以限制用户对数据的访问权限,不暴露敏感数据;创建只读视图用户只能看不能改;
计算列:可创建实时统计字段
向后兼容:从新设计数据库基础表,不需要修改应用程序,只需要修改视图即可

#缺点
性能低:从数据库视图查询数据可能会很慢,特别是如果视图是基于其他视图创建的。
高耦合:与基础表高耦合,如果更改了表结构同时需要更改视图

#视图的限制:
不能在视图上创建索引,查询的时候会使用mysql底层表的索引。
MySQL 5.7.7之前,不能在SELECT语句的FROM子句中使用子查询来定义视图。
删除或重命名视图所基于的表,MySQL会使视图无效,可以使用CHECK TABLE语句来检查视图是否有效。
简单的视图可以更新表中数据,基于具有连接,子查询等的复杂SELECT语句创建的视图无法更新。
MySQL是不支持物理视图

#创建视图
MySQL提供了三种算法:MERGE,TEMPTABLE和UNDEFINED,默认是UNDEFINED;
UNDEFINED算法使MySQL可以选择使用MERGE或TEMPTABLE算法。
MySQL优先使用MERGE算法进行TEMPTABLE算法,因为MERGE算法效率更高。
TEMPTABLE算法创建的视图将不能更新

[code]CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW showTestTable AS
SELECT * FROM test LIMIT 2;[/code]

#基于另一个视图创建视图
[code]CREATE OR REPLACE VIEW showTest AS
SELECT * FROM showTestTable WHERE id=1;[/code]

#创建一个数据一致性的视图,WITH CHECK OPTION子句创建的视图在插入、更新、删除的时候会检查数据是否符合视图的定义,不符合的将拒绝执行
[code]CREATE OR REPLACE VIEW showTestTable4 AS
SELECT * FROM test WHERE id<10
WITH CHECK OPTION;[/code]

#查询视图
[code]SELECT * FROM showTestTable2;[/code]

#通过视图修改数据
[code]UPDATE showTestTable3 SET created=2 WHERE id=1;[/code]

#通过视图删除数据
[code]DELETE FROM showTestTable2 WHERE id=1;[/code]

#通过视图插入数据,使用了WITH CHECK OPTION语句的视图,不满足视图数据一致性的数据无法插入
[code]INSERT INTO showTestTable4 VALUES(10,11111);[/code]

#查询可以update的视图
[code]SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema = ‘demo';[/code]

#查看所有的表,视图属于表
[code]SHOW TABLES;[/code]

#查看哪些是表,哪些是视图
[code]SHOW FULL TABLES;[/code]

#查看视图的定义
[code]SHOW CREATE VIEW showTestTable;[/code]

#修改视图
[code]ALTER VIEW showTestTable4 AS
SELECT * FROM test WHERE id=8;

SELECT * from showTestTable4;[/code]

#删除视图
[code]DROP VIEW IF EXISTS showTestTable;[/code]

mysql储存过程

#概念

存储过程是存储在数据库目录中的一段声明性SQL语句。

#优点

高性能:MySQL存储过程按需编译,在编译存储过程之后,MySQL将其放入缓存中,应用程序在单个连接中多次使用存储过程,则使用编译版本,否则存储过程的工作方式类似于查询。
节约流量:应用程序不必发送多个冗长的SQL语句,而只能发送存储过程的名称和参数。
重用性:存储过程将数据库接口暴露给所有应用程序,开发人员不必开发存储过程中已支持的功能。
安全性:数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限,而不向基础数据库表提供任何权限。

#缺点

消耗内存和CPU:大量存储过程会导致每个连接的内存使用量将会大大增加;存储过程中过度使用大量逻辑操作,则CPU使用率也会增加。
开发维护成本高
很难调试
存储过程的构造不利于复杂的业务逻辑开发

#创建一个储存过程
[code]
DELIMITER //
CREATE PROCEDURE procedureName()
BEGIN
SELECT * FROM test;
END //
DELIMITER ;
CALL procedureName();
[/code]

#创建一个带参数的储存过程 in
[code]
DELIMITER //
CREATE PROCEDURE procedureName(in p_id int(11))
BEGIN
SELECT * FROM test WHERE id=p_id;
END //
DELIMITER ;
CALL procedureName(1);
[/code]

#创建一个带参数的储存过程 out
[code]
DELIMITER //
CREATE PROCEDURE procedureName(OUT total int)
BEGIN
SELECT count(*) INTO total FROM test;
END //
DELIMITER ;
CALL procedureName(@total);
SELECT @total;
[/code]
#创建一个带参数的储存过程 inout
[code]
DELIMITER //
CREATE PROCEDURE procedureName(INOUT total int)
BEGIN
SET total=total+1;
END //
DELIMITER ;

SET @countTotal=1;
CALL procedureName(@countTotal);
SELECT @countTotal;
[/code]

#创建一个带参数的储存过程 返回多个值
[code]
DELIMITER //
CREATE PROCEDURE procedureName(
OUT idcount1 int,
OUT idcount2 int,
OUT idcount3 int,
OUT idcount4 int
)
BEGIN
SELECT COUNT(*) INTO idcount1 FROM test WHERE id>=1;
SELECT COUNT(*) INTO idcount2 FROM test WHERE id>=2;
SELECT COUNT(*) INTO idcount3 FROM test WHERE id>=4;
SELECT COUNT(*) INTO idcount4 FROM test WHERE id>=4;
END //
DELIMITER ;
CALL procedureName(@idcount1,@idcount2,@idcount3,@idcount4)
SELECT @idcount1,@idcount2,@idcount3,@idcount4
[/code]

#IF 判断
[code]
DELIMITER //
CREATE PROCEDURE procedureName(in p_id int(11),OUT res VARCHAR(20))
BEGIN
if(p_id>0 and p_id<=2) THEN
SET res=’aaa';
ELSEIF(p_id>2 and p_id<=4) THEN
SET res=’bbb';
ELSE
SET res=’ggg';
END IF;
END //
DELIMITER ;
CALL procedureName(5,@res);
SELECT @res
[/code]

#CASE WHEN 单条件
[code]
DELIMITER //
CREATE PROCEDURE procedureName(in p_id int(11),OUT res VARCHAR(20))
BEGIN
CASE p_id
WHEN 1 THEN
SET res=’aaa';
WHEN 2 THEN
SET res=’bbb';
WHEN 3 THEN
SET res=’ccc';
ELSE
SET res=’no';
END CASE;
END //
DELIMITER ;
CALL procedureName(1,@res);
SELECT @res
[/code]

#CASE WHEN 多条件
[code]
DELIMITER //
CREATE PROCEDURE procedureName(in p_id int(11),OUT res VARCHAR(20))
BEGIN
CASE
WHEN p_id>=1 AND p_id<=5 THEN
SET res=’aaa';
WHEN p_id>=6 AND p_id<=10 THEN
SET res=’bbb';
WHEN p_id>=11 AND p_id<=15 THEN
SET res=’ccc';
ELSE
SET res=’no';
END CASE;
END //
DELIMITER ;
CALL procedureName(44,@res);
SELECT @res
[/code]

#WHILE DO 满足条件的循环
[code]
DELIMITER //
CREATE PROCEDURE procedureName(OUT res VARCHAR(255))
BEGIN
DECLARE x INT;

SET x = 1;
SET res = ”;

WHILE x <= 5 DO
SET res = CONCAT(res,x,’,’);
SET x = x + 1;
END WHILE;
#SELECT res;
END //
DELIMITER ;
CALL procedureName(@res);
SELECT @res;
[/code]

#REPEAT UNTIL 不满足条件的循环
[code]
DELIMITER //
CREATE PROCEDURE procedureName(OUT res VARCHAR(255))
BEGIN
DECLARE x INT;

SET x = 1;
SET res = ”;

REPEAT
SET res = CONCAT(res,x,’,’);
SET x = x + 1;
UNTIL x > 5
END REPEAT;
#SELECT res;
END //
DELIMITER ;
CALL procedureName(@res);
SELECT @res;
[/code]

#loop LEAVE等于break ITERATE等于continue
[code]
DELIMITER //
CREATE PROCEDURE procedureName(OUT res VARCHAR(255))
BEGIN
DECLARE x INT;

SET x = 1;
SET res = ”;

loop_label: LOOP
IF x > 10 THEN
LEAVE loop_label;
END IF;

SET x = x + 1;
IF (x mod 2) THEN
ITERATE loop_label;
ELSE
SET res = CONCAT(res,x,’,’);
END IF;
END LOOP;
END //
DELIMITER ;
CALL procedureName(@res);
SELECT @res;
[/code]

#创建一个存储过程来获取用户的电子邮箱
[code]
DELIMITER //
CREATE PROCEDURE procedureName (INOUT email_list varchar(4000))
BEGIN
— 声明两个变量,是否是末尾,当前的邮箱账号
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_email varchar(100) DEFAULT "";

— 声明游标
DEClARE email_cursor CURSOR FOR SELECT email FROM users_info;

— 游标找不到数据的时候设置v_finished=1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

— 打开游标
OPEN email_cursor;

— LOOP 循环
get_email: LOOP

— 读取游标中的电子邮箱账号
FETCH email_cursor INTO v_email;

— 未找到数据的时候跳槽循环
IF v_finished = 1 THEN
LEAVE get_email;
END IF;

— 拼接邮箱账号
SET email_list = CONCAT(v_email,";",email_list);

END LOOP get_email;
CLOSE email_cursor;
END//
DELIMITER ;

CALL procedureName(@res);
SELECT @res;
[/code]

#删除一个储存过程
[code]
DROP procedure IF EXISTS `procedureName`;
[/code]

#查询demo库中的存储过程
[code]
SHOW PROCEDURE STATUS WHERE db=’demo';
[/code]

#查询存储过程的创建语句
[code]
SHOW CREATE PROCEDURE `procedureName`;
[/code]

#储存过程中的异常处理
[code]
DELIMITER $$
CREATE PROCEDURE procedureName(IN article_id INT, IN tag_id INT)
BEGIN

— 主键冲突的时候抛出异常提示,并继续执行CONTINUE 终止执行EXIT
DECLARE CONTINUE HANDLER FOR 1062
SELECT CONCAT(‘duplicate keys (‘,article_id,’,’,tag_id,’) found’) AS msg;

INSERT INTO article_tags(article_id,tag_id) VALUES(article_id,tag_id);

SELECT COUNT(*) FROM article_tags;
END$$
DELIMITER ;

CALL procedureName(1,2);
CALL procedureName(1,3);
CALL procedureName(1,2);
[/code]

#mysql自定义函数
自定义函数可以在sql中调用,而存储过程只能在单独调用;下面是一个返回用户等级的函数
[code]
DELIMITER $$
CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
DECLARE lvl varchar(10);

IF p_creditLimit > 50000 THEN
SET lvl = ‘1’;
ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
SET lvl = ‘2’;
ELSEIF p_creditLimit < 10000 THEN
SET lvl = ‘3’;
END IF;
RETURN (lvl);
END $$
DELIMITER ;

SELECT CustomerLevel(100000);
[/code]

#删除函数
[code]
DROP FUNCTION IF EXISTS `CustomerLevel`;
[/code]

#预先执行的建表预计
[code]
CREATE TABLE `article_tags` (
`article_id` int(11) NOT NULL,
`tag_id` int(11) NOT NULL,
PRIMARY KEY (`article_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`created` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE `users_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
[/code]

删除mysql中的重复数据

[code]
//创建表
CREATE TABLE users_info (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) DEFAULT NULL,
email VARCHAR(255) NOT NULL
);

//插入数据
INSERT INTO users_info (username,email)
VALUES (‘Carine ‘,’aa@qq.com’),
(‘Jean’,’aa@qq.com’),
(‘Peter’,’bb@qq.com’),
(‘Janine ‘,’bb@qq.com’),
(‘Jonas ‘,’cc@qq.com’),
(‘Janine ‘,’cc@qq.com’);

//查看超过一条的数据
SELECT email, COUNT(email) FROM users_info GROUP BY email HAVING COUNT(email) > 1;

//删除重复数据,保留id最大的
DELETE t1 FROM users_info as t1 INNER JOIN users_info as t2 WHERE t1.id<t2.id AND t1.email=t2.email

//删除重复数据,保留id最小的
DELETE t1 FROM users_info as t1 INNER JOIN users_info as t2 WHERE t1.id>t2.id AND t1.email=t2.email

//查看删除后的结果
SELECT * FROM users_info
[/code]

解决 mysql “is marked as crashed and should be repaired”故障

找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:
myisamchk -c -r ../data/tablename/comment.MYI

然后myisamchk 工具会帮助你恢复数据表的索引。好象也不用重新启动mysql,问题就解决了。

MySQL如何在高并发场景下更新并保持数据正确性

一张表 两个字段 一个id 一个useCount
表里存了100个id 每个id对应自己的useCount
业务场景是:当id每使用一次 useCount要加1。 当useCount大于1000时 这个id就不能在被使用了(换句话说 无法从数据库中查出)

在高并发情况下,会遇到一种问题:
假设数据表中有一条记录为 id=123456; useCount=999
a与b两个连接并发查询这个id 123456 都执行下列sql:

select * from table where id=123456 and useCount < 1000

a先执行 得到id 123456的useCount是999 之后在程序里做了一些逻辑判断或业务操作后执行sql: update useCount + 1
在a做判断且没有update之前 b也执行了查询sql 发现useCount是999 之后它也会执行sql: update useCount +1
但是 事实上b不应该取得这个id 因为a已经是第1000个使用者

所以请教一下 在高并发情况下 仅使用数据库 如何完成这一需求?

1、使用悲观锁:

select * from table where id=123456 and useCount < 1000 for update;

则 b 连接的事务 会被 a连接的事务block; 直到a的事务结束,b的select才能返回.

2、使用乐观锁:
每次update判断 useCount;
假设a,b连接都拿到 id 123456的useCount是999, update语句写为:

update table set useCount=useCount+1 where id=123456 and useCount=999;

程序中查询上述update返回值,如果为1则表明更新了一条记录, update成功;
如果为0, 则表示没有更新任何记录, 即useCount值已经被更改了, 则重新查询useCount,重新提交update.

在WEB中使用PHP连接MySQL的方法

PHP和MYSQL组合编程已经成为WEB开发中比较流行的一种方式,对于新手来讲,他们可能会问:PHP为什么能够操作MySQL数据库,PHP是如何和MySQL组合编程的呢,有哪些方式可以实现PHP连接mysql数据库呢?接下来,本文将一一来为你解答这些问题。

Q、PHP为什么能操作MySQL?

A、PHP安装目录中有一个名为ext的专门存放PHP扩展文件的目录,其中有一个叫php_mysql.dll的扩展,该扩展可以使用PHP能够操作MYSQL,当然使该扩展起作用,还得做相关的设置才可以用,那就是要在PHP配置文件php.ini中找到如下一行:

;extension=php_mysql.dll

去掉该行全面的分号,然后保存该文件,重启Apache服务器,该扩展就可以生效使用了。

Q、PHP连接MYSQL的方法有哪些呢?

A、有两种方式:第一种就是通过php的mysql相关函数来连接,另一种通过php的ODBC相关函数。这两种方法的连接过程都差不多,都是先建立连接,然后再通过PHP页面提交数据至MySQL,存储成功给出成功提示,不成功则提示失败。

Q、如何实现PHP和MySQL组合编程?

A、通过开启PHP扩展库(php_mysql.dll、php_mysqli.dll、php_pdo_mysql.dll、php_pdo_odbc.dll),再通过这些扩展的函数,我们就可以实现PHP+MySQL组合编程了。

附:PHP操作MySQL的相关函数

1、MYSQL函数
mysql_affected_rows: 得到 MySQL 最后操作影响的列数目。
mysql_close: 关闭 MySQL 伺服器连线。
mysql_connect: 开启 MySQL 伺服器连线。
mysql_create_db: 建立一个 MySQL 新资料库。
mysql_data_seek: 移动内部传回指标。
mysql_db_query: 送查询字串 (query) 到 MySQL 资料库。
mysql_drop_db: 移除资料库。
mysql_errno: 传回错误讯息代码。
mysql_error: 传回错误讯息。
mysql_fetch_array: 传回阵列资料。
mysql_fetch_field: 取得栏位资讯。
mysql_fetch_lengths: 传回单列各栏资料最大长度。
mysql_fetch_object: 传回物件资料。
mysql_fetch_row: 传回单列的各栏位。
mysql_field_name: 传回指定栏位的名称。
mysql_field_seek: 设定指标到传回值的某栏位。
mysql_field_table: 获得目前栏位的资料表 (table) 名称。
mysql_field_type: 获得目前栏位的型态。
mysql_field_flags: 获得目前栏位的旗标。
mysql_field_len: 获得目前栏位的长度。
mysql_free_result: 释放传回占用记忆体。
mysql_insert_id: 传回最后一次使用 INSERT 指令的 ID。
mysql_list_fields: 列出指定资料表的栏位 (field)。
mysql_list_dbs: 列出 MySQL 伺服器可用的资料库 (database)。
mysql_list_tables: 列出指定资料库的资料表 (table)。
mysql_num_fields: 取得传回栏位的数目。
mysql_num_rows: 取得传回列的数目。
mysql_pconnect: 开启 MySQL 伺服器长期连线。
mysql_query: 送出一个 query 字串。
mysql_result: 取得查询 (query) 的结果。
mysql_select_db: 选择一个资料库。
mysql_tablename: 取得资料表名称。

2、ODBC函数(使用ODBC函数需安装MYSQL ODBC)
odbc_autocommit: 开关自动更动功能。
odbc_binmode: 设定二进位资料处理方式。
odbc_close: 关闭 ODBC 连结。
odbc_close_all: 关闭所有 ODBC 连结。
odbc_commit: 更动 ODBC 资料库。
odbc_connect: 连结至 ODBC 资料库。
odbc_cursor: 取得游标名。
odbc_do: 执行 SQL 指令。
odbc_exec: 执行 SQL 指令。
odbc_execute: 执行预置 SQL 指令。
odbc_fetch_into: 取得传回的指定列。
odbc_fetch_row: 取得传回一列。
odbc_field_name: 取得栏位名称。
odbc_field_type: 取得栏位资料形态。
odbc_field_len: 取得栏位资料长度。
odbc_free_result: 释出传回资料的记忆体。
odbc_longreadlen: 设定传回栏的最大值。
odbc_num_fields: 取得栏位数目。
odbc_pconnect: 长期连结至 ODBC 资料库。
odbc_prepare: 预置 SQL 指令。
odbc_num_rows: 取得传回列数目。
odbc_result: 取得传回资料。
odbc_result_all: 传回 HTML 表格资料。
odbc_rollback: 撤消当前交易。
odbc_setoption: 调整 ODBC 设定。

MySQ数据库的备份方法

当一个公司拥有海量的数据信息存储在数据库的时候,数据的安全性和稳定性就显得尤为重要,而对数据进行备份就是一种比较可行的解决方案。在MySQL数据库中我们如何对已经存储的数据信息进行备份呢,看完这篇文章也许你就有眉目了。

第一种方法在复制数据文件时使用LOCK TABLES命令锁定这些表。该命令语法如下所示:

LOCK TABLE table lock_type [, table lock_type …]

每一个表必须是表的名称,而锁定的类型可以是READ或WRITE。对于备份来说,只需要READ锁定。在执行备份之前,必须执行FLUSH TABLES;命令来确保对索引所做的任何修改将写入到磁盘。

在执行备份时,用户和脚本还可以运行只读查询。如果有大量可以修改数据库的查询,这种解决方案并不可行。

第二种方法,是一种比较好的方法,那就是使用mysql_dump命令。该命令是在操作系统的命令行下使用的,该命令的典型用法如下所示:

mysqldump –opt –all-databases > all.sql

上述命令将所有用来重新构建一个SQL数据库所需的内容都导出到一个名为all.sql的文件中。之后,可以停止mysqld进程,并且通过 –log-bin[=logfile]命令选项重新启动它。保存在日志文件中的更新将给出上次备份后数据库发生的变化。在任何常规文件的备份中,还应该备份所有日志文件的。

第三种方法是使用mysqlhotcopy脚本。该脚本的使用方法如下:

mysqlhotcopy database /path/for/backup

然后,必须遵照前面介绍的启动和停止数据库的操作。

最后一个方法是维护数据库的一个副本。副本也就是将一个数据库复制成多个,复制是一个允许提供相同数据库的多个数据库服务器的技术。通过复制数据库,可以载入共享并提高系统可靠性;如果和个服务器停止运行,其他服务器还能继续工作。复制一旦设置成功,它也可以用作备份。