MySql 表&库操作

MySql 表&库操作

一 查询操作-单表查询

• 简单查询
• 过滤查询: () >算术运算符> NOT > AND >OR
  • BETWEEN 条件一 AND 条件二
  • IN(?,?)
  • LIKE ‘%keyWord’ ‘keyWord%’ ‘%keyWor_’ ‘%eyW%’
  • IS NULL 空
• 结果排序: ASC(升序_默认) DESC 降序
• 分页查询: LIMIT (currentPage-1)*pageSize,pageSize 从索引0开始
• 聚合函数: AVG,SUM,COUNT,MAX,MIN
• 分组查询: GROUP BY 列名 HAVING 组函数;
• CRUD
  • select * from table_name;
  • insert into table_name(column01,column01…) values(value01,value02…);
  • update table_name set column01=value01,column02=value02…;
  • delete table_name where id = ?;

-- 查看数据库中存在哪些表:
SHOW TABLES;

-- 查看表结构：
DESC product;

-- 查看表的详细定义(定义表的SQL语句)					
SHOW CREATE TABLE product;

-- 删除表:
DROP TABLE table_name;		


-- MyISAM 具有批量插入,查询速度块,但不支持事务,不支持外键
-- InnoDB 支持事务,支持外键,支持行级锁定,性能较低

-- 1_简单查询:
-- 查询所有货品信息
SELECT * FROM product;
-- 查询所有货品的id,productName,salePrice
SELECT id,productName,salePrice FROM product;

-- 2_消除结果中重复的数据,只对某一列有效;
SELECT DISTINCT dir_id FROM product;

-- 3_实现数学运算查询:
-- 查询所有货品的id，名称和批发价(批发价=卖价*折扣)
SELECT id,productName,salePrice*cutoff FROM product;

-- 查询所有货品的id，名称，和各进50个的成本价(成本=costPrice)
SELECT id,productName,costPrice*50 FROM product;

-- 查询所有货品的id，名称，各进50个，并且每个运费1元的成本
SELECT id,productName,(costPrice+1)*50 FROM product;

-- 4_设置列名的别名
-- 查询所有货品的id，名称，各进50个，并且每个运费1元的成本(使用别名)
SELECT id,productName,(costPrice+1)*50 as mo FROM product

-- 5_ 设置显示格式
-- 查询商品的名字和零售价。  格式：xxx商品的零售价为：xxx
SELECT  CONCAT(productName,'商品的零售价为：',salePrice) as productSalePrice FROM product;

-- 6_ 过滤查询
-- 6_1 比较运算符: = >= <= > < !=(<>)
-- 查询货品零售价为119的所有货品信息.
SELECT * FROM product WHERE salePrice = 119;

-- 查询货品名为罗技G9X的所有货品信息.
SELECT * FROM product WHERE productName = '罗技G9X';

-- 查询货品名 不为 罗技G9X的所有货品信息.
SELECT * FROM product WHERE productName <>  '罗技G9X';
SELECT * FROM product WHERE productName != '罗技G9X';

-- 查询分类编号不等于2的货品信息
EXPLAIN SELECT  id,productName,cutoff FROM product WHERE id <> 2 ORDER BY cutoff LIMIT 0,5;
-- 添加唯一索引
ALTER TABLE product ADD UNIQUE CUTOFF_NAME ON(cutoff(2,2));
ALTER TABLE product ADD INDEX CUTOFF(cutoff);
DESC product;

-- 查询货品名称,零售价小于等于200的货品
SELECT productName,salePrice FROM product WHERE salePrice <= 200;

-- 查询id，货品名称，批发价大于350的货品
SELECT id,productName,salePrice*cutoff as wholesale FROM product WHERE (salePrice*cutoff)>350 ;

-- 思考：使用where后面使用别名不行,总结select和where的执行顺序 where 优先执行

