一基本查询
-- 创建商品表
CREATE TABLE products(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
price DOUBLE
);
查询的格式: select [distinct]*(所有)|字段名,...字段名 from 表名 [where 条件过滤]
-- 查询指定字段信息pname,price
SELECT pname,price FROM products;
-- 去除金额重复的记录
SELECT DISTINCT price FROM products;
-- 别名查询，使用的as关键字，as可以省略的
SELECT pname AS 名称,price AS 价格 FROM products;
SELECT pname 名称,price 价格 FROM products;
-- 我们在sql语句的操作中，可以直接对列进行运算。
SELECT (1+1);
SELECT (5/2);
-- 查询出所有的商品价格,把价格增加20元
SELECT pname,price+20 price FROM products;
二条件查询
/*
格式:
select [distinct]*(所有)|字段名,...字段名 from 表名 [where 条件过滤]
比较运算符
> < >= <= = <>(!=)
is null 是否为null
逻辑运算符
与 and
或 or
非 not
模糊查询
like
%:任意多个字符
_:单个字符
*/
查询商品名称为“花花公子”的商品所有信息
SELECT * FROM products WHERE pname = '花花公子';
SELECT * FROM products WHERE pname IN ('花花公子');
查询价格为800商品
SELECT * FROM products WHERE price = 800;
查询价格不是800的所有商品
SELECT * FROM products WHERE price != 800;
SELECT * FROM products WHERE price <> 800;
查询商品价格大于60元的所有商品信息
SELECT * FROM products WHERE price > 60;
查询商品价格在200到1000之间所有商品
使用between...and进行改造
小的数值必须写在前边,可以多日期进行查询
SELECT * FROM products WHERE price BETWEEN 200 AND 1000;
查询商品价格是200或800的所有商品
SELECT * FROM products WHERE price=200 OR price=800;
使用in(多个字段)改造
SELECT * FROM products WHERE price IN(200,800);
查询名称以'香'开头的所有商品
SELECT * FROM products WHERE pname LIKE '香%'
查询名称以'霸'结尾的所有商品
SELECT * FROM products WHERE pname LIKE '%霸'
查询名称含有'霸'字的所有商品
SELECT * FROM products WHERE pname LIKE '%霸%'
查询出名称中是五个字的所有商品
SELECT * FROM products WHERE pname LIKE '_____';
查询名称第二个字为'想'的所有商品
SELECT * FROM products WHERE pname LIKE '_想%';
查询商品名称是null的值
SELECT * FROM products WHERE pname IS NULL;
查询商品名称不是null的值
SELECT * FROM products WHERE pname IS NOT NULL; -- 是不空
三排序查询
/*
排序查询
格式:
select 字段|* from 表名 [where 条件过滤] [order by 字段[ASC][DESC]]
升序:ASC 默认为升序
降序:DESC
注意:
排序order by 要写在select语句末尾
*/
1.使用价格排序(升序) asc或者省略
SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY price;
2.显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM products ORDER BY price DESC;
3.显示商品的价格大于1000的商品所有信息，并排序(降序)
SELECT * FROM products WHERE price >1000 ORDER BY price DESC;
4.根据商品名称排序默认使用编码表排序
SELECT * FROM products ORDER BY pname DESC;
四聚合函数
聚合函数:
对列进行操作,返回的结果是一个单一的值,忽略空值
count：统计指定列不为NULL的记录行数；
sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
格式:
select 聚合函数(字段) from 表名
例:统计出商品表中价钱的最大与最小值
SELECT MAX(price),MIN(price) FROM products;
五分组查询
分组查询语句:
select 被分组的字段 from 表名 group by 字段 [having 字段]
注意:
被分组的字段,一定要写在select后面
需求:
根据商品名称相同,对数据进行分组,对每组数据进行求和
SELECT pname,SUM(price) FROM products GROUP BY pname;
-- 需求:对分组求和的结果进行过滤,只显示求和结果大于1000的商品
SELECT pname,SUM(price) s FROM products GROUP BY pname
HAVING SUM(price)>1000;
自定义函数
CREATE FUNCTION f1(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;
存储过程
创建存储过程
Create PROCEDURE sp1() SELECT VERSION();
调用存储过程
CALL sp1
//
CREATE PROCEDURE removerUserById(In pid INT UNSIGNED)
BEGIN
DELETE FROM user_skills WHERE id = pid;
END
//
CALL removerUserById(1)
DELIMITER //
CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM user1_test WHERE id = p_id;
SELECT count(id) FROM user1_test INTO userNums;
END
//
