SQL单表查询

sql单表查询

一、单表查询的语法

select列名1,列名2... from 表名
              where 条件
              group by field
              having 筛选
              order by field
              limit 限制条数

二、关键字的执行优先级

from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.where指定的约束条件
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.distinct去重
7.将结果按条件排序：order by
8.limit限制结果的显示条数

三、select 语句

3.1查询全部的列

select　* from;

3.2为列设置别名

​ 别名可以使用中文，使用中文时需要用双引号（"）括起来。请注意
不是单引号（'）

3.3常数的查询

select '商品' as string, 38 as number, '2009-02-24' as date,
product_id, product_name
from product;
执行结果
string | number | date | product_id | product_name
---------+-----------+--------------+-------------+--------------
商品 | 38 | 2009-02-24 | 0001 | t恤衫
商品 | 38 | 2009-02-24 | 0002 | 打孔器
商品 | 38 | 2009-02-24 | 0003 | 运动t恤
商品 | 38 | 2009-02-24 | 0004 | 菜刀
商品 | 38 | 2009-02-24 | 0005 | 高压锅
商品 | 38 | 2009-02-24 | 0006 | 叉子
商品 | 38 | 2009-02-24 | 0007 | 擦菜板
商品 | 38 | 2009-02-24 | 0008 | 圆珠笔

如上述执行结果所示，所有的行中都显示出了select 子句中的常数。

3.4去重

1.语法：select distinct product_type from product;

2.**distinct 关键字只能用在第一个列名之前**
    错误：regist_date, distinct product_type。
3.如果多个数据为null,也会合并成一个。

3.5where语句

​ where子句要紧跟在from子句之后。

​ 首先通过where 子句查询出符合指定条件的记录，然后再选取出select语句指
定的列。

四、算数运算、比较运算和逻辑运算

1.例子select product_name, sale_price,sale_price * 2 as "sale_price_x2"from product;

2.包含null 的计算，结果是null

3.不等号为<>

4.字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。例如'10' 和'11' 同样都是以'1' 开头的字符串，判定为比'2' 小

5.不能对null使用比较运算符（=、<>、>、<、>=、<=）例如：...where purchase_price = null，是查询不到数据的，可以使用is null，反之，希望选取不是null 的记录时，需要使用is not null

6.and运算符的优先级高于or运算符，想要优先执行or运算符时可以使用括号。

select product_name, product_type, regist_date
from product
where product_type = '办公用品'
and regist_date = '2009-09-11'
or regist_date = '2009-09-20';
执行结果
product_name | product_type | regist_date
---------------+--------------+------------
t恤衫 | 衣服 | 2009-09-20
打孔器 | 办公用品 | 2009-09-11
菜刀 | 厨房用具 | 2009-09-20
叉子 | 厨房用具 | 2009-09-20

select product_name, product_type, regist_date
from product
where product_type = '办公用品'
and ( regist_date = '2009-09-11'
or regist_date = '2009-09-20');
product_name | product_type | regist_date
---------------+--------------+------------
打孔器 | 办公用品 | 2009-09-11
这样就选取出了想要得到的“打孔器”。
法则

五、sql中常用的聚合函数

​ sum()求和 count（）计数 max()最大值 min()最小值 avg()平均值

3.1 **聚合函数会将null排除在外。但count(*)例外，并不会排除null。**

3.2 count函数的结果根据参数的不同而不同。count*)会得到包含null的数据
行数，而count()会得到null之外的数据行数。

