MySQL总结Word文档下载推荐.docx
- 文档编号:22959217
- 上传时间:2023-02-06
- 格式:DOCX
- 页数:21
- 大小:43.55KB
MySQL总结Word文档下载推荐.docx
《MySQL总结Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《MySQL总结Word文档下载推荐.docx(21页珍藏版)》请在冰豆网上搜索。
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只能用于作为主键的列
(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'
引号无所谓加不加
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
分组同样可以基于多个属性
查询公司内各个部门不同职位的员工人数
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
总经理'
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'
当前时
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL 总结