MySQL之day2

参考: https://www.cnblogs.com/programmer-tlh/p/5782418.html

 1.建立数据给 product 表格table

mysql> show databases; 2 +--------------------+ 3 | Database| 4 +--------------------+ 5 | information_schema | 6 | myshool| 7 | mysql  | 8 | performance_schema | 9 | shop|10 | sys|11 +--------------------+12 6 rows in set (0.00 sec)13 14 mysql> show tables;15 +----------------+16 | Tables_in_shop |17 +----------------+18 | product|19 +----------------+20 1 row in set (0.09 sec)21 22 mysql> describe product;23 +----------------+--------------+------+-----+---------+-------+24 | Field  | Type | Null | Key | Default | Extra |25 +----------------+--------------+------+-----+---------+-------+26 | product_id | char(4)  | NO| PRI | NULL||27 | product_name| varchar(100) | NO| | NULL||28 | product_type| varchar(32)  | NO| | NULL||29 | sale_price | int(11)  | YES  | | NULL||30 | purchase_price | int(11)  | YES  | | NULL||31 | regist_date| date | YES  | | NULL||32 +----------------+--------------+------+-----+---------+-------+

1.1  查询所有

mysql> select * from product; 2 +------------+--------------+--------------+------------+----------------+-------------+ 3 | product_id | product_name | product_type | sale_price | purchase_price | regist_date | 4 +------------+--------------+--------------+------------+----------------+-------------+ 5 | 0001| T恤  | 衣服 |1000 |500 | 2009-09-20  | 6 | 0002| 打孔器| 办公用品 |500 |320 | 2009-09-11  | 7 | 0003| 运行T恤  | 衣服 |4000 |2800 | NULL| 8 | 0004| 菜刀 | 厨房用品 |3000 |2800 | 2009-09-20  | 9 | 0005| 高压锅| 厨房用品 |6800 |5000 | 2009-01-15  |10 | 0006| 叉子 | 厨房用品 |500 |NULL | 2009-09-20  |11 | 0007| 砧板 | 厨房用品 |880 |790 | 2008-04-28  |12 | 0008| 圆珠笔| 办公用品 |100 |NULL | 2009-11-11  |13 +------------+--------------+--------------+------------+----------------+-------------+14 8 rows in set (0.00 sec)

 1.2 为列设置别名   ,  常数查询

mysql> select 'product_type' as tpye , 39 as number ,sale_price from product; 2 +--------------+--------+------------+ 3 | tpye | number | sale_price | 4 +--------------+--------+------------+ 5 | product_type | 39 |1000 | 6 | product_type | 39 |500 | 7 | product_type | 39 |4000 | 8 | product_type | 39 |3000 | 9 | product_type | 39 |6800 |10 | product_type | 39 |500 |11 | product_type | 39 |880 |12 | product_type | 39 |100 |13 +--------------+--------+------------+14 8 rows in set (0.00 sec)15 16 mysql> select product_type as tpye , 39 as number ,sale_price from product;17 +--------------+--------+------------+18 | tpye | number | sale_price |19 +--------------+--------+------------+20 | 衣服 | 39 |1000 |21 | 办公用品 | 39 |500 |22 | 衣服 | 39 |4000 |23 | 厨房用品 | 39 |3000 |24 | 厨房用品 | 39 |6800 |25 | 厨房用品 | 39 |500 |26 | 厨房用品 | 39 |880 |27 | 办公用品 | 39 |100 |28 +--------------+--------+------------+29 8 rows in set (0.00 sec)

 1.3   从结果中删除重复行

mysql> select distinct product_type 2 -> from product; 3 +--------------+ 4 | product_type | 5 +--------------+ 6 | 衣服 | 7 | 办公用品 | 8 | 厨房用品 | 9 +--------------+10 3 rows in set (0.03 sec)

　　对含有null 的数据 的列使用distinct关键字, 将null 会保留下来

　　distinct 只能用在第一个列之前.

1.4  根据where 语句 来选择 记录

 

　　select <列名>,  ...

　　 from <表名>

　　where <条件表达式>;

 注意:  随意改变顺序会报错

mysql> select product_name2 -> where product_tpye  = '衣服'3 -> from product;4 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from product' at line 35 mysql>

1.5 注释

   单行 :   --

　双行 :  /*   xxx */

1.6  算术运算符 和逻辑运算符

1.6.1 算术运算符

