1.准备测试表
##创建数据库
create database company;
use company
##创建表
CREATE TABLE employee5(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int
)
2.给表里插入数据
insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);
2.1查看下表内数据
select * from employee5;
--简单查看
mysql> SELECT name, salary, dep_id from employee5;
--四则运算查看
select 4*7;
--查看14个月的总薪资
SELECT name, salary, salary*14 FROM employee5;
--定义显示格式
concat()函数用于连接字符串
select concat (`name`, `salary`*14) 总工资 from employee5
--单条件查询
工资等于5000的人的名字 where 指定条件
SELECT name from employee5 WHERE salary=5000;
--多条件查询
工资大于5000并小于6000的人
SELECT name from employee5 WHERE salary>5000 and salary<6000;
--关键字between and
工资在5000和15000之间的人
SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
--关键字 is null
查找职位描述是空的人名
SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
查找职位描述不是为空的人名
SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;
2.2.NULL说明:
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作,结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他
--关键字in
查询工资是4000 或者5000 或者6000 或者 9000
SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000;
--查询工资最高的人名字
select `name`,salary from employee5 where salary= (select max(salary) from employee5)
据都小,所以NULL值总是排在最前。