MySQL之day2

最新推荐文章于 2024-07-14 19:46:27 发布

anchang5350

最新推荐文章于 2024-07-14 19:46:27 发布

阅读量106

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/liuyuanq/p/11134597.html

版权

参考: 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

　　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(*)

转载于:https://www.cnblogs.com/liuyuanq/p/11134597.html

anchang5350

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL之day2

参考:https://www.cnblogs.com/programmer-tlh/p/5782418.html1.建立数据给 product 表格table 1 mysql> show databases; 2 +--------------------+ 3 | Database | 4 +------------------...
复制链接

扫一扫