数据库查询

数据的完整性

作⽤：保证⽤户输⼊的数据保存到数据库中是正确的。确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：实体完整性,域完整性,引⽤完整性.

实体完整性

​ 实体定义
即表中的⼀⾏(⼀条记录)代表⼀个实体（entity）实体完整性的作⽤：标识每⼀⾏数据不重复。
​ 约束类型
主键约束（primary key）
唯⼀约束(unique)
⾃动增⻓列(auto_increment)

主键约束（primary key）

注：每个表中要有⼀个主键。特点：数据唯⼀，且不能为null
例：第⼀种添加⽅式：
CREATE TABLE student(
id int primary key,
name varchar(50)
);
第⼆种添加⽅式：此种⽅式优势在于，可以创建联合主键
CREATE TABLE student(
id int,
name varchar(50),
primary key(id)
);
CREATE TABLE student(
classid int,
stuid int,
name varchar(50),
primary key(classid,stuid)
);
第三种添加⽅式：单独添加主键
CREATE TABLE student(
id int,
name varchar(50)
);
ALTER TABLE student ADD PRIMARY KEY (id);

唯⼀约束(unique)

特点：数据不能重复。(可⻅null不算重复)

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) unique
);

⾃动增⻓列(auto_increment)

sqlserver数据库 (identity) oracle数据库( sequence)
特点:默认从最⼤值开始+1操作
给主键添加⾃动增⻓的数值，列只能是整数类型

CREATE TABLE student(
    Id int primary key auto_increment,
    Name varchar(50)
);
INSERT INTO student(name) values('tom');

域完整性

​ 域完整性的作⽤：
限制此单元格的数据正确，不对照此列的其它单元格⽐较,域代表当前单元格
​ 域完整性约束分类：
数据类型
⾮空约束（not null）
默认值约束(default)

check约束（mysql不⽀持）check(sex=‘男’ or sex=‘⼥’)
详情

数据类型:

数值类型、⽇期类型、字符串类型

⾮空约束：not null

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) not null,
    Sex varchar(10)
);
INSERT INTO student values(1,'tom',null);

默认值约束 default

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) not null,
    Sex varchar(10) default '男'
);
insert into student values(1,'tom','⼥');
insert into student values(2,'jerry',default);

引⽤完整性（参照完整性）

外键约束：FOREIGN KEY

例：CREATE TABLE stu(
    sid int primary key,
    name varchar(50) not null,
    sex varchar(10) default '男'
);
create table score(
    id int primary key,
    score int,
    sid int , -- 外键列的数据类型⼀定要与主键的类型⼀致
	foreign key (sid) references stu(sid)
);
-- 撤销外键
先选中外键所在的表,在右下⻆找到info选项,拉到最后,找到创建score表的信息,我们可以发现主外键关联的标识score_ibfk_1
ALTER TABLE score DROP FOREIGN KEY score_ibfk_1
第⼆种添加外键⽅式。
ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(sid);

表与表之间的关系

⼀对⼀
例如t_person表和t_card表，即⼈和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。⼈可以没有身份证，但身份证必须要有⼈才⾏，所以⼈是主表，⽽身份证是从表。设计从表可以有两种⽅案：在t_card表中添加外键列（相对t_user表），并且给外键添加唯⼀约束；给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。
⼀对多（多对⼀）
最为常⻅的就是⼀对多！⼀对多和多对⼀，这是从哪个⻆度去看得出来的。t_user和t_section的关系，从t_user来看就是⼀对多，⽽从t_section的⻆度来看就是多对⼀！这种情况都是在多⽅创建外键！
多对多
例如t_stu和t_teacher表，即⼀个学⽣可以有多个⽼师，⽽⼀个⽼师也可以有多个学⽣。这种情况通常需要创建中间表来处理多对多关系。例如再创建⼀张表t_stu_tea表，给出两个外键，⼀个相对t_stu表的外键，另⼀个相对t_teacher表的外键。

多表查询

多表查询分类
合并结果集 UNION 、UNION ALL
了解连接查询内连接 [INNER] JOIN ON
外连接 OUTER JOIN ON
左外连接 LEFT [OUTER] JOIN
右外连接 RIGHT [OUTER] JOIN
全外连接（MySQL不⽀持）FULL JOIN
⾃然连接 NATURAL JOIN

