常用SQL语句及MySQL数据库的备份与恢复03

一、库的操作
1、连接数据库

mysql -uroot -p密码

或者

mysql -u root -p 回车，给出提示在输入密码

2、创建数据库

create database db1;

db1是数据库名称
或者

create database db1 character set utf8 collate utf8_bin;

这里的character set是指定了数据库的默认字符集，collate是为数据库指定的默认校对规则

3、显示所有的数据库

show databases;

显示数据库创建语句：

show create databases db1;

4、切换数据库

use db2；

db2是你想切换到的数据库的名字
没有退出数据库的命令,如果想退出当前数据库进入另一个数据,直接use 另一个数据库 就可以了

5、修改数据库

alter database db2 character set utf8;

查看服务器中的数据库，并把mydb2库的字符集修改为utf8

6、删除数据库

drop database db1;

删除前面创建的mydb1数据库

二、表的操作
对表进行操作之前，需要想了解SQL中常见的数据类型和SQL中的字段约束。

1、SQL中常见数据类型
1)字符串型 —varchar：
变长的字符串，需要在声明字段时指定能存储的最大字符数，真实占用的空间取决于存入的字符数，存入的越多占用空间越多。适合保存内容长度不定的字符类型数据。能包含数据的大小，mysql5.0以前0-255字节，mysql5.0以后0~65535字节
char:
定长字符串，需要在声明字段时指定固定字符数。即使存入的字符数少于该长度，该字段也会占用该固定长度。适合存储长度不变的字符类型数据。能包含数据的大小，0~255字节

2)大数据类型
blob：
大二进制类型，可以存入二进制类型的数据，通过这个字段，可以将图片、音频、视频等数据以二进制的形式存入数据库。最大为4GB。
text：
大文本，被声明为这种类型的字段，可以保存大量的字符数据，最大为4GB。
注意：text属于mysql的方言，在其他数据库中为clob类型

3)数值型
tinyint:占用1个字节，相对于java中的byte
smallint:占用2个字节，相对于java中的short
int：占用4个字节，相对于java中的int
bigint：占用8个字节，相对于java中的long
float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double

4)逻辑型
bit：
位类型，可以存储指定位的值，可以指定位的个数，如果不指定则默认值为1位，即只能保存0或1，对应到java中可以是boolean型。

5)日期型
date：日期
time：时间
datetime：日期时间
timestamp：时间戳

**datetime和timestamp的区别？

• datetime和timestamp显示的结果是一样的，都是固定的"YYYY-MM-DD HH:MM:SS"的格式
• datetime支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。timestamp的显示范围是’1970-01-01 00:00:00’到2037年，且其实际的存储值为1970年到当前时间的毫秒数。
• 在建表时，列为timestamp的日期类型可以设置一个默认值，而datetime不行。
• 在更新表时，可以设置timestamp类型的列自动更新时间为当前时间。

2、字段约束
1)主键约束
通常每张表都会有一个字段或多个字段联合起来唯一标识表记录，这样的字段称为这张表的主键。主键约束要求字段必须不能为空且值必须唯一。
增加主键约束：primary key
可以在定义主键时指定主键的自动增长auto_increment，在新增记录时不需要指定主键的值，数据库会自动维护一个计数器，每次计数器的值+1后作为主键，保证主键的唯一性。

2)唯一约束
指定某个字段的值不能重复，可以为该字段指定唯一约束
增加唯一约束：unique

3)非空约束
指定某个字段的值不能为空，可以为该字段指定非空约束
增加非空约束：not null

4)外键约束
这块比较复杂，后面单独出一个模块说。

3、具体操作语句
新增表：

create table table1
(
	field1  datatype,
	field2  datatype,
	field3  datatype
)

这里的 field：指定数据库中的数据表中的列名　datatype是指定列类型
如下：

create table employee(
	id int primary key auto_increment,
	name varchar(20) unique,
	gender bit not null,
	birthday date,
	entry_date date,
	job varchar(100),
	salary double,
	resume text
);

注意这里的最后是分号，属性间是逗号。
1)查看表
查看表结构：

desc table1

查看当前所有表：

show tables

查看当前数据库表建表语句

show create table table1;

2)修改列(直接用例子表示)
增加列：

alter table employee add image blob;

在上面员工表的基础上增加一个image列。

修改列：

alter table employee modify job varchar(60);

修改job列，使其长度为60。

删除列：

alter table employee drop gender;

删除gender列。

修改表名：

alter table employee rename to user;
或
rename table employee to user;

修改列名称：

alter table user change name username varchar(20);

列名name修改为username

修改列的顺序：

 alter table user modify image blob after gender;

将image插入到gender列的后面

修改表的字符集：

alter table user character set utf8;

修改表的字符集为utf8

3)删除表

drop table user;

删除user表

三、MySQL数据库的备份与恢复

1、备份数据库:
cmd窗口中使用mysqldump 备份数据库

mysqldump -u root -p db_name > d:/xxx.sql

例如：mysqldump -u root -p db2 > d:/db2.sql

2、恢复数据库:
方式1:cmd窗口中使用mysql命令 恢复数据库,
注意:只能恢复数据库中的数据,不能恢复数据库本身!!! 所以要提前建好数据库.

mysql -u root -p db_name < d:/xxx.sql

例如：mysql -u root -p db2 < d:/db2.sql

方式2:在mysql命令下,source xxx.sql,这个命令的作用就是,在当前位置执行sql文件中的所有的sql.首先新建出数据库,进入数据库,在source执行备份的sql文件即可。
例如：source d:/xxx.sql

注意：如果在cmd窗口下，执行出现“拒绝访问”提示，用管理员打开cmd窗口即可。