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重复则替换/字段隔离符/⾏隔离符/数据被插⼊之前锁表]
数据导出：
mysql视图和存储过程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/myf⽂件
查看某属性当前的值： 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/

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。