Linux下MySQL configure参数

–prefix=/program/mysql 安装路径

–localstatedir=/var/lib/mysql 数据库文件存放路径

–with-comment=Source

–with-server-suffix=-Community

–with-mysqld-user=mysql

–without-debug

–with-big-tables

–with-charset=gbk utf8

–with-collation=gbk_chinese_ci utf8_unicode_ci

–with-extra-charsets=all

–with-pthread

–enable-static

–enable-thread-safe-client

–with-client-ldflags=-all-static

–with-mysqld-ldflags=-all-static

–enable-assembler

–without-innodb

–without-isam

–without-ndb-debug

–with-unix-socket-path=/home/mysql/tmp/mysql.sock

Table ‘xxxx’ is marked as crashed…

今天VODCMS的管理员反映无法登陆后台,我想可能是Apache或者数据库又出错了。

重启了下Apache,正如所料想的一样,问题依旧。

查看了一下数据库的表,发现有个表出错了,修复不能!

Table Op Msg_type Msg_text
vodcms.vodcms_members optimize Error Table ‘.\vodcms\vodcms_members’ is marked as crash…
vodcms.vodcms_members optimize Error Table ‘vodcms_members’ is marked as crashed and la…
vodcms.vodcms_members optimize error Corrupt

网上搜了搜,发现这个错误很严重,数据基本上没有了,不管用MySQL提供的修复工具还是PhpMyAdmin都修复不能,查看了下data文件夹,发现少了个*.MYD文件。

所以呢,从新从网上下了个VODCMS5.0将错误的表重新创建,数据重新导入,

要是错误出在其他的重要的数据表上的话问题可就严重了。

记得前些时候BBS的post表也出过错,不过能修复好,要是出现这样的错误可就严重了。

所以以后记得要随时备份数据库。

MySQL外键(foreign key)的用法

在MySQL中MyISAM和InnoDB存储引擎都支持外键(foreign key),但是MyISAM只能支持语法,却不能实际使用。下面通过例子记录下InnoDB中外键的使用方法:

创建主表:
mysql> create table parent(id int not null,primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.04 sec)
创建从表:
mysql> create table child(id int,parent_id int,foreign key (parent_id) references parent(id) on delete cascade) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

插入主表测试数据:
mysql> insert into parent values(1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
插入从表测试数据:
mysql> insert into child values(1,1),(1,2),(1,3),(1,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test/child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE)

因为4不在主表中,插入时发生了外键约束错误。

只插入前三条:
mysql> insert into child values(1,1),(1,2),(1,3);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
成功!

删除主表记录,从表也将同时删除相应记录:
mysql> delete from parent where id=1;
Query OK, 1 row affected (0.03 sec)
mysql> select * from child;
+——+———–+
| id | parent_id |
+——+———–+
| 1 | 2 |
| 1 | 3 |
+——+———–+
2 rows in set (0.00 sec)

更新child中的外键,如果对应的主键不存在,则报错:
mysql> update child set parent_id=4 where parent_id=2;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test/child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE)

如果改为主表中存在的值,则可以正常更新:
mysql> update child set parent_id=2 where parent_id=2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0

如果要在父表中更新或者删除一行,并且在子表中也有一行或者多行匹配,此时子表的操作有5个选择:
· CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在两个表之间,你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。
· SET NULL: 从父表删除或更新行,并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词,这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。
· NO ACTION: 在ANSI SQL-92标准中,NO ACTION意味这不采取动作,就是如果有一个相关的外键值在被参考的表里,删除或更新主要键值的企图不被允许进行(Gruber, 掌握SQL, 2000:181)。 InnoDB拒绝对父表的删除或更新操作。
· RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样,删除ON DELETE或ON UPDATE子句。(一些数据库系统有延期检查,并且NO ACTION是一个延期检查。在MySQL中,外键约束是被立即检查的,所以NO ACTION和RESTRICT是同样的)。
· SET DEFAULT: 这个动作被解析程序识别,但InnoDB拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定义。

MySQL视图简介

一. 视图概述

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

对其中所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。

视图是存储在数据库中的查询的SQL 语句,它主要出于两种原因:安全原因, 视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。

  视图:查看图形或文档的方式。

  视图是从一个或多个表或视图中导出的表,其结构和数据是建立在对表的查询基础上的。和表一样,视图也是包括几个被定义的数据列和多个数据行,但就本质而言这些数据列和数据行来源于其所引用的表。

  所以视图不是真实存在的基础表而是一张虚表,视图所对应的数据并不实际地以视图结构存储在数据库中,而是存储在视图所引用的表中。

  视图一经定义便存储在数据库中,与其相对应的数据并没有像表那样又在数据库中再存储一份,通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样,可以对其进行查询、修改(有一定的限制)、删除。

  当对通过视图看到的数据进行修改时,相应的基本表的数据也要发生变化,同时,若基本表的数据发生变化,则这种变化也可以自动地反映到视图中。

  视图有很多优点,主要表现在:

  •视点集中
  •简化操作
  •定制数据
  •合并分割数据
  •安全性

二. 创建视图——CREATE VIEW

1. 语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]通过该语句可以创建视图,若给定了[OR REPLACE],则表示当已具有同名的视图时,将覆盖原视图。select_statement是一个查询语句,这个查询语句可从表或其它的视图中查询。视图属于数据库,因此需要指定数据库的名称,若未指定时,表示在当前的数据库创建新视图。

表和数据库共享数据库中相同的名称空间,因此,数据库不能包含相同名称的表和视图,并且,视图的列名也不能重复。

