MySQL总结.docx
《MySQL总结.docx》由会员分享，可在线阅读，更多相关《MySQL总结.docx（21页珍藏版）》请在冰点文库上搜索。
MySQL总结
一、理论背景
不同角色的人对数据库知识学习的侧重点
•研发人员（测试及开发人员）：
主要学习如何操作数据库的逻辑对象，如表、索引和视图等，以得到应用程序中需要的数据。
•数据库管理员（DBA）：
要学习和掌握数据库的全部内容，包括数据库对象管理、性能优化、备份与恢复、安全管理等
软件测试在什么时候会用到数据库的知识
•搭建测试环境
•通用数据库操作功能的测试（增、删、改、查等）
•快速创建测试数据，提高测试工作效率
关系数据模型的基本数据结构
DDL和DML
SQL简介
•结构化查询语言（StructuredQueryLanguage）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库脚本文件的扩展名
•编写规范
1）sql语句的所有表名、字段名全部小写，系统保留字、内置函数名、sql保留字大写。
2）连接符or、in、and、以及＝、<=、>=等前后加上一个空格。
3）对较为复杂的sql语句、过程、函数加上注释，说明算法、功能。
4）多表连接时，使用表的别名来引用列
主要掌握查询语句
语言分类
名称
详细描述
1、数据查询语句（DQL）
select
数据查询
2、数据操纵语句（DML）
Insert
Update
Delete
插入
更新
删除
3、数据定义语句（DDL）
Create
Alter
Drop
Rename
Truncate
新建
修改结构
删除
重命名
删除
4、事务控制语句（TC）
Commit
Rollback
Savepoint
提交
事务回滚
存储点
5、数据控制语言（DCL）
Grant
Revoke
授权
解除授权
DDL与DML
DDL（数据定义语言）是用来定义数据结构，完成数据库对象的创建（CREATE）、修改（ALTER）、删除（DROP）
数据库对象包括：
数据库实例，数据表，视图，索引，约束，存储过程
DML（数据操纵语言）是用于维护数据表中的具体数据，完成数据的增加（INSERT）、更新（UPDATE）、删除（DELETE）
1、MySQL支持的数据类型
MySQL数据库支持的数据类型：
整型、浮点型、字符串、日期和时间
字符串数据类型
通常定义为varchar，输入字符串时必须加””或者’’
日期和时间数据类型
输入值时必须加’’
2、数据表约束
a.主键约束
b.外键约束
定义外键约束：
FOREIGNKEY指定test_table1表中的属性name为外键约束
REFERENCES关联父表test_table2中的主键属性n
ONDELETE、ONUPDATE指明父表中对主键进行删除、更新操作，不指定则表示不能进行删除、更新操作
CASCADE表明子表中外键字段值也会被更新或删除，除了CASCADE，还可以使用SETNULL（父表更新或删除时，子表字段值设置为NULL）、NOACTION（父表更新或删除时，子表不进行任何操作）
c.唯一约束
d.非空约束
NOTNULL
e.默认值约束default
CREATETABLEtest_table
（
idINTPRIMARYKEY,
nameVARCHAR（20）,
ageINTDEFAULT20
）;
f.AUTO_INCREMENT
当属性列定义为AUTO_INCREMENT时，能够为新插入的行赋值为上一次插入的值+1,MySQL要求AUTO_INCREMENT只能用于作为主键的列
CREATETABLEtest_table
（idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR（20）
）;
数据表中的属性可以同时定义多个约束，但是需要注意：
-如果同一个属性有默认值约束和其他约束，默认值要写在其他约束前面
-如果同一个属性有多个约束，中间使用空格分隔开
二、常用命令-database，table
1、数据库
createdatabasetest07#新建数据库test07
showDATABASES#查看已创建的数据库
usetest07#使用数据库
createdatabasenew07defaultcharactersetutf8COLLATEutf8_general_ci
createdatabasetesting07defaultcharactersetutf8
#新建数据库的时候，指定字符集
showcharacterset#查看mysql支持的字符集
showcollation#查看MySQL字符集支持的校对集命令
ALTERDATABASE数据库名称DEFAULTCHARACTER字符集COLLATE校对集
#使用alter命令来完成数据库的修改，一般是修改数据库的字符集和校对集
#删除数据库
语法：
DROPDATABASE数据库名称
例子：
DROPDATABASEtest_db
2、数据表
createTABLE表名#新建数据表
例：
usetest07
createTABLEstudent
（
idintprimarykey,
namevarchar（20）notnull,
ageint,
sexvarchar（10）,
xivarchar（20）,
gradeint
）
showtables#查看已存在的数据表
desc表名#查看数据表的属性
select*from表名[where条件子句]#查看表的内容
#新增属性
ALTERTABLE表名ADD属性名称数据类型[约束]
ALTERTABLEtest_tableADDnameVARCHAR（20）NOTNULL;
#修改属性数据类型，名称（change）
语法：
ALTERTABLE表名MODIFY属性名称数据类型[约束]
语法：
ALTERTABLE表名CHANGE属性名称属性名称数据类型[约束]
例子：
ALTERTABLEtest_tableMODIFYnameVARCHAR（20）;
ALTERTABLEtest_tableCHANGEnamenameVARCHAR（20）;
#新增属性约束
fk可加可不加
语法：
ALTERTABLE表名ADDCONSTRAINT约束别名约束（属性）
例：
ALTERTABLEtest_tableADDCONSTRAINTPRIMARYKEY（id）;
ALTERTABLEtest_tableADDCONSTRAINTfkFOREIGNKEYtest_table（name）REFERENCEStest_table2（name）
删除属性约束
语法：
ALTERTABLE表名DROP约束
例子：
ALTERTABLEtest_tableDROPPRIMARYKEY;
ALTERTABLEtest_table1DROPFOREIGNKEYfk;
#修改表名
语法：
ALTERTABLE表名RENAMETO新表名
语法：
RENAMETABLE原表名TO新表名
例子：
ALTERTABLEtest_tableRENAMETOtest_table_new
RENAMETABLEtest_tableTOtest_table_new
#删除数据表
语法：
DROPTABLE表名运行速度：
drop>truncate>delete
语法：
deletefrom表名
语法：
truncate表名
例子：
DROPTABLEtest_table
#数据插入
语法：
INSERTINTO表名（属性1,属性2,……）VALUES（值1,值2,……）
例子：
INSERTINTOtest_table（id,name）VALUES（1,'zhangsan'）
向数据表中插入数据时，值与属性要一一对应，且数据类型要匹配还需要注意属性的约束要满足。
当使用INSERT语句向表中插入数据时，如果不指定具体属性，则表示向所有属性中插入数据
#特殊用法：
将一张表中的数据插入到另外一张表
语法：
INSERTINTO表名1（属性1,….）SELECT表名2.属性1,….FROM表名2
例子：
INSERTINTOtest_table1SELECTid,nameFROMtest_table;
需要注意，在当前这种用法中，SELECT语句查询到的属性值和向表1中插入的属性要一一对应，且数据类型要相同，属性约束要满足
#数据更新
语法：
UPDATE表名SET属性1=新值1,属性2=新值2,….[WHERE条件子句]
例子：
UPDATEtest_tableSETname='lisi'WHEREid=1;
UPDATE语句会将数据表中满足WHERE条件的对应元组的属性1、属性2…更新为新的值，如果后面不接WHERE条件子句，则会更新整张表的所有数据
#数据删除
语法：
DELETEFROM表名[WHERE条件子句]
例子：
DELETEFROMtest_tableWHEREid=1;
DELETE语句会将数据表中满足WHERE条件的对应元组删除
如果后面不接WHERE条件子句，则会删除数据表中的所有数据
三、DQL查询语句-select
基本语法：
SELECT属性1,属性2,….FROM表名通配符*代表所有
使用SELECT语句进行数据查询时，还可以进行算术运算、限定查询、排序、分组
1、算术运算
比较运算：
in有时可以用or替代
%代表若干个字符，_代表一个字符，like‘%经_’
逻辑运算符：
！
后面括号不能省略
and后面还有逻辑运算符时，例如and（条件1or条件2）,注意加上（）,避免查询结果有重复的
运算符优先级：
数学运算符>比较运算符>逻辑运算符
注意：
在WHERE子句中
字符串和日期要用引号括起来
对字符串不区分大小写
日期值是格式敏感的
2、限定查询
限定结果-LIMIT
使用LIMIT子句可以限定查询返回的记录数
例如：
查询数据表中前两条记录
SQL语句：
SELECT*FROMhzdl_empLIMIT2;
例子：
SELECT*FROMhzdl_empLIMIT2,3
LIMIT子句后面可以有一个参数，也可以有两个参数，如果有一个参数，该参数表示从表开始位置查询对应的记录数量，如果有两个参数，那么第一个参数表示起始位置与第一行的偏移量，第二个参数表示查询的数量
去除重复-DISTINCT
使用DISTINCT关键字能够将查询到的结果集中重复的记录去除
例子：
SELECTdeptnoFROMhzdl_emp
SELECTDISTINCTdeptnoFROMhzdl_emp
设置别名-AS
使用AS关键字可以在查询时设置属性（列）的别名，不过AS可以省略
SELECTempnoAS'员工号',enameAS'员工姓名'FROMhzdl_emp;
SELECTempno'员工号',ename'员工姓名'FROMhzdl_emp;
引号无所谓加不加
3、排序
使用ORDERBY子句可以将SELECT查询到的结果集按照指定属性排序
默认采用升序（ASC）排序方式，也可以使用降序（DESC）
例子：
SELECT*FROMhzdl_empORDERBYdeptno;
SELECT*FROMhzdl_empORDERBYdeptnoASC;
SELECT*FROMhzdl_empORDERBYdeptnoDESC;
同样，排序时也可以指定多个属性排序
当多属性排序方式有冲突时，按照指定先后顺序排序
例子：
SELECT*FROMhzdl_empORDERBYdeptno,jobno;
SELECT*FROMhzdl_empORDERBYdeptno,jobnoDESC
4、分组-GROUPBY
一般和聚合函数一起使用。
常见聚合函数有：
-COUNT计数函数，统计表中某个属性对应的记录数
-AVG求平均值函数，统计表中某个属性对应的平均值
-SUM求和函数SUM，统计表中某个属性的汇总值（和）
-MAX最大值函数
-MIN最小值函数
-GROUP_CONCAT列值拼接函数，将表中某个属性的所有值拼接到同一个结果中，默认值与值之间用逗号分隔。
也可以使用SEPARATOR关键字指定分隔符。
只能拼接字符型，如果要拼接其他类型，需用cast（xastype）函数转换。
SELECTGROUP_CONCAT（jobnameSEPARATOR'+'）FROMhzdl_job
和分组GROUPBY子句经常一起使用的还有HAVING子句，和where子句类似，它们的区别是：
-WHERE子句在查询前限定查询条件
-HAVING子句在分组后进行进一步限定
例子：
SELECTdeptno,COUNT（empno）FROMhzdl_empGROUPBYdeptnoHAVINGCOUNT（ename）>2
分组同样可以基于多个属性
例如：
查询公司内各个部门不同职位的员工人数
SQL语句：
SELECTdeptno,jobno,COUNT（empno）FROMhzdl_empGROUPBYdeptno,jobno;
在MySQL数据库中使用分组时，SELECT语句后查询的属性可以不出现在GROUPBY子句中,但是在其他类型的数据库中，SELECT语句后只能使用聚合函数和GROUPBY子句后出现的属性。
最好select后都跟上，便于理解。
单表查询语句从以下几个方面着手：
1．在那个表；2.查询的属性；3.限制条件
练习1：
参考案例2-DQL语句1
1、查询hzdl_emp表中所有员工的员工姓名
2、查询hzdl_job表中年薪大于4W的职位
3、查询hzdl_emp表中姓张的员工信息
提示：
员工姓名中含有“张”这个字
4、查询hzdl_emp表中财务部员工的人数
5、查询hzdl_emp表中同一天入职的员工人数
6、查询hzdl_emp表中最近刚入职的员工姓名
7、查询hzdl_emp表中2016年6月前入职的员工姓名
5、单行函数
字符串函数
日期时间函数
数学函数
•在MySQL中，round函数用于数据的四舍五入，它有两种形式：
•1、round（x,d） ，x指要处理的数，d是指保留几位小数
•这里有个值得注意的地方是，d可以是负数，正数和0.
•2、round（x） ,其实就是round（x,0）,也就是默认d为0；
round,d）
d为正数，例如3，round,3）=
d为负数，例如-3，round,-3）=1000
d为负数时，直接在整数部分取整，从第d位往d+1位入
类型转换函数
需要注意的是可以转换的数据类型是有限制的：
-整型（signed）、无符号整型（unsigned）、浮点型（decimal）、字符型（char）、日期（date）、时间（time）、日期时间型（datetime）
控制流函数
1）在设计表的时候，如果发现这列是需要运算的列，建议设置默认值，比如0；
2）在整型列的运算的时候，不管是加，减，乘，除等。
都需要使用ifnull，对列进行处理。
否则报表数据就会错。
例子：
#查询各个职位的年薪，如果是董事长，按15薪，总经理，13薪。
selectjobname,round
（CASE
whenjobname='董事长'then（sal+comm）*15/10000
whenjobname='总经理'then（sal+comm）*13/10000
else（sal+comm）*12/10000
end）fromhzdl_jobgroupbyjobnameorderbyjobno
方法2：
selectjobname,IF（jobname='董事长',（sal+comm）*15,IF（jobname='总经理’,（sal+comm）*13,（sal+comm）*12））fromhzdl_job
四、多表查询
多表查询分为两类：
内连接，外连接
内连接
概念：
进行多表查询时，按照匹配条件，在结果集中只保留各个表中满足匹配条件的记录，又叫等值连接。
#查询工作地点为成都的员工姓名
#1.查询的表：
hzdl_emp,hzdl_dept
#2.查询的属性：
#3.表的关联属性：
限制条件：
loc='成都'
select,fromhzdl_emp,hzdl_deptwhere=andloc='成都'
select,fromhzdl_empinnerjoinhzdl_depton=whereloc='成都'
#别名
select,fromhzdl_empa,hzdl_deptbwhere=and='成都'
select,fromhzdl_empainnerjoinhzdl_deptbon=where='成都'
外连接
概念：
进行多表查询时，分为主、从表，在结果集中保留主表中的所有记录以及从表中满足匹配条件的记录。
分为左外连接，右外连接
子查询
将一个SELECT语句A使用（嵌套）于另外一个SELECT语句B中，则A即为B中的子查询
使用子查询注意事项：
子查询一定要使用括号括起来
子查询放在比较运算符的右边
子查询中不要加ORDERBY子句
对单行子查询使用单行运算符
对多行子查询使用多行运算符
单行子查询
单行运算符：
多行运算符：
多列子查询
概念：
即子查询语句返回多行多列
多行比较运算符：
IN
例子：
#查询入职日期大于2016-6-1的所有员工姓名
SELECTename，deptnoFROMhzdl_empWHERE（deptno,hiredate）IN（SELECTdeptno,hiredateFROMhzdl_empWHEREhiredate>’2016-6-1’）;
合并结果集
在MySQL中使用UNION语句可以将多个SELECT语句的结果集合并消除表中任何重复行。
不消重，使用unionall。
使用union的要求：
1、多个SELECT语句中的列名（别名）可以不一样，UNION结果集中的列名是等于第一个SELECT语句中的列名
2、多个SELECT语句必须拥有相同数量的列
3、多个SELECT语句列必须拥有相似的数据类型
4、多个SELECT语句列的顺序必须相同
使用UNION进行合并时，如果SELECT语句中使用ORDERBY、LIMIT子句，需要使用括号将SELECT语句括起来
五、数据库对象
索引
据库中使用索引，目的在于提高查询效率，本质上就是针对设置了索引的属性列按照特定顺序排序，便于查询
#创建索引的语法：
CREATEINDEX索引名称ON表名（属性名称）
例子：
CREATEINDEXindex_aONtest_a（name）;
#或者在使用ALTER语句更新数据表时，新增索引
例子：
ALTERTABLEtestADDINDEXindex_test（id）;
#或者建表时直接创建
#要查看某个数据表上创建的索引，可以使用SHOW命令
语法：
SHOWINDEXFROM表名
例子：
SHOWINDEXFROMtest;
#要删除索引，可以使用DROP命令
语法：
DROPINDEX索引名称ON表名
例子：
DROPINDEXindex_testONtest
视图
视图在数据库中是一个虚拟的表，实际并不存在，但它可以像真实的数据表一样使用。
使用视图安全性更强，查询效率更高。
主要应用：
通过视图来更新真实表，同样因为这一特性，在原表中做的数据更新，也会反馈到视图中
#创建视图
语法：
CREATEVIEW视图名称ASSELECT语句
例子：
CREATEVIEWview_test
AS
SELECT*FROMtable1WHEREid=1;
#查看数据库上的视图，可以使用命令：
SHOWTABLESTATUSWHERECOMMENT='VIEW'
#更新视图
语法：
ALTERVIEW视图名称ASSELECT语句
语法：
CREATEORREPLACEVIEW视图名称ASSELECT语句
注意：
这里是修改后面的查询语句select
例子：
ALTERVIEWview_testASSELECT*FROMtable1WHEREid=2;
CREATEORREPLACEVIEWview_testASSELECT*FROMtable1WHEREid=2;
#删除视图
语法：
DROPVIEW视图名称
例子：
DROPVIEWview_test
自定义函数
在自定义函数前，需要先确认创建函数功能是否开启
语法：
SHOWVARIABLESLIKE'%func%'
如果返回的log_bin_trust_function_creators变量值为OFF，需要将其开启
语法：
SETGLOBALlog_bin_trust_function_creators=1
#创建函数
语法：
CREATEFUNCTION函数名称（参数1数据类型,参数2数据类型,…..）
RETURNS返回值类型
函数体
函数体可以是一条SQL语句，也可以是多条SQL语句
函数体以BEGIN开始，END结束
例子：
CREATEFUNCTIONget_date（ddatetime）
RETURNSVARCHAR（50）
BEGIN
RETURNDATE_FORMAT（d,'%Y年%m月%d日%h时%i分%s秒'）;
END;
函数定义完成后，使用SELECT语句调用:
SELECTget_date（‘2016-11-0310:
10:
10’）;
也可以给自定义的函数传递其他的值来完成格式转换
例子：
SELECTget_date（now（））AS'当前时
升级会员 