MySQL数据库学习体系笔记

一、数据库系统概论
学习知识点
1.数据管理计算的产生和发展
2.关系型数据库中常用概念
3.结构化查询语音SQL简介

1.数据管理计算的产生和发展
应用程序通过数据库管理系统与数据库进行数据交互（插入、更新、删除数据、检索数据）
应用程序<=>数据库管理系统（DBMS）<=>数据库

2.关系型数据库中常用概念
表
行（记录）
列
元组
字段（域）
主键（不能重复，不能为空，唯一区分表记录）
外键

是否存在数据冗余
是否失去了数据完整性
主键为空，失去实体完整性
是否失去了域完整性（类型、范围、格式）

3.结构化查询语音SQL简介
概念：结构化查询语言（Structured Query Language）
用于存储数据以及查询、更新和管理关系数据库系统。
SQL分类
数据定义语言（DDL）：create、alter、drop
数据操纵语言（DML）：增加（insert）、删除（delete）、更新（update）和查询（select）
数据控制语言（DCL）：分配（grant）、回收（revoke）和拒绝（deny）

本节总结
数据库、数据库管理系统
关系型数据库常用概念
结构化查询语言SQL简介

二、mysql简介及安装配置
学习知识点
1.mysql简介
2.mysql下载与安装
3.登录mysql
4.数据库管理

1.mysql简介
发展历史：由瑞士mysql AB公司开发，目前属于Oracle旗下公司。
mysql特点：开源性 、占用资源少、适用于多种编程语言、支持客户端访问。

2.mysql下载与安装
下载mysql：登录mysql官方网站下载（www.mysql.com），选择社区版本（community），选择图形化安装包（mysql installer msi）。
常用版本：
mysql community server 社区版本，开源免费，但不提供官方技术支持
mysql enterprise edition 企业版本，需付费，可以试用30天
mysql cluster 集群版，免费开源。可将几个mysql server封装成一个server
mysql cluster CGE 高级集群版，需付费
安装及配置mysql：略。

3.登录mysql
首先启动mysql服务：windows下的服务中启动
（1）使用命令窗口登录mysql
（2）使用客户端登录mysql

4.数据库管理
数据库的管理主要分成两个部分：数据表管理和数据管理。

三、mysql数据表管理（上）
学习知识点
1.mysql数据类型
2.创建数据表
3.为列添加约束

1.mysql数据类型
二进制 bit
定点数 decimal(p,s)
浮点数 float() 4字节 double(p,s) 8字节
日期/时间 date time datetime
文本 char varchar text
整数 tinyint smallint int bigint

2.创建数据表
ER图
数据库设计的基本步骤:
(1)需求分析阶段：分析客户的业务和数据处理需求。
(2)概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整。
(3)详细设计阶段：将E-R图转换为多张表，进行逻辑设计，并应用数据库设计的三大范式进行审核。
(4)代码编写阶段：选择具体数据库进行物理实现，并编写代码实现前端应用。

需求分析
需求分析：系统要记录哪些信息？
数据表：游戏表、玩家表、分数表
每个表的字段：

实体关系图
实体：正方形
属性：椭圆形
关系：菱形
项目E-R图

使用范式检查数据表设计合理性
什么是范式？设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据冗余越小。
Dr E.F.codd 最初定义了规范化的几个级别，范式用以定义具有最小冗余的表结构。
检查范式：第一范式、第二范式、第三范式
第一范式：如果每列都是不可再分的最小数单元 （也称为最小的原子单元），则满足第一范式（1NF）。
第一范式的目标是确保每列的原子性。

第二范式：如果关系满足1NF，并且除了主键以外的其他列，都完全依赖于该主键，则满足第二范式（2NF）。
第二范式要求每个表只描述一件事情。

第三范式：如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）。
第三范式要求表中不存在冗余字段。

使用客户端创建数据表
使用命令创建数据表

3.为列添加约束
首先明确：约束，实际是充当数据表的数据检查官的作用，主要是检查插入或修改的数据是否符合表设定的规范。

主键约束
primary key作用：保证实体完整性

外键约束
reference作用：保证引用完整性
（Reference 引用某表中的某列，如果某表中的某列不存在，则添加不成功）