-- MySQL查询区分大小写: BINARY
SELECT * FROM product WHERE BINARY productName ='罗技G9X';

-- 6_2逻辑运算:() 运算符 NOT ADD OR
-- 选择id，货品名称，批发价在300-400之间的货品
SELECT id,productName,salePrice*cutoff as price FROM product where (salePrice*cutoff)>300 AND (salePrice*cutoff)<400;

-- 选择id，货品名称，分类编号为2,4的所有货品
EXPLAIN SELECT id,productName FROM product WHERE id ON(2,4);

-- 选择id，货品名词，分类编号不为2的所有商品
SELECT id,productName FROM product WHERE id <> 2;


-- 选择id，货品名称，分类编号的货品零售价大于等于250或者是成本大于等于200
SELECT id,productName FROM product WHERE salePrice>=250 OR costPrice>=200;

-- 范围查询-BETWEEN AND
-- 选择id，货品名称，批发价在300-400之间的货品
SELECT id,productName,(salePrice*cutoff) AS price FROM product WHERE (salePrice*cutoff) BETWEEN 300 AND 400;

-- 选择id，货品名称，批发价不在300-400之间的货品
SELECT id,productName,(salePrice*cutoff) AS price FROM product WHERE (salePrice*cutoff) NOT  BETWEEN 300 AND 400;

-- 6_3集合查询- IN  使用IN运算符，判断列的值是否在指定的集合中。
-- 选择id，货品名称，分类编号为2,4的所有货品
SELECT id,productName FROM product WHERE id IN(2,4);

-- 选择id，货品名称，分类编号不为2,4的所有货品
SELECT id,productName FROM product WHERE id NOT IN(2,4);

-- 6_4空值查询 IS NULL
--  查询商品名为NULL的所有商品信息。
SELECT * FROM product WHERE productName is NULL;

-- 6_5模糊查询 LIKE  使用LIKE运算符执行通配查询，查询条件可包含文字字符或数字：
-- 查询id，货品名称，货品名称匹配'%罗技M9_'
EXPLAIN SELECT id,productName FROM product WHERE productName LIKE '%罗技M9_';

-- 查询id，货品名称，分类编号,零售价大于等于200并且货品名称匹配'%罗技M1__'
SELECT id,productName,dir_id,salePrice FROM product WHERE salePrice>=200 AND productName LIKE '%罗技M___';

-- 6.6 结果排序
-- 使用ORDER BY子句将结果的记录排序： 升序ASC 默认 DESC 降序	

-- 选择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,salePrice;


-- 查询M系列并按照批发价排序(加上别名)
SELECT id,productName,salePrice*cutoff as price FROM product  WHERE productName LIKE '%M%' ORDER BY price;

-- 6_7分页查询 LIMIT
-- 假分页(逻辑分页): 把数据全部查询出来,存在于内存中,翻页的时候,直接从内存中去截取.
-- 真分页(物理分页): 每次翻页都去数据库中去查询数据.
-- 假分页: 翻页比较快,但是第一次查询很慢,若数据过大,可能导致内存溢出.
-- 真分页: 翻页比较慢,若数据过大,不会导致内存溢出.

SELECT * FROM product LIMIT 0,5;

-- 6_7聚集函数
-- 查询所有商品平均零售价
SELECT AVG(salePrice) FROM product;

-- 查询商品总记录数(注意在Java中必须使用long接收)
SELECT COUNT(id) FROM product;

-- 查询分类为2的商品总数
SELECT COUNT(id) FROM product WHERE dir_id = 2;

-- 查询商品的最小零售价，最高零售价，以及所有商品零售价总和
SELECT MIN(salePrice) as minprice,MAX(salePrice) as maxprice,SUM(salePrice) as sum FROM product;

-- 7_0 分组查询 GROUP BY 将表中数据分为若干组,再对分组后的数据进行统计计算,一般使用聚合函数才使用GROUP BY
-- 查询每个商品分类编号和每个商品分类各自的平均零售价
SELECT productName,dir_id,AVG(salePrice) FROM product GROUP BY dir_id;

