超全！Mysql数据库 常用基本操作总结（含快速索引目录）

<1> 数据库 & 表 基本操作

1.登陆数据库

#root用户使用密码登录
mysql -u root -p
#注意该root可以是其他用户名

#或者不用密码登录
mysql -u root 

2.建立数据库

mysql>create database <库名称>;	

3.查看数据库

mysql>show databases；

4.在指定库中建表

mysql>use <库名称>				##进入库 注意该语句不需要分号
#提示data changed
mysql>creat table <表名称>(			##建立表
    -> username varchar(10) not null,
    -> password varchar(50) not null
    -> );
    #在没有结束整个语句（放置“ ; ”）前，可以回车分割代码便于浏览
    #大小写都ok

5.查看表结构

mysql>describe <表名称>；

6.在表中查询指定的内容

mysql>select <指定字段1>，<指定字段2>.... from <表名称>  
#指定字段间用逗号分隔

#例子
SELECT Host,User,Password FROM user;

7.快速查询表中所有内容

#使用 * 替代指定内容
mysql>select * from <表名称>

8.向指定表中添加内容

insert into <表名称> values(<内容1>,<内容2>....) 
#注意内容用逗号隔开，且插入内容要与表结构匹配

#例子：
INSERT INTO sample VALUES('tony','123');	##插入数据到sample表中
INSERT INTO sample VALUES('pake','123');	

9.清空指定表

mysql>truncate <表名称>；

10.删除n行数据

#方法（1）删除行
mysql> delete from <表名称> whrere <字段名>=<指定值>
#例子
mysql> delete from sample where trust = 'shit' ;


#方法（2）将指定字段名中与in集中内容相同的行删去
mysql> delete from <表名称>  whrere  <字段名>  in( <指定值1>,<指定值2>...... )
#例子
mysql> delete from sample where trust  in( 'shit'1,'shit2' );

11.删除表

drop table <表名称>;

12.删除库

drop database <数据库名>;

---

<2> 数据库的数据类型

(1) MySQL的数据类型

主要包括以下五大类：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

1、整型

• 取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。

2、浮点型(float和double)

• 设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.

3、定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

• decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

4、字符串(char,varchar,_text)

char和varchar

1. char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。

2. char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，
   所以varchar(4),存入3个字符将占用4个字节。

3. char类型的字符串检索速度要比varchar类型的快。

• 上面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别

varchar和text

4. varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。

5. text类型不能有默认值。

6. varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

5.二进制数据(_Blob)

1. _BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。

2. _BLOB存储的数据只能整体读出。

3. _TEXT可以指定字符集，_BLO不用指定字符集。

6.日期时间类型

• 若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

(2) 数据类型的属性

特别说明 auto_increment

• mysql有一个定义列为自增的属性：AUTO_INCREMENT；
  要用到唯一的递增/自增编号时，可以使用。

指定了AUTO_INCREMENT的列必须要建索引！不然会报错！

mysql> create table t4 (id int auto_increment);
#以下是提示
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>

下面的定义把sample表的id使用了auto_increment且建立了索引（primary key），不会报错，顺利建表：

mysql> create table sample (id int not null auto_increment,
mysql> name varchar(20) not null,
mysql> primary key(id) using btree)engin=innodb
mysql> default charset=utf8; 

(3) MYSQL数据类型的长度和范围

• 各数据类型及字节长度一览表：

<表1>

<表2>

(4) 使用建议

1. 在指定数据类型的时候一般是采用从小原则，比如能用TINY INT的最好就不用INT，能用FLOAT类型的就不用DOUBLE类型，这样会对MYSQL在运行效率上提高很大，尤其是大数据量测试条件下。

2. 不需要把数据表设计的太过复杂，功能模块上区分或许对于后期的维护更为方便，慎重出现大杂烩数据表

3. 数据表和字段的起名字也是一门学问

4. 设计数据表结构之前请先想象一下是你的房间，或许结果会更加合理、高效

5. 数据库的最后设计结果一定是效率和可扩展性的折中，偏向任何一方都是欠妥的

---

<3> 数据库用户管理

1.创建用户

create user <用户名>@localhost identified by '<密码>';
#注意创建用户的名称要以字母开头
#@localhost表示用户只能本机登陆，test@'%':表示test可以从远程登陆数据库

2.查看数据库中的用户

mysql> select User from mysql.user;  #查看mysql中的用户

3.用户授权

给用户赋予指定库的权限
mysql> grant all privileges on <库名称>.* to '<用户名>'@localhost identified by '<密码>';

mysql> grant all privileges on <库名称>.* to '<用户名>'@127.0.0.1 identified by '<密码>';

mysql> flush privileges;

4.显示特定用户对数据库的权限

show grants for <用户名>@localhost;

5.撤销特定授权

revoke insert on <库名称>.* from <用户名>@localhost;
#insert为all privileges的一个子集

6.重新加载授权表

flush privileges;
注意：在每次对用户权限更改之后都要重新加载授权表

7.删除授权用户

mysql> DROP USER <用户名>@localhost;
mysql> DROP USER <用户名>@127.0.0.1;

---

<4> 数据库 & 表 编码格式

1.查看数据库编码格式

mysql> show variables like 'character_set_database';

2.查看数据表的编码格式

mysql> show create table <表名>;

3.创建数据库时指定数据库的字符集

mysql>create database <数据库名> character set <字符集>;

4.创建数据表时指定数据表的编码格式

mysql>create table tb_books (
    name varchar(45) not null,
    price double not null,
    bookCount int not null,
    author varchar(45) not null ) default charset = utf8;

5.修改数据库的编码格式

mysql>alter database <数据库名> character set utf8;

6.修改数据表格编码格式

mysql>alter table <表名> character set utf8;

7.修改字段编码格式

mysql>alter table <表名> change <字段名> <字段名> <类型> character set utf8;

#例子
mysql>alter table user change username username varchar(20) character set utf8 not null;

8.添加外键

mysql>alter table tb_product add constraint fk_1 foreign key(factoryid) references tb_factory(factoryid);
mysql>alter table <表名> add constraint <外键名> foreign key<字段名> REFERENCES <外表表名><字段名>;

9.删除外键

mysql>alter table tb_people drop foreign key fk_1;
mysql>alter table <表名> drop foreign key <外键名>;

---

<5> 数据库备份

1.备份数据库到指定位置

mysqldump -u root -psample sample > /mnt/sample.sql              ##将库sample整个备份
mysqldump -u root -psample sample  --no-data > /mnt/sample.sql 	##将sample库的结构备份，不备份数据

2.备份所有数据库

mysqldump -u root -psample --all-database > /mnt/alldata.sql	##备份框架和内容
mysqldump -u root -psample --all-database --no-data  > /mnt/alldata.sql	   ##只备份框架，不备份内容

3.删除指定数据库

mysql -uroot -psample -e "DROP DATABASE sample;"  ##-e:参数后加动作:删除sample库

4.数据库的恢复

mysql -uroot -psample sample </mnt/sample.sql
由于数据库中已经没有sample数据库，恢复的时候会遇到问题

• 恢复方式1

mysql -uroot -psample -e "CREATE DATABASE sample;"  ##先新建sample库，再恢复数据
mysql -uroot -psample sample < /mnt/sample.sql      ##从备份的目录中恢复

• 恢复方式2

vim /mnt/sample.sql  ##删除sample库，直接在备份文件中写入创建数据库命令
21行 CREATE DATABASE sample;
22行 USE sample;
mysql -uroot -psample < /mnt/sample.sql             ##从备份的目录中恢复