SQL语言

数据库相关

1. 如何创建数据库

-- 格式：create database 数据库名; 使用的是默认字符集
-- 指定字符集的格式：create database 数据库名 character set utf8/gbk;
create database db1;

create database db3 character set utf8;

create database db4 character set gbk;

2. 查询所有数据库

show databases;

3. 删除数据库

-- 格式：drop database 数据库名;
drop database db4;

4. 使用数据库

-- 作用：对表或者数据进行操作时必须先指定你要操作的是哪个数据库里面的
-- 格式：use 数据库名;
use db1;

5. 查询数据库详情(主要看下字符集)

-- 格式：show create database 数据库名;
show create database db1;

表相关SQL

执行表相关的SQL必须先使用了某个数据库才可以

create database db5;
use db5;

1. 创建表

-- 格式：create table 表名(字段1名 类型,字段2名 类型)
-- 指定字符集创建格式：create table 表名(字段1名 类型,字段2名 类型)charset=utf8/gbk;
create table person(name varchar(10),age int);

create table hero(name varchar(10),gender varchar(5),money int)charset=gbk;

2. 查询所有表

show tables;

3. 查询表详情

-- 格式：show create table 表名;
show create table hero;

4. 查询表字段

-- 格式：desc表名;
desc hero;

5. 删除表

-- 格式：drop table 表名;
drop table hero;

6. 修改表名

-- 格式：rename table 原名 to 新名;
rename table person to per;

7. 添加表字段

-- 最后添加格式：alter table 表名 add 字段名 类型;
alter table per add salary int;

-- 最前面添加格式：alter table 表名 add 字段名 类型 first;
alter table per add id int first;

-- 在某个字段的后面添加格式：alter table 表名 add 字段名 类型 after 字段名;
alter table per add gender varchar(5) after name;

8. 删除表字段

-- 格式：alter table 表名 drop 字段名;
alter table per drop gender;

9. 修改表字段

-- 格式：alter table 表名 change 原名 新名 新类型;
alter table per change salary sal int;

DML数据操作语言(增删改查)

操作数据必须先使用了某个数据库，并且准备好了数据对应的表格

-- 创建数据库mydb2，设置字符集为utf-8
create database mydb2 character set utf8;

-- 使用数据库mydb2
use mydb2;

-- 创建含有年龄为int类型，名字为可变字符串长度为10的person表，设置字符集为utf-8
create table person(name varchar(10),age int)charset=utf8;

1. 插入数据

-- 1.全表插入
-- 格式：insert into 表名 values(值1,值2); 值的数量和顺序必须和表字段数量和顺序一致
insert into person values('Tom',18);

-- 2.指定字段插入
-- 格式：insert into 表名(字段1名,字段2名)values(值1,值2); 值的数量和顺序必须和指定的字段一致
insert into person(name) values('Jerry');

-- 3.批量插入
-- 格式：insert into 表名 values(值1,值2),(值1,值2);
insert into person values('Lucy',20),('Lily',21);
insert into person(name)values('anan'),('lengleng');

-- 4.插入中文问题
insert into person values('胡歌',20); 
-- 如果出现报错：1366(HY000)：Incorrect string value -> 不正确的字符串值，执行
set names gbk;

2. 查询数据

-- 格式：select 字段信息 from 表名 where 条件;
-- select * from 表名;         *代表查询所有字段，没有条件代表查询的是所有数据
-- select 字段名 from 表名;     查询对应表中所有数据的某个字段
-- select 字段名1,字段名2 from 表名 where 查询条件;     查询符合条件的字段1和字段2

-- 1.查询person表中所有数据的name
select name from person;

-- 2.查询年龄大于20岁的姓名和年龄
select name,age from person where age>20;         

3. 修改数据

-- 格式：update 表名 set 字段名1=值,字段名2=值 where条件
-- 当设置条件为null时，不能用=，要用is

