MySQL2约束 多表

MySQL2

约束

-- 约束：MySQL中的约束是对数据中的一种限定，为了保证数据的有效性和完整性。

-- MySQL中常见的约束：
-- 主键约束
-- 自增长约束
-- 唯一约束
-- 非空约束
-- 非负约束
-- 外键约束

-- 主键约束：非空且唯一，一张表中只能有一个主键，我们也建议你在表中提供主键。

-- 添加主键的方式：
-- 1.建表时添加主键
create table test(
		username verchar(20) primary key,
    	password verchar(30)
)
-- 2.
create table test2(
		username verchar(20),
    	password verchar(30),
    	primary key(username) -- 指定主键
)
-- 3.表已经建好，我们突然想要添加主键，那么我们可以通过修改表来添加主键
create table test3(
		username verchar(20),
    	password verchar(30)
)
alter table test3 add primary key(username);

-- 联合主键：把多个字段看成一个整体，设定为主键
-- 采用修改表的方式来设置联合主键
create table test4(
		username verchar(20),
    	password verchar(30)
)
alter table test4 add primary key(username ,password);
-- username，password只要有一项不同，就可以插入



-- 自增长约束，他是要配合这个主键一起来使用，而且针对的是整数型的字段。
create table test6(
    	id int primary key auto_increment, -- 主键自增长（int类型）
		username verchar(20),
    	password verchar(30)
)
insert into test6 values(null,'aaa','12345678')
-- 删除
delete from test6 where id=10;
-- 当删除一条数据，下次再增加一条时，不会再使用之前删除过的那条主键的值



-- 唯一约束：
create table test7(
		username verchar(20) unique, -- 唯一约束
    	password verchar(30)
)
-- unique 对null值没有作用 

-- 非空约束：
create table test8(
		username verchar(20) unique not null, -- 唯一约束 非空约束
    	password verchar(30) not null -- 非空约束
)



-- 非负约束 不允许插入复制，但正值范围扩大一倍
create table test9(
    	id tinyint，
		username verchar(20),
    	password verchar(30)
)
insert into test9 values(-1,'aaa','123')

create table test10(
    	id tinyint unsigned，
		username verchar(20),
    	password verchar(30)
)
insert into test9 values(-1,'aaa','123')
insert into test10 values(128,'aaa','123')


-- 枚举类型 
create table test10(
    	id tinyint unsigned，
		username verchar(20),
    	sex enum('男','女') -- 枚举类型，能起到约束的作用
) 

-- 删除表中所有数据
delete from test; -- 逐行删除，当表中的数据特别多时，删除效率比较慢

--
truncate test; -- 直接删掉整张表，重新创建一张空表

多张表

-- 实体和实体之间的关系
-- 用户表 订单表 商品表
-- 一对多 用户表1--订单表N 我们把一方这张表一般会称主表或单表 把多方这些表叫做 多表或从表
-- 多对多 订单表N-商品表N
-- 一对一


-- 为了保证数据的有效性和完成性，我们可以添加外键约束，让两张表之间形成外键约束，来避免数据的不合理
-- 一般我们会在多表一方添加外键约束，去关联主表一方的主键

-- 如果添加了外键约束有如下特点：
-- 主表一方，不能删除从表还在引用的数据
-- 多表一方，不能添加主表没有描述的数据

-- 通过修改表的方式来添加外键约束
alter table orders add foreign key(user_id) reference user(id); 

-- 级联删除 加级联删除后 可以直接删除主表的数据，从表与主表关联的数据也会被删除
alter table orders add foreign key(user_id) references user(id) on delete cascade;
-- 级联更新 加级联更新后 可以直接更新主表的数据，从表与主表关联的数据也会被更新
alter table orders add foreign key(user_id) reference user(id) on update cascade;



-- 多对多
-- 对于多对多的关系，我们在实际开发中，要引入一张中间表，把多对多转换为一对多

多表查询

-- 多表查询：多张表联合起来查询
-- 多表联合
-- 我要查询每个人的订单信息 
-- 多张表没有关联条件的查询，查询出的结果是没有意义的
-- 像这种吧多张表简单关联起来查询，查询出来称之为笛卡尔积
-- 就是把多张表的列数相加，行数相乘，这样得出的查询结果
select user.*,order.* from user,order; -- 多张表没有条件查询

