MySQL

2023-08-12 17:19:04

1. MySQL常见数据类型

数值型

MySQL

浮点型

MySQL

日期型

MySQL

字符型

char和varchar的区别:

  • 这两个都是字符串类型, char类型是定长字符串, 范围是不能超过255个字符
    varchar类型是变长字符串, 范围是不能超过65535个字节
    需要注意的是, char(n) varchar(n) 中的n都表示字符数
  • char类型中如果存入的数据小于最大字符数n, 剩余的空间会使用空格填充, 所以char类型可能会造成空间浪费
    因此char类型适合存储长度固定的数据(比如学生编号,员工编号,身份证号等), 这样既不会浪费空间, 存储数据的效率还比varchar略高一些!
  • varchar类型中如果存入的数据小于最大字符数n, 剩余的空间还可以留给别的数据使用, 所以varchar类型不会造成空间浪费.
    因此varchar类型适合存储长度不固定的数据(用户名/密码/姓名/自我描述等…), 这样虽然存储效率不如char类型, 但至少不会浪费空间！
    MySQL

2. SQL语句

  • SQL ： Structured Query Language 结构化查询语言
  • SQL是在关系数据库上执行数据操作、检索及维护所使用的标准语言
  • 可以用来查询数据，操纵数据，定义数据，控制数据

2.1 Sql语句分类

  • 数据定义语言(DDL)：Data Definition Language
  • 数据操纵语言(DML)：Data Manipulation Language
  • 数据查询语言(DQL)：Data Query Language
  • 数据控制语言(DCL)：Data Control Language
  • 事务控制语言(TCL)：Transaction Control Language

库相关的DDL

创建数据库并指定字符集—CREATE

CREATE DATABASE mybase CHARACTER SET UTF8;

查看所有数据库—SHOW

SHOW DATABASES;

查看当前使用的数据库—SELECT

SELECT DATABASE();

修改数据库编码—ALTER

ALTER DATABASE mybase CHARACTER SET UTF8;

删除数据库—DROP

DROP DATABASE mybase;

切换数据库—USE

USE mybase;

数据库操作

  • 查看所有数据库:
 show databases;
  • 创建一个新的数据库:
 create database mydb1 charset utf8;
  • 删除数据库(如果存在):
drop database if exists mydb1;

数据表操作

  • 查看所有的表:
use mydb1;
show tables;
  • 创建一个学生表:
create table stu(
		id int,
		name varchar(50),
		gender varchar(10),
		birthday date,
		score double
	);
  • 删除学生表:
drop table stu;

**查看**表结构: 

desc stu;

新增/修改/删除表记录

  • 新增表记录:
insert into stu(id,name,gender,birthday,score) 
				values(1,'张飞','男','2000-1-1',89);
insert into stu values(2,'赵云','男','2001-12-11',75);
  • 修改表记录:
update stu set score=score+10;
update stu set score=80 where id=1;
  • 删除表记录:
delete from stu; -- 删除stu表中的所有记录
delete from stu where id>3; -- 删除复合条件的记录

2.2 查询表记录(单表查询)

1.基础查询

select name, sal, bonus from emp;

select distinct bonus from emp; -- distinct用于剔除重复记录

2.where子句查询

WHERE子句查询语法：SELECT 列名称 | * FROM 表名称 WHERE 列 运算符 值

WHERE子句后面跟的是条件，条件可以有多个，多个条件之间用连接词（or | and）进行连接。
MySQL

  • 查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工，显示员工姓名、总薪资
  • ifnull(列名, 值)函数: 判断指定的列是否包含null值，如果有null值，用第二个值替换null值
select name, sal+bonus from emp
where sal+bonus > 3500;

select name, sal+ifnull(bonus,0) from emp
where sal+ifnull(bonus,0) > 3500;
  • 注意查看上面查询结果中的表头，如何将表头中的 sal+bonus 修改为 "总薪资"使用as可以为表头指定别名（格式：列名 as 别名）
    另外as可省略
