SQL基础语句

数据库格式：

• 以分号结尾
• 不区分大小写
• 可以包含空格和换行

 

数据库相关SQL

1. 查询所有 show databases;
2. 创建 create database db1 charset=utf8/gbk;
3. 查询数据库信息 show create database db1;
4. 删除数据库 drop database db1;
5. 使用数据库 use db1;

表相关SQL

1. 创建 create table t1(name varchar(50),age int)charset=utf8/gbk;
2. 查询所有表 show tables;
3. 查询表信息 show create table t1;
4. 表字段 desc t1;
5. 删除表 drop table t1;
6. 修改表名 rename table t1 to t2;
7. 添加字段 alter table t1 add 字段名 类型 first/after xxx;
8. 删除字段 alter table t1 drop 字段名;
9. 修改字段 alter table t1 change 原名 新名 新类型;

数据相关SQL

1. 插入数据 insert into t1(字段1名,字段2名) values(值1,值2),(值1,值2);
2. 查询数据 select 字段信息 from t1 where 条件;
3. 修改数据 update t1 set xxx=xxx,xxx=xxx where 条件;
4. 删除数据 delete from t1 where 条件;

主键约束 primary key

• 什么是主键: 表示数据唯一性的字段

• 什么是约束: 创建表时给表字段添加的限制条件

• 主键约束: 限制主键字段的值 唯一且非空

• 格式:

  create table t1(id int primary key,name varchar(50))charset=utf8;

  insert into t1 values(1,'aaa');

  insert into t1 values(1,'bbb'); //报错: 不能插入重复的数据

 

主键约束+自增

• 自增规则: 从历史最大值+1

• 格式:

  create table t2(id int primary key auto_increment,name varchar(50));

  insert into t2 values(null,'aaa'); id=1

  insert into t2 values(null,'bbb'); id=2

  insert into t2 values(10,'ccc'); id=10

  insert into t2 values(null,'ddd'); id=11

  delete from t2 where id>=10;

  insert into t2 values(null,'eee'); id=12

 

数据类型

1. 整数: int(m) 和 bigint(m) bigint等效java中的long m代表显示长度 m=5 存 18 查出来00018 用来补零 需要结合zerofill关键字使用

   create table t3(age int(5) zerofill);

   insert into t3 values(18);

   select * from t3;

2. 浮点数: double(m,d)和float(m,d) m代表总长度 d代表小数长度 56.123 m=5 d=3 ,超高精度浮点数 decimal(m,d) 只有涉及到超高精度运算时使用

   create table t4(salary double(5,3));

   insert into t4 values(56.789);

   insert into t4 values(34.56789);

   insert into t4 values(345.678); //报错 超出范围

3. 字符串:

   • char(m):固定长度 m=10 存abc 占10个字符长度 好处:执行效率略高, 最大长度255
   • varchar(m): 可变长度 m=10 存abc 占3个字符长度, 好处:节省空间, 最大长度65535 ,建议保存长度较小的数据时使用(低于255时使用)
   • text(m): 可变长度 最大长度65535 ,建议保存长度较大的数据时使用

4. 日期:

   • date: 只能保存年月日

   • time: 只能保存时分秒

   • datetime:年月日时分秒, 最大值9999-12-31, 默认值为null

   • timestamp(时间戳,以距离1970年1月1日的毫秒数保存时间):年月日时分秒,最大值2038-1-19, 默认值为当前系统时间

   • 举例:

     create table t5(t1 date,t2 time,t3 datetime,t4 timestamp);

     insert into t5 values('2020-11-20',null,null,null);

     insert into t5 values(null,'10:58:20','2019-10-20 10:20:30',null);

导入*.sql文件

• 通过以下指令: 格式: source 路径;

  source e:/emp.sql;

• 导入完成后 测试查询

  show tables; 查询出两个表 emp和dept

  select * from emp; 里面会有一堆数据

去重distinct

1. 查询员工表中出现了哪几种不同的工作

   select distinct job from emp;

2. 查询员工表里面有哪几个部门id

   select distinct deptId from emp;

is null 和 is not null

• 当查询字段的值为空值时 不能用等号进行判断,使用is

1. 查询没有上级领导的员工信息;

   select * from emp where manager is null;

2. 查询有上级领导的员工信息;

   select * from emp where manager is not null;

比较运算符 > < >= <= = !=和<>

1. 查询工资大于等于3000的员工姓名和工资

   select name,sal from emp where sal>=3000;

2. 查询1号部门的员工姓名和工作

   select name,job from emp where deptId=1;

3. 查询不是程序员的员工姓名,工资和工作 (用到上面两种不等的写法)

   select name,sal,job from emp where job!='程序员';

   select name,sal,job from emp where job<>'程序员';

4. 查询有奖金的员工姓名和奖金

   select name,comm from emp where comm>0;

and / or / not 与或非

• and 类似Java中的 &&
• or 类似Java中的||
• not 类似Java中的!

1. 查询1号部门工资高于2000块钱的员工信息

   select * from emp where deptId=1 and sal>2000;

2. 查询是程序员或者工资等于5000的员工信息

   select * from emp where job='程序员' or sal=5000;

