（五）MySQL约束和运算符

约束

• 查看表中约束

• SHOW CREATE TABLE <数据表名>;

主键约束

#创建表时设置单字段主键
#定义字段的同时指定主键  <字段名> <数据类型> PRIMARY KEY [默认值]
mysql> CREATE TABLE tb_emp3
    -> (
    -> id INT(11) PRIMARY KEY,
    ->...
#或者在定义完所有字段之后指定主键  [CONSTRAINT <约束名>] PRIMARY KEY [字段名]
mysql> CREATE TABLE tb_emp4
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> PRIMARY KEY(id)
    -> );

#创建表时设置联合主键  PRIMARY KEY [字段1，字段2，…,字段n]
mysql> CREATE TABLE tb_emp5
    -> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );

#在修改数据表时添加主键约束 
ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

#删除主键约束
ALTER TABLE <数据表名> DROP PRIMARY KEY;

主键自增长（ AUTO_INCREMENT）

mysql> CREATE TABLE student100 (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> PRIMARY KEY(ID)
    -> )AUTO_INCREMENT=100;

• 默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
• AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
• AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。
• AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。
• 末尾的AUTO_INCREMENT=100，为可选。若没有这个语句则自增从1 开始，有了则从100 开始

  INSERT INTO student100(name) VALUES('Java'),('MySQL'),('Python');

  

外键约束（FOREIGN KEY） 

• 外键约束可以在定义表时设置，但只能定义为表级约束
• ON DELETE

唯一约束（UNIQUE）

• 确保不是主键的属性不会出现重复数据
• 唯一约束和主键约束的区别：

1. 一个表只能定义一个主键约束，但是可以定义多个唯一约束
2. 主键约束不允许属性值为空，而唯一性约束的属性允许为空
3. 定义唯一约束时，同样会在属性列上自动创建索引，加快查询速度

检查约束（CHECK）

默认值约束（DEFAULT）

• 向表中插入数据时，若用户没有给明确给出属性值，数据库会自动为其添加默认值

非空约束（NOT NULL）

• 确保属性列无空值，不设定则默认允许为空
• 修改表时添加：ALTER TABLE 表名 MODIFY 属性名 数据类型 NOT NULL;
• 删除约束：ALTER TABLE 表名 MODIFY 属性名 数据类型 NULL；

运算符

• 按表达式顺序从左到右依次计算，列表优先级由高到低

• 运算符

  !

  -(负号）、〜（位反转）

  ^

  *、/、%

  -(减号）、+

  <<、>>

  &

  |

  =(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN

  BETWEEN、CASE、WHEN、THEN、ELSE

  NOT

  &&、AND

  XOR

  II、OR

  =(赋值运算）、:=

   

比较运算符

MySQL 中的比较运算符

运算符	作用
=	等于
<=>	安全的等于
<> 或者 !=	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL 或者 ISNULL	判断一个值是否为空
IS NOT NULL	判断一个值是否不为空
BETWEEN AND	判断一个值是否落在两个值之间

算术运算符

• 算术运算，加（+）、减（-）、乘（*）、除（/）、求余（%MOD）

CREATE TABLE temp(num INT);
INSERT INTO temp VALUE (64);
SELECT num,num+10,num-3+5,num+36.5 FROM temp;

#返回结果
+------+--------+---------+----------+
| num  | num+10 | num-3+5 | num+36.5 |
+------+--------+---------+----------+
|   64 |     74 |      66 |    100.5 |
+------+--------+---------+----------+

 SELECT num,num*2,num/2,num/3,num%3 FROM temp;

+------+-------+---------+---------+-------+
| num  | num*2 | num/2   | num/3   | num%3 |
+------+-------+---------+---------+-------+
|   64 |   128 | 32.0000 | 21.3333 |     1 |
+------+-------+---------+---------+-------+

#其中num%3，和 MOD(num,3) 运行结果相同

逻辑运算符

• 布尔运算符，用来确定表达式的真和假。

MySQL 中的逻辑运算符

运算符	作用
NOT 或者 !	逻辑非
AND 或者 &&	逻辑与
OR 和 ||	逻辑或
XOR	逻辑异或

• 返回结果不同是由于优先级导致的。not 1+1 等于not (1+1)，！1+1 等于（！1）+1。

位运算符

MySQL 中的位运算符

运算符	说明	使用形式	举例
|	位或	a | b	5 | 8
&	位与	a & b	5 & 8
^	位异或	a ^ b	5 ^ 8
~	位取反	~a	~5
<<	位左移	a << b	5 << 2，表示整数 5 按位左移 2 位
>>	位右移	a >> b	5 >> 2，表示整数 5 按位右移 2 位

IN和NOT IN

• 判断表达式的值是否位于给出的列表中；如果是，返回值为 1，否则返回值为 0。

mysql>  SELECT NULL IN (1,3,5,'thks'),10 IN (1,10,NULL,'thks');
+------------------------+--------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,10,NULL,'thks') |
+------------------------+--------------------------+
|                   NULL |                        1 |
+------------------------+--------------------------+