SQL 基本條件查詢DQL 練習

DQL

DQL（Data Query Language）是SQL語言中的一種類型，用于執行數據查詢操作。它是SQL的一部分，用于從數據庫中檢索數據。DQL語句用于從一個或多個表中選擇、過濾和排序數據。常見的DQL查詢語句包括SELECT、FROM、WHERE、GROUP BY、HAVING和ORDER BY等關鍵字，它們允許用戶根據特定的條件和需求來查詢數據庫中的數據。DQL語句可以幫助用戶獲取所需的數據，并且可以根據需要對數據進行排序、分組和過濾。

初始化表，及表數據

create table emp(
id int comment '編號',
workno varchar(10) comment '工號',
name varchar(10) comment '姓名',
gender char(1) comment '性別',
age tinyint unsigned comment '年齡',
idcard char(18) comment '身份證號',
workaddress varchar(50) comment '工作地址',
entrydate date comment '入職時間'
)comment '員工表';
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳巖666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '張無忌', '男', 18, '123456789012345670', '北京', '2005-09-01');INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韋一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '趙敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '楊逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑤', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛綺絲', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范涼涼', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陳友諒', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '張士誠', '男', 55, '123567897123465670', '江蘇', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '張三豐', '男', 88, '123656789012345678', '江蘇', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '滅絕', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

問題：

-- 1.查詢年齡等于88的員工
-- 2.查詢年齡小于20的員工信息
-- 3.查詢年齡小于等于20的員工信息
-- 4.查詢沒有身份證號的員工信息
-- 5.查詢有身份證號的員工信息
-- 6.查詢年齡不等于88 的員工信息
-- 7.查詢年齡在15歲（包含）到20歲（包含）之間的員工信息
-- 8.查詢性別為女且年齡小于 25 歲的員工信息
-- 9.查詢年齡等于18 或 20 或 40 的員工信息
-- 10.查詢姓名為兩個字的員工信息
-- 11.查詢身份證號最后一位是X的員工信息

解決代碼：

drop table if exists emp;
create table emp(
id int comment '編號',
workno varchar(10) comment '工號',
name varchar(10) comment '姓名',
gender char(1) comment '性別',
age tinyint unsigned comment '年齡',
idcard char(18) comment '身份證號',
workaddress varchar(50) comment '工作地址',
entrydate date comment '入職時間'
)comment '員工表';
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳巖666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '張無忌', '男', 18, '123456789012345670', '北京', '2005-09-01');INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韋一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '趙敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '楊逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑤', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛綺絲', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范涼涼', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陳友諒', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '張士誠', '男', 55, '123567897123465670', '江蘇', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '張三豐', '男', 88, '123656789012345678', '江蘇', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '滅絕', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');-- 條件查詢
-- 1.查詢年齡等于88的員工
select * from emp where age = 88;-- 2.查詢年齡小于20的員工信息
select * from emp where age < 20;-- 3.查詢年齡小于等于20的員工信息
select * from emp where age <= 20;-- 比較重要
-- 4.查詢沒有身份證號的員工信息
select * from emp where idcard is null;-- 5.查詢有身份證號的員工信息
select * from emp where idcard is not null;-- 6.查詢年齡不等于88 的員工信息
select * from emp where age != 88;
select * from emp where age <> 88;-- 7.查詢年齡在15歲（包含）到20歲（包含）之間的員工信息
select * from emp where age>=15 && age<=20;
select * from emp where age>=15 and age<=20;
select * from emp where age between 15 and 20;
-- between 跟最小值 and跟最大值-- 8.查詢性別為女且年齡小于 25 歲的員工信息
select * from emp where gender = '女' && age < 25;
-- 9.查詢年齡等于18 或 20 或 40 的員工信息
select * from emp where age = 18 || age = 20 || age = 40;
select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in(18,20,40);
-- in（列表）滿足其中一個數值即可-- 10.查詢姓名為兩個字的員工信息
-- LIKE 占位符 _單個字符 %多個字符
select * from emp where name like '__';
-- 形式where 字符 like '' ,里面兩個_兩個字符-- 11.查詢身份證號最后一位是X的員工信息
select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';
-- 多個字符%最后一個X，即%X
-- 或者17個下劃線替代掉%