目录
一、库的操作
1. 创建数据库
最基础的创建数据库的语句是“create database (if not exists) db_name;”。在这个语句中,用()括起来的部分是代表可选项,这个可选项的意思是“如果不存在”,表示如果要创建的数据库不存在,就创建,反之则不创建。
例如我们先用create database命令创建一个数据库,然后再用create database if not exists创建同一个数据库:
此时就发出了警告,因为该数据库已经存在,无法再次创建,用这个命令一般是为了一些安全问题。
2. 数据库的编码集
2.1 数据库的编码问题
一般来讲,在创建一个数据库时该数据库有两个编码集,分别是数据库的字符集和数据库的校验集,数据的存储和读取都是需要使用特定的编码的,在数据库中也是一样。我们用什么编码集来存储数据,那么在读取数据时就要采用同样的编码集。
在使用数据库时,我们需要向数据库中存储数据,要存储数据就需要使用特定的编码集进行编码,这就是数据库的编码集的作用。而当我们未来在使用时,势必要涉及到对数据的查找,要查找数据就必须要有能对数据进行比较的能力。因此我们需要采用和存储时相同的编码集对数据进行读取和比对,这样才能正确的找到数据。
简单来讲,数据库的字符集是用于数据库未来存储数据的;而数据库的校验集,则是用来支持数据库使用同样的编码集进行字段比较的。
由此我们可以得出一个结论——数据库无论对数据做任何操作,都必须保证存储和读取时的编码一致。如果使用的编码集不同,例如存储的时候用utf8,读取的时候用gbk,就可能导致出现乱码。
2.2 查看系统默认字符集和校验规则
要查看系统默认字符集,输入“show variables like 'character_set_database';”命令:
可以看到,当前mysql中使用的默认字符集就是utf8。
如果要查看数据库的校验集,输入"show variables like 'collation_database';"命令:
可以看到,当前mysql中使用的校验集就是utf8_general_ci的。
当然,我们也可以执行“show variables like 'collation_%';”命令查看一些其他的校验场景的校验集:
这里面就展示了连接时、数据库和服务的校验场景,都是使用的utf8_general_ci。大家的mysql中使用的编码集可能与图中有所差异,因为我这里的编码集是在配置文件中配置过的,指定使用了utf8的。如果你的mysql没有配置或配置不一样,这里的结果也就不一样。
当然,大家也可以执行“show charset”命令查看mysql中支持的所有字符集集,或使用“show collation;”命令查看mysql中支持的所有校验集,这里就不再演示了。
2.3 创建数据库时指定字符集和校验集
如果我们在创建数据库时没有指定字符集或校验集,那么这个数据库就默认使用配置文件中指定的字符集和校验集:
如果你想查看你的数据库使用编码集,可以到该数据库的配置文件中看。上文中讲过,数据库的本质其实就是一个目录,所以这个配置文件就在对应数据库的目录的下一级,名字一般是"db.opt"。通过上面的两张图就可以看到,不带字符集和校验集时,创建的数据库就默认使用配置文件中的。
要指定字符集和校验集,可以使用charset=和collate字段。例如使用“create database d2 charset=gbk;”创建一个字符集为gbk的数据库:
有人可能会奇怪,这里仅仅是指定了字符集,为什么这个数据库的校验集也改变了呢?其实是因为在mysql中默认对字符集和校验集做了匹配的,如果你修改了字符集,mysql就会自动将校验集替换为与字符集相匹配的校验集。
可以输入“show collation;”命令查看字符集与校验集的匹配关系,这里的截图并不完整,只是其中的一部分。
当然,你也可以使用charset和collate命令指定字符集和校验集:
同样的, 我们也可以为数据库单独指定一个校验集,此时mysql也同样会自动匹配字符集:
2.4 校验规则对数据库的影响
为了方便看到不同校验集对数据库的影响,在这里我们创建两个数据库,校验集分别为utf8_general_ci和utf8_bin。前一个校验集不区分大小写,后一个校验集区分大小写。并且这两个校验集都匹配utf8字符集。
创建好数据库后我们可以再向这两个数据库中创建一个表,表内插入数据。上文中讲过,存储进数据库的数据会按照数据库字符集进行存储,按照校验集进行读取,而表是存在于特定数据库内的,所以它的存储和读取时的编码集就和数据库的编码集是一样的。
首先使用“create database d1 collate utf8_general_ci;”和"create database d2 collate utf8_bin;”创建两个采用不同校验集的数据库:
创建好后,我们再使用"use database;"数据库名;”进入这两个数据库,然后用"create table if not exists person (name varchar(20));"命令分别创建一个表:
表创建好后,首先向b1数据库中的表插入多个字母数据:
这个表中插入了多个字母数据,但是并没有截全。
然后执行select * from person;命令,查看表中的数据:
上图就是d1的表中的数据。然后再执行select * from person where name='a';命令,搜索a数据:
在搜索的结果中,就将a和A全部显示了。
重复上面步骤,在数据库d2中也创建表和添加同样的数据然后搜索a:
可以看到,d2中的表就只显示了a。原因也很简单,数据库d1的校验集采用的是utf8_general_ci,不会区分大小写;而d2采用的则是utf8_bin,会区分大小写。
这个区别不仅仅在查找数据上,排序数据时也会有所区别。分别在这两个数据库中执行select * from person order by name;命令,对表中的数据按照name进行排序:
很明显可以看到,不区分大小写的d1数据库的排序时的升序就是大小写放在一起的,而区分大小写的d2数据库的排序就是大小写分开排序的。
3. 进入数据库
要操作一个数据库的内容,就需要进入数据库。执行"use 库名;”即可进入数据库:
4. 确认自己当前所处的数据库
有时我们在对数据库做操作时,可能突然忘了自己当前在哪个数据库,此时就可以执行“select database();”命令查看当前所处的数据库:
5. 修改数据库的编码集
如果想修改数据库的编码集,可以使用校验“alter database 库名 charset=字符集 collate=集”进行修改:
6. 查看创建数据库时的语句
如果想查看你当初创建数据库时用的语句,可以执行“show create databse 库名;”查看:
此时就可以看见我们当初是用什么语句创建数据库的了。注意,这里面的创建语句后面带有一段话,这段话并不是注释,而是表示如果当前mysql版本大于4.01,就执行这个语句。这个语句大家仔细观察的话就可以发现,它就是在设置字符集。设置的是你自己指定的字符集。这个d2采用的字符集是gbk,所以这里这句话就也是gbk。
7. 库的删除
要删除数据库很简单,直接使用“drop database 库名;”即可。
注意,当我们删除一个数据库后,在数据库内部就无法找到这个数据库了,同时对应数据库内的所有文件和数据都会被删除。所以,在一般情况,千万不要随意删除数据库。
8. 库的备份和恢复
8.1 备份
我们之前说过,数据库在linux中其实就是一个目录,所以如果你想备份数据库,一个简单粗暴的方法就是直接将这个目录打包备份。但是这种方法是有问题,的因为数据库在备份时不仅需要数据库内的数据,还可能需要一些其他的东西。如果直接打包目录,在未来你把这个目录重新放回到mysql中时,就很可能出各种问题。因此,这种方法可行,但非常不推荐,很容易出问题。
有时大家也可能会想对数据库重命名,但是mysql中并没有提供可以直接对数据库重命名的指令。当然,有人可能会说既然数据库本质是目录,那直接在linux上用mv命令重命名不就行了。确实可以,但是非常不推荐这么做,因为这样做可能会导致数据库在使用时出现各种各样的问题。
因此,一般遇到需要重命名时,比较推荐使用mysql提供的备份工具来重命名,即在恢复数据库的时候重新命名。
要对数据库做备份就需要使用mysqldump工具,这个工具在安装好mysql时就已经自动安装了。在备份时,首先在linux下选择一个存放备份数据库的目录,然后执行“mysqldump -P端口号 -u root -p -B 库名 > 文件名”命令:
上图中的命令的意思是用mysqldump工具,将3306接口下的root账户中的d1数据库的内容打包并重定向到d1.sql文件中。此时就完成了数据库的备份。
执行vim命令,查看这个备份文件中的内容:
这里的内容虽然有些我们看不懂,但是可以发现, 这个文件中除了数据库内的数据,还包含了我们当初在该数据库中执行的有效操作。这就是说,数据库在备份后,备份文件中除了数据库的有效数据外,还包含我们曾经对这个数据库执行的有效操作等内容。这也就是为什么非常不推荐大家直接复制目录。
如果你只想备份数据库内的某张表,而不是数据库整体,同样是执行上面的指令,只是在数据库后面加上表名而已。
8.2 恢复
为了演示恢复数据库,在这里就先将原来的数据库删除。
要恢复数据,就在mysql中执行“source 备份数据路径;”命令即可。
可以看到,此时就成功将数据库恢复了。
如果你想在恢复数据库时修改数据库的名字,那么就需要你在备份数据库是不要带上“-B”选项。不带该选项的话,要恢复数据库就要求先创建一个空的数据库,然后使用数据库,再使用source来还原。
简单来讲,不带-B其实就是让数据库在备份时不备份创建数据库时的语句。
当然,虽然这种方式可以修改数据库的名字,但是非常不推荐随意修改数据库的名字,因为一旦你修改了数据库的名字,上层应用的依赖关系就会被破坏,无法通过它保存的数据库名字找到对应的数据库,进而出现各种错误。
9. 查看数据库连接情况
数据库同时可以供很多人一起使用。如果你想查看当前的数据库有哪些人在使用,使用"show processlist;"命令即可查看:
二、表的操作
1. 创建表
1.1 语法
创建表的语法如下:
在这里面,create table是主语句。
table_name是表的名字。表中的内容需要放在()里面。
field表示列名,datatype则表示列的类型。
character set表示字符集、collate表示校验集或者说校验规则、engine表示存储引擎。
在这里面的字符集,校验规则和存储引擎都可以不写。如果不写就使用配置文件中指定的内容。
在datatype的后面还可以接上comment '注释',用于标识该列的内容是什么,这个注释会被存放进表的创建信息中。
1.2 创建实例
要创建表,首先要先创建一个数据库,这里就不再演示了,上文中已经介绍了如何创建数据库。
为了方便演示,这里就创建一个带有comment和自行指定了字符集能内容的表。
在上面创建表时,在create table后面加的if not exists是可选项,可写可不写。包括后面的字符集等内容也是。在这里字符集采用的是另一种书写方式,和上面的图是一样的效果。
2. 查看表结构
如果你不知道自己在哪个数据库里面,可以使用select database();命令来查看你在哪个数据库中。
上图就表示我当前在d1数据库中。
如果你想查看你所在的数据库中存在哪些表,则使用show tables;命令即可。
而如果你想看这个表的详细信息,例如有哪些列,这些列的属性是什么,就可以使用“desc 表名称;”查看:
但是这样看的话你可能不知道这些表中的列代表什么意思。此时你就可以执行“show create table 表名称 \G”命令,以格式化的方式查看你在创建这个表时的信息:
这个命令就可以将你当初创建表时使用的语句展示出来。如果这里的详细信息和你自己写的有点区别也不用奇怪,因为数据库会将这些信息进行优化后再记录起来。所以拿出来时的信息就是优化过后的。
至于上面的表结构中后面的如NULL、Key等内容在这里就先不解释,到后面的内容中再解释。
3. 修改表
在实际使用中,我们可能会遇到需要修改表的结构,比如字段名字、字段大小、字段类型等等内容,也可能遇到需要添加字段或删除字段等情况。遇到这些情况时,就需要对表进行修改。在这里就分为几个情况来介绍如何修改表。
在修改表时,使用的指令的主语句都带有“alter table”。
3.1 修改表名
要修改表名,使用“alter table 表名 rename to 新表名;”即可:
3.2 新增一列
要新增一列,使用“alter table 表名 add 列名 数据类型 after 列名;”即可。
在上图的指令中就表示,在user这个表的birthday后面添加一个名字为age,数据类型为char(6),注释为年龄的列。
3.3 修改列的属性
在当前我们有一个user表,里面的列的属性如下:
再执行show create table user \G命令查看详细信息:
注意,此时它里面的age列是有注释的。
执行“alter table user modify age char(2);”命令,将user表里面的age的数据类型修改为char(2):
然后再查看它的详细信息:
可以看到,age的数据类型确实被修改了。此时在去查看这个表的创建时的信息:
可以看到,创建时的信息内的age列也被修改了。这就说明修改列的属性时还会将创建信息也一并修改。
3.4 删除列
要删除一列,执行"alter table 表名 drop 列名;"命令即可。
在删除列时,不仅会删除列,还会将这一列中保存的数据全部删除,所以执行删除时要格外小心,不要误删。
3.5 修改列名
要修改列名,则需要执行"alter table 表名 change 列名 新列名 属性;"命令。
注意,在mysql中修改列名时,必须要将这个列的其他属性也一并带上,所以与其叫修改列名,不如叫重置列名。因此,如果你在修改列名是不想改变它的属性,比如数据类型,注释等内容,你可以用"show create table 表名 \G"命令查看这个表的创建时的信息,然后找到对应的列的属性,将那些属性复制过来直接放到修改列名的命令后面即可。
4. 删除表
要删除一个表很简单,执行“drop table (if exists) 表名;”命令即可。在这个指令中用()括起来的部分可写可不写。
if exists的作用是判断是否存在这个表,存在就删除,不存在就警告。
虽然上面我们将了关于列的删除、修改列名、修改列的属性等等内容,但大家在实际中最好尽量不要去执行这些操作,因为一旦你执行了这些操作,那么依赖于这个数据库的上层程序就可能会出一些莫名其妙的错误。是很令人头疼的。