该博客用于对MySQL学习的总结！！如有侵权请告知删除~~
目录
一、SQL分类
1、数据定义语言（简称DDL），用来定义数据库对象
数据库database，表table，列column等。
关键字：创建create，修改alter，删除drop等（结构）
2、数据操作语言（简称DML），用来对数据库中表的记录进行更新
关键字：插入insert，删除delete，更新update等（数据）
3、数据查询语言（简称DQL），用来查询数据库中表的记录
关键字：select，from，where等
4、数据控制语言（简称DCL），用来定义数据库的访问权限和安全级别以及创建用户
关键字：grant等
二、数据库的操作
2.1 数据库相关说明
用户通过在dos窗口中输入后面代码进入数据库：mysql [-h 连接的主机ip -P端口3306] -u 用户名 -p 密码
说明：
1、一个数据库软件可以安装多个数据仓库，数据仓库可以简称为数据库，在数据库中创建数据表来保存数据。
2、数据库的一行称为记录，可以理解成java实例化后的一个对象。
3、数据库的一列称为字段，理解成java类中的属性。
4、一个数据仓库中是可以有多张表的。
2.2 单表操作
2.2.1 查询所有数据库
1、查询当前所有数据库
语法：show databases;
2、查看当前数据库的创建方式，查看数据库的编码表
语法：show create database 库名;
2.2.2 创建数据库
创建数据库的语法：
1、create database 数据库名 ：由于创建数据库时没有指定编码表，因此会使用安装数据库时默认的编码表
2、create database 数据库名 character set 编码表名； 创建数据库会使用指定的编码表
注意：如果创建数据库指定的编码表是utf8,记住不能写utf-8。utf-8 是错误的(mysql不认识utf-8)。
java中的常用编码: UTF-8; GBK;GB2312;ISO-8859-1;
对应mysql数据库中的编码: utf8; gbk;gb2312; latin1;
2.2.3 删除数据库
语法：drop database 数据库名;
2.2.4 修改数据库编码集
语法：alter database 数据库名称 character set 字符集;
2.2.5 切换数据库
语法： use 数据库名;
2.2.6 查询当前正在使用的数据库
语法：select database();
2.2.7 数据表的创建
注意：创建表的时候，一定要先切换到某一个具体的数据库。因为只有某一个具体的仓库下才会有数据库表。
语法：
create table 表名(
列名 类型(长度), ----某一列,存放的数据是什么类型.长度表示允许列名存储数据的最大长度.
列名 类型(长度),
..........................
列名 类型(长度)---最后一个列不需要逗号。
);
注意：
1)一个数据表可以存在很多列（字段），每列具有类型和长度。
2)多个列之间使用逗号隔开，最后一个列不需要输入逗号。
3)创建表的时候，一定切换到某一个具体的数据库。use 数据库名。
数据类型：
字符串 -- varchar char
整数 int
浮点 float double
日期 date time DateTime timestamp
注：
1、varchar(列的长度):列的长度可变。
name varchar(10):设置name字段的长度为10,name的储存长度在10以内都可以，并且会自动适应长短。
补充：保存字符个数：0-255.
2、char(列的长度):列的长度固定，不能改变。
name char(10):设置name字段的长度为10，name的存储长度在10以内，如果不满10，用空格补足。
举例：假设我们要存储 abcd 这个字符串。
如果定义成varchar(10) 这时储存的数据不足10个，这时会把多余取消，只占4个。
如果使用char(10)，它会把存储的数据添加到10个长度。剩下的用空格补足。
如果存储的长度超出了表中列的长度，存储报错。
总结：char 的性能好。 varchar可以节省空间。
通常的情况，如果长度不固定，我们使用varchar。
使用char的情况。当某个字段的长度固定的时候，可以采用char。例如身份证号或者手机号。
2.2.8 查看该数据库的所有的表
语法：show tables;
2.2.9 查看表的列的信息（查看表结构）
语法：desc(describe缩写) 表名;
2.2.10 单表创建时约束
约束：通过某些限制，来规定当前某张数据表中的列数据是否可以为null，是否可以重复，当前某一列中的数据在当前表中必须唯一等限制。
约束的目的：只是为了保证存储在数据表中的数据完整性和有效性。从而可以确保数据库满足业务规则。
语法：列名 数据类型 约束条件;
1、主键约束：primary key -- 非空 唯一
格式：列名 列的类型 primary key;
补充：自增长 auto_increment
如果表的主键是int 类型，这时可以在主键的后面添加 auto_increment ，这时表中这一列在添加数据的时候，会自动的增长。
【示例】
create table 表名 (
id int primary key auto_increment,
name varchar(20),
…………
)；2、唯一约束：unique
该列（字段）的值不允许重复。可以为null（null不算重复）。
解释：一张表中可以有很多个唯一约束。
格式：列名 类型（长度） unique;
【示例】
create table 表名 (
id int primary key auto_increment,
name varchar(20) unique,
…………
)；唯一约束和主键约束的区别：
1、唯一约束可以是空(null)。 但是主键约束不能为空
2、一张表中只能有一个主键，但是唯一约束可以有多个
3、非空约束，not null。表示该列的内容不允许为空。该字段的值不能为空。
格式：写法： 列名 类型（长度） not null；
【示例】
create table emp(
id int primary key auto_increment,
name varchar(20) unique,
age int not null,
…………
);主键约束 -- 唯一非空 只能存在一个
唯一约束 unique 可以有多个
非空约束 not null 可以有多个
2.2.11 修改数据表
可以对表名、表中的列名、列的类型等进行增删改。
语法：alter table 表名 增/删/改 列名 类型(长度) 约束；
1、增加列
语法： alter table 表名 add 列名 类型(长度) 约束;
2、修改现有列类型、长度和约束
语法：alter table 表名 modify 列名 类型(长度) 约束;
3、修改现有列名称
语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;
4、删除现有列
语法：alter table 表名 drop 列名;
add -- 添加列
modify -- 字段的类型以及约束
change -- 修改的列名称
rename -- 表名 Rename table 表名 to 新表名;
5、修改表名
语法： rename table 旧表名 to 新表名;
6、修改表的字符集
语法：alter table 表名 character set 编码集;
2.2.12 数据表删除
语法：drop table 表名;
2.2.13 insert语句----向数据表插入数据
方式一：全写
语法：insert into 表名 (列名1，列名2，列名3......) values (值1，值2，值3......);
语法：insert into 表名 values (值1，值2，值3......);
注意事项：
1、值与列一一对应。有多少个列，就需要写多少个值。如果某一个列没有值。可以使用null。表示插入空。
2、值的数据类型，与列被定义的数据类型要相匹配。并且值的长度，不能够超过定义的列的长度。
3、字符串：插入字符类型的数据，建议写英文单引号括起来。在mysql中，使用单引号表示字符串。
4、date 时间类型的数据也得使用英文单引号括起来： 如’yyyy-MM-dd’,’yyyy/MM/dd’,’yyyyMMdd’ 。
5、如果主键是自动增长的，可以直接插入数值null。
方式二：省略部分列
语法：insert into 表名(列名，列名，列名。。。。) values（值，值，值。。。。）
注意：
可以省略部分列名。某一列 有默认值，或者允许为空，才可以省略。
主键是自增长的认为是有默认值的。也可以省略
注意：在插入数据的时候，如果某些列可以为null，或者是自动增长的列，或者有默认值的，在插入的时候可以省略。
方式三：省略所有的列(掌握)
语法：insert into 表名 values（值，值，值，值）;
1、表中有多少列，就有多个值。
2、按照表的结构，列和值去对应。 desc 表名 得到列的顺序。 从上倒下，对应的值，从左到右插入数据。
注意：如果给表中的所有列插入数据，这时可以省略表名后面的列名，直接写values。
总结：
1）如果主键：是自增长。不需要人工赋值（可以给值null）。数据库会按照自己的算法，为主键填充值。
2）通常遇到主键自增长，人工赋值的时候，赋值null。mysql会自己把null替换成新的值。
数据的插入:
两种方式 -- 根据列名插入对应的值
直接插入所有的列 - 将列名省略掉;
2.2.14 数据记录修改操作(update语句)
语法: update 表名 set 列名=值,列名=值.... [ where条件语句 ];
注意事项：
1、如果不加条件语句，将会修改某一列的所有值。
2、一般修改数据时，都需要增加条件。（重点）
2.2.15 delete语句 -- 删除表中数据的语句
语法：delete from 表名 [where条件语句]
注意：
- 如果删除表中的记录时，没有添加where条件，这时会把表中的所有数据删除。表依然存在，是空的表。
- delete是删除行的数据。
2.2.16 truncate 语句----删除数据
语法：truncate table 表名;
先删除表,再创建表，就等于将数据全部删除了。也就是说上述语句执行时会把表的所有内容都删除掉，然后在创建一个新的表。
性能问题： truncate table的性能相对来说更好，尤其删除的数据越多，越明显。
如果要删除一张表中的所有数据，也可以使用 truncate table 表名;
执行时会把表的所有内容都删除掉，然后在创建一个新的表。
truncate 缺点：不能有条件的删除。不能恢复数据
delete 是逐行删除，并没有恢复初始化值。 可以恢复数据
delect 和 truncate 删除的区别：
DELETE
-
DML语言
-
数据还可以找回来，恢复回来
-
可以有条件的删除。DELETE FROM 表名 WHERE 条件
TRUNCATE TABLE
-
DDL语言
-
数据不可以恢复
-
先将整个表删除，再重新创建
-
删除速度比delete快。
2.2.17 数据表记录的查询
语法一：查询数据库中的某张表的所有数据
语法：select * from 表名;
语法二: 查询某张表中指定的列
语法： select 列名,列名... from 表名;
语法三：按条件查询
语法：select 列名,列名..... from 表名 where 条件;
运算符：
1）>(大于) <(小于) >=(大于等于) <=(小于等于) =(相等) <>或者!= (不相等)
2）
and 逻辑与 ，多个条件同时成立。&&
or 逻辑或 ，多个条件任意一个成立。||
not 逻辑非 ，相反的意思。!
3）区间：between ... and ... 在两者之间取值。 格式：列名 between 开始值 and 结束值;
4）in(值1,值2,值3) 在指定值中任意取。 格式：where 列名 in (值，值，值.......);
5）模糊查询：like '模糊查询部分
表达式有两个占位符：
A: % 表示零或任意多个字符 ;
B: _ 任意单个字符;
6）is null 判断该列值是否为空
语法四 ：过滤重复数据
查询排重：select distinct 列名 from 表名 [where 条件];
说明：distinct： 去重复。显示distinct后面列的内容，并且过滤掉重复的数据。
需求：显示不重复的年龄。
语法五 ：对查询的结果进行排序
语法：select * from 表名 order by 列名1 asc|desc，列名2 (asc|desc)，列名3 (asc|desc) ......;
asc是升序排列，desc是降序排列。默认是asc升序。
2.2.18 别名：可以对查询出来的列名起别名
语法：select 列名 as 别名,列名 as 别名,列名 as 别名.... from 表名 where 条件;
注意：在使用别名的时候，as 关键字可以省略。
2.2.19 聚合函数(聚集函数)
多个数据进行运算，运算出一个结果。例如，求记录数，求和，平均值，最大值，最小值。
作用：做统计使用的。 例如：可以统计班级的平均分。
SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：
count(数目) : 统计个数（行数）,统计有多少行，或者有多少条数据。
sum(和)函数：求和。
avg(均值)函数：求平均值。
max(最大值)：求最大值。
min(最小值)：求最小值。
- count函数--统计记录数(统计行数)
语法：select count(*)或者count(具体列名) from 表名;
注意：
1、count(具体列名)：在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。按照列去统计有多少行数据。
2、
select count(列名) from 表名:按具体列来进行统计行数。
select count(*) from 表名: 统计表中的行数。
- sum函数：求和
语法：select sum(列名) ,sum(列名) ,......from 表名;
注意事项：
如果使用sum 多列进行求和的时候，如果某一列中含有null，这一列所在的行中的其他数据不会被加到总和中。
案例：统计年龄与成绩的总和值
方法一：
方法二：
问题：两种方法相差84
产生问题的原因：
上述写法会先将每一行的年龄值和分数值进行相加，然后再把每一行的年龄值和分数值相加后的值进行求和。
这样写会出现一个问题，因为在mysql中null值和任何值相加为null,导致在进行柳岩的年龄和分数相加的时候，柳岩的年龄和分数和值就变为了null。而最后sum求和的时候，就把柳岩的年龄和分数和值null给排除，因此最后的和值会缺少柳岩的年龄和分数和值84。
解决方案：在sql语句中我们可以使用数据库提供的函数ifnull(列名, 默认值)来解决上述问题。
ifnull(列名, 默认值)函数表示判断该列名是否为null,如果为null，返回默认值，如果不为null，返回实际的值。
数据库中的函数：truncate(列名, 截取的小数位) 表示截取的意思，保留几位小数。
- avg函数：求平均值
语法： select avg(列名) from 表名;
注意：求某列的平均值，avg里面的null不作为统计。
- max，min函数：最大最小值
max(列) /min(列) 统计该列的最大值或者最小值。
select max(列名),min(列名) from 表名;
注意：null 排除在外。
2.2.20 group by分组函数（★★★★★）
语法：select … from … group by 列名，列名
注意：
- 聚合函数：分组之后进行计算；
- 通常 select后面的内容是被分组的列，以及聚合函数;
分组函数结合where出现的问题：
是因为上述的sql语句的使用有错误。
主要原因：在sql语句中的where 后面不允许添加聚合函数，添加就会报上述错误。
那么既然这里不能使用where来解决问题，但是我们还依然要进行过滤，所以在sql语句中，如果分组之后，还需要一些条件。
可以使用having条件，表示分组之后的条件，在having后面可以书写聚合函数。
关于 having 的用法解释：
having和group by 一起使用，having和where的用法一模一样，where怎么使用having就怎么使用，where不能使用的，having也可以使用，比如说where后面不可以使用聚合函数，但是在having后面是可以使用聚合函数的。
修改后的sql语句如下所示：
总结：
一、关于group by 和having一起使用的规则如下所示
group by 列名 having 条件
分组之后加过滤条件。
现在 我们学习过的sql语句有很多,那么我们一起为这些语句的顺序排个序.
S(select)… 查询
F(from)… 从哪个表中
W(where)… 关联一些条件
G(group by)… 分组查询
H(having)… 关联一些条件(还可以关联聚合函数)
O(order by); 排序 asc升序 desc 降序
S(select) 查询的字段 F(from) tablename W(where) 关联条件 G(group by) 分组的字段 H(having) 关联条件(聚合函数)O(order by) (asc desc);
二、where 和 having 的区别。
1、having 通常与group by 分组结合使用。 where 和分组无关。
2、having是否可以替换之前的where呢？
但当查询语句如下时结果就大不一样了：
使用where：
使用having：
因为前面的select product,id ，并没有筛选出price。所以会报错。
总结：开发中建议上述情况下使用where，避免没有必要的错误。
1）where的使用没有什么限制；having跟的这个条件字段必须要被查询的字段所包含。
例：select product,id from order having price > 20; -- 要被查询的字段中并无price
2）having可以跟聚合函数，where不支持聚合函数
3）如果不关联聚合函数，直接使用where；如果使用聚合函数，直接使用having。
S(select) 查询的字段 F(from) tablename W(where) 关联条件 G(group by) 分组的字段 H(having) 关联条件(聚合函数)O(order by) (asc desc);
2.3 多表操作
2.3.1 外键约束
外键：
从表中的公共字段，称为外键，外键不一定是从表的主键。
两个表的公共字段，名字可以不一样，但是字段的数据类型必须要一样。
语法：foreign key( 当前表中的列名 ) references 被引用表名(被引用表的列名);
给第三张表添加外键约束有两种方式：
第一种方式：给已经存在的表添加外键约束：掌握着一种!!
alter table coder_project add foreign key(coder_id) references coder(id);
alter table coder_project add foreign key(project_id) references project(id);第二种方式：创建表时就添加外键约束：
create table coder_project(
coder_id int,
project_id int,
foreign key(coder_id) references coder(id),
foreign key(project_id) references project(id)
);
添加外键需要注意的问题：
- 如果从表(引入字段的表，coder_project)要去添加一个外键约束。要求主表(被引用字段的那个表,coder或者project)被引用的字段是主键或者唯一的。通常使用主键。
- 如果要删除主表中的数据。要求在从表中这个数据，要没有被引用，才可以去删除。
- 如果要向从表中去添加数据。要求在主表中，要有对应的数据。才可以去添加。
- 如果要删除表。要先删除从表。然后去删除主表。
- 新建表的时候。需要先去创建主表，然后去创建从表。
外键约束作用：保持数据的完整性，和有效性。
2.3.2 实体之间的关系
一对多关系(掌握)：
其中也有2个实体，但是其中A实体中的数据可以对应另外B实体中的多个数据，反过来B实体中的多个数据只能对应A实体中的一个数据。
例：一个学生(stuinfo)有多个成绩(stumarks)
一对一关系(了解)：
一对一关系表在实际开发中使用的并不多，其中也是2个实体，其中A实体中的数据只能对应B实体中的一个数据，同时B实体中的数据也只能对应A实体中的一个数据。例如：人和身份证对应关系，老公和老婆的对应关系。
而一对一在建表的时候，可以在任意一方的表中添加另外一方的主键作为外键即可。
多对多关系：
主表中的一条记录对应从表中的多条记录，同时从表中的一条记录对应主表中的多条记录
例：科目和学生
2.3.3 数据规范化
| 范式 | 特点 |
| 1NF | 原子性：表中每列不可再拆分。 |
| 2NF | 不产生局部依赖，一张表只描述一件事情。表中的每一列是完全依赖于主键的。 |
| 3NF | 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。 |
2.3.4 交叉连接查询
语法：select * from a,b;
以上查询回产生九条数据，产生的原因：
说明：
a表中的每一条记录，都和b表中的每一条进行匹配连接。所得到的最终结果是：a表中的条目数乘以b表中的数据的条目数。
将a表的每行记录和b表的每行记录组合的结果就是笛卡尔积。
解决办法：
2.3.5 内连接查询
内连接查询的结果：两表的交集(公共部分)。
- 隐式内连接查询
语法：select 列名 , 列名 .... from 表名1,表名2 where 表名1.列名 = 表名2.列名;
- 显式内连接查询
语法：select * from 表名1 inner join 表名2 on 条件 ;
2.3.6 外连接查询
外链接：左外连接、右外连接。
- 左外连接
语法：select * from 表1 left outer join 表2 on 条件;
左外连接：用左边表去右边表中查询对应记录，不管是否找到，都将显示左边表中全部记录。
分析：香蕉是没有价格的，但是由于香蕉位于左边的表中，所以即使香蕉的价格是null，也会将香蕉的信息显示出来。
- 右外连接
语法：select * from 表1 right outer join 表2 on 条件;
用右边表去左边表查询对应记录，不管是否找到，右边表全部记录都将显示。
分析：在b表中id为4到a表中查询是没有对应水果描述的，但是使用右外连接也会将b表中的价格查询出来。
总结：
2.3.7 SQL关联子查询
子查询：把一个sql的查询结果作为另外一个查询的参数存在。
2.3.8 in的用法：(掌握)
-- 需求：查询不及格的学生(使用in完成)。
分析：
- 在中间表中查询不及格的学生编号;
- 在学生表中根据学生编号查询学生信息;
查询结果如下：
2.3.9 exists的用法：（了解）
exists的意思是表示存在,如果子查询有返回数据行，就认为是true，否则就认为是false，关注的是子查询是否有数据。只要有数据就认为匹配成功
语法：select * from 表名 where exists (select … from 表名 where 条件) ;
将外表中的查询结果拿到内表中去逐行判断条件是否成立，如果成立，取出该行结果。
-- 需求：查询不及格的学生(使用exists完成)。
2.3.10 all的用法(了解)
2.3.11 any的使用方法(了解)
any:表示任何一个
说明：使用any,只是把in换成 = 即可。
2.3.12 some的使用方法(了解)
some： 表示任何一个，和any的作用相同。
any和some是没有区别的，some和any 效果一样 ，代表一部分记录。
2.3.13 as的使用方法(掌握)
as:不仅可以用来做列的别名，还可以将查询结果通过as作为一张表来使用。
【示例】
查询不及格的学生信息和不及格分数
分析：
-- 1）在中间表studentcourse中查询不及格的学生的id和分数;
说明：可以把上述查询的结果看作为一张数据库的临时表。
-- 2）在学生student表中通过学生的id查询学生的信息并且显示分数;
2.3.14 limit的用法-(理解)
作用 : 限制查询结果返回的数量。-- 查询数据库中的一部分数据
语法：select * from 表名 limit offset, row_count;
offset:表示索引，注意这里的索引从0开始。
row_count:表示查询记录数。
加入条件约束语法：select * from 表名 where 条件 limit offset, row_count;
2.4 事务
2.4.1 事务管理方式一
事务：指的是逻辑上的一组操作，组成这组操作的各个逻辑单元，要么全都成功，要么全都失败。
例如：A汇款给B，如果A扣了钱之后系统出了异常，导致B增加钱的代码没有执行，那么就会出现A扣了钱，而B没有增加钱，那么我们怎么解决这个问题来提高汇款的安全性呢？？
a=1000 b=1000
-- 开启事务
start transaction;
-- 执行逻辑
update account set money = money -100 where name='a';
update account set money = money +100 where name='b';
-- 提交事务(sql语句对数据库产生的操作才会被永久的保存)
commit;
那么事务是如何处理异常情况的呢？
a=1000 b=1000
-- 开启事务
start transaction;
-- 执行逻辑
update account set money = money -100 where name='a';
-- 出现异常
update account set money = money +100 where name='b';
-- 事务的回滚(撤销已经成功执行的sql语句)
rollback;| sql语句 | 描述 |
| start transaction; | 开启事务 |
| commit; | 提交事务 |
| rollback; | 回滚事务 |
注意 : 不管是使用了 rollback还是 commit,都会结束事务!!!!!!!!!
只要不是通过commit提交的事务,其他任何情况下异常发生都会rollback
但是实际上数据库中的数据没有发生变化!!!
进行事务的回滚,回滚后发现数据库中的内容其实并没有发生变化
如果想让事务中的一组操作全部成功,可以使用commit提交事务
这时候数据库中的内容也发生了变化!!
2.4.2 事务管理方式二
使用命令：show variables like '%commit%'; -- 查看当前自动提交状态,开启状态!!
使用命令开启或关闭自动提交状态
set autocommit = 1; 开启
set autocommit = 0; 关闭
关闭自动提交
在自动提交的状态下我们继续使用SQL操作数据库
这时候会发现 并没有更改数据库中的内容!!!
如果想要更改数据库中的内容 需要我们手动提交!!!(因为自动提交已经关闭)
3057
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