select name as 姓名, sal+ifnull(bonus,0) as 总薪资 from emp
where sal+ifnull(bonus,0) > 3500;

select name 姓名, sal+ifnull(bonus,0) 总薪资 from emp
where sal+ifnull(bonus,0) > 3500;
  • 查询emp表中【薪资在3000和4500之间】的员工，显示员工姓名和薪资
  • between…and… 在…和…之间

MySQL
MySQL

不等于 <> 或者 ！=

select name, sal from emp where sal<>5000;
select name, sal from emp where sal!=5000;
  • 查询emp表中【薪资为 1400、1600、1800】的员工，显示员工姓名和薪资
    – or: 或，只要满足or两边中的任何一个条件即可！
    – in：只要sal是in后面括号中的任何一个值，就算符合条件！
select name,sal from emp
where sal=1400 or sal=1600 or sal=1800;

select name,sal from emp
where sal in(1400,1600,1800);
  • 查询薪资不为【 1400、1600、1800】的员工，显示员工姓名和薪资
    not表示对条件进行取反
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);

select * from emp
where not(dept is null); -- 查询dept列不为null的
-- 或
select * from emp
where dept is not null; -- 查询dept列不为null的

3.Like模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
可以和通配符（ %、 _ ）配合使用，其中 " % " 表示0或多个任意的字符 , " _ " 表示一个任意的字符。
语法：SELECT 列 | * FROM 表名 WHERE 列名 LIKE 值

  • %: 可以通配表示0个或多个任意字符
  • _: 可以通配表示一个任意字符
select name from stu where name='刘';
select name from stu where name like '刘'; -- 和上面的语句效果相同

select name from stu where name like '刘%'; -- 查询以刘开头的
select name from stu where name like '刘_'; -- 查询以刘开头的,并且名字是两个字的记录

select name from stu where name like '%涛%'; -- 查询姓名中包含'涛'字的员工;

4.Group By分组查询

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT，SUM，AVG，MAX，MIN等函数。
语法：·SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;

  • 对emp表，按照部门对员工进行分组，查看分组后效果
-- 按照部门分组（3个组）
select * from emp group by dept;
-- 对上面3个组的人数进行统计
select dept,count(*) from emp group by dept;

MySQL

  • 对emp表按照职位进行分组，并统计每个职位的人数，显示职位和对应人数
-- 按照职位分组（3个组）
select * from emp group by job;
-- 对上面3个组的人数进行统计
select job,count(*) from emp group by job;

MySQL

  • 对emp表按照部门进行分组，求每个部门的最高薪资(不包含奖金)，显示部门名称和最
    高薪资
-- 根据部门进行分组（3个组）
select * from emp group by dept;
-- 求每个部门（每个组）的最高薪资
select dept,max(sal) from emp group by dept;

MySQL

5.多行函数查询

多行函数也叫做聚合(聚集)函数，根据某一列或所有列进行统计。 包装结果列字段，对结果统计

  • 多行函数不能用在where子句中

  • 多行函数在统计时会对null值进行过滤，直接将null值丢弃，不参与统计。

  • 使用多行函数时,表记录有没有分组查询的结果有很大区别：

    • select count(*) from emp;
      MySQL

    如果没有分组, 所有的查询结果默认是一个组,统计时,一个组就统计出一个结果

    • select count(*) from emp group by job;
      MySQL

    如果有分组, 有多少个组就统计出多少个结果,按照职位分为可以分为5个组,此时可count(*)会分别对这五个组进行统计,因此返回五个组的统计结果。

count(*|列名) -- 统计查询结果有多少行记录
max(列) -- 求某一列中所有值的最大值
min(列) -- 求某一列中所有值的最小值
sum(列) -- 求某一列中所有值的和
avg(列) -- 求某一列中所有值的平均值
  • 统计薪资大于3000的员工个数
select count(id) from emp where sal>3000; -- 7
select count(bonus) from emp where sal>3000; -- 6
-- 由于bonus中有null值，在统计时，null值直接别丢弃，不参与统计

