前言
这些都是在Navicat上实现的
一、创建表
CREATE TABLE products
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(40) DEFAULT NULL,
price
double DEFAULT NULL,
category
varchar(40) DEFAULT NULL,
pnum
int(11) DEFAULT NULL,
description
varchar(255) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
创建完成如下:
二、MySQL语法的练习
1.查询出所有商品信息
SELECT * FROM products;
2.查询出所有商品的名称,价格,类别及数量信息
SELECT name,price,category,pnum FROM products;
3.查询出所有的商品类别
SELECT DISTINCT category FROM products;
4.查询出所有商品的名称及价格,将所有商品价格加10
SELECT name,price+10 as price from products;
5.查询出每一个商品的总价及名称
SELECT name,pnum*price from products;
1.查询所有计算机类商品信息
SELECT * FROM products WHERE category=“计算机”;
2.查询出商品价格大于90的商品信息
SELECT * FROM products WHERE price >90;
3.查询出商品总价大于10000的商品信息
Select * from products where price*pnum>10000;
4.查询出价格在100-200之间的商品信息
SELECT * FROM products WHERE price>=100 AND price <=200;
SELECT * from products where price between 100 and 200;
5.查询出商品价格是65,100或190的商品信息
SELECT * from products where price in (65,100,190);
6.查询出商品的名称中包含java的商品信息。
SELECT * FROM products WHERE name LIKE ‘%java%’;
SELECT * FROM products WHERE name LIKE ‘java%’;
7.查询出书名是两个字的商品信息
SELECT * from products where name like ‘__’;
8.查询出商品价格不为null商品信息
SELECT * FROM products WHERE price IS NOT NULL;
1.查询出所有商品,并根据价格进行升序排序
SELECT * from products order by price asc;
2.查询出所有商品,根据数量进行升序排列,如果数量相同,根据价格进行降序排列
SELECT * from products order by pnum asc,price desc;
1.统计商品表中共有多少条记录
SELECT count(id) from products;
SELECT count() from products;
2.统计商品表中价格大于50的有多少条记录
SELECT count() from products where price >50;
3.统计有多少商品(如果有重复的商品怎么办呢?)
SELECT sum(pnum) from products;
4.统计所有商品的总价值
SELECT sum(pricepnum) from products;
5.统计所有商品的平均价格
SELECT sum(pricepnum)/sum(pnum) from products;
SELECT avg(price*pnum) from products;
6.统计出记录中price的平均值
SELECT AVG(price) FROM products;
7.统计出商品表中price最大与最小值
SELECT MAX(price),MIN(price) FROM products;
1.对商品分类别统计,求出每一种类商品的总数量
SELECT category,sum(pnum) from products group by category;
2.对商品分类别统计,求出每一种类商品的总数量,数量要大于100
SELECT category,sum(pnum) from products group by category HAVING sum(pnum)>100;