DQL----数据库查询语言

一、Select 查询操作

互联网用户查询余额，查询装备，查询商品的操作；

DQL----数据库查询语言

创建数据库

#create database company

创建表

mysql> CREATE TABLE company.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 );

查看表结构

#desc database();

DQL----数据库查询语言

插入数据

mysql> 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),

('aofa','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);

如图所示

DQL----数据库查询语言

简答查询：

查看所有列： select * from 表名（前提是在库里面，不在库里要加库名字）

查看部分列：select 列1，列2，列3 from 表名

通过四则运算查询：select name,salary,salary*14 from employee5 ;(查年薪)

条件查询：

1、单条件查询

查询hr部门的员工姓名：

select name，salary from employee5 where post=’hr’

2、多条件查询AND/OR

查询hr的部门员工，并且工资大于1000

Select name,salary from employee5 where post=’hr’ and salary > 1000;

查询所有部门员工，并且工资是6000或者8000

Select name,salary from employee5 where salary=6000 or salary=8000;

3、关键字between and 在什么之间

查询薪资在5000-15000之间

Select name，salary from employee5 where salary between 5000-15000；

查询薪资不在5000-15000之间

Select name，salary from employee5 where salary not between 5000-15000

关键字in 集合查询

工资可能是4000，也可能是5000，还有可能是9000，怎么查

Select name,salary from employee5 where salary in (4000,5000,9000);

关键字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

空格' '-------查询空的那位员工信息

SELECT name,job_description FROM employee5 WHERE job_description=' ';

关键字like模糊查询

好像有个员工叫a

Select * from employee5 where name like ‘a%’

Select * from employee5 where name like ‘a___’

下滑线的长度和名字一样长

查询排序

升序：select * from 表名 order by salary ASC;

降序：select * from 表名 order by salary desc;

工资最高的前五名：

select * from 表名 order by salary desc limit 5 ;

DQL----数据库查询语言

相关推荐