2021-07-07数据库查询语句基础48

最新推荐文章于 2024-09-14 19:55:29 发布

weixin_51630304

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量82

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/weixin_51630304/article/details/118556009

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文介绍了SQL查询的基础，包括使用WHERE子句进行条件筛选，LIKE子句进行模式匹配，以及聚合函数如MIN、MAX、AVG和SUM的应用。此外，还讲解了GROUP BY用于数据分组和HAVING子句进行分组后的过滤，以及LIMIT在限制查询结果数量上的使用。通过对score和student表格的操作，展示了这些概念的实际运用。

摘要由CSDN通过智能技术生成

数据库查询语句基础48

查询语句基础

查询语句基础

where条件

#where 条件
	查看customers表格内容：
	 select * from customers;
	+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
	| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
	+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
	|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
	|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
	|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
	|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
	|   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       | NULL                |
	+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
	5 rows in set (0.00 sec)

	用where条件语句查询cust_id=10001 的顾客：
	select * from customers where cust_id = 10001;
	+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+
	| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      |
	+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+
	|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |
	+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+
	1 row in set (0.00 sec)

like子句


查询student表格内容：
	select * from student;
	+-------+-----------+----------+------+------+-----------+-----------+------------+------------------+
	| stuid | stuname   | password | sex  | gid  | telphone  | address   | birthday   | email            |
	+-------+-----------+----------+------+------+-----------+-----------+------------+------------------+
	| 1     | 张三      | sdaf45   | 男   |    2 | 131333333 | 山东      | 1989-03-12 | 423412345@qq.com |
	| 2     | 李四      | fdg7fd3  | 女   |    1 | 131333333 | 廊坊      | 1999-03-18 | 456346@qq.com    |
	| 3     | 王五      | rt7er8   | 男   |    4 | 131333333 | 山西      | 1995-06-16 | 8474154@qq.com   |
	| 4     | 赵四      | cvbc41   | 女   |    3 | 131333333 | 内蒙古    | 1997-08-22 | 789456@qq.com    |
	| 5     | 王六      | ewr789wx | 男   |    3 | 131333333 | 台湾      | 2000-12-16 | 97456@qq.com     |
	| 6     | 钱七      | jty465tr | 女   |    1 | 131333333 | 深圳      | 1880-03-09 | 78971@qq.com     |
	| 7     | 金蝶      | y4tr84   | 女   |    2 | 123131313 | 北京      | 1898-01-12 | 484564@qq.com    |
	| 8     | 凌凌七    | d4fs56   | 男   |    1 | 123131313 | 山东      | 1997-12-19 | 4564@qq.com      |
	| 9     | 凌厉      | 4fd5s6   | 男   |    1 | 123131313 | 深圳      | 1990-01-01 | 7786@qq.com      |
	+-------+-----------+----------+------+------+-----------+-----------+------------+------------------+
	9 rows in set (0.00 sec)
		使用like子句实现查询stuname中包含字段“七”的学生：
	select * from student where stuname like '%七%';
	+-------+-----------+----------+------+------+-----------+---------+------------+--------------+
	| stuid | stuname   | password | sex  | gid  | telphone  | address | birthday   | email        |
	+-------+-----------+----------+------+------+-----------+---------+------------+--------------+
	| 6     | 钱七      | jty465tr | 女   |    1 | 131333333 | 深圳    | 1880-03-09 | 78971@qq.com |
	| 8     | 凌凌七    | d4fs56   | 男   |    1 | 123131313 | 山东    | 1997-12-19 | 4564@qq.com  |
	+-------+-----------+----------+------+------+-----------+---------+------------+--------------+
	2 rows in set (0.00 sec)

		查询stuname中包含字段“七”,且满足“七”字段前只有一个字段的学生：
		select * from student where stuname like '_七%';
	+-------+---------+----------+------+------+-----------+---------+------------+--------------+
	| stuid | stuname | password | sex  | gid  | telphone  | address | birthday   | email        |
	+-------+---------+----------+------+------+-----------+---------+------------+--------------+
	| 6     | 钱七    | jty465tr | 女   |    1 | 131333333 | 深圳    | 1880-03-09 | 78971@qq.com |
	+-------+---------+----------+------+------+-----------+---------+------------+--------------+
	1 row in set (0.00 sec)
    --注意：where子句中的like在使用%或_时，效率不高，应当注意：
    	--尽可能的不去使用%或_；
    	--如果一定要使用，也尽量不要用在开头。

