Java初学笔记36-【数据库】【mysql 约束，primary key，not null，unique，foreign key，check，自增长，mysql 索引，主键，唯一，普通，全文索引】

二十、mysql 约束

1. 介绍

约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括: not null、unique、primary key、foreign key和check五种。

2. primary key(主键)

（1）写法

字段名  字段类型	primary key

（2）作用

用于唯一的标识表行的数据，当定义主键约束后，该列不能重复

（3）细节

【1】primary key不能重复而且不能为null。
【2】一张表最多只能有一个主键,但可以是复合主键
【3】主键的指定方式有两种：直接在字段名后指定: 字段名 primakry key；在表定义最后写primary key(列名);
【4】使用 desc 表名，可以看到primary key的情况。
【5】在实际开发中，每个表往往都会设计一个主键。

（4）举例

– 复合主键 (id 和 name 做成复合主键)

CREATE TABLE t18
(id INT , ` name ` VARCHAR(32),
email VARCHAR(32), PRIMARY KEY (id, `name`) -- 这里就是复合主键
);

– 1. 直接在字段名后指定：字段名 primakry key
– 2. 在表定义最后写 primary key(列名);

CREATE TABLE t19
(id INT , `name` VARCHAR(32) PRIMARY KEY, email VARCHAR(32)
);

CREATE TABLE t20
(id INT , `name` VARCHAR(32) , email VARCHAR(32), PRIMARY KEY(`name`) 
-- 在表定义最后写 primary key(列名)
);

3. not null(非空)

（1）写法

字段名  字段类型  not null

（2）作用

如果在列上定义了not null,那么当插入数据时，必须为列提供数据。

4. unique(唯一)

（1）写法

字段名  字段类型  unique

（2）作用

当定义了唯一约束后，该列值是不能重复的.。

（3）细节

【1】如果没有指定 not null，则unique字段可以有多个null
【2】一张表可以有多个unique字段
【3】如果一个列(字段) 是 unique not null，使用效果类似primary key。

5. foreign key(外键)

（1）写法

FOREIGN KEY (本表字段名) REFERENCES 主表名(主键名或unique字段名)

（2）作用

用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主
键列存在或是为null。

（3）细节

【1】从表中外键所指向的主表的字段，要求是primary key或者是unique
【2】表的类型是innodb，这样的表才支持外键
【3】外键字段的类型要和主键字段的类型一致(长度可以不同)
【4】外键字段的值，必须在主键字段中出现过，或者为null。 [前提是外键字段允许为null]
【5】一旦建立主外键的关系，数据不能随意删除了.

（4）实例

6. check

（1）写法

列名    类型   check (check条件)

（2）作用

用于强制行数据必须满足的条件,假定在薪水sal列上定义了check约束,并要求sal列值在1000 ~ 2000之间，如果不再1000 ~ 2000之间就会提示出错。另外oracle 和sql server 均支持check ,但是mysq15.7
目前还不支持check ,只做语法校验，但不会生效。

（3）示例

7. 自增长

（1）作用

在某张表中，存在一个id列(整数)，在添加记录的时候，该列从1开始，每加一个数据，自动的增长

（2）写法

字段名  int  primary key auto_ increment

添加自增长的字段方式

insert into xXX (字段1,字段2,....)  values(null, '值'....);
insert into Xx (字段2,.... )values('值2',....);
insert into xxx values(null, '值1',....)

（3）细节

（1）一般来说自增长是和primary key配合使用的
（2）自增长也可以单独使用[但是需要配合一个unique
（3）自增长修饰的字段为整数型的(虽然小数也可以但是很少这样使用)
（4）自增长默认从1开始，也可以通过如下命令修改alter table 表名 auto increment =新的开始值;
（5）如果添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据。

（4）实例

8. 练习

二十一、mysql 索引

1. 索引的快速入门

（1）索引本身也占用空间

-- 索引快速入门
-- 创建测试数据库

`temp`

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

DELIMITER $$

#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 DECLARE chars_str VARCHAR(100) DEFAULT
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0; 
 WHILE i < n DO
    # concat 函数 : 连接函数mysql函数
   SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
   SET i = i + 1;
   END WHILE;
  RETURN return_str;
  END $$


 #这里我们又自定了一个函数,返回一个随机的部门号
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(10+RAND()*500);
RETURN i;
END $$

 #创建一个存储过程， 可以添加雇员
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
 #autocommit = 0 含义: 不要自动提交
 SET autocommit = 0; #默认不提交sql语句
 REPEAT
 SET i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
 INSERT INTO emp VALUES ((START+i) 
 ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
  UNTIL i = max_num
 END REPEAT;
 #commit整体提交所有sql语句，提高效率
   COMMIT;
 END $$

 #添加8000000数据
CALL insert_emp(100001,8000000)$$

#命令结束符，再重新设置为;
DELIMITER ;


-- 测试速度
SELECT * FROM emp WHERE empno = 1234567   -- 9.316秒

-- 创建索引
CREATE INDEX empno_index ON emp (empno);

SELECT * FROM emp WHERE empno = 1234567   -- 0.082秒

2. 索引的原理

（1）没有索引，查询时全盘扫描，花费时间很多
（2）有了索引，会生成索引的二叉树，查询时花费的时间就很少。
（3）索引的代价：占用磁盘空间，对update delete insert语句的执行效率有影响。

3. 索引的类型

（1）主键索引，有主键为主索引(类型Primary key)
（2）唯一索引，使用unique(UNIQUE)
（3）普通索引(INDEX)
（4）全文索引(FULLTEXT) [适用于MyISAM]

4. 索引的使用

（1）注意：一般开发，不使用mysql自带的全文索引，而是使用下面两个框架：
全文搜索Solr和ElasticSearch (ES)
（2）如果某列的值，是不会重复的，则优先考虑使用unique索引，否则使用普通索引
（3）添加索引的方法：

-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id); 
-- 添加普通索引方式1
CREATE INDEX id_index ON t25 (id)
-- 添加普通索引方式 2
ALTER TABLE t25 ADD INDEX id_index (id)

（4）查找索引的方法

-- 方法1
SHOW INDEX FROM t25
-- 方法2
SHOW KEYS FROM t25

（5）删除索引

-- 删除普通索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY

5. 什么列适合使用索引

（1）较频繁的作为查询条件字段，应该创建索引

select * from emp where empno = 1

（2）唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select * from emp where sex = '男

（3）更新非常频繁的宇段不适合创建索引

select * from emp where logincount = 1

（4）不会出现在WHERE子句中字段不该创建索引