1.常用MySQL语句
1.1 数据库操作
show databases;
use database_name;
drop database if not exists database_name;
1.2 数据表操作
1.2.1 数据表基本操作语句
show tables;
desc table_name;
alter table table_name rename to/as new_table_name
1.2.2 创建表
drop table if exists table_name;
create table table_name(
field_name varcher(10) primary key,
field_name double unique not null,
...
field_name tinyint default 0,
field_name timestamp default current_timestamp,
field_name timestamp default current_timestamp on update current_timestamp,
constraint index_name foreign key(current_table_field) references target_table(primary_key)
);
comment on table table_name is '备注';
comment on column table_name.field_name is '备注';
1.2.3 表存在添加/删除约束
alter table table_name add/drop check_name<primary key | unique>(field_name)
alter table table_name add constraint check_name foreign key(current_table_field)
references target_table(primary_key)
alter table table_name drop foreign key check_name
alter table table_name drop index check_name
1.2.4 设置自动增长/更新字段类型和是否NULL
alter table table_name modify field_name int auto_increment
alter table table_name modify field_name int
alter table table_name modify field_name 数据类型 NULL/NOT NULL
1.2.5 设置/删除表字段默认值
alter table table_name alter field_name set default '值'
alter table table_name alter field_name drop default
1.2.6 新增字段/改名
alter table table_name add column field_name varchar(10)
alter table table_name change column field_name new_field_name varchar(10)
1.3 用户权限
show grants for username@host
create user username@host IDENTIFIED BY 'password';
drop user username@host
databasename:数据库名 tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用表示,如.*)
grant/revoke privileges ON databasename.tablename TO username@host