数据库SQL基础知识点

**

1、 多表查询

**

– 创建teacher 表

create table teacher(
id int,
name VARCHAR(20) not null,
gender CHAR(5) not null,
primary key(id)
);

– 创建学生表格

create table student(
id int,
name VARCHAR(20) not NULL,
age int not null
);
alter table student add primary key(id);

– 第三张关系表

create table tea_std(
id int PRIMARY key auto_increment,
teacher_id int,
student_id int, 
CONSTRAINT fk_teacher FOREIGN KEY(teacher_id) REFERENCES teacher(id),
CONSTRAINT fk_student FOREIGN key(student_id) REFERENCES student(id)
);

– 一对一

create table users(
id int primary key auto_increment,
name varchar(20),
age int
);

create table card(
id int PRIMARY key,
num VARCHAR(20) not null,
address VARCHAR(100) not null,


CONSTRAINT user_card_fk FOREIGN KEY(id) REFERENCES users(id)
)


drop table card;
drop table users;

– 多表查询
– 交叉连接，笛卡尔积， 注意 结果不正确。
– 隐式

select * from users,card ;

– 显式、使用关键字

select * from users CROSS JOIN card;

– 内连接
– 查询出每个用户及身份证信息显示出来。

select * from users  ,card  where users.id = card.id;

– 表格使用别名

select * from users u ,card as c where u.id = c.id;

/**
u.* 表示 users 表格中所有的字段，
c.num、c.address 表示 card 表中的字段。
**/

select u.*,c.num,c.address from users u ,card as c where u.id = c.id;

– 查询用户对应的所有订单信息以及个人信息。

select * from customers as c,orders as o where c.id=o.customer_id; 

– 使用关键字 INNER JOIN

select * from customers as c INNER JOIN orders as o ON c.id=o.customer_id; 

– 左外连接

select * from customers as c LEFT JOIN orders as o ON c.id= o.customer_id;

– 右 外连接

select * from customers as c RIGHT JOIN orders as o ON c.id=o.customer_id;

select * from orders as o  RIGHT JOIN  customers as c ON c.id=o.customer_id;

– 多对多

create table teacher(
id int primary key auto_increment,
name varchar(20) not null,
sub VARCHAR(20) not null

);

create table student(
id int primary key auto_increment,
name varchar(20) not null,
gender  varchar(5) not null
);

create table tea_std(
id int primary key auto_increment,
t_id int,
s_id int,

score int,

CONSTRAINT teacher_fk FOREIGN KEY(t_id) REFERENCES teacher(id),
CONSTRAINT student_fk FOREIGN KEY(s_id) REFERENCES student(id)
);

– 查询出id为1的老师教过的所有学生。

– 步骤1，先从第三张表中查询出教过的学生的id

select s_id from tea_std  where t_id = 1; 

– 步骤2：根据id 去学生表中查找学生的信息。

select * from student where id in(1,2);

– 使用嵌套子查询

select * from student where id in(select s_id from tea_std  where t_id = 1);

– 相关子查询

– 求：每一科考试成绩大于平均分的学生的分数。

-- select * from tea_std;
-- select avg(score) from tea_std where t_id=1;

select * from tea_std as t1 where t1.score>(select avg(t2.score) from tea_std t2  where t1.t_id=t2.t_id);

– 分析步骤:
– 先执行 外部查询 拿到第一条记录
– 1 1 1 60

-- select * from tea_std as t1 where t1.score>(select avg(t2.score) from tea_std t2  where t2.t_id=1);

– 执行内部查询(子查询)

-- select avg(t2.score) from tea_std t2  where t2.t_id=1

– 1 1 1 60
– 2 1 2 90
– 75
– 比较结果 ，每一天记录的分数 和 平均分进行比较。
– select * from tea_std as t1 where t1.score>75; 60>75

- 聚合函数

count() 计数 ，统计所有的记录。
– （1）、统计一个班级共有多少学生？

select COUNT(*) from student;

– (2)、统计数学ui大于60的学生有多少个？

select COUNT(*) as total from score where ui>60;

– (2)、统计总分大于200的人数有多少？

select count(*) from score where(python+mysql+ui)>200;

select * from score where(python+mysql+ui)>200;

– count(*) 不会忽略掉 null。 使用其他字段 会自动忽略null。

select count(*) from score;
select count(python) from score;

– sum() 求和函数
– （1）、统计一个班级mysql总成绩？

select sum(mysql) from score;

– （2）、统计一个班级python、mysql、ui各科的总成绩

select sum(python),sum(mysql),sum(ui) from score;

– （3）、统计一个班级每一个人的 python、mysql、ui的成绩总和。
– `

select s1.name, s1.python,s1.mysql ,s1.ui, s1.python+s1.mysql+s1.ui '总分' from score s1 , score s2 where s1.id=s2.id;`
select name,python,mysql,ui ,python+mysql+ui as total from score; 

– （4）、统计一个班级python成绩平均分

select AVG(python) from score;
select sum(python)/COUNT(python) from score;
select sum(python)/COUNT(*) from score;

– avg(): 求平均数。
– （2）、求一个班级总分平均分
– SELECT AVG(chinese+english+math) FROM student
– min() 求最小 、max() 求最大

select min(ui),max(ui) from score;
create table t_order(
id int primary key,
product varchar(20),
price float(8,2)
);
insert into t_order values(1,'xiaomi', 1000);
insert into t_order values(2,'xiaomi',1100);
insert into t_order values(3,'huawei',2200);
insert into t_order values(4,'apple',8200);

select * from t_order;

– 1.对订单表中商品归类后，显示每一类商品的总价

select  product, SUM(price) from t_order GROUP BY product;

– 2.对订单表中商品归类后,查询每一类商品总价格大于3000的商品

select  product, SUM(price) as total from t_order GROUP BY product HAVING total>3000;

select  product, SUM(price) from t_order GROUP BY product HAVING SUM(price)>3000;