MySQL之day2

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

 1.建立数据给 product 表格table

 1 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 +----------------+--------------+------+-----+---------+-------+
View Code

1.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 为列设置别名   ,  常数查询

 1 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   从结果中删除重复行

 1 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)
View Code

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

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

1.4  根据where 语句 来选择 记录

 

　　select <列名>,  ...

　　 from <表名>

　　where <条件表达式>;

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

1 mysql> select product_name
2     -> 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 3
5 mysql>
View Code

1.5 注释

   单行 :   --

　双行 :  /*   xxx */

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

1.6.1 算术运算符

 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)
View Code

注意 :

　　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

 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)
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  Warnings: 0
19 
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)
View Code
 1 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>
View Code

num 

　　null 不在其中计算

avg

　　会把 null 删除再平均  ! 

max /min 

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

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

 1 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)
View Code
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>
View Code

 3.1 分组 Groud by

 1 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)
View Code

  　　group by 就像一把切分表的刀

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

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

 

　　select <列名>,...

　　from <表名>

　　where <表达式>

　　group by <列名>, ...

 1 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)
View Code

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

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

1 mysql> select purchase_price ,sale_price ,count(*)
2     -> from product
3     -> 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
View Code

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

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

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

　　having

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

 1 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 product
15     -> group by product_type
16     -> ;
17 +--------------+----------+
18 | product_type | count(*) |
19 +--------------+----------+
20 | 衣服         |        2 |
21 | 办公用品     |        2 |
22 | 厨房用品     |        4 |
23 +--------------+----------+
24 3 rows in set (0.00 sec)
View Code

　构成要素:

　　　　常数

　　　　聚合函数

　　　　Groud by 指定的列名 即 聚合键

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

     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)
View Code

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

可以使用聚合函数 

　　如 :  order by count(*)

posted @ 2019-07-04 19:59  找工作好爽啊哈哈哈呜  阅读(159)  评论(0)  编辑  收藏  举报