SQL必知必会_学习笔记1

1 表操作

1.1 创建表

这里创建的表也是后面要用到的数据
创建dx_customers表

CREATE TABLE dx_customers{
	cust_id char(10) not null,
	cust_name char(50) not null，
	cust_address char(50) null,
	cust_city char(50) null,
	cust_state char(5) null,
	cust_zip char(10) null,
	cust_country char(50) null,
	cust_contact char(50) null,
	cust_email char(255) null
}

• 使用NULL值，NULL值就是没有或者缺失。允许那列为Null，就是允许在插入时，可以不给出该列的值。不允许NULL值的列，一定要给出该列的值。
• NULL值与字符串不同，NULL值是没有值，但是空字符串是有效的值。
• 创建表时，指定默认值

// 第一步
CREATE TABLE dx_customers{
	cust_id char(10) not null,
	cust_name char(50) not null，
	cust_address char(50) null  default '11 street'    	 ,
	cust_city char(50) null default 'Nanjing',
	cust_state char(5) null,
	cust_zip char(10) null,
	cust_country char(50) null,
	cust_contact char(50) null,
	cust_email char(255) null
}

// 第二步
insert into dx_customers(cust_id, cust_name)
values('001', 'tom');

cust_id	cust_name	cust_address	cust_city	cust_state	cust_zip
001	tom	11 street	Nanjing	null	null

• cust_address和cust_city指定了默认值，然后插入只有cust_id和cust_name的数据，其他都是省略列，此时cust_address和cust_city填入的是默认值，其他列填入的是null。

创建dx_orderItems表

CREATE TABLE dx_orderItems{
	order_num int not null,
	order_item int not null,
	prod_id char(10) not null,
	quantity int not null,
	item_price decimal(8,2) not null
}

• 看下decimal类型

创建dx_orders表

CREATE TABLE dx_orders{
	order_num int not null,
	order_date datetime not null,
	cust_id char(10) not null
}

• 看下datetime和text类型

创建vendors表

CREATE TABLE dx_vendors{
	vend_id char(10) not null,
	vend_name char(50) not null,
	vend_address char(50) null,
	vend_city char(50) null,
	vend_state char(5) null,
	vend_zip char(10) null,
	vend_country char(50) null
}

1.2 更新表

1.2.1 给表增加列

给dx_customers表增加一列cust_age

alter TABLE dx_customers
add cust_age int null;

1.2.2 删除表中的列

删除dx_customers表中的cust_email列和cust_age列

alter TABLE dx_customers
drop column cust_email, drop column cust_age;

1.3 删除表

删除表dx_customers

drop table dx_customers;

• drop table 表名，不仅删除表中数据，连表结构也删除
• 如果表不存在，删除会报错

表存在，才删除

drop table if exists dx_customers;

• 如果dx_customers表存在，才删除。

2. 约束

2.1 主键

• 主键用来保证一列或者一组列是唯一的，主键要满足如下几个条件
  • 任意两行的主键值都不能相同
  • 每行都有一个主键值，主键值不能为null
  • 包含主键值的列从不修改或者更新，哪怕DBMS允许这样做，也别去改
  • 主键值不能重用，如果从表中删除某一行，这行的主键值也不要分配给新的行

2.1.1 创建主键

方式1.在创建表的时候就添加主键

• 把单个字段设置为主键
  • 在创建表的时候，就紧接着字段设置primary key，此时name就是主键了

CREATE TABLE student{
	name char(50) not null primary key,
	id char(20) not null,
	age char(10) null
}

• 把多个字段设置为主键
  错误写法

// 这样写会报错:定义了多个主键
CREATE TABLE student{
	name char(50) not null primary key,
	id char(20) not null primary key,
	age char(10) null
}

正确写法
把name和id设置为主键

CREATE TABLE student{
	name char(50) not null,
	id char(20) not null,
	age char(10) null,
	constraint studentId primary key(name, id)
};

• 在constraint中用primary key把多个列定义为一个主键，虽然是多个列，但仍然是一个主键
• 可以用constraint studentId给主键起别名，就叫studentId

方式2.创建表之后，再添加主键

// 先创建表
CREATE TABLE student{
	name char(50) not null,
	id char(20) not null,
	age char(10) null
};
// 然后增加主键
alter table student
add constraint studentKey primary key(name,id);

• 先创建表，然后在把name和id列作为主键

2.1.2 撤销主键

alter table student
drop primary key;

2.1.3 使用的数据中的主键

• 只有不允许null的列才可以作为主键

alter table dx_customers add primary key(cust_id);
alter table dx_orderItems add primary key(order_num, order_item);
alter table dx_orders add primary key(order_num);
alter table dx_products add primary key(prod_id);
alter table dx_vendors add primary key(vend_id);

2.2 外键

外键是表的一列，外键的值必须在另一个表的主键中，外键是为了保证引用的完整性，用于防止破坏表之间连接的行为，也能防止非法的数据插入外键列，因为外键的值必须在外键指向的表的主键中。

2.2.1 创建外键

方式1.在创建表的时候，就创建外键

create table student(
	id char(20) not null primary key,
	name char(50) not null,
	age char(10) null,
	order_num int not null,
	order_item int not null,
	constraint fk_student foreign key (order_num, order_item) references dx_orderitems(order_num, order_item)
);

• student这个表的主键是id
• student这个表的外键是多个列order_num和order_item共同作为外键
• 用constraint fk_student给外键起别名，用references dx_orderitems(order_num, order_item)关联到dx_orderitems表
• studnet表中的order_num、order_item字段类型，要与表dx_orderitems中的order_num、order_item的字段类型一致，不然会出错，报incompatible不兼容问题。

方式2.创建表后，再添加外键

// 第一步，创建student表
create table student(
	id char(20) not null primary key,
	name char(50) not null,
	age char(10) null,
	order_num int not null,
	order_item int not null,
);
// 第二步，添加外键
alter table student 
add constraint fk_student foreign key(order_num,order_item) references dx_orderitems(order_num,order_item);

2.2.2 删除外键

alter table student 
drop foreign key fk_student;

• 这里用外键的别名fk_studnet来删除外键

2.2.3 使用的数据中的外键

alter table dx_orderItems
add constraint FK_OrderItems_Orders foreign key(order_num) references dx_orders(order_num);
alter table dx_orderItems
add constraint FK_OrderItems_Products foreign key(prod_id) references dx_products(prod_id);
alter table dx_orders
add constraint FK_Orders_Customers foreign key(cust_id) references dx_customers(cust_id);
alter table dx_products
add constraint FK_Products_Venders foreign key(vend_id) references dx_vendors(vend_id);

• 疑问：一个表可以添加多个外键到另外多个不同表吗？

2.3 唯一约束

唯一约束用来保证一列或者一组列的数据是唯一的，类似主键，但是又不同

• 表可以包含多个唯一约束，但是每个表只允许一个主键
• 唯一约束列可以包含null，但是主键不能包含null
• 唯一约束列可以修改或者更新，但是主键不能
• 唯一约束列的值可以重复使用，但是主键不能
• 唯一约束列不能用来定义外键，但是主键可以用来定义外键