SQL语言高级

ゝ一世哀愁。 2022-06-08 23:09 240阅读 0赞

**一、数据的完整性**

作用：保证用户输入的数据保存到数据库中是正确的。  
确保数据的完整性 = 在创建表时给表中添加约束  
完整性的分类：

1.  实体完整性
2.  域完整性
3.  引用完整性

*1、实体完整性*

实体：即表中的一行(一条记录)代表一个实体（entity）  
实体完整性的作用：标识每一行数据不重复。  
约束类型： 主键约束（primary key） 唯一约束(unique) 自动增长列(auto\_increment)

1.1 主键约束（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);

1.2 唯一约束(unique)

特点：数据不能重复。

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

1.3 自动增长列(auto_increment)

*  mysql数据库(auto\_increment)
 *  sqlserver数据库 (identity)
 *  oracle数据库( sequence)

给主键添加自动增长的数值，列只能是整数类型
        CREATE TABLE student(
            Id int primary key auto_increment,
            Name varchar(50)
        );
        INSERT INTO student(name) values(‘tom’);

*2、域完整性*

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较  
域代表当前单元格  
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)  
check约束（mysql不支持）check(sex=’男’ or sex=’女’)

1.1 数据类型:（数值类型、日期类型、字符串类型）
    
    1.2 非空约束：not null

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

1.3 默认值约束 default

CREATE TABLE student(
            Id int pirmary key,
            Name varchar(50) not null,
            Sex varchar(10) default ‘男’
        );
        insert into student1 values(1,'tom','女');
        insert into student1 values(2,'jerry',default);

*3、引用完整性（参照完整性）*

外键约束：FOREIGN KEY

例：
        CREATE TABLE student(
            sid int pirmary key,
            name varchar(50) not null,
            sex varchar(10) default ‘男’
        );
    
        create table score(
            id int,
            score int,
            sid int , -- 外键列的数据类型一定要与主键的类型一致
            CONSTRAINT fk_score_sid foreign key (sid) references student(id)
        );
    第二种添加外键方式
        ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

*4、表与表之间的关系*

*  一对一  
    
    
     *  例如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表的外键。

![表与表之间的关系][SouthEast]

**二、多表查询（\*）**

多表查询有如下几种：

*  合并结果集；UNION 、 UNION ALL
 *  连接查询  
    
    
     *  内连接 \[INNER\] JOIN ON
     *  外连接 OUTER JOIN ON  
        
        
         *  左外连接 LEFT \[OUTER\] JOIN
         *  右外连接 RIGHT \[OUTER\] JOIN
         *  全外连接（MySQL不支持）FULL JOIN  
            
            
             *  自然连接 NATURAL JOIN
 *  子查询

*1、合并结果集*

1.  作用：合并结果集就是把两个select语句的查询结果合并到一起！
2.  要求：被合并的两个结果：列数、列类型必须相同。
3.  合并结果集有两种方式：

UNION：去除重复记录
        SELECT * FROM t1 UNION SELECT * FROM t2；
    UNION ALL：不去除重复记录
        SELECT * FROM t1 UNION ALL SELECT * FROM t2。

![UNION][]  
![UNION ALL][]

*2、连接查询 （重要）*

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1\*t2。  
![连接查询][SouthEast 1]  
连接查询会产生笛卡尔积，假设集合A=\{a,b\}，集合B=\{0,1,2\}，则两个集合的笛卡尔积为\{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)\}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

例如：  
![例子][SouthEast 2]

使用主外键关系做为条件来去除无用信息
        SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

![使用主外键来去除无用的信息][SouthEast 3]

上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。
        SELECT emp.ename,emp.sal,emp.comm,dept.dname 
        FROM emp,dept 
        WHERE emp.deptno=dept.deptno;

![使用条件进行筛选][SouthEast 4]

还可以为表指定别名，然后在引用列时使用别名即可
        SELECT e.ename,e.sal,e.comm,d.dname 
        FROM emp AS e,dept AS d
        WHERE e.deptno=d.deptno;

