基础sql语句大全（详细解析，注意事项）

1. 数据库及表操作

注意：如果需要本文的数据库文件，请下载，否则自己建表练习

1.1. 创建、删除、查看数据库

1.1.1. 查看mysql服务器中所有数据库

SHOW DATABASES;
show databases;
show datebases; -- 错误写法

书写规范: SQL语句中的关键字在书写时应该大写;自定义的库名、表(别)名、列(别)名 应该小写。
SELECT * FROM stu WHERE id>2; -- 规范写法
select * from stu where id>2; -- 不规范写法,但也可以执行!
几乎所有的SQL语句在书写完成后,都需要在后面添加分号,表示语句到此结束!

1.1.2. 进入某一数据库（进入数据库后，才能操作库中的表和表记录）

-- 语法：USE 库名;
use mysql;
use test;

-- 查看已进入的库(了解)
select database();

1.1.3. 查看当前数据库中的所有表

– 先进入某一个库,再查看当前库中的所有表 use mysql; show tables;

1.1.4. 删除mydb1库

– 语法：DROP DATABASE 库名; drop database test; drop database mydb1; – 删除不存在的库,会报错!

– 思考：当删除的库不存在时，如何避免错误产生? drop database if exists mydb1; – 如果存在则删除mydb1

1.1.5. 重新创建mydb1库，指定编码为utf8

– 语法：CREATE DATABASE 库名 CHARSET 编码; create database mydb1 charset utf8; # 需要注意的是，mysql中不支持横杠（-），所以utf-8要写成utf8；

– 如果不存在则创建mydb1; create database if not exists mydb1 charset utf8; – 如果不存在则创建

1.1.6. 查看建库时的语句(了解)（并验证数据库库使用的编码）

– 语法：SHOW CREATE DATABASE 库名; show create database mydb1;

1.2. 创建、删除、查看表

1.2.1. 进入mydb1库，删除stu学生表(如果存在)

– 语法：DROP TABLE 表名; use mydb1; drop table if exists stu;

1.2.2. 创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]），建表的语法：

CREATE TABLE 表名(
	列名 数据类型,
	列名 数据类型,
	...
  列名 数据类型
);
创建stu表的SQL语句如下:
use mydb1;
drop table if exists stu;
create table stu(
	id int primary key auto_increment,
	name varchar(50),
	gender varchar(10),
	birthday date,
	score double
);
insert into stu(id,name,gender) value(null,test1,male);

1.2.3. 查看stu学生表结构

– 语法：desc 表名 desc stu;

2. 新增、修改、删除表记录

2.1. 往学生表(stu)中插入记录(数据)

-- 语法：INSERT INTO 表名(列名1,列名2,列名3...) VALUES(值1,值2,值3...);
-- 如果是在cmd中执行插入记录的语句,先 set names gbk; 再插入记录!
set names gbk;
insert into stu(id,name,gender,birthday,score) 
						value(1,tom,male,1995-2-1,90);
insert into stu value(2,王海涛,male,2000-3-4,80);
insert into stu value(3,小乔,female,2001-5-4,75);
select * from stu;

插入记录时需要注意:
	1) 插入值的个数和顺序必须要和列的个数和顺序保持一致!
	2) SQL语句中的字符串和日期类型的值用单引号引起来(即使有些数据库版本支持双引号,也建议使用单引号)
	3) 如果是要给所有的列都赋值,而且值的顺序和个数,和声明时列的顺序和个数完全一致，列名可以省略！
	
常见问题1: 在往stu表中插入中文数据出现乱码解决方法
	1)首先检查当前表(stu)所在的库(mydb1)创建时有没有指定编码
		create database mydb1 charset utf8;
		如果没有指定,将库删除再重建,创建时按照上面的语法指定编码
	2)如果建库时指定了编码,那么在插入数据之前,有没有设置 set names gbk;
		如果没有设置,先设置编码,再插入数据。
	3)如果前面确认了没有问题,可以尝试设置 set names utf8;再插入数据。
	4)如果上面都确认了没有问题,最后可以尝试,在cmd窗口顶部,右键"属性",在弹出的窗口中点击"选项",在底部,勾选"使用旧版控制台",确定

常见问题2: 关于查询stu表中的数据没有对齐的解决方法
	1)关闭之前的cmd窗口,新开一个cmd窗口,连接上mysql服务器
	2)设置set names GBK;编码之后再查询,如果还是没有对齐看一步
	3)再新开一个cmd窗口,使用 mysql --default-character-set=gbk -uroot -proot 连接mysql服务器,连接后不用再 set names gbk; 直接查询数据, 看数据是否对齐。