-- 1.将年龄为空的修改为10
update person set age=10 where age is null;  

-- 2.修改Lucy的年龄为80 
update person set age=80 where name='Lucy'; 

-- 3.修改年龄18的名字为汤姆  
update person set name='汤姆' where age=18;   

4. 删除数据

-- 格式：delete from 表名 where 条件; 当不写条件时，表示删除全部

-- 1.删除person表数据
delete from person;

-- 2.删除名字为AAA的人
delete from person where name='AAA';

-- 3.删除年龄小于20的人
delete from person where age<20;

数据类型

1. 整数类型

int(m)m代表显示长度，需要结合zerofill使用。bigint(m)大整型，等效java中的long

create table t1(age int(10)zerofill);

insert into t1 values(186);

select * from t1;

2. 浮点数

double(m,d)m代表总长度，d代表小数长度。例：28.234 -> m=5，d=3，超高精度浮点数decimal(m,d)只有涉及到超高精度运算时使用

3. 字符串

char(m)：固定长度字符串，优点：执行效率略高于可变长度，最大长度255
例：m=10，存"abc"，占10
varchar(m)：可变长度，优点：节省空间，最大长度65535。但建议保存字符长度为255以下的内容
例：m=10，存"abc"，占3
text(m)：可变长度。最大长度65535，建议保存长度大于255的字符串

4. 日期

date：只能保存年月日
time：只能保存时分秒
datetime：年月日时分秒，默认值为null，最大9999-12-31，格式：年-月-日 时:分:秒
timestamp：时间戳，年月日时分秒，默认值为当前系统时间，最大2038-1-19

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

insert into t_date values('2020-7-14',null,null,null);

insert into t_date values(null,'17:50:55','2020-7-14 17:51:55',null);

主键约束：primary key

约束：约束是创建表时给表字段添加的限制条件
主键：表示数据唯一性的字段称为主键
主键约束：唯一且非空

报错，主键值重复

create table t_pri(id int primary key,name varchar(10));

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

insert into t_pri values(1,'bbb'); --报错，主键值重复

报错，主键值不能为空

insert into t_pri values(null,'ccc'); --报错，主键值不能为空

主键约束+自增：primary key auto_increment

给字段添加自增后，赋值null则触发自增
自增规格：从历史最大值+1

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

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

导入*.sql文件到MySQL中

把**.sql文件放在某个盘中，在终端中执行：source 路径;

查看是否乱码：select * from emp; -> 如果有乱码 -> 执行 set names gbk;

is null 和 is not null

如果查询字段的值为 null 是 is null，不为 null，则使用 is not null

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

select * from emp where mgr is null;

2. 查询有上级领导的员工姓名/工资和领导编号

select ename,sal,mgr from emp where mgr is not null;

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

!=和<>都是不等于

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

select ename,sal from emp where sal<3000;

2. 查询不是程序员的员工姓名和工作(两种写法)

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

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

去重distinct

-- 格式：select distinct 去重字段名 from 表名;

-- 1. 查询员工表中所有员工所在部门编号有哪些
select distinct deptno from emp;

-- 2. 查询员工表中员工从事了哪些不同的工作
select distinct job from emp;

and 和 or

and类似java中的&&，查询多个条件同时满足时使用and
or类似java中的||，查询多个条件满足一个就行时使用or

1. 查询1号部门工资小于3000的员工信息

select * from emp where deptno=1 and sal<3000;

2. 查询3号部门或工作是程序员的员工信息

select * from emp where deptno=3 or job='程序员';

3. 有上级领导的员工中工资小于2000的员工姓名，工资和领导编号

select ename,sal,mgr from emp where mgr is not null and sal<2000;

in关键字

当查询的某个字段的值为多个值的时候使用in关键字

1. 查询工资为3000，5000和1500的员工姓名和工资

select ename,sal from emp where sal=3000 or sal=5000 or sal=1500;

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

2. 查询工资不是3000，5000的员工信息

