学习任务列表：
	一、字段约束条件
		1.1 作用：限制字段赋值
		1.2 约束条件包括 4部分
			是否允许为空(null)
			键值
			默认值 
			额外设置
mysql> create table db1.t23 (class  char(7), name char(10) not null default  "" , age int not null  default 399 , likes  set("a","b","c","d") default "a,b");
Query OK, 0 rows affected (0.01 sec)

mysql> desc db1.t23;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| class | char(7)              | YES  |     | NULL    |       |
| name  | char(10)             | NO   |     |         |       |
| age   | int(11)              | NO   |     | 399     |       |
| likes | set('a','b','c','d') | YES  |     | a,b     |       |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into db1.t23(class) values ("nsd2002");
Query OK, 1 row affected (0.01 sec)

mysql> select  * from db1.t23;
+---------+------+-----+-------+
| class   | name | age | likes |
+---------+------+-----+-------+
| nsd2002 |      | 399 | a,b   |
+---------+------+-----+-------+
1 row in set (0.00 sec)

mysql> insert into db1.t23(class,name,age) values ("nsd2002","bob",37);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t23;
+---------+------+-----+-------+
| class   | name | age | likes |
+---------+------+-----+-------+
| nsd2002 |      | 399 | a,b   |
| nsd2002 | bob  |  37 | a,b   |
+---------+------+-----+-------+
2 rows in set (0.00 sec)

mysql> insert into db1.t23 values ("nsd2002",null,null,"b,c,d");
ERROR 1048 (23000): Column 'name' cannot be null
mysql> 
mysql> insert into db1.t23 values ("nsd2002","nb",null,"b,c,d");
ERROR 1048 (23000): Column 'age' cannot be null
mysql> 
mysql> insert into db1.t23 values ("nsd2002","nb",25,"b,c,d");
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert into db1.t23 values ("nsd2002","dc",19,null);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t23;
+---------+------+-----+-------+
| class   | name | age | likes |
+---------+------+-----+-------+
| nsd2002 |      | 399 | a,b   |
| nsd2002 | bob  |  37 | a,b   |
| nsd2002 | nb   |  25 | b,c,d |
| nsd2002 | dc   |  19 | NULL  |
+---------+------+-----+-------+
4 rows in set (0.00 sec)

mysql> );
		1.2 修改表结构
			alter table 库.表 操作动作；

			添加新字段  add
mysql> alter table  db1.t2 add age int not null default 19 ;
mysql> alter table  db1.t2 add  school char(10) default "tarena" first;
mysql> alter table  db1.t2 add class char(7) after name ;


			删除字段    drop
mysql> alter table  db1.t2 drop school , drop class ;
mysql> select  * from  db1.t2;
mysql> desc  db1.t2;
			修改字段类型 modify

mysql> alter table  db1.t2  modify  name varchar(5) not null;
mysql> alter table  db1.t2 modify name varchar(2) not null  after email ;
			修改字段名   change
mysql> alter table  db1.t2 change email  mail varchar(15);
mysql> alter table  db1.t2 change  home address  varchar(30) ;

			
			修改表名     rename
			mysql> alter table  t2 rename teacher;		
			mysql> show tables;


	二、MySQL键值
		种类：
		普通索引
		唯一索引
		主键
		外键
		全文索引

		2.1 普通索引index的使用
			1 什么是索引
			2 优/缺点

			3 普通索引Index的使用
				3.1 使用规则
				3.2 创建索引
					建表时创建索引
		mysql> create table db1.t24(name char(10) , age  int  ,  class char(7) , sex enum("g","b") , index(name) , index(class) );

		mysql> db1.t24;
					 在已有表里创建索引
		mysql> create index  xh on t3(money);
		mysql> desc  db1.t3;
	
				3.3 查看索引
					desc  库.表；
					show index from 库.表；

				3.4 删除索引
				drop index class on db1.t24; 
				drop  index  xh on  t3;

		主键的使用
			1 主键使用规则
			2 主键的使用
				2.1 建表时创建主键
mysql> create table db1.t25 ( name char(10) primary key , class char(7) , age int );

mysql> create table db1.t26 ( name char(10) , class char(7) , age int , primary key(name))；

