sql（约束和查询）

sql（约束）

约束：生活的约束是一种限定，一种束缚。在数据库中的约束是对字段的一种限定，避免不符合要求的数据，为了数据的有效性和完整性所做出的一种限定.

数据库中常见的约束

1. 主键约束 PRIMARY KEY
2. 自增长约束
3. 非空约束
4. 唯一约束
5. 非负约束
6. 外键约束

主键约束 PRIMARY KEY
主键约束的特点，非空且唯一，一张表中只能有一个主键，也建议你给一张表中提供一个主键。

创建表格
 CREATE TABLE test1(
     tname VARCHAR(10),
     tage INT
  )
  往表格中添加数据
  INSERT INTO test1 VALUES('aaaa',10)
    
  INSERT INTO test1 VALUES(NULL,10)

添加主键的方式1:在建表的时候就可以添加主键约束

 CREATE TABLE test2(
     tname VARCHAR(10)PRIMARY KEY,  -- tname 添加了主键约束
     tage INT
  )
  
  
  INSERT INTO test2 VALUES('aaaa',10)
    
  INSERT INTO test2 VALUES(NULL,10)
  

添加主键的方式2：表建好之后添加主键约束，通过修改表来添加主键约束

 CREATE TABLE test3(
     tname VARCHAR(10),
     tage INT
  )
  
  
  ALTER TABLE test3 ADD PRIMARY KEY(tname);--给tname添加主键约束
  INSERT INTO test3 VALUES('aaaa',NULL)
    
  INSERT INTO test3 VALUES(NULL,NULL)

添加主键的方式3：建表的时候添加主键约束

 CREATE TABLE test4(
     tname VARCHAR(10),
     tage INT,
     PRIMARY KEY(tname) -- 添加约束的位置
  )

联合主键：可以将多个字段看做一个统一的整体作为主键，当两个主键都重复了才会出错。联合主键的创建方法和主键的创建方法是一样的。

CREATE TABLE test5(
     tname VARCHAR(10),
     tage INT,
     PRIMARY KEY(tname,tage) -- 添加约束的位置
  )
  
  
  
   INSERT INTO test5 VALUES('aaaa',10)
   
   INSERT INTO test5 VALUES('aaaa',100)
   
   
   INSERT INTO test5 VALUES(NULL,100)
  
  
    CREATE TABLE test6(
     tname VARCHAR(10),
     tage INT

  )
  
  ALTER TABLE test6 ADD PRIMARY KEY (tname,tage);

唯一约束: UNIQUE 字段的值不能重复 但是对null值不做唯一限定。添加方式和之间约束一样。

 -- 添加唯一约束的方式1：
 CREATE TABLE test7(
    tname VARCHAR(10) UNIQUE, -- 添加了唯一约束
    tage INT
    );
    INSERT INTO test7 VALUES('aaaa',100)
    INSERT INTO test7 VALUES(NULL,100)
    INSERT INTO test7 VALUES('aaaa',NULL)-- 不行
    INSERT INTO test7 VALUES(NULL,100)-- 可以重复拥有
    添加唯一约束的方式2：
    CREATE TABLE test8(
  tname VARCHAR(10),
  tage INT,
  UNIQUE(tname,tage)
);

INSERT INTO test8 VALUES('aaaa',100)

-- 添加唯一约束的方式3

  CREATE TABLE test9(
  tname VARCHAR(10),
  tage INT
);


ALTER TABLE test9 ADD UNIQUE(tname,tage);

非空约束; not null

特点:被修饰过的字段非空

 CREATE TABLE test10(
  tname VARCHAR(10) NOT NULL,
  tage INT NOT NULL
);


INSERT INTO test10 VALUES('aaaa',100)



INSERT INTO test10 VALUES(NULL,100)-- 失败'tname' cannot be null
    
    
    CREATE TABLE test11(
  tname VARCHAR(10) NOT NULL  UNIQUE,
  tage INT NOT NULL UNIQUE
);


INSERT INTO test11 VALUES(NULL,100) --  'tname' cannot be null
 

NOT NULL UNIQUE 跟 PRIMARY KEY 是一样吗？ 不是
– 主键一张表中只能有一个主键。
– NOT NULL UNIQUE 属于两个约束，同时限定，可以给多个字段加上约束

创建多表：可以描述表与表之间的关系

需求:
把网上商城里面用的实体创建成表,并且将他们之间建立关系.

网上商城的实体：用户 订单 商品 分类

常见关系：

1. 一对多. 用户和订单 分类和商品

2. 多对多. 订单和商品 学生和课程

3. 一对一. 丈夫和妻子

   ER图可以描述实体于实体之间的关系
   实体用矩形表示
   属性用椭圆表示
   关系用菱形表示

ER图
在ER图中有如下四个成分：
矩形框：表示实体，在框中记入实体名。
菱形框：表示联系，在框中记入联系名。
椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。
连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。
（对于一对一联系，要在两个实体连线方向各写1；
对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。)