2.2. 查询stu表所有学生的信息

– 语法：SELECT 列名 | * FROM 表名 select * from stu;

2.3.修改stu表中所有学生的成绩，加10分特长分

– 修改语法: UPDATE 表名 SET 列=值,列=值,列=值…[WHERE子句]; update stu set score=score+10;

2.4.修改stu表中编号为1的学生成绩，将成绩改为83分。

update stu set score=83 where id=1;
提示：where子句用于对记录进行筛选过滤，保留符合条件的记录，将不符合条件的记录剔除。

2.5.删除stu表中所有的记录

– 删除记录语法: DELETE FROM 表名 [where子句] delete from stu; – 仅删除符合条件的 delete from stu where id=1;

3. 查询表记录

3.1. 基础查询

– 准备数据： 以下练习将使用db10库中的表及表记录，请先进入db10数据库!!

3.1.1.查询emp表中的所有员工，显示姓名，薪资，奖金

select * from emp; -- 查询所有列
select name,sal,bonus from emp; -- 查询指定列

*(星号)是通配符,在select后面表示查询所有的列

3.1.2.查询emp表中的所有部门和职位

select dept,job from emp;
-- 上面的查询结果中有大量重复记录,可以通过distinct剔除重复记录
select distinct dept,job from emp;

3.2.WHERE子句查询

3.2.1. 查询emp表中【薪资大于3000】的所有员工，显示员工姓名、薪资

select name,sal from emp where sal>3000;

3.2.2. 查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工，显示员工姓名、总薪资

select name,sal+bonus from emp; -- 查询所有员工的薪资加奖金
select name,sal+bonus from emp where sal+bonus > 3500; -- 有误差

– ifnull(列名, 值)函数: 判断指定的列是否包含null值，如果有null值，用第二个值替换null值 select name,sal+ifnull(bonus,0) from emp where sal+ifnull(bonus,0)>3500;

– 注意查看上面查询结果中的表头，如何将表头中的 sal+bonus 修改为 “总薪资” select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp where sal+ifnull(bonus,0)>3500;

– 使用as可以为表头指定别名(另外as可以省略) select name 姓名,sal+ifnull(bonus,0) 总薪资 from emp where sal+ifnull(bonus,0)>3500;

3.2.3. 查询emp表中【薪资在3000和4500之间】的员工，显示员工姓名和薪资

-- 不包含3000和4500
select name,sal from emp where sal>3000 and sal<4500;
-- 包含3000和4500
select name,sal from emp where sal>=3000 and sal<=4500;
-- 如果包含3000和4500,也可以使用 between..and...
select name,sal from emp where sal between 3000 and 4500;

3.2.4. 查询emp表中【薪资为 1400、1600、1800】的员工，显示员工姓名和薪资

select name,sal from emp where sal=1400 or sal=1600 or sal=1800;

-- 或者使用in: in前面的列的值只要等于in后面括号中的任何一个值,就算满足条件!
select name,sal from emp where sal in(1400,1600,1800);

3.2.5. 查询薪资不为1400、1600、1800的员工，显示员工姓名和薪资

select name,sal from emp where not(sal=1400 or sal=1600 or sal=1800);
-- 或
select name,sal from emp where sal not in(1400,1600,1800);

3.2.6.(自己完成) 查询emp表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资。

select name,sal from emp where sal>4000 or sal<2000;

3.2.7.(自己完成) 查询emp表中薪资大于3000并且奖金小于600的员工，显示员工姓名、薪资、奖金。

select name,sal,bonus from emp where sal>3000 and ifnull(bonus,0)<600;

3.2.7. 查询没有部门的员工（即部门列为null值）

select * from emp where dept=null; -- 错误写法!
select * from emp where dept is null; -- 判断某一列中有没有null,用is,而不是用=

– 思考：如何查询有部门的员工（即部门列不为null值） select * from emp where dept is not null; select * from emp where not(dept is null);

3.3. 模糊查询

模糊查询可以通过like关键字按照指定的模式进行匹配
需要配合 % 和 _(下划线)使用
%: 匹配0或多个任意字符
_: 匹配1个任意字符

3.3.1. 查询emp表中姓名中以"刘"字开头的员工，显示员工姓名。

select name from emp where name like 刘%;

3.3.2.查询emp表中姓名中包含"涛"字的员工，显示员工姓名。

select name from emp where name like %涛%

3.3.3.查询emp表中姓名以"刘"开头，并且姓名为两个字的员工，显示员工姓名。

select name from emp where name like 刘_;
select name from emp where name like 刘__;

3.4.多行函数查询