-- emp表中的最高薪资（max函数）
select max(sal) from emp;
-- emp表中的最低薪资（min函数）
select min(sal) from emp;
-- 求emp表中薪资最高的员工
select name,max(sal) from emp;
-- 上面的结果，max(sal)是求薪资中的最大值，而name只是name列中的
--第一个姓名，因此name和max(sal)很可能是不对应的。因此结果是错的！

MySQL

  • 统计emp表中所有员工的薪资总和(不包含奖金)
select sum(sal) from emp; -- 求薪资这一列所有值的和
select sum(bonus) from emp; -- 求奖金这一列所有值的和
  • 统计emp表员工的平均薪资(不包含奖金)
select avg(sal) from emp; -- 薪资平均值（总薪资/12）
select avg(bonus) from emp; -- 奖金平均值（总奖金/11）,有null值

多行函数需要注意的问题:

  • 多行函数和是否分组有关，如果查询结果中的数据没有经过分组，默认整个查询结果是
    一个组，多行函数就会默认统计当前这一个组的数据。产生的结果只有一个。
  • 如果查询结果中的数据经过分组（分的组不止一个），多行函数会根据分的组进行统
    计，有多少个组，就会统计出多少个结果。

统计emp表中的人数

-- 如果没有分组，默认整个查询结果是一个组
select count(*) from emp; -- 14

MySQL
结果返回的就是emp表中的所有人数

根据职业对所有员工进行分组，再统计每组的人数，显示职业和对应人数

-- 根据职业分组，最终分为五个组，统计五个组的人数（3,4，3，3，1）
select gender,count(*) from emp group by job;

MySQL

6.Order排序查询

使用 ORDER BY 子句将结果集中记录根据指定的列排序后再返回

语法：SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC]

ASC(默认)升序，即从低到高；DESC 降序，即从高到低。

order by 要排序的列
	asc: 默认的, 升序, 从低到高
	desc: 降序, 从高到低
	select * from emp order by sal; -- 按照薪资升序排序
	select * from emp order by sal asc; -- 按照薪资升序排序
	select * from emp order by bonus desc; -- 按照奖金降序排序
	select * from emp order by sal+ifnull(bonus,0) desc; -- 按照总薪资降序排序

7.分页查询

  • limit (页码-1)*每页显示的记录数, 每页显示的记录数
-- 分页查询：每页显示3条，返回第1页数据：
select * from emp limit 0, 3;
-- 分页查询：每页显示3条，返回第2页数据：
select * from emp limit 3, 3;
-- 分页查询：每页显示3条，返回第3页数据：
select * from emp limit 6, 3;
-- 分页查询：每页显示3条，返回第4页数据：
select * from emp limit 9, 3;
  • emp表中薪资最高的前3名员工的信息，显示姓名和薪资
-- 按照薪资对员工信息进行排序，降序排序
select name,sal from emp order by sal desc;
-- 在上面查询的基础上，分页查询，每页显示3条，查询第一页
select name,sal from emp order by sal desc limit 0, 3;

MySQL

8.其他函数查询

curdate() -- 返回一个年月日格式的日期
curtime() -- 返回一个时分秒格式的时间
sysdate() -- 返回一个年月日 时分秒格式的时期加时间
now() --  返回一个年月日 时分秒格式的时期加时间

year(),month(),day(),hour(),minute(),second()
	-- 上面的函数分别用于获取年月日时分秒中的年份/月份/天数/小时/分钟/秒值

concat(s1,s2,s3...sn) 
	-- 将s1,s2 等多个字符串合并为一个字符串
concat_ws(x,s1,s2,s3...sn) 
	-- 将传入的多个字符串连接在一起,每两个连接时会通过x作为分隔符进行连接
  • 查询emp表中所有【在1993和1995年之间出生】的员工，显示姓名、出生日期
select name,birthday from emp
where birthday between 1993 and 1995;
-- birthday(日期) 和 1993、1995不能比较，因为类型不同
-- 解决方案1：将1993、1995转换成日期后，再和birthday进行比较
select name,birthday from emp
where birthday between '1993-1-1' and '1995-12-31';
-- 解决方案2：将birthday中的年份取出，和1993、1995进行比较
select name,birthday from emp
where year(birthday) between 1993 and 1995;
  • 查询emp表中本月过生日的所有员工

