测试常用SQL语句

数据查询语言（DQL）

查询表的所有字段：

Select * from test;

查询表的指定字段：

Select id,name,age from test;

常见关键字执行顺序：

1. From
2. On
3. Join
4. Where
5. group by
6. Having
7. Select
8. Distinct
9. order by
10. limit

Distinct去重（单个字段去重和多个字段组合去重）：

Select distinct age from test;

Select distinct name,age from test;

Select count(distinct age) from test;

as设置别名：

Select age as “年龄”,name as “姓名” from test as a;

limit限制查询：

(1)从第3条记录开始，一共返回两条记录

Select * from test limit 2,2;

1. 一共返回5条记录

Select * from test limit 5;

1. 从第2条记录开始，一共返回5条记录

Select * from test limit 5 offset 1;

Order by对结果进行排序：

(1)根据id倒序排序

Select * from test order by id desc;

1. 先根据sex倒序排序，然后根据height升序排序

Select * from test order by sex desc, height asc;

where条件查询：

1. 单一条件的查询

select * from test where id = 1;

select * from test where id != 1;

select * from test where height > 170;

select * from test where height >= 175;

select * from test where age < 20;

select * from test where age <= 20;

(2)多条件的查询

select * from test where sex = 1 and height >175;

select * from test where sex = 1 && height >175;

select * from test where height < 165 or height >175;

select * from test where height < 165 || height >175;

(3)查询 age 小于 21，并且 height 小于 165 的学生信息和 age 大于 21，并且 height 小于等于 165 的记录

select * from test where age < 21 xor height >= 165;

like模糊查询

(1)查询username字段包含test的记录

Select * from test where username like “%test%”;

(2)查询username字段开头不为test且department字段等于see的记录

Select * from test where username not like “test%” and department = “see”;

(3)查询username字段test开头且后面只跟一个字符结尾的记录

Select * from test where username like “test_”;

(4)区分大小写

Select * from test where username like binary “TEST_”;

(5)查询包含%的字符串

Select * from test where username like “%\%”;

Is null控制查询

select * from test where sex is null;

select * from test where sex is not null;

Between and范围查询

select * from test where age between 19 and 21;

select * from test where age not between 19 and 21;

Group by分组查询

(1)单字段分组

select * from test group by sex;

(2)多字段分组

select * from test group by age,department;

(3)group_concat()将分组内的值都显示出来

select department,group_concat(username) as "部门员工名字" from test group by department;

(4)group by+聚合函数

# count统计条数

select count(*) from test group by department;

# sum总和

select sum(age) from test group by department;

# max最大值

select max(age) from test group by department;

# min最小值

select min(age) from test group by department;

# 平均值

select avg(age) from test group by department;

(5)with rollup用来在所有记录的最后加上一条记录，显示上面所有记录每个字段的总和(字符串用,连接)(数字相加)

select sum(age) from test group by department with rollup;

select GROUP_CONCAT(username) from test group by department with rollup;

select count(*) from test group by department with rollup ;

Having过滤分组结果集

(1)根据age分组，将分组后的结果过滤出department为see的分组记录

select *,GROUP_CONCAT(username) from test group by age having department = "see";

(2)先查询sex = 1的所有记录，将查询的记录按照department分组，然后过滤出department=see的分组

select *,GROUP_CONCAT(username) from test where sex = "1" group by department having department = "see";

(3)sex = 1的所有记录，将查询的记录按照department分组，然后过滤出max(date) > "2020-05-08"的分组

select *,GROUP_CONCAT(date) from test where sex = "1" group by department having max(date) > "2020-05-08";

多表查询

Cross join交叉连接(多表之间的笛卡尔积)

(1)cross join单独使用

select * from emp cross join dept;

总记录数 = emp记录数* dept记录数

(2)cross join + where

select * from emp as a cross join dept as b on a.dept_id = b.id;

Inner join

1. 标准内连接：两张表相连

select * from emp as a inner join dept as b on a.dept_id = b.id;

select * from emp as a join dept as b on a.dept_id = b.id;

1. 特殊内连接：自连接

Select * from emp as a inner join emp as b on a.leader = b.id;

1. 特殊内连接：不等值连接

Select * from emp as a inner join dept as b on a.dept_id > b.id;

Left/right join外连接

select * from emp as a left join dept as b on a.dept_id = b.id;

select * from emp as a left join dept as b on a.dept_id = b.id where b.id is null;

select * from emp as a right join dept as b on a.dept_id = b.id;

union全连接

(1)union all

select * from emp as a left join dept as b on a.dept_id = b.id

union all

select * from emp as a right join dept as b on a.dept_id = b.id;

(2)union

select * from emp as a left join dept as b on a.dept_id = b.id

union

select * from emp as a right join dept as b on a.dept_id = b.id;

In/exists子查询

1. 比较运算符

select * from emp where dept_id = (select id from dept where name = "销售部");

select * from emp where dept_id <> (select id from dept where name = "销售部");

1. In

select * from emp where dept_id in (select id from dept where name = "财务部" or name ="销售部");

select * from emp where dept_id not in (select id from dept where name = "财务部" or name ="销售部");

1. Exists

select * from emp where exists(select * from dept where id = 1);

regexp正则表达式查询

select * from product where product_name regexp '^2018';

insert插入数据

1. 不指定字段，添加记录

INSERT INTO emp

VALUES

    ( "20", "员工1", 3, 1, 1 );

1. 指定所有字段，添加记录

INSERT INTO emp ( id, NAME, dept_id, leader, is_enable )

VALUES

    ( "20", "员工1", 3, 1, 1 );

1. 复制表数据来插入

INSERT INTO emp ( id, NAME, dept_id, leader, is_enable ) SELECT

15,

NAME,

dept_id,

leader,

is_enable

FROM

    emp

WHERE

id = 1;

update修改数据

(1)修改单个字段

UPDATE emp

SET is_enable = 0

WHERE

    id = 1

(2)修改多个字段

UPDATE emp

SET is_enable = 0,

NAME = "修改的名字",

dept_id = 2

WHERE

id = 1

delete删除数据

1. 删除表中的全部数据

Dekete from emp;

1. 根据条件删除表中的数据

Delete from emp where is_enable = 0 or is_enable is null;