数据库课程体系

在PHP阶段,将数据库分为三个阶段:

?

基础阶段(就业班第一个阶段): 6天, mysql数据库的基本操作(增删改查), 以及一些高级操作(视图, 触发器,函数,存储过程等), 和PHP操作mysql数据库

优化阶段(就业班后期): 如何提高数据库效率, 如索引, 分表等

部署阶段(最后): 如何搭建真实的环境系统, 如服务器集群, 负载均衡

数据库基础

1. 什么是数据库?

   数据库: database, 存储数据的仓库

   数据库: 高效的存储和处理数据的介质(介质主要是两种: 磁盘和内存)

   ?

2. 数据库的分类?

   数据库基于存储介质的不同: 进行了分类,分为两类: 关系型数据库(SQL)和非关系型数据库(NoSQL: Not Only SQL, 不是关系型的数据库都叫做非关系型数据库)

   ?

3. 不同的数据库阵营中的产品有哪些?

   关系型数据库

   大型: Oracle, DB2

   中型: SQL-SERVER, Mysql等

   小型: access等

   非关系型数据库: memcached, mongodb, redis(同步到磁盘)

   ?

4. 两种数据库阵营的区别?

   关系型数据库: 安全(保存磁盘基本不可能丢失), 容易理解, 比较浪费空间(二维表)

   非关系型数据库: 效率高, 不安全(断电丢失)

   关系型数据库

5. 什么是关系型数据库?

   关系型数据库: 是一种建立在关系模型(数学模型)上的数据库.

   ?

   关系模型: 一种所谓建立在关系上的模型. 关系模型包含三个方面:

   数据结构: 数据存储的问题, 二维表(有行和列)

   操作指令集合: 所有SQL语句

   完整性约束: 表内数据约束(字段与字段), 表与表之间约束(外键)

   ?

6. 关系型数据库的设计?

   关系型数据库: 从需要存储的数据需求中分析, 如果是一类数据(实体)应该设计成一张二维表: 表是由表头(字段名: 用来规定数据的名字)和数据部分组成(实际存储的数据单元)

   二维表: 行和列

   表头	字段名 1	字段名 2
   数据单元	数据 1	数据 2

   ?

   以实际案例来进行处理: 分析一个教学系统, 讲师负责教学, 教学生, 在教室教学生.

   ?

   ①找出系统中所存在的实体: 讲师表, 学生表, 班级表

   ②找出实体中应该存在的数据信息

   讲师: 姓名,性别,年龄,工资

   学生: 姓名,性别,学号,学科

   班级: 班级名字, 教室编号

   ?

   关系型数据库: 维护实体内部, 实体与实体之间的联系.

   ?

   实体内部联系: 每个学生都有姓名,性别, 学号,学科信息

   姓名	性别	学号	学科	年龄
   陈明	男	Itcast0001	PHP	20
   陈晶晶	女	Itcast0002	PHP	?
   王威	?	Itcast0003	UI	?

   ?

   第二行的所有字段, 都是在描述陈明这个学生(内部联系); 第二列只能放性别(内部约束)

   ?

   关系型数据库的特点之一: 如果表中对应的某个字段没有值(数据), 但是系统依然要分配空间: 关系型数据库比较浪费空间

   ?

   实体与实体之间的联系: 每个学生肯定属于某个班级, 每个班级一定有多个学生(一对多)

   ?

   学生表

   姓名	性别	学号	学科	年龄
   陈明	男	Itcast0001	PHP	20
   陈晶晶	女	Itcast0002	PHP	?
   王威	?	Itcast0003	UI	?

   ?

   班级表

   班级名称	教室编号
   PHP0810	B205
   PHP0710	A203

   ?

   解决方案: 在学生表中增加一个班级字段来指向班级(必须能够唯一的找到一个班级信息)

   姓名	性别	学号	学科	年龄	班级名称
   陈明	男	Itcast0001	PHP	20	PHP0810
   陈晶晶	女	Itcast0002	PHP	?	PHP0810
   王威	?	Itcast0003	UI	?	UI0810

   ?

   学生实体与班级实体的关联关系: 实体与实体之间的关系

   关键字说明

   数据库: database

   数据库系统: DBS(Database System): 是一种虚拟系统, 将多种内容关联起来的称呼

   DBS = DBMS + DB

   DBMS: Database Management System, 数据库管理系统, 专门管理数据库

   DBA: Database Administrator,数据库管理员

   ?

   行/记录: row/record, 本质是一个东西: 都是指表中的一行(一条记录): 行是从结构角度出发,记录是从数据角度出发

   列/字段: column/field, 本质是一个东西

   SQL

   SQL: Structured Query Language, 结构化查询语言(数据以查询为主: 99%是在进行查询操作)

   ?

   SQL分为三个部分

   DDL: Data Definition Language, 数据定义语言, 用来维护存储数据的结构(数据库,表), 代表指令: create, drop, alter等

   DML: Data Manipulation Language, 数据操作语言, 用来对数据进行操作(数据表中的内容), 代表指令: insert, delete,update等: 其中DML内部又单独进行了一个分类: DQL(Data Query Language: 数据查询语言, 如select)

   DCL: Data Control Language, 数据控制语言, 主要是负责权限管理(用户), 代表指令: grant,revoke等

   ?

   SQL是关系型数据库的操作指令, SQL是一种约束,但不强制(类似W3C): 不同的数据库产品(如Oracle,mysql)可能内部会有一些细微的区别.

   ?

   Mysql数据库

   Mysql数据库是一种c/s结构的软件: 客户端/服务端, 若想访问服务器必须通过客户端(服务器一直运行, 客户端在需要使用的时候运行).

   ?

   交互方式

