-- 水果表
CREATE TABLE fruits
(
  f_id CHAR(10) NOT NULL,
  s_id INT NOT NULL,
  f_name char(255) NOT NULL,
  f_price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY(f_id)
);

-- 插入数据

INSERT INTO fruits
  (f_id,s_id,f_name,f_price)
VALUES
  ('a1' , 101 , 'apple' , 5.2),
  ('b1' , 101 , 'blackberry' , 10.2),
  ('bs1' , 102 , 'orange' , 11.2),
  ('bs2' , 105 , 'melon' , 8.2),
  ('t1' , 102 , 'banana' , 10.3),
  ('t2' , 102 , 'grape' , 5.3),
  ('o2' , 103 , 'coconut' , 9.2),
  ('c0' , 101 , 'cherry' , 3.2),
  ('a2' , 103 , 'apricot' , 2.2),
  ('l2' , 104 , 'lemon' , 6.4),
  ('b2' , 104 , 'berry' , 7.6),
  ('m1' , 106, 'mango' , 15.6),
  ('m2' , 105 , 'xbabay' , 2.6),
  ('t4' , 107, 'xbababa' , 3.6),
  ('m3' , 105 , 'xxtt' , 11.6),
  ('b5' , 107, 'xxxx' , 3.6 );

-- 1.1、查询所有字段
select *
from fruits;

-- 1.2、查询指定字段
select f_name
from fruits;

-- 1.3、查询指定记录
select *
from fruits
where f_name = 'apple';

-- 　1.4、带IN关键字的查询   IN关键字：IN(xx，yy，...) 满足条件范围内的一个值即为匹配项
select *
from fruits
where f_name in('apple','mango');

-- 　1.5、带BETWEEN AND 的范围查询  between...and:在...到...之间的值都为匹配项
select *
from fruits
where f_price BETWEEN 10.2 and 15.6;

-- 　1.6、带LIKE的字符匹配查询 
select *
from fruits
where f_name like 'm____';

-- 　1.7、查询空值

-- 　　1.8、带AND的多条件查询

-- 　1.9、带OR的多条件查询
select *
from fruits
where f_name like 'm%' or f_price > 15;
-- 1.10、关键字DISTINCT(查询结果不重复)
SELECT distinct(s_id)
from fruits;

-- 　1.11、对查询结果排序(ORDER BY)
SELECT s_id
from fruits
order by s_id desc;
-- 　1.12、分组查询(GROUP BY)
select count(f_name), group_concat(f_name)
from fruits
group by s_id
having count(f_name) > 2;
-- 　1.13、使用LIMIT限制查询结果的数量　　




CREATE TABLE suppliers
(
  s_id INT NOT NULL,
  s_name CHAR(50) NOT NULL,
  s_city CHAR(50) NULL,
  s_zip CHAR(10) NULL,
  s_call CHAR(50) NOT NULL,
  PRIMARY KEY(s_id)
);

INSERT INTO suppliers
  (s_id,s_name,s_city,s_zip,s_call)
VALUES
  (101, 'Supplies A', 'Tianjin', '400000', '18075'),
  (102, 'Supplies B', 'Chongqing', '400000', '44333'),
  (103, 'Supplies C', 'Shanghai', '400000', '90046'),
  (104, 'Supplies D', 'Zhongshan', '400000', '11111'),
  (105, 'Supplies E', 'Taiyuang', '400000', '22222'),
  (106, 'Supplies F', 'Beijing', '400000', '45678'),
  (107, 'Supplies G', 'Zhengzhou', '400000', '33332');


-- 查询水果的批发商编号，批发商名字，水果名称，水果价格
select s.s_id, s.s_name, f.f_name, f.f_price
from suppliers as s, fruits as f
where s.s_id = f.s_id;

--
select s.s_id, s.s_name, f.f_name, f.f_price
from suppliers as s inner join fruits as f on s.s_id = f.s_id;

-- 查询供应f_id='a1'的水果供应商提供的其他水果种类？
select f2.f_name
from fruits as f1 inner join fruits as f2 on f1.s_id = f2.s_id and f1.f_id = 'a1';
select f_name
from fruits
where s_id = (select s_id
from fruits
where f_id = 'a1');

-- 
SELECT s.s_id, s.s_name, f.f_id, f.f_name
from suppliers as s left join fruits as f on s.s_id = f.f_id;


SELECT s.s_id, s.s_name, f.f_id, f.f_name
from fruits as f right join suppliers as s on s.s_id = f.s_id;


-- 在fruits和suppliers表中使用INNER JOIN语法查询suppliers表中s_id为107的供应商的供货信息？
select s.s_id, s.s_name, f.f_id, f.f_name
from fruits as f inner join suppliers as s on f.s_id = s.s_id and s.s_id = 107;

-- 在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，并对查询结果进行排序
select s.s_id, f.f_id
from fruits as f inner join suppliers as s on s.s_id = f.s_id
order by s.s_id;



create table students
(
  id int
  unsigned not null auto_increment comment '编号',  name varchar
  (20) not null comment '姓名',  gender enum
  ('男','女') not null comment '性别',  tel bigint unsigned not null comment '手机号',  primary key
  (id) 
) comment='学生表';


  create table course
  (
    id int
    unsigned not null auto_increment comment '编号',  course_name varchar
    (50) not null comment '课程名称',  primary key
    (id) 
) comment='课程表'; 
 


 select * from students UNION select * from person;

 select id,name from students unicon select * from course;