select * from emp
where month(now())=month(birthday);-- 求本月过生日的员工

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

select * from emp
where ( month(now())+1 )%12=month(birthday); -- 问题：如果当前是12月份

select * from emp
where ( month(now())+1 )%12=month(birthday)%12;-- 问题：如果当前是11月份 最终版本：求下个月过生日的员工
  • concat(s1,s2,s3…sn)
    – 将s1,s2 等多个字符串合并为一个字符串
  • concat_ws(x,s1,s2,s3…sn)
    – 将传入的多个字符串连接在一起,每两个连接时会通过x作为分隔符进行连接
  • .查询emp表中员工的姓名和薪资（薪资格式为: xxx(元) 、 xxx/元）
select name,concat(sal,'(元)') from emp;
select name,concat(sal,'/元') from emp;
select name,concat_ws('/',sal,'元') from emp;

3.mysql的字段约束

3.1主键约束(primary key)

	如果一个列添加了主键约束, 那么这个列中的值就不能为空, 且不能重复
	
除此之外, 主键的列可以唯一的表示一行表记录(可以作为一行表记录的唯一标识, 这就和身份证类似, 身份证可以作为人的唯一标识)

drop table if exists stu; -- 删除stu表
		create table stu( -- 重新创建stu表时，指定主键自增
		    id int,
		    name varchar(20),
		    gender char(1),
		    birthday date
		);
		desc stu; -- 查看表结构

		-- 上面的stu表没有添加主键及主键自增,现在通过修改表的语句来进行添加
		alter table stu modify id int primary key auto_increment;
		-- 如果要删除主键及自增, 先删除自增
		alter table stu modify id int; -- 这是删除自增
		-- 再删除主键约束
		alter table stu drop primary key; -- 这是删除主键约束

3.2 非空约束

如果一个列添加了非空约束, 那么这个列的值就不能是空的(null), 但可以重复

create table stu( -- 重新创建stu表时，指定主键自增
		    gender varchar(10) not null,
		);

		-- 如果建表时每天添加非空约束, 也可以在建表之后再添加!
		alter table stu modify gender char(1) not null;

3.3 唯一约束(unique)

如果一个列添加了唯一约束, 那么这个列的值就是唯一的(也是这个列的值不能重复),但可以为空

create table stu(
			email varchar(20) unique,
		);

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

3.4 外键约束

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

外键: 用来通知数据库 两张表数据之间具有对应关系的列

  • 如何保存两张表（dept、emp）之间的关系
    通常我们会在多的一方(emp)添加一个列(dept_id)来保存一的一方(dept)的主键(id)
    以此来保存两张表数据之间的对应关系
create table emp(
 id int,
 name varchar(50),
 dept_id int,
 foreign key(dept_id) references dept(id)
);

MySQL

4.表关系

常见的表关系分为以下三种：
一对多(多对一)·、一对一、多对多
MySQL
MySQL

MySQL
MySQL

5. 多表查询

where和having子句的区别

相同点: 都是对查询的结果进行筛选过滤
不同点:

  • where是在分组之前对数据进行筛选过滤; 而having是在分组之后对数据进行筛选过滤；

  • where子句中不能使用多行数据, 也不能使用列别名, 但可以使用表别名; 而having中可以使用多行函数, 也可以使用列别名和表别名;

where和on的区别

ON 条件是在生成临时表时使用的条件，它不管 ON 中的条件是否为真，都会返回左边表中的记录；
WHERE 条件是在临时表已经生成后，对临时表进行的过滤条件。因为此时已经没有 LEFT JOIN 的含义（必须返回左侧表的记录）了，所以如果 WHERE 条件不为真的记录就会被过滤掉。

连接查询

  • 查询部门和部门对应的员工信息