7. 客户端连接认证: 连接服务器, 认证身份: mysql.exe -hPup

   

8. 客户端发送SQL指令
9. 服务器接收SQL指令: 处理SQL指令: 返回操作结果
10. 客户端接收结果: 显示结果

    

11. 断开连接(释放资源: 服务器并发限制): exit/quit/\q

    

    Mysql服务器对象

    没有办法完全了解服务器内部的内容: 只能粗略的去分析数据库服务器的内部的结构.

    ?

    将mysql服务器内部对象分成了四层: 系统(DBMS) -> 数据库(DB) -> 数据表(Table) -> 字段(field)

    

    ?

    ?

    SQL基本操作

    基本操作: CRUD

    ?

    将SQL的基本操作根据操作对象进行分类 ,分为三类: 库操作, 表操作(字段), 数据操作

    ?

    库操作

    对数据库的增删改查.

    新增数据库

    基本语法

    Create database 数据库名字 [库选项];

    ?

    库选项: 用来约束数据库, 分为两个选项

    字符集设定: charset/character set 具体字符集(数据存储的编码格式): 常用字符集: GBK和UTF8

    校对集设定: collate 具体校对集(数据比较的规则)

    

    ?

    其中: 数据库名字不能用关键字(已经被使用的字符)或者保留字(将来可能会用到的)

    

    ?

    如果非要使用关键字或者保留字,那么必须使用反引号(esc键下面的键在英文状态下的输出:`)

    

    ?

    中文数据库是可以的,但是有前提条件: 保证服务器能够识别(建议不用)

    

    ?

    当创建数据库的SQL语句执行之后,发生了什么?

12. 在数据库系统中,增加了对应的数据库信息
13. 会在保存数据的文件夹下: Data目录,创建一个对应数据库名字的文件夹

    

14. 每个数据库下都有一个opt文件: 保存了库选项

    

    查看数据库

    ?

15. 查看所有数据库: show databases;

    

    ?

16. 查看指定部分的数据库: 模糊查询

    Show databases like ‘pattern‘;????-- pattern是匹配模式

    %: 表示匹配多个字符

    _: 表示匹配单个字符

    

    ?

17. 查看数据库的创建语句: show create database 数据库名字;

    

    ?

    更新数据库

    数据库名字不可以修改.

    ?

    数据库的修改仅限库选项: 字符集和校对集(校对集依赖字符集)

    Alter database 数据库名字 [库选项];

    Charset/ character set [=] 字符集

    Collate 校对集

    

    删除数据库

    所有的操作中: 删除是最简单

    Drop database 数据库名字;

    

    ?

    当删除数据库语句执行之后,发生了什么?

18. 在数据库内部看不到对应的数据库

    

19. 在对应的数据库存储的文件夹内: 数据库名字对应的文件夹也被删除（级联删除：里面的数据表全部删除）

    

    ?

    注意：数据库的删除不是闹着玩的，不要随意删除，应该先进行备份后操作．（删除不可逆）

    表操作

    表与字段是密不可分的．

    新增数据表

    Create table [if not exists] 表名(

    字段名字 数据类型,

    字段名字 数据类型????-- 最后一行不需要逗号

    )[表选项];

    ?

    If not exists: 如果表名不存在,那么就创建,否则不执行创建代码: 检查功能

    表选项: 控制表的表现

    字符集: charset/character set 具体字符集;????-- 保证表中数据存储的字符集

    校对集: collate 具体校对集;

    存储引擎: engine 具体的存储引擎(innodb和myisam)

    

    任何一个表的设计都必须指定数据库.

    ?

    方案1: 显示的指定表所属的数据库

    Create table 数据库名.表名();????-- 将当前数据表创建到指定的数据库下

    

    ?

    方案2: 隐式的指定表所属数据库: 先进入到某个数据库环境,然后这样创建的表自动归属到某个指定的数据库.

    进入数据库环境: use 数据库名字;

    

    ?

    当创建数据表的SQL指令执行之后,到底发生了什么?

20. 指定数据库下已经存在对应的表
21. 在数据库对应的文件夹下,会产生对应表的结构文件(跟存储引擎有关系)

    

    ?

    查看数据表

    数据库能查看的方式,表都可以查看.

    1.????查看所有表: show tables;

    

    ?

22. 查看部分表: 模糊匹配: show tables like ‘pattern‘;

    

    ?

23. 查看表的创建语句: show create table 表名;

    

    ?

24. 查看表结构: 查看表中的字段信息

    show columns from/desc/describe 表名;

    

    修改数据表

    ?

    表本身存在, 还包含字段: 表的修改分为两个部分: 修改表本身和修改字段

    ?

    ?

    修改表本身

    ?

    表本身可以修改: 表名和表选项

    ?

    修改表名: rename table 老表名 to 新表名;

    

    ?

    修改表选项: 字符集,校对集和存储引擎

    Alter table 表名 表选项 [=] 值;

    

    ?

    修改字段

    字段操作很多: 新增, 修改, 重命名, 删除

    ?

    新增字段

    Alter table 表名 add [column] 字段名 数据类型 [列属性] [位置];

    位置: 字段名可以存放表中的任意位置

    First: 第一个位置

    After: 在哪个字段之后: after 字段名; 默认的是在最后一个字段之后

    

    ?

    修改字段: 修改通常是修改属性或者数据类型

    Alter table 表名 modify 字段名 数据类型 [属性] [位置];

    

    ?

    重命名字段

    Alter table 表名 change 旧字段 新字段名 数据类型 [属性] [位置];

    

    ?

    删除字段

    Alter table 表名 drop 字段名;

    

    ?

    小心: 如果表中已经存在数据,那么删除字段会清空该字段的所有数据(不可逆)

    删除数据表

    Drop table 表名1,表名2...;????-- 可以一次性删除多张表

    

    ?

    当删除数据表的指令执行之后发生了什么?

25. 在表空间中,没有了指定的表(数据也没有了)
26. 在数据库对应的文件夹下, 表对应的文件(与存储引擎有关)也会被删除

    

    ?

    注意: 删除有危险,操作需谨慎(不可逆)

    数据操作

    新增数据

    有两种方案

    ?

    方案1: 给全表字段插入数据, 不需要指定字段列表: 要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致: 凡是非数值数据,都需要使用引号(建议是单引号)包裹

    Insert into 表名 values(值列表)[,(值列表)];????-- 可以一次性插入多条记录

    

    ?

    方案2: 给部分字段插入数据,需要选定字段列表: 字段列表出现的顺序与字段的顺序无关; 但是值列表的顺序必须与选定的字段的顺序一致.

    Insert into 表名 (字段列表) values (值列表)[,(值列表)];

    

    查看数据

    Select */字段列表 from 表名 [where条件];

    ?

    查看所有数据

    

    ?

    查看指定字段,指定条件的数据.

    

    更新数据

    Update 表名 set 字段 = 值 [where条件];????-- 建议都有where: 要不是更新全部

    

    ?

    更新不一定会成功: 如没有真正要更新的数据

    

    删除数据

    删除是不可逆的: 谨慎删除

    ?

    Delete from 表名 [where条件];

    

    中文数据问题

    中文数据问题本质是字符集问题.

    ?

    计算机只识别二进制: 人类更多是识别符号: 需要有个二进制与字符的对应关系(字符集)

    ?

    客户端向服务器插入中文数据: 没有成功

    

    ?

    原因: \xD5\xC5\xD4\xBD代表的是"张越"在当前编码(字符集)下对应的二进制编码转换成的十六进制: 两个汉字 => 四个字节(GBK)

    

    ?

    报错: 服务器没有识别对应的四个字节: 服务器认为数据是UTF8, 一个汉字有三个字节: 读取三个字节转换成汉字(失败),剩余的再读三个字节(不够): 最终失败.

    ?

    所有的数据库服务器认为(表现)的一些特性都是通过服务器端的变量来保存: 系统先读取自己的变量,看看应该怎么表现.

    //查看服务器到底识别哪些字符集

    Show character set;

    

    基本上: 服务器是万能,什么字符集都支持

    ?

    //既然服务器识别这么多: 总有一种是服务器默认的跟客户端打交道的字符集

    Show variables like ‘character_set%‘;

    

    ?

    ?

    问题根源: 客户端数据只能是GBK, 而服务器认为是UTF8: 矛盾产生

    解决方案: 改变服务器, 默认的接收字符集为GBK;

    Set character_set_client = gbk;

    

    ?

    插入中文的效果

    

    ?

    查看数据效果: 依然是乱码

    

    ?

    原因: 数据来源是服务器, 解析数据是客户端(客户端只识别GBK: 只会两个字节一个汉字): 但是事实服务器给的数据却是UTF8,三个字节一个汉字: 乱码

    ?

    解决方案: 修改服务器给客户端的数据字符集为GBK

    Set character_set_results = GBK ;

    

    ?

    查看数据效果

    

    ?

    Set 变量 = 值; 修改只是会话级别(当前客户端,当次连接有效: 关闭失效)

    

    ?

    设置服务器对客户端的字符集的认识: 可以使用快捷方式: set names 字符集

    Set names gbk; ====> character_set_client,character_set_results,character_set_connection

    

    ?

    Connection连接层: 是字符集转变的中间者,如果统一了效率更高,不统一也没问题.

    校对集问题

    校对集: 数据比较的方式

    ?

    校对集有三种格式

    _bin: binary,二进制比较, 取出二进制位,一位一位的比较, 区分大小写

    _cs: case sensitive,大小写敏感, 区分大小写

    _ci: case insensitice,大小写不敏感,不区分大小写

    ?

    查看数据库所支持的校对集: show collation;

    

    ?

    ?

    校对集应用: 只有当数据产生比较的时候,校对集才会生效.

    

    ?

    对比: 使用utf8 的_bin和_ci来验证不同的校对集的效果

    ?

27. 创建不同校对集对应的表

    

28. 插入数据

    

29. 比较: 根据某个字段进行排序: order by 字段名 [asc|desc]; asc升序,desc降序,默认是升序

    

    ?

    校对集: 必须在没有数据之前声明好,如果有了数据,那么再进行校对集修改: 那么修改无效.

    

    Web乱码问题

    动态网站由三部分构成: 浏览器, apache服务器(PHP),数据库服务器, 三个部分都有自己的字符集(中文), 数据需要在三个部分之间来回传递: 很容易产生乱码.

    ?

    如何解决乱码问题: 统一编码(三码合一)

    ?

    但是事实上不可能: 浏览器是用户管理(根本不可能控制).

    但是必须要解决这些问题: 主要靠PHP来做

    

    ?

    对应的sql脚本代码：

    -- 双中划线+空格: 注释(单行注释),也可以使用#号 ? -- 创建数据库 create database mydatabase charset utf8; ? -- 创建关键字数据库 create database database charset utf8; ? -- 使用反引号 create database `database` charset utf8; ? -- 创建中文数据库 create database 中国 charset utf8; create database `中国` charset utf8; ? -- 解决方案: 告诉服务器当前中文的字符集是什么 set names gbk; create database 中国 charset utf8; ? -- 查看所有数据库 show databases; ? -- 创建数据库 create database informationtest charset utf8; ? -- 查看以information_开始的数据库: _需要被转义 show databases like ‘information\_%‘; show databases like ‘information_%‘;????-- 相当于information% ? -- 查看数据库创建语句 show create database mydatabase; show create database `database`;????-- 关键字需要使用反引号 ? -- 修改数据库informationtest的字符集 alter database informationtest charset GBK; ? -- 删除数据库 drop database informationtest; ? -- 创建表 create table if not exists mydatabase.student(????-- 显示的将student表放到mydatabase数据库下 name varchar(10), gender varchar(10), number varchar(10), age int )charset utf8; ? -- 创建数据表 -- 进入数据库 use mydatabase; ? -- 创建表 create table class( name varchar(10), room varchar(10) )charset utf8; ? -- 查看所有表 show tables; ? -- 查看以s结尾的表 show tables like ‘%s‘; ? -- 查看表创建语句 show create table student\g????-- \g ==== ; show create table student\G????-- 将查到的结构旋转90度变成纵向 ? -- 查看表结构 desc class; describe class; show columns from class; ? -- 重命名表: student表 -> my_student(取数据库名字前两个字母) rename table student to my_student; ? -- 修改表选项: 字符集 alter table my_student charset = GBK; ? -- 给学生表增加ID放到第一个位置 alter table my_student add column id int first;????-- mysql会自动寻找分号: 语句结束符 ? -- 将学生表中的number学号字段变成固定长度,且放到第二位(id之后) alter table my_student modify number char(10) after id; ? -- 修改学生表中的gender字段为sex alter table my_student change gender sex varchar(10); ? -- 删除学生表中的年龄字段(age) alter table my_student drop age; ? -- 删除数据表 drop table class; ? -- 插入数据 insert into my_student values(1,‘itcast0001‘,‘Jim‘,‘male‘), (2,‘itcast0002‘,‘Hanmeimei‘,‘female‘); ? -- 插入数据: 指定字段列表 insert into my_student(number,sex,name,id) values (‘itcast0003‘,‘male‘,‘Tom‘,3), (‘itcast0004‘,‘female‘,‘Lily‘,4); ? -- 查看所有数据 select * from my_student; ? -- 查看指定字段,指定条件数据 select id,number,sex,name from my_student where id = 1;????-- 查看满足id为1的学生信息 ? -- 更新数据 update my_student set sex = ‘female‘ where name = ‘jim‘; ? -- 删除数据 delete from my_student where sex = ‘male‘; ? -- 插入数据(中文) insert into my_student values(5,‘itcast0005‘,‘张越‘,‘男‘); ? -- 查看所有字符集 show character set; ? -- 查看服务器默认的对外处理的字符集 show variables like ‘character_set%‘; ? -- 修改服务器认为的客户端数据的字符集为GBK set character_set_client = gbk; ? -- 修改服务器给定数据的字符集为GBK set character_set_results = gbk; ? -- 快捷设置字符集 set names gbk; ? ? -- 查看所有校对集 show collation; ? -- 创建表使用不同的校对集 create table my_collate_bin( name char(1) )charset utf8 collate utf8_bin; ? create table my_collate_ci( name char(1) )charset utf8 collate utf8_general_ci; ? -- 插入数据 insert into my_collate_bin values(‘a‘),(‘A‘),(‘B‘),(‘b‘); insert into my_collate_ci values(‘a‘),(‘A‘),(‘B‘),(‘b‘); ? -- 排序查找 select * from my_collate_bin order by name; select * from my_collate_ci order by name; ? -- 有数据后修改校对集 alter table my_collate_ci collate = utf8_bin; alter table my_collate_ci collate = utf8_general_ci;

    ?

原文：http://www.cnblogs.com/Prozhu/p/5754006.html