[打卡]xgl 查询

#not null 非空约束只能用列级约束，不能表级约束

CREATE DATABASE school;

use school;

#在创建的时候添加约束
CREATE TABLE stu_1(
id int not null,
last_name VARCHAR(15) not null,
email VARCHAR(25),
salary int
);

ALTER TABLE stu_1 CHANGE last_name last_name VARCHAR(15) not null;

DESC stu_1;

INSERT into stu_1 (id,last_name,email,salary)
VALUES(1,'dog','123456@qq.com',5000);

SELECT * from stu_1;

UPDATE stu_1
set email = null
where id=1;

UPDATE stu_1
set salary =6000
where id=1;

desc stu_1;

# 在 ALTER TABLE 时添加约束
#如果报错，是本身有null值，再改为null时不能添加
ALTER TABLE stu_1 MODIFY
email VARCHAR(25) not null;

#ALTER TABLE 时删除约束
ALTER TABLE stu_1 MODIFY
last_name VARCHAR(10) null;

# UNIQUE 唯一性约束
#在 CREATE TABLE 时添加约束
CREATE TABLE stu_2(
id int UNIQUE, #列级约束
last_name VARCHAR(15),
email VARCHAR(20),
salary int,

#表级约束
CONSTRAINT uk_stu_2_email UNIQUE(email)
);

desc stu_2;

INSERT into stu_2(id,last_name,email,salary)
VALUES(1,'tom','tom@163.com',8000);

#在ALTER TABLE 时添加约束
#方式一：
ALTER TABLE stu_2
add CONSTRAINT uk_stu_2_salary UNIQUE(salary);

#方式二：
ALTER TABLE stu_2
MODIFY last_name VARCHAR(15) UNIQUE;