MySQL学习笔记 2：约束、修改数据表和操作数据表中的记录

一、约束以及修改数据表

1.约束分类

   约束：表级约束、列级约束

   约束：NOT NULL , PRIMARY KEY , UNIQUE KEY , DEFAULT , FOREIGN KEY

2.FOREIGN KEY外键约束

2.1外键约束的要求：父表和子表必须使用相同的存储引擎，且只能为InnoDB（Windows下MySQL默认配置文件在C:/ProgramData目录下的my.ini，其中有存储引擎的配置）

                                      外键列和参照列必须具有相似的数据类型，其中数字的长度以及有无符号位必须相同，字符的长度可以不同

                                      外键列和参照列必须创建索引，如果外键列不存在索引，系统自动创建索引

2.2外键约束的语法CREATE TABLE tb_name(

                                  ...

                                  pid TINYINT UNSIGNED,

                                  FOREIGN KEY (pid) REFERENCES ref_table_name (id)

                                   );

   这里的外键定义放在了列定义之后，也可以放在列定义中，如pid TINYINT UNSIGNED FOREIGN KEY (pid) REFERENCES ref_table_name (id)

2.3外键约束的参照操作：父表进行操作时，子表是否进行相应操作

     CASCADE:父表删除或更新时子表进行相应删除或更新 FOREIGN KEY (pid) REFERENCES ref_table_name (id) ON DELETE CASCADE

     SET NULL :父表删除或更新时子表相应列设为NULL

     RESTRICT:拒绝父表的删除或更新操作

     NO ACTION:和RESTRICT相同

3.修改数据表ALTER

3.1添加单列 ALTER TABLE tb_name ADD [COLUMN] col_name col_definition [FIRST | AFTER col_name]

      添加多列 ALTER TABLE tb_name ADD [COLUMN] (col_name col_definiton,...)无法指定位置，只能添加到最后

3.2删除列 ALTER TABLE tb_name DROP [COLUMN] col_name

3.3添加约束

      添加主键约束ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] col_name ,symbol是自己给约束起的名字，index_type有哈希索引和 btree索引，默认为btree索引

      添加唯一约束ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,..)

      添加外键约束ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)  reference_definition

     添加默认约束ALTER TABLE tb_name ALTER [COLUMN] col_name SET DEFAULT literal

3.4删除约束

     删除唯一约束ALTER TABLE tb_name DROP {INDEX | KEY} index_name

     删除外键约束ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol  (fk_symbol通过SHOW CREATE TABLE tb_name查看，外键约束之后索引还存在）

     删除默认约束ALTER TABLE tb_name ALTER [COLUMN] col_name DROP DEFAULT

3.5修改列定义 ALTER TABLE tb_name MODIFY [COLUMN]  col_name col_definition [FIRST | AFTER col_name]

      修改列名称及定义ALTER TABLE tb_name CHANGE [COLUMN] col_name col_name_new col_definition [FIRST | AFTER col_name]

3.6数据表更名

     方法一ALTER TABLE tb_name RENAME [TO/AS] new_tb_name

     方法二ALTER TABLE tb_name TO new_tb_name [,tb_name2 TO new_tb_name2,...]

二、操作数据表中的记录

1.INSERT插入记录

1.1 方法一： INSERT tb_name (col_name1,...) VALUES (val1,...)

1.2 方法二： INSERT tb_name SET col_name1=val1,col_name2=val2,... 此方法可使用子查询

1.3 方法三： INSERT tb_name [(col_name1,...)] SELECT ... 可将查询结果插入到指定的数据表

2.UPDATE更新记录（单表更新）

 UPDATE tb_name SET col_name1=val1 [,col_name2=val2,...] [WHERE condition]

3. DELETE删除记录（单表删除）

 DELETE FROM tb_name [WHERE condition]

4.SELECT查找记录

 SELECT select_expr [,select_expr...]

 [

    FROM table_references

    [WHERE where_condition]

    [GROUP BY {col_name | position} [ASC | DESC],...]

    [HAVING where_condition]

    [ORDER BY {col_name | expr | position} [ASC | DESC],...]

    [LIMIT { [offset,] row_count | rowcount OFFSET offset} ]

 ]

4.1每个select_expr表示想要的一列，星号（*）表示所有列，select_expr可以用AS alias_name赋予别名，别名可用于GROUP BY,ORDER BY或者HAVING子句

4.2WHERE条件表达式对记录进行过滤

4.3GROUP BY对查询结果分组显示

4.4HAVING只对符合条件的记录进行分组，条件语句应该是个聚合函数或者条件语句中的列名出现在select_expr中

4.5ORDER BY对查询结果进行排序

4.6LIMIT限制查询结果返回的数量，offset从0开始编号