MySQL基础
数据库概述
非关系型数据库
-
键值型数据库:Key-Value。使用场景是作为缓存,如Redis
-
文档型数据库:可以是xml或json等格式,像是特别的键值型数据库,但值是文档,如MongoDB;
-
搜索引擎数据库:弥补了关系型数据库使用索引但针对全文索引效率却较低的情况。核心原理是“倒排索引”,如:Solr,Elasticsearch;
-
列式数据库(大数据):相较于Oracle、MySQL等行式存储,可以大量降低系统的I/O,适用于分布式文件系统,不足在于功能相对有限,如:HBase;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xEwQ9eW2-1659000720284)(day05.assets/image-20220614130116006.png)]
降低I/O的逻辑:如需要查询RowID和Material两个属性的数据,而非按记录作为单位。在行式存储中则需要进行等于数据库字段数的查询;而在列式存储中则只需要进行[1,n]次查询(n为数据表字段数)
NoSQL
非关系型数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nJrUrM5I-1659000720285)(day05.assets/image-20220614131021807.png)]
对SQL一个很好的补充,性能更高,成本更低。比如:日志收集、排行榜、定时器等。
ORM
1table<———>1javabean
1record1<———>objectofJava
1fieldor1column<———>1attributeofJava
SQL语句
分类
DDL(数据定义语言)
CREATE、DROP、ALTER
DML(数据操作语言)
INSERT、DELETE、UPDATE、SELECT(DQL)
DCL(数据控制语言)
GRANT、REVOKE、COMMIT、ROLLBACK(TCL)、SAVEPOINT
编写顺序与执行过程
编写顺序
1select
2from表(或结果集)
3where…
4groupby…having…
5orderby…
6limitstart,count
–注:1、2属于最基本语句,必须含有。
–注:1、2可以与3、4、5、6中任⼀搭配,也可以与3、4、5、6中多个同时搭配。
执行过程
1、from子句组装来自不同数据源的数据;
2、where子句基于指定的条件对记录行进行筛选;
3、groupby子句将数据划分为多个分组;
4、使用聚集函数进行计算;
5、使用having子句筛选分组;
6、计算所有的表达式;
7、select获得结果集;
8、使用orderby对结果集进行排序;
9、使用limitstart,length限制输出
比较运算符
LEAST(values…)\GREATEST(values…):查询数据中字段的最大\最小值
<spanstyle=“color:grey”>NOT BETWEEN…AND:查询<spanstyle=“color:grey”>不在开区间范围值的记录
正则函数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6ER74oQE-1659000720285)(day05.assets/image-20220614151118224.png)]
(1)‘^’匹配以该字符后面的字符开头的字符串。
(2)‘$’匹配以该字符前面的字符结尾的字符串。
(3)‘.’匹配任何一个单字符。
(4)“[…]”匹配在方括号内的任何字符。例如,“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围,使用一个‘-’。“[a-z]”匹配任何字母,而“[0-9]”匹配任何数字。
(5)‘\’匹配零个或多个在它前面的字符。例如,“x\”匹配任何数量的‘x’字符,“[0-9]\”匹配任何数量的数字,而“”匹配任何数量的任何字符。
细节
-
MySQL在windows环境下大小写不敏感;Linux环境下大小写敏感。
规范:MySQL关键字与函数用大写,其余表明字段名都用小写;
插入字符串数据与别名用单引号’',使用着重号``使与关键字重名的表名或字段名进行语义的区分;
-
NULL字段参与运算或判断结果均为NULL
用IFNULL(value1,value2),将value1为NULL的值替换成value2;(1)
-
比较运算符:
字符串和其他类型比较存在隐式转换为比较类型,如果转换不成功则默认为0;
用<=>来将NULL作为一般数值来判断。(2)
ISNULL和ISNOTNULL(3)
-
上述(1)(2)(3)三种方法为正确处理NULL数据的方法。
-
WHEREyear=2000OR1999OR1998<spanstyle=“color:red”>error
WHEREyear=2000ORyear=1999ORyear=1998⭐⭐⭐
WHEREyearIN(2000,1999,1998)⭐⭐⭐⭐⭐
-
WHERE和ON
对等查询的where和join速度基本一致,join略快;
不对等查询时一般join更慢(LEFTJOIN)。
IN、OR和UNIONALL
- 复杂查询可能会导致IN或OR不使用索引,这时候我们使用UNIONALL(UNIONALL不会区分重复数据进行合并);
- 只有非索引字段不使用UNIONALL,因为会成倍增加扫描次数;
- 没有索引字段,IN明显比OR快,因为IN会使用二叉树进行查找;
- 速度比较:between>in>or。
逻辑与位运算符
逻辑运算符
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VYn2Wlzc-1659000720286)(day05.assets/image-20220614170410360.png)]
位运算符
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-21jet9MK-1659000720286)(day05.assets/image-20220614170606440.png)]
排序和分页
-
ORDERBY在SELECT最后,GROUP_BY…HAVING后
-
ASC:升序
-
DESC:降序
<spanstyle=“color:red”>字段别名不能作为查询条件(列的别名只能在ORDERBY中使用不能在WHERE中使用),表的别名创建了必须使用。
-
-
LIMIT在SELECT最最后,ORDERBY后
LIMITint1,int2
int1:位置偏移量,开区间,不包括该数字,(PageNo-1)PageSize;
int2:行数,PageSize。
每一次的显示结果的记录范围为:(int1,int1+int2],即:
LIMIT(PageNo-1)PageSize,PageSize
MYSQL应用
FROM多表查询
自连接:
SELECTCONCAT(worker.last_name,'worksfor'
,manager.last_name)
FROMemployeesworker,employeesmanager
WHEREworker.manager_id=manager.employee_id;
内连接与外连接
-
内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
-
外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行,这种连接称为左(或右)外连接。没有匹配的行时,结果表中相应的列为空(NULL)。
- 如果是左外连接,则连接条件中左边的表也称为主表,右边的表称为从表。
- 如果是右外连接,则连接条件中右边的表也称为主表,左边的表称为从表。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sOWIKgR9-1659000720287)(day05.assets/image-20220615134942757.png)]
SQL99
<spanstyle=“color:blue”>自然连接:自动对两张表相同字段进行等值连接
SELECTemployee_id,last_name,department_name
FROMemployeeseJOINdepartmentsd
ONe.`department_id`=d.`department_id`
ANDe.`manager_id`=d.`manager_id`;
可以替换成
SELECTemployee_id,last_name,department_name
FROMemployeeseNATURALJOINdepartmentsd;
<spanstyle=“color:blue”>USING连接:指定数据表中同名字段进行等值连接,但是只能配合JOIN使用
SELECTemployee_id,last_name,department_name
FROMemployeese,departmentsd
WHEREe.department_id=d.department_id;
可以替换成
SELECTemployee_id,last_name,department_name
FROMemployeeseJOINdepartmentsd
USING(department_id);
INSERT数据插入
除了基本插入方式外,可以将查询结果插入表中:
INSERTINTO目标表名
(tar_column1[,tar_column2,…,tar_columnn])
SELECT
(src_column1[,src_column2,…,src_columnn])
FROM源表名
[WHEREcondition]
示例:
INSERTINTOemp2
SELECT
FROMemployees
WHEREdepartment_id=90;
细节与注意事项
阿里开发规范
-
表名,字段名必须小写字母或数字,不能数字开头,禁止下划线中间出现数字;
-
禁止使用保留字;
-
表必备三字段:id,gmt_create,gmt_modified
id:主键,类型为BIGINTUNSIGNED、单表自增、步长为1;
gmt_create:主动式更新日期;
gmt_modified:被动式更新。
-
对于数据库中表记录的查询和变更,只要涉及多个表,都需要在列名前加表的别名(或表名)进行限定;
-
超过三个表禁止join。需要join的字段,数据类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引;
-
根据记录特征的区分度,varchar字段必须指定长度;
-
TRUNCATETABLE比DELETE速度快,且使用的系统和事务日志资源少,但TRUNCATE无事务且不触发TRIGGER,有可能造成事故,不建议在开发代码中使用此语句。
注意事项
-
查询的非组函数字段必须声明在GROUPBY中;
GROUPBY中声明的字段可以不出现在组函数中;
过滤条件需要用HAVING代替WHERE,且使用HAVING前提是使用了GROUPBY。
细节
- TRUNCATETABLE
- 删除表中所有的数据;
- 释放表的存储空间;
- 不能回滚。
- 多行的INSERT语句在处理过程中效率更高,在插入多条记录时最好选择使用单条INSERT语句的方式插入。
约束
SQL以约束的方式对<spanstyle=“color:#842626”>表数据进行额外的条件限制
- 实体完整性(EntityIntegrity):例如,同一个表中,<spanstyle=“color:orange”>不能存在两条完全相同无法区分的记录;
- 域完整性(DomainIntegrity):例如:<spanstyle=“color:orange”>范围,年龄范围0-120,性别唯二“男/女”;
- 引用完整性(ReferentialIntegrity):例如:员工所在部门,在部门表中<spanstyle=“color:orange”>要能找到相关引用数据(这个部门);
- 用户自定义完整性(User-definedIntegrity):例如:<spanstyle=“color:orange”>用户名唯一、密码不能为空等,本部门经理的工资不得高于本部门职工的平均工资的5倍。
列级约束与表级约束
列级约束:只能作用在一个列上,跟在列的定义后面;
表级约束:可以作用在多个列上,不与列一起,而是单独定义。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zlXCOOgw-1659000720287)(day05.assets/image-20220618122522859.png)]
约束关键字
<spanstyle=“color:#842626”>NOTNULL非空约束,规定某个字段不能为空
<spanstyle=“color:#842626”>UNIQUE
唯一约束,规定某个字段在整个表中是唯一的,MySQL会给唯一约束的列上默认创建一个唯一索引。下面展示创建单一字段唯一约束与组合字段唯一约束
字段名字段类型UNIQUE约束名
#或
CONSTRAINT约束名UNIQUE(约束字段1,约束字段2...)
<spanstyle=“color:#842626”>PRIMARYKEY主键:非空且唯一约束(NOTNULLANDUNIQUE)
<spanstyle=“color:#842626”>AUTO_INCREMENT自增列
- 一个表最多只能有一个自增长列;
- 自增长列约束的列必须是键列(主键列,唯一键列);
- 如果自增列指定了0和null,会在当前最大值的基础上自增;如果自增列手动指定了具体值,直接赋值为具体值。
MySQL8.0-自增变量持久化:重启mysql服务会区别于MySQL5.7,MySQL8.0会保留重启前的自增列递增裂缝
<spanstyle=“color:#842626”>CHECK检查约束。MySQL7.0不支持,MySQL8.0中可以使用check约束了。
createtableemployee(
eidintprimarykey,
enamevarchar(5),
gendercharcheck('男'or'女')
);
<spanstyle=“color:#842626”>DEFAULT默认值
更改约束:
ALTERTABLEtablename
MODIFYfieldnamedatatype约束
外键约束
<spanstyle=“color:#842626”>FOREIGNKEY:根据表关系进行引用完整性的约束,要求子(从)表中对父(主)表记录的实际值的引用
创建表时添加外键约束
createtable从表名称(
字段1数据类型primarykey,
字段2数据类型,
[CONSTRAINT<外键约束名称>]FOREIGNKEY(从表的某个字段)
REFERENCES主表名(被参考字段)
);
#示例
createtableemp(#从表
eidintprimarykey,#员工编号
enamevarchar(5),#员工姓名
deptidint,#员工所在的部门
foreignkey(deptid)referencesdept(did)#在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致,意义都是表示部门的编号
);
创建表后添加外键约束
ALTERTABLE从表名
ADD[CONSTRAINT约束名]FOREIGNKEY(从表的字段)
REFERENCES主表名(被引用字段)[onupdatexx][ondeletexx];
#示例
ALTERTABLEempADDFOREIGNKEY(deptid)
REFERENCESdept(did);
约束等级
Noaction方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
Restrict方式:同noaction,都是立即检查外键约束
上为默认模式
Cascade方式:在父表上update/delete记录时,同步update/delete掉子表的匹配记录
Setnull方式:在父表上update/delete记录时,将子表上匹配记录的列设为null,但是要注意子表的外键列不能为notnull
Setdefault方式(在可视化工具SQLyog中可能显示空白):父表有变更时,子表将外键列设置成一个默认的值,但Innodb不能识别
createtableemp(
eidintPRIMARYKEY,#员工编号
enamevarchar(5),#员工姓名
deptidint,#员工所在的部门
FOREIGNKEY(deptid)REFERENCESdept(did)ONUPDATECASCADEONDELETESETNULL
#把修改操作设置为级联修改等级,把删除操作设置为setnull等级
);
对于外键约束,最好是采用:ONUPDATECASCADEONDELETERESTRICT的方式。
注意事项
- 必须引用/参考主表的主键或唯一约束的列;
- 在创建外键约束时,系统默认会在所在的列上建立对应的普通索引,但是索引名是外键的约束名。如果不给外键约束命名,默认名不是列名,而是自动产生一个外键名(例如student_ibfk_1;),也可以指定外键约束名;
- 被从表引用的子表记录不能被删除,删表时,先删从表(或先删除外键约束),再删除主表;
- 删除外键约束后,必须<spanstyle=“color:orange”>手动删除对应的索引。
- 外键约束是有成本的,需要消耗系统资源。对于大并发的SQL操作,有可能会不适合。比如大型网站的中央数据库,可能会因为外键约束的系统开销而变得非常慢。
阿里规范:【<spanstyle=“color:#842626”>强制】不得使用外键与级联,一切外键概念必须在应用层解决。
外键与级联更新适用于单机低并发,不适合分布式、高并发集群;
级联更新是强阻塞,存在数据库更新风暴的风险;
外键影响数据库的插入速度。
Oracle
函数
TopicName |
---|
字符串处理函数:UPPER(string),LOWER(string);CONCAT(string1,string2);TRIM(string),LTRIM(string),RTRIM(string);–截取空格LPAD(string,length,padstring)/RPAD(string,length,padstring);–左右填充INSTR(string1,substring,startposition,[occurrencenum]);–获取字串位置SUBSTR(string,startposition,length);–截取字串TRANSLATE(string,search_set,replace_set)—字符替换 |
数学函数:ROUND(num,length);–四舍五入MOD(num1,num2)求num1/num2余数;TRUNC(num,decimalplaces)保留位数 |
转换函数:[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BezoXluq-1659000720288)(day05.assets/image-20220728134132702.png)] |
空值处理函数:NVL(输入的值,为NULL替换返回的值);COALESCE(expression1,expression2,expression3,…)返回直到exprssion不为空的值。 |
条件函数:Decode(expression, [true, false, [true, false,[true,false]]])类似嵌套if-else; |
CASE [WHEN expression1 THEN return] [WHEN expression2 THEN return]…[WHEN expressionX THEN return] END |
集合函数:COUNT(列|表达式);MAX(列|表达式);MIN(列|表达式);SUM(列|表达式);AVG(列|表达式) |
TO_CHAR()/TO_DATE():字符串日期转换
符号 | 意义 |
---|---|
YY | 表示年份。以2位数字的形式显示。例如,03,09等 |
YYYY | 表示年份。以4位数字的形式显示 |
MM | 表示月份。以2位数字的形式显示 |
WW(weekbyyear) | 表示一年当中的星期数。显示范围1-53 |
W(weekbymonth) | 表示该月当中的星期数。显示范围1-5 |
DD | 编审日子。以2位数字的形式显示 |
YEAR | 表示年份。以英文的形式显示 |
MONTH | 表示月份。以英文的全拼的形式显示。例如,MARCH |
MON | 表示月份。以英文的缩写的形式显示。例如,MAR |
DAY(SUNDAY) | 表示工作日。以英文的全拼的形式显示。例如,SUNDAY |
DY(SUN) | 表示工作日。以英文的全拼的形式显示。例如,SUN |
HH12 | 表示时间。以12小时的形式显示。例如,下午4点表示为04 |
HH24 | 表示时间。以24小时的形式显示。显示范围0-24。例如,下午4点表示为14 |
MI(minute) | 表示分钟。显示范围0-59 |
SS(second) | 表示秒钟。显示范围0-59 |
Q(season) | 表示季度。显示范围1-4 |
CURRENT_DATE,SYSDATE
SELECTCURRENT_DATE,SYSDATEFROMDUAL;
--current_date返回的是当前会话时间,而sysdate返回的是服务器时间.
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J2QFeZ57-1659000720288)(day05.assets/image-20220728111713078.png)]
NEXT_DAY(date,‘某一天工作日字’)
SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期三')FROMDUAL;
EXTRACT(dateFROM[year,month,day])取得日期值中的指定内容
SELECT
EMP.HIREDATE,
EXTRACT(YEARFROMEMP.HIREDATE)
FROM
EMP;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F3nD1Ay7-1659000720289)(day05.assets/image-20220728133430193.png)]
TO_CHAR():转换数字
符号 | 意义 |
---|---|
9 | 表示数字。如果转换的数字值前面有0,则0被忽略 |
0 | 表示数字补0。如果转换的数字值的位数不足,则会强制补0 |
L | 表示本地货币符号。在转换后的数字值前加上一个本地货币符号 |
C | 表示国际货币符号。在转换后的数字值前加上一个国际货币符号 |
$ | 表示美元符号。在转换后的数字值前加上一个美元符号 |
D | 在转换后的数字值的指定位置显示小数点 |
G | 在转换后的数字值的指定位置显示分隔符 |
. | 表示小数点。在转换后的数字值的指定位置显示小数点 |
, | 表示千位分隔符。在转换后的数字值的指定位置显示分隔符 |
SELECT TRIM(TO_CHAR(EMP.SAL,'$999,999,999')) FROM EMP
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IpSrGiLu-1659000720289)(day05.assets/image-20220728141957481.png)]
和MySQL的区别(不断发现)
语法及使用
1.别名
首先两者均是:AS可以加也可以不加
“” | ‘’ | ` | 不写 | |
---|---|---|---|---|
MySQL | √ | √ | √ | √ |
Oracle | √ | × | × | √ |
2.GROUPBY和HAVING
mysql
-
select后边有几个字段,分组的时候写几个都行;
-
还可以用select后面没要查的字段进行分组,都可以查出数据不会报错;
-
having可以不结合groupby,单独使用。
oracle
-
select后边的字段,groupby的时候要全加上(除了函数的),否则就要报错;
-
更不能用select后边没有的字段来分组;
-
having一定要结合groupby使用。
SELECT
EMP.ENAME,
EMP.DEPTNO,
EMP.JOB,
EMP.HIREDATE,
NEXT_DAY(EMP.HIREDATE,'星期一')AS"入职时间",
SUM(EMP.SAL)
FROM
EMP
GROUPBY
EMP.ENAME,
EMP.DEPTNO,
EMP.JOB,
EMP.HIREDATE;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TRDP9Q2f-1659000720290)(day05.assets/image-20220728133643532.png)]
3.日期函数区别
MySQL
SELECT
STR_TO_DATE(job_history.start_date,'%Y-%m-%d')
FROM
job_history;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ganFoboj-1659000720290)(day05.assets/image-20220728132425105.png)]
Oracle
SELECT
TO_CHAR(EMP.HIREDATE,'yyyy-MM-dd'),
TO_DATE(TO_CHAR(EMP.HIREDATE,'yyyy-MM-dd'),'yyyy/mm/dd')
FROM
EMP;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c3RdEnLF-1659000720290)(day05.assets/image-20220728132505509.png)]
4.DUAL
DUAL表是一个单行单列的虚拟表
这个表只有1列DUMMY,最常见的一个简单的例子SELECTsysdateFROMdauldual是Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select语句块中。
调优
1.SQL语句大写
因为oracle总是先解析SQL语句,把小写的字母转换成大写的再执行。
2.表别名
多表join时,尽量使用表别名,同时把表别名前缀于每一列上,减少解析的时间及由列歧义引起的语法错误
3.from从右到左解析表名
from子句中写在最后的表(基础表也称为驱动表,driving table)将被最先处理,尽量记录条数最少的表作为基础表放最后,如维表
4.where自下向上解析
表之间的连接必须写在其他where条件之前, 那些可以过滤掉最大数量记录的条件必须写在where子句的末尾
5.select *
尽量避免使用*查询全部列,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
6.避免使用耗费资源的操作
带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作,而其他的至少需要执行两次排序。
7.union all和union
UNION两个集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序。如果用UNION ALL替代UNION, 这样排序就不是必要了。效率就会因此得到提高。注意UNION ALL将重复输出两个结果集合中相同记录。
8.用EXISTS替代IN、用NOT EXISTS替代 NOT IN
在基于基础表的查询中经常需要对另一个表进行联接。在这种情况下, 使用EXISTS(或NOTEXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的(要对子查询中的表执行了一个全表遍历)。所以尽量将NOT IN改写成外连接(Outer Joins)或NOT EXISTS。
9.用Where子句替换HAVING子句
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。最好能通过WHERE子句限制记录的数目。
10.用>=替代>
区别:前者DBMS将直接跳到第一个ID等于4的记录而后者将首先定位到ID=3的记录并且向前扫描到第一个ID大于3的记录。
11.建立索引
索引要点:
- 索引之后,按索引字段重复最少的来排序,会达到最优的效果。以我们的表来说,如果建立了No的聚集索引,把No放在where子句的第一位是最佳的,其次是Id,然后是MgrObjId,最后是时间,时间索引如果表是一个小时的,最好不要用
- where子句的顺序
- 决定了查询分析器是否使用索引来查询。比如建立了MgrObjId和Id的索引,那么
where MgrObjId='' and Id='' and Dtime=''
就会采用索引查找,而where Dtime='' and MgrObjId='' and Id=''
则不一定会采用索引查找。 - 把非索引列的结果列放在包含列中。因为我们条件是MgrObjId和Id以及Dtime,因此返回结果中只需包含Dtime和Value即可,因此把Dtime和Value放在包含列中,返回的索引结果就有这个值,不用再查物理表,可以达到最优的速度。