2. 使用举例

Eg. 本例创建一个产品表(product)和一个购买记录表(purchase),再通过视图purchase_detail查询出购买的详细信息。

CREATE TABLE product

(

product_id INT NOT NULL,

name VARCHAR(50) NOT NULL,

price DOUBLE NOT NULL

);

INSERT INTO product VALUES(1, ‘apple ‘, 5.5);

CREATE TABLE purchase

(

id INT NOT NULL,

product_id INT NOT NULL,

qty INT NOT NULL DEFAULT 0,

gen_time DATETIME NOT NULL

);

INSERT INTO purchase VALUES(1, 1, 10, NOW());

CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

创建成功后,输入:SELECT * FROM purchase_detail;

运行效果如下:

+——-+——-+—–+————-+

| name | price | qty | total_value |

+——-+——-+—–+————-+

| apple | 5.5 | 10 | 55 |

+——-+——-+—–+————-+

1 row in set (0.01 sec)

3. 注意事项

创建视图存在如下注意事项:

(1) 运行创建视图的语句需要用户具有创建视图(CRATE VIEW)的权限,若加了[OR REPLACE]时,还需要用户具有删除视图(DROP VIEW)的权限;

(2) SELECT语句不能包含FROM子句中的子查询;

(3) SELECT语句不能引用系统或用户变量;

(4) SELECT语句不能引用预处理语句参数;

(5) 在存储子程序内,定义不能引用子程序参数或局部变量;

(6) 在定义中引用的表或视图必须存在。但是,创建了视图后,能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题,可使用CHECK TABLE语句;

(7) 在定义中不能引用TEMPORARY表,不能创建TEMPORARY视图;

(8) 在视图定义中命名的表必须已存在;

(9) 不能将触发程序与视图关联在一起;

(10) 在视图定义中允许使用ORDER BY,但是,如果从特定视图进行了选择,而该视图使用了具有自己ORDER BY的语句,它将被忽略。

三. 修改视图——ALTER VIEW

1. 语法

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。

2. 使用举例

Eg. 将上一小节中中创建的视purchase_detail进行修改,去掉qty列,语句如下:

ALTER VIEW purchase_detail AS SELECT product.name as name, product .price as price, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

此时通过语句:select * from purchase_detail;对视图进行查询时,结果如下:

+——-+——-+————-+

| name | price | total_value |

+——-+——-+————-+

| apple | 5.5 | 55 |

+——-+——-+————-+

3. 注意事项

修改视图的注意事项除了第一条外跟创建视图的注意事项是一样的。第(1)条应改为:

该语句需要具有针对视图的CREATE VIEW和DROP权限,也需要针对SELECT语句中引用的每一列的某些权限。

四. 删除视图——DROP VIEW

1. 语法

DROP VIEW [IF EXISTS] view_name [, view_name] … [RESTRICT | CASCADE]该语句用户删除视图,可一次删除多个视图。[IF EXISTS]选项确保语句正确运行。若没有该子句,当指定的视图不存在时,将发生错误。

2. 使用举例

Eg1. 删除在前面的小节中创建的视图purchase_detail:DROP VIEW purchase_detail;

Eg2. 删除一个未知的视图:DROP VIEW IF EXISTS test_view;

Eg3. 删除多个视图:DROP VIEW IF EXISTS test_view1, test_view2;

3. 注意事项

必须对要删除的一个或多个视图拥有DROP VIEW的权限。

update的多表更新

在开发中,数据库来回换,而有些关键性的语法又各不相同,这是一件让开发人员很头痛的事情.本文总结了Update语句更新多表时在SQL Server,Oracle,MySQL三种数据库中的用法.我也试了SQLite数据库,都没成功,不知是不支持多表更新还是咋的. 在本例中: 我们要用表gdqlpj中的gqdltks,bztks字段数据去更新landleveldata中的同字段名的数据,条件是当landleveldata 中的GEO_Code字段值与gdqlpj中的lxqdm字段值相等时进行更新.


SQL Server语法:UPDATE { table_name WITH ( < table_hint_limited > [ …n ] ) | view_name | rowset_function_limited } SET { column_name = { expression | DEFAULT | NULL } | @variable = expression | @variable = column = expression } [ ,…n ] { { [ FROM { < table_source > } [ ,…n ] ] [ WHERE < search_condition > ] } | [ WHERE CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name } ] } [ OPTION ( < query_hint > [ ,…n ] ) ]


SQL Server示例: update a set a.gqdltks=b.gqdltks,a.bztks=b.bztks from landleveldata a,gdqlpj b where a.GEO_Code=b.lxqdm






Oracle语法: UPDATE updatedtable SET (col_name1[,col_name2…])= (SELECT col_name1,[,col_name2…] FROM srctable [WHERE where_definition])

 

Oracel 示例: update landleveldata a set (a.gqdltks, a.bztks)= (select b.gqdltks, b.bztks from gdqlpj b where a.GEO_Code=b.lxqdm)

 

 

 

 


MySQL语法: UPDATE table_references SET col_name1=expr1 [, col_name2=expr2 …] [WHERE where_definition]

 

MySQL 示例: update landleveldata a, gdqlpj b set a.gqdltks= b.gqdltks, a.bztks= b.bztks where a.GEO_Code=b.lxqdm

 

 

 

PS:

不知道是什么原因,我试了一个多小时进行MYSQL的UPDATE多表更新总是失败,语法是没有错误的。

最后发现要是几个表中有表没有值,即为空的情况下,多表更新操作就会失败,

所以,我还是老实巴交地分别更新吧哎。