mysql学习04-表

  1. innodb_file_per_table ## 每张表数据可以单独放到一个表空间

    show variables like 'innodb_file_per_table';
    
  2. 约束 约束是为了保证数据库中的数据完整性

  3. 实体完整性

    保证表中有一个主键。

  4. 域完整性

    保证数据的值满足特定的条件。

    参照完整性

    保证两张表之间的关系。

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;

分区表