mysql> insert into  db1.t26 values ("tom" , "nsd2003" , 17);
ERROR 1062 (23000): Duplicate entry 'tom' for key 'PRIMARY'
mysql> insert into  db1.t26 values ("toma" , "nsd2003" , 17);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t26;
+------+---------+------+
| name | class   | age  |
+------+---------+------+
| tom  | nsd2002 |   19 |
| toma | nsd2003 |   17 |
+------+---------+------+
2 rows in set (0.00 sec)

mysql> 
			在已有表里创建主键
	        mysql> delete from  teacher;
		mysql> alter table  teacher add primary key(address);

	
			删除主键
		mysql> alter table  teacher drop  primary key ;	
                MySQL> desc  teacher;

		
		多个字段都作为主键，称为复合主键，必须一起创建
  
  PRI	   PRI
  cip      port   status
 1.1.1.1   22      yes
 1.1.1.1   80      no
 2.1.1.1   80      yes

create table db1.t27(cip char(15) , port  smallint , status enum("yes","no") , primary key(cip,port)  );

mysql> insert into db1.t27 values("1.1.1.1" , 22 , "yes");
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.t27 values("1.1.1.1" , 22 , "yes");
ERROR 1062 (23000): Duplicate entry '1.1.1.1-22' for key 'PRIMARY'
mysql> 
mysql> insert into db1.t27 values("1.1.1.1" , 80 , "yes");
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.t27 values("1.1.1.1" , 80 , "no");
ERROR 1062 (23000): Duplicate entry '1.1.1.1-80' for key 'PRIMARY'
mysql> 
mysql> insert into db1.t27 values("2.1.1.1" , 80 , "no");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t27;
+---------+------+--------+
| cip     | port | status |
+---------+------+--------+
| 1.1.1.1 |   22 | yes    |
| 1.1.1.1 |   80 | yes    |
| 2.1.1.1 |   80 | no     |
+---------+------+--------+

  主键通常与auto_increment 连用
		  auto_increment：让字段通过自己加1的方式赋值

mysql> create table db1.t28(id int primary key  auto_increment , name char(10),home varchar(15), class char(7));
Query OK, 0 rows affected (0.04 sec)

mysql> desc db1.t28;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | char(10)    | YES  |     | NULL    |                |
| home  | varchar(15) | YES  |     | NULL    |                |
| class | char(7)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into db1.t28(name , home , class ) values ("bob","usa","nsd2002") ;
Query OK, 1 row affected (0.00 sec)

mysql> select  * from  db1.t28;
+----+------+------+---------+
| id | name | home | class   |
+----+------+------+---------+
|  1 | bob  | usa  | nsd2002 |
+----+------+------+---------+
1 row in set (0.00 sec)

mysql> insert into db1.t28(name , home , class ) values ("tom","usa","nsd2002") ;
Query OK, 1 row affected (0.01 sec)

mysql> select  * from  db1.t28;
+----+------+------+---------+
| id | name | home | class   |
+----+------+------+---------+
|  1 | bob  | usa  | nsd2002 |
|  2 | tom  | usa  | nsd2002 |
+----+------+------+---------+
2 rows in set (0.00 sec)

mysql> insert into db1.t28(name , home , class ) values ("lucy","usa","nsd2002") ;
Query OK, 1 row affected (0.00 sec)

mysql> select  * from  db1.t28;
+----+------+------+---------+
| id | name | home | class   |
+----+------+------+---------+
|  1 | bob  | usa  | nsd2002 |
|  2 | tom  | usa  | nsd2002 |
|  3 | lucy | usa  | nsd2002 |
+----+------+------+---------+
3 rows in set (0.00 sec)

mysql> insert into db1.t28(name , home , class ) values ("john","usa","nsd2002") ;
Query OK, 1 row affected (0.01 sec)

mysql> select  * from  db1.t28;
+----+------+------+---------+
| id | name | home | class   |
+----+------+------+---------+
|  1 | bob  | usa  | nsd2002 |
|  2 | tom  | usa  | nsd2002 |
|  3 | lucy | usa  | nsd2002 |
|  4 | john | usa  | nsd2002 |
+----+------+------+---------+
4 rows in set (0.00 sec)

