MySQL介绍
了解MySQL
MySQL分为企业版和社区版,社区版是完全免费开源的
MySQL是属于关系型数据库,和其他的关系型数据库最大的区别在于支持可插拔式的存储引擎,其中InnoDB非常强大
目前大部分公司使用的关系型数据库都是以MySQL作为数据存储解决方案
MySQL设计是C/S客户端服务端模式,应用作为MySQL Client向MySQL Server发送请求,获取响应结果,因此MySQL非常适用于集群环境,方便做主从复制,读写分离操作
为了提高效率,MySQL Client和MySQLServer处于不同的主机上,当通过网络socket进行网络通信,如果在同一台主机上,Client和server之间是通过内存进行通信,效率比socket通信要高
MySQL server的服务端模块采用IO复用技术+可伸缩的线程池,实现网络高并发的服务器的经典模型
MySQL基础知识
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):每一列保持原子特性
列都是基本数据项,不能进行分割,否则要设计成一对多的实体关系
注意:不符合第一范式不能称之为关系型数据库
示例:用户表(用户id,用户姓名,省份证,用户地址)
地址信息,可以在细分为省、市、区等不可拆分字段
通过分析用户表示不具有原子特性
需要拆分:
用户表:(用户id,用户姓名,省份证,省,市,区)
第二范式(2NF):属性完全依赖于主键(针对联合主键)
非主属性完全依赖于主属性,如果不能依赖于主属性,应该拆分为新的实体,设计成一对多的关系
示例:选课表(学号,姓名,年龄,课程名称,成绩,学分)
“学号,课程名称”是联合主键
姓名-》联合主键 =》部分依赖(姓名依赖于学号,不依赖于课程名称)
年龄-》联合主键 =》部分依赖(年龄依赖于学号,不依赖于课程名称)
成绩-》联合主键 =》完全依赖
学分-》联合主键 =》部分依赖(学分依赖于课程名称,不依赖于学号)
不满足第二范式;对选课表进行拆分:
学生表(学号,姓名,年龄)
课程学分表(课程名称,学分)
学生成绩表(学号,课程名称,成绩)
第三范式(3NF):属性不依赖于其他非主属性
要求一个数据库表中不包含已在其他表中已包含的非主键信息
注:一般关系数据库满足第三范式就可以
示例:学生表(学号,姓名,年龄,学院名称,学院地点,学院电话) 主键:学号
姓名,年龄,学员名称是完全依赖于主键
而学院电话、学院地点依赖于所在学院,并不依赖于主键学号
因此该设计不符合第三范式,就应该进行拆分,应该将学院专门设计成一张表
拆分成:
学院表(学院名称、学院地址、学院电话)=》学院名称
学生表(学号、姓名、年龄、学员名称)=》学号
BC范式:每个表中只要一个候选键
第四范式:消除表中的多重依赖
注:
应用的范式越高,表越多会带来问题:
1.查询时需要连接多个表,增加了查询的复杂度
2.查询时需要连接多个表,降低了数据库的查询性能
并不是范式越高越好,具体需要根据实际情况而定,而第三范式已经很大程度上减少了数据冗余,基本预防了插入异常、更新异常和删除异常
基础SQL
连接和断开数据库
进行SQL操作之前需要连接MySQL服务器
mysql -u XXX -pXXX
mysql代表客户端命令 -u:是连接数据库的用户名 -p:需要输入密码
退出数据库:
exit
库操作:
1.创建数据库SQL语句;
create database database_name;
2.查看数据库SQL语句
show databases;
3.删除数据库SQL语句
drop database database_name;
4.选择数据库语句
use database_name;
5.查看数据库下的数据表的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.查看表
2.1使用desc查看
desc table_name;
2.2.使用show命令
show create table table_name;
(SQL语句可以用','号或者是\G结尾,使用\G使打印格式更加清晰)
show命令可以打印出创建表的SQL语句,并限制该表的存储引擎及字符集编码信息
3.修改表
在使用过程中不满足使用的情况下,使用alter命令修改
修改表名:
alter table old_name rename new_name;
修改字段的数据类型:
alter table teble_name modify 属性名 数据类型;
修改字段名:
alter table table_name change 旧属性名 新属性名 数据类型;
增加字段:
alter table table_name add 属性名 数据类型 完整性约束 first|after 属性名2;
删除字段:
alter table table_name drop 属性名;
修改属性排列位置:
alter table table_name modify 属性名 数据类型 first|after 属性名2;
修改表的存储引擎:
alter table table_name 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);
插入中文数据出错:
解决方案1:
- 运行命令show create table 表名,可以看到,数据库默认的编码格式CHARSET=latin1;
- 将这个编码改为UTF-8,执行命令alter table 表名 character set utf8;
- 将字段的默认编码改为UTF-8,执行命令alter table 表名 modify 属性名 数据格式 character set utf8;
解决方案2:
set character_set_server=utf8;
5.查询表
SQL的基本查询结构:
select 属性列表(*) from 表名
[where 条件表达式]
[group by 属性名1[ having 条件表达式2] ]
[order by 属性名2[ASC|DESC] ]
5.1.带in的子查询
select 属性列表(*) from 表名 where 属性名 [not] in(元素1,元素2,...,元素n)
表示将in后面的元素存在的查询出来/剔除存在的元素
5.2.带between和and的范围查询
select 属性列表(*) from 表名 where 属性名 [not] between 取值1 and 取值2
5.3.带like的通配符匹配查询
select 属性列表(*) from 表名 where 属性名 [not] like '字符串'
此处的like后面的字符串可以携带通配符:
%:表示0个或者任意长度的字符串
_:表示单个通配符
5.4.空值查询
select 属性列表(*) from 表名 where 属性名 is (not) null;
5.5.带and的多条件查询
select 属性列表(*) from 表名 where 属性名 表达式1 and 表达式2 and ...表达式n;
5.6.带or的多条件查询
select 属性列表(*) from 表名 where 属性名 表达式1 or 表达式2 or ...表达式n;
5.7.去重查询
select distinct 属性名 from 表名;
5.8.对结果排序
select * from 表名 order by 属性名 asc|desc
ASC:升序 DESC:降序
5.9. limit分页查询
select * from 表名 limit num 不指定初始位置的limit记录数 num:记录数,一次最多加载num的数据
select * from 表名 limit index, num 指定起始位置 index初始位置 , num:记录数
多表查询
SQL强大功能之一在于在数据检索查询时执行连接(Join)表,可以很好的将多表进行连接查询操作
表与表关系
多表连接主要决定关系:方向性、主从关系
一对一关系:
在一对一关系中,A表中的一行最多只能匹配B表中的一行,反之亦然,创建的就是一对一关系
一对多关系:
一对多关系是最普通一种关系,A表中的一行可以匹配B表中的多行,但是B表中的一行只能匹配A表中的一行。
例如:存在部门表和人员表之间具有一对多的关系,每个部门有很多的员工,但是每个员工只属于一个部门,只有当一个相关列是一个主键或者具有唯一性约束时,才能创建一对多的关系
多对多关系:
在多对多的关系中,A表中一行可以匹配B表中的多行,反之亦然。要创建这种关系,需要定义第三张表,称之为结合表,他的主键由A表和B表外部键组成
mysql> select * from student;
+-----+----------+------+------+
| SID | Sname | Ssex | Sage |
+-----+----------+------+------+
| 1 | tulun | nan | 23 |
| 2 | qiandian | nan | 20 |
| 3 | sunfen | nan | 21 |
| 4 | wulan | nv | 18 |
| 5 | CJ1210 | 1 | 11 |
| 6 | LG1213 | nan | 2 |
| 7 | CJ1210 | 1 | NULL |
| 8 | GY1803 | NULL | NULL |
| 11 | TL | nan | 11 |
| 12 | TL12 | nan | 12 |
| 13 | 13 | 13 | 13 |
| 17 | 18 | man | 22 |
| 18 | tulun1 | man | 23 |
+-----+----------+------+------+
13 rows in set (0.01 sec)
mysql> select * from SC;
+------+------+-------+
| SID | CID | score |
+------+------+-------+
| 1 | 1 | 80 |
| 1 | 3 | 87 |
| 2 | 2 | 89 |
| 6 | 1 | 66 |
| 3 | 3 | 88 |
| 2 | 2 | 98 |
| 9 | 2 | 99 |
+------+------+-------+
7 rows in set (0.00 sec)
笛卡尔积
笛卡尔积的元素是元组,关系A和关系B的笛卡尔积可以记为(AXB),如果A表为a条,B表为b条,那么A和B的笛卡尔积为(a+b)列数,(a*b)行的元素集合
检索出来的条目是将第一个表中的行数乘以第二个表中的行数
select * from 表1,表2;
内连接
按照连接条件,返回两张表中的满足条件的记录
内连接称之为普通连接或自然连接
等值连接:当条件为”=“的连接,称之为等值连接
自然连接:去除冗余的列
select * from student natural join SC;
外连接:
外连接不仅包含符合条件的连接的行,而且还包含左表(左外连接)、右表(右外连接)中所有的数据行
左连接(left join)
左连接是以左表中的数据为基准,若左表中有数据而右表中没有数据,则显示左表中的数据,右表中的数据显示为空
select * from 左表 left join 右表 on 条件;
右连接
右连接是以右表的数据为基准,若右表有数据且左表没有数据,则显示右表中的数据,左表中的数据则显示为空
select * from 左表 right join 右表 on 条件;
练习:
1、学生表
Student(SID,Sname,Sage,Ssex)
--SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别
2.课程表
Course(CID,Cname,TID)
--CID --课程编号,Cname 课程名称,TID 教师编号
3.教师表
Teacher(TID,Tname)
--TID 教师编号,Tname 教师姓名
4.成绩表
SC(SID,CID,score)
--SID 学生编号,CID 课程编号,score 分数
学生表:
编号 姓名 年龄 性别
1 赵雷 20 男
2 钱电 20 男
3 孙风 21 男
4 吴兰 18 女
5 孙兰 17 女
课程表:
课程号 课程名称 教师编号
1 语文 2
2 数学 1
3 英语 3
教师表:
教师编号 教师名
1 张三
2 李四
3 王五
成绩表:
学生编号 课程编号 分数
1 1 80
1 2 71
1 3 87
2 1 88
2 2 70
2 3 89
3 1 68
3 2 78
3 3 87
4 1 67
4 2 58
4 3 89
5 1 56
5 2 89
6 3 38
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select 查询结果 from 表 where 条件表达式
关注查询结果、查询表、查询条件
通过分析:
学生信息-》学生表
课程分数-》成绩表
”01“课程、”02“课程 -》成绩表
表:学生表、成绩表
查询结果:学生信息、课程分数
条件表达式:查询"01"课程比"02"课程成绩高的学生-》同一个学生,既有”01“课程分数且有”02“课程分数,
且”01“课程分数比”02“课程分数高 成绩表当做两张表
select s.SID ,s.Sname,s.Sage,s.Ssex, sc1.score '01课程成绩',sc2.score '02课程成绩' from student as s ,sc as sc1 ,sc sc2 where s.SID=sc1.SID and s.SID=sc2.SID and sc1.CID=1 and sc2.CID=2 and sc1.score > sc2.score;
2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.SID,s.Sname,avg(sc.score) '平均成绩' from student s,sc where s.SID=sc.SID group by(sc.SID) having avg(sc.score)>60;
3、查询在sc表中不存在成绩的学生信息
select * from student where SID not in (select distinct SID from sc );
4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.SID,s.Sname,count(sc.CID) '选课总数',sum(sc.score) '总成绩' from student s, sc where s.SID=sc.SID group by s.SID;
5、查询"李"姓老师的数量
select count(TID) '李老师' from teacher where Tname like '李%';
6、查询学过"张三"老师授课的同学的信息
select * from student where SID in(select SID from sc where CID in (select CID from course where TID in(select TID from teacher where Tname='张三')));
7、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select s.* from student s,sc sc1 , sc sc2 where s.SID=sc1.SID and s.SID=sc2.SID and sc1.CID=1 and sc2.CID=2;
8、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select s.SID,s.Sname,avg(sc.score) '平均成绩' from student s, sc where s.SID=sc.SID and sc.score<60 group by sc.SID having count(sc.CID)>=2;