select * from dept,emp;

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

  • 笛卡尔积查询
    所谓笛卡尔积查询就是指，查询两张表，其中一张表有m条记录，另一张表有n条记录，查询的结果是m*n条。虽然笛卡尔积查询中包含大量错误数据，但我们可以通过where子句将错误数据剔除，保留下来的就是正确数据。
select * from dept,emp
where emp.dept_id=dept.id;

select * from dept inner join emp
on emp.dept_id=dept.id;
-- 内连接查询,结果和上面的连接查询相同!

– 因为id在两张表中都存在,所以为了区分,必须在列名前面加上[表名.]，通过where子句将笛卡尔积查询中的错误数据剔除,保留正确的数据,这就是连接查询!

左外连接查询

  • 查询【所有部门】及部门对应的员工，如果某个部门下没有员工，员工显示为null
-- 查询的结果中都是有员工的部门和有部门的员工
select * from dept, emp
where emp.dept_id=dept.id;
-- 如果要查询部门表中的所有部门,以及部门对应的员工
select * from dept left join emp
on emp.dept_id=dept.id;

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

右外连接查询

  • 查询【所有员工】及员工所属的部门，如果某个员工没有所属部门，部门显示为null即可
-- 查询的结果中都是有员工的部门和有部门的员工
select * from dept, emp
where emp.dept_id=dept.id;
-- 如果要查询所有员工及员工对应的部门
select * from dept right join emp
on emp.dept_id=dept.id;

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

全外连接查询

如果想将两张表中的所有数据都查询出来(左外+右外并去除重复记录)，可以使用全外连接查询，但是mysql又不支持全外连接查询。
可以使用union将左外连接查询的结果和右外连接查询的结果合并在一起，并去除重复的记录。
注意：union将两条SQL语句执行的结果合并的前提是:

  • 两条SQL语句查询的结果列数必须一致
  • 两条SQL语句查询的结果列名、顺序也必须一致，并且union默认就会将两个查询中重复的记录去除(如果不希望去除重复记录，可以使用
    union all)。
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;

MySQL
MySQL

子查询

将一条SQL语句执行的结果作为另外一条SQL语句的条件来执行,这种查询叫做子查询

  • 列出薪资比’王海涛’的薪资高的所有员工，显示姓名、薪资
-- 求出'王海涛'的薪资
select sal,name from emp where name='王海涛'; -- 2450

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

MySQL

  • .列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位
-- 求出'刘沛霞'从事的职位
select job from emp where name='刘沛霞'; -- 推销员
-- 求出和'刘沛霞'从事相同职位的员工
select name,job from emp
where job=(select job from emp where name='刘沛霞');

多表查询练习

  • 列出在’培优部’任职的员工，假定不知道’培优部’的部门编号，显示部门名称，员工名称。
    ① 连接查询部门表和员工表
select dept.name, emp.name from emp,dept
where emp.dept_id=dept.id;

MySQL
②求出部门名称为’培优部’的所有员工

select dept.name, emp.name from emp,dept
where emp.dept_id=dept.id
 and dept.name='培优部';

MySQL
③ 为emp和dept表分别指定表别名

-- 为emp和dept表分别指定表别名
select d.name '部门', e.name '员工姓名' from emp e,dept d
where e.dept_id=d.id
 and d.name='培优部';
  • (自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名

将emp表看做两张表, emp e1(员工表) emp e2(上级表)

显示的列: e1.name, e2.id, e2.name

查询的表: emp e1, emp e2

连接条件: e1.topid=e2.id
MySQL

select e1.name, e2.id, e2.name
from emp e1, emp e2
where e1.topid=e2.id;

MySQL

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

①求出每种职位的最低薪资(将员工按照职位分组,求每组的最低薪资)

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

②在上面查询的基础上, 筛选出最低薪资大于1500的职位

select job,min(sal) from emp group by job
having min(sal)>1500;
  • 列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资
    ①
select dept_id 部门编号, count(*) 员工人数, avg(sal) 平均薪资
from emp group by dept_id;
上一篇：url 传递参数(特殊字符)解决方法


下一篇：mysql的简单查询 和 复杂查询