mysql学习04-表
- innodb_file_per_table ## 每张表数据可以单独放到一个表空间
show variables like 'innodb_file_per_table';
- 约束 约束是为了保证数据库中的数据完整性
- 实体完整性 保证表中有一个主键。
- 域完整性 保证数据的值满足特定的条件。
参照完整性
保证两张表之间的关系。
InnoDB的约束：
Primary Key
Unique Key
Forign Key
Default
NOT NULL
示例：
create table u (id int , name varchar(20) , primary key (id) , unique key (name));
infomation_schema库的表TABLE_CONSTRAINTS存放了数据库所有约束
mysql> select * from TABLE_CONSTRAINTS;
+--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+
| def | lin | PRIMARY | lin | u | PRIMARY KEY |
| def | lin | name | lin | u | UNIQUE |
| def | lin | PRIMARY | lin | user1 | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | columns_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | db | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | engine_cost | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | event | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | func | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | gtid_executed | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | help_category | PRIMARY KEY |
| def | mysql | name | mysql | help_category | UNIQUE |
| def | mysql | PRIMARY | mysql | help_keyword | PRIMARY KEY |
| def | mysql | name | mysql | help_keyword | UNIQUE |
| def | mysql | PRIMARY | mysql | help_relation | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | help_topic | PRIMARY KEY |
| def | mysql | name | mysql | help_topic | UNIQUE |
| def | mysql | PRIMARY | mysql | innodb_index_stats | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | innodb_table_stats | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | ndb_binlog_index | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | plugin | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | proc | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | procs_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | proxies_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | server_cost | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | servers | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | slave_master_info | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | slave_relay_log_info | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | slave_worker_info | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | tables_priv | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_leap_second | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_name | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_transition | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | time_zone_transition_type | PRIMARY KEY |
| def | mysql | PRIMARY | mysql | user | PRIMARY KEY |
| def | sys | PRIMARY | sys | sys_config | PRIMARY KEY |
+--------------------+-------------------+-----------------+--------------+---------------------------+-----------------+
36 rows in set (0.00 sec)
sql_mode设置数据库约束等级
默认：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SU
手动设置set sql_mode='';，后允许插入非法数据，如下：
mysql> show create table a;
+-------+---------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------+
| a | CREATE TABLE `a` (
`id` int(11) NOT NULL,
`date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
## 非法插入
mysql> insert into a select null, '2009-02-30';
Query OK, 1 row affected, 2 warnings (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 2
## 结果
mysql> select * from a;
+----+------------+
| id | date |
+----+------------+
| 0 | 0000-00-00 |
+----+------------+
1 row in set (0.00 sec)
## 警告
mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1048 | Column 'id' cannot be null |
| Warning | 1264 | Out of range value for column 'date' at row 1 |
+---------+------+-----------------------------------------------+
2 rows in set (0.00 sec)
推荐设置为：STRICT_TRANS_TABLES
触发器与约束
触发器示例：
delimiter $$
create trigger trg_usercash before update on usercash
for each row
begin
if new.cash - old.cash > 0 then
insert into usercash_err_log select old.userid, old.cash, new.cash, user(), now();
set new.cash = old.cash;
end if;
end;
$$
delimiter ;
外键
父表：
CREATE TABLE `parent` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
子表:
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
可以定义的子表的操作：
cascade：当父表delete或者update是，子表同步update和delete
set null：父表update或者delete时，子表设置威null
no action：抛出错误
restrict:抛出错误
视图
视图是一个命名的虚表，它由一个查询来定义，可以当作表来使用。与持久表不同的是，没有物理表现形式。
create view v_t as select * from t where t < 0;
alter view v_t as select * from t where id<10 with check option;