数据库SQL的高级查询

高级查询

高级查询以及导入导出，表的复制、重命名，

• 1.子查询
  定义：一个查询语句中嵌套另一个查询
• 例如
• 查询金额超过平均值的订单
  select * from orders where amt >(select avg(amt) from orders);
• 查询所有名字以D开头的客户下的订单

select * from orders where cust_id in
  (select distinct cust_id
  from customer
  where cust_id like 'c%');

• 说明

括号找那个的部分称为子查询
-- 子查询可以返回一个值也可以多个值
-- 根据外层查询的要求来确定
-- 先执行子查询，将子查询的结果，作为外层查询的条件，在执行外层查询

• 联合查询

什么是联合查询：也叫连接查询，将多个表中的数据进行连接，得到一个查询结果集

什么情况下使用联合查询：当从一个表无法查询到想要的数据时，使用联合查询

 前提：联合的表之间一定要有逻辑上的关联性
 
笛卡尔积（联合查询的理论依据）
	什么是笛卡尔积：两个集合的乘积，产生一个新的集合，表示两个集合所有的可能的组合情况
	笛卡尔积和关系：笛卡尔积中，去掉没意义或不存在的组合，就是关系（规范的二维表）

• 连接查询

内连接（inner Join）：没有关联到的数据不显示

• 例如：查询订单编号、金额、客户名称、客户电话

1. 方式一：where进行条件关联

   select orders.order_id,orders.amt,customer.cust_name,customer.tel_no
   from orders,customer where orders.cust_id = customer.cust_id;
   

2. 方式一起别名

   select a.order_id,a.amt,b.cust_name,b.tel_no
   from orders a,customer b where a.cust_id = b.cust_id;
   

3. 方式二：利用inner、jion关键字

		select a.order_id,a.amt,b.cust_name,b.tel_no
		from orders a inner join customer b
		on a.cust_id = b.cust_id;

外连接（outer JOIN）没有关联到的数据也会现在到结果当中
左连接：以左表为基准，右表的数据进行关联
左表数据全部显示，右表中的字段如果没有关联到，
则显示NULL

select a.order_id,a.amt,b.cust_name,b.tel_no
from orders a left join customer b
on a.cust_id = b.cust_id;

右链接：以右表为基准，左表的数据进行关联
右表数据全部显示，左表中的字段
如果没有关联到，则显示NULL

select a.order_id,a.amt,b.cust_name,b.tel_no
from orders a right join customer b
on a.cust_id = b.cust_id;

• 3）约束
  – 1）什么是约束，数据必须遵循的规则
  – 2。目的：保证数据一致性，完整性
  – 从数据库层面对数据进行；安检
  – 3）分类
  – 1）非空约束
  – 定义：not null 要求字段的值不能为空
  – 2）唯一约束
  – 定义：unique 字段的值 不能重复。
  – 语法：字段名称 类型（长度）unique
  – 3)主键（primary key ,简写pk):非空、唯一
  – 定义：主键在表中唯一标识，区分一个实体，非空，唯一
  – 语法：字段名称 类型（长度）primary key

-- 创建一个新的表，设定为非空，唯一，主键
    create table t1(
    stu_no varchar(32) primary key ,
    stu_name varchar(32) not null ,
    id_card_no varchar(32) unique
    );
--     插入一个正常的数据
    insert into t1 values
    ('001','jerry','54454555545');
--     插入一个姓名为空的数据、报错
    insert into t1 values
    ('002',null ,'255454');
--     插入一个id_card_no 为重复的数据、报错
    insert into t1 values
    ('003','xiaoming',255454);
--     插入一个主键为空的、报错
    insert into t1(stu_no,stu_name)
    values (null ,'Dekie');
--     插入主键stu_no值重复，报错
    insert into t1(stu_no ,stu_name)
    values('001','Dokas');

• 4）默认值
  – 定义：指定某个字段的默认值，如果新插入一笔数据没有对该字段赋值，
  – 系统会自动填入一个默认值。
• 5）自动增长（auto_increment)
  – 定义：当某一个字段被设置为自增长时，插入时不需要赋值，系统在原
  – 最大基础上自动加1（要求：要求这个字段必须是主键或者设置了unique约束。
  – 语法：字段名称 类型（长度）auto_increment

创建一个t2的新表。设置id 为整形并且设置主键且自动增加
status设置默认为0.
create table t2(
id int primary key auto_increment,
name varchar (32),
status int default 0
);
插入一个id 为空的值。
insert into t2 values (null,'Jerry',1);
insert into t2 values (null,'Tom',2);
从插入两个指定值，status为空，查看结果
insert into t2(id,name) values (null,'Hennry');

• 6)外键约束（foreign key,简称fk)
  – 什么是外键，一种约束，建立在外键的前提是；
  – 某个字段在当前表中不是pk，但在另外表（也称为’外表’）是主键
  – 作用：保证被参照的实体一定存在（参照的完整性）
  – 字段呗设置外键约束后，影响有；
  – 当插入一个在外表中不存在的实体时。报错
  – 当删除外表中已经被参照的实体，报错
  • 创建外键语法：
    创建course(课程信息表，主键course_id)
    创建teacher(教室信息表，主键course_id,在course_id字段创建外键)

create table course(
course_id varchar (32) primary key,
name varchar (32)
);

create table teacher(
id int auto_increment primary key,
name varchar(32),
course_id varchar(32),
constraint fk_course foreign key(course_id)
references course(course_id)
);

insert into course
values ('0001','Python编程');

insert into teacher
values (1,'Jerry','0001');

delete from course where course_id = '0001';
insert into teacher values(2,'Jerry','0003');

外键使用的条件
表的存储引擎必须为innodb
外键字段在另外表中必须是主键
当前表、外表中字段类型必须一致。

• 随手练习

创建表 t6 id ,name,status,course_id，tel_no五个数据，

create table t6(
id int ,name varchar(32),
status int,course_id varchar(4),
tel_no varchar(32));
#修改   id  为主键
alter table t6 add primary key(id);
#修改  id  为整形并且自动增长
alter table t6 modify id int auto_increment;
#修改 status 为整形并且默认为0
alter table t6 modify status int default 0;
#修改  tel_no  为字符串并且唯一
alter table t6 modify tel_no varchar(32) unique ;
#修改  course_id  为course表的外键
alter table t6 add constraint fk_course_id
foreign key(course_id)
references course(course_id);

• 导出&导入
  语法

select 查询语句
into outfile '文件路径'
fields terminated by '字段分割符'
lines terminated by '行分割符'

示例：导出orders表中所有的数据

select * from orders
into outfile '/var/lib/mysql-files/orders.csv'
fields terminated by ','
lines terminated by '\n'

查看导出的文件（linux Shell下执行）:

sudo cat /var/lib/mysql-files/orders.csv

• 导入
  语法

load data infile '备份文件路径'
into table 表名称
fields terminted by ','
lines termintedby '\n';

导入数据

load data infile '/var/lib/mysql-files/orders.csv'
into table orders
fields terminated by ','
lines terminated by '\n';

• 表的复制、重命名
  表的复制
  将orders数据，表结构全部复制到orders_new表

create table orders_new
select * from orders;

将orders结构复制到orders_new表

create table orders_new
select * from orders where 1=0;
该方式不会讲键的属性复制到新表中。

• 表的重命名
  讲orders重命名为orders_bak

alter table orders rename to orders_bak;
alter table orders_bak rename to orders;