mysql> select product_name,sale_price 2 -> ,sale_price * 2 as "sale_price_x2" 3 -> from product; 4 +--------------+------------+---------------+ 5 | product_name | sale_price | sale_price_x2 | 6 +--------------+------------+---------------+ 7 | T恤  |1000 |  2000 | 8 | 打孔器|500 |  1000 | 9 | 运行T恤  |4000 |  8000 |10 | 菜刀 |3000 |  6000 |11 | 高压锅|6800 | 13600 |12 | 叉子 |500 |  1000 |13 | 砧板 |880 |  1760 |14 | 圆珠笔|100 |200 |15 +--------------+------------+---------------+16 8 rows in set (0.04 sec)

注意 :

　　null / 3 

　　4 / null

　　2 - null 

　　....

　执行结果都为null

1.6.2 select 计算

mysql> select (23+232)/3 as calculation;+-------------+| calculation |+-------------+| 85.0000 |+-------------+1 row in set (0.03 sec)

1.6.3 比较运算符

注意:     

　　1.符号位置!!!!

　　2.char 排序 按照字典 和 数字不同 ,参考目录模式

　　如 : 11

　　　　1-8

　　　　2

　　　　22

　　　　3

 　　3.  null  不可用于比较!!!

　　　　可以用  is null 或者 not is null 

1.6.4  not  or and 运算符

　　1.  where not <条件表达式>

　　2. where  <表达式>  or / and  <表达式>

　　附:   含有 null 时为 不确定

2   聚合与排序 

2.1　　聚合函数 

• 　　sum 
• 　　avg
• 　　count
• 　　max
• 　　min

 count

mysql> select * from product ; 2 +------------+--------------+--------------+------------+----------------+-------------+ 3 | product_id | product_name | product_type | sale_price | purchase_price | regist_date | 4 +------------+--------------+--------------+------------+----------------+-------------+ 5 | 0001| T恤  | 衣服 |1000 |500 | 2009-09-20  | 6 | 0002| 打孔器| 办公用品 |500 |320 | 2009-09-11  | 7 | 0003| 运行T恤  | 衣服 |4000 |2800 | NULL| 8 | 0004| 菜刀 | 厨房用品 |3000 |2800 | 2009-09-20  | 9 | 0005| 高压锅| 厨房用品 |6800 |5000 | 2009-01-15  |10 | 0006| 叉子 | 厨房用品 |500 |NULL | 2009-09-20  |11 | 0007| 砧板 | 厨房用品 |880 |790 | 2008-04-28  |12 | 0008| 圆珠笔| 办公用品 |100 |NULL | 2009-11-11  |13 +------------+--------------+--------------+------------+----------------+-------------+14 8 rows in set (0.00 sec)15 16 mysql> alter table product add column co varchar(12) ;17 Query OK, 0 rows affected (1.08 sec)18 Records: 0  Duplicates: 0  Waings: 019 20 mysql> select * from product ;21 +------------+--------------+--------------+------------+----------------+-------------+------+22 | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co|23 +------------+--------------+--------------+------------+----------------+-------------+------+24 | 0001| T恤  | 衣服 |1000 |500 | 2009-09-20  | NULL |25 | 0002| 打孔器| 办公用品 |500 |320 | 2009-09-11  | NULL |26 | 0003| 运行T恤  | 衣服 |4000 |2800 | NULL| NULL |27 | 0004| 菜刀 | 厨房用品 |3000 |2800 | 2009-09-20  | NULL |28 | 0005| 高压锅| 厨房用品 |6800 |5000 | 2009-01-15  | NULL |29 | 0006| 叉子 | 厨房用品 |500 |NULL | 2009-09-20  | NULL |30 | 0007| 砧板 | 厨房用品 |880 |790 | 2008-04-28  | NULL |31 | 0008| 圆珠笔| 办公用品 |100 |NULL | 2009-11-11  | NULL |32 +------------+--------------+--------------+------------+----------------+-------------+------+33 8 rows in set (0.00 sec)

mysql> select count(co ),count(purchase_price),count(*) 2 -> from product; 3 +------------+-----------------------+----------+ 4 | count(co ) | count(purchase_price) | count(*) | 5 +------------+-----------------------+----------+ 6 |  0 | 6 |8 | 7 +------------+-----------------------+----------+ 8 1 row in set (0.00 sec) 9 10 mysql>

num 

　　null 不在其中计算

avg

　　会把 null 删除再平均  ! 

max /min 

　　几乎适合所有数据类型的列  而sum /avg 只能数值类型

2.2 也可以在聚合函数中使用  distinct         