一对多

在开发中,关系中的一方称之为主表或者一表,关系中的多方称之为多表或者从表,
为了表示一对多的关系,一般会在多表的一方添加一个字段,字段名称自定义(建议:主表的名称_id)
字段类型一般和主表的主键的类型保持一致,我们称这个字段为外键

为了保证数据的有效性和完整性,添加约束(外键约束).

​ 在多表的一方添加外键约束
​ 格式:
​ alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);

添加了外键约束之后有如下特点:

​ 1.主表中不能删除从表中已引用的数据
​ 2.从表中不能添加主表中不存在的数据

开发中处理一对多:
在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,
为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可.

在添加了外键之后想删除主表中的数据：

1. 方式1：级联删除
   ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE;
   然后你就可以删除主表中的数据了
   当然你可以级联删除和级联更新 都加上 FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)
2. 方式2：先把带有外键的多表的数据删除,再删除一表中的数据

多对多

一张表只能有一个主键但是一张表至少可以有两个外键

开发中处理多对多：

引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分成两个一对多了，为了保证数据的有效性和完整性，需要在中间表上添加两个外键约束即可.

多表查询

N张表关联查询，所需要的关联条件只少 要 N-1 个条件。

也就是将多张表联合起来查询

内连接
外连接
子查询

笛卡尔积:多张表的列数相加，行数相乘得到的结果。
多张表无条件的联合查询.没有任何意思
select a.,b. from a,b;

内连接:

​ 格式1:显式的内连接
​ select a.,b. from a [inner] join b on ab的连接条件
​ 格式2:隐式的内连接
​ select a.,b. from a,b where ab的连接条件

外连接:

​ 左外连接:
​ select a.,b. from a left [outer] join b on 连接条件; outer 可以不写
​ 意思:
​ 先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示.
​ 右外连接:
​ select a.,b. from b right [outer] join a on 连接条件; outer 可以不写
​ 意思:
​ 先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以null值展示.

左外连接和右外连接互换一下就可以得出一模一样的结果

子查询:
一个查询依赖另一个查询.

-- 一对多  一个用户对应多个订单
--  创建用户表
CREATE TABLE USER(		
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20)
);
-- 创建订单表
CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT,
totalprice DOUBLE,
user_id INT -- 外键
);
-- 为了保证数据的有效性和完整性,添加约束(外键约束).
-- 在多表的一方添加外键约束
-- 在多表的一方添加外键约束
	-- 格式:
	-- alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id)
-- 多对多  商品和订单 需要创建中间表
-- 创建商品表
CREATE TABLE product(
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(20),
		price DOUBLE
	);
	CREATE TABLE orderitem(
		oid INT,-- 订单的外键
		pid INT -- 商品的外键
	);
	ALTER TABLE orderitem ADD FOREIGN KEY(oid) REFERENCES orders(id);
	ALTER TABLE orderitem ADD FOREIGN KEY(pid) REFERENCES product(id);
	
	
	-- 多表查询
	-- 查询所有用户的订单信息
	
-- 内连接：不符合条件的数据不展示。


-- 隐式内连接

SELECT user.*,orders.* FROM USER,orders WHERE user.`id`=orders.`user_id`;

-- 显示内连接  from a表 inner join 表 on 条件    注意：inner可以省略不写。

SELECT user.*,orders.* FROM USER INNER JOIN orders ON user.`id`=orders.`user_id`;

	-- 给表起别名,as
	
-- SELECT u.*,o.* FROM USER AS u INNER JOIN orders AS o ON u.`id`=o.`user_id`;
SELECT user.`id` AS uid,user.`username`AS 姓名,orders.`totalprice`AS 订单价格,orders.`user_id`AS 外键 FROM USER,orders WHERE user.`id`=orders.`user_id`;

-- 子查询
-- 查看用户为cherry的订单信息
-- 第一步 在用户表查询cherry的id
SELECT id FROM USER WHERE user.`username`='cherry';
-- 第二步：拿着上部查询出的id去订单表里面查
SELECT*FROM orders WHERE orders.`user_id`=1;
-- 把上面两步合二为一
-- 主查询的 条件 来自另外一个查询的结果
-- 语法：注意 子查询 用括号括起来
SELECT*FROM orders WHERE orders.`user_id`=(SELECT id FROM USER WHERE user.`username`='cherry');

-- 查询出订单的价格大于300的所有用户信息。
SELECT user_id FROM orders WHERE orders.`totalprice`>300;
SELECT*FROM USER WHERE id=1 OR id=3;
SELECT*FROM USER WHERE id=(SELECT user_id FROM orders WHERE orders.`totalprice`>300);
-- 查询订单价格大于300的订单信息及相关用户的信息。

SELECT user.*,orders.*FROM USER,orders WHERE user.`id`=orders.`user_id` AND orders.`totalprice`>300 AND user.`username`='cherry';