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 //