数据库命令大总结
创建数据库(MYSQL)
create database db_name;
修改数据库
alter database db_mane ....
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification [, alter_specification] ...
alter_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
删除数据库
drop database db_mane;
创建表
create table tab_name
(
field_name1 DataType(size) not null auto_increment,
field_name2 DataType(size),
.......,
primary key(field_name),
index(field_name)
)engine=myIsam;
修改表
alter table tab_name
add field_name dt not null default ' '
drop (field_name/primary key/ index)
modify field_name column_definition [FIRST | AFTER col_name]
删除表
删除表内容:delete from tab_name where condition!
删除整表:drop table tab_name ;
表重名
rename table tab_name,tab_name2 to new_tab_name, new_tab_name;
插入数据
insert into tab_name (fieldname,field2))
更新数据
update tab_name set field_name="" where field_name=""
查询数据
select field_name/ * from tab_name where condiction;
数据排序
select field_name/* from tab_name where condition order by field_name;
分组
select field_name/* from tab_name where condition group by field_name;
连接
提供通往表存储引擎接口的直接通道,HANDLER可以用于MyISAM和InnoDB表
HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE
函数
(1)创建函数
CREATE FUNCTION fun_name (param_name datatype)
RETURNS type
(2)修改函数
ALTER FUNCTION fun_name
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
(3)删除函数
DROP FUNCTION IF EXISTS fun_name;
(4)显示函数的语句内容
SHOW FUNCTION STATUS LIKE '......';
SHOW CREATE FUNCTION proc_name
(5)执行函数
EXECUTE fum_name [USING @var_name [, @var_name] ...];
触发器
delimiter |
CREATE TRIGGER trigger_name before/after
insert/update/delete
ON tbl_name FOR EACH ROW trigger_stmt
delimiter
过程
指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。(FUNCTION参数总是被认为是IN参数)
RETURNS字句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。
(1)创建过程
CREATE PROCEDURE proc_name (IN/OUT/INOUT parameter1.....parameterN DataType)
BEGIN
DECLARE VARIAL;
STATEMENTS; (INSERT, UPDATE,DELETE, SELECT, DROP, CREATE, REPLACE)
END
(2)修改过程
ALTER PROCEDURE PROC_NAME
BEGIN
DECLARE .......;
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
END;
(3)删除过程
DROP PROCEDURE [IF EXISTS] proc_name
(4)查看存储过程(返回一个可用来重新创建已命名子程序的确切字符串。)
SHOW PROCEDURE STATUS LIKE '......' ;
SHOW CREATE PROCEDURE proc_name
(5)调用过程
call proc_name(parameter p1)
CALL语句可以用声明为OUT或的INOUT参数的参数给它的调用者传回值。它也“返回”受影响的行数,客户端程序可以在SQL级别通过调用ROW_COUNT()函数获得这个数,
视图
(1)创建视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
(2)修改视图
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
(3)删除视图
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
(4)显示视图的建立语句
SHOW CREATE VIEW view_name
索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type](数据库引擎myIsam/innodb/memory/heap)
ON tbl_name (index_col_name,...)
注:以下语句会导致即时commit当前事务
ALTER FUNCTION ALTER PROCEDURE ALTER TABLE
BEGIN CREATE DATABASE CREATE FUNCTION
CREATE INDEX CREATE PROCEDURE CREATE TABLE
DROP DATABASE DROP FUNCTION DROP INDEX
DROP PROCEDURE DROP TABLE UNLOCK TABLES
LOAD MASTER DATA LOCK TABLES RENAME TABLE
trUNCATE TABLE SET AUTOCOMMIT=1 START TRANSACTION