3MYSQL基础知识总结.docx
- 文档编号:10866766
- 上传时间:2023-02-23
- 格式:DOCX
- 页数:45
- 大小:1.71MB
3MYSQL基础知识总结.docx
《3MYSQL基础知识总结.docx》由会员分享,可在线阅读,更多相关《3MYSQL基础知识总结.docx(45页珍藏版)》请在冰豆网上搜索。
3MYSQL基础知识总结
一、数据库基础:
SQL语句分为三个部分:
DDL:
数据库定义语言,维护数据库结构的语言,例如create,drop,alter
DML:
数据库操作语言,维护数据的语言,增删改查:
insert,delete,update,select
DCL:
数据库控制语言,负责权限的管理(用户),grant,revoke等
*SQL是关系型数据库的操作指令,是一种约束,但不强制(类似W3C),不同的数据库产品,如Oracle,mysql,内部可能会有一些细微的区别
二、数据库设计准则
1.首先找到设计需求中的实体部分,一个实体建一张表,比如学生、老师、班级这些
2.找出实体表中应当存在的信息,比如年龄,性别,班级编号等。
3.实体表与实体表之间应当根据范式的要求使用外键关联起来。
4.在实际创建表的过程中,很少使用真实的业务数据作为主键字段(业务主键,如学号,课程号)大部分的时候是使用逻辑性字段(字段没有业务含义,值是什么都没有关系),将这种字段主键称之为逻辑主键:
主键只是为了保证数据的唯一性,所以通常把一个逻辑性字段设置为主键,并使其自增长
关系:
1.一对一关系:
一张表中的一条记录只能与另一张表的一条记录进行对应,反之亦然
解决方案:
通常将常用的和不常用的信息加以分离存储,分成两张表,然后用一个统一的字段(例如主键)进行联系
2.一对多关系:
一张表中的一条记录对应另一张表中的多条记录,反过来另一张表中的一条记录只能对应第一张表的一条记录
解决方案:
在A表中增加一个字段,能够找到B表中的记录,应该在子表中增加一个字段来指向父表,因为一张子表只能匹配一个父表
3.多对多关系:
一张表中(A)的一条记录能够与另一张表(B)中的多条记录对应,同时B表中的一条记录也能够对应A表中的多条记录
解决方案:
增加一张新表,专门维护A与B的对应关系
范式
范式:
是一种分层结构的规范,分为六层,每一层都比上一层更加严格:
若要满足下一层范式,前提是满足上一层范式
范式一:
INF
第一范式要求字段的数据具有原子性(不可再分),拿来用时不需要再次拆分
范式二:
2NF
范式二为了解决表中的部分依赖关系。
只要不存在复合主键,就不存在复合依赖,就符合第二范式
解决方案:
1.可以将性别与讲师单独成表,班级与教师也单独成表;2.取消复合主键,使用逻辑主键:
范式三:
3NF
第三范式解决传递依赖的问题,即一张表中的所有字段都应该直接依赖主键(逻辑主键,代表的是业务主键),而不是依赖某个非主键字段,进而出现传递依赖的现象
以上设计方案中满足第一第二范式,但是性别依赖讲师存在,而讲师依赖主键;教师依赖班级,而班级依赖主键:
此时性别和教师都存在传递依赖
解决方案:
将存在传递依赖的字段,以及依赖的字段本身单独取出,形成一个单独的表,然后在需要对应的信息的时候,使用对应的实体表的主键加进来
*注意:
讲师表中,ID=讲师;班级表中,ID=班级
*理论上来讲我们不看任何逻辑主键,因为逻辑主键并不存在任何实际的意义
三、数据库基本操作
1.连接数据库:
mysql.exe-hlocalhost-P3306-uroot-p;
2.选择、进入数据库:
use数据库名;
3.断开连接:
\q
四、库操作
*库操作,都会创建/修改/删除data目录下相应的文件夹
*每个数据库下都有一个opt文件:
其中保存了库选项
增:
createdatabase数据库名[库选项];
库选项:
字符集设定:
charset/characterset具体的字符集
校对集设定:
collate具体的校对集
查:
showdatabases;
查看指定数据库——模糊查询:
showdatabaseslike‘数据库名’;
%:
匹配多个字符
_:
匹配一个字符
查询指定数据库创建时使用的SQL语句:
showcreatedatabases`数据库名字`;
改:
alterdatabase数据库名字库选项;--数据库名不能更改,只能更改库选项
删:
dropdatabase数据库名;
五、表操作
*所有对表的操作,都会创建/修改/删除相应的库文件中的表文件
增:
createtable[ifnotexists][数据库名.]表名(字段名数据类型(占用字节数)/列类型(占用字节数),字段名数据类型/列类型)[表选项];
表选项:
设置字符集:
charset/characterset具体的字符集
设置校对集:
collate具体的校对集
指定存储引擎:
engine具体的存储引擎(innodb和myisam)
ifnotexists:
只有表不存在才会创建
查:
showtables;
查看部分表——模糊匹配:
showtableslike‘表名’;
查看指定表创建时使用的语法:
showcreatetable表名;
\G参数:
将查询结果旋转90度变成纵向
查看表结构(字段信息):
desc/describe/showcolumnsfrom表名;
改:
修改表名:
renametable老表明to新表名;
修改表选项:
字符集、校对集、搜索引擎
altertable表名表选项[=]值;
新增字段:
altertable表名add[column]字段名数据类型[列属性][位置];
位置:
字段名可以存放在表中的任意位置
frist:
第一个位置
after:
在哪个字段之后:
after字段名;默认为最后一个字段后
修改字段属性:
altertable表名modify字段名[属性][位置];
修改字段名:
altertable表名旧字段名新字段名数据类型[属性][位置];
删:
删除字段:
altertable表名drop字段名;
删除数据表:
droptable表名1,表名2….;
六、数据操作
增:
insertinto[库名.]表名values(值列表1,值列表2….),[(值列表1,值列表2….)];
查:
select*/字段列表from表名[where条件];
改:
update表名set字段=值[where条件];--如不使用where,则更新所有数据
删:
deletefrom表名[where条件];
七、列类型
*所谓的数据类型:
对数据进行统一的分类(从人的角度考虑为了数据统计),但是从系统的角度出发,是为了能够使用统一的方式进行管理:
更好的利用有限的空间
*SQL中将数据分为三大类:
数值类型,字符串类型,时间类型。
整数型:
tinyint:
迷你整型,使用一个字节(8位)存储,表示的状态最多为256种(常用)
smallint:
小整型,使用2个字节存储,表示的状态最多为65536种
mediumint:
中整型,使用3个字节存储
int:
标准整型,使用4个字节存储(常用)
bigint:
大整型,使用8个字节存储
小数型:
小数型分为浮点数,和定点数,浮点数精度会丢失,而定点数不会
*浮点型数据的插入:
整形部分是不能超出长度的,但是小数部分可以超出长度(系统会自动四舍五入),浮点数如果是因为系统进位导致整数部分超出指定的长度,那么系统也允许成立
创建单精度浮点:
精度范围在7位左右
createtablemy_float(
f1double,----表示没有小数部分
f2float(10,2),----10表示该数字的总长度(小数部分+整数部分),2表示小数部分的长度,整数部分为10–2=8
)charsetutf8;
定点型:
绝对保证整数部分不会被四舍五入(不会丢失精度)小数部分有可能(理论上小数部分也不会丢失精度)丢失精度
插入:
定点数的整数部分一定不能超出长度(进位不可以),小数部分的长度可以随意超出(因为系统自动四舍五入)
创建定点数:
createtablemy_decimal(
f1float(10,2),
f2decimal(10,2)-----定点数,精度不会丢失
)charsetutf8;
日期类型:
datetime:
时间日期,格式YYYY-MM-DDHH:
ii:
ss
date:
YYYY-MM-DD
time:
时间段,某个时间区间之间,表示过去的某个时间到将来的某个时间的区间
timestamp:
时间戳
year:
年份,YYYY,两种形式,year
(2)和year(4):
1901-2156
字符串类型:
*字符串类型的长度是以【字符】为单位的,而数字类型是以【字节】为单位的
定长字符串char:
在定义时就已经确定了最终存储的数据长度有多少个字符
变长字符串varchar:
在分配空间的时候,按照最大空间分配,但是最终用了多少是根据具体数据来定的
*如果数据量非常大,通常说超过255个字符,就会使用文本字符串text
blob类型:
用于存储二进制数据,例如图片
枚举:
enum,事先将所有可能出现的结果都设计好,实际上存储的数据必须是规定好的数据中的一个:
createtablemy_enum(
genderenum(‘男’,’女’,’保密’)
)charsetutf8;
插入数据:
insertintomy_enumvalues(‘男’),(‘女’),(‘保密’);
*枚举实际存储的是数值,而不是字符串本身!
集合:
与枚举类似,但是集合可以使用多个列表中的元素,用逗号分隔
createtablemy_set(
hobbyset(‘篮球’,’足球’,’乒乓球’,’棒球’)
)charsetutf8;
插入数据:
insertintomy_set(‘篮球’,’足球’);
*集合将会对插入的数据,使用逗号,进行分隔
*集合中,每一个元素都是对应一个二进制的位!
被选中为1,没有被选中为0
*集合中元素的顺序没有关系:
最终系统都会去匹配顺序
MYSQL中规定:
任何一条记录最长不能超过65535个字节.(varchar永远达不到理论值)
varchar实际存储长度能达到多少?
看字符集编码.
utf8下varchar的实际顶配:
21844字符
GBK写的varchar的实际顶配:
32766字符
八、列属性
定义时,列属性写在列类型之后,额外的约束字段
空属性:
NULL:
字段允许为空
NOTNULL:
字段不允许为空
列描述:
comment:
列的注释
默认值:
default列的默认值
主键:
primarykey,约束字段具有唯一性,一张表中只能有一个主键,想要使用多个主键可以使用复合主键
新增主键:
createtablemy_pril(
namevarchar(20)notnullcomment‘姓名’,
numberchar(10)primarykeycomment‘主键,学号:
不能重复’
)charsetutf8;
复合主键:
createtablemy_pril2(
numberchar(10)comment‘学号’,
coursechar(10)comment‘课程代码’,
scoretinyintunsigneddefault60comment‘成绩’,
primarykey(number,course)-----复合主键,number和course都要具备
唯一性
)charsetutf8;
对创建好的表追加主键:
1、altertablemy_pri3modifycoursechar(10)primarykey;
2、altertablemy_pri3addprimarykey(course);
更新&删除主键:
altertable表名dropprimarykey
自动增长:
对应的字段每当给值(包括NULL)都会被系统触发自增长,为当前字段以后的最大值进行+1操作,得到一个不新的不同的字段,自动增长通常和主键一起使用
自增长特点:
auto_increment
1.任何一个字段要做自增长,前提必须是其本身是一个索引(key—栏有值)
2.自增长字段必须是数字(整型)
3.一张表最多只能有一个自增长
4.如果对应的字段输入了值,那么自增长失效,但是下一次还是能够正确的自增长(从最大值+1)
5.查看自增长对应的变量:
showvariableslike‘auto_increment%’;
修改自增长值:
altertable表名auto_increment=值;
删除自增长:
altertable表名modify字段类型;
唯一键:
约束某一键在表中必须是唯一的,唯一键与主键的区别在于,唯一键允许其值为空,并可以有多个
创建唯一键:
1、createtablemy_unique(
idint
(2)primarykeyauto_incrementcomment‘主键,唯一,自增长,不允许为空’,
numberchar(10)uniquecomment‘学号,唯一,允许为空,
agechar(10)uniquecomment‘年龄,唯一,允许为空’
)charsetutf8;
2、createtablemy_unique(
numberchar(10)notnullcomment‘学号’,
namevarchar(20)notnull,
uniquekey(number)
)charsetutf8;
3、altertablemy_uniqueadduniquekey(number);
删除&更新唯一键:
altertable表名dropindex索引名字;---------唯一键默认使用其字段名为索引名字
九、数据高级操作
新增数据
主键冲突:
在插入数据时,如果插入的主键值已经存在,则会进行指定操作
冲突时更新:
更新冲突的主键为指定值,并把数据插入修改主键值后的字段中
insertinto表名[(字段列表:
包含主键)]values(值列表)onduplicatekeyupdate主键字段名=修改后的主键字段名;
冲突时替换:
冲突时,替换该主键所在列的对应字段的值
replaceinto表名values(值列表);
蠕虫复制:
从已有的数据中获取数据,然后将数据进行新增操作,使数据成倍的增加只复制表结构:
createtable新建的用来保存复制来的表结构的表名like需要复制的目标表名;
蠕虫复制:
查出目标表的数据,然后插入指定表中。
insertinto需要插入数据的表名select字段列表from需要复制的表名;
更新数据(限制记录)
语法:
update表名set字段=值[where条件][limit更新的数量];
*以上limit字段表示强制限制受更新操作影响的行数
删除数据(限制)
语法:
deletefrom表名[where条件][limit数量];
先删除指定表,然后新增该表:
truncate表名;
查询数据:
完整语法:
select[select选项]字段列表[字段别名]/*from数据源[where条件子句][groupby子句][orderby子句][limit子句];
select选项:
对查询结果进行处理
all:
默认的,保留所有结果
distinct:
去重,去除重复的数据
字段别名:
对查询出来的字段进行临时的重命名
语法:
字段名[as]别名;
数据源:
单表数据源:
select*from表名;
多表数据源:
select*from表名1,表名2….;
子查询:
数据的来源,是子查询中的结果
select*from(select语句)as别名;
where子句:
where子句返回的结果:
0或者1,0代表false,1代表true
判断条件:
比较运算符:
>,<,>=,<=,!
=,<>,=,like,betweenand,in/notin
逻辑运算符:
&&(and),||(or),!
(not)
where原理:
where是唯一一个直接从磁盘回取数据的时候就开始判断的条件:
从磁盘取出一条记录,开始进行where判断:
判断的结果如果成立保存到内存,如果失败直接放弃
*between本身是闭区间,between左边的值必须小于或者等于右边的值
groupby子句:
分组,根据指定字段对结果集进行分组
语法:
groupby字段名[,字段名2…][asc|desc];
相关函数:
count():
统计分组后的记录数:
每一组有多少记录
max():
统计分组后,每组中的最大值
min():
统计最小值
avg():
统计平均值
sum():
统计和
having子句:
与where一样的功能,但是where是针对磁盘数据进行判断的,如果磁盘数据进入内存后,再查询结果就需要使用having子句
having能够使用字段别名,而where不能,因为别名是在字段进入内存后才会产生的
orderby子句:
排序,根据某个字段升序或降序排序,依赖校对集
语法:
orderby字段名[,字段名2…][asc|desc];
limit子句:
限定查询结果的数量
语法:
limit数据量;limit起始位置,长度;
十、连接查询
交叉连接:
把左表(join关键字左边的表)与右表的数据进行一个N*M的组合显示,即显示结果是一个笛卡尔积。
select字段列表from左表名crossjoin右表名;
内连接:
根据对左表的指定条件,来匹配右表,其返回的结果是左表与B表的交集
select字段列表from左表名innerjoin右表名on匹配条件;
select*fromAinnerjoinBonA.id=B.id;
*由于连接查询返回的值可能存在同名问题(两张表中有相同的字段名),所以返回的数组值如果交由PHP处理就不好办了,所以我们通常将连接查询,与别名结合使用:
**注意:
如果使用了表别名,那么整个SQL语句中,被定义别名表名都要写成别名的方式,否则就会因找不到表而报错!
注意看上图中的s.*注意体会s表别名
外链接:
取出主表中的记录,拿子表中的每一条数据与主表中的记录进行对比,无论是否匹配都会存放在结果集中显示
左链接:
以左表为主
select*from左表leftjoin右表[on做表.字段表达式右表.字段];
右链接:
以右表为主
select*from左表rightjoin右表[on左表.字段表达式右表.字段]:
十一、外键
一张表中的一个字段指向另一张表的[主键],称之为外键.
存储engine必须是innodb,且外键需要指向另一张表的主键,才会具备约束效果
创建外键之后,为子表(外键所在表)的数据进行增、改时,如果对应的外键字段在父表中找不到对应的匹配,那么操作会失败!
例如插入数据时必须指定插入的数据所引用的外键,或者说,必须为指明这条数据对应父表中引用字段的哪条记录!
同时父表(被子表引用的表)对数据进行删、改操作时,如果对应的主键在子表中已经被数据所引用,那么操作就会失败。
新增外键:
创建表时增加:
….foreignkey(外键字段名)references外部表名(外表主键名);
追加创建:
….altertable表名addconstraint外键名foreginkey(外键字段)references外表名(主键字段);
删除&修改外键:
altertable表名dropforeignkey外键名;
外键约束:
之前所讲的外键作用:
是默认的作用;其实可以通过对外键的需求进行定制操作
外键约束有三种约束模式:
都是针对父表的约束
1.district:
严格模式(默认的),父表不能删除或者更新一个已经被子表数据引用的记录
2.cascade:
级联模式:
父表的操作对应子表关联的数据也跟着操作
3.setnull:
置空模式:
父表的操作之后,子表对应的数据(外键字段)被置空
语法格式:
下例表示创建的外键模式为删除置空,更新级联
十二、联合查询
将多次查询(多条select语句)的结果集进行拼接(字段不会增加)
基本语法:
select语句1union[union选项]select语句2……;
union选项:
all保留所有(不过滤重复)
distinct去重(去除所有重复)默认的
联合查询orderby:
男生按照年龄升序排序,女生按照年龄降序排序
(select*frommyclasswheresex=’男’orderbyageasc)union(select*frommyclass2wheresex=’女’orderbyagedesc);
*联合查询要求查询的字段数量一致
十三、子查询
在某一个查询结果之后,进行的查询,称之为子查询(一条select语句包含另一条select语句)
标量子查询:
得到的结果是一行一列
例如,知道班级名为php100,想获取该班的所有学生
select*frommy_studentwhereid=(selectidfrommyclasswherec_name=’php100’);
上例语句中,首先会执行()内的select语句,查询出所有在myclass表中,c_name=‘php100’的记录,然后再在my_student表中查询出所有id等于()内的select语句查询结果的记录
列子查询:
得到的结果是一列多行
例如,查询出所有在读班级的学生(班级中存在的班级)
select*frommy_studentwherec_idin(selectidfrommy_class);
上例语句中,首先查询出my_class表中所有的id字段对应的数据,然后再在其中查询出,与my_student表中的c_id字段相等的id字段的列
还可以使用c_id=any;c_id=some;c_id=all();来表示肯定,也可以使用来c_id!
=any;c_id!
=some;c_id!
=all();表示
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MYSQL 基础知识 总结