count(*|列名) -- 统计查询的结果中的所有列或某一列的行数
	max(列名) -- 求某一列中的最大值,例如,max(sal)求最高薪资
	min(列名) -- 求某一列中的最小值,例如,min(sal)求最低薪资
	sum(列名) -- 求某一列中所有值的和,例如,sum(sal)求薪资这一列中所有值的和
	avg(列名) -- 求某一列中所有值的平均值,例如,avg(sal)求薪资这一列的平均薪资

3.4.1.统计emp表中薪资大于3000的员工个数

-- 查询薪资大于3000的员工有哪些
select * from emp where sal>3000;
-- 统计薪资大于3000的员工个数
select count(*) from emp where sal>3000;
select count(name) from emp where sal>3000;
select count(bonus) from emp where sal>3000;
# 使用多行函数对某一列统计,如果该列中包含null值,会直接将null值丢弃,不参与统计

3.4.2. 求emp表中的最高/最低薪资

select max(sal) from emp;
select min(sal) from emp;

3.4.3. 统计emp表中所有员工的薪资总和(不包含奖金)

select sum(sal) from emp;
select sum(bonus) from emp;

3.4.4. 统计emp表员工的平均薪资(不包含奖金)

select avg(sal) from emp;
select sum(sal)/count(*) from emp;

#多行函数和分组之间的联系
1) 如果不进行分组,那么整个查询结果默认就是一个组,在统计时就返回一个结果
	select * from emp; -- 查询结果有12条记录,默认是一个组
	此时如果使用多行函数对该查询结果进行统计,其实就是对这一个组进行统计,统计的结果也只有一个
	select count(*) from emp;
2) 如果添加了分组,最终分成了几个组,在统计时,就返回几个统计结果
	select count(*) from emp; -- 默认一个组,统计结果只有一个
	select gender,count(*) from emp group by gender; -- 分成两个组,统计结果有两个
	select job,count(*) from emp group by job; -- 分成3个组,统计结果就有3个

3.5. 分组查询

语法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;

3.5.1.对emp表，按照部门对员工进行分组，查看分组后效果。

select * from emp group by dept;
-- 按照部门分组,统计每一组的人数
select dept,count(*) from emp group by dept;

3.5.2.对emp表按照职位进行分组，并统计每个职位的人数，显示职位和对应人数

-- 按照职位分组,统计每一组的人数
select job,count(*) from emp group by job;
# 在分组之后进行统计,显示的列中除了统计结果,还可以将进行分组的列一并显示

3.5.3. 对emp表按照部门进行分组，求每个部门的最高薪资(不包含奖金)，显示部门名称和最高薪资

select dept,max(sal) from emp group by dept;

3.6.排序查询

语法：SELECT 列名 FROM 表名 `ORDER BY 列名 [ASC|DESC]`
ASC(默认)升序，即从低到高；DESC 降序，即从高到低。

3.6.1.对emp表中所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资。

select name,sal from emp order by sal; -- 默认是升序,asc可以省略
select name,sal from emp order by sal asc;

3.6.2.对emp表中所有员工的奖金进行降序(从高到低)排序，显示员工姓名、奖金。

select name,bonus from emp order by bonus desc;

-- 按照奖金降序排序,如果奖金相同,再按照薪资降序排序
select name,bonus,sal from emp order by bonus desc,sal desc;

3.7.分页查询

在mysql中，通过limit进行分页查询，查询公式为：
`limit (页码-1)*每页显示记录数, 每页显示记录数`

3.7.1.查询emp表中的所有记录，分页显示：每页显示3条记录，返回所有页的数据

select * from emp limit 0,3;  -- 查询第 1 页
select * from emp limit 3,3;  -- 查询第 2 页
select * from emp limit 6,3;  -- 查询第 3 页
select * from emp limit 9,3;  -- 查询第 4 页

3.7.2.求emp表中薪资最高的前3名员工的信息，显示姓名和薪资

-- 按照薪资降序排序(从高到低),再分页,每页显示3条,只查询第1页
select name,sal from emp order by sal desc limit 0,3;

3.7.3.其他函数

curdate() -- 返回当前日期,格式:年月日
curtime() -- 返回当前时间,格式:时分秒
sysdate() / now() -- 返回当前日期+时间,格式:年月日 时分秒

year(日期)/month(日期)/day(日期)/hour(时间)/minute(时间)/second(时间)
-- 以上6个函数分别获取日期+时间中的 年份/月份/天数/小时/分钟/秒值

concat(s1,s2,s3..sn) -- 将传入其中的子字符串拼接在一起。
concat_ws(x,s1,s2,s3..sn) -- 将传入其中的子字符串拼接在一起，在拼接时会通过第一个参数,即间隔符进行拼接。