3.3 在聚合函数的参数中使用distinct，可以删除重复数据。例如sum(distinct 列名

六、group by语句

​ 1.在group by 子句中指定的列称为聚合键或者分组列。

​ 2.聚合键中包含null时，在结果中会以“不确定”行（空行）的形式表现出来

​ 3.where子句时group by的执行结果，会先根据where子句指定的条件进行过滤，然后再进行聚合。

聚合函数和group by子句有关常见错误

​ 错误一：在select子句中书写了多余的列（聚合键之外的列名书写在select 子句之中）

    在使用count 这样的聚合函数时，select 子句中的元素有严格的限制。实际上，使用聚合函数时，select 子句中只能存在以下三种
元素。
● 常数
● 聚合函数
● group by子句中指定的列名（也就是聚合键）
如果需要聚合键之外的列，使用group_concat（列名）

​ 错误二：在where子句中使用聚合函数

七、注释

● 单行注释
书写在“--”之后，只能写在同一行。
● 多行注释
书写在“/”和“/”之间，可以跨多行。

八、代码演示

create table class1_score(id int primary key auto_increment,name char(10),gender enum('男'，'女'),math int default 0,eng int default 0)

insert into class1_score(id,name,gender,math,eng) values

(1,'赵艺','女',85,88),

(2,'钱尔','女',85,90),

(3,'孙散','男',90,90),

(4,'李思','男',80,85),

(5,'周武','男',80,85),

(6,'吴流','女',90,90),

(7,'郑其','男',70,75),

(8,'王','男',70,75),

(9,'冯九','男',95,85),

(10,'陈时','男',60,60);

mysql> select * from class1_score;
+----+--------+--------+------+------+
| id | name   | gender | math | eng  |
+----+--------+--------+------+------+
|  1 | 赵艺   | 女     |   85 |   88 |
|  2 | 钱尔   | 女     |   85 |   90 |
|  3 | 孙散   | 男     |   90 |   90 |
|  4 | 李思   | 男     |   80 |   85 |
|  5 | 周武   | 男     |   80 |   85 |
|  6 | 吴流   | 女     |   90 |   90 |
|  7 | 郑其   | 男     |   70 |   75 |
|  8 | 王     | 男     |   70 |   75 |
|  9 | 冯九   | 男     |   95 |   85 |
| 10 | 陈时   | 男     |   60 |   60 |
+----+--------+--------+------+------+
10 rows in set (0.00 sec)
mysql> select * from class1_score where gender='男';
+----+--------+--------+------+------+
| id | name   | gender | math | eng  |
+----+--------+--------+------+------+
|  3 | 孙散   | 男     |   90 |   90 |
|  4 | 李思   | 男     |   80 |   85 |
|  5 | 周武   | 男     |   80 |   85 |
|  7 | 郑其   | 男     |   70 |   75 |
|  8 | 王     | 男     |   70 |   75 |
|  9 | 冯九   | 男     |   95 |   85 |
| 10 | 陈时   | 男     |   60 |   60 |
+----+--------+--------+------+------+
7 rows in set (0.00 sec)
mysql> select name,math,eng from class1_score where math>89 and eng> 89;
+--------+------+------+
| name   | math | eng  |
+--------+------+------+
| 孙散   |   90 |   90 |
| 吴流   |   90 |   90 |
+--------+------+------+
2 rows in set (0.00 sec)
mysql> select * from class1_score where gender='男' having math>70;
+----+--------+--------+------+------+
| id | name   | gender | math | eng  |
+----+--------+--------+------+------+
|  3 | 孙散   | 男     |   90 |   90 |
|  4 | 李思   | 男     |   80 |   85 |
|  5 | 周武   | 男     |   80 |   85 |
|  9 | 冯九   | 男     |   95 |   85 |
+----+--------+--------+------+------+
4 rows in set (0.00 sec)
#选择性别，根据性别进行分组
mysql> select gender from class1_score group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
+--------+

2 rows in set (0.00 sec)
mysql> select name,math from class1_score where math between 80 and 90;
+--------+------+
| name   | math |
+--------+------+
| 赵艺   |   85 |
| 钱尔   |   85 |
| 孙散   |   90 |
| 李思   |   80 |
| 周武   |   80 |
| 吴流   |   90 |
+--------+------+
6 rows in set (0.00 sec)
#通配符%与_
#%代指不定位，_代指一个字符。
mysql> insert into class1_score values(11,'赵伊伊','女',85,88),(12,'赵十二','女',85,88);
mysql> select * from class1_score;
+----+-----------+--------+------+------+
| id | name      | gender | math | eng  |
+----+-----------+--------+------+------+
|  1 | 赵艺      | 女     |   85 |   88 |
|  2 | 钱尔      | 女     |   85 |   90 |
|  3 | 孙散      | 男     |   90 |   90 |
|  4 | 李思      | 男     |   80 |   85 |
|  5 | 周武      | 男     |   80 |   85 |
|  6 | 吴流      | 女     |   90 |   90 |
|  7 | 郑其      | 男     |   70 |   75 |
|  8 | 王        | 男     |   70 |   75 |
|  9 | 冯九      | 男     |   95 |   85 |
| 10 | 陈时      | 男     |   60 |   60 |
| 11 | 赵伊伊    | 女     |   85 |   88 |
| 12 | 赵十二    | 女     |   85 |   88 |
| 13 | 赵十三    | 男     |   82 |   88 |
| 14 | 赵十思    | 女     |   85 |   85 |
+----+-----------+--------+------+------+
14 rows in set (0.00 sec)
mysql> select * from class1_score where name like '赵%';
+----+-----------+--------+------+------+
| id | name      | gender | math | eng  |
+----+-----------+--------+------+------+
|  1 | 赵艺      | 女     |   85 |   88 |
| 11 | 赵伊伊    | 女     |   85 |   88 |
| 12 | 赵十二    | 女     |   85 |   88 |
+----+-----------+--------+------+------+
mysql> select * from class1_score where name like '_思';                        ,85,85)
+----+--------+--------+------+------+
| id | name   | gender | math | eng  |
+----+--------+--------+------+------+
|  4 | 李思   | 男     |   80 |   85 |
+----+--------+--------+------+------+
1 row in set (0.00 sec)
#查询分组（按性别分组）中人数
mysql> select count(1),gender from class1_score group by gender;
# select count(1) as '男/女人数' ,gender from class1_score group by gender;
+----------+--------+
| count(1) | gender |
+----------+--------+
|        8 | 男     |
|        6 | 女     |
+----------+--------+
2 rows in set (0.00 sec)
#查询并计算男生、女生数学总分
mysql> select sum(math),gender from class1_score group by gender;
+-----------+--------+
| sum(math) | gender |
+-----------+--------+
|       627 | 男     |
|       515 | 女     |
+-----------+--------+
2 rows in set (0.00 sec)
#查询男生、女生英语最高分。
mysql> select max(eng),gender from class1_score group by gender; 
+----------+--------+
| max(eng) | gender |
+----------+--------+
|       90 | 男     |
|       90 | 女     |
+----------+--------+
2 rows in set (0.00 sec)
#求男女生数学平均值
mysql> select avg(math),gender from class1_score group by gender;
+-----------+--------+
| avg(math) | gender |
+-----------+--------+
|   78.3750 | 男     |
|   85.8333 | 女     |
+-----------+--------+
2 rows in set (0.00 sec)
#group by 与 having联用时，having 后的字段要与group by相同

