版权声明:本文为博主原创文章,未经博主允许不得转载。
一、单选题
1.1 基本查询
1.1.1 普通查询
1.如图所示:( B )
- A .is
- B .as
- C .on
- D .in
2.关于数据分页,下列描述不正确的是( C )
- A .可以使用limit关键字限定结果集的数量
- B . limit写在from 表名之后,格式为limit 起始位置,偏移量
- C .如果limit子句只有一个参数时,该参数表示的是起始位置,起始值默认为1
- D .如果limit子句只有一个参数时,该参数表示的是偏移量,起始值默认为0 【√】
3.关于结果集排序,下列描述不正确的是( D )(选一项)
- A .使用order by子句对结果集按照某种顺序进行排序
- B .order by 列名 ASC:表示对结果集进行升序排列
- C .order by 列名 DESC:表示对结果集进行降序排列
- D .order by 列名:表示对结果集进行降序排列 【默认:升序排列】
4.已有student表如下图所示,查询学生缴纳的学费(tuition)金额,按学费从大到小排序,下列选项正确的是 ( B )
- A .select name,tuition from student ORDER BY tuition; 【默认:升序排列】
- B .select name,tuition from student ORDER BY tuition desc;
- C .select name tuition from student ORDER BY tuition DESC; 【语法错误】
- D .select name,tuition from student ORDER BY tuition ASC; 【升序排列】
5.已有student表如下图所示,查询表中的学生姓名(name),入学日期(enrollment_date),缴纳的学费(tuition)金额,按学费和入学日期依次从小到大排序,下列选项正确的是( )
A.select enrollment_date,tuition from t_student ORDER BY tuition,enrollment_date asc;【×】
B.select name,enrollment_date,tuition from t_student ORDER BY tuition, enrollment_date;
C.select name,enrollment_date,tuition from t_student ORDER BY tuition, enrollment_date asc;
D.select name,enrollment_date,tuition from t_student ORDER BY tuition, enrollment_date DESC; 【×】
6.已有student表如下图所示,查询缴纳学费(tuition)的金额(不重复),下列选项正确的是( B )
- A .select DISTINT tuition from student;
- B . select DISTINCT tuition from student;
- C . select DISTINCT ,tuition from student;
- D .select tuition from student;
7.关于数据库的查询,下列描述不正确的是( B )
- A .使用as关键字可以给指定字段起别名
- B .使用limit关键字可以显示结果集 【如果结果集的记录很多,可以使用LIMIT关键字限定结果集的数量】
- C .使用order by 关键字可以给结果集进行排序
- D .使用distinct 关键字去除重复的记录
1.1.2 条件查询
8.已有student表如下图所示,查询小白和小灰的学生信息,下列选项正确的是( C )
- A .select * from student where name=‘小白’ ,name=‘小灰’;
- B .select * from student where name=‘小白’ and name=‘小灰’;
- C .select * from student where name=‘小白’ or name=‘小灰’;
- D .select * from student where name=‘小白’ & name=‘小灰’;
9.已有student表如下图所示,查询未缴纳宿舍费用的学生姓名,下列选项正确的是( C )
- A .SELECT name from student where dorm_money is not null;
- B .SELECT name from student where dorm_money = null;
- C .SELECT name from student where dorm_money is null;
- D .SELECT name from student where dorm_money == null;
10.已有student表如下图所示,查询姓名以 a—l开头以s结尾的学生信息,下列选项正确的是( C )
A .SELECT * from student where name REGEXP '^[a-l]s$';
B .SELECT * from student where name like '^[a-l]*s$';
C .SELECT * from student where name REGEXP '^[a-l]+s$';
D .SELECT * from student where name REGEXP '[a-l]+s$';
11.关于逻辑运算符,下列描述不正确的是( D )
- A .表达式Not表示非关系
- B . 表达式AND表示与关系
- C .表达式OR表示或关系
- D .表达式XOR表示或者关系 【XOR:异或关系】
12.已有student表如下图所示,查询总学费为除去4000和4500之外的学生信息,下列选项正确的是( A )
# 查询总学费为除去4000和4500之外的学生信息
SELECT * FROM t_student WHERE NOT tuition + IFNULL(dorm_money, 0) in (4000, 4500);
13.已有student表如下图所示,查询性别为女且姓名以赵或王开头的学生信息,下列选项正确的是( B )
# 查询性别为女且姓名以赵或王开头的学生信息
SELECT * FROM t_student WHERE sex='女' AND name LIKE '赵%' OR name LIKE '王%';
SELECT * FROM t_student WHERE (name LIKE '赵%' OR name LIKE '王%') AND sex='女';
A. select * from student WHERE name like'赵%'or name like '王%' and sex='女';
B. select * from student WHERE (name like'赵%'or name like '王%') and sex='女';
C. select * from student WHERE name like'赵%'and name like '王%';
D. select * from student WHERE name like'赵%' and name like '王%' and sex='女';
14.关于子句的执行顺序,下列正确的是( D )
- A .FROM→WHERE→LIMIT→SELECT→ORDER BY
- B .FROM→SELECT→WHERE→ORDER BY→LIMIT
- C .FROM→WHERE→SELECT→LIMIT→ORDER BY
- D .FROM→WHERE→SELECT→ORDER BY→LIMIT
二、多选题
三、练习题
3.1 基本查询
3.1.1 普通查询
# 明确要使用的逻辑空间
USE lsj2021_practice;
# 查询全部记录
SELECT * FROM t_emp;
# 查询指定记录
SELECT empno, ename, sal FROM t_emp;
# 使用列别名查询
SELECT
empno,
sal*12 AS "income"
FROM t_emp;
# 查询员工表的前几条记录 代表从第5条到第10条数据
SELECT empno, ename FROM t_emp LIMIT 5,5;
3.1.2 条件查询
1、t_student 表
# 创建表
CREATE TABLE t_student(
id INT UNSIGNED PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex ENUM("男", "女") NOT NULL,
enrollment_date DATE NOT NULL,
tel CHAR(11) NOT NULL,
tuition DECIMAL(10, 2) DEFAULT NULL
);
# 查询
SELECT name, enrollment_date, tuition FROM t_student ORDER BY tuition ASC, enrollment_date ASC;
select enrollment_date,tuition from t_student ORDER BY tuition,enrollment_date asc;
select name,enrollment_date,tuition from t_student ORDER BY tuition, enrollment_date;
select name,enrollment_date,tuition from t_student ORDER BY tuition, enrollment_date asc;
select name,enrollment_date,tuition from t_student ORDER BY tuition, enrollment_date DESC;
# 查询缴纳学费(tuition)的金额(不重复)
SELECT DISTINCT tuition FROM t_student;
# 查询小白和小灰的学生信息
SELECT * FROM t_student WHERE name='小白' OR name='小灰';
# 添加一列数据
ALTER TABLE t_student
ADD dorm_money DECIMAL;
# 删除某一列数据
ALTER TABLE t_student
DROP dorm_money;
# 查询未缴纳宿舍费用的学生姓名
SELECT name, dorm_money FROM t_student WHERE dorm_money is null;
# 查询总学费在4000至4500的学生信息(包含5000,7000)
SELECT * FROM t_student WHERE tuition + IFNULL(dorm_money, 0) >= 5000 AND tuition + IFNULL(dorm_money, 0) <= 7000;
SELECT * FROM t_student WHERE tuition + IFNULL(dorm_money,0) BETWEEN 5000 AND 7000;
# 查询姓名以a—l开头以s结尾的学生信息
SELECT * FROM t_student WHERE name like '^[a-l]*s$';
SELECT * FROM t_student WHERE name REGEXP '^[a-l]+s$';
SELECT * FROM t_student WHERE name REGEXP '[a-l]+s$';
# 查询总学费为除去4000和4500之外的学生信息
SELECT * FROM t_student WHERE NOT tuition + IFNULL(dorm_money, 0) in (4000, 4500);
# 查询性别为女且姓名以赵或王开头的学生信息
SELECT * FROM t_student WHERE sex='女' AND name LIKE '赵%' OR name LIKE '王%';
SELECT * FROM t_student WHERE (name LIKE '赵%' OR name LIKE '王%') AND sex='女';
2、t_emp 表
# 明确要使用的逻辑空间
USE lsj2021_practice;
# 查询全部记录
SELECT * FROM t_emp;
# 查询指定记录
SELECT empno, ename, sal FROM t_emp;
# 使用列别名查询
SELECT
empno,
sal*12 AS "income"
FROM t_emp;
# 查询员工表的前几条记录 代表从第5条到第10条数据
SELECT empno, ename FROM t_emp LIMIT 5,5;
# 数据集排序
SELECT empno, ename, sal, deptno FROM t_emp ORDER BY sal DESC;
SELECT ename, sal, hiredate FROM t_emp ORDER BY hiredate DESC;
# 多个排序字段
SELECT empno, ename, sal, hiredate FROM t_emp ORDER BY sal DESC, hiredate DESC;
SELECT deptno, ename, sal, hiredate FROM t_emp ORDER BY deptno ASC, sal DESC;
# 取出工资排在前5位的数据
SELECT deptno, ename, deptno, sal, hiredate FROM t_emp ORDER BY sal DESC LIMIT 0, 5;
# 结果集中的重复记录
SELECT DISTINCT job FROM t_emp;
# 条件查询
SELECT deptno, empno, ename, sal FROM t_emp WHERE deptno=10 AND sal >= 2000;
SELECT deptno, empno, ename, sal FROM t_emp WHERE (deptno=10 OR deptno=20) AND sal >= 2000;
# 结合算数运算符查询:注意:普通数字+NULL = NULL 普通数字*NULL = NULL
# 遇到Null值转换为0,DATEDIFF():计算2个日期偏差多少天
SELECT NOW(); # 取到当前日期
# 查询使用算数运算符:部门编号:10, 年收入:大于等于15000,工龄:大于等于20年
SELECT deptno, ename, sal, hiredate FROM t_emp WHERE deptno=10 AND (sal+IFNULL(comm, 0))*12 >= 15000 AND DATEDIFF(NOW(), hiredate)/365 >= 20;
# 查询使用比较运算符:部门是:10, 20, 30 , 入职时间:1985年以前, 职位不能是SALESMAN
SELECT deptno IN(10,20,30) FROM t_emp;
SELECT deptno, ename, job, hiredate, sal FROM t_emp WHERE deptno IN(10,20,30) AND hiredate < '1985-1-1' AND job != 'SALESMAN';
# mgr:上级员工编号
#查询使用比较运算符:佣金不为空的
SELECT deptno, ename, job, hiredate, sal, comm FROM t_emp WHERE comm is NOT NULL;
#查询使用比较运算符:佣金为空的
SELECT deptno, ename, job, hiredate, sal, comm FROM t_emp WHERE comm IS NULL AND sal BETWEEN 2000 AND 3000 AND ename LIKE "%S%" OR ename LIKE "_LAKE";
# 正则表达式
SELECT ename, sal, comm FROM t_emp WHERE comm is NOT NULL AND sal BETWEEN 1000 AND 3000 AND ename REGEXP "^[\\u4e00-\\u9fa5]{2,4}$";
#查询使用逻辑运算符
SELECT deptno, ename,sal FROM t_emp WHERE NOT deptno IN(10,20) XOR sal>=2000;
# 二进制按位运算
SELECT 3 & 7;
SELECT 3 | 7;
四、编程题
版权声明:本文为博主原创文章,未经博主允许不得转载。