3.7.4.查询emp表中所有【在1993和1995年之间出生】的员工，显示姓名、出生日期。

select name,birthday from emp 
where birthday between 1993 and 1995; -- 日期不能和数值比较,错误!
-- 方式一: 将birthday中的年份提取出来,和1993、1995进行比较
select name,birthday from emp
where year(birthday) between 1993 and 1995;

-- 方式二: 将1993、1995转成日期(1993-1-1、1995-12-31)再和birthday比较
select name,birthday from emp
where birthday between 1993-1-1 and 1995-12-31;

3.7.5.查询emp表中本月过生日的所有员工

-- 获取当前月份 和 员工的出生月份 进行比较
select * from emp
where month( now() )=month( birthday );

-- 获取下个月过生日的员工
select * from emp
where (month( now() )+1) % 12 =month( birthday ) % 12;

3.7.6.查询emp表中员工的姓名和薪资（薪资格式为: xxx(元) ）

select name,concat( sal, (元) ) from emp;

– 补充练习：查询emp表中员工的姓名和薪资（薪资格式为: xxx/元 ） select name,concat( sal, ‘/元’ ) from emp; select name,concat_ws( ‘/’, sal, ‘元’ ) from emp;

mysql的字段约束： 1）主键约束: 如果一个列可以唯一的表示一行表记录（或可以作为一行表记录的唯一标识），通常会给这样的列添加主键约束，通常情况下，每张表都会有一个主键。主键特点：唯一且不为空。（但不是每个唯一且不为空的列都是主键！） 如何添加主键约束: create table stu( id int primary key auto_increment, … ); 主键自增策略: 如果主键是数值类型,可以为主键添加自增策略(目的是为了更方便插入主键的值) 添加了自增策略后，以后再插入数据时，可以不为主键赋值，数据库会自己维护一个变量（AUTO_INCREMENT）,该变量的值从1开始,每次用完后会自动加1,当插入数据时,如果没有给主键赋值,数据库就会从AUTO_INCREMENT变量上获取一个值,作为主键值插入到表中。

2）非空约束: 如果某一列的值要求不能为空（某些系统要求用户性别 或 密码不能为空），可以为这个列添加非空约束，这个列的值就不能为空（但可以重复） 如何添加非空约束: create table stu( gender varchar(10) not null, ); 3）唯一约束: 如果某一列的值要求不能重复，可以为这个列添加唯一约束，这个值就不能重复（但可以为空） 如何添加唯一约束: create table stu( username varchar(50) unique not null, email varchar(20) unique, );

4）外键约束（foreign key）: 问题1: 如何保存两张表数据之间的对应关系? 在其中的一张表中添加列,用于保存另外一张表的主键,以此来保存两张表数据之间的对应关系。 例如：在emp表中添加一个dept_id列,用于保存dept表中的id列,表示员工所属的部门编号

问题2: 如何避免在删除部门后,员工表中出现冗余数据? 方式一: 在删除每一个部门之前,先检查部门下还有没有对应的员工,如果有,先将员工删除或者移到别的部门,再删除部门即可 方式二: 通知数据库员工表和部门表之间存在对应关系,员工表中的dept_id列是要严格参考部门表中的id列。即设置dept_id这个列为外键。通知完后，数据库会一直帮我们盯着，如果再删除部门，部门下有员工，数据库就会阻止我们删除，或者当我们插入一个员工，而员工对应的部门编号在部门表中是不存在的，数据库会组织我们插入！

问题3: 什么是外键? 外键就是用于通知数据库两张表(比如部门和员工表)数据之间存在对应关系的这么一个列。 例如：将emp的dept_id设置为外键,就等同于告诉数据库,emp和dept表之间存在对应关系。

问题4: 如何添加外键? create table dept( id int primary key auto_increment, name varchar(50) ); create table emp( id int primary key auto_increment, name varchar(50), dept_id int, foreign key(dept_id) references dept(id) );

练习: 切换到db20库，分别查询dept和emp表中的数据 1）在没有将dept_id设置为外键的情况下去删除一个部门,查看是否能删除? 可以删除，数据库不知道两张表存在对应关系，所以不会阻止我们删除！ 2）如果将dept_id设置为外键的情况下去删除一个部门,查看是否能删除? 如果部门下有员工，删除会失败，此时数据库知道两张表存在对应关系 并且会帮我们维护这个关系，所以会阻止我们删除有员工的部门！

3）如果加了级联删除,则在删除某一个部门的同时,先删除该部门下的所有员工,再删除部门本身!

