本文主要是介绍数据库_MySql高级，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、数据完整性（约束）

1.概念：

• 数据完整性：保证数据库中的数据是准确有效的；
• 要实现数据的准确性和有效性，就需要我们在创建数据表的时候，给表的数据增加限制条件（在限制数据类型和大小的基础上额外增加），这种限制条件即约束；

2.完整性分类

• 实体完整性（行完整性）：
• 域完整性（列完整性）：
• 引用完整性（关联表完整性）：
  • 主键约束：primary key
  • 唯一约束：unique [key]
  • 非空约束：notnull
  • 默认约束：default
  • 自动增长：auto_increment
  • 外键约束：foreign key
• 多个约束条件之间用空格间隔（建议在创建表时进行约束设置）

2.1实体完整性

• 数据表中的每一行数据为一个实体（一条数据）
• 实体完整性：两条数据的同一列的内容不重复
• 约束分类：
  • 主键约束：primary key
  • 唯一约束：unique [key]
  • 自动增长：auto_increment

（1）主键约束：primary key

• 每个表中要有一个主键
• 主键值不可重复，不能为空（null）
• 添加方式

  • 定义时添加：
    CREATE TABLE student( id int primary key, name varchar(50) );

    mysql> create database hello;
    Query OK, 1 row affected (0.62 sec)
    
    mysql> use hello;
    Database changed
    mysql> create table student(id int primary key,name varchar(5));
    Query OK, 0 rows affected (1.33 sec)
    

  • 所有列定义完成后添加（可创建联合主键）：
    CREATE TABLE student( classid int, stuid int, name varchar(50), primary key(classid,stuid) );

    mysql> drop table student;
    Query OK, 0 rows affected (0.92 sec)
    
    mysql> create table student(id int,classid int,name varchar(10),primary key(id,classid));
    Query OK, 0 rows affected (1.50 sec)
    

  • 通过修改添加约束：
    CREATE TABLE student( id int, name varchar(50) );
    ALTER TABLE student ADD PRIMARY KEY (id);

    mysql> drop table student;
    Query OK, 0 rows affected (0.64 sec)
    
    mysql> create table student(id int,name varchar(4));
    Query OK, 0 rows affected (1.20 sec)
    
    mysql> alter table student add primary key(id);
    Query OK, 0 rows affected (0.88 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

（2）唯一约束：unique

• 数据不能重复

  CREATE TABLE student( Id int primary key, Name varchar(50) unique );
  mysql> drop table student;
  Query OK, 0 rows affected (0.66 sec)
  
  mysql> create table student(id int primary key,name varchar(5) unique);
  Query OK, 0 rows affected (1.07 sec)
  

（3）自动增长列：auto_increment

• 添加数据时自动增长数值

• 列必须为整数类型
  CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );
  INSERT INTO student(name) values(‘tom’);
  mysql> create table student(id int primary key auto_increment,name varchar(5));
  Query OK, 0 rows affected (0.36 sec)

  mysql> create table student(id int primary key auto_increment,name varchar(5));
  Query OK, 0 rows affected (0.36 sec)
  mysql> insert into student(name) values('刘强东');
  Query OK, 1 row affected (0.49 sec)
  
  mysql> select * from student;
  +----+-----------+
  | id | name      |
  +----+-----------+
  |  1 | 刘强东    |
  +----+-----------+
  1 row in set (0.00 sec)
  

可以看到，id列数据自动补充

2.2域完整性

• 限制单元格（当前行当前列对应的唯一单元格）数据
• 域完整性约束：
  • 数据类型：
  • 非空约束：not null;
  • 默认值约束：default
  • check约束：MySQL不支持

（1）数据类型（引用自菜鸟教程www.runoob.com）

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

（2）非空约束：not null

mysql> create table student(id int primary key,name varchar(5) not null);
Query OK, 0 rows affected (1.55 sec)

mysql> insert into student values(1001,'大毛');
Query OK, 1 row affected (0.55 sec)

mysql> insert into student(id) values(1001);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

