zzzzy09

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
MYSQL 第七章 约束、函数和运算符

MySQL约束概述

在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。

例如，在数据表中存放年龄的值时，如果存入 200、300 这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。

在 MySQL 中，主要支持以下 6 种约束：

1）主键约束

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。

主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

2）外键约束

外键约束经常和主键约束一起使用，用来确保数据的一致性。

例如，一个水果摊，只有苹果、桃子、李子、西瓜 4 种水果，那么，你来到水果摊要买水果只能选择苹果、桃子、李子和西瓜，不能购买其它的水果。

3）唯一约束

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。

例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

4）检查约束

检查约束是用来检查数据表中，字段值是否有效的一个手段。

例如，学生信息表中的年龄字段是没有负数的，并且数值也是有限制的。如果是大学生，年龄一般应该在 18~30 岁之间。在设置字段的检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

5）非空约束

非空约束用来约束表中的字段不能为空。例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

6）默认值约束

默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。

例如，在注册学生信息时，如果不输入学生的性别，那么会默认设置一个性别或者输入一个“未知”。

默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

以上 6 种约束中，一个数据表中只能有一个主键约束，其它约束可以有多个。

MySQL主键（PRIMARY KEY）

主键（PRIMARY KEY）的完整称呼是“主键约束”，是 MySQL 中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。

主键分为单字段主键和多字段联合主键，本节将分别讲解这两种主键约束的创建、修改和删除。

使用主键应注意以下几点：

    • 每个表只能定义一个主键。
    • 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。
    • 一个字段名只能在联合主键字段表中出现一次。
    • 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

 

主键