4. 多表查询

4.1. 连接查询

– 准备数据： 以下练习将使用db30库中的表及表记录，请先进入db30数据库!!!

4.1.1. 查询部门和部门对应的员工信息

select * from dept,emp;

上面的SQL语句执行的结果中有大量错误的数据，一般我们不会直接使用这种查询。
笛卡尔积查询: 是指两张表联查,其中一张表有m条记录,另一张表有n条记录,笛卡尔积查询的结果就是m*n条。
虽然上面的结果中包含错误数据，但也包含正确数据。可以通过where子句将其中错误的记录剔除，只保留正确的记录。

select * from dept,emp where emp.dept_id=dept.id;

如何书写where条件:
1)两张表联查,通常这两张表是有对应关系的,找到两张表中的对应关系的列(dept_id)
2)找到该列后,再找出这个列所对应的另外一张表的主键,让这个列和另外一张表的主键相等即可

4.2.连接查询

4.2.1. 查询【所有部门】及部门对应的员工，如果某个部门下没有员工，员工显示为null即可

select * from dept left join emp on emp.dept_id=dept.id;
#select * from emp right join dept on emp.dept_id=dept.id;
【左外连接查询】：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。

– 44.查询【所有员工】及员工所属部门，如果某个员工没有所属部门，部门显示为null select * from dept right join emp on emp.dept_id=dept.id;

【右外连接查询】：可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null。

4.3.子查询练习

– 准备数据：以下练习将使用db40库中的表及表记录，请先进入db40数据库!!!

4.3.1.列出薪资比’王海涛’的薪资高的所有员工，显示姓名、薪资

-- 查询王海涛的薪资
select sal from emp where name=王海涛; #2450
-- 查询 薪资比王海涛的薪资还高的员工
select name,sal from emp where sal > (
	select sal from emp where name=王海涛
);

– 46.列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位。 – 查询’刘沛霞’的职位 select job from emp where name=‘刘沛霞’;

-- 查询 和刘沛霞从事相同职位的员工
select name,job from emp where job=(select job from emp where name=刘沛霞);

4.4.多表查询练习

4.4.1.列出在’培优部’任职的员工，假定不知道’培优部’的部门编号，显示部门名称，员工名称。

-- 连接查询dept和emp表
	select d.name,e.name from dept d,emp e 
	where e.dept_id=d.id;
	
-- 求出培优部的员工
	select d.name,e.name from dept d,emp e 
	where e.dept_id=d.id and d.name=培优部;

– 48.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名 /* emp e1(员工表), emp e2(上级表) 查询的列: e1.name, e1.topid, e2.name 查询的表: emp e1, emp e2 筛选条件: e1.topid=e2.id */ select e1.name, e1.topid, e2.name from emp e1, emp e2 where e1.topid=e2.id;

4.4.2. 列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资

-- 按照职位进行分组(职位相同的为一组),求出每组(每个职位)的最低薪资
	select job,min(sal) from emp group by job;

	-- 求出哪些职位的最低薪资是大于1500的
	select job,min(sal) from emp group by job having min(sal)>1500;

	总结: where和having的区别:
	1)where是在分组之前对结果进行筛选过滤,where中不能包含多行函数,并且where中不能使用列别名(但可以使用表别名)
	2)having是在分组之后对结果进行筛选过滤,having中可以包含多行函数,并且having中可以使用列别名以及表别名。

– 50.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。 – 按照部门对员工进行分组(每个部门的员工为一组) select * from emp group by dept_id; – 再统计每个部门的人数(count)以及平均薪资(avg) select dept_id,count(*),avg(sal) from emp group by dept_id;

4.4.3.列出受雇日期早于直接上级的所有员工，显示员工编号、员工姓名、上级姓名、部门名称。

/* emp e1(员工表),emp e2(上级表),dept d(部门表)
	显示的列: e1.id,e1.name,e2.name,d.name
	查询的表: emp e1,emp e2,dept d
	连接条件: e1.topid=e2.id  e1.dept_id=d.id
	筛选条件: e1.hdate<e2.hdate
 */
	select e1.id,e1.name,e2.name,d.name
	from emp e1,emp e2,dept d
	where e1.topid=e2.id and e1.dept_id=d.id
				and e1.hdate<e2.hdate;

	select e1.id 员工编号,e1.name 员工姓名,e2.name 上级姓名,d.name 所属部门
	from emp e1,emp e2,dept d
	where e1.topid=e2.id and e1.dept_id=d.id
				and e1.hdate<e2.hdate;

本文感谢讲师张慎政教导，不过没有传送门。