select * from emp where sal!=3000 and sal!=5000;

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

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;

模糊查询like

%：代表0或多个未知字符
_：代表1个未知字符

x%        -- 以x开头
%x        -- 以x结尾
%x%		  -- 包含x
_x%  	  -- 第二个字符是x
%x__	  -- 这是两个下划线,倒数第三个字符是x
_x%y	  -- 第二个是x最后一个是y

1. 查询姓孙的员工信息

select * from emp where ename like '孙%';

2. 查询工作中包含销售的员工姓名和工作

select ename,job from emp where job like '%销售%';

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

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

4. 查询工作名称第二个字是序的员工姓名和工作名称

select ename,job from emp where job like '_序%'; 

排序

让查询结果安装某个字段升序或者降序排序

-- 格式：order by 字段名 asc(默认,升序)/desc(降序);

-- 1. 查询1号部门的员工姓名和工资，按照工资升序排序
select ename,sal from emp where deptno=1 order by sal;

-- 2. 查询所有员工信息，按照工资降序显示
select * from emp order by sal desc;

-- 3. 查询1号部门有领导的员工信息按照工资降序排序
select * from emp where deptno=1 and mgr is not null order by sal desc;

多字段排序

-- 格式：order by 字段名,字段名;

-- 1. 查询所有员工信息按照部门编号升序排序，如果部门编号一致则按照工资降序排序
select * from emp order by deptno,sal desc;

分页查询limit

查询数据只查询满足条件的一部分而不是全部的时候使用limit分页查询

-- 格式：limit 跳过的条数，请求的条数(每页条数)

-- 1. 查询所有员工中工资升序，只查询前3条数据(等同于请求第一页数据，每页3条)
select * from emp order by sal limit 0,3;

-- 2. 查询所有员工中工资升序，查询第三页的3条数据(第三页就是跳过去2页，跳过去2*3条数据)
select * from emp order by sal limit 6,3;

-- 3. 查询员工表第四页的2条数据
select * from emp limit 6,2;

-- 4. 查询员工表中工资最高的员工信息
select * from emp order by sal desc limit 0,1;

-- 5. 查询3号部门工资最低的员工信息
select * from emp where deptno=3 order by sal limit 0,1;

别名

给查询的字段起一个别的名字
格式：
select 字段名 as ‘别名’ from 表名
select 字段名 ‘别名’ from 表名
select 字段名 别名 from 表名

1. select ename as '名字' from emp;

2. select ename '名字' from emp;

3. select ename 名字 from emp;

字段的数值计算

-- 1. 查询每个员工的姓名，工资和年终奖(年终奖=5*工资)
select ename,sal,5*sal 年终奖 from emp;

-- 2. 查询每个员工的姓名，工资和涨薪5块钱之后的工资
select ename,sal 工资,sal+5 涨薪后工资 from emp;

聚合函数

聚合函数就是对查询的多条数据进行统计查询
统计方式：平均值avg(字段名)，最大值max(字段名)，最小值min(字段名)，求和sum(字段名)，计数count(字段名)/count(*)

-- 1. 查询2号部门的**平均**工资
select avg(sal) from emp where deptno=2;

-- 2. 查询程序员的**平均**工资
select avg(sal) from emp where job='程序员';

-- 3. 查询1号部门的**最高**工资
select max(sal) from emp where deptno=1;

-- 4. 查询有领导的员工中**最低**工资
select min(sal) from emp where mgr is not null;

-- 5. 查询3号部门工资**总和**
select sum(sal) from emp where deptno=3;

-- 6. 查询3号部门的**人数**
select count(*) from emp where deptno=3;

-- 7. 查询1号部门工资在2000到3000之间的员工**人数**
select count(*) from emp where deptno=1 and sal between 2000 and 3000;

分组查询

分组查询可以将某个字段相同数值的划分为一组，以每组为单位进行统计查询(聚合函数)

