mysql $的用法_Mysql一些基础用法

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/