一 mysql 内容介绍
1mysql1.数据库，是当前应用非常广泛的一款关系型数据库
2 目前mysql在数据库中的排名是第二，也是第一oracle（甲骨文）公司的产品。
3主要知识点包括：
a)数据库与表的操作
b)数据的增加、修改、删除
c)数据的查询（重点）
d)与python交互
二 创建库和表
1.主要知识点包括：能够与mysql建立连接，创建数据库、表，分别从图形界面与脚本界面两个方面讲解
2.相关的知识点包括：E-R关系模型，数据库的3范式，mysql中数据字段的类型，字段约束
3.数据库的操作，包括创建、删除
4.表的操作，包括创建、修改、删除
5.数据的操作，包括增加、修改、删除、查询，简称crud
6.学生表结构：
a)id
b)名称
c)性别
d)地址
e)生日
8.科目表结构：
a)id
名称
1 数据库简介
人类在进化的过程中，创造了数字、文字、符号等来进行数据的记录，但是承受着认知能力和创造能力的提升，数据量越来越大，对于数据的记录和准确查找，成为了一个重大难题。
计算机诞生后，数据开始在计算机中存储并计算，并设计出了数据库系统数据库系统解决的问题：持久化存储，优化读写，保证数据的有效性
当前使用的数据库，主要分为两类
1.文档型，如sqlite，就是一个文件，通过对文件的复制完成数据库的复制
2.服务型，如mysql、oracle、postgre，数据存储在一个物理文件中，但是需要使用终端以tcp/ip协议连接，进行数据库的读写操作
E-R模型
当前物理的数据库都是按照E-R模型进行设计的
E（entry ） 实体
R( relationship) 关系
一个实体转换为数据库中的一个表
关系描述两个实体之间的对应规则，包括
1一对一
现代的婚姻：一个男人娶一个女人，且一个女人只能价一个男人
2一对多
古代的婚姻，一个男人娶多个老婆，一个女人只能嫁一个男人
3多对多
不同教室可以被多个同学使用，同学可以使用多个教室
关系转换为数据库表中的一个列在关系型数据库中一行就是一个对象
三范式
经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式
1.第一范式（1NF)：列不可拆分
2.第二范式（2NF)：唯一标识
3.第三范式（3NF)：引用主键
说明：后一个范式，都是在前一个范式的基础上建立的
2 数据完整性
一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表中
在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束
字段类型
在mysql中包含的数据类型很多，这里主要列出来常用的几种
1.数字：int,decimal
decimal(5,2)整数3位，小数2位
2.字符串：char,varchar,text
char：不根据内容改变字符串长度
varchar：根据内容改变字符串长度
字符=字节+编码
char因为是定长，所以查询效率高，浪费空间
varchar因为是边长，所以节省空间，查询效率相对低
3.日期：datetime
date：年月日
time：时分秒
datetime：年月日时分秒
4.布尔：bit
0或1
约束
1.主键primary key
2.非空not null
3.惟一unique
4.默认default
5.外键foreign key
三 使用图形窗口连接
·windows下的navicat点击“连接”弹出窗口，按照提示填写连接信息，如下图
·连接成功后，会在连接名称下面显示出当前的数据库
·双击选中数据库，就可以编辑此数据库
·下次再进入此软件时，通过双击完成连接、编辑操作
数据库操作
·在连接的名称上右击，选择“新建数据库”，弹出窗口，并按提示填写
在数据库上右击，选择“删除数据库”可以完成删除操作
表操作
·当数据库显示为高亮时，表示当前操作此数据库，可以在数据中创建表
·一个实体对应一张表，用于存储特定结构的数据
·点击“新建表”，弹出窗口，按提示填写信息
·主键的名称一般为id，设置为int型，无符号数，自动增长，非空
·自动增长表示由mysql系统负责维护这个字段的值，不需要手动维护，所以不用关心这个字段的具体值
·字符串varchar类型需要设置长度，即最多包含多少个字符
·点击“添加栏位”，可以添加一个新的字段
·点击“保存”，为表定义名称
数据操作
·表创建成功后，可以在右侧看到，双击表打开新窗口，如下图
·在此窗口中可以增加、修改、删除数据
逻辑删除
·对于重要数据，并不希望物理删除，一旦删除，数据无法找回
·一般对于重要数据，会设置一个isDelete的列，类型为bit，表示逻辑删除
·大于大量增长的非重要数据，可以进行物理删除
·数据的重要性，要根据实际开发决定
四 脚本命令操作
sql语句分为两大类：
1、dml对表的数据内容进行增删改查
2、ddl除了dml之外
·命令操作方式，在工作中使用的更多一些，所以要达到熟练的程度
·打开终端，运行命令
>mysql -uroot -p
·退出登录
quit或exit
·登录成功后，输入如下命令查看效果
·注意：在语句结尾要使用分号;
远程连接
·一般在公司开发中，可能会将数据库统一搭建在一台服务器上，所有开发人员共用一个数据库，而不是在自己的电脑中配置一个数据库
·运行命令
mysql -hip地址-uroot -p
·-h后面写要连接的主机ip地址
·-u后面写连接的用户名
·-p回车后写密码
数据库操作
·创建数据库
create database数据库名charset=utf8;
·删除数据库
drop database数据库名;
·切换数据库
use数据库名;
·查看当前选择的数据库
select database();
表操作
·查看当前数据库中所有表
show tables;
·创建表
auto_increment表示自动增长,只能是数字类型
主键本身是用来唯一标识这一行，没有业务逻辑意义，所以是什么值不重要，只要唯一就行，所以如果是主键不需要修改
create table表名(列及类型);
如：
create table students(
id int auto_increment primary key,
sname varchar(10) not null
);
·修改表
alter table表名add|modify|drop列名类型;
如：
alter table students add birthday datetime;
·删除表
drop table表名;
·查看表结构
desc表名;
·更改表名称
rename table原表名to新表名;
·查看表的创建语句
show create table '表名';
数据操作
·查询
select * from表名
·增加
全列插入：insert into表名values(...)
缺省插入：insert into表名(列1,...) values(值1,...)
同时插入多条数据：insert into表名values(...),(...)...;
或insert into表名(列1,...) values(值1,...),(值1,...)...;
·主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准
·修改
update表名set列1=值1,... where条件
·删除
delete from表名where条件
·逻辑删除，本质就是修改操作update
alter table students add isdelete bit default 0;
如果需要删除则
update students isdelete=1 where ...;
五 查询
内容简介
查询的基本方法
select * from 表名；
·from关键字后面写表名，表示数据来源于是这张表
·select后面写表中的列名，如果是*表示在结果中显示表中所有列
·在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中
·如果要查询多个列，之间使用逗号分隔
消除重复行
1 条件
使用where子句对表中的数据筛选，结果为true的行会出现在结果集中
语法如下
select * from 表名 where 条件；
1比较运算符
·等于=
·大于>
·大于等于>=
·小于<
·小于等于<=
·不等于!=或<>
·查询编号大于3的学生
例子：
2 逻辑运算符
and，or，not
例1：选择id小于10且isdelete=1的项
例2：选择id<10或isdelete=0
3 模糊查找
·like
·%表示任意多个任意字符
·_表示一个任意字符
select * t_student2 where name like 'l%';
例：
4 范围查询
·in表示在一个非连续的范围内
例：查询编号是1或3或8的学生
·between ... and ...表示在一个连续的范围内
·例：查询学生是3至8的学生
5 空判断
·注意：null与''是不同的
·判空is null·
查询没有填写地址的学生
6 优先级
·小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用
7 聚合
·为了快速得到统计数据，提供了5个聚合函数
·1）count(*)表示计算总行数，括号中写星与列名，结果是相同的
如果所在列有null那么count（列名）比总行数小
2）max(列)表示求此列的最大值
3）·min(列)表示求此列的最小值
4）·sum(列)表示求此列的和
5）·avg(列)表示求此列的平均值
8 分组
select
from
where
group by
having
limit
·按照字段分组，表示此字段相同的数据会被放到一个组中
·分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中
·可以对分组后的数据进行统计，做聚合运算
1）·语法：
select列1,列2,聚合... from表名group by列1,列2,列3...
2）分组后的数据筛选
·语法：
select列1,列2,聚合... from表名
group by列1,列2,列3...
having列1,...聚合...
·having后面的条件运算符与where的相同
查询男生总人数
对比where与having
·where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
·having是对group by的结果进行筛选
9 排序
·为了方便查看数据，可以对数据进行排序
·语法：
select * from表名
order by列1 asc|desc,列2 asc|desc,...
选择为被删除的男生，按倒叙排列
10 分页
·当数据量过大时，在一页中查看数据是一件非常麻烦的事情
·语法
select * from表名
limit start,count
·从start开始，获取count条数据
·start索引从0开始
示例：分页
·已知：每页显示m条数据，当前显示第n页
·求总页数：此段逻辑后面会在python中实现
o查询总条数p1
o使用p1除以m得到p2
o如果整除则p2为总数页
o如果不整除则p2+1为总页数
·求第n页的数据
select * from students
where isdelete=0
limit (n-1)*m,m
例如：如果要求第5页的数据，每页数据为10
则 limit 4*10，10
六 高级
1 关系
创建成绩表scores，结构如下
id
学生
科目
成绩
思考：学生列应该存什么信息呢？
答：学生列的数据不是在这里新建的，而应该从学生表引用过来，关系也是一条数据；根据范式要求应该存储学生的编号，而不是学生的姓名等其它信息，同理，科目表也是关系列，引用科目表中的数据。
外键
·思考：怎么保证关系列数据的有效性呢？任何整数都可以吗？
·答：必须是学生表中id列存在的数据，可以通过外键约束进行数据的有效性验证
·为stuid添加外键约束
alter table scores add constraint stu_sco foreign key(stuid) references students(id);
·此时插入或者修改数据时，如果stuid的值在students表中不存在则会报错
·在创建表时可以直接创建约束
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);
外键的级联操作
·在删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常
·推荐使用逻辑删除，还可以解决这个问题
·可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作
·语法
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;
·级联操作的类型包括：
Ørestrict（限制）：默认值，抛异常
Øcascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
Øset null：将外键设置为空
Øno action：什么都不做
2 连接查询
·问：查询每个学生每个科目的分数
·分析：学生姓名来源于students表，科目名称来源于subjects，分数来源于scores表，怎么将3个表放到一起查询，并将结果显示在同一个结果集中呢？
·答：当查询结果来源于多张表时，需要使用连接查询
·关键：找到表间的关系，当前的关系是
Østudents表的id---scores表的stuid
Øsubjects表的id---scores表的subid
·则上面问题的答案是：
select students.sname,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;
·结论：当需要对有关系的多张表进行查询时，需要使用连接join
·连接查询分类如下：
Ø表A inner join表B：表A与表B匹配的行会出现在结果中
Ø表A left join表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
Ø表A right join表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充
·在查询或条件中推荐使用“表名.列名”的语法
·如果多个表中列名不重复可以省略“表名.”部分
·如果表的名称太长，可以在表名后面使用' as简写名'或'简写名'，为表起个临时的简写名称
3 自关联
·设计省信息的表结构provinces
Øid
Øptitle
·设计市信息的表结构citys
Øid
Øctitle
Øproid
·citys表的proid表示城市所属的省，对应着provinces表的id值
·问题：能不能将两个表合成一张表呢？
·思考：观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的
·意义：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大
·答案：定义表areas，结构如下
Øid
Øatitle
Øpid
·因为省没有所属的省份，所以可以填写为null
·城市所属的省份pid，填写省所对应的编号id
·这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
·在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息
·创建areas表的语句如下：
create table areas(
id int primary key,
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);
·从sql文件中导入数据
source areas.sql;
·查询一共有多少个省
·查询省的名称为“山西省”的所有城市
select city.* from areas as city
inner join areas as province on city.pid=province.id
where province.atitle='山西省';
·查询市的名称为“广州市”的所有区县
select dis.*,dis2.* from areas as dis
inner join areas as city on city.id=dis.pid
left join areas as dis2 on dis.id=dis2.pid
where city.atitle='广州市';
4 子查询
·查询支持嵌套使用
·查询各学生的语文、数学、英语的成绩
select sname,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='语文' and stuid=stu.id) as语文,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='数学' and stuid=stu.id) as数学,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='英语' and stuid=stu.id) as英语
from students stu;
2 内置函数
1 字符串函数
·查看字符的ascii码值ascii(str)，str是空串时返回0
查看ascii码值对应的字符char(数字)
·拼接字符串concat(str1,str2...)
select concat(12,34,'ab');
·包含字符个数length(str)
select length('abc');
·截取字符串
Øleft(str,len)返回字符串str的左端len个字符
Øright(str,len)返回字符串str的右端len个字符
Øsubstring(str,pos,len)返回字符串str的位置pos起len个字符
select substring('abc123',2,3);
·去除空格
Øltrim(str)返回删除了左空格的字符串str
Ørtrim(str)返回删除了右空格的字符串str
Øtrim([方向remstr from str)返回从某侧删除remstr后的字符串str，方向词包括both、leading、trailing，表示两侧、左、右
select trim(' bar ');
select trim(leading 'x' FROM 'xxxbarxxx');
select trim(both 'x' FROM 'xxxbarxxx');
select trim(trailing 'x' FROM 'xxxbarxxx');
·返回由n个空格字符组成的一个字符串space(n)
select space(10);
·替换字符串replace(str,from_str,to_str)
select replace('abc123','123','def');
·大小写转换，函数如下
Ølower(str)
Øupper(str)
select lower('aBcD');
select upper('aBcD');
2数学函数
·求绝对值abs(n)
select abs(-32);
·求m除以n的余数mod(m,n)，同运算符%
select mod(10,3);
select 10%3;
·地板floor(n)，表示不大于n的最大整数
select floor(2.3);
·天花板ceiling(n)，表示不小于n的最大整数
select ceiling(2.3);
·求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0
select round(1.6);
·求x的y次幂pow(x,y)
select pow(2,3);
·获取圆周率PI()
select PI();
·随机数rand()，值为0-1.0的浮点数
select rand();
·还有其它很多三角函数，使用时可以查询文档
网友评论