Mysql数据库基础知识点梳理总结/三

来源:一匹脱缰的野马 发布时间:2019-04-26 16:12:08 阅读量:1124

一、表格设计

多对多

(1)需要创建第三张表,并且公共字段可以放入到第三张表中


create table teacher11(


id int PRIMARY key auto_increment,


name VARCHAR(20) not null,


sub VARCHAR(20) not null


);


create table student11(


id int PRIMARY KEY auto_increment,


name VARCHAR(20) not null,


sex VARCHAR(5) not null


);


create table tea_std11(


id int PRIMARY KEY auto_increment,


t_id int,


s_id int,


score int,


CONSTRAINT teacher_fk FOREIGN KEY (t_id) REFERENCES teacher11(id),


CONSTRAINT student_fk FOREIGN KEY (s_id) REFERENCES student11(id)


);


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

步骤一 ,从第三张表中查出老师教过的学生


select * from tea_std11 where t_id = 1;




步骤二 根据上面id取查询学生的信息,(1,2)代表学生id


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




(2)设置外键对应其它表中的主键


 

一对一

一般情况一张表即可

也可以设计成两张表格

第二张表格中的创建字段作为外键(设置成唯一 unique)对应第一张表格中的主键

直接使用第二张表格的主键作为外键,对应第一张表格中的主键

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)

)


一对多

示例如下:


创建客户表:


CREATE TABLE customers(


       id int,


       name varchar(100),


       address varchar(255),


       PRIMARY KEY(id)


);


创建订单表:


CREATE TABLE orders(


       order_num int primary key,


       price float(8,2),


       status int,


       customer_id int,


       注:constraint: 约束的意思。foreign key: 外键。references: 参照


CONSTRAINT customer_id_fk FOREIGN KEY(customer_id) REFERENCES customers(id)


);


 


二、多表查询

1.交叉连接 笛卡尔积(查询结果是错误的)


2.内连接


(1)语法:


隐式:select * from 表1,表2 where 条件;


例:select * from users,card;


显示:select * from 表1 inner join 表2 ON 条件;


例:select * from users CROSS JOIN card;



查询出每个用户及身份证信息,显式出来

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


使用别名

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


u.*表示users表格中的所有字段,c.num表示c中的字段num

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


3.外连接


(1)左外链接


语法:select * from 表1 LEFT JOIN 表2 ON 条件


连接规则:没有拼接上就显示为空


select * from card as c LEFT JOIN student as s ON c.id = s.id;


(2)右外连接


语法:select * from 表1 RIGHT JOIN 表2 ON 条件


连接规则:没有拼接上就显示为空


select * from card as c RIGHT JOIN student as s ON c.id = s.id;


4.子查询


嵌套子查询


执行流程:先执行内部,后执行外部查询


特点:内部查询可以单独运行


 


使用嵌套子查询


select * from student11 where id in (select s_id from tea_std11 where t_id=1);




相关子查询


执行流程:先执行外部查询,将记录传递给内部


特点:内部查询需要外部查询的字段


 


 


create table teacher11(

id int PRIMARY key auto_increment,

name VARCHAR(20) not null,

sub VARCHAR(20) not null

);

create table student11(

id int PRIMARY KEY auto_increment,

name VARCHAR(20) not null,

sex VARCHAR(5) not null

);

create table tea_std11(

id int PRIMARY KEY auto_increment,

t_id int,

s_id int,

score int,

CONSTRAINT teacher_fk FOREIGN KEY (t_id) REFERENCES teacher11(id),

CONSTRAINT student_fk FOREIGN KEY (s_id) REFERENCES student11(id)

);


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

步骤一 ,从第三张表中查出老师教过的学生

select * from tea_std11 where t_id = 1;

步骤二 根据上面id取查询学生的信息,(1,2)代表学生id

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


使用嵌套子查询

select * from student11 where id in (select s_id from tea_std11 where t_id=1);


相关子查询


求t_id=1的平均分

select avg(score) from tea_std11 where t_id=1;


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

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


select * from tea_std11;




select avg(score) from tea_std11 where t_id=1;




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




常用函数

聚合函数


Count()计数


(1)统计一个班多少学生

select count(id) from student;

select count(*) from student;

(2)统计ui分数大于60的学生个数

select count(*) from score where ui>60;

统计总分大于200的人数

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


Sum()  求和



(1)统计一个班mysql总成绩

select sum(mysql) from score;

(2)统计各科的总成绩

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

(3)统计python,mysql,ui总成绩之和

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


Avg()   求平均数


统计一个班python成绩的平均分

select avg(python) from score;

select sum(python)/count(python) from score;


 


select avg(python+mysql+ui) from score;


Max(),min() 最大最小


select min(ui),max(ui) from score;


Group by分组


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);

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; 


having注意事项


WHERE是在分组(group by)前进行条件过滤,


HAVING子句是在分组(group by)后进行条件过滤,


WHERE子句中不能使用聚合函数,HAVING子句可以使用聚合函数。



分享:
评论:
你还没有登录,请先