#如果想按性别分，并查看名字，下面会出错，因为按性别划分后只有性别字段
mysql>select name,gender from class1_score group by gender;
error 1055 (42000): expression #1 of select list is not in group by clause and contains nonaggregated column 'db1.class1_score.name' which is not functionally dependent on columns in group by clause; this is incompatible with sql_mode=only_full_group_by

# group_concat
mysql> select group_concat(name),gender from class1_score group by gender;
+---------------------------------------------------------+--------+
| group_concat(name)                                      | gender |
+---------------------------------------------------------+--------+
| 孙散,李思,周武,郑其,王,冯九,陈时,赵十三                 | 男     |
| 赵艺,钱尔,吴流,赵伊伊,赵十二,赵十思                     | 女     |
+---------------------------------------------------------+--------+
2 rows in set (0.00 sec)
#查询名字和数学成绩并按数学成绩升序
mysql> select name,math from class1_score order by math; 
+-----------+------+
| name      | math |
+-----------+------+
| 陈时      |   60 |
| 郑其      |   70 |
| 王        |   70 |
| 李思      |   80 |
| 周武      |   80 |
| 赵十三    |   82 |
| 赵艺      |   85 |
| 钱尔      |   85 |
| 赵伊伊    |   85 |
| 赵十二    |   85 |
| 赵十思    |   85 |
| 孙散      |   90 |
| 吴流      |   90 |
| 冯九      |   95 |
+-----------+------+
14 rows in set (0.00 sec)
#数学升序（asc），英语降序（desc）
#排序键中包含null时，会在开头或末尾进行汇总。
mysql> select name,math,eng from class1_score order by math asc,eng desc;
+-----------+------+------+
| name      | math | eng  |
+-----------+------+------+
| 陈时      |   60 |   60 |
| 郑其      |   70 |   75 |
| 王        |   70 |   75 |
| 李思      |   80 |   85 |
| 周武      |   80 |   85 |
| 赵十三    |   82 |   88 |
| 钱尔      |   85 |   90 |
| 赵艺      |   85 |   88 |
| 赵伊伊    |   85 |   88 |
| 赵十二    |   85 |   88 |
| 赵十思    |   85 |   85 |
| 孙散      |   90 |   90 |
| 吴流      |   90 |   90 |
| 冯九      |   95 |   85 |
+-----------+------+------+
14 rows in set (0.00 sec)
#数学前三（limit 起始位置，获取个数 可用于分页 索引类似，从0开始）
mysql> select name,math from class1_score order by math desc limit 0,3;
+--------+------+
| name   | math |
+--------+------+
| 冯九   |   95 |
| 孙散   |   90 |
| 吴流   |   90 |
+--------+------+
3 rows in set (0.00 sec)

列名>表名>