mysql> select  * from db1.t28 where id=2;
+----+------+------+---------+
| id | name | home | class   |
+----+------+------+---------+
|  2 | tom  | usa  | nsd2002 |
+----+------+------+---------+
1 row in set (0.01 sec)

mysql> insert into db1.t28 values (3,"john","usa","nsd2002") ;
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> insert into db1.t28 values (7,"jeryy","usa","nsd2002") ;
Query OK, 1 row affected (0.01 sec)

mysql> select  * from db1.t28 where id=2;
+----+------+------+---------+
| id | name | home | class   |
+----+------+------+---------+
|  2 | tom  | usa  | nsd2002 |
+----+------+------+---------+
1 row in set (0.00 sec)

mysql> select  * from db1.t28 ;
+----+-------+------+---------+
| id | name  | home | class   |
+----+-------+------+---------+
|  1 | bob   | usa  | nsd2002 |
|  2 | tom   | usa  | nsd2002 |
|  3 | lucy  | usa  | nsd2002 |
|  4 | john  | usa  | nsd2002 |
|  7 | jeryy | usa  | nsd2002 |
+----+-------+------+---------+
5 rows in set (0.00 sec)

mysql> insert into db1.t28(name , home , class ) values ("lili","usa","nsd2002") ;
Query OK, 1 row affected (0.01 sec)

mysql> select  * from db1.t28 ;
+----+-------+------+---------+
| id | name  | home | class   |
+----+-------+------+---------+
|  1 | bob   | usa  | nsd2002 |
|  2 | tom   | usa  | nsd2002 |
|  3 | lucy  | usa  | nsd2002 |
|  4 | john  | usa  | nsd2002 |
|  7 | jeryy | usa  | nsd2002 |
|  8 | lili  | usa  | nsd2002 |
+----+-------+------+---------+
6 rows in set (0.00 sec)

mysql> insert into db1.t28 values (null,"alices","usa","nsd2002") ;
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t28 ;
+----+--------+------+---------+
| id | name   | home | class   |
+----+--------+------+---------+
|  1 | bob    | usa  | nsd2002 |
|  2 | tom    | usa  | nsd2002 |
|  3 | lucy   | usa  | nsd2002 |
|  4 | john   | usa  | nsd2002 |
|  7 | jeryy  | usa  | nsd2002 |
|  8 | lili   | usa  | nsd2002 |
|  9 | alices | usa  | nsd2002 |
+----+--------+------+---------+
7 rows in set (0.00 sec)

mysql>

mysql> alter table  db1.t6 add num  int  primary key  auto_increment first;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select  * from t6;
+-----+-------+------+------------+----------+---------------------+
| num | name  | csnf | birthday   | upclass  | party               |
+-----+-------+------+------------+----------+---------------------+
|   1 | bob   | 1990 | 2020-11-20 | 09:00:00 | 2020-06-18 20:30:00 |
|   2 | tom   | 2023 | NULL       | NULL     | NULL                |
|   3 | john  | 1988 | NULL       | NULL     | NULL                |
|   4 | jerry | 2020 | 2020-05-15 | 18:26:14 | 2020-05-15 18:26:14 |
+-----+-------+------+------------+----------+---------------------+
4 rows in set (0.01 sec)

mysql> select  * from t6 where num = 3;
+-----+------+------+----------+---------+-------+
| num | name | csnf | birthday | upclass | party |
+-----+------+------+----------+---------+-------+
|   3 | john | 1988 | NULL     | NULL    | NULL  |
+-----+------+------+----------+---------+-------+
1 row in set (0.00 sec)

mysql> 

外键功能
插入记录时，字段值在另一个表字段值范围内选择。
使用规则
表存储引擎必须是innodb
字段类型要一致
被参照字段必须要是索引类型的一种(primary key)

mysql> create table db1.yg_tab(yg_id int primary key auto_increment,name char(10))engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> desc db1.yg_tab;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| yg_id | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


mysql> insert into db1.yg_tab(name) values ("bob");
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.yg_tab(name) values ("tom");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.yg_tab;
+-------+------+
| yg_id | name |
+-------+------+
|     1 | bob  |
|     2 | tom  |
+-------+------+
2 rows in set (0.00 sec)

mysql> 

