MySQL(一)--数据库入门

自学小笔记

介绍

SQL: 这是关系数据库的语言标准，STRUCT QUERY LANGUAGE (结构化查询语言);

几个基本的概念

• 数据库database: 用于存储一个项目/产品/软件所使用的各种数据的一个逻辑单位
• 数据库管理系统dbms: 其实就是指一个具体的数据库软件产品:database management system
• 表 table: 用于存储一种数据的结构形式
• 字段 filed，列 column: 一个数据表中的一个纵列，其实有一个名字又叫字段
• 记录 record，行 row:

mac mysql的默认数据库数据的路径

/usr/local/var/mysql

登录/退出mysql系统

• 登录: mysql -h 服务器地址 -u 登录名 [-P 端口号] -p
• 完整登录: mysql --host=服务器地址 --user=用户名 --port=端口 --password
• 退出: quit; exit; \q;
• 注意: 登录数据库系统后，需要使用 set names 编码名; 来设定当前连接数据库的"环境编码名"，及当前跟数据库打交道的客户端本身的编码，通常来说，cmd客户端中是固定的gbk编码，而php网页中，是该网页文件的编码。

数据库的备份和恢复

备份: 就是讲一个数据库，完整的转换为一个可以随时携带和传送的文件。

语法: mysqldump -h 服务器地址 -u 登录名 -p 数据库名 > 文件名

恢复: 就是将一个备份的数据库文件，完整的还原为一个可以使用的数据库。

语法: mysql -h 服务器地址 -u 登录名 -p 数据库名 < 文件名

注意: 这两个命令，都是在没有登录 进入的时候使用。其中musqldump命令还要求为管理员身份。

技术语法规定

注释

1. 单行注释: #注释内容
2. 单行注释: -- 注释内容(–后面有空格存在)
3. 多行注释: /*注释内容*/

语句行

默认情况下，以英文分号作为一条语句的结束，而且常规操作中都是一次执行一条语句。

mysql中可以认为设定语句的结束符。 delimiter 新的结束符。

大小写问题

mysql语言内部本身不区分大小写。但是mysql某些命令执行会生成文件夹，此时他们会区分，例如在区分大小写的系统中，unix，linux系统。

数据库定义语句

创建数据库

形式: create database 数据库名 [charset 字符编码名称] [collate 排序规则];

说明:

• 字符编码名称是用于设定当前数据库中存储的字符内容以什么编码来存储。
• collate排序规则用于设定其中的字符内容的大小关系(先后顺序，对于英文基本没有任何问题,基本不用)。

查看mysql可用的字符集: show charset

查看mysql可用的排序规则: show collation

删除数据库

形式: drop database [if exists] 数据库名;

说明: if exists 适用于一种安全运行的考虑，如果数据库不存在，也不会报错。

修改数据库(字符编码)

基本上，就是修改数据库的属性：只有两个

修改编码和修改排序规则

alert database 数据库名 charset 新的编码名 collate 新的排序规则名

显示所有数据库

show databases;

显示一个数据库的创建语句

形式: show create database 数据库名;

字段类型(数据类型)

mysql中，数据类型主要分3大类，数字型，字符型，时间型

整数字节

主要有: int, tinyint, smallint, mediumint, bigint

整数类型所占的空间字节:

• int: 占4个字节，既32位
• tinyint: 占1个字节，既8位，最多能存储256个数字，默认范围是-128~127
• bigint: 占8个字节，既64位

整数类型字段的设定形式：

• 类型名 [(m)] [unsigned] [zerofill]

说明:

1. m表示设定该整数的显示长度，既select输出的时候，123可能显示为00123.
2. unsigned用于设定该整数位无符号数，其实就是没有负数
3. zerofill用于设定是否填充 0 到一个数字的左边，此时需与设定的长度m配合。

小数类型

有三种: float, double , decimal

• float: 单精度浮点型，使用4个字节存储数据，其精度大约只有6~7位有效数字
• double: 双精度浮点型，使用8个字节存储数据类型，其精度大约有20个有效数字
• decimal: 定点小数类型，整数部分最长可以有65位，小数部分最长可以有30位，一般设置格式为: decimal(总位数，小数部分位数)

php操作mysql数据库的必要代码

通常在php网页中完成有关数据库的操作，首先需要如下代码。

$link = mysql_connect("数据库服务器地址","用户名","密码");	//连接数据库系统
mysql_query("set names 网页文件编码名");	//设定连接编码
	//或者 mysql_set_charset("网页文件编码名");
