1 --创建表空间 2 create tablespace tablespace_name 3 innodb and ndb: 4 add datafile 'file_name' 5 innodb only: 6 [file_block_size = value] 7 ndb only: 8 use logfile group logfile_group 9 [extent_size [=] extent_size] 10 [initial_size [=] initial_size] 11 [autoextend_size [=] autoextend_size] 12 [max_size [=] max_size] 13 [nodegroup [=] nodegroup_id] 14 [wait] 15 [comment [=] comment_text] 16 innodb and ndb: 17 [engine [=] engine_name] 18 --创建数据库 19 create {database | schema} [if not exists] db_name 20 [ 21 create_specification 22 [, create_specification] ... 23 ] 24 create_specification: 25 [default] character set charset_name--指定数据库字符集（Charset）， 26 |[default] collate collation_name--指定字符集的校对规则，collation_name为校对规则名称 27 --修改数据库 28 alter {database | schema} [db_name] 29 alter_specification [, alter_specification] ... 30 alter_specification: 31 --删除数据库 32 drop database [if exists] db_name 33 --创建表 34 create [temporary] table [if not exists] tbl_name 35 [( [column_definition],...|[index_definition])] 36 [table_option] 37 [select_statement]; 38 column_definition: 39 col_name type 40 [not null | null] 41 [default default_value] 42 [auto_increment]--设置自增属性，只有整型列才能设置此属性。当插入NULL值或0到一个AUTO_INCREMENT列中时，列被设置为value+1， 43 --在这里value是此前表中该列的最大值。AUTO_INCREMENT顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且它必须被索引。 44 [unique [key]| 45 [primary] key] 46 [comment 'string'] 47 [reference_definition] 48 reference_definition: 49 references tbl_name [(index_col_name,...)] 50 [on delete {restrict | cascade | set null | no action}] 51 [on update {restrict | cascade | set null | no action}] 52 index_definition: 53 [constraint [symbol]]primary key [index_type] (index_col_name,...) /*主键*/ 54 |{index | key} [index_name] [index_type] (index_col_name,...) /*索引*/ 55 |[constraint [symbol]] unique [index] [index_name] [index_type] (index_col_name,...)/*唯一性索引*/ 56 |[fulltext|spatial] [index] [index_name] (index_col_name,...) /*全文索引*/ 57 |[constraint [symbol]] foreign key [index_name] (index_col_name,...) [reference_definition]/*外键*/ 58 }] 59 table_option: 60 {engine | type} = engine_name /*存储引擎*/ 61 | auto_increment = value /*初始值*/ 62 | avg_row_length = value /*表的平均行长度*/ 63 | [default] character set charset_name [collate collation_name] /*默认字符集和校对*/ 64 | checksum = {0 | 1} /*设置为1表示求校验和*/ 65 | comment = 'string' /*注释*/ 66 | connection = 'connect_string' /*连接字符串*/ 67 | max_rows = value /*行的最大数*/ 68 | min_rows = value /*列的最小数*/ 69 | pack_keys = {0 | 1 | default} 70 | password = 'string' /*对.frm文件加密*/ 71 | delay_key_write = {0 | 1} /*对关键字的更新*/ 72 | row_format = {default|dynamic|fixed|compressed|redundant|compact}/*定义各行应如何储存*/ 73 | union = (tbl_name[,tbl_name]...) /*表示哪个表应该合并*/ 74 | insert_method = { no | first | last } /*是否执行insert语句*/ 75 | data directory = 'absolute path to directory' /*数据文件的路径*/ 76 | index directory = 'absolute path to directory' /*索引的路径*/ 77 select_statement: 78 --修改表 79 alter [ignore] table tbl_name 80 alter_specification [, alter_specification] ... 81 alter_specification: 82 add [column] column_definition [first | after col_name ] /*添加列*/ 83 | alter [column] col_name {set default literal | drop default} /*修改默认值*/ 84 | change [column] old_col_name column_definition [first|after col_name] /*对列重命名*/ 85 | modify [column] column_definition [first | after col_name] /*修改列类型*/ 86 | drop [column] col_name /*删除列*/ 87 | rename [to] new_tbl_name /*重命名该表*/ 88 | order by col_name /*排序*/ 89 | convert to character set charset_name [collate collation_name] /*将字符集转换为 90 二进制*/ 91 | [default] character set charset_name [collate collation_name] /*修改默认字符集*/ 92 | table_options 93 | 列或表中索引项的增、删、改 94 --复制表 95 create [temporary] table [if not exists] tbl_name 96 [ () like old_tbl_name [ ] ]--创建一个一模一样的空表 97 | [as (select_statement)];--复制创建表并复制内容，但索引和完整性约束是不会复制的 98 --删除表 99 drop [temporary] table [if exists] tbl_name [, tbl_name] ... 100 -------------------------------------------------------------------------------------------------------------------- 101 --创建索引 102 create [unique | fulltext | spatial] index index_name--spatial表示为空间索引 103 [using index_type]--为存储引擎支持的索引类型的名称(btree|hash),默认btree 104 on tbl_name (index_col_name,...) 105 index_col_name: 106 col_name [(length)] [asc | desc]--length表示使用列的前length个字符创建索引。 107 --alter table语句 108 alter [ignore] table tbl_name 109 add index [index_name] [index_type] (index_col_name,...) /*添加索引*/ 110 | add [constraint [symbol]] primary key [index_type] (index_col_name,...)/*添加主键*/ 111 | add [constraint [symbol]]unique [index_name] [index_type] (index_col_name,...)/*添加唯一性索引*/ 112 | add [fulltext | spatial] [index_name] (index_col_name,...) /*添加全文索引*/ 113 | add [constraint [symbol]] foreign key [index_name] (index_col_name,...)[reference_definition]/*添加外键*/ 114 | disable keys--只在MyISAM表中有用，使用ALTER TABLE...DISABLE KEYS可以让MySQL在更新表时停止更新MyISAM表中的非唯一索引， 115 | enable keys --然后使用ALTER TABLe ... enable keys重新创建丢失的索引，这样可以大大地加快查询的速度。 116 --删除索引 117 drop index index_name on tbl_name 118 --添加用户 119 CREATE USER 120 user [auth_option] [, user [auth_option]] ... 121 122 user: 123 (see Section 6.2.3, “Specifying Account Names”) 124 125 auth_option: 126 { 127 IDENTIFIED BY 'auth_string' 128 | IDENTIFIED BY PASSWORD 'hash_string' 129 | IDENTIFIED WITH auth_plugin 130 | IDENTIFIED WITH auth_plugin AS 'hash_string' 131 } 132 --删除用户 133 drop user user [, user_name] ... 134 --修改用户名 135 rename user old_user to new_user, 136 [, old_user to new_user] ... 137 --修改密码 138 SET PASSWORD [FOR user]= PASSWORD('newpassword') 139 --例 140 create user 'wyl'@'localhost' identified with sha256_password; 141 set old_passwords = 2; 142 set password for 'wyl'@'localhost' = password('www123'); 143 ------------------------------------------------------------------------------------------------------------- 144 --grant语法格式 145 grant priv_type [(column_list)] [, priv_type [(column_list)]] ... 146 on [object_type] {tbl_name | * | *.* | db_name.*} 147 to user [identified by [password] 'password'] 148 [, user [identified by [password] 'password']] ... 149 [with with_option [with_option] ...] 150 priv_type: 151 insert,select、update、delete、references、create、alter、index、drop、all或all privileges(表) 152 SELECT、INSERT、DELETE、UPDATE、REFERENCES、CREATE、ALTER、INDEX、DROP、CREATE TEMPORARY TABLES、CREATE VIEW、SHOW VIEW、 153 CREATE ROUTINE、ALTER ROUTINE、EXECUTE ROUTINE、LOCK TABLES、ALL或ALL PRIVILEGES 154 object_type: 155 table 156 | function 157 | procedure 158 with_option ：--表示TO子句中指定的所有用户都有把自己所拥有的权限授予其他用户的权利，而不管其他用户是否拥有该权限。 159 grant option 160 | max_queries_per_hour count 161 | max_updates_per_hour count 162 | max_connections_per_hour count 163 | max_user_connections count 164 --回收权限 165 revoke priv_type [(column_list)] [, priv_type [(column_list)]] ... 166 on {tbl_name | * | *.* | db_name.*} 167 from user [, user] ... 168 --或者： 169 revoke all privileges, grant option from user [, user] ... 170 --数据库恢复 171 --导出（使用sql） 172 select * from table_name into outfile 'file_name' export_options | dumpfile 'file_name' 173 export_options: 174 [fields 175 [terminated by 'string']--用来指定字段值之间的符号,如',' 176 [[optionally] enclosed by 'char']--用来指定包裹文件中字符值的符号,如'"' 177 [escaped by 'char' ]--用来指定转义字符 178 ] 179 [lines terminated by 'string' ]--指定一行结束的标志 180 --默认 181 fields terminated by ' ' enclosed by '' escaped by '\' 182 lines terminated by ' ' 183 --如果使用DUMPFILE而不是使用OUTFILE，导出的文件里所有的行都彼此紧挨着放置，值和行之间没有任何标记，成了一个长长的值。 184 --导入（使用sql） 185 load data [low_priority | concurrent] [local] infile 'file_name.txt'-- 186 [replace | ignore] 187 into table tbl_name 188 [fields 189 [terminated by 'string'] 190 [[optionally] enclosed by 'char'] 191 [escaped by 'char' ] 192 ] 193 [lines 194 [starting by 'string'] 195 [terminated by 'string'] 196 ] 197 [ignore number lines] 198 [(col_name_or_user_var,...)] 199 [set col_name = expr,...)] 200 --使用mysqldump备份数据 (可以用source导入) 201 mysqldump [OPTIONS] database [tables] 202 OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] > filename 203 OR mysqldump [OPTIONS] --all-databases [OPTIONS] > filename 204 options: 205 tables: 206 --使用mysqlimport导入数据(只能跟据文件名导入表) 207 mysqlimport [options] db_name filename ... 208 --使用binlog 209 --1.启用日志： 210 --在my.ini mysqld 后面加一句 log-bin 211 --2.重启服务 212 net stop mysql 213 net start mysql 214 --3.恢复 215 mysqlbinlog bin_log.000001 | mysql -uroot -p12345 216 --4.删除日志（如果日志太多） 217 reset master;--删除所有日志 218 purge {master | binary} logs to 'log_name'--按日志名称删除 219 --或 220 purge {master | binary} logs before 'date'--按时间删除 221 --例 222 purge master logs to 'shay3wwx2945501-bin.000003'