约束和高级查询

• 查看约束 

   

show create table table-name;

• 主键约束

主键约束最显著的特征是主键列中的值是不允许重复的，通过主键约束可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，且 PRIMARY KEY 约束中的列不能接受NULL值。

alter table table-name ADD [CONSTRAINT pk_name] PRIMARY KEY(字段名);

1. 创建表时指定主键约束，例：

create  table  dept(
id int primary key,             --指定主键
name varchar(20));

create table dept(
id int,
name varchar(20),
primary key (id));  -- 指定主键

     

       2. 修改某一列为主键

alter table teacher add  primary key(id);

ALTER TABLE 表名称 ADD [CONSTRAINT] PRIMARY KEY(id);
ALTER TABLE 表名称 MODIFY [COLUMN] 字段名 属性 PRIMARY KEY; -- 修改列类型
ALTER TABLE 表名称 CHANGE [COLUMN] 字段名 字段名 属性 PRIMARY KEY; --修改列名称和类

        3.删除表中的主键约束

alter table table-name drop primary key;

• 联合主键

1. 创建表时同时创建主键

create table students(
    student_id int,course_id int,primary key(student_id,course_id));

        2.删除主键

alter table students drop primary key;

• 唯一约束

1. 创建表时

CREATE TABLE `table_name` (
`id` int,
`name` varchar(20) UNIQUE # 唯一约束
);

CREATE TABLE `table_name` (
`id` int,
`name` varchar(20),
CONSTRAINT uq UNIQUE(name)

        2.修改表

ALTER TABLE 表名 ADD UNIQUE(列名称);
ALTER TABLE 表名 ADD CONSTRAINT [constraint_name] UNIQUE(列名称);
ALTER TABLE 表名 CHANGE [COLUMN] 列名 列名 类型 UNIQUE;
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 UNIQUE;

        3.删除表

ALTER TABLE 表名称 DROP INDEX 设置唯一时的名称;
# 如果没有设置约束名称，名称默认是字段名 唯一约束允许有多个NULL值

•  默认约束       

1. 创建表时

CREATE TABLE `table_name` (
`id` int DEFAULT value,
`name` varchar(20) unique,
);

        2.修改表

ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 DEFAULT 默认值;
ALTER TABLE 表名 CHANGE 列名 列名 列类型 DEFAULT 默认值;

        3.删除表

ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型;
ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT; 【8.0.23以上的版本】
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT NULL;【8.0.23以前的版本】    如果已经设置了值，默认值则无效

• 非空约束   NOT NULL ：非空，用于保证该字段的值不能为空。

1. 创建表

CREATE TABLE `table_name` (
`id` int NOT NULL, # 非空约束
`name` varchar(20)
);

        2.修改表

ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 NOT NULL;
ALTER TABLE 表名 CHANGE 列名 列名 列类型 NOT NULL;

        3.删除表

ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 [null];

修改列的约束确保现有数据满足非空约束条件，否则可能导致操作失败。

• 检查约束

1. 创建表的时候，添加检查约束

create table employee(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> age int,
    -> check(age >= 18)); --添加检查约束，年龄必须大于10岁

  2.修改表的时候

ALTER TABLE 表名 CHANGE 列名 列名 列类型 CHECK(condition);ALTER TABLE 表名 MODIFY 列名 列类型 CHECK(condition);ALTER TABLE 表名 ADD CONSTRAINT 列名 CHECK(condition);

    3.删除检查约束名

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

• 外键约束

       用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。表中列的值来自于 另外一张表的主键或 唯一键的列称为外键 FK ，将被引用值的表称为主表或父表，将引用值的表称为从表或子表。

1. 创建表时

ALTER TABLE 表名 CHANGE 列名 列名 列类型 CHECK(condition);ALTER TABLE 表名 MODIFY 列名 列类型 CHECK(condition);ALTER TABLE 表名 ADD CONSTRAINT 列名 CHECK(condition);

        2.外键约束

ALTER TABLE 从表表名 ADD [CONSTRAINT] 约束名称 FOREIGN KEY (从表字段) REFERENCES 主表表名(主表字段);
alter table students add  fk_s  foreign key(course_id) references courses(course_id);       #course_id 为 courses 表的主键

        3.删除外键约束

alter table students drop foreign key(fk_s); #设置外键时的名称

外键列类型需要与引用列类型一致

外键列的值必须是主表中引用列的值或者 NULL

一个表可以有多个外键列

从表列可以随便删除

删除主表数据时，会先检查从表中有没有对此数据的关联，如果有就不能直接删除

• 在设置外键的时候后面添加  on delete cascade / on update  cascade        不加主从表不能同步，命令不通
• 在删除/更新主表时，级联删除/更新外键列的数据在设置外键的时候后面添加 on delete set null / on update set  null 在删除/更新主表时，外键列的值会变成null

---

• 聚合函数

MYSQL 中内置了 5 种聚合函数，分别是： SUM 、 MAX 、 MIN 、 AVG 、 COUNT 。

1. sum 求和

select sum(列) from table_name [其他子句];

        2 .max 求最大值

select max(列) from table_name [其他子句];

        3. min : 求最小值

select min(列) from table_name [其他子句];

        4. avg ： 求平均值

select avg(列) from table_name [其他子句];

        5. count : 求数量

select count(列) from table_name [其他子句];

group by

group by 是对数据进行分组，分组时，表中有相同值的分为一组。分组后可以进行聚合查询。

group by 分组后的查询中， select 的列不能出现除了 group by 分组条件以及聚合函数外的其他列。

having

having 是对 group by 分组后的结果集进行筛选。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;
select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having分组后条件;