###################MySQL数据库基本操作-DQL-基本查询#######################
-- select
-- [all][distinct]
-- <目标列的表达式1> [别名],
-- <目标列的表达式2> [别名],
-- from <表名或视图名> [别名],<表名或视图名> [别名]...
-- [where<条件表达式>]
-- [group by <列名>]
-- [having <条件表达式>]
-- [order by <列名> [asc|desc]]
-- [limit <数字或者列表>];
CREATE DATABASE mydb2;
USE mydb2;
CREATE TABLE products(
pid INT PRIMARY KEY AUTO_INCREMENT ,-- 商品的编号
pname VARCHAR(20) NOT NULL, -- 商品的名字
price DOUBLE, -- 商品的价格
category_id VARCHAR(20) -- 商品所属分类
);
INSERT INTO products VALUES(NULL,'海尔洗衣机',5000,'c001');
INSERT INTO products VALUES(NULL,'美的冰箱',8000,'c001');
INSERT INTO products VALUES(NULL,'格力空调',7000,'c001');
INSERT INTO products VALUES(NULL,'九阳电饭煲',6000,'c001');
INSERT INTO products VALUES(NULL,'九阳水壶',1000,'c001');
INSERT INTO products VALUES(NULL,'啄木鸟衬衣',300,'c002'),
(NULL,'恒源祥西裤',800,'c002'),
(NULL,'花花公子夹克',440,'c002'),
(NULL,'劲霸休闲裤',266,'c002'),
(NULL,'海澜之家卫衣',180,'c002'),
(NULL,'杰克琼斯卫衣裤',430,'c002');
INSERT INTO products VALUES(NULL,'兰蔻面霜',300,'c003'),
(NULL,'雅诗兰黛精华水',200,'c003'),
(NULL,'香奈儿香水',350,'c003'),
(NULL,'SK-II神仙水',350,'c003'),
(NULL,'资生堂粉底液',180,'c003');
INSERT INTO products VALUES(NULL,'老北京方便面',56,'c004'),
(NULL,'良品铺子海带丝',17,'c004'),
(NULL,'三只松鼠坚果',88,'c004');
###############################################################
#需求一:查询所有商品
-- 查询所有列方式1:
SELECT pid,pname,price,category_id FROM products;
-- 查询方式2:
SELECT * FROM products;
#需求二:查询商品名和商品价格
SELECT pname,price FROM products;
#需求三:别名查询,使用的关键字是as(as可以省略)
-- 3.1表别名
SELECT * FROM products AS p;
-- 或者 select * from product p;
-- 使用场合:当要查询两个表里面的相同字段名id时,需要给表取一个简单的表名
-- select p.id ,u.id from products p,user u;
-- 或者SELECT product.id ,user.id FROM products,USER;
-- 3.2 列别名
SELECT pname AS '商品名',price AS'商品价格' FROM products;
#需求四:去掉重复值(distinct)
SELECT DISTINCT price FROM products;
SELECT DISTINCT * FROM products
-- 如果该表中某两行全部相同,则去掉
#需求五: 查询结果是表达式(运算查询):将所有的商品加价10元进行显示
SELECT pname,price+10 AS 'new price' FROM products;
###############################################################
#MySQL运算符:数据库的表结构确定之后,表中数据代表的意义就已经确定,通过MySQL运算符进行运算,就可以获取到表结构以外的另一种数据
#算数运算符:+、-、*、/(DIV)、%(MOD)
#比较运算符:=、<=、>=、<=>、!=、is null、is not null、least、greatest、between and、in、not in、like、regexp(正则表达式匹配)
#逻辑运算符:not ! AND && or || xor(逻辑异或)不同为真,相同为假
#位运算符:| & ^ >> <<
SELECT 3 & 5;
0011
0101
-----
0001
SELECT 3 | 5;
0011
0101
----
0111
SELECT 3^5; -- 位异或(相同为0.不同为1)
0011
0101
----
0110
SELECT 3>>1; -- 位右移
0011 >>1 --->0001
SELECT 3<<1; -- 位左移
0011 <<1 ---->0110
SELECT ~3; -- 位取反
0000000000000000001 --->11111111111111111110
###############################################################
#练习
-- 查询商品名称为海尔洗衣机的商品的所有信息
SELECT * FROM products WHERE pname='海尔洗衣机';
-- 查询价格为800的商品
SELECT * FROM products WHERE price=800;
-- 查询价格不是800 的商品
SELECT * FROM products WHERE price!=800;
SELECT * FROM products WHERE price <> 800;
SELECT * FROM products WHERE price NOT(price=800);
-- 查询商品价格大于等于60元的所有商品信息
SELECT * FROM products WHERE price>=60;
-- 查询商品价格在200到1000之间的所有商品
SELECT * FROM products WHERE price BETWEEN 200 AND 1000;
SELECT * FROM products WHERE price >= 200 AND price<=1000;
SELECT * FROM products WHERE price >=200 && price<=1000;
-- 查询商品时200或者800的所有商品
SELECT * FROM products WHERE (price=200)||(price=800);
SELECT * FROM products WHERE price IN(200,800);
-- 查询含有鞋字的所有商品
SELECT * FROM products WHERE pname LIKE '%鞋%';-- pname里面包含鞋字、%鞋(表示最后一个字是鞋)%(表示任意字符,表示模糊匹配)
SELECT * FROM products WHERE pname LIKE '%裤%';
SELECT * FROM products WHERE pname LIKE '%海%';
SELECT * FROM products WHERE pname LIKE '_蔻%';-- 下划线匹配单个字符
-- 查询category_id 为null的商品
SELECT * FROM products WHERE category_id IS NULL;
SELECT * FROM products WHERE category_id IS NOT NULL;
-- 使用least 求最小值(如果求最小值时,其中有一个值为null,就不会进行比较,结果直接为Null)
SELECT LEAST(10,5,20) AS small_number;
SELECT GREATEST(10,20,30) AS big_number;
######################排序######################
#如果想要对读取的数据进行排序,我们就要使用MySQL的order by 子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果
SELECT
字段名1,字段名2....
FROM 表名
ORDER BY 字段名1[ASC|DESC],字段名2[ASC|DESC]....
-- asc 代表升序,desc代表降序,如果不写默认为升序
-- order by 用于子句中可以支持单个字段,多个字段,表达式,函数,别名
-- order by 子句,放在查询语句的最后面,LIMIT子句除外
-- 默认升序
SELECT * FROM products ORDER BY price;
-- 使用价格排序(降序)
SELECT * FROM products ORDER BY price DESC;
-- 在价格排序(降序)的基础上,以分类排序(降序)
SELECT * FROM products ORDER BY price DESC,category_id DESC;
-- 第一个字段相同的前提下,按照第二个字段条件进行排序
-- 显示商品的价格(去重复),并降序
SELECT DISTINCT price FROM products ORDER BY price DESC;
################################################
#聚合查询
-- count();统计指定列不为null的记录行数
-- sum():计算指定列的数值和,如果指定列不是数值类型,那麽计算结果为0
-- max():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算
-- min():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算
-- avg():计算指定列的平均值,如果指定列不是数值类型,那么计算结果为0
-- 查询商品的总条数
SELECT COUNT(pid) FROM products;
SELECT COUNT(*) FROM products;
-- 查询价格大于200商品的总条数
SELECT COUNT(pid) FROM products where price >200;
SELECT COUNT(*) FROM products where price >200;
-- 查询分类为'c001'的所有商品的价格总和
SELECT SUM(price) FROM products WHERE catgory_id='c001';
-- 查询商品的最大价格
SELECT MAX(price) FROM products;
-- 查询商品的最小价格
SELECT MIN(price) FROM products;
-- 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM products WHERE category_id = 'c002';
#聚合查询对null值的处理
-- count()函数对Null值的查询
如果count()函数的参数为(星号*),则统计所有的记录的个数,而如果参数为某字段,不统计含null值的记录个数
-- sum和avg函数对null值的处理
忽略null值的存在
-- max和min函数对Null值的处理
忽略null值的存在
#分组查询
-- 使用group by 字句对查询信息进行分组
-- select 字段1,字段2...from 表名 group by 分组字段 having 分组条件;
-- 例如统计各个分类商品的个数
SELECT category_id,COUNT(pid) AS '分组个数' FROM products GROUP BY category_id;-- 如果要进行分组的话,则select 子句之后,只能统计出现分组的字段和统计函数,其他的字段不能出现。
-- 分组之后的条件筛选-having
-- 分组之后对统计结果进行筛选的话必须使用having,不能使用where
-- where子句用来筛选FROM子句中指定的操作所产生的行
-- group by子句用来分组where子句的输出
-- having 子句用来从分组的结果中筛选行
SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 HAVING 分组条件
#分页查询-limit
-- 使用背景 :由于数据量很大、显示屏长度有限,因此对数据要采取分页显示方式。例如数据共有30条,每页显示5条,第一页显示1-5条,第二页显示6-10条
-- 方式一:显示前n条
SELECT 字段1,字段2... FROM 表明 LIMIT n
-- 方式二:分页显示
-- select 字段1,字段2... from 表明 limit m,n
-- m:整数,表示从第几条索引开始,计算方式(当前页-1)* 每页显示条数
-- n:整数,表示查询多少条数据
-- 例如:查询products表的前5条记录
SELECT * FROM products LIMIT 5;
-- 例如:从第四条开始显示,显示5条
SELECT * FROM products LIMIT 4,5;
#insert into select 语句:将一个表的数据导入另一个表中,可以使用insert into select语句
-- 格式一:insert into Table2(field1,field2,...) select value1,value2,... from table1
-- 格式二:insert into table2 select * from table1
#注意:目标表table2必须存在