基本SQL语句(一篇就够了)

根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类

SQL基本语法

SQL指令不区分大小写

每条SQL表达式结束之后都以;结束

SQL关键字之间都以空格进行分割

SQL之间可以不限制换行(可以有空格的地方就可以有换行)

使用DDL语句可以创建数据库，删除数据库，修改数据库

查询数据库

##显示mysql中所有是数据库

showdatabases;

##显示指定数据库的创建的SQL指令

showcreatedatabase;

创建数据库

##在mysql中创建一个数据库dbName表示数据库名称

createdatabase;

##创建数据库，指定名称不存在的时候创建

createdatabaseifnotexists;

##在创建数据库的同时指定数据库的字符集(字符集：数据存储在数据库)

createdatabasecharacterset字符集;

修改数据库

##修改数据库的字符集

alterdatabasecharacterset字符集;

删除数据库

删除数据库时会删除数据库中所有的表以及数据库中的所有的数据

##删除数据库

dropdatabase;

##如果数据库存在则删除数据库

dropdatabaseifexists;

使用/切换数据库

use;

创建数据表

数据表实际上就是一个二维的表格，一个表格是由多列组成的表格[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-msYCT6UW-1638636879574)(/home/ddj/图片/2021-11-2101-51-51的屏幕截图.png)]

查询数据表

showtables;

查询表结构

desc;

删除数据表

##删除数据表

droptable;

##删除数据表如果表存在

droptableifexists;

修改数据表

##修改表名

altertablerenameto;

##数据表也是有字符集的,默认字符集和数据库一致

altertablecharacterset字符集;

##添加字段(列)

altertableadd;

##修改的列表名和类型

altertablechange;

##只修改字段类型

altertablemodify;

##删除字段(列)

altertabledrop;

在MySQL中有多种数据类型可以有存放数值，不同类型存放的数值范围或者形式不同的

存储字符序列的类型

在创建数据表的时候,指定的对数据的列的数据限制性的要求(对表的列中的数据进行限制)

为什么给表中的数据添加数据呢

字段常见的约束有那些呢？

主键–就是数据表中的记录中的唯一标识，在一张表中只能有一个主键(主键可以是一个字段,也可以是多个列组合)

createtablebooks(book_isbnchar(4),book_namevarchar(20)primarykey);或者createtablebooks(book_isbnchar(4);book_namevarchar(20),primarykey(book_name));删除数据表主键约束altertablebooksdropprimarykey;创建表之后添加主键约束##创建表时没有添加主键约束createtablebooks(book_isbnchar(4),book_namevarchar(10));##创建表之后添加主键约束altertablebooksmodifybook_isbnchar(4)primarykey;主键自动增长在我们创建一张数据表时，如果数据表中有可以作为主键(列如:学生表的学号,图书表的isbn)我们可以直接设为这个字段为主键,

当有些数据没有合适的字段作为主键的时候，我们可以定义一个与记录无关的列(ID)作为主键

,此数据无具体含义主要作为标识唯一,在mysql中我们可以将此定义为int，同事设置为自动增长

当我们想数据表中新曾一条数据时，无需提供ID列的值，它会自动增长.

auto_increment

createtabletypes(type_iddoubleprimarykeyauto_increment,type_namevarchar(20)notnull,type_reamrkvarchar(20));注意：自动增长从1开始，每次添加一条数据，自动增长的列会+1，但是如果把某条数据删除，自动增长的数据也不会重复生产(自动增长的数据不会保证连续)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B9j68VJc-1638636879576)(/home/ddj/图片/2021-11-2115-18-40的屏幕截图.png)]

createtablegrades(stu_numchar(8),course_idint,scoreint,primarykey(stu_num,course_id));注意:在实际企业项目的数据库设计中，联合主键使用频率不高，当一个数据表中没有明确的字段可以作为主键时，我们可以额外添加一个ID字段作为主键

pass

用于完成对数据表中的数据中插入，删除，修改

从数据表中删除满足特定条件(所有)的数据

deletefromwhereconditions;示例##删除学号为20210102的记录deletefromstuswherestu_num=220210102;##删除年龄大于20岁的学生信息(如果多条数据满足情况则删除多条记录)deletefromstuswherestu_age>20;##如果删除语句没有where子句，则表示删除当前数据表中的所有记录(敏感操作)deletefromstus;修改数据对数据表中已经添加的记录进行修改

updatesetcolumName=值[whereconditions]示例

##select关键字查询的是selectcolnumName1,..from;##如果要显示查询到的的记录的所有列，可以使用*代替字段列表(在项目中不建议使用)select*from;where子句在删除，修改及修改的语句后都可以添加where子句(conditions)，用于筛选出满足特定的数据进行，删除，修改和查询操作．

