继上文查询相关语句的一些案例

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

#安全等于：<=>
#案例：查询没有奖金的员工名和奖金率
SELECT
    last_name,
    commission_pct
FROM
    employees
WHERE
    commission_pct <=> NULL;
 
#案例2：查询工资为12000的员工信息
SELECT
    last_name,
    salary
FROM
    employees
WHERE
    salary <=> 12000;
 
# is null pk <=>
/*
IS NULL:紧紧可以判断null值，可读性较高，建议使用 
<=>：既可以判断null值，又可以判断普通的数值，可读性较低
*/
#测试
#1.查询工资大于12000的员工姓名和工资
SELECT
    salary
FROM
    employees
WHERE
    salary > 12000;
 
#2.查询员工号为176的员工的姓名和部门号和年薪
SELECT
    last_name,
    department_id,
    salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
    employees;
 
#3.选择工资不在5000到12000的员工的姓名和工资
SELECT
    first_name,
    last_name,
    salary
FROM
    employees
WHERE
    NOT (salary < 5000 AND salary > 12000);
 
#----------------------------------------------------
SELECT
    first_name,
    last_name,
    salary
FROM
    employees
WHERE
    salary NOT BETWEEN 5000
AND 12000;
 
#4.选择在20或50号部门工作的员工姓名和部门号
SELECT
    first_name,
    last_name,
    department_id
FROM
    employees
WHERE
    department_id = 20
OR department_id = 50;
 
#5.选择公司中没有管理者的员工姓名及job_id
SELECT
    first_name,
    last_name,
    job_id
FROM
    employees
WHERE
    manager_id IS NULL;
 
#6.选择公司中有奖金的员工姓名、工资和奖金级别
SELECT
    first_name,
    last_name,
    salary,
    commission_pct
FROM
    employees
WHERE
    commission_pct <> 'NULL' #7.选择员工姓名的第三个字母是a的员工姓名
    SELECT
        last_name
    FROM
        employees
    WHERE
        last_name LIKE '__a%';
 
#8.选择姓名中有字母a和c的员工姓名
SELECT
    last_name
FROM
    employees
WHERE
    last_name LIKE '%a%'
AND last_name LIKE '%c%';
 
#9.显示出来表中first_name以'e'结尾的员工信息
SELECT
    first_name
FROM
    employees
WHERE
    first_name LIKE '%e';
 
#10.显示出部门编号在80-100之间的姓名、职位
SELECT
    first_name,
    last_name
FROM
    employees
WHERE
    department_id BETWEEN 80
AND 100;
 
#11.显示manager_id是100,101,110的员工姓名、职位
SELECT
    first_name,
    last_name,
    job_title
FROM
    employees
JOIN jobs
WHERE
    manager_id = 100
OR manager_id = 101
OR manager_id = 110;
 
#查询没有奖金，且工资小鱼18000的salary，last_name
SELECT
    salary,
    last_name
FROM
    employees
WHERE
    commission_pct IS NULL
AND salary < 18000;
 
#查询表中job_id不为'it'或者工资为12000的员工信息
SELECT
    *
FROM
    employees
WHERE
    job_id <> 'IT'
OR salary = 12000;
 
#查看部门deparments表的结构
DESC departments;
 
#查询部门departments表中设计到了那些位置编号
SELECT DISTINCT
    location_id
FROM
    departments;