3. 查询出CEO和项目经理的名字

   select name from emp where job='CEO' or job='项目经理';

4. 查询出奖金为500并且是销售的员工信息 select * from emp where comm=500 and job='销售';

in关键字

• 当查询某个字段的值为多个值的时候使用

1. 查询出工资为3000,1500和5000的员工信息

   select * from emp where sal=3000 or sal=1500 or sal=5000;

   select * from emp where sal in(3000,1500,5000);

2. 查询工资不是3000,1500和5000的员工信息

   select * from emp where sal not in(3000,1500,5000);

3. 查询1号和2号部门工资大于2000的员工信息

   select * from emp where deptId in(1,2) and sal>2000;

between x and y

• 查询数据在两者之间使用 , 包含x和y

1. 查询工资在2000到3000之间的员工信息

select * from emp where sal>=2000 and sal<=3000;

select * from emp where sal between 2000 and 3000;

1. 查询工资在2000到3000之外的员工信息

select * from emp where sal not between 2000 and 3000;

 

模糊查询like

• _: 代表1个未知字符
• %: 代表0或多个未知字符
• 举例:
  • 以x开头 x%
  • 以x结尾 %x
  • 包含x %x%
  • 第二个字符是x _x%
  • 第三个是x倒数第二个是y _ _ x%y _

1. 查询姓孙的员工信息

   select * from emp where name like "孙%";

1. 查询工作中第二个字是售的员工信息

   select * from emp where job like "_售%";

2. 查询名字中以精结尾的员工姓名

   select name from emp where name like '%精';

3. 查询名字中包含僧的员工并且工资高于2000的员工信息

   select * from emp where name like '%僧%' and sal>2000;

4. 查询1号和2号部门中工作以市开头的员工信息

   select * from emp where deptId in(1,2) and job like '市%';

5. 查询有领导的员工中是经理的员工姓名

   select name from emp where manager is not null and job like '%经理%';

排序 order by

• 格式: order by 排序字段名 asc升序(默认)或desc降序

1. 查询所有员工的姓名和工资并安装工资升序排序

   select name,sal from emp order by sal;

2. 查询所有员工的姓名和工资并安装工资降序排序

   select name,sal from emp order by sal desc;

3. 查询所有员工姓名,工资和部门编号 , 安装部门编号升序排序,如果部门编号一致则按照工资降序排序

   select name,sal,deptId from emp order by deptId,sal desc;

分页查询limit

• 格式: limit 跳过的条数,请求的条数(每页的条数)
• 跳过的条数=(请求的页数-1)*每页的条数
• 举例:
  • 查询第一页的10条数据 limit 0,10
  • 查询第二页的10条数据 limit 10,10
  • 查询第5页的10条数据 limit 40,10
  • 查询第8页的5条数据 limit 35,5
  • 查询第7页的9条数据 limit 54,9

1. 工资升序排序 查询前三名

   select * from emp order by sal limit 0,3;

2. 查询员工表中工资降序排序 第二页的3条数据

   select * from emp order by sal desc limit 3,3;

1. 查询1号部门中工资最高的员工信息

   select * from emp where deptId=1 order by sal desc limit 0,1;

2. 查询销售相关工作里面赚钱最少的员工姓名和工资

   select name,sal from emp where job like '%销售%'

   order by sal limit 0,1;

3. 按照工资降序排序查询工资高于1000的所有员工姓名和工资, 查询第三页的两条数据

   select name,sal from emp where sal>1000 order by sal desc limit 4,2;

数值计算 + - * / %

1. 查询每个员工的姓名,工资和年终奖(年终奖=5*月工资)

   select name,sal,5*sal from emp ;

2. 查询2号部门中的员工姓名,工资和涨薪5块钱之后的工资

   select name,sal,sal+5 from emp where deptId=2;

3. 让员工表中3号部门的员工每人涨薪5块钱

   update emp set sal=sal+5 where deptId=3;

别名

select name as '姓名' from emp;

select name '姓名' from emp;

select name 姓名 from emp;

 

聚合函数

• 可以对查询的多条数据进行统计查询
• 包括的统计方式有:
  • 平均值 avg(字段名)
  • 最大值 max(字段名)
  • 最小值 min(字段名)
  • 求和 sum(字段名)
  • 计数 count(字段名或*)

1. 平均值avg(字段名)

   • 查询1号部门的平均工资

     select avg(sal) from emp where deptId=1;

2. 最大值max(字段名)

   • 查询程序员的最高工资

     select max(sal) from emp where job='程序员';

3. 最小值min(字段名)

   • 查询2号部门的最低工资

     select min(sal) from emp where deptId=2;

4. 求和sum(字段名)

   • 查询3号部门的工资总和

     select sum(sal) from emp where deptId=3;

5. 计数count(字段名或*)

   • 查询1号部门的人数

   select count(*) from emp where deptId=1;

分组查询 group by

• 分组查询可以将某个字段相同值得数据划分为一组,以组为单位进行统计查询

1. 查询每一种工作的平均工资

   select job,avg(sal) from emp group by job;

2. 查询每个部门的平均工资

   select deptId,avg(sal) from emp group by deptId;

