MySql基础

我的网站:欢迎大家访问

Day26-MySql基础

数据库概念

概念

什么是数据库？

数据库(Database,简称DB)

是按照数据结构来组织、存储和管理数据的仓库.保存有组织的数据的容器(通常是一个文件或一组文件)

发展分类

1. 人工管理阶段

2. 文件系统阶段

3. 层次数据库和网状数据库技术阶段

4. 关系型数据库技术阶段

5. 后关系数据库技术阶段

   我们主要学习关系型数据库，常见的关系型数据库有：
   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;