MySQL数据库的常用约束条件、修改表结构、MySQL键值索引的用法及操作。
| 常用约束条件 | |
| 条件约束 | 功能描述 |
| null | 默认，允许为空 |
not null | 不允许为空 |
| key | 索引类型 |
| default | 设置默认值，缺省为null |
用法举例：
create table school.restrict(name char(5) not null,gender enum("male","female") not null default "male",age int(3) not null default 21,interest set("book","movie","eat")); //新建表
insert into school.restrict(name) values ("tom"); //指定输入字段内容，不输入其他项，其他字段使用默认值。| 修改表结构 | |
| 执行动作 | 功能描述 |
| add | 添加字段 |
| modify | 修改字段类型 |
| change | 修改自定名称 |
| drop | 删除字段 |
| rename | 修改表名称 |
用法举例：
alter table school.restrict add email varchar(30); //添加字段 alter table school.restrict add phone varchar(30) not null after name; //添加字段，且放置在name之前。（after） alter table school.restrict add addr varchar(30) not null default 'beijing' first; //添加字段，且放置在所有字段之前。（first） alter table school.restrict modify addr varchar(50) default 'shanghai' after name; //修改字段addr，且放置在name之前。 alter table school.restrict change name myname varchar(10); //修改字段名称，且同时修改字段类型长度。name重命名为myname alter table school.restrict drop interest; //删除字段interest。 alter table school.restrict rename school.rest; //重命名数据表名称。restrict命名为rest。
| 键值的类型 | |
| index | 普通索引 |
| unique | 唯一索引 |
| fulltext | 全文索引 |
| primary key | 主键 |
| foreign key | 外键 |
索引就是对数据表中的若干字段进行排序的方法。其底层原理为：二叉树。
优点：
通过创建唯一性索引，可用保证数据库表中的每一行数据的唯一性。
可用加快数据的检索速度。
缺点：
当对表中的数据进行增加、删除、修改时，索引需要动态维护，会降低数据库的写速度。
索引需要额外的物理空间。
普通索引用法举例：
mysql> create table school.info(
-> id int(6) not null,
-> name varchar(5),
-> sex enum ('male','female'),
-> age int(3) default 17,
-> index(id),index(name)); //添加索引字段，index(字段)
Query OK, 0 rows affected (0.28 sec)
mysql> desc school.info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(6) | NO | MUL | NULL | |
| name | varchar(5) | YES | MUL | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | 17 | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec) //index的key字段标志为MUL
mysql> show index from school.info \G; //查询表中设置index参数的字段。在已有的数据表中创建或删除索引：
create index age on school.info(age); //新增 create index nianling on school.info(age); //给age索引命名 drop index name on school.info; //删除表中name的索引
primary key主键索引：
一个表中只能有一个primary key字段
对应的字段值不允许由重复，且不允许赋NULL值
如果由多个字段都作为primary key，则称为符合主键，必须一起创建
主键字段的KEY标志是PRI
通常与AUTO_INCREMENT连用
经常把表中能够唯一标识记录的字段设置为主键字段（记录编号字段）
用法举例：
mysql> create table school.student(
-> stu_id char(9),
-> name char(5),
-> primary key(stu_id)); //定义主键字段
Query OK, 0 rows affected (0.26 sec)
mysql> desc school.student;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| stu_id | char(9) | NO | PRI | NULL | |
| name | char(5) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec) //PRI标识
insert into school.student values('123456789','test'); //添加数据在已经存在的数据表中添加或删除主键索引：
create table school.std( id char(9), name char(5)); //创建数据表 alter table school.std add primary key(id); //add添加主键 alter table school.std drop primary key; //drop删除整张表中的主键
创建复合主键：
姓名和单位不能重复，但是单独的姓名或单位可以重复
create table school.book( 姓名 char(10), 单位 char(20), 年龄 char(3), primary key(姓名,单位)); //添加两条字段
自动添加属性（数据自动+1，自增长）：
mysql> create table school.demo(
-> id int(100) auto_increment primary key, //新建数据写auto_increment
-> name char(10));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into school.demo(name) values('a'); //只写name
Query OK, 1 row affected (0.12 sec)
mysql> insert into school.demo(name) values('b');
Query OK, 1 row affected (0.02 sec)
mysql> insert into school.demo(name) values('c');
Query OK, 1 row affected (0.02 sec)
mysql> insert into school.demo(name) values('d');
Query OK, 1 row affected (0.02 sec)
mysql> select * from school.demo; //查询数据，自增长数值默认为最大的数值+1
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
4 rows in set (0.00 sec)foreign key 外键索引：
外键就是让当前表字段的值在另一个表中的某个字段值的范围内选择。
举例：
一个作者信息表；一个图书信息表；要求图书表中的图书作者必须是作者表中的作者。
mysql> create database press character set utf8; //创建新库
Query OK, 1 row affected (0.01 sec)
mysql> create table press.au( //创建作者表
-> 姓名 char(10) primary key,
-> 地址 char(10));
Query OK, 0 rows affected (0.21 sec)
mysql> insert into press.au values //添加数据
-> ('曹雪芹','苏州'),
-> ('罗贯中','山西');
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> create table prees.bk( //创建新表
-> 书名 char(20),
-> 作者 char(10),
-> foreign key(作者)references press.au(姓名) //写外键规则：本表的作者信息，存在与另一表的姓名产生的依赖关系
-> on update cascade on delete cascade); //开启同步更新和同步删除
mysql> insert into press.bk values ('红楼梦','匿名') ; //添加失败，因au表中不存在‘匿名’报错
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`press`.`bk`, CONSTRAINT `bk_ibfk_1` FOREIGN KEY (`作者`) REFERENCES `au` (`姓名`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> insert into press.bk values ('红楼梦','曹雪芹') ; //添加成功
Query OK, 1 row affected (0.03 sec)
mysql> delete from press.au where 姓名='曹雪芹'; //删除数据后，两表中的书名、人名均被删除。删除外键：
删除外键需要按照外键名称删除，先查询外键名称。
mysql> show create table press.bk\G; //查询数据表 *************************** 1. row *************************** Table: bk Create Table: CREATE TABLE `bk` ( `书名` char(20) DEFAULT NULL, `作者` char(10) DEFAULT NULL, KEY `作者` (`作者`), CONSTRAINT `bk_ibfk_1` FOREIGN KEY (`作者`) REFERENCES `au` (`姓名`) //外键名称为bk_ibfk_1 ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql> alter table press.bk drop foreign key bk_ibfk_1 ; //删除外键
在现有的数据表中创建外键：
mysql> create table press.bok( //新建数据表 -> 书名 char(20), -> 作者 char(10)); Query OK, 0 rows affected (0.34 sec) mysql> alter table press.bok -> add //add添加 -> foreign key(作者) references press.au(姓名) //编写外键规则 -> on update cascade //开启同步更新 -> on delete cascade; //开启同步删除
发表评论