3. 查询每种工作的人数

   select job,count(*) from emp group by job;

4. 查询每个部门工资大于2000的人数

   select deptId,count(*) from emp where sal>2000 group by deptId;

5. 查询平均工资最高的部门编号

   select deptId from emp group by deptId order by avg(sal) desc limit 0,1;

6. 查询人数最多的工作名称

   select job from emp group by job order by count(*) desc limit 0,1;

having

• where后面只能写普通字段的条件,不能写聚合函数条件
• having关键字 和 group by分组查询 结合使用 ,写在group by的后面
• 聚合函数条件写在having 后面

1. 查询每个部门的平均工资,只查询平均工资高于2000的数据

select deptId,avg(sal) from emp group by deptId having avg(sal)>2000;

select deptId,avg(sal) a from emp group by deptId having a>2000;

1. 查询每种工作的人数,只查询人数大于1的工作名称和人数.

   select job,count(*) c from emp group by job having c>1;

1. 查询每个部门的工资总和，只查询有领导的员工，并且要求工资总和高于5400

   select deptId,sum(sal) s from emp where manager is not null group by deptId having s>5400;

2. 查询每个部门的平均工资,只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的部门信息

   select deptId,avg(sal) a from emp where sal between 1000 and 3000 group by deptId having a>=2000;

3. 查询每种工作的人数要求人数大于1个,并且只查询1号部门和2号部门的员工, 按照人数降序排序

   select job,count(*) c from emp where deptId in(1,2) group by job having c>1 order by c desc;

4. 查询高于2000工资人数最多的工作

   select job from emp where sal>2000 group by job order by count(*) desc limit 0,1;

子查询(嵌套查询)

• 将一条SQL语句嵌入到另外一条SQL语句中, 当做查询条件的值

1. 查询工资高于1号部门平均工资的员工信息

• 查询1号部门的平均工资

  select avg(sal) from emp where deptId=1;

• 查询工资高于上面结果的员工信息

  select * from emp where sal>(select avg(sal) from emp where deptId=1);

1. 查询工资最高的员工信息

   select * from emp where sal=(select max(sal) from emp);

2. 查询工资高于2号部门最低工资的员工信息

   select * from emp where sal>(select min(sal) from emp where deptId=2);

3. 查询和孙悟空相同工作的员工信息

   select * from emp where job=(select job from emp where name='孙悟空') and name!='孙悟空';

4. 查询最低工资员工的同事们的信息(同事指同一部门)

   select min(sal) from emp;

   select deptId from emp where sal=(select min(sal) from emp);

   select * from emp where deptId=(select deptId from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

关联关系

• 创建表时, 表和表之间存在的业务关系
• 有哪几种关系?
  • 一对一: 有AB两张表,A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条.
  • 一对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条.
  • 多对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条.
• 表和表之间如何建立关系?
  • 通过一个单独的字段指向另外一张表的主键
  • 一对一的关系: 有AB两张表,在任意一张表中添加字段指向另外一个表的主键
  • 一对多的关系: 有AB两张表,在一对多的关系中,多的一端添加一个单独字段指向另外一张表的主键
  • 多对多的关系: 有AB两张表 还需要创建一个单独的关系表,里面两个字段分别指向另外两张表的主键

关联查询

• 同时查询多张表数据的查询方式称为关联查询

• 有三种关联查询的方式:

  • 等值连接
  • 内连接
  • 外连接

等值连接

• 格式: select 字段信息 from A,B where 关联关系 and 条件;

1. 查询工资高于2000的员工的姓名,工资以及对应的部门名

   select e.name,sal,d.name

   from emp e,dept d

   where e.deptId=d.id and sal>2000;

1. 查询 有领导并且和销售相关工作的员工姓名,工作,部门名和部门地点

   select e.name,job,d.name,loc

   from emp e,dept d

   where e.deptId=d.id

   and manager is not null and job like '%销售%';

内连接

• 等值连接和内连接查询到的数据是一样的 都是两个表的交集数据,只是书写格式不一样
• 格式: select 字段信息 from A join B on 关联关系 where 条件

1. 查询工资高于2000的员工的姓名,工资以及对应的部门名

   select e.name,sal,d.name

   from emp e join dept d on e.deptId=d.id

   where sal>2000;

2. 查询 有领导并且和销售相关工作的员工姓名,工作,部门名和部门地点

select e.name,job,d.name,loc

from emp e join dept d on e.deptId=d.id

where manager is not null and job like '%销售%';

外连接

• 查询一张表的全部和另外一张表的交集数据,使用外连接
• 格式: select 字段信息 from A left/right join B on 关联关系 where 条件

1. 查询所有员工姓名和对应的部门名

insert into emp(name,sal) values('灭霸',88);

select e.name,d.name

from emp e left join dept d

on e.deptId=d.id;

1. 查询所有部门名对应的员工姓名和工资

select d.name,e.name,sal

from emp e right join dept d

on e.deptId=d.id;

关联查询总结

• 如果需要查询的数据时两个表的交集数据,使用等值连接或内连接(推荐)
• 如果查询的是一张表的全部和另外一张表的交集使用外连接