MySQL基础

@MySQl的自我总结

数据库概述

所谓的数据库就是指存储和管理数据的仓库
早期：层次式数据库、网络型数据库
现在：关系型数据库、非关系型数据库

连接MySQL服务器

1、连接mysql服务器：

mysql -uroot -p密码

-u:后面的root是用户名
-p 后面是密码

数据库及表操作

创建、删除、查看数据库

1. 创建数据库

CREATE DATABASE mydb1;

2. 查看MySQL服务器中的所有数据库

show databases;

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

use test;  --语法： USE 库名

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

show tables;

5. .删除mydb1库

drop database mydb1;  -- 语法：DROP DATABASE 库名;

drop database if exists mydb1; --当删除的库不存在时，如何避免错误产生?`

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

create database if not exists mydb1 charset utf8;

7…查看建库时的语句（并验证数据库库使用的编码）

show create database mydb1; -- 查看建库时的语句
show create table stu; -- 查看建表时的语句

创建、删除、查看表

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

use mydb1;
drop table if exists stu;

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

drop table if exists stu;
create table stu(
	id int primary key auto_increment, -- 将id设置为主键(唯一且不能为空),并且设置为自增
  name varchar(50),
  gender varchar(10),
  birthday date,
  score double
);

• 查看stu学生表结构

desc stu;

修改表结构的语法

• 给stu表添加一个课程(course)的字段
• 增加列定义用add关键字

alter table stu
add course varchar(255);

alter table stu
add course varchar(255) default 'JAVA';  --添加默认字段字符串的值是单引号

• 修改stu表中的课程(course)字段，类型从varchar改成char
• 修改列定义用modify关键字

alter table stu
modify course char(255);

• 修改表名，把stu表明改成stu1

alter table stu
rename to stu1;

• 把stu表中课程(course)字段改成class班级字段

alter table stu
change course class char(255);

新增、更新、删除表记录

• 往学生表中(stu)插入记录

-- 如果是在cmd中执行插入记录的语句,先 set names gbk; 再插入记录!
insert into stu(id,name,gender,birthday,score) value (null,'tom','male','1985-1-1',78);
insert into stu value(null,'吕布','female','1985-2-2',88);
insert into stu value(null,'典韦','male','1999-3-3',68);

• 查询stu表所有学生的信息

select * from stu;

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

update stu set score=score+10;

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

update stu set score=83 where id=1;

• 删除stu表中所有的记录

delete from stu;

• 仅删除符合条件的

delete from stu where id>1;

基础查询

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

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

select name,sal,bonus from emp;

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

select dept,job from emp;

• 在select之后、列名之前，使用DISTINCT 剔除重复的记录

select distinct dept,job from emp;

where子句查询

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

select name,sal+bonus from emp where sal+bonus > 3500;

• ifnull(列, 值)函数: 判断指定的列是否包含null值，如果有null值，用第二个值替换null值

select name,ifnull(sal,0)+ifnull(bonus,0) from emp
where ifnull(sal,0)+ifnull(bonus,0) > 3500;

• 使用as可以为表头指定别名

select name as 姓名,ifnull(sal,0)+ifnull(bonus,0) as 总薪资 from emp
where ifnull(sal,0)+ifnull(bonus,0) > 3500;

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

select name,sal from emp where sal between 3000 and 4500;

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

select name,sal from emp
where sal in(1400,1600,1800);

• 查询薪资不为1400、1600、1800的员工

select name,sal from emp
where sal not in(1400,1600,1800);

• 如何查询有部门的员工（即部门列不为null值）

select * from emp where dept is not null;

模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。可以和通配符（%、_）配合使用，其中"%“表示0或多个任意的字符。”_"表示一个任意的字符。

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

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

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

select name from emp
where name like '刘%'; -- 查询以'刘'开头的

select name from emp
where name like '%涛'; -- 查询以'涛'结尾的

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

select name from emp
where name like '刘_'; -- 以"刘"开头，并且姓名为两个字

select name from emp
where name like '刘__'; -- 以"刘"开头，并且姓名为三个字

多行函数查询

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

select count(*) from emp where sal>3000;

• 求emp表中的最高薪资

select sal from emp; -- 查询emp表中的所有薪资
select max(sal) from emp; -- 统计emp表中的最高薪资
select min(sal) from emp; -- 统计emp表中的最低薪资

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

select sum(sal) from emp; -- 统计sal这一列中的所有值的和
select sum(bonus) from emp; -- 统计bonus这一列中的所有值的和

select sum(sal)+sum(bonus) from emp; -- 统计 薪资总和+奖金总和
select sum(sal+bonus) from emp; -- 因为bonus列中有null值,所以结果有误差
select sum(sal+ifnull(bonus,0)) from emp; -- 处理null值,结果正确

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

select avg(sal) from emp;

分组查询

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

select * from emp group by dept; -- 按照部门分组, 部门相同的是一组

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

select * from emp group by job; -- 按照职位分组, 职位相同的是一组
select job,count(*) from emp group by job; -- 按照职位分组, 统计每个职位的人数

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

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

排序查询

使用 ORDER BY 子句将结果集中记录根据指定的列排序后再返回
语法：SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC]
ASC(默认)升序，即从低到高；DESC 降序，即从高到低。

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

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

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

select name,bonus from emp order by bonus desc; -- 按照奖金降序排序

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

分页查询

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

• 查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 1 页。

select * from emp limit 0,3; -- 每页显示3条, 查询第1页
select * from emp limit 3,3; -- 每页显示3条, 查询第2页
select * from emp limit 6,3; -- 每页显示3条, 查询第3页

• 查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 2 页。

select * from emp limit 3,3; -- 每页显示3条, 查询第2页

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

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

其他函数

函数名	解释说明
curdate(）	获取当前日期，格式是：年月日
curtime()	获取当前时间 ，格式是：时分秒
sysdate()/now()	获取当前日期+时间，格式是：年月日 时分秒
year(date)	返回date中的年份
month(date)	返回date中的月份
day(date)	返回date中的天数
hour(date)	返回date中的小时
minute(date)	返回date中的分钟
second(date)	返回date中的秒
CONCAT(s1,s2…)	将s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x,s1,s2…)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x，x是分隔符

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

select name,birthday from emp
where birthday>='1993-1-1' and birthday<='1995-12-31';
-- 或 将birthday中的年份取出来, 和1993及1995进行比较!
select name,birthday from emp
where year(birthday)>=1993 and year(birthday)<=1995;

• 查询emp表中下个月过生日的所有员工

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

• 查询emp表中员工的姓名和薪资（薪资格式为: xxx/元 ）

select name, concat_ws('/', sal, '元') from emp;

MySQL中varchar与char区别

区别一，定长和变长

char 表示定长，长度固定，varchar表示变长，即长度可变。当所插入的字符串超出它们的长度时，视情况来处理，如果是严格模式，则会拒绝插入并提示错误信息，如果是宽松模式，则会截取然后插入。如果插入的字符串长度小于定义长度时，则会以不同的方式来处理，如char（10），表示存储的是10个字符，无论你插入的是多少，都是10个，如果少于10个，则用空格填满。而varchar（10），小于10个的话，则插入多少个字符就存多少个。

varchar怎么知道所存储字符串的长度呢？实际上，对于varchar字段来说，需要使用一个（如果字符串长度小于255）或两个字节（长度大于255）来存储字符串的长度。但是因为他需要有一个prefix来表示他具体bytes数是多少（因为varchar是变长的，没有这个长度值他不知道如何读取数据）。

区别之二，存储的容量不同

对 char 来说，最多能存放的字符个数 255，和编码无关。

而 varchar 呢，最多能存放 65532 个字符。VARCHAR 的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节

在MySQL中用来判断是否需要进行对据列类型转换的规则

1、在一个数据表里，如果每一个数据列的长度都是固定的，那么每一个数据行的长度也将是固定的．

2、只要数据表里有一个数据列的长度的可变的，那么各数据行的长度都是可变的．

3、如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型．例外：长度小于4个字符的char数据列不会被转换varchar类型

扩展内容3：(面试题)char(n)、varchar(n)、text都可以表示字符串类型，其区别在于：

(1)char(n)在保存数据时，如果存入的字符串长度小于指定的长度n，后面会用空格补全，因此可能会造成空间浪费，但是char类型的存储速度较varchar和text快。

因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比后两者还快！

(2)varchar(n)保存数据时，按数据的真实长度存储，剩余的空间可以留给别的数据用，因此varchar不会浪费空间。

因此varchar适合存储长度不固定的数据，这样不会有空间的浪费。

(3)text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储。tex也分很多种，其中bigtext存储数据的长度约为 4GB

日期类型

date：年月日
time：时分秒
datetime：年月日时分秒
timestamp：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同，两者的区别是：

• timestamp最大表示2038年，而datetime范围是1000~9999
• timestamp在插入数据、修改数据时，可以自动更新成系统当前时间

MySQL的字段约束

主键约束

主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。
主键的作用: 作为一个唯一标识，唯一的表示一条表记录（作用类似于人的身份证号，可以唯一的表示一个人一样。）

• 添加主键约束，例如将id设置为主键：

create table stu(
	id int primary key,
	...
);

• 主键自增策略是指：设置了自增策略的主键，可以在插入记录时，不给id赋值，只需要设置一个null值，数据库会自动为id分配一个值（AUTO_INCREMENT变量，默认从1开始，后面依次+1），这样既可以保证id是唯一的，也省去了设置id的麻烦。

create table stu(
	id int primary key auto_increment,  --将id主键设置为自增
	...
);

非空约束

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

create table user(
	password varchar(50) not null,
	...
);

唯一约束

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

create table user(
	username varchar(50) unique not null,
	...
);

外键约束

外键其实就是用于通知数据库两张表数据之间对应关系的这样一个列。
这样数据库就会帮我们维护两张表中数据之间的关系。

create table emp(
	id int,
	name varchar(50),
	dept_id int,
	foreign key(dept_id) references dept(id)
);

将dept_id列设置为外键，等同于通知数据库，部门表和员工表之间存在对应关系，dept_id列中的数据要参考部门的主键，数据库一旦知道部门和员工表之间存在关系，就会帮我们维护这层关系。

表关系

常见的表关系分为以下三种：

一对多(多对一)、一对一、多对多

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200514204819915.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwOTA5NDEx,size_16,color_FFFFFF,t_70

多表查询

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

连接查询

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

select * from dept,emp;

上面的查询中存在大量错误的数据，一般我们不会直接使用这种查询。

笛卡尔积查询：所谓笛卡尔积查询就是指，查询两张表，其中一张表有m条记录，另一张表有n条记录，查询的结果是m*n条。

-- 条件: 员工所属的部门编号等于部门的编号
select * from dept,emp 
where emp.dept_id=dept.id;

通过where子句将笛卡尔积查询中的错误数据剔除,保留正确的数据,这就是连接查询!
上面的查询可以换成下面的查询：

select * from dept inner join emp 
on emp.dept_id=dept.id; -- 内连接查询

左外连接查询

• 查询所有部门和部门下的员工，如果部门下没有员工，员工显示为null

select * from dept left join emp 
on emp.dept_id=dept.id; -- 左外连接查询

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

• .查询部门和所有员工，如果员工没有所属部门，部门显示为null

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

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

扩展：如果想将两张表中的所有数据都查询出来(左外+右外并去除重复记录)，可以使用全外连接查询，但是mysql又不支持全外连接查询。

select * from dept left join emp on emp.dept_id=dept.id
union
select * from dept right join emp on emp.dept_id=dept.id;

可以使用union将左外连接查询的结果和右外连接查询的结果合并在一起，并去除重复的记录。
需要注意的是：union可以将两条SQL语句执行的结果合并有前提:

(1)两条SQL语句查询的结果列数必须一致

(2)两条SQL语句查询的结果列名、顺序也必须一致

并且union默认就会将两个查询中重复的记录去除(如果不希望去除重复记录，可以使用union all)

子查询练习

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

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

-- 求出'王海涛'的薪资
select sal from emp where name='王海涛'; -- 2450
-- 列出比'王海涛'薪资还高的员工
select name,sal from emp 
where sal>(select sal from emp where name='王海涛');

• 列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位。

-- 求出'刘沛霞'的职位
select job from emp where name='刘沛霞'; -- 推销员
-- 求出和'刘沛霞'从事相同职位的员工
select name,job from emp
where job=(select job from emp where name='刘沛霞');

• 列出薪资比’大数据部’部门(已知部门编号为30)所有员工薪资都高的员工信息，显示员工姓名、薪资和部门名称。
  如果不考虑没有部门的员工

-- 1、连接查询员工和部门
select e.name,e.sal,d.name from dept d,emp e
where e.dept_id=d.id;
-- 2、求出大数据部门的最高薪资(查询员工表中部门编号为30的最高薪资)
select max(sal) from emp where dept_id=30;
-- 3、求出比大数据部门最高薪资还高的员工信息
select e.name,e.sal,d.name from dept d,emp e
where e.dept_id=d.id 
	and sal>(select max(sal) from emp where dept_id=30);

如果加上没有部门的员工

-- 1、用外连接查询所有员工和对应的部门
select e.name,e.sal,d.name from dept d right join emp e
on e.dept_id=d.id;
-- 2、求出大数据部门的最高薪资
select max(sal) from emp where dept_id=30;
-- 3、求出比大数据部门最高薪资还高的员工信息
select e.name,e.sal,d.name from dept d right join emp e
on e.dept_id=d.id and
sal>3000;

多表查询练习

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

-- 关联查询两张表
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='培优部';

• (自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名

/* emp e1 员工表, emp e2 上级表
 * 查询的表: emp e1, emp e2
 * 显示的列: e1.name, e2.id, e2.name
 * 连接条件: e1.topid=e2.id
 */
select e1.name, e2.id, e2.name
from emp e1, emp e2
where e1.topid=e2.id;

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

-- 根据职位进行分组，求出每种职位的最低薪资
select job,min(sal) from emp group by job;
-- 求出最低薪资大于1500的职位
select job,min(sal) from emp where min(sal)>1500 group by job; -- 错误写法

select job,min(sal) from emp group by job having min(sal) > 1500; -- 正确

补充内容：where和having子句的区别:

(1)相同点: where和having都可以对记录进行筛选过滤。
(2)区别：where是在分组之前,对记录进行筛选过滤,并且where子句中不能使用多行函数以及列别名(但是可以使用表别名)
(3)区别：having是在分组之后,对记录进行筛选过滤,并且having子句中可以使用多行函数以及列别名、表别名。

• 列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。

-- 根据部门对员工进行分组, 统计每个组(部门)的人数和平均薪资
select dept_id, count(*), avg(sal) from emp group by dept_id;

• 查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。

-- 连接查询部门表和员工表
select d.id,d.name,d.loc from emp e,dept d
where e.dept_id=d.id;
-- 按照部门进行分组,统计部门人数
select d.id,d.name,d.loc,count(*) from emp e,dept d
where e.dept_id=d.id 
group by d.id 
having count(*) >= 1;

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

/* emp e1 员工表, emp e2 上级表, dept d 部门表
 * 显示的列: e1.id, e1.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, 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 name, max(sal) from emp; -- 错误写法
select name,sal from emp order by sal desc limit 0,1; -- 正确写法
-- 求出emp表中的最高薪资
select max(sal) from emp;
-- 根据最高薪资到emp表中查询, 该薪资对应的员工信息
select * from emp where sal=(select max(sal) from emp);

SQL语句的书写顺序:

select * | 列名 -- 确定要查询的列有哪些
from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤，剔除不符合条件的记录
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount -- 指定返回第几页记录以及每页显示多少条

SQL语句的执行顺序:

from 表名 -- 确定查询哪张表
where 条件 -- 通过筛选过滤，剔除不符合条件的记录
select * | 列名 列别名 -- 确定要查询的列有哪些，
group by 分组的列 -- 指定根据哪一列进行分组
having 条件 -- 通过条件对分组后的数据进行筛选过滤
order by 排序的列 -- 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from 表 where id in (select 字段名 from 表名称 group by 字段名 having count(字段名) > 1)

2、查找表中多余的重复记录（多个字段）

select * from 表 as a where (a.字段名,a.字段名) in (select 字段名,字段名 from 表名称 group by 字段名,字段名 having count(*) > 1)