mysql> select count(distinct product_type) 2 -> from product; 3 +------------------------------+ 4 | count(distinct product_type) | 5 +------------------------------+ 6 |3 | 7 +------------------------------+ 8 1 row in set (0.00 sec) 9 10 mysql> select distinct count( product_type)11 -> from product;12 +----------------------+13 | count( product_type) |14 +----------------------+15 |8 |16 +----------------------+17 1 row in set (0.06 sec)

mysql> select sum(sale_price), sum(distinct sale_price)-> from product;+-----------------+--------------------------+| sum(sale_price) | sum(distinct sale_price) |+-----------------+--------------------------+|16780 |16280 |+-----------------+--------------------------+1 row in set (0.00 sec)mysql>

 3.1 分组 Groud by

mysql> select product_type , count(*) 2 -> from product 3 -> group by product_type ; 4 +--------------+----------+ 5 | product_type | count(*) | 6 +--------------+----------+ 7 | 衣服 |2 | 8 | 办公用品 |2 | 9 | 厨房用品 |4 |10 +--------------+----------+11 3 rows in set (0.00 sec)

  　　group by 就像一把切分表的刀

  聚合键中包含null, 结果会以 "不确定" 行 (空行) 的形式 表现出来  

3.1.1  使用 where 语句进行group by ,显示是无序的 

 

　　select <列名>,...

　　from <表名>

　　where <表达式>

　　group by <列名>, ...

mysql> select purchase_price ,count(*) 2 -> from product 3 -> where product_type = '衣服' 4 -> group by purchase_price; 5 +----------------+----------+ 6 | purchase_price | count(*) | 7 +----------------+----------+ 8 |500 |1 | 9 |2800 |1 |10 +----------------+----------+11 2 rows in set (0.33 sec)

执行顺序  :   from --> where--> group --> select 

注意:  select 语句 书写聚合键 以外 的列名 会发生错误!!

mysql> select purchase_price ,sale_price ,count(*)2 -> from product3 -> where product_type = '衣服'4 -> group by purchase_price;5 ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.product.sale_price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

注意:  　1. 别名 as 在select 中不可使用 ,因为执行顺序 见上!!!!

　　　　2. 不可在 where 语句中使用 count 聚合语句, 且只有having , select , order by 可以!!!

4.1  为聚合结果使用 指定条件 

　　having

执行顺序 :  select --> from --> where --> group by -->  having 

mysql> select product_type ,count(*) 2 -> from product 3 -> group by product_type 4 -> having count(*) = 2; 5 +--------------+----------+ 6 | product_type | count(*) | 7 +--------------+----------+ 8 | 衣服 |2 | 9 | 办公用品 |2 |10 +--------------+----------+11 2 rows in set (0.00 sec)12 13 mysql> select product_type ,count(*)14 -> from product15 -> group by product_type16 -> ;17 +--------------+----------+18 | product_type | count(*) |19 +--------------+----------+20 | 衣服 |2 |21 | 办公用品 |2 |22 | 厨房用品 |4 |23 +--------------+----------+24 3 rows in set (0.00 sec)

　构成要素:

　　　　常数

　　　　聚合函数

　　　　Groud by 指定的列名 即 聚合键

mysql> select product_type ,count(*)2 -> from product3 -> group by product_type4 -> having product_name = '圆珠笔';5 ERROR 1054 (42S22): Unknown column 'product_name' in 'having clause'

     where  指定 行 所对应的 条件

     having  指定 组 所对应的 条件

5.1 排序  order by 语句 

　　顺序 :  在最后 !!!

　　　　select --> from --> where --> group by -->  having --> order by 

• 　　升序 / 降序  
• 　　asc  / desc 

　　指定多个键时 优先排序 写在前面的  

mysql> select product_id , product_name , sale_price , purchase_price-> from product-> order by sale_price asc , purchase_price desc;+------------+--------------+------------+----------------+| product_id | product_name | sale_price | purchase_price |+------------+--------------+------------+----------------+| 0008| 圆珠笔|100 |NULL || 0002| 打孔器|500 |320 || 0006| 叉子 |500 |NULL || 0007| 砧板 |880 |790 || 0001| T恤  |1000 |500 || 0004| 菜刀 |3000 |2800 || 0003| 运行T恤  |4000 |2800 || 0005| 高压锅|6800 |5000 |+------------+--------------+------------+----------------+8 rows in set (0.32 sec)

　　注意 : 排序键有 null 放在 后面/前面

可以使用聚合函数 

　　如 :  order by count(*)