java mysql 表关系分析_我爱Java系列---【mysql查询DQL&多表关系】

于 2021-02-06 21:20:52 发布
阅读量126 收藏
点赞数
文章标签： java mysql 表关系分析
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_35792468/article/details/113898187
版权

/*

#创建商品表：

pid int

pname varchar(20)

price double

category_id varchar(32)

插入一条数据:

pid=1,pname='联想',price=5000,category_id='c001'

*/

代码如下：

CREATE DATABASE day171;

USE day171;

CREATE TABLE product(

pid int,

pname varchar(20),

price double,

category_id varchar(32)

);

INSERT INTO product VALUES (1,'联想',5000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');

INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');

INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

面试题：having 和where 的区别

/*

简单查询

格式:

select 列名1,列名2 ... from 表名 where 条件

练习:

#1.查询所有的商品

#2.查询商品名和商品价格

#3.查询所有的价格(重复的只显示一个)

#4.查询结果是表达式(运算查询)：将所有商品的价格+10元进行显示

*/

USE day171;

#1.查询所有的商品

SELECT pid,pname,price,category_id FROM product ;

#如果查询表中所有字段，可以使用*代替所有字段的名字

SELECT * FROM product;

#2.查询商品名和商品价格

SELECT pname ,price FROM product;

/*

扩展：

查询显示结果字段名,可以起别名

格式:

字段名 as '别名'

注意:

1.as可以省略

2.''可以省略,但是如果别名中有',就不能省略了

3.表名也是可以起别名的,但是不能写''

*/

SELECT pname  AS '商品名称',price AS '商品价格'FROM product;

#as 可以省略(1)

SELECT pname '商品名称',price AS '商品价格' FROM product;

#''可以省略

SELECT pname 商品名称, price AS 商品价格 FROM product;

#表起别名

#注意

#SELECT pname 商品名称,price 商品价格 FROM product AS 'p';#错误的

SELECT pname 商品名称,price 商品价格 FROM product AS p ;#正确的

#as 可以省略(2)

SELECT pname 商品名称,price 商品价格 FROM product  p ;#也是正确的

#3.查询所有的价格

SELECT price FROM product ;

#查询所有的价格(重复的只显示一个)

/*

上面的查询方式发现查询结果中有重复数据,2个5000,2个800

重复的只显示一个:

需要使用关键字: distinct

*/

SELECT DISTINCT price FROM product;

#4.查询结果是表达式(运算查询)：将所有商品的价格+10元进行显示

#select 后面是可以写表达式的

SELECT 10+10,10-10,10/10;

#查询商品名称及价格(显示2次)

SELECT pname , price price FROM product;

#将所有商品的价格+10元进行显示

SELECT pname ,price +10 FROM product;

SELECT pname 商品名称,price 涨价前,price+10 涨价后 FROM product;

/*

条件查询:

select 列名1,列名2 ... from 表名 where 条件

条件:

between ... and ...: 显示在某一区间的值(含头含尾)

in(100,200): 等于100或者等于200

练习:

#查询商品名称为“花花公子”的商品所有信息

#查询价格为800商品

#查询价格不是800的所有商品

#查询商品价格大于60元的所有商品信息

#查询商品价格在200到1000之间所有商品

#查询商品价格是200或800的所有商品

*/

#查询商品名称为“花花公子”的商品所有信息

SELECT * FROM product WHERE pname = '花花公子';

#查询价格为800的商品

SELECT * FROM product WHERE price =800;

#查询价格不是800的所有商品

SELECT * FROM product WHERE price !=800;

#查询商品价格大于60元的所有商品信息

SELECT * FROM product WHERE price > 60;

#查询商品价格再说200 到100之间所有商品

SELECT * FROM product WHERE price > 200 AND price <1000;

#查询商品价格是200或800的所有商品(两种表示方式)

SELECT * FROM product WHERE price = 200 OR price = 800;

SELECT * FROM product WHERE price IN (200,800);

/*

模糊查询

格式:

使用关键字 like

通配符:

1.%: 代表任意多个字符(0个,1个,2个...)

2._: 有且仅有1个字符

select 列名1,列名2 ... from 表名 where ... like ...

IS NULL: 判断是否为空

IS NOT NULL: 判断是否不为空

练习:

#查询含有'霸'字的所有商品

#查询以'香'开头的所有商品

#查询第二个字为'想'的所有商品

#查询没有分类的商品

#查询有分类的商品

*/

#查询含有‘霸’字的所有商品

SELECT * FROM product WHERE pname LIKE '%霸%';

#查询所有以‘香’字开头的所有商品

SELECT * FROM product WHERE pname LIKE '香%';

#查询第二个字为‘想’的所有商品

SELECT * FROM product WHERE pname LIKE '_想%';

#查询没有分类的商品

SELECT * FROM product WHERE category_id IS NULL ;

#查询有分类的商品

SELECT * FROM product WHERE category_id IS NOT NULL ;

SELECT * FROM product WHERE NOT (category_id IS NULL );

/*

排序:

格式:

select ... from 表名 where 条件  order by 排序字段1 ASC|DESC,排序字段2 ASC|DESC;

注意:

1.ASC: 升序,不写默认就是ASC

2.DESC: 降序

3.order by: 一般写在sql语句的最后

练习:

#查询id 为1 3 5 7的商品 并按价格降序排列

#使用价格排序(降序)

#在价格排序(降序)的基础上，以分类排序(降序)

#显示商品的价格(去重复)，并排序(降序)

*/

#查询id为1 3 5 7的商品，并按价格降序排列

SELECT * FROM product WHERE pid IN (1,3,5,7) ORDER BY price DESC ;

#使用价格排序(降序)

SELECT * FROM product ORDER BY price DESC ;

#在价格排序(降序)的基础上，以分类排序(降序)

SELECT * FROM product ORDER BY price DESC ,category_id DESC;#价格相同时，按照分类id排序

#显示商品的价格(去重复)，并排序(降序)

SELECT DISTINCT price FROM product ORDER BY price DESC;

SELECT pname,price FROM product GROUP BY price ORDER BY price DESC;

/*

聚合函数:

1.count：统计指定列不为NULL的记录行数

2.sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0

3.max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

4.min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

5.avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

练习:

#1.查询商品的总条数

#2.查询商品的价格和

#3.查询价格大于200商品的总条数

#4.查询分类为'c001'的所有商品价格的总和

#5.查询分类为'c002'所有商品的平均价格

#6.查询商品的最大价格和最小价格

#7.查询pid为1 3 7 商品价格的平均值

#8.查询pid为1 3 7 14 商品价格的平均值

#9.统计指定列不为NULL的记录行数

*/

-- 1.count：统计指定列不为NULL的记录行数

SELECT COUNT(category_id) FROM product;#最后一个记录category_id的值是null，不统计

-- 2.sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0

SELECT SUM(pname) FROM product;#pname列是varchar类型,结果0

-- 3.max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

SELECT MAX(pname)FROM product ;#pname列是varchar类型，结果0

-- 4.min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SELECT MIN(pname) FROM product;#pname列是varchar类型，结果0

-- 5.avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

SELECT AVG (paname) FROM product ;

#1.查询商品的总条数

SELECT COUNT(*) FROM product;

SELECT COUNT(pid) FROM product;

#2.查询商品的价格和

SELECT COUNT(price) FROM product;

#3.查询价格大于200商品的总条数

SELECT COUNT(*)FROM product WHERE price >200;

SELECT COUNT(pid) FROM product WHERE price >200;

SELECT COUNT(price) FROM product WHERE price >200;

#4.查询分类为'c001'的所有商品价格的总和

SELECT price FROM product WHERE category_id ='c001';

SELECT SUM(price) FROM product WHERE category_id='c001';

#5.查询分类为'c002'所有商品的平均价格

SELECT AVG(price) FROM product WHERE category_id='c002';

#6.查询商品的最大价格和最小价格

#价格可以显示多次

SELECT price,price FROM product;

SELECT MAX(price) AS '最大值',MIN(price) AS '最小值' FROM product;

#7.查询pid为1 3 7 商品价格的平均值

SELECT AVG(price) FROM product WHERE pid IN (1,3,7);#平均值：4000

#插入pid为14的商品

INSERT INTO product(pid,pname,price,category_id) VALUES(14,'卤煮',NULL,NULL);

SELECT * FROM product;

#8.查询pid为1 3 7 14 商品价格的平均值

SELECT AVG(price) FROM product WHERE pid IN(1,3,7,14);#price是null,不参与计算

#9.统计指定列不为NULL的记录行数

SELECT COUNT(category_id) FROM product WHERE category_id IS NOT NULL;

/*

练习:

#查看所有商品的平均价格

#查看所有商品的平均价格,所有商品的平均价格>800的话,就显示出所有商品的平均价格

#查询商品名称为“花花公子”的商品所有信息

注意:

1.where后面不能写聚合函数

2.having后面可以使用聚合函数

3.where 后面不能使用select中定义的别名

SQL语句书写顺序：select ... from... where.... group by... having... order by ...

SQL语句执行顺序：from... where...group by... having.... select ... order by...

*/

#删除price为null的记录

DELETE FROM product WHERE pid = 14;

#查看所有商品的平均价格

SELECT AVG (price) FROM product;

#查看所有商品的平均价格，所有商品的平均价格>800的话，就显示出所有商品的平均价格

SELECT AVG(price) FROM product WHERE AVG (price)>800;#错误，运行不了

SELECT AVG(price) FROM product WHERE price>800;#计算price>800的商品的平均价格，不满足条件

SELECT AVG(price) FROM product HAVING AVG(price)>800;#having后面可以使用聚合函数

#查询商品名称为“花花公子”的商品所有信息

SELECT * FROM product WHERE pname = '花花公子';

SELECT pid,pname,price,category_id FROM product WHERE pname='花花公子';

#起别名

SELECT pid AS 'no',pname AS 'pn',price AS 'pr',category_id AS 'cid'

FROM product WHERE pname = '花花公子';

SELECT pid AS 'no',pname AS 'pn',price AS 'pr',category_id AS 'cid' FROM product WHERE pname='花花公子';

SELECT pid AS 'no',pname AS 'pn',price AS 'pr',category_id AS 'cid' FROM product AS p WHERE p.pname='花花公子';

/*

学生成绩表scores:

sid: 学生成绩的id

score: 成绩

sname: 课程

*/

CREATE TABLE scores (

sid INT PRIMARY KEY,

score INT,

sname VARCHAR(200)

);

INSERT INTO scores(sid,score,sname) VALUES(1,90,'语文');

INSERT INTO scores(sid,score,sname) VALUES(2,70,'数学');

INSERT INTO scores(sid,score,sname) VALUES(3,90,'外语');

INSERT INTO scores(sid,score,sname) VALUES(4,50,'语文');

INSERT INTO scores(sid,score,sname) VALUES(5,90,'数学');

INSERT INTO scores(sid,score,sname) VALUES(6,80,'外语');

INSERT INTO scores(sid,score,sname) VALUES(7,100,'语文');

INSERT INTO scores(sid,score,sname) VALUES(8,20,'数学');

INSERT INTO scores(sid,score,sname) VALUES(9,100,'外语');

INSERT INTO scores(sid,score,sname) VALUES(10,75,'编程');

#查询所有

SELECT sname,score FROM scores;

/*

注意:

1.分组一般要和聚合函数一起使用

2.作为分组的字段,一般建议显示出来,方便查看数据

3.如果聚合函数作为条件,只能使用having

*/

/*

#统计每门课程的平均分

1.分组: 按照课程分组

2.聚合函数: 平均分 avg

*/

SELECT sname, AVG(score) FROM scores GROUP BY sname;

/*

#统计每门课程的平均分,且只显示平均分>70分的信息

1.分组: 按照课程分组

2.聚合函数: 平均分 avg

3.条件: 平均分>70,需要使用到聚合函数,只能使用having

*/

SELECT sname,AVG(score) FROM scores GROUP BY sname HAVING AVG(score)>70;

SELECT 2>1,2<1;#sql: true 1,false 0

/*

分组的练习

统计各个分类商品的个数

统计各个分类商品的个数,且只显示个数大于1的信息

统计各个分类商品的平均价格

统计各个分类商品的平均价格,且只显示平均价格>800的信息

*/

/*

统计各个分类商品的个数

1.分组: 按照商品分类id,category_id

2.聚合函数: 个数 count

*/

#删除category_id的值是null的记录

DELETE FROM product WHERE category_id IS NULL ;

#select category_id,count(*)from product where category_id is not null group by category_id;

SELECT category_id, COUNT(*) FROM product GROUP BY category_id;

/*

统计各个分类商品的个数,且只显示个数大于1的信息

1.分组: 按照商品分类id,category_id

2.聚合函数: 个数 count

3.条件: 个数大于1 用到聚合函数,必须使用having

*/

SELECT category_id,COUNT(*)FROM product GROUP BY category_id HAVING COUNT(*)>1;

SELECT category_id,COUNT(pid) FROM product GROUP BY category_id HAVING COUNT(pid)>1;

/*

统计各个分类商品的平均价格

1.分组: 按照商品分类id,category_id

2.聚合函数: 平均价格 avg

*/

SELECT category_id,AVG(price) FROM product GROUP BY category_id;

/*

统计各个分类商品的平均价格,且只显示平均价格>800的信息

1.分组: 按照商品分类id,category_id

2.聚合函数: 平均价格 avg

3.条件: 平均价格>800 用到聚合函数,必须使用having

*/

SELECT category_id,AVG(price) FROM product GROUP BY category_id HAVING AVG(price)>800;

#起别名

SELECT category_id,AVG(price) AS 'ap' FROM product GROUP BY category_id HAVING ap>800;

/*

统计各个分类商品的平均价格

1.分组: 按照商品分类id,category_id

2.聚合函数: 平均价格 avg

*/

SELECT category_id,AVG(price) FROM product GROUP BY category_id;

/*

统计各个分类商品的平均价格,且只显示平均价格>800的信息

1.分组: 按照商品分类id,category_id

2.聚合函数: 平均价格 avg

3.条件: 平均价格>800 用到聚合函数,必须使用having

*/

SELECT category_id,AVG(price) FROM product GROUP BY category_id HAVING AVG(price)>800;

#起别名

SELECT category_id,AVG(price) AS 'ap' FROM product GROUP BY category_id HAVING ap>800;

/*

完成商品分类和商品信息表创建

步骤:

1.创建商品分类表

2.向商品分类表中添加数据

3.创建商品信息表

4.给商品信息表添加外键约束

5.向商品信息表中添加数据

*/

#1.创建商品分类表

CREATE TABLE category(

cid INT PRIMARY    KEY,

cname VARCHAR(100)

);

#2.向商品分类表中添加数据

INSERT INTO category(cid,cname) VALUES(1,'家电');

INSERT INTO category(cid,cname) VALUES(2,'饮品');

INSERT INTO category(cid,cname) VALUES(3,'服装');

INSERT INTO category(cid,cname) VALUES(4,'水果');

SELECT * FROM category;

#3.创建商品信息表

CREATE TABLE products(

pid INT PRIMARY    KEY,

pname VARCHAR(100),

price INT,

cid INT

);

#插入一条记录

#目前没有添加外键约束,所以cid的值可以随便写

INSERT INTO products(pid,pname,price,cid) VALUES(1,'可乐',3,1000);

SELECT * FROM products;

/*

4.给商品信息表添加外键约束

主表: 商品分类表

主表中的主键: 商品分类表中的cid

从表: 商品信息表

从表中的外键: 商品信息表中的cid

用商品分类表中的cid限制商品信息表中的cid

用主表的主键限制从表的外键

添加外键约束的格式: 通过修改从表表结构的方式

alter table 从表名 add [constraint 外键约束名称] foreign key 从表名(从表中作为外键的字段) references 主表名(主表中的主键)

*/

#删除从表中的无效数据

DELETE FROM products;

ALTER TABLE products ADD CONSTRAINT fk_p_cid FOREIGN KEY products(cid) REFERENCES category(cid);

#5.向商品信息表中添加数据

#目前已经添加外键约束,所以cid的值不可以随便写

#INSERT INTO products(pid,pname,price,cid) VALUES(1,'可乐',3,1000);

INSERT INTO products(pid,pname,price,cid) VALUES(1,'冰箱',8000,1);

INSERT INTO products(pid,pname,price,cid) VALUES(2,'彩电',7800,1);

INSERT INTO products(pid,pname,price,cid) VALUES(3,'洗衣机',5000,1);

INSERT INTO products(pid,pname,price,cid) VALUES(4,'娃哈哈',10,2);

INSERT INTO products(pid,pname,price,cid) VALUES(5,'冰红茶',3,2);

/*

商品信息和商品订单的创建

步骤:

1.创建商品订单表

2.向商品订单表添加数据

3.创建中间表

4.为中间表添加2个外键约束

5.向中间表中添加数据

*/

#1.创建商品订单表

CREATE TABLE orders(

oid INT PRIMARY KEY,

totalprice INT

);

#2.向商品订单表添加数据

INSERT INTO orders(oid,totalprice) VALUES (1,15800);

INSERT INTO orders(oid,totalprice) VALUES (2,13000);

INSERT INTO orders(oid,totalprice) VALUES (3,8010);

INSERT INTO orders(oid,totalprice) VALUES (4,13);

INSERT INTO orders(oid,totalprice) VALUES (5,7803);

SELECT * FROM orders;

#3.创建中间表

CREATE TABLE pro_ord(

pid INT,

oid INT

);

/*

4.为中间表添加2个外键约束

(1).为中间表的pid添加外键约束

主表: 商品信息表

主表中的主键: 商品信息表 中的pid

从表: 中间表

从表中的外键: 中间表中的pid

添加外键约束的格式: 通过修改从表表结构的方式

alter table 从表名 add [constraint 外键约束名称] foreign key 从表名(从表中作为外键的字段) references 主表名(主表中的主键)

*/

ALTER TABLE pro_ord ADD CONSTRAINT fk_pro_ord_pid FOREIGN KEY pro_ord(pid) REFERENCES products(pid);

/*(2).为中间表的oid添加外键约束

主表: 商品订单表

主表中的主键: 商品订单表 中的oid

从表: 中间表

从表中的外键: 中间表中的oid

添加外键约束的格式: 通过修改从表表结构的方式

alter table 从表名 add [constraint 外键约束名称] foreign key 从表名(从表中作为外键的字段) references 主表名(主表中的主键)

*/

ALTER TABLE pro_ord ADD CONSTRAINT fk_pro_ord_oid FOREIGN KEY pro_ord(oid) REFERENCES orders(oid);

#5.向中间表中添加数据

INSERT INTO pro_ord(pid,oid)

VALUES

(1,1),

(1,2),

(1,3),

(2,1),

(2,5),

(3,2),

(4,3),

(4,4),

(5,4),

(5,5);

/*

唯一约束: unique

一个表可以有多个

*/

/*

添加唯一约束方式一:

在定义表结构时,作为唯一约束的字段后面+unique

*/

#删除表

DROP TABLE category;

#创建分类表category

CREATE TABLE category(

cid INT PRIMARY KEY AUTO_INCREMENT,

cname VARCHAR(100)UNIQUE

);

/*

添加唯一约束方式二:

在定义表结构时,已经指定了所有的字段,

在最后使用constraint关键字,添加唯一约束

格式:

[constraint 唯一约束名称] unique(作为唯一的字段名称)

注意:

[]里面的内容是可以省略的,如果要写,不能写[]

[]: 代表的是,里面的内容,作为可选项

*/

#删除表

DROP TABLE category;

CREATE TABLE category (

cid INT PRIMARY KEY AUTO_INCREMENT,

cname VARCHAR(100),

CONSTRAINT un_cname UNIQUE(cname)

);

/*

添加唯一约束方式三:

在定义完毕表结构后,通过修改表结构方式

格式:

alter table 表名 add [constraint 唯一约束名称] unique(作为唯一约束的字段名称)

注意:

[]里面的内容是可以省略的,如果要写,不能写[]

[]: 代表的是,里面的内容,作为可选项

*/

#删除表

DROP TABLE category;

DELETE FROM category;

#创建分类表category

CREATE TABLE category(

cid INT,

cname VARCHAR(100)

);

#通过修改表结构方式，添加唯一约束

ALTER TABLE category ADD CONSTRAINT qun_cname UNIQUE (cname);

/*

删除唯一约束:

格式:

alter table 表名 drop index 字段名或者唯一约束名;

如果指定了唯一约束名称,则必须通过唯一约束名称,来删除唯一约束

*/

ALTER TABLE category DROP INDEX qun_cname;

/*

删除唯一约束:

格式:

alter table 表名 drop index 字段名或者唯一约束名;

如果没有指定唯一约束名称,则必须通过字段名臣删除唯一约束

*/

#给cname添加唯一约束

ALTER TABLE category ADD CONSTRAINT UNIQUE (cname);

#通过字段名，删除该字段上具有的唯一约束

ALTER TABLE category DROP INDEX cname;

呆呆美要暴富
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值