mysql> create table tb_emp6 (
    -> id int(11),
    -> name varchar(25),
    -> deptID int(10),
    -> salary float，
    -> primary key (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc tb_emp6;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptID | int(10)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

 

联合主键

mysql> create table tb_emp7 (
    -> id int(11),
    -> name varchar(24),
    -> deptID int(20),
    -> salary float,
    -> primary key(id, deptID)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc tb_emp7;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(24) | YES  |     | NULL    |       |
| deptID | int(20)     | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

 

 

修改表时添加主键约束

mysql> create table tb_emp3 (
    -> id int(10),
    -> name varchar(20),
    -> deptID int(11),
    -> salary float
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc tb_emp3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(10)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| deptID | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table tb_emp3 add primary key (id,deptID);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_emp3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(10)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| deptID | int(11)     | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

 删除主键约束

mysql> desc tb_emp7;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(24) | YES  |     | NULL    |       |
| deptID | int(20)     | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 
mysql> alter table tb_emp7 drop primary key;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_emp7;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(24) | YES  |     | NULL    |       |
| deptID | int(20)     | NO   |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>

 

 

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。语法格式如下：

字段名 数据类型 AUTO_INCREMENT

    • 默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
    • 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
    • AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
    • AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。
    • AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。
mysql> create table tb_sss ( id int(4) primary key auto_increment, name varchar(20) not null );
Query OK, 0 rows affected (0.02 sec)

mysql> desc tb_sss
    -> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> select * from tb_sss;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
+----+------+
3 rows in set (0.00 sec)

指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加。

mysql> create table tb_a ( id int not null auto_increment, name varchar(20) not null, primary key(id)) auto_increment=100;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from tb_a;
Empty set (0.00 sec)


mysql> insert into tb_a(name) values('ccc');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_a;
+-----+------+
| id  | name |
+-----+------+
| 100 | ccc  |
+-----+------+
1 row in set (0.00 sec)

mysql>

主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

定义外键时，需要遵守下列规则：

    • 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
    • 必须为主表定义主键。
    • 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
    • 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
    • 外键中列的数目必须和主表的主键中列的数目相同。
    • 外键中列的数据类型必须和主表主键中对应列的数据类型相同。
mysql> create table tb_for ( id int(11) primary key, name varchar(25), deptId int(11), salary float, constraint test_foreign foreign key(deptId) references tb_1(id) );
ERROR 1050 (42S01): Table 'tb_for' already exists
mysql> desc tb_for;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc tb-1；
    -> ;
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 '-1；' at line 1
mysql> desc tb_1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

 MySQL 唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。如果其中一条记录的 id 值为‘0001’，那么该表中就不能出现另一条记录的 id 值也为‘0001’。

唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束。

mysql> create table tb_2 (
    -> id int(10) primary key,
    -> name varchar(25) unique,
    -> location varchar(40)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc tb_2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | NO   | PRI | NULL    |       |
| name     | varchar(25) | YES  | UNI | NULL    |       |
| location | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

在修改表时添加唯一约束，删除唯一约束

mysql> desc tb_3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | NO   | PRI | NULL    |       |
| name     | varchar(25) | YES  |     | NULL    |       |
| location | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb_3 add constraint con_1 unique(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | NO   | PRI | NULL    |       |
| name     | varchar(25) | YES  | UNI | NULL    |       |
| location | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb_3 drop index name;
ERROR 1091 (42000): Can't DROP 'name'; check that column/key exists
mysql> alter table tb_3 drop index con_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | NO   | PRI | NULL    |       |
| name     | varchar(25) | YES  |     | NULL    |       |
| location | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

 

选取设置检查约束的字段 增删改查

检查约束使用 CHECK 关键字，具体的语法格式如下：

CHECK <表达式>

其中，“表达式”指的就是 SQL 表达式，用于指定需要检查的限定条件。

若将 CHECK 约束子句置于表中某个列的定义之后，则这种约束也称为基于列的 CHECK 约束。

在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。MySQL 可以使用简单的表达式来实现 CHECK 约束，也允许使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。

mysql> create table tb_4 ( id int(11) primary key, name varchar(25) not null, deptId int(20), salary float, check(salary>0 and salary<100) );

 

 

默认值（Default）的完整称呼是“默认值约束（Default Constraint）”，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。

例如，员工信息表中，部门位置在北京的较多，那么部门位置就可以默认为“北京”，系统就会自动为这个字段赋值为“北京”。

默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

mysql> create table tb_7 ( id int(11) primary key, name varchar(25) not null, location varchar(50)  );
Query OK, 0 rows affected (0.02 sec)

mysql> alter table tb_7 change column location location varchar(50) default 'BeiJing';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_7;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(25) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | BeiJing |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb_7 change column location location varchar(50) default NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tb_7;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(25) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc tb_7;

 

 

在创建表时设置非空约束

创建表时可以使用 NOT NULL 关键字设置非空约束，具体的语法格式如下：

<字段名> <数据类型> NOT NULL;

mysql> create table tb_7 ( id int(11) primary key, name varchar(25) not null );
Query OK, 0 rows affected (0.03 sec)

mysql> alter table tb_7 change column mame name varchar(25) null;
ERROR 1054 (42S22): Unknown column 'mame' in 'tb_7'
mysql> alter table tb_7 change column name name varchar(25) null;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> alter table tb_7 change column name name varchar(25) not null;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(25) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 查看数据表中的约束语法格式如下：

SHOW CREATE TABLE <数据表名>;

mysql> create table tb_8 ( id int(11) primary key, name varchar(22) unique, deptId int(11) not null, salary float default 0 );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+--------------+
| Tables_in_t1 |
+--------------+
| tb_7         |
| tb_8         |
+--------------+
2 rows in set (0.00 sec)

mysql> show create table tb_8 \G
*************************** 1. row ***************************
       Table: tb_8
Create Table: CREATE TABLE `tb_8` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `deptId` int(11) NOT NULL,
  `salary` float DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table tb_8 add constraint cons_t 
    -> foreign key(deptId) 
    -> references tb_7(id);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_8 \G
*************************** 1. row ***************************
       Table: tb_8
Create Table: CREATE TABLE `tb_8` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `deptId` int(11) NOT NULL,
  `salary` float DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `cons_t` (`deptId`),
  CONSTRAINT `cons_t` FOREIGN KEY (`deptId`) REFERENCES `tb_7` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

MySQL常用运算符概述

MySQL 所提供的运算符可以直接对表中数据或字段进行运算，进而实现用户的新需求，增强了 MySQL 的功能。

每种数据库都支持 SQL 语句，但是它们也都有各自支持的运算符。我们除了需要学会使用 SQL 语句外，还需要掌握各种运算符。 

MySQL 支持 4 种运算符，分别是：

1) 算术运算符

执行算术运算，例如：加、减、乘、除等。

2) 比较运算符

包括大于、小于、等于或不等于、等等。主要用于数值的比较、字符串的匹配等方面。

3) 逻辑运算符

包括与、或、非和异或、等逻辑运算符。其返回值为布尔型，真值（1 或 true）和假值（0 或 false）。

4) 位运算符

包括按位与、按位或、按位取反、按位异或、按位左移和按位右移等位运算符。位运算必须先将数据转换为补码，然后在根据数据的补码进行操作。运算完成后，将得到的值转换为原来的类型（十进制数），返回给用户。

mysql> create table temp(num int);
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+--------------+
| Tables_in_t1 |
+--------------+
| tb_7         |
| tb_8         |
| temp         |
+--------------+
3 rows in set (0.00 sec)

mysql> insert into temp values(64);
Query OK, 1 row affected (0.01 sec)

mysql> select * from temp;
+------+
| num  |
+------+
|   64 |
+------+
1 row in set (0.00 sec)

mysql> select num,num+100,num+3-6,num-100 from temp;
+------+---------+---------+---------+
| num  | num+100 | num+3-6 | num-100 |
+------+---------+---------+---------+
|   64 |     164 |      61 |     -36 |
+------+---------+---------+---------+
1 row in set (0.00 sec)

mysql>

 

posted on 2020-06-08 16:34  zzzzy09  阅读(317)  评论(0)  编辑  收藏  举报