mysql_query("use 数据库名"); //选定要使用的数据库
	//或者: musql_select_db("数据库名");

当执行了连接的前置动作 之后就可以使用mysql_query(""); 来执行任何的sql语句。

• 场景一:执行没有数据返回的语句，比如：insert,update,delete,create,create table,drop...（通过true和false判断）
• 场景二:执行有数据返回的语句: select, show tables,show databases, desc 表名(显示数据结构)失败返回false，成功返回一个数据集。

补充: mysql_error(): 获取mysql执行失败时的错误信息，通常在mysql_query()之后使用。

结果集的处理

while($rec = mysql_fetch_array($result)){
	// mysql_fetch_array()会取出该结果集中的"一行数据",并取得该行数据后赋值值给$rec;
	//$rec就是一个数组，其下标就是字段名
	//在此while循环中，mysql_fetch_array()会一次次(一行行)取出结果集中的所有数据。
}

fetch函数的3中形式

• mysql_fetch_assoc(); 返回的是字段名 和对应的字段值
• mysql_fetch_row(); 返回的没有字段名以下标0为起始点作为键
• mysql_fetch_array(); 返回的是键 为有规律的数字，也有字段名的两种组合，就是上面的两种组合。

扩展php中操作mysql数据的几个函数:

$n1 = mysql_num_rows(结果集);	//获得该结果集的数据行数
$n2 = mysql_num_fields(结果集);	//获得该结果集的数据列数
$n3 = mysql_field_name(结果集,$i);	//获得该结果集的第i个字段的名字，从0开始。

字段类型

字符类型

• varchar类型: 可变长度字符串，使用时我们必须设定其长度，其最大长度理论值为65535个，实际其实最大只能是65533个，单考虑到存储的字符编码不同，也会有进一步减少。例如如果哦存储中文gbk，则最多是65533/2个，如果存储中文utf8,则最多是65533/3个。此类型的实际长度是存储内容决定，而设定值只是表示最多可存储的字符个数，
• char类型: 定长字符串；使用时通常需要设定其长度，如果不设定默认是1，最大理论长度是255个。定长字符串使用与存储的数据都是可预见的明确的固定长度的字符，比如手机号，中国邮政编码。实际存储的时候，如果少于设定长度，也能存，但都会补空格填满。
• enum类型: 单选项字符串数据类型。它分成适用于存储表单界面中的单项值。它设定的时候，是需要给定固定的几个选项，然后存储的时候，就只存储其中一个值。使用形式: enum(“选项1”,“选项2”,“选项3”,“选项4”….);实际内部: 这些字符串选项值对应的是如下数字值:1234….65535个轩轩个
• set类型: 单选项字符串数据类型。它分成适用于存储表单界面中的多项值。它设定的时候，是需要给定固定的几个选项，然后存储的时候，就只存储其中若干值。使用形式: set(“选项1”,“选项2”,“选项3”,“选项4”….);实际内部: 这些字符串选项值对应的是如下数字值:1248….64个选项
• text类型: 长文本字符类型，通常，其中存储的数据不占据表格中的数据容量限制。其本身最长可存储65535个字符。其他同类字符类型: smalltext,tinytext, longtext.

其他类型(了解):

• binary类型: 定长二进制字符串类型，里面存储的是二进制；
• varbinary类型: 边仓二进制字符串类型，里面存储的是二进制值；
• blob类型: 二进制数据类型，存的仍然是二进制值，但其适用于存储图片，其他文件等。单极少用。

时间类型

• datetime类型: 时间日期类型
• date类型: 日期类型
• time类型: 时间类型
• year类型: 年份类型

在应用中，时间日期类型，在我们自己给定的数据情况下，需要使用单引号引起来，跟字符串一样。

timestamp: 时间戳类型，就是指一个时间的数据值，本质就是一个数字，类似js中的GetTime()或php中的Time()。 它的一个重要的作用是: 是会自动获得时间戳的数据值–相当于"now()"

表定义语句

创建表

create table [if not exists] 表名(字段列表[,索引或约束列表]) [表选项列表];

字段设定形式

字段名 类型 [字段属性1 字段属性2 ...]

