mysql 中ref

转载

架构设计师之光 2024-07-16 12:53:27

文章标签 mysql 中ref mysql 外键字段外键约束 文章分类 MySQL 数据库

《一》、约束

一、foreign key 外键约束：
保持数据一致性，完整性，一对一或一对多关系
1.父表和子表(具有PRIMARY KEY 外键列的表)必须使用相同的引擎存储，而且禁止使用临时表
2.数据表的存储引擎只能为InnoDB
3.外键列和参照列额必须具有相似的数据类型。其中数字的长度或是否有符号必须相同；而字符型下的长度则可以不同
4.外键列和参照列必须创建索引。如果外键不存在索引的话，MySQL将自动创建索引。

CREATE TABLE provinces(
 id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 province VARCHAR(20) NOT NULL
);

CREATE TABLE users(
 id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(10) NOT NULL,
 pid SMALLINT UNSIGNED ,
 FOREIGN KEY (pid)REFERENCES provinces(id)
);

show indexes from users; 查询表的关联性
show create table users;
外键列不会自动创建索引、参照列会自己创建索引

二、外键约束的参照操作
* 1.cascade：从父表删除或更新且自动删除或更新子表中匹配的行
例句： foreign key (pid) references provinces (id) on delete cascade
解释（这样设置后，如果父表删除了这一行数据那么字表所关联的数据也会删除）
* 2.set null：从父表删除或更新行，并设置字表中的外键列
* 3.restrict：拒绝对父表的删除或更新操作
* 4.no action：标准sql的关键字，在mysql中与restrict相同

《二》、数据表操作

删除列：ALTER TABLE tb1_name DROP[COLUMN] col_name;

ALTER TABLE tb1_name DROP password, DROP name;

添加多列：ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,…);
添加单列：ALTER TABLE tb!_name ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name];

ALERT TABLE table_name ADD age TINYINT UNSIGNED NOU NULL DEFAULT 10;

添加主键约束
ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY(index_col_name);
添加唯一约束
ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY](index_col_name,...);
添加外键约束：ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES provinces (id);
添加默认约束：ALTER TABLE users2 ALTER age SET DEFAULT 15;
删除默认约束：ALTER TABLE users2 ALTER age DROP DEFAULT;
删除主键约束 ALTER TABLE table_name DROP PRIMARY KEY;
删除唯一约束：ALTER TABLE table_name DROP INDEX/KEY(索引)index_name;
删除外键约束 DROP FOREIGN KEY 外键名（SHOW CREATE TABLE 表名查看）;
或者 DROP INDEX 索引名； // 删除外键
修改列定义
这里不仅可以修改列位置，也可以修改列数据类型
ALTER TABLE tb_name MODIFY col_name colimn_definition [FIRST|AFTER col_name];
修改列名称
ALTER TABLE tb_name CHANGE old_col_name new_col_name col_definition [FIRST|AFTER col_name];
修改数据表名
ALTER TABLE tb_name RENAME [TO|AS] new_tb_name RENAME TABLE tb_name TO new_tb_name[,tb_name2 TO new_tb_name2]

《三》、操作数据表中的记录

1、insert插入
在插入操作的时候，注意default和NULL的用法。若某个字段为自动编号，在插入时，可以用NULL和default代替。若某个字段设置了default的数字，在插入时就可以用default来代替那个数字。NULL是主键插值的输入。

insert tbname(column…) values(value…);

INSERT users VALUES(NULL, 'tom', 22);
INSERT users VALUES(DEFAULT, 'tom', DEFAULT);

插入记录方法二：INSERT [INTO] tbl_name SET col_name={expr|DEFAULT}, …
插入记录方法三：INSERT [INTO] tbl_name [(col_name, … )] SELECT …

2、update更新

单表更新：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition];

如： UPDATE users SET age = age + 5;

3、delete删除
delete from tbl_name [where condition];
4、SELECT 查找记录
5、where条件表达式
where 后各种根据条件(>、<、=、>=、<=、!=、<>、IS NOT NULL),根据逻辑(and,or），根据结合方式left join、right join等，根据模式匹配(IN、NOT IN、like、not like、regexp)，使用各种MySQL函数和表达式，从表集合中筛选记录。
6、查询结果分组 GROUP BY
[GROUP BY {col_name|position} [ASC|DESC],...]

ASC：升序，默认
DESC：降序

7、having分组条件
条件可以是聚合函数或字段比较，且字段必须出现在select子句中；
select name from tbl_t1 having age <6;
#select 后的字段与having后的字段不同，报错
select name,age from tbl_t1 having age <6;
必须包含了having字段的select才能执行
select name from tbl_t1 having count(age)<6; #having后是聚合函数表达式select后不用包含
8、对分组结果进行排序 Oder by +desc/asc
9、limit 限制查询结果显示数量
初始记录行的偏移量是 0(而不是 1)：
LIMIT [offset,] rows | rows OFFSET offset;