MySQL是关系型数据库,和其他的关系型数据库最大的区别在于支持可插拔式的存储引擎,其中InNoDB非常强大;
MySQL设计是C/S客户端服务端模式,应用作为MySQL Client 向MySQL server发送请求,获取响应结果,因此MySQL非常适用于集群环境,方便做主从复制,读写分离操作;
为了提高效率,MySQL Client 和MySQL serve处于不同的主机上,当通过网络socket进行网络通信,如果在同一台主机上,Client和server之前是通过内存进行通信, 效率比socket通信要高;
MySQL serve的服务端模块采用IO复用技术+可伸缩的线程池,实现网络高并发的服务器的经典模型。
基础知识
SQL语句的划分
DDL(Data definition Language):数据定义语言,DDL语言是定义不同的数据库、表、列等数据库对象的定义,常见的语句关键字包括create、drop、alter等
DML(Data Manipulation Language):数据操纵语言,用于添加、删除、修改和查询数据库记录,常用的关键字包括insert、delete、update和select等
DCL(Data Control Language)数据控制语言,用户控制不同数据段直接的许可或访问级别的语句,定义了数据库、表、字段的用户访问权限和安全级别,主要的语句包括grant,remove
MySQL数据类型
数据类型是规定了数据的大小,因此使用时需要选择合适的类型,不仅会降低表占用的磁盘空间,间接减少磁盘IO次数,表的访问效率,和设计的表结构类型也是息息相关的。
MySQL中提供了多种的数据类型,包括整数类型,浮点类型,定点类型,日期和时间类型,字符串和二进制类型
归纳数据类型分为三类:数值类型,日期和时间类型和字符串类型
数值类型:包括定点类型、浮点类型和整数类型
日期/时间类型:
字符串类型 :文本数据类型
char(5) 12 占用是5字节
varchar(5) 12 3个字节(有一个结束符)
二进制类型数据:
选择数据类型时:尽量选择可以保证数据正确存储的最小数据类型,小的数据类型占用空间少,操作起来更快
数据库范式
实体:现实世界中客观存在并可以被区别对待的事务,比如“一个学生”,“一门课”等;
属性:实体上具有的某一特征。比如:性别是人的一个属性,在关系型数据库中,属性又是个物理概念,属性可以看做是“表的一列”;
元组:表中的一行就是一个元组;
分量:元组中某个属性值,在一个关系型数据库中,是一个操作原子,即关系型数据库在做任何操作的时候,属性是不可分的,否则就不是关系型数据库;
码:表中可以唯一确定一个元组的某个属性,如果这个码不止一个,那么都叫候选码,从中挑选的一个就叫做主码;
全码:如果一个码包含所有的属性,这个码称之为全码;
主属性:一个属性只要在一个候选码中出现过,这个属性就是主属性;
非主属性:与主属性相反,没有在任何候选码中出现过,这个属性就是非主属性;
外码:一个属性(或者属性组),他不是码,但是他是别的表的码,他就是外码。
第一范式(1NF):每一列保持原子特性
列都是基本数据项,不能进行分割,否则设计成一对多的实体关系
注意:不符合第一范式不能称之为关系型数据库
eg:用户表(用户id,用户姓名,省份证,用户地址)
地址信息,可以在细分为省、市、区等不可拆分字段
通过分析用户表不具有原子特性
需要拆分:
用户表:(用户id,用户姓名,省份证,省,市,区)
第二范式(2NF):属性完全依赖于主键(针对联合主键)
非主属性完全依赖于主关键字,如果不能依赖主键,应该拆分成新的主体,设计成一对多的关系
示例:选课表(学号,姓名,年龄,课程名称,成绩,学分)
将“学号,课程名称”是联合主键
姓名->联合主键 =>部分依赖(姓名依赖于学号,不依赖于课程名称)
年龄->联合主键 =>部分依赖(年龄依赖于学号,不依赖于课程名称)
成绩->联合主键 =>完全依赖
学分->联合主键 =>部分依赖(学分依赖于课程名称,不依赖于学号)
不满足第二范式,对选课表进行拆分:
学生表(学号,姓名,年龄)
课程学分表(课程名称,学分)
学生成绩表(学号,课程名称,成绩)
第三范式(3NF):属性不依赖于其他非主属性
要求一个数据库中不包含已在其他表中已包含的非主键信息
一般关系型数据库满足第三范式就可以
示例:学生表(学号,姓名,年龄,学院名称,学院地点,学院电话) 主键:学号
姓名,年龄,学院名称是完全依赖于主键
而学院电话、学院地点依赖于所在学院,并不依赖于主键学号
因此该设计不符合第三范式,就应该进行拆分,应该学员专门设计成一张表
拆分成:
学院表(学院名称、学院地址、学院电话)
学生表(学号、姓名、年龄、学院名称)
BC范式:每个表中只有一个候选键
第四范式:消除表中的多值依赖
应用范式越高,表越多,即会带来的问题:
1、查询表时需要连接多个表,增加了查询的复杂度;
1、查询表时需要连接多个表,降低了数据库的查询性能;
并不是范式越高越好,需要根据实际情况而定,第三范式已经很大程度上减少了数据冗余,基本预防了数据插入异常,更新异常,删除异常。
基础SQL
连接和断开数据库:
进行SQL操作之前需要连接MySQL服务器
mysql -u root -pXXXXX
退出数据库:exit;
库操作
创建数据库SQL语句:
create database database_name;
查看数据库SQL语句:
show databases;
删除数据库SQL语句:
drop database database_name;
选择数据库SQL语句:
use database_name;
查看数据库下的数据表的SQL语句:
show tables;
注意:该命令必须先选择一个库,使用use XXX后才能查看表
表操作
1、创建表
create table table_name(
属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
...
属性名 数据类型 [完整性约束条件]
);
注意;创建表时需要选取合适的数据类型,而且还可以给字段添加完整性约束条件;
比如:主键,非空主键,唯一键...
示例:
CREATE TABLE `tulun` (
`id` int(11) NOT NULL,
`className` varchar(10) NOT NULL,
`content` varchar(50) DEFAULT '无'
)
2、查看表
①使用desc查看
desc table_name;
用desc命令可以查看表的字段名称,属性类型,是否为空,约束条件,默认值及备注信息;
②使用show命令
show create table table_name;
可以用‘;’或者‘\G’结束,后者打印格式更加清晰;
show命令可以打印出创建表时的SQL语句,并限制该表的存储引擎及字符编码信息;
3、修改表
在使用过程中不满足使用的情况下,使用alter命令修改
修改表名:
alter table old_Name rename new_Name;
修改字段的数据类型:
alter table table_name modify 属性名 数据类型;
修改字段名:
alter table 表名 change 旧属性名 新属性名 新数据类型;
增加字段:
alter table 表名 add 属性名 属性类型[完整性约束] [first | after 属性名2];
删除字段:
alter table 表名 drop 属性名;
修改属性排列位置:
alter table 表名 modify 属性名1 数据类型 first | after 属性名2 ;
修改表的存储引擎:
alter table 表名 engine=InNoDB|MyISAM;
4、数据插入到表
单条数据插入
insert into 表名(属性1,属性2,...,属性n)
values (值1,值2,...,值n);
批量数据插入
insert into 表名(属性1,属性2,...,属性n)
values
(值1,值2,...,值n),
(值1,值2,...,值n),
...,
(值1,值2,...,值n);
5、查询表
SQL的基本查询结构:
select 属性列表(*) from 表名
[where 条件表达式]
[group by 属性名1[ having 条件表达式2] ]
[order by 属性名2[ASC|DESC] ]
带in的子查询:
where 属性名 [not] in(元素1,元素2,...,元素n);
表示将in后面的元素存在的查询出来/剔除存在的元素
带between and的范围查询:
where 属性名 [not] between 取值1 and 取值2;
带like的通配符匹配查询:
where 属性名 [not] like '字符串'
此处的like后面的字符串可以携带通配符
%:表示0个或者任意长度的字符串
_:只能是表示单个通配符
空值查询:
where 属性名 is [not] null