DQL
1 查询数据
【在navicat中完成】
右击新建查询
- 查询表中所有数据
select * from students;
- 查询指定列
select sname,age from students;
select * from students
where sex='nan' and age =16;
在navicat中可以保存查询语句方便下次使用
select * from students
where sno=1 or sname='wc';
navicat中注释 ctrl+/
- IN查询同一属性的多值,固定范围值
select * from students where sno in(1,2,3);
等同于 select * from students where sno=1 or sno=2 or sno=3;
- 查询sname值为null的记录
select * from students where sname is null;
- 查询sname值不为null的记录
select * from students where sname is not null;
- 查询非值的数据
select * from students where sex!='nan';
- 查询两者之间的数据
select * from students where age>=5 and age<=12;
等同于select * from students where age between 5 and 12;
2 模糊查询
- 查询姓名由5个字母构成的学生记录
select * from students where sname like '_____';
-- 五个“_”
- 查询姓名为5个字母且最后一个字母是x的学生记录
select * from students where sname like '____x';
- 以m开头的学生
select * from students where sname like 'm%';
- 第二个字母是s的
select * from students where sname like '_s%';
- 包含s的
select * from students where sname like '%s%';
3 字段控制查询
- 去除重复查询操作distinct
select distinct name from stu;
- 对字段结果进行运算来生成新的字段
select *, age+score from students;
- 若值是null则变为0来进行运算
select *, ifnull(age,0)+ifnull(score,0) from students;
- 改变新字段列名
select *, ifnull(age,0)+ifnull(score,0) as total from students;
select sname as resname from students;
- navicat中创建表
create table emp(
id int(11) not null,
name varchar(50) default null,
gender varchar(1) default null,
hire_date date default null,
salary decimal(10,0) default null,
performance double(255,0) default null,
manage double(255,0) default null,
department varchar(255) default null
);
-- decimal 定义时划定整数和小数的位数,decimal(10,0)指10位整数,0位小数
在“表”处右击刷新,看到我们新建的表
- navicat中查看建表语句
CREATE TABLE `emp` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`gender` varchar(1) DEFAULT NULL,
`hire_date` date DEFAULT NULL,
`salary` decimal(10,0) DEFAULT NULL,
`performance` double(255,0) DEFAULT NULL,
`manage` double(255,0) DEFAULT NULL,
`department` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
4 排序
- 升序(排序默认升序)
以薪水升序排列
select * from employee order by salary;
等同于select * from employee order by salary ASC;
- 降序
以薪水降序排列
select * from employee order by salary DESC;
- 多重排序
当出现相同值时,采用多重排序
select * from employee order by salary DESC,id DESC;
5 聚合函数
- 计数
数据条数
select conut(*) from employee;
- 条件计数
薪水大于2500的数据条数
select count(*) from employee where salary>2500;
薪水加绩效大于5000的数据条数
select count(*) from employee
where ifnull(salary,0) + ifnull(performance,0)>5000;
- 多条计数
select count(performance),count(manage) from employee;
- 求和
select sum(salary) from employee;
- 多条求和
select sum(salary),sum(performance) from emplyoee;
- 求多条和
select sum(salary+ifnull(performance,0)) from emplyoee;
- 求平均
select avg(salary) from employee;
- 求最大
select max(salary) from employee;
- 求最小
select min(salary) from employee;