-- 我们在进行多张表查询的时候，要有关联条件
-- 如果是N张表关联查询，需要N-1个关联条件
-- 我要查询每个人的订单信息
-- 将主键与外键关联起来
select user.*,order.* from user,order where user.id=order.user_id;

-- 给表起别名 (as 可以省略)
select u.*,o.* from user as u,orders as o where u.id=o.user_id;

-- 上述方法称为内连接
-- 内连接：不符合条件的数据是不展示出来的
-- 隐式内连接
select user.*,`orders`.* from user,orders where user.`id`=`orders`.`user_id`;
-- 显式内连接 
select user.*,orders.* from user inner join orders on user.is=orders.user_id;

-- 外连接:左外连接和右外连接 这两个可以互换
-- 查询所有用户的订单，所有用户必须展示出来，这个用户如果没有订单，他的订单信息以null展示出来
-- 左外连接
-- 想让哪张表全部展示出来，就把哪张表写在left左边
select user.*,orders.* from user left outer join orders on user.id=orders.user_id;
select user.*,orders.* from orders left outer join user on user.id=orders.user_id;
-- 右外连接
select user.*,orders.* from orders right outer join user on user.id=orders.user_id;
select user.*,orders.* from user right outer join orders on user.id=orders.user_id;

 




-- 子查询：一个主查询所要的条件，他来自另一个子查询，用于一步查不出来然后分步骤查，最后再用子查询写出来。
-- 查询用户为张三的订单详情
-- 1.我们先在user表中把张三的id查出来
select user.id from user where usr.username='张三';
-- 2.拿着上一步查出的id去订单表里面查出订单的信息
select orders.* from orders where orders.user_id=3;

-- 使用子查询，把上面两步合二为一  子查询需要用括号引起来
select orders.* from orders where orders.user_id=(select user.id from user where usr.username='张三');


-- 查询出订单价格大于300的所有用户信息
-- 1.在订单表中吧大于300的订单的user_id查出来
select orders.`user_id` from orders where orders.`totalprice`>300
-- 2.拿着上一步的user_id去用户表里查询
select user.* from user where id=3 OR id=5;

-- 合二为一
 SELECT user.* FROM USER WHERE id IN(SELECT orders.`user_id` FROM orders WHERE orders.`totalprice`>300)
         
-- 再次嵌套查看相关客户的订单信息         
SELECT lsb.*,orders.* FROM (SELECT user.* FROM USER WHERE id IN(SELECT orders.`user_id` FROM orders WHERE orders.`totalprice`>300)) AS lsb INNER JOIN orders ON lsb.id=orders.`user_id`
         

SELECT lsb.*,orders.* FROM (SELECT user.* FROM USER WHERE id IN(SELECT orders.`user_id` FROM orders WHERE orders.`totalprice`>300)) AS lsb LEFT OUTER JOIN orders ON lsb.id=orders.`user_id`


-- 不使用子查询，用内连接直接做
SELECT orders.*,user.* FROM USER,orders WHERE orders.`user_id`=user.`id` AND orders.`totalprice`>300;





-- 自查询
-- 查询emp表中 员工姓名所对应的上司姓名
-- 将这张表看做两张完全想用的A B表
-- A表员工的老板号=B表老板的员工号

select a.ename,b.ename from emp as a,emp as b where a.mgr=b.empno;


-- 查询所有用户的所有订单以及订单中的所有信息
-- 显式内连接
SELECT u.*,o.*,product.* FROM USER u INNER JOIN orders o ON u.`id`=o.`user_id` INNER JOIN `orderitem` ON o.`id`=`orderitem`.`oid` INNER JOIN `product` ON `orderitem`.`pid`=product.`id`


SELECT u.*,o.*,product.* FROM USER u LEFT  JOIN orders o ON u.`id`=o.`user_id` LEFT JOIN `orderitem` ON o.`id`=`orderitem`.`oid` LEFT JOIN `product` ON `orderitem`.`pid`=product.`id`



-- 把一张旧表中的数据，复制到一张新表中
create table myemp as select * from emp;

-- 只想要旧表中的表头，不要表中的数据 需要写一个条件使条件为假就行
create table myemp2 as select * from emp where 1=2;

-- 只想复制其中个别字段
CREATE TABLE  myemp4 AS SELECT emp.`empno`,emp.`ename`,emp.`sal` FROM emp;