MySQL建视图：
CREATE VIEW view_name AS SELECT * FROM table1;
ALTER VIEW view_name AS SELECT * FROM table1;
建触发器
create trigger trig_name
before insert on table_name
for each row
begin
insert into table2 set field2 = NEW.field1;
delete from table3 where field3 = NEW.field1;
update table4 set field4 = field4+1 where field4 = NEW.field1;
end
table_name必须是永久性表，不能是视图或临时表
trigger_time可为：before,after
trigger_event可为：insert,update,delete
建存储过程：
create procedure storage_proc(IN onumber INT, OUT ototal DECIMAL(8, 2) )
declare var_name INT default 0;
begin
select Sum(item_price* quantity) from orderitems where order_num = onumber into ototal;
end;
调用时： CALL storage_proc(5)
建索引（index）:
create [unique|fulltext|primary] index index_name on tbl_name (index_col_name(length));
注：对于BLOB、TEXT等长度可变的类型，必须指定length；
alter table_name ADD UNIQUE index_name on (username(length));
建事务（Transaction）：
以下为一个表锁（从t1读取并更新t2为一个原子操作）
LOCK TABLES t1 READ, t2 WRITE;
select sum(field1) from t1 where customer_id = certain_Value;
update t2 set field3 = summ where customer_id = certain_Value;
UNLOCK TABLES;
新增用户：
CREATE USER user_name IDENTIFIED BY 'password';
授权命令：
GRANT select[,update][,delete] ON table_name TO user_name;
grant all privileges on *.* to dbuser_name;
grant all privileges on *.* to dbuser_name@localhost with grant option;
grant all privileges on *.* to dbuser_name@'%' with grant option;
创建新用户并授予权限
grant all privileges on *.* to dbuser_name@'%' IDENTIFIED BY 'password' with grant option; flush privileges;
数据导入：
1. $ mysql db_name -u root -p < data.sql
2. mysql> source data.sql
3. $ mysqlimport -u root -p -h 10.21.0.96 db_name data.sql
4. mysql>load data file data.sql into table table_name
注：mysqlimport [-d/f/i/r/--field-terminated-by=char/--line-terminated-by=char/-lock-tables/]
[导入前清空/遇错仍继续/key重复则跳过/key重复则替换/字段隔离符/行隔离符/数据被插入之前锁表]
数据导出：
1. mysqldump -u root -p db_name > data.sql 即将数据库db_name的数据全部导出至data.sql文件中
2. select * from table_name into outfile data.sql 或 select * into outfile data.sql from table_name
数据备份： backup table table_name to '/home/mysqldb/backup/'
数据恢复： restore table table_name from '/home/mysqldb/backup/'
[Mysql配置及优化]
对mysql配置的修改，可以改/etc/mysql/my.cnf文件
查看某属性当前的值： mysql> show variables like '%query_cache%';
[存储引擎]
创建表时可以指定其存储引擎的类型： create table student(sno int(not null), sname char(20)) engine = InnoDB; 亦可修改： alter table student engine = MyISAM;
InnoDB与MyISAM两者的比较：
1. MyISAM不支持事务、外键约束等高级特性，InnoDB则支持；
2. MyISAM性能更好，执行速度快，InnoDB则强调安全性；
3. MyISAM支持表级锁定，InnoDB则进一步支持行级锁定；
4. 存储相同的内容，MyISAM所需的存储空间比InnoDB少；
5. MyISAM支持全文索引，而InnoDB不支持；
php.ini文件位于 /etc/php5/apache2
Apache项目目录：/var/www/