对数据进行提取
*代码的部分一定要熟练掌握
1.条件查询:
运行符及关键字:=、!=、<>、<、<=、>、>=
between…and
in(set)(查询固定的范围值)
is null / in not null(查询为空的属性/不为空的属性的记录)
and(且)
or(或)
not(非)
例如: **select * from student2 where gender='男' and age=78;(且)
select * from student2 where id=3 or age=78;(或)
select * from student2 where id in(2,3,4);(在某范围内查找)
select * from student2 where gender is not null;(不为空)
select * from student2 where gender !='男';(不等于)**
**注释语句:--加上一个空格**
-
模糊查询:根据指定的关键字进行查询
( %:任意多个字符
_:(下划线)代表任意多个字符)**select * from student2 where name like '______'; select name from student2 where name like '______'; select name from student2 where name like 'h_____'; select name from student2 where name like 'h%'; select name from student2 where name like '%李%'; select name from student2 where name like '__k%';**
3.字段去重:select distinct name from student2;
对数值型字段进行数学运算,生成新字段**:select *,name+score from student2;**(产生的新字段名称为name+score)
把null变成 0: *select ,ifnull(score,0)+ifnull(age,0) from student2;
关键:ifnull(字段,0)
4.把新添加的一行起一个别名: *ifnull(score,0)+ifnull(age,0) as total
select ,ifnull(score,0)+ifnull(age,0) as total from student2;
select name as nenemm from student2;
5.对查询结果排序:order by
升序asc(默认)
降序desc
select * from student3 order by age asc,id desc;
6. 从navicat导出数据
导出结果:
INSERT INTO student2
VALUES (1, ‘hghghgh’, 78, 2541, ‘2019-4-9’, 54665, NULL);
INSERT INTO student2
VALUES (2, ‘kjkjkjkj’, 45457, 8566, ‘2019-4-26’, 9999, NULL);
INSERT INTO student2
VALUES (3, ‘huhuhu’, 44444545, 4444, ‘2019-4-8’, 8888, NULL);
INSERT INTO student2
VALUES (4, ‘kpkpkp’, 254565, 126565, ‘2019-4-10’, 254556, NULL);
INSERT INTO student2
VALUES (5, ‘mkmkmk’, 5584, 566, ‘2019-4-5’, 58555, NULL);
INSERT INTO student2
VALUES (6, ‘mkmkmk’, 586, 23, ‘2019-4-3’, 555, NULL);
INSERT INTO student2
VALUES (7, ‘mkmkmk’, 255, 241, ‘2019-4-10’, 855989, NULL);
7.创建表
8.聚合函数:对查询结果进行统计计算
统计有多少条记录:count()
select count() from student3;
select count() from student3 where age+ifnull(salary,0)> 20;
select count(id),count(name) from student3;
统计一列中的最大值和最小值:max()、min()
select max(salary) from student3;
求和:sum()
select sum(ifnull(salary,0)) from student3;
select sum(ifnull(salary,0)), sum(ifnull(department,0))from student3;
求平均avg()
select avg(salary) from student3;