合并结果集

作⽤
合并结果集就是把两个select语句的查询结果合并到⼀起！
要求：被合并的两个结果：列数、列类型必须相同。

-- 注意:在进⾏合并结果集时,两个表的字段个数和类型必须⼀致
-- 合并结果集时不是对原表进⾏合并,是对查出来的虚拟表进⾏合并
-- 当类型有⾼低等级,会转成⾼等级类型
-- 对于union会⾃动进⾏去重
-- 对于union all 不会去重

连接查询

创建表并添加数据(有主外键关系)

直接连接查询两个表

SELECT * FROM student stu,scores sco

我们发现通过stu连接sco，查询出的结果就是stuxco,即(2x3=6条记录)

结果出现了多个表的乘积,我们称为笛卡尔积.这⾥⾯有错误的数据.
假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。那么多表查询产⽣这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。stu⼀共2⾏记录，sco表⼀共3⾏记录，那么连接后查询出的结果是6⾏记录。你只是想在查询stu表的同时，把每个学员的成绩显示出来，那么就需要使⽤主外键来去除
⽆⽤信息了。

​ 使⽤主外键关系做为条件来去除⽆⽤信息
注意:多表查询,如果⼀个字段的名字是唯⼀的,不需要使⽤.语法,可以直接使⽤.

但是如果多表中同⼀个字段出现了多次,要使⽤.语法
这⾥直接使⽤的是默认的查询⽅式–相当于内连接

SELECT * FROM student stu,scores sco WHERE stu.id=sco.id 

内连接
上⾯的连接语句就是内连接，但它不是SQL标准中的查询⽅式，可以理解为⽅⾔！

总结:内连接的特点：只能查出两个表都有的id记录,如果⼀个记录只有主表有,副表没有,使⽤内连接,⽆法查出这条记录

外连接（左外连接、右外连接）

外连接的特点：查询出的结果存在不满⾜条件的可能。
左外连接

SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满⾜条件的显示出来，不满⾜条件的显示NULL。
⼯作原理:以左表为主,左表的信息会全部查出来,右表中只能查出与左表中id相同的记录,其他的查不出.只有左表有数据,右表没有对应数据的显示null
左连接:写法 select from 表⼀ left outer(可省略) join 表⼆ on 表⼀.id=表⼆.id

右外连接
右连接:写法 select from 表⼀ right outer(可省略) join 表⼆ on 表⼀.id=表⼆.id
⼯作原理:以右表为主,右表的信息会全部查出来,左表中只能查出与右表中id相同的记录,其他的查不出,只有右表有数据,左表没有对应数据的显示null

连接查询⼼得：
连接不限于两张表，连接查询也可以是三张、四张，甚⾄N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，⽽只是需要其中⼀部分，那么这时就需要使⽤条件来去除不需要的记录。这个条件⼤多数情况下都是使⽤主外键关系去除。两张表的连接查询⼀定有⼀个主外键关系，三张表的连接查询就⼀定有两个主外键关系，所以在⼤家不是很熟悉连接查询时，⾸先要学会去除⽆⽤笛卡尔积，那么就是⽤主外键关系作为条件来处理。如果两张表的查询，那么⾄少有⼀个主外键条件，三张表连接⾄少有两个主外键条件。

⾃然连接

⼤家也都知道，连接查询会产⽣⽆⽤笛卡尔积，我们通常使⽤主外键关系等式来去除它。⽽⾃然连接⽆需你去给出主外键等式，它会⾃动找到这⼀等式：
两张连接的表中名称和类型完全⼀致的列作为条件，例如stu和sco表都存在id列，并且类型⼀致，所以会被⾃然连接找到！
当然⾃然连接还有其他的查找条件的⽅式，但其他⽅式都可能存在问题！

-- 注意:1.要有主外键,⽽且主外键的的名字要相同.如果没有主外键,他默认会找字段相同的所有字段进--- ⾏⽐较,如果没有字段相同的字段报错
-- 2.⾃然连接时,会将相同的字段合并
-- 3.如果只写natural 默认当作内连接
SELECT * FROM student stu NATURAL JOIN scores sco
SELECT * FROM student stu NATURAL LEFT JOIN scores sco
SELECT * FROM student stu NATURAL RIGHT JOIN scores sco