-- 格式：select 字段名 from 表名 group by 分组字段名; 
-- 在需求中出现每个xxx或每种xxx时，以xxx作为分组的字段

-- 1. 查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

-- 2. 查询每个部门的最高工资
select deptno,max(sal) from emp group by deptno;

-- 3. 查询每个部门的人数
select deptno,count(*) from emp group by deptno;

-- 4. 查询每种工作的人数
select job,count(*) from emp group by job;

-- 5. 查询每个部门工资低于2000的人数
select deptno,count(*) from emp where sal<2000 group by deptno;

-- 6. 查询1号部门每种工作的人数
select job,count(*) from emp where deptno=1 group by job;

-- 7. 查询每种工作的最低工资
select job,min(sal) from emp group by job;

having 关键字

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

-- 1. 查询每个部门的平均工资，只查平均工资大于2000的信息
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

-- 2. 查询每个部门人数，只查询人数为3的
select deptno,count(*)人数 from emp group by deptno having 人数=3;

-- 3. 查询每个部门的平均工资，只查询工资在1000到3000之间的，并且过滤掉平均工资低于2000的部门
select deptno,avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)>=2000;

各个关键字的位置

select 字段信息 from 表名 where 普通字段条件 group by 分组字段 having 聚合函数条件 order by 排序字段 limit 跳过的条数,请求的条数;

子查询(嵌套查询)

把一条SQL语句嵌入到另一条SQL语句中，把查询语句的值作为另一条SQL查询条件的值

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

select avg(sal) from emp where deptno=1;   -- 1号部门的平均工资

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

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

select max(sal) from emp;   -- 最高工资

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

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

select min(sal) from emp where deptno=2;   -- 2号部门的最低工资

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

4. 查询和孙悟空相同工作的其他员工信息

select job from emp where ename='孙悟空';	-- 孙悟空的工作

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

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

select min(sal) from emp;	-- 最低工资

select deptno from emp order by sal limit 0,1;	-- 最低工资员工的部门

select * from emp where deptno=(select deptno from emp order by sal limit 0,1) and sal!=(select min(sal) from emp);

6. 查询白骨精的部门信息

select deptno from emp where eanme='白骨精';	-- 白骨精的部门编号

select * from dept where deptno=(select deptno from emp where ename='白骨精');

7. 查询所有员工的部门信息(部门只有1，2，3 但是部门表里有1，2，3，4 只根据员工表中出现的部门编号去查询部门信息)

select distinct deptno from emp;	-- 所有员工的部门

select * from dept where deptno in(select distinct deptno from emp);

关联查询

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

关联查询的查询方式有：等值连接、内连接、外连接

1. 等值连接

-- 格式：select * from A,B where 关联关系 and 其他条件；

-- 1. 查询每个员工的姓名和对应的部门名
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>2000;

-- 2. 查询有上级领导的员工姓名，工资，上级领导编号，部门名和部门地址
select e.ename,e.sal,e.mgr,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and e.mgr is not null;

2. 内连接

内连接和等值连接查询到的结果是一样的，都是查询到两张表的交集数据

-- 格式：select * from A join B on 关联关系 where 其他条件

-- 1. 查询每个员工的姓名和对应的部门名
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;

-- 2. 查询2号部门和1号部门员工的姓名，工资和部门名，部门地址
select e.ename,e.sal,d.dname,d.loc from emp e join dept d on e.deptno=d.deptno where e.deptno in(1,2);

3. 外连接

查询一张表的全部数据和另一张表的交集数据，外连接分为左外连接和右外连接

-- 格式：select * from A left/right join B on 关联关系 where 条件; 
-- left指向A，right指向B，指向谁，就是查谁的全部数据

-- 1. 查询所有员工的姓名和对应的部门名
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;

关联查询总结：如果查询的是两张表的交集数据，则使用等值连接或内连接(推荐)，如果查询的是一张表的全部数据和另外一张表的交集数据，则使用外连接