我的网站:欢迎大家访问
Day26-MySql基础
数据库概念
概念
什么是数据库?
数据库(Database,简称DB)
是按照数据结构来组织、存储和管理数据的仓库.保存有组织的数据的容器(通常是一个文件或一组文件)
发展分类
-
人工管理阶段
-
文件系统阶段
-
层次数据库和网状数据库技术阶段
-
关系型数据库技术阶段
-
后关系数据库技术阶段
我们主要学习关系型数据库,常见的关系型数据库有:
Oracle、MySQL、DB2、SQL SERVER
SQL语言六大部分
一、数据查询语言(DQL)
二、数据操作语言(DML)
三、事务处理语言(TPL)
四、数据控制语言(DCL)
五、数据定义语言(DDL)
六、指针控制语言(CCL)
数据库存储引擎
MyISAM:拥有较高的插入,查询速度,但不支持事务,不支持外键。
InnoDB:支持事务,支持外键,支持行级锁定,性能较低。
它提供了具有提交、回滚和崩溃恢复能力的事务安全。但对比MyISAM,处理效率差,且会占用更多 的磁盘空间以保留数据和索引
表的创建
建表的语法:
CREATE TABLE 表名(
列名1 列的类型 [约束],
列名2 列的类型 [约束],
…
列名N 列的类型 约束
);
查询操作
简单查询
语法:
SELECT {*, column [alias],…}
FROM table_name;
-
去重操作
语法: SELECT DISTINCT 列名,…
-
数学运算
对NUMBER型数据可以使用算数操作符创建表达式(+ - * /)
对DATE型数据可以使用部分算数操作符创建表达式 (+ -) -
设置别名
语法:select id ad ‘别名’ from test;
-
设置显示格式
为方便用户浏览查询的结果数据,有时需要设置显示格式,可以使用CONCAT函数来连接字符串。
SELECT CONCAT(productName,‘这个是:’,salePrice) AS productSalePrice FROM product;
过滤查询
-
比较运算
语法
SELECT
FROM table_name
WHERE 条件1 AND/OR 条件2; -
逻辑运算
select id from test where id=1 AND id=2
select id from test where id=1 OR id=2
select id from test where id!=2 AND NOT id = 2; -
范围查询
语法:
SELECT
FROM table_name
WHERE 列名 BETWEEN minvalue AND maxvalue:闭区间。 -
模糊查询
使用LIKE运算符执行通配查询,查询条件可包含文字字符或数字:
%:通配符:可表示零或多个任意的字符。
_:通配符:可表示任意的一个字符。
通配符:用来实现匹配部分值得特殊字符。
结果排序
使用ORDER BY子句将结果的记录排序:
ASC : 升序,缺省–不写默认。
DESC: 降序。
ORDER BY 语句出现在SELECT语句的最后。
语法:
SELECT
FROM table_name
WHERE 条件
ORDER BY 列名1 [ASC/DESC],列名2 [ASC/DESC]…;
分页查询
语法:
SELECT * FROM table_name LIMIT ?,?;
SELECT * FROM table_name LIMIT beginIndex,pageSize;
beginIndex = (currentPage-1) * pageSize;
第一个?: 表示本页,开始索引(从0开始).
第二个?: 每页显示的条数
聚集函数
定义:聚集函数作用于一组数据,并对一组数据返回一个值。
COUNT:统计结果记录数 如果列的值为null 不会计算在内的
MAX: 统计计算最大值
MIN: 统计计算最小值
SUM: 统计计算求和
AVG: 统计计算平均值 如果列的值为null 不会计算在内的
分组查询
可以使用GROUP BY 子句将表中的数据分成若干组,再对分组之后的数据做统计计算,一般使用聚集函数才使用GROUP BY.
语法格式:
SELECT 聚集函数或者分组的列
FROM table_name
WHERE 条件
GROUP BY 列名
HAVING 分组之后的条件;
注意:GROUP BY 后面的列名的值要有重复性分组才有意义;
使用HAVING字句,对分组之后的结果作筛选;
不能在 WHERE 子句中使用组函数(注意);
可以在 HAVING 子句中使用组函数;
Demo
有以下表product
根据需求写出相应的sql语句:
#需求: 查询所有货品信息
#需求: 查询所有货品的id,productName,salePrice
select id,productName,salePrice from product;
#需求: 查询商品的分类编号。
select dir_id from product;
#需求: 查询所有货品的id,名称和批发价(批发价=卖价*折扣)
select id,productName,salePrice*cutoff from product;
#需求: 查询所有货品的id,名称,和各进50个的成本价(成本=costPirce)
select id,productName,costPrice*50 '进50个的成本价'from product;
#需求: 查询所有货品的id,名称,各进50个,并且每个运费1元的成本
select id,productName,(costPrice+1)*50 '进50个加运费的成本价'from product;
#需求: 查询所有货品的id,名称,各进50个,并且每个运费1元的成本(使用别名)
select id,productName,(costPrice+1)*50 '进50个加运费的成本价'from product;
#需求: 查询商品的名字和零售价。格式:xxx商品的零售价为:xxx
select concat (productName,'商品的零售价为',salePrice) '零售价' from product;
#需求: 查询货品零售价为119的所有货品信息.
select * from product where salePrice=119;
#需求: 查询货品名为罗技G9X的所有货品信息.
select * from product where productName='罗技G9X';
#需求: 查询货品名 不为 罗技G9X的所有货品信息.
select * from product where productName<>'罗技G9X';
#需求: 查询分类编号不等于2的货品信息
select * from product where dir_id<>2;
#需求: 查询货品名称,零售价小于等于200的货品
select productName,salePrice from product where salePrice<=200;
#需求: 查询id,货品名称,批发价大于350的货品
select id,productName,costPrice from product where costPrice > 350;
#需求: 查询id,货品名称,批发价在300-400之间的货品(使用 and)
select id,productName,costPrice from product where costPrice>300 and costPrice<400;
#需求: 查询id,货品名称,分类编号为2,4的所有货品
select id,productName,dir_id from product where dir_id=2 or dir_id=4;
#需求: 查询id,货品名词,分类编号不为2的所有商品
select id,productName,dir_id from product where dir_id !=2;
#需求: 选择id,货品名称,分类编号的货品零售价大于等于250或者是成本大于等于200
select id,productName,dir_id from product where salePrice>=250 or costPrice >=200;
#需求: 查询id,货品名称,批发价在300-400之间的货品(使用between)
select id,productName,salePrice from product where salePrice between 300 and 400;
#需求: 查询id,货品名称,批发价不在300-400之间的货品
select id,productName,salePrice from product where salePrice not between 300 and 400;
#需求: 查询id,货品名称,分类编号为2,4的所有货品
select id,productName,dir_id from product where dir_id=2 or dir_id=4;
#需求: 查询id,货品名称,分类编号不为2,4的所有货品
select id,productName,dir_id from product where dir_id!=2 or dir_id!=4;
#需求: 查询商品名为NULL的所有商品信息。
select * from product where productName = '';
#需求: 查询id,货品名称,货品名称匹配'%罗技M9_'
select id,productName from product where productName like '%罗技M9_';
#需求: 查询id,货品名称,分类编号,零售价大于等于200并且货品名称匹配'%罗技M1__'
select id,productName from product where salePrice>=200 and productName like '%罗技M1__';
#需求: 查询id,货品名称,分类编号,零售价并且按零售价降序排序
select id,productName,dir_id,salePrice from product order by salePrice desc;
#需求: 查询id,货品名称,分类编号,零售价先按分类编号排序,再按零售价排序
select id,productName,dir_id,salePrice from product order by dir_id and salePrice;
#需求: 查询M系列并按照批发价排序(加上别名)
select id,productName,salePrice*cutoff 'M系列' from product where productName like "%M%" order by salePrice*cutoff;
#需求: 查询分类为2并按照批发价排序(加上别名)
select id,productName,salePrice*cutoff '分类为2' from product where dir_id = 2 order by salePrice*cutoff;
#需求:分页查询
#每页最多3条记录: pageSize = 3:
#------------------------------------------
#第一页:
#第二页:
#第三页:
#第四页:
#第七页:
#第N页:
select * from product limit 0,3;
#需求: 查询所有商品平均零售价
select avg(saleprice) '所有商品平均零售价'from product;
#需求: 查询商品总记录数(注意在Java中必须使用long接收)
select count(*) '总记录数'from product;
#需求: 查询分类为2的商品总数
select count(*) '分类为2的商品总数'from product where dir_id=2;
#需求: 查询商品的最小零售价,最高零售价,以及所有商品零售价总和
select min(salePrice) '最小零售价',max(salePrice) '最高零售价',sum(salePrice) '零售价总价' from product
#需求: 查询每个商品分类编号和每个商品分类各自的平均零售价
select avg(salePrice) '平均零售价',dir_id from product group by dir_id;
#需求: 查询每个商品分类编号和每个商品分类各自的商品总数。
select count(*) '商品总数',dir_id from product group by dir_id;
#需求: 查询每个商品分类编号和每个商品分类中零售价大于100的商品总数:
select count(*) '商品总数',dir_id,saleprice FROM product where salePrice>100 group by dir_id;
#需求: 查询零售价总和大于1500的商品分类编号以及总零售价和:
select sum(salePrice) '零售价总和',dir_id from product group by dir_id having sum(salePrice)>1500;