⼦查询

定义
⼀个select语句中包含另⼀个完整的select语句。
⼦查询就是嵌套查询，即SELECT中包含SELECT，如果⼀条语句中存在两个，或两个以上SELECT，那么就是⼦查询语句了。
⼦查询出现的位置：
where后，作为被查询条件的⼀部分；
from后，作表；
注意:当⼦查询出现在where后作为条件时，还可以使⽤如下关键字：(很少⽤)any all
⼦查询结果集的形式：
单⾏单列（⽤于条件）
单⾏多列（⽤于条件）
多⾏单列（⽤于条件）
多⾏多列（⽤于表）

⾃连接

⾃⼰连接⾃⼰，起别名

求7369员⼯编号、姓名、经理编号和经理姓名

SELECT e1.empno , e1.ename,e1.mgr,e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno AND e1.empno = 7369;

MySQL中的函数

常⽤系统函数

⽇期函数

字符串函数

常规函数

操作示例

-- mysql常⽤的函数
SELECT ADDTIME('2007-12-30 21:50:50', '1:1:1')
SELECT ADDTIME(NOW(),'1:1:1')
SELECT CURRENT_DATE()
SELECT CURRENT_TIME()
SELECT CURRENT_TIMESTAMP()
SELECT DATE(NOW())-- 获取当前时间的⽇期部分
SELECT DATE_ADD('2016-6-6' ,INTERVAL -1 DAY)
SELECT DATEDIFF('2016-6-8','2016-6-10') -- 前⾯的时间-后⾯的时间的
差值
SELECT NOW();
SELECT YEAR|MONTH|DAY(DATETIME )-- 不能这样做
SELECT YEAR(NOW())-- 单独获取年
SELECT MONTH(NOW())-- 单独获取⽉
SELECT DAY(NOW())-- 单独获取⽇
-- 对字符串的操作
SELECT CHARSET('hello') -- 当前的编码格式
SELECT CONCAT('he','lo') -- 合并字符串
SELECT *,CONCAT(ename,job) FROM emp;
SELECT INSTR('hello','e')-- 当在原字符串中找不到⼦字符串时,会返回0
SELECT LEFT('hello',2)-- -- 不能这样做
SELECT REPLACE('hello','h','wo')
SELECT STRCMP('hello','heloo') -- ⽐较两个字符串 前⾯⼤返回1 后
⾯⼤返回-1 相等返回0
SELECT LTRIM(' hello') -- 去除左边的空格
SELECT RTRIM('hello ') -- 去除右边的空格
SELECT MOD(34,0); -- 返回null

⾃定义函数

什么是函数

mysql中的函数与存储过程类似，都是⼀组SQL集；

与存储过程的区别

函数可以return值，存储过程不能直接return，但是有输出参数可以输出多个返回值；
函数可以嵌⼊到sql语句中使⽤，⽽存储过程不能；
函数⼀般⽤于实现较简单的有针对性的功能（如求绝对值、返回当前时间等），存储过程⽤于实现复杂的功能（如复杂的业务逻辑功能）；
函数的关键字是function 存储过程是:procedure

mysql中的存储过程

为什么要使⽤存储过程

mysql从1.5开始⽀持procedure
存储过程简单来说，就是为以后的使⽤⽽保存的⼀条或多条MySQL语句的集合。可将其视为批件，虽然它们的作⽤不仅限于批处理。在我看来， 存储过程就是有业务逻辑和流程的集合， 可以在存储过程中创建表，更新数据， 删除等等。通过把处理封装在容易使⽤的单元中，简化复杂的操作(正如前⾯例⼦所述)。 由于不要求反复建⽴⼀系列处理步骤，这保证了数据的完整性。如果所有开发⼈员和应⽤程序都使⽤同⼀(试验和测试)存储过程，则所使⽤的代码都是相同的。这⼀点的延伸就是防⽌错误。需要执⾏的步骤越多，出错的可能性就越⼤。防⽌错误保证了数据的⼀致性。 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使⽤它的⼈员甚⾄不需要知道这些变化。

参数说明

参数in的使⽤

