MySQL介绍

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:

  1. 运行命令show create table 表名,可以看到,数据库默认的编码格式CHARSET=latin1;
  2. 将这个编码改为UTF-8,执行命令alter table 表名 character set utf8;
  3. 将字段的默认编码改为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;

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值