第3章PHP数据库编程.ppt

生物信息学数据库设计;*;*;mysql命令行工具 1. 调用mysql 命令提示符下调用mysql： mysql -hhostname -uusername -ppassword 其中hostname指定要连接的MySQL服务器的主机名，若要连接本机 上的MySQL服务器，主机名可用localhost表示；username指定用户 名，如root；password表示登录密码，如123456。若使用了-p选项而 未指定密码，则会显示“Enter Password:”，提示输入密码。 ;创建、显示和删除数据库 1. 创建数据库 CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name 2. 显示数据库列表 SHOW DATABASES [LIKE pattern] 3. 删除数据库 DROP {DATABASE | SCHEMA} [IF EXISTS] db_name ;表的创建与维护 1. 表的创建 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name ( column_definition, ... ) [CHARACTER SET charset_name] [COLLATE collation_name] [COMMENT string] 2. 查看表信息 SHOW [FULL] TABLES [FROM db_name] [LIKE pattern] 3. 查看列信息 SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE pattern] ;4. 表的修改 ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification： ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition , ...) | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | RENAME [TO] new_tbl_name 5. 表的重命名 RENAME TABLE tbl_name TO new_tbl_name [ , tbl_name2 TO new_tbl_name2] ... 6. 表的删除 DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [ , tbl_name] ... ; 插入记录 1. 使用指定值插入记录 INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name , ...)] VALUES ( { expr | DEFAULT },... ) , ( ... ) , ... [ ON DUPLICATE KEY UPDATE col_name=expr , ... ] 2. 从一个表向另一个表插入记录 INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] ;查询记录 SELECT select_expr, ... [ INTO OUTFILE file_name export_options | INTO DUMPFILE file_name] [ FROM table_ref