-- 查询每个商品分类编号和每个商品分类各自的商品总数。
SELECT productName,dir_id,COUNT(dir_id) FROM product GROUP BY dir_id;
SELECT productName,dir_id,sum(dir_id) FROM product GROUP BY dir_id;

-- 查询每个商品分类编号和每个商品分类中零售价大于100的商品总数：
SELECT productName,salePrice,COUNT(id) FROM product WHERE salePrice > 100 GROUP BY dir_id;

-- 查询零售价总和大于1500的商品分类编号以及总零售价和：
SELECT productName,dir_id,SUM(salePrice)as sum FROM product WHERE salePrice > 100 GROUP BY dir_id HAVING sum>1500;

-- CRUD 
-- 查找商品名称为null的商品
SELECT * FROM product WHERE productName IS NULL;

-- 修改
UPDATE product SET productName = '联想' WHERE productName IS NULL;

-- 删除某表中的某一行数据
DELETE product WHERE id = 21;

-- 插入一行数据
INSERT INTO product(productName,dir_id,salePrice,supplier,brand,cutoff,costPrice) VALUES('ROG',4,120,'华硕','华硕',0.2,30);

二 查询—多表操作

查询所有的货品信息+对应的货品分类信息
SELECT * FROM product p JOIN productdir d WHERE p.id=d.id

1_内连接查询
查询货品id，货品名称，货品所属分类名称

  查询零售价大于200的无线鼠标
  SELECT product.id,productName,salePrice,dirName FROM product  JOIN productdir ON dir_id = productdir.id WHERE salePrice > 200 AND dir_id = '2'

  查询零售价大于200的无线鼠标(使用表的别名)
	SELECT p.id,p.productName,p.salePrice,d.dirName FROM product p  JOIN productdir d ON dir_id = d.id WHERE p.salePrice > 200 AND dir_id = '2'

  查询每个货品对应的分类以及对应的库存
	SELECT productName,dirName,store_num FROM product p JOIN productdir d ON dir_id=d.id JOIN product_stock ps ON product_id = p.id ORDER BY p.id

2_自连接: 必须起别名
	查询每个商品分类的名称和父分类名称（所属分类的名称）:
	SELECT super.dirName,son.dirName FROM productdir super JOIN productdir son ON son.parent_id = super.id

3_子查询: 一个查询语句嵌套到另一个查询语句中,内层查询的结果可作为外层查询条件__一般的，嵌套在WHERE或者FROM字句中。
	查询零售价比罗技MX1100更高的所有商品信息。
	SELECT * FROM product WHERE salePrice > (SELECT salePrice FROM product WHERE productName='罗技MX1100')

	查询分类编号和折扣与罗技M100相同的所有商品信息。
	SELECT * FROM product WHERE (dir_id,costPrice) = (SELECT dir_id,costPrice FROM product WHERE productName = '罗技M100')


数据操作(DML) 
1_插入
INSERT INTO productdir(dirName,parent_id) VALUES('枪战鼠标',3)

2_批量插入
INSERT INTO productdir(dirName,parent_id) VALUES('枪战鼠标02',3),('金牌鼠标',4),('枪战鼠标03',3)

查询
SELECT * FROM productdir WHERE dirName='枪战鼠标02' or dirName='金牌鼠标' or dirName='枪战鼠标03';


3_修改
**需求:将零售价大于300的货品零售价上调0.2倍**
UPDATE product SET salePrice=salePrice*20 WHERE salePrice>300
SELECT * FROM product WHERE salePrice>200

**需求:将零售价大于300的有线鼠标的货品零售价上调0.1倍**
UPDATE product SET salePrice=salePrice*1.2 WHERE salePrice>200

4_删除
DELETE FROM product WHERE salePrice=330