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;
  
  • 第一个参数指定第一个返回记录行的偏移量，
  • 第二个参数指定返回记录行的最大数目。