<———本篇只涉及单表查询———–>
建一张student表:
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
age INT,
sex VARCHAR(2)
)
插入几条数据:
INSERT INTO student VALUES(1,’张三’,20,’男’)
INSERT INTO student VALUES(2,’李四’,20,’男’)
INSERT INTO student VALUES(3,’王五’,20,’男’)
1.查询所有列 :
SELECT *FROM TABLE_NAME
SELECT *FROM student
2.查询指定列:
(多列逗号分隔)
SELECT column FROM TABLE_NAME
SELECT id FROM student
3.查询时指定别名:
SELECT id (as)’学号’,NAME (as)’姓名’ FROM student
4.查询时合并:
为student添加两列成绩
ALTER TABLE student ADD jsp INT ,ADD java INT;
UPDATE student SET jsp=90,java=80
(注意:只能合并数值类型 非数字类型忽略)
5.查询时去重复列:
SELECT DISTINCT sex FROM student
6.条件查询(where):
逻辑条件
and:查询name=张三,age=20的学生
SELECT *FROM student WHERE NAME=’张三’ AND age=20
or:查询name=张三或者李四的学生
SELECT *FROM student WHERE NAME=’张三’ or name=’李四’
比较条件:
,<,=>,<=, ==,<>:
查询age>19的学生信息
SELECT *FROM student WHERE age>19
![]()
between and:
查询java成绩在60到90到学生信息
select *from student between 60and 90(包括边界)
判断非空(NULL):null是没有值 ’ ‘是空
SELECT *FROM student WHERE NAME NOT IS NULL
模糊条件(like):
SELECT *FROM student WHERE NAME LIKE ‘张_’ (下划线是匹配一个)
SELECT *FROM student WHERE NAME LIKE ‘张%’(%匹配多个)
![]()
7.聚合查询
sum():
SELECT SUM(java) AS java总和 FROM student
![]()
avg():
SELECT AVG(java) AS java平均 FROM student
![]()
max():
SELECT MAX(java) AS java最高分 FROM student
![]()
min():
SELECT min(java) AS java最低分 FROM student
![]()
count():
(注意count统计的数量不包括空,
使用该列不为空的字段)
SELECT COUNT(*)FROM student
8.分页查询(limit 起始行,查询几行)从0开始:
分页:当前页 每页显示多少条
SELECT FROM student LIMIT ( 当前页 -1) 每页显示多少条 ,每页显示多少条;
查1.2行(第一页)
SELECT *FROM student LIMIT 0,2
查3.4行(第二页)
SELECT *FROM student LIMIT 2,2
查5.6行(第三页)
SELECT *FROM student LIMIT 4,2
9.查询排序(order by) asc/desc
(默认情况按插入顺序排序)
asc:顺序 正序,数值 递增,字母 自然顺序
desc:倒序 反序,数值 递减,字母 自然反序
SELECT *FROM student ORDER BY id DESC
![]()
(注意:多个排序条件重复的地方才执行第二种排序)
SELECT *FROM student ORDER BY jsp ASC,java DESC
10.分组查询(group by):
先分组(group by sex)
再统计每组人数(count(*))
SELECT sex,COUNT(*) FROM student GROUP BY sex
11.分组查询后筛选(having)
分组之前条件用where分组之后用having
SELECT sex,COUNT() FROM student GROUP BY sex HAVING COUNT()>1;
12.查询时添加一个常量列:
添加一列“软件工程”
SELECT id,NAME,age,sex,’软件工程’ as 专业 FROM student