selectcolnumName,...fromstuswherestu_age>20;计算列对从数据表中查询的记录的列进行一定的运算之后显示出来.

##出生年份＝现在的年份-年龄selectstu_name,2021-stu_agefromstus;字段别名我们可以为查询的结果的字段取一个语义更强的别名

as字段别名

##给2021-stu_age取一个别名(as可以省略)selectstu_name,2021-stu_ageas出生fromstus;消除重复行distinct

从查询的结果中将重复的记录消除

selectdistinctstu_agefromstus;orderby排序将查询到的满足条件的记录按照指定的列的值升序/降序排列

select*fromwhereconditionsbyorderbycolumnNameasc|desc;示例##单字段排序select*fromstuorderbysexdesc;##多字段排序首先性别降序然后id升序select*fromstuorderbysex,idasc,聚合函数sql中提供了一些可以对查询记录进行列计算的的函数–聚合函数

当我们向日期类型的列添加数据时,可以通过字符串类型(字符串格式必须是yyyy-MM-ddhh:mm:ss)

##upper(colynm)将字段的值转换成大写selectupper(name)fromstu;##lower(column)将指定列的值转换成小写selectupper(name)fromstu;##substring(column,start,len)从指定列中截取部分显示start从1开始selectid,substring(name,4,8);分组查询select分组字段/聚合函数from表名[where条件]groupby分组字段名;#先对查询的学生信息按性别进行分组(分成男,女),然后分别统计每组学生的个数selectsex,count(sex)fromstugroupbysex;#先对查询的学生信息按性别进行分组然后计算selectsex,avg(scroe)fromstugroupbysex;#先对学生按照班级分组然后统计各组的学生数量，在排序selectage,count(stu)fromstugroupbyageorderbyageasc;#查询所有学生,按年龄进行分组,然后分别统计每组>1的人数,再筛选年龄升序(having隐藏记录有groupby才能有having)selectage,count(num)fromstugroupbyagehavingcount(num)>1orderbyageasc;分页查询当数据表中的记录比较多的时候,如果一次性全部查询出来的显示给用户,用户的可读性/体验性就不太好,因为此我们可以将这些数据分页进行展示.

select...from...where...limitparam1,param2;案例对数据表中的学生信息进行分页显示,总共59条数据,我们每页显示20条

总记录数:count59

每页显示page20

总页数pageCount=cpimt%pagecount/page:count/page+1;

#查询第一页:select*fromstulimit0,20;#查询第二页select*fromstulimit20,20;#查询第三页select*fromstulimit40,20;#如果一张表中,pageNum表示查询的页码,pageSize表示查询每页的条数:select*fromstulimit(pageNum-1)*pageSize,pageSize;数据表的关联关系MySQL是一个关系型数据库,不仅可以存储数据,还可以维护数据与数据之间的关系–通过数据表中的数据字段的外键约束

人–身份证

学生–学籍

用户–用户详情

方案一:主键关联–两张数据表中的主键相同的数据互相对应的数据

方案二:唯一外键–在任意一张表中添加外键约束与另一张表主键关联,并且将外键列添加唯一约束

班级—学生(一对多)一个班级可以包含多个学生

学生–班级(多对一)多个学生可以属于同一个班级

图书–分类商品–商品类别

学生–课程一个学生可以选择多门课,一门课也可以由多个学生选择

会员–社团一个会员

外键约束–将一个列添加外键约束与另一张表的主键(唯一列)进行关联之后,这个外键约束添加的数据必须要在关联的主键字段中存在

案例:学生表与班级表(在学生表中添加外键与班级表的主键进行关联)

#在创建表的时候,定义cid字段,并添加外键约束#由于cid列要与classes表class_id进行关联,因此cid字段类型长度要与class_id一致createtablestudents( stu_numchar(8)primarykey, stu_namevarchar(30)notnullunique, stu_genderchar(2)notnull, stu_ageintnotnull, cidint, constraintFK_STUDENTS_CLASSESforeignkey(cid)referencesclasses(class_id));#创建表之后为cid添加外键约束altertablestudentsaddconstraintFK_STUENTS_CLASSESforeignkey(cid)classes(class_id);deletefromclasseswhereclass_id=2;updateclassessetclass_id=8whereclass_id=2;#SQL错误[1451][23000]:Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails(`dp_test2`.`students`,CONSTRAINT`FK_STUDENTS_CLASSES`FOREIGNKEY(`cid`)REFERENCES`classes`(`class_id`))修改和删除被关联表的记录或者ID如果一定要修改Java2104的班级ID,该如何实现