常用函数（聚集函数）


查询score表格内容：
	select * from score;
	+---------+-------+-----------+-------+------------+
	| scoreid | stuno | subjectid | score | examtime   |
	+---------+-------+-----------+-------+------------+
	|       1 | 1     |         1 |   100 | 2021-03-12 |
	|       2 | 1     |         2 |   100 | 2021-03-12 |
	|       3 | 1     |         3 |    90 | 2021-03-12 |
	|       4 | 2     |         1 |   100 | 2021-03-12 |
	|       5 | 2     |         2 |    79 | 2021-03-18 |
	|       6 | 3     |         1 |    56 | 2021-03-01 |
	|       7 | 4     |         2 |    55 | 2021-03-15 |
	|       8 | 4     |         3 |   100 | 2021-03-21 |
	|       9 | 4     |         1 |    99 | 2021-03-04 |
	|      10 | 5     |         1 |   100 | 2021-03-21 |
	+---------+-------+-----------+-------+------------+
	10 rows in set (0.00 sec)
	查询score表格中最小score，最大score，score平均值，score和：
	select min(score),max(score),avg(score),sum(score) from score;
	+------------+------------+------------+------------+
	| min(score) | max(score) | avg(score) | sum(score) |
	+------------+------------+------------+------------+
	|         55 |        100 |    87.9000 |        879 |
	+------------+------------+------------+------------+
	1 row in set (0.00 sec)


	使用as函数将上述查询结果重命名：
	select min(score) as min,max(score) as max,avg(score)as average,sum(score) sum from score;
	+------+------+---------+------+
	| min  | max  | average | sum  |
	+------+------+---------+------+
	|   55 |  100 | 87.9000 |  879 |
	+------+------+---------+------+
	1 row in set (0.00 sec)
	注意：上述写法中as可以使用空格代替。

	使用count函数统计数据量：
	select count(score) from score;
	+--------------+
	| count(score) |
	+--------------+
	|           10 |
	+--------------+
	1 row in set (0.00 sec)

	select count(*) from score;
	+----------+
	| count(*) |
	+----------+
	|       10 |
	+----------+
	1 row in set (0.00 sec)

	注意：上述两种统计方法区别：前者只对score计数，后者只要一行中存在数据就会计数。

group by


group by根据一列或几列对结果进行分组。
		根据科目id分组对score数据量进行统计：
		select subjectid,count(score) from score group by subjectid;
		+-----------+--------------+
		| subjectid | count(score) |
		+-----------+--------------+
		|         1 |            5 |
		|         2 |            3 |
		|         3 |            2 |
		+-----------+--------------+
		3 rows in set (0.00 sec)

		根据性别对学生人数统计：
		select sex,count(*) from student group by sex;
		+------+----------+
		| sex  | count(*) |
		+------+----------+
		| 女   |        4 |
		| 男   |        5 |
		+------+----------+
		2 rows in set (0.00 sec)

		having在分组计算出结果后，对结果进行类似where条件语句的过滤：
		select sex,count(*) num from student group by sex having num>4;
		+------+-----+
		| sex  | num |
		+------+-----+
		| 男   |   5 |
		+------+-----+
		1 row in set (0.00 sec)

limit


查询score中最低的三条成绩：
	 select score from score order by score limit 3;
	+-------+
	| score |
	+-------+
	|    55 |
	|    56 |
	|    79 |
	+-------+
	3 rows in set (0.00 sec)

	limit n:查询n条数据
	limit n,m:跳过n条数据，查询接下来的m条数据


	查询语句是比较灵活的，熟练掌握还得大家不断地练习。