mysql序号重拍_mysql 命令行基本操作命令

1：

启动或关闭mysql服务：service mysqld start,service mysqld stop(或/etc/init.d/mysqld start,/etc/init.d/mysqld stop)

2:

修改mysql密码：mysql_secure_installation或mysqladmin -u用户名 -p旧密码 password 新密码

3：

显示MySQL版本信息：select version();

4:

显示MySQL日期信息：select current_date();

5:

显示MySQL库：show databases;

6:

创建数据库：create database fuckTable character set utf8 collate=utf8_general_ci;

7:

删除数据库：drop database fuckTable;

8:

切换数据库：use fuckTable;

9:

连接mysql:mysql -u用户名 -p

10:

退出mysql界面：quit或ctrl+c;

11:

显示数据表：show tables;

12:

创建数据表：create table fuckTable(

fuckId int(10) unsigned not null auto_increment,

fuckName varchar(255) null,

fuckText char(100) default '-',

primary key(`fuckId`)

)engine=innodb default charset=utf8 collate=utf8_general_ci;

13:

为表添加字段：alter table fuckTable add column `fuckColumn` decimal(10,2) default 0 after fuckText;

14:

修改字段:alter table fuckTable `fuckColumn` `fuckColumn` decimal(8,2) default 0 comment 'yourSister';

15:

创建FK:create table yourSisterTable(

ystId int(10) not null auto_increment,

upupFuckId int(10) not null,

primary key(`ystId`),

constraint upupFuckId_FK foreign key(upupFuckId) references fuckTable(fuckId)

)engine=innodb default charset=utf8 collate=utf8_general_ci;

16:

删除数据表：drop table fuckTable;

17:

显示创建数据库的信息:show create database fuckDB;

18:

显示创建数据表的信息:show create table fuckTable;

19:

显示数据表信息：desc fuckTable 或 show columns from fuckTable;

20:

数据表添加主键：alter table fuckTable add primary key(`fuckId`,`fuckxx`);

21:

数据表添加唯一索引：alter table fuckTable add unique `fuckUnique` (`fuckName`,`fuckxx`);

22:

数据表添加普通索引：alter table fuckTable add index `fuckIndex` (`fuckText`,`fuckxx`);

23:

删除主键：alter table fuckTable drop primary key(`fuckId`);

24:

删除唯一索引:alter table fuckTable drop unique `fuckUnique` (`fuckName`);

25:

删除普通索引:alter table fuckTable drop index `fuckIndex` (`fuckText`);

26:

删除字段:alter table fuckTable drop fuckText;

27:

修改数据表名:alter table fuckTable rename to yourSisterTable;

28:

导出数据库(此时在mysql command line外面操作)：

mysqldump -u用户名 -p密码 --no-data fuckDB > fuckDB.sql(此时导出的是数据库的结构，无数据)

mysqldump -u用户名 -p密码 fuckDB > fuckDB.sql(此时导出的是数据库的结构，包含表的数据)

29：

导出数据表(此时在mysql command line外面操作)：

mysqldump -u用户名 -p密码 --no-data fuckDB fuckTable > fuckTable.sql(此时导出的是数据表的结构，无数据)

mysqldump -u用户名 -p密码 fuckDB fuckTable > fuckTable.sql(此时导出的是数据表的结构，包含表的数据)

30：

导入数据库(此时在mysql command line外面操作)：

mysql -uroot -p123456 fuckDB < fuckDB.sql

31：

导入数据：source /root/upload/fuckDB.sql

32：

mysql分区：create table fuckTable(

id int(10) unsigned not null auto_increment,

addTime int(10) unsiged not null,

title varchar(255) not null,

content text,

primary key(`id`),

key `addTime` (`addTime`)

)engine=innodb default charset=utf8 collate=utf8_general_ci

partition by range(id) (

partition p0 values less than (3),

partition p1 values less than (6),

partition p2 values less than maxvalue

)

33:

查看分区信息：select * from information_schema.partitions where table_name='fuckTable'

34:

显示mysql可用的变量：show variables

35:

mysql授权：grant all on *.* to 'fuckLoginName'@'%' identified by 'fuckPassword'

36：

复制表结构：create table t2 like t1;

37：

复制表数据 insert into t2 select field1,field2 from t1;

38：

修改字段属性：alter table fuckTable change `field` `field` int(10);

39：

创建视图：create view viewTable(field1,field2,field3) as select a.field1,a.field2,b.field3 from a,b;

40：删除视图：drop view viewTable;

41：mysql预处理语句：

prepare statementName from "select * from fuckTable where id > ?";

set @i=3;

execute statementName using @i;

42：删除预处理语句：drop prepare statementName;

43：auto_increment重拍：alter table fuckTable auto_increment=1;

44：存储：

delimiter //

create proceduce pFuck()

begin

set @i=1;

while @i < 100 do

insert into fuckTable values (concate('test',@i));

set @i=@i+1;

end while;

end //

45：触发器：

delimiter //

create trigger tFuck before update on fuckTable for each row

begin

update fuck2Table set title=new.title where title=old.title;

end //