####对于SQL语言的高级知识总结
1、MySql的编码问题
注释：创建数据库的时候，已经指定了该数据库的编码格式，但是由于计算机操作系统的不同，所以出现编码问题
以Windows操作系统为例---手动表达各种嫌弃，使用linux安装mysql的时候直接设置编码格式为utf-8，建表也设置，一点问题都没有，就windows逼事多
a、当在Windows上使用MySql的时候，使用命令窗口操作的时候，存在两个部分的编码问题
问题一：Windows的命令行方式默认使用的是gbk编码，且不容易更改，当使用命令行操作MySql的时候，在cmd中输入中文，
经过传输，进入MySql中的时候，无论是什么编码格式，MySql都默认存储称utf-8格式，
问题二：当从数据库中读取文件的时候，如果包含中文，服务器会将其首先转换成utf-8格式，然后再传输给客户端即：命令
行窗口，由于窗口仍然是gbk编码，所以由于编码不统一，会出现乱码
b、解决方案：
方案一：针对问题一，使用set character_set_client=gbk;将客户端向服务器的传送格式设置为gbk格式
针对问题二，使用set character_set_results=gbk;将服务器向客户端的传送编码格式设置为gbk格式
注意：上面的两步设置，只是临时性的，即只在当前命令行窗口有效
方案二：在MySql的安装目录中寻找到my.ini设置文件，并找到default_character_set=gbk;
注意：这种方式可以永久设置，来解决上面的问题
c、显示数据库的编码格式
show variables like 'char%';采用上面的方案二设置的时候，设置前后可以使用这条语句来看一下数据库的编码
2、MySql数据库的备份与恢复
a、备份（从数据库中导出sql脚本）
使用mysqldump.exe这个执行程序
格式：mysqldump _u用户名 _p密码 数据库名称 > 生成的脚本文件的路径
备注：上面操作的是对象某个用户具有曹组权限的可操作的数据库
b、恢复数据（sql脚本读入到数据库）
1、首先要创建一个新的数据库
2、导入之前的sql备份文件
格式：mysql _u用户名 _p密码 数据库<sql脚本文件存储路径
c、对于上面的两种操作
可以使用第三方工具的小按钮进行完成，对于不同的第三方工具，按钮不同
###对于数据库的约束
A、主键约束
B、非空约束
C、唯一约束
D、外键约束
AAA关于主键约束
1、主键的特性
a、非空（主键不可以为null）
b、唯一（主键所在的列，不能存在重复的两个行值）
c、可引用（作为外键使用）
d、对于主键一个表中只能有一个，但是在某些情况下，也会存在多个主键，这些主键都来自，其他表的主键，也就是本表的外键（举例：在查询学生成绩生成成绩表的时候，列由课程表，学生表，教师表组合构成）
2、主键的设置与修改
a、创建表的时候添加主键
create table 表名称 (主键列名称 列类型 primary key，列名二 列类型，列名三 列类型....);
create table 表名称 (列名一 列类型，列名二 列类型，列名三 列类型.....primary key(想要设置成主键的列名称));
b、修改表的时候后添加主键
alter table 表名称 add primary key(想要设置成主键的列名称);
c、修改表的时候删除主键
alter table 表名称 drop primary key;这里不需要写上列名称的参数，因为一张表的主键只有一个，随意默认删除了主键
3、关于主键怎么来确定
a、对于主键尽量使用代理主键，不要使用自然主键，对于人来说，身份证号是唯一的，可以作为主键来设置，但是一般不这么做，假如有必要插入相同身份证号的另一个人的时候，假如已经设置身份证号为主键，那么将不能完成添加
4、主键的自增长
备注：主键具有唯一性，插入记录的时候需要了解原来表中的记录的主键是什么，为了不出现重复，引入了主键自增长
a、满足自增长的条件
①要求主键列必须是int类型
②使用一个一起在增长的序列来做主键
③当指定主键为null的时候，主键有系统指定从1开始
b、自增长的实现
①在创建表的时候添加主键自增长
create table 表名称 (s_num int primary key auto_increment , s_name vachar(30) ,s_age int);
②在修改表的时候设置主键自增长
alter table stu change sid stuid int auto_increment;
c、假设一种情况，当通过自增长，得到一系列的存储数据后，主键编号为1, 2, 3, 4, 5。。。。。当我删除了主键为3,4的数据再次添加数据记录的时候，生成主键，并不会将3,4的空缺补上，而是在5后面依次在递增，生成新的主键值
5、对于java开发中主键的使用
a、集群数据库中不能使用像上面那样简单的主键值
java提供了主键生成的类，UUID类，自己查看
b、在老师自己编写的itcast。。。包中已经将上面的UUID封装成了工具
c、对于更为大型的系统不会使用，随机生成主键的方式
这样就需要使用数据库编码的知识
BBB关于非空约束
1、什么是非空约束
非空约束是指，表记录的某个列不允许为null
2、怎么实现非空约束
在创建表的时候，在列名后面添加上not null
s_name varchar(50) not null;
CCC关于唯一性约束
1、什么是唯一性约束
添加了唯一性约束的列记录值不能存在重复
2、怎么设置唯一性约束
使用unique
s_name varchar(50) unique; 这里添加了唯一性约束
3、假如同时将非空和唯一性添加到同一个列上呢？
s_name varchar(40) unique not null; 二者顺序不能变化，
这个时候s_name列和主键列的区别只有是否可以引用，主键列可以被其他表引用为外键
DDD关于外键约束
备注：外键是引用的其他表的主键得到的，提供主键的表叫做主表，接收主键作为外键的表叫做从表
从表依赖主表，若没有主表，则从表也不存在
1、外键的特征
a、外键可以重复
b、外键可以为空
c、外键与本表主键之间是多对一关系
d、外键必须来自另一张表的主键（此条尚不严密）
e、在没有删除从表的时候，直接先删除主表，会报错！
2、外键的创建
a、创建表的时候，指定外键
语法格式：
constraint 外键约束名称 foreign key (本表外键的列名) references 主键表 (引用的主键列名);
备注：本表外键的列名与引用的主键列名最好名称相同，外键约束名最好取名要有意义，表示主从表之间的关系
举例：
主表：
create table tb_dept(
d_id char(32) primary key,
d_name varchar(50));从表：
create table tb_emp(
e_id char(30) primary key,
e_name varchar(50),
d_id char(32),
constraint fk_emp_dept foreign key (d_id) references tb_dept (d_id));b、修改表时添加外键的语法
语法格式：alter table emp constraint 约束名称 foreign key (本表外键的列名) references 主键表 (引用的主键列名);
c、修改表时删除外键的语法
语法格式：alter table emp drop foreign key 约束名称;
d、主键与外键的比较
主键：唯一、可引用、非空
外键：可空、可重、来自主表的主键
EEE关于关系的探讨
1、多对一的关系
举例：学生和班级
实现：使用外键来实现
2、多对多的关系
举例：学生和老师
实现：需要借助中间表来实现，在中间表中分别引用学生表和老师表的主键，来构成自己的主键
即：在中间表中设置两个外键
3、一对一的关系
举例：老公和老婆
实现：a、主表的主键不变
b、将从表的主键设置成（来自主表主键的）外键
create table husband(
h_id int primary key,
h_name varchar(20));
create table wife(
w_id int peimary key,
w_name varchar(30),
constrain fk_h_w foreign key (w_id) references husband (h_id));FFF关于对象模型和关系模型的探讨，此外还有一个概念模型？？
1、什么是对象模型
对象模型是指在java中的类，
2、什么是关系模型
关系模型是指在数据库中的数据表
3、二者有什么关系
a、在进行数据的存储和提取的时候，二者要相互转化
数据记录提取成java类的对象
java类的对象存储到数据表中
b、对于上面描述的三种关系（多对一、多对多、一对一）在java类中是怎么描述的呢？？？
1、一对一关系
class Wife{
private String(列类型) w_id;
private String w_name;
private Husband husband;(相当于设置了对丈夫的关联)
}
class Husband{
private String h_id;
private String h_name;
private Wife wife;(相当于设置了对妻子的关联)
}2、多对一关系
class Emp{
private int e_id;
private String e_name;
private Dept d_id;(设置了对Dept的关联)
}
class Dept{
private int d_id;
private String d_name;
private List<Emp> emplist;(设置了对Emp的多个关联)
}3、多对多关系
class Student{
private int s_id;
private String s_name;
private List<Teacher> teacherlist;(设置了对Teacher的多个关联)
}
class Teacher{
private int t_id;
private String t_name;
private List<Student> studentlist;(设置了对Student的多个关联)
}备注：使用对象模型的时候，不存在中间表对应的java类
###在这里对多表查询的知识进行总结，包括验证，使用exam数据库中的表格练习查询操作
AAA关于多表查询的知识
1、分类
a、合并结果集查询，把两个结果纵向排列在一起（行数增多、列数不变）
b、链接查询，把两个结果集横向排列（行数不变，列数增多）
c、子查询，即select查询的嵌套
BBB对合并结果集查询的知识
1、使用条件
a、两张要合并的表的列数相同
b、两张表对应的列的类型相同
2、使用关键字
a、union，去除重复行，只有完全重复的时候才会去除，相应的那一行
b、union all，不去除重复行
CCC对连接查询的知识总结
1、分类
a、内连接方式
分为三种形式：MySql的方言、SQL标准、自然
b、外连接方式
分为三种形式：左外连接、右外连接、全外连接
2、准备知识
a、关于笛卡尔积的理解
b、在进行多表操作的时候，一般会使用起别名的方式，来区分不同表
3、关于内连接的学习
a、内连接的特点
①结果集的行=笛卡尔积的结果
②结果集的列=a表的列+b表的列（即：两个表的列数之和）
b、MySql的方言
查询emp、dept中所有的结果集，即笛卡尔积
select * from emp，dept;
---------------------
起别名，进行标识
select * from emp e1,emp e2;
---------------------
查询emp和dept两表的结果集，查询所有员工信息及所在部门信息
select * from emp e,dept d where e.deptno=d.deptno; 这里是去积条件
---------------------
查询员工的名字及所在部门的名称
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; 这里是去积条件
---------------------
查询张飞的工资及所在部门名称
select e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and e.ename='张飞';
--------------------------
c、SQL的标准方式
同样是上面的两个要求，使用标准格式进行改写
查询emp和dept两表的结果集，查询所有员工信息及所在部门信息
select * from emp e inner join dept d on e.deptno=d.deptno;
--------------------------------
查询员工的名字及所在部门的名称
select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
--------------------------
查询张飞所在部门信息及工资
select e.sal,d.* from emp e inner join dept d on e.deptno=d.deptno where e.ename='张飞';
---------------------------
d、自然方式查询
备注：自然方式查询与上面两种方式的区别：省去了去除笛卡尔积的操作
select * from 表1 别名1 natural join 表2 别名2;
-------------------------
select * from emp e natural join dept d where e.name='张飞';
使用自然方式存在一些缺点：这种方式虽然完成了自动去积的操作，但是它同时把两张表的主外键进行了合并
DDD对于外连接知识的学习
备注：由于内连接存在查询过程中将一些数据丢失的问题，这里引入了外连接的知识
例如：上面的表中数据张三，由于没有隶属的部门，采用去积操作的时候，就被派出了
或者采用其他去积条件的时候仍然会丢失其他数据
1、分类
a、左外连接查询b、右外连接查询c、全外连接查询
2、左外连接的学习
a、语法格式
select * from 表1 别名1 left outer join 表2 别名2 on 去积条件
-------------------------
b、具体操作实现
查询所有员工的信息及其对应的部门信息
select * from emp e left outer join dept d on e.deptno=d.deptno;
-----------------------------
查询所有员工及其直接领导的姓名和员工编号
select * from emp e1 left outer join emp e2 on e1.mgr=e2.empno;
-----------------------------
3、右外连接的学习
a、语法格式
select * from 表1 别名1 right outer join 表2 别名2 on 去积条件;
------------------------------
b、对于上面想要达到的查询目的使用右外连接也可以实现
查询所有员工信息及所对应的部门信息
分析：由于存在员工不存存在隶属部门，所以想要把员工都显示出来，应当把员工表放在右边
select * from dept d right outer join emp e on e.deptno=d.deptno;
------------------------------
查询所有员工和他的直接上的名字和员工编号
分析：由于有的员工不存在直接上级，所以讲代表领导的那一张员工表放在右边
select e1.ename,e1.mgr,e2.ename,e2.empno from emp e1 right outer join emp e2 on e1.mgr=e2.empno; e2表作为了领导表
-----------------------------
4、全外连接的知识
备注：根据上面对左外连接和右外连接的学习，仍然会出现一种特殊情况，即：将左右两边的数据都显示出来
a、语法结构
select * from emp full outer join dept on emp.deptno=dept.deptno;
--------------------------------------
b、但是这种全外连接的方式，在MySql中不支持
但是存在一种代替方式
使用union（去除重复）进行合并结果集的操作，即：将左外连接和右外连接的结果接进行union连接
5、总备注
a、对于单独的左外连接和右外连接，哪一张表放在左边或右边，它的数据就可以都显示出来，另一端的表没哟相关记录的时候
则使用null补足
b、对于全外连接下面的第一个测试是不正确的，进行union操作的时候要满足，列数相同、列类型相同的条件
c、左外连接：左边表数据完整，右边表数据不够，使用null补充
右外连接：右边表数据完整，左边表数据不够，使用null补充
###Mysql优化
1、影响所有语句的一个因素是:你的许可设置得越复杂,所需要的开销越多。执行GRANT语句时使用简单的许可,当客户执行语句时,可以使MySQL降低许可检查开销。例如,如果未授予任何表级或列级权限,服务器不需要检查tables_priv和columns_priv表的内容。同样地,如果不对任何 账户进行限制,服务器不需要对资源进行统计。如果查询量很高,可以花一些时间使用简化的授权结构来降低许可检查开销。
2、Where语句优化
a、去除不必要的括号:
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
b、常量重叠:
(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
c、去除常量条件(由于常量重叠需要):
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
将重叠条件组合成一个最终的范围。
d、取消总是true或false的条件
3、DISTINCT子句可以视为GROUP BY的特殊情况
271
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
