mysql 找工作_MySQL之day2

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.00sec)13

14 mysql>show tables;15 +----------------+

16 | Tables_in_shop |

17 +----------------+

18 | product |

19 +----------------+

20 1 row in set (0.09sec)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 * fromproduct;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 fromproduct;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.00sec)15

16 mysql> select product_type as tpye , 39 as number ,sale_price fromproduct;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 distinctproduct_type2 -> fromproduct;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> selectproduct_name2 -> where product_tpye = '衣服'

3 -> fromproduct;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> selectproduct_name,sale_price2 -> ,sale_price * 2 as"sale_price_x2"3 -> fromproduct;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 ascalculation;+-------------+

| 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 * fromproduct ;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.00sec)15

16 mysql> alter table product add column co varchar(12) ;17 Query OK, 0 rows affected (1.08sec)18 Records: 0 Duplicates: 0 Warnings: 0

19

20 mysql> select * fromproduct ;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 -> fromproduct;3 +------------+-----------------------+----------+

4 | count(co ) | count(purchase_price) | count(*) |

5 +------------+-----------------------+----------+

6 | 0 | 6 | 8 |

7 +------------+-----------------------+----------+

8 1 row in set (0.00sec)9

10 mysql>

View Code

num

null 不在其中计算

avg

会把 null 删除再平均  !

max /min

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

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

1 mysql> select count(distinctproduct_type)2 -> fromproduct;3 +------------------------------+

4 | count(distinct product_type) |

5 +------------------------------+

6 | 3 |

7 +------------------------------+

8 1 row in set (0.00sec)9

10 mysql> select distinct count( product_type)11 -> fromproduct;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(distinctsale_price)-> fromproduct;+-----------------+--------------------------+

| sum(sale_price) | sum(distinct sale_price) |

+-----------------+--------------------------+

| 16780 | 16280 |

+-----------------+--------------------------+

1 row in set (0.00sec)

mysql>

View Code

3.1 分组 Groud by

1 mysql> select product_type , count(*)2 -> fromproduct3 -> group byproduct_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 -> fromproduct3 -> where product_type = '衣服'

4 -> group bypurchase_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 -> fromproduct3 -> where product_type = '衣服'

4 -> group bypurchase_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 -> fromproduct3 -> group byproduct_type4 -> having count(*) = 2;5 +--------------+----------+

6 | product_type | count(*) |

7 +--------------+----------+

8 | 衣服 | 2 |

9 | 办公用品 | 2 |

10 +--------------+----------+

11 2 rows in set (0.00sec)12

13 mysql> select product_type ,count(*)14 -> fromproduct15 -> group byproduct_type16 ->;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 -> fromproduct3 -> group byproduct_type4 -> 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> selectproduct_id , product_name , sale_price , purchase_price-> fromproduct-> 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(*)