可以看到，添加同学大毛没有问题，但是只添加id，不添加name会报错

（3）默认值约束：default

格式：列名 default ‘默认值’

mysql> alter table student change name name varchar(5) default '空白名';
Query OK, 0 rows affected (1.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into student(id) values(8080);
Query OK, 1 row affected (0.52 sec)

mysql> select * from student;
+------+-----------+
| id   | name      |
+------+-----------+
| 1001 | 大毛      |
| 8080 | 空白名    |
+------+-----------+
2 rows in set (0.00 sec)

可以看到，设置默认值约束后，添加数据未设置域内的值时，自动填充为默认值；

2.3引用完整性

外键约束

• foreign key
• 格式：constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
• 外键列的数据类型一定要与主键列相同（参考的主键表的主键列）

create table student(studentid int primary key,stuname varchar(5),classid int);
Query OK, 0 rows affected (1.09 sec)
mysql> create table socore(id int primary key auto_increment,stuid int,constraint haha foreign key(stuid) references student(studentid));
Query OK, 0 rows affected (0.67 sec)

这样，就把score的stuid设置为指向student里studentid列的外键

或者，通过修改添加外键：

mysql> create table class(classid int primary key,classname varchar(10));
Query OK, 0 rows affected (0.72 sec)

mysql> create table student(stuid int primary key,studentname varchar(5),classid int);
Query OK, 0 rows affected (0.50 sec)

mysql> alter table student add constraint hehe foreign key(classid) references class(classid);
Query OK, 0 rows affected (2.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意：外键指向的一定是另一个表的主键

二、多表查询

1.多表关系

• 一对多/多对一关系：
  • 原则：在多的一方创建字段（列），作为外键指向一方的主键；
  • 例：学生表classid对应班级表classid，多个学生，对应一个classid；
• 一对一关系
  • 实现1：一方创建外键指向一的一方主键，外键设置uique约束；
  • 实现2：一方主键作为外键指向另一方
  • 一对一可以创建在一张表中，故应用不多

2.多表查询

• 合并结果集：union、union all
• 连接查询：
  • 内连接[inner] join on
  • 左外连接 left [outer] join
  • 右外连接 right [outer] join
  • 全外连接 full join（MySQL不支持）
  • 自然连接：natural join
• 子查询

2.1合并结果集

• 作用：将查询结果合并在一起（数据条数）（列数和列的类型需要相同）

• union：去除重复记录

• union all：不去除重复数据

• 例：

  # 使用union查询
  mysql> select name,depid from employee union select name,depid from employee;
  +--------+-------+
  | name   | depid |
  +--------+-------+
  | 张三   | 111   |
  | 李四   | 111   |
  | 王五   | 222   |
  | 赵六   | 222   |
  | 前妻   | 333   |
  +--------+-------+
  5 rows in set (0.00 sec)
  # 使用union all查询
  mysql> select name,depid from employee union all select name,depid from employee;
  +--------+-------+
  | name   | depid |
  +--------+-------+
  | 张三   | 111   |
  | 李四   | 111   |
  | 王五   | 222   |
  | 赵六   | 222   |
  | 前妻   | 333   |
  | 张三   | 111   |
  | 李四   | 111   |
  | 王五   | 222   |
  | 赵六   | 222   |
  | 前妻   | 333   |
  +--------+-------+
  10 rows in set (0.00 sec)
  

2.2连接查询

• 连接查询是指将两个表的数据每一行连接在一起

• 例：表a,有数据a1,a2,a3;表b有数据b1,b2,b3;连接查询后结果相当于a*b，即a1+b1,a1+b2,a1+b3,a2+b1,a2+b2,a2+b3,a3+b1,a3+b2,a3+b3;

• 实例：

  mysql> select * from employee;
  +-------+--------+------+-----------------+------------+-------+
  | empid | name   | sex  | title           | birthday   | depid |
  +-------+--------+------+-----------------+------------+-------+
  | 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   |
  | 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   |
  | 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   |
  | 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   |
  | 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   |
  +-------+--------+------+-----------------+------------+-------+
  5 rows in set (0.00 sec)
  
  mysql> select * from department;
  +-------+-----------+
  | depid | depname   |
  +-------+-----------+
  | 111   | 生产部    |
  | 222   | 销售部    |
  | 333   | 人事部    |
  +-------+-----------+
  3 rows in set (0.00 sec)
  
  mysql> select * from,employee,department;
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',employee,department' at line 1
  mysql> select * from employee,department;
  +-------+--------+------+-----------------+------------+-------+-------+-----------+
  | empid | name   | sex  | title           | birthday   | depid | depid | depname   |
  +-------+--------+------+-----------------+------------+-------+-------+-----------+
  | 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   | 111   | 生产部    |
  | 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   | 222   | 销售部    |
  | 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   | 333   | 人事部    |
  | 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   | 111   | 生产部    |
  | 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   | 222   | 销售部    |
  | 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   | 333   | 人事部    |
  | 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   | 111   | 生产部    |
  | 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   | 222   | 销售部    |
  | 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   | 333   | 人事部    |
  | 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   | 111   | 生产部    |
  | 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   | 222   | 销售部    |
  | 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   | 333   | 人事部    |
  | 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   | 111   | 生产部    |
  | 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   | 222   | 销售部    |
  | 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   | 333   | 人事部    |
  +-------+--------+------+-----------------+------------+-------+-------+-----------+
  15 rows in set (0.00 sec)
  

• 这种产生无用数据的相乘行为结果成为笛卡尔积，可通过 表1外键=表二主键 这种条件将无用数据筛选掉；

• 例：

  mysql> select * from employee e,department d where e.depid=d.depid;
  +-------+--------+------+-----------------+------------+-------+-------+-----------+
  | empid | name   | sex  | title           | birthday   | depid | depid | depname   |
  +-------+--------+------+-----------------+------------+-------+-------+-----------+
  | 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   | 111   | 生产部    |
  | 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   | 111   | 生产部    |
  | 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   | 222   | 销售部    |
  | 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   | 222   | 销售部    |
  | 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   | 333   | 人事部    |
  +-------+--------+------+-----------------+------------+-------+-------+-----------+
  5 rows in set (0.00 sec)
  

  这样就筛选掉了无用数据，但是存在depid这样的相同列，可以通过下面学到的自然查询删除；

（1）内连接

• 语法：select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名
• 例：

  mysql> select * from employee e inner join department d on e.depid=d.depid;
  +-------+--------+------+-----------------+------------+-------+-------+-----------+
  | empid | name   | sex  | title           | birthday   | depid | depid | depname   |
  +-------+--------+------+-----------------+------------+-------+-------+-----------+
  | 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   | 111   | 生产部    |
  | 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   | 111   | 生产部    |
  | 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   | 222   | 销售部    |
  | 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   | 222   | 销售部    |
  | 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   | 333   | 人事部    |
  +-------+--------+------+-----------------+------------+-------+-------+-----------+
  5 rows in set (0.00 sec)
  

  on后面的语句就相当于where后面的条件

（2）外连接

• 左外连接：主表 left [out] join 次表 on 主表.列名=次表.列名;
• 右外连接：主表 right [out] join 次表 on 主表.列名=次表.列名;
• 特点：主表全部显示，匹配不到次表中的数据时，次表显示null;
• left join 表示主表在左侧，右侧接次表

mysql> select * from employee e left join department d on e.depid=d.depid;
+-------+--------+------+-----------------+------------+-------+-------+-----------+
| empid | name   | sex  | title           | birthday   | depid | depid | depname   |
+-------+--------+------+-----------------+------------+-------+-------+-----------+
| 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   | 111   | 生产部    |
| 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   | 111   | 生产部    |
| 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   | 222   | 销售部    |
| 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   | 222   | 销售部    |
| 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   | 333   | 人事部    |
+-------+--------+------+-----------------+------------+-------+-------+-----------+
5 rows in set (0.00 sec)

on后面的格式为规范填写，但并不固定，但是on语句必须填写：

mysql> select * from employee e left join department d on 1=1;
+-------+--------+------+-----------------+------------+-------+-------+-----------+
| empid | name   | sex  | title           | birthday   | depid | depid | depname   |
+-------+--------+------+-----------------+------------+-------+-------+-----------+
| 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   | 111   | 生产部    |
| 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   | 222   | 销售部    |
| 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 111   | 333   | 人事部    |
| 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   | 111   | 生产部    |
| 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   | 222   | 销售部    |
| 1002  | 李四   | 女   | 助工            | 1985-01-01 | 111   | 333   | 人事部    |
| 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   | 111   | 生产部    |
| 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   | 222   | 销售部    |
| 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 222   | 333   | 人事部    |
| 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   | 111   | 生产部    |
| 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   | 222   | 销售部    |
| 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 222   | 333   | 人事部    |
| 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   | 111   | 生产部    |
| 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   | 222   | 销售部    |
| 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 333   | 333   | 人事部    |
+-------+--------+------+-----------------+------------+-------+-------+-----------+
15 rows in set (0.00 sec)

（3）自然连接：natural+连接语句

• 特点：必须具有相同的属性列（列名相同，并且会消除相同列）
• 自然内连接：natural [inner] join

mysql> select * from employee e natural join department d;
+-------+-------+--------+------+-----------------+------------+-----------+
| depid | empid | name   | sex  | title           | birthday   | depname   |
+-------+-------+--------+------+-----------------+------------+-----------+
| 111   | 1001  | 张三   | 男   | 高级工程师      | 1975-01-01 | 生产部    |
| 111   | 1002  | 李四   | 女   | 助工            | 1985-01-01 | 生产部    |
| 222   | 1003  | 王五   | 男   | 工程师          | 1978-11-11 | 销售部    |
| 222   | 1004  | 赵六   | 男   | 工程师          | 1979-01-01 | 销售部    |
| 333   | 1005  | 前妻   | 男   | 工程师          | 1999-01-02 | 人事部    |
+-------+-------+--------+------+-----------------+------------+-----------+
5 rows in set (0.00 sec)

可以看到，不仅消除了重复列depid，并且不会产生笛卡尔积，非常滴好用

• 自然外连接：natural left/right [outer] join

mysql> SELECT * FROM department d NATURAL LEFT JOIN employee e NATURAL LEFT JOIN salary;
+-------+-------+-----------+--------+------+-----------------+------------+------------+-------------+-----------+
| empid | depid | depname   | name   | sex  | title           | birthday   | basesalary | titlesalary | deduction |
+-------+-------+-----------+--------+------+-----------------+------------+------------+-------------+-----------+
| 1001  | 111   | 生产部    | 张三   | 男   | 高级工程师      | 1975-01-01 |       2200 |        1100 |       200 |
| 1002  | 111   | 生产部    | 李四   | 女   | 助工            | 1985-01-01 |       1200 |         200 |       100 |
| 1003  | 222   | 销售部    | 王五   | 男   | 工程师          | 1978-11-11 |       1900 |         700 |       200 |
| 1004  | 222   | 销售部    | 赵六   | 男   | 工程师          | 1979-01-01 |       1950 |         700 |       150 |
| 1005  | 333   | 人事部    | 前妻   | 男   | 工程师          | 1999-01-02 |       1850 |         600 |       100 |
+-------+-------+-----------+--------+------+-----------------+------------+------------+-------------+-----------+
5 rows in set (0.00 sec)

(4)子查询

• 一个查询语句中包含其它查询语句，可以理解为嵌套查询：

mysql> # 查询比销售部门所有员工基本工资都高的雇员姓名
mysql> SELECT e.name FROM employee e,salary s WHERE e.empid=s.empid AND s.basesalary>
    (SELECT MAX(basesalary) FROM employee e,salary s,department d
    WHERE e.empid=s.empid AND e.depid=d.depid AND d.depname='销售部');
+--------+
| name   |
+--------+
| 张三   |
+--------+
1 row in set (0.00 sec)

三、结束

这篇关于数据库_MySql高级的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！