(代表输⼊，意思说你的参数要传到存过过程的过程⾥⾯去)
为了避免存储过程中分号(";")结束语句，我们使⽤分隔符告诉mysql解释器,该段命令是否已经结束了。

参数out的使⽤

(代表往外输出)
这⾥还要注意⼀点的就是我们的输出参数⼀定要设置相应类型的初始，否则不管你怎么计算得出的结果都为NULL值

参数inout的使⽤

(既能输⼊⼀个值⼜能传出来⼀个值)

变量说明

mysql的变量分为两种：系统变量和⽤户变量,但是在实际使⽤中，还会有局部变量、会话变量等分法

局部变量

局部变量⼀般⽤在sql语句块中，⽐如存储过程的begin/end。其作⽤域仅限于该语句块，在该语句块执⾏完毕后，局部变量就消失了。

局部变量⼀般⽤declare来声明，可以使⽤default来说明默认值。

⽤户变量

⽤户变量的作⽤域要⽐局部变量要⼴。⽤户变量可以作⽤于当前整个连接，但是当当前连接断开后，其所定义的⽤户变量都会消失。
select @变量名
对⽤户变量赋值有两种⽅式，⼀种是直接⽤"=“号，另⼀种是⽤”:=“号。其区别在于使⽤set命令对⽤户变量进⾏赋值时，两种⽅式都可以使⽤；当使⽤select语句对⽤户变量进⾏赋值时，只能使⽤”:=“⽅式，因为在select语句中，”="号被看作是⽐较操作符。

会话变量

服务器为每个连接的客户端维护⼀系列会话变量。在客户端连接时，使⽤相应全局变量的当前值对客户端的会话变量进⾏初始化。设置会话变量不需要特殊权限，但客户端只能更改⾃⼰的会话变量，⽽不能更改其它客户端的会话变量。会话变量的作⽤域与⽤户变量⼀样，仅限于当前连接。当当前连接断开后，其设置的所有会话变量均失效。

全局变量

全局变量影响服务器整体操作。当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项⽂件中或在命令⾏中指定的选项进⾏更改。要想更改全局变量，必须具有SUPER权限。全局变量作⽤于server的整个⽣命周期，但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续⽣效，需要更改相应的配置⽂件。

MySQL数据库的备份与恢复

通过脚本实现数据的备份与恢复

⽣成SQL脚本 导出数据

在CMD下 命令不能加;

在控制台使⽤mysqldump命令可以⽤来⽣成指定数据库的脚本⽂本，但要注意，脚本⽂本中只包含数据库的内容，⽽不会存在创建数据库的语句！所以在恢复数据时，还需要⾃已⼿动创建⼀个数据库之后再去恢复数据。

mysqldump –u⽤户名 –p密码 --databases 数据库名 > ⽣成的脚本⽂件路径
例如:执⾏下⾯的终端命令
C:\Users\bihai\Desktop>mysqldump -uroot -p123456 --databases
dbtest1 > dbtest1.sql
//下⾯的是警告,不⽤管
mysqldump: [Warning] Using a password on the command line
interface can be insecure.

现在可以在桌⾯找到dbtest1.sql⽂件了！
注意，mysqldump命令是在Windows控制台下执⾏，⽆需登录mysql！！！

执⾏SQL脚本 恢复数据

前提：必须先创建数据库名
通过下⾯的⽅式来执⾏脚本⽂件：

在终端执⾏下⾯的命令
mysql -uroot -p123456 新创建的库 < c:\dbtest1.sql
mysql –u⽤户名 –p密码 数据库<要执⾏脚本⽂件路径

这种⽅式⽆需登录mysql！

通过可视化⼯具实现数据的备份与恢复

使⽤sqlyog实现数据的备份

1.选中要备份的数据库
2.右键,选中Backup Database As SQL Dump

3.在弹出的窗⼝中按照我的提示选择,最后点击按钮export,当下⾯的进度条变成绿⾊,说明导出成功

使⽤sqlyog实现数据的恢复

1.必须要新建⼀个数据库
2.选中数据库,右键选中Restore From SQL Dump,导⼊即可

mysql –u⽤户名 –p密码 数据库<要执⾏脚本⽂件路径

这种⽅式⽆需登录mysql！