检查约束
check（gno>0）作用：保证域完整性

默认约束
default作用：保证域完整性

自增列
auto_increment作用：保证实体完整性

四、mysql数据表管理（下）
学习知识点
1.查看数据表
2.修改数据表
3.删除数据表

1.查看数据表
查看表基本结构
语法：describe/desc table_name
结果含义
field：字段名
type：字段类型
null：是否可以为空
key：是否编制索引
default：默认值
extra：附加信息，如自增列

查看表详细结构
语法：show create table table_name
格式化结果：show create table table_name\G

2.修改数据表
修改表名
语法：alter table old_name rename to new_name

修改字段名
语法：alter table table_name change old_name new_name new_type

修改字段数据类型
语法：alter table table_name modify col_name new_type

添加和删除字段
添加字段语法：alter table table_name add new_col_name new_type
删除字段语法：alter table table_name drop col_name

增补约束
添加主键语法：alter table table_name add constraint con_name primary key(col_name)
添加外键语法：alter table f_table add constraint con_name foreign key(f_col) references m_table(m_col)
添加检查约束语法：alter table table_name add constraint con_name check(exp)
添加默认值语法：alter table table_name alter col_name set default value
添加自动增长语法：alter table table_name modify column col_name … auto_increment

3.删除数据表
删除无关联数据表
语法：drop table [if exists] table_name
删除有关联数据表
先解除关联关系：alter table f_table_name drop foreign key con_name
删除表：drop table table_name

五、数据管理
学习知识点
1.插入数据
2.修改数据
3.删除数据

1.插入数据
所有列都插入
语法 insert [into] table_name values(v1,v2…)
特点 列值同数，列值同序

为特定列插入
语法 insert [into] table_name(col1,col2…) values(v1,v2…)
特点 指定顺序，列值对应

一次性插入多条记录
语法 insert [into] table_name[(col1,col2…)] values(v1,v2…),values(v1,v2…)…
特点 列值同数，列值同序

2.修改数据
修改全部数据
语法 update table_name set{col_name=expression}[,…n]

修改特定数据
语法 update table_name set{col_name=expression}[,…n] where condition_expression

3.删除数据
使用delete命令删除数据
delete [from] table_name [where conditiion_expression]

