高级查询
高级查询以及导入导出,表的复制、重命名,
- 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):没有关联到的数据不显示
- 例如:查询订单编号、金额、客户名称、客户电话
-
方式一:where进行条件关联
select orders.order_id,orders.amt,customer.cust_name,customer.tel_no from orders,customer where orders.cust_id = customer.cust_id;
-
方式一起别名
select a.order_id,a.amt,b.cust_name,b.tel_no from orders a,customer b where a.cust_id = b.cust_id;
-
方式二:利用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;