基础sql语句大全（详细解析，注意事项）
1. 数据库及表操作
注意：如果需要本文的数据库文件，请下载，否则自己建表练习
sql文件
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和4500select name,sal from emp where sal>3000 and 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 empwhere year(birthday) between 1993 and 1995;-- 方式二: 将1993、1995转成日期('1993-1-1'、'1995-12-31')再和birthday比较select name,birthday from empwhere birthday between '1993-1-1' and '1995-12-31';3.7.5.查询emp表中本月过生日的所有员工
-- 获取当前月份 和 员工的出生月份 进行比较select * from empwhere month( now() )=month( birthday );-- 获取下个月过生日的员工select * from empwhere (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.namefrom emp e1,emp e2,dept dwhere e1.topid=e2.id and e1.dept_id=d.idand e1.hdate<e2.hdate;select e1.id 员工编号,e1.name 员工姓名,e2.name 上级姓名,d.name 所属部门from emp e1,emp e2,dept dwhere e1.topid=e2.id and e1.dept_id=d.idand e1.hdate<e2.hdate;本文感谢讲师张慎政教导，不过没有传送门。