使用truncate table删除数据(清空表中所有数据
truncate table table_name

六、数据查询（上）
学习知识点
1.查询基础
2.简单查询
3.条件查询
4.对查询结果排序

1.查询基础
tips：没必要死记硬背，只要正确理解并应用

查询和提取数据的过程:
客户程序发送->查询请求->数据库
客户程序<-查询结果集<-数据库

select语句简介
语法 select col1,col2,… from table1,table2… [where conditions] [group by group_by_list] [having conditions] [order by order_list asc | desc]

2.简单查询
查询表的全部行和列
select * from table_name

查询表的部分列
select col1,col2 from table_name

别名的使用
select col1 as ‘rename1’,col2 as ‘rename2’ from table_name

distinct关键字
作用：消除结果集中的重复行
select distinct col1 from table_name

limit关键字
select * from table_name limit2,3 (显示表中第3至第5条数据)

3.条件查询
普通条件查询
语法 select col_list from table_name [where condition_expression]
示例：
select * from table_name where col1=’123’
select * from table_name where col1>100

比较运算符
= 等于
<> 不等于
大于
= 大于等于
< 小于
<= 小于等于

逻辑运算符
and 并且
or 或者
not 非

4.模糊查询
示例：查询所有列col1在100和1000之间的数据
select * from table_name where col1>100 and col1<1000
select * from table_name where col1 between 100 and 1000

示例：查询所有列col1不在100和1000之间的数据
select * from table_name where col1 not between 100 and 1000

示例：查询所有列col1在日期2000-01-01和2017-07-10之间的数据
select * from table_name where col1 between ‘2000-01-01’ and ‘2017-07-10’

通配符
‘_’ 一个字符
% 任意长度
[] 指定范围内
[^] 不在括号中

示例：查询所有列col1姓陈的数据
select * from table_name where col1 like ‘陈%’

示例：查询所有列col1中非姓陈的数据
select * from table_name where col1 not like ‘陈%’

查询空值的运算符
示例：查询所有列col1为null的所有信息

select * from table_name where col1 is null
示例：查询所有列col1不为null的所有信息

select * from table_name where col1 is not null

5.对查询结果排序
对指定列进行排序
单列排序要素：排序依据（依据哪一列排序）、排序方式（升序|降序）
语法：select col_list from table_name order by order_by_list [asc|desc]

多列排序
多列排序要素：排序依据（依据哪一列排序）、优先级
示例：查询表中所有数据按col1升序排序，col2降序排序，其中col1为优先级高
Select * from table_name order by col1 asc,col2 desc

七、数据查询（下）
学习知识点
1.汇总和分组数据
2.连接查询
3.子查询
4.联合查询

1.汇总和分组数据
聚合函数：对多条数据进行统计
查询语句 -> 结果集返回多条数据 通过聚合函数可以统计结果集 生成一条记录
常用的聚合函数：
Sum() 支持数据类型数字 对指定列中的所有非空值求总和
Avg() 数字 对指定列中的所有非空值求平均值
Min() 数字、字符、datetime 返回指定列中的最小数字、最早的日期或者最小的字符串
Max() 数字、字符、datetime 返回指定列中的最大数字、最近的日期或者最大的字符串
Count() 任意基于行的数据类型 统计结果集中全部记录行的数量
示例：查询表中一共有多少记录信息
select count(col1) from table_name
select count(*) from table_name
示例：查询id为123的表总分数
select sum(score) as ‘总分数’ from table_name where col1=’123’
示例：查询id为123的表平均分数
select avg(score) as ‘平均分数’ from table_name where col1=’123’
示例：查询id为123的表最高分数
select max(score) as ‘最高分数’ from table_name where col1=’123’

在结果集内分组
使用group by 分组
示例：查询每个col1的表总分、平均分、最高分数
select
sum(score) as ‘总分数’,
avg(score) as ‘平均分数’,
max(score) as ‘最高分数’
from table_name group by col1

筛选分组结果
在使用group by子句时，可用having子句为分组统计进一步设置统计条件，having子句与group by子句的关系相当于where子句于select子句之间的关系
与where子句的区别是，在having子句中是以聚合函数的统计结果为筛选条件
示例：查询平均分数大于2000的玩家id，总分数
select col1 sum(score) as ‘总分数’ from table_name group by col1 having avg(score) > 2000
示例：查询所有用户的平均分，和总分数，并按平均分数倒序排列
select col1 avg(score) as ‘平均分’,sum(score) as ‘总分数’ from table_name group by col1 order by avg(score) desc

Select语句的执行顺序
from 子句指定数据源
where 子句基于指定的条件对记录进行筛选
group by 子句将数据划分为多个分组
使用聚合函数进行计算
使用having子句筛选分组
使用order by子句对结果进行排序

2.连接查询
from子句进行多表查询
笛卡儿积：什么是笛卡儿积？
有t1，t2两张表，如果执行了select * from t1,t2会产生怎样的结果集？

连接查询分为内连接和外连接
内连接特点：相连接的两张表地位平等，如果一张表中在另一张表中不存在对应数据，则不做连接。
from子句后面直接出现多个表名，这种连接方式即属于内连接，是隐式内连接。
语法：select col_list from t1, [inner] join t2 on t1.col = t2.col
示例：查询平均分数大于3500的分数信息，显示玩家昵称，总分数，平均分数，并按照平均分数降序排列
select user_name ‘昵称’，sum(score) ‘总分’，avg(score) ‘平均分’
from games g inner join scores s on g.gno = s.sno
inner join users u on s.user_qq = u.user_qq
group by u.user_qq,user_name
having avg(score)>3500
order by avg(score) desc

外连接又分左外连接和右外连接
外连接特点：做连接的两个表地位不平等，其中有一张是基础表，基础表中的每一条数据必须出现，即使另一张表中没有数据与之匹配，也要用null补齐。左外连接时左表是基础表，右外连接时右表是基础表，语句中先出现的表为‘左表’，后出现的表为‘右表’。
语法：select col_list from t1 left|right [outer] join t2 on t1.col = t2.col

3.子查询
使用in关键字的子查询
示例；查询游戏类型是‘棋牌类’的游戏分数信息
游戏分数表中并未包含游戏类型信息
思路一：采用连接查询
思路二：分两步进行，首先找到所有棋牌类游戏的编号，再以这一组编号为查询依据完成查询
select gno from games where gtype = ‘棋牌类’
select * from scores where gno in (select gno from games where gtype = ‘棋牌类’)
注意：子查询一般不写成select * from…

使用exists关键字的子查询
示例：如果存在昵称为‘孙悟空’，则查询分数表中数据
select * from scores where exists (select * from users where user_name = ‘孙悟空’)

4.联合查询
作用与特点：可以把多条查询语句所产生的结果集纵向连接为一体。有all关键字可以显示全部数据（即重复的也显示出来）。列的数量与类型都要兼容。
示例：查询玩家表中所有女性玩家和生日为空的玩家
select * from users where user_sex = ‘女’ union select * from users where user_birthday is null
示例：查询qq号是‘123’的玩家所有分数并计算出总分数和平均分，并显示到同一结果集中
select user_qq,gno,score from scores where user_qq = ‘123’
union all
select ‘总分’，”,sum(score) from scores where user_qq = ‘123’
union all
select ‘平均分’，”,avg(score) from scores where user_qq = ‘123’

八、mysql函数
学习知识点
1.日期与时间函数
2.数学函数
3.字符串函数
4.系统函数

函数的概念：按指定格式输入参数，返回正确结果的运算单元。可以理解成函数是运算的单元，当输入适当的参数，则返回相应的运算结果。

1.日期与时间函数
返回当前日期
curdate()
current_date()
curdate()+0 可以将当前日期值转换为数值型
返回当前时间
curtime()
current_time()
curtime()+0 可以将当前时间值转换为数值型
返回当前日期和时间
current_timestamp()
localtime()
now()
sysdate()
获取月份
month(date)
monthname(date)
获取星期
dayname(date)
dayofweek(date)
weekday(date)
week(date)
weekofyear(date)
获取天数
dayofyear(date)
dayofmonth(date)
获取年份、季度、小时、分钟、秒钟
year(date)
quarter(date)
hour(time)
minute(time)
second(time)

2.数学函数
绝对值函数
abs(x)
符号函数
sign(x)
获取随机数函数
rand()
rand(x)
获取整数函数
ceil(x)/ceiling(x)
floor(x)
四舍五入函数
round(x)
round(x,y)
truncate(x,y)
求余函数
mod(x,y)
幂运算函数
pow(x,y)/power(x,y)
exp(x)
sqrt(x)
角度弧度互换函数
radians(x)
degrees(s)
圆周率函数
pi()
三角函数
sin(x)
asin(x)
cos(x)
acos(x)
tan(x)
atan(x)
cot(x)

3.字符串函数
计算字符数和字符串长度的函数
char_length(s)
length(s)
合并字符串函数
concat(s1,s2…)
concat_ws(x,s1,s2…)
替换字符串函数
insert(s1,x,len,s2)
replace(s,s1,s2)
截取字符串的函数
left(s,n)
right(s,n)
重复生成字符串函数
repeat(s,n)
大小写转换函数
lower(x)/lcase(x)
upper(x)/ucase(x)
填充字符串的函数
lpad(s1,len,s2)
rpad(s1,len,s2)
删除空格函数
ltrim(s)/rtrim(s)
trim(s)
删除指定字符串
trim(s1 from s)
获取子字符串
substring(s,n,len)
mid(s,n,len)
返回指定位置字符串函数
elt(n,s1,s2…)
返回指定字符串位置
field(s,s1,s2…)

4.系统函数
获取mysql版本号的函数
version()
查看当前用户的连接数
connection_id()
查看当前所用数据库函数
database()
schema()
获取用户名的函数
user()
current_user()
system_user()
session_user()

九、何在线学习开发

1.你的问题？
学习自制力不强：易拖沓、易放弃
无从下手
没有项目经验
学的不系统、学过就忘
不知道现在的技术能力是否满足工作需要

2.培训机构和自学的区别是什么？
有经验的老师带领逐个掌握知识点
学到项目经验
项目团队管理经验
通过测评体系，章节测评，消化知识，掌握知识
就业服务体系帮助你更快更好的就业