MySQL-约束

唯一约束

对于非主键列中的值也要求唯一性时，就需要唯一约束

-- 创建表时
CREATE TABLE `table_name`  (
  `id` int,
   `name` varchar(20) UNIQUE # 唯一约束
);
CREATE TABLE `table_name`  (
  `id` int,
   `name` varchar(20),
    CONSTRAINT uq UNIQUE(name)   #唯一约束
);

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

-- 删除唯一约束
ALTER TABLE 表名称 DROP INDEX 设置唯一时的名称;

# 如果没有设置约束名称，名称默认是字段名

唯一约束允许有多个NULL值

默认约束

为列中的值设置默认值，DEFAULT value

-- 创建表时
CREATE TABLE `table_name`  (
  `id` int DEFAULT value,
   `name` varchar(20) unique, 
);

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

-- 删除
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 ：非空，用于保证该字段的值不能为空。

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

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

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

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

检查约束

从 MySQL 8.0.19 版本开始，MySQL 支持了检查约束。检查约束允许你在表定义中声明条件，并确保符合该条件的数据才能插入或更新到相应的列中。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    CHECK (age >= 18) -- 添加检查约束：年龄必须大于等于18
);

-- 修改表的时候
ALTER TABLE 表名 CHANGE 列名 列名 列类型 CHECK(condition);
ALTER TABLE 表名 MODIFY 列名 列类型 CHECK(condition);
ALTER TABLE 表名 ADD CONSTRAINT 列名 CHECK(condition);

-- 删除检查约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名;

外键约束

用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

表中列的值来自于另外一张表的主键或唯一键的列称为外键FK，将被引用值的表称为主表或父表，将引用值的表称为从表或子表。

-- 创建表时
CREATE TABLE `table_name`  (
  `id` int NOT NULL,
   `name` varchar(20),
    `rid` int,
	CONSTRAINT fk_a_b FOREIGN KEY(rid) REFERENCES b(id) 
);


ALTER TABLE 从表表名 ADD [CONSTRAINT] 约束名称 FOREIGN KEY (从表字段) REFERENCES 主表表名(主表字段);
ALTER TABLE goods ADD [CONSTRAINT] fk_category_no FOREIGN KEY (category_no) REFERENCES category(no);

-- 删除
ALTER TABLE 表名称 DROP FOREIGN KEY 索引名 ;#  设置外键时的名称

• 外键列类型需要与引用列类型一致
• 外键列的值必须是主表中引用列的值或者 NULL
• 一个表可以有多个外键列
• 从表列可以随便删除
• 删除主表数据时，会先检查从表中有没有对此数据的关联，如果有就不能直接删除
  • 在设置外键的时候后面添加 on delete cascade / on update cascade 在删除/更新主表时，级联删除/更新外键列的数据
  • 在设置外键的时候后面添加 on delete set null / on update set null 在删除/更新主表时，外键列的值会变成null

课堂笔记：

create table user (
	id int primary key auto_increment comment "编号",
	username varchar(20) unique comment "用户名",
	password varchar(32)
);

drop table user
create table user (
	id int primary key auto_increment comment "编号",
	username varchar(20) comment "用户名",
	password varchar(32) default '123456',
	create_time datetime DEFAULT CURRENT_TIMESTAMP
);
-- default value(类型取决于生效的字段的数据类型)

alter table user modify username varchar(20) null

alter table user add unique (username)
-- drop index 唯一约束的名称（默认是字段名）
alter table user drop index uq_name


alter table user add CONSTRAINT uq_name unique (username)

-- 可以添加null值吗，如果可以，可以添加多个吗？
insert into user(username,password) values('zs', '2023-11-09'),('ls', '2023-11-09');

alter table user alter COLUMN password drop DEFAULT
select * from user
desc user

show create table emp

-- 
create table emp(
	id int PRIMARY key auto_increment,
	username varchar(20),
	salary double,
	age int,
	check(age >= 18)
);
select * from emp
insert into emp(username, salary, age) value('zs', 9999.9, 18)

alter table emp drop constraint emp_chk_1


create table `class`(
	id int PRIMARY key auto_increment,
	name varchar(20)
);
-- 外键：1.从表插入的时候校验从表插入的数据，主表中是否存在
-- 2. 主表删除数据的时候会校验从表是否有引用该数据，如果没有才能删掉
-- 3. 如果要删除主表数据，得先删除从表引用的数据记录
-- on delete cascade --> 级联删除 删除主表，引用了主表数据的从表一起被删除
create table student(
	id int PRIMARY key auto_increment,
	name varchar(20),
	cid int,
	CONSTRAINT fk_class_cid FOREIGN key(cid) references `class`(id) on delete set null
);

drop table student

alter table student drop foreign key fk_class_cid

insert into `class`(name) values('3营'),('4营'),('2营')

delete from `class` where id = 5

delete from student where cid = 1

insert into student(name, cid) values('qs',5)

desc `student`

show create table `class`
select * from `student`

练习

继续在company数据库中根据以下图示创建表，表结构如下，并插入以下数据，完成下面的sql。

products表结构

products表数据：

完成以下sql：

# 写一条SQL查询语句，找出所有属于"Electronics"类别的产品信息。
SELECT * from products where category = "Electronics";
# 写一条SQL查询语句，找出价格高于等于100.00的产品信息。
SELECT * from products where price >= 100.00;
# 写一条SQL查询语句，找出价格在20.00到1000.00之间的产品信息。
SELECT * from products where price between 20.00 and 1000.00;
# 对于"products"表，有一个新的需求：将"category"列改名为"product_category"
alter table products change category product_category varchar(14);
# 将"price"列的数据类型从DECIMAL(10, 2)改为DECIMAL(12, 2)。请提供相应的SQL语句来执行这些修改。
ALTER table products MODIFY price DECIMAL(12, 2); 
# 对于"products"表，又有一个新的需求：由于某些原因，我们不再销售名为"Laptop"的产品，需要从表中删除该记录。请提供一条SQL删除语句来执行此操作。
DELETE from products where product_name = "Laptop";
# 写一条SQL查询语句，找出商品名称包含字母"e"的商品信息。
SELECT * from products where product_name like "%e%";
# 如果要购买10个Mouse，请显示出最终的价格。
SELECT price*10 as Mouse_price from products WHERE product_name = "Mouse";
# 将商品的名字，商品的类别全部转换成小写形式并展示所有信息。
SELECT product_id,lower(product_name) as lowercase_name,lower(product_category) as lowercase_category,price from products;
# 将商品类别和名称拼接起来并显示其余的信息。比如：Elctronics-Mouse
SELECT CONCAT(product_category, '-', product_name) AS concatenated_info,   
       price   
FROM products;