数据库操作语言DQL

准备环境

素材1

1. 准备一张表，包含id int 序号、name varchar 姓名、age int 年龄

create table t3(id int,name varchar(50),age int);

2. 再插入测试数据

insert into t3 values (1,"zhangsan",23),(2,"lisi",23),(3,"wangwu",18);

素材2

1. 创建数据库

create database company;

2. 表

create table company.employees(
    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
);

3. 查看表结构

desc company.employees;

4. 插入数据

insert into company.employees
(name, sex, hire_date,post, job_description, salary, office, dep_id)values
  ('jack','male','20240622','instructor','teach',5000,501,100),
  ('tom','male','20240620','instructor','teach',8000,501,100),
  ('xdn','female','20240612','instructor','teach',50000,501,100),
  ('lyx','female','20240613','hr','hrcc',5000,502,101),
  ('zjx','male','20240613','sale','',30000,502,101);

简单查询

1. 查看所有列
   进入数据库的时候只输入表名，不进入数据库，就输入库名.表名
   语法

select * from 表名；

2. 查看部分列

select 列1，列2，列3... 表名；

3. 通过四则运算查询
   例：看一看年薪

select name,salary,salary*14 from employees;

条件查询

1. 单条件查询where
   例：查看hr部门的员工姓名

select name,post from employees where post="hr";

2. 多条件查询AND/OR
   例1：查询hr部门的员工，并且工资大于1000(and)

select name,salary from employees where post="hr" and salary>1000;

例2：查询所有部门的员工，并且工资是5000或者8000的员工(or)

select name,salary from employees where salary=5000 or salary=8000;

3. 关键字BETWEEN AND在什么之间
   例1：查一查薪资在5000到1500的员工

select name,salary from employees where salary between 5000 and 15000;

例2：查一查薪资不在5000~15000的员工（not）

select name,salary from employees where salary not between 5000 and 15000;

4. 关键字IN集合查询
   例1：查询工资是4000,5000,6000,9000的员工

select name,salary from empolyees where salary in (4000,5000,6000,9000);

例2：查询工资不是4000,5000,6000,9000的员工

select name,salary from empolyees where salary not in (4000,5000,6000,9000);

5. 关键字IS NULL
   例1：查询职位为空的员工信息

select id,name,job_description from employees where job_description is NULL;

例2：查询职位不为空的员工信息

select id,name,job_description from employees where job_description is not NULL;

例3：查询空格

select id,name,job_description from employees where job_description="";

6. 关键字LIKE模糊查询
   例1：查询姓名为x开头的员工信息

select * from employees where name like 'x%';

例2：查询姓名为x开头的员工信息,几个_代表几个字符

select * from employees where name like 'x__';

查询排序

例1：以工资升序排列

select * from 表名 order by 工资的列名 ASC；

例2：以工资降序排列

select * from 表名 order by 工资的列名 DESC；

例5：工资最高的前五名

select * from 表名 order by 工资的列名 DESC LIMIT 5；