UPDATEstudentssetcid=nullwherecid=2;UPDATEclassessetclass_id=4whereclass_id=2;UPDATEstudentssetcid=4wherecidisnull;我们可以通通过级联操作实现:(onxxxcascade)#删除原有的外键altertablesutdentsdropforeignkeyFK_STUDENTS_CLASSES;#重新添加外键,并设置级联修改和级联删除altertablestudentsaddconstraintFK_STUDENTS_CLASSESforeignkey(cid)referencesclasses(class_id)onupdatecascadeondeletecascade;连接查询通过DQL的学习,我们可以很轻松的从一张数据表中查询出需要的数据:在企业的应用开发,我们经常需要从多张表中查询数据(列如:我们查询学生信息的时候),可以通过连接查询从多张数据表提取数据:

在MySQL中可以使用join实现多表的联合查询–链接查询,

select...fromtableNameinnerjointableName2;笛卡尔积内连接条件两张表同时用innerjoin连接查询之后产生笛卡尔积数据很多是无意义的,我们如何消除无意义的数据----添加两张进行连接的查询时的条件

如果连接查询的多张表中存在相同名字的字段,我们可以使用表名.字段名来分区,如果表名太长则不便于SQL语句的编写,我们可以使用数据表别名

selects.*,c.class_namefromstudentssinnerjoinclassescons.cid=c.class_id;子查询/嵌套查询子查询–先进行一次查询,第一次查询的结果作为第二次查询的/条件(源)(第二次查询是基于第一次查询的结果进行的)

从SQL执行的流程中我们分析存在的问题

createprocedure([IN/OUTargs])begin --SQLend;--示例createprocedureprod_test(inaint,inbint,outcint)begin setc=a+b;end;调用存储过程createprocedureproc_test1(inaint,inbint,outcint)begin setc=a+b;end;--调用存储过程set@m;callproc_test1(3,200,@m);--显示变量表select@mfromdual;--dual表是数据库自带的用来存储标量的表存储过程中变量的使用存储过程中的变量分为2种,局部变量和用户变量

局部变量:定义在存储过程中的变量,只能存储过程内部使用

--局部变量要定义在存储过程中,而且必须定义存储过程开始declare[defaultvalue];--示例--创建一个存储过程:计算输入参数的平方与输入参数/2之和createprocedureproc_test3(inaint,outrint)begin declarexintdefault0; declareyintdefault1; setx=a/2; sety=a/2; setr=x+y;end;用户变量用户变量:相当于全家变量,定义的用户变量可以通过select@attrNamedual进行查询

–用户变量会存储在mysql数据库的字典中(dual)

–用户变量定义使用set关键字直接定义,变量名以@开头

set@n=1;

将存储过程中使用select…into…给变量赋值

createprocedureproc_test4(outcint)begin selectcount(stu_name)intocfromstudents;--查询到学生数量赋值给cend;CALLproc_test4(@ddj);SELECT@ddjfromdual;用户变量使用的注意事项因为用户变量相当于全局变量,可以使用SQL指令以及多个存储过程中共享,在开发中建议尽量少使用用户变量,用户变量过多会导致程序不易理解,难以维护

MySQL存储过程的参数一共三种:IN\OUT\INOUT

输入参数–在调用过程中传递数据存储过程的参数(在调用过程必须具有实际值的变量或者字面值)

查询存储过程:查询某个数据库中,有哪些存储过程

alterprocedure特征1[特征2][特征3]存储过程的特征参数:

--删除存储过程--drop删除数据库的对象,数据库数据表存储过程视图触发器索引dropprocedureproc_test1;存储过程练习案例使用存储过程解决企业项目中开发过程中的问题

案例:使用存储过程完成借书操作

创建一个存储过程实现借书的操作:哪个学生借哪本书,借了多少本

操作:

条件:

游标可以用来依次取出查询结果集中的每一条数据–逐条读取查询结果集中的记录

案例

在MySQL,只有执行insert\delete\update操作才能触发触发器的执行

语法:

视图,就是由数据库中的一张表或者多张表特定的条件查询出数据构造得到得到虚拟表

createviewasselect_statement示例视图数据的特性视图是虚拟表,查询视图的数据是源于数据表的,当对视图进行操作时,对原数据表中的数据是否有影响呢

**查询操作:**如果在数据表中添加了新的数据,而且这个数据满足创建视图时查询语句的条件,通过查询视图也可以查询出新增数据,当删除原表中满足条件的数据时,也会葱视图中删除

新增数据:如果在视图中新增数据,数据会被添加到原始数据表

删除数据:如果视图删除数据,数据也会从原表中删除

修改操作:如果通过修改数据,则也将修改原数据表中的数据

索引,就是用来提高数据表中数据的查询效率的.

索引,就是将数据表中的某一列/某几列的值取出来构造成便于查找的结构进行存储,生成数据表的目录当前我们进行数据查询的时候,则先在目录中进行查找得到对于的数据地址,然后在到数据表中根据地址快速的获取数据记录,避免全表扫描

MySQL中的索引,根据创建的所有的列不同,可以分为:

说明:

createfulltextindexon表名(字段名);索引是使用索引创建完成之后无需调用,当根据创建索引的列进行数据查询的时候,会自动使用索引;

组合索引需要根据创建索引的索引字段进行查询时触发

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CGXFLbyx-1638636879579)(/home/ddj/图片/2021-12-0301-39-40的屏幕截图.png)]

--命令行showcreatetabletb_testindex\G;--查询数据表的索引showindexesfromtb_testindex;--查询索引showkeysfromtb_testindex;删除索引--删除索引:索引是建立在表字段上的,不同的表中可能出现相同名称的索引--因此删除索引时需要指定表名dropindexindex_test3ontb_testindex;索引的使用总结优点

缺点

注意事项

ACID特性,高频面试题

开启事务,就是关闭自动提交

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8MBpgV8N-1638636879581)(/home/ddj/图片/2021-12-0317-25-15的屏幕截图.png)]

MySQL数据库事务隔离级别:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2gl80hq6-1638636879583)(/home/ddj/图片/2021-12-0317-53-02的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iYhRoTtT-1638636879584)(/home/ddj/图片/2021-12-0319-59-44的屏幕截图.png)]

我们可以通过设置数据库默认的事务的隔离界别来控制事务之间的隔离性;

也可以通过客户端与数据库的连接设置事务间的事务之间的隔离性;(在应用程序中设置–Spring)

学校图书管理系统(借书)

以下表不满足第一范式(在数据库中创建不出不满足第一范式的表)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RSCDc6aD-1638636879585)(/home/ddj/图片/2021-12-0321-11-07的屏幕截图.png)]

将细分的列作为单独的一列

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J4TXRVWe-1638636879586)(/home/ddj/图片/2021-12-0321-11-44的屏幕截图.png)]

不存在非关键字段对关键字段部分依赖

以下表不满足第二范式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TPHVHC7b-1638636879586)(/home/ddj/图片/2021-12-0321-29-12的屏幕截图.png)]

将每个关键字段列出来\关键字段的组合也列出来,依次检查每个非关键字段

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xc41FBdP-1638636879587)(/home/ddj/图片/2021-12-0321-34-21的屏幕截图.png)]

不存在非关键字段的之间的传递依赖

以下数据表不满足第三范式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EhhoDHcx-1638636879588)(/home/ddj/图片/2021-12-0322-49-27的屏幕截图.png)]

将关键字段和被依赖的关键字段分贝作为主键,依次检查所有非关键字段的依赖关系

E-R(Entity_Relationship)实体关系图,用于直观的体现实体与实体之间的关联关系(一对一,一对多,多对一,多对多)

E-R图基本图例

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-znNJ5TyI-1638636879589)(/home/ddj/图片/2021-12-0417-40-27的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8wIH1pSm-1638636879590)(/home/ddj/图片/2021-12-0417-41-02的屏幕截图.png)]

每个实体创建一张数据表

多堆多关联:额外创建一个数据表维护关系,关系表分别创建外键与两张表关联

一对多,多对一关联:在多的一端添加外键与一的一端主键建立外键约束

一对多关联:在任意一端创建外键与另一端关联主键关联,并将外键设置#基本SQL语句(一篇就够了)

SQL(StructuredQueryLanguage)结构化查询语言，用于存取，查询，更新数据以及管理关系型数据库系统

数据表实际上就是一个二维的表格，一个表格是由多列组成的表格[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DxyJRKz9-1638636880609)(/home/ddj/图片/2021-11-2101-51-51的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PZwNkKVA-1638636880610)(/home/ddj/图片/2021-11-2115-18-40的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mk3UBAko-1638636880612)(/home/ddj/图片/2021-12-0301-39-40的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dRrusj8Q-1638636880614)(/home/ddj/图片/2021-12-0317-25-15的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LdG8Ws1e-1638636880615)(/home/ddj/图片/2021-12-0317-53-02的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9efp4NaU-1638636880616)(/home/ddj/图片/2021-12-0319-59-44的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D0osUjjo-1638636880617)(/home/ddj/图片/2021-12-0321-11-07的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Olxftps-1638636880618)(/home/ddj/图片/2021-12-0321-11-44的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nbiDJetl-1638636880619)(/home/ddj/图片/2021-12-0321-29-12的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oCEr0qzz-1638636880620)(/home/ddj/图片/2021-12-0321-34-21的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GyrPsK6Y-1638636880621)(/home/ddj/图片/2021-12-0322-49-27的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Z0x3tZtb-1638636880622)(/home/ddj/图片/2021-12-0417-40-27的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UngaqQjl-1638636880623)(/home/ddj/图片/2021-12-0417-41-02的屏幕截图.png)]

一对多关联:在任意一端创建外键与另一端关联主键关联,并将外键设置