MySQL 练习

MySQL 练习

2024 年 04 月 23 日

254 次浏览

2949字数

```sql
create database db1;
use db1;

create table product2
(
	pid int primary key auto_increment,
	pname varchar(20) not null,
	price double,
	category_id varchar(20)
	
);

drop table product;

insert into product values(null,'海尔洗衣机',5000,'c001');
insert into product values(null,'美的冰箱',3000,'c001');
insert into product values(null,'格力空调',5000,'c001');
insert into product values(null,'九阳电饭煲',200,'c001');
insert into product values(null,'啄木鸟衬衣',300,'c002');
insert into product values(null,'恒源祥西裤',800,'c002');
insert into product values(null,'花花公子夹克',440,'c002');
insert into product values(null,'劲霸休闲裤',266,'c002');
insert into product values(null,'海澜之家卫衣',180,'c002');
insert into product values(null,'杰克琼斯运动裤',430,'c002');
 
insert into product values(null,'兰蔻面霜',300,'c003');
insert into product values(null,'雅诗兰黛精华水',200,'c003');
insert into product values(null,'香奈儿香水',350,'c003');
insert into product values(null,'SK-II神仙水',350,'c003');
insert into product values(null,'资生堂粉底液',180,'c003');
 
insert into product values(null,'老北京方便面',56,'c004');
insert into product values(null,'良品铺子海带丝',17,'c004');
insert into product values(null,'三只松鼠坚果',88,null);

-- 排序查询

-- 使用价格排序（降序)
select * from product order by price desc;

-- 价格排序降序基础 分类排序
select * from product order by price desc,category_id desc;

-- 去重复 price 以降序排序
select distinct * from product order by price desc;

-- count sum max min avg

-- 聚合查询
select count(*) from product;

select count(1) from product;

select count(*) from product where price>200;

select sum(price) as 'id=c001的price的合' from product where category_id='c001';

select max(price) from product;

select min(price) from product;

select avg(price) from product where category_id='c001';

create table test_null(
	c1 varchar(20),
	c2 int
);

insert into test_null values('aaa',3),('bbb',3),('ccc',null),('ddd',6);

select count(*) from test_null;-- 4
select count(*) as total from test_null;-- 4

select count(1) from test_null;-- 4

select count(c2) c2 from test_null;-- 3

select sum(c2) from test_null;-- 12
select avg(c2) from test_null;-- 12/3      4

select max(c2) from test_null; --  6
select min(c2) from test_null; -- 3

select * from product;

-- 分组查询  字段相同为一组
select category_id id,count(*) '总数' from product group by category_id;

select * from product group by category_id;

-- category_id 数量>4 为一组 
select category_id,count(*) from product group by category_id having count(*)>4;
select category_id,count(*) total from product group by category_id having total>4;

-- 分页查询亲五条
select * from product limit 5;

-- 从4（n-1)
select * from product limit 3,5;

create table table1 select * from product;
TRUNCATE table1;
desc table1;
select * from table1;
drop table table1;

insert into product1 select * from product;
TRUNCATE product1;
select * from product1;
desc product1;
```

MySQL 练习