mysql> create table db1.gz (gz_id  int , gz int , foreign key(gz_id) references yg_tab(yg_id) on update cascade  on delete cascade  )engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> desc db1.gz;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| gz_id | int(11) | YES  | MUL | NULL    |       |
| gz    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>
mysql> show create table db1.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) DEFAULT NULL,
  `gz` int(11) DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> 
mysql> select  * from db1.gz;
Empty set (0.00 sec)

mysql> 

验证外键
mysql> insert into db1.gz values (3,35000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> 
mysql> insert into db1.gz values (1,35000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into db1.gz values (2,45000);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.gz;
+-------+-------+
| gz_id | gz    |
+-------+-------+
|     1 | 35000 |
|     2 | 45000 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> 

验正on update cascade 的功能
mysql> select  * from db1.yg_tab;
+-------+------+
| yg_id | name |
+-------+------+
|     1 | bob  |
|     2 | tom  |
+-------+------+
2 rows in set (0.00 sec)

mysql> select  * from db1.gz;
+-------+-------+
| gz_id | gz    |
+-------+-------+
|     1 | 35000 |
|     2 | 45000 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> update db1.yg_tab  set  yg_id=8 where yg_id=2;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select  * from db1.gz;
+-------+-------+
| gz_id | gz    |
+-------+-------+
|     1 | 35000 |
|     8 | 45000 |
+-------+-------+
2 rows in set (0.00 sec)

mysql>

验证 on  delete  cascade 的功能

mysql> 
mysql> select  * from db1.yg_tab;
+-------+------+
| yg_id | name |
+-------+------+
|     1 | bob  |
|     8 | tom  |
+-------+------+
2 rows in set (0.01 sec)

mysql> select  * from db1.gz;
+-------+-------+
| gz_id | gz    |
+-------+-------+
|     1 | 35000 |
|     8 | 45000 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> delete from db1.yg_tab where yg_id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.yg_tab;
+-------+------+
| yg_id | name |
+-------+------+
|     8 | tom  |
+-------+------+
1 row in set (0.00 sec)

mysql> select  * from db1.gz;
+-------+-------+
| gz_id | gz    |
+-------+-------+
|     8 | 45000 |
+-------+-------+
1 row in set (0.00 sec)

mysql> 



完善 修改表结构
mysql> insert into db1.gz values(8,65000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.gz values(8,65000);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.gz;
+-------+-------+
| gz_id | gz    |
+-------+-------+
|     8 | 45000 |
|     8 | 65000 |
|     8 | 65000 |
|     8 | 65000 |
+-------+-------+
4 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> insert into db1.gz values(null,55000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into db1.gz values(null,55000);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.gz;
+-------+-------+
| gz_id | gz    |
+-------+-------+
|     8 | 45000 |
|     8 | 65000 |
|     8 | 65000 |
|     8 | 65000 |
|  NULL | 55000 |
|  NULL | 55000 |
+-------+-------+
6 rows in set (0.00 sec)

mysql> desc db1.gz;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| gz_id | int(11) | YES  | MUL | NULL    |       |
| gz    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> delete from db1.gz;



mysql> select  * from db1.gz;
Empty set (0.00 sec)

mysql> desc db1.gz;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| gz_id | int(11) | YES  | MUL | NULL    |       |
| gz    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table db1.gz add primary key(gz_id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.gz;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| gz_id | int(11) | NO   | PRI | NULL    |       |
| gz    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into db1.gz values(null,55000);
ERROR 1048 (23000): Column 'gz_id' cannot be null
mysql> 
mysql> insert into db1.gz values(7,55000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> 
mysql> insert into db1.gz values(8,55000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.gz values(8,55000);
ERROR 1062 (23000): Duplicate entry '8' for key 'PRIMARY'
mysql> 
mysql> 

删除外键

mysql> insert into db1.gz values(9,55000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> 
mysql> show create table db1.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) NOT NULL,
  `gz` int(11) DEFAULT NULL,
  PRIMARY KEY (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table  db1.gz drop foreign key gz_ibfk_1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> show create table db1.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) NOT NULL,
  `gz` int(11) DEFAULT NULL,
  PRIMARY KEY (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into db1.gz values(9,55000);
Query OK, 1 row affected (0.01 sec)

mysql>