• 字段名可以自己取
• 类型就是前面所学的数据类型: int, tinyint, float, double , char(5), varchar(25),text,datetime...
• 子弹属性可以有多个，相互之间直接空格隔开; 主要有auto_increment: 只用于整数类型，让该字段的值自动获得一个增长值。通常用于做一个表的第一个字段的设定，并且通常还当做主键(primary key)primary key : 用于设定该字段为主键，此时该字段的值就可以唯一的确定一行数据。unique key: 设定该字段是惟一的，也就是不重复的not null: 用于设定该字段不能为空。default xx值: 用于水规定该字段的默认值，如果此时insert没有给值得时候就使用该默认值comment : 字段说明文字。

索引

系统内部自动维护的隐藏的数据表，它的作用是可以极大的加快数据的查询速度。

这个隐藏的数据表，其中的数据是自动排好序的，其查找速度就是建立在这个基础上的。

索引的类型

• 普通索引: 形式 key(字段名)含义 仅仅是一个索引，没有其他作用只能加快查找速度。
• 唯一索引: 形式 unique key(字段名)含义 是一个索引，而且具有区分改变中的任何一行数据的作用(其实也是唯一性),与唯一索引区别，唯一性可以为null，而主键不能为空。
• 主键索引: 形式 primary key(字段名)
• 全文索引: 形式 fulltext (字段名)
• 外建索引: 形式 foreign key(字段名) references 其他表(对应其他表中的字段名)

外键: 就是设定某个表中的某一个字段，它的数据的值，必须在另一个表中某个字段中存在。

约束

含义: 就是要求数据满足什么条件的一种规定。

• 主键约束 作用：使该设定字段的值可以用于唯一确定一行数据，其实就是主键的意思
• 唯一约束 作用：是该设定字段字段具有唯一性，自然也是可区分的
• 外键约束 作用：使该设定字段的值，必须在其设定的对应表的对应字段中已经有该值了。
• 非空约束 not null: 设定一个字段时写的那个not null属性。
• 默认约束 default xx值. :设定一个字段的默认值属性。
• 检查约束 check(某种判断语句)。

索引和约束其实是相同一件事情的不同角度的两种说法，例如方法和函数。

表选项列表

创建一个表的时候，对该表的整体设定，主要有如下几个

• charset=要使用的字符编码
• engine=要使用的存储引擎(也叫表选项)
• auto_increment=设定当前表的自增长字段的初始值，默认是1
• comment='该表的一些说明文字'

说明:

1. 设定的字符编码是为了跟数据库设定的不一样，如果一样就不需要设定了，因为其会自动使用数据库级别的设定。
2. engine 存储引擎，在代码层面，就是一个名词:InnoDB,MyIsam,BDB,archive,Memory.

存储引擎:

存储引擎是讲数据存储到硬盘的机制。不同的存储引擎，其实主要从两大层面设计存储机制-速度或者功能。

修改表

说明:

1. 修改表，是指修改表的结构–正如创建表也是设定表的结构。
2. 创建表能做的事，修改表几乎都能做–但不推荐去修改表，而是应该在创建表的时候就基本确定表的结构。
3. 对字段进行增删改；对索引进行增删
4. 标的选项通常都是修改，即使不写任何表选项，他们都有其默认值。

常见的操作:

• 新建字段: alert table 表名 add [column] 新字段名 字段类型 [字段属性列表];
• 修改字段: alert table 表名 change [column] 旧字段名 新字段名 新字段类型 [新字段属性列表];
• 删除字段: alert table 表名 drop [column] 字段名;
• 添加普通索引: alter table 表名 add key [索引名] (字段名1[,字段名2,...]);
• 添加唯一索引(约束): alert table 表名 add unique key(字段名1[,字段名2,...]);
• 添加主键索引(约束): alter table 表名 add primary key(字段名1[,字段名2,...]);
• 修改表名: alter table 旧表名 rename [to] 新表名;

删除表

drop table [if exists] 表名;

其他表的相关语句

• 显示当前数据库中的所有表: show tables;
• 显示某表的结构: desc 表名; 或者 describe 表名;
• 显示某表的创建语句: show create table 表名;
• 重命名表: rename table 旧表名 to 新表名;
• 从已有表复制表结构: create table [if not exists] 新表名 like 元表名;

视图的定义

含义

类似于函数的功能。 就是一个select语句(通常比较复杂),我们给其一个名字(视图名),以后要使用该select语句，直接用该视图名就可以。

创建语法

语法形式: create view 视图名 as select语句;

举例: create view v1 as select id, fi, name, age from table where id>7 and id<100 or fi<1000 and age>10;

使用视图

当做一个表用即可： select * from 视图名 [查询条件];

删除视图

drop view [if exists] 视图名;