2.1　内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：
        SELECT * FROM emp e 
        INNER JOIN dept d ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。

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

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

左连接：
        SELECT * FROM emp e 
        LEFT OUTER JOIN dept d 
        ON e.deptno=d.deptno;
    
    左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

右连接：
    右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。
    
    例如：
        在dept表中的40部门并不存在员工，但在右连接中，
        如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL
        SELECT * FROM emp e 
        RIGHT OUTER JOIN dept d 
        ON e.deptno=d.deptno;

![查询][SouthEast 5]

连接查询心得：  
连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。  
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

*3、自然连接*

连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式： 两张连接的表中名称和类型完全一致的列作为条件

例如：
    emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！
    当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！
    
        SELECT * FROM emp NATURAL JOIN dept;
        SELECT * FROM emp NATURAL LEFT JOIN dept;
        SELECT * FROM emp NATURAL RIGHT JOIN dept;

*4、子查询(非常重要)*

一个select语句中包含另一个完整的select语句。  
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

*  子查询出现的位置  
    
    
     *  where后，作为条为被查询的一条件的一部分；
     *  from后，作为表；
 *  当子查询出现在where后作为条件时，还可以使用如下关键字  
    
    
     *  any
     *  all
 *  子查询结果集的形式  
    
    
     *  单行单列（用于条件）
     *  单行多列（用于条件）
     *  多行单列（用于条件）
     *  多行多列（用于表）

*6、自连接:自己连接自己，起别名*

**三、MySQL中的函数**  
![时间日期相关函数][SouthEast 6]

![字符串相关函数][SouthEast 7]

![数学相关函数][SouthEast 8]

**四、MySQL数据库的备份与恢复**

*1、生成SQL脚本——导出数据*

在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。

mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

![mysql生成SQL脚本][mysql_SQL]  
现在可以在C盘下找到mydb1.sql文件了！

注意，mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

*2、执行SQL脚本 恢复数据*

前提：必须先创建数据库名  
执行SQL脚本需要登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！  
执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！

SOURCE C:\mydb1.sql

![执行SQL脚本][SQL]

注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！  
例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！

还可以通过下面的方式来执行脚本文件：

mysql –u用户名 –p密码 数据库<要执行脚本文件路径
    
    mysql -uroot -p123 mydb1<c:\mydb1.sql

![执行SQL脚本][SQL 1]

这种方式无需登录mysql！  
注意：在CMD下 命令不能加“；”号

[SouthEast]: /images/20220609/08bbcf7442a54f8c8c3f88c8f5bac908.png
[UNION]: /images/20220609/540ade1907954d7d99ed136d38d857d5.png
[UNION ALL]: /images/20220609/a67bd17f874a4171a4708914fecd33d1.png
[SouthEast 1]: /images/20220609/e83f97574f704fd7b1d19ebff51e05ab.png
[SouthEast 2]: /images/20220609/f8f5558ae98f411fb208c16f4ac1d5d1.png
[SouthEast 3]: /images/20220609/1e942e1645ab4c5d8d328a4ea53d6fa2.png
[SouthEast 4]: /images/20220609/14b9a89b83b54bf5ab5908cae8c9386b.png
[SouthEast 5]: /images/20220609/f584c3a0fd47417082269eee0d1eb404.png
[SouthEast 6]: /images/20220609/3437527e58764d2fa32d85e8505c7a7f.png
[SouthEast 7]: /images/20220609/4fb654af8ac040d89a155fbbbf1f5c9b.png
[SouthEast 8]: /images/20220609/2ab0b9b096154d29880f1b48f04c2a92.png
[mysql_SQL]: /images/20220609/d0aa1304edd14fbeb595617c5bd62c2d.png
[SQL]: /images/20220609/cc98e5dd97d74a6a8ca181da6f6778f3.png
[SQL 1]: /images